#!/usr/bin/env python3
"""
MLB Weather Micro-Effects Analysis
Analyzes wind, humidity, and park factors impact on totals.
"""
import psycopg2
from datetime import datetime, timezone
from collections import defaultdict
import sys


# Park factors (runs/game relative to avg)
PARK_FACTORS = {
    'COL': 1.25, 'CIN': 1.10, 'TEX': 1.08, 'PHI': 1.05, 'BOS': 1.05,
    'CHC': 1.03, 'BAL': 1.02, 'ATL': 1.00, 'MIL': 1.00, 'TOR': 1.00,
    'NYY': 0.98, 'CLE': 0.98, 'HOU': 0.97, 'LAD': 0.97, 'WSH': 0.97,
    'MIN': 0.96, 'DET': 0.95, 'KC': 0.95, 'SD': 0.94, 'SF': 0.93,
    'OAK': 0.92, 'SEA': 0.92, 'MIA': 0.90, 'TB': 0.90, 'NYM': 0.95,
    'LAA': 0.98, 'ARI': 1.02, 'PIT': 0.96, 'STL': 0.98, 'CHW': 0.97
}


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_weather_effects(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_WEATHER'
    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 weather effects for {season}...')

    # Get games with weather data
    cur.execute('''
        SELECT g."homeTeam", g."awayTeam", g."homeScore", g."awayScore", g.total,
               w."windSpeed", w."windDirection", w.humidity, w.temperature
        FROM "SportsGame" g
        LEFT JOIN "GameWeather" w ON g."externalGameId" = w."gameId"
        WHERE g.league = 'mlb' AND g.season = %s AND g.status = 'Final'
    ''', (season,))
    
    games = cur.fetchall()
    print(f'  Games: {len(games)}')

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

    # Track by team and conditions
    team_stats = defaultdict(lambda: {
        'high_wind_games': 0, 'high_wind_overs': 0,
        'high_temp_games': 0, 'high_temp_overs': 0,
        'total_games': 0, 'total_overs': 0
    })

    for home, away, hscore, ascore, total, wind_spd, wind_dir, humid, temp 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_stats[team]['total_games'] += 1
            if went_over:
                team_stats[team]['total_overs'] += 1

            # High wind (>10mph)
            if wind_spd and wind_spd > 10:
                team_stats[team]['high_wind_games'] += 1
                if went_over:
                    team_stats[team]['high_wind_overs'] += 1

            # High temp (>85F)
            if temp and temp > 85:
                team_stats[team]['high_temp_games'] += 1
                if went_over:
                    team_stats[team]['high_temp_overs'] += 1

    # Insert metrics
    for team, stats in team_stats.items():
        metrics = {}
        
        if stats['high_wind_games'] >= 5:
            metrics['mlb_high_wind_over_rate'] = stats['high_wind_overs'] / stats['high_wind_games']
        if stats['high_temp_games'] >= 5:
            metrics['mlb_high_temp_over_rate'] = stats['high_temp_overs'] / stats['high_temp_games']
        if stats['total_games'] >= 10:
            metrics['mlb_overall_over_rate'] = stats['total_overs'] / stats['total_games']
        
        # Add park factor if known
        if team in PARK_FACTORS:
            metrics['mlb_park_factor'] = PARK_FACTORS[team]

        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 weather effects 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_weather_effects(season)