<?php
declare(strict_types=1);

require_once __DIR__ . '/../vendor/autoload.php';
require_once __DIR__ . '/db.php';
require_once __DIR__ . '/ranking_lib.php';

// ★ここに追加：セッションを必ず最初に開始する
if (session_status() === PHP_SESSION_NONE) {
    // セッションの有効期限や設定が必要ならここで配列を渡せますが、まずはデフォルトで開始します
    session_start();
}

header('Content-Type: application/json; charset=UTF-8');
mb_internal_encoding('UTF-8');

try {
    // db.php 内で定義された PostgreSQL 接続 (DbCompatでラップされたもの) を取得
    // ※ 前回の db.php を使用している前提です
    $pdoRaw = db();
    $db = new DbCompat($pdoRaw);

    $mode = $_GET['mode'] ?? $_POST['mode'] ?? 'ping';

    // メインルーター
    switch ($mode) {
        case 'ping':
            echo json_encode([
                'ok' => true,
                'env' => $_ENV['APP_ENV'] ?? 'unknown',
            ]);
            break;

        case 'name_chain_list':
            handle_name_chain_list($db);
            break;

        case 'trip_chain_rank':
            handle_trip_chain_rank($db);
            break;

        case 'trip_chain_detail':
            handle_trip_chain_detail($db);
            break;

        case 'rating_rank':
            handle_rating_rank($db);
            break;

        // ★ 村詳細
        case 'village_detail':
            handle_village_detail($db);
            break;

        // ★ 最新村一覧
        case 'village_recent':
            handle_village_recent($db);
            break;

        case 'monthly_name_rank':        // 名前別・参戦数
            respondRankFromCache($db, 'games', 'name');
            break;

        case 'monthly_trip_rank':        // トリップ別・参戦数
            respondRankFromCache($db, 'games', 'trip');
            break;

        case 'monthly_name_win_rank':    // 名前別・勝率
            respondRankFromCache($db, 'win_rate', 'name');
            break;

        case 'monthly_trip_win_rank':    // トリップ別・勝率
            respondRankFromCache($db, 'win_rate', 'trip');
            break;

        case 'monthly_name_talk_rank':   // 発言密度（名前）
            respondRankFromCache($db, 'talk', 'name');
            break;

        case 'monthly_name_days_rank':   // 参加日数（名前）
            respondRankFromCache($db, 'days', 'name');
            break;

        case 'monthly_role_win_rank':    // 役職別勝率
            respondRankFromCache($db, 'role_win', null);
            break;

        case 'monthly_haiyaku_win_rank': // 配役別勝率
            respondRankFromCache($db, 'haiyaku_win', null);
            break;

        case 'monthly_village_rank':     // 村名別
            respondRankFromCache($db, 'village', null);
            break;

        case 'monthly_haiyaku_rank':     // 配役別
            respondRankFromCache($db, 'haiyaku', null);
            break;

        case 'monthly_rm_rank':          // RM別
            respondRankFromCache($db, 'rm', null);
            break;

        case 'monthly_tsuiho_rank':      // 追放
            respondRankFromCache($db, 'tsuiho', null);
            break;

        case 'stats_by_class':           // 通常/カオス別統計
            handle_stats_by_class($db);
            break;

        case 'profile_get':
            handle_profile_get($db);
            break;

        case 'months':
            handle_months($db);
            break;

        case 'personal':
            handle_personal($db);
            break;

        // ログイン系
        case 'login':
            handle_login($db);
            break;
        case 'logout':
            handle_logout();
            break;
        case 'me':
            handle_me();
            break;

        // プロフィール更新
        case 'profile_update':
            handle_profile_update($db);
            break;

        // Wiki / コメント
        case 'wiki_save':
            handle_wiki_save($db);
            break;
        case 'comment_add':
            handle_comment_add($db);
            break;

        // 通算ランキング
        case 'room_rank':  // 通算 村名ベース村建て回数
            $_GET['ym'] = 'all';
            respondRankFromCache($db, 'village', null);
            break;

        case 'rm_rank':    // 通算 RMベース村建て回数
            $_GET['ym'] = 'all';
            respondRankFromCache($db, 'rm', null);
            break;

        case 'tsuiho_rank': // 通算 追放ランキング
            $_GET['ym'] = 'all';
            respondRankFromCache($db, 'tsuiho', null);
            break;

        case 'haiyaku_rank': // 通算 配役別ゲーム数
            $_GET['ym'] = 'all';
            respondRankFromCache($db, 'haiyaku', null);
            break;

        // RM向け
        case 'multi_trip':
            handle_multi_trip($db);
            break;

        case 'multi_summary':
            handle_multi_summary($db);
            break;

        case 'bbs':
            if ($_SERVER['REQUEST_METHOD'] === 'POST') {
                handle_bbs_post($db);
            } else {
                handle_bbs_list($db);
            }
            break;

        case 'shared_trips':
            handle_shared_trips($db);
            break;

        case 'trip_identities':
            handle_trip_identities($db);
            break;

        case 'shared_trips_count':
            handle_shared_trips_count($db);
            break;

        case 'rm_summary':
            handle_rm_summary($db);
            break;

                case 'notice':
            // notice.txt を読むだけ（更新はファイル編集でOK）
            $NOTICE_TXT = __DIR__ . '/notice.txt';

            $text = '';
            $updated_at = '';

            if (is_file($NOTICE_TXT)) {
                $raw = @file_get_contents($NOTICE_TXT);
                $text = is_string($raw) ? trim($raw) : '';

                $mtime = @filemtime($NOTICE_TXT);
                $updated_at = $mtime ? date('Y-m-d H:i:s', $mtime) : '';
            }

            echo json_encode([
                'ok' => true,
                'notice' => [
                    'text' => $text,
                    'updated_at' => $updated_at,
                ],
            ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
            break;


        default:
            echo json_encode([
                'ok'    => false,
                'error' => 'Unknown mode',
                'msg'   => 'mode パラメータが不正です。',
            ], JSON_UNESCAPED_UNICODE);
    }

    $db->close();

} catch (Throwable $e) {
    http_response_code(500);
    echo json_encode([
        'ok'    => false,
        'error' => 'Internal error: ' . $e->getMessage(),
    ]);
}

// === ログイン共通ユーティリティ =======================

function current_login(): ?array {
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }
    if (empty($_SESSION['login_trip'])) {
        return null;
    }
    return [
        'trip' => $_SESSION['login_trip'],
        'name' => $_SESSION['login_name'] ?? null,
    ];
}

function require_login(): array {
    $login = current_login();
    if (!$login) {
        echo json_encode([
            'ok'    => false,
            'error' => 'ログインが必要です。',
        ], JSON_UNESCAPED_UNICODE);
        exit;
    }
    return $login;
}

function normalize_trip(string $trip): string {
    $trip = trim($trip);
    $trip = preg_replace('/^[◆★☆#＃\+]+/u', '', $trip);
    return $trip;
}

function normalize_key(string $keyType, string $raw): string {
    $raw = trim($raw);
    if ($keyType === 'trip') {
        return normalize_trip($raw);
    }
    return $raw; // name
}

// ログイン状態管理
function get_login_trip(): ?string {
    if (session_status() === PHP_SESSION_NONE) session_start();
    return $_SESSION['login_trip'] ?? null;
}
function get_login_name(): ?string {
    if (session_status() === PHP_SESSION_NONE) session_start();
    return $_SESSION['login_name'] ?? null;
}
function set_login_user(string $tripNorm, string $name): void {
    if (session_status() === PHP_SESSION_NONE) session_start();
    $_SESSION['login_trip'] = $tripNorm;
    $_SESSION['login_name'] = $name;
}

// ◆付き/記号付きトリップを「中身だけ」にする
function normalize_trip_string(string $trip): string {
    return preg_replace('/^[◆#＃$☆★＊\*]+/u', '', trim($trip));
}

function int_param(array $src, string $key, int $default): int {
    if (!isset($src[$key])) return $default;
    if (!is_numeric($src[$key])) return $default;
    return (int)$src[$key];
}

function clamp_int(int $v, int $min, int $max): int {
    return max($min, min($max, $v));
}

function normalize_recent_paging(): array {
    $limit  = int_param($_GET, 'limit', 10);
    $page   = int_param($_GET, 'page', 0);
    $offset = int_param($_GET, 'offset', -1);

    if ($offset < 0) {
        $offset = $page * $limit;
    }

    $limit  = clamp_int($limit, 1, 50);
    $offset = clamp_int($offset, 0, 10000);

    return [$limit, $offset];
}

/**
 * トリップキーからトリップ文字列（◆なし中身）を生成
 */
function normalize_trip_key_input(string $raw): string {
    $value = trim(str_replace('＃', '#', $raw));
    if ($value === '') return '';
    if (str_starts_with($value, '##')) return substr($value, 1);
    if (str_starts_with($value, '#')) return substr($value, 1);
    $pos = strpos($value, '##');
    if ($pos !== false) return '#' . substr($value, $pos + 2);
    $pos = strpos($value, '#');
    if ($pos !== false) return substr($value, $pos + 1);
    return $value;
}

function calc_trip_from_key(string $key): string {
    $key = normalize_trip_key_input($key);
    if ($key === '') {
        return '';
    }

    if (strlen($key) >= 12) {
        $key_sjis = mb_convert_encoding($key, 'SJIS', 'UTF-8');
        $mark = substr($key_sjis, 0, 1);

        if ($mark === '#' || $mark === '$') {
            if (preg_match('|^#([[:xdigit:]]{16})([./0-9A-Za-z]{0,2})|', $key_sjis, $str)) {
                $trip = substr(crypt(pack('H*', $str[1]), "H."), -12);
            } else {
                $trip = '???';
            }
        } else {
            $trip = substr(base64_encode(sha1($key_sjis, true)), 0, 12);
            $trip = str_replace(['+', '/'], ['.', '_'], $trip);
        }
    } else {
        $salt = preg_replace('/[^.-z]/', '.', substr($key . 'H.', 1, 2));
        $map  = [
            ':' => 'A', ';' => 'B', '<' => 'C', '=' => 'D', '>' => 'E', '?' => 'F', '@' => 'G',
            '[' => 'a', '\\' => 'b', ']' => 'c', '^' => 'd', '_' => 'e', '`' => 'f',
        ];
        $salt = strtr($salt, $map);
        $trip = substr(crypt($key, $salt), -10);
    }

    return '◆' . $trip;
}

/**
 * 名前 + トリップキー から 正規化済みトリップを返す（◆なし）
 */
function expand_trip_from_name_and_key(string $name, string $tripKey): string {
    return calc_trip_from_key($tripKey); // ◆付きのまま返す（既存実装のまま）
}


// =============================
// プロフィール読み込み
// =============================
function load_profile_for_trip(DbCompat $db, string $tripNorm): array {
    $stmt = $db->prepare("
        SELECT self_intro, x_id, x_display, updated_at
        FROM player_profile
        WHERE trip = :t
        LIMIT 1
    ");
    if (!$stmt) {
        json_error('SQL_PREPARE_ERROR_PROFILE: ' . $db->lastErrorMsg(), 500);
    }

    $stmt->bindValue(':t', $tripNorm, SQLITE3_TEXT);
    $res = $stmt->execute();
    if (!$res) {
        json_error('SQL_EXEC_ERROR_PROFILE: ' . $db->lastErrorMsg(), 500);
    }

    $row = $res->fetchArray(SQLITE3_ASSOC);
    $res->finalize();
    $stmt->close();

    if (!$row) {
        return [
            'trip'       => $tripNorm,
            'self_intro' => '',
            'x_id'       => null,
            'x_display'  => null,
            'updated_at' => null,
            'editable'   => false,
        ];
    }
    return [
        'trip'       => $tripNorm,
        'self_intro' => $row['self_intro'] ?? '',
        'x_id'       => $row['x_id'] ?? null,
        'x_display'  => $row['x_display'] ?? null,
        'updated_at' => (int)($row['updated_at'] ?? 0),
        'editable'   => false,
    ];
}


// =============================
// Wiki 読み込み
// =============================
function load_wiki(DbCompat $db, string $keyType, string $keyValue): array {
    // 本体
    $stmt = $db->prepare("
        SELECT id, body, updated_at
        FROM wiki
        WHERE key_type = :kt AND key_value = :kv
        LIMIT 1
    ");
    if (!$stmt) {
        json_error('SQL_PREPARE_ERROR_WIKI: ' . $db->lastErrorMsg(), 500);
    }

    $stmt->bindValue(':kt', $keyType, SQLITE3_TEXT);
    $stmt->bindValue(':kv', $keyValue, SQLITE3_TEXT);
    $res = $stmt->execute();
    if (!$res) {
        json_error('SQL_EXEC_ERROR_WIKI: ' . $db->lastErrorMsg(), 500);
    }

    $row = $res->fetchArray(SQLITE3_ASSOC);
    $res->finalize();
    $stmt->close();

    if (!$row) {
        return [
            'id'              => null,
            'body'            => '',
            'updated_at'      => null,
            'updated_by_trip' => null,
            'updated_by_name' => null,
        ];
    }

    $id         = (int)$row['id'];
    $body       = $row['body'] ?? '';
    $updated_at = isset($row['updated_at']) ? (int)$row['updated_at'] : null;

    // 直近の履歴から編集者情報だけ拾う（無ければ null）
    $stmt2 = $db->prepare("
        SELECT editor_name, editor_trip
        FROM wiki_hist
        WHERE key_type = :kt AND key_value = :kv
        ORDER BY created_at DESC
        LIMIT 1
    ");
    if (!$stmt2) {
        json_error('SQL_PREPARE_ERROR_WIKI_HIST: ' . $db->lastErrorMsg(), 500);
    }
    $stmt2->bindValue(':kt', $keyType, SQLITE3_TEXT);
    $stmt2->bindValue(':kv', $keyValue, SQLITE3_TEXT);
    $res2 = $stmt2->execute();
    if (!$res2) {
        json_error('SQL_EXEC_ERROR_WIKI_HIST: ' . $db->lastErrorMsg(), 500);
    }
    $hist = $res2->fetchArray(SQLITE3_ASSOC) ?: null;
    $res2->finalize();
    $stmt2->close();

    return [
        'id'              => $id,
        'body'            => $body,
        'updated_at'      => $updated_at,
        'updated_by_trip' => $hist['editor_trip']  ?? null,
        'updated_by_name' => $hist['editor_name'] ?? null,
    ];
}


// =============================
// コメント一覧読み込み
// =============================
function load_comments(DbCompat $db, string $keyType, string $keyValue, int $limit = 50): array {
    $stmt = $db->prepare("
        SELECT id, body, author_name, author_trip, created_at
        FROM comments
        WHERE key_type = :kt
          AND key_value = :kv
          AND deleted = 0
        ORDER BY created_at DESC
        LIMIT :lim
    ");
    if (!$stmt) {
        json_error('SQL_PREPARE_ERROR_COMMENT: ' . $db->lastErrorMsg(), 500);
    }

    $stmt->bindValue(':kt',  $keyType,  SQLITE3_TEXT);
    $stmt->bindValue(':kv',  $keyValue, SQLITE3_TEXT);
    $stmt->bindValue(':lim', $limit,    SQLITE3_INTEGER);

    $res = $stmt->execute();
    if (!$res) {
        json_error('SQL_EXEC_ERROR_COMMENT: ' . $db->lastErrorMsg(), 500);
    }

    $rows = [];
    while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
        $rows[] = [
            'id'          => (int)$row['id'],
            'body'        => $row['body'],
            'author_name' => $row['author_name'] ?? '',
            'author_trip' => $row['author_trip'] ?? null,
            'created_at'  => (int)$row['created_at'],
        ];
    }
    $res->finalize();
    $stmt->close();

    return $rows;
}

/**
 * 逆引き（戦数つき・多い順）
 * - name検索: その名前に紐づく trip 一覧（戦数つき）
 * - trip検索: そのtripに紐づく name 一覧（戦数つき）
 *
 * ★方針: trip文字列は一切いじらない（◆付け外し/正規化しない）
 */
function load_reverse_lookup(DbCompat $db, string $keyType, string $keyValueNorm, int $limit = 20): array
{
    if ($limit <= 0) $limit = 20;
    if ($limit > 100) $limit = 100;

    $out = [
        'names_to_trips' => [],
        'trips_to_names' => [],
    ];

    if (!table_exists($db, 'players')) return $out;

    if ($keyType === 'name') {
        // name -> trips（DBのtrip文字列をそのまま。戦数で降順）
        $stmt = $db->prepare("
            SELECT trip, COUNT(*) AS games
            FROM players
            WHERE is_spectator = 0
              AND name = :name
              AND trip IS NOT NULL
              AND trip <> ''
            GROUP BY trip
            ORDER BY games DESC, trip ASC
            LIMIT :lim
        ");
        if (!$stmt) return $out;

        $stmt->bindValue(':name', $keyValueNorm, SQLITE3_TEXT);
        $stmt->bindValue(':lim',  $limit,       SQLITE3_INTEGER);

        $res = $stmt->execute();
        if (!$res) { $stmt->close(); return $out; }

        $tripList = [];
        while ($r = $res->fetchArray(SQLITE3_ASSOC)) {
            $t = (string)($r['trip'] ?? '');
            if ($t === '') continue;
            $tripList[] = [
                'trip'  => $t, // ★DBそのまま（◆付け替え禁止）
                'games' => (int)($r['games'] ?? 0),
            ];
        }
        $res->finalize();
        $stmt->close();

        $out['names_to_trips'][] = [
            'name'  => $keyValueNorm,
            'trips' => $tripList,
        ];
        return $out;
    }

    if ($keyType === 'trip') {
        $tripInput = trim($keyValueNorm);
        if ($tripInput === '') return $out;

        // trip -> names（tripは入力そのまま一致のみ）
        $stmt = $db->prepare("
            SELECT name, COUNT(*) AS games
            FROM players
            WHERE is_spectator = 0
              AND trip = :t
              AND name IS NOT NULL
              AND name <> ''
            GROUP BY name
            ORDER BY games DESC, name ASC
            LIMIT :lim
        ");
        if (!$stmt) return $out;

        $stmt->bindValue(':t',   $tripInput, SQLITE3_TEXT);
        $stmt->bindValue(':lim', $limit,     SQLITE3_INTEGER);

        $res = $stmt->execute();
        if (!$res) { $stmt->close(); return $out; }

        $nameList = [];
        while ($r = $res->fetchArray(SQLITE3_ASSOC)) {
            $n = trim((string)($r['name'] ?? ''));
            if ($n === '') continue;
            $nameList[] = [
                'name'  => $n,
                'games' => (int)($r['games'] ?? 0),
            ];
        }
        $res->finalize();
        $stmt->close();

        $out['trips_to_names'][] = [
            'trip'  => $tripInput, // ★入力そのまま
            'names' => $nameList,
        ];
        return $out;
    }

    return $out;
}



// ◆付き/記号付きトリップから「素トリップの芯」だけを抜き出す
function normalize_plain_trip_for_chain(string $trip): string {
    $trip = trim($trip);
    if ($trip === '') return '';

    // 先頭の記号（◆★☆#＃など）を剥がす
    $trip = preg_replace('/^[◆＃#★☆]+/u', '', $trip);
    // 全角英数→半角
    $trip = mb_convert_kana($trip, 'as');
    return trim($trip);
}

/**
 * 名前変遷ランキング
 * GET: mode=trip_chain_rank
 * limit, offset, min_games, min_span_days, both_modes
 */
function handle_trip_chain_rank(DbCompat $db): void
{
    $limit        = isset($_GET['limit']) ? (int)$_GET['limit'] : 50;
    $offset       = isset($_GET['offset']) ? (int)$_GET['offset'] : 0;
    $minGames     = isset($_GET['min_games']) ? (int)$_GET['min_games'] : 10;
    $minSpanDays  = isset($_GET['min_span_days']) ? (int)$_GET['min_span_days'] : 30;
    $bothModesFlg = isset($_GET['both_modes']) ? (int)$_GET['both_modes'] : 0;

    if ($limit <= 0 || $limit > 200) $limit = 50;
    if ($offset < 0) $offset = 0;
    if ($minGames < 0) $minGames = 0;
    if ($minSpanDays < 0) $minSpanDays = 0;

    $minSpanSec = $minSpanDays * 86400;

    $where = [];
    $where[] = 's.games_total >= :min_games';
    $where[] = 's.span_seconds >= :min_span_sec';
    if ($bothModesFlg) {
        $where[] = 's.plain_games > 0 AND s.fixed_games > 0';
    }
    $whereSql = implode(' AND ', $where);

    $sql = "
        SELECT
            s.*,
            c.latest_trip,
            c.trip_count,
            (
                SELECT n.name
                FROM plain_trip_chain_names n
                WHERE n.plain_trip = s.plain_trip
                ORDER BY n.games_total DESC, n.first_ts ASC
                LIMIT 1
            ) AS main_name
        FROM plain_trip_chain_summary s
        LEFT JOIN name_trip_chain c
               ON c.representative_trip = s.plain_trip
        WHERE {$whereSql}
        ORDER BY
            s.span_seconds DESC,
            s.games_total DESC,
            s.plain_trip ASC
        LIMIT :limit OFFSET :offset
    ";

    $stmt = $db->prepare($sql);
    if (!$stmt) {
        echo json_encode(['ok' => false, 'error' => 'SQL_PREPARE_ERROR_TRIP_CHAIN_RANK'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $stmt->bindValue(':min_games',   $minGames,   SQLITE3_INTEGER);
    $stmt->bindValue(':min_span_sec',$minSpanSec, SQLITE3_INTEGER);
    $stmt->bindValue(':limit',       $limit,      SQLITE3_INTEGER);
    $stmt->bindValue(':offset',      $offset,     SQLITE3_INTEGER);

    $res = $stmt->execute();
    if (!$res) {
        echo json_encode(['ok' => false, 'error' => 'SQL_EXEC_ERROR_TRIP_CHAIN_RANK'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $rows = [];
    while ($r = $res->fetchArray(SQLITE3_ASSOC)) {
        $spanSec  = (int)$r['span_seconds'];
        $spanDays = $spanSec > 0 ? round($spanSec / 86400, 1) : 0.0;

        $rows[] = [
            'plain_trip'     => $r['plain_trip'],
            'main_name'      => $r['main_name'] ?? '',
            'latest_trip'    => $r['latest_trip'] ?? null,
            'trip_count'     => isset($r['trip_count']) ? (int)$r['trip_count'] : null,
            'games_total'    => (int)$r['games_total'],
            'name_count'     => (int)$r['name_count'],
            'first_ts'       => (int)$r['first_ts'],
            'last_ts'        => (int)$r['last_ts'],
            'span_seconds'   => $spanSec,
            'span_days'      => $spanDays,
            'plain_games'    => (int)$r['plain_games'],
            'fixed_games'    => (int)$r['fixed_games'],
            'first_plain_ts' => $r['first_plain_ts'] !== null ? (int)$r['first_plain_ts'] : null,
            'first_fixed_ts' => $r['first_fixed_ts'] !== null ? (int)$r['first_fixed_ts'] : null,
        ];
    }
    $res->finalize();

    echo json_encode([
        'ok'     => true,
        'rows'   => $rows,
        'limit'  => $limit,
        'offset' => $offset,
    ], JSON_UNESCAPED_UNICODE);
}


/**
 * 1本の素トリップの名前変遷詳細
 * GET: mode=trip_chain_detail
 * plain_trip=...  または  trip=...
 */
function handle_trip_chain_detail(DbCompat $db): void
{
    $plain = trim($_GET['plain_trip'] ?? '');
    if ($plain === '') {
        $tripRaw = trim($_GET['trip'] ?? '');
        if ($tripRaw === '') {
            echo json_encode([
                'ok' => false,
                'error' => 'NO_TRIP',
                'msg' => 'plain_trip か trip を指定してください',
            ], JSON_UNESCAPED_UNICODE);
            return;
        }
        $plain = normalize_plain_trip_for_chain($tripRaw);
    }

    if ($plain === '') {
        echo json_encode([
            'ok' => false,
            'error' => 'INVALID_TRIP',
            'msg' => 'plain_trip が正しく解釈できませんでした',
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    // summary
    $stmt = $db->prepare("
        SELECT *
        FROM plain_trip_chain_summary
        WHERE plain_trip = :plain
        LIMIT 1
    ");
    if (!$stmt) {
        echo json_encode(['ok'=>false,'error'=>'SQL_PREPARE_ERROR_TRIP_CHAIN_DETAIL'], JSON_UNESCAPED_UNICODE);
        return;
    }
    $stmt->bindValue(':plain', $plain, SQLITE3_TEXT);
    $res  = $stmt->execute();
    $rowS = $res ? $res->fetchArray(SQLITE3_ASSOC) : false;
    if ($res) $res->finalize();
    $stmt->close();

    if (!$rowS) {
        echo json_encode([
            'ok' => false,
            'error' => 'NOT_FOUND',
            'msg' => '指定のトリップの変遷情報がありません',
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    $spanSec  = (int)$rowS['span_seconds'];
    $spanDays = $spanSec > 0 ? round($spanSec / 86400, 1) : 0.0;

    $summary = [
        'plain_trip'     => $rowS['plain_trip'],
        'games_total'    => (int)$rowS['games_total'],
        'name_count'     => (int)$rowS['name_count'],
        'first_ts'       => (int)$rowS['first_ts'],
        'last_ts'        => (int)$rowS['last_ts'],
        'span_seconds'   => $spanSec,
        'span_days'      => $spanDays,
        'plain_games'    => (int)$rowS['plain_games'],
        'fixed_games'    => (int)$rowS['fixed_games'],
        'first_plain_ts' => $rowS['first_plain_ts'] !== null ? (int)$rowS['first_plain_ts'] : null,
        'first_fixed_ts' => $rowS['first_fixed_ts'] !== null ? (int)$rowS['first_fixed_ts'] : null,
    ];

    // names（名前変遷一覧）
    $stmtN = $db->prepare("
        SELECT *
        FROM plain_trip_chain_names
        WHERE plain_trip = :plain
        ORDER BY first_ts ASC
    ");
    if (!$stmtN) {
        echo json_encode(['ok'=>false,'error'=>'SQL_PREPARE_ERROR_TRIP_CHAIN_NAMES'], JSON_UNESCAPED_UNICODE);
        return;
    }
    $stmtN->bindValue(':plain', $plain, SQLITE3_TEXT);
    $resN = $stmtN->execute();

    $names = [];
    while ($r = $resN->fetchArray(SQLITE3_ASSOC)) {
        $names[] = [
            'name'           => $r['name'],
            'games_total'    => (int)$r['games_total'],
            'first_ts'       => (int)$r['first_ts'],
            'last_ts'        => (int)$r['last_ts'],
            'plain_games'    => (int)$r['plain_games'],
            'fixed_games'    => (int)$r['fixed_games'],
            'first_plain_ts' => $r['first_plain_ts'] !== null ? (int)$r['first_plain_ts'] : null,
            'first_fixed_ts' => $r['first_fixed_ts'] !== null ? (int)$r['first_fixed_ts'] : null,
        ];
    }
    $resN->finalize();
    $stmtN->close();

    echo json_encode([
        'ok'      => true,
        'summary' => $summary,
        'names'   => $names,
    ], JSON_UNESCAPED_UNICODE);
}


// =============================
// ハンドラ群
// =============================

function handle_profile_get(DbCompat $db): void
{
    // セッションにログイン情報がなければ空返し
    if (session_status() === PHP_SESSION_NONE) session_start();
    if (empty($_SESSION['login_trip'])) {
        echo json_encode([
            'ok'        => true,
            'logged_in' => false,
            'profile'   => null,
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    $trip = $_SESSION['login_trip'];

    $stmt = $db->prepare("
        SELECT self_intro, x_id, x_display
        FROM player_profile
        WHERE trip = :trip
        LIMIT 1
    ");
    if (!$stmt) {
        json_error('SQL_PREPARE_ERROR_PROFILE: ' . $db->lastErrorMsg(), 500);
    }

    $stmt->bindValue(':trip', $trip, SQLITE3_TEXT);
    $res = $stmt->execute();
    if (!$res) {
        json_error('SQL_EXEC_ERROR_PROFILE: ' . $db->lastErrorMsg(), 500);
    }

    $row = $res->fetchArray(SQLITE3_ASSOC) ?: [];
    $res->finalize();
    $stmt->close();

    echo json_encode([
        'ok'        => true,
        'logged_in' => true,
        'profile'   => [
            'self_intro' => $row['self_intro'] ?? '',
            'x_id'       => $row['x_id']       ?? '',
            'x_display'  => $row['x_display']  ?? '',
        ],
    ], JSON_UNESCAPED_UNICODE);
}



// --- ログイン（名前＋トリップキー） ---
function handle_login(DbCompat $db): void {
    $raw = file_get_contents('php://input');
    $in  = json_decode($raw, true) ?: [];

    $name    = trim($in['name'] ?? '');
    $tripKey = trim($in['trip_key'] ?? '');

    if ($name === '') {
        json_error('名前を入力してください。');
    }
    if ($tripKey === '') {
        json_error('トリップキーを入力してください。');
    }

    $tripNorm = expand_trip_from_name_and_key($name, $tripKey);
    if ($tripNorm === '') {
        json_error('トリップ展開に失敗しました。');
    }

    $stmt = $db->prepare("
        SELECT 1
        FROM players
        WHERE trip = :t
        LIMIT 1
    ");
    $stmt->bindValue(':t', '' . $tripNorm, SQLITE3_TEXT);

    $res = $stmt->execute();
    if (!$res) json_error('SQL_EXEC_ERROR_LOGIN: ' . $db->lastErrorMsg(), 500);
    $row = $res->fetchArray(SQLITE3_ASSOC);
    $res->finalize();


    if (!$row) {
        json_error("このトリップ（{$tripNorm}）は戦績DBに存在しません。", 401);
    }

    // セッションには◆なしで統一
    set_login_user($tripNorm, $name);

    echo json_encode([
        'ok'   => true,
        'trip' => $tripNorm,
        'name' => $name,
        'mode' => 'login',
    ], JSON_UNESCAPED_UNICODE);
    exit;
}

// --- ログアウト ---
function handle_logout()
{
    if (session_status() === PHP_SESSION_NONE) session_start();
    $_SESSION = [];
    if (session_id() !== '') {
        session_regenerate_id(true);
    }
    echo json_encode(['ok' => true], JSON_UNESCAPED_UNICODE);
}

// --- ログイン状態確認 ---
function handle_me()
{
    $login = current_login();

    echo json_encode([
        'ok'        => true,
        'logged_in' => (bool)$login,
        'trip'      => $login['trip'] ?? null,
        'name'      => $login['name'] ?? null,
    ], JSON_UNESCAPED_UNICODE);
}

function table_exists(DbCompat $db, string $name): bool {
    // PostgreSQL用テーブル存在チェック
    $sql = "SELECT table_name FROM information_schema.tables WHERE table_name = :name";
    $stmt = $db->prepare($sql);
    if (!$stmt) return false;

    $stmt->bindValue(':name', $name, SQLITE3_TEXT);
    $res = $stmt->execute();
    if (!$res) return false;

    $row = $res->fetchArray(SQLITE3_ASSOC);
    $res->finalize();
    $stmt->close();
    return (bool)$row;
}

// --- プロフィール更新（自己紹介＋X） ---
function handle_profile_update(DbCompat $db): void {
    $login = require_login();

    $loginTrip = get_login_trip();
    if (!$loginTrip) {
        json_error('ログインが必要です。', 401);
    }

    $raw = file_get_contents('php://input');
    $in  = json_decode($raw, true) ?: [];

    $selfIntro = trim($in['self_intro'] ?? '');
    $xId       = trim($in['x_id'] ?? '');
    $xDisplay  = trim($in['x_display'] ?? '');
    $now       = time();

    // PostgreSQL: ON CONFLICT ... DO UPDATE
    $stmt = $db->prepare("
        INSERT INTO player_profile (trip, self_intro, x_id, x_display, updated_at)
        VALUES (:t, :si, :xid, :xd, :ts)
        ON CONFLICT(trip) DO UPDATE SET
          self_intro = excluded.self_intro,
          x_id       = excluded.x_id,
          x_display  = excluded.x_display,
          updated_at = excluded.updated_at
    ");
    if (!$stmt) {
        json_error('SQL_PREPARE_ERROR_PROFILE_UPDATE: ' . $db->lastErrorMsg(), 500);
    }

    $stmt->bindValue(':t',   $loginTrip, SQLITE3_TEXT);
    $stmt->bindValue(':si',  $selfIntro, SQLITE3_TEXT);
    $stmt->bindValue(':xid', $xId,       SQLITE3_TEXT);
    $stmt->bindValue(':xd',  $xDisplay,  SQLITE3_TEXT);
    $stmt->bindValue(':ts',  $now,       SQLITE3_INTEGER);

    if (!$stmt->execute()) {
        json_error('SQL_EXEC_ERROR_PROFILE_UPDATE: ' . $db->lastErrorMsg(), 500);
    }

    echo json_encode([
        'ok'         => true,
        'trip'       => $loginTrip,
        'self_intro' => $selfIntro,
        'x_id'       => $xId,
        'x_display'  => $xDisplay,
        'updated_at' => $now,
    ], JSON_UNESCAPED_UNICODE);
    exit;
}


// --- Wiki 保存 ---
function handle_wiki_save(DbCompat $db): void {
    $login = require_login();

    if (!$login || empty($login['trip'])) {
        echo json_encode(['ok' => false, 'error' => 'ログインが必要です。']);
        return;
    }

    $raw   = file_get_contents('php://input');
    $input = json_decode($raw, true) ?? [];

    $key_type_raw  = trim($input['key_type']  ?? '');
    $key_value_raw = trim($input['key_value'] ?? '');
    $body          = trim($input['body']      ?? '');

    if ($key_type_raw === '' || $key_value_raw === '') {
        echo json_encode(['ok' => false, 'error' => 'パラメータ不足です。']);
        return;
    }

    $key_type  = $key_type_raw;
    $key_value = trim($key_value_raw);

    $editor_name = trim($login['name'] ?? '');
    $editor_trip = $login['trip'];

    // wiki.key は「key_type:key_value」で埋める
    $key = $key_type . ':' . $key_value;

    // === Wiki 本体：PostgreSQL対応 ===
    // EXTRACT(EPOCH FROM NOW())::BIGINT, CAST(CURRENT_DATE AS TEXT)
    $sql = '
        INSERT INTO wiki
          (key, key_type, key_value, body, updated_at, updated_at_text)
        VALUES
          (:key, :kt, :kv, :body, EXTRACT(EPOCH FROM NOW())::BIGINT, CAST(CURRENT_DATE AS TEXT))
        ON CONFLICT(key_type, key_value) DO UPDATE SET
          key             = excluded.key,
          body            = excluded.body,
          updated_at      = excluded.updated_at,
          updated_at_text = excluded.updated_at_text
    ';
    $stmt = $db->prepare($sql);
    if (!$stmt) {
        echo json_encode([
            'ok'    => false,
            'error' => 'SQL_PREPARE_ERROR_WIKI_SAVE: ' . $db->lastErrorMsg(),
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    $stmt->bindValue(':key',  $key,       SQLITE3_TEXT);
    $stmt->bindValue(':kt',   $key_type,  SQLITE3_TEXT);
    $stmt->bindValue(':kv',   $key_value, SQLITE3_TEXT);
    $stmt->bindValue(':body', $body,      SQLITE3_TEXT);

    $res = $stmt->execute();
    if (!$res) {
        echo json_encode([
            'ok'    => false,
            'error' => 'SQL_EXEC_ERROR_WIKI_SAVE: ' . $db->lastErrorMsg(),
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    // === 履歴テーブル ===
    $hst = $db->prepare('
        INSERT INTO wiki_hist
           (key_type, key_value, body, editor_name, editor_trip, created_at)
        VALUES (:kt, :kv, :body, :en, :et, EXTRACT(EPOCH FROM NOW())::BIGINT)
    ');
    if (!$hst) {
        echo json_encode([
            'ok'    => false,
            'error' => 'SQL_PREPARE_ERROR_WIKI_HIST: ' . $db->lastErrorMsg(),
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    $hst->bindValue(':kt',   $key_type,    SQLITE3_TEXT);
    $hst->bindValue(':kv',   $key_value,   SQLITE3_TEXT);
    $hst->bindValue(':body', $body,        SQLITE3_TEXT);
    $hst->bindValue(':en',   $editor_name, SQLITE3_TEXT);
    $hst->bindValue(':et',   $editor_trip, SQLITE3_TEXT);

    if (!$hst->execute()) {
        echo json_encode([
            'ok'    => false,
            'error' => 'SQL_EXEC_ERROR_WIKI_HIST: ' . $db->lastErrorMsg(),
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    echo json_encode(['ok' => true]);
}


function handle_comment_add(DbCompat $db): void {

    $login = require_login();
    if (!$login || empty($login['trip'])) {
        echo json_encode(['ok' => false, 'error' => 'ログインが必要です。'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $s_trim = function ($v): string {
        if ($v === null) return '';
        if (is_string($v)) return trim($v);
        if (is_int($v) || is_float($v) || is_bool($v)) return trim((string)$v);
        return '';
    };

    $raw = file_get_contents('php://input');
    $in  = json_decode($raw, true);
    if (!is_array($in)) $in = [];

    $key_type  = $s_trim($in['key_type']  ?? null);
    $key_value = $s_trim($in['key_value'] ?? null);
    $body      = $s_trim($in['body']      ?? null);

    if ($key_type === '' || $key_value === '' || $body === '') {
        echo json_encode(['ok'=>false,'error'=>'パラメータ不足です'], JSON_UNESCAPED_UNICODE);
        return;
    }

    if ($key_value === 'undefined' || $key_value === 'null') {
        echo json_encode(['ok'=>false,'error'=>'key_value が不正です'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $authorName = $s_trim($login['name'] ?? null);
    if ($authorName === '') $authorName = '名無しさん';

    $authorTrip = (string)$login['trip'];

    // PostgreSQL: EXTRACT(EPOCH FROM NOW())::BIGINT
    $stmt = $db->prepare(
        'INSERT INTO comments
         (key_type, key_value, body, author_name, author_trip, created_at)
         VALUES (:kt, :kv, :body, :an, :at, EXTRACT(EPOCH FROM NOW())::BIGINT)'
    );
    if (!$stmt) {
        echo json_encode(['ok' => false, 'error' => 'DBエラー（コメント準備に失敗しました）'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $stmt->bindValue(':kt',   $key_type,    SQLITE3_TEXT);
    $stmt->bindValue(':kv',   $key_value,   SQLITE3_TEXT);
    $stmt->bindValue(':body', $body,        SQLITE3_TEXT);
    $stmt->bindValue(':an',   $authorName,  SQLITE3_TEXT);
    $stmt->bindValue(':at',   $authorTrip,  SQLITE3_TEXT);

    if (!$stmt->execute()) {
        echo json_encode(['ok' => false, 'error' => 'DBエラー（コメント追加に失敗しました）'], JSON_UNESCAPED_UNICODE);
        return;
    }

    echo json_encode(['ok' => true], JSON_UNESCAPED_UNICODE);
}


/**
 * 個人戦績
 */
/**
 * 個人戦績
 * - ★ first_ym（初登場年月 YYYY-MM）を返す
 */
function handle_personal(DbCompat $db): void
{
    $keyType = (string)($_GET['key_type'] ?? '');
    $qRaw    = trim((string)($_GET['q'] ?? ''));

    if ($keyType !== 'trip' && $keyType !== 'name') {
        echo json_encode([
            'ok'    => false,
            'error' => 'INVALID_KEY_TYPE',
            'msg'   => 'key_type=name|trip を指定してください。',
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    if ($qRaw === '') {
        echo json_encode([
            'ok'    => false,
            'error' => 'NO_QUERY',
            'msg'   => 'q を指定してください。',
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    $ratingInfo = null;

    // ★方針: trip文字列は一切いじらない（◆付け外し/normalizeしない）
    $keyValueNorm = $qRaw;

    // -----------------------------
    // rating（tripのみ）
    // -----------------------------
    if ($keyType === 'trip') {
        $sqlRating = '
            SELECT rating, games
            FROM player_ratings
            WHERE trip = :t
            LIMIT 1
        ';
        $stmtR = $db->prepare($sqlRating);
        if ($stmtR) {
            $stmtR->bindValue(':t', $keyValueNorm, SQLITE3_TEXT);
            $resR = $stmtR->execute();
            if ($resR) {
                $rowR = $resR->fetchArray(SQLITE3_ASSOC);
                if ($rowR) {
                    $ratingInfo = [
                        'rating' => (float)$rowR['rating'],
                        'games'  => (int)$rowR['games'],
                    ];
                }
                $resR->finalize();
            }
            $stmtR->close();
        }
    }

    // -----------------------------
    // 初登場（first_ts / first_ym）をSQLで先に取る（軽い）
    // -----------------------------
    $firstTs = null;
    $lastTs  = null;

    if ($keyType === 'trip') {
        $stmtFT = $db->prepare("
            SELECT
              MIN(g.started_at) AS first_ts,
              MAX(g.started_at) AS last_ts
            FROM players p
            JOIN games g ON g.id = p.game_id
            WHERE p.is_spectator = 0
              AND p.trip = :t
        ");
        if ($stmtFT) {
            $stmtFT->bindValue(':t', $keyValueNorm, SQLITE3_TEXT);
            $resFT = $stmtFT->execute();
            if ($resFT) {
                $rowFT = $resFT->fetchArray(SQLITE3_ASSOC) ?: null;
                if ($rowFT) {
                    $firstTs = isset($rowFT['first_ts']) ? (int)$rowFT['first_ts'] : null;
                    $lastTs  = isset($rowFT['last_ts'])  ? (int)$rowFT['last_ts']  : null;
                }
                $resFT->finalize();
            }
            $stmtFT->close();
        }
    } else {
        $stmtFT = $db->prepare("
            SELECT
              MIN(g.started_at) AS first_ts,
              MAX(g.started_at) AS last_ts
            FROM players p
            JOIN games g ON g.id = p.game_id
            WHERE p.is_spectator = 0
              AND p.name = :q
        ");
        if ($stmtFT) {
            $stmtFT->bindValue(':q', $keyValueNorm, SQLITE3_TEXT);
            $resFT = $stmtFT->execute();
            if ($resFT) {
                $rowFT = $resFT->fetchArray(SQLITE3_ASSOC) ?: null;
                if ($rowFT) {
                    $firstTs = isset($rowFT['first_ts']) ? (int)$rowFT['first_ts'] : null;
                    $lastTs  = isset($rowFT['last_ts'])  ? (int)$rowFT['last_ts']  : null;
                }
                $resFT->finalize();
            }
            $stmtFT->close();
        }
    }

    $firstYm = null;
    if ($firstTs !== null && $firstTs > 0) {
        // started_at はUTC想定、表示側はJSTで合わせてるのでここも +9h
        $firstYm = gmdate('Y-m', $firstTs + 9 * 3600);
    }

    // -----------------------------
    // 個人戦績 本体
    // -----------------------------
    if ($keyType === 'trip') {
        $sql = "
            SELECT
                p.*,
                g.village_name,
                g.log_id,
                g.started_at,
                g.haiyaku,
                g.rm_name
            FROM players p
            JOIN games g ON g.id = p.game_id
            WHERE p.is_spectator = 0
              AND p.trip = :t
            ORDER BY g.started_at ASC
        ";
        $stmt = $db->prepare($sql);
        if (!$stmt) {
            json_error('SQL_PREPARE_ERROR_PERSONAL_TRIP: ' . $db->lastErrorMsg(), 500);
        }
        $stmt->bindValue(':t', $keyValueNorm, SQLITE3_TEXT);
    } else {
        $sql = "
            SELECT
                p.*,
                g.village_name,
                g.log_id,
                g.started_at,
                g.haiyaku
            FROM players p
            JOIN games g ON g.id = p.game_id
            WHERE p.name = :q
              AND p.is_spectator = 0
            ORDER BY g.started_at ASC
        ";
        $stmt = $db->prepare($sql);
        if (!$stmt) {
            json_error('SQL_PREPARE_ERROR_PERSONAL_NAME: ' . $db->lastErrorMsg(), 500);
        }
        $stmt->bindValue(':q', $keyValueNorm, SQLITE3_TEXT);
    }

    $res = $stmt->execute();

    $rows = [];
    while ($r = $res->fetchArray(SQLITE3_ASSOC)) {
        $rows[] = $r;
    }
    $res->finalize();
    $stmt->close();

    if (!$rows) {
        echo json_encode([
            'ok'    => true,
            'found' => false,
            'query' => $qRaw,
            'key_type' => $keyType,
            'first_ts' => $firstTs,
            'last_ts'  => $lastTs,
            'first_ym' => $firstYm,
            'data'  => null,
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    // 集計ロジック
    $totalGames = count($rows);
    $winCount   = 0;
    $talkSum    = 0;
    $liveSum    = 0;

    $haiyakuStats = [];
    $roleStats    = [];
    $villageStats = [];
    $partnerStats = [];

    $byHour    = array_fill(0, 24, ['games' => 0, 'wins' => 0]);
    $byWeekday = array_fill(0, 7, ['games' => 0, 'wins' => 0]);
    $byMonth   = [];

    $calendarDays  = [];
    $calendarStart = null;
    $calendarEnd   = null;

    foreach ($rows as $row) {
        $win  = (int)$row['win'];
        $role = (string)$row['role'];
        $hai  = (string)$row['haiyaku'];
        $vill = (string)$row['village_name'];
        $ts   = (int)$row['started_at'];
        $talk = (int)$row['talk_count'];
        $live = (int)$row['live_days'];

        $winCount += $win;
        $talkSum  += $talk;
        $liveSum  += $live;

        $localDate = gmdate('Y-m-d', $ts + 9 * 3600);

        if (!isset($calendarDays[$localDate])) {
            $calendarDays[$localDate] = ['games' => 0, 'wins' => 0, 'loses' => 0];
        }
        $calendarDays[$localDate]['games']++;
        if ($win === 1) {
            $calendarDays[$localDate]['wins']++;
        } elseif ($win === 0) {
            $calendarDays[$localDate]['loses']++;
        }

        if ($calendarStart === null || $localDate < $calendarStart) $calendarStart = $localDate;
        if ($calendarEnd === null || $localDate > $calendarEnd) $calendarEnd = $localDate;

        if (!isset($haiyakuStats[$hai])) $haiyakuStats[$hai] = ['games' => 0, 'wins' => 0];
        $haiyakuStats[$hai]['games']++;
        $haiyakuStats[$hai]['wins'] += $win;

        if (!isset($roleStats[$role])) $roleStats[$role] = ['games' => 0, 'wins' => 0];
        $roleStats[$role]['games']++;
        $roleStats[$role]['wins'] += $win;

        if (!isset($villageStats[$vill])) $villageStats[$vill] = 0;
        $villageStats[$vill]++;

        $h  = (int)gmdate('G', $ts + 9 * 3600);
        $wd = (int)gmdate('w', $ts + 9 * 3600);
        $ym = gmdate('Y-m', $ts + 9 * 3600);

        $byHour[$h]['games']++;
        $byHour[$h]['wins'] += $win;

        $byWeekday[$wd]['games']++;
        $byWeekday[$wd]['wins'] += $win;

        if (!isset($byMonth[$ym])) $byMonth[$ym] = ['games' => 0, 'wins' => 0];
        $byMonth[$ym]['games']++;
        $byMonth[$ym]['wins'] += $win;
    }

    // 同村ランキング
    $gameIds = array_values(array_unique(array_column($rows, 'game_id')));
    if ($gameIds) {
        $ph = implode(',', array_fill(0, count($gameIds), '?'));
        $sqlP = "
            SELECT p2.*
            FROM players p2
            WHERE p2.game_id IN ($ph)
              AND p2.is_spectator = 0
        ";

        $stmtP = $db->prepare($sqlP);
        if (!$stmtP) {
            json_error('SQL_PREPARE_ERROR_PERSONAL_PARTNER: ' . $db->lastErrorMsg(), 500);
        }

        foreach ($gameIds as $i => $gid) {
            $stmtP->bindValue($i + 1, (int)$gid, SQLITE3_INTEGER);
        }
        $resP = $stmtP->execute();

        $selfTrip = ($keyType === 'trip') ? $rows[0]['trip'] : null;
        $selfName = ($keyType === 'name') ? $rows[0]['name'] : null;

        while ($r2 = $resP->fetchArray(SQLITE3_ASSOC)) {
            if ((int)$r2['is_spectator']) continue;

            if ($keyType === 'trip') {
                if ($r2['trip'] === $selfTrip) continue;
                $key = $r2['trip'] ?: $r2['name'];
            } else {
                if ($r2['name'] === $selfName) continue;
                $key = $r2['name'];
            }

            if (!isset($partnerStats[$key])) $partnerStats[$key] = 0;
            $partnerStats[$key]++;
        }
        $resP->finalize();
        $stmtP->close();
    }

    // recent_logs（DBから直接）
    [$recentLimit, $recentOffset] = normalize_recent_paging();

    if ($keyType === 'trip') {
        $sqlRecent = "
            SELECT DISTINCT
                g.log_id,
                g.village_name,
                g.rm_name,
                g.started_at
            FROM players p
            JOIN games g ON g.id = p.game_id
            WHERE p.is_spectator = 0
              AND p.trip = :t
            ORDER BY g.started_at DESC
            LIMIT :lim OFFSET :ofs
        ";
        $stmtRecent = $db->prepare($sqlRecent);
        $stmtRecent->bindValue(':t', $keyValueNorm, SQLITE3_TEXT);
    } else {
        $sqlRecent = "
            SELECT DISTINCT
                g.log_id,
                g.village_name,
                g.started_at
            FROM players p
            JOIN games g ON g.id = p.game_id
            WHERE p.is_spectator = 0
              AND p.name = :q
            ORDER BY g.started_at DESC
            LIMIT :lim OFFSET :ofs
        ";
        $stmtRecent = $db->prepare($sqlRecent);
        $stmtRecent->bindValue(':q', $keyValueNorm, SQLITE3_TEXT);
    }

    $stmtRecent->bindValue(':lim', $recentLimit + 1, SQLITE3_INTEGER);
    $stmtRecent->bindValue(':ofs', $recentOffset, SQLITE3_INTEGER);

    $resRecent = $stmtRecent->execute();

    $recent = [];
    while ($r = $resRecent->fetchArray(SQLITE3_ASSOC)) {
        $recent[] = [
            'log_id'       => (int)$r['log_id'],
            'village_name' => $r['village_name'],
            'rm_name'      => $r['rm_name'] ?? null,
            'started_at'   => (int)$r['started_at'],
        ];
    }
    $resRecent->finalize();
    $stmtRecent->close();

    $recentHasMore = false;
    if (count($recent) > $recentLimit) {
        array_pop($recent);
        $recentHasMore = true;
    }

    // ソート・整形
    arsort($villageStats);
    arsort($partnerStats);

    $haiyakuArr = [];
    foreach ($haiyakuStats as $k => $x) {
        $g = (int)$x['games'];
        $w = (int)$x['wins'];
        $haiyakuArr[] = [
            'haiyaku'  => $k,
            'games'    => $g,
            'wins'     => $w,
            'win_rate' => $g > 0 ? round($w / $g * 100, 1) : 0,
        ];
    }
    usort($haiyakuArr, fn($a, $b) => $b['games'] <=> $a['games']);

    $roleArr = [];
    foreach ($roleStats as $k => $x) {
        $g = (int)$x['games'];
        $w = (int)$x['wins'];
        $roleArr[] = [
            'role'     => $k,
            'games'    => $g,
            'wins'     => $w,
            'win_rate' => $g > 0 ? round($w / $g * 100, 1) : 0,
        ];
    }
    usort($roleArr, fn($a, $b) => $b['games'] <=> $a['games']);

    $villageArr = [];
    foreach ($villageStats as $k => $v) {
        $villageArr[] = ['village' => $k, 'games' => $v];
    }
    $villageArr = array_slice($villageArr, 0, 30);

    $partnerArr = [];
    foreach ($partnerStats as $k => $v) {
        $partnerArr[] = ['key' => $k, 'games' => $v];
    }
    $partnerArr = array_slice($partnerArr, 0, 30);

    $byHourArr = [];
    for ($i = 0; $i < 24; $i++) {
        $g = $byHour[$i]['games'];
        $w = $byHour[$i]['wins'];
        $byHourArr[] = [
            'hour'     => $i,
            'games'    => $g,
            'wins'     => $w,
            'win_rate' => $g > 0 ? round($w / $g * 100, 1) : 0,
        ];
    }

    $weekdayLabel = ['日','月','火','水','木','金','土'];
    $byWeekdayArr = [];
    for ($i = 0; $i < 7; $i++) {
        $g = $byWeekday[$i]['games'];
        $w = $byWeekday[$i]['wins'];
        $byWeekdayArr[] = [
            'weekday'  => $i,
            'label'    => $weekdayLabel[$i],
            'games'    => $g,
            'wins'     => $w,
            'win_rate' => $g > 0 ? round($w / $g * 100, 1) : 0,
        ];
    }

    ksort($byMonth);
    $byMonthArr = [];
    foreach ($byMonth as $ym => $x) {
        $g = $x['games'];
        $w = $x['wins'];
        $byMonthArr[] = [
            'ym'       => $ym,
            'games'    => $g,
            'wins'     => $w,
            'win_rate' => $g > 0 ? round($w / $g * 100, 1) : 0,
        ];
    }

    $avgTalk = $liveSum > 0 ? round($talkSum / $liveSum, 2) : 0;
    $winRate = $totalGames > 0 ? round($winCount / $totalGames * 100, 1) : 0;

    // トリップ変遷（これは「芯」を作る仕様。trip一致検索とは独立）
    $tripChain = null;
    if ($keyType === 'trip') {
        $plain = normalize_plain_trip_for_chain($qRaw);
        if ($plain !== '') {
            $stmtC = $db->prepare("
                SELECT *
                FROM plain_trip_chain_summary
                WHERE plain_trip = :plain
                LIMIT 1
            ");
            if ($stmtC) {
                $stmtC->bindValue(':plain', $plain, SQLITE3_TEXT);
                $resC = $stmtC->execute();
                $rowC = $resC ? $resC->fetchArray(SQLITE3_ASSOC) : false;
                if ($resC) $resC->finalize();
                $stmtC->close();

                if ($rowC) {
                    $spanSec  = (int)$rowC['span_seconds'];
                    $spanDays = $spanSec > 0 ? round($spanSec / 86400, 1) : 0.0;

                    $stmtCN = $db->prepare("
                        SELECT name, games_total, first_ts, last_ts,
                               plain_games, fixed_games
                        FROM plain_trip_chain_names
                        WHERE plain_trip = :plain
                        ORDER BY first_ts ASC
                    ");
                    if ($stmtCN) {
                        $stmtCN->bindValue(':plain', $plain, SQLITE3_TEXT);
                        $resCN = $stmtCN->execute();
                        $nameChains = [];
                        while ($rN = $resCN->fetchArray(SQLITE3_ASSOC)) {
                            $nameChains[] = [
                                'name'        => $rN['name'],
                                'games_total' => (int)$rN['games_total'],
                                'first_ts'    => (int)$rN['first_ts'],
                                'last_ts'     => (int)$rN['last_ts'],
                                'plain_games' => (int)$rN['plain_games'],
                                'fixed_games' => (int)$rN['fixed_games'],
                            ];
                        }
                        $resCN->finalize();
                        $stmtCN->close();

                        $tripChain = [
                            'plain_trip'  => $plain,
                            'span_days'   => $spanDays,
                            'games_total' => (int)$rowC['games_total'],
                            'name_count'  => (int)$rowC['name_count'],
                            'names'       => $nameChains,
                        ];
                    }
                }
            }
        }
    }

    $profile  = null;
    $wiki     = ['body' => '', 'updated_at' => null];
    $comments = [];

    // プロフィール（tripはそのまま一致）
    if ($keyType === 'trip' && table_exists($db, 'player_profile')) {
        $tmp = load_profile_for_trip($db, $keyValueNorm);
        if (is_array($tmp)) {
            $profile = $tmp;
            $loginTrip = get_login_trip();
            if ($loginTrip && $loginTrip === $keyValueNorm) {
                $profile['editable'] = true;
            }
        }
    }

    $keyValueForMeta = trim($qRaw);
    $wiki     = load_wiki($db, $keyType, $keyValueForMeta);
    $comments = load_comments($db, $keyType, $keyValueForMeta, 50);

    $calendar = [
        'start' => $calendarStart,
        'end'   => $calendarEnd,
        'days'  => $calendarDays,
    ];

    $reverseLimit = isset($_GET['reverse_limit']) ? (int)$_GET['reverse_limit'] : 20;
    $reverse = load_reverse_lookup($db, $keyType, $keyValueNorm, $reverseLimit);

    echo json_encode([
        'ok'        => true,
        'found'     => true,
        'query'     => $qRaw,
        'key_type'  => $keyType,

        // ★追加：初登場
        'first_ts'  => $firstTs,
        'last_ts'   => $lastTs,
        'first_ym'  => $firstYm,

        'summary'   => [
            'total_games'       => $totalGames,
            'wins'              => $winCount,
            'win_rate'          => $winRate,
            'talk_sum'          => $talkSum,
            'live_days_sum'     => $liveSum,
            'avg_talk_per_day'  => $avgTalk,
        ],
        'calendar'      => $calendar,
        'haiyaku_stats' => $haiyakuArr,
        'role_stats'    => $roleArr,
        'village_top'   => $villageArr,
        'partners_top'  => $partnerArr,
        'recent_logs'   => $recent,
        'by_hour'       => $byHourArr,
        'by_weekday'    => $byWeekdayArr,
        'by_month'      => $byMonthArr,
        'profile'       => $profile,
        'wiki'          => $wiki,
        'comments'      => $comments,
        'rating'        => $ratingInfo,
        'trip_chain'    => $tripChain,
        'reverse'       => $reverse,
        'recent_limit'     => $recentLimit,
        'recent_offset'    => $recentOffset,
        'recent_has_more'  => $recentHasMore,
    ], JSON_UNESCAPED_UNICODE);
}



function handle_village_detail(DbCompat $db): void
{
    $id    = isset($_GET['id'])     ? (int)$_GET['id']     : 0;
    $logId = isset($_GET['log_id']) ? (int)$_GET['log_id'] : 0;

    if ($id <= 0 && $logId <= 0) {
        echo json_encode([
            'ok'    => false,
            'error' => 'NO_ID',
            'msg'   => 'id か log_id を指定してください。',
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    if ($id > 0) {
        $stmt = $db->prepare("
            SELECT id, log_id, village_name, started_at, haiyaku
            FROM games
            WHERE id = :id
            LIMIT 1
        ");
        if (!$stmt) {
            json_error('SQL_PREPARE_ERROR_VILLAGE_DETAIL_GAME_ID: ' . $db->lastErrorMsg(), 500);
        }
        $stmt->bindValue(':id', $id, SQLITE3_INTEGER);
    } else {
        $stmt = $db->prepare("
            SELECT id, log_id, village_name, started_at, haiyaku
            FROM games
            WHERE log_id = :log_id
            LIMIT 1
        ");
        if (!$stmt) {
            json_error('SQL_PREPARE_ERROR_VILLAGE_DETAIL_GAME_LOG: ' . $db->lastErrorMsg(), 500);
        }
        $stmt->bindValue(':log_id', $logId, SQLITE3_INTEGER);
    }

    $res = $stmt->execute();
    if (!$res) {
        json_error('SQL_EXEC_ERROR_VILLAGE_DETAIL_GAME: ' . $db->lastErrorMsg(), 500);
    }

    $game = $res->fetchArray(SQLITE3_ASSOC) ?: null;
    $res->finalize();
    $stmt->close();

    if (!$game) {
        echo json_encode([
            'ok'    => false,
            'error' => 'NOT_FOUND',
            'msg'   => '指定された村が見つかりません。',
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    $gameId = (int)$game['id'];

    $village = [
        'id'           => $gameId,
        'log_id'       => (int)$game['log_id'],
        'village_name' => $game['village_name'],
        'started_at'   => (int)$game['started_at'],
        'haiyaku'      => $game['haiyaku'],
    ];

    $stmtP = $db->prepare("
        SELECT
            id,
            name,
            trip,
            role,
            haiyaku,
            win,
            is_spectator,
            talk_count,
            live_days
        FROM players
        WHERE game_id = :gid
        ORDER BY is_spectator ASC, id ASC
    ");
    if (!$stmtP) {
        json_error('SQL_PREPARE_ERROR_VILLAGE_DETAIL_PLAYERS: ' . $db->lastErrorMsg(), 500);
    }
    $stmtP->bindValue(':gid', $gameId, SQLITE3_INTEGER);

    $resP = $stmtP->execute();
    if (!$resP) {
        json_error('SQL_EXEC_ERROR_VILLAGE_DETAIL_PLAYERS: ' . $db->lastErrorMsg(), 500);
    }

    $players = [];
    $totalPlayers    = 0;
    $totalSpectators = 0;
    $winnerCount     = 0;
    $talkSum         = 0;
    $liveDaysSum     = 0;

    $roleStats    = [];
    $haiyakuStats = [];

    while ($row = $resP->fetchArray(SQLITE3_ASSOC)) {
        $isSpectator = (int)$row['is_spectator'] ? 1 : 0;
        $win         = (int)$row['win'];
        $talk        = (int)$row['talk_count'];
        $liveDays    = (int)$row['live_days'];
        $role        = (string)$row['role'];
        $hai         = (string)$row['haiyaku'];

        if ($isSpectator) {
            $totalSpectators++;
        } else {
            $totalPlayers++;
            $winnerCount += $win;
            $talkSum     += $talk;
            $liveDaysSum += $liveDays;

            if (!isset($roleStats[$role])) $roleStats[$role] = ['games' => 0, 'wins' => 0];
            $roleStats[$role]['games']++;
            $roleStats[$role]['wins']  += $win;

            if (!isset($haiyakuStats[$hai])) $haiyakuStats[$hai] = ['games' => 0, 'wins' => 0];
            $haiyakuStats[$hai]['games']++;
            $haiyakuStats[$hai]['wins'] += $win;
        }

        $players[] = [
            'id'           => (int)$row['id'],
            'name'         => $row['name'],
            'trip'         => $row['trip'],
            'role'         => $role,
            'haiyaku'      => $hai,
            'win'          => $win,
            'is_spectator' => $isSpectator,
            'talk_count'   => $talk,
            'live_days'    => $liveDays,
        ];
    }
    $resP->finalize();
    $stmtP->close();

    $avgTalkPerPlayer = $totalPlayers > 0 ? round($talkSum / $totalPlayers, 2) : 0.0;
    $avgTalkPerDay    = $liveDaysSum > 0 ? round($talkSum / $liveDaysSum, 2) : 0.0;

    $summary = [
        'total_players'       => $totalPlayers,
        'total_spectators'    => $totalSpectators,
        'winners'             => $winnerCount,
        'losers'              => max(0, $totalPlayers - $winnerCount),
        'talk_sum'            => $talkSum,
        'live_days_sum'       => $liveDaysSum,
        'avg_talk_per_player' => $avgTalkPerPlayer,
        'avg_talk_per_day'    => $avgTalkPerDay,
    ];

    $roleArr = [];
    foreach ($roleStats as $k => $x) {
        $g = (int)$x['games'];
        $w = (int)$x['wins'];
        $roleArr[] = [
            'role'     => $k,
            'games'    => $g,
            'wins'     => $w,
            'win_rate' => $g > 0 ? round($w / $g * 100, 1) : 0,
        ];
    }
    usort($roleArr, fn($a, $b) => $b['games'] <=> $a['games']);

    $haiArr = [];
    foreach ($haiyakuStats as $k => $x) {
        $g = (int)$x['games'];
        $w = (int)$x['wins'];
        $haiArr[] = [
            'haiyaku'  => $k,
            'games'    => $g,
            'wins'     => $w,
            'win_rate' => $g > 0 ? round($w / $g * 100, 1) : 0,
        ];
    }
    usort($haiArr, fn($a, $b) => $b['games'] <=> $a['games']);

    echo json_encode([
        'ok'            => true,
        'village'       => $village,
        'summary'       => $summary,
        'players'       => $players,
        'role_stats'    => $roleArr,
        'haiyaku_stats' => $haiArr,
    ], JSON_UNESCAPED_UNICODE);
}


function handle_name_chain_list(DbCompat $db): void
{
    $page  = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
    $limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 50;
    if ($limit <= 0 || $limit > 200) {
        $limit = 50;
    }
    $offset = ($page - 1) * $limit;

    $orderParam = $_GET['order'] ?? 'last_ts_desc';
    switch ($orderParam) {
        case 'first_ts_asc':
            $orderBy = 'c.first_ts ASC';
            break;
        case 'games_desc':
            $orderBy = 'c.games_total DESC';
            break;
        case 'span_days_desc':
            $orderBy = '(c.last_ts - c.first_ts) DESC';
            break;
        case 'last_ts_desc':
        default:
            $orderBy   = 'c.last_ts DESC';
            $orderParam = 'last_ts_desc';
            break;
    }

    $totalRes = $db->query('SELECT COUNT(*) AS cnt FROM name_trip_chain');
    if ($totalRes === false) {
        echo json_encode(['ok' => false, 'error' => 'count_failed'], JSON_UNESCAPED_UNICODE);
        return;
    }
    $totalRow = $totalRes->fetchArray(SQLITE3_ASSOC);
    $total    = (int)($totalRow['cnt'] ?? 0);

    $sql = <<<SQL
SELECT
  c.id,
  c.representative_trip,
  c.latest_trip,
  c.first_ts,
  c.last_ts,
  c.games_total,
  c.trip_count,
(
  SELECT p2.name
  FROM players p2
  JOIN games g2 ON g2.id = p2.game_id
  WHERE p2.trip = c.latest_trip
  ORDER BY g2.started_at DESC
  LIMIT 1
) AS latest_name

FROM name_trip_chain c
ORDER BY {$orderBy}
LIMIT :limit OFFSET :offset
SQL;

    $stmt = $db->prepare($sql);
    if ($stmt === false) {
        echo json_encode(['ok' => false, 'error' => 'prepare_failed'], JSON_UNESCAPED_UNICODE);
        return;
    }
    $stmt->bindValue(':limit',  $limit,  SQLITE3_INTEGER);
    $stmt->bindValue(':offset', $offset, SQLITE3_INTEGER);

    $res = $stmt->execute();
    if ($res === false) {
        echo json_encode(['ok' => false, 'error' => 'query_failed'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $rows = [];
    while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
        $firstTs = (int)$row['first_ts'];
        $lastTs  = (int)$row['last_ts'];
        $spanDays = ($lastTs > $firstTs) ? (int)floor(($lastTs - $firstTs) / 86400) : 0;

        $rows[] = [
            'id'                => (int)$row['id'],
            'representative_trip' => $row['representative_trip'],
            'latest_trip'       => $row['latest_trip'],
            'latest_name'       => $row['latest_name'] ?? '',
            'first_ts'          => $firstTs,
            'last_ts'           => $lastTs,
            'span_days'         => $spanDays,
            'games_total'       => (int)$row['games_total'],
            'trip_count'        => (int)$row['trip_count'],
        ];
    }

    echo json_encode([
        'ok'     => true,
        'total'  => $total,
        'page'   => $page,
        'limit'  => $limit,
        'order'  => $orderParam,
        'rows'   => $rows,
    ], JSON_UNESCAPED_UNICODE);
}


function handle_months(DbCompat $db): void
{
    $months = RankingLib::getMonths($db);
    echo json_encode([
        'ok'     => true,
        'months' => $months,
    ], JSON_UNESCAPED_UNICODE);
}


function handle_multi_trip(DbCompat $db): void
{
    $raw = file_get_contents('php://input');
    $input = json_decode($raw ?: '', true);
    if (!is_array($input)) {
        $input = $_POST + $_GET;
    }

    $tripsText = trim((string)($input['trips'] ?? ''));
    if ($tripsText === '') {
        echo json_encode([
            'ok'    => false,
            'error' => 'NO_TRIPS',
            'msg'   => 'trips を1行1トリップで指定してください。',
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    // ★方針: trip文字列は一切いじらない（◆付け外し/正規化/分割トリックなし）
    $work  = preg_replace('/\r\n?/', "\n", $tripsText);
    $lines = preg_split('/\n/u', $work);
    $lines = array_values(array_filter(array_map('trim', $lines), fn($v) => $v !== ''));

    if (!$lines) {
        echo json_encode([
            'ok' => false,
            'error' => 'NO_VALID_TRIPS',
            'msg' => '有効なトリップがありません。',
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    // 入力順を保ったままユニーク化
    $seen = [];
    $trips = [];
    foreach ($lines as $t) {
        if (isset($seen[$t])) continue;
        $seen[$t] = true;
        $trips[] = $t;
    }

    $ph = implode(',', array_fill(0, count($trips), '?'));

    $sql = "
        SELECT
            p.trip,
            COUNT(*)               AS total_games,
            COUNT(DISTINCT p.name) AS unique_names
        FROM players p
        WHERE p.trip IN ($ph)
          AND p.is_spectator = 0
        GROUP BY p.trip
    ";

    $st = $db->prepare($sql);
    if (!$st) {
        echo json_encode(['ok' => false, 'error' => 'SQL_PREPARE_ERROR_MULTI_TRIP'], JSON_UNESCAPED_UNICODE);
        return;
    }

    foreach ($trips as $i => $t) {
        $st->bindValue($i + 1, $t, SQLITE3_TEXT);
    }

    $res = $st->execute();
    if (!$res) {
        $st->close();
        echo json_encode(['ok' => false, 'error' => 'SQL_EXEC_ERROR_MULTI_TRIP'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $map = [];
    while ($r = $res->fetchArray(SQLITE3_ASSOC)) {
        $trip = (string)$r['trip'];
        $map[$trip] = [
            'trip'         => $trip,
            'total_games'  => (int)($r['total_games'] ?? 0),
            'unique_names' => (int)($r['unique_names'] ?? 0),
            'logs'         => [],
            'main_name'    => null,
        ];
    }
    $res->finalize();
    $st->close();

    // main_name（そのtripで最も多い名前）
    if ($map) {
        $tripsForName = array_keys($map);
        $ph2 = implode(',', array_fill(0, count($tripsForName), '?'));

        $sqlName = "
            SELECT
                trip,
                name,
                COUNT(*) AS cnt
            FROM players
            WHERE trip IN ($ph2)
              AND is_spectator = 0
              AND name IS NOT NULL
              AND name <> ''
            GROUP BY trip, name
        ";

        $st2 = $db->prepare($sqlName);
        if ($st2) {
            foreach ($tripsForName as $i => $t) {
                $st2->bindValue($i + 1, $t, SQLITE3_TEXT);
            }
            $res2 = $st2->execute();
            if ($res2) {
                $best = [];
                while ($row = $res2->fetchArray(SQLITE3_ASSOC)) {
                    $trip = (string)$row['trip'];
                    $name = (string)$row['name'];
                    $cnt  = (int)$row['cnt'];

                    if (!isset($best[$trip]) || $cnt > $best[$trip]['cnt']) {
                        $best[$trip] = ['name' => $name, 'cnt' => $cnt];
                    }
                }
                $res2->finalize();
                $st2->close();

                foreach ($best as $trip => $info) {
                    if (isset($map[$trip])) {
                        $map[$trip]['main_name'] = $info['name'];
                    }
                }
            } else {
                $st2->close();
            }
        }
    }

    // 直近ログID（5件）
    if ($map) {
        $sqlL = "
            SELECT DISTINCT g.log_id
            FROM players p
            JOIN games g ON g.id = p.game_id
            WHERE p.trip = :trip
              AND p.is_spectator = 0
            ORDER BY g.log_id DESC
            LIMIT 5
        ";
        $stL = $db->prepare($sqlL);
        if ($stL) {
            foreach ($map as $trip => &$info) {
                $stL->reset();
                $stL->bindValue(':trip', $trip, SQLITE3_TEXT);
                $resL = $stL->execute();
                if ($resL) {
                    while ($r2 = $resL->fetchArray(SQLITE3_NUM)) {
                        $info['logs'][] = (int)$r2[0];
                    }
                    $resL->finalize();
                }
            }
            unset($info);
            $stL->close();
        }
    }

    // 入力順で返す（存在しないtripは0件）
    $results = [];
    foreach ($trips as $t) {
        if (isset($map[$t])) {
            $results[] = $map[$t];
        } else {
            $results[] = [
                'trip'         => $t,
                'total_games'  => 0,
                'unique_names' => 0,
                'logs'         => [],
                'main_name'    => null,
            ];
        }
    }

    echo json_encode([
        'ok'      => true,
        'results' => $results,
    ], JSON_UNESCAPED_UNICODE);
}

function handle_multi_summary(DbCompat $db): void
{
    $keyType = ($_GET['key_type'] ?? 'trip') === 'name' ? 'name' : 'trip';
    $raw = trim((string)($_GET['trips'] ?? ''));
    if ($raw === '') {
        echo json_encode(['ok' => false, 'error' => 'trips が空です'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $lines = preg_split('/\r\n|\r|\n/u', $raw);
    $keys = [];
    $seen = [];
    foreach ($lines as $line) {
        $v = trim((string)$line);
        if ($v === '') continue;
        if ($keyType === 'trip') $v = normalize_trip($v);
        if (isset($seen[$v])) continue;
        $seen[$v] = true;
        $keys[] = $v;
    }
    if (!$keys) {
        echo json_encode(['ok' => false, 'error' => '有効な対象がありません'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $col = $keyType === 'trip' ? 'trip' : 'name';
    $ph = implode(',', array_fill(0, count($keys), '?'));
    $sql = "
        SELECT p.{$col} AS k,
               p.win,
               COALESCE(g.haiyaku_class, '通常') AS cls,
               g.started_at
          FROM players p
          JOIN games g ON g.id = p.game_id
         WHERE p.is_spectator = 0
           AND p.{$col} IN ({$ph})
    ";
    $stmt = $db->prepare($sql);
    if (!$stmt) {
        echo json_encode(['ok' => false, 'error' => 'SQL準備に失敗しました'], JSON_UNESCAPED_UNICODE);
        return;
    }
    foreach ($keys as $i => $key) {
        $stmt->bindValue($i + 1, $key, SQLITE3_TEXT);
    }
    $res = $stmt->execute();

    $map = [];
    foreach ($keys as $key) {
        $map[$key] = [
            'found' => false,
            'normal' => ['games' => 0, 'wins' => 0, 'win_rate' => 0.0],
            'chaos' => ['games' => 0, 'wins' => 0, 'win_rate' => 0.0],
            'first_ts' => null,
            'first_ym' => null,
            'is_shared_trip' => false,
        ];
    }
    while ($res && ($r = $res->fetchArray(SQLITE3_ASSOC))) {
        $key = (string)$r['k'];
        if (!isset($map[$key])) continue;
        $bucket = (($r['cls'] ?? '通常') === '通常') ? 'normal' : 'chaos';
        $map[$key]['found'] = true;
        $map[$key][$bucket]['games']++;
        $map[$key][$bucket]['wins'] += (int)($r['win'] ?? 0);
        $ts = isset($r['started_at']) ? (int)$r['started_at'] : 0;
        if ($ts && ($map[$key]['first_ts'] === null || $ts < $map[$key]['first_ts'])) {
            $map[$key]['first_ts'] = $ts;
        }
    }

    if ($keyType === 'trip') {
        $st = $db->prepare("SELECT trip FROM shared_trips WHERE trip IN ({$ph})");
        if ($st) {
            foreach ($keys as $i => $key) {
                $st->bindValue($i + 1, $key, SQLITE3_TEXT);
            }
            $sr = $st->execute();
            while ($sr && ($r = $sr->fetchArray(SQLITE3_ASSOC))) {
                $t = (string)$r['trip'];
                if (isset($map[$t])) $map[$t]['is_shared_trip'] = true;
            }
        }
    }

    foreach ($map as &$row) {
        foreach (['normal', 'chaos'] as $bucket) {
            $g = $row[$bucket]['games'];
            $w = $row[$bucket]['wins'];
            $row[$bucket]['win_rate'] = $g > 0 ? round($w / $g * 100, 1) : 0.0;
        }
        if ($row['first_ts']) {
            $row['first_ym'] = date('Y-m', (int)$row['first_ts']);
        }
    }
    unset($row);

    echo json_encode(['ok' => true, 'results' => $map], JSON_UNESCAPED_UNICODE);
}

function handle_trip_identities(DbCompat $db): void
{
    $raw = trim((string)($_GET['trips'] ?? ''));
    $keys = [];
    foreach (preg_split('/\r\n|\r|\n/u', $raw) ?: [] as $line) {
        $trip = preg_replace('/^◆/u', '', trim((string)$line));
        if ($trip !== '' && !in_array($trip, $keys, true)) $keys[] = $trip;
        if (count($keys) >= 40) break;
    }
    if (!$keys) {
        echo json_encode(['ok' => true, 'results' => (object)[]], JSON_UNESCAPED_UNICODE);
        return;
    }
    $variants = [];
    foreach ($keys as $key) { $variants[] = $key; $variants[] = '◆' . $key; }
    $variants = array_values(array_unique($variants));
    $ph = implode(',', array_fill(0, count($variants), '?'));
    $sql = "
        WITH grouped AS (
            SELECT regexp_replace(p.trip, '^◆', '') AS trip_key,
                   p.name,
                   COUNT(DISTINCT p.game_id) AS name_games,
                   MAX(COALESCE(g.started_at, p.created_at, 0)) AS last_ts
              FROM players p JOIN games g ON g.id = p.game_id
             WHERE p.is_spectator = 0 AND p.trip <> '' AND p.name <> ''
               AND p.trip IN ({$ph})
             GROUP BY regexp_replace(p.trip, '^◆', ''), p.name
        ), ranked AS (
            SELECT *, SUM(name_games) OVER (PARTITION BY trip_key) AS total_games,
                   ROW_NUMBER() OVER (PARTITION BY trip_key ORDER BY name_games DESC, last_ts DESC, name ASC) AS rank_no
              FROM grouped
        )
        SELECT trip_key, name AS top_name, name_games AS top_name_games, total_games, last_ts
          FROM ranked WHERE rank_no = 1
    ";
    $stmt = $db->prepare($sql);
    foreach ($variants as $i => $key) $stmt->bindValue($i + 1, $key, SQLITE3_TEXT);
    $res = $stmt->execute();
    $found = [];
    while ($res && ($r = $res->fetchArray(SQLITE3_ASSOC))) {
        $found[(string)$r['trip_key']] = [
            'found' => true,
            'top_name' => (string)$r['top_name'],
            'top_name_games' => (int)$r['top_name_games'],
            'total_games' => (int)$r['total_games'],
            'last_ts' => (int)$r['last_ts'],
        ];
    }
    $results = [];
    foreach ($keys as $key) {
        $results[$key] = $found[$key] ?? ['found' => false, 'top_name' => '', 'top_name_games' => 0, 'total_games' => 0, 'last_ts' => 0];
    }
    echo json_encode(['ok' => true, 'results' => $results], JSON_UNESCAPED_UNICODE);
}

function ensure_bbs_table(DbCompat $db): void
{
    $db->exec("
        CREATE TABLE IF NOT EXISTS bbs_posts (
            id SERIAL PRIMARY KEY,
            body TEXT NOT NULL DEFAULT '',
            author_name TEXT DEFAULT '',
            author_trip TEXT DEFAULT '',
            created_at BIGINT DEFAULT 0,
            deleted SMALLINT NOT NULL DEFAULT 0
        )
    ");
}

function sanitize_plain_text(string $text, int $maxLen): string
{
    $text = trim(strip_tags($text));
    if (mb_strlen($text, 'UTF-8') > $maxLen) {
        $text = mb_substr($text, 0, $maxLen, 'UTF-8');
    }
    return $text;
}

function handle_bbs_list(DbCompat $db): void
{
    ensure_bbs_table($db);
    $limit = clamp_int((int)($_GET['limit'] ?? 20), 1, 50);
    $offset = clamp_int((int)($_GET['offset'] ?? 0), 0, 100000);

    $total = (int)$db->querySingle("SELECT COUNT(*) FROM bbs_posts WHERE deleted = 0");
    $stmt = $db->prepare("
        SELECT id, author_name, author_trip, body, created_at
          FROM bbs_posts
         WHERE deleted = 0
         ORDER BY created_at DESC, id DESC
         LIMIT :limit OFFSET :offset
    ");
    $stmt->bindValue(':limit', $limit, SQLITE3_INTEGER);
    $stmt->bindValue(':offset', $offset, SQLITE3_INTEGER);
    $res = $stmt->execute();
    $posts = [];
    while ($res && ($r = $res->fetchArray(SQLITE3_ASSOC))) {
        $posts[] = [
            'id' => (int)$r['id'],
            'name' => $r['author_name'] ?? '',
            'trip' => $r['author_trip'] ?? '',
            'body' => $r['body'] ?? '',
            'created_at' => (int)($r['created_at'] ?? 0),
        ];
    }
    echo json_encode([
        'ok' => true,
        'posts' => $posts,
        'total' => $total,
        'limit' => $limit,
        'offset' => $offset,
        'has_more' => $offset + $limit < $total,
    ], JSON_UNESCAPED_UNICODE);
}

function handle_bbs_post(DbCompat $db): void
{
    ensure_bbs_table($db);
    $raw = file_get_contents('php://input');
    $in = json_decode($raw ?: '', true);
    if (!is_array($in)) $in = $_POST;

    $name = sanitize_plain_text((string)($in['name'] ?? ''), 30);
    $body = sanitize_plain_text((string)($in['body'] ?? ''), 100);
    $tripKey = sanitize_plain_text((string)($in['trip_key'] ?? ''), 100);
    $trip = $tripKey !== '' ? calc_trip_from_key($tripKey) : '';

    if ($name === '') {
        echo json_encode(['ok' => false, 'error' => '名前を入力してください'], JSON_UNESCAPED_UNICODE);
        return;
    }
    if ($body === '') {
        echo json_encode(['ok' => false, 'error' => '本文を入力してください'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $stmt = $db->prepare("
        INSERT INTO bbs_posts (body, author_name, author_trip, created_at, deleted)
        VALUES (:body, :name, :trip, :created_at, 0)
    ");
    $stmt->bindValue(':body', $body, SQLITE3_TEXT);
    $stmt->bindValue(':name', $name, SQLITE3_TEXT);
    $stmt->bindValue(':trip', $trip, SQLITE3_TEXT);
    $stmt->bindValue(':created_at', time(), SQLITE3_INTEGER);
    $ok = $stmt->execute();
    if (!$ok) {
        echo json_encode(['ok' => false, 'error' => '投稿に失敗しました'], JSON_UNESCAPED_UNICODE);
        return;
    }
    echo json_encode(['ok' => true, 'trip' => $trip], JSON_UNESCAPED_UNICODE);
}



function respondRankFromCache(DbCompat $db, string $kind, ?string $keyType = null): void
{
    $ym     = $_GET['ym']     ?? '';
    $limit  = (int)($_GET['limit']  ?? 100);
    $offset = (int)($_GET['offset'] ?? 0);

    if ($limit <= 0 || $limit > 1000) $limit = 100;
    if ($offset < 0) $offset = 0;

    if ($ym === '') {
        echo json_encode(['ok' => false, 'error' => 'ym が指定されていません'], JSON_UNESCAPED_UNICODE);
        return;
    }

    if ($ym === 'all') {
        $scope = 'all';
    } elseif (preg_match('/^\d{4}-\d{2}$/', $ym)) {
        $scope = 'month';
    } elseif (preg_match('/^\d{4}$/', $ym)) {
        $scope = 'year';
    } else {
        echo json_encode(['ok' => false, 'error' => 'ym の形式が不正です'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $sql = "
        SELECT payload, total
          FROM rank_cache_json
         WHERE scope = :scope
           AND ym    = :ym
           AND kind  = :kind
           AND (
                (:ktype IS NULL AND key_type IS NULL)
             OR key_type = :ktype
           )
         LIMIT 1
    ";
    $stmt = $db->prepare($sql);
    $stmt->bindValue(':scope', $scope, SQLITE3_TEXT);
    $stmt->bindValue(':ym',    $ym,    SQLITE3_TEXT);
    $stmt->bindValue(':kind',  $kind,  SQLITE3_TEXT);
    if ($keyType === null) {
        $stmt->bindValue(':ktype', null, SQLITE3_NULL);
    } else {
        $stmt->bindValue(':ktype', $keyType, SQLITE3_TEXT);
    }

    $res = $stmt->execute();
    $row = $res ? $res->fetchArray(SQLITE3_ASSOC) : false;

    if ($row) {
        $payloadJson = $row['payload'] ?? '{}';
        $payload     = json_decode($payloadJson, true);
        if (!is_array($payload) || !isset($payload['rows'])) {
            echo json_encode(['ok' => false, 'error' => 'キャッシュ形式が不正です'], JSON_UNESCAPED_UNICODE);
            return;
        }
        $allRows = $payload['rows'] ?? [];
        $total   = (int)($payload['total'] ?? $row['total'] ?? count($allRows));
        $rows = array_slice($allRows, $offset, $limit);

        echo json_encode([
            'ok'    => true,
            'rows'  => $rows,
            'total' => $total,
            'limit' => $limit,
            'offset'=> $offset,
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    // フォールバック（リアルタイム集計）
    $result = null;

    switch ($kind) {
        case 'games':
            if ($keyType === 'name') {
                $result = RankingLib::buildMonthlyNameGames($db, $ym);
            } elseif ($keyType === 'trip') {
                $result = RankingLib::buildMonthlyTripGames($db, $ym);
            }
            break;

        case 'win_rate':
            $minGames = max(1, (int)($_GET['min_games'] ?? 30));
            if ($keyType === 'name') {
                $result = RankingLib::buildMonthlyNameWinRate($db, $ym, ['min_games' => $minGames]);
            } elseif ($keyType === 'trip') {
                $result = RankingLib::buildMonthlyTripWinRate($db, $ym, ['min_games' => $minGames]);
            }
            break;

        case 'talk':
            $minLiveDays = max(1, (int)($_GET['min_live_days'] ?? 10));
            $result = RankingLib::buildMonthlyTalk($db, $ym, ['min_live_days' => $minLiveDays]);
            break;

        case 'days':
            $result = RankingLib::buildMonthlyDays($db, $ym);
            break;

        case 'role_win':
            $minGames = max(1, (int)($_GET['min_games'] ?? 30));
            $result = RankingLib::buildMonthlyRoleWin($db, $ym, ['min_games' => $minGames]);
            break;

        case 'haiyaku_win':
            $minPlayers = max(1, (int)($_GET['min_players'] ?? 100));
            $result = RankingLib::buildMonthlyHaiyakuWin($db, $ym, ['min_players' => $minPlayers]);
            break;

        case 'village':
            $result = RankingLib::buildMonthlyVillage($db, $ym);
            break;

        case 'haiyaku':
            $result = RankingLib::buildMonthlyHaiyaku($db, $ym);
            break;

        case 'rm':
            $result = RankingLib::buildMonthlyRm($db, $ym);
            break;

        case 'tsuiho':
            $result = RankingLib::buildMonthlyTsuiho($db, $ym);
            break;
    }

    if ($result === null) {
        echo json_encode(['ok'=>false, 'error'=>'フォールバック未実装'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $allRows = $result['rows']  ?? [];
    $total   = $result['total'] ?? count($allRows);
    $rows = array_slice($allRows, $offset, $limit);

    echo json_encode([
        'ok'    => true,
        'rows'  => $rows,
        'total' => $total,
        'limit' => $limit,
        'offset'=> $offset,
    ], JSON_UNESCAPED_UNICODE);
}

function handle_stats_by_class(DbCompat $db): void
{
    $ym = $_GET['ym'] ?? 'all';
    $where = '';
    $bind = null;

    if ($ym === 'all') {
        $scope = 'all';
    } elseif (preg_match('/^\d{4}-\d{2}$/', $ym)) {
        $scope = 'month';
        $where = "AND to_char(to_timestamp(g.started_at) AT TIME ZONE 'Asia/Tokyo', 'YYYY-MM') = :ym";
        $bind = $ym;
    } elseif (preg_match('/^\d{4}$/', $ym)) {
        $scope = 'year';
        $where = "AND to_char(to_timestamp(g.started_at) AT TIME ZONE 'Asia/Tokyo', 'YYYY') = :ym";
        $bind = $ym;
    } else {
        echo json_encode(['ok' => false, 'error' => 'ym の形式が不正です'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $stmt = $db->prepare("SELECT payload FROM rank_cache_json WHERE cache_key = :cache_key LIMIT 1");
    if ($stmt) {
        $stmt->bindValue(':cache_key', 'class_stats:' . $ym, SQLITE3_TEXT);
        $res = $stmt->execute();
        $row = $res ? $res->fetchArray(SQLITE3_ASSOC) : false;
        if ($row && !empty($row['payload'])) {
            $payload = json_decode((string)$row['payload'], true);
            if (is_array($payload)) {
                echo json_encode(['ok' => true] + $payload, JSON_UNESCAPED_UNICODE);
                return;
            }
        }
    }

    $sql = "
        SELECT COALESCE(g.haiyaku_class, '通常') AS cls,
               COUNT(*) AS games,
               COALESCE(SUM(p.win), 0) AS wins
          FROM players p
          JOIN games g ON g.id = p.game_id
         WHERE p.is_spectator = 0 {$where}
         GROUP BY COALESCE(g.haiyaku_class, '通常')
    ";
    $stmt = $db->prepare($sql);
    if (!$stmt) {
        echo json_encode(['ok' => false, 'error' => 'SQL準備に失敗しました'], JSON_UNESCAPED_UNICODE);
        return;
    }
    if ($bind !== null) $stmt->bindValue(':ym', $bind, SQLITE3_TEXT);
    $res = $stmt->execute();

    $result = [
        'all' => ['games' => 0, 'wins' => 0, 'win_rate' => 0.0],
        'normal' => ['games' => 0, 'wins' => 0, 'win_rate' => 0.0],
        'chaos' => ['games' => 0, 'wins' => 0, 'win_rate' => 0.0],
        'ym' => $ym,
        'scope' => $scope,
    ];
    while ($res && ($r = $res->fetchArray(SQLITE3_ASSOC))) {
        $games = (int)($r['games'] ?? 0);
        $wins = (int)($r['wins'] ?? 0);
        $key = (($r['cls'] ?? '通常') === '通常') ? 'normal' : 'chaos';
        $result[$key]['games'] += $games;
        $result[$key]['wins'] += $wins;
        $result['all']['games'] += $games;
        $result['all']['wins'] += $wins;
    }
    foreach (['all', 'normal', 'chaos'] as $key) {
        $g = $result[$key]['games'];
        $w = $result[$key]['wins'];
        $result[$key]['win_rate'] = $g > 0 ? round($w / $g * 100, 1) : 0.0;
    }

    echo json_encode(['ok' => true] + $result, JSON_UNESCAPED_UNICODE);
}


function handle_village_recent(DbCompat $db): void
{
    $limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 100;
    if ($limit <= 0 || $limit > 200) {
        $limit = 100;
    }

    $sql = "
        SELECT
            g.id,
            g.log_id,
            g.village_name,
            g.started_at,
            g.haiyaku
        FROM games g
        JOIN (
            SELECT
                village_name,
                MAX(started_at) AS max_started_at
            FROM games
            GROUP BY village_name
        ) latest
          ON latest.village_name  = g.village_name
         AND latest.max_started_at = g.started_at
        ORDER BY g.started_at DESC
        LIMIT :limit
    ";

    $stmt = $db->prepare($sql);
    if (!$stmt) {
        json_error('SQL_PREPARE_ERROR_VILLAGE_RECENT: ' . $db->lastErrorMsg(), 500);
    }

    $stmt->bindValue(':limit', $limit, SQLITE3_INTEGER);

    $res = $stmt->execute();
    if (!$res) {
        json_error('SQL_EXEC_ERROR_VILLAGE_RECENT: ' . $db->lastErrorMsg(), 500);
    }

    $rows = [];
    while ($r = $res->fetchArray(SQLITE3_ASSOC)) {
        $rows[] = [
            'id'           => (int)$r['id'],
            'log_id'       => (int)$r['log_id'],
            'village_name' => $r['village_name'],
            'started_at'   => (int)$r['started_at'],
            'haiyaku'      => $r['haiyaku'],
        ];
    }
    $res->finalize();
    $stmt->close();

    echo json_encode([
        'ok'    => true,
        'limit' => $limit,
        'rows'  => $rows,
    ], JSON_UNESCAPED_UNICODE);
}


function handle_rating_rank(DbCompat $db): void
{
    $limit    = isset($_GET['limit'])     ? (int)$_GET['limit']     : 100;
    $offset   = isset($_GET['offset'])    ? (int)$_GET['offset']    : 0;
    $minGames = isset($_GET['min_games']) ? (int)$_GET['min_games'] : 20;

    if ($limit <= 0 || $limit > 500) $limit = 100;
    if ($offset < 0) $offset = 0;
    if ($minGames < 0) $minGames = 0;

    $sqlCount = <<<SQL
SELECT COUNT(*) AS cnt
  FROM player_ratings
 WHERE games >= :min_games
SQL;
    $stmt = $db->prepare($sqlCount);
    if (!$stmt) {
        echo json_encode(['ok' => false, 'error' => 'SQL準備に失敗しました(count)'], JSON_UNESCAPED_UNICODE);
        return;
    }
    $stmt->bindValue(':min_games', $minGames, SQLITE3_INTEGER);
    $res = $stmt->execute();
    if (!$res) {
        echo json_encode(['ok' => false, 'error' => 'SQL実行に失敗しました(count)'], JSON_UNESCAPED_UNICODE);
        return;
    }
    $row   = $res->fetchArray(SQLITE3_ASSOC);
    $total = (int)($row['cnt'] ?? 0);
    $res->finalize();

    if ($total === 0) {
        echo json_encode([
            'ok'    => true,
            'total' => 0,
            'limit' => $limit,
            'offset'=> $offset,
            'rows'  => [],
        ], JSON_UNESCAPED_UNICODE);
        return;
    }

    $sql = <<<SQL
SELECT
    trip,
    rating,
    games,
    main_name
FROM
    player_ratings
WHERE
    games >= :min_games
ORDER BY
    rating DESC,
    games  DESC,
    trip   ASC
LIMIT :limit OFFSET :offset
SQL;

    // PostgreSQLではbusy_timeoutは不要
    $stmt = $db->prepare($sql);
    if (!$stmt) {
        echo json_encode(['ok' => false, 'error' => 'SQL準備に失敗しました(list)'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $stmt->bindValue(':min_games', $minGames, SQLITE3_INTEGER);
    $stmt->bindValue(':limit',     $limit,    SQLITE3_INTEGER);
    $stmt->bindValue(':offset',    $offset,   SQLITE3_INTEGER);

    $res = $stmt->execute();
    if (!$res) {
        echo json_encode(['ok' => false, 'error' => 'SQL実行に失敗しました(list)'], JSON_UNESCAPED_UNICODE);
        return;
    }

    $rows = [];
    while ($r = $res->fetchArray(SQLITE3_ASSOC)) {
        $rows[] = [
            'trip'      => $r['trip'],
            'name'      => $r['main_name'] ?? '',
            'main_name' => $r['main_name'] ?? null,
            'rating'    => (float)$r['rating'],
            'games'     => (int)$r['games'],
        ];
    }
    $res->finalize();

    echo json_encode([
        'ok'    => true,
        'total' => $total,
        'limit' => $limit,
        'offset'=> $offset,
        'rows'  => $rows,
    ], JSON_UNESCAPED_UNICODE);
}

// ─── 割れ酉判定 ───
function handle_shared_trips(DbCompat $db): void {
    $raw   = $_GET['trips'] ?? '';
    $trips = array_values(array_filter(array_map('trim', explode("\n", str_replace("\r\n", "\n", $raw)))));
    if (!$trips) {
        echo json_encode(['ok' => true, 'shared_trips' => []], JSON_UNESCAPED_UNICODE);
        return;
    }
    // shared_tripsテーブルから対象tripを検索
    $placeholders = implode(',', array_fill(0, count($trips), '?'));
    $sql  = "SELECT trip FROM shared_trips WHERE trip IN ($placeholders)";
    $stmt = $db->prepare($sql);
    foreach ($trips as $i => $t) {
        $stmt->bindValue($i + 1, $t, SQLITE3_TEXT);
    }
    $res  = $stmt->execute();
    $found = [];
    while ($r = $res->fetchArray(SQLITE3_ASSOC)) {
        $found[] = $r['trip'];
    }
    echo json_encode(['ok' => true, 'shared_trips' => $found], JSON_UNESCAPED_UNICODE);
}

// ─── RM個別サマリー ───
function handle_rm_summary(DbCompat $db): void {
    $rm = trim($_GET['name'] ?? '');
    if (!$rm) {
        echo json_encode(['ok' => false, 'error' => 'name required'], JSON_UNESCAPED_UNICODE);
        return;
    }
    $total = (int)$db->querySingle("SELECT COUNT(*) FROM games WHERE rm_name=?", false, [$rm]);
    if (!$total) {
        echo json_encode(['ok' => true, 'found' => false, 'rm_name' => $rm], JSON_UNESCAPED_UNICODE);
        return;
    }
    // 最近50件
    $stmt = $db->prepare("SELECT log_id, started_at, village_name, haiyaku,
        (SELECT COUNT(*) FROM players p WHERE p.game_id=g.id AND p.is_spectator=0) AS player_count
        FROM games g WHERE g.rm_name=? ORDER BY g.started_at DESC LIMIT 50");
    $stmt->bindValue(1, $rm, SQLITE3_TEXT);
    $res = $stmt->execute();
    $games = [];
    while ($r = $res->fetchArray(SQLITE3_ASSOC)) {
        $ts = (int)$r['started_at'];
        $ym = $ts ? date('Y-m', $ts) : null;
        $games[] = [
            'log_id'       => (int)$r['log_id'],
            'village_name' => $r['village_name'],
            'haiyaku'      => $r['haiyaku'],
            'player_count' => (int)$r['player_count'],
            'first_ym'     => $ym,
        ];
    }
    // 村名集計TOP20
    $vstmt = $db->prepare("SELECT village_name, COUNT(*) AS cnt FROM games WHERE rm_name=? GROUP BY village_name ORDER BY cnt DESC LIMIT 20");
    $vstmt->bindValue(1, $rm, SQLITE3_TEXT);
    $vres = $vstmt->execute();
    $villages = [];
    while ($r = $vres->fetchArray(SQLITE3_ASSOC)) {
        $villages[] = ['name' => $r['village_name'], 'count' => (int)$r['cnt']];
    }
    // 初登場
    $firstTs = (int)$db->querySingle("SELECT MIN(started_at) FROM games WHERE rm_name=?", false, [$rm]);
    $firstYm = $firstTs ? date('Y-m', $firstTs) : null;

    echo json_encode([
        'ok'          => true,
        'found'       => true,
        'rm_name'     => $rm,
        'total_games' => $total,
        'first_ym'    => $firstYm,
        'village_top' => $villages,
        'recent_games'=> $games,
    ], JSON_UNESCAPED_UNICODE);
}

// ─── 割れ酉件数 ───
function handle_shared_trips_count(DbCompat $db): void {
    $count = (int)$db->querySingle("SELECT COUNT(*) FROM shared_trips");
    echo json_encode(['ok' => true, 'count' => $count], JSON_UNESCAPED_UNICODE);
}
