// Connexion SQLite unique (node:sqlite), migration idempotente au chargement.
import { DatabaseSync } from "node:sqlite";
import { mkdirSync } from "node:fs";
import { dirname, resolve } from "node:path";
import { SCHEMA } from "./schema.ts";

let _db: DatabaseSync | null = null;

export function db(): DatabaseSync {
  if (_db) return _db;
  const envPath = process.env.DATABASE_PATH || "./data/immbot.db";
  if (envPath === ":memory:") {
    _db = new DatabaseSync(":memory:");
  } else {
    const path = resolve(process.cwd(), envPath);
    mkdirSync(dirname(path), { recursive: true });
    _db = new DatabaseSync(path);
  }
  _db.exec(SCHEMA);
  // Micro-migrations idempotentes (colonnes ajoutées après la v1)
  try {
    _db.exec("ALTER TABLE messages ADD COLUMN tool_trace TEXT NOT NULL DEFAULT '[]'");
  } catch { /* colonne déjà présente */ }
  return _db;
}

// Helpers minimalistes autour des requêtes préparées.
// node:sqlite renvoie des objets à prototype null — on les normalise en objets simples,
// sinon React refuse de les sérialiser des Server Components vers les Client Components.
export function get<T = Record<string, unknown>>(sql: string, ...params: unknown[]): T | undefined {
  const row = db().prepare(sql).get(...(params as never[]));
  return row === undefined ? undefined : ({ ...(row as object) } as T);
}
export function all<T = Record<string, unknown>>(sql: string, ...params: unknown[]): T[] {
  return (db().prepare(sql).all(...(params as never[])) as object[]).map((r) => ({ ...r }) as T);
}
export function run(sql: string, ...params: unknown[]) {
  return db().prepare(sql).run(...(params as never[]));
}
export function transaction<T>(fn: () => T): T {
  const d = db();
  d.exec("BEGIN");
  try {
    const out = fn();
    d.exec("COMMIT");
    return out;
  } catch (e) {
    d.exec("ROLLBACK");
    throw e;
  }
}

// Paramètres globaux (table settings, valeurs JSON).
export function getSetting<T>(key: string, fallback: T): T {
  const row = get<{ value: string }>("SELECT value FROM settings WHERE key = ?", key);
  if (!row) return fallback;
  try {
    return JSON.parse(row.value) as T;
  } catch {
    return fallback;
  }
}
export function setSetting(key: string, value: unknown) {
  run(
    "INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value",
    key,
    JSON.stringify(value)
  );
}