import { sql } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client';
import type { LoadCtx } from '@/server/page-layout/types';
import type { Config } from './config';
import {
  queryActiveDeals,
  filterPrefetchedPool,
  toDealCardDeal,
  getHotDealThreshold,
} from '@/features/feed/FeedDataLoader';
import { captureCaught } from '@/server/observability/capture.server';

async function resolveAffinityCategoryId(
  db: DrizzleClient,
  userId: string | undefined,
): Promise<string | null> {
  if (userId) {
    // Most-purchased category for this user.
    const result = (await db.execute(sql`
      SELECT d.category_id AS "categoryId", COUNT(*) AS c
      FROM "order" o
      JOIN order_line ol ON ol.order_id = o.id
      JOIN deal_skus ds ON ds.id = ol.variant_id
      JOIN deals d ON d.id = ds.deal_id
      WHERE o.buyer_user_id = ${userId}
        AND d.category_id IS NOT NULL
      GROUP BY d.category_id
      ORDER BY c DESC
      LIMIT 1
    `)) as { rows: Array<{ categoryId: string }> };
    const row = result.rows[0] as { categoryId: string } | undefined;
    if (row?.categoryId) return row.categoryId;
  }
  // Guest / no purchase history: most active-deal category.
  const result = (await db.execute(sql`
    SELECT category_id AS "categoryId", COUNT(*) AS c
    FROM deals
    WHERE deal_state = 'ACTIVE' AND category_id IS NOT NULL
    GROUP BY category_id
    ORDER BY c DESC
    LIMIT 1
  `)) as { rows: Array<{ categoryId: string }> };
  const row = result.rows[0] as { categoryId: string } | undefined;
  return row?.categoryId ?? null;
}

export async function loadData(db: DrizzleClient, config: Config, ctx: LoadCtx) {
  try {
    const hotThreshold =
      ctx.hotDealThreshold !== undefined ? ctx.hotDealThreshold : await getHotDealThreshold(db);

    // Guest fast path: derive category + deal list entirely from the prefetched
    // pool — zero extra Neon HTTP calls. Without this, two sequential DB
    // round-trips (category lookup + deal query) push cold-cache home-page SSR
    // over the 10 ms Workers CPU ceiling → error 1102.
    if (ctx.prefetchedDeals) {
      const counts = new Map<string, number>();
      for (const d of ctx.prefetchedDeals) {
        if (d.categoryId) counts.set(d.categoryId, (counts.get(d.categoryId) ?? 0) + 1);
      }
      let topId: string | null = null;
      let topCount = 0;
      for (const [id, c] of counts) {
        if (c > topCount) {
          topCount = c;
          topId = id;
        }
      }
      if (!topId) return [];
      const rows = filterPrefetchedPool(ctx.prefetchedDeals, {
        limit: config.limit,
        categoryId: topId,
      });
      return rows.map((r) => toDealCardDeal(r, hotThreshold));
    }

    // Logged-in path: personalised by purchase history, direct DB queries.
    const categoryId = await resolveAffinityCategoryId(db, ctx.userId);
    if (!categoryId) return [];
    const rows = await queryActiveDeals(db, { limit: config.limit, categoryId });
    return rows.map((r) => toDealCardDeal(r, hotThreshold));
  } catch (err) {
    captureCaught(err, {
      scope: 'server.page-layout.modules.deal-row-affinity.loadData',
      severity: 'warning',
    });
    return null;
  }
}