#!/usr/bin/env python3
"""
NFL Offensive Line Metrics (nfl_data_py)
Computes pass protection metrics for OL props correlation.
"""
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_oline_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_OLINE'
    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
    dropbacks = pbp[(pbp['qb_dropback'] == 1) & (pbp['posteam'].notna())]
    
    # Check for column availability
    has_pressure = 'was_pressure' in dropbacks.columns
    has_qb_hit = 'qb_hit' in dropbacks.columns
    print(f'  Has pressure: {has_pressure}, Has QB hit: {has_qb_hit}')

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

    # ========== Basic OL Stats (sacks allowed - always available) ==========
    oline_basic = dropbacks.groupby('posteam').agg({
        'sack': ['sum', 'count']
    })
    oline_basic.columns = ['sacks_allowed', 'dropbacks']
    oline_basic['sack_rate'] = oline_basic['sacks_allowed'] / oline_basic['dropbacks']

    for team, row in oline_basic.iterrows():
        games = games_per_team.get(team, 1)
        sacks_per_game = row['sacks_allowed'] / games
        cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_sacks_allowed_per_game', float(sacks_per_game)))
        cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_sack_rate_allowed', float(row['sack_rate'])))
        stats_added += 2

    # ========== Pressure-based stats if available ==========
    if has_pressure:
        oline_pressure = dropbacks.groupby('posteam').agg({
            'was_pressure': 'sum',
            'play_id': 'count'
        }).rename(columns={'play_id': 'dropbacks'})
        
        for team, row in oline_pressure.iterrows():
            games = games_per_team.get(team, 1)
            pressure_rate = row['was_pressure'] / row['dropbacks'] if row['dropbacks'] > 0 else 0
            pass_block_win_rate = 1 - pressure_rate
            
            cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_pass_block_win_rate', float(pass_block_win_rate)))
            cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_pressures_allowed_per_game', float(row['was_pressure'] / games)))
            stats_added += 2

    # ========== QB Hits if available ==========
    if has_qb_hit:
        qb_hits = dropbacks.groupby('posteam')['qb_hit'].sum()
        for team, val in qb_hits.items():
            games = games_per_team.get(team, 1)
            cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_qb_hits_allowed_per_game', float(val / games)))
            stats_added += 1

    # ========== Run Blocking: Yards per rush ==========
    run_plays = pbp[(pbp['play_type'] == 'run') & (pbp['posteam'].notna())]
    
    run_efficiency = run_plays.groupby('posteam').agg({
        'yards_gained': 'mean',
        'first_down_rush': 'mean'
    })
    
    for team, row in run_efficiency.iterrows():
        cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_yards_per_rush', float(row['yards_gained'])))
        cur.execute(sql_team, ('nfl', season, game_key, game_date, team, 'nfl_rush_first_down_rate', float(row['first_down_rush'])))
        stats_added += 2

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

    print(f'✅ NFL offensive line 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_oline_metrics(season)