#!/usr/bin/env python3
"""NFL EPA Data Ingest - uses nfl_data_py"""
import nfl_data_py as nfl
import pandas as pd
import psycopg2
import os
from datetime import datetime

def load_db_url():
    db_url = os.environ.get('SPORTS_DATABASE_URL', '')
    if db_url:
        return db_url.split('?')[0]
    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_epa(seasons=[2024]):
    print(f'🏈 Ingesting NFL EPA for {seasons}')
    weekly = nfl.import_weekly_data(seasons)
    print(f'   {len(weekly)} player-weeks fetched')
    
    conn = psycopg2.connect(load_db_url())
    cur = conn.cursor()
    added = 0
    
    for _, row in weekly.iterrows():
        if all(pd.isna(row.get(c)) for c in ['passing_epa', 'rushing_epa', 'receiving_epa']):
            continue
        
        player_id = str(row.get('player_id', ''))
        player_name = row.get('player_name', '')
        season = int(row.get('season', 2024))
        week = int(row.get('week', 1))
        team = row.get('recent_team', '')
        opponent = row.get('opponent_team', '')
        position = row.get('position', '')
        game_key = f'{season}_W{week}_{team}_{opponent}'
        game_date = datetime(season, 9, 1)  # Approx start
        
        for stat_key, col in [
            ('nfl_passing_epa', 'passing_epa'),
            ('nfl_rushing_epa', 'rushing_epa'), 
            ('nfl_receiving_epa', 'receiving_epa'),
            ('nfl_attempts', 'attempts'),
            ('nfl_completions', 'completions'),
            ('nfl_passing_yards', 'passing_yards'),
            ('nfl_passing_tds', 'passing_tds'),
            ('nfl_interceptions', 'interceptions'),
            ('nfl_dakota', 'dakota'),
        ]:
            val = row.get(col)
            if pd.notna(val):
                try:
                    cur.execute('''
                        INSERT INTO "PlayerGameMetric" 
                        (league, season, "gameKey", "gameDate", "playerExternalId", "playerName", position, team, opponent, "statKey", value)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                        ON CONFLICT (league, season, "gameKey", "playerExternalId", "statKey") 
                        DO UPDATE SET value = EXCLUDED.value
                    ''', (
                        'nfl', season, game_key, game_date, player_id, player_name, 
                        position, team, opponent, stat_key, float(val)
                    ))
                    added += 1
                except Exception as e:
                    if 'violates unique constraint' not in str(e):
                        print(f'Error: {e}')
                    conn.rollback()
        
        if added % 10000 == 0 and added > 0:
            conn.commit()
            print(f'   {added} stats...')
    
    conn.commit()
    
    # Defensive rankings
    print('   Adding defensive rankings...')
    def_ranks = weekly.groupby('opponent_team').agg({
        'passing_epa': 'sum', 'rushing_epa': 'sum'
    })
    def_ranks['total'] = def_ranks['passing_epa'] + def_ranks['rushing_epa']
    def_ranks = def_ranks.sort_values('total')
    
    for i, (team, row) in enumerate(def_ranks.iterrows(), 1):
        for stat_key, val in [
            ('nfl_pass_epa_allowed', row['passing_epa']),
            ('nfl_rush_epa_allowed', row['rushing_epa']),
            ('nfl_total_epa_allowed', row['total']),
            ('nfl_def_epa_rank', i)
        ]:
            cur.execute('''
                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
            ''', ('nfl', seasons[0], f'{seasons[0]}_SEASON', datetime(seasons[0], 9, 1), team, stat_key, float(val)))
            added += 1
    
    conn.commit()
    cur.close()
    conn.close()
    print(f'✅ Done: {added} stats')
    return added

if __name__ == '__main__':
    import sys
    seasons = [int(s) for s in sys.argv[1:]] if len(sys.argv) > 1 else [2024]
    ingest_nfl_epa(seasons)