import { Pool } from 'pg';
import { describe, expect, it } from 'vitest';
import { PostgresAssessorRepository } from '../../server/postgres/postgres-assessor-repository';
import { repositoryConnectionString } from '../../server/repository-factory';
import type { StoredPolicyEvaluation } from '../../src/domain/policy-evaluation';
import { selectCurrentPolicyEvaluations } from '../../src/domain/unified-result';
import { policyRun, repeatedBaselineRuns } from '../fixtures/policy-current-runs';

async function readComparisons(execute: (sql: string, values: unknown[]) => Promise<{ rows: unknown[] }>) {
  const pool = { query: async (sql: string, values: unknown[] = []) => {
    if (sql.startsWith('SELECT revision')) return { rows: [{ revision: 0 }] };
    if (sql.includes('WITH ranked AS')) return execute(sql, values);
    return { rows: [] };
  } } as unknown as Pool;
  return (await PostgresAssessorRepository.create(pool)).queryUnifiedResults({
    projectId: 'synthetic', sourceKind: ['policy-evaluation'], audience: 'internal-analysis', deviation: true
  });
}

it('uses compared package coverage in the actual PostgreSQL Result DB query, with reference fallback and scope', async () => {
  let calls = 0;
  await readComparisons(async (sql, values) => {
    calls++;
    expect(sql).toContain("count(DISTINCT NULLIF(compared.data->>'baselineId', ''))");
    expect(sql).toContain("), 0), jsonb_array_length(evaluation.data->'run'->'baselineAnalysisIds')) DESC");
    expect(sql).toContain('compared.evaluation_run_id = evaluation.id');
    expect(sql).toContain('evaluation.completed_at DESC, evaluation.id DESC');
    expect(sql).toContain('selected.project_id = $2');
    expect(values[1]).toBe('synthetic');
    return { rows: [] };
  });
  expect(calls).toBe(1);
});

// Opt-in, read-only PostgreSQL execution. Every relation in the production SELECT
// is shadowed by a synthetic CTE: no schema setup, DDL, writes or tenant reads.
const readonlyUrl = process.env.ASSESSOR_READONLY_TEST_DATABASE_URL
  ?? (process.env.ASSESSOR_READONLY_TEST_POSTGRES === '1' ? repositoryConnectionString({
    ASSESSOR_DATABASE_URL: process.env.ASSESSOR_DATABASE_URL,
    ASSESSOR_POSTGRES_HOST: process.env.ASSESSOR_POSTGRES_HOST,
    ASSESSOR_POSTGRES_PORT: process.env.ASSESSOR_POSTGRES_PORT,
    ASSESSOR_POSTGRES_USER: process.env.ASSESSOR_POSTGRES_USER,
    ASSESSOR_POSTGRES_PASSWORD: process.env.ASSESSOR_POSTGRES_PASSWORD,
    ASSESSOR_POSTGRES_DB: process.env.ASSESSOR_POSTGRES_DB
  }) : undefined);
describe.skipIf(!readonlyUrl)('PostgreSQL / Work current-run behavioral parity (synthetic CTEs only)', () => {
  const scenarios: [string, () => StoredPolicyEvaluation[], string][] = [
    ['repeated versions', repeatedBaselineRuns, 'newer-distinct'],
    ['broader before newer', () => [policyRun('broader', ['alpha', 'beta'], 2, 17), policyRun('newer-narrow', ['alpha'], 9, 18)], 'broader'],
    ['stable tie', () => [policyRun('tie-a', ['alpha'], 1, 18), policyRun('tie-z', ['alpha'], 1, 18)], 'tie-z'],
    ['reference fallback', () => [policyRun('fallback', [undefined, ''], 3, 17), policyRun('narrow', ['alpha'], 1, 18)], 'fallback'],
    ['empty results fallback', () => [policyRun('empty', [], 3, 17), policyRun('narrow', ['alpha'], 1, 18)], 'empty']
  ];
  it.each(scenarios)('%s selects the same run without changing stored inputs', async (_name, makeRuns, expected) => {
    const runs = makeRuns();
    // An unrelated aggregation must be ranked independently and a foreign project
    // must never enter the scoped Result DB result set.
    runs.push(policyRun('other-snapshot', ['alpha'], 1, 18, 'other-aggregation'));
    const foreign = policyRun('foreign', ['alpha', 'beta', 'gamma'], 3, 18, 'foreign-aggregation');
    foreign.run.projectId = 'foreign';
    runs.push(foreign);
    const before = JSON.stringify(runs);
    const connection = new Pool({ connectionString: readonlyUrl, max: 1,
      options: '-c default_transaction_read_only=on', connectionTimeoutMillis: 5000 });
    try {
      const result = await readComparisons(async (sql, values) => {
        const runParam = `$${values.length + 1}`;
        const resultParam = `$${values.length + 2}`;
        const synthetic = `WITH assessor_policy_evaluation_runs AS (
          SELECT * FROM jsonb_to_recordset(${runParam}::jsonb) AS r(id text, project_id text, aggregation_run_id text,
            evaluator_version text, completed_at timestamptz, data jsonb)
        ), assessor_policy_evaluation_results AS (
          SELECT * FROM jsonb_to_recordset(${resultParam}::jsonb) AS r(id text, evaluation_run_id text, data jsonb)
        ), assessor_policy_snapshot_aggregation_runs AS (
          SELECT DISTINCT aggregation_run_id AS id, 'synthetic-snapshot'::text AS snapshot_id FROM assessor_policy_evaluation_runs
        ), assessor_policy_snapshot_aggregation_sources AS (
          SELECT id AS aggregation_run_id, 'synthetic-analysis'::text AS analysis_run_id FROM assessor_policy_snapshot_aggregation_runs
        ), assessor_policy_identity_analysis_runs AS (
          SELECT 'synthetic-analysis'::text AS id, 'synthetic-artifact'::text AS artifact_id, NULL::text AS supersedes_analysis_run_id
        ), assessor_import_artifacts AS (
          SELECT 'synthetic-artifact'::text AS id, 'slot'::text AS assessment_id
        ), assessor_policy_finding_occurrences AS (
          SELECT NULL::text AS id, NULL::text AS evaluation_result_id WHERE false
        ), ` + sql.trimStart().slice('WITH '.length);
        return connection.query(synthetic, [...values,
          JSON.stringify(runs.map(({ run }) => ({ id: run.id, project_id: run.projectId, aggregation_run_id: run.aggregationRunId,
            evaluator_version: run.evaluator.version, completed_at: run.completedAt, data: { run } }))),
          JSON.stringify(runs.flatMap(({ run, results }) => results.map(data => ({ id: data.id, evaluation_run_id: run.id, data }))))]);
      });
      const selected = selectCurrentPolicyEvaluations(runs).filter(item => item.run.projectId === 'synthetic');
      expect(selected.find(item => item.run.aggregationRunId === 'synthetic-aggregation')?.run.id).toBe(expected);
      expect(result.items.map(item => item.sourceResultId).sort()).toEqual(selected.flatMap(item => item.results.map(row => row.id)).sort());
      expect(result.items.every(item => item.assessmentId === 'slot' && item.projectId === 'synthetic')).toBe(true);
      expect(JSON.stringify(runs)).toBe(before);
    } finally { await connection.end(); }
  });
});