import { DatabaseSync, type SQLInputValue } from "node:sqlite";
import { existsSync, mkdirSync, chmodSync } from "node:fs";
import { dirname, resolve, sep } from "node:path";
export const databasePath = () => {
  const path = resolve(process.env.DATABASE_PATH || "data/institution.sqlite");
  if (path.startsWith(resolve("public") + sep))
    throw new Error("database_must_be_private");
  return path;
};
let connection: DatabaseSync | undefined;
export function openDatabase() {
  if (connection) return connection;
  const path = databasePath();
  if (!existsSync(path)) throw new Error("database_not_migrated");
  connection = new DatabaseSync(path);
  connection.exec(
    "PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000;",
  );
  return connection;
}
export function createDatabase() {
  const path = databasePath();
  mkdirSync(dirname(path), { recursive: true, mode: 0o700 });
  const db = new DatabaseSync(path);
  chmodSync(path, 0o600);
  db.exec(
    "PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000;",
  );
  return db;
}
export class BoundStatement {
  sql: string;
  args: SQLInputValue[] = [];
  constructor(sql: string) {
    this.sql = sql;
  }
  bind(...args: SQLInputValue[]) {
    this.args = args;
    return this;
  }
  async first<T>() {
    return (
      (openDatabase()
        .prepare(this.sql)
        .get(...this.args) as T | undefined) ?? null
    );
  }
  async all<T>() {
    return {
      results: openDatabase()
        .prepare(this.sql)
        .all(...this.args) as T[],
    };
  }
  async run() {
    const result = this.execute();
    return {
      meta: {
        changes: Number(result.changes),
        last_row_id: Number(result.lastInsertRowid),
      },
    };
  }
  execute() {
    return openDatabase()
      .prepare(this.sql)
      .run(...this.args);
  }
}
export const DB = {
  prepare: (sql: string) => new BoundStatement(sql),
  async batch(statements: BoundStatement[]) {
    const db = openDatabase();
    db.exec("BEGIN IMMEDIATE");
    try {
      const results = statements.map((s) => s.execute());
      db.exec("COMMIT");
      return results;
    } catch (error) {
      db.exec("ROLLBACK");
      throw error;
    }
  },
};