import { randomUUID } from 'node:crypto';
import { Pool } from 'pg';
import { expect, it } from 'vitest';
import { repositoryConnectionString } from '../../server/repository-factory';
import { runDatabaseMigrations } from '../../server/postgres/migration-runner';
import { PostgresAssessorRepository } from '../../server/postgres/postgres-assessor-repository';
import { ReferenceRecognitionService } from '../../server/reference-data/reference-recognition-service';
import { bundledReferenceCatalogs } from '../../server/reference-data/reference-catalogs';
import { seedReferenceScan } from '../fixtures/reference-recognition';

// Opt-in after a verified backup. Uses ONLY the canonical DB, with isolated synthetic IDs.
it.skipIf(process.env.ASSESSOR_REFERENCE_POSTGRES_TEST !== '1')('persists reference history and catalog snapshots across repository restarts with exact-scope cleanup', async () => {
  process.loadEnvFile('.env.postgres');
  const connectionString = 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_DB: process.env.ASSESSOR_POSTGRES_DB,
    ASSESSOR_POSTGRES_USER: process.env.ASSESSOR_POSTGRES_USER,
    ASSESSOR_POSTGRES_PASSWORD: process.env.ASSESSOR_POSTGRES_PASSWORD
  });
  if (!connectionString) throw new Error('Canonical database configuration required.');
  const address = new URL(connectionString);
  if (address.hostname !== '127.0.0.1' || address.port !== '5432' || address.pathname !== '/assessor') throw new Error('Only the canonical datastore is permitted.');
  const pool = new Pool({ connectionString, max: 2 });
  const prefix = `synthetic-reference-${randomUUID()}`;
  const projectId = `${prefix}-project`;
  try {
    await runDatabaseMigrations(pool);
    const repository = await PostgresAssessorRepository.create(pool);
    const { assessment, model } = await seedReferenceScan(repository, prefix);
    const service = new ReferenceRecognitionService(repository);
    await service.reprocess(projectId);
    const revision = repository.revision;
    await service.reprocess(projectId);
    expect(repository.revision).toBe(revision);
    const restarted = await PostgresAssessorRepository.create(pool);
    const stored = (await restarted.loadAssessmentModel(assessment.id))!;
    expect(stored.findingOccurrences).toEqual(model.findingOccurrences);
    expect(stored.referenceRuns).toHaveLength(1);
    expect(stored.referenceRuns![0].results[0].references).toHaveLength(4);
    const exported = await restarted.exportProject(projectId);
    expect(exported.referenceCatalogs.map(catalog => catalog.sha256).sort()).toEqual(bundledReferenceCatalogs.map(catalog => catalog.sha256).sort());
    expect(exported.referenceRuns).toEqual(stored.referenceRuns);
    // Superseding a scan must not orphan the source evidence of exported reference history.
    const successor = { ...model.scanRuns[0], id: `${prefix}-successor`, sequence: 2, importedAt: '2026-09-26T00:30:00.000Z' };
    const replacementFile = { ...model.importFiles[0], id: `${prefix}-replacement-file`, scanRunId: successor.id };
    const replacementOccurrence = { ...model.findingOccurrences[0], id: `${prefix}-replacement-occurrence`, scanRunId: successor.id, importFileId: replacementFile.id };
    const replacement = await restarted.beginImport({ sessionId: `${prefix}-replacement`, projectId, assessmentId: assessment.id, scanRun: successor });
    await replacement.stage({ importFiles: [replacementFile], findingDefinitions: model.findingDefinitions, findingOccurrences: [replacementOccurrence] });
    await replacement.commit();
    await restarted.adoptScanRunReplacement(projectId, model.scanRuns[0].id, successor.id, '2026-09-26T01:00:00.000Z');
    const withHistory = await restarted.exportProject(projectId);
    expect(withHistory.scanRuns.map(run => run.id)).toContain(model.scanRuns[0].id);
    expect(withHistory.findingOccurrences.map(item => item.id)).toContain(stored.referenceRuns![0].results[0].occurrenceId);
    expect(withHistory.importFiles.map(item => item.id)).toContain(stored.referenceRuns![0].results[0].importFileId);
    await expect(restarted.saveReferenceRun({ ...stored.referenceRuns![0], projectId: 'another-client', id: `${prefix}-foreign` }, bundledReferenceCatalogs)).rejects.toThrow();
    expect(await restarted.listReferenceRuns('another-client')).toEqual([]);
    await restarted.deleteProject(projectId, { projectId, cascade: true });
    expect(await restarted.listReferenceRuns(projectId)).toEqual([]);
    expect(await restarted.listReferenceCatalogs(projectId)).toEqual([]);
  } finally {
    // This ID was generated in this test; no unrelated project is queried or deleted.
    const repository = await PostgresAssessorRepository.create(pool);
    if (await repository.getProject(projectId)) await repository.deleteProject(projectId, { projectId, cascade: true });
    await pool.end();
  }
}, 30_000);