#!/usr/bin/env python3 """ Compute Post-Loss (Bounce-Back) Performance Fixes: Q6 "Is this a good bounce-back spot?" Works across all major sports """ import psycopg2 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_post_loss(): conn = psycopg2.connect(load_db_url()) cur = conn.cursor() # Create PostLossPerformance table cur.execute(''' CREATE TABLE IF NOT EXISTS "PostLossPerformance" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, team VARCHAR(100) NOT NULL, season VARCHAR(20), -- Post-Loss Stats "postLossGames" INTEGER DEFAULT 0, "postLossWins" INTEGER DEFAULT 0, "postLossWinPct" NUMERIC, "postLossAvgMargin" NUMERIC, "postLossATSWins" INTEGER DEFAULT 0, "postLossATSPct" NUMERIC, -- Post-Bad-Loss Stats (10+ pt loss) "postBadLossGames" INTEGER DEFAULT 0, "postBadLossWins" INTEGER DEFAULT 0, "postBadLossWinPct" NUMERIC, -- Post-Win Stats (for comparison) "postWinGames" INTEGER DEFAULT 0, "postWinWins" INTEGER DEFAULT 0, "postWinWinPct" NUMERIC, -- Bounce-back Rating "bounceBackRating" VARCHAR(30), "bounceBackScore" INTEGER, "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, team, season) ) ''') conn.commit() print("PostLossPerformance table created/verified") # Process each league leagues = ['nba', 'nfl', 'nhl', 'mlb', 'ncaab', 'ncaaf'] for league in leagues: print(f"\nComputing {league.upper()} post-loss performance...") cur.execute(''' WITH game_sequence AS ( SELECT sg.league, sg.season, sg."homeTeam" as team, sg."gameDate", sg."homeScore" as team_score, sg."awayScore" as opp_score, sg."homeScore" - sg."awayScore" as margin, CASE WHEN sg."homeScore" > sg."awayScore" THEN 1 ELSE 0 END as won, LAG(sg."homeScore" - sg."awayScore") OVER ( PARTITION BY sg.league, sg."homeTeam" ORDER BY sg."gameDate" ) as prev_margin, LAG(CASE WHEN sg."homeScore" > sg."awayScore" THEN 1 ELSE 0 END) OVER ( PARTITION BY sg.league, sg."homeTeam" ORDER BY sg."gameDate" ) as prev_won FROM "SportsGame" sg WHERE sg.league = %s AND sg."homeScore" IS NOT NULL UNION ALL SELECT sg.league, sg.season, sg."awayTeam" as team, sg."gameDate", sg."awayScore" as team_score, sg."homeScore" as opp_score, sg."awayScore" - sg."homeScore" as margin, CASE WHEN sg."awayScore" > sg."homeScore" THEN 1 ELSE 0 END as won, LAG(sg."awayScore" - sg."homeScore") OVER ( PARTITION BY sg.league, sg."awayTeam" ORDER BY sg."gameDate" ) as prev_margin, LAG(CASE WHEN sg."awayScore" > sg."homeScore" THEN 1 ELSE 0 END) OVER ( PARTITION BY sg.league, sg."awayTeam" ORDER BY sg."gameDate" ) as prev_won FROM "SportsGame" sg WHERE sg.league = %s AND sg."homeScore" IS NOT NULL ), team_post_loss AS ( SELECT league, season, team, -- Post any loss COUNT(*) FILTER (WHERE prev_won = 0) as post_loss_games, SUM(won) FILTER (WHERE prev_won = 0) as post_loss_wins, ROUND(AVG(margin) FILTER (WHERE prev_won = 0)::numeric, 1) as post_loss_margin, -- Post bad loss (10+ pts) COUNT(*) FILTER (WHERE prev_margin < -10) as post_bad_loss_games, SUM(won) FILTER (WHERE prev_margin < -10) as post_bad_loss_wins, -- Post win COUNT(*) FILTER (WHERE prev_won = 1) as post_win_games, SUM(won) FILTER (WHERE prev_won = 1) as post_win_wins FROM game_sequence WHERE prev_won IS NOT NULL GROUP BY league, season, team HAVING COUNT(*) FILTER (WHERE prev_won = 0) >= 3 ) INSERT INTO "PostLossPerformance" (league, team, season, "postLossGames", "postLossWins", "postLossWinPct", "postLossAvgMargin", "postBadLossGames", "postBadLossWins", "postBadLossWinPct", "postWinGames", "postWinWins", "postWinWinPct", "bounceBackRating", "bounceBackScore") SELECT league, team, season, post_loss_games, post_loss_wins, ROUND((post_loss_wins::numeric / NULLIF(post_loss_games, 0) * 100)::numeric, 1), post_loss_margin, post_bad_loss_games, post_bad_loss_wins, ROUND((post_bad_loss_wins::numeric / NULLIF(post_bad_loss_games, 0) * 100)::numeric, 1), post_win_games, post_win_wins, ROUND((post_win_wins::numeric / NULLIF(post_win_games, 0) * 100)::numeric, 1), CASE WHEN post_loss_wins::numeric / NULLIF(post_loss_games, 0) >= 0.6 THEN 'EXCELLENT_BOUNCE_BACK' WHEN post_loss_wins::numeric / NULLIF(post_loss_games, 0) >= 0.5 THEN 'GOOD_BOUNCE_BACK' WHEN post_loss_wins::numeric / NULLIF(post_loss_games, 0) >= 0.4 THEN 'AVERAGE_BOUNCE_BACK' ELSE 'POOR_BOUNCE_BACK' END, ROUND((post_loss_wins::numeric / NULLIF(post_loss_games, 0) * 100)::numeric, 0)::int FROM team_post_loss ON CONFLICT (league, team, season) DO UPDATE SET "postLossGames" = EXCLUDED."postLossGames", "postLossWins" = EXCLUDED."postLossWins", "postLossWinPct" = EXCLUDED."postLossWinPct", "postLossAvgMargin" = EXCLUDED."postLossAvgMargin", "postBadLossGames" = EXCLUDED."postBadLossGames", "postBadLossWins" = EXCLUDED."postBadLossWins", "postBadLossWinPct" = EXCLUDED."postBadLossWinPct", "postWinGames" = EXCLUDED."postWinGames", "postWinWins" = EXCLUDED."postWinWins", "postWinWinPct" = EXCLUDED."postWinWinPct", "bounceBackRating" = EXCLUDED."bounceBackRating", "bounceBackScore" = EXCLUDED."bounceBackScore", "updatedAt" = NOW() ''', (league, league)) count = cur.rowcount conn.commit() print(f" {league.upper()}: {count} team-seasons") # Summary cur.execute(''' SELECT league, "bounceBackRating", COUNT(*) FROM "PostLossPerformance" GROUP BY league, "bounceBackRating" ORDER BY league, COUNT(*) DESC ''') print("\n\nBounce-Back Rating 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") # Show best bounce-back teams cur.execute(''' SELECT league, team, "postLossWinPct", "postLossGames" FROM "PostLossPerformance" WHERE "bounceBackRating" = 'EXCELLENT_BOUNCE_BACK' AND "postLossGames" >= 5 ORDER BY "postLossWinPct" DESC LIMIT 10 ''') print("\n\nTop Bounce-Back Teams (60%+ win rate after loss):") for row in cur.fetchall(): print(f" {row[0].upper()} {row[1]}: {row[2]}% ({row[3]} games)") cur.execute('SELECT COUNT(*) FROM "PostLossPerformance"') total = cur.fetchone()[0] cur.close() conn.close() print(f"\n✅ Post-loss performance complete: {total} team-seasons") if __name__ == '__main__': compute_post_loss()