import type { DrizzleClient, TxDrizzleClient } from '@/server/db/client.js';
import type { MultidealEnv } from '@/server/env.js';
import type { CronEnv } from '@/server/cron/deal-expiry.js';
import { writeNotification } from '@/server/notifications/send.js';
import { invalidateCatalog } from '@/server/cache/invalidate.js';
import { sql } from 'drizzle-orm';

export async function checkAndEmitRestockAlerts(
  db: TxDrizzleClient,
  env: CronEnv,
  dealId: string,
): Promise<boolean> {
  // Step 0: flip SOLD_OUT → ACTIVE when stock_remaining > 0
  // Ensures the deal is buyable when a subscriber lands on the page.
  // Runs OUTSIDE the transaction so it commits before step 1 reads d.stock_remaining.
  const restockResult = (await db.execute(sql`
    UPDATE deals
    SET deal_state = 'ACTIVE', sold_out_at = NULL
    WHERE id = ${dealId}::uuid
      AND deal_state = 'SOLD_OUT'
      AND stock_remaining > 0
    RETURNING id
  `)) as { rows: unknown[] };
  const restocked = restockResult.rows.length > 0;
  if (restocked) {
    await invalidateCatalog(db, { scope: 'deal', dealId });
  }

  // Step 1 + 2 in a single transaction:
  //   - SELECT pending subscribers FOR UPDATE SKIP LOCKED (prevents double-notify from concurrent cron instances)
  //   - writeNotification per subscriber (inserts notification + outbox row)
  //   - SET notified_at = NOW() per subscriber

  await db.transaction(async (tx) => {
    const txCtx = { db: tx as unknown as DrizzleClient, env: env as unknown as MultidealEnv };

    // Deal-level: deal now has stock, user watched entire deal
    const dealRows = (await tx.execute(sql`
      SELECT sw.id, sw.user_id, d.title_he AS deal_title, d.slug AS deal_slug
      FROM stock_watchlist sw
      JOIN deals d ON d.id = sw.deal_id
      WHERE sw.deal_id = ${dealId}::uuid
        AND sw.sku_id IS NULL
        AND sw.notified_at IS NULL
        AND sw.expires_at > NOW()
        AND d.stock_remaining > 0
      FOR UPDATE OF sw SKIP LOCKED
    `)) as { rows: Array<Record<string, unknown>> };

    // SKU-level: specific SKU now has available stock
    const skuRows = (await tx.execute(sql`
      SELECT sw.id, sw.user_id, sw.sku_id, d.title_he AS deal_title, d.slug AS deal_slug
      FROM stock_watchlist sw
      JOIN deals d ON d.id = sw.deal_id
      JOIN deal_skus ds ON ds.id = sw.sku_id
      WHERE sw.deal_id = ${dealId}::uuid
        AND sw.sku_id IS NOT NULL
        AND sw.notified_at IS NULL
        AND sw.expires_at > NOW()
        AND (ds.quantity_total - ds.quantity_sold) > 0
      FOR UPDATE OF sw SKIP LOCKED
    `)) as { rows: Array<Record<string, unknown>> };

    const allRows = [...dealRows.rows, ...skuRows.rows];

    for (const row of allRows) {
      const userId = row['user_id'] as string;
      const dealSlug = (row['deal_slug'] as string | null) ?? dealId;
      const dealTitle = (row['deal_title'] as string | null) ?? '';
      const watchId = row['id'] as string;

      await writeNotification({
        userId,
        event: 'deal.back_in_stock',
        data: { dealTitle },
        ctx: txCtx,
        link: `/deals/${dealSlug}`,
      });

      await tx.execute(sql`
        UPDATE stock_watchlist
        SET notified_at = NOW()
        WHERE id = ${watchId}::uuid
      `);
    }
  });

  return restocked;
}