// @vitest-environment node

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

const initialPath = path.join(process.cwd(), "supabase", "migrations", "202607250001_initial_schema.sql");
const hardeningPath = path.join(process.cwd(), "supabase", "migrations", "202607250002_security_hardening.sql");
const privateWorkflowPath = path.join(process.cwd(), "supabase", "migrations", "202607260001_private_sponsor_workflow.sql");
const authPhonePath = path.join(process.cwd(), "supabase", "migrations", "202607260002_auth_phone_hardening.sql");
const financialRefactorPath = path.join(process.cwd(), "supabase", "migrations", "202607260003_optional_orphans_financial_refactor.sql");
const uploadFoldersPath = path.join(process.cwd(), "supabase", "migrations", "202607260004_orphan_upload_folders.sql");
const sharedTransfersPath = path.join(process.cwd(), "supabase", "migrations", "202607260005_shared_orphan_transfers.sql");
const sponsorFirstTransfersPath = path.join(process.cwd(), "supabase", "migrations", "202607260006_sponsor_first_transfer_selection.sql");

async function migration(filePath: string) {
  return readFile(filePath, "utf8");
}

describe("database security migrations", () => {
  it("keeps sensitive tables under RLS", async () => {
    const sql = `${await migration(initialPath)}\n${await migration(hardeningPath)}`;
    expect(sql).toContain("alter table public.orphans enable row level security");
    expect(sql).toContain("alter table public.file_assets enable row level security");
    expect(sql).toContain("revoke all on public.orphans from anon");
  });

  it("removes public orphan data and the sponsorship-request workflow", async () => {
    const sql = await migration(privateWorkflowPath);
    expect(sql).toContain("drop table if exists public.public_orphan_profiles");
    expect(sql).toContain("drop table if exists public.sponsorship_requests");
    expect(sql).toContain("drop type if exists public.request_status");
    expect(sql).toContain("drop table if exists public.donor_profiles");
  });

  it("requires active links for sponsor records and all protected documents", async () => {
    const sql = `${await migration(hardeningPath)}\n${await migration(privateWorkflowPath)}`;
    expect(sql).toContain("private.donor_linked_to_orphan");
    expect(sql).toContain("create or replace function private.donor_may_view_documents");
    expect(sql).toContain("select private.donor_linked_to_orphan(target_orphan)");
    expect(sql).toContain("donor_id = (select auth.uid())");
  });

  it("creates immutable case numbers and three required document types", async () => {
    const sql = await migration(privateWorkflowPath);
    expect(sql).toContain("orphan_case_number_seq");
    expect(sql).toContain("orphan_case_number_immutable");
    expect(sql).toContain("'orphan_national_id'");
    expect(sql).toContain("'father_death_certificate'");
    expect(sql).toContain("'guardian_national_id'");
    expect(sql).toContain("create_orphan_with_documents");
    expect(sql).toContain("replace_orphan_document");
  });

  it("separates sponsor payments from sponsor-linked orphan transfers", async () => {
    const sql = await migration(financialRefactorPath);
    expect(sql).toContain("create table public.sponsor_monthly_payments");
    expect(sql).toContain("create table public.orphan_transfers");
    expect(sql).toContain("orphan_transfer_id uuid");
    expect(sql).toContain("unique (sponsorship_id, payment_year, payment_month)");
    expect(sql).toContain("Transfer links do not match the selected sponsor obligation");
    expect(sql).toContain("legacy_financial_review");
  });

  it("normalizes Auth phone values and rejects phone-less sponsor profiles", async () => {
    const sql = await migration(authPhonePath);
    expect(sql).toContain("phone is not null");
    expect(sql).toContain("new.raw_user_meta_data ->> 'phone'");
    expect(sql).toContain("normalized_phone");
    expect(sql).toContain("security definer");
  });

  it("allows optional orphan data and keeps destructive RPCs service-only", async () => {
    const sql = await migration(financialRefactorPath);
    expect(sql).toContain("alter column full_name drop not null");
    expect(sql).toContain("alter column monthly_need drop not null");
    expect(sql).toContain("jsonb_array_length(p_documents) > 3");
    expect(sql).toContain("delete_orphan_complete");
    expect(sql).toContain("to service_role");
  });

  it("binds sponsor reads and receipt files to the exact sponsor", async () => {
    const sql = await migration(financialRefactorPath);
    expect(sql).toContain("sponsor_id = (select auth.uid())");
    expect(sql).toContain("transfer.id = orphan_transfer_id");
    expect(sql).toContain("transfer.sponsor_id = (select auth.uid())");
  });

  it("creates orphan folders from stable ids and derives transfer status from receipts", async () => {
    const sql = await migration(uploadFoldersPath);
    expect(sql).toContain("create_orphan_with_documents_v2");
    expect(sql).toContain("p_orphan_id uuid");
    expect(sql).toContain("transfer_status_is_receipt_derived");
    expect(sql).toContain("from public.transfer_receipts");
    expect(sql).toContain("to service_role");
  });

  it("shares one orphan transfer with every linked sponsor without duplicating money", async () => {
    const sql = await migration(sharedTransfersPath);
    expect(sql).toContain("create table public.orphan_transfer_sponsors");
    expect(sql).toContain("primary key (orphan_transfer_id, sponsor_id)");
    expect(sql).toContain("drop column sponsor_monthly_payment_id");
    expect(sql).toContain("where sponsorship.orphan_id = p_orphan_id");
    expect(sql).toContain("and sponsorship.status = 'active'");
    expect(sql).toContain("linked_sponsor_count");
    expect(sql).toContain("link.sponsor_id = (select auth.uid())");
  });

  it("validates sponsor-first transfer edits and refreshes links when the orphan changes", async () => {
    const sql = await migration(sponsorFirstTransfersPath);
    expect(sql).toContain("create or replace function public.create_orphan_transfer_for_sponsor");
    expect(sql).toContain("create or replace function public.update_orphan_transfer");
    expect(sql).toContain("sponsorship.donor_id = p_sponsor_id");
    expect(sql).toContain("sponsorship.orphan_id = p_orphan_id");
    expect(sql).toContain("original_orphan_id is distinct from p_orphan_id");
    expect(sql).toContain("delete from public.orphan_transfer_sponsors");
    expect(sql).toContain("grant execute on function public.update_orphan_transfer");
  });
});
