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

const TABLE = "sdk_runtime_state";
const LEGACY_JOB_STATE_FILE = path.join("/tmp", "job-state.json");
const LEGACY_IMPORT_FLAG_KEY = "_legacyJobStateImported";

let db: Database.Database | null = null;

function dbPath(): string {
  return process.env.SQLITE_DB_PATH || "./local.sqlite3";
}

function ensureDbDirectory(filename: string): void {
  const parentDir = path.dirname(path.resolve(filename));
  try {
    fs.mkdirSync(parentDir, { recursive: true });
  } catch (error) {
    if ((error as NodeJS.ErrnoException).code !== "EEXIST") {
      throw error;
    }
  }
}

function getDb(): Database.Database {
  if (!db) {
    const filename = dbPath();
    ensureDbDirectory(filename);
    const instance = new Database(filename);
    instance.pragma("journal_mode = WAL");
    instance.pragma("busy_timeout = 5000");
    instance.exec(`
      CREATE TABLE IF NOT EXISTS ${TABLE} (
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL,
        updated_at TEXT NOT NULL
      );
    `);
    db = instance;
    try {
      importLegacyJobStateFileOnce();
    } catch (error) {
      instance.close();
      db = null;
      throw error;
    }
  }
  return db;
}

function readRaw(key: string): string | null {
  const row = getDb()
    .prepare(`SELECT value FROM ${TABLE} WHERE key = ?`)
    .get(key) as { value: string } | undefined;
  return row?.value ?? null;
}

function writeRaw(key: string, value: string): void {
  const updatedAt = new Date().toISOString();
  getDb()
    .prepare(
      `INSERT INTO ${TABLE} (key, value, updated_at) VALUES (?, ?, ?)
       ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at`
    )
    .run(key, value, updatedAt);
}

function importLegacyJobStateFileOnce(): void {
  if (readRaw(LEGACY_IMPORT_FLAG_KEY)) {
    return;
  }

  if (fs.existsSync(LEGACY_JOB_STATE_FILE)) {
    const raw = fs.readFileSync(LEGACY_JOB_STATE_FILE, "utf-8");
    const data = JSON.parse(raw) as Record<string, unknown>;

    if (data.companyInfo && !readRaw("companyInfo")) {
      writeRaw("companyInfo", JSON.stringify(data.companyInfo));
    }
    if (data.mmApiToken && !readRaw("mmApiToken")) {
      writeRaw("mmApiToken", JSON.stringify(data.mmApiToken));
    }
    if (
      data.initialLoadComplete !== undefined &&
      !readRaw("initialLoadComplete")
    ) {
      writeRaw(
        "initialLoadComplete",
        JSON.stringify(data.initialLoadComplete)
      );
    }
    if (data.databaseLock && !readRaw("databaseLock")) {
      writeRaw("databaseLock", JSON.stringify(data.databaseLock));
    }
  }

  writeRaw(LEGACY_IMPORT_FLAG_KEY, JSON.stringify(true));
}

/**
 * Reads the stored text for `key`, or null when unset, without parsing it. Throws on a
 * store failure; lets a caller tell a failed read from unparseable content.
 */
export function getRuntimeStateText(key: string): string | null {
  return readRaw(key);
}

/**
 * Reads a JSON value for `key`, or null when unset.
 */
export function getRuntimeStateJson<T>(key: string): T | null {
  const raw = readRaw(key);
  if (raw === null) {
    return null;
  }
  return JSON.parse(raw) as T;
}

/**
 * Upserts a JSON-serializable value for `key` without touching other keys.
 */
export function setRuntimeStateJson(key: string, value: unknown): void {
  writeRaw(key, JSON.stringify(value));
}

/**
 * Runs `fn` inside BEGIN IMMEDIATE so cross-process writers serialize safely.
 */
export function withImmediateTransaction<T>(fn: () => T): T {
  const database = getDb();
  database.prepare("BEGIN IMMEDIATE").run();
  try {
    const result = fn();
    database.prepare("COMMIT").run();
    return result;
  } catch (error) {
    database.prepare("ROLLBACK").run();
    throw error;
  }
}

/** Visible for integration tests that need an isolated database file. */
export function resetRuntimeStateDbForTests(): void {
  if (db) {
    db.close();
    db = null;
  }
}