#!/usr/bin/env npx tsx

import pool from '../db';

interface MlbLineupPlayer {
  name: string;
  position: string;
  injuryStatus: string;
}

interface MlbGameLineupRow {
  gameDate: string;
  awayTeam: string;
  homeTeam: string;
  gameTime: string;
  awayStatus: string;
  homeStatus: string;
  awayPlayers: MlbLineupPlayer[];
  homePlayers: MlbLineupPlayer[];
}

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

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;
}

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

function mapPlayers(players: any[] | undefined): MlbLineupPlayer[] {
  if (!Array.isArray(players)) return [];
  return players.map((player: any) => ({
    name: player?.fullName || player?.useName || 'Unknown',
    position: player?.primaryPosition?.abbreviation || '?',
    injuryStatus: 'Healthy',
  }));
}

function mapGame(game: any): MlbGameLineupRow {
  const homePlayers = mapPlayers(game?.lineups?.homePlayers);
  const awayPlayers = mapPlayers(game?.lineups?.awayPlayers);
  return {
    gameDate: game?.officialDate || TARGET_DATE,
    awayTeam: game?.teams?.away?.team?.abbreviation || '',
    homeTeam: game?.teams?.home?.team?.abbreviation || '',
    gameTime: game?.gameDate || '',
    awayStatus: awayPlayers.length >= 9 ? 'Confirmed Lineup' : 'Unknown Lineup',
    homeStatus: homePlayers.length >= 9 ? 'Confirmed Lineup' : 'Unknown Lineup',
    awayPlayers,
    homePlayers,
  };
}

async function ensureTableShape(): Promise<void> {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS "GameLineup" (
      id SERIAL PRIMARY KEY,
      league TEXT NOT NULL,
      "gameDate" DATE NOT NULL DEFAULT CURRENT_DATE,
      "awayTeam" TEXT,
      "homeTeam" TEXT,
      "gameTime" TEXT,
      odds TEXT,
      "awayStatus" TEXT DEFAULT 'Expected',
      "homeStatus" TEXT DEFAULT 'Expected',
      "awayPlayers" JSONB,
      "homePlayers" JSONB,
      source TEXT DEFAULT 'rotowire',
      "updatedAt" TIMESTAMP DEFAULT NOW(),
      "createdAt" TIMESTAMP DEFAULT NOW(),
      UNIQUE(league, "gameDate", "awayTeam", "homeTeam", source)
    )
  `);

  const newCols = [
    ['"awayProjMinutes"', 'JSONB'],
    ['"homeProjMinutes"', 'JSONB'],
    ['"awayUsageStats"', 'JSONB'],
    ['"homeUsageStats"', 'JSONB'],
  ];
  for (const [col, type] of newCols) {
    await pool.query(`ALTER TABLE "GameLineup" ADD COLUMN IF NOT EXISTS ${col} ${type}`).catch(() => {});
  }
}

async function upsertGames(games: MlbGameLineupRow[]): Promise<number> {
  let inserted = 0;
  for (const game of games) {
    if (!game.homeTeam || !game.awayTeam) continue;
    await pool.query(
      `INSERT INTO "GameLineup"
         (league, "gameDate", "awayTeam", "homeTeam", "gameTime", odds,
          "awayStatus", "homeStatus", "awayPlayers", "homePlayers",
          "awayProjMinutes", "homeProjMinutes", "awayUsageStats", "homeUsageStats",
          source, "updatedAt")
       VALUES ('mlb', $1::date, $2, $3, $4, NULL, $5, $6, $7::jsonb, $8::jsonb, NULL, NULL, NULL, NULL, 'mlb_statsapi', NOW())
       ON CONFLICT (league, "gameDate", "awayTeam", "homeTeam", source)
       DO UPDATE SET
         "gameTime" = EXCLUDED."gameTime",
         "awayStatus" = EXCLUDED."awayStatus",
         "homeStatus" = EXCLUDED."homeStatus",
         "awayPlayers" = EXCLUDED."awayPlayers",
         "homePlayers" = EXCLUDED."homePlayers",
         "updatedAt" = NOW()`,
      [
        game.gameDate,
        game.awayTeam,
        game.homeTeam,
        game.gameTime,
        game.awayStatus,
        game.homeStatus,
        JSON.stringify(game.awayPlayers),
        JSON.stringify(game.homePlayers),
      ],
    );
    inserted += 1;
  }
  return inserted;
}

async function main(): Promise<void> {
  const date = ensureDate(TARGET_DATE);
  await ensureTableShape();
  const games = await fetchSchedule(date);
  const mapped = games.map(mapGame);
  const inserted = await upsertGames(mapped);

  console.log(JSON.stringify({
    source: 'mlb_statsapi',
    league: 'mlb',
    date,
    games: mapped.length,
    inserted,
    confirmedGames: mapped.filter(g => g.homeStatus === 'Confirmed Lineup' || g.awayStatus === 'Confirmed Lineup').length,
    teams: mapped.map(g => `${g.awayTeam}@${g.homeTeam}`),
    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(() => {});
  });