/**
 * The database: one SQLite file under FORGE_APP_DATA, so production and
 * every preview each have their own. WAL so readers never wait on the
 * writer (the terminal is a second process on the same file), foreign keys
 * on, and a forward-only list of migrations applied once, in a
 * transaction, when the file is opened.
 *
 *   bun run src/db.ts --check    the schema version and an integrity check
 */

import { Database } from "bun:sqlite";
import { mkdirSync } from "node:fs";
import { join } from "node:path";
import { dataDir } from "./env.ts";

/** Append, never edit: a migration that has run is history. */
export const migrations: string[] = [
  `CREATE TABLE note (
    id INTEGER PRIMARY KEY,
    body TEXT NOT NULL CHECK (length(body) BETWEEN 1 AND 500),
    author_id TEXT NOT NULL,
    author_name TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
  )`,
  `CREATE INDEX note_author ON note (author_id)`,
  // Who tried to change what, and whether they were let. Appended by
  // src/audit.ts, never edited; read by admins at GET /api/audit.
  `CREATE TABLE audit (
    id INTEGER PRIMARY KEY,
    at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    who_id TEXT,
    who_name TEXT,
    role TEXT NOT NULL,
    resource TEXT NOT NULL,
    verb TEXT NOT NULL,
    target TEXT,
    allowed INTEGER NOT NULL CHECK (allowed IN (0, 1)),
    outcome TEXT NOT NULL
  )`,
];

export function openDb(dir = dataDir): Database {
  mkdirSync(dir, { recursive: true });
  const db = new Database(join(dir, "app.db"));
  db.exec("PRAGMA journal_mode = WAL");
  db.exec("PRAGMA foreign_keys = ON");
  db.exec("PRAGMA busy_timeout = 5000");
  migrate(db);
  return db;
}

/** Apply the migrations past the recorded version, all or none. Returns the version. */
export function migrate(db: Database): number {
  db.exec(
    "CREATE TABLE IF NOT EXISTS schema_version (version INTEGER PRIMARY KEY, applied_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')))",
  );
  return db.transaction(() => {
    const current = db
      .query<
        { version: number },
        []
      >("SELECT coalesce(max(version), 0) AS version FROM schema_version")
      .get()!.version;
    migrations.slice(current).forEach((sql, i) => {
      db.exec(sql);
      db.query("INSERT INTO schema_version (version) VALUES (?1)").run(
        current + i + 1,
      );
    });
    return migrations.length;
  })();
}

export function check(db: Database): { version: number; integrity: string } {
  const version = db
    .query<
      { version: number },
      []
    >("SELECT max(version) AS version FROM schema_version")
    .get()!.version;
  const integrity = db
    .query<{ integrity_check: string }, []>("PRAGMA integrity_check")
    .get()!.integrity_check;
  return { version, integrity };
}

if (import.meta.main) {
  const db = openDb();
  const { version, integrity } = check(db);
  console.log(`schema version ${version}`);
  if (process.argv.includes("--check")) {
    console.log(`integrity ${integrity}`);
    process.exit(integrity === "ok" ? 0 : 1);
  }
}