/**
 * Real PGlite RLS isolation through {@link createSqlStoreHandle}.
 *
 * Covers the gap unit tests missed: a live policy, two concurrent identity
 * bags on one shared connection, and row-level isolation. PGlite `query`
 * rejects multi-command batches — the stamp is split statements pinned
 * through `SqlConnection.transaction`.
 */

import { afterAll, beforeAll, describe, expect, test } from "bun:test";
import { connectPglite } from "../../drivers/pglite.ts";
import { connectPostgres } from "../../drivers/postgres.ts";
import { installOkeRlsHelpers } from "../../drivers/pg-rls.ts";
import type { SqlConnection } from "../../drivers/types.ts";
import { createSqlStoreHandle, type SqlStoreHandle } from "./sql-session.ts";

const NOTES_DDL = `CREATE TABLE notes (
  id text PRIMARY KEY,
  owner text NOT NULL,
  body text NOT NULL
)`;

const OWNER_POLICY = `CREATE POLICY owner_select ON notes
  AS PERMISSIVE FOR SELECT TO public
  USING (owner = oke.user())`;

/** File-scoped warmed PGlite. */
let conn: SqlConnection;
/** Stamped as alice. */
let asAlice: SqlStoreHandle;
/** Stamped as bob. */
let asBob: SqlStoreHandle;
/** No stamp — table-owner / superuser bypass. */
let asOwner: SqlStoreHandle;

beforeAll(async () => {
  conn = await connectPglite({ url: "memory://sql-rls-isolation", role: "primary" });
  await conn.exec(NOTES_DDL);
  await conn.exec(`INSERT INTO notes VALUES ('a', 'alice', 'hi'), ('b', 'bob', 'yo')`);
  await conn.exec(`ALTER TABLE notes ENABLE ROW LEVEL SECURITY`);
  await installOkeRlsHelpers((sql) => conn.exec(sql));
  await conn.exec(OWNER_POLICY);
  asAlice = createSqlStoreHandle("sql:app", {
    connection: conn,
    classifications: new Map(),
    routedRole: "primary",
    domainDdl: "off",
    rls: { gate: "member", userId: "alice", scopes: ["member"] },
  });
  asBob = createSqlStoreHandle("sql:app", {
    connection: conn,
    classifications: new Map(),
    routedRole: "primary",
    domainDdl: "off",
    rls: { gate: "member", userId: "bob", scopes: ["member"] },
  });
  asOwner = createSqlStoreHandle("sql:app", {
    connection: conn,
    classifications: new Map(),
    routedRole: "primary",
    domainDdl: "off",
  });
}, 15_000);

afterAll(async () => {
  await conn.close();
});

describe("sql-session RLS isolation (pglite)", () => {
  test("pglite query rejects a multi-command batch (the old prelude)", async () => {
    await expect(
      conn.query("BEGIN; SET LOCAL row_security = on; SELECT set_config('oke.user', $1, true)", [
        "alice",
      ]),
    ).rejects.toThrow(/multiple commands/i);
  });

  test("sequential identities see only their rows", async () => {
    const alice = await asAlice.raw(`SELECT id, owner FROM notes ORDER BY id`);
    const bob = await asBob.raw(`SELECT id, owner FROM notes ORDER BY id`);
    expect(alice).toEqual([{ id: "a", owner: "alice" }]);
    expect(bob).toEqual([{ id: "b", owner: "bob" }]);
  });

  test("concurrent identities do not leak across the shared connection", async () => {
    const [alice, bob, aliceAgain] = await Promise.all([
      asAlice.raw(`SELECT id, owner FROM notes ORDER BY id`),
      asBob.raw(`SELECT id, owner FROM notes ORDER BY id`),
      asAlice.raw(`SELECT id, owner FROM notes ORDER BY id`),
    ]);
    expect(alice).toEqual([{ id: "a", owner: "alice" }]);
    expect(bob).toEqual([{ id: "b", owner: "bob" }]);
    expect(aliceAgain).toEqual([{ id: "a", owner: "alice" }]);
  });

  test("unstamped owner/superuser still sees every row", async () => {
    const rows = await asOwner.raw(`SELECT id, owner FROM notes ORDER BY id`);
    expect(rows).toEqual([
      { id: "a", owner: "alice" },
      { id: "b", owner: "bob" },
    ]);
  });

  test("tenant_id = oke.tenant() isolates concurrent tenants", async () => {
    await conn.exec(`CREATE TABLE bookings (
      id text PRIMARY KEY,
      tenant_id text NOT NULL,
      body text NOT NULL
    )`);
    await conn.exec(`INSERT INTO bookings VALUES ('1', 'acme', 'a'), ('2', 'globex', 'b')`);
    await conn.exec(`ALTER TABLE bookings ENABLE ROW LEVEL SECURITY`);
    await conn.exec(`GRANT SELECT, INSERT, UPDATE, DELETE ON bookings TO oke_app`);
    await conn.exec(`CREATE POLICY tenant_all ON bookings
      AS PERMISSIVE FOR ALL TO public
      USING (tenant_id = oke.tenant())
      WITH CHECK (tenant_id = oke.tenant())`);

    const asAcme = createSqlStoreHandle("sql:app", {
      connection: conn,
      classifications: new Map(),
      routedRole: "primary",
      domainDdl: "off",
      rls: { gate: "member", userId: "u1", scopes: ["member"], tenantId: "acme" },
    });
    const asGlobex = createSqlStoreHandle("sql:app", {
      connection: conn,
      classifications: new Map(),
      routedRole: "primary",
      domainDdl: "off",
      rls: { gate: "member", userId: "u1", scopes: ["member"], tenantId: "globex" },
    });

    const [acme, globex, acmeAgain] = await Promise.all([
      asAcme.raw(`SELECT id, tenant_id FROM bookings ORDER BY id`),
      asGlobex.raw(`SELECT id, tenant_id FROM bookings ORDER BY id`),
      asAcme.raw(`SELECT id, tenant_id FROM bookings ORDER BY id`),
    ]);
    expect(acme).toEqual([{ id: "1", tenant_id: "acme" }]);
    expect(globex).toEqual([{ id: "2", tenant_id: "globex" }]);
    expect(acmeAgain).toEqual([{ id: "1", tenant_id: "acme" }]);
  });
});

const LIVE_PG =
  process.env.OKE_TEST_DOCKER === "1" &&
  (process.env.DATABASE_URL ?? process.env.OKE_STORE_SQL_URL)?.startsWith("postgres");
if (!LIVE_PG) {
  console.log("skip: live Postgres RLS isolation needs OKE_TEST_DOCKER=1 and DATABASE_URL");
}

describe("sql-session RLS isolation (live postgres)", () => {
  const live = LIVE_PG ? test : test.skip;
  const table = "oke_rls_isolation_notes";

  live(
    "split stamp isolates concurrent identities",
    async () => {
      const pg = await connectPostgres({
        url: process.env.DATABASE_URL ?? process.env.OKE_STORE_SQL_URL,
        role: "primary",
      });
      try {
        await pg.exec(`DROP TABLE IF EXISTS ${table}`);
        await pg.exec(
          `CREATE TABLE ${table} (id text PRIMARY KEY, owner text NOT NULL, body text NOT NULL)`,
        );
        await pg.exec(`INSERT INTO ${table} VALUES ('a', 'alice', 'hi'), ('b', 'bob', 'yo')`);
        await pg.exec(`ALTER TABLE ${table} ENABLE ROW LEVEL SECURITY`);
        await installOkeRlsHelpers((sql) => pg.exec(sql));
        await pg.exec(
          `CREATE POLICY owner_select ON ${table} AS PERMISSIVE FOR SELECT TO public USING (owner = oke.user())`,
        );
        const alice = createSqlStoreHandle("sql:app", {
          connection: pg,
          classifications: new Map(),
          routedRole: "primary",
          domainDdl: "off",
          rls: { gate: "member", userId: "alice", scopes: ["member"] },
        });
        const bob = createSqlStoreHandle("sql:app", {
          connection: pg,
          classifications: new Map(),
          routedRole: "primary",
          domainDdl: "off",
          rls: { gate: "member", userId: "bob", scopes: ["member"] },
        });
        const [a, b] = await Promise.all([
          alice.raw(`SELECT id, owner FROM ${table} ORDER BY id`),
          bob.raw(`SELECT id, owner FROM ${table} ORDER BY id`),
        ]);
        expect(a).toEqual([{ id: "a", owner: "alice" }]);
        expect(b).toEqual([{ id: "b", owner: "bob" }]);
      } finally {
        try {
          await pg.exec(`DROP TABLE IF EXISTS ${table}`);
        } catch {
          // Best-effort cleanup.
        }
        await pg.close();
      }
    },
    20_000,
  );
});