예제 소스

db.ts

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

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,
  GameWinner,
  MoveRec,
} from "@/lib/gameTypes";
import {
  applyRankOutcome,
  promotionTarget,
  rankLabel,
  type MatchOutcome,
  type PlayerStats,
} from "@/lib/ranking";

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

const DATA_DIR = path.join(process.cwd(), "data");
const LEGACY_DB = path.join(DATA_DIR, "mariago_games.sqlite");
const DB_NAME_RE = /^mariago_\d{8}_\d{6}\.sqlite$/;

let db: DatabaseSync | null = null;
let dbPathResolved: string | null = null;

function pad2(n: number): string {
  return String(n).padStart(2, "0");
}

/** 로컬 시각 기준 YYYYMMDD_HHMMSS */
export function formatDbStamp(d = new Date()): string {
  return (
    `${d.getFullYear()}${pad2(d.getMonth() + 1)}${pad2(d.getDate())}_` +
    `${pad2(d.getHours())}${pad2(d.getMinutes())}${pad2(d.getSeconds())}`
  );
}

function resolveDbPath(): string {
  if (dbPathResolved) return dbPathResolved;
  fs.mkdirSync(DATA_DIR, { recursive: true });

  if (fs.existsSync(LEGACY_DB)) {
    const st = fs.statSync(LEGACY_DB);
    let dest = path.join(DATA_DIR, `mariago_${formatDbStamp(st.mtime)}.sqlite`);
    let n = 0;
    while (fs.existsSync(dest)) {
      n += 1;
      dest = path.join(DATA_DIR, `mariago_${formatDbStamp(st.mtime)}_${n}.sqlite`);
    }
    fs.renameSync(LEGACY_DB, dest);
    dbPathResolved = dest;
    return dest;
  }

  const dated = fs
    .readdirSync(DATA_DIR)
    .filter((f) => DB_NAME_RE.test(f))
    .sort()
    .reverse();
  if (dated.length > 0) {
    dbPathResolved = path.join(DATA_DIR, dated[0]);
    return dbPathResolved;
  }

  dbPathResolved = path.join(DATA_DIR, `mariago_${formatDbStamp()}.sqlite`);
  return dbPathResolved;
}

/** 현재 사용 중인 SQLite 파일명 (경로 제외) */
export function getActiveDbFileName(): string {
  return path.basename(resolveDbPath());
}

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

function getDb(): DatabaseSync {
  if (db) return db;
  const dbPath = resolveDbPath();
  db = new DatabaseSync(dbPath);
  db.exec(`
    CREATE TABLE IF NOT EXISTS games (
      code TEXT PRIMARY KEY,
      guest_id TEXT NOT NULL,
      access_key TEXT,
      board TEXT NOT NULL,
      to_play INTEGER NOT NULL,
      ko_r INTEGER,
      ko_c INTEGER,
      last_r INTEGER,
      last_c INTEGER,
      move_no INTEGER NOT NULL DEFAULT 0,
      cap_b INTEGER NOT NULL DEFAULT 0,
      cap_w INTEGER NOT NULL DEFAULT 0,
      pass_streak INTEGER NOT NULL DEFAULT 0,
      epoch INTEGER NOT NULL,
      started INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'playing',
      end_text TEXT,
      winner TEXT,
      result_applied INTEGER NOT NULL DEFAULT 0,
      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);
    CREATE TABLE IF NOT EXISTS players (
      client_id TEXT PRIMARY KEY,
      rank_level INTEGER NOT NULL DEFAULT 0,
      rank_points INTEGER NOT NULL DEFAULT 0,
      wins INTEGER NOT NULL DEFAULT 0,
      losses INTEGER NOT NULL DEFAULT 0,
      draws INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
  `);
  try {
    db.exec(`ALTER TABLE games ADD COLUMN access_key TEXT`);
  } catch {
    /* already exists */
  }
  try {
    db.exec(`ALTER TABLE games ADD COLUMN winner TEXT`);
  } catch {
    /* already exists */
  }
  try {
    db.exec(`ALTER TABLE games ADD COLUMN result_applied INTEGER NOT NULL DEFAULT 0`);
  } catch {
    /* already exists */
  }
  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 `G${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 koR = row.ko_r as number | null;
  const koC = row.ko_c as number | null;
  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,
    board: JSON.parse(String(row.board)) as number[],
    to_play: Number(row.to_play),
    ko: koR != null && koC != null ? { r: koR, c: koC } : null,
    last_move: lastR != null && lastC != null ? { r: lastR, c: lastC } : null,
    move_no: Number(row.move_no),
    cap_b: Number(row.cap_b),
    cap_w: Number(row.cap_w),
    pass_streak: Number(row.pass_streak),
    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,
    winner:
      row.winner === "black" || row.winner === "white" || row.winner === "draw"
        ? row.winner
        : 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): GameRow {
  const d = getDb();
  const code = newGameCode();
  const accessKey = newAccessKey();
  const now = new Date().toISOString();
  const board = Array(361).fill(0);
  const epoch = Date.now() & 0xffffffff;
  d.prepare(
    `INSERT INTO games (
      code, guest_id, access_key, board, to_play, move_no, cap_b, cap_w, pass_streak,
      epoch, started, status, moves, created_at, updated_at
    ) VALUES (?, ?, ?, ?, 1, 0, 0, 0, 0, ?, 0, 'playing', '[]', ?, ?)`,
  ).run(code, guestId, accessKey, JSON.stringify(board), epoch, now, now);
  ensurePlayer(guestId, now);
  return getGameRaw(code)!;
}

function ensurePlayer(clientId: string, now = new Date().toISOString()): void {
  getDb()
    .prepare(
      `INSERT OR IGNORE INTO players (
        client_id, rank_level, rank_points, wins, losses, draws, created_at, updated_at
      ) VALUES (?, 0, 0, 0, 0, 0, ?, ?)`,
    )
    .run(clientId, now, now);
}

function rowToPlayer(row: Record<string, unknown>): PlayerStats {
  const rankLevel = Number(row.rank_level);
  const wins = Number(row.wins);
  const losses = Number(row.losses);
  const draws = Number(row.draws);
  return {
    client_id: String(row.client_id),
    rank_level: rankLevel,
    rank_label: rankLabel(rankLevel),
    rank_points: Number(row.rank_points),
    promotion_target: promotionTarget(rankLevel),
    wins,
    losses,
    draws,
    total_games: wins + losses + draws,
    created_at: String(row.created_at),
    updated_at: String(row.updated_at),
  };
}

export function getPlayerStats(clientId: string): PlayerStats {
  const id = clientId.trim();
  const now = new Date().toISOString();
  ensurePlayer(id, now);
  const row = getDb().prepare(`SELECT * FROM players WHERE client_id = ?`).get(id);
  return rowToPlayer(row as Record<string, unknown>);
}

function outcomeForHumanBlack(winner: GameWinner): MatchOutcome {
  if (winner === "draw") return "draw";
  return winner === "black" ? "win" : "loss";
}

function applyGameResult(clientId: string, winner: GameWinner, now: string): PlayerStats {
  ensurePlayer(clientId, now);
  const current = getPlayerStats(clientId);
  const outcome = outcomeForHumanBlack(winner);
  const progress = applyRankOutcome(current.rank_level, current.rank_points, outcome);
  getDb()
    .prepare(
      `UPDATE players SET
        rank_level = ?, rank_points = ?,
        wins = wins + ?, losses = losses + ?, draws = draws + ?,
        updated_at = ?
       WHERE client_id = ?`,
    )
    .run(
      progress.rankLevel,
      progress.rankPoints,
      outcome === "win" ? 1 : 0,
      outcome === "loss" ? 1 : 0,
      outcome === "draw" ? 1 : 0,
      now,
      clientId,
    );
  return getPlayerStats(clientId);
}

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

/** 인증된 경우에만 반환. 실패 시 null + reason */
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 listGamesForGuest(guestId: string, limit = 80): GameListItem[] {
  const d = getDb();
  const rows = d
    .prepare(
      `SELECT code, access_key, move_no, started, status, end_text, 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,
      created_at: String(row.created_at),
      updated_at: String(row.updated_at),
    };
  });
}

export function updateGameAuthorized(
  code: string,
  auth: GameAuth,
  patch: GamePatch,
): { game: GameRow; player: PlayerStats } | { error: "not_found" | "forbidden" } {
  const authed = getGameAuthorized(code, auth);
  if ("error" in authed) return authed;
  const d = getDb();
  const now = new Date().toISOString();
  const winner =
    patch.winner === "black" || patch.winner === "white" || patch.winner === "draw"
      ? patch.winner
      : null;
  d.exec("BEGIN IMMEDIATE");
  try {
    const before = d
      .prepare(`SELECT guest_id, result_applied FROM games WHERE code = ?`)
      .get(code.toUpperCase()) as Record<string, unknown> | undefined;
    if (!before) {
      d.exec("ROLLBACK");
      return { error: "not_found" };
    }
    const r = d
      .prepare(
        `UPDATE games SET
          board = ?, to_play = ?,
          ko_r = ?, ko_c = ?,
          last_r = ?, last_c = ?,
          move_no = ?, cap_b = ?, cap_w = ?, pass_streak = ?,
          epoch = ?, started = ?, status = ?, end_text = ?, winner = ?,
          moves = ?, updated_at = ?
         WHERE code = ?`,
      )
      .run(
        JSON.stringify(patch.board),
        patch.to_play,
        patch.ko?.r ?? null,
        patch.ko?.c ?? null,
        patch.last_move?.r ?? null,
        patch.last_move?.c ?? null,
        patch.move_no,
        patch.cap_b,
        patch.cap_w,
        patch.pass_streak,
        patch.epoch,
        patch.started ? 1 : 0,
        patch.status,
        patch.end_text ?? null,
        winner,
        JSON.stringify(patch.moves),
        now,
        code.toUpperCase(),
      );
    if (r.changes === 0) {
      d.exec("ROLLBACK");
      return { error: "not_found" };
    }

    const clientId = String(before.guest_id);
    const shouldApply =
      patch.status === "ended" &&
      patch.started &&
      winner !== null &&
      Number(before.result_applied || 0) === 0;
    let player = getPlayerStats(clientId);
    if (shouldApply) {
      player = applyGameResult(clientId, winner, now);
      d.prepare(`UPDATE games SET result_applied = 1 WHERE code = ?`).run(code.toUpperCase());
    }
    d.exec("COMMIT");
    const game = getGameRaw(code);
    if (!game) return { error: "not_found" };
    return { game, player };
  } catch (error) {
    d.exec("ROLLBACK");
    throw error;
  }
}