import assert from "node:assert/strict";
import { randomUUID } from "node:crypto";
import { readFile, readdir } 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 schema auth;create table auth.users(id uuid primary key);
 create function auth.uid() returns uuid language sql as $$select null::uuid$$;
 create schema storage;create table storage.buckets(id text primary key,name text,public boolean,file_size_limit bigint);`);
  for (const file of (await readdir("supabase/migrations"))
    .filter((f) => f.endsWith(".sql"))
    .sort())
    await db.exec(await readFile(`supabase/migrations/${file}`, "utf8"));
  const owner = randomUUID(),
    mod = randomUUID(),
    admin = randomUUID();
  for (const [id, role] of [
    [owner, "contributor"],
    [mod, "moderator"],
    [admin, "admin"],
  ]) {
    await db.query("insert into auth.users values($1)", [id]);
    await db.query("insert into public.profiles(id,role) values($1,$2)", [
      id,
      role,
    ]);
  }
  const manifest = {
    name: "Discord fixture",
    description: "Notification verification",
    bounds: { minX: 0, minZ: 0, maxX: 64, maxZ: 64 },
    viewpoints: Array.from({ length: 5 }, (_, i) => ({
      id: `p${i}`,
      x: i,
      y: 64,
      z: i,
    })),
  };
  const map = (
    await db.query(
      `insert into public.datasets(owner_id,name,source,bounds,state,panorama_count) values($1,'Discord fixture','plugin',$2,'review',5) returning id`,
      [owner, JSON.stringify(manifest.bounds)],
    )
  ).rows[0].id;
  await db.query(
    "insert into public.dataset_inputs(dataset_id,manifest,validated) values($1,$2,true)",
    [map, JSON.stringify(manifest)],
  );
  assert.equal(
    (
      await db.query(
        "select count(*)::int as n from public.discord_announcements",
      )
    ).rows[0].n,
    0,
  );
  await db.query("select public.publish_dataset($1,$2,'approve',null)", [
    map,
    mod,
  ]);
  assert.equal(
    (
      await db.query(
        "select count(*)::int as n from public.discord_announcements where kind='map' and target_id=$1",
        [map],
      )
    ).rows[0].n,
    1,
  );
  await db.query(
    "update public.datasets set update_state='review' where id=$1",
    [map],
  );
  await db.query("select public.publish_dataset($1,$2,'approve',null)", [
    map,
    mod,
  ]);
  await db.query("select public.publish_dataset($1,$2,'unpublish',null)", [
    map,
    admin,
  ]);
  await db.query("update public.datasets set state='review' where id=$1", [
    map,
  ]);
  await db.query("select public.publish_dataset($1,$2,'approve',null)", [
    map,
    admin,
  ]);
  assert.equal(
    (
      await db.query(
        "select count(*)::int as n from public.discord_announcements",
      )
    ).rows[0].n,
    1,
    "updates and re-publication do not spam new-map announcements",
  );
  const hash = "a".repeat(64),
    friend = "b".repeat(64),
    outsider = "c".repeat(64);
  const create = async () =>
    (
      await db.query(
        "select public.create_map_room($1,'normal','Host fixture',4,$2,$3) as id",
        [map, hash, owner],
      )
    ).rows[0].id;
  const room = await create();
  await db.query(
    "select public.multiplayer_action($1,$2,'join','{\"name\":\"Friend fixture\"}')",
    [room, friend],
  );
  for (const secret of [friend, outsider])
    await assert.rejects(
      db.query("select public.publish_discord_party($1,$2)", [room, secret]),
      /Only the room owner/,
    );
  const event = (
    await db.query("select public.publish_discord_party($1,$2) as id", [
      room,
      hash,
    ])
  ).rows[0].id;
  assert.equal(
    (
      await db.query("select public.publish_discord_party($1,$2) as id", [
        room,
        hash,
      ])
    ).rows[0].id,
    event,
    "repeated host clicks return the same event",
  );
  const claimed = (
    await db.query(
      "select * from public.claim_discord_announcements('first',array['party'],null)",
    )
  ).rows;
  assert.equal(claimed.length, 1);
  assert.equal(claimed[0].attempts, 1);
  assert.equal(
    (
      await db.query(
        "select * from public.claim_discord_announcements('second',array['party'],null)",
      )
    ).rows.length,
    0,
    "active leases prevent double sends",
  );
  await db.query(
    "update public.discord_announcements set lease_until=clock_timestamp()-interval '1 second' where id=$1",
    [event],
  );
  assert.equal(
    (
      await db.query(
        "select * from public.claim_discord_announcements('second',array['party'],null)",
      )
    ).rows[0].attempts,
    2,
    "expired workers recover",
  );
  await db.query(
    "update public.discord_announcements set attempts=8,lease_until=clock_timestamp()-interval '1 second' where id=$1",
    [event],
  );
  assert.equal(
    (
      await db.query(
        "select * from public.claim_discord_announcements('third',array['party'],null)",
      )
    ).rows.length,
    0,
  );
  assert.equal(
    (
      await db.query(
        "select state from public.discord_announcements where id=$1",
        [event],
      )
    ).rows[0].state,
    "failed",
    "exhausted expired leases become actionable failures",
  );
  await assert.rejects(
    db.query("select public.retry_discord_announcements($1)", [mod]),
    /Admin permission/,
  );
  await db.query("select public.retry_discord_announcements($1)", [admin]);
  assert.equal(
    (
      await db.query(
        "select state from public.discord_announcements where id=$1",
        [event],
      )
    ).rows[0].state,
    "pending",
  );
  const full = await create();
  for (let i = 0; i < 3; i++)
    await db.query("select public.multiplayer_action($1,$2,'join',$3)", [
      full,
      String(i + 4).repeat(64),
      JSON.stringify({ name: `Player ${i}` }),
    ]);
  await assert.rejects(
    db.query("select public.publish_discord_party($1,$2)", [full, hash]),
    /room is full/,
  );
  const expired = await create();
  await db.query(
    "update public.multiplayer_rooms set created_at=clock_timestamp()-interval '25 hours' where id=$1",
    [expired],
  );
  await assert.rejects(
    db.query("select public.publish_discord_party($1,$2)", [expired, hash]),
    /Only open lobbies/,
  );
  await db.query(
    "update public.multiplayer_rooms set status='closed' where id=$1",
    [room],
  );
  await assert.rejects(
    db.query("select public.publish_discord_party($1,$2)", [room, hash]),
    /Only open lobbies/,
  );
  await db.query(
    "update public.discord_announcements set state='sent',channel_id='123456789012345678',message_id='223456789012345678' where id=$1",
    [event],
  );
  let cleanup = (
    await db.query(
      "select * from public.claim_discord_cleanup('starting:first',$1)",
      [room],
    )
  ).rows;
  assert.equal(
    cleanup.length,
    1,
    "a send acknowledged after closure is cleaned up",
  );
  assert.ok(cleanup[0].closed_at);
  assert.equal(
    (
      await db.query(
        "select * from public.claim_discord_cleanup('starting:second',$1)",
        [room],
      )
    ).rows.length,
    0,
    "cleanup scheduling has a lease",
  );
  const deadline = (
    await db.query("select public.mark_discord_party_closed($1) as deadline", [
      event,
    ])
  ).rows[0].deadline;
  assert.ok(
    Math.abs(new Date(deadline).getTime() - Date.now() - 300000) < 5000,
    "red notice gets five minutes",
  );
  assert.equal(
    String(
      (
        await db.query(
          "select public.mark_discord_party_closed($1) as deadline",
          [event],
        )
      ).rows[0].deadline,
    ),
    String(deadline),
    "retry never extends deletion deadline",
  );
  await db.query(
    "update public.discord_announcements set cleanup_run_id='run_active',cleanup_lease_until=null where id=$1",
    [event],
  );
  assert.equal(
    (
      await db.query(
        "select * from public.claim_discord_cleanup('starting:third',$1)",
        [room],
      )
    ).rows.length,
    0,
    "durable workflow remains scheduled beyond the lease",
  );
  await db.query(
    "update public.discord_announcements set deleted_at=clock_timestamp(),cleanup_run_id=null where id=$1",
    [event],
  );
  assert.equal(
    (
      await db.query(
        "select * from public.claim_discord_cleanup('starting:fourth',$1)",
        [room],
      )
    ).rows.length,
    0,
    "removed messages stay removed",
  );
  const closedSentRoom = await create();
  const closedSentEvent = (
    await db.query("select public.publish_discord_party($1,$2) as id", [
      closedSentRoom,
      hash,
    ])
  ).rows[0].id;
  await db.query(
    "update public.discord_announcements set state='sent',channel_id='123456789012345678',message_id='223456789012345679' where id=$1",
    [closedSentEvent],
  );
  assert.equal(
    (
      await db.query(
        "select * from public.claim_discord_cleanup('starting:open',$1)",
        [closedSentRoom],
      )
    ).rows.length,
    0,
    "open rooms are untouched",
  );
  await db.query("select public.multiplayer_action($1,$2,'leave','{}')", [
    closedSentRoom,
    hash,
  ]);
  assert.equal(
    (
      await db.query(
        "select * from public.claim_discord_cleanup('starting:leave',$1)",
        [closedSentRoom],
      )
    ).rows.length,
    1,
    "last-player departure queues cleanup",
  );
  const hiddenRoom = await create();
  await db.query("select public.publish_dataset($1,$2,'unpublish',null)", [
    map,
    admin,
  ]);
  await assert.rejects(
    db.query("select public.publish_discord_party($1,$2)", [hiddenRoom, hash]),
    /Only parties on published maps/,
  );
  const imported = (
    await db.query(
      `insert into public.datasets(owner_id,name,source,bounds,state,panorama_count,imported_from_id,imported_owner_name) values($1,'Imported fixture','plugin',$2,'draft',5,$3,'Original owner') returning id`,
      [owner, JSON.stringify(manifest.bounds), map],
    )
  ).rows[0].id;
  await db.query(
    "insert into public.dataset_inputs(dataset_id,manifest,validated) values($1,$2,true)",
    [imported, JSON.stringify(manifest)],
  );
  const privateRoom = (
    await db.query(
      "select public.create_map_room($1,'normal','Host fixture',4,$2,$3) as id",
      [imported, hash, owner],
    )
  ).rows[0].id;
  await assert.rejects(
    db.query("select public.publish_discord_party($1,$2)", [privateRoom, hash]),
    /Only parties on published maps/,
  );
  for (const role of ["anon", "authenticated"]) {
    await db.exec(`set role ${role}`);
    await assert.rejects(
      db.query("select * from public.discord_announcements"),
      /permission denied/,
    );
    for (const query of [
      ["select public.publish_discord_party($1,$2)", [room, hash]],
      [
        "select * from public.claim_discord_announcements('spoof',array['map'],null)",
        [],
      ],
      ["select public.retry_discord_announcements($1)", [admin]],
      ["select * from public.claim_discord_cleanup('spoof',null)", []],
      ["select public.mark_discord_party_closed($1)", [event]],
    ])
      await assert.rejects(db.query(...query), /permission denied/);
    await db.exec("reset role");
  }
  console.log(
    "Discord SQL passed: first-publication events, one post per party, host authorization, private-map isolation, channel-independent queue, leases, retries, expiry, capacity and server-only access.",
  );
} finally {
  await db.close();
}