import { createDbService } from '@/server/services/db.js';
import { sql } from 'drizzle-orm';
import { OUTBOX_MAX_RETRIES } from '@/server/db/queries/outbox.js';
import type { AlertSeverity } from '@/server/cron/settlement-monitor.js';
import { redactError } from '../redact.js';
import type { CheckAdapter, CheckContext, CheckResult } from '../types.js';

export interface BacklogRow {
  source: string;
  pendingCount: number;
  oldestPendingMinutes: number;
}
export interface BacklogConfig {
  maxAgeMinutes: number;
  maxCount: number;
}

export function evaluateBacklog(
  rows: BacklogRow[],
  cfg: BacklogConfig,
): { severity: AlertSeverity; detail: string } {
  const offenders = rows.filter(
    (r) => r.oldestPendingMinutes > cfg.maxAgeMinutes || r.pendingCount > cfg.maxCount,
  );
  if (offenders.length === 0)
    return {
      severity: 'info',
      detail: rows.map((r) => `${r.source}=${r.pendingCount}`).join(', ') || 'no backlog',
    };
  return {
    severity: 'warn',
    detail: offenders
      .map((r) => `${r.source}: ${r.pendingCount} pending, oldest ${r.oldestPendingMinutes}m`)
      .join('; '),
  };
}

const SOURCES: Array<{ source: string; table: string; where: string }> = [
  {
    source: 'outbox',
    table: 'outbox',
    where: `processed_at IS NULL AND dead_at IS NULL AND (failed_at IS NULL OR (retry_count < ${OUTBOX_MAX_RETRIES} AND failed_at < now() - interval '5 minutes'))`,
  },
  { source: 'llm_jobs', table: 'llm_jobs', where: "status = 'PENDING'" },
  { source: 'translation_jobs', table: 'translation_jobs', where: "status = 'PENDING'" },
];

export const queueBacklog: CheckAdapter = {
  key: 'queue_backlog',
  title: 'Queue backlog (PENDING)',
  defaultConfig: { maxAgeMinutes: 30, maxCount: 200 },
  defaultIntervalHours: 1,
  async run(ctx: CheckContext): Promise<CheckResult> {
    const cfg = {
      maxAgeMinutes: Number(ctx.config.maxAgeMinutes ?? 30),
      maxCount: Number(ctx.config.maxCount ?? 200),
    };
    try {
      const db = createDbService({ DATABASE_URL: ctx.env.DATABASE_URL });
      const rows: BacklogRow[] = [];
      for (const s of SOURCES) {
        const res = await db.execute<{ n: number; oldest: string | null }>(
          sql.raw(
            `SELECT count(*)::int AS n, MIN(created_at) AS oldest FROM ${s.table} WHERE ${s.where}`,
          ),
        );
        const row = res.rows[0];
        const oldest = row?.oldest ? new Date(row.oldest) : null;
        rows.push({
          source: s.source,
          pendingCount: row?.n ?? 0,
          oldestPendingMinutes: oldest
            ? Math.floor((ctx.now.getTime() - oldest.getTime()) / 60_000)
            : 0,
        });
      }
      const ev = evaluateBacklog(rows, cfg);
      const metrics: Record<string, number> = {};
      for (const r of rows) metrics[`${r.source}_pending`] = r.pendingCount;
      return {
        key: this.key,
        severity: ev.severity,
        title: this.title,
        detail: ev.detail,
        metrics,
      };
    } catch (err) {
      return {
        key: this.key,
        severity: 'warn',
        degraded: true,
        title: this.title,
        detail: redactError(err, 'check threw'),
      };
    }
  },
};