#!/usr/bin/env python3
"""
NBA Game Scores - nba_api
Fetches game-by-game scores for totals analysis
Calculates combined scores for O/U comparison
"""
from nba_api.stats.endpoints import leaguegamelog, teamestimatedmetrics
import psycopg2
import time
from datetime import datetime, timezone

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_nba_game_scores(season='2024-25'):
    """Ingest NBA game scores for totals analysis"""
    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
    
    season_year = int(season.split('-')[0])
    
    sql = '''
        INSERT INTO "TeamGameMetric"
        (league, season, "gameKey", "gameDate", team, opponent, "statKey", value)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (league, season, "gameKey", team, "statKey")
        DO UPDATE SET value = EXCLUDED.value
    '''
    
    # Fetch game log
    print(f'Fetching NBA game scores for {season}...')
    time.sleep(1)
    try:
        gamelog = leaguegamelog.LeagueGameLog(
            season=season,
            season_type_all_star='Regular Season',
            player_or_team_abbreviation='T'
        )
        df = gamelog.get_data_frames()[0]
        print(f'  Found {len(df)} team-game records')
        
        # Group games by GAME_ID to get both teams
        games = {}
        for _, row in df.iterrows():
            game_id = row['GAME_ID']
            team = row['TEAM_ABBREVIATION']
            pts = row['PTS']
            game_date = row['GAME_DATE']
            matchup = row['MATCHUP']
            wl = row['WL']
            
            if game_id not in games:
                games[game_id] = {'date': game_date, 'teams': []}
            
            # Parse opponent from matchup (e.g., "BOS vs. NYK" or "NYK @ BOS")
            if ' vs. ' in matchup:
                parts = matchup.split(' vs. ')
                opponent = parts[1]
            elif ' @ ' in matchup:
                parts = matchup.split(' @ ')
                opponent = parts[1]
            else:
                opponent = ''
            
            games[game_id]['teams'].append({
                'team': team,
                'opponent': opponent,
                'pts': pts,
                'wl': wl,
                'matchup': matchup,
                'pace': row.get('PACE', None),  # Some endpoints include pace
                # Additional stats
                'reb': row.get('REB'),
                'ast': row.get('AST'),
                'fgm': row.get('FGM'),
                'fga': row.get('FGA'),
                'fg3m': row.get('FG3M'),
                'fg3a': row.get('FG3A'),
            })
        
        print(f'  Processing {len(games)} games...')
        
        game_count = 0
        for game_id, game_data in games.items():
            game_date_str = game_data['date']
            try:
                game_date = datetime.strptime(game_date_str, '%Y-%m-%d').replace(tzinfo=timezone.utc)
            except:
                game_date = datetime(season_year, 10, 1, tzinfo=timezone.utc)
            
            # Calculate combined total
            total_pts = sum(t['pts'] or 0 for t in game_data['teams'])
            
            for team_data in game_data['teams']:
                team = team_data['team']
                opponent = team_data['opponent']
                pts = team_data['pts']
                
                # Store individual team stats
                metrics = [
                    ('nba_game_pts', pts),
                    ('nba_game_reb', team_data.get('reb')),
                    ('nba_game_ast', team_data.get('ast')),
                    ('nba_game_fgm', team_data.get('fgm')),
                    ('nba_game_fga', team_data.get('fga')),
                    ('nba_game_fg3m', team_data.get('fg3m')),
                    ('nba_game_fg3a', team_data.get('fg3a')),
                    ('nba_game_total', total_pts),  # Combined score for O/U
                    ('nba_game_win', 1 if team_data['wl'] == 'W' else 0),
                ]
                
                for stat_key, val in metrics:
                    if val is not None:
                        try:
                            cur.execute(sql, (
                                'nba', season_year, game_id, game_date,
                                team, opponent, stat_key, float(val)
                            ))
                            stats_added += 1
                        except Exception as e:
                            conn.rollback()
                
                game_count += 1
        
        conn.commit()
        print(f'  Game stats added: {stats_added}')
        
    except Exception as e:
        print(f'Error fetching game log: {e}')
    
    # Also fetch team pace estimates for pace-adjusted analysis
    print(f'Fetching team estimated metrics (pace)...')
    try:
        time.sleep(1)
        metrics = teamestimatedmetrics.TeamEstimatedMetrics(season=season)
        df = metrics.get_data_frames()[0]
        print(f'  Found {len(df)} teams')
        
        game_key = f'{season}_NBA_PACE'
        game_date = datetime(season_year, 10, 1, tzinfo=timezone.utc)
        
        pace_count = 0
        for _, row in df.iterrows():
            team = row.get('TEAM_ABBREVIATION', '')
            if not team:
                continue
            
            pace_metrics = [
                ('nba_season_pace', row.get('E_PACE')),
                ('nba_season_off_rating', row.get('E_OFF_RATING')),
                ('nba_season_def_rating', row.get('E_DEF_RATING')),
                ('nba_season_net_rating', row.get('E_NET_RATING')),
            ]
            
            for stat_key, val in pace_metrics:
                if val is not None:
                    try:
                        cur.execute(sql, (
                            'nba', season_year, game_key, game_date,
                            team, None, stat_key, float(val)
                        ))
                        stats_added += 1
                        pace_count += 1
                    except:
                        conn.rollback()
        
        conn.commit()
        print(f'  Pace metrics added: {pace_count}')
        
    except Exception as e:
        print(f'Error fetching pace: {e}')
    
    cur.close()
    conn.close()
    
    print(f'')
    print(f'✅ NBA Game Scores ingested: {stats_added} total metrics')
    return {'success': True, 'stats_added': stats_added}

if __name__ == '__main__':
    import sys
    season = sys.argv[1] if len(sys.argv) > 1 else '2024-25'
    ingest_nba_game_scores(season)