MineGeo/scripts/test-round-panoramas-sql.mjs

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

// Real game-creation functions, isolated from production sessions and scores.
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,allowed_mime_types text[]);
    create table storage.objects(id uuid primary key,bucket_id text,name text,owner_id text);
    create function storage.foldername(text) returns text[] language sql as $$ select string_to_array($1, '/') $$;`);
  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 = (
    await db.query(
      "insert into auth.users values(gen_random_uuid()) returning id",
    )
  ).rows[0].id;
  await db.query("insert into public.profiles(id,display_name) values($1,'Host')", [owner]);
  const map = (
    await db.query(
      `insert into public.datasets(owner_id,name,source,bounds,state,panorama_count)
    values($1,'Repeated capture fixture','fabric','{"minX":-64,"minZ":-64,"maxX":64,"maxZ":64}','published',17) returning id`,
      [owner],
    )
  ).rows[0].id;
  const points = Array.from({ length: 10 }, (_, i) => ({
    id: `p${i}`,
    x: i - 5,
    y: 65,
    z: i - 5,
  }));
  // Reproduce the live map: unique IDs, but seven captures repeat a position.
  const manifest = {
    viewpoints: [
      ...points,
      ...points.slice(0, 7).map((p, i) => ({ ...p, id: `repeat${i}` })),
    ],
  };
  const revision = (
    await db.query(
      "insert into public.revisions(dataset_id,asset_root,manifest) values($1,'fixture',$2) returning id",
      [map, JSON.stringify(manifest)],
    )
  ).rows[0].id;
  await db.query("update public.datasets set revision_id=$1 where id=$2", [
    revision,
    map,
  ]);
  const pool = async (viewpoints) =>
    (
      await db.query("select public.game_viewpoint_pool($1,'fixture') pool", [
        JSON.stringify({ viewpoints }),
      ])
    ).rows[0].pool;
  assert.equal((await pool(manifest.viewpoints)).length, 10);
  assert.equal(
    (
      await pool([
        { id: "a", x: -0.1, y: 65, z: 0 },
        { id: "b", x: -0.9, y: 65.8, z: 0.8 },
      ])
    ).length,
    1,
  );
  assert.equal(
    (
      await pool([
        { id: "a", x: 0, y: 65, z: 0 },
        { id: "b", x: 4, y: 65, z: 4, assetId: "a" },
      ])
    ).length,
    1,
  );
  assert.equal(
    (
      await pool([
        { id: "a", x: 0, y: 65, z: 0 },
        { id: "b", x: 4, y: 65, z: 4, assetId: "a", assetRoot: owner },
      ])
    ).length,
    2,
  );

  for (let i = 0; i < 30; i++) {
    const solo = (
      await db.query("select public.create_game($1,'normal',$2,$3) id", [
        map,
        owner,
        String(i).padStart(64, "0"),
      ])
    ).rows[0].id;
    const multi = (
      await db.query(
        "select public.create_map_room($1,'hard','Host',2,$2,$3) id",
        [map, String(i).padStart(64, "0"), owner],
      )
    ).rows[0].id;
    for (const [table, column, id] of [
      ["game_rounds", "session_id", solo],
      ["multiplayer_rounds", "room_id", multi],
    ]) {
      const rounds = (
        await db.query(
          `select round_number,viewpoint from public.${table} where ${column}=$1 order by round_number`,
          [id],
        )
      ).rows;
      assert.deepEqual(
        rounds.map((r) => r.round_number),
        [0, 1, 2, 3, 4],
      );
      assert.equal(
        new Set(
          rounds.map((r) =>
            [r.viewpoint.x, r.viewpoint.y, r.viewpoint.z]
              .map(Math.floor)
              .join(","),
          ),
        ).size,
        5,
      );
      assert.equal(
        new Set(
          rounds.map(
            (r) =>
              `${r.viewpoint.assetRoot ?? "fixture"}/${r.viewpoint.assetId ?? r.viewpoint.id}`,
          ),
        ).size,
        5,
      );
    }
  }
  const countBefore = (
    await db.query("select count(*)::int n from public.game_sessions")
  ).rows[0].n;
  const insufficient = (
    await db.query(
      "insert into public.revisions(dataset_id,asset_root,manifest) values($1,'fixture',$2) returning id",
      [
        map,
        JSON.stringify({
          viewpoints: [...points.slice(0, 4), { ...points[0], id: "repeat" }],
        }),
      ],
    )
  ).rows[0].id;
  await db.query("update public.datasets set revision_id=$1 where id=$2", [
    insufficient,
    map,
  ]);
  await assert.rejects(
    db.query("select public.create_game($1,'normal',$2,$3)", [
      map,
      owner,
      "0".repeat(64),
    ]),
    /five distinct/,
  );
  await assert.rejects(
    db.query("select public.create_map_room($1,'normal','Host',2,$2,$3)", [
      map,
      "0".repeat(64),
      owner,
    ]),
    /five distinct/,
  );
  assert.equal(
    (await db.query("select count(*)::int n from public.game_sessions")).rows[0]
      .n,
    countBefore,
  );
  assert.equal(
    (
      await db.query(
        "select has_function_privilege('anon','public.game_viewpoint_pool(jsonb,text)','EXECUTE') allowed",
      )
    ).rows[0].allowed,
    false,
  );
  assert.equal(
    (
      await db.query(
        "select has_function_privilege('service_role','public.game_viewpoint_pool(jsonb,text)','EXECUTE') allowed",
      )
    ).rows[0].allowed,
    true,
  );
  console.log(
    "Round panorama SQL regressions passed: 30 solo games, 30 multiplayer rooms, duplicate positions/media, insufficient locations and permissions.",
  );
} finally {
  await db.close();
}