import fs from 'node:fs/promises';
import path from 'node:path';
import { drizzle, type SQLJsDatabase } from 'drizzle-orm/sql-js';
import initSqlJs, { type Database as RawSqlJsDatabase } from 'sql.js';
import * as schema from './schema';

// SQLite via sql.js (WASM) — no native compilation, so it builds and runs in the
// Docker image without a toolchain. Trade-off: the DB lives in memory and every write
// must be exported to disk; persist() does that atomically (tmp file + rename).

export interface GameDb {
  db: SQLJsDatabase<typeof schema>;
  raw: RawSqlJsDatabase;
  file: string;
}

// CREATE TABLE statements mirroring lib/db/schema.ts — keep in sync. Game tables land with
// the turn engine; _meta is DB plumbing (not game state): a brand-new sql.js database exports
// as ZERO bytes, so without at least one real table there would be nothing valid to persist
// on the ./db volume from first boot.
const DDL = `
CREATE TABLE IF NOT EXISTS _meta (key TEXT PRIMARY KEY, value TEXT);
`;

// SPACE_WANDERER_DB_PATH override exists so unit tests can point at a temp file instead
// of the real ./db/space-wanderer.db.
export function defaultDbPath(): string {
  return process.env.SPACE_WANDERER_DB_PATH ?? path.join(process.cwd(), 'db', 'space-wanderer.db');
}

async function open(file: string): Promise<GameDb> {
  const SQL = await initSqlJs();
  let bytes: Buffer | undefined;
  try {
    bytes = await fs.readFile(file);
  } catch (err) {
    if ((err as NodeJS.ErrnoException).code !== 'ENOENT') throw err;
  }
  const raw = bytes ? new SQL.Database(new Uint8Array(bytes)) : new SQL.Database();
  raw.exec(DDL); // no-op for tables that already exist in an older file

  const instance: GameDb = { db: drizzle(raw, { schema }), raw, file };

  // First run: stamp when this DB was created and land the file on disk so the ./db volume
  // mount has a valid SQLite file to persist from boot (instrumentation.ts opens at server start).
  if (!bytes) {
    raw.run('INSERT OR IGNORE INTO _meta (key, value) VALUES (?, ?)', ['created_at', new Date().toISOString()]);
    await persist(instance);
  }
  return instance;
}

// Durably save the in-memory DB: export bytes, write to a temp file, rename over
// the target so a crash mid-write never leaves a truncated DB.
export async function persist(instance: GameDb): Promise<void> {
  const bytes = instance.raw.export();
  await fs.mkdir(path.dirname(instance.file), { recursive: true });
  const tmp = `${instance.file}.tmp`;
  await fs.writeFile(tmp, Buffer.from(bytes));
  await fs.rename(tmp, instance.file);
}

// Singleton for the app process; openGameDb() is the uncached variant (tests).
// Lives on globalThis because Next.js can load this module more than once per process —
// instrumentation.ts and route handlers get separate module instances, so a plain `let`
// would give each its own in-memory sql.js DB (both persisting to the same file). One world per process.
const g = globalThis as unknown as { __spaceWandererGameDb?: Promise<GameDb> };

export function getGameDb(): Promise<GameDb> {
  return (g.__spaceWandererGameDb ??= open(defaultDbPath()));
}

export async function openGameDb(file: string): Promise<GameDb> {
  return open(file);
}