import { readFileSync } from "node:fs";
import { fileURLToPath } from "node:url";

import { describe, expect, it } from "vitest";

const schemaPaths = [
  "../database/migrations/0001_initial_schema.sql",
  "../supabase/schema.sql"
];

const lifecycleMigrationPath = "../database/migrations/0002_generation_lifecycle_fencing.sql";

function readSchema(relativePath: string): string {
  return readFileSync(fileURLToPath(new URL(relativePath, import.meta.url)), "utf8").replace(/\r\n/g, "\n");
}

describe.each(schemaPaths)("wallet SQL contract: %s", (schemaPath) => {
  const sql = readSchema(schemaPath);

  it("scopes wallet idempotency to an organization", () => {
    expect(sql).not.toMatch(/idempotency_key\s+text\s+not\s+null\s+unique/i);
    expect(sql).toMatch(/create unique index if not exists billing_ledger_organization_idempotency_idx\s+on billing_ledger\s*\(organization_id, idempotency_key\)/i);
    expect(sql).toMatch(/drop constraint %I/);
    expect(sql).toMatch(/drop index %I\.%I/);
    expect(sql).toMatch(/hashtextextended\(jsonb_build_array\(p_organization_id, p_idempotency_key\)::text, 0\)/i);
    expect(sql).toMatch(/from billing_ledger\s+where organization_id = p_organization_id\s+and idempotency_key = p_idempotency_key/i);
  });

  it("rejects reuse of an idempotency key with a different payload", () => {
    for (const comparison of [
      "v_existing.account_id is distinct from v_account_id",
      "v_existing.job_id is distinct from p_job_id",
      "v_existing.kind is distinct from p_kind",
      "v_existing.delta_fen is distinct from p_delta_fen",
      "v_existing.currency is distinct from p_currency"
    ]) {
      expect(sql.toLowerCase()).toContain(comparison);
    }
    expect(sql.toLowerCase()).not.toContain("v_existing.description is distinct from p_description");
    expect(sql.toLowerCase()).not.toContain("v_existing.metadata is distinct from coalesce(p_metadata");
    expect(sql).toMatch(/errcode = 'P0001'[\s\S]*message = 'BILLING_IDEMPOTENCY_PAYLOAD_MISMATCH'/);
  });

  it("fails safely instead of deleting duplicate usage events", () => {
    expect(sql).not.toMatch(/delete\s+from\s+usage_events/i);
    expect(sql).toMatch(/group by job_id\s+having count\(\*\) > 1/i);
    expect(sql).toMatch(/errcode = '23505'[\s\S]*USAGE_EVENTS_DUPLICATE_JOB_ID/);
    expect(sql.indexOf("USAGE_EVENTS_DUPLICATE_JOB_ID")).toBeLessThan(sql.indexOf("create unique index if not exists usage_events_job_id_idx"));
  });
});

it("keeps the Supabase compatibility snapshot at the latest migrated schema", () => {
	const baseline = readSchema(schemaPaths[0]);
	const lifecycle = readSchema(lifecycleMigrationPath);
	const snapshot = readSchema(schemaPaths[1]);

	for (const fragment of [
		"provider_dispatch_started_at timestamptz",
		"dispatch_ready_at timestamptz",
		"finalized_at timestamptz",
		"generation_jobs_finalize_claim_idx",
		"where dispatch_ready_at is not null",
		"and finalized_at is null"
	]) {
		expect(lifecycle.toLowerCase()).toContain(fragment);
		expect(snapshot.toLowerCase()).toContain(fragment);
	}
	for (const fragment of [
		"create table if not exists assets",
		"create table if not exists billing_ledger",
		"billing_post_wallet_entry"
	]) {
		expect(baseline.toLowerCase()).toContain(fragment);
		expect(snapshot.toLowerCase()).toContain(fragment);
	}
	expect(snapshot).toContain("Compatibility snapshot for existing Supabase deployments");
});