import Database from "better-sqlite3";
import { drizzle, type BetterSQLite3Database } from "drizzle-orm/better-sqlite3";
import { lstatSync, readdirSync, readFileSync } from "node:fs";
import { chmodSync, existsSync } from "node:fs";
import path from "node:path";
import type { AppConfig } from "../config.js";
import * as schema from "./schema.js";

export type DatabaseContext = {
  sqlite: Database.Database;
  db: BetterSQLite3Database<typeof schema>;
};

function migrate(sqlite: Database.Database, migrationsDir: string): void {
  sqlite.exec("CREATE TABLE IF NOT EXISTS schema_migrations (name TEXT PRIMARY KEY, applied_at INTEGER NOT NULL) STRICT");
  const applied = new Set(
    (sqlite.prepare("SELECT name FROM schema_migrations").all() as Array<{ name: string }>).map((row) => row.name),
  );
  const files = readdirSync(migrationsDir).filter((name) => name.endsWith(".sql")).sort();
  const apply = sqlite.transaction((name: string, sqlText: string) => {
    sqlite.exec(sqlText);
    sqlite.prepare("INSERT INTO schema_migrations(name, applied_at) VALUES (?, ?)").run(name, Date.now());
  });
  for (const name of files) {
    if (!applied.has(name)) apply(name, readFileSync(path.join(migrationsDir, name), "utf8"));
  }
}

export function openDatabase(config: AppConfig): DatabaseContext {
  const sqlite = new Database(config.dbPath);
  sqlite.pragma("foreign_keys = ON");
  sqlite.pragma("journal_mode = WAL");
  sqlite.pragma("synchronous = FULL");
  sqlite.pragma("busy_timeout = 5000");
  sqlite.pragma("temp_store = MEMORY");
  migrate(sqlite, config.migrationsDir);
  // SQLite creates the database and journal files after the initial directory
  // preparation. Enforce private permissions again after opening so a broad
  // process umask can never expose financial data to other local users.
  for (const filePath of [config.dbPath, `${config.dbPath}-wal`, `${config.dbPath}-shm`]) {
    if (existsSync(filePath)) chmodSync(filePath, 0o600);
  }
  const foreignKeys = sqlite.pragma("foreign_keys", { simple: true });
  if (foreignKeys !== 1) throw new Error("SQLite 外键未启用");
  return { sqlite, db: drizzle(sqlite, { schema }) };
}

/**
 * Open an existing database without creating directories, changing journal
 * mode, running migrations, or changing file permissions. This is used by
 * administrative status checks that must be side-effect free.
 */
export function openDatabaseReadOnly(config: AppConfig): DatabaseContext {
  const info = lstatSync(config.dbPath);
  if (!info.isFile() || info.isSymbolicLink()) throw new Error(`数据库文件不是安全的普通文件：${config.dbPath}`);
  const sqlite = new Database(config.dbPath, { readonly: true, fileMustExist: true });
  sqlite.pragma("foreign_keys = ON");
  sqlite.pragma("busy_timeout = 5000");
  sqlite.pragma("temp_store = MEMORY");
  sqlite.pragma("query_only = ON");
  const foreignKeys = sqlite.pragma("foreign_keys", { simple: true });
  if (foreignKeys !== 1) {
    sqlite.close();
    throw new Error("SQLite 外键未启用");
  }
  return { sqlite, db: drizzle(sqlite, { schema }) };
}