import { and, desc, eq, gt, gte, lt, lte, isNull, isNotNull, inArray, or, sql } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import {
  deals,
  vendors,
  dealTagAssignments,
  dealImages,
  dealVariantAxes,
  dealSkus,
  dealTranslations,
} from '@/server/db/schema.js';
import { PUBLIC_VENDOR_STATES } from '@/server/catalog/_shared/predicates.js';
import type { DealType } from '@/lib/deal-types';
import { queryHotDealIds } from '@/server/db/queries/hot-deals.js';
import { notSoldOutDrizzle } from './sold-out-filter.js';

export interface ListActiveFilteredOpts {
  locale?: 'he' | 'en';
  type?: DealType;
  categoryIds?: string[];
  tagIds?: string[];
  priceMin?: number;
  priceMax?: number;
  sort: 'hot' | 'newest' | 'biggest-discount' | 'ending-soon';
  page: number;
  limit: number;
  offset: number;
  /**
   * Pre-resolved hot IDs to use for sort='hot' ordering. When provided, the
   * caller has run queryHotDealIds in parallel with slug-resolve, so we skip
   * the in-function await. When omitted (legacy callers), we fall back to
   * fetching hot IDs sequentially here.
   */
  hotIds?: string[];
  /**
   * When true, list query LEFT JOINs deal_images and returns primaryImageUrl
   * per row in a single round-trip. Avoids a sequential image-batch query.
   */
  withImages?: boolean;
  /** When true, scope to urgency set (expiring <24h OR stock 1–9). */
  whatsLeft?: boolean;
}

export interface ListActiveFilteredResult {
  rows: Array<
    typeof deals.$inferSelect & {
      primaryImageUrl?: string | null;
      vendorBusinessName: string;
      vendorLogoUrl: string | null;
      /** Number of active variant axes for this deal. 0 = single-SKU. */
      axesCount: number;
      /** ID of the default/only SKU when there are no variant axes; null otherwise. */
      defaultSkuId: string | null;
      /** Headline qty-tier (highest-discount across SKUs) for the grid badge. Null if none. */
      qtyTierTop: { minQty: number; discountPercent: number } | null;
      /** Number of SKUs on this deal (>=1). */
      skuCount: number;
      /** Hebrew slug for canonical deal URL — present when a he translation exists. */
      heSlug?: string | null;
    }
  >;
  total: number;
}

/**
 * Active-deal browse list with filters, sort, and pagination.
 *
 * When `whatsLeft` is set, results are scoped to the urgency set (expiring <24h OR stock 1–9).
 * Divergence from FeedDataLoader.queryActiveDeals: this query additionally requires
 * `minPrice IS NOT NULL` (for price sort/filter) — a whats-left deal with no priced SKU is
 * excluded here. Acceptable: cards need a price to render and be price-filterable.
 */
export async function listActiveFiltered(
  db: DrizzleClient,
  opts: ListActiveFilteredOpts,
): Promise<ListActiveFilteredResult> {
  const conditions = [
    eq(deals.dealState, 'ACTIVE'),
    inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
    or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date())),
    isNotNull(deals.minPrice),
    notSoldOutDrizzle(),
  ];

  if (opts.whatsLeft) {
    const now = new Date();
    const in24h = new Date(now.getTime() + 24 * 60 * 60 * 1000);
    conditions.push(
      or(
        and(lt(deals.windowEnd, in24h), gt(deals.windowEnd, now)),
        and(
          isNotNull(deals.stockRemaining),
          gt(deals.stockRemaining, 0),
          lt(deals.stockRemaining, 10),
        ),
      )!,
    );
  }

  if (opts.type) conditions.push(eq(deals.dealType, opts.type));
  if (opts.categoryIds?.length) conditions.push(inArray(deals.categoryId, opts.categoryIds));
  if (opts.priceMin !== undefined) conditions.push(gte(deals.minPrice, String(opts.priceMin)));
  if (opts.priceMax !== undefined) conditions.push(lte(deals.minPrice, String(opts.priceMax)));
  if (opts.tagIds?.length) {
    conditions.push(
      sql`EXISTS (SELECT 1 FROM ${dealTagAssignments} WHERE ${dealTagAssignments.dealId} = ${deals.id} AND ${dealTagAssignments.tagId} IN (${sql.join(
        opts.tagIds.map((id) => sql`${id}`),
        sql`, `,
      )}))`,
    );
  }

  let orderBy;
  if (opts.sort === 'hot') {
    const hotIds = opts.hotIds ?? (await queryHotDealIds(db, '24h', 50));
    if (hotIds.length > 0) {
      const idArray = sql`ARRAY[${sql.join(
        hotIds.map((id) => sql`${id}`),
        sql`, `,
      )}]::uuid[]`;
      orderBy = sql`array_position(${idArray}, ${deals.id}) ASC NULLS LAST, ${deals.createdAt} DESC`;
    } else {
      orderBy = desc(deals.createdAt);
    }
  } else if (opts.sort === 'biggest-discount') {
    orderBy = sql`${deals.maxDiscountPercent} DESC NULLS LAST`;
  } else if (opts.sort === 'ending-soon') {
    orderBy = sql`${deals.windowEnd} ASC NULLS LAST`;
  } else {
    orderBy = desc(deals.createdAt);
  }

  // Subqueries for variant-aware card display (Wave 9)
  const axesCountSq = sql<number>`(
    SELECT COUNT(*)::int FROM ${dealVariantAxes}
    WHERE ${dealVariantAxes.dealId} = ${deals.id}
    AND ${dealVariantAxes.isActive} = true
  )`;
  const defaultSkuIdSq = sql<string | null>`(
    SELECT id FROM ${dealSkus}
    WHERE ${dealSkus.dealId} = ${deals.id}
    AND ${dealSkus.optionIdsHash} = 'default'
    LIMIT 1
  )`;
  // Subqueries for qty-tier badge (feat-qty-tier-bundles)
  const qtyTierTopSq = sql<{ minQty: number; discountPercent: number } | null>`(
    SELECT json_build_object('minQty', t.min_qty, 'discountPercent', t.discount_percent)
    FROM sku_qty_tiers t
    JOIN deal_skus s ON s.id = t.deal_sku_id
    WHERE s.deal_id = ${deals.id}
    ORDER BY t.discount_percent DESC, t.min_qty ASC
    LIMIT 1
  )`;
  const skuCountSq = sql<number>`(
    SELECT COUNT(*)::int FROM deal_skus s2 WHERE s2.deal_id = ${deals.id}
  )`;
  const heSlugSq = sql<string | null>`(
    SELECT dt_he.slug FROM ${dealTranslations} dt_he
    WHERE dt_he.deal_id = ${deals.id} AND dt_he.locale = 'he' AND dt_he.status = 'OK'
    LIMIT 1
  )`;
  const localizedTitleSq = opts.locale
    ? sql<string>`COALESCE((
        SELECT NULLIF(dt_locale.title, '') FROM ${dealTranslations} dt_locale
        WHERE dt_locale.deal_id = ${deals.id}
        AND dt_locale.locale = ${opts.locale}
        AND dt_locale.status = 'OK'
        LIMIT 1
      ), ${deals.title})`
    : deals.title;

  if (opts.withImages) {
    const [rowsResult, totalResult] = await Promise.all([
      db
        .select({
          deals,
          primaryImageUrl: dealImages.url,
          vendorBusinessName: vendors.businessName,
          vendorLogoUrl: vendors.logoUrl,
          axesCount: axesCountSq,
          defaultSkuId: defaultSkuIdSq,
          qtyTierTop: qtyTierTopSq,
          skuCount: skuCountSq,
          heSlug: heSlugSq,
          localizedTitle: localizedTitleSq,
        })
        .from(deals)
        .innerJoin(vendors, eq(deals.vendorId, vendors.id))
        .leftJoin(dealImages, and(eq(dealImages.dealId, deals.id), eq(dealImages.isPrimary, true)))
        .where(and(...conditions))
        .orderBy(orderBy)
        .limit(opts.limit)
        .offset(opts.offset),
      db
        .select({ count: sql<number>`COUNT(*)::int` })
        .from(deals)
        .innerJoin(vendors, eq(deals.vendorId, vendors.id))
        .where(and(...conditions)),
    ]);

    return {
      rows: rowsResult.map((r) => ({
        ...r.deals,
        primaryImageUrl: r.primaryImageUrl ?? null,
        vendorBusinessName: r.vendorBusinessName,
        vendorLogoUrl: r.vendorLogoUrl ?? null,
        axesCount: r.axesCount ?? 0,
        defaultSkuId: r.defaultSkuId ?? null,
        qtyTierTop: r.qtyTierTop ?? null,
        skuCount: r.skuCount ?? 1,
        heSlug: r.heSlug ?? null,
        title: r.localizedTitle,
      })),
      total: totalResult[0]?.count ?? 0,
    };
  }

  const [rowsResult, totalResult] = await Promise.all([
    db
      .select({
        deals,
        vendors,
        axesCount: axesCountSq,
        defaultSkuId: defaultSkuIdSq,
        qtyTierTop: qtyTierTopSq,
        skuCount: skuCountSq,
        heSlug: heSlugSq,
        localizedTitle: localizedTitleSq,
      })
      .from(deals)
      .innerJoin(vendors, eq(deals.vendorId, vendors.id))
      .where(and(...conditions))
      .orderBy(orderBy)
      .limit(opts.limit)
      .offset(opts.offset),
    db
      .select({ count: sql<number>`COUNT(*)::int` })
      .from(deals)
      .innerJoin(vendors, eq(deals.vendorId, vendors.id))
      .where(and(...conditions)),
  ]);

  return {
    rows: rowsResult.map((r) => ({
      ...r.deals,
      vendorBusinessName: r.vendors.businessName,
      vendorLogoUrl: r.vendors.logoUrl ?? null,
      axesCount: r.axesCount ?? 0,
      defaultSkuId: r.defaultSkuId ?? null,
      qtyTierTop: r.qtyTierTop ?? null,
      skuCount: r.skuCount ?? 1,
      heSlug: r.heSlug ?? null,
      title: r.localizedTitle,
    })),
    total: totalResult[0]?.count ?? 0,
  };
}