import { eq, inArray, and, gt, isNull, or, sql } from 'drizzle-orm';
import { israelWeekdayIndex } from '@/lib/datetime';
import type { DrizzleClient } from '@/server/db/client';
import type { LoadCtx } from '@/server/page-layout/types';
import { vendors, vendorAddresses, businessHours, deals } from '@/server/db/schema';
import type { VendorCardData } from '@/components/ui/domain/VendorCard';
import type { Config } from './config';
import { captureCaught } from '@/server/observability/capture.server';

/**
 * Load the pinned vendors in the order specified by vendorIds.
 * Returns null on error so the renderer can skip this module gracefully.
 */
export async function loadData(
  db: DrizzleClient,
  config: Config,
  _ctx: LoadCtx,
): Promise<VendorCardData[] | null> {
  if (config.vendorIds.length === 0) return [];

  try {
    // Run vendor details and deal counts in parallel — they are independent queries
    const [rows, dealCounts] = await Promise.all([
      db
        .select({
          id: vendors.id,
          businessName: vendors.businessName,
          displayName: vendors.displayName,
          logoUrl: vendors.logoUrl,
          heroImageUrl: vendors.heroImageUrl,
          reviewsScore: vendors.reviewsScore,
          reviewsCount: vendors.reviewsCount,
          businessTypeNames: sql<string[]>`COALESCE(
          (SELECT array_agg(bt.name_he ORDER BY bt.sort_order)
             FROM vendor_business_types vbt
             JOIN business_types bt ON bt.id = vbt.business_type_id
            WHERE vbt.vendor_id = ${vendors.id}),
          ARRAY[]::text[]
        )`,
          city: sql<string>`COALESCE(${vendorAddresses.city}, '')`,
          lat: vendorAddresses.lat,
          lng: vendorAddresses.lng,
          mondayOpen: businessHours.mondayOpen,
          mondayClose: businessHours.mondayClose,
          mondayClosed: businessHours.mondayClosed,
          tuesdayOpen: businessHours.tuesdayOpen,
          tuesdayClose: businessHours.tuesdayClose,
          tuesdayClosed: businessHours.tuesdayClosed,
          wednesdayOpen: businessHours.wednesdayOpen,
          wednesdayClose: businessHours.wednesdayClose,
          wednesdayClosed: businessHours.wednesdayClosed,
          thursdayOpen: businessHours.thursdayOpen,
          thursdayClose: businessHours.thursdayClose,
          thursdayClosed: businessHours.thursdayClosed,
          fridayOpen: businessHours.fridayOpen,
          fridayClose: businessHours.fridayClose,
          fridayClosed: businessHours.fridayClosed,
          saturdayOpen: businessHours.saturdayOpen,
          saturdayClose: businessHours.saturdayClose,
          saturdayClosed: businessHours.saturdayClosed,
          sundayOpen: businessHours.sundayOpen,
          sundayClose: businessHours.sundayClose,
          sundayClosed: businessHours.sundayClosed,
        })
        .from(vendors)
        .leftJoin(vendorAddresses, eq(vendorAddresses.vendorId, vendors.id))
        .leftJoin(businessHours, eq(businessHours.vendorId, vendors.id))
        .where(
          and(
            inArray(vendors.id, config.vendorIds),
            inArray(vendors.accountState, ['ACTIVE', 'VETERAN']),
          ),
        ),
      // Count active deals per vendor
      db
        .select({
          vendorId: deals.vendorId,
          count: sql<number>`count(*)::int`,
          types: sql<string[]>`array_agg(DISTINCT ${deals.dealType}::text)`,
        })
        .from(deals)
        .where(
          and(
            inArray(deals.vendorId, config.vendorIds),
            eq(deals.dealState, 'ACTIVE'),
            or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date())),
          ),
        )
        .groupBy(deals.vendorId),
    ]);

    const dealsCountMap = new Map(dealCounts.map((d) => [d.vendorId, d.count]));
    const dealsTypesMap = new Map(dealCounts.map((d) => [d.vendorId, d.types ?? []]));

    // Map rows to VendorCardData and preserve the admin-specified order
    const rowMap = new Map<string, VendorCardData>(
      rows.map((r) => [
        r.id,
        {
          id: r.id,
          businessName: r.businessName,
          displayName: r.displayName,
          logoUrl: r.logoUrl ?? null,
          heroImageUrl: r.heroImageUrl ?? null,
          reviewsScore: r.reviewsScore,
          reviewsCount: r.reviewsCount,
          city: r.city,
          lat: r.lat ?? null,
          lng: r.lng ?? null,
          businessTypeNames: r.businessTypeNames ?? [],
          activeDealsCount: dealsCountMap.get(r.id) ?? 0,
          activeDealsTypes: dealsTypesMap.get(r.id) ?? [],
          todayHours: getTodayHours(r),
          yesterdayHours: getYesterdayHours(r),
        } satisfies VendorCardData,
      ]),
    );

    return config.vendorIds.map((id) => rowMap.get(id)).filter((v): v is VendorCardData => !!v);
  } catch (err) {
    captureCaught(err, {
      scope: 'server.page-layout.modules.vendor-spotlight.loadData',
      severity: 'warning',
    });
    return null;
  }
}

// ─── Helpers ──────────────────────────────────────────────────────────────────

type HoursRow = {
  mondayOpen: string | null;
  mondayClose: string | null;
  mondayClosed: boolean | null;
  tuesdayOpen: string | null;
  tuesdayClose: string | null;
  tuesdayClosed: boolean | null;
  wednesdayOpen: string | null;
  wednesdayClose: string | null;
  wednesdayClosed: boolean | null;
  thursdayOpen: string | null;
  thursdayClose: string | null;
  thursdayClosed: boolean | null;
  fridayOpen: string | null;
  fridayClose: string | null;
  fridayClosed: boolean | null;
  saturdayOpen: string | null;
  saturdayClose: string | null;
  saturdayClosed: boolean | null;
  sundayOpen: string | null;
  sundayClose: string | null;
  sundayClosed: boolean | null;
};

function getTodayHours(row: HoursRow): string | null {
  const dayIndex = israelWeekdayIndex();
  return getHoursForDay(row, dayIndex);
}

function getYesterdayHours(row: HoursRow): string | null {
  const dayIndex = israelWeekdayIndex();
  return getHoursForDay(row, (dayIndex + 6) % 7);
}

function getHoursForDay(row: HoursRow, dayIndex: number): string | null {
  const days = [
    { open: row.sundayOpen, close: row.sundayClose, closed: row.sundayClosed },
    { open: row.mondayOpen, close: row.mondayClose, closed: row.mondayClosed },
    { open: row.tuesdayOpen, close: row.tuesdayClose, closed: row.tuesdayClosed },
    { open: row.wednesdayOpen, close: row.wednesdayClose, closed: row.wednesdayClosed },
    { open: row.thursdayOpen, close: row.thursdayClose, closed: row.thursdayClosed },
    { open: row.fridayOpen, close: row.fridayClose, closed: row.fridayClosed },
    { open: row.saturdayOpen, close: row.saturdayClose, closed: row.saturdayClosed },
  ];
  const day = days[dayIndex];
  if (!day) return null;
  if (day.closed) return 'closed';
  if (day.open && day.close) return `${day.open}-${day.close}`;
  return null;
}