/**
 * FeedDataLoader - server-side data fetcher for the Feed page.
 *
 * loadFeedData(db, { userId? }) returns { forYou, hotDeals, whatsLeft }
 *
 * FOR YOU algorithm per FDS §4.1:
 *  - Up to 3 deals within user's GPS radius if userId present
 *  - Remaining slots from category purchase history
 *  - Remaining from preferences_profile
 *  - Fall back to popular (recency) if no history / guest
 *
 * HOT DEALS: ACTIVE deals where stock_remaining < 10 (low stock cache col).
 * WHAT'S LEFT: ACTIVE deals where windowEnd is approaching (within 24h) OR low stock.
 */

import { eq, and, sql, desc, lt, gt, inArray, isNull, or } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client';
import {
  deals,
  vendors,
  dealImages,
  orderLine,
  order,
  dealTranslations,
  dealVariantAxes,
  dealSkus,
} from '@/server/db/schema';
import { getSystemConfig } from '@/server/db/queries/system-config';
import { queryHotDealIds, type HotDealWindow } from '@/server/catalog/public/hot';
import { queryDealsFeed } from '@/server/db/queries/feed';
import { notSoldOutDrizzle } from '@/server/db/queries/sold-out-filter';

import { PUBLIC_VENDOR_STATES } from '@/server/catalog/_shared/predicates.js';
import type { DealCardDeal } from '@/components/ui/domain/DealCard';
import type { DealType } from '@/lib/deal-types';

/** Enriched deal row suitable for DealCard */
export type DealRow = typeof deals.$inferSelect & {
  vendorName: string;
  city: string;
  imageSrc: string;
  imageAlt: string;
  soldCount7d?: number;
  heSlug?: string | null;
  axesCount?: number;
  defaultSkuId?: string | null;
  skuCount?: number;
  qtyTierTop?: { minQty: number; discountPercent: number } | null;
};

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
)`;

export async function queryActiveDeals(
  db: DrizzleClient,
  options: {
    limit?: number;
    lowStock?: boolean;
    nearExpiry?: boolean;
    /** "What's Left": nearExpiry OR lowQuantity (< 10 remaining). */
    whatsLeft?: boolean;
    dealType?: DealType;
    /** Filter by category UUID. */
    categoryId?: string;
    /** Restrict results to these deal IDs (score-ranked). Caller reorders after fetch. */
    dealIds?: string[];
  } = {},
): Promise<DealRow[]> {
  const {
    limit = 20,
    lowStock = false,
    nearExpiry = false,
    whatsLeft = false,
    dealType,
    categoryId,
    dealIds,
  } = options;

  // Build filters - always exclude deals whose window has already closed,
  // and always restrict to deals from publicly-visible vendors.
  const filters = [
    eq(deals.dealState, 'ACTIVE'),
    or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date()))!,
    inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
    notSoldOutDrizzle(),
  ];

  if (lowStock) {
    // Low stock: stock_remaining cache col < 10 (and not fully sold out = stock > 0).
    filters.push(
      sql`${deals.stockRemaining} IS NOT NULL`,
      sql`${deals.stockRemaining} > 0`,
      sql`${deals.stockRemaining} < 10`,
    );
  }

  if (whatsLeft) {
    // nearExpiry (windowEnd within 24h) OR lowQuantity (stock_remaining < 10 and not sold out)
    const in24h = new Date(Date.now() + 24 * 60 * 60 * 1000);
    const nearExpiryCondition = and(lt(deals.windowEnd, in24h), gt(deals.windowEnd, new Date()))!;
    const lowQuantityCondition = and(
      sql`${deals.stockRemaining} IS NOT NULL`,
      sql`${deals.stockRemaining} > 0`,
      sql`${deals.stockRemaining} < 10`,
    )!;
    filters.push(or(nearExpiryCondition, lowQuantityCondition)!);
  } else if (nearExpiry) {
    const in24h = new Date(Date.now() + 24 * 60 * 60 * 1000);
    filters.push(lt(deals.windowEnd, in24h), gt(deals.windowEnd, new Date()));
  }

  if (dealType) {
    filters.push(eq(deals.dealType, dealType));
  }

  if (categoryId) {
    filters.push(eq(deals.categoryId, categoryId));
  }

  if (dealIds?.length) {
    filters.push(inArray(deals.id, dealIds));
  }

  const ago7d = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000);

  const piSq = db
    .select({ dealId: dealImages.dealId, url: dealImages.url })
    .from(dealImages)
    .where(eq(dealImages.isPrimary, true))
    .as('pi');

  const scSq = db
    .select({
      dealId: dealSkus.dealId,
      count: sql<number>`COALESCE(SUM(${orderLine.qty}), 0)::int`.as('count'),
    })
    .from(orderLine)
    .innerJoin(order, eq(order.id, orderLine.orderId))
    .innerJoin(dealSkus, eq(dealSkus.id, orderLine.variantId))
    .where(and(eq(order.status, 'completed'), gt(orderLine.createdAt, ago7d)))
    .groupBy(dealSkus.dealId)
    .as('sc');

  const rows = await db
    .select({
      deal: deals,
      vendorName: vendors.displayName,
      imageSrc: piSq.url,
      soldCount7d: scSq.count,
      heSlug: dealTranslations.slug,
      axesCount: axesCountSq,
      defaultSkuId: defaultSkuIdSq,
    })
    .from(deals)
    .innerJoin(vendors, eq(deals.vendorId, vendors.id))
    .leftJoin(piSq, eq(piSq.dealId, deals.id))
    .leftJoin(scSq, eq(scSq.dealId, deals.id))
    .leftJoin(
      dealTranslations,
      and(eq(dealTranslations.dealId, deals.id), eq(dealTranslations.locale, 'he')),
    )
    .where(and(...filters))
    .orderBy(desc(deals.createdAt))
    .limit(limit);

  return rows.map(
    ({ deal, vendorName, imageSrc, soldCount7d, heSlug, axesCount, defaultSkuId }) => ({
      ...deal,
      vendorName,
      city: '',
      imageSrc: imageSrc ?? '',
      imageAlt: deal.title,
      soldCount7d: soldCount7d ?? 0,
      heSlug: heSlug ?? undefined,
      axesCount: axesCount ?? 0,
      defaultSkuId: defaultSkuId ?? null,
    }),
  );
}

/**
 * Trending score formula (Hacker-News style with purchase velocity):
 *   score = (purchases_24h * 3 + fill_pct * 10 + units_sold_24h * 0.5) / (age_hours + 2)^1.5
 *
 * Signals:
 *   - purchases_24h: completed purchases in last 24h (strongest recency signal)
 *   - fill_pct: SUM(quantity_sold)/SUM(quantity_total) across deal_skus (overall demand signal)
 *   - units_sold_24h: total units sold in last 24h (high-quantity deal boost)
 *   - age_hours: hours since deal was approved (time decay keeps fresh deals visible)
 */
async function queryTrendingDeals(db: DrizzleClient, limit = 5): Promise<DealRow[]> {
  const ago24h = new Date(Date.now() - 24 * 60 * 60 * 1000);

  const rows = await db
    .select({ deal: deals, vendorName: vendors.displayName, heSlug: dealTranslations.slug })
    .from(deals)
    .innerJoin(vendors, eq(deals.vendorId, vendors.id))
    .leftJoin(dealSkus, eq(dealSkus.dealId, deals.id))
    .leftJoin(orderLine, eq(orderLine.variantId, dealSkus.id))
    .leftJoin(order, eq(order.id, orderLine.orderId))
    .leftJoin(
      dealTranslations,
      and(eq(dealTranslations.dealId, deals.id), eq(dealTranslations.locale, 'he')),
    )
    .where(
      and(
        eq(deals.dealState, 'ACTIVE'),
        or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date()))!,
        inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
        notSoldOutDrizzle(),
      ),
    )
    .groupBy(deals.id, vendors.displayName, dealTranslations.slug)
    .orderBy(
      desc(
        sql`(
          COUNT(${orderLine.id}) FILTER (
            WHERE ${orderLine.createdAt} >= ${ago24h.toISOString()}::timestamptz
            AND ${order.status} = 'completed'
          ) * 3.0
          + COALESCE((
            SELECT SUM(ds.quantity_sold)::float / NULLIF(SUM(ds.quantity_total), 0)
            FROM deal_skus ds WHERE ds.deal_id = ${deals.id}
          ), 0.0) * 10.0
          + COALESCE(SUM(${orderLine.qty}) FILTER (
            WHERE ${orderLine.createdAt} >= ${ago24h.toISOString()}::timestamptz
            AND ${order.status} = 'completed'
          ), 0.0) * 0.5
        ) / POWER(
          EXTRACT(EPOCH FROM (NOW() - COALESCE(${deals.approvedAt}, ${deals.createdAt}))) / 3600.0 + 2.0,
          1.5
        )`,
      ),
    )
    .limit(limit);

  const dealIds = rows.map((r) => r.deal.id);
  let imageMap: Map<string, { url: string }> = new Map();

  if (dealIds.length > 0) {
    const images = await db
      .select({ dealId: dealImages.dealId, url: dealImages.url })
      .from(dealImages)
      .where(and(eq(dealImages.isPrimary, true), inArray(dealImages.dealId, dealIds)))
      .limit(limit);
    imageMap = new Map(images.map((img) => [img.dealId, img]));
  }

  return rows.map(({ deal, vendorName, heSlug }) => ({
    ...deal,
    vendorName,
    city: '',
    imageSrc: imageMap.get(deal.id)?.url ?? '',
    imageAlt: deal.title,
    heSlug: heSlug ?? undefined,
  }));
}

export function toDealCardDeal(row: DealRow, hotThreshold = 10): DealCardDeal {
  const effectiveMinPrice = parseFloat(row.minPrice ?? '0');
  return {
    id: row.id,
    title: row.title,
    vendorName: row.vendorName,
    city: row.city,
    originalPrice: effectiveMinPrice,
    discountedPrice: effectiveMinPrice,
    imageSrc: row.imageSrc,
    imageAlt: row.imageAlt,
    windowEnd:
      row.windowEnd?.toISOString() ?? new Date(Date.now() + 24 * 60 * 60 * 1000).toISOString(),
    stockRemaining: row.stockRemaining ?? 0,
    stockTotal: row.stockRemaining ?? 0,
    dealType: row.dealType,
    discountPercent: row.maxDiscountPercent ?? 0,
    isHotDeal: row.soldCount7d !== undefined ? row.soldCount7d >= hotThreshold : undefined,
    heSlug: row.heSlug ?? undefined,
    minPrice: row.minPrice ?? null,
    maxPrice: row.maxPrice ?? null,
    maxDiscountPercent: row.maxDiscountPercent ?? null,
    axesCount: row.axesCount ?? 0,
    defaultSkuId: row.defaultSkuId ?? null,
    skuCount: row.skuCount ?? 1,
    qtyTierTop: row.qtyTierTop ?? null,
  };
}

// Isolate-local memo — global value read on every page render across many
// module loadData paths; 60s TTL avoids redundant DB hits without staleness risk.
let _hotThresholdCache: { value: number; expires: number } | null = null;
const HOT_THRESHOLD_TTL_MS = 60_000;
export async function getHotDealThreshold(db: DrizzleClient): Promise<number> {
  const now = Date.now();
  if (_hotThresholdCache && _hotThresholdCache.expires > now) {
    return _hotThresholdCache.value;
  }
  const raw = await getSystemConfig(db, 'deal_hot_threshold');
  const value = parseInt(raw, 10) || 10;
  _hotThresholdCache = { value, expires: now + HOT_THRESHOLD_TTL_MS };
  return value;
}

/**
 * Filter a pre-fetched deal pool in-memory for simple deal-row module patterns.
 *
 * Avoids a live DB round-trip when LoadCtx.prefetchedDeals is available.
 * Falls back to null when pool is absent, so callers can query directly.
 *
 * Supported filters (mirrors queryActiveDeals subset used by simple modules):
 *   - limit: slice to first N rows
 *   - dealType: 'GROUP' | 'COUPON' — filter by dealType field
 *   - categoryId: filter by category UUID
 *   - whatsLeft: nearExpiry (windowEnd < now+24h) OR lowQuantity (< 10 remaining)
 *
 * NOT supported (these modules must still query directly):
 *   - lowStock (hot-deals velocity window) — needs queryHotDealsByWindowCards
 *   - near-you geo scoring — needs spatial query
 *   - dealIds-based ordering — needs score-ranked IDs from hot-deals query
 */
export function filterPrefetchedPool(
  pool: DealRow[],
  opts: {
    limit: number;
    dealType?: DealType;
    categoryId?: string;
    whatsLeft?: boolean;
  },
): DealRow[] {
  const { limit, dealType, categoryId, whatsLeft } = opts;
  const now = Date.now();
  const in24h = now + 24 * 60 * 60 * 1000;

  return pool
    .filter((r) => {
      // Mirror the SQL sold-out exclusion: hide single-SKU deals with 0/NULL stock.
      const soldOut = (r.stockRemaining ?? 0) === 0 && (r.axesCount ?? 0) === 0;
      if (soldOut) return false;
      if (dealType && r.dealType !== dealType) return false;
      if (categoryId && r.categoryId !== categoryId) return false;
      if (whatsLeft) {
        const windowEndMs = r.windowEnd ? r.windowEnd.getTime() : null;
        const nearExpiry = windowEndMs !== null && windowEndMs < in24h && windowEndMs > now;
        const stockLeft = r.stockRemaining ?? 0;
        const lowQuantity = stockLeft > 0 && stockLeft < 10;
        if (!nearExpiry && !lowQuantity) return false;
      }
      return true;
    })
    .slice(0, limit);
}

/**
 * Pre-fetch a large pool of active deals for page-layout coalescing.
 *
 * The loader calls this once per guest page render and injects the result into
 * LoadCtx.prefetchedDeals. Deal-row modules that use simple queryActiveDeals
 * filters (forYou, whatsLeft, groupDeals, category) slice the pool in-memory
 * instead of firing their own Neon HTTP round-trips — collapsing N separate
 * subrequests into one.
 *
 * Pool size 50: covers max realistic consumer (~5 deal-row modules × ≤10 cards
 * each = 50). Prior 200 inflated cache.put JSON.stringify payload and tripped
 * Worker 1102 on busy isolates even after coalesce. 50 keeps cache payload
 * ~4x smaller while still satisfying every observed page layout.
 */
export async function prefetchPageDeals(db: DrizzleClient): Promise<DealRow[]> {
  return queryActiveDeals(db, { limit: 50 });
}

/**
 * Load the top N trending deals as DealCardDeal[] for use outside the feed
 * (e.g. the Club page hero carousel).
 */
export async function loadTrendingDeals(db: DrizzleClient, limit = 5): Promise<DealCardDeal[]> {
  const [rows, hotThreshold] = await Promise.all([
    queryTrendingDeals(db, limit),
    getHotDealThreshold(db),
  ]);
  return rows.map((r) => toDealCardDeal(r, hotThreshold));
}

// ─── Per-section query helpers (used by page-organizer modules) ───────────────

/**
 * Returns active deals where stock is low (≥70% sold) as DealCardDeal[].
 * Used by the `deal-row:hot-deals` page-organizer module.
 */
export async function queryHotDealsCards(
  db: DrizzleClient,
  limit = 10,
  hotThreshold?: number,
): Promise<DealCardDeal[]> {
  const [rows, threshold] = await Promise.all([
    queryActiveDeals(db, { limit, lowStock: true }),
    hotThreshold !== undefined ? Promise.resolve(hotThreshold) : getHotDealThreshold(db),
  ]);
  return rows.map((r) => toDealCardDeal(r, threshold));
}

/**
 * Returns hot deals for a given time window as DealCardDeal[].
 * Scores deals by unique-buyer velocity (bucket-decayed), then applies the
 * standard queryActiveDeals visibility filter to ensure only ACTIVE deals
 * with valid vendor state are returned.
 * Used by the deal-row:hot-deals page-organizer module and /api/deals/hot.
 */
export async function queryHotDealsByWindowCards(
  db: DrizzleClient,
  window: HotDealWindow,
  limit = 10,
  hotThreshold?: number,
): Promise<DealCardDeal[]> {
  const [scoredIds, threshold] = await Promise.all([
    queryHotDealIds(db, window, limit),
    hotThreshold !== undefined ? Promise.resolve(hotThreshold) : getHotDealThreshold(db),
  ]);

  if (scoredIds.length === 0) return [];

  const rows = await queryActiveDeals(db, { dealIds: scoredIds, limit: scoredIds.length });
  if (rows.length === 0) return [];

  // Reorder by score rank; queryActiveDeals visibility filter may exclude some IDs
  const resultMap = new Map(rows.map((r) => [r.id, r]));
  const ordered = scoredIds
    .map((id) => resultMap.get(id))
    .filter((r): r is DealRow => r !== undefined)
    .slice(0, limit);

  return ordered.map((r) => toDealCardDeal(r, threshold));
}

/**
 * Returns active GROUP deals as DealCardDeal[].
 * Used by the `deal-row:group-deals` page-organizer module.
 */
export async function queryGroupDealsCards(
  db: DrizzleClient,
  limit = 10,
  hotThreshold?: number,
): Promise<DealCardDeal[]> {
  const [rows, threshold] = await Promise.all([
    queryActiveDeals(db, { limit, dealType: 'GROUP' }),
    hotThreshold !== undefined ? Promise.resolve(hotThreshold) : getHotDealThreshold(db),
  ]);
  return rows.map((r) => toDealCardDeal(r, threshold));
}

/**
 * Returns active deals expiring within 24h as DealCardDeal[].
 * Used by the `deal-row:whats-left` page-organizer module.
 */
export async function queryWhatsLeftCards(
  db: DrizzleClient,
  limit = 10,
  hotThreshold?: number,
): Promise<DealCardDeal[]> {
  const [rows, threshold] = await Promise.all([
    queryActiveDeals(db, { limit, whatsLeft: true }),
    hotThreshold !== undefined ? Promise.resolve(hotThreshold) : getHotDealThreshold(db),
  ]);
  return rows.map((r) => toDealCardDeal(r, threshold));
}

/**
 * Returns recent active deals as DealCardDeal[] (personalization deferred to Phase 4 ML).
 * Used by the `deal-row:auto-scroll` (For You) page-organizer module.
 */
export async function queryForYouCards(
  db: DrizzleClient,
  limit = 10,
  hotThreshold?: number,
): Promise<DealCardDeal[]> {
  const [rows, threshold] = await Promise.all([
    queryActiveDeals(db, { limit }),
    hotThreshold !== undefined ? Promise.resolve(hotThreshold) : getHotDealThreshold(db),
  ]);
  return rows.map((r) => toDealCardDeal(r, threshold));
}

export interface FeedData {
  forYou: DealCardDeal[];
  nearYou: DealCardDeal[];
  hotDeals: DealCardDeal[];
  groupDeals: DealCardDeal[];
  whatsLeft: DealCardDeal[];
  trendingDeals: DealCardDeal[];
}

/**
 * Load personalized feed data.
 * FOR YOU: top active deals, ordered by recency (GPS/category/prefs enhancement deferred to Phase 4 ML).
 * HOT DEALS: deals with >= 70% sold.
 * WHAT'S LEFT: deals expiring within 24h.
 */
export async function loadFeedData(
  db: DrizzleClient,
  { userId: _userId }: { userId?: string } = {},
): Promise<FeedData> {
  const [
    hotThreshold,
    forYouRows,
    nearYouResult,
    hotDealRows,
    groupDealRows,
    whatsLeftRows,
    trendingRows,
  ] = await Promise.all([
    getHotDealThreshold(db),
    queryActiveDeals(db, { limit: 10 }),
    queryDealsFeed(db, {
      preset: 'near-you',
      radius: { lat: 32.0853, lng: 34.7818, km: 10 },
      limit: 10,
      hours: { mode: 'any' },
    }),
    queryActiveDeals(db, { limit: 10, lowStock: true }),
    queryActiveDeals(db, { limit: 10, dealType: 'GROUP' }),
    queryActiveDeals(db, { limit: 10, whatsLeft: true }),
    queryTrendingDeals(db, 5),
  ]);

  return {
    forYou: forYouRows.map((r) => toDealCardDeal(r, hotThreshold)),
    nearYou: nearYouResult.deals,
    hotDeals: hotDealRows.map((r) => toDealCardDeal(r, hotThreshold)),
    groupDeals: groupDealRows.map((r) => toDealCardDeal(r, hotThreshold)),
    whatsLeft: whatsLeftRows.map((r) => toDealCardDeal(r, hotThreshold)),
    trendingDeals: trendingRows.map((r) => toDealCardDeal(r, hotThreshold)),
  };
}