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

const migration = fs
  .readFileSync(
    path.join(
      process.cwd(),
      "supabase",
      "migrations",
      "202607270003_donor_transfer_total_and_account_deletion.sql"
    ),
    "utf8"
  )
  .toLowerCase();

describe("donor contribution total migration", () => {
  it("sums linked orphan transfers once for the authenticated donor", () => {
    expect(migration).toContain("create or replace function public.get_my_total_contributions()");
    expect(migration).toContain("sum(transfer.amount)");
    expect(migration).toContain("from public.orphan_transfer_sponsors link");
    expect(migration).toContain("link.orphan_transfer_id = transfer.id");
    expect(migration).toContain("link.sponsor_id = (select auth.uid())");
    expect(migration).not.toContain("sponsor_monthly_payments");
    expect(migration).not.toContain("received_amount");
  });

  it("runs with row-level security and is available only to authenticated users", () => {
    expect(migration).toContain("security invoker");
    expect(migration).toContain("revoke all on function public.get_my_total_contributions()");
    expect(migration).toContain("from public, anon");
    expect(migration).toContain("grant execute on function public.get_my_total_contributions()");
    expect(migration).toContain("to authenticated");
  });

  it("is transactional and refreshes the API schema", () => {
    expect(migration.trimStart().startsWith("begin;")).toBe(true);
    expect(migration).toContain("notify pgrst, 'reload schema'");
    expect(migration.trimEnd().endsWith("commit;")).toBe(true);
  });
});
