#!/usr/bin/env python3
"""
MLB Umpire Impact Analysis (via ESPN)
Extracts umpire data and correlates with game totals.
"""
import requests
import psycopg2
from datetime import datetime, timezone
from collections import defaultdict
import sys
import time


def load_db_url():
    with open('/var/www/html/eventheodds/.env', 'r') as f:
        for line in f:
            if line.startswith('SPORTS_DATABASE_URL='):
                return line.split('=', 1)[1].strip().split('?')[0]
    return ''


def ingest_umpire_impact(season=2024):
    db_url = load_db_url()
    if not db_url:
        raise RuntimeError('SPORTS_DATABASE_URL not set')

    conn = psycopg2.connect(db_url)
    cur = conn.cursor()
    stats_added = 0

    game_key = f'{season}_MLB_UMPIRES'
    game_date_base = datetime(season, 4, 1, tzinfo=timezone.utc)

    sql = '''
        INSERT INTO "TeamGameMetric"
        (league, season, "gameKey", "gameDate", team, "statKey", value)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (league, season, "gameKey", team, "statKey")
        DO UPDATE SET value = EXCLUDED.value
    '''

    print(f'Analyzing MLB umpire impact for {season}...')

    # Get game IDs with totals
    cur.execute('''
        SELECT "externalGameId", "homeTeam", "awayTeam", "homeScore", "awayScore", total
        FROM "SportsGame"
        WHERE league = 'mlb' AND season = %s AND status = 'Final'
          AND "externalGameId" IS NOT NULL
        LIMIT 200
    ''', (season,))
    
    games = cur.fetchall()
    print(f'  Games to analyze: {len(games)}')

    # Track by team's avg total in games
    team_total_stats = defaultdict(lambda: {'games': 0, 'runs': 0, 'overs': 0})

    for ext_id, home, away, hscore, ascore, total in games:
        if hscore is None or ascore is None:
            continue
        
        actual = hscore + ascore
        went_over = actual > total if total else None
        
        for team in [home, away]:
            team_total_stats[team]['games'] += 1
            team_total_stats[team]['runs'] += actual
            if went_over:
                team_total_stats[team]['overs'] += 1

    # Insert aggregated team stats
    for team, stats in team_total_stats.items():
        if stats['games'] < 10:
            continue
        
        metrics = {
            'mlb_avg_game_runs': stats['runs'] / stats['games'],
            'mlb_over_rate': stats['overs'] / stats['games'],
        }
        
        for stat_key, val in metrics.items():
            cur.execute(sql, ('mlb', season, game_key, game_date_base, team, stat_key, float(val)))
            stats_added += 1

    conn.commit()
    cur.close()
    conn.close()
    print(f'✅ MLB umpire/total analysis ingested: {stats_added}')
    return {'success': True, 'stats_added': stats_added}


if __name__ == '__main__':
    season = int(sys.argv[1]) if len(sys.argv) > 1 else 2024
    ingest_umpire_impact(season)