#!/usr/bin/env python3
"""
Ingest NBA lineup metrics (starter vs bench) into TeamGameMetric using nba_api.
"""
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_lineups(season_end: int):
    try:
        from nba_api.stats.endpoints import leaguedashlineups
    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)
    df = leaguedashlineups.LeagueDashLineups(
        season=season_str,
        group_quantity=5,
        per_mode_detailed='Per100Possessions',
        timeout=60
    ).get_data_frames()[0]

    # Columns we care about: TEAM_ABBREVIATION, MIN, PACE, OFF_RATING, NET_RATING
    rows = df.to_dict("records")

    by_team = {}
    for r in rows:
        team = r.get('TEAM_ABBREVIATION')
        mins = float(r.get('MIN') or 0)
        if not team or mins <= 0:
            continue
        by_team.setdefault(team, []).append(r)

    metrics = {}
    for team, lineups in by_team.items():
        lineups.sort(key=lambda x: float(x.get('MIN') or 0), reverse=True)
        starter = lineups[0]
        bench = lineups[1:] if len(lineups) > 1 else []

        def wavg(items, key):
            total = sum(float(i.get('MIN') or 0) for i in items)
            if total <= 0:
                return None
            return sum(float(i.get(key) or 0) * float(i.get('MIN') or 0) for i in items) / total

        starter_pace = float(starter.get('PACE') or 0)
        starter_ortg = float(starter.get('OFF_RATING') or 0)
        starter_net = float(starter.get('NET_RATING') or 0)
        bench_pace = wavg(bench, 'PACE')
        bench_ortg = wavg(bench, 'OFF_RATING')
        bench_net = wavg(bench, 'NET_RATING')

        if bench_pace is None:
            bench_pace = starter_pace
        if bench_ortg is None:
            bench_ortg = starter_ortg
        if bench_net is None:
            bench_net = starter_net

        metrics[team] = {
            'nba_starter_pace': starter_pace,
            'nba_bench_pace': bench_pace,
            'nba_pace_diff': starter_pace - bench_pace,
            'nba_starter_ortg': starter_ortg,
            'nba_bench_ortg': bench_ortg,
            'nba_ortg_diff': starter_ortg - bench_ortg,
            'nba_starter_net': starter_net,
            'nba_bench_net': bench_net,
        }

    conn = psycopg2.connect(db_url)
    cur = conn.cursor()
    game_key = f'{season_end}_NBA_LINEUPS'
    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 team, stats in metrics.items():
        for stat_key, val in stats.items():
            cur.execute(sql_upsert, ('nba', season_end, game_key, game_date, team, stat_key, float(val)))
            written += 1

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


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