import { mkdirSync } from 'node:fs';
import path from 'node:path';
import Database from 'better-sqlite3';
import { migrate } from './migrate.js';
import type { MetaSnapshot } from './meta.js';

export type SqliteDatabase = Database.Database;

/**
 * The database handle plus a memoised metadata snapshot.
 *
 * Reading table/column metadata happens on nearly every request, so it is
 * cached and invalidated explicitly by the schema operations that change it.
 * `metaVersion` lets callers cheaply detect that their snapshot went stale.
 */
export interface Db {
  sqlite: SqliteDatabase;
  metaVersion: number;
  /** Populated lazily by `loadMeta`; cleared by `invalidateMeta`. */
  metaCache: MetaSnapshot | null;
  /**
   * The `metaVersion` the compiled `v_*` views were last built against.
   *
   * Starts behind `metaVersion` so the first reader builds them. Kept here
   * rather than in `engine/sql-views` so that anything which invalidates the
   * catalogue makes the views stale for free — see `ensureSqlViews`.
   */
  sqlViewsVersion: number;
}

export function invalidateMeta(db: Db): void {
  db.metaCache = null;
  db.metaVersion++;
}

export interface OpenOptions {
  /** `:memory:` for tests. */
  file: string;
  readonly?: boolean;
  verbose?: (message?: unknown, ...rest: unknown[]) => void;
}

/**
 * Open a SQLite file with Wellformed's standard settings.
 *
 * Shared by the control database and every workspace database, so the two
 * cannot drift apart on durability or locking behaviour.
 */
export function openSqlite({ file, verbose }: OpenOptions): SqliteDatabase {
  if (file !== ':memory:') {
    mkdirSync(path.dirname(file), { recursive: true });
  }

  const sqlite = new Database(file, verbose ? { verbose } : {});

  // WAL keeps readers from blocking the single writer. Not available in-memory.
  if (file !== ':memory:') sqlite.pragma('journal_mode = WAL');
  sqlite.pragma('foreign_keys = ON');
  sqlite.pragma('busy_timeout = 5000');
  sqlite.pragma('synchronous = NORMAL');
  // A runaway formula must not be able to allocate the process to death.
  sqlite.pragma('cache_size = -20000');

  return sqlite;
}

/** Open one workspace's database and bring its schema up to date. */
export function openDatabase(options: OpenOptions): Db {
  const db: Db = { sqlite: openSqlite(options), metaVersion: 0, metaCache: null, sqlViewsVersion: -1 };
  migrate(db);
  return db;
}

export function closeDatabase(db: Db): void {
  db.sqlite.close();
}

/** Run `fn` inside a transaction, rolling back on any throw. */
export function transact<T>(db: Db, fn: () => T): T {
  const run = db.sqlite.transaction(fn);
  return run() as T;
}