#!/usr/bin/env python3 """ Compute Player Fatigue Impact Player stats by rest days and schedule density Answers: "Ja Morant scoring down with short rest?" "Clippers profitable in 3-in-4 with Kawhi?" 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_player_fatigue(): conn = psycopg2.connect(load_db_url()) cur = conn.cursor() # Create PlayerFatigueImpact table cur.execute(''' CREATE TABLE IF NOT EXISTS "PlayerFatigueImpact" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, season VARCHAR(20), "playerExternalId" TEXT, "playerName" VARCHAR(200), team VARCHAR(100) NOT NULL, -- Rest day splits "gamesOnNoRest" INTEGER DEFAULT 0, "avgPointsNoRest" NUMERIC, "avgMinutesNoRest" NUMERIC, "gamesOnOneDay" INTEGER DEFAULT 0, "avgPointsOneDay" NUMERIC, "gamesOnTwoPlusDays" INTEGER DEFAULT 0, "avgPointsTwoPlusDays" NUMERIC, -- Schedule density "gamesInThreeInFour" INTEGER DEFAULT 0, "avgPointsThreeInFour" NUMERIC, "teamATSThreeInFour" NUMERIC, "gamesInFourInFive" INTEGER DEFAULT 0, "avgPointsFourInFive" NUMERIC, -- Fatigue impact "restImpactScore" NUMERIC, "fatigueType" VARCHAR(30), "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, "playerExternalId", team, season) ) ''') conn.commit() print("PlayerFatigueImpact table created/verified") # NBA fatigue analysis (using win rates instead of ATS - spread not available in SportsGame) print("\nComputing NBA player fatigue impact...") cur.execute(''' WITH player_with_schedule AS ( SELECT pgm.league, pgm."playerExternalId", pgm.team, pgm."gameDate", MAX(CASE WHEN pgm."statKey" IN ('espn_pts', 'nba_pts', 'points') THEN pgm.value END) as points, MAX(CASE WHEN pgm."statKey" IN ('espn_min', 'nba_min', 'minutes') THEN pgm.value END) as minutes, gsm."restDays", gsm."isBackToBack", gsm."isThreeInFour", gsm."isFourInFive", -- Get team win result (instead of ATS) CASE WHEN pgm.team = sg."homeTeam" THEN CASE WHEN sg."homeScore" > sg."awayScore" THEN 1 ELSE 0 END ELSE CASE WHEN sg."awayScore" > sg."homeScore" THEN 1 ELSE 0 END END as won FROM "PlayerGameMetric" pgm JOIN "GameScheduleMeta" gsm ON gsm.league = pgm.league AND gsm."gameDate" = pgm."gameDate" AND gsm.team = pgm.team JOIN "SportsGame" sg ON sg.league = pgm.league AND sg."gameDate" = pgm."gameDate" AND (sg."homeTeam" = pgm.team OR sg."awayTeam" = pgm.team) WHERE pgm.league = 'nba' AND pgm."statKey" IN ('espn_pts', 'nba_pts', 'points', 'espn_min', 'nba_min', 'minutes') AND sg."homeScore" IS NOT NULL GROUP BY pgm.league, pgm."playerExternalId", pgm.team, pgm."gameDate", gsm."restDays", gsm."isBackToBack", gsm."isThreeInFour", gsm."isFourInFive", sg."homeTeam", sg."awayTeam", sg."homeScore", sg."awayScore" HAVING MAX(CASE WHEN pgm."statKey" IN ('espn_min', 'nba_min', 'minutes') THEN pgm.value END) > 10 ), aggregated AS ( SELECT league, "playerExternalId", team, EXTRACT(YEAR FROM "gameDate")::text as season, -- No rest (B2B) COUNT(CASE WHEN "restDays" = 0 OR "isBackToBack" THEN 1 END) as games_no_rest, AVG(CASE WHEN "restDays" = 0 OR "isBackToBack" THEN points END) as pts_no_rest, AVG(CASE WHEN "restDays" = 0 OR "isBackToBack" THEN minutes END) as mins_no_rest, -- One day rest COUNT(CASE WHEN "restDays" = 1 THEN 1 END) as games_one_day, AVG(CASE WHEN "restDays" = 1 THEN points END) as pts_one_day, -- Two+ days rest COUNT(CASE WHEN "restDays" >= 2 THEN 1 END) as games_two_plus, AVG(CASE WHEN "restDays" >= 2 THEN points END) as pts_two_plus, -- 3-in-4 COUNT(CASE WHEN "isThreeInFour" THEN 1 END) as games_3in4, AVG(CASE WHEN "isThreeInFour" THEN points END) as pts_3in4, AVG(CASE WHEN "isThreeInFour" THEN won END) as win_rate_3in4, -- 4-in-5 COUNT(CASE WHEN "isFourInFive" THEN 1 END) as games_4in5, AVG(CASE WHEN "isFourInFive" THEN points END) as pts_4in5, -- Overall average for comparison AVG(points) as overall_avg_pts FROM player_with_schedule WHERE points IS NOT NULL GROUP BY league, "playerExternalId", team, EXTRACT(YEAR FROM "gameDate") HAVING COUNT(*) >= 5 ) INSERT INTO "PlayerFatigueImpact" (league, season, "playerExternalId", team, "gamesOnNoRest", "avgPointsNoRest", "avgMinutesNoRest", "gamesOnOneDay", "avgPointsOneDay", "gamesOnTwoPlusDays", "avgPointsTwoPlusDays", "gamesInThreeInFour", "avgPointsThreeInFour", "teamATSThreeInFour", "gamesInFourInFive", "avgPointsFourInFive", "restImpactScore", "fatigueType") SELECT league, season, "playerExternalId", team, games_no_rest, ROUND(pts_no_rest::numeric, 1), ROUND(mins_no_rest::numeric, 1), games_one_day, ROUND(pts_one_day::numeric, 1), games_two_plus, ROUND(pts_two_plus::numeric, 1), games_3in4, ROUND(pts_3in4::numeric, 1), ROUND(win_rate_3in4::numeric, 3), games_4in5, ROUND(pts_4in5::numeric, 1), -- Rest impact = % drop from rested to tired ROUND(CASE WHEN pts_two_plus > 0 AND pts_no_rest IS NOT NULL THEN ((pts_no_rest - pts_two_plus) / pts_two_plus * 100) ELSE NULL END::numeric, 1), -- Fatigue classification CASE WHEN pts_no_rest IS NOT NULL AND pts_two_plus IS NOT NULL AND pts_no_rest < pts_two_plus * 0.85 THEN 'HIGH_FATIGUE_IMPACT' WHEN pts_no_rest IS NOT NULL AND pts_two_plus IS NOT NULL AND pts_no_rest > pts_two_plus * 1.05 THEN 'THRIVES_ON_ACTION' ELSE 'NEUTRAL' END FROM aggregated ON CONFLICT (league, "playerExternalId", team, season) DO UPDATE SET "gamesOnNoRest" = EXCLUDED."gamesOnNoRest", "avgPointsNoRest" = EXCLUDED."avgPointsNoRest", "avgMinutesNoRest" = EXCLUDED."avgMinutesNoRest", "gamesOnOneDay" = EXCLUDED."gamesOnOneDay", "avgPointsOneDay" = EXCLUDED."avgPointsOneDay", "gamesOnTwoPlusDays" = EXCLUDED."gamesOnTwoPlusDays", "avgPointsTwoPlusDays" = EXCLUDED."avgPointsTwoPlusDays", "gamesInThreeInFour" = EXCLUDED."gamesInThreeInFour", "avgPointsThreeInFour" = EXCLUDED."avgPointsThreeInFour", "teamATSThreeInFour" = EXCLUDED."teamATSThreeInFour", "gamesInFourInFive" = EXCLUDED."gamesInFourInFive", "avgPointsFourInFive" = EXCLUDED."avgPointsFourInFive", "restImpactScore" = EXCLUDED."restImpactScore", "fatigueType" = EXCLUDED."fatigueType", "updatedAt" = NOW() ''') nba_count = cur.rowcount conn.commit() print(f" NBA: {nba_count} player fatigue records") # Add player names print("\nAdding player names...") cur.execute(''' UPDATE "PlayerFatigueImpact" pfi SET "playerName" = p.name FROM "Player" p WHERE pfi."playerExternalId" = p."externalPlayerId" AND pfi."playerName" IS NULL ''') conn.commit() # Summary cur.execute(''' SELECT "fatigueType", COUNT(*), ROUND(AVG("restImpactScore")::numeric, 1), ROUND(AVG("gamesOnNoRest")::numeric, 0) FROM "PlayerFatigueImpact" WHERE "fatigueType" IS NOT NULL GROUP BY "fatigueType" ORDER BY COUNT(*) DESC ''') print("\nFatigue Type Distribution:") for row in cur.fetchall(): print(f" {row[0]}: {row[1]} players (avg impact: {row[2]:+.1f}%, avg B2B games: {row[3]})") cur.execute('SELECT COUNT(*) FROM "PlayerFatigueImpact"') total = cur.fetchone()[0] cur.close() conn.close() print(f"\n✅ Player fatigue impact complete: {total} records") if __name__ == '__main__': compute_player_fatigue()