#!/usr/bin/env python3
"""
Ingest NBA referee impact metrics (avg total, avg FTA, avg fouls) into TeamGameMetric.
Uses nba_api boxscoreofficials + boxscoretraditionalv2.
"""
import sys
from datetime import datetime, timezone

import psycopg2


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 season_str_from_end_year(end_year: int) -> str:
    start = end_year - 1
    return f"{start}-{str(end_year)[-2:]}"


def ingest_referees(season_end: int, limit_games: int = 0):
    try:
        from nba_api.stats.endpoints import leaguegamelog, boxscoreofficials, boxscoretraditionalv2
    except Exception as e:
        print("Missing dependency: nba_api. Install it first:", file=sys.stderr)
        print("  pip install nba_api", file=sys.stderr)
        raise

    db_url = load_db_url()
    if not db_url:
        raise RuntimeError('SPORTS_DATABASE_URL not set')

    season_str = season_str_from_end_year(season_end)
    games_df = leaguegamelog.LeagueGameLog(
        season=season_str,
        season_type_all_star='Regular Season',
        timeout=60
    ).get_data_frames()[0]

    games = games_df[['GAME_ID', 'GAME_DATE', 'PTS']].drop_duplicates('GAME_ID')
    if limit_games and limit_games > 0:
        games = games.head(limit_games)

    ref_stats = {}
    for _, row in games.iterrows():
        game_id = str(row['GAME_ID'])
        try:
            officials_df = boxscoreofficials.BoxScoreOfficials(game_id=game_id, timeout=30).get_data_frames()[0]
            officials = officials_df['OFFICIAL_NAME'].tolist() if 'OFFICIAL_NAME' in officials_df else []
            if not officials:
                continue
            bs = boxscoretraditionalv2.BoxScoreTraditionalV2(game_id=game_id, timeout=30).get_data_frames()
            team_df = bs[1] if len(bs) > 1 else None
            if team_df is None or team_df.empty:
                continue
            total_pts = team_df['PTS'].sum()
            total_fta = team_df['FTA'].sum() if 'FTA' in team_df else 0
            total_pf = team_df['PF'].sum() if 'PF' in team_df else 0
        except Exception:
            continue

        for ref in officials:
            if not ref:
                continue
            ref_stats.setdefault(ref, {'games': 0, 'total_pts': 0.0, 'total_fta': 0.0, 'total_pf': 0.0})
            ref_stats[ref]['games'] += 1
            ref_stats[ref]['total_pts'] += float(total_pts)
            ref_stats[ref]['total_fta'] += float(total_fta)
            ref_stats[ref]['total_pf'] += float(total_pf)

    conn = psycopg2.connect(db_url)
    cur = conn.cursor()
    game_key = f'{season_end}_NBA_REF'
    game_date = datetime(season_end, 10, 1, tzinfo=timezone.utc)

    sql_upsert = '''
        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
    '''

    written = 0
    for ref, data in ref_stats.items():
        games = data['games']
        if games <= 0:
            continue
        avg_total = data['total_pts'] / games
        avg_fta = data['total_fta'] / games
        avg_fouls = data['total_pf'] / games

        for stat_key, val in [
            ('nba_ref_avg_total', avg_total),
            ('nba_ref_avg_fta', avg_fta),
            ('nba_ref_avg_fouls', avg_fouls),
            ('nba_ref_games', games),
        ]:
            cur.execute(sql_upsert, ('nba', season_end, game_key, game_date, ref, stat_key, float(val)))
            written += 1

    conn.commit()
    cur.close()
    conn.close()
    print(f'✅ NBA referee metrics ingested: {written}')


if __name__ == '__main__':
    season = int(sys.argv[1]) if len(sys.argv) > 1 else 2025
    limit = int(sys.argv[2]) if len(sys.argv) > 2 else 0
    ingest_referees(season, limit_games=limit)