/**
 * Vendor directory queries - public-facing store listing with filters.
 *
 * Used by GET /api/stores to power the customer Stores page.
 */

import { israelWeekdayIndex, TZ } from '@/lib/datetime';
import {
  eq,
  inArray,
  and,
  gt,
  sql,
  ilike,
  or,
  count,
  type SQL,
  type SQLWrapper,
} from 'drizzle-orm';
import type { FeedFilter } from '@/server/schemas/feed.js';
import type { DrizzleClient } from '../client.js';
import { vendors, vendorAddresses, businessHours } from '../schema.js';

export interface VendorDirectoryRow {
  id: string;
  businessName: string;
  displayName: string;
  logoUrl: string | null;
  heroImageUrl: string | null;
  reviewsScore: string;
  reviewsCount: number;
  city: string;
  lat: string | null;
  lng: string | null;
  businessTypeNames: string[];
  activeDealsCount: number;
  activeDealsTypes: string[];
  todayHours: string | null;
  yesterdayHours: string | null;
}

export interface ListForDirectoryOpts {
  city?: string;
  query?: string;
  cursor?: string;
  page?: number;
  limit?: number;
  lat?: number;
  lng?: number;
  businessTypeId?: string;
  hours?: FeedFilter['hours'];
  sortBy?: 'rating' | 'deals' | 'distance';
}

const HOURS_BY_DAY = [
  {
    open: businessHours.sundayOpen,
    close: businessHours.sundayClose,
    closed: businessHours.sundayClosed,
  },
  {
    open: businessHours.mondayOpen,
    close: businessHours.mondayClose,
    closed: businessHours.mondayClosed,
  },
  {
    open: businessHours.tuesdayOpen,
    close: businessHours.tuesdayClose,
    closed: businessHours.tuesdayClosed,
  },
  {
    open: businessHours.wednesdayOpen,
    close: businessHours.wednesdayClose,
    closed: businessHours.wednesdayClosed,
  },
  {
    open: businessHours.thursdayOpen,
    close: businessHours.thursdayClose,
    closed: businessHours.thursdayClosed,
  },
  {
    open: businessHours.fridayOpen,
    close: businessHours.fridayClose,
    closed: businessHours.fridayClosed,
  },
  {
    open: businessHours.saturdayOpen,
    close: businessHours.saturdayClose,
    closed: businessHours.saturdayClosed,
  },
] as const;

function minutesOfDay(column: SQLWrapper): SQL<number> {
  return sql<number>`(
    EXTRACT(HOUR FROM ${column})::int * 60 + EXTRACT(MINUTE FROM ${column})::int
  )`;
}

export function buildDirectoryHoursCondition(hours: FeedFilter['hours']): SQL | undefined {
  if (hours.mode === 'any') return undefined;

  if (hours.mode === 'openNow') {
    const currentDay = sql<number>`EXTRACT(DOW FROM now() AT TIME ZONE ${TZ})::int`;
    const currentMinutes = sql<number>`(
      EXTRACT(HOUR FROM now() AT TIME ZONE ${TZ})::int * 60 +
      EXTRACT(MINUTE FROM now() AT TIME ZONE ${TZ})::int
    )`;
    const dayConditions = HOURS_BY_DAY.map((day, dayIndex) => {
      const previous = HOURS_BY_DAY[(dayIndex + 6) % 7]!;
      const openMinutes = minutesOfDay(day.open);
      const closeMinutes = minutesOfDay(day.close);
      const previousOpenMinutes = minutesOfDay(previous.open);
      const previousCloseMinutes = minutesOfDay(previous.close);

      return sql`(
        ${currentDay} = ${dayIndex} AND (
          (
            ${day.closed} = false AND ${day.open} IS NOT NULL AND ${day.close} IS NOT NULL AND
            (
              (${closeMinutes} > ${openMinutes} AND ${currentMinutes} BETWEEN ${openMinutes} AND ${closeMinutes}) OR
              (${closeMinutes} <= ${openMinutes} AND ${currentMinutes} >= ${openMinutes})
            )
          ) OR (
            ${previous.closed} = false AND ${previous.open} IS NOT NULL AND ${previous.close} IS NOT NULL AND
            ${previousCloseMinutes} <= ${previousOpenMinutes} AND ${currentMinutes} <= ${previousCloseMinutes}
          )
        )
      )`;
    });

    return sql`(${sql.join(dayConditions, sql` OR `)})`;
  }

  const day = HOURS_BY_DAY[hours.dayOfWeek]!;
  const previous = HOURS_BY_DAY[(hours.dayOfWeek + 6) % 7]!;
  const openMinutes = minutesOfDay(day.open);
  const closeMinutes = minutesOfDay(day.close);
  const previousOpenMinutes = minutesOfDay(previous.open);
  const previousCloseMinutes = minutesOfDay(previous.close);
  const requestEnd = hours.endMin < hours.startMin ? hours.endMin + 1440 : hours.endMin;

  const sameDay = sql`(
    ${day.closed} = false AND ${day.open} IS NOT NULL AND ${day.close} IS NOT NULL AND
    ${openMinutes} <= ${hours.startMin} AND
    (CASE WHEN ${closeMinutes} <= ${openMinutes} THEN ${closeMinutes} + 1440 ELSE ${closeMinutes} END) >= ${requestEnd}
  )`;
  if (hours.endMin < hours.startMin) return sameDay;

  return sql`(
    ${sameDay} OR (
      ${previous.closed} = false AND ${previous.open} IS NOT NULL AND ${previous.close} IS NOT NULL AND
      ${previousCloseMinutes} <= ${previousOpenMinutes} AND
      ${previousOpenMinutes} <= ${hours.startMin + 1440} AND
      ${previousCloseMinutes} + 1440 >= ${hours.endMin + 1440}
    )
  )`;
}

/**
 * List active/veteran vendors for the public directory.
 *
 * - Filters by city if provided.
 * - Cursor-paginates by vendor id (UUID lexicographic order).
 * - Sorted by reviewsScore DESC + totalSales DESC (geosort is a planned follow-up).
 */
export async function listForDirectory(
  db: DrizzleClient,
  opts: ListForDirectoryOpts = {},
): Promise<{
  vendors: VendorDirectoryRow[];
  nextCursor: string | null;
  totalCount: number;
  totalPages: number;
}> {
  const limit = opts.limit ?? 12;
  const page = opts.page ?? 1;
  const offset = (page - 1) * limit;

  // Build WHERE conditions
  const conditions = [inArray(vendors.accountState, ['ACTIVE', 'VETERAN'])];

  if (opts.cursor) {
    conditions.push(gt(vendors.id, opts.cursor));
  }

  if (opts.businessTypeId) {
    conditions.push(sql`EXISTS (
      SELECT 1 FROM vendor_business_types vbt
       WHERE vbt.vendor_id = ${vendors.id}
         AND vbt.business_type_id = ${opts.businessTypeId}
    )`);
  }

  if (opts.city) {
    conditions.push(eq(vendorAddresses.city, opts.city));
  }

  if (opts.query) {
    const q = `%${opts.query}%`;
    conditions.push(or(ilike(vendors.businessName, q), ilike(vendors.displayName, q))!);
  }

  const hoursCondition = buildDirectoryHoursCondition(opts.hours ?? { mode: 'any' });
  if (hoursCondition) conditions.push(hoursCondition);

  // Count total matching vendors for pagination
  const [countRow] = await db
    .select({ total: count() })
    .from(vendors)
    .leftJoin(
      vendorAddresses,
      and(eq(vendorAddresses.vendorId, vendors.id), eq(vendorAddresses.isPublic, true)),
    )
    .leftJoin(businessHours, eq(businessHours.vendorId, vendors.id))
    .where(and(...conditions));

  const totalCount = countRow?.total ?? 0;
  const totalPages = Math.max(1, Math.ceil(totalCount / limit));

  const activeDealsCountSql = sql<number>`(
    SELECT COUNT(*)::int FROM deals d
    WHERE d.vendor_id = ${vendors.id}
      AND d.deal_state = 'ACTIVE'
      AND (d.window_end IS NULL OR d.window_end > now())
  )`;
  const distanceSql =
    opts.sortBy === 'distance' && opts.lat !== undefined && opts.lng !== undefined
      ? sql<number>`(
          6371 * 2 * ASIN(SQRT(
            POWER(SIN(RADIANS((${vendorAddresses.lat}::double precision - ${opts.lat}) / 2)), 2) +
            COS(RADIANS(${opts.lat})) * COS(RADIANS(${vendorAddresses.lat}::double precision)) *
            POWER(SIN(RADIANS((${vendorAddresses.lng}::double precision - ${opts.lng}) / 2)), 2)
          ))
        )`
      : null;
  const directoryOrder = distanceSql
    ? sql`${distanceSql} ASC NULLS LAST, ${vendors.reviewsScore} DESC, ${vendors.id} ASC`
    : opts.sortBy === 'deals'
      ? sql`${activeDealsCountSql} DESC, ${vendors.reviewsScore} DESC, ${vendors.id} ASC`
      : sql`${vendors.reviewsScore} DESC, ${vendors.totalSales} DESC, ${vendors.id} ASC`;

  // Fetch vendors page (use offset when page param given, cursor otherwise)
  const rows = await db
    .select({
      id: vendors.id,
      businessName: vendors.businessName,
      displayName: vendors.displayName,
      logoUrl: vendors.logoUrl,
      heroImageUrl: vendors.heroImageUrl,
      reviewsScore: vendors.reviewsScore,
      reviewsCount: vendors.reviewsCount,
      totalSales: vendors.totalSales,
      city: sql<string>`COALESCE(${vendorAddresses.city}, '')`,
      lat: vendorAddresses.lat,
      lng: vendorAddresses.lng,
      // Hours for today (will be processed in JS)
      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,
      // Inline deals aggregation to avoid a second sequential Neon RTT
      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[]
      )`,
      activeDealsCount: activeDealsCountSql,
      activeDealsTypes: sql<string[]>`(
        SELECT array_agg(DISTINCT d.deal_type::text) FROM deals d
        WHERE d.vendor_id = ${vendors.id}
          AND d.deal_state = 'ACTIVE'
          AND (d.window_end IS NULL OR d.window_end > now())
      )`,
    })
    .from(vendors)
    .leftJoin(
      vendorAddresses,
      and(eq(vendorAddresses.vendorId, vendors.id), eq(vendorAddresses.isPublic, true)),
    )
    .leftJoin(businessHours, eq(businessHours.vendorId, vendors.id))
    .where(and(...conditions))
    .orderBy(directoryOrder)
    .limit(opts.page !== undefined ? limit : limit + 1)
    .offset(opts.page !== undefined ? offset : 0);

  const pageRows = opts.page !== undefined ? rows : rows.slice(0, limit);
  const hasMore = opts.page !== undefined ? page < totalPages : rows.length > limit;
  const nextCursor = hasMore ? (pageRows[pageRows.length - 1]?.id ?? null) : null;

  const result: VendorDirectoryRow[] = pageRows.map((r) => ({
    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: r.activeDealsCount ?? 0,
    activeDealsTypes: r.activeDealsTypes ?? [],
    todayHours: getTodayHours(r),
    yesterdayHours: getYesterdayHours(r),
  }));

  return { vendors: result, nextCursor, totalCount, totalPages };
}

/**
 * Get distinct cities from active vendors' addresses.
 */
export async function getDistinctCities(db: DrizzleClient): Promise<string[]> {
  const rows = await db
    .selectDistinct({ city: vendorAddresses.city })
    .from(vendorAddresses)
    .innerJoin(vendors, eq(vendors.id, vendorAddresses.vendorId))
    .where(
      and(
        inArray(vendors.accountState, ['ACTIVE', 'VETERAN']),
        sql`${vendorAddresses.city} != ''`,
        eq(vendorAddresses.isPublic, true),
      ),
    )
    .orderBy(vendorAddresses.city);
  return rows.map((r) => r.city);
}

/**
 * Re-export active business types for the stores filters envelope.
 */
export { listActiveBusinessTypes as getActiveBusinessTypes } from './business-types.js';

// ─── 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) {
    // Postgres time columns return HH:MM:SS — normalize to HH:MM for isVendorOpen regex
    return `${day.open.slice(0, 5)}-${day.close.slice(0, 5)}`;
  }
  return null;
}