예제 소스

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, GamePatch, GameRow, MoveRec } from "@/lib/gameTypes";
import { DEFAULT_SIZE, emptyBoard } from "@/lib/board";

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

const DATA_DIR = path.join(process.cwd(), "data");
const DB_PATH = path.join(DATA_DIR, "omook_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,
      size INTEGER NOT NULL DEFAULT 15,
      board TEXT NOT NULL,
      to_play INTEGER NOT NULL,
      last_r INTEGER,
      last_c INTEGER,
      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);
  `);
  return db;
}

const CODE_ALPHABET = "********";

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 `O${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 rowToGame(row: Record<string, unknown>): GameRow {
  const code = String(row.code);
  const lastR = row.last_r as number | null;
  const lastC = row.last_c as number | null;
  const accessKey = ensureAccessKey(
    code,
    row.access_key != null ? String(row.access_key) : null,
  );
  return {
    code,
    guest_id: String(row.guest_id),
    access_key: accessKey,
    size: Number(row.size) || DEFAULT_SIZE,
    board: JSON.parse(String(row.board)) as number[],
    to_play: Number(row.to_play),
    last_move: lastR != null && lastC != null ? { r: lastR, c: lastC } : null,
    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 function createGame(guestId: string, size: number = DEFAULT_SIZE): GameRow {
  const d = getDb();
  const code = newGameCode();
  const accessKey = newAccessKey();
  const now = new Date().toISOString();
  const board = emptyBoard(size);
  const epoch = Date.now() & 0xffffffff;
  d.prepare(
    `INSERT INTO games (
      code, guest_id, access_key, size, board, to_play, move_no,
      epoch, started, status, moves, created_at, updated_at
    ) VALUES (?, ?, ?, ?, ?, 1, 0, ?, 0, 'playing', '[]', ?, ?)`,
  ).run(code, guestId, accessKey, size, JSON.stringify(board), 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
       ORDER BY updated_at DESC LIMIT 1`,
    )
    .get(guestId);
  if (!row) return null;
  return rowToGame(row as Record<string, unknown>);
}

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
        size = ?, board = ?, to_play = ?,
        last_r = ?, last_c = ?,
        move_no = ?, epoch = ?, started = ?, status = ?, end_text = ?,
        moves = ?, updated_at = ?
       WHERE code = ?`,
    )
    .run(
      patch.size,
      JSON.stringify(patch.board),
      patch.to_play,
      patch.last_move?.r ?? null,
      patch.last_move?.c ?? null,
      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 };
}