import { createDbService } from '@/server/services/db.js';
import { captureCaught } from '@/server/observability/capture.server.js';
import { sql } from 'drizzle-orm';
import { touchFinding } from '@/server/db/queries/system-health.js';

export interface SettlementReleaseMessage {
  kind: 'settlement.release';
  batchKey: string;
  releaseIds: string[];
}

export interface Env {
  DATABASE_URL: string;
  SETTLEMENTS: {
    sendBatch(
      messages: Array<{ body: SettlementReleaseMessage; contentType: 'json' }>,
    ): Promise<void>;
  };
}

export async function runSettlementRelease(env: Env): Promise<{ dispatched: number }> {
  const db = createDbService({ DATABASE_URL: env.DATABASE_URL });

  const result = await db.execute<{
    id: string;
    vendor_id: string;
    payout_batch_key: string;
  }>(sql`
    WITH due AS (
      SELECT id, vendor_id
        FROM vendor_payout_releases
       WHERE status = 'held' AND release_at < now()
         -- Only enqueue rows we can actually pay. Backfilled rows for vendors with
         -- no Stripe Connect account carry the sentinel 'backfill:no_acct:<vendorId>';
         -- enqueueing them makes the consumer's balance/payout call fail permanently,
         -- which retries x5 -> DLQ -> sweep -> re-enqueue forever, burning the Queues
         -- free tier (2026-06-13 incident). Leave them 'held' (obligation preserved,
         -- still visible) until the vendor onboards and vendor_acct_id becomes a real
         -- acct_ id. Defense-in-depth: the consumer also acks (not retries) permanent
         -- Stripe errors so a stray row can never re-storm.
         AND starts_with(vendor_acct_id, 'acct_')
         -- Exponential backoff: a held row that already failed N times waits longer before the
         -- next re-enqueue, measured from its LAST ATTEMPT. First attempt (claim_attempts=0) is
         -- immediate (still gated by release_at). attempt 1 -> +30m, 2 -> +1h, 3 -> +2h ...
         -- capped at 24h. Keeps retrying forever (vendor may fund later) while decaying ops cost.
         AND (claim_attempts = 0
              OR now() >= claimed_at
                          + least(interval '24 hours',
                                  interval '15 minutes' * power(2, least(claim_attempts, 10))))
       ORDER BY release_at ASC
       LIMIT 500
       FOR UPDATE SKIP LOCKED
    )
    UPDATE vendor_payout_releases r
       SET status           = 'enqueued',
           enqueued_at      = now(),
           payout_batch_key = COALESCE(
             r.payout_batch_key,
             concat('vbatch-', d.vendor_id, '-', extract(epoch from now())::bigint)
           )
      FROM due d
     WHERE r.id = d.id
     RETURNING r.id, r.vendor_id, r.payout_batch_key
  `);

  const claimed = result.rows;

  if (claimed.length === 0) return { dispatched: 0 };

  const byBatch = new Map<string, string[]>();
  for (const r of claimed) {
    const list = byBatch.get(r.payout_batch_key) ?? [];
    list.push(r.id);
    byBatch.set(r.payout_batch_key, list);
  }

  await env.SETTLEMENTS.sendBatch(
    [...byBatch.entries()].map(([batchKey, releaseIds]) => ({
      body: { kind: 'settlement.release' as const, batchKey, releaseIds },
      contentType: 'json' as const,
    })),
  );

  return { dispatched: byBatch.size };
}

export async function sweepStuckReleases(
  env: Pick<Env, 'DATABASE_URL'>,
): Promise<{ requeued: number }> {
  const db = createDbService({ DATABASE_URL: env.DATABASE_URL });

  const result = await db.execute<{ id: string }>(sql`
    UPDATE vendor_payout_releases
       SET status     = 'held',
           last_error = 'requeue_stuck'
     WHERE status IN ('enqueued', 'releasing')
       AND COALESCE(claimed_at, enqueued_at) < now() - interval '30 minutes'
     RETURNING id
  `);
  const requeued = result.rows.length;

  const SWEEP_ABNORMAL_THRESHOLD = 5;
  if (requeued > SWEEP_ABNORMAL_THRESHOLD) {
    try {
      const detail = `sweepStuckReleases requeued ${requeued} torn releases this tick (>${SWEEP_ABNORMAL_THRESHOLD}) — possible systemic send failure.`;
      await touchFinding(db, {
        kind: 'settlement_sweep_abnormal',
        entityType: 'settlement',
        entityId: 'sweep',
        detail,
      });
    } catch (err) {
      captureCaught(err, {
        scope: 'workflows.settlement-scheduler.sweep-telemetry',
      });
    }
  }

  return { requeued };
}