#!/usr/bin/env python3
"""
MLB Bullpen Fatigue Analysis
Tracks recent bullpen usage and correlates with game totals.
"""
import psycopg2
from datetime import datetime, timezone, timedelta
from collections import defaultdict
import sys


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_bullpen_fatigue(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_BULLPEN'
    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 bullpen usage for {season}...')

    # Get games with totals
    cur.execute('''
        SELECT "homeTeam", "awayTeam", "gameDate", "homeScore", "awayScore", total
        FROM "SportsGame"
        WHERE league = 'mlb' AND season = %s AND status = 'Final'
        ORDER BY "gameDate"
    ''', (season,))
    
    games = cur.fetchall()
    print(f'  Games: {len(games)}')

    if len(games) == 0:
        conn.close()
        return {'success': True, 'stats_added': 0}

    # Track game frequency per team
    team_games = defaultdict(list)
    for home, away, gdate, hscore, ascore, total in games:
        actual_total = (hscore or 0) + (ascore or 0)
        went_over = actual_total > total if total else None
        team_games[home].append({'date': gdate, 'total': actual_total, 'line': total, 'over': went_over})
        team_games[away].append({'date': gdate, 'total': actual_total, 'line': total, 'over': went_over})

    # Analyze fatigue pattern: games when team played a lot recently
    for team, games_list in team_games.items():
        games_sorted = sorted(games_list, key=lambda x: x['date'])
        
        heavy_usage_games = 0
        heavy_usage_overs = 0
        normal_games = 0
        normal_overs = 0

        for i, game in enumerate(games_sorted):
            if i < 3:
                continue
            
            # Count games in last 4 days
            recent = [g for g in games_sorted[max(0, i-4):i] 
                     if (game['date'] - g['date']).days <= 4]
            
            if len(recent) >= 3:  # Heavy schedule
                heavy_usage_games += 1
                if game['over']:
                    heavy_usage_overs += 1
            else:
                normal_games += 1
                if game['over']:
                    normal_overs += 1

        metrics = {}
        if heavy_usage_games >= 5:
            metrics['mlb_bullpen_tired_over_rate'] = heavy_usage_overs / heavy_usage_games
            metrics['mlb_bullpen_tired_games'] = heavy_usage_games
        if normal_games >= 5:
            metrics['mlb_normal_rest_over_rate'] = normal_overs / normal_games

        for stat_key, val in metrics.items():
            if val is not None:
                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 bullpen fatigue 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_bullpen_fatigue(season)