import assert from "node:assert/strict";
import { existsSync, readFileSync } from "node:fs";
import { fileURLToPath } from "node:url";
import test from "node:test";

const migration = readFileSync(
  new URL(
    "../supabase/migrations/20260812010000_agentic_rectification_v9_runtime.sql",
    import.meta.url,
  ),
  "utf8",
);
const dbMigrationsCopy = fileURLToPath(
  new URL("../db/migrations/20260812010000_agentic_rectification_v9_runtime.sql", import.meta.url),
);

test("v9 runtime migration sorts after the newest business migration and stays unique", () => {
  assert.ok(
    "20260812010000_agentic_rectification_v9_runtime.sql" >
      "20260811010000_consultation_status_service_role_read.sql",
  );
  assert.match(migration, /^begin;[\s\S]*^commit;$/m);
});

test("v9 runtime migration must never be duplicated into the identity foundation", () => {
  assert.equal(
    existsSync(dbMigrationsCopy),
    false,
    "business migration must not be copied into frontend/db/migrations (BUG-127/BUG-144)",
  );
});

test("v9 runtime migration creates the five durable tables with required columns", () => {
  assert.match(migration, /create table if not exists public\.agentic_rectification_cases \(/);
  assert.match(migration, /user_id uuid not null references auth\.users\(id\) on delete cascade/);
  assert.match(migration, /session_id uuid not null unique references public\.chat_sessions\(id\) on delete cascade/);
  assert.match(migration, /baseline_profile_fingerprint text not null/);
  assert.match(migration, /baseline_birth_snapshot jsonb not null/);
  assert.match(migration, /skill_version text not null/);
  assert.match(migration, /accepted_time time without time zone/);
  assert.match(migration, /confirmed_time time without time zone/);
  assert.match(migration, /completed_at timestamptz/);
  assert.match(migration, /closed_reason text/);

  assert.match(migration, /create table if not exists public\.agentic_rectification_evidence \(/);
  assert.match(migration, /source_turn_id uuid not null references public\.agentic_rectification_turns\(id\)/);
  assert.match(migration, /user_quote text not null/);
  assert.match(migration, /event_kind text not null/);
  assert.match(migration, /date_precision text not null check \(date_precision in \('year', 'month', 'day', 'range', 'unknown'\)\)/);
  assert.match(migration, /supersedes_evidence_id uuid references public\.agentic_rectification_evidence\(id\)/);

  assert.match(migration, /create table if not exists public\.agentic_rectification_turns \(/);
  assert.match(migration, /status text not null check \(status in \('pending', 'completed', 'failed', 'retryable'\)\)/);
  assert.match(migration, /model_name text not null/);
  const turnsTable = migration.slice(
    migration.indexOf("create table if not exists public.agentic_rectification_turns"),
    migration.indexOf("-- 3. Evidence"),
  );
  assert.doesNotMatch(turnsTable, /reasoning/);

  assert.match(migration, /create table if not exists public\.agentic_rectification_tool_receipts \(/);
  assert.match(migration, /input_fingerprint text/);
  assert.match(migration, /result_fingerprint text/);
  assert.match(migration, /safe_error_code text/);
  assert.doesNotMatch(migration, /create table if not exists public\.agentic_rectification_tool_receipts \([\s\S]*payload/);

  assert.match(migration, /create table if not exists public\.agentic_rectification_open_ledger \(/);
  assert.match(migration, /primary key \(user_id, request_id\)/);
});

test("v9 runtime migration enforces one resumable case per user at the database level", () => {
  assert.match(
    migration,
    /create unique index if not exists agentic_rectification_cases_one_resumable_per_user[\s\S]*where status in \([\s\S]*'draft'[\s\S]*'collecting_evidence'[\s\S]*'candidate_ready'[\s\S]*'candidate_accepted'[\s\S]*'needs_rebaseline'[\s\S]*'paused'[\s\S]*\)/,
  );
});

test("v9 runtime migration keeps Case/Session bidirectional consistency", () => {
  assert.match(migration, /alter table public\.chat_sessions\s+add column if not exists agentic_rectification_case_id uuid/);
  assert.match(migration, /create unique index if not exists chat_sessions_agentic_rectification_case_unique/);
  assert.match(migration, /agentic_rectification_cases_sync_session/);
  assert.match(migration, /agentic_rectification_case_session_mismatch/);
  assert.match(migration, /agentic_rectification_case_owner_mismatch/);
});

test("v9 runtime migration extends results with case_id and fingerprints", () => {
  assert.match(migration, /add column if not exists case_id uuid/);
  assert.match(migration, /add column if not exists evidence_ledger_fingerprint text/);
  assert.match(migration, /add column if not exists candidate_range_fingerprint text/);
  assert.match(migration, /add column if not exists skill_version text/);
});

test("v9 runtime migration grants only service_role on the new tables", () => {
  for (const table of [
    "agentic_rectification_cases",
    "agentic_rectification_turns",
    "agentic_rectification_evidence",
    "agentic_rectification_tool_receipts",
    "agentic_rectification_open_ledger",
  ]) {
    assert.match(migration, new RegExp(`revoke all on table public\\.${table} from public, anon, authenticated, service_role`));
    assert.match(migration, new RegExp(`grant all on table public\\.${table} to service_role`));
    assert.doesNotMatch(migration, new RegExp(`grant select on table public\\.${table} to authenticated`));
  }
});

test("v9 RPCs are security definer and service-role only", () => {
  for (const functionName of [
    "open_agentic_rectification_case",
    "get_agentic_rectification_entry_summary",
    "get_agentic_rectification_case",
    "close_agentic_rectification_case",
    "upgrade_agentic_rectification_skill",
    "append_agentic_rectification_turn",
    "insert_agentic_rectification_tool_receipt",
    "propose_agentic_rectification_evidence",
    "confirm_agentic_rectification_evidence",
    "revise_agentic_rectification_evidence",
  ]) {
    assert.match(migration, new RegExp(`create or replace function public\\.${functionName}\\(`));
    assert.match(migration, new RegExp(`grant execute on function public\\.${functionName}\\([\\s\\S]*?to service_role`));
  }
  assert.doesNotMatch(
    migration,
    /grant execute on function public\.open_agentic_rectification_case\([\s\S]*?to authenticated/,
  );
});

test("open RPC serializes same-user requests and forbids silent supersede", () => {
  assert.match(migration, /pg_catalog\.pg_advisory_xact_lock\(/);
  assert.match(migration, /hashtext\('agentic_rectification_open:' \|\| p_user_id::text\)/);
  assert.match(migration, /agentic_rectification_active_case_conflict/);
  assert.doesNotMatch(migration, /supersede_active/);
  assert.doesNotMatch(migration, /p_supersede/);
});

test("open RPC creates the case and session atomically and derives shouldStartOpening", () => {
  const open = migration.slice(
    migration.indexOf("create or replace function public.open_agentic_rectification_case"),
    migration.indexOf("-- 12. Entry summary"),
  );
  assert.match(open, /insert into public\.chat_sessions/);
  assert.match(open, /insert into public\.agentic_rectification_cases/);
  assert.match(open, /insert into public\.agentic_rectification_open_ledger/);
  assert.match(open, /exception when unique_violation/);
  assert.match(open, /'should_start_opening', true/);
  // Snapshot/range validation lives inside the create block, before the
  // inserts -- a session/view-only open never needs a complete profile.
  const createBlock = open.slice(
    open.indexOf("Create a new case + a new session atomically"),
    open.lastIndexOf("return jsonb_build_object("),
  );
  assert.match(createBlock, /agentic_rectification_profile_incomplete/);
  assert.match(createBlock, /agentic_rectification_invalid_range/);
});

test("open RPC never creates a case for an incomplete profile", () => {
  assert.match(migration, /agentic_rectification_profile_incomplete/);
  assert.match(migration, /p_baseline_birth_snapshot ->> 'birth_date' is null/);
});

test("terminal cases reject evidence and turn writes", () => {
  const propose = migration.slice(
    migration.indexOf("create or replace function public.propose_agentic_rectification_evidence"),
    migration.indexOf("create or replace function public.confirm_agentic_rectification_evidence"),
  );
  assert.match(propose, /agentic_rectification_case_terminal/);
  const append = migration.slice(
    migration.indexOf("create or replace function public.append_agentic_rectification_turn"),
    migration.indexOf("-- 17. Tool receipt"),
  );
  assert.match(append, /agentic_rectification_case_terminal/);
});

test("evidence proposals require quote grounding in the source turn", () => {
  assert.match(migration, /agentic_rectification_quote_not_grounded/);
  assert.match(migration, /agentic_rectification_normalize_quote\(p_user_quote\)/);
  assert.match(migration, /agentic_rectification_normalize_quote\(v_turn\.user_message\)/);
});

test("evidence revisions are append-only and never generate confirmed evidence from text", () => {
  assert.match(migration, /set status = 'superseded'/);
  assert.match(migration, /supersedes_evidence_id[\s\S]*'pending_confirmation', v_target\.id/);
  const backfill = migration.slice(
    migration.indexOf("create or replace function public.backfill_agentic_rectification_legacy_cases"),
    migration.indexOf("-- 20. Backfill verification"),
  );
  assert.doesNotMatch(backfill, /insert into public\.agentic_rectification_evidence/);
});

test("legacy backfill maps every documented status and keeps one resumable per user", () => {
  const backfill = migration.slice(
    migration.indexOf("create or replace function public.backfill_agentic_rectification_legacy_cases"),
    migration.indexOf("-- 20. Backfill verification"),
  );
  assert.match(backfill, /when result_kind = 'engine_confirmed' then 'confirmed'/);
  assert.match(backfill, /when result_kind = 'user_accepted' then 'candidate_accepted'/);
  assert.match(backfill, /when has_messages then case when has_results then 'candidate_ready' else 'collecting_evidence' end/);
  assert.match(backfill, /then 'superseded'/);
  assert.match(backfill, /then 'draft'/);
  assert.match(backfill, /agentic_rectification_legacy_fingerprint/);
  assert.match(backfill, /update public\.agentic_rectification_results\s+set case_id = v_case_id/);
  assert.match(backfill, /not exists \(\s*select 1 from public\.agentic_rectification_cases c where c\.session_id = s\.id\s*\)/);
});

test("backfill apply runs inside the migration and verification is re-runnable", () => {
  assert.match(migration, /select public\.backfill_agentic_rectification_legacy_cases\(\) into v_result/);
  assert.match(migration, /create or replace function public\.verify_agentic_rectification_backfill\(\)/);
  assert.match(migration, /resumable_conflicts/);
  assert.match(migration, /orphan_cases/);
});

test("v9 fingerprint uses the already-installed pgcrypto digest", () => {
  assert.match(migration, /public\.digest\(/);
  assert.match(migration, /'sha256'/);
});

// ---------------------------------------------------------------------------
// 20260813010000_agentic_rectification_v9_agent_api.sql
// ---------------------------------------------------------------------------

const agentApiMigration = readFileSync(
  new URL(
    "../supabase/migrations/20260813010000_agentic_rectification_v9_agent_api.sql",
    import.meta.url,
  ),
  "utf8",
);

test("v9 agent api migration sorts after the v9 runtime and stays a single transaction", () => {
  assert.ok(
    "20260813010000_agentic_rectification_v9_agent_api.sql" >
      "20260812010000_agentic_rectification_v9_runtime.sql",
  );
  assert.match(agentApiMigration, /^begin;[\s\S]*^commit;$/m);
});

test("v9 agent api migration adds the durable run phases table for skill evidence", () => {
  assert.match(agentApiMigration, /create table if not exists public\.agentic_rectification_run_phases \(/);
  assert.match(agentApiMigration, /phase text not null check \(\s*phase in \(/);
  assert.match(agentApiMigration, /'skill\.started', 'skill\.loaded'/);
  assert.match(agentApiMigration, /alter table public\.agentic_rectification_run_phases enable row level security/);
  assert.match(agentApiMigration, /grant all on table public\.agentic_rectification_run_phases to service_role/);
});

test("v9 agent api migration adds dossier, finalize, candidate and consent RPCs", () => {
  assert.match(agentApiMigration, /create or replace function public\.get_agentic_rectification_case_dossier\(/);
  assert.match(agentApiMigration, /create or replace function public\.get_agentic_rectification_case_compute\(/);
  assert.match(agentApiMigration, /create or replace function public\.finalize_agentic_rectification_turn\(/);
  assert.match(agentApiMigration, /create or replace function public\.persist_agentic_rectification_candidate\(/);
  assert.match(agentApiMigration, /create or replace function public\.accept_agentic_rectification_candidate_for_case\(/);
  assert.match(agentApiMigration, /create or replace function public\.confirm_agentic_rectification_birth_time\(/);
  assert.match(agentApiMigration, /create or replace function public\.transition_agentic_rectification_case_status\(/);
  assert.match(agentApiMigration, /create or replace function public\.insert_agentic_rectification_run_phase\(/);
  assert.match(agentApiMigration, /create or replace function public\.get_agentic_rectification_turn_receipt\(/);
});

test("candidate fingerprint cache reuse and terminal/skill-version guards are enforced", () => {
  assert.match(agentApiMigration, /evidence_ledger_fingerprint = p_evidence_ledger_fingerprint/);
  assert.match(agentApiMigration, /candidate_range_fingerprint = p_candidate_range_fingerprint/);
  assert.match(agentApiMigration, /agentic_rectification_skill_version_mismatch/);
  assert.match(agentApiMigration, /agentic_rectification_case_terminal/);
  assert.match(agentApiMigration, /agentic_rectification_confirmation_blocked/);
  assert.match(agentApiMigration, /agentic_rectification_confirm_time_mismatch/);
});

test("accept replay idempotency precedes the profile baseline check", () => {
  // Regression guard: a second accept of the same candidate must return
  // idempotent=true, not fail with candidate_profile_changed. The profile
  // legitimately diverges from the baseline snapshot after the first accept,
  // so the replay branch (selected_time already set) must come BEFORE the
  // baseline comparison. Verified against a real PostgreSQL 17 run.
  const acceptFn = agentApiMigration.slice(
    agentApiMigration.indexOf("create or replace function public.accept_agentic_rectification_candidate_for_case"),
    agentApiMigration.indexOf("-- ---------------------------------------------------------------------------\n-- 6. Confirm birth time"),
  );
  const idempotentBranch = acceptFn.indexOf("if v_result.selected_time is not null then");
  const profileCheck = acceptFn.indexOf("agentic_rectification_candidate_profile_changed");
  assert.ok(idempotentBranch >= 0, "idempotent replay branch must exist");
  assert.ok(profileCheck >= 0, "profile baseline check must exist");
  assert.ok(
    idempotentBranch < profileCheck,
    "idempotent replay must be evaluated before the profile baseline check",
  );
  // The replay validates the profile against the accepted selection, not the
  // stale baseline snapshot.
  const replayProfileCheck = acceptFn.slice(idempotentBranch, acceptFn.indexOf("v_snapshot := v_case.baseline_birth_snapshot;"));
  assert.match(replayProfileCheck, /v_profile\.active_birth_time is distinct from v_result\.selected_time/);
});

test("confirmation gate requires a consent quote grounded in the source turn", () => {
  assert.match(agentApiMigration, /agentic_rectification_consent_not_grounded/);
  assert.match(agentApiMigration, /agentic_rectification_normalize_quote\(p_consent_quote\)/);
  assert.match(agentApiMigration, /agentic_rectification_normalize_quote\(v_turn\.user_message\)/);
  assert.match(agentApiMigration, /reaches 'confirmed'/);
  assert.match(agentApiMigration, /birth_time_status = 'confirmed'/);
});

test("needs_rebaseline guard flips resumable cases on profile change", () => {
  assert.match(agentApiMigration, /create or replace function public\.agentic_rectification_profiles_rebaseline_guard\(\)/);
  assert.match(agentApiMigration, /create trigger agentic_rectification_profiles_rebaseline_guard_trigger/);
  assert.match(agentApiMigration, /status = 'needs_rebaseline'/);
  assert.match(agentApiMigration, /agentic_rectification_resumable_statuses\(\)/);
});

test("v9 agent api migration seeds the DB-driven runtime selector flag", () => {
  assert.match(agentApiMigration, /'rectification_runtime_version'/);
  assert.match(agentApiMigration, /\"version\":\"v9\",\"legacy_mode\":\"readonly\"/);
  assert.match(agentApiMigration, /on conflict \(flag_key, version\) do nothing/);
});

test("v9 agent api migration must never be duplicated into the identity foundation", () => {
  const copy = fileURLToPath(
    new URL("../db/migrations/20260813010000_agentic_rectification_v9_agent_api.sql", import.meta.url),
  );
  assert.equal(
    existsSync(copy),
    false,
    "business migration must not be copied into frontend/db/migrations (BUG-127/BUG-144)",
  );
});