import { readFile } from 'node:fs/promises';
import { resolve } from 'node:path';
import { describe, expect, it } from 'vitest';

const migrationPath = resolve(import.meta.dirname, '..', '..', 'server', 'postgres', 'migrations', '001_initial_schema.sql');
const environmentMigrationPath = resolve(import.meta.dirname, '..', '..', 'server', 'postgres', 'migrations', '018_scanner_environment_context.sql');

describe('PostgreSQL initial schema', () => {
  it('defines relational integrity, cascade behavior and repository revision state', async () => {
    const sql = await readFile(migrationPath, 'utf8');
    expect(sql).toContain('CREATE TABLE IF NOT EXISTS assessor_repository_state');
    expect(sql).toContain('REFERENCES assessor_projects(id) ON DELETE CASCADE');
    expect(sql).toContain('REFERENCES assessor_assessments(id) ON DELETE CASCADE');
    expect(sql).toContain('REFERENCES assessor_scan_runs(id) ON DELETE CASCADE');
    expect(sql).toContain('REFERENCES assessor_finding_definitions(id) ON DELETE RESTRICT');
    expect(sql).toContain('UNIQUE (assessment_id, sequence)');
    expect(sql).toContain('CHECK (revision >= 1)');
  });

  it('contains GIN indexes for full-text and JSONB queries plus foreign-key indexes', async () => {
    const sql = await readFile(migrationPath, 'utf8');
    expect(sql.match(/USING gin/gi)).toHaveLength(3);
    expect(sql).toContain('search_document tsvector GENERATED ALWAYS AS');
    expect(sql).toContain('data jsonb_path_ops');
    expect(sql).toContain('assessor_assessments_project_idx');
    expect(sql).toContain('assessor_occurrences_scan_run_idx');
  });

  it('stores complete scanner sections and typed environment observations with scan-run cascade behavior', async () => {
    const sql = await readFile(environmentMigrationPath, 'utf8');
    expect(sql).toContain('CREATE TABLE assessor_scanner_report_sections');
    expect(sql).toContain('CREATE TABLE assessor_environment_observations');
    expect(sql.match(/REFERENCES assessor_scan_runs\(id\) ON DELETE CASCADE/g)).toHaveLength(2);
    expect(sql).toContain("'license-restricted'");
    expect(sql).toContain('property_key text NOT NULL');
    expect(sql.match(/USING gin/gi)).toHaveLength(2);
  });
});