#!/usr/bin/env npx tsx

import pool from '../db';

const DATE_ARG_INDEX = process.argv.indexOf('--date');
const DAYS_ARG_INDEX = process.argv.indexOf('--days');
const TARGET_DATE = DATE_ARG_INDEX >= 0 ? process.argv[DATE_ARG_INDEX + 1] : new Date().toISOString().slice(0, 10);
const LOOKBACK_DAYS = DAYS_ARG_INDEX >= 0 ? Number(process.argv[DAYS_ARG_INDEX + 1]) : 3;

function ensureDate(date: string): string {
  if (!/^\d{4}-\d{2}-\d{2}$/.test(date)) {
    throw new Error(`Invalid date "${date}". Expected YYYY-MM-DD.`);
  }
  return date;
}

function offsetDate(date: string, delta: number): string {
  const dt = new Date(`${date}T00:00:00Z`);
  dt.setUTCDate(dt.getUTCDate() + delta);
  return dt.toISOString().slice(0, 10);
}

async function fetchSchedule(date: string): Promise<any[]> {
  const response = await fetch(`https://statsapi.mlb.com/api/v1/schedule?sportId=1&date=${date}`, {
    signal: AbortSignal.timeout(15000),
  });
  if (!response.ok) {
    throw new Error(`MLB schedule ${response.status} ${response.statusText}`);
  }
  const data = await response.json() as any;
  return data?.dates?.[0]?.games || [];
}

async function fetchBoxscore(gamePk: number): Promise<any> {
  const response = await fetch(`https://statsapi.mlb.com/api/v1/game/${gamePk}/boxscore`, {
    signal: AbortSignal.timeout(15000),
  });
  if (!response.ok) {
    throw new Error(`MLB boxscore ${response.status} ${response.statusText} for ${gamePk}`);
  }
  return response.json();
}

async function ensureTable(): Promise<void> {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS rm_mlb_bullpen_usage (
      id SERIAL PRIMARY KEY,
      game_pk BIGINT NOT NULL,
      game_date DATE NOT NULL,
      team_short TEXT NOT NULL,
      pitcher_id BIGINT NOT NULL,
      pitcher_name TEXT NOT NULL,
      pitches INTEGER,
      outs INTEGER,
      batters_faced INTEGER,
      earned_runs INTEGER,
      runs INTEGER,
      holds INTEGER,
      saves INTEGER,
      blown_saves INTEGER,
      inherited_runners INTEGER,
      inherited_runners_scored INTEGER,
      source TEXT NOT NULL DEFAULT 'mlb_statsapi',
      updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
      created_at TIMESTAMP NOT NULL DEFAULT NOW(),
      UNIQUE (game_pk, team_short, pitcher_id, source)
    )
  `);
  await pool.query(`CREATE INDEX IF NOT EXISTS idx_rm_mlb_bullpen_usage_team_date ON rm_mlb_bullpen_usage (team_short, game_date DESC)`);
}

function toInt(value: any): number {
  const num = Number(value);
  return Number.isFinite(num) ? num : 0;
}

async function upsertTeamBullpen(gamePk: number, gameDate: string, teamBlock: any): Promise<number> {
  const teamShort = String(teamBlock?.team?.abbreviation || '').toUpperCase();
  const players = Object.values(teamBlock?.players || {}) as any[];
  if (!teamShort || players.length === 0) return 0;

  let inserted = 0;
  for (const player of players) {
    const pitching = player?.stats?.pitching || {};
    const pitcherId = Number(player?.person?.id || 0);
    if (!pitcherId) continue;
    const pitches = toInt(pitching.numberOfPitches ?? pitching.pitchesThrown);
    const outs = toInt(pitching.outs);
    const battersFaced = toInt(pitching.battersFaced);
    const gamesPitched = toInt(pitching.gamesPitched);
    const gamesStarted = toInt(pitching.gamesStarted);
    const appeared = gamesPitched > 0 && gamesStarted === 0;
    if (!appeared) continue;

    await pool.query(
      `INSERT INTO rm_mlb_bullpen_usage
         (game_pk, game_date, team_short, pitcher_id, pitcher_name, pitches, outs, batters_faced, earned_runs, runs,
          holds, saves, blown_saves, inherited_runners, inherited_runners_scored, source, updated_at)
       VALUES
         ($1, $2::date, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, 'mlb_statsapi', NOW())
       ON CONFLICT (game_pk, team_short, pitcher_id, source)
       DO UPDATE SET
         pitcher_name = EXCLUDED.pitcher_name,
         pitches = EXCLUDED.pitches,
         outs = EXCLUDED.outs,
         batters_faced = EXCLUDED.batters_faced,
         earned_runs = EXCLUDED.earned_runs,
         runs = EXCLUDED.runs,
         holds = EXCLUDED.holds,
         saves = EXCLUDED.saves,
         blown_saves = EXCLUDED.blown_saves,
         inherited_runners = EXCLUDED.inherited_runners,
         inherited_runners_scored = EXCLUDED.inherited_runners_scored,
         updated_at = NOW()`,
      [
        gamePk,
        gameDate,
        teamShort,
        pitcherId,
        player?.person?.fullName || `Pitcher ${pitcherId}`,
        pitches,
        outs,
        battersFaced,
        toInt(pitching.earnedRuns),
        toInt(pitching.runs),
        toInt(pitching.holds),
        toInt(pitching.saves),
        toInt(pitching.blownSaves),
        toInt(pitching.inheritedRunners),
        toInt(pitching.inheritedRunnersScored),
      ],
    );
    inserted += 1;
  }

  return inserted;
}

async function main(): Promise<void> {
  const date = ensureDate(TARGET_DATE);
  await ensureTable();
  await pool.query(
    `DELETE FROM rm_mlb_bullpen_usage
     WHERE game_date >= $1::date - ($2::text || ' days')::interval
       AND game_date < $1::date
       AND source = 'mlb_statsapi'`,
    [date, LOOKBACK_DAYS],
  );

  let gamesProcessed = 0;
  let rowsUpserted = 0;
  const dates: string[] = [];

  for (let offset = -LOOKBACK_DAYS; offset <= -1; offset += 1) {
    const gameDate = offsetDate(date, offset);
    dates.push(gameDate);
    const games = await fetchSchedule(gameDate);
    for (const game of games) {
      const isFinal = String(game?.status?.detailedState || '').toLowerCase().includes('final');
      if (!isFinal) continue;
      const boxscore = await fetchBoxscore(game.gamePk);
      rowsUpserted += await upsertTeamBullpen(game.gamePk, gameDate, boxscore?.teams?.home);
      rowsUpserted += await upsertTeamBullpen(game.gamePk, gameDate, boxscore?.teams?.away);
      gamesProcessed += 1;
    }
  }

  console.log(JSON.stringify({
    source: 'mlb_statsapi',
    date,
    lookbackDays: LOOKBACK_DAYS,
    dates,
    gamesProcessed,
    rowsUpserted,
    timestamp: new Date().toISOString(),
  }));
}

main()
  .catch((err) => {
    console.error(JSON.stringify({ error: err.message || String(err) }));
    process.exitCode = 1;
  })
  .finally(async () => {
    await pool.end().catch(() => {});
  });