hexifyer/server/db.ts

import initSqlJs, { type Database } from 'sql.js';
import { readFileSync, writeFileSync, existsSync, mkdirSync } from 'fs';
import { resolve, dirname } from 'path';
import { fileURLToPath } from 'url';

const __dirname = dirname(fileURLToPath(import.meta.url));
const DB_PATH = process.env.DB_PATH || resolve(__dirname, '..', 'data', 'hexifyer.db');

let db: Database;

interface Migration {
  name: string;
  up: () => void;
}

const MIGRATIONS: Migration[] = [
  {
    name: '001-initial-schema',
    up() {
      db.run(`
        CREATE TABLE IF NOT EXISTS hex_maps (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          image_width INTEGER NOT NULL DEFAULT 8000,
          image_height INTEGER NOT NULL DEFAULT 12000,
          tile_url TEXT NOT NULL DEFAULT '/tiles/{z}/{x}/{y}.jpg',
          min_zoom INTEGER NOT NULL DEFAULT 0,
          max_zoom INTEGER NOT NULL DEFAULT 6,
          hex_size REAL NOT NULL DEFAULT 48.0,
          origin_x REAL NOT NULL DEFAULT 0.0,
          origin_y REAL NOT NULL DEFAULT 0.0,
          created_at TEXT DEFAULT (datetime('now')),
          updated_at TEXT DEFAULT (datetime('now'))
        )
      `);

      db.run(`
        CREATE TABLE IF NOT EXISTS hexes (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          map_id INTEGER NOT NULL,
          q INTEGER NOT NULL,
          r INTEGER NOT NULL,
          base_terrain TEXT NOT NULL DEFAULT 'plains',
          updated_at TEXT DEFAULT (datetime('now')),
          FOREIGN KEY (map_id) REFERENCES hex_maps(id) ON DELETE CASCADE,
          UNIQUE(map_id, q, r)
        )
      `);

      db.run(`
        CREATE TABLE IF NOT EXISTS hex_features (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          hex_id INTEGER NOT NULL,
          terrain_id TEXT NOT NULL,
          edge_mask INTEGER NOT NULL,
          FOREIGN KEY (hex_id) REFERENCES hexes(id) ON DELETE CASCADE
        )
      `);

      db.run('CREATE INDEX IF NOT EXISTS idx_hexes_map_coord ON hexes(map_id, q, r)');
      db.run('CREATE INDEX IF NOT EXISTS idx_hex_features_hex ON hex_features(hex_id)');
    },
  },
];

function runMigrations(): void {
  db.run(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      name TEXT PRIMARY KEY,
      applied_at TEXT DEFAULT (datetime('now'))
    )
  `);

  const applied = new Set<string>();
  const rows = db.exec('SELECT name FROM schema_migrations');
  if (rows.length > 0) {
    for (const row of rows[0].values) applied.add(row[0] as string);
  }

  for (const m of MIGRATIONS) {
    if (applied.has(m.name)) continue;
    console.log(`[db] Running migration: ${m.name}`);
    m.up();
    db.run('INSERT INTO schema_migrations (name) VALUES (?)', [m.name]);
  }
}

export async function initDb(): Promise<Database> {
  const SQL = await initSqlJs();

  if (existsSync(DB_PATH)) {
    const buf = readFileSync(DB_PATH);
    db = new SQL.Database(buf);
    console.log(`[db] Loaded existing database from ${DB_PATH}`);
  } else {
    db = new SQL.Database();
    console.log('[db] Created new database');
  }

  runMigrations();
  saveDb();
  return db;
}

export function saveDb(): void {
  mkdirSync(dirname(DB_PATH), { recursive: true });
  const data = db.export();
  writeFileSync(DB_PATH, Buffer.from(data));
}

export function getDb(): Database {
  return db;
}