import { mkdirSync, unlinkSync } from "node:fs";
/**
 * SQLite database singleton for OpenFinClaw plugin.
 * Database path: ~/.openfinclaw/workspace/openfinclaw-plugin.db
 *
 * Uses globalThis to persist the singleton across hot-reloads.
 * When OpenClaw hot-reloads a plugin the module cache is cleared,
 * so a plain module-level `let _db` would be reset to null — creating
 * a second DatabaseSync connection while the old HTTP server still
 * holds a reference to the first one via the old module scope.
 */
import { homedir } from "node:os";
import { join } from "node:path";
import { DatabaseSync } from "node:sqlite";
import { cleanupOldRows, ensureSchema } from "./schema.js";

/** globalThis key — survives module hot-reloads within the same process. */
const GLOBAL_DB_KEY = "__openfinclaw_db__" as const;

/** Resolve the database file path under ~/.openfinclaw/workspace/. */
function resolveDbPath(): string {
  const base = join(homedir(), ".openfinclaw", "workspace");
  mkdirSync(base, { recursive: true });
  return join(base, "openfinclaw-plugin.db");
}

/**
 * Get (or lazily initialise) the SQLite database singleton.
 * Calls ensureSchema on first access to create missing tables.
 * The instance is stored on globalThis so it survives plugin hot-reloads.
 */
export function getDb(): DatabaseSync {
  const existing = (globalThis as Record<string, unknown>)[GLOBAL_DB_KEY] as
    | DatabaseSync
    | undefined;
  if (existing) return existing;

  // node:sqlite is available in Node 22+
  const dbPath = resolveDbPath();
  let db: DatabaseSync;
  try {
    db = new DatabaseSync(dbPath);
    ensureSchema(db);
  } catch (err) {
    // If the file is corrupted ("file is not a database"), delete and retry once.
    try {
      unlinkSync(dbPath);
    } catch {
      // File may already be gone — ignore.
    }
    db = new DatabaseSync(dbPath);
    ensureSchema(db);
  }
  (globalThis as Record<string, unknown>)[GLOBAL_DB_KEY] = db;
  // Best-effort cleanup of old rows on first access each process lifecycle
  cleanupOldRows(db);
  return db;
}

/** Close the database (used in tests / graceful shutdown). */
export function closeDb(): void {
  const db = (globalThis as Record<string, unknown>)[GLOBAL_DB_KEY] as DatabaseSync | undefined;
  if (db) {
    db.close();
    (globalThis as Record<string, unknown>)[GLOBAL_DB_KEY] = undefined;
  }
}