lib/db/src/index.ts

import { drizzle } from "drizzle-orm/sqlite-proxy";
import { DatabaseSync } from "node:sqlite";
import path from "path";
import fs from "fs";
import * as schema from "./schema";

const dbPath = process.env.DB_PATH || path.join(process.cwd(), "raqim.db");

// __dirname is injected by esbuild (bundle lives at artifacts/api-server/dist/)
// ../../../lib/db/drizzle → workspace-root/lib/db/drizzle in both dev and Docker
const migrationsDir =
  process.env.MIGRATIONS_DIR ||
  path.join(__dirname, "../../../lib/db/drizzle");

// ── Open / create the SQLite database ────────────────────────────────────────
export const sqlite = new DatabaseSync(dbPath);
sqlite.exec("PRAGMA journal_mode = WAL");
sqlite.exec("PRAGMA foreign_keys = ON");

// ── Apply pending migrations on startup ───────────────────────────────────────
function applyMigrations() {
  sqlite.exec(`CREATE TABLE IF NOT EXISTS __drizzle_migrations (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    hash TEXT    NOT NULL UNIQUE,
    applied_at INTEGER NOT NULL DEFAULT (unixepoch() * 1000)
  )`);

  if (!fs.existsSync(migrationsDir)) {
    console.warn("[db] No migrations directory found at:", migrationsDir);
    return;
  }

  const sqlFiles = fs
    .readdirSync(migrationsDir)
    .filter((f) => f.endsWith(".sql"))
    .sort();

  for (const file of sqlFiles) {
    const hash = file.replace(".sql", "");
    const already = sqlite
      .prepare("SELECT 1 FROM __drizzle_migrations WHERE hash = ?")
      .get(hash);

    if (already) continue;

    const sql = fs.readFileSync(path.join(migrationsDir, file), "utf-8");
    const statements = sql
      .split("--> statement-breakpoint")
      .map((s) => s.trim())
      .filter(Boolean);

    for (const stmt of statements) {
      sqlite.exec(stmt);
    }

    sqlite
      .prepare("INSERT INTO __drizzle_migrations (hash) VALUES (?)")
      .run(hash);

    console.log(`[db] Applied migration: ${file}`);
  }
}

applyMigrations();

// ── Drizzle ORM wrapping node:sqlite via sqlite-proxy ────────────────────────
export const db = drizzle(
  async (sql, params, method) => {
    try {
      const stmt = sqlite.prepare(sql);
      if (method === "run") {
        stmt.run(...(params as Parameters<typeof stmt.run>));
        return { rows: [] };
      }
      // "get" expects a flat value array for the single row, not an array of arrays
      if (method === "get") {
        const row = stmt.get(...(params as Parameters<typeof stmt.get>)) as
          | Record<string, unknown>
          | undefined;
        return { rows: row ? Object.values(row) : [] };
      }
      const result = stmt.all(...(params as Parameters<typeof stmt.all>)) as Record<
        string,
        unknown
      >[];
      return { rows: result.map((row) => Object.values(row)) };
    } catch (e) {
      const msg = e instanceof Error ? e.message : String(e);
      console.error(`[db] ${method} error: ${msg}\n  sql: ${sql.slice(0, 200)}\n  params: ${JSON.stringify(params)}`);
      throw new Error(`DB error [${method}]: ${msg}`);
    }
  },
  { schema },
);

export * from "./schema";