SpaceWanderer/lib/db/client.ts

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. _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);

CREATE TABLE IF NOT EXISTS saves (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  seed TEXT NOT NULL,
  turn INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  schema_version INTEGER -- which save-schema version this row was backfilled up to (NULL = v1)
);

CREATE TABLE IF NOT EXISTS solar_systems (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  save_id INTEGER NOT NULL REFERENCES saves(id),
  name TEXT NOT NULL,
  x REAL NOT NULL,
  y REAL NOT NULL,
  discovered INTEGER NOT NULL DEFAULT 0,
  is_current INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS jump_routes (
  from_system INTEGER NOT NULL REFERENCES solar_systems(id),
  to_system INTEGER NOT NULL REFERENCES solar_systems(id),
  PRIMARY KEY (from_system, to_system)
);

CREATE TABLE IF NOT EXISTS planets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  system_id INTEGER NOT NULL REFERENCES solar_systems(id),
  parent_id INTEGER, -- NULL for a planet, set to the parent planet's id for a moon
  name TEXT NOT NULL,
  type TEXT NOT NULL,
  hazard INTEGER NOT NULL DEFAULT 0,
  explored INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS inventory_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  save_id INTEGER NOT NULL REFERENCES saves(id),
  kind TEXT NOT NULL, -- 'resource' | 'part'
  name TEXT NOT NULL,
  qty INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS crew (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  save_id INTEGER NOT NULL REFERENCES saves(id),
  name TEXT NOT NULL,
  role TEXT NOT NULL,
  trait_good TEXT NOT NULL,
  trait_bad TEXT NOT NULL,
  origin TEXT NOT NULL -- 'STARTING CREW' or '<planet>, <system>' for members found while exploring
);

CREATE TABLE IF NOT EXISTS ship_systems (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  save_id INTEGER NOT NULL REFERENCES saves(id),
  type TEXT NOT NULL,
  level INTEGER NOT NULL DEFAULT 0, -- 0 = not built yet ('built' is derived: level > 0)
  capacity INTEGER NOT NULL DEFAULT 0,
  UNIQUE (save_id, type)
);

CREATE TABLE IF NOT EXISTS tech (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  save_id INTEGER NOT NULL REFERENCES saves(id),
  slug TEXT NOT NULL, -- stable key, e.g. WEAPONS_SYSTEMS
  name TEXT NOT NULL, -- display name, e.g. 'WEAPONS SYSTEMS'
  cost_kind TEXT NOT NULL, -- 'resource' | 'part'
  cost_qty INTEGER NOT NULL DEFAULT 1,
  prerequisites TEXT NOT NULL DEFAULT '', -- comma-separated tech slugs, '' when none
  effect TEXT NOT NULL, -- effect slug — semantics in EFFECTS (lib/game/tech.ts)
  researched INTEGER NOT NULL DEFAULT 0,
  UNIQUE (save_id, slug)
);

CREATE TABLE IF NOT EXISTS missions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  save_id INTEGER NOT NULL REFERENCES saves(id),
  type TEXT NOT NULL, -- 'DELIVER' | 'EXPLORE' | 'FIND'
  description TEXT NOT NULL, -- human-readable objective, e.g. "Jump to Kepler Drift"
  target_system_id INTEGER, -- DELIVER only — destination system (NULL otherwise)
  condition_kind TEXT, -- FIND only — 'resource' | 'part' (NULL otherwise)
  progress_target INTEGER NOT NULL DEFAULT 1, -- EXPLORE: scans required; 1 for the others
  progress INTEGER NOT NULL DEFAULT 0, -- scans counted since acceptance; 0/1 for DELIVER/FIND
  reward_kind TEXT NOT NULL, -- 'resource' | 'part'
  reward_qty INTEGER NOT NULL DEFAULT 1,
  state TEXT NOT NULL DEFAULT 'AVAILABLE' -- 'AVAILABLE' | 'ACTIVE' | 'DONE'
);

CREATE TABLE IF NOT EXISTS events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  save_id INTEGER NOT NULL REFERENCES saves(id),
  turn INTEGER NOT NULL,
  type TEXT NOT NULL,
  message TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS tasks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  save_id INTEGER NOT NULL REFERENCES saves(id),
  type TEXT NOT NULL, -- 'JUMP' | 'EXPLORE' | 'SHIP_UPGRADE' | 'SHIP_EXPAND' | 'SHIP_BUILD' | 'RESEARCH'
  payload TEXT NOT NULL, -- JSON per type: JUMP {systemId, hops} · SHIP_UPGRADE/EXPAND {systemId} · SHIP_BUILD {type} · RESEARCH {techId} · EXPLORE {planetId}
  lead_crew_id INTEGER NOT NULL REFERENCES crew(id),
  helpers TEXT NOT NULL DEFAULT '[]', -- JSON array of crew ids
  duration INTEGER NOT NULL,
  remaining INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'ACTIVE', -- 'ACTIVE' | 'PENDING' | 'DONE'
  queued_at_turn INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS crew_skills (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  save_id INTEGER NOT NULL REFERENCES saves(id),
  crew_id INTEGER NOT NULL REFERENCES crew(id),
  skill TEXT NOT NULL, -- 'NAVIGATION' | 'SCANNING' | 'ENGINEERING' | 'RESEARCH' (SkillName)
  level INTEGER NOT NULL DEFAULT 0, -- 0–5
  xp INTEGER NOT NULL DEFAULT 0, -- cumulative completions in this skill (Task 13 grants)
  UNIQUE (crew_id, skill)
);
`;

// 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

  // Idempotent evolution for files created by older code: CREATE TABLE IF NOT EXISTS above cannot
  // add columns, so check and add any that are missing. This is what makes DB wipes unnecessary —
  // old saves open fine and get backfilled by ensureSave (see lib/game/save.ts).
  const saveCols = raw.exec('PRAGMA table_info(saves)')[0]?.values.map((v) => v[1]) ?? [];
  if (!saveCols.includes('schema_version')) {
    raw.exec('ALTER TABLE saves ADD COLUMN schema_version INTEGER');
  }

  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);
}