#!/usr/bin/env python3 """ Compute Late-Game Performance Metrics Analyzes team/player performance in close games and late-game situations Fixes: Q31-34, Q37 "How does this team/player perform in crunch time?" Supports: NBA, NFL, NHL, NCAAB, NCAAF """ import psycopg2 from datetime import datetime, timedelta 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_late_game_metrics(): conn = psycopg2.connect(load_db_url()) cur = conn.cursor() # Create LateGamePerformance table for teams cur.execute(''' CREATE TABLE IF NOT EXISTS "LateGamePerformance" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, season VARCHAR(20), team VARCHAR(100) NOT NULL, -- Close game stats "closeGames" INTEGER DEFAULT 0, "closeGameWins" INTEGER DEFAULT 0, "closeGameWinPct" NUMERIC, -- Comeback ability "comebackAttempts" INTEGER DEFAULT 0, "successfulComebacks" INTEGER DEFAULT 0, "comebackPct" NUMERIC, -- Lead protection "gamesWithLead" INTEGER DEFAULT 0, "blownLeads" INTEGER DEFAULT 0, "blowLeadPct" NUMERIC, -- Clutch rating (0-100) "clutchRating" INTEGER, "clutchTier" VARCHAR(20), -- Foul discipline (basketball specific) "avgTeamFouls" NUMERIC, "foulTroubleGames" INTEGER DEFAULT 0, "foulDiscipline" VARCHAR(20), "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, team, season) ) ''') conn.commit() print("LateGamePerformance table created/verified") # Sport-specific configurations sports_config = { 'nba': {'close_margin': 5, 'foul_limit': 6, 'has_fouls': True}, 'ncaab': {'close_margin': 5, 'foul_limit': 5, 'has_fouls': True}, 'nfl': {'close_margin': 7, 'foul_limit': 0, 'has_fouls': False}, 'nhl': {'close_margin': 1, 'foul_limit': 0, 'has_fouls': False}, 'ncaaf': {'close_margin': 7, 'foul_limit': 0, 'has_fouls': False}, } for league, config in sports_config.items(): print(f"\nComputing {league.upper()} late-game metrics...") # Compute late-game performance from GameStateTracking cur.execute(''' WITH team_close_games AS ( -- Home team perspective SELECT league, "homeTeam" as team, EXTRACT(YEAR FROM "gameDate")::text as season, "closeGame", CASE WHEN "finalHomeScore" > "finalAwayScore" THEN 1 ELSE 0 END as won, "homeComeback" as comeback_success, "homeBlownLead" as blown_lead, CASE WHEN "maxHomeLead" > 0 THEN 1 ELSE 0 END as had_lead, CASE WHEN "awayBlownLead" THEN 1 ELSE 0 END as comeback_attempt FROM "GameStateTracking" WHERE league = %s UNION ALL -- Away team perspective SELECT league, "awayTeam" as team, EXTRACT(YEAR FROM "gameDate")::text as season, "closeGame", CASE WHEN "finalAwayScore" > "finalHomeScore" THEN 1 ELSE 0 END as won, "awayComeback" as comeback_success, "awayBlownLead" as blown_lead, CASE WHEN "maxAwayLead" > 0 THEN 1 ELSE 0 END as had_lead, CASE WHEN "homeBlownLead" THEN 1 ELSE 0 END as comeback_attempt FROM "GameStateTracking" WHERE league = %s ), team_stats AS ( SELECT league, team, season, SUM(CASE WHEN "closeGame" THEN 1 ELSE 0 END) as close_games, SUM(CASE WHEN "closeGame" AND won = 1 THEN 1 ELSE 0 END) as close_wins, SUM(CASE WHEN comeback_attempt = 1 THEN 1 ELSE 0 END) as comeback_attempts, SUM(CASE WHEN comeback_success THEN 1 ELSE 0 END) as successful_comebacks, SUM(CASE WHEN had_lead = 1 THEN 1 ELSE 0 END) as games_with_lead, SUM(CASE WHEN blown_lead THEN 1 ELSE 0 END) as blown_leads FROM team_close_games GROUP BY league, team, season HAVING SUM(CASE WHEN "closeGame" THEN 1 ELSE 0 END) >= 3 ) INSERT INTO "LateGamePerformance" (league, season, team, "closeGames", "closeGameWins", "closeGameWinPct", "comebackAttempts", "successfulComebacks", "comebackPct", "gamesWithLead", "blownLeads", "blowLeadPct", "clutchRating", "clutchTier") SELECT league, season, team, close_games, close_wins, ROUND((close_wins::numeric / NULLIF(close_games, 0))::numeric, 3), comeback_attempts, successful_comebacks, ROUND((successful_comebacks::numeric / NULLIF(comeback_attempts, 0))::numeric, 3), games_with_lead, blown_leads, ROUND((blown_leads::numeric / NULLIF(games_with_lead, 0))::numeric, 3), -- Clutch rating formula (weighted) LEAST(100, GREATEST(0, (COALESCE(close_wins::numeric / NULLIF(close_games, 0), 0.5) * 40) + (COALESCE(successful_comebacks::numeric / NULLIF(comeback_attempts, 0), 0.5) * 30) + ((1 - COALESCE(blown_leads::numeric / NULLIF(games_with_lead, 0), 0.5)) * 30) ))::int, CASE WHEN (close_wins::numeric / NULLIF(close_games, 0)) >= 0.65 AND (blown_leads::numeric / NULLIF(games_with_lead, 0)) <= 0.20 THEN 'ELITE_CLUTCH' WHEN (close_wins::numeric / NULLIF(close_games, 0)) >= 0.55 THEN 'CLUTCH' WHEN (close_wins::numeric / NULLIF(close_games, 0)) >= 0.45 THEN 'AVERAGE' WHEN (close_wins::numeric / NULLIF(close_games, 0)) >= 0.35 THEN 'BELOW_AVERAGE' ELSE 'POOR_CLUTCH' END FROM team_stats ON CONFLICT (league, team, season) DO UPDATE SET "closeGames" = EXCLUDED."closeGames", "closeGameWins" = EXCLUDED."closeGameWins", "closeGameWinPct" = EXCLUDED."closeGameWinPct", "comebackAttempts" = EXCLUDED."comebackAttempts", "successfulComebacks" = EXCLUDED."successfulComebacks", "comebackPct" = EXCLUDED."comebackPct", "gamesWithLead" = EXCLUDED."gamesWithLead", "blownLeads" = EXCLUDED."blownLeads", "blowLeadPct" = EXCLUDED."blowLeadPct", "clutchRating" = EXCLUDED."clutchRating", "clutchTier" = EXCLUDED."clutchTier", "updatedAt" = NOW() ''', (league, league)) count = cur.rowcount conn.commit() print(f" {league.upper()}: {count} team late-game records") # Add foul discipline for basketball sports if config['has_fouls']: print(f" Computing foul discipline for {league.upper()}...") cur.execute(''' WITH team_fouls AS ( SELECT pgm.league, pgm.team, EXTRACT(YEAR FROM pgm."gameDate")::text as season, pgm."gameDate", SUM(pgm.value) as game_fouls, COUNT(DISTINCT pgm."playerExternalId") as players FROM "PlayerGameMetric" pgm WHERE pgm.league = %s AND pgm."statKey" = 'espn_pf' GROUP BY pgm.league, pgm.team, pgm."gameDate" ), foul_stats AS ( SELECT league, team, season, ROUND(AVG(game_fouls)::numeric, 1) as avg_fouls, COUNT(CASE WHEN game_fouls >= %s * 3 THEN 1 END) as high_foul_games FROM team_fouls GROUP BY league, team, season ) UPDATE "LateGamePerformance" lgp SET "avgTeamFouls" = fs.avg_fouls, "foulTroubleGames" = fs.high_foul_games, "foulDiscipline" = CASE WHEN fs.avg_fouls <= 15 THEN 'DISCIPLINED' WHEN fs.avg_fouls <= 20 THEN 'AVERAGE' ELSE 'FOUL_PRONE' END FROM foul_stats fs WHERE lgp.league = fs.league AND lgp.team = fs.team AND lgp.season = fs.season ''', (league, config['foul_limit'])) conn.commit() # Summary cur.execute(''' SELECT league, "clutchTier", COUNT(*), ROUND(AVG("clutchRating")::numeric, 0) FROM "LateGamePerformance" GROUP BY league, "clutchTier" ORDER BY league, "clutchTier" ''') print("\n\nClutch Performance Distribution:") current_league = None for row in cur.fetchall(): if row[0] != current_league: current_league = row[0] print(f"\n {current_league.upper()}:") print(f" {row[1]}: {row[2]} teams (avg rating: {row[3]})") cur.execute('SELECT COUNT(*) FROM "LateGamePerformance"') total = cur.fetchone()[0] cur.close() conn.close() print(f"\n✅ Late-game performance complete: {total} team records") if __name__ == '__main__': compute_late_game_metrics()