예제 소스

db.ts

/** Server-only SQLite (Node built-in). MariaGO 패턴. */

import { DatabaseSync } from "node:sqlite";
import fs from "node:fs";
import path from "node:path";
import { randomBytes } from "node:crypto";
import type { GameAuth, GameListItem, GamePatch, GameRow, MoveRec } from "@/lib/gameTypes";
import { DEFAULT_SETTINGS } from "@/lib/types";
import { Game } from "@/lib/game";

export type { GameAuth, GameListItem, GamePatch, GameRow, MoveRec } from "@/lib/gameTypes";

const DATA_DIR = path.join(process.cwd(), "data");
const DB_PATH = path.join(DATA_DIR, "janggy_games.sqlite");

let db: DatabaseSync | null = null;

function newAccessKey(): string {
  return randomBytes(18).toString("base64url");
}

function getDb(): DatabaseSync {
  if (db) return db;
  fs.mkdirSync(DATA_DIR, { recursive: true });
  db = new DatabaseSync(DB_PATH);
  db.exec(`
    CREATE TABLE IF NOT EXISTS games (
      code TEXT PRIMARY KEY,
      guest_id TEXT NOT NULL,
      access_key TEXT,
      cho_formation TEXT NOT NULL DEFAULT 'inner',
      han_formation TEXT NOT NULL DEFAULT 'inner',
      mode TEXT NOT NULL DEFAULT 'human_cho',
      ai_depth INTEGER NOT NULL DEFAULT 3,
      bikjang INTEGER NOT NULL DEFAULT 1,
      snapshot TEXT NOT NULL,
      move_no INTEGER NOT NULL DEFAULT 0,
      epoch INTEGER NOT NULL,
      started INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'playing',
      end_text TEXT,
      moves TEXT NOT NULL DEFAULT '[]',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_games_guest_updated
      ON games(guest_id, updated_at DESC);
  `);
  const cols = db.prepare("PRAGMA table_info(games)").all() as { name: string }[];
  if (!cols.some((c) => c.name === "ai_depth_han")) {
    db.exec("ALTER TABLE games ADD COLUMN ai_depth_han INTEGER NOT NULL DEFAULT 3");
  }
  return db;
}

const CODE_ALPHABET = "ABCDEFGHJKLMNPQRSTUVWXYZ23456789";

export function newGameCode(): string {
  const d = getDb();
  for (let attempt = 0; attempt < 32; attempt++) {
    let code = "";
    for (let i = 0; i < 6; i++) {
      code += CODE_ALPHABET[Math.floor(Math.random() * CODE_ALPHABET.length)]!;
    }
    const hit = d.prepare("SELECT 1 AS x FROM games WHERE code = ?").get(code);
    if (!hit) return code;
  }
  return `J${Date.now().toString(36).slice(-5).toUpperCase()}`;
}

function ensureAccessKey(code: string, existing: string | null | undefined): string {
  if (existing) return existing;
  const key = newAccessKey();
  getDb().prepare(`UPDATE games SET access_key = ? WHERE code = ?`).run(key, code);
  return key;
}

function emptySnapshot(): string {
  const g = new Game();
  g.newGame(DEFAULT_SETTINGS.choFormation, DEFAULT_SETTINGS.hanFormation);
  return JSON.stringify(g.snapshot());
}

function rowToGame(row: Record<string, unknown>): GameRow {
  const code = String(row.code);
  const accessKey = ensureAccessKey(
    code,
    row.access_key != null ? String(row.access_key) : null,
  );
  return {
    code,
    guest_id: String(row.guest_id),
    access_key: accessKey,
    cho_formation: String(row.cho_formation ?? "inner"),
    han_formation: String(row.han_formation ?? "inner"),
    mode: String(row.mode ?? "human_cho"),
    ai_depth: Number(row.ai_depth ?? 3),
    ai_depth_han: Number(row.ai_depth_han ?? 3),
    bikjang: Boolean(row.bikjang ?? 1),
    snapshot: String(row.snapshot ?? emptySnapshot()),
    move_no: Number(row.move_no),
    epoch: Number(row.epoch),
    started: Boolean(row.started),
    status: row.status === "ended" ? "ended" : "playing",
    end_text: row.end_text != null ? String(row.end_text) : null,
    moves: JSON.parse(String(row.moves || "[]")) as MoveRec[],
    created_at: String(row.created_at),
    updated_at: String(row.updated_at),
  };
}

export type CreateGameOpts = {
  cho_formation?: string;
  han_formation?: string;
  mode?: string;
  ai_depth?: number;
  ai_depth_han?: number;
  bikjang?: boolean;
  snapshot?: string;
};

export function createGame(guestId: string, opts: CreateGameOpts = {}): GameRow {
  const d = getDb();
  const code = newGameCode();
  const accessKey = newAccessKey();
  const now = new Date().toISOString();
  const epoch = Date.now() & 0xffffffff;
  const snapshot = opts.snapshot ?? emptySnapshot();
  d.prepare(
    `INSERT INTO games (
      code, guest_id, access_key, cho_formation, han_formation, mode, ai_depth, ai_depth_han,
      bikjang, snapshot, move_no, epoch, started, status, moves, created_at, updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, 0, 'playing', '[]', ?, ?)`,
  ).run(
    code,
    guestId,
    accessKey,
    opts.cho_formation ?? "inner",
    opts.han_formation ?? "inner",
    opts.mode ?? "human_cho",
    opts.ai_depth ?? 3,
    opts.ai_depth_han ?? 3,
    opts.bikjang === false ? 0 : 1,
    snapshot,
    epoch,
    now,
    now,
  );
  return getGameRaw(code)!;
}

function getGameRaw(code: string): GameRow | null {
  const d = getDb();
  const row = d.prepare("SELECT * FROM games WHERE code = ?").get(code.toUpperCase());
  if (!row) return null;
  return rowToGame(row as Record<string, unknown>);
}

export function authorizeGame(game: GameRow, auth: GameAuth): boolean {
  const guest = auth.guestId?.trim();
  const key = auth.accessKey?.trim();
  if (guest && guest === game.guest_id) return true;
  if (key && key === game.access_key) return true;
  return false;
}

export function getGameAuthorized(
  code: string,
  auth: GameAuth,
): { game: GameRow } | { error: "not_found" | "forbidden" } {
  const game = getGameRaw(code);
  if (!game) return { error: "not_found" };
  if (!authorizeGame(game, auth)) return { error: "forbidden" };
  return { game };
}

export function getLatestPlayingForGuest(guestId: string): GameRow | null {
  const d = getDb();
  const row = d
    .prepare(
      `SELECT * FROM games
       WHERE guest_id = ? AND status = 'playing' AND (started = 1 OR move_no > 0)
       ORDER BY updated_at DESC LIMIT 1`,
    )
    .get(guestId);
  if (!row) return null;
  return rowToGame(row as Record<string, unknown>);
}

export function listGamesForGuest(guestId: string, limit = 80): GameListItem[] {
  const d = getDb();
  const rows = d
    .prepare(
      `SELECT code, access_key, move_no, started, status, end_text, mode, created_at, updated_at
       FROM games
       WHERE guest_id = ? AND (started = 1 OR move_no > 0)
       ORDER BY updated_at DESC
       LIMIT ?`,
    )
    .all(guestId, limit) as Record<string, unknown>[];

  return rows.map((row) => {
    const code = String(row.code);
    const accessKey = ensureAccessKey(code, row.access_key != null ? String(row.access_key) : null);
    const endText = row.end_text != null ? String(row.end_text) : null;
    return {
      code,
      access_key: accessKey,
      move_no: Number(row.move_no),
      started: Boolean(row.started),
      status: row.status === "ended" ? ("ended" as const) : ("playing" as const),
      end_text: endText,
      summary: endText ? endText.split("\n")[0] : null,
      mode: String(row.mode ?? "human_cho"),
      created_at: String(row.created_at),
      updated_at: String(row.updated_at),
    };
  });
}

export function getActiveDbFileName(): string {
  return path.basename(DB_PATH);
}

export function updateGameAuthorized(
  code: string,
  auth: GameAuth,
  patch: GamePatch,
): { game: GameRow } | { error: "not_found" | "forbidden" } {
  const authed = getGameAuthorized(code, auth);
  if ("error" in authed) return authed;
  const d = getDb();
  const now = new Date().toISOString();
  const r = d
    .prepare(
      `UPDATE games SET
        cho_formation = ?, han_formation = ?, mode = ?, ai_depth = ?,
        ai_depth_han = COALESCE(?, ai_depth_han), bikjang = ?,
        snapshot = ?, move_no = ?, epoch = ?, started = ?, status = ?, end_text = ?,
        moves = ?, updated_at = ?
       WHERE code = ?`,
    )
    .run(
      patch.cho_formation,
      patch.han_formation,
      patch.mode,
      patch.ai_depth,
      patch.ai_depth_han ?? null,
      patch.bikjang ? 1 : 0,
      patch.snapshot,
      patch.move_no,
      patch.epoch,
      patch.started ? 1 : 0,
      patch.status,
      patch.end_text ?? null,
      JSON.stringify(patch.moves),
      now,
      code.toUpperCase(),
    );
  if (r.changes === 0) return { error: "not_found" };
  const game = getGameRaw(code);
  if (!game) return { error: "not_found" };
  return { game };
}