<?php
/**
 * get_pitcher_boxscores_cli.php
 * CLEAN PRODUCTION VERSION
 */

date_default_timezone_set('America/Phoenix');
ini_set('display_errors', 1);
error_reporting(E_ALL);
ini_set('max_execution_time', 1500);

require_once 'connection.php';
$mysqli = get_mysql_connection();
$mysqli->set_charset("utf8mb4");

/* ============================================================
   DATE RANGE
   ============================================================ */
$start_date = $argv[1] ?? '2026-02-23';
$end_date   = $argv[2] ?? '2026-02-23';

echo "=== ⚾ Fetching Pitcher Boxscores ($start_date → $end_date) ===\n";

/* ============================================================
   INSERT / UPDATE
   ============================================================ */
function insert_or_update_pitcher($mysqli, $p)
{
    $sql = "
        INSERT INTO MLB_PitcherBoxscores (
            GameID, Season, PlayerID, PlayerName,
            Position, TeamName, Opponent,
            InningsPitched, Hits, Runs, EarnedRuns,
            Walks, StrikeOuts, HomeRuns, Pitches, Strikes,
            GameDate, TeamNameFranchise, TeamFranchise,
            IsStarter, IsWin, IsLoss, IsQualityStart,
            IsSave, IsHold, IsBlownSave, FanDuelPoints
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
            InningsPitched = VALUES(InningsPitched),
            Hits = VALUES(Hits),
            Runs = VALUES(Runs),
            EarnedRuns = VALUES(EarnedRuns),
            Walks = VALUES(Walks),
            StrikeOuts = VALUES(StrikeOuts),
            HomeRuns = VALUES(HomeRuns),
            Pitches = VALUES(Pitches),
            Strikes = VALUES(Strikes),
            IsStarter = VALUES(IsStarter),
            IsWin = VALUES(IsWin),
            IsLoss = VALUES(IsLoss),
            IsQualityStart = VALUES(IsQualityStart),
            IsSave = VALUES(IsSave),
            IsHold = VALUES(IsHold),
            IsBlownSave = VALUES(IsBlownSave),
            FanDuelPoints = VALUES(FanDuelPoints)
    ";

    $stmt = $mysqli->prepare($sql);

    $stmt->bind_param(
        "iiissssdiiiiiiiisssiiiiiiid",
        $p['GameID'],
        $p['Season'],
        $p['PlayerID'],
        $p['PlayerName'],
        $p['Position'],
        $p['TeamName'],
        $p['Opponent'],
        $p['InningsPitched'],
        $p['Hits'],
        $p['Runs'],
        $p['EarnedRuns'],
        $p['Walks'],
        $p['StrikeOuts'],
        $p['HomeRuns'],
        $p['Pitches'],
        $p['Strikes'],
        $p['GameDate'],
        $p['TeamNameFranchise'],
        $p['TeamFranchise'],
        $p['IsStarter'],
        $p['IsWin'],
        $p['IsLoss'],
        $p['IsQualityStart'],
        $p['IsSave'],
        $p['IsHold'],
        $p['IsBlownSave'],
        $p['FanDuelPoints']
    );

    $stmt->execute();
    $stmt->close();
}

/* ============================================================
   FETCH SCHEDULE
   ============================================================ */
$url = "https://statsapi.mlb.com/api/v1/schedule?sportId=1"
     . "&gameType=S&startDate=$start_date&endDate=$end_date";

$json = @file_get_contents($url);
if (!$json) die("❌ Could not fetch schedule\n");

$data = json_decode($json, true);
if (empty($data['dates'])) die("No games found.\n");

$totalGames = 0;
$totalPitchers = 0;

/* ============================================================
   MAIN LOOP
   ============================================================ */
foreach ($data['dates'] as $day) {
    foreach ($day['games'] as $game) {

        $gamePk   = $game['gamePk'];
        $gameDate = date('Y-m-d', strtotime($game['gameDate']));
        $season   = intval(substr($gameDate, 0, 4));

        $liveUrl = "https://statsapi.mlb.com/api/v1.1/game/$gamePk/feed/live";
        $liveJson = @file_get_contents($liveUrl);
        if (!$liveJson) continue;

        $live = json_decode($liveJson, true);
        $teams = $live['liveData']['boxscore']['teams'] ?? [];
        if (!$teams) continue;

        /* OFFICIAL DECISIONS */
        $decisions = $live['liveData']['decisions'] ?? [];
        $winningPitcherId = $decisions['winner']['id'] ?? null;
        $losingPitcherId  = $decisions['loser']['id'] ?? null;
        $savePitcherId    = $decisions['save']['id'] ?? null;
        
        foreach (['home','away'] as $side) {
            $teamAbbr = $game['teams'][$side]['team']['abbreviation'] ?? '';
            $teamName = $teams[$side]['team']['name'] ?? '';
            $teamName = $teams[$side]['team']['name'] ?? '';
            $opponent = $teams[$side === 'home' ? 'away' : 'home']['team']['name'] ?? '';

            $players = $teams[$side]['players'] ?? [];
            $pitcherOrder = $teams[$side]['pitchers'] ?? [];
            $starterId = $pitcherOrder[0] ?? null;

            foreach ($players as $pdata) {

                if (!isset($pdata['stats']['pitching'])) continue;

                $ps = $pdata['stats']['pitching'];

                $playerId = $pdata['person']['id'] ?? 0;
                $playerName = $pdata['person']['fullName'] ?? '';

                $innings = floatval($ps['inningsPitched'] ?? 0);
                $earnedRuns = intval($ps['earnedRuns'] ?? 0);

                /* FLAGS */
                $isStarter = ($playerId == $starterId) ? 1 : 0;
                $isWin  = ($playerId == $winningPitcherId) ? 1 : 0;
                $isLoss = ($playerId == $losingPitcherId) ? 1 : 0;
                $isSave = ($playerId == $savePitcherId) ? 1 : 0;

                $isHold = intval($ps['holds'] ?? 0);
                $isBlownSave = intval($ps['blownSaves'] ?? 0);

                $isQualityStart = (
                    $isStarter &&
                    $innings >= 6 &&
                    $earnedRuns <= 3
                ) ? 1 : 0;

                /* FanDuel scoring */
                $fanDuelPoints =
                    ($innings * 3) +
                    (($ps['strikeOuts'] ?? 0) * 3) +
                    ($isWin * 6) +
                    ($isQualityStart * 4) -
                    ($earnedRuns * 3);

                $p = [
                    'GameID' => $gamePk,
                    'Season' => $season,
                    'PlayerID' => $playerId,
                    'PlayerName' => $playerName,
                    'Position' => $pdata['position']['abbreviation'] ?? 'P',
                    'TeamName' => $teamName,
                    'Opponent' => $opponent,
                    'InningsPitched' => $innings,
                    'Hits' => $ps['hits'] ?? 0,
                    'Runs' => $ps['runs'] ?? 0,
                    'EarnedRuns' => $earnedRuns,
                    'Walks' => $ps['baseOnBalls'] ?? 0,
                    'StrikeOuts' => $ps['strikeOuts'] ?? 0,
                    'HomeRuns' => $ps['homeRuns'] ?? 0,
                    'Pitches' => $ps['pitchesThrown'] ?? 0,
                    'Strikes' => $ps['strikes'] ?? 0,
                    'GameDate' => $gameDate,
                    'TeamNameFranchise' => $teamName,
                    'TeamFranchise' => $teamAbbr,
                    'IsStarter' => $isStarter,
                    'IsWin' => $isWin,
                    'IsLoss' => $isLoss,
                    'IsQualityStart' => $isQualityStart,
                    'IsSave' => $isSave,
                    'IsHold' => $isHold,
                    'IsBlownSave' => $isBlownSave,
                    'FanDuelPoints' => $fanDuelPoints
                ];

                insert_or_update_pitcher($mysqli, $p);
                $totalPitchers++;
            }
        }

        echo "✔ Processed GameID: $gamePk ($gameDate)\n";
        $totalGames++;
        flush();
        usleep(100000);
    }
}

echo "\n=== ✅ COMPLETED SUCCESSFULLY ===\n";
echo "Games Processed: $totalGames\n";
echo "Pitchers Inserted/Updated: $totalPitchers\n";

$mysqli->close();
?>
