import Database from "better-sqlite3";
import path from "node:path";
import fs from "node:fs";

// process.cwd() es /app dentro del contenedor (WORKDIR /app en el Dockerfile),
// donde Coolify monta el volumen persistente en /app/data.
function createDb() {
  const dir = path.join(process.cwd(), "data");
  fs.mkdirSync(dir, { recursive: true });
  const db = new Database(path.join(dir, "informe.db"));
  db.pragma("journal_mode = WAL");
  db.exec(`
    CREATE TABLE IF NOT EXISTS metas (
      email TEXT PRIMARY KEY,
      monto REAL NOT NULL,
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS config (
      clave TEXT PRIMARY KEY,
      valor TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS geo_override (
      opportunity_id TEXT PRIMARY KEY,
      lat REAL,
      lon REAL,
      zona TEXT,
      depto TEXT
    );
  `);
  return db;
}

const g = globalThis;
const db = g.__informeDb ?? (g.__informeDb = createDb());
export default db;