/**
 * Cron: Vendor Lifecycle Sweep — runs daily at 06:00 UTC in the 0 6 bundle.
 *
 * Sweeps for vendor-side lifecycle email trigger candidates and enqueues
 * outbox rows for each. Two sweeps per run:
 *
 *   1. vendor.deal.expiry3d      — deals expiring in ~3 days, vendor not yet notified
 *   2. vendor.deal.low_redemption — deals live 7+ days with <5 purchases, vendor not yet notified
 *
 * Dedup is enforced via marketing_trigger_state with a deal-scoped trigger key.
 * Uses raw SQL via db.execute<T>() to avoid Drizzle join typing friction.
 */

import { createDbService } from '@/server/services/db.js';
import { sql } from 'drizzle-orm';
import { withSentry } from '@/server/observability/with-sentry';
import { captureCaught } from '@/server/observability/capture.server';
import { insertOutboxRow } from '../db/queries/outbox.js';
import { enqueueOutbox } from '../queues/outbox-producer.js';
import { recordTriggered } from '../db/queries/marketing.js';
import type { CronEnv } from './deal-expiry.js';

// ── Types for raw query results ───────────────────────────────────────────────

interface DealExpiryRow extends Record<string, unknown> {
  deal_id: string;
  title: string;
  window_end: Date;
  vendor_id: string;
  owner_user_id: string;
}

interface LowRedemptionRow extends Record<string, unknown> {
  deal_id: string;
  title: string;
  purchase_count: string; // numeric from COUNT()
  vendor_id: string;
  owner_user_id: string;
}

// ── Main export ───────────────────────────────────────────────────────────────

export const runVendorLifecycle = withSentry(
  async function runVendorLifecycle(env: CronEnv): Promise<void> {
    if (!env.RESEND_MARKETING_FROM_EMAIL) {
      return;
    }

    const db = createDbService({ DATABASE_URL: env.DATABASE_URL });

    // ── Sweep 1: Deal expiry 3-day warning ───────────────────────────────────
    try {
      const expiryRows = await db.execute<DealExpiryRow>(sql`
        SELECT
          d.id            AS deal_id,
          d.title         AS title,
          d.window_end    AS window_end,
          d.vendor_id     AS vendor_id,
          v.owner_user_id AS owner_user_id
        FROM deals d
        JOIN vendors v ON v.id = d.vendor_id
        JOIN users u ON u.id = v.owner_user_id
          AND (u.notif_prefs->'marketing'->>'all')::boolean = true
        WHERE
          d.window_end BETWEEN (NOW() + INTERVAL '2 days') AND (NOW() + INTERVAL '4 days')
          AND d.deal_state IN ('ACTIVE', 'PAUSED')
          AND NOT EXISTS (
            SELECT 1 FROM marketing_trigger_state m
            WHERE m.user_id = v.owner_user_id
              AND m.trigger_key = 'vendor.deal.expiry3d.' || d.id
          )
        LIMIT 100
      `);

      for (const row of expiryRows.rows) {
        try {
          const { id: outboxId } = await insertOutboxRow(db, {
            aggregateType: 'user',
            aggregateId: row.owner_user_id,
            eventType: 'vendor.deal.expiry3d',
            payload: {
              userId: row.owner_user_id,
              vendorId: row.vendor_id,
              dealTitle: row.title,
              expiryDateStr: new Date(row.window_end).toISOString().slice(0, 10),
            },
          });
          await enqueueOutbox(outboxId);
          await recordTriggered(db, row.owner_user_id, `vendor.deal.expiry3d.${row.deal_id}`);
        } catch (rowErr) {
          captureCaught(rowErr, {
            scope: 'server.cron.vendor-lifecycle.expiry3d.row',
            severity: 'warning',
          });
        }
      }
    } catch (err) {
      captureCaught(err, { scope: 'server.cron.vendor-lifecycle.expiry3d', severity: 'warning' });
    }

    // ── Sweep 2: Low redemption warning ─────────────────────────────────────
    try {
      const lowRows = await db.execute<LowRedemptionRow>(sql`
        SELECT
          d.id            AS deal_id,
          d.title         AS title,
          d.vendor_id     AS vendor_id,
          v.owner_user_id AS owner_user_id,
          COUNT(ol.id)    AS purchase_count
        FROM deals d
        JOIN vendors v ON v.id = d.vendor_id
        JOIN users u ON u.id = v.owner_user_id
          AND (u.notif_prefs->'marketing'->>'all')::boolean = true
        LEFT JOIN deal_skus ds ON ds.deal_id = d.id
        LEFT JOIN order_line ol ON ol.variant_id = ds.id
        LEFT JOIN "order" o ON o.id = ol.order_id AND o.status = 'paid'
        WHERE
          d.created_at < (NOW() - INTERVAL '7 days')
          AND (d.window_end IS NULL OR d.window_end > NOW())
          AND d.deal_state = 'ACTIVE'
          AND NOT EXISTS (
            SELECT 1 FROM marketing_trigger_state m
            WHERE m.user_id = v.owner_user_id
              AND m.trigger_key = 'vendor.deal.low_redemption.' || d.id
          )
        GROUP BY d.id, d.title, d.vendor_id, v.owner_user_id
        HAVING COUNT(ol.id) < 5
        LIMIT 100
      `);

      for (const row of lowRows.rows) {
        try {
          const { id: outboxId } = await insertOutboxRow(db, {
            aggregateType: 'user',
            aggregateId: row.owner_user_id,
            eventType: 'vendor.deal.low_redemption',
            payload: {
              userId: row.owner_user_id,
              vendorId: row.vendor_id,
              dealTitle: row.title,
              redemptionCount: Number(row.purchase_count),
            },
          });
          await enqueueOutbox(outboxId);
          await recordTriggered(db, row.owner_user_id, `vendor.deal.low_redemption.${row.deal_id}`);
        } catch (rowErr) {
          captureCaught(rowErr, {
            scope: 'server.cron.vendor-lifecycle.low_redemption.row',
            severity: 'warning',
          });
        }
      }
    } catch (err) {
      captureCaught(err, {
        scope: 'server.cron.vendor-lifecycle.low_redemption',
        severity: 'warning',
      });
    }
  },
  { name: 'cron.vendor-lifecycle', kind: 'cron' },
);