import assert from "node:assert/strict";
import { randomUUID } from "node:crypto";
import { readFile } from "node:fs/promises";
import { PGlite } from "../.data/sql-verification/node_modules/@electric-sql/pglite/dist/index.js";

const db = new PGlite();
try {
  await db.exec(`create role anon; create role authenticated; create role service_role;
    create table public.profiles(id uuid primary key, role text not null default 'contributor', account_status text not null default 'active');`);
  for (const name of ["20261029105929_moderator_applications.sql", "20261029111310_fix_moderator_review.sql"])
    await db.exec(await readFile(`supabase/migrations/${name}`, "utf8"));
  const player = randomUUID(), other = randomUUID();
  await db.query("insert into public.profiles(id,username) values ($1,'player_one'),($2,'player_two')", [player, other]);
  await assert.rejects(db.query("update public.profiles set username='player_one' where id=$1", [other]), /unique/i);
  await db.query("update public.profiles set username='renamed' where id=$1", [player]);
  await db.exec("insert into public.moderator_bot_admin(id,discord_user_id) values(true,'admin-id')");
  const create = async (id, status) => (await db.query("insert into public.moderator_applications(platform,platform_user_id,requested_username,profile_id,confirmation_hash,status) values('discord',$1,'renamed',$2,$3,$4) returning id", [id, player, randomUUID(), status])).rows[0].id;
  const review = (id, decision, reviewer) => db.query("select public.review_moderator_application($1,$2,$3)", [id, decision, reviewer]);
  const pending = await create("one", "pending");
  await assert.rejects(review(pending, "approved", "wrong-id"), /Admin permission required/);
  await review(pending, "declined", "admin-id");
  assert.equal((await db.query("select role from public.profiles where id=$1", [player])).rows[0].role, "contributor");
  const unconfirmed = await create("two", "confirming");
  await assert.rejects(review(unconfirmed, "approved", "admin-id"), /no longer pending/);
  const approved = await create("three", "pending");
  await review(approved, "approved", "admin-id");
  assert.equal((await db.query("select role from public.profiles where id=$1", [player])).rows[0].role, "moderator");
  await assert.rejects(review(approved, "approved", "admin-id"), /no longer pending/);
  console.log("Moderator application SQL checks passed.");
} finally { await db.close(); }