#!/usr/bin/env python3 """ Compute Game Script Impact Player/team stats by lead/deficit size buckets Answers: "Lamar less aggressive when leading?" "Bengals more pass-heavy when trailing?" Supports: NBA, NFL, NHL """ 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_game_script_impact(): conn = psycopg2.connect(load_db_url()) cur = conn.cursor() # Create GameScriptImpact table cur.execute(''' CREATE TABLE IF NOT EXISTS "GameScriptImpact" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, season VARCHAR(20), team VARCHAR(100) NOT NULL, -- Games by script type "gamesWhenLeading" INTEGER DEFAULT 0, "gamesWhenTrailing" INTEGER DEFAULT 0, "gamesWhenClose" INTEGER DEFAULT 0, "gamesAsBlowoutWinner" INTEGER DEFAULT 0, "gamesAsBlowoutLoser" INTEGER DEFAULT 0, -- Performance by script "atsWhenLeading" NUMERIC, "atsWhenTrailing" NUMERIC, "atsWhenClose" NUMERIC, -- Comeback/blown lead metrics "comebackWinRate" NUMERIC, "blownLeadRate" NUMERIC, -- Script tendencies "avgLeadWhenWinning" NUMERIC, "avgDeficitWhenLosing" NUMERIC, "maxComebackWin" INTEGER, "maxBlownLead" INTEGER, -- Behavior classification "scriptType" VARCHAR(30), "closeGameTeam" BOOLEAN DEFAULT false, "blowoutTeam" BOOLEAN DEFAULT false, "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, team, season) ) ''') conn.commit() print("GameScriptImpact table created/verified") # Compute from GameStateTracking (using win rates instead of ATS - spread data not available in SportsGame) print("\nComputing game script impact...") cur.execute(''' WITH team_game_scripts AS ( -- Home team perspective SELECT gst.league, gst."homeTeam" as team, EXTRACT(YEAR FROM gst."gameDate")::text as season, gst."gameDate", gst."closeGame", gst.blowout, gst."maxHomeLead" as max_lead, gst."maxAwayLead" as max_deficit, gst."homeBlownLead" as blown_lead, gst."homeComeback" as comeback, CASE WHEN gst."finalHomeScore" > gst."finalAwayScore" THEN 1 ELSE 0 END as won, -- Determine game script from quarter leads CASE WHEN gst."homeLeadQ3" > 5 THEN 'LEADING' WHEN gst."homeLeadQ3" < -5 THEN 'TRAILING' ELSE 'CLOSE' END as q3_script FROM "GameStateTracking" gst WHERE gst."finalHomeScore" IS NOT NULL UNION ALL -- Away team perspective SELECT gst.league, gst."awayTeam" as team, EXTRACT(YEAR FROM gst."gameDate")::text as season, gst."gameDate", gst."closeGame", gst.blowout, gst."maxAwayLead" as max_lead, gst."maxHomeLead" as max_deficit, gst."awayBlownLead" as blown_lead, gst."awayComeback" as comeback, CASE WHEN gst."finalAwayScore" > gst."finalHomeScore" THEN 1 ELSE 0 END as won, CASE WHEN -gst."homeLeadQ3" > 5 THEN 'LEADING' WHEN -gst."homeLeadQ3" < -5 THEN 'TRAILING' ELSE 'CLOSE' END as q3_script FROM "GameStateTracking" gst WHERE gst."finalHomeScore" IS NOT NULL ), aggregated AS ( SELECT league, team, season, -- Game counts by script COUNT(CASE WHEN q3_script = 'LEADING' THEN 1 END) as games_leading, COUNT(CASE WHEN q3_script = 'TRAILING' THEN 1 END) as games_trailing, COUNT(CASE WHEN q3_script = 'CLOSE' THEN 1 END) as games_close, COUNT(CASE WHEN blowout AND won = 1 THEN 1 END) as blowout_wins, COUNT(CASE WHEN blowout AND won = 0 THEN 1 END) as blowout_losses, -- Win rate by script (instead of ATS since spread not available) AVG(CASE WHEN q3_script = 'LEADING' THEN won END) as win_rate_leading, AVG(CASE WHEN q3_script = 'TRAILING' THEN won END) as win_rate_trailing, AVG(CASE WHEN q3_script = 'CLOSE' THEN won END) as win_rate_close, -- Comeback/blown lead AVG(CASE WHEN max_deficit > 0 THEN CASE WHEN comeback THEN 1 ELSE 0 END END) as comeback_rate, AVG(CASE WHEN max_lead > 5 THEN CASE WHEN blown_lead THEN 1 ELSE 0 END END) as blown_lead_rate, -- Margins AVG(CASE WHEN won = 1 THEN max_lead END) as avg_lead_wins, AVG(CASE WHEN won = 0 THEN max_deficit END) as avg_deficit_losses, MAX(CASE WHEN comeback AND won = 1 THEN max_deficit END) as max_comeback, MAX(CASE WHEN blown_lead AND won = 0 THEN max_lead END) as max_blown FROM team_game_scripts GROUP BY league, team, season HAVING COUNT(*) >= 5 ) INSERT INTO "GameScriptImpact" (league, season, team, "gamesWhenLeading", "gamesWhenTrailing", "gamesWhenClose", "gamesAsBlowoutWinner", "gamesAsBlowoutLoser", "atsWhenLeading", "atsWhenTrailing", "atsWhenClose", "comebackWinRate", "blownLeadRate", "avgLeadWhenWinning", "avgDeficitWhenLosing", "maxComebackWin", "maxBlownLead", "scriptType", "closeGameTeam", "blowoutTeam") SELECT league, season, team, games_leading, games_trailing, games_close, blowout_wins, blowout_losses, ROUND(win_rate_leading::numeric, 3), ROUND(win_rate_trailing::numeric, 3), ROUND(win_rate_close::numeric, 3), ROUND(comeback_rate::numeric, 3), ROUND(blown_lead_rate::numeric, 3), ROUND(avg_lead_wins::numeric, 1), ROUND(avg_deficit_losses::numeric, 1), max_comeback, max_blown, -- Script type classification CASE WHEN comeback_rate > 0.3 THEN 'COMEBACK_TEAM' WHEN blown_lead_rate > 0.3 THEN 'CHOKE_TEAM' WHEN games_close > games_leading + games_trailing THEN 'GRINDER' WHEN blowout_wins > blowout_losses * 2 THEN 'DOMINANT' ELSE 'BALANCED' END, games_close > (games_leading + games_trailing) * 0.5, (blowout_wins + blowout_losses) > games_close FROM aggregated ON CONFLICT (league, team, season) DO UPDATE SET "gamesWhenLeading" = EXCLUDED."gamesWhenLeading", "gamesWhenTrailing" = EXCLUDED."gamesWhenTrailing", "gamesWhenClose" = EXCLUDED."gamesWhenClose", "gamesAsBlowoutWinner" = EXCLUDED."gamesAsBlowoutWinner", "gamesAsBlowoutLoser" = EXCLUDED."gamesAsBlowoutLoser", "atsWhenLeading" = EXCLUDED."atsWhenLeading", "atsWhenTrailing" = EXCLUDED."atsWhenTrailing", "atsWhenClose" = EXCLUDED."atsWhenClose", "comebackWinRate" = EXCLUDED."comebackWinRate", "blownLeadRate" = EXCLUDED."blownLeadRate", "avgLeadWhenWinning" = EXCLUDED."avgLeadWhenWinning", "avgDeficitWhenLosing" = EXCLUDED."avgDeficitWhenLosing", "maxComebackWin" = EXCLUDED."maxComebackWin", "maxBlownLead" = EXCLUDED."maxBlownLead", "scriptType" = EXCLUDED."scriptType", "closeGameTeam" = EXCLUDED."closeGameTeam", "blowoutTeam" = EXCLUDED."blowoutTeam", "updatedAt" = NOW() ''') count = cur.rowcount conn.commit() print(f" Records created: {count}") # Summary cur.execute(''' SELECT league, "scriptType", COUNT(*), ROUND(AVG("comebackWinRate")::numeric, 3), ROUND(AVG("blownLeadRate")::numeric, 3) FROM "GameScriptImpact" GROUP BY league, "scriptType" ORDER BY league, COUNT(*) DESC ''') print("\nScript Type Distribution:") current_league = None for row in cur.fetchall(): if row[0] != current_league: current_league = row[0] print(f"\n {current_league.upper()}:") comeback = row[3] if row[3] else 0 blown = row[4] if row[4] else 0 print(f" {row[1]}: {row[2]} teams (comeback: {comeback:.1%}, blown: {blown:.1%})") cur.execute('SELECT COUNT(*) FROM "GameScriptImpact"') total = cur.fetchone()[0] cur.close() conn.close() print(f"\n✅ Game script impact complete: {total} records") if __name__ == '__main__': compute_game_script_impact()