#!/usr/bin/env python3
"""
NFL Pressure Metrics (nfl_data_py)
Computes pressure rate, sack rate, pressure-to-sack conversion.
"""
import nfl_data_py as nfl
import psycopg2
from datetime import datetime, timezone
import sys


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_pressure_metrics(season=2025):
    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_PRESSURE'
    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
    '''

    print(f'Fetching NFL PBP for {season}...')
    pbp = nfl.import_pbp_data([season])
    print(f'  Plays: {len(pbp)}')

    # Filter to dropbacks only
    dropbacks = pbp[(pbp['qb_dropback'] == 1) & (pbp['posteam'].notna())]
    print(f'  Dropbacks: {len(dropbacks)}')

    # Check if pressure data is available
    has_pressure = 'was_pressure' in dropbacks.columns
    print(f'  Has pressure data: {has_pressure}')

    # Games per team
    games_per_team = dropbacks.groupby('defteam')['game_id'].nunique().to_dict()

    # ========== DEFENSIVE Sack Stats (always available) ==========
    def_sacks = dropbacks.groupby('defteam').agg({
        'sack': ['sum', 'count']
    })
    def_sacks.columns = ['sacks', 'dropbacks']
    def_sacks['sack_rate'] = def_sacks['sacks'] / def_sacks['dropbacks']

    for team, row in def_sacks.iterrows():
        games = games_per_team.get(team, 1)
        cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_def_sack_rate', float(row['sack_rate'])))
        cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_def_sacks_per_game', float(row['sacks'] / games)))
        stats_added += 2

    # ========== Pressure stats if available ==========
    if has_pressure:
        def_pressure = dropbacks.groupby('defteam').agg({
            'was_pressure': ['sum', 'count'],
            'sack': 'sum'
        })
        def_pressure.columns = ['pressures', 'dropbacks', 'sacks']
        def_pressure['pressure_rate'] = def_pressure['pressures'] / def_pressure['dropbacks']
        def_pressure['pressure_to_sack'] = def_pressure.apply(
            lambda r: r['sacks'] / r['pressures'] if r['pressures'] > 0 else 0, axis=1
        )
        league_avg_p2s = def_pressure['pressure_to_sack'].mean()

        for team, row in def_pressure.iterrows():
            cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_def_pressure_rate', float(row['pressure_rate'])))
            cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_def_pressure_to_sack', float(row['pressure_to_sack'])))
            beats_exp = row['pressure_to_sack'] - league_avg_p2s
            cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_def_pressure_beats_exp', float(beats_exp)))
            stats_added += 3

        # Offensive pressure allowed
        off_pressure = dropbacks.groupby('posteam').agg({
            'was_pressure': ['sum', 'count'],
            'sack': 'sum'
        })
        off_pressure.columns = ['pressures_allowed', 'dropbacks', 'sacks_allowed']
        off_pressure['pressure_allowed_rate'] = off_pressure['pressures_allowed'] / off_pressure['dropbacks']

        for team, row in off_pressure.iterrows():
            cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_off_pressure_allowed_rate', float(row['pressure_allowed_rate'])))
            stats_added += 1

    # ========== OFFENSIVE Sack Allowed Stats ==========
    off_sacks = dropbacks.groupby('posteam').agg({
        'sack': ['sum', 'count']
    })
    off_sacks.columns = ['sacks_allowed', 'dropbacks']
    off_sacks['sack_allowed_rate'] = off_sacks['sacks_allowed'] / off_sacks['dropbacks']

    off_games = dropbacks.groupby('posteam')['game_id'].nunique().to_dict()
    for team, row in off_sacks.iterrows():
        games = off_games.get(team, 1)
        cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_off_sack_allowed_rate', float(row['sack_allowed_rate'])))
        cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_off_sacks_allowed_per_game', float(row['sacks_allowed'] / games)))
        stats_added += 2

    conn.commit()
    cur.close()
    conn.close()

    print(f'✅ NFL pressure metrics ingested: {stats_added}')
    return {'success': True, 'stats_added': stats_added}


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