import { describe, it, expect, beforeAll, afterAll } from "vitest";
import { tmpdir } from "os";
import { join } from "path";
import { writeFileSync, unlinkSync, existsSync } from "fs";
import Database from "better-sqlite3";
import { WRITE_PATTERN } from "../toolsProvider";
import { sqliteQuery } from "../sqlite";
import { postgresQuery } from "../postgres";

// The database plugin is "read-only by default". These tests pin the two layers
// that enforce that: (1) the WRITE_PATTERN gate at the tool layer, and (2) a
// connection-level read-only guarantee as defense-in-depth — the readonly SQLite
// connection, and (for Postgres) a read-only transaction. They also document
// honestly that the regex ALONE is not sufficient, which is why layer (2) matters.

describe("WRITE_PATTERN — tool-layer write gate", () => {
  it("flags standard write statements (any case / leading space)", () => {
    for (const sql of [
      "INSERT INTO t VALUES (1)", "update t set x=1", "  DELETE FROM t",
      "DROP TABLE t", "CREATE TABLE t(x)", "ALTER TABLE t ADD c int",
      "TRUNCATE t", "replace into t values (1)",
    ]) {
      expect(WRITE_PATTERN.test(sql), sql).toBe(true);
    }
  });

  it("allows genuine read statements through", () => {
    for (const sql of [
      "SELECT * FROM t", "select count(*) from t",
      "WITH x AS (SELECT 1) SELECT * FROM x", "PRAGMA table_info(t)", "EXPLAIN SELECT 1",
    ]) {
      expect(WRITE_PATTERN.test(sql), sql).toBe(false);
    }
  });

  it("does NOT catch writes hidden after a read or comment (regex is start-anchored)", () => {
    // Factual limitation, not endorsed behavior: these are writes the regex misses.
    // The connection-level read-only layer is what blocks them — on SQLite (below)
    // and on Postgres (live test at the end, when a DSN is provided).
    expect(WRITE_PATTERN.test("SELECT 1; DELETE FROM t")).toBe(false);
    expect(WRITE_PATTERN.test("/* c */ DELETE FROM t")).toBe(false);
    expect(WRITE_PATTERN.test("WITH x AS (DELETE FROM t RETURNING *) SELECT * FROM x")).toBe(false);
  });
});

describe("SQLite read path — connection-level defense-in-depth", () => {
  const dbPath = join(tmpdir(), `db-readonly-test-${process.pid}.db`);

  beforeAll(() => {
    const db = new Database(dbPath);
    db.exec("CREATE TABLE t(id INTEGER); INSERT INTO t VALUES (1),(2)");
    db.close();
  });
  afterAll(() => { if (existsSync(dbPath)) unlinkSync(dbPath); });

  it("reads succeed", () => {
    expect(sqliteQuery(dbPath, "SELECT id FROM t ORDER BY id", 100).rowCount).toBe(2);
  });

  it("a plain write routed to the read path is rejected (not executed)", () => {
    // better-sqlite3's .all() refuses statements that return no rows, so a bare
    // DELETE never runs. The safety property that matters: the write does not happen.
    expect(() => sqliteQuery(dbPath, "DELETE FROM t", 100)).toThrow();
    expect(sqliteQuery(dbPath, "SELECT count(*) c FROM t", 100).rows[0][0]).toBe(2);
  });

  it("a RETURNING write is blocked specifically by the readonly connection", () => {
    // .all() accepts this (RETURNING yields rows), so it reaches — and is stopped
    // by — the readonly connection layer itself.
    expect(() => sqliteQuery(dbPath, "DELETE FROM t RETURNING id", 100)).toThrow(/readonly/i);
  });

  it("a regex-bypassing multi-statement write is still blocked", () => {
    // The exact string WRITE_PATTERN misses above — caught here by SQLite itself.
    expect(() => sqliteQuery(dbPath, "SELECT 1; DELETE FROM t", 100)).toThrow(/more than one statement/i);
  });
});

// Live Postgres check. Skipped unless PG_TEST_DSN points at a throwaway database
// (CREATE/DROP rights, no real data). Verifies the connection-level read-only fix
// actually rejects regex-bypassing writes on the read path.
const PG_DSN = process.env.PG_TEST_DSN;
describe.skipIf(!PG_DSN)("Postgres read path — connection-level read-only (live)", () => {
  it("rejects a write that the regex misses", async () => {
    await expect(
      postgresQuery(PG_DSN!, "SELECT 1; DROP TABLE IF EXISTS __pg_ro_probe", 10, /* readOnly */ true),
    ).rejects.toThrow(/read-only transaction/i);
  }, 15_000);

  it("still allows a plain read", async () => {
    const r = await postgresQuery(PG_DSN!, "SELECT 1 AS x", 10, true);
    expect(r.rows[0][0]).toBe(1);
  }, 15_000);
});

import { describe, it, expect, beforeAll, afterAll } from "vitest";
import { tmpdir } from "os";
import { join } from "path";
import { writeFileSync, unlinkSync, existsSync } from "fs";
import Database from "better-sqlite3";
import { WRITE_PATTERN } from "../toolsProvider";
import { sqliteQuery } from "../sqlite";
import { postgresQuery } from "../postgres";

// The database plugin is "read-only by default". These tests pin the two layers
// that enforce that: (1) the WRITE_PATTERN gate at the tool layer, and (2) a
// connection-level read-only guarantee as defense-in-depth — the readonly SQLite
// connection, and (for Postgres) a read-only transaction. They also document
// honestly that the regex ALONE is not sufficient, which is why layer (2) matters.

describe("WRITE_PATTERN — tool-layer write gate", () => {
  it("flags standard write statements (any case / leading space)", () => {
    for (const sql of [
      "INSERT INTO t VALUES (1)", "update t set x=1", "  DELETE FROM t",
      "DROP TABLE t", "CREATE TABLE t(x)", "ALTER TABLE t ADD c int",
      "TRUNCATE t", "replace into t values (1)",
    ]) {
      expect(WRITE_PATTERN.test(sql), sql).toBe(true);
    }
  });

  it("allows genuine read statements through", () => {
    for (const sql of [
      "SELECT * FROM t", "select count(*) from t",
      "WITH x AS (SELECT 1) SELECT * FROM x", "PRAGMA table_info(t)", "EXPLAIN SELECT 1",
    ]) {
      expect(WRITE_PATTERN.test(sql), sql).toBe(false);
    }
  });

  it("does NOT catch writes hidden after a read or comment (regex is start-anchored)", () => {
    // Factual limitation, not endorsed behavior: these are writes the regex misses.
    // The connection-level read-only layer is what blocks them — on SQLite (below)
    // and on Postgres (live test at the end, when a DSN is provided).
    expect(WRITE_PATTERN.test("SELECT 1; DELETE FROM t")).toBe(false);
    expect(WRITE_PATTERN.test("/* c */ DELETE FROM t")).toBe(false);
    expect(WRITE_PATTERN.test("WITH x AS (DELETE FROM t RETURNING *) SELECT * FROM x")).toBe(false);
  });
});

describe("SQLite read path — connection-level defense-in-depth", () => {
  const dbPath = join(tmpdir(), `db-readonly-test-${process.pid}.db`);

  beforeAll(() => {
    const db = new Database(dbPath);
    db.exec("CREATE TABLE t(id INTEGER); INSERT INTO t VALUES (1),(2)");
    db.close();
  });
  afterAll(() => { if (existsSync(dbPath)) unlinkSync(dbPath); });

  it("reads succeed", () => {
    expect(sqliteQuery(dbPath, "SELECT id FROM t ORDER BY id", 100).rowCount).toBe(2);
  });

  it("a plain write routed to the read path is rejected (not executed)", () => {
    // better-sqlite3's .all() refuses statements that return no rows, so a bare
    // DELETE never runs. The safety property that matters: the write does not happen.
    expect(() => sqliteQuery(dbPath, "DELETE FROM t", 100)).toThrow();
    expect(sqliteQuery(dbPath, "SELECT count(*) c FROM t", 100).rows[0][0]).toBe(2);
  });

  it("a RETURNING write is blocked specifically by the readonly connection", () => {
    // .all() accepts this (RETURNING yields rows), so it reaches — and is stopped
    // by — the readonly connection layer itself.
    expect(() => sqliteQuery(dbPath, "DELETE FROM t RETURNING id", 100)).toThrow(/readonly/i);
  });

  it("a regex-bypassing multi-statement write is still blocked", () => {
    // The exact string WRITE_PATTERN misses above — caught here by SQLite itself.
    expect(() => sqliteQuery(dbPath, "SELECT 1; DELETE FROM t", 100)).toThrow(/more than one statement/i);
  });
});

// Live Postgres check. Skipped unless PG_TEST_DSN points at a throwaway database
// (CREATE/DROP rights, no real data). Verifies the connection-level read-only fix
// actually rejects regex-bypassing writes on the read path.
const PG_DSN = process.env.PG_TEST_DSN;
describe.skipIf(!PG_DSN)("Postgres read path — connection-level read-only (live)", () => {
  it("rejects a write that the regex misses", async () => {
    await expect(
      postgresQuery(PG_DSN!, "SELECT 1; DROP TABLE IF EXISTS __pg_ro_probe", 10, /* readOnly */ true),
    ).rejects.toThrow(/read-only transaction/i);
  }, 15_000);

  it("still allows a plain read", async () => {
    const r = await postgresQuery(PG_DSN!, "SELECT 1 AS x", 10, true);
    expect(r.rows[0][0]).toBe(1);
  }, 15_000);
});