// @vitest-environment node

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

const migrationPath = path.join(
  process.cwd(),
  "supabase",
  "migrations",
  "202607270004_telegram_sponsor_payments.sql",
);
const numberedSelectionMigrationPath = path.join(
  process.cwd(),
  "supabase",
  "migrations",
  "202607270005_telegram_numbered_selection.sql",
);

async function migration() {
  return readFile(migrationPath, "utf8");
}

describe("Telegram sponsor-payment migration", () => {
  it("keeps authorization and pending sessions private", async () => {
    const sql = await migration();
    expect(sql).toContain("create table private.telegram_payment_operators");
    expect(sql).toContain("telegram_user_id bigint unique");
    expect(sql).toContain("create table private.telegram_payment_sessions");
    expect(sql).toContain("interval '10 minutes'");
    expect(sql).toContain("candidate_sponsor_ids uuid[]");
    expect(sql).toContain("candidate_sponsorship_ids uuid[]");
    expect(sql).toContain("status in ('pending', 'completed', 'cancelled', 'expired')");
  });

  it("deduplicates Telegram deliveries and secures callback ownership", async () => {
    const sql = await migration();
    expect(sql).toContain("create table private.telegram_payment_processed_updates");
    expect(sql).toContain("callback_query_id text unique");
    expect(sql).toContain("on conflict do nothing");
    expect(sql).toContain("v_session.telegram_user_id <> p_telegram_user_id");
    expect(sql).toContain("v_session.chat_id <> p_chat_id");
    expect(sql).toContain("v_session.status = 'completed'");
  });

  it("confirms the complete sponsor payment and audit in one RPC transaction", async () => {
    const sql = await migration();
    expect(sql).toContain("create or replace function public.handle_telegram_sponsor_payment");
    expect(sql).toContain("received_amount = expected_amount_snapshot");
    expect(sql).toContain("'sponsor_payment.confirmed'");
    expect(sql).toContain("'source', 'telegram_n8n'");
    expect(sql).toContain("'telegram_user_id', p_telegram_user_id");
    expect(sql).toContain("'workflow_execution_id', p_workflow_execution_id");
    expect(sql).toContain("v_payment.payment_status <> 'paid'");
    expect(sql).toContain("log_telegram_payment_delivery_failure");
    expect(sql).toContain("'telegram_reply_failed'");
  });

  it("uses the canonical monthly uniqueness rule and never writes orphan transfers", async () => {
    const sql = await migration();
    expect(sql).toContain("on conflict (sponsorship_id, payment_year, payment_month) do nothing");
    expect(sql).not.toContain("insert into public.orphan_transfers");
    expect(sql).not.toContain("update public.orphan_transfers");
    expect(sql).not.toContain("delete from public.orphan_transfers");
  });

  it("allows only the Supabase service role to invoke the public RPCs", async () => {
    const sql = await migration();
    expect(sql).toContain("from public, anon, authenticated");
    expect(sql).toContain("grant execute on function public.handle_telegram_sponsor_payment");
    expect(sql).toContain("to service_role");
  });

  it("normalizes and ranks Arabic sponsor names deterministically", async () => {
    const sql = await migration();
    expect(sql).toContain("create or replace function private.normalize_arabic_search");
    expect(sql).toContain("'أإآٱىةؤئ'");
    expect(sql).toContain("extensions.similarity");
    expect(sql).toContain("profile.role = 'donor'");
    expect(sql).toContain("profile.is_active = true");
  });

  it("returns nearest donors and supports secure numbered replies", async () => {
    const sql = await readFile(numberedSelectionMigrationPath, "utf8");
    expect(sql).toContain("handle_telegram_sponsor_payment_v2");
    expect(sql).toContain("limit 6");
    expect(sql).toContain("v_text ~ '^[1-6]$'");
    expect(sql).toContain("profile.role = 'donor'");
    expect(sql).toContain("'kp:' || v_session.token");
    expect(sql).toContain("to service_role");
  });
});
