/**
 * v0.32 EXP-5 — DB-authoritative receipt persistence on real Postgres.
 *
 * Pure-PGLite tests already cover the receipt-write contract; this E2E
 * verifies that the same code path works against actual Postgres:
 *   - migration v49 lands the eval_takes_quality_runs table
 *   - INSERT with receipt_json JSONB roundtrips correctly
 *   - 4-sha UNIQUE constraint enforces ON CONFLICT DO NOTHING idempotency
 *   - trend SELECT path returns expected shape
 *   - rubric_version segregation (codex review #3) holds against postgres.js
 *
 * Skips gracefully when DATABASE_URL is unset (CI parity with existing
 * test/e2e/* files via the hasDatabase() helper).
 */
import { describe, test, expect, beforeAll, afterAll } from 'bun:test';
import { setupDB, teardownDB, hasDatabase, getEngine } from './helpers.ts';
import {
  writeReceiptToDb,
  writeReceipt,
} from '../../src/core/takes-quality-eval/receipt-write.ts';
import { loadTrend } from '../../src/core/takes-quality-eval/trend.ts';
import { loadReceiptFromDb } from '../../src/core/takes-quality-eval/replay.ts';
import type { TakesQualityReceipt } from '../../src/core/takes-quality-eval/receipt.ts';
import { withEnv } from '../helpers/with-env.ts';
import { mkdtempSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';

const RUN = hasDatabase();
const d = RUN ? describe : describe.skip;

let tmpHome: string;

beforeAll(async () => {
  if (!RUN) return;
  await setupDB();
  tmpHome = mkdtempSync(join(tmpdir(), 'gbrain-e2e-receipt-'));
  // Best-effort: clear any prior e2e fixtures to keep test isolation.
  const engine = getEngine();
  await engine.executeRaw(`DELETE FROM eval_takes_quality_runs WHERE receipt_sha8_corpus LIKE 'e2e%'`);
});

afterAll(async () => {
  if (!RUN) return;
  const engine = getEngine();
  await engine.executeRaw(`DELETE FROM eval_takes_quality_runs WHERE receipt_sha8_corpus LIKE 'e2e%'`);
  await teardownDB();
  if (tmpHome) rmSync(tmpHome, { recursive: true, force: true });
});

function fixture(opts: {
  corpus: string;
  rubric_version?: string;
  rubric_sha8?: string;
  verdict?: 'pass' | 'fail' | 'inconclusive';
  overall?: number;
}): TakesQualityReceipt {
  return {
    schema_version: 1,
    ts: new Date().toISOString(),
    rubric_version: opts.rubric_version ?? 'v1.0',
    rubric_sha8: opts.rubric_sha8 ?? 'rrrr0001',
    corpus: { source: 'db', n_takes: 10, slug_prefix: null, corpus_sha8: opts.corpus },
    prompt_sha8: 'pppp0001',
    models_sha8: 'mmmm0001',
    models: ['openai:gpt-4o', 'anthropic:claude-opus-4-7'],
    cycles_run: 1,
    successes_per_cycle: [2],
    verdict: opts.verdict ?? 'pass',
    scores: {
      accuracy: { mean: 8, min: 8, max: 8, scores: [8, 8], per_model: { 'openai:gpt-4o': 8, 'anthropic:claude-opus-4-7': 8 } },
    },
    overall_score: opts.overall ?? 8.0,
    cost_usd: 0.5,
    improvements: [],
    errors: [],
    verdictMessage: 'PASS test',
  };
}

d('v0.32 EXP-5 — eval_takes_quality_runs on real Postgres', () => {
  test('migration v49 created the table with expected columns', async () => {
    const engine = getEngine();
    const cols = await engine.executeRaw<{ column_name: string; data_type: string }>(
      `SELECT column_name, data_type FROM information_schema.columns
         WHERE table_name = 'eval_takes_quality_runs'
         ORDER BY ordinal_position`,
    );
    const names = cols.map(c => c.column_name);
    expect(names).toContain('receipt_sha8_corpus');
    expect(names).toContain('receipt_sha8_prompt');
    expect(names).toContain('receipt_sha8_models');
    expect(names).toContain('receipt_sha8_rubric');
    expect(names).toContain('rubric_version');
    expect(names).toContain('verdict');
    expect(names).toContain('overall_score');
    expect(names).toContain('dim_scores');
    expect(names).toContain('cost_usd');
    expect(names).toContain('receipt_json');
    expect(names).toContain('created_at');
  });

  test('writeReceiptToDb persists full receipt_json on Postgres', async () => {
    const engine = getEngine();
    const r = fixture({ corpus: 'e2e_001' });
    await writeReceiptToDb(engine, r);

    const rows = await engine.executeRaw<{ verdict: string; receipt_json: any; rubric_version: string }>(
      `SELECT verdict, receipt_json, rubric_version FROM eval_takes_quality_runs
         WHERE receipt_sha8_corpus = $1`,
      ['e2e_001'],
    );
    expect(rows).toHaveLength(1);
    expect(rows[0].verdict).toBe('pass');
    expect(rows[0].rubric_version).toBe('v1.0');
    const json = typeof rows[0].receipt_json === 'string' ? JSON.parse(rows[0].receipt_json) : rows[0].receipt_json;
    expect(json.schema_version).toBe(1);
    expect(json.corpus.corpus_sha8).toBe('e2e_001');
    expect(json.scores.accuracy.mean).toBe(8);
  });

  test('4-sha UNIQUE constraint enforces idempotency on Postgres', async () => {
    const engine = getEngine();
    const r = fixture({ corpus: 'e2e_002' });
    await writeReceiptToDb(engine, r);
    await writeReceiptToDb(engine, r);
    await writeReceiptToDb(engine, r);
    const rows = await engine.executeRaw<{ n: number | string }>(
      `SELECT count(*)::int AS n FROM eval_takes_quality_runs
         WHERE receipt_sha8_corpus = $1`,
      ['e2e_002'],
    );
    expect(Number(rows[0].n)).toBe(1);
  });

  test('rubric_version segregation: distinct rubric_sha8 → distinct row (codex review #3)', async () => {
    const engine = getEngine();
    await writeReceiptToDb(engine, fixture({ corpus: 'e2e_003', rubric_version: 'v1.0', rubric_sha8: 'rrrr1' }));
    await writeReceiptToDb(engine, fixture({ corpus: 'e2e_003', rubric_version: 'v2.0', rubric_sha8: 'rrrr2' }));

    const rows = await engine.executeRaw<{ rubric_version: string }>(
      `SELECT rubric_version FROM eval_takes_quality_runs
         WHERE receipt_sha8_corpus = $1
         ORDER BY rubric_version`,
      ['e2e_003'],
    );
    expect(rows).toHaveLength(2);
    expect(rows.map(r => r.rubric_version)).toEqual(['v1.0', 'v2.0']);
  });

  test('loadTrend reads in DESC order on Postgres', async () => {
    const engine = getEngine();
    // Different ts values to exercise the index ORDER BY.
    const earlier = { ...fixture({ corpus: 'e2e_004' }) };
    earlier.ts = '2026-04-01T00:00:00Z';
    const later = { ...fixture({ corpus: 'e2e_005' }) };
    later.ts = '2026-04-02T00:00:00Z';
    await writeReceiptToDb(engine, earlier);
    await writeReceiptToDb(engine, later);

    const rows = await loadTrend(engine, { limit: 200 });
    const ours = rows.filter(r => r.corpus_sha8 === 'e2e_004' || r.corpus_sha8 === 'e2e_005');
    expect(ours.length).toBeGreaterThanOrEqual(2);
    // ours[0] is the more recent (e2e_005).
    expect(ours[0].corpus_sha8).toBe('e2e_005');
    expect(ours[1].corpus_sha8).toBe('e2e_004');
  });

  test('loadReceiptFromDb reconstructs receipt JSON on Postgres', async () => {
    const engine = getEngine();
    const r = fixture({ corpus: 'e2e_006' });
    await writeReceiptToDb(engine, r);
    const loaded = await loadReceiptFromDb(engine, {
      corpus_sha8: 'e2e_006',
      prompt_sha8: 'pppp0001',
      models_sha8: 'mmmm0001',
      rubric_sha8: 'rrrr0001',
    });
    expect(loaded.corpus.corpus_sha8).toBe('e2e_006');
    expect(loaded.verdict).toBe('pass');
    expect(loaded.scores.accuracy?.mean).toBe(8);
  });

  test('writeReceipt (combined) succeeds with disk artifact + DB row on Postgres', async () => {
    const engine = getEngine();
    await withEnv({ GBRAIN_HOME: tmpHome }, async () => {
      const r = fixture({ corpus: 'e2e_007' });
      const result = await writeReceipt(engine, r);
      expect(result.db).toBe(true);
      expect(result.disk_path).toBeDefined();
      // DB row exists.
      const rows = await engine.executeRaw<{ n: number | string }>(
        `SELECT count(*)::int AS n FROM eval_takes_quality_runs WHERE receipt_sha8_corpus = $1`,
        ['e2e_007'],
      );
      expect(Number(rows[0].n)).toBe(1);
    });
  });

  test('trend index used: explain shows index access on (rubric_version, created_at)', async () => {
    const engine = getEngine();
    // Seed a couple of rows to have something to scan.
    await writeReceiptToDb(engine, fixture({ corpus: 'e2e_008' }));
    const rows = await engine.executeRaw<{ 'QUERY PLAN': string }>(
      `EXPLAIN SELECT id, created_at FROM eval_takes_quality_runs
         WHERE rubric_version = 'v1.0'
         ORDER BY created_at DESC
         LIMIT 5`,
    );
    const plan = rows.map((r: any) => r['QUERY PLAN']).join('\n');
    // On a small table the planner can pick Seq Scan; the test passes
    // either way as long as the query is satisfiable. We don't gate on
    // index pickup specifically (small-table planner heuristics make it
    // brittle) — just verify the SELECT shape executes.
    expect(plan.length).toBeGreaterThan(0);
  });
});
