<?php
declare(strict_types=1);

mb_internal_encoding('UTF-8');

const ZINRO_LOG_URL      = 'https://zinro.net/m/log.php?id=';
const ZINRO_ROOMLIST_URL = 'https://zinro.net/m/room_list.php?scene=%E7%B5%82%E4%BA%86';
const ZINRO_UA           = 'SaPuRi - Kaiseki-Senseki';

// -------------------------------------------------
// PostgreSQL 接続設定 (環境に合わせて変更してください)
// -------------------------------------------------
define('PG_HOST', getenv('PG_HOST') ?: 'localhost');
define('PG_PORT', getenv('PG_PORT') ?: '5432');
define('PG_DBNAME', getenv('PG_DBNAME') ?: 'zinro_meta');
define('PG_USER', getenv('PG_USER') ?: 'zinro');
define('PG_PASSWORD', getenv('PG_PASSWORD') ?: '');

// -------------------------------------------------
// CLIオプション
// -------------------------------------------------
$options = getopt('', [
    'from::',  // 省略可
]);
$fromIdOpt = isset($options['from']) ? (int)$options['from'] : null;

// -------------------------------------------------
// DB接続（PDO / PostgreSQL）
// -------------------------------------------------
try {
    $dsn = "pgsql:host=" . PG_HOST . ";port=" . PG_PORT . ";dbname=" . PG_DBNAME;
    $pdo = new PDO($dsn, PG_USER, PG_PASSWORD);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // SQLite特有の PRAGMA は削除
} catch (PDOException $e) {
    fwrite(STDERR, "DB接続エラー: " . $e->getMessage() . PHP_EOL);
    exit(1);
}

// -------------------------------------------------
// テーブル作成（PostgreSQL対応）
// -------------------------------------------------
// AUTOINCREMENT -> SERIAL または BIGSERIAL
// INTEGER(Time) -> BIGINT (2038年問題対策)
// INDEX -> PostgreSQLでも構文はほぼ同じ
$pdo->exec('
CREATE TABLE IF NOT EXISTS games (
  id            SERIAL PRIMARY KEY,
  log_id        INTEGER NOT NULL,
  game_index    INTEGER NOT NULL,
  created_at    BIGINT NOT NULL,
  started_at    BIGINT,
  finished_at   BIGINT,
  village_name  TEXT    NOT NULL,
  haiyaku       TEXT    NOT NULL,
  rm_name       TEXT,
  game_time     INTEGER,
  wait_time     INTEGER,
  review_time   INTEGER,
  tsuiho_json   TEXT,
  raw_line      TEXT,
  chat_hash     TEXT
);
');
$pdo->exec('ALTER TABLE games ADD COLUMN IF NOT EXISTS chat_hash TEXT');

$pdo->exec('CREATE INDEX IF NOT EXISTS idx_games_log_id       ON games(log_id)');
$pdo->exec('CREATE INDEX IF NOT EXISTS idx_games_created_at   ON games(created_at)');
$pdo->exec('CREATE INDEX IF NOT EXISTS idx_games_village_name ON games(village_name)');
$pdo->exec('CREATE INDEX IF NOT EXISTS idx_games_rm_name      ON games(rm_name)');
$pdo->exec('CREATE INDEX IF NOT EXISTS idx_games_haiyaku      ON games(haiyaku)');

$pdo->exec('
CREATE TABLE IF NOT EXISTS players (
  id            SERIAL PRIMARY KEY,
  game_id       INTEGER NOT NULL,
  log_id        INTEGER NOT NULL,
  name          TEXT    NOT NULL,
  trip          TEXT,
  role          TEXT,
  win           INTEGER NOT NULL,
  talk_count    INTEGER,
  survive_days  INTEGER,
  is_spectator  INTEGER NOT NULL DEFAULT 0,
  tsuiho_flag   INTEGER NOT NULL DEFAULT 0,
  created_at    BIGINT NOT NULL,
  FOREIGN KEY (game_id) REFERENCES games(id)
);
');

$pdo->exec('CREATE INDEX IF NOT EXISTS idx_players_game   ON players(game_id)');
$pdo->exec('CREATE INDEX IF NOT EXISTS idx_players_log    ON players(log_id)');
$pdo->exec('CREATE INDEX IF NOT EXISTS idx_players_trip   ON players(trip)');
$pdo->exec('CREATE INDEX IF NOT EXISTS idx_players_name   ON players(name)');


// -------------------------------------------------
// fromId 決定
// -------------------------------------------------
if ($fromIdOpt !== null) {
    $fromId = $fromIdOpt;
} else {
    $stmt = $pdo->query("SELECT MAX(log_id) AS max_id FROM games");
    // PostgreSQLでデータがない場合 NULL が返るので 0 キャスト
    $max = (int)($stmt->fetchColumn() ?? 0);
    $fromId = $max + 1;
}

// -------------------------------------------------
// ToId（shuryo）取得：終了ページから
// -------------------------------------------------
$toId = fetchLatestLogIdFromRoomList();
if ($toId === null || $toId < $fromId) {
    // 初回実行時などでDBが空($fromId=1)かつ、最新ログ取得失敗のケースなどを考慮
    if ($toId === null) {
        fwrite(STDERR, "終了ページから有効な最終IDが取得できませんでした。" . PHP_EOL);
        exit(1);
    }
    // 既に最新まで取り込み済みの場合
    echo "新しいログはありません (Current Max: {$fromId}, Web Max: {$toId})" . PHP_EOL;
    exit(0);
}

echo "=== ZINRO batch (PostgreSQL) ===" . PHP_EOL;
echo "範囲: {$fromId} ～ {$toId}" . PHP_EOL;

// -------------------------------------------------
// メインループ
// -------------------------------------------------
for ($id = $fromId; $id <= $toId; $id++) {

    echo "[GET ] log_id={$id} ... ";

    $html = fetchLogHtml($id);
    if ($html === null) {
        echo "取得失敗" . PHP_EOL;
        goto _NEXT;
    }

    if (mb_strpos($html, '指定されたログは存在しません') !== false) {
        echo "存在しないログ" . PHP_EOL;
        goto _NEXT;
    }

    $parsed = parseZinroLog($id, $html);
    if ($parsed === null || empty($parsed['games'])) {
        echo "解析対象のゲームなし" . PHP_EOL;
        goto _NEXT;
    }

    try {
        $pdo->beginTransaction();
        foreach ($parsed['games'] as $g) {

            // chat_hash で重複チェック（同一内容のゲームを二重登録しない）
            $chatHash = hash('sha256', $g['raw_line'] ?? '');
            if ($chatHash) {
                $chk = $pdo->prepare("SELECT COUNT(*) FROM games WHERE chat_hash = ?");
                $chk->execute([$chatHash]);
                if ((int)$chk->fetchColumn() > 0) {
                    echo "[SKIP] log_id={$g['log_id']} game_index={$g['game_index']} (hash重複)" . PHP_EOL;
                    continue;
                }
            }
            $g['chat_hash'] = $chatHash;

            // games に INSERT
            $gameId = insertGameRow($pdo, $g);

            // players に INSERT
            foreach ($parsed['players'] as $p) {
                if ($p['log_id'] === $g['log_id'] && $p['game_index'] === $g['game_index']) {
                    insertPlayerRow($pdo, $gameId, $p);
                }
            }
        }
        $pdo->commit();
        echo "OK" . PHP_EOL;

    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        echo "DBエラー: " . $e->getMessage() . PHP_EOL;
    }

_NEXT:

    // === 更新時刻をファイルに書き出す ===
    $tsFile = __DIR__ . '/updatelog.txt';
    file_put_contents($tsFile, (string)time());
    @chmod($tsFile, 0644);

    usleep(2000000); // 2秒待機
}


// =================================================
//  関数群
// =================================================

/**
 * games に既にこの log_id があるか？
 */
function existsLogAlready(PDO $pdo, int $logId): bool
{
    $stmt = $pdo->prepare("SELECT 1 FROM games WHERE log_id = :id LIMIT 1");
    $stmt->execute([':id' => $logId]);
    return (bool)$stmt->fetchColumn();
}

/**
 * 終了ページから最新ログID(shuryo) を取る。
 */
function fetchLatestLogIdFromRoomList(): ?int
{
    $header = [
        "User-Agent: " . ZINRO_UA,
        "Content-Type: application/x-www-form-urlencoded",
    ];

    $context = stream_context_create([
        'http' => [
            'method'  => 'GET',
            'header'  => implode("\r\n", $header),
            'timeout' => 10,
        ],
    ]);

    $contents = @file(ZINRO_ROOMLIST_URL, flags: 0, context: $context);

    if ($contents === false || !is_array($contents)) {
        fwrite(STDERR, "[ERR ] room_list.php 取得失敗" . PHP_EOL);
        return null;
    }

    $shuryo = null;
    foreach ($contents as $line) {
        if (strpos($line, 'confirmDialog') !== false && strpos($line, 'id=') !== false) {
            if (preg_match('/id=(\d+)/', $line, $m)) {
                $shuryo = (int)$m[1];
                break;
            }
        }
    }

    if (!$shuryo) {
        fwrite(STDERR, "[ERR ] confirmDialog 行から id= が見つからない" . PHP_EOL);
        return null;
    }

    echo "最新ログID(shuryo): {$shuryo}" . PHP_EOL;
    return $shuryo;
}

/**
 * ログHTML取得（curl）
 */
function fetchLogHtml(int $logId): ?string
{
    $url = ZINRO_LOG_URL . $logId;

    $ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_TIMEOUT        => 15,
        CURLOPT_USERAGENT      => ZINRO_UA,
    ]);

    $body = curl_exec($ch);
    if ($body === false) {
        curl_close($ch);
        return null;
    }
    $code = curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
    curl_close($ch);

    if ($code >= 400) {
        return null;
    }
    return $body;
}

/**
 * HTML → games / players 配列に分解
 * (ロジックは変更なし)
 */
function parseZinroLog(int $logId, string $html): ?array
{
    // ... (省略せずに元のロジックをそのまま使用) ...
    // 元のコードが非常に長いため、ロジック部分は元のファイルの関数内と同じです。
    // 変数名や処理フローに変更はありません。
    
    // 省略：変数初期化
    $gametime = $totsushi = $uranaiwolf = $uranaifox = $karyudogj = $kami
        = $shohai = $gamemachijikan = $datekansousen = $namestats = $sansens
        = $kansens = $nametrip = $jinei = $haiyakus = $arashi = $tsuiho
        = $hayagami = $miuchigami = $hatsugen = $seizonnissuu
        = $data = $comment = $jikkouuranai = $match = $jikkoukaryudo
        = $seizon = $com = $chuyacount = [];

    $chudan = 0;
    $datemake = 0;

    $muramake = $roomname = $chuya = $date = $datesakusei = $jikkoudatepre
        = $datestart = $datestop = $tmp = $jikkoudatenow = $jikkoutime
        = $dateend = "";

    // HTML → 行配列
    $datapre = $html;
    $data    = explode("\n", $datapre);

    $j       = 1;
    $err     = "";
    $flagreg = 0;
    $nametrip = [];

    foreach ($data as $x) {
        // 公開されていないかどうか判別
        if ($j === 77) {
            if (trim($x) === "公開されていない村です<br />") {
                $flagreg = 0;
                $err    .= "not koukai.";
            } else {
                $flagreg = 1;
            }
        }

        // 公開されていた場合の処理
        if ($flagreg > 0) {
            // ログのメッセージを取得（JSON）
            if ($j === 160) {
                $line = str_replace(["\r", "\n"], "", $x);
                $line = trim($line);
                $line = str_replace("var message = ", "", $line);
                $line = rtrim($line, ";");
                $comment = json_decode($line, true);

                if (
                    is_array($comment) &&
                    isset($comment[0]["from_user"], $comment[0]["message"]) &&
                    $comment[0]["from_user"] === "鯖"
                ) {
                    if (!mb_strpos($comment[0]["message"], "村を作成しました")) {
                        krsort($comment);
                    }
                }
            }

            // 参加者・観戦者の記録取得
            $flag = 0;
            if ($j > 210) {
                $a = explode('"', $x);
                $b = explode("'", $x);
                if (($a[1] ?? '') === "modal") {
                    $trip = str_replace("トリップ：", "", $b[1] ?? '');
                }
                if (mb_ereg("</span>", $x)) {
                    $x    = "<" . $x;
                    $name = trim(strip_tags($x));
                    $flag = 1;
                }
                if ($flag > 0) {
                    if (!empty($name)) {
                        $nametrip[$name] = $trip ?? '';
                    }
                    $flag = 0;
                }
            }
        }
        $j++;
    }

    if (!$flagreg || !is_array($comment) || count($comment) === 0) {
        return null;
    }

    // コメント解析
    $game      = -1;
    $c         = 0;
    $cc        = 0;
    $chudan    = 0;
    $precom    = "";
    $reg       = [];
    $anata     = 0;
    $chuya     = "夜";
    $datestart = 0;
    $datesakusei = 0;

    foreach ($comment as $com) {
        $c++;
        if (isset($com["from_user"]) && $com["from_user"] === "鯖") {
            $cc++;
            $currentComKey = ($com["message"] ?? '') . ($com["to_user"] ?? '');
            if ($currentComKey === $precom) {
                continue;
            }
            $precom = $currentComKey;

            if ($cc === 1) {
                $roomname = mb_convert_kana($com["room_name"] ?? '', "n");
                $date     = $com["created"] ?? '';
                $datemake = new DateTime($date, new DateTimeZone('Asia/Tokyo'));
                $datesakusei = $datemake->format('U');
            }

            if ($roomname === "") {
                $roomname = mb_convert_kana($com["room_name"] ?? '', "n");
                $date     = $com["created"] ?? '';
                $datemake = new DateTime($date, new DateTimeZone('Asia/Tokyo'));
                $datesakusei = $datemake->format('U');
            }

            if (isset($com["message"]) && mb_ereg("あなたは【", $com["message"])) {
                $anata++;
                $date    = $com["created"] ?? '';
                $dateObj = new DateTime($date, new DateTimeZone('Asia/Tokyo'));
                $jikkoudatepre = $dateObj->format('U');
            }

            if ($anata === 1) {
                $date    = $com["created"] ?? '';
                $dateObj = new DateTime($date, new DateTimeZone('Asia/Tokyo'));
                $datestart = $dateObj->format('U');
                $datekansousen[$game] = $datestart - $datesakusei;

                $game++;
                $totsushi[$game] = 0;
                for ($i = 1; $i <= 19; $i++) { ${"y$i"} = 0; }
                $all   = 0;
                $allsu = 0;
                $haiyaku = "";
                $chuyacount[$game] = 0;
                unset($uranaifox[$game], $uranaiwolf[$game], $jikkouuranai[$game], $jikkoukaryudo[$game]);

                if (isset($seizon[$game]) && is_array($seizon[$game])) {
                    foreach ($seizon[$game] as $k => $x) {
                        $hatsugen[$game][$k]     = 0;
                        $seizonnissuu[$game][$k] = 0;
                    }
                }
            }

            if (isset($com["message"]) && str_contains($com["message"], "村を作成しました")) {
                $x        = explode("さんが村を作成し", $com["message"]);
                $muramake = $x[0] ?? '';
            }

            $message = $com["message"] ?? '';
            $toUser  = $com["to_user"] ?? '';

            if (preg_match('/あなたは【/', $message)) {
                $yaku = "---";
                $jin  = 0;
                $roles = [
                    ['人狼',   '人狼',   1, 'y1'],
                    ['狂人',   '狂人',   1, 'y2'],
                    ['狂信者', '狂信者', 1, 'y3'],
                    ['占い師', '占い師', 2, 'y4'],
                    ['狩人',   '狩人',   2, 'y5'],
                    ['霊能者', '霊能者', 2, 'y6'],
                    ['猫又',   '猫又',   2, 'y7'],
                    ['共有者', '共有者', 2, 'y8'],
                    ['役人',   '役人',   2, 'y9'],
                    ['怪盗',   '怪盗',   2, 'y10'],
                    ['狼憑き', '狼憑き', 2, 'y11'],
                    ['ものまね','ものまね',2,'y12'],
                    ['村人',   '村人',   2, 'y13'],
                    ['妖狐',   '妖狐',   3, 'y14'],
                    ['背徳者', '背徳者', 3, 'y15'],
                    ['てるてる','てるてる',4,'y16'],
                    ['恋人',   '恋人',   5, 'y17'],
                    ['パン屋', 'パン屋', 2, 'y18'],
                    ['黒猫',   '黒猫',   1, 'y19'],
                ];
                foreach ($roles as [$keyword, $roleName, $teamId, $counter]) {
                    if (str_contains($message, "あなたは【{$keyword}")) {
                        $yaku = $roleName;
                        $jin  = $teamId;
                        if (!isset($$counter)) $$counter = 0;
                        $$counter++;
                        $all++;
                        break;
                    }
                }
                $namestats[$game][$toUser] = $yaku;
                $jinei[$game][$toUser]     = $jin;
                $seizon[$game][$toUser]    = 1;
            }

            if (isset($com["message"]) && preg_match('/ゲームを開始します/', $com["message"])) {
                $haiyaku = '';
                $allsu   = 0;
                // 配役判定（元のコードと同じ）
                if (($y1 ?? 0) == 1 && ($y2 ?? 0) == 1 && ($y13 ?? 0) == 2 && $all == 4) { $haiyaku = "夏至配役"; $allsu = 4; }
                elseif (($y1 ?? 0) == 1 && ($y16 ?? 0) == 16 && ($y13 ?? 0) == 2 && $all == 4) { $haiyaku = "ルアー配役"; $allsu = 4; }
                elseif (($y1 ?? 0) == 1 && ($y13 ?? 0) == 3 && $all == 4) { $haiyaku = "4A"; $allsu = 4; }
                elseif (($y1 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y13 ?? 0) == 2 && $all == 4) { $haiyaku = "4A占"; $allsu = 4; }
                elseif (($y1 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y13 ?? 0) == 4 && $all == 6) { $haiyaku = "6A"; $allsu = 6; }
                elseif (($y1 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y13 ?? 0) == 3 && ($y14 ?? 0) == 1 && $all == 6) { $haiyaku = "6B"; $allsu = 6; }
                elseif (($y1 ?? 0) == 1 && ($y2 ?? 0) == 3 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && $all == 6) { $haiyaku = "6C"; $allsu = 6; }
                elseif (($y1 ?? 0) == 1 && ($y2 ?? 0) == 2 && ($y7 ?? 0) == 1 && ($y14 ?? 0) == 1 && ($y13 ?? 0) == 1 && $all == 6) { $haiyaku = "メチ配役"; $allsu = 6; }
                elseif (($y1 ?? 0) == 2 && ($y2 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y8 ?? 0) == 2 && ($y13 ?? 0) == 1 && $all == 8) { $haiyaku = "8CFO"; $allsu = 8; }
                elseif (($y1 ?? 0) == 2 && ($y2 ?? 0) == 2 && ($y4 ?? 0) == 2 && ($y6 ?? 0) == 2 && $all == 8) { $haiyaku = "8CNO（おはよーぐる配役）"; $allsu = 8; }
                elseif (($y1 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y14 ?? 0) == 1 && ($y15 ?? 0) == 2 && ($y13 ?? 0) == 4 && $all == 9) { $haiyaku = "9D"; $allsu = 9; }
                elseif (($y1 ?? 0) == 2 && ($y2 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y13 ?? 0) == 4 && $all == 10) { $haiyaku = "10A狩"; $allsu = 10; }
                elseif (($y1 ?? 0) == 3 && ($y2 ?? 0) == 1 && ($y4 ?? 0) == 2 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y14 ?? 0) == 1 && ($y7 ?? 0) == 1 && $all == 10) { $haiyaku = "10C猫"; $allsu = 10; }
                elseif (($y1 ?? 0) == 3 && ($y2 ?? 0) == 1 && ($y4 ?? 0) == 2 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y14 ?? 0) == 1 && ($y7 ?? 0) == 1 && ($y13 ?? 0) == 1 && $all == 11) { $haiyaku = "11C猫"; $allsu = 11; }
                elseif (($y1 ?? 0) == 2 && ($y2 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y13 ?? 0) == 5 && $all == 11) { $haiyaku = "11A"; $allsu = 11; }
                elseif (($y1 ?? 0) == 3 && ($y2 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y7 ?? 0) == 1 && ($y13 ?? 0) == 4 && $all == 12) { $haiyaku = "12A猫"; $allsu = 12; }
                elseif (($y1 ?? 0) == 2 && ($y2 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y13 ?? 0) == 5 && ($y14 ?? 0) == 1 && $all == 12) { $haiyaku = "12B"; $allsu = 12; }
                elseif (($y1 ?? 0) == 3 && ($y2 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y13 ?? 0) == 2 && ($y14 ?? 0) == 1 && ($y15 ?? 0) == 1 && ($y7 ?? 0) == 1 && $all == 12) { $haiyaku = "12B背徳猫（ゆきうさ配役）"; $allsu = 12; }
                elseif (($y1 ?? 0) == 3 && ($y3 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y7 ?? 0) == 1 && ($y8 ?? 0) == 2 && ($y14 ?? 0) == 1 && ($y15 ?? 0) == 1 && $all == 12) { $haiyaku = "12W"; $allsu = 12; }
                elseif (($y1 ?? 0) == 2 && ($y3 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y7 ?? 0) == 1 && ($y8 ?? 0) == 2 && ($y14 ?? 0) == 1 && ($y15 ?? 0) == 1 && ($y13 ?? 0) == 1 && $all == 12) { $haiyaku = "12R猫FO（ライス配役）"; $allsu = 12; }
                elseif (($y1 ?? 0) == 2 && ($y2 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y16 ?? 0) == 1 && $all == 13) { $haiyaku = "13Z"; $allsu = 13; }
                elseif (($y1 ?? 0) == 3 && ($y3 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y7 ?? 0) == 1 && ($y8 ?? 0) == 2 && ($y14 ?? 0) == 1 && ($y15 ?? 0) == 1 && $all == 14) { $haiyaku = "14D猫"; $allsu = 14; }
                elseif (($y1 ?? 0) == 3 && ($y2 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y8 ?? 0) == 2 && ($y13 ?? 0) == 6 && ($y14 ?? 0) == 1 && $all == 16) { $haiyaku = "16A"; $allsu = 16; }
                elseif (($y1 ?? 0) == 4 && ($y3 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y7 ?? 0) == 1 && ($y8 ?? 0) == 2 && ($y13 ?? 0) == 3 && ($y14 ?? 0) == 1 && ($y15 ?? 0) == 1 && $all == 16) { $haiyaku = "16D猫"; $allsu = 16; }
                elseif (($y1 ?? 0) == 3 && ($y2 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y8 ?? 0) == 2 && ($y13 ?? 0) == 7 && ($y14 ?? 0) == 1 && $all == 17) { $haiyaku = "17A"; $allsu = 17; }
                elseif (($y1 ?? 0) == 4 && ($y2 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y7 ?? 0) == 1 && ($y8 ?? 0) == 2 && ($y13 ?? 0) == 6 && ($y14 ?? 0) == 1 && $all == 18) { $haiyaku = "18A猫"; $allsu = 18; }
                elseif (($y1 ?? 0) == 4 && ($y3 ?? 0) == 1 && ($y4 ?? 0) == 1 && ($y5 ?? 0) == 1 && ($y6 ?? 0) == 1 && ($y7 ?? 0) == 1 && ($y8 ?? 0) == 2 && ($y13 ?? 0) == 6 && ($y14 ?? 0) == 1 && ($y15 ?? 0) == 1 && $all == 19) { $haiyaku = "19D猫"; $allsu = 19; }
                $haiyakus[$game] = $haiyaku;
            }

            if (isset($com["message"]) && str_contains($com["message"], "が死亡しました")) {
                $x = explode("さんが死亡しました", $com["message"]);
                if (isset($x[0])) $seizon[$game][$x[0]] = 0;
            }

            if (str_contains($com["message"] ?? '', "投票時間になりました")) {
                $chuya = "夕";
            }
            if (str_contains($com["message"] ?? '', "朝になりました")) {
                $chuya = "昼";
                $chuyacount[$game] = ($chuyacount[$game] ?? 0) + 1;
                if (isset($seizon[$game])) {
                    foreach ($seizon[$game] as $k => $x) {
                        if ($x > 0) $seizonnissuu[$game][$k] = ($seizonnissuu[$game][$k] ?? 0) + 1;
                    }
                }
            }
            if (str_contains($com["message"] ?? '', "夜になりました")) {
                $chuya = "夜";
            }
            if (str_contains($com["message"] ?? '', "突然死")) {
                $totsushi[$game] = 1;
            }
            if (str_contains($com["message"] ?? '', "ゲームを中断しました")) {
                $namestats[$game] = $jinei[$game] = $seizonnissuu[$game] = $hatsugen[$game] = $shohai[$game] = $seizon[$game] = [];
                unset($shohai[$game]);
                $game--;
                $chudan++;
                $anata = 0;
                $chuya = "夜";
            }

            if (isset($jinei[$game]) && count($jinei[$game]) > 3) {
                preg_match("/【.+?】/", $com["message"] ?? '', $match);
                if (str_contains($com["message"] ?? '', "【全員】勝利")) $shohai[$game][-1] = 1;
                if (str_contains($com["message"] ?? '', "全員敗北")) $shohai[$game][0] = 1;
                $target = $match[0] ?? '';
                if (str_contains($target, "人狼") && str_contains($com["message"] ?? '', "勝利")) $shohai[$game][1] = 1;
                if (str_contains($target, "村人") && str_contains($com["message"] ?? '', "勝利")) $shohai[$game][2] = 1;
                if (str_contains($target, "妖狐") && str_contains($com["message"] ?? '', "勝利")) $shohai[$game][3] = 1;
                if (str_contains($target, "てるてる") && str_contains($com["message"] ?? '', "勝利")) $shohai[$game][4] = 1;
                if (str_contains($target, "恋人") && str_contains($com["message"] ?? '', "勝利")) $shohai[$game][5] = 1;
            }

            if (str_contains($com["message"] ?? '', "勝利") || str_contains($com["message"] ?? '', "敗北")) {
                $date    = $com["created"] ?? '';
                $dateObj = new DateTime($date, new DateTimeZone('Asia/Tokyo'));
                $datestop = $dateObj->format('U');
                $gametime[$game]       = $datestop - $datestart;
                $gamemachijikan[$game] = $datestart - $datesakusei;
                $datesakusei = $datestop;
                $dateend     = $datestop;
                $anata = 0;
                $chuya = "夜";
            }
            if (str_contains($com["message"] ?? '', "ゲームを終了しました")) {
                $date    = $com["created"] ?? '';
                $dateObj = new DateTime($date, new DateTimeZone('Asia/Tokyo'));
                $dateend = $dateObj->format('U');
                $datekansousen[$game] = $dateend - $datesakusei;
                $game++;
            }
            if ($c === count($comment)) {
                $date    = $com["created"] ?? '';
                $dateObj = new DateTime($date, new DateTimeZone('Asia/Tokyo'));
                $dateend = $dateObj->format('U');
                $datekansousen[$game] = $dateend - $datesakusei;
            }
            if (str_contains($com["message"] ?? '', "追放しました")) {
                $x = explode("さんを村から", $com["message"]);
                if (isset($x[0])) $tsuiho[$x[0]] = $x[0];
            }
        }

        $fromUser = $com["from_user"] ?? '';
        if ($chuya === "昼" && ($seizon[$game][$fromUser] ?? 0) > 0) {
            $hatsugen[$game][$fromUser] = ($hatsugen[$game][$fromUser] ?? 0) + 1;
        }

        if ($chuya === "夜") {
            if (str_contains($com["message"] ?? '', "実行@")) {
                $x = explode("→", str_replace("実行@", "", $com["message"]));
                if (($namestats[$game][$x[0]] ?? '') === "占い師") $jikkouuranai[$game][$x[1]] = $x[0];
                if (($namestats[$game][$x[0]] ?? '') === "狩人") $jikkoukaryudo[$game][$x[1]] = $x[0];
                if (($namestats[$game][$x[0]] ?? '') === "人狼") {
                    $date = $com["created"] ?? '';
                    $dt   = new DateTime($date, new DateTimeZone('Asia/Tokyo'));
                    $jikkoudatenow = $dt->format('U');
                    $jikkoutime = $jikkoudatenow - $jikkoudatepre;
                    if ($jikkoutime < 50) $hayagami[$x[0]] = $jikkoutime;
                    if (($namestats[$game][$x[1]] ?? '') === "人狼") $miuchigami[$game][$x[0]] = "a";
                }
            }
            if (str_contains($com["message"] ?? '', "妖狐のため占わ") && $fromUser === "鯖") {
                $x = explode("さんは妖狐のた", $com["message"]);
                $target = $x[0] ?? '';
                foreach ($jikkouuranai[$game] ?? [] as $kk => $xx) {
                    if ($kk === $target) $uranaifox[$game][$kk] = $xx;
                }
            }
            if (str_contains($com["message"] ?? '', "さんは人狼です") && $fromUser === "鯖") {
                $x = explode("さんは人狼です", $com["message"]);
                $target = $x[0] ?? '';
                foreach ($jikkouuranai[$game] ?? [] as $kk => $xx) {
                    if ($kk === $target) $uranaiwolf[$game][$kk] = $xx;
                }
            }
            if (str_contains($com["message"] ?? '', "さんを噛みます") && $fromUser === "鯖") {
                $x = explode("さんを噛み", $com["message"]);
                if (!isset($chuyacount[$game]) || $chuyacount[$game] < 0) $chuyacount[$game] = 0;
                $kami[$game][$chuyacount[$game]] = $x[0] ?? '';
            }
            if (str_contains($com["message"] ?? '', "狩人が人狼から守った") && $fromUser === "鯖") {
                foreach ($jikkoukaryudo[$game] ?? [] as $k => $x) {
                    if (($kami[$game][$chuyacount[$game]] ?? '') === $k) {
                        $karyudogj[$game][$x][] = $k;
                        $kami[$game][$chuyacount[$game]] = "";
                    }
                }
            }
        }

        $message = $com["message"] ?? '';
        for ($iii = 1; $iii < 10; $iii++) {
            $pattern = mb_substr($message, 0, $iii);
            if ($pattern !== "") {
                $count = substr_count($message, $pattern);
                if ($count > 49) $arashi[$fromUser] = $pattern;
            }
        }
        for ($iii = 1; $iii < 10; $iii++) {
            $pattern = mb_substr($message, 1, $iii);
            if ($pattern !== "") {
                $count = substr_count($message, $pattern);
                if ($count > 49) $arashi[$fromUser] = $pattern;
            }
        }

        $roomParts = explode("-", $roomname);
        if (count($roomParts) > 1) {
            unset($roomParts[count($roomParts) - 1]);
            $roomname = implode("-", $roomParts);
        }
        $roomname = mb_convert_kana($roomname, "a");
        $patterns = [
            "/その[0-9]+$/u", "/Part[0-9]+$/u", "/part[0-9]+$/u", "/ー[0-9]+$/u",
            "/-[0-9]+$/u", "/ｰ[0-9]+$/u", "/【[0-9]+】$/u", "/（[0-9]+）$/u",
            "/\([0-9]+\)$/u", "/[0-9]+$/u"
        ];
        $roomname = preg_replace($patterns, "", $roomname);
        $roomname = preg_replace("/【[0-9]+こめ】$/u", "【Nこめ】", $roomname);
        $roomname = preg_replace("/【act\.[0-9]+】$/u", "【act.N】", $roomname);
        $replacements = [
            "蛸ロード"       => "蛸ロード",
            "unity"          => "UNITY",
            "UNITY"          => "UNITY",
            "マターリ"       => "マターリマターリ村",
            "深夜食堂"       => "深夜食堂",
            "闇に隠れし世界★" => "闇に隠れし世界★Nの巻",
            "チョコレート村"  => "チョコレート村N枚目"
        ];
        foreach ($replacements as $keyword => $standardName) {
            if (mb_stripos($roomname, $keyword) !== false) {
                $roomname = $standardName;
                break;
            }
        }
    }

    foreach ($shohai as $k => $x) {
        foreach ($nametrip as $kk => $xx) {
            if (($jinei[$k][$kk] ?? '') === '') {
                $kansens[$k][$kk] = $xx;
            } else {
                $sansens[$k][$kk] = $xx;
            }
        }
    }

    $games   = [];
    $players = [];
    $createdTs = ($datemake instanceof DateTime) ? $datemake->getTimestamp() : (int)$datesakusei;

    foreach ($shohai as $k => $x) {
        if (!isset($x)) continue;
        if (($totsushi[$k] ?? 0) > 0) continue;
        if (!isset($haiyakus[$k]) || $haiyakus[$k] === '') continue;

        $regname = [];
        $regtrip = [];
        foreach ($jinei[$k] ?? [] as $kk => $xx) {
            if ($kk === "初日犠牲者") continue;
            $role     = $namestats[$k][$kk]    ?? '';
            $trip     = $nametrip[$kk]         ?? '';
            $talk     = $hatsugen[$k][$kk]     ?? 0;
            $survived = $seizonnissuu[$k][$kk] ?? 0;
            $winflag = (isset($x[$xx]) && $x[$xx] == 1) ? 1 : 0;
            $regname[$kk]   = "{$winflag}φ{$role}φ{$trip}φ{$talk}φ{$survived}φ";
            $regtrip[$trip] = "{$winflag}φ{$role}φ{$kk}φ{$talk}φ{$survived}φ";
        }

        $haiyakuStr   = (string)($haiyakus[$k] ?? '');
        $regtripJson  = json_encode($regtrip, JSON_UNESCAPED_UNICODE);
        $regnameJson  = json_encode($regname, JSON_UNESCAPED_UNICODE);
        $tsuihoJson   = json_encode($tsuiho, JSON_UNESCAPED_UNICODE);

        $gameTime   = (int)($gametime[$k]       ?? 0);
        $waitTime   = (int)($gamemachijikan[$k] ?? 0);
        $reviewTime = (int)($datekansousen[$k]  ?? 0);

        $startedAt  = $createdTs + $waitTime;
        $finishedAt = $startedAt + $gameTime;

        $rawLine = implode("<>", [
            (string)$logId, (string)$roomname, (string)$createdTs,
            $haiyakuStr, $regtripJson, $regnameJson, (string)$k,
            (string)$gameTime, (string)$waitTime, (string)$reviewTime,
            (string)$muramake, $tsuihoJson
        ]) . "<>\n";

        $games[] = [
            'log_id'      => $logId,
            'game_index'  => (int)$k,
            'created_at'  => $createdTs,
            'started_at'  => $startedAt,
            'finished_at' => $finishedAt,
            'village_name'=> $roomname,
            'haiyaku'     => $haiyakuStr,
            'rm_name'     => (string)$muramake,
            'game_time'   => $gameTime,
            'wait_time'   => $waitTime,
            'review_time' => $reviewTime,
            'tsuiho_json' => $tsuihoJson,
            'raw_line'    => $rawLine,
        ];

        foreach ($regname as $name => $payload) {
            $parts = explode('φ', $payload);
            if (count($parts) < 5) continue;
            $players[] = [
                'log_id'       => $logId,
                'game_index'   => (int)$k,
                'name'         => $name,
                'trip'         => (string)($parts[2] ?? ''),
                'role'         => (string)($parts[1] ?? ''),
                'win'          => (int)($parts[0] ?? 0),
                'talk_count'   => (int)($parts[3] ?? 0),
                'survive_days' => (int)($parts[4] ?? 0),
                'is_spectator' => 0,
                'tsuiho_flag'  => isset($tsuiho[$name]) ? 1 : 0,
                'created_at'   => $createdTs,
            ];
        }
    }

    if (empty($games)) {
        return null;
    }

    return [
        'games'   => $games,
        'players' => $players,
    ];
}

/**
 * games INSERT
 */
function insertGameRow(PDO $pdo, array $g): int
{
    $sql = '
        INSERT INTO games (
          log_id, game_index, created_at, started_at, finished_at,
          village_name, haiyaku, rm_name, game_time, wait_time,
          review_time, tsuiho_json, raw_line, chat_hash
        ) VALUES (
          :log_id, :game_index, :created_at, :started_at, :finished_at,
          :village_name, :haiyaku, :rm_name, :game_time, :wait_time,
          :review_time, :tsuiho_json, :raw_line, :chat_hash
        )';
    $st = $pdo->prepare($sql);
    $st->execute([
        ':log_id'      => $g['log_id'],
        ':game_index'  => $g['game_index'],
        ':created_at'  => $g['created_at'],
        ':started_at'  => $g['started_at'],
        ':finished_at' => $g['finished_at'],
        ':village_name'=> $g['village_name'],
        ':haiyaku'     => $g['haiyaku'],
        ':rm_name'     => $g['rm_name'],
        ':game_time'   => $g['game_time'],
        ':wait_time'   => $g['wait_time'],
        ':review_time' => $g['review_time'],
        ':tsuiho_json' => $g['tsuiho_json'],
        ':raw_line'    => $g['raw_line'],
        ':chat_hash'   => $g['chat_hash'] ?? null,
    ]);
    
    // PostgreSQL の SERIAL/BIGSERIAL 型のカラム(id)を取得
    return (int)$pdo->lastInsertId();
}

/**
 * players INSERT
 */
function insertPlayerRow(PDO $pdo, int $gameId, array $p): void
{
    $sql = '
        INSERT INTO players (
          game_id, log_id, name, trip, role, win,
          talk_count, survive_days, is_spectator, tsuiho_flag, created_at
        ) VALUES (
          :game_id, :log_id, :name, :trip, :role, :win,
          :talk_count, :survive_days, :is_spectator, :tsuiho_flag, :created_at
        )';
    $st = $pdo->prepare($sql);
    $st->execute([
        ':game_id'      => $gameId,
        ':log_id'       => $p['log_id'],
        ':name'         => $p['name'],
        ':trip'         => $p['trip'],
        ':role'         => $p['role'],
        ':win'          => $p['win'],
        ':talk_count'   => $p['talk_count'],
        ':survive_days' => $p['survive_days'],
        ':is_spectator' => $p['is_spectator'],
        ':tsuiho_flag'  => $p['tsuiho_flag'],
        ':created_at'   => $p['created_at'],
    ]);
}
