/**
 * P6.2: Scheduled fleet digest reports.
 *
 * Same in-process timer pattern as the retention job (design §3/§6): an hourly
 * tick reads control_schedule_meta.last_run_at and runs the digest when due,
 * so a boot after a missed window catches up immediately. No cron dependency,
 * no new scheduler abstraction.
 *
 * The report gathers usage, trends vs the prior period, swap counts, the eval
 * leaderboard, and bench regression anomalies, renders a markdown digest, and
 * persists both the markdown and the structured stats to control_reports.
 */

import type { Sql } from '../db.js';

export type ReportInterval = 'daily' | 'weekly';

export interface ReportStats {
  periodStart: string;
  periodEnd: string;
  interval: ReportInterval;
  totalRequests: number;
  priorRequests: number;
  totalInputTokens: number;
  totalOutputTokens: number;
  bySource: Array<{ source: string; requests: number; inputTokens: number; outputTokens: number }>;
  byProvider: Array<{ providerId: string; requests: number; swaps: number }>;
  leaderboard: Array<{ providerId: string; model: string; kind: string; avgScore: number | null }>;
  regressions: Array<{ providerId: string; model: string; avgGenTps: number | null }>;
}

function intervalHours(interval: ReportInterval): number {
  return interval === 'weekly' ? 24 * 7 : 24;
}

/**
 * Gather the structured stats for a report window. Pure read; no writes.
 */
export async function gatherReportStats(
  sql: Sql,
  interval: ReportInterval,
  now: Date,
): Promise<ReportStats> {
  const hours = intervalHours(interval);
  const periodEnd = now;
  const periodStart = new Date(now.getTime() - hours * 3600_000);
  const priorStart = new Date(periodStart.getTime() - hours * 3600_000);

  const startIso = periodStart.toISOString();
  const endIso = periodEnd.toISOString();
  const priorIso = priorStart.toISOString();

  const totals = await sql<{ requests: number; in_tokens: number; out_tokens: number }[]>`
    SELECT COUNT(*)::int AS requests,
           COALESCE(SUM(input_tokens), 0)::int AS in_tokens,
           COALESCE(SUM(output_tokens), 0)::int AS out_tokens
    FROM control_requests
    WHERE ts >= ${startIso} AND ts < ${endIso}
  `;

  const prior = await sql<{ requests: number }[]>`
    SELECT COUNT(*)::int AS requests
    FROM control_requests
    WHERE ts >= ${priorIso} AND ts < ${startIso}
  `;

  const bySource = await sql<{ source: string | null; requests: number; in_tokens: number; out_tokens: number }[]>`
    SELECT source,
           COUNT(*)::int AS requests,
           COALESCE(SUM(input_tokens), 0)::int AS in_tokens,
           COALESCE(SUM(output_tokens), 0)::int AS out_tokens
    FROM control_requests
    WHERE ts >= ${startIso} AND ts < ${endIso}
    GROUP BY source
    ORDER BY requests DESC
  `;

  const byProviderReqs = await sql<{ provider_id: string; requests: number }[]>`
    SELECT provider_id, COUNT(*)::int AS requests
    FROM control_requests
    WHERE ts >= ${startIso} AND ts < ${endIso}
    GROUP BY provider_id
  `;

  // Swap counts: a model entering 'ready' / 'starting' marks a load/swap.
  const swaps = await sql<{ provider_id: string; swaps: number }[]>`
    SELECT provider_id, COUNT(*)::int AS swaps
    FROM control_model_events
    WHERE ts >= ${startIso} AND ts < ${endIso}
      AND state IN ('ready', 'starting')
    GROUP BY provider_id
  `;

  const swapMap = new Map<string, number>();
  for (const r of swaps) swapMap.set(r.provider_id, r.swaps);
  const providerIds = new Set<string>([
    ...byProviderReqs.map((r) => r.provider_id),
    ...swaps.map((r) => r.provider_id),
  ]);
  const reqMap = new Map<string, number>();
  for (const r of byProviderReqs) reqMap.set(r.provider_id, r.requests);

  const byProvider = Array.from(providerIds)
    .sort()
    .map((providerId) => ({
      providerId,
      requests: reqMap.get(providerId) ?? 0,
      swaps: swapMap.get(providerId) ?? 0,
    }));

  // Leaderboard: latest completed eval avgScore per (provider, model, kind).
  const leaderboard = await sql<{ provider_id: string; model: string; kind: string; avg_score: number | null }[]>`
    SELECT er.provider_id, er.model, es.kind,
           (er.aggregate::jsonb ->> 'avgScore')::float AS avg_score
    FROM eval_runs er
    JOIN eval_suites es ON er.suite_id = es.id
    WHERE er.status = 'completed' AND er.aggregate IS NOT NULL
      AND er.finished_at = (
        SELECT MAX(er2.finished_at) FROM eval_runs er2
        JOIN eval_suites es2 ON er2.suite_id = es2.id
        WHERE er2.provider_id = er.provider_id AND er2.model = er.model
          AND es2.kind = es.kind AND er2.status = 'completed'
      )
    ORDER BY avg_score DESC NULLS LAST
    LIMIT 20
  `;

  // Regression anomalies: bench runs flagged 'regression' in the window.
  const regressions = await sql<{ provider_id: string; model: string; avg_gen_tps: number | null }[]>`
    SELECT bs.provider_id, bs.model,
           (br.aggregate::jsonb ->> 'avgGenTps')::float AS avg_gen_tps
    FROM bench_runs br
    JOIN bench_suites bs ON br.suite_id = bs.id
    WHERE br.regression_flag = 'regression'
      AND br.finished_at >= ${startIso} AND br.finished_at < ${endIso}
    ORDER BY br.finished_at DESC
  `;

  return {
    periodStart: startIso,
    periodEnd: endIso,
    interval,
    totalRequests: totals[0]?.requests ?? 0,
    priorRequests: prior[0]?.requests ?? 0,
    totalInputTokens: totals[0]?.in_tokens ?? 0,
    totalOutputTokens: totals[0]?.out_tokens ?? 0,
    bySource: bySource.map((r) => ({
      source: r.source ?? '(unattributed)',
      requests: r.requests,
      inputTokens: r.in_tokens,
      outputTokens: r.out_tokens,
    })),
    byProvider,
    leaderboard: leaderboard.map((r) => ({
      providerId: r.provider_id,
      model: r.model,
      kind: r.kind,
      avgScore: r.avg_score,
    })),
    regressions: regressions.map((r) => ({
      providerId: r.provider_id,
      model: r.model,
      avgGenTps: r.avg_gen_tps,
    })),
  };
}

/**
 * Render a markdown digest from gathered stats. Pure — unit-testable.
 */
export function renderReportMarkdown(stats: ReportStats): string {
  const lines: string[] = [];
  const pct = (cur: number, prev: number): string => {
    if (prev === 0) return cur === 0 ? '0%' : 'new';
    const d = ((cur - prev) / prev) * 100;
    return `${d >= 0 ? '+' : ''}${d.toFixed(0)}%`;
  };

  lines.push(`# Fleet ${stats.interval} report`);
  lines.push('');
  lines.push(`Period: ${stats.periodStart} to ${stats.periodEnd}`);
  lines.push('');

  lines.push('## Usage');
  lines.push('');
  lines.push(`- Requests: ${stats.totalRequests} (${pct(stats.totalRequests, stats.priorRequests)} vs prior period)`);
  lines.push(`- Input tokens: ${stats.totalInputTokens}`);
  lines.push(`- Output tokens: ${stats.totalOutputTokens}`);
  lines.push('');

  if (stats.bySource.length > 0) {
    lines.push('## By source');
    lines.push('');
    lines.push('| Source | Requests | Input tok | Output tok |');
    lines.push('| --- | ---: | ---: | ---: |');
    for (const s of stats.bySource) {
      lines.push(`| ${s.source} | ${s.requests} | ${s.inputTokens} | ${s.outputTokens} |`);
    }
    lines.push('');
  }

  if (stats.byProvider.length > 0) {
    lines.push('## By host');
    lines.push('');
    lines.push('| Host | Requests | Swaps |');
    lines.push('| --- | ---: | ---: |');
    for (const p of stats.byProvider) {
      lines.push(`| ${p.providerId} | ${p.requests} | ${p.swaps} |`);
    }
    lines.push('');
  }

  if (stats.leaderboard.length > 0) {
    lines.push('## Leaderboard');
    lines.push('');
    lines.push('| Model | Kind | Score |');
    lines.push('| --- | --- | ---: |');
    for (const l of stats.leaderboard) {
      lines.push(`| ${l.providerId}/${l.model} | ${l.kind} | ${l.avgScore != null ? l.avgScore.toFixed(3) : 'n/a'} |`);
    }
    lines.push('');
  }

  lines.push('## Anomalies');
  lines.push('');
  if (stats.regressions.length === 0) {
    lines.push('No speed regressions flagged this period.');
  } else {
    for (const r of stats.regressions) {
      lines.push(`- Regression: ${r.providerId}/${r.model} (avg gen ${r.avgGenTps != null ? r.avgGenTps.toFixed(1) : 'n/a'} tok/s)`);
    }
  }
  lines.push('');

  return lines.join('\n');
}

/**
 * Generate a report for the given interval and persist it. Returns the new id.
 */
export async function generateReport(
  sql: Sql,
  interval: ReportInterval,
  now: Date = new Date(),
): Promise<string> {
  const stats = await gatherReportStats(sql, interval, now);
  const markdown = renderReportMarkdown(stats);
  const id = `report_${now.getTime()}_${interval}`;

  await sql`
    INSERT INTO control_reports (id, kind, interval, period_start, period_end, markdown, stats)
    VALUES (${id}, 'digest', ${interval}, ${stats.periodStart}, ${stats.periodEnd}, ${markdown}, ${sql.json(stats as never)})
    ON CONFLICT (id) DO NOTHING
  `;

  return id;
}

/**
 * Decide whether a scheduled report is due. Pure helper for testing.
 */
export function isReportDue(
  lastRunAt: Date | null,
  interval: ReportInterval,
  now: Date,
): boolean {
  if (!lastRunAt) return true;
  const elapsed = now.getTime() - lastRunAt.getTime();
  return elapsed >= intervalHours(interval) * 3600_000;
}

/**
 * Run one scheduler tick: check control_schedule_meta and generate the digest
 * if due. Catch-up-on-boot is achieved by calling this once at startup, then
 * hourly.
 */
export async function runReportSchedulerTick(
  sql: Sql,
  now: Date = new Date(),
): Promise<{ ran: boolean; reportId?: string }> {
  const rows = await sql<{ interval: string; enabled: boolean; last_run_at: string | null }[]>`
    SELECT interval, enabled, last_run_at
    FROM control_schedule_meta WHERE name = 'report-digest'
  `;
  const meta = rows[0];
  if (!meta || !meta.enabled) return { ran: false };

  const interval = (meta.interval === 'weekly' ? 'weekly' : 'daily') as ReportInterval;
  const lastRunAt = meta.last_run_at ? new Date(meta.last_run_at) : null;

  if (!isReportDue(lastRunAt, interval, now)) return { ran: false };

  const reportId = await generateReport(sql, interval, now);
  await sql`
    UPDATE control_schedule_meta SET last_run_at = ${now.toISOString()}
    WHERE name = 'report-digest'
  `;
  return { ran: true, reportId };
}
