<?php
declare(strict_types=1);

mb_internal_encoding('UTF-8');

const INITIAL_RATING = 1500.0;
const BASE_K         = 24.0;   // レート変動幅のベース
// 将来しきい値: ランキング側で games >= MIN_GAMES でフィルタする想定

// ---------------------------------------------
// DB 接続（DATABASE_URL_RATE 環境変数 → .env ファイルの順で取得）
// ---------------------------------------------
$databaseUrl = getenv('DATABASE_URL_RATE') ?: '';

if ($databaseUrl === '') {
    // 同ディレクトリの .env をフォールバックとして読む
    $envFile = __DIR__ . '/.env';
    if (file_exists($envFile)) {
        foreach (file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) as $line) {
            if (str_starts_with(trim($line), '#')) continue;
            if (preg_match('/^DATABASE_URL_RATE\s*=\s*(.+)$/', $line, $m)) {
                $databaseUrl = trim($m[1], " \t\"'");
                break;
            }
        }
    }
}

if ($databaseUrl === '') {
    fwrite(STDERR, "ERROR: DATABASE_URL_RATE が環境変数にも .env にも設定されていません。\n");
    exit(1);
}

// "postgresql://user:pass@host:port/dbname" 形式をパース
$parsed  = parse_url($databaseUrl);
$pgHost  = $parsed['host'] ?? '127.0.0.1';
$pgPort  = $parsed['port'] ?? 5432;
$pgDb    = ltrim($parsed['path'] ?? '/zinro_meta', '/');
$pgUser  = $parsed['user'] ?? '';
$pgPass  = rawurldecode($parsed['pass'] ?? '');

$dsn = sprintf('pgsql:host=%s;port=%d;dbname=%s', $pgHost, (int)$pgPort, $pgDb);

$pdo = new PDO($dsn, $pgUser, $pgPass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// ---------------------------------------------
// player_ratings テーブル確認・作成
// （schema_pg.sql 適用済み環境では CREATE は何もしない）
// ---------------------------------------------
$pdo->exec('
CREATE TABLE IF NOT EXISTS player_ratings (
    trip       TEXT PRIMARY KEY,
    rating     NUMERIC(8,2) NOT NULL DEFAULT 1500.00,
    games      INTEGER      NOT NULL DEFAULT 0,
    main_name  TEXT         DEFAULT NULL
);
');

// このスクリプト独自の追加列を、未作成の場合のみ追加
// （schema_pg.sql 側に含めなかった列のマイグレーション相当）
foreach ([
    'first_game_id INTEGER      DEFAULT NULL',
    'last_game_id  INTEGER      DEFAULT NULL',
    'last_updated  BIGINT       DEFAULT 0',
    'deviation     NUMERIC(6,2) DEFAULT NULL',  // 偏差値（全プレイヤー対象）
] as $colDef) {
    $colName = strtok($colDef, ' ');
    $exists  = (int)$pdo->query(
        "SELECT COUNT(*) FROM information_schema.columns
          WHERE table_name = 'player_ratings'
            AND column_name = " . $pdo->quote($colName)
    )->fetchColumn();
    if (!$exists) {
        $pdo->exec("ALTER TABLE player_ratings ADD COLUMN $colDef");
    }
}

// フルリビルドなので一旦クリア
$pdo->exec('DELETE FROM player_ratings');

// ---------------------------------------------
// レート計算用メモリ構造
// ---------------------------------------------
// $ratings[trip] = [
//   'rating'        => float,
//   'games'         => int,
//   'first_game_id' => int,
//   'last_game_id'  => int,
// ];
$ratings = [];

// ---------------------------------------------
// 全ゲームを created_at 昇順で処理
// ---------------------------------------------
$gameStmt = $pdo->query('SELECT id FROM games ORDER BY created_at ASC, id ASC');

while ($gameRow = $gameStmt->fetch(PDO::FETCH_ASSOC)) {
    $gameId = (int)$gameRow['id'];

    // このゲームのプレイヤー取得（観戦は除外）
    $pStmt = $pdo->prepare('
        SELECT trip, role, win
          FROM players
         WHERE game_id    = :game_id
           AND is_spectator = 0
    ');
    $pStmt->execute([':game_id' => $gameId]);

    $players = [];
    while ($row = $pStmt->fetch(PDO::FETCH_ASSOC)) {
        $trip = trim((string)$row['trip']);
        $role = trim((string)$row['role']);
        $win  = (int)$row['win'];

        // トリップなし・「なし」系はレート対象外
        // DBの実値は「なし」「◆なし」「」など複数パターンあり
        if ($trip === '' || $trip === 'なし' || $trip === '◆なし' || mb_strtolower($trip) === 'none') {
            continue;
        }

        $side = role_to_side($role);
        // 対象外ロール（てるてる・恋人など）はレート計算から完全除外
        if ($side === null) {
            continue;
        }

        $players[] = [
            'trip' => $trip,
            'role' => $role,
            'side' => $side,   // 'wolf' | 'village' | 'fox'
            'win'  => $win,
        ];
    }

    if (count($players) < 4) {
        // 極端に少ないゲームはノイズになりやすいのでスキップ（好みで調整可）
        continue;
    }

    // ゲーム内の陣営ごとのプレイヤーを集約
    $sides = [
        'wolf'    => [],
        'village' => [],
        'fox'     => [],
    ];

    foreach ($players as $p) {
        $sides[$p['side']][] = $p;
    }

    // 少なくとも2陣営いないとレーティングの意味が薄いのでスキップ
    $activeSides = [];
    foreach ($sides as $sideName => $sidePlayers) {
        if (count($sidePlayers) > 0) {
            $activeSides[$sideName] = $sidePlayers;
        }
    }
    if (count($activeSides) < 2) {
        continue;
    }

    // 勝利陣営を判定
    $winSides = [];
    foreach ($activeSides as $sideName => $sidePlayers) {
        $hasWin = false;
        foreach ($sidePlayers as $p) {
            if ($p['win'] === 1) {
                $hasWin = true;
                break;
            }
        }
        if ($hasWin) {
            $winSides[] = $sideName;
        }
    }

    // 勝利陣営がちょうど1つのときのみレーティング更新
    if (count($winSides) !== 1) {
        // 恋人＋村勝利等の複雑系はここで弾かれる
        continue;
    }
    $winnerSide = $winSides[0];

    // チーム平均レートの算出
    $teamRating = [];
    foreach ($activeSides as $sideName => $sidePlayers) {
        $sum = 0.0;
        $cnt = 0;
        foreach ($sidePlayers as $p) {
            $trip = $p['trip'];
            if (!isset($ratings[$trip])) {
                $ratings[$trip] = [
                    'rating'        => INITIAL_RATING,
                    'games'         => 0,
                    'first_game_id' => $gameId,
                    'last_game_id'  => $gameId,
                ];
            } else {
                $ratings[$trip]['last_game_id'] = $gameId;
            }

            $sum += $ratings[$trip]['rating'];
            $cnt++;
        }
        if ($cnt > 0) {
            $teamRating[$sideName] = $sum / $cnt;
        }
    }

    if (count($teamRating) < 2) {
        continue;
    }

    // Elo期待値計算
    $elo = function (float $Ra, float $Rb): float {
        return 1.0 / (1.0 + pow(10.0, ($Rb - $Ra) / 400.0));
    };

    // 陣営間組み合わせごとのΔを計算
    $deltaTeam = [];

    $sideNames  = array_keys($teamRating);
    $sideCount  = count($sideNames);

    for ($i = 0; $i < $sideCount; $i++) {
        for ($j = $i + 1; $j < $sideCount; $j++) {
            $sideA = $sideNames[$i];
            $sideB = $sideNames[$j];

            if ($sideA === $winnerSide && $sideB !== $winnerSide) {
                $S_A = 1.0;
            } elseif ($sideB === $winnerSide && $sideA !== $winnerSide) {
                $S_A = 0.0;
            } else {
                continue;
            }

            $Ra  = $teamRating[$sideA];
            $Rb  = $teamRating[$sideB];
            $E_A = $elo($Ra, $Rb);
            $E_B = 1.0 - $E_A;
            $K   = BASE_K;

            $deltaA = $K * ($S_A - $E_A);
            $deltaB = $K * ((1.0 - $S_A) - $E_B);

            if (!isset($deltaTeam[$sideA])) { $deltaTeam[$sideA] = 0.0; }
            if (!isset($deltaTeam[$sideB])) { $deltaTeam[$sideB] = 0.0; }
            $deltaTeam[$sideA] += $deltaA;
            $deltaTeam[$sideB] += $deltaB;
        }
    }

    // 計算されたΔを各プレイヤーに適用
    foreach ($activeSides as $sideName => $sidePlayers) {
        if (!isset($deltaTeam[$sideName])) {
            continue;
        }
        $delta = $deltaTeam[$sideName];

        foreach ($sidePlayers as $p) {
            $trip = $p['trip'];

            if (!isset($ratings[$trip])) {
                $ratings[$trip] = [
                    'rating'        => INITIAL_RATING,
                    'games'         => 0,
                    'first_game_id' => $gameId,
                    'last_game_id'  => $gameId,
                ];
            }

            $ratings[$trip]['rating'] += $delta;
            $ratings[$trip]['games']  += 1;

            if (!isset($ratings[$trip]['first_game_id'])) {
                $ratings[$trip]['first_game_id'] = $gameId;
            }
            $ratings[$trip]['last_game_id'] = $gameId;
        }
    }
}

// ---------------------------------------------
// 計算結果を DB に保存
// ---------------------------------------------
$now = time();

// PostgreSQL の UPSERT（ON CONFLICT ... DO UPDATE）
// ※ SQLite と構文は同じだが PostgreSQL でも正式サポート
$insertStmt = $pdo->prepare('
    INSERT INTO player_ratings
        (trip, rating, games, first_game_id, last_game_id, last_updated)
    VALUES
        (:trip, :rating, :games, :first_game_id, :last_game_id, :last_updated)
    ON CONFLICT (trip) DO UPDATE SET
        rating        = EXCLUDED.rating,
        games         = EXCLUDED.games,
        first_game_id = EXCLUDED.first_game_id,
        last_game_id  = EXCLUDED.last_game_id,
        last_updated  = EXCLUDED.last_updated
');

$pdo->beginTransaction();
foreach ($ratings as $trip => $info) {
    $insertStmt->execute([
        ':trip'          => $trip,
        ':rating'        => $info['rating'],
        ':games'         => $info['games'],
        ':first_game_id' => $info['first_game_id'] ?? null,
        ':last_game_id'  => $info['last_game_id']  ?? null,
        ':last_updated'  => $now,
    ]);
}
$pdo->commit();

// ---------------------------------------------
// 代表名前（main_name）を埋める処理
// ---------------------------------------------
// players テーブルから、各 trip ごとに
// 「一番出現回数が多い name」を選んで main_name に入れる
$sqlUpdateMainName = <<<SQL
UPDATE player_ratings AS pr
   SET main_name = (
       SELECT   p.name
         FROM   players p
        WHERE   p.trip         = pr.trip
          AND   p.is_spectator = 0
          AND   p.name IS NOT NULL
          AND   p.name <> ''
        GROUP BY p.name
        ORDER BY COUNT(*) DESC, p.name ASC
        LIMIT 1
   )
SQL;

$pdo->beginTransaction();
$pdo->exec($sqlUpdateMainName);
$pdo->commit();

// ---------------------------------------------
// 偏差値（deviation）を計算して更新
// ---------------------------------------------
// 母集団を games >= 10 のプレイヤーに絞って avg/stddev を算出する。
// games=1〜2 の初期レート1500付近の大量プレイヤーを含めると
// 標準偏差が極小になり上位が偏差値400超えになるため。
// 偏差値の値自体は全プレイヤーに書き込む（ゲーム数少ない人はNULL）
const DEVIATION_MIN_GAMES = 10;

$statsRow = $pdo->query('
    SELECT AVG(rating) AS avg, STDDEV_POP(rating) AS stddev
      FROM player_ratings
     WHERE games >= ' . DEVIATION_MIN_GAMES
)->fetch(PDO::FETCH_ASSOC);

$avg    = (float)($statsRow['avg']    ?? 1500.0);
$stddev = (float)($statsRow['stddev'] ?? 0.0);

if ($stddev > 0.0) {
    // games >= 10 のプレイヤーのみ偏差値を書く（それ以外は NULL）
    $pdo->exec("
        UPDATE player_ratings
           SET deviation = CASE
               WHEN games >= " . DEVIATION_MIN_GAMES . "
               THEN ROUND( CAST(50.0 + 10.0 * (rating - {$avg}) / {$stddev} AS NUMERIC), 2 )
               ELSE NULL
           END
    ");
    echo sprintf(
        "偏差値計算完了: 母集団=games>=%d, avg=%.2f, stddev=%.2f",
        DEVIATION_MIN_GAMES, $avg, $stddev
    ) . PHP_EOL;
} else {
    $pdo->exec("UPDATE player_ratings SET deviation = CASE WHEN games >= " . DEVIATION_MIN_GAMES . " THEN 50.00 ELSE NULL END");
    echo "偏差値計算: 標準偏差=0のため対象者全員50に設定" . PHP_EOL;
}

echo "rebuild_rating: done. players = " . count($ratings) . PHP_EOL;

// ---------------------------------------------
// 役職 → 陣営マッピング
// ---------------------------------------------
/**
 * 役職名から陣営を返す
 *
 * @return 'wolf'|'village'|'fox'|null
 */
function role_to_side(string $role): ?string
{
    $role = trim($role);

    // 狼陣営
    $wolfRoles = ['人狼', '狂人', '狂信者', '黒猫'];
    foreach ($wolfRoles as $r) {
        if (mb_strpos($role, $r) !== false) { return 'wolf'; }
    }

    // 村陣営
    $villageRoles = [
        '占い師', '狩人', '霊能者', '猫又', '共有者',
        '役人', '怪盗', '狼憑き', 'ものまね', '村人', 'パン屋',
    ];
    foreach ($villageRoles as $r) {
        if (mb_strpos($role, $r) !== false) { return 'village'; }
    }

    // 狐陣営
    $foxRoles = ['妖狐', '背徳者'];
    foreach ($foxRoles as $r) {
        if (mb_strpos($role, $r) !== false) { return 'fox'; }
    }

    // てるてる / 恋人 / それ以外の特殊職はレート対象外
    return null;
}
