import { Pool } from 'pg';
import { afterAll, beforeAll, describe, expect, it } from 'vitest';
import type {
  Assessment,
  AssessmentProject,
  CanonicalAssessmentModel,
  FindingReview,
  ImportFileRecord,
  ScanRun,
  SourceTableRecord
} from '../../src/domain/canonical-model';
import type { StoredGpoBackupInstance } from '../../src/domain/gpo-analysis-persistence';
import type { ImportStagingData } from '../../src/persistence/assessor-repository';
import type { ImportArtifact, ImportProcessingRun, IngestionJob } from '../../src/domain/ingestion-persistence';
import { compareScanRuns } from '../../src/comparison/comparison-engine';
import { RepositoryConflictError } from '../../src/persistence/repository-errors';
import { runDatabaseMigrations } from '../../server/postgres/migration-runner';
import { PostgresAssessorRepository } from '../../server/postgres/postgres-assessor-repository';
import { createRepositoryRuntime } from '../../server/repository-factory';

const connectionString = process.env.ASSESSOR_TEST_DATABASE_URL;
const describePostgres = connectionString ? describe : describe.skip;
const timestamp = '2026-07-17T08:00:00.000Z';
const confidence = { level: 'high' as const, score: 1, reasons: ['integration'] };
const provenance = { decisionSource: 'source-native' as const, confidence, mappingVersion: '1.0.0' };
const project: AssessmentProject = { id: 'pg-project', name: 'PostgreSQL Project', createdAt: timestamp, updatedAt: timestamp, attributes: {} };
const assessment: Assessment = { id: 'pg-assessment', projectId: project.id, name: 'PostgreSQL Assessment', createdAt: timestamp, updatedAt: timestamp };

function run(sequence: number): ScanRun {
  return {
    id: `pg-run-${sequence}`, assessmentId: assessment.id, sequence, label: `Run ${sequence}`,
    tool: { id: 'pingcastle', name: 'PingCastle', version: '3.3' }, importedAt: timestamp
  };
}

function importData(scanRun: ScanRun, severity: 'high' | 'critical' = 'high'): ImportStagingData {
  const fileId = `pg-file-${scanRun.sequence}`;
  const tableId = `pg-table-${scanRun.sequence}`;
  const occurrenceId = `pg-occurrence-${scanRun.sequence}`;
  const file: ImportFileRecord = {
    id: fileId, scanRunId: scanRun.id, fileName: `${scanRun.id}.csv`, format: 'CSV', sizeBytes: 100,
    importedAt: timestamp, detectedTool: scanRun.tool, detection: confidence,
    processedWith: { parserId: 'csv', parserVersion: '1.0.0', profileId: 'pingcastle', profileVersion: '1.0.0', mappingVersion: '1.0.0' },
    sourceArtifact: { kind: 'not-retained', reason: 'Integration test' }
  };
  const table: SourceTableRecord = {
    id: tableId, importFileId: fileId, name: 'Findings', index: 0,
    rowCount: 1, columnCount: 3, attributes: {}
  };
  return {
    importFiles: [file], sourceTables: [table],
    findingDefinitions: [{
      id: 'pg-definition', canonicalKey: 'pingcastle:postgres-rule', title: 'PostgreSQL searchable finding',
      description: 'Persistent database evidence', category: 'Privileged Accounts',
      frameworkReferences: [], createdAt: timestamp, updatedAt: timestamp
    }],
    findingOccurrences: [{
      id: occurrenceId, definitionId: 'pg-definition', scanRunId: scanRun.id, importFileId: fileId, sourceTableId: tableId,
      sourceIdentity: { tool: scanRun.tool, originalRuleId: 'PG-1', canonicalRuleId: 'PG-1', originalTitle: 'PostgreSQL searchable finding' },
      sourcePosition: { rowIndex: 1 },
      originalFields: [{ key: 'rule', label: 'Rule', value: 'PG-1', columnIndex: 0 }],
      fieldMappings: [{ targetRole: 'rule-id', sourceFieldKeys: ['rule'], normalizedValue: 'PG-1', provenance }],
      severity: { originalValue: severity, normalizedValue: severity, provenance },
      status: { originalValue: 'Open', normalizedValue: 'open', provenance }
    }],
    evidence: [{ id: `pg-evidence-${scanRun.sequence}`, occurrenceId, kind: 'text', value: 'Database evidence', sourceFieldKeys: ['evidence'] }],
    diagnostics: [{ id: `pg-diagnostic-${scanRun.sequence}`, importFileId: fileId, severity: 'info', code: 'integration', message: 'PostgreSQL integration' }]
  };
}

describePostgres('PostgresAssessorRepository integration', () => {
  let pool: Pool;
  let repository: PostgresAssessorRepository;

  beforeAll(async () => {
    pool = new Pool({ connectionString, max: 4, connectionTimeoutMillis: 5000 });
    await runDatabaseMigrations(pool);
    await expect(runDatabaseMigrations(pool)).resolves.toEqual([]);
    repository = await PostgresAssessorRepository.create(pool);
  }, 30000);

  afterAll(async () => {
    await pool?.end();
  });

  it('persists the complete repository contract with atomic imports, search and controlled deletion', async () => {
    await repository.saveProject(project);
    await repository.saveAssessment(assessment);

    const first = run(1);
    const firstSession = await repository.beginImport({ sessionId: 'pg-session-1', projectId: project.id, assessmentId: assessment.id, scanRun: first });
    await firstSession.stage(importData(first));
    expect((await repository.loadAssessmentModel(assessment.id))?.scanRuns).toHaveLength(0);
    await firstSession.commit();

    const second = run(2);
    const secondSession = await repository.beginImport({ sessionId: 'pg-session-2', projectId: project.id, assessmentId: assessment.id, scanRun: second });
    await secondSession.stage(importData(second, 'critical'));
    await secondSession.commit();

    await expect(repository.findOccurrences({ assessmentId: assessment.id, search: 'database evidence' }))
      .resolves.toMatchObject({ total: 2 });
    await expect(repository.findOccurrences({ severity: 'critical' })).resolves.toMatchObject({ total: 1 });

    const review: FindingReview = {
      id: 'pg-review', target: { kind: 'occurrence', occurrenceId: 'pg-occurrence-1' },
      workflowStatus: 'open', priority: 'high', includeInActionPlan: true, markers: [],
      createdAt: timestamp, updatedAt: timestamp, revision: 1
    };
    await repository.saveReview(review, 0);
    await expect(repository.saveReview({ ...review, revision: 2 }, 0)).rejects.toBeInstanceOf(RepositoryConflictError);

    await expect(repository.queryUnifiedResults({
      projectId: project.id,
      sourceTool: ['pingcastle'],
      severity: ['critical']
    })).resolves.toMatchObject({
      total: 1,
      items: [{
        sourceKind: 'scanner-finding',
        sourceResultId: 'pg-occurrence-2',
        outcomeNormalized: 'unknown',
        technicalExecutionState: 'completed'
      }]
    });
    await expect(repository.getUnifiedResultById('scanner-finding:pg-occurrence-1')).resolves.toMatchObject({
      reviewStatus: 'open',
      reviewPriority: 'high'
    });
    await expect(repository.queryUnifiedResultFacets({ projectId: project.id })).resolves.toMatchObject({
      total: 2,
      facets: expect.arrayContaining([
        expect.objectContaining({ id: 'severity', values: expect.arrayContaining([
          { value: 'critical', label: 'critical', count: 1 },
          { value: 'high', label: 'high', count: 1 }
        ]) })
      ])
    });
    await expect(repository.listResultCategories({ projectId: project.id })).resolves.toEqual([
      expect.objectContaining({
        namespace: 'pingcastle',
        categoryCode: 'Privileged Accounts',
        sourceProfileId: 'pingcastle',
        sourceProfileVersion: '1.0.0'
      })
    ]);
    const storedDimensions = await pool.query(
      'SELECT outcome_normalized, technical_execution_state FROM assessor_finding_occurrences ORDER BY id'
    );
    expect(storedDimensions.rows).toHaveLength(2);
    expect(storedDimensions.rows.every(row =>
      row.outcome_normalized === 'unknown' && row.technical_execution_state === 'completed')).toBe(true);
    await expect(pool.query(
      'SELECT occurrence_id FROM assessor_finding_occurrence_categories ORDER BY occurrence_id'
    )).resolves.toMatchObject({
      rows: [{ occurrence_id: 'pg-occurrence-1' }, { occurrence_id: 'pg-occurrence-2' }]
    });

    await repository.saveProfileRegistration({ profileId: 'pingcastle', profileVersion: '1.0.0', name: 'PingCastle', schemaVersion: 1, enabled: true });
    await repository.saveLocalComparisonRule(project.id, {
      version: 1, id: 'pg-mapping', left: { toolId: 'pingcastle', ruleId: 'old' },
      right: { toolId: 'pingcastle', ruleId: 'PG-1' }, source: 'user-confirmed'
    });
    const model = await repository.loadAssessmentModel(assessment.id);
    const comparison = compareScanRuns(model!, first.id, second.id);
    await repository.saveComparison({ id: 'pg-comparison', projectId: project.id, assessmentId: assessment.id, comparison });

    const exported = await repository.exportProject(project.id);
    expect(exported.scanRuns).toHaveLength(2);
    expect(exported.findingOccurrences).toHaveLength(2);
    expect(exported.reviews).toHaveLength(1);
    expect(exported.comparisons).toHaveLength(1);
    expect(exported.localComparisonRules).toHaveLength(1);

    const conflicting = run(3);
    const conflictSession = await repository.beginImport({ sessionId: 'pg-conflict', projectId: project.id, assessmentId: assessment.id, scanRun: conflicting });
    await conflictSession.stage(importData(conflicting));
    await repository.saveProfileRegistration({ profileId: 'maester', profileVersion: '1.0.0', name: 'Maester', schemaVersion: 1, enabled: true });
    await expect(conflictSession.commit()).rejects.toBeInstanceOf(RepositoryConflictError);
    expect((await repository.loadAssessmentModel(assessment.id))?.scanRuns).toHaveLength(2);

    await expect(repository.deleteScanRun(project.id, first.id, {
      projectId: project.id, scanRunId: first.id, cascade: true
    })).resolves.toBe(true);
    const afterScanDelete = await repository.loadAssessmentModel(assessment.id);
    expect(afterScanDelete?.scanRuns.map(item => item.id)).toEqual([second.id]);
    expect(afterScanDelete?.findingOccurrences.map(item => item.id)).toEqual(['pg-occurrence-2']);
    await expect(repository.listReviews(project.id)).resolves.toEqual([]);
    await expect(repository.listComparisons(assessment.id)).resolves.toEqual([]);

    await expect(repository.deleteProject(project.id, { projectId: project.id, cascade: true })).resolves.toBe(true);
    await expect(repository.getProject(project.id)).resolves.toBeNull();
    await expect(repository.loadAssessmentModel(assessment.id)).resolves.toBeNull();
  }, 30000);

  it('persists immutable artifacts, processing history and leased jobs atomically', async () => {
    const ingestionProject: AssessmentProject = {
      ...project, id: 'pg-ingestion-project', name: 'PostgreSQL ingestion project'
    };
    await pool.query('DELETE FROM assessor_projects WHERE id = $1', [ingestionProject.id]);
    await repository.saveProject(ingestionProject);
    const artifact: ImportArtifact = {
      version: 1, id: 'pg-artifact', projectId: ingestionProject.id, kind: 'source-file',
      relativePath: 'upload/synthetic.zip', sha256: 'a'.repeat(64), sizeBytes: 100,
      sensitivity: 'confidential', parseStatus: 'pending',
      storage: { kind: 'backend-controlled', storageKey: 'pg-ingestion-project/pg-artifact' }, createdAt: timestamp
    };
    await repository.saveImportArtifact(artifact);
    const processing: ImportProcessingRun = {
      version: 1, id: 'pg-processing-1', projectId: ingestionProject.id, artifactId: artifact.id,
      status: 'partial', processors: [{ kind: 'artifact-parser', id: 'gpo-backup-ingestion', version: '1.0.0' }],
      diagnosticCodes: [], startedAt: timestamp, completedAt: '2026-07-17T08:00:01.000Z'
    };
    const backupRecord: StoredGpoBackupInstance = {
      version: 1,
      id: 'pg-stored-gpo-backup',
      projectId: ingestionProject.id,
      artifactId: artifact.id,
      processingRunId: processing.id,
      instance: {
        version: 1,
        id: 'pg-gpo-instance',
        projectId: ingestionProject.id,
        sourceArtifactId: artifact.id,
        identity: {
          status: 'resolved',
          gpoId: '11111111-1111-4111-8111-111111111111',
          backupId: '22222222-2222-4222-8222-222222222222',
          evidence: 'backup-metadata'
        },
        domain: { status: 'resolved', key: 'dns:synthetic.example', evidence: 'backup-metadata' },
        collection: { status: 'observed', collectedAt: timestamp, evidence: 'backend-receipt' },
        backupSet: { scope: 'unknown', evidence: 'unknown' },
        metadataStatus: 'complete',
        sections: { computer: 'present', user: 'unknown' },
        inventory: {
          version: 1,
          status: 'complete',
          entries: [{
            version: 1,
            artifactId: 'pg-metadata-artifact',
            relativePath: 'synthetic/Backup.xml',
            role: 'backup-metadata',
            scope: 'shared',
            disposition: 'supported',
            secretIndicatorStatus: 'not-detected'
          }, {
            version: 1,
            artifactId: 'pg-security-artifact',
            relativePath: 'synthetic/DomainSysvol/GPO/Machine/Microsoft/Windows NT/SecEdit/GptTmpl.inf',
            role: 'security-template',
            scope: 'computer',
            disposition: 'supported',
            secretIndicatorStatus: 'not-inspected'
          }, {
            version: 1,
            artifactId: 'pg-advanced-audit-artifact',
            relativePath: 'synthetic/DomainSysvol/GPO/Machine/Microsoft/Windows NT/Audit/audit.csv',
            role: 'advanced-audit-policy',
            scope: 'computer',
            disposition: 'supported',
            secretIndicatorStatus: 'not-inspected'
          }, {
            version: 1,
            artifactId: 'pg-gpp-registry-artifact',
            relativePath: 'synthetic/DomainSysvol/GPO/Machine/Preferences/Registry/Registry.xml',
            role: 'group-policy-preference',
            scope: 'computer',
            disposition: 'supported',
            secretIndicatorStatus: 'not-detected'
          }, {
            version: 1,
            artifactId: 'pg-gpp-groups-artifact',
            relativePath: 'synthetic/DomainSysvol/GPO/Machine/Preferences/Groups/Groups.xml',
            role: 'group-policy-preference',
            scope: 'computer',
            disposition: 'supported',
            secretIndicatorStatus: 'present'
          }, {
            version: 1,
            artifactId: 'pg-gpp-services-artifact',
            relativePath: 'synthetic/DomainSysvol/GPO/Machine/Preferences/Services/Services.xml',
            role: 'group-policy-preference',
            scope: 'computer',
            disposition: 'supported',
            secretIndicatorStatus: 'present'
          }, {
            version: 1,
            artifactId: 'pg-gpp-drives-artifact',
            relativePath: 'synthetic/DomainSysvol/GPO/User/Preferences/Drives/Drives.xml',
            role: 'group-policy-preference',
            scope: 'user',
            disposition: 'supported',
            secretIndicatorStatus: 'present'
          }],
          diagnosticCodes: []
        },
        policyObservations: [{
          version: 1,
          id: 'pg-policy-observation',
          backupInstanceId: 'pg-gpo-instance',
          sourceArtifactId: artifact.id,
          artifactId: 'pg-security-artifact',
          sourcePath: 'synthetic/DomainSysvol/GPO/Machine/Microsoft/Windows NT/SecEdit/GptTmpl.inf',
          sourceSha256: 'b'.repeat(64),
          scope: 'computer',
          deliveryMechanism: 'security-template',
          parser: { id: 'gpo-security-template-inf', version: '1.0.0' },
          reducer: { id: 'gpo-security-template-reducer', version: '1.0.0' },
          identity: { status: 'unresolved', namespace: 'security-template', section: 'system access', key: 'MinimumPasswordLength' },
          state: 'configured',
          values: [{ kind: 'scalar', raw: '14' }],
          sourceLines: [4],
          effectiveState: 'unknown',
          diagnosticCodes: []
        }, {
          version: 1,
          id: 'pg-advanced-audit-observation',
          backupInstanceId: 'pg-gpo-instance',
          sourceArtifactId: artifact.id,
          artifactId: 'pg-advanced-audit-artifact',
          sourcePath: 'synthetic/DomainSysvol/GPO/Machine/Microsoft/Windows NT/Audit/audit.csv',
          sourceSha256: 'c'.repeat(64),
          scope: 'computer',
          deliveryMechanism: 'advanced-audit-policy',
          parser: { id: 'gpo-advanced-audit-csv', version: '1.0.0' },
          reducer: { id: 'gpo-advanced-audit-reducer', version: '1.0.0' },
          identity: {
            status: 'unresolved', namespace: 'advanced-audit-policy',
            section: 'system audit subcategory', key: '0cce9210-69ae-11d9-bed3-505054503030'
          },
          state: 'configured',
          values: [{ kind: 'audit-flags', code: 3, success: true, failure: true }],
          sourceLines: [2],
          effectiveState: 'unknown',
          diagnosticCodes: []
        }, {
          version: 1,
          id: 'pg-gpp-registry-observation',
          backupInstanceId: 'pg-gpo-instance',
          sourceArtifactId: artifact.id,
          artifactId: 'pg-gpp-registry-artifact',
          sourcePath: 'synthetic/DomainSysvol/GPO/Machine/Preferences/Registry/Registry.xml',
          sourceSha256: 'd'.repeat(64),
          scope: 'computer',
          deliveryMechanism: 'group-policy-preference',
          parser: { id: 'gpo-preferences-xml', version: '1.0.0' },
          reducer: { id: 'gpo-preferences-registry-reducer', version: '1.0.0' },
          identity: {
            status: 'unresolved', namespace: 'group-policy-preference',
            section: 'registry', key: 'HKEY_LOCAL_MACHINE\Software\Synthetic\Mode'
          },
          state: 'instruction',
          values: [{
            kind: 'registry-preference', hive: 'HKEY_LOCAL_MACHINE', key: 'Software\Synthetic',
            target: 'value', valueName: 'Mode', valueType: 'REG_SZ', rawData: 'Strict'
          }],
          preference: {
            cse: 'registry', itemUid: '33333333-3333-4333-8333-333333333333',
            operation: 'update', sequence: 0, disabled: false, removePolicy: false, applyOnce: false,
            targeting: { status: 'none', filterTypes: [] }
          },
          sourceLines: [1],
          effectiveState: 'unknown',
          diagnosticCodes: []
        }, {
          version: 1,
          id: 'pg-gpp-local-user-observation',
          backupInstanceId: 'pg-gpo-instance',
          sourceArtifactId: artifact.id,
          artifactId: 'pg-gpp-groups-artifact',
          sourcePath: 'synthetic/DomainSysvol/GPO/Machine/Preferences/Groups/Groups.xml',
          sourceSha256: 'e'.repeat(64),
          scope: 'computer',
          deliveryMechanism: 'group-policy-preference',
          parser: { id: 'gpo-preferences-xml', version: '1.0.0' },
          reducer: { id: 'gpo-preferences-local-users-groups-reducer', version: '1.0.0' },
          identity: {
            status: 'unresolved', namespace: 'group-policy-preference',
            section: 'local user', key: 'syntheticoperator'
          },
          state: 'instruction',
          values: [{
            kind: 'local-user-preference', userName: 'SyntheticOperator',
            passwordAttributePresent: true, accountDisabled: true
          }],
          preference: {
            cse: 'local-users-groups', itemUid: '44444444-4444-4444-8444-444444444444',
            operation: 'update', sequence: 0, disabled: false, removePolicy: false, applyOnce: false,
            targeting: { status: 'none', filterTypes: [] }
          },
          sourceLines: [1],
          effectiveState: 'unknown',
          diagnosticCodes: ['gpo-secret-indicator-present']
        }, {
          version: 1,
          id: 'pg-gpp-service-observation',
          backupInstanceId: 'pg-gpo-instance',
          sourceArtifactId: artifact.id,
          artifactId: 'pg-gpp-services-artifact',
          sourcePath: 'synthetic/DomainSysvol/GPO/Machine/Preferences/Services/Services.xml',
          sourceSha256: 'f'.repeat(64),
          scope: 'computer',
          deliveryMechanism: 'group-policy-preference',
          parser: { id: 'gpo-preferences-xml', version: '1.0.0' },
          reducer: { id: 'gpo-preferences-services-reducer', version: '1.0.0' },
          identity: {
            status: 'unresolved', namespace: 'group-policy-preference',
            section: 'service', key: 'syntheticservice'
          },
          state: 'instruction',
          values: [{
            kind: 'service-preference', serviceName: 'SyntheticService', serviceAction: 'restart-if-required',
            startupType: 'automatic', accountName: 'Synthetic\\Service', passwordAttributePresent: true,
            thirdFailure: 'restart', restartServiceDelay: 900000
          }],
          preference: {
            cse: 'services', itemUid: '55555555-5555-4555-8555-555555555555',
            operation: 'configure', sequence: 0, disabled: false, removePolicy: false, applyOnce: false,
            targeting: { status: 'none', filterTypes: [] }
          },
          sourceLines: [1],
          effectiveState: 'unknown',
          diagnosticCodes: ['gpo-secret-indicator-present']
        }, {
          version: 1,
          id: 'pg-gpp-drive-map-observation',
          backupInstanceId: 'pg-gpo-instance',
          sourceArtifactId: artifact.id,
          artifactId: 'pg-gpp-drives-artifact',
          sourcePath: 'synthetic/DomainSysvol/GPO/User/Preferences/Drives/Drives.xml',
          sourceSha256: '1'.repeat(64),
          scope: 'user',
          deliveryMechanism: 'group-policy-preference',
          parser: { id: 'gpo-preferences-xml', version: '1.0.0' },
          reducer: { id: 'gpo-preferences-drive-maps-reducer', version: '1.0.0' },
          identity: {
            status: 'unresolved', namespace: 'group-policy-preference',
            section: 'drive map', key: 'S:exact'
          },
          state: 'instruction',
          values: [{
            kind: 'drive-map-preference', path: '\\\\synthetic-server\\share', label: 'Synthetic Share',
            persistent: true, userName: 'SYNTHETIC\\DriveUser', passwordAttributePresent: true,
            letter: 'S', useLetter: true, selectionMode: 'exact', thisDrive: 'show', allDrives: 'no-change'
          }],
          preference: {
            cse: 'drive-maps', itemUid: '66666666-6666-4666-8666-666666666666',
            operation: 'replace', sequence: 0, disabled: false, removePolicy: false, applyOnce: false,
            targeting: { status: 'none', filterTypes: [] }
          },
          sourceLines: [1],
          effectiveState: 'unknown',
          diagnosticCodes: ['gpo-secret-indicator-present']
        }],
        discoveredAt: timestamp
      },
      createdAt: '2026-07-17T08:00:01.000Z'
    };
    await repository.saveGpoBackupAnalysis(processing, [backupRecord]);
    await expect(repository.listProjectProcessingRuns(ingestionProject.id)).resolves.toMatchObject([{ id: processing.id, artifactId: artifact.id }]);
    const revisionAfterAnalysis = repository.revision;
    await expect(repository.saveGpoBackupAnalysis(processing, [backupRecord])).resolves.toBeUndefined();
    expect(repository.revision).toBe(revisionAfterAnalysis);
    await expect(repository.saveGpoBackupAnalysis(processing, [{ ...backupRecord, createdAt: '2026-07-17T08:00:02.000Z' }]))
      .rejects.toBeInstanceOf(RepositoryConflictError);

    const job: IngestionJob = {
      version: 1, id: 'pg-job', projectId: ingestionProject.id, artifactId: artifact.id,
      kind: 'analyze-gpo-backup', idempotencyKey: 'gpo:pg-artifact:1.0.0', status: 'queued',
      attempts: 0, maxAttempts: 3, availableAt: timestamp, createdAt: timestamp, updatedAt: timestamp
    };
    await repository.enqueueIngestionJob(job);
    const revisionAfterJobInsert = repository.revision;
    await expect(repository.enqueueIngestionJob({ ...job, id: 'ignored-by-idempotency' })).resolves.toMatchObject({ id: job.id });
    expect(repository.revision).toBe(revisionAfterJobInsert);
    await repository.claimNextIngestionJob({ workerId: 'worker-old', now: timestamp, leaseDurationMs: 1000, kinds: ['analyze-gpo-backup'] });
    const recovered = await repository.claimNextIngestionJob({
      workerId: 'worker-new', now: '2026-07-17T08:00:01.001Z', leaseDurationMs: 1000, kinds: ['analyze-gpo-backup']
    });
    expect(recovered).toMatchObject({ id: job.id, status: 'running', attempts: 2, leaseOwner: 'worker-new' });
    await expect(repository.completeIngestionJob(job.id, 'worker-old', '2026-07-17T08:00:01.200Z')).rejects.toBeInstanceOf(RepositoryConflictError);
    await expect(repository.completeIngestionJob(job.id, 'worker-new', '2026-07-17T08:00:01.500Z'))
      .resolves.toMatchObject({ status: 'succeeded', attempts: 2 });

    const exported = await repository.exportProject(ingestionProject.id);
    expect(exported).toMatchObject({ importArtifacts: [{ id: artifact.id }], processingRuns: [{ id: processing.id }], gpoBackupInstances: [{ id: backupRecord.id, instance: { policyObservations: [{ id: 'pg-policy-observation' }, { id: 'pg-advanced-audit-observation' }, { id: 'pg-gpp-registry-observation' }, { id: 'pg-gpp-local-user-observation' }, { id: 'pg-gpp-service-observation' }, { id: 'pg-gpp-drive-map-observation' }] } }], ingestionJobs: [{ id: job.id }] });
    await repository.deleteProject(ingestionProject.id, { projectId: ingestionProject.id, cascade: true });
  }, 30000);
  it('commits a GPO artifact and its job atomically and rolls both back on an idempotency conflict', async () => {
    const atomicProject: AssessmentProject = { ...project, id: 'pg-atomic-gpo-project', name: 'PostgreSQL atomic GPO project' };
    await pool.query('DELETE FROM assessor_projects WHERE id = $1', [atomicProject.id]);
    await repository.saveProject(atomicProject);
    const firstArtifact: ImportArtifact = {
      version: 1, id: 'pg-atomic-artifact-1', projectId: atomicProject.id, kind: 'source-file',
      relativePath: 'gpo-backups/first.zip', sha256: 'b'.repeat(64), sizeBytes: 10,
      sensitivity: 'secret', parseStatus: 'pending',
      storage: { kind: 'backend-controlled', storageKey: 'synthetic-atomic-storage-1' }, createdAt: timestamp
    };
    const firstJob: IngestionJob = {
      version: 1, id: 'pg-atomic-job-1', projectId: atomicProject.id, artifactId: firstArtifact.id,
      kind: 'analyze-gpo-backup', idempotencyKey: 'atomic-gpo-key', status: 'queued', attempts: 0, maxAttempts: 3,
      availableAt: timestamp, createdAt: timestamp, updatedAt: timestamp
    };
    await expect(repository.saveImportArtifactAndEnqueueJob(firstArtifact, firstJob)).resolves.toMatchObject({
      artifactCreated: true, jobCreated: true, artifact: { id: firstArtifact.id }, job: { id: firstJob.id }
    });
    const conflictingArtifact: ImportArtifact = {
      ...firstArtifact,
      id: 'pg-atomic-artifact-2',
      relativePath: 'gpo-backups/second.zip',
      sha256: 'c'.repeat(64),
      storage: { kind: 'backend-controlled', storageKey: 'synthetic-atomic-storage-2' }
    };
    const conflictingJob: IngestionJob = { ...firstJob, id: 'pg-atomic-job-2', artifactId: conflictingArtifact.id };

    await expect(repository.saveImportArtifactAndEnqueueJob(conflictingArtifact, conflictingJob)).rejects.toBeInstanceOf(RepositoryConflictError);
    await expect(repository.listImportArtifacts(atomicProject.id)).resolves.toEqual([firstArtifact]);
    await expect(repository.listIngestionJobs(atomicProject.id)).resolves.toEqual([firstJob]);
    await repository.deleteProject(atomicProject.id, { projectId: atomicProject.id, cascade: true });
  }, 30000);
  it('selects PostgreSQL through the same backend runtime factory without silent fallback', async () => {
    const runtime = await createRepositoryRuntime({
      ASSESSOR_DATABASE_URL: connectionString,
      ASSESSOR_DATABASE_SSL: 'disable',
      ASSESSOR_DATABASE_POOL_SIZE: '2'
    });
    try {
      expect(runtime.persistence).toBe('postgresql');
      await expect(runtime.repository.listProjects()).resolves.toEqual([]);
    } finally {
      await runtime.close();
    }
  });
  it('persists durable import fingerprints and increments duplicate counters transactionally', async () => {
    const sha256 = 'f'.repeat(64);
    await pool.query('DELETE FROM assessor_import_fingerprints WHERE sha256 = $1', [sha256]);
    const entry = { sha256, fileName: 'repeat.csv', sizeBytes: 99, lastModified: 17 };
    await repository.recordImportFingerprints([entry], '2026-07-17T10:00:00.000Z');
    const repeated = await repository.recordImportFingerprints([entry], '2026-07-17T11:00:00.000Z');
    expect(repeated[0]).toMatchObject({ ...entry, importCount: 2, firstImportedAt: '2026-07-17T10:00:00.000Z', lastImportedAt: '2026-07-17T11:00:00.000Z' });
  });
  it('commits a prepared canonical batch and its fingerprint in one PostgreSQL revision', async () => {
    const batchProject: AssessmentProject = { ...project, id: 'pg-batch-project', name: 'Batch project' };
    const batchAssessment: Assessment = { ...assessment, id: 'pg-batch-assessment', projectId: batchProject.id };
    const batchRun: ScanRun = { ...run(7), id: 'pg-batch-run', assessmentId: batchAssessment.id };
    const sha256 = 'e'.repeat(64);
    const staged = importData(batchRun);
    const model: CanonicalAssessmentModel = {
      schemaVersion: 1,
      project: batchProject,
      assessment: batchAssessment,
      scanRuns: [batchRun],
      importFiles: staged.importFiles!.map(file => ({ ...file, sha256 })),
      sourceTables: staged.sourceTables!,
      findingDefinitions: staged.findingDefinitions!,
      findingOccurrences: staged.findingOccurrences!,
      evidence: staged.evidence!,
      reviews: [],
      diagnostics: staged.diagnostics!
    };
    await pool.query('DELETE FROM assessor_import_fingerprints WHERE sha256 = $1', [sha256]);
    const before = repository.revision;
    const committed = await repository.commitCanonicalImport({
      sessionId: 'pg-canonical-batch',
      model,
      fingerprints: [{ sha256, fileName: staged.importFiles![0].fileName, sizeBytes: 100, lastModified: 0 }]
    });
    expect(committed).toMatchObject({ repositoryRevision: before + 1, importedFiles: 1, importedOccurrences: 1 });
    await expect(repository.loadAssessmentModel(batchAssessment.id)).resolves.toMatchObject({ importFiles: [{ id: staged.importFiles![0].id }] });
    await expect(repository.findImportFingerprints([sha256])).resolves.toMatchObject([{ importCount: 1 }]);
    await repository.deleteProject(batchProject.id, { projectId: batchProject.id, cascade: true });
  });
});