lifegame/server/db.js

/**
 * SQLite persistence. games/players hold identity + metadata; tokens covers
 * both unclaimed room invite links (kind='invite', player_id NULL) and
 * per-player reconnect secrets (kind='session'). config/state are stored as
 * JSON text — Phase 1 never needs to query into them.
 */

import Database from 'better-sqlite3';

const SCHEMA = `
CREATE TABLE IF NOT EXISTS games (
  id         TEXT PRIMARY KEY,
  code       TEXT NOT NULL UNIQUE,
  board_id   TEXT NOT NULL,
  config     TEXT NOT NULL,
  state      TEXT NOT NULL,
  status     TEXT NOT NULL DEFAULT 'lobby' CHECK (status IN ('lobby','active','finished')),
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

CREATE TABLE IF NOT EXISTS players (
  id           TEXT PRIMARY KEY,
  game_id      TEXT NOT NULL REFERENCES games(id) ON DELETE CASCADE,
  name         TEXT NOT NULL,
  seat         INTEGER NOT NULL,
  color        TEXT NOT NULL,
  created_at   TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  last_seen_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_players_game_id ON players(game_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_players_game_seat ON players(game_id, seat);

CREATE TABLE IF NOT EXISTS tokens (
  token      TEXT PRIMARY KEY,
  game_id    TEXT NOT NULL REFERENCES games(id) ON DELETE CASCADE,
  player_id  TEXT REFERENCES players(id) ON DELETE CASCADE,
  kind       TEXT NOT NULL CHECK (kind IN ('invite','session')),
  expires_at TEXT,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
CREATE INDEX IF NOT EXISTS idx_tokens_game_id ON tokens(game_id);
CREATE INDEX IF NOT EXISTS idx_tokens_player_id ON tokens(player_id);
`;

let db;

export function initDb(dbPath) {
  db = new Database(dbPath);
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');
  db.exec(SCHEMA);
  return db;
}

function now() {
  return new Date().toISOString();
}

function deserializeGame(row) {
  if (!row) return null;
  return { ...row, config: JSON.parse(row.config), state: JSON.parse(row.state) };
}

export function createGame({ id, code, boardId, config, state }) {
  db.prepare(
    `INSERT INTO games (id, code, board_id, config, state) VALUES (?, ?, ?, ?, ?)`
  ).run(id, code, boardId, JSON.stringify(config), JSON.stringify(state));
  return getGameById(id);
}

export function getGameByCode(code) {
  return deserializeGame(db.prepare(`SELECT * FROM games WHERE code = ?`).get(code));
}

export function getGameById(id) {
  return deserializeGame(db.prepare(`SELECT * FROM games WHERE id = ?`).get(id));
}

export function updateGameState(id, state, status) {
  db.prepare(`UPDATE games SET state = ?, status = ?, updated_at = ? WHERE id = ?`).run(
    JSON.stringify(state),
    status,
    now(),
    id
  );
}

export function createPlayer({ id, gameId, name, seat, color }) {
  db.prepare(
    `INSERT INTO players (id, game_id, name, seat, color) VALUES (?, ?, ?, ?, ?)`
  ).run(id, gameId, name, seat, color);
}

export function getPlayersByGame(gameId) {
  return db.prepare(`SELECT * FROM players WHERE game_id = ? ORDER BY seat`).all(gameId);
}

export function countPlayers(gameId) {
  return db.prepare(`SELECT COUNT(*) AS n FROM players WHERE game_id = ?`).get(gameId).n;
}

export function touchPlayerLastSeen(playerId) {
  db.prepare(`UPDATE players SET last_seen_at = ? WHERE id = ?`).run(now(), playerId);
}

export function createToken({ token, gameId, playerId = null, kind, expiresAt = null }) {
  db.prepare(
    `INSERT INTO tokens (token, game_id, player_id, kind, expires_at) VALUES (?, ?, ?, ?, ?)`
  ).run(token, gameId, playerId, kind, expiresAt);
}

export function getToken(token) {
  return db.prepare(`SELECT * FROM tokens WHERE token = ?`).get(token) ?? null;
}