#!/usr/bin/env python3
"""
Compute NFL Situational Stats from PBP Data
Aggregates: QB scramble rates, INT rates, team tendencies by game state
Answers: "Mahomes scrambling when protecting leads?" "Allen INT rate in comeback?"
"""
import psycopg2
from datetime import datetime

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 compute_nfl_situational():
    conn = psycopg2.connect(load_db_url())
    cur = conn.cursor()

    # Check we have PBP data
    cur.execute('SELECT COUNT(*) FROM "NFLPlayByPlay"')
    pbp_count = cur.fetchone()[0]
    if pbp_count == 0:
        print("No NFLPlayByPlay data. Run scrape_nflfastr_pbp.py first.")
        return

    print(f"Computing NFL situational stats from {pbp_count:,} plays...")

    # Truncate and recompute
    cur.execute('TRUNCATE TABLE "NFLSituationalStats"')
    cur.execute('TRUNCATE TABLE "NFLTeamTendencies"')
    conn.commit()

    # Compute QB situational stats from raw PBP
    # Note: Scrambles are recorded with rusherName, not passerName
    print("\nComputing QB situational stats...")
    cur.execute('''
        WITH pass_plays AS (
            SELECT
                "passerName" as player_name,
                team,
                season,
                COUNT(*) as plays,
                SUM(CASE WHEN "passAttempt" THEN 1 ELSE 0 END) as pass_attempts,
                SUM("yardsGained") as total_yards,
                SUM(CASE WHEN "touchdown" THEN 1 ELSE 0 END) as touchdowns,
                SUM(CASE WHEN "interception" THEN 1 ELSE 0 END) as interceptions,
                -- Leading (score diff > 7)
                SUM(CASE WHEN "scoreDifferential" > 7 THEN 1 ELSE 0 END) as plays_leading,
                SUM(CASE WHEN "scoreDifferential" > 7 AND "interception" THEN 1 ELSE 0 END) as ints_leading,
                SUM(CASE WHEN "scoreDifferential" > 7 AND "passAttempt" THEN 1 ELSE 0 END) as pass_att_leading,
                SUM(CASE WHEN "scoreDifferential" > 7 THEN "yardsGained" ELSE 0 END) as yards_leading,
                -- Trailing (score diff < -7)
                SUM(CASE WHEN "scoreDifferential" < -7 THEN 1 ELSE 0 END) as plays_trailing,
                SUM(CASE WHEN "scoreDifferential" < -7 AND "interception" THEN 1 ELSE 0 END) as ints_trailing,
                SUM(CASE WHEN "scoreDifferential" < -7 AND "passAttempt" THEN 1 ELSE 0 END) as pass_att_trailing,
                SUM(CASE WHEN "scoreDifferential" < -7 THEN "yardsGained" ELSE 0 END) as yards_trailing,
                -- Close game
                SUM(CASE WHEN ABS("scoreDifferential") <= 7 THEN 1 ELSE 0 END) as plays_close,
                SUM(CASE WHEN ABS("scoreDifferential") <= 7 AND "passAttempt" THEN 1 ELSE 0 END) as pass_att_close,
                SUM(CASE WHEN ABS("scoreDifferential") <= 7 THEN "yardsGained" ELSE 0 END) as yards_close,
                -- Q4
                SUM(CASE WHEN "quarter" = 4 THEN 1 ELSE 0 END) as plays_q4,
                SUM(CASE WHEN "quarter" = 4 AND "passAttempt" THEN 1 ELSE 0 END) as pass_att_q4,
                SUM(CASE WHEN "quarter" = 4 THEN "yardsGained" ELSE 0 END) as yards_q4,
                -- Clutch
                SUM(CASE WHEN "quarter" = 4 AND ABS("scoreDifferential") <= 7 THEN 1 ELSE 0 END) as plays_clutch,
                SUM(CASE WHEN "quarter" = 4 AND ABS("scoreDifferential") <= 7 THEN "yardsGained" ELSE 0 END) as yards_clutch
            FROM "NFLPlayByPlay"
            WHERE "passerName" IS NOT NULL
            GROUP BY "passerName", team, season
        ),
        scramble_plays AS (
            SELECT
                "rusherName" as player_name,
                team,
                season,
                COUNT(*) as scrambles,
                SUM(CASE WHEN "scoreDifferential" > 7 THEN 1 ELSE 0 END) as scrambles_leading,
                SUM(CASE WHEN "scoreDifferential" < -7 THEN 1 ELSE 0 END) as scrambles_trailing
            FROM "NFLPlayByPlay"
            WHERE "qbScramble" = true AND "rusherName" IS NOT NULL
            GROUP BY "rusherName", team, season
        ),
        qb_plays AS (
            SELECT
                p.player_name,
                p.team,
                p.season,
                p.plays + COALESCE(s.scrambles, 0) as total_plays,
                p.pass_attempts,
                p.total_yards,
                p.touchdowns,
                p.interceptions,
                COALESCE(s.scrambles, 0) as scrambles,
                p.plays_leading,
                COALESCE(s.scrambles_leading, 0) as scrambles_leading,
                p.ints_leading,
                p.pass_att_leading,
                p.yards_leading,
                p.plays_trailing,
                COALESCE(s.scrambles_trailing, 0) as scrambles_trailing,
                p.ints_trailing,
                p.pass_att_trailing,
                p.yards_trailing,
                p.plays_close,
                p.pass_att_close,
                p.yards_close,
                p.plays as plays_2h,
                p.pass_attempts as pass_att_2h,
                p.total_yards as yards_2h,
                p.plays_q4,
                p.pass_att_q4,
                p.yards_q4,
                p.plays_clutch,
                p.yards_clutch
            FROM pass_plays p
            LEFT JOIN scramble_plays s ON p.player_name = s.player_name AND p.team = s.team AND p.season = s.season
            WHERE p.plays >= 50
        )
        INSERT INTO "NFLSituationalStats"
        ("playerId", "playerName", team, "position", season,
         "totalPlays", "totalPassAttempts", "totalYards", "totalTouchdowns", "totalInterceptions",
         "playsWhenLeading", "passAttemptsLeading", "yardsLeading", "scrambleRateLeading", "intRateLeading",
         "playsWhenTrailing", "passAttemptsTrailing", "yardsTrailing", "scrambleRateTrailing", "intRateTrailing",
         "playsWhenClose", "passAttemptsClose", "yardsClose",
         "secondHalfPlays", "secondHalfPassAttempts", "secondHalfYards",
         "q4Plays", "q4PassAttempts", "q4Yards",
         "clutchPlays", "clutchYards",
         "gameScriptType", "scrambleType", "clutchType")
        SELECT
            player_name as player_id,
            player_name,
            team,
            'QB',
            season,
            total_plays, pass_attempts, total_yards, touchdowns, interceptions,
            plays_leading, pass_att_leading, yards_leading,
            ROUND(scrambles_leading::numeric / NULLIF(plays_leading, 0), 4),
            ROUND(ints_leading::numeric / NULLIF(pass_att_leading, 0), 4),
            plays_trailing, pass_att_trailing, yards_trailing,
            ROUND(scrambles_trailing::numeric / NULLIF(plays_trailing, 0), 4),
            ROUND(ints_trailing::numeric / NULLIF(pass_att_trailing, 0), 4),
            plays_close, pass_att_close, yards_close,
            plays_2h, pass_att_2h, yards_2h,
            plays_q4, pass_att_q4, yards_q4,
            plays_clutch, yards_clutch,
            -- Game script type
            CASE
                WHEN plays_leading >= 20 AND plays_trailing >= 20 AND
                     scrambles_leading::numeric / NULLIF(plays_leading, 0) > scrambles_trailing::numeric / NULLIF(plays_trailing, 0) + 0.03
                THEN 'SCRAMBLES_WHEN_LEADING'
                WHEN plays_leading >= 20 AND plays_trailing >= 20 AND
                     scrambles_trailing::numeric / NULLIF(plays_trailing, 0) > scrambles_leading::numeric / NULLIF(plays_leading, 0) + 0.03
                THEN 'SCRAMBLES_WHEN_TRAILING'
                ELSE 'NEUTRAL'
            END,
            -- Scramble type
            CASE
                WHEN scrambles::numeric / NULLIF(total_plays, 0) >= 0.08 THEN 'HIGH_SCRAMBLE'
                WHEN scrambles::numeric / NULLIF(total_plays, 0) >= 0.04 THEN 'MODERATE_SCRAMBLE'
                ELSE 'POCKET_PASSER'
            END,
            -- Clutch type
            CASE
                WHEN plays_clutch >= 20 AND yards_clutch::numeric / NULLIF(plays_clutch, 0) > total_yards::numeric / NULLIF(total_plays, 0) + 1
                THEN 'CLUTCH_PERFORMER'
                WHEN plays_clutch >= 20 AND yards_clutch::numeric / NULLIF(plays_clutch, 0) < total_yards::numeric / NULLIF(total_plays, 0) - 1
                THEN 'CLUTCH_STRUGGLES'
                ELSE 'CLUTCH_NEUTRAL'
            END
        FROM qb_plays
        ON CONFLICT ("playerId", team, season) DO UPDATE SET
            "totalPlays" = EXCLUDED."totalPlays",
            "scrambleRateLeading" = EXCLUDED."scrambleRateLeading",
            "scrambleRateTrailing" = EXCLUDED."scrambleRateTrailing",
            "intRateLeading" = EXCLUDED."intRateLeading",
            "intRateTrailing" = EXCLUDED."intRateTrailing",
            "gameScriptType" = EXCLUDED."gameScriptType",
            "scrambleType" = EXCLUDED."scrambleType",
            "clutchType" = EXCLUDED."clutchType",
            "updatedAt" = NOW()
    ''')
    qb_count = cur.rowcount
    conn.commit()
    print(f"  Computed {qb_count} QB situational records")

    # Compute team tendencies
    print("\nComputing team tendencies...")
    cur.execute('''
        WITH team_plays AS (
            SELECT
                team,
                season,
                COUNT(*) as total_plays,
                SUM(CASE WHEN "rushAttempt" THEN 1 ELSE 0 END) as rush_attempts,
                SUM(CASE WHEN "passAttempt" THEN 1 ELSE 0 END) as pass_attempts,
                -- Leading
                SUM(CASE WHEN "scoreDifferential" > 7 THEN 1 ELSE 0 END) as plays_leading,
                SUM(CASE WHEN "scoreDifferential" > 7 AND "rushAttempt" THEN 1 ELSE 0 END) as rush_leading,
                SUM(CASE WHEN "scoreDifferential" > 7 AND "passAttempt" THEN 1 ELSE 0 END) as pass_leading,
                -- Trailing
                SUM(CASE WHEN "scoreDifferential" < -7 THEN 1 ELSE 0 END) as plays_trailing,
                SUM(CASE WHEN "scoreDifferential" < -7 AND "rushAttempt" THEN 1 ELSE 0 END) as rush_trailing,
                SUM(CASE WHEN "scoreDifferential" < -7 AND "passAttempt" THEN 1 ELSE 0 END) as pass_trailing,
                -- Second half
                SUM(CASE WHEN "quarter" >= 3 THEN 1 ELSE 0 END) as plays_2h,
                SUM(CASE WHEN "quarter" >= 3 AND "rushAttempt" THEN 1 ELSE 0 END) as rush_2h,
                SUM(CASE WHEN "quarter" >= 3 AND "passAttempt" THEN 1 ELSE 0 END) as pass_2h,
                -- Close game
                SUM(CASE WHEN ABS("scoreDifferential") <= 7 THEN 1 ELSE 0 END) as plays_close,
                SUM(CASE WHEN ABS("scoreDifferential") <= 7 AND "rushAttempt" THEN 1 ELSE 0 END) as rush_close
            FROM "NFLPlayByPlay"
            WHERE team IS NOT NULL
            GROUP BY team, season
            HAVING COUNT(*) >= 100
        )
        INSERT INTO "NFLTeamTendencies"
        (season, team, "totalPlays", "rushRate", "passRate",
         "rushRateLeading", "passRateLeading",
         "rushRateTrailing", "passRateTrailing",
         "rushRateSecondHalf", "passRateSecondHalf",
         "rushRateClose",
         "runHeavyWhenLeading", "passHeavyWhenTrailing", "conservativeType")
        SELECT
            season, team, total_plays,
            ROUND(rush_attempts::numeric / NULLIF(total_plays, 0), 4),
            ROUND(pass_attempts::numeric / NULLIF(total_plays, 0), 4),
            ROUND(rush_leading::numeric / NULLIF(plays_leading, 0), 4),
            ROUND(pass_leading::numeric / NULLIF(plays_leading, 0), 4),
            ROUND(rush_trailing::numeric / NULLIF(plays_trailing, 0), 4),
            ROUND(pass_trailing::numeric / NULLIF(plays_trailing, 0), 4),
            ROUND(rush_2h::numeric / NULLIF(plays_2h, 0), 4),
            ROUND(pass_2h::numeric / NULLIF(plays_2h, 0), 4),
            ROUND(rush_close::numeric / NULLIF(plays_close, 0), 4),
            -- Run heavy when leading (rush rate increases by 10%+)
            rush_leading::numeric / NULLIF(plays_leading, 0) > rush_attempts::numeric / NULLIF(total_plays, 0) + 0.10,
            -- Pass heavy when trailing
            pass_trailing::numeric / NULLIF(plays_trailing, 0) > pass_attempts::numeric / NULLIF(total_plays, 0) + 0.10,
            -- Conservative type
            CASE
                WHEN rush_leading::numeric / NULLIF(plays_leading, 0) > rush_attempts::numeric / NULLIF(total_plays, 0) + 0.10
                     AND pass_trailing::numeric / NULLIF(plays_trailing, 0) > pass_attempts::numeric / NULLIF(total_plays, 0) + 0.10
                THEN 'GAME_SCRIPT_DEPENDENT'
                WHEN rush_leading::numeric / NULLIF(plays_leading, 0) > rush_attempts::numeric / NULLIF(total_plays, 0) + 0.10
                THEN 'CONSERVATIVE_WHEN_LEADING'
                WHEN pass_trailing::numeric / NULLIF(plays_trailing, 0) > pass_attempts::numeric / NULLIF(total_plays, 0) + 0.10
                THEN 'AGGRESSIVE_WHEN_TRAILING'
                ELSE 'NEUTRAL'
            END
        FROM team_plays
        ON CONFLICT (season, team) DO UPDATE SET
            "totalPlays" = EXCLUDED."totalPlays",
            "rushRate" = EXCLUDED."rushRate",
            "rushRateLeading" = EXCLUDED."rushRateLeading",
            "runHeavyWhenLeading" = EXCLUDED."runHeavyWhenLeading",
            "conservativeType" = EXCLUDED."conservativeType",
            "updatedAt" = NOW()
    ''')
    team_count = cur.rowcount
    conn.commit()
    print(f"  Computed {team_count} team tendency records")

    # Summary stats
    print("\n" + "=" * 60)
    print("QB SCRAMBLE RATES BY GAME STATE")
    print("=" * 60)
    cur.execute('''
        SELECT "playerName", team, season, "totalPlays",
               "scrambleRateLeading", "scrambleRateTrailing",
               "intRateLeading", "intRateTrailing",
               "gameScriptType", "scrambleType"
        FROM "NFLSituationalStats"
        WHERE "totalPlays" >= 200 AND "position" = 'QB'
        ORDER BY "scrambleRateLeading" DESC NULLS LAST
        LIMIT 15
    ''')
    results = cur.fetchall()
    for row in results:
        leading = f"{row[4]:.1%}" if row[4] else "N/A"
        trailing = f"{row[5]:.1%}" if row[5] else "N/A"
        int_lead = f"{row[6]:.1%}" if row[6] else "N/A"
        int_trail = f"{row[7]:.1%}" if row[7] else "N/A"
        print(f"{row[0]:15} ({row[1]}) {row[2]}: Scramble L={leading} T={trailing} | INT L={int_lead} T={int_trail} | {row[8]}")

    print("\n" + "=" * 60)
    print("TEAM TENDENCIES WHEN LEADING")
    print("=" * 60)
    cur.execute('''
        SELECT team, season, "rushRate", "rushRateLeading", "rushRateTrailing", "conservativeType"
        FROM "NFLTeamTendencies"
        WHERE "runHeavyWhenLeading" = true
        ORDER BY "rushRateLeading" DESC NULLS LAST
        LIMIT 15
    ''')
    results = cur.fetchall()
    for row in results:
        overall = f"{row[2]:.1%}" if row[2] else "N/A"
        leading = f"{row[3]:.1%}" if row[3] else "N/A"
        trailing = f"{row[4]:.1%}" if row[4] else "N/A"
        print(f"{row[0]:4} {row[1]}: Overall={overall} Leading={leading} Trailing={trailing} - {row[5]}")

    cur.execute('SELECT COUNT(*) FROM "NFLSituationalStats"')
    player_total = cur.fetchone()[0]
    cur.execute('SELECT COUNT(*) FROM "NFLTeamTendencies"')
    team_total = cur.fetchone()[0]

    cur.close()
    conn.close()
    print(f"\nNFL situational stats complete: {player_total} players, {team_total} teams")

if __name__ == '__main__':
    compute_nfl_situational()