<?php
/**
 * 人狼オンライン戦績解析 ランキングキャッシュバッチ（LIB直叩き版）
 *
 * 1日1回くらい cron から:
 *   php batch_build_rank_cache_lib.php
 *
 * 役割:
 *   - SQLite (zinro_meta.db) に直接接続
 *   - ranking_lib.php の関数を叩いて
 *       - 月間 / 年間 / 通算
 *       - 名前 / トリップ / 村 / 配役 / RM / 追放 など
 *     を全部 rank_cache_json にキャッシュする
 *
 * 前提:
 *   - 同ディレクトリに zinro_meta.db, ranking_lib.php がある
 *   - ranking_lib.php 側で以下のような関数（例）が実装されている想定:
 *
 *     - RankingLib::getMonths(SQLite3 $db): array
 *         // 例: ['2019-01','2019-02',...]
 *
 *     - RankingLib::buildMonthlyTripGames(SQLite3 $db, string $ym): array{rows: array, total: int}
 *     - RankingLib::buildMonthlyTripWinRate(SQLite3 $db, string $ym, array $opt = []): array{rows,total}
 *     - RankingLib::buildMonthlyNameGames(...)
 *     - RankingLib::buildMonthlyNameWinRate(...)
 *     - RankingLib::buildMonthlyTalk(...)
 *     - RankingLib::buildMonthlyDays(...)
 *     - RankingLib::buildMonthlyVillage(...)
 *     - RankingLib::buildMonthlyHaiyaku(...)
 *     - RankingLib::buildMonthlyHaiyakuWin(...)
 *     - RankingLib::buildMonthlyRm(...)
 *     - RankingLib::buildMonthlyTsuiho(...)
 *
 *     - 通算(all)版は ym='all' を渡したときにも同じ関数で取れる or 別関数を用意
 *       →今回は「ym='all' も受け付ける」設計にしておくのを推奨
 */

declare(strict_types=1);

ini_set('display_errors', '1');
error_reporting(E_ALL);

require_once __DIR__ . '/ranking_lib.php';

// ===== 設定 =====

// SQLite ファイルパス
const DB_PATH  = __DIR__ . '/zinro_meta.db';

// 1回の実行で「今月から何ヶ月前まで」を毎回再計算するか
// 例: 2 → 今月/先月は毎回再計算。それより前の月は「一度キャッシュがあればスキップ」
const FROZEN_MARGIN_MONTHS = 2;

// ===== ログユーティリティ =====

function logInfo(string $msg): void {
    fwrite(STDOUT, '[' . date('Y-m-d H:i:s') . '] ' . $msg . PHP_EOL);
}

function logError(string $msg): void {
    fwrite(STDERR, '[' . date('Y-m-d H:i:s') . '] ERROR: ' . $msg . PHP_EOL);
}

// ===== 凍結判定系 =====

/**
 * "YYYY-MM" が「今月から何ヶ月前か」を返す（0=今月, 1=先月, ...）
 */
function monthAgeInMonths(string $ym): int {
    if (!preg_match('/^(\d{4})-(\d{2})$/', $ym, $m)) {
        return 9999;
    }
    $y  = (int)$m[1];
    $mo = (int)$m[2];

    $now = new DateTimeImmutable('first day of this month 00:00:00');
    $ny  = (int)$now->format('Y');
    $nmo = (int)$now->format('m');

    return ($ny - $y) * 12 + ($nmo - $mo);
}

/**
 * 月間キャッシュの「凍結済み」判定
 *   - ym が "YYYY-MM" で
 *   - monthAge >= FROZEN_MARGIN_MONTHS
 */
function isFrozenMonth(string $ym): bool {
    if ($ym === 'all' || $ym === '') {
        return false;
    }
    $age = monthAgeInMonths($ym);
    return $age >= FROZEN_MARGIN_MONTHS;
}

/**
 * "YYYY" が「凍結済み年」かどうか。
 *   - 今年より前の年は true（もう変わらない前提）
 *   - 今年は false
 */
function isFrozenYear(string $year): bool {
    $y   = (int)$year;
    $cur = (int)date('Y');
    return $y < $cur;
}

/**
 * scope, ym, kind, key_type からキャッシュキーを作る。
 *
 * 例:
 *   month:2025-10:games:name
 *   month:2025-10:village:-
 *   all:all:tsuiho:-
 *   year:2025:games:trip
 */
function makeCacheKey(string $scope, string $ym, string $kind, ?string $keyType): string {
    return sprintf(
        '%s:%s:%s:%s',
        $scope,
        $ym,
        $kind,
        $keyType ?? '-'
    );
}

// ===== 年間集計: 月キャッシュを合成 =====

/**
 * 指定した year(YYYY) ＋ keyType(name/trip) について、
 * 月キャッシュ(scope=month, kind='games')を合成して
 * 年版の「参戦数ランキング」「勝率ランキング」の rows を作る。
 *
 * 戻り値:
 * [
 *   'games_rows' => [...],  // ゲーム数順ソート済
 *   'win_rows'   => [...],  // 勝率順ソート済
 * ]
 */
function buildYearGamesWinFromMonthly(SQLite3 $db, string $year, string $keyType): ?array
{
    if ($keyType !== 'name' && $keyType !== 'trip') {
        return null;
    }

    $like = $year . '-%';

    $stmt = $db->prepare("
        SELECT payload
          FROM rank_cache_json
         WHERE scope    = 'month'
           AND ym       LIKE :ym
           AND kind     = 'games'
           AND key_type = :key_type
    ");
    $stmt->bindValue(':ym',       $like,    SQLITE3_TEXT);
    $stmt->bindValue(':key_type', $keyType, SQLITE3_TEXT);

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

    // key: name or trip
    $map = []; // key => ['name'/'trip','games','wins']

    while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
        $payload = json_decode($row['payload'] ?? '[]', true);
        if (!is_array($payload)) continue;
        $rows = $payload['rows'] ?? [];
        if (!is_array($rows)) continue;

        foreach ($rows as $r) {
            if ($keyType === 'trip') {
                $key = $r['trip'] ?? '';
                if ($key === '') continue;
                if (!isset($map[$key])) {
                    $map[$key] = [
                        'trip'  => $key,
                        'games' => 0,
                        'wins'  => 0,
                    ];
                }
            } else {
                $key = $r['name'] ?? '';
                if ($key === '') continue;
                if (!isset($map[$key])) {
                    $map[$key] = [
                        'name'  => $key,
                        'games' => 0,
                        'wins'  => 0,
                    ];
                }
            }

            $g = isset($r['games']) ? (int)$r['games'] : 0;
            $w = isset($r['wins'])  ? (int)$r['wins']  : 0;

            $map[$key]['games'] += $g;
            $map[$key]['wins']  += $w;
        }
    }

    if (empty($map)) {
        return [
            'games_rows' => [],
            'win_rows'   => [],
        ];
    }

    foreach ($map as &$v) {
        $g = $v['games'] ?? 0;
        $w = $v['wins']  ?? 0;
        $v['win_rate'] = ($g > 0)
            ? round($w * 100.0 / $g, 1)
            : 0.0;
    }
    unset($v);

    // ゲーム数順
    $rowsGames = array_values($map);
    usort($rowsGames, function ($a, $b) use ($keyType) {
        if ($a['games'] !== $b['games']) return $b['games'] <=> $a['games'];
        if ($a['wins']  !== $b['wins'])  return $b['wins']  <=> $a['wins'];
        $ka = $keyType === 'trip' ? ($a['trip'] ?? '') : ($a['name'] ?? '');
        $kb = $keyType === 'trip' ? ($b['trip'] ?? '') : ($b['name'] ?? '');
        return strcmp((string)$ka, (string)$kb);
    });

    // 勝率順
    $rowsWin = array_values($map);
    usort($rowsWin, function ($a, $b) use ($keyType) {
        if ($a['win_rate'] != $b['win_rate']) {
            return $b['win_rate'] <=> $a['win_rate'];
        }
        if ($a['games'] !== $b['games']) return $b['games'] <=> $a['games'];
        if ($a['wins']  !== $b['wins'])  return $b['wins']  <=> $a['wins'];
        $ka = $keyType === 'trip' ? ($a['trip'] ?? '') : ($a['name'] ?? '');
        $kb = $keyType === 'trip' ? ($b['trip'] ?? '') : ($b['name'] ?? '');
        return strcmp((string)$ka, (string)$kb);
    });

    return [
        'games_rows' => $rowsGames,
        'win_rows'   => $rowsWin,
    ];
}

// ===== メイン =====

// CLI 以外から呼ばれたら弾く
if (php_sapi_name() !== 'cli') {
    http_response_code(403);
    echo "Run from CLI.\n";
    exit(1);
}

$batchStart = microtime(true);
logInfo('=== ranking cache batch (LIB版) start ===');

// DB 接続
$db = new SQLite3(DB_PATH);
$db->busyTimeout(5000);
$db->exec("PRAGMA journal_mode = WAL");
$db->exec("PRAGMA synchronous = NORMAL");

// キャッシュテーブル準備
$db->exec("
    CREATE TABLE IF NOT EXISTS rank_cache_json (
      cache_key  TEXT PRIMARY KEY,
      scope      TEXT NOT NULL,
      ym         TEXT,
      kind       TEXT NOT NULL,
      key_type   TEXT,
      payload    TEXT NOT NULL,
      total      INTEGER NOT NULL,
      built_at   INTEGER NOT NULL
    );
");
$db->exec("
    CREATE INDEX IF NOT EXISTS idx_rank_cache_json_scope
      ON rank_cache_json(scope, kind, ym);
");

// 1) months 一覧を ranking_lib 経由で取得
$months = RankingLib::getMonths($db);
if (!is_array($months) || !count($months)) {
    logError('getMonths() returned empty; abort');
    exit(1);
}
logInfo('months = ' . implode(', ', $months));

// 2) ターゲット一覧を作成
$targets = [];

// ====== 月間スコープ ======
foreach ($months as $ym) {
    // 参戦数 / 勝率（名前・トリップ）
    foreach (['name', 'trip'] as $keyType) {
        $targets[] = [
            'scope'    => 'month',
            'ym'       => $ym,
            'kind'     => 'games',
            'key_type' => $keyType,
            'lib_fn'   => ($keyType === 'trip')
                ? 'buildMonthlyTripGames'
                : 'buildMonthlyNameGames',
        ];
        $targets[] = [
            'scope'    => 'month',
            'ym'       => $ym,
            'kind'     => 'win_rate',
            'key_type' => $keyType,
            'lib_fn'   => ($keyType === 'trip')
                ? 'buildMonthlyTripWinRate'
                : 'buildMonthlyNameWinRate',
        ];
    }

    // 発言密度 / 参加日数（名前のみ）
    $targets[] = [
        'scope'    => 'month',
        'ym'       => $ym,
        'kind'     => 'talk',
        'key_type' => 'name',
        'lib_fn'   => 'buildMonthlyTalk',
    ];
    $targets[] = [
        'scope'    => 'month',
        'ym'       => $ym,
        'kind'     => 'days',
        'key_type' => 'name',
        'lib_fn'   => 'buildMonthlyDays',
    ];

    // その他（key_type なし）
    foreach ([
        ['kind' => 'role_win',    'fn' => 'buildMonthlyRoleWin'],
        ['kind' => 'haiyaku_win', 'fn' => 'buildMonthlyHaiyakuWin'],
        ['kind' => 'village',     'fn' => 'buildMonthlyVillage'],
        ['kind' => 'haiyaku',     'fn' => 'buildMonthlyHaiyaku'],
        ['kind' => 'rm',          'fn' => 'buildMonthlyRm'],
        ['kind' => 'tsuiho',      'fn' => 'buildMonthlyTsuiho'],
    ] as $cfg) {
        $targets[] = [
            'scope'    => 'month',
            'ym'       => $ym,
            'kind'     => $cfg['kind'],
            'key_type' => null,
            'lib_fn'   => $cfg['fn'],
        ];
    }
}

// ====== 通算スコープ（ym=all） ======
$allYm = 'all';

// 参戦数 / 勝率（名前・トリップ）
foreach (['name', 'trip'] as $keyType) {
    $targets[] = [
        'scope'    => 'all',
        'ym'       => $allYm,
        'kind'     => 'games',
        'key_type' => $keyType,
        'lib_fn'   => ($keyType === 'trip')
            ? 'buildMonthlyTripGames'
            : 'buildMonthlyNameGames',
    ];
    $targets[] = [
        'scope'    => 'all',
        'ym'       => $allYm,
        'kind'     => 'win_rate',
        'key_type' => $keyType,
        'lib_fn'   => ($keyType === 'trip')
            ? 'buildMonthlyTripWinRate'
            : 'buildMonthlyNameWinRate',
    ];
}

// 発言密度 / 参加日数（全期間）
$targets[] = [
    'scope'    => 'all',
    'ym'       => $allYm,
    'kind'     => 'talk',
    'key_type' => 'name',
    'lib_fn'   => 'buildMonthlyTalk',
];
$targets[] = [
    'scope'    => 'all',
    'ym'       => $allYm,
    'kind'     => 'days',
    'key_type' => 'name',
    'lib_fn'   => 'buildMonthlyDays',
];

// その他（全期間）
foreach ([
    ['kind' => 'role_win',    'fn' => 'buildMonthlyRoleWin'],
    ['kind' => 'haiyaku_win', 'fn' => 'buildMonthlyHaiyakuWin'],
    ['kind' => 'village',     'fn' => 'buildMonthlyVillage'],
    ['kind' => 'haiyaku',     'fn' => 'buildMonthlyHaiyaku'],
    ['kind' => 'rm',          'fn' => 'buildMonthlyRm'],
    ['kind' => 'tsuiho',      'fn' => 'buildMonthlyTsuiho'],
] as $cfg) {
    $targets[] = [
        'scope'    => 'all',
        'ym'       => $allYm,
        'kind'     => $cfg['kind'],
        'key_type' => null,
        'lib_fn'   => $cfg['fn'],
    ];
}

logInfo('target count = ' . count($targets));

// 3) 一括でキャッシュ構築
$now = time();

$db->exec('BEGIN IMMEDIATE');
try {
    foreach ($targets as $t) {
        $scope   = $t['scope'];
        $ym      = $t['ym'];
        $kind    = $t['kind'];
        $keyType = $t['key_type'];
        $libFn   = $t['lib_fn'];

        $cacheKey = makeCacheKey($scope, $ym, $kind, $keyType);
        $label    = "scope={$scope}, ym={$ym}, kind={$kind}, key_type=" . ($keyType ?? 'null');

        // ---- 凍結チェック（月間のみ） ----
        $skipByFrozen = false;
        if ($scope === 'month' && isFrozenMonth($ym)) {
            $checkStmt = $db->prepare("
                SELECT built_at
                  FROM rank_cache_json
                 WHERE cache_key = :key
                 LIMIT 1
            ");
            $checkStmt->bindValue(':key', $cacheKey, SQLITE3_TEXT);
            $existing = $checkStmt->execute()->fetchArray(SQLITE3_ASSOC);
            if ($existing) {
                logInfo("skip frozen month (already cached): {$label}");
                $skipByFrozen = true;
            }
        }
        if ($skipByFrozen) {
            continue;
        }

        // ---- ranking_lib 呼び出し ----
        if (!method_exists(RankingLib::class, $libFn)) {
            logError("lib fn not found: {$libFn} for {$label}, skip");
            continue;
        }

        logInfo("build cache start: {$label} via {$libFn}()");
        $tStart = microtime(true);

        // LIB 側の関数シグネチャ想定:
        //   static function buildMonthlyTripGames(SQLite3 $db, string $ym): array{rows,total}
        //   static function buildMonthlyTripWinRate(SQLite3 $db, string $ym, array $opt = []): array{rows,total}
        //   ...
        // 「all」対応は ym='all' をそのまま渡す設計にしておく。
        $res = null;

        // 勝率系だけ min_games を指定したい場合など、ここでオプション振り分け


// 勝率系だけ min_games を指定
if (in_array($kind, ['win_rate', 'role_win', 'haiyaku_win'], true)) {
    $res = call_user_func([RankingLib::class, $libFn], $db, $ym, [
        'min_games'   => 30,
        'min_players' => 100,
    ]);
} elseif ($kind === 'talk') {
    $res = call_user_func([RankingLib::class, $libFn], $db, $ym, [
        'min_live_days' => 10,
    ]);
} else {
    // デフォルト: (db, ym) だけ
    $res = call_user_func([RankingLib::class, $libFn], $db, $ym);
}


        $elapsedOne = microtime(true) - $tStart;

        if (!is_array($res) || !isset($res['rows'], $res['total'])) {
            logError("lib fn returned invalid result: {$libFn} for {$label}, elapsed=" . sprintf('%.3f', $elapsedOne));
            continue;
        }

        logInfo("build cache done: {$label}, rows={$res['total']}, elapsed=" . sprintf('%.3f', $elapsedOne) . " sec");

        $payloadArr  = [
            'ok'    => true,
            'rows'  => $res['rows'],
            'total' => $res['total'],
        ];
        $payloadJson = json_encode($payloadArr, JSON_UNESCAPED_UNICODE);

        $stmt = $db->prepare("
            INSERT INTO rank_cache_json
              (cache_key, scope, ym, kind, key_type, payload, total, built_at)
            VALUES
              (:cache_key, :scope, :ym, :kind, :key_type, :payload, :total, :built_at)
            ON CONFLICT(cache_key) DO UPDATE SET
              scope    = excluded.scope,
              ym       = excluded.ym,
              kind     = excluded.kind,
              key_type = excluded.key_type,
              payload  = excluded.payload,
              total    = excluded.total,
              built_at = excluded.built_at
        ");
        $stmt->bindValue(':cache_key', $cacheKey, SQLITE3_TEXT);
        $stmt->bindValue(':scope',     $scope,   SQLITE3_TEXT);
        $stmt->bindValue(':ym',        $ym,      SQLITE3_TEXT);
        $stmt->bindValue(':kind',      $kind,    SQLITE3_TEXT);
        if ($keyType === null) {
            $stmt->bindValue(':key_type', null, SQLITE3_NULL);
        } else {
            $stmt->bindValue(':key_type', $keyType, SQLITE3_TEXT);
        }
        $stmt->bindValue(':payload',   $payloadJson,   SQLITE3_TEXT);
        $stmt->bindValue(':total',     $res['total'],  SQLITE3_INTEGER);
        $stmt->bindValue(':built_at',  $now,           SQLITE3_INTEGER);
        $stmt->execute();
    }

    // ===== 年間キャッシュ（games / win_rate × name/trip） =====
    $years = array_values(array_unique(array_map(
        fn(string $ym) => substr($ym, 0, 4),
        $months
    )));
    sort($years);

    foreach ($years as $year) {
        $frozen = isFrozenYear($year);

        // 凍結対象の年で、既にキャッシュがあればスキップ
        if ($frozen) {
            $check = $db->prepare("
                SELECT 1 FROM rank_cache_json
                 WHERE scope    = 'year'
                   AND ym       = :ym
                   AND kind     = 'games'
                   AND key_type = 'name'
                 LIMIT 1
            ");
            $check->bindValue(':ym', $year, SQLITE3_TEXT);
            $exists = $check->execute()->fetchArray(SQLITE3_ASSOC);
            if ($exists) {
                logInfo("skip year (already cached & frozen): year={$year}");
                continue;
            }
        }

        logInfo("build yearly cache start: year={$year}");

        foreach (['name', 'trip'] as $keyType) {
            $agg = buildYearGamesWinFromMonthly($db, $year, $keyType);
            if ($agg === null) {
                logError("yearly aggregate failed: year={$year}, key_type={$keyType}");
                continue;
            }

            // games
            $rowsGames    = $agg['games_rows'];
            $payloadGames = [
                'ok'    => true,
                'rows'  => $rowsGames,
                'total' => count($rowsGames),
            ];
            $cacheKeyGames = makeCacheKey('year', $year, 'games', $keyType);

            $stmt = $db->prepare("
                INSERT INTO rank_cache_json
                  (cache_key, scope, ym, kind, key_type, payload, total, built_at)
                VALUES
                  (:cache_key, 'year', :ym, 'games', :key_type, :payload, :total, :built_at)
                ON CONFLICT(cache_key) DO UPDATE SET
                  payload  = excluded.payload,
                  total    = excluded.total,
                  built_at = excluded.built_at
            ");
            $stmt->bindValue(':cache_key', $cacheKeyGames, SQLITE3_TEXT);
            $stmt->bindValue(':ym',        $year,         SQLITE3_TEXT);
            $stmt->bindValue(':key_type',  $keyType,      SQLITE3_TEXT);
            $stmt->bindValue(':payload',   json_encode($payloadGames, JSON_UNESCAPED_UNICODE), SQLITE3_TEXT);
            $stmt->bindValue(':total',     count($rowsGames), SQLITE3_INTEGER);
            $stmt->bindValue(':built_at',  $now,          SQLITE3_INTEGER);
            $stmt->execute();

            // win_rate
            $rowsWin    = $agg['win_rows'];
            $payloadWin = [
                'ok'    => true,
                'rows'  => $rowsWin,
                'total' => count($rowsWin),
            ];
            $cacheKeyWin = makeCacheKey('year', $year, 'win_rate', $keyType);

            $stmt = $db->prepare("
                INSERT INTO rank_cache_json
                  (cache_key, scope, ym, kind, key_type, payload, total, built_at)
                VALUES
                  (:cache_key, 'year', :ym, 'win_rate', :key_type, :payload, :total, :built_at)
                ON CONFLICT(cache_key) DO UPDATE SET
                  payload  = excluded.payload,
                  total    = excluded.total,
                  built_at = excluded.built_at
            ");
            $stmt->bindValue(':cache_key', $cacheKeyWin, SQLITE3_TEXT);
            $stmt->bindValue(':ym',        $year,        SQLITE3_TEXT);
            $stmt->bindValue(':key_type',  $keyType,     SQLITE3_TEXT);
            $stmt->bindValue(':payload',   json_encode($payloadWin, JSON_UNESCAPED_UNICODE), SQLITE3_TEXT);
            $stmt->bindValue(':total',     count($rowsWin), SQLITE3_INTEGER);
            $stmt->bindValue(':built_at',  $now,         SQLITE3_INTEGER);
            $stmt->execute();
        }

        logInfo("build yearly cache done: year={$year}");
    }

    $db->exec('COMMIT');

    // === 更新時刻をファイルに書き出す ===
    $tsFile = __DIR__ . '/updaterank.txt';
    // 中身は使わなくてもいいので time() だけ書いておく
    file_put_contents($tsFile, (string)time());
    @chmod($tsFile, 0644);

    $elapsed = microtime(true) - $batchStart;
    logInfo(sprintf('=== ranking cache batch (LIB版) done (%.3f sec) ===', $elapsed));

} catch (Throwable $e) {
    $db->exec('ROLLBACK');
    logError('exception: ' . $e->getMessage());
    exit(1);
}
