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

const migrationPath = path.join(
  process.cwd(),
  "supabase",
  "migrations",
  "202607260007_historical_monthly_reporting.sql"
);
const sql = fs.readFileSync(migrationPath, "utf8").toLowerCase();
const fixMigrationPath = path.join(
  process.cwd(),
  "supabase",
  "migrations",
  "202607260008_fix_historical_snapshot_rpc.sql"
);
const fixSql = fs.readFileSync(fixMigrationPath, "utf8").toLowerCase();

describe("historical monthly reporting migration", () => {
  it("adds detached monthly orphan and sponsorship snapshots", () => {
    expect(sql).toContain("create table public.reporting_monthly_periods");
    expect(sql).toContain("create table public.reporting_orphan_monthly_snapshots");
    expect(sql).toContain("create table public.reporting_sponsorship_monthly_snapshots");
    expect(sql).toContain("monthly_need_snapshot");
    expect(sql).toContain("monthly_amount_snapshot");

    const snapshotTables = sql.slice(
      sql.indexOf("create table public.reporting_monthly_periods"),
      sql.indexOf("create or replace function private.protect_closed_reporting_snapshot")
    );
    expect(snapshotTables).not.toContain("references public.orphans");
    expect(snapshotTables).not.toContain("references public.profiles");
    expect(snapshotTables).not.toContain("references public.sponsorships");
  });

  it("uses start and end dates and freezes captured past months", () => {
    expect(sql).toContain("sponsorship.start_date <= month_end");
    expect(sql).toContain("sponsorship.end_date >= month_start");
    expect(sql).toContain("closed reporting snapshots are immutable");
    expect(sql).toContain("reporting_periods_closed_immutable");
    expect(sql).toContain("reporting_orphan_snapshots_closed_immutable");
    expect(sql).toContain("reporting_sponsorship_snapshots_closed_immutable");
  });

  it("backfills safely and keeps payments and transfers in their source ledgers", () => {
    expect(sql).toContain("perform private.capture_reporting_month");
    expect(sql).toContain("from public.sponsor_monthly_payments payment");
    expect(sql).toContain("from public.orphan_transfers transfer");
    expect(sql).toContain("sum(payment.received_amount)");
    expect(sql).toContain("sum(transfer.amount)");
    expect(sql).not.toContain("delete from public.sponsor_monthly_payments");
    expect(sql).not.toContain("delete from public.orphan_transfers");
  });

  it("exposes only an admin-safe read model and service-role capture functions", () => {
    expect(sql).toContain("create or replace view public.reporting_monthly_history");
    expect(sql).toContain("with (security_invoker = true)");
    expect(sql).toContain("enable row level security");
    expect(sql).toContain("using (private.is_admin())");
    expect(sql).toContain("grant select on public.reporting_monthly_history to service_role");
    expect(sql).toContain("grant execute on function public.ensure_reporting_year(smallint)");
    expect(sql).toContain("to service_role");
  });

  it("runs in one transaction and reloads the API schema", () => {
    expect(sql.trimStart().startsWith("begin;")).toBe(true);
    expect(sql).toContain("notify pgrst, 'reload schema'");
    expect(sql.trimEnd().endsWith("commit;")).toBe(true);
  });
});

describe("historical reporting RPC compatibility fix", () => {
  it("casts the integer loop counter to the snapshot function signature", () => {
    expect(fixSql).toContain("month_number::smallint");
    expect(fixSql).toContain("'automatic'::text");
    expect(fixSql).toContain("create or replace function public.ensure_reporting_year");
  });

  it("preserves service-role-only execution and transactional application", () => {
    expect(fixSql.trimStart().startsWith("begin;")).toBe(true);
    expect(fixSql).toContain("revoke all on function public.ensure_reporting_year(smallint)");
    expect(fixSql).toContain("grant execute on function public.ensure_reporting_year(smallint)");
    expect(fixSql).toContain("to service_role");
    expect(fixSql.trimEnd().endsWith("commit;")).toBe(true);
  });
});
