#!/usr/bin/env python3 """ Compute NBA Player On/Off Impact Creates NBAPlayerOnOff table with win% differential when player is on vs off court. Answers Q15-16: Player impact and lineup analysis. """ import psycopg2 from datetime import datetime, timezone from collections import defaultdict 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_player_onoff(): db_url = load_db_url() conn = psycopg2.connect(db_url) cur = conn.cursor() print("=" * 60) print("COMPUTE NBA PLAYER ON/OFF IMPACT") print(f"Time: {datetime.now(timezone.utc).isoformat()}") print("=" * 60) # Create table cur.execute(''' CREATE TABLE IF NOT EXISTS "NBAPlayerOnOff" ( id BIGSERIAL PRIMARY KEY, league VARCHAR(20) DEFAULT 'nba', season INT, "playerName" VARCHAR(100), "playerExternalId" VARCHAR(50), team VARCHAR(100), -- Games played "gamesPlayed" INT, "gamesStarted" INT, "minutesPerGame" FLOAT, -- With player stats "gamesWithPlayer" INT, "winsWithPlayer" INT, "winPctWith" FLOAT, "avgMarginWith" FLOAT, "avgPointsForWith" FLOAT, "avgPointsAgainstWith" FLOAT, -- Without player stats "gamesWithoutPlayer" INT, "winsWithoutPlayer" INT, "winPctWithout" FLOAT, "avgMarginWithout" FLOAT, "avgPointsForWithout" FLOAT, "avgPointsAgainstWithout" FLOAT, -- Impact metrics "winPctDiff" FLOAT, -- winPctWith - winPctWithout "marginDiff" FLOAT, -- avgMarginWith - avgMarginWithout "netImpact" FLOAT, -- Combined impact score "updatedAt" TIMESTAMP DEFAULT NOW(), UNIQUE(league, season, "playerExternalId", team) ) ''') cur.execute('CREATE INDEX IF NOT EXISTS "NBAPlayerOnOff_team" ON "NBAPlayerOnOff" (team)') cur.execute('CREATE INDEX IF NOT EXISTS "NBAPlayerOnOff_impact" ON "NBAPlayerOnOff" ("winPctDiff" DESC)') total_records = 0 # Get all NBA games with scores print("\nFetching NBA game data...") cur.execute(''' SELECT id, "gameDate", "homeTeam", "awayTeam", "homeScore", "awayScore" FROM "SportsGame" WHERE league = 'nba' AND season >= 2024 AND "homeScore" IS NOT NULL ORDER BY "gameDate" ''') games = cur.fetchall() print(f" Found {len(games)} NBA games") # Get player game logs - match by date and team instead of gameKey print("Fetching player game metrics...") cur.execute(''' SELECT DISTINCT "playerExternalId", "playerName", team, "gameDate"::date, MAX(CASE WHEN "statKey" = 'points' THEN value ELSE 0 END) as points FROM "PlayerGameMetric" WHERE league = 'nba' AND season >= 2024 AND "statKey" = 'points' GROUP BY "playerExternalId", "playerName", team, "gameDate"::date HAVING MAX(CASE WHEN "statKey" = 'points' THEN value ELSE 0 END) >= 0 ''') player_games = cur.fetchall() print(f" Found {len(player_games)} player-game records") # Build player game participation map using (date, team) composite key player_game_map = defaultdict(set) # player_id -> set of (date, team) tuples player_teams = defaultdict(set) # player_id -> set of teams player_names = {} # player_id -> name for pg in player_games: pid, pname, team, game_date, points = pg if points is not None and points >= 0: # Use (date_str, team) as composite key date_key = game_date.strftime('%Y-%m-%d') if hasattr(game_date, 'strftime') else str(game_date) player_game_map[pid].add((date_key, team)) player_teams[pid].add(team) player_names[pid] = pname print(f" Tracking {len(player_game_map)} unique players") # Build game results by team using (date, team) composite key team_games = defaultdict(list) # team -> [{'key': (date, team), 'won', 'margin', 'pf', 'pa'}] for g in games: game_id, gdate, home, away, hscore, ascore = g date_str = gdate.strftime('%Y-%m-%d') if hasattr(gdate, 'strftime') else str(gdate)[:10] home_won = hscore > ascore home_margin = hscore - ascore team_games[home].append({ 'key': (date_str, home), 'won': home_won, 'margin': home_margin, 'pf': hscore, 'pa': ascore }) team_games[away].append({ 'key': (date_str, away), 'won': not home_won, 'margin': -home_margin, 'pf': ascore, 'pa': hscore }) # Calculate on/off for each player print("\nCalculating player on/off impact...") for pid, game_keys in player_game_map.items(): if len(game_keys) < 10: # Need minimum games continue pname = player_names.get(pid, 'Unknown') for team in player_teams[pid]: all_team_games = team_games.get(team, []) if len(all_team_games) < 15: continue # Split games into with/without player with_player = [g for g in all_team_games if g['key'] in game_keys] without_player = [g for g in all_team_games if g['key'] not in game_keys] if len(with_player) < 5 or len(without_player) < 3: continue # Calculate stats with player wins_with = sum(1 for g in with_player if g['won']) win_pct_with = wins_with / len(with_player) * 100 margin_with = sum(g['margin'] for g in with_player) / len(with_player) pf_with = sum(g['pf'] for g in with_player) / len(with_player) pa_with = sum(g['pa'] for g in with_player) / len(with_player) # Calculate stats without player wins_without = sum(1 for g in without_player if g['won']) win_pct_without = wins_without / len(without_player) * 100 margin_without = sum(g['margin'] for g in without_player) / len(without_player) pf_without = sum(g['pf'] for g in without_player) / len(without_player) pa_without = sum(g['pa'] for g in without_player) / len(without_player) # Calculate impact win_pct_diff = win_pct_with - win_pct_without margin_diff = margin_with - margin_without net_impact = (win_pct_diff * 0.5) + (margin_diff * 2) # Weighted impact cur.execute(''' INSERT INTO "NBAPlayerOnOff" ( league, season, "playerName", "playerExternalId", team, "gamesPlayed", "gamesWithPlayer", "winsWithPlayer", "winPctWith", "avgMarginWith", "avgPointsForWith", "avgPointsAgainstWith", "gamesWithoutPlayer", "winsWithoutPlayer", "winPctWithout", "avgMarginWithout", "avgPointsForWithout", "avgPointsAgainstWithout", "winPctDiff", "marginDiff", "netImpact" ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (league, season, "playerExternalId", team) DO UPDATE SET "gamesWithPlayer" = EXCLUDED."gamesWithPlayer", "winPctWith" = EXCLUDED."winPctWith", "winPctWithout" = EXCLUDED."winPctWithout", "winPctDiff" = EXCLUDED."winPctDiff", "netImpact" = EXCLUDED."netImpact", "updatedAt" = NOW() ''', ( 'nba', 2024, pname, pid, team, len(with_player), len(with_player), wins_with, win_pct_with, margin_with, pf_with, pa_with, len(without_player), wins_without, win_pct_without, margin_without, pf_without, pa_without, win_pct_diff, margin_diff, net_impact )) total_records += 1 conn.commit() # Show top impact players print("\nTop 10 Highest Impact Players:") cur.execute(''' SELECT "playerName", team, "gamesWithPlayer", "gamesWithoutPlayer", "winPctWith", "winPctWithout", "winPctDiff", "netImpact" FROM "NBAPlayerOnOff" ORDER BY "netImpact" DESC LIMIT 10 ''') for r in cur.fetchall(): print(f" {r[0]} ({r[1]}): With={r[4]:.0f}% Without={r[5]:.0f}% Diff={r[6]:+.1f}%") cur.close() conn.close() print(f"\n✅ Total player on/off records: {total_records}") return total_records if __name__ == '__main__': compute_player_onoff()