#!/usr/bin/env python3
"""
NFL Advanced Stats - nfl_data_py
Fetches CPOE, Success Rate, Air Yards, Pressure Rate, and more
"""
import nfl_data_py as nfl
import psycopg2
import pandas as pd
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_nfl_advanced(season=2024):
    """Ingest NFL advanced stats from nfl_data_py"""
    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}_NFL_SEASON'
    game_date = datetime(season, 9, 1, tzinfo=timezone.utc)
    
    sql_team = '''
        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
    '''
    
    sql_player = '''
        INSERT INTO "PlayerGameMetric"
        (league, season, "gameKey", "gameDate", "playerExternalId", "playerName", team, "statKey", value)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (league, season, "gameKey", "playerExternalId", "statKey")
        DO UPDATE SET value = EXCLUDED.value
    '''
    
    # 1. Seasonal QB Stats (CPOE, Air Yards, etc.)
    print(f'Fetching NFL seasonal stats for {season}...')
    try:
        seasonal = nfl.import_seasonal_data([season])
        print(f'  Found {len(seasonal)} player-seasons')
        
        qb_count = 0
        for _, row in seasonal.iterrows():
            player_id = str(row.get('player_id', ''))
            player_name = row.get('player_display_name', row.get('player_name', ''))
            team = row.get('recent_team', '')
            
            if not player_id or not player_name:
                continue
            
            # QB-specific metrics
            metrics = [
                ('nfl_completions', row.get('completions')),
                ('nfl_attempts', row.get('attempts')),
                ('nfl_passing_yards', row.get('passing_yards')),
                ('nfl_passing_tds', row.get('passing_tds')),
                ('nfl_interceptions', row.get('interceptions')),
                ('nfl_sacks', row.get('sacks')),
                ('nfl_sack_yards', row.get('sack_yards')),
                ('nfl_passing_air_yards', row.get('passing_air_yards')),
                ('nfl_passing_yac', row.get('passing_yards_after_catch')),
                ('nfl_passing_first_downs', row.get('passing_first_downs')),
                ('nfl_passing_epa', row.get('passing_epa')),
                ('nfl_passing_2pt', row.get('passing_2pt_conversions')),
                ('nfl_pacr', row.get('pacr')),  # Passer Air Conversion Ratio
                ('nfl_dakota', row.get('dakota')),  # CPOE composite
                # Rushing
                ('nfl_carries', row.get('carries')),
                ('nfl_rushing_yards', row.get('rushing_yards')),
                ('nfl_rushing_tds', row.get('rushing_tds')),
                ('nfl_rushing_first_downs', row.get('rushing_first_downs')),
                ('nfl_rushing_epa', row.get('rushing_epa')),
                # Receiving
                ('nfl_receptions', row.get('receptions')),
                ('nfl_targets', row.get('targets')),
                ('nfl_receiving_yards', row.get('receiving_yards')),
                ('nfl_receiving_tds', row.get('receiving_tds')),
                ('nfl_receiving_air_yards', row.get('receiving_air_yards')),
                ('nfl_receiving_yac', row.get('receiving_yards_after_catch')),
                ('nfl_receiving_first_downs', row.get('receiving_first_downs')),
                ('nfl_receiving_epa', row.get('receiving_epa')),
                ('nfl_racr', row.get('racr')),  # Receiver Air Conversion Ratio
                ('nfl_wopr', row.get('wopr')),  # Weighted Opportunity Rating
                # Fantasy
                ('nfl_fantasy_points', row.get('fantasy_points')),
                ('nfl_fantasy_points_ppr', row.get('fantasy_points_ppr')),
            ]
            
            for stat_key, val in metrics:
                if val is not None and pd.notna(val):
                    try:
                        cur.execute(sql_player, (
                            'nfl', season, game_key, game_date,
                            player_id, player_name, team,
                            stat_key, float(val)
                        ))
                        stats_added += 1
                        qb_count += 1
                    except:
                        conn.rollback()
        
        conn.commit()
        print(f'  Seasonal player stats added: {qb_count}')
    except Exception as e:
        print(f'Error fetching seasonal: {e}')
    
    # 2. Next-Gen Stats (if available)
    print(f'Fetching NFL Next-Gen stats for {season}...')
    try:
        ngs_passing = nfl.import_ngs_data('passing', [season])
        print(f'  Found {len(ngs_passing)} NGS passing records')
        
        ngs_count = 0
        for _, row in ngs_passing.iterrows():
            player_id = str(row.get('player_gsis_id', row.get('player_id', '')))
            player_name = row.get('player_display_name', row.get('player_name', ''))
            team = row.get('team_abbr', '')
            
            if not player_name:
                continue
            
            metrics = [
                ('nfl_ngs_avg_time_to_throw', row.get('avg_time_to_throw')),
                ('nfl_ngs_avg_completed_air_yards', row.get('avg_completed_air_yards')),
                ('nfl_ngs_avg_intended_air_yards', row.get('avg_intended_air_yards')),
                ('nfl_ngs_avg_air_yards_differential', row.get('avg_air_yards_differential')),
                ('nfl_ngs_aggressiveness', row.get('aggressiveness')),
                ('nfl_ngs_max_completed_air_distance', row.get('max_completed_air_distance')),
                ('nfl_ngs_avg_air_yards_to_sticks', row.get('avg_air_yards_to_sticks')),
                ('nfl_ngs_passer_rating', row.get('passer_rating')),
                ('nfl_ngs_completion_pct', row.get('completion_percentage')),
                ('nfl_ngs_expected_completion_pct', row.get('expected_completion_percentage')),
                ('nfl_ngs_cpoe', row.get('completion_percentage_above_expectation')),  # CPOE!
            ]
            
            for stat_key, val in metrics:
                if val is not None and pd.notna(val):
                    try:
                        cur.execute(sql_player, (
                            'nfl', season, game_key, game_date,
                            player_id or player_name, player_name, team, team,
                            stat_key, float(val)
                        ))
                        stats_added += 1
                        ngs_count += 1
                    except:
                        conn.rollback()
        
        conn.commit()
        print(f'  NGS passing stats added: {ngs_count}')
    except Exception as e:
        print(f'Error fetching NGS passing: {e}')
    
    # 3. NGS Rushing
    try:
        ngs_rushing = nfl.import_ngs_data('rushing', [season])
        print(f'  Found {len(ngs_rushing)} NGS rushing records')
        
        rush_count = 0
        for _, row in ngs_rushing.iterrows():
            player_name = row.get('player_display_name', row.get('player_name', ''))
            player_id = str(row.get('player_gsis_id', player_name))
            team = row.get('team_abbr', '')
            
            if not player_name:
                continue
            
            metrics = [
                ('nfl_ngs_efficiency', row.get('efficiency')),
                ('nfl_ngs_percent_attempts_gte_8', row.get('percent_attempts_gte_eight_defenders')),
                ('nfl_ngs_avg_time_to_los', row.get('avg_time_to_los')),
                ('nfl_ngs_rush_yards_over_expected', row.get('rush_yards_over_expected')),
                ('nfl_ngs_rush_yards_over_expected_per_att', row.get('rush_yards_over_expected_per_att')),
                ('nfl_ngs_rush_pct_over_expected', row.get('rush_pct_over_expected')),
            ]
            
            for stat_key, val in metrics:
                if val is not None and pd.notna(val):
                    try:
                        cur.execute(sql_player, (
                            'nfl', season, game_key, game_date,
                            player_id, player_name, team,
                            stat_key, float(val)
                        ))
                        stats_added += 1
                        rush_count += 1
                    except:
                        conn.rollback()
        
        conn.commit()
        print(f'  NGS rushing stats added: {rush_count}')
    except Exception as e:
        print(f'Error fetching NGS rushing: {e}')
    
    # 4. NGS Receiving
    try:
        ngs_receiving = nfl.import_ngs_data('receiving', [season])
        print(f'  Found {len(ngs_receiving)} NGS receiving records')
        
        rec_count = 0
        for _, row in ngs_receiving.iterrows():
            player_name = row.get('player_display_name', row.get('player_name', ''))
            player_id = str(row.get('player_gsis_id', player_name))
            team = row.get('team_abbr', '')
            
            if not player_name:
                continue
            
            metrics = [
                ('nfl_ngs_avg_cushion', row.get('avg_cushion')),
                ('nfl_ngs_avg_separation', row.get('avg_separation')),
                ('nfl_ngs_avg_intended_air_yards', row.get('avg_intended_air_yards')),
                ('nfl_ngs_pct_share_of_intended_air_yards', row.get('percent_share_of_intended_air_yards')),
                ('nfl_ngs_catch_pct', row.get('catch_percentage')),
                ('nfl_ngs_avg_yac', row.get('avg_yac')),
                ('nfl_ngs_avg_expected_yac', row.get('avg_expected_yac')),
                ('nfl_ngs_yac_over_expected', row.get('avg_yac_above_expectation')),
            ]
            
            for stat_key, val in metrics:
                if val is not None and pd.notna(val):
                    try:
                        cur.execute(sql_player, (
                            'nfl', season, game_key, game_date,
                            player_id, player_name, team,
                            stat_key, float(val)
                        ))
                        stats_added += 1
                        rec_count += 1
                    except:
                        conn.rollback()
        
        conn.commit()
        print(f'  NGS receiving stats added: {rec_count}')
    except Exception as e:
        print(f'Error fetching NGS receiving: {e}')
    
    # 5. Team-level stats
    print(f'Fetching NFL team stats for {season}...')
    try:
        # Use play-by-play to calculate team success rates
        pbp = nfl.import_pbp_data([season])
        print(f'  Processing {len(pbp)} plays for team stats...')
        
        # Filter to regular plays
        plays = pbp[pbp['play_type'].isin(['pass', 'run'])]
        
        # Calculate team offense stats
        team_off = plays.groupby('posteam').agg({
            'epa': 'mean',
            'success': 'mean',
            'yards_gained': 'mean',
            'air_yards': 'mean',
            'yards_after_catch': 'mean',
        }).reset_index()
        
        team_count = 0
        for _, row in team_off.iterrows():
            team = row['posteam']
            if not team or pd.isna(team):
                continue
            
            metrics = [
                ('nfl_off_epa_play', row.get('epa')),
                ('nfl_off_success_rate', row.get('success')),
                ('nfl_off_yards_play', row.get('yards_gained')),
                ('nfl_off_air_yards_avg', row.get('air_yards')),
                ('nfl_off_yac_avg', row.get('yards_after_catch')),
            ]
            
            for stat_key, val in metrics:
                if val is not None and pd.notna(val):
                    try:
                        cur.execute(sql_team, ('nfl', season, game_key, game_date, team, stat_key, float(val)))
                        stats_added += 1
                        team_count += 1
                    except:
                        conn.rollback()
        
        # Calculate team defense stats
        team_def = plays.groupby('defteam').agg({
            'epa': 'mean',
            'success': 'mean',
        }).reset_index()
        
        for _, row in team_def.iterrows():
            team = row['defteam']
            if not team or pd.isna(team):
                continue
            
            metrics = [
                ('nfl_def_epa_play', row.get('epa')),
                ('nfl_def_success_rate', row.get('success')),
            ]
            
            for stat_key, val in metrics:
                if val is not None and pd.notna(val):
                    try:
                        cur.execute(sql_team, ('nfl', season, game_key, game_date, team, stat_key, float(val)))
                        stats_added += 1
                        team_count += 1
                    except:
                        conn.rollback()
        
        conn.commit()
        print(f'  Team EPA/Success stats added: {team_count}')
    except Exception as e:
        print(f'Error calculating team stats: {e}')
    
    cur.close()
    conn.close()
    
    print(f'')
    print(f'✅ NFL Advanced Stats ingested: {stats_added} total metrics')
    return {'success': True, 'stats_added': stats_added}

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