#!/usr/bin/env python3 """ Calculate Player Impact Metrics from Existing Data Uses PlayerGameMetric + SportsGame to calculate: - Player impact on team wins/losses - Team scoring with/without player - Approximate on/off analysis from games played vs missed This is a workaround for NBA.com API blocking. Run: Weekly """ import psycopg2 import os from datetime import datetime, timezone import argparse def load_db_url(): env_path = '/var/www/html/eventheodds/.env' try: with open(env_path, 'r') as f: for line in f: if line.startswith('SPORTS_DATABASE_URL='): return line.split('=', 1)[1].strip().split('?')[0] except FileNotFoundError: pass return os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0] def ensure_impact_table(cur): """Create PlayerImpact table""" cur.execute(''' CREATE TABLE IF NOT EXISTS "PlayerImpact" ( id BIGSERIAL PRIMARY KEY, league VARCHAR(50) NOT NULL, season VARCHAR(20), "playerId" VARCHAR(100), "playerName" VARCHAR(100), team VARCHAR(50), "gamesPlayed" INT, "gamesMissed" INT, "teamWinsWithPlayer" INT, "teamLossesWithPlayer" INT, "teamWinsWithoutPlayer" INT, "teamLossesWithoutPlayer" INT, "winPctWithPlayer" FLOAT, "winPctWithoutPlayer" FLOAT, "winPctDiff" FLOAT, "avgPointsWithPlayer" FLOAT, "avgPointsWithoutPlayer" FLOAT, "avgAllowedWithPlayer" FLOAT, "avgAllowedWithoutPlayer" FLOAT, "netImpact" FLOAT, "updatedAt" TIMESTAMP DEFAULT NOW(), UNIQUE(league, season, "playerId") ) ''') cur.execute('CREATE INDEX IF NOT EXISTS "PlayerImpact_team_idx" ON "PlayerImpact" (team, season)') cur.execute('CREATE INDEX IF NOT EXISTS "PlayerImpact_impact_idx" ON "PlayerImpact" ("netImpact" DESC)') def calculate_player_impact(league='nba', min_games=10): """Calculate player impact for a league""" db_url = load_db_url() if not db_url: raise RuntimeError('SPORTS_DATABASE_URL not set') conn = psycopg2.connect(db_url) cur = conn.cursor() ensure_impact_table(cur) conn.commit() print("=" * 60) print(f"CALCULATE PLAYER IMPACT - {league.upper()}") print(f"Time: {datetime.now(timezone.utc).isoformat()}") print("=" * 60) # Get all players with games this season (use espn_min or points as proxy) cur.execute(''' SELECT DISTINCT "playerExternalId", "playerName", team FROM "PlayerGameMetric" WHERE league = %s AND "gameDate" >= '2025-10-01' AND "statKey" IN ('espn_min', 'points') AND value > 0 ''', (league,)) players = cur.fetchall() print(f"Found {len(players)} players with minutes this season") processed = 0 significant = 0 for player_id, player_name, team in players: # Get games this player played in cur.execute(''' SELECT DISTINCT "gameKey", "gameDate" FROM "PlayerGameMetric" WHERE league = %s AND "playerExternalId" = %s AND "statKey" IN ('espn_min', 'points') AND value > 0 ''', (league, player_id)) played_dates = {r[1].strftime('%Y-%m-%d') for r in cur.fetchall()} if len(played_dates) < min_games: continue # Get team's full schedule cur.execute(''' SELECT id, "homeTeam", "awayTeam", "homeScore", "awayScore", "gameDate"::date FROM "SportsGame" WHERE league = %s AND "gameDate" >= '2025-10-01' AND "homeScore" IS NOT NULL AND (%s = "homeTeam" OR %s = "awayTeam") ''', (league, team, team)) team_games = cur.fetchall() if not team_games: continue # Calculate stats with and without player with_player = {'wins': 0, 'losses': 0, 'points_for': [], 'points_against': []} without_player = {'wins': 0, 'losses': 0, 'points_for': [], 'points_against': []} for game_id, home, away, home_score, away_score, game_date in team_games: is_home = (team == home) team_score = home_score if is_home else away_score opp_score = away_score if is_home else home_score won = team_score > opp_score # Check if player played on this date player_played = game_date.strftime('%Y-%m-%d') in played_dates if player_played: if won: with_player['wins'] += 1 else: with_player['losses'] += 1 with_player['points_for'].append(team_score) with_player['points_against'].append(opp_score) else: if won: without_player['wins'] += 1 else: without_player['losses'] += 1 without_player['points_for'].append(team_score) without_player['points_against'].append(opp_score) # Only include if player missed at least 3 games (meaningful comparison) games_played = with_player['wins'] + with_player['losses'] games_missed = without_player['wins'] + without_player['losses'] if games_played < min_games or games_missed < 3: continue # Calculate metrics win_pct_with = with_player['wins'] / games_played if games_played > 0 else 0 win_pct_without = without_player['wins'] / games_missed if games_missed > 0 else 0 win_pct_diff = win_pct_with - win_pct_without avg_pts_with = sum(with_player['points_for']) / len(with_player['points_for']) if with_player['points_for'] else 0 avg_pts_without = sum(without_player['points_for']) / len(without_player['points_for']) if without_player['points_for'] else 0 avg_allowed_with = sum(with_player['points_against']) / len(with_player['points_against']) if with_player['points_against'] else 0 avg_allowed_without = sum(without_player['points_against']) / len(without_player['points_against']) if without_player['points_against'] else 0 # Net impact = (points diff with) - (points diff without) net_with = avg_pts_with - avg_allowed_with net_without = avg_pts_without - avg_allowed_without net_impact = net_with - net_without try: cur.execute(''' INSERT INTO "PlayerImpact" ( league, season, "playerId", "playerName", team, "gamesPlayed", "gamesMissed", "teamWinsWithPlayer", "teamLossesWithPlayer", "teamWinsWithoutPlayer", "teamLossesWithoutPlayer", "winPctWithPlayer", "winPctWithoutPlayer", "winPctDiff", "avgPointsWithPlayer", "avgPointsWithoutPlayer", "avgAllowedWithPlayer", "avgAllowedWithoutPlayer", "netImpact" ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (league, season, "playerId") DO UPDATE SET "gamesPlayed" = EXCLUDED."gamesPlayed", "gamesMissed" = EXCLUDED."gamesMissed", "teamWinsWithPlayer" = EXCLUDED."teamWinsWithPlayer", "teamLossesWithPlayer" = EXCLUDED."teamLossesWithPlayer", "teamWinsWithoutPlayer" = EXCLUDED."teamWinsWithoutPlayer", "teamLossesWithoutPlayer" = EXCLUDED."teamLossesWithoutPlayer", "winPctWithPlayer" = EXCLUDED."winPctWithPlayer", "winPctWithoutPlayer" = EXCLUDED."winPctWithoutPlayer", "winPctDiff" = EXCLUDED."winPctDiff", "avgPointsWithPlayer" = EXCLUDED."avgPointsWithPlayer", "avgPointsWithoutPlayer" = EXCLUDED."avgPointsWithoutPlayer", "avgAllowedWithPlayer" = EXCLUDED."avgAllowedWithPlayer", "avgAllowedWithoutPlayer" = EXCLUDED."avgAllowedWithoutPlayer", "netImpact" = EXCLUDED."netImpact", "updatedAt" = NOW() ''', ( league, '2025-26', player_id, player_name, team, games_played, games_missed, with_player['wins'], with_player['losses'], without_player['wins'], without_player['losses'], round(win_pct_with, 3), round(win_pct_without, 3), round(win_pct_diff, 3), round(avg_pts_with, 1), round(avg_pts_without, 1), round(avg_allowed_with, 1), round(avg_allowed_without, 1), round(net_impact, 1), )) processed += 1 if abs(win_pct_diff) >= 0.15: significant += 1 except Exception as e: print(f" Error inserting {player_name}: {e}") conn.commit() # Show top impact players cur.execute(''' SELECT "playerName", team, "gamesPlayed", "gamesMissed", "winPctWithPlayer", "winPctWithoutPlayer", "winPctDiff", "netImpact" FROM "PlayerImpact" WHERE league = %s AND season = '2025-26' ORDER BY "winPctDiff" DESC LIMIT 15 ''', (league,)) print(f"\nTop Impact Players (by win% diff):") print("-" * 80) for row in cur.fetchall(): print(f" {row[0]:<20} {row[1]:<5} GP:{row[2]:>2} Miss:{row[3]:>2} " f"W%: {row[4]:.3f} vs {row[5]:.3f} (diff: {row[6]:+.3f}) Net: {row[7]:+.1f}") cur.close() conn.close() print(f"\n{'='*60}") print(f"TOTAL: {processed} players analyzed, {significant} with significant impact (>=15%)") print("=" * 60) return {'processed': processed, 'significant': significant} def main(): parser = argparse.ArgumentParser(description='Calculate player impact metrics') parser.add_argument('--leagues', type=str, default='nba,nfl,nhl', help='Comma-separated leagues') parser.add_argument('--min-games', type=int, default=10, help='Minimum games played to include') args = parser.parse_args() leagues = [l.strip().lower() for l in args.leagues.split(',')] total_processed = 0 total_significant = 0 for league in leagues: result = calculate_player_impact(league, args.min_games) total_processed += result['processed'] total_significant += result['significant'] print(f"\nGrand Total: {total_processed} players, {total_significant} significant") if __name__ == '__main__': main()