import { readFile } from 'node:fs/promises';
import { resolve } from 'node:path';
import { describe, expect, it } from 'vitest';
import { databaseMigrations } from '../../server/postgres/migration-catalog';

const migrationPath = resolve(
  import.meta.dirname,
  '..',
  '..',
  'server',
  'postgres',
  'migrations',
  '009_asset_graph.sql'
);

describe('PostgreSQL asset-graph migration', () => {
  it('is registered in the migration catalog', async () => {
    expect(databaseMigrations.find(migration => migration.version === '009')).toMatchObject({
      version: '009',
      name: 'asset-graph'
    });
  });

  it('creates the four additive RESULT_CONTRACT asset/edge tables', async () => {
    const sql = await readFile(migrationPath, 'utf8');
    expect(sql).toContain('CREATE TABLE assessor_canonical_assets');
    expect(sql).toContain('CREATE TABLE assessor_asset_properties');
    expect(sql).toContain('CREATE TABLE assessor_asset_edges');
    expect(sql).toContain('CREATE TABLE assessor_edge_coverage');
    expect(sql).not.toMatch(/DROP\s+(TABLE|COLUMN)/i);
  });

  it('keeps node identity authoritative and deduplicated per assessment', async () => {
    const sql = await readFile(migrationPath, 'utf8');
    expect(sql).toContain('CONSTRAINT assessor_canonical_assets_key_uq UNIQUE (assessment_id, asset_key)');
    expect(sql).toContain("'sid', 'object_guid', 'gpo_guid', 'domain_sid', 'trust_key', 'synthetic'");
    expect(sql).toContain('CHECK (key_confidence >= 0 AND key_confidence <= 1)');
  });

  it('requires provenance on every edge', async () => {
    const sql = await readFile(migrationPath, 'utf8');
    expect(sql).toContain("NOT NULL CHECK (btrim(edge_source) <> '')");
    expect(sql).toContain("NOT NULL CHECK (btrim(collection_method) <> '')");
    expect(sql).toContain("NOT NULL CHECK (btrim(collector_version) <> '')");
  });

  it('references the existing scope tables instead of introducing a parallel schema', async () => {
    const sql = await readFile(migrationPath, 'utf8');
    expect(sql).toContain('REFERENCES assessor_assessments(id) ON DELETE RESTRICT');
    expect(sql).toContain('REFERENCES assessor_canonical_assets(id) ON DELETE CASCADE');
    expect(sql).toContain('REFERENCES assessor_finding_occurrences(id) ON DELETE SET NULL');
    expect(sql).not.toContain('CREATE SCHEMA');
    expect(sql).not.toContain('search_path');
    expect(sql).not.toContain('DEFAULT gen_random_uuid()');
  });
});