import { executeRows } from '../execute-rows.js';
import { sql } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client';

export type HotDealWindow = '24h' | '7d' | '30d';

interface WindowBuckets {
  windowInterval: string;
  tier1Interval: string;
  tier2Interval: string;
}

const WINDOW_BUCKETS: Record<HotDealWindow, WindowBuckets> = {
  '24h': { windowInterval: '24 hours', tier1Interval: '4 hours', tier2Interval: '12 hours' },
  '7d': { windowInterval: '7 days', tier1Interval: '1 day', tier2Interval: '3 days' },
  '30d': { windowInterval: '30 days', tier1Interval: '7 days', tier2Interval: '14 days' },
};

/**
 * Isolate-local memo. queryHotDealIds is the heaviest single query in a home
 * render (5 CTEs over purchases). Result is globally shared — no user-scoped
 * filter — so memoizing per (window, limit) for 30s is safe and slashes
 * repeat work across nearby requests handled by the same isolate.
 */
const HOT_DEAL_IDS_TTL_MS = 30_000;
const _hotDealIdsCache = new Map<string, { value: string[]; expires: number }>();

/**
 * Returns deal IDs ranked by unique-buyer velocity within the given time window.
 * Scoring: each buyer counted once per deal (most recent purchase determines bucket).
 * Bucket weights: tier1=3pts, tier2=2pts, tier3=1pt.
 * Tie-breaks: total units sold DESC, then most recent purchase DESC.
 * Returns up to limit*3 IDs (caller slices after visibility filter).
 */
export async function queryHotDealIds(
  db: DrizzleClient,
  window: HotDealWindow,
  limit: number,
): Promise<string[]> {
  const memoKey = `${window}:${limit}`;
  const now = Date.now();
  const hit = _hotDealIdsCache.get(memoKey);
  if (hit && hit.expires > now) return hit.value;

  const { windowInterval, tier1Interval, tier2Interval } = WINDOW_BUCKETS[window];

  const result = await db.execute<{ deal_id: string }>(sql`
    WITH buyer_scores AS (
      SELECT
        ds.deal_id,
        o.buyer_user_id AS user_id,
        MAX(o.created_at)  AS last_purchase_at,
        SUM(ol.qty)        AS units_bought,
        CASE
          WHEN MAX(o.created_at) > NOW() - INTERVAL ${sql.raw(`'${tier1Interval}'`)} THEN 3
          WHEN MAX(o.created_at) > NOW() - INTERVAL ${sql.raw(`'${tier2Interval}'`)} THEN 2
          ELSE 1
        END AS score
      FROM "order" o
      JOIN order_line ol ON ol.order_id = o.id
      JOIN deal_skus ds ON ds.id = ol.variant_id
      WHERE o.created_at > NOW() - INTERVAL ${sql.raw(`'${windowInterval}'`)}
        AND o.status = 'paid'
      GROUP BY ds.deal_id, o.buyer_user_id
    ),
    deal_scores AS (
      SELECT
        deal_id,
        COUNT(*)::int              AS unique_buyers,
        SUM(score)::int            AS hot_score,
        SUM(units_bought)::int     AS total_units,
        MAX(last_purchase_at)      AS latest_purchase_at
      FROM buyer_scores
      GROUP BY deal_id
    )
    SELECT deal_id
    FROM deal_scores
    ORDER BY hot_score DESC, total_units DESC, latest_purchase_at DESC
    LIMIT ${limit * 3}
  `);

  const value = executeRows<{ deal_id: string }>(result).map((r) => r.deal_id);
  _hotDealIdsCache.set(memoKey, { value, expires: now + HOT_DEAL_IDS_TTL_MS });
  return value;
}