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)",
  );
});

// ---------------------------------------------------------------------------
// 20260813020000_rectification_birth_context_activity.sql
// ---------------------------------------------------------------------------

const birthContextActivityMigration = readFileSync(
  new URL(
    "../supabase/migrations/20260813020000_rectification_birth_context_activity.sql",
    import.meta.url,
  ),
  "utf8",
);

const birthContextActivityMigrationCopy = fileURLToPath(
  new URL(
    "../db/migrations/20260813020000_rectification_birth_context_activity.sql",
    import.meta.url,
  ),
);

test("birth-context activity migration follows the existing V9 API migration in one transaction", () => {
  assert.ok(
    "20260813020000_rectification_birth_context_activity.sql" >
      "20260813010000_agentic_rectification_v9_agent_api.sql",
  );
  assert.match(birthContextActivityMigration, /^-- Created 2026-08-12\.[\s\S]*\nbegin;[\s\S]*^commit;$/m);
});

test("birth-context activity migration persists only allowlisted completed tool methods", () => {
  assert.match(
    birthContextActivityMigration,
    /add column if not exists executed_methods jsonb not null default '\[\]'::jsonb/,
  );
  assert.match(
    birthContextActivityMigration,
    /drop function if exists public\.insert_agentic_rectification_tool_receipt\(\s*uuid, uuid, uuid, text, text, text, text, text, text, text\s*\)/,
  );
  const insertReceipt = birthContextActivityMigration.slice(
    birthContextActivityMigration.indexOf("create or replace function public.insert_agentic_rectification_tool_receipt"),
    birthContextActivityMigration.indexOf("create or replace function public.get_agentic_rectification_turn_receipt"),
  );
  assert.match(insertReceipt, /p_executed_methods jsonb/);
  assert.match(insertReceipt, /uuid, uuid, uuid, text, text, text, text, text, text, text, jsonb/);
  for (const method of [
    "d1-rashi", "d2-hora", "d4-chaturthamsha", "d9-navamsa", "d10-dashamsa",
    "d11-labhamsha", "d24-chaturvimshamsha", "d30-trimshamsha",
    "vimshottari-dasha", "narayana-dasha", "gochara", "ashtakavarga",
    "shadbala", "arudha-pada", "functional-benefic-malefic",
  ]) {
    assert.match(insertReceipt, new RegExp(`'${method}'`));
  }

  const getReceipt = birthContextActivityMigration.slice(
    birthContextActivityMigration.indexOf("create or replace function public.get_agentic_rectification_turn_receipt"),
    birthContextActivityMigration.indexOf("create temporary table rectification_birth_context_affected_cases"),
  );
  assert.equal((getReceipt.match(/tr\.status = 'completed'/g) ?? []).length, 2);
  assert.match(getReceipt, /'methods', v_methods/);
});

test("birth-context profile changes rebaseline resumable cases and invalidate active results", () => {
  assert.match(birthContextActivityMigration, /baseline_birth_snapshot[\s\S]*'birth_place_label'[\s\S]*'timezone_id'/);
  assert.match(
    birthContextActivityMigration,
    /update public\.agentic_rectification_cases c\s+set status = 'needs_rebaseline'[\s\S]*from rectification_birth_context_affected_cases affected\s+where c\.id = affected\.id;/,
  );
  assert.match(birthContextActivityMigration, /status = any \(public\.agentic_rectification_resumable_statuses\(\)\)/);
  assert.match(birthContextActivityMigration, /update public\.agentic_rectification_results r[\s\S]*r\.invalidated_at is null/);
  assert.ok((birthContextActivityMigration.match(/old\.birth_place_label is distinct from new\.birth_place_label/g) ?? []).length >= 2);
  assert.ok((birthContextActivityMigration.match(/old\.timezone_id is distinct from new\.timezone_id/g) ?? []).length >= 2);
  assert.ok((birthContextActivityMigration.match(/after update of birth_date, birth_place_label/g) ?? []).length >= 2);
});

test("birth-context activity migration stays out of the identity migration tree", () => {
  assert.equal(
    existsSync(birthContextActivityMigrationCopy),
    false,
    "business migration must not be copied into frontend/db/migrations (BUG-127/BUG-144)",
  );
});

// ---------------------------------------------------------------------------
// 20260813030000_rectification_turn_message_projection.sql
// ---------------------------------------------------------------------------

const turnMessageProjectionMigration = readFileSync(
  new URL(
    "../supabase/migrations/20260813030000_rectification_turn_message_projection.sql",
    import.meta.url,
  ),
  "utf8",
);

const turnMessageProjectionMigrationCopy = fileURLToPath(
  new URL(
    "../db/migrations/20260813030000_rectification_turn_message_projection.sql",
    import.meta.url,
  ),
);

test("rectification turn projection migration follows the V9 activity migration", () => {
  assert.ok(
    "20260813030000_rectification_turn_message_projection.sql" >
      "20260813020000_rectification_birth_context_activity.sql",
  );
  assert.match(turnMessageProjectionMigration, /^-- Preserve both logical messages[\s\S]*\nbegin;[\s\S]*^commit;$/m);
});

test("rectification dossier expands each physical turn into user and assistant messages", () => {
  assert.match(
    turnMessageProjectionMigration,
    /cross join lateral \(\s*values\s*\(1, 'user'::text, t\.user_message\),\s*\(2, 'assistant'::text, t\.assistant_message\)\s*\) as message\(ordinal, role, text\)/,
  );
  assert.match(turnMessageProjectionMigration, /'role', message\.role/);
  assert.match(turnMessageProjectionMigration, /'text', message\.text/);
  assert.match(turnMessageProjectionMigration, /order by t\.created_at, message\.ordinal/);
  assert.match(turnMessageProjectionMigration, /and message\.text is not null/);
  assert.doesNotMatch(
    turnMessageProjectionMigration,
    /coalesce\(t\.user_message, t\.assistant_message\)/,
  );
});

test("rectification turn projection migration stays out of the identity migration tree", () => {
  assert.equal(
    existsSync(turnMessageProjectionMigrationCopy),
    false,
    "business migration must not be copied into frontend/db/migrations (BUG-127/BUG-144)",
  );
});

// ---------------------------------------------------------------------------
// 20260813040000_allow_parallel_rectification_cases.sql
// ---------------------------------------------------------------------------

const parallelCasesMigration = readFileSync(
  new URL(
    "../supabase/migrations/20260813040000_allow_parallel_rectification_cases.sql",
    import.meta.url,
  ),
  "utf8",
);

const parallelCasesMigrationCopy = fileURLToPath(
  new URL(
    "../db/migrations/20260813040000_allow_parallel_rectification_cases.sql",
    import.meta.url,
  ),
);

test("parallel rectification migration follows the turn projection and stays transactional", () => {
  assert.ok(
    "20260813040000_allow_parallel_rectification_cases.sql" >
      "20260813030000_rectification_turn_message_projection.sql",
  );
  assert.match(parallelCasesMigration, /^begin;[\s\S]*^commit;$/m);
});

test("parallel rectification migration removes the single-active-case restriction", () => {
  assert.match(
    parallelCasesMigration,
    /drop index if exists public\.agentic_rectification_cases_one_resumable_per_user/,
  );
  assert.doesNotMatch(parallelCasesMigration, /agentic_rectification_active_case_conflict/);
  assert.doesNotMatch(
    parallelCasesMigration,
    /if p_intent = 'homepage'[\s\S]*?'disposition', 'resumed'/,
  );
});

test("parallel rectification open keeps request idempotency and exact-session recovery", () => {
  assert.match(parallelCasesMigration, /pg_catalog\.pg_advisory_xact_lock\(/);
  assert.match(parallelCasesMigration, /l\.request_id = p_request_id/);
  assert.match(parallelCasesMigration, /if p_intent = 'session' then/);
  assert.match(parallelCasesMigration, /where id = p_session_id and user_id = p_user_id/);
  assert.match(parallelCasesMigration, /insert into public\.chat_sessions/);
  assert.match(parallelCasesMigration, /insert into public\.agentic_rectification_cases/);
  assert.match(parallelCasesMigration, /insert into public\.agentic_rectification_open_ledger/);
});

test("parallel rectification migration stays out of the identity migration tree", () => {
  assert.equal(
    existsSync(parallelCasesMigrationCopy),
    false,
    "business migration must not be copied into frontend/db/migrations (BUG-127/BUG-144)",
  );
});

// ---------------------------------------------------------------------------
// 20260813050000_allow_rectification_candidate_reselection.sql
// ---------------------------------------------------------------------------

const candidateReselectionMigration = readFileSync(
  new URL(
    "../supabase/migrations/20260813050000_allow_rectification_candidate_reselection.sql",
    import.meta.url,
  ),
  "utf8",
);

const candidateReselectionMigrationCopy = fileURLToPath(
  new URL(
    "../db/migrations/20260813050000_allow_rectification_candidate_reselection.sql",
    import.meta.url,
  ),
);

test("candidate reselection migration follows parallel cases and stays transactional", () => {
  assert.ok(
    "20260813050000_allow_rectification_candidate_reselection.sql" >
      "20260813040000_allow_parallel_rectification_cases.sql",
  );
  assert.match(candidateReselectionMigration, /^begin;[\s\S]*^commit;$/m);
  assert.match(
    candidateReselectionMigration,
    /create or replace function public\.accept_agentic_rectification_candidate_for_case\(/,
  );
});

test("candidate reselection preserves replay and safely changes only accepted candidates", () => {
  assert.match(
    candidateReselectionMigration,
    /v_result\.selected_time is not distinct from p_time[\s\S]*v_case\.accepted_time is not distinct from p_time[\s\S]*'idempotent', true/,
  );
  assert.match(
    candidateReselectionMigration,
    /v_case\.status is distinct from 'candidate_accepted'/,
  );
  assert.match(
    candidateReselectionMigration,
    /v_result\.selection_kind is distinct from 'user_accepted'/,
  );
  assert.match(
    candidateReselectionMigration,
    /v_profile\.active_birth_time is distinct from v_result\.selected_time/,
  );
  assert.match(
    candidateReselectionMigration,
    /v_profile\.birth_time_status is distinct from 'accepted'/,
  );
  assert.match(
    candidateReselectionMigration,
    /set selected_time = p_time,[\s\S]*selection_kind = 'user_accepted'/,
  );
  assert.match(
    candidateReselectionMigration,
    /set active_birth_time = p_time,[\s\S]*birth_time = p_time,[\s\S]*birth_time_status = 'accepted'/,
  );
  assert.match(
    candidateReselectionMigration,
    /set status = 'candidate_accepted',[\s\S]*accepted_time = p_time/,
  );
  assert.doesNotMatch(
    candidateReselectionMigration,
    /selected_time is distinct from p_time[\s\S]{0,160}agentic_rectification_candidate_already_selected/,
  );
});

test("candidate reselection keeps terminal and confirmed selections immutable", () => {
  assert.match(
    candidateReselectionMigration,
    /v_case\.status in \('confirmed', 'closed', 'abandoned', 'superseded'\)[\s\S]*agentic_rectification_case_terminal/,
  );
  assert.match(
    candidateReselectionMigration,
    /v_result\.selection_kind is distinct from 'user_accepted'[\s\S]*agentic_rectification_candidate_selection_blocked/,
  );
  assert.match(candidateReselectionMigration, /birth_time_status = 'accepted'/);
  assert.doesNotMatch(
    candidateReselectionMigration,
    /set selected_time = p_time,[\s\S]{0,180}selection_kind = 'engine_confirmed'/,
  );
});

test("candidate reselection migration stays out of the identity migration tree", () => {
  assert.equal(
    existsSync(candidateReselectionMigrationCopy),
    false,
    "business migration must not be copied into frontend/db/migrations (BUG-127/BUG-144)",
  );
});

// ---------------------------------------------------------------------------
// 20260813060000_rectification_turn_regeneration.sql
// ---------------------------------------------------------------------------

const turnRegenerationMigration = readFileSync(
  new URL(
    "../supabase/migrations/20260813060000_rectification_turn_regeneration.sql",
    import.meta.url,
  ),
  "utf8",
);
const turnRegenerationMigrationCopy = fileURLToPath(
  new URL(
    "../db/migrations/20260813060000_rectification_turn_regeneration.sql",
    import.meta.url,
  ),
);

test("turn regeneration migration is forward-only, transactional and business-tree only", () => {
  assert.ok(
    "20260813060000_rectification_turn_regeneration.sql"
      > "20260813050000_allow_rectification_candidate_reselection.sql",
  );
  assert.match(turnRegenerationMigration, /^begin;[\s\S]*^commit;$/m);
  assert.equal(
    existsSync(turnRegenerationMigrationCopy),
    false,
    "business migration must not be copied into frontend/db/migrations (BUG-127/BUG-144)",
  );
});

test("turn regeneration is ownership-bound, latest-only and request-id idempotent", () => {
  assert.match(turnRegenerationMigration, /where id = p_case_id and user_id = p_user_id/);
  assert.match(turnRegenerationMigration, /if v_case\.session_id <> p_session_id/);
  assert.match(turnRegenerationMigration, /where request_id = p_request_id/);
  assert.match(turnRegenerationMigration, /agentic_rectification_regeneration_request_conflict/);
  assert.match(turnRegenerationMigration, /pg_advisory_xact_lock/);
  assert.match(turnRegenerationMigration, /v_case\.status in \('confirmed', 'closed', 'abandoned', 'superseded'\)/);
  assert.match(turnRegenerationMigration, /where id = p_turn_id and case_id = p_case_id/);
  assert.match(turnRegenerationMigration, /v_turn\.status <> 'completed'/);
  assert.match(turnRegenerationMigration, /order by created_at desc, id desc[\s\S]*limit 1/);
  assert.match(turnRegenerationMigration, /v_latest_turn_id is distinct from p_turn_id/);
});

test("turn regeneration updates only the existing Assistant text and timestamp", () => {
  const update = turnRegenerationMigration.slice(
    turnRegenerationMigration.indexOf("update public.agentic_rectification_turns"),
    turnRegenerationMigration.indexOf("insert into public.agentic_rectification_turn_regenerations"),
  );
  const setClause = update.slice(update.indexOf("set "), update.indexOf("where "));
  assert.match(update, /set assistant_message = v_message,[\s\S]*updated_at = pg_catalog\.now\(\)/);
  assert.doesNotMatch(setClause, /user_message|status\s*=|case_id\s*=|session_id\s*=/);
  assert.doesNotMatch(
    turnRegenerationMigration,
    /update public\.(?:agentic_rectification_evidence|agentic_rectification_results|agentic_rectification_cases|profiles)/,
  );
  assert.match(turnRegenerationMigration, /create or replace function public\.get_agentic_rectification_turn_regeneration/);
  assert.match(turnRegenerationMigration, /create or replace function public\.regenerate_agentic_rectification_turn/);
});