#!/usr/bin/env python3 """ Compute Standings & Motivation Context Derives playoff position, elimination status, and motivation scores Fixes: Q10 "Is this a must-win game for either team?" """ 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_standings(): conn = psycopg2.connect(load_db_url()) cur = conn.cursor() # Create StandingsContext table cur.execute(''' CREATE TABLE IF NOT EXISTS "StandingsContext" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, season VARCHAR(20), team VARCHAR(100) NOT NULL, "asOfDate" DATE, wins INTEGER DEFAULT 0, losses INTEGER DEFAULT 0, "winPct" NUMERIC, "gamesPlayed" INTEGER DEFAULT 0, "gamesRemaining" INTEGER, "conferenceRank" INTEGER, "divisionRank" INTEGER, "gamesBack" NUMERIC, "last10Wins" INTEGER, "streak" INTEGER, "streakType" VARCHAR(10), "playoffPosition" VARCHAR(30), "eliminationNumber" INTEGER, "clinchNumber" INTEGER, "motivationScore" INTEGER, "motivationType" VARCHAR(30), "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, team, season, "asOfDate") ) ''') conn.commit() print("StandingsContext table created/verified") # Process major leagues leagues_config = { 'nba': {'season_games': 82, 'playoff_teams': 10, 'current_season': '2024-25'}, 'nfl': {'season_games': 17, 'playoff_teams': 7, 'current_season': '2024'}, 'nhl': {'season_games': 82, 'playoff_teams': 8, 'current_season': '2024-25'}, 'mlb': {'season_games': 162, 'playoff_teams': 6, 'current_season': '2024'}, 'ncaab': {'season_games': 35, 'playoff_teams': 68, 'current_season': '2024-25'}, 'ncaaf': {'season_games': 15, 'playoff_teams': 12, 'current_season': '2024'}, } for league, config in leagues_config.items(): print(f"\nComputing {league.upper()} standings...") # Compute current standings from game results cur.execute(''' WITH team_games AS ( -- Home games SELECT league, season, "homeTeam" as team, "gameDate", CASE WHEN "homeScore" > "awayScore" THEN 1 ELSE 0 END as won, 1 as played FROM "SportsGame" WHERE league = %s AND "homeScore" IS NOT NULL UNION ALL -- Away games SELECT league, season, "awayTeam" as team, "gameDate", CASE WHEN "awayScore" > "homeScore" THEN 1 ELSE 0 END as won, 1 as played FROM "SportsGame" WHERE league = %s AND "homeScore" IS NOT NULL ), standings AS ( SELECT league, season, team, MAX("gameDate") as as_of_date, SUM(won) as wins, SUM(played) - SUM(won) as losses, SUM(played) as games_played, ROUND((SUM(won)::numeric / NULLIF(SUM(played), 0))::numeric, 3) as win_pct FROM team_games WHERE "gameDate" > NOW() - INTERVAL '365 days' GROUP BY league, season, team HAVING SUM(played) >= 5 ), ranked AS ( SELECT *, RANK() OVER (PARTITION BY league, season ORDER BY win_pct DESC) as conf_rank, FIRST_VALUE(win_pct) OVER (PARTITION BY league, season ORDER BY win_pct DESC) as leader_pct FROM standings ) INSERT INTO "StandingsContext" (league, season, team, "asOfDate", wins, losses, "winPct", "gamesPlayed", "gamesRemaining", "conferenceRank", "gamesBack", "playoffPosition", "motivationScore", "motivationType") SELECT league, season, team, as_of_date, wins, losses, win_pct, games_played, %s - games_played, conf_rank, ROUND(((leader_pct - win_pct) * games_played)::numeric, 1), CASE WHEN conf_rank <= %s THEN 'PLAYOFF_POSITION' WHEN conf_rank <= %s + 2 THEN 'PLAYOFF_RACE' ELSE 'OUT_OF_RACE' END, -- Motivation score (0-100) CASE -- High motivation: tight playoff race WHEN conf_rank BETWEEN %s - 2 AND %s + 2 AND (%s - games_played) <= 20 THEN 85 -- High motivation: fighting for seeding WHEN conf_rank <= 4 AND (%s - games_played) <= 15 THEN 75 -- Medium: solid playoff team WHEN conf_rank <= %s THEN 60 -- Medium: bubble team WHEN conf_rank <= %s + 4 THEN 70 -- Low: out of race ELSE 40 END, CASE WHEN conf_rank BETWEEN %s - 2 AND %s + 2 AND (%s - games_played) <= 20 THEN 'MUST_WIN' WHEN conf_rank <= 4 AND (%s - games_played) <= 15 THEN 'SEEDING_BATTLE' WHEN conf_rank <= %s THEN 'PLAYOFF_LOCK' WHEN conf_rank <= %s + 4 THEN 'BUBBLE_TEAM' ELSE 'PLAYING_OUT_STRING' END FROM ranked ON CONFLICT (league, team, season, "asOfDate") DO UPDATE SET wins = EXCLUDED.wins, losses = EXCLUDED.losses, "winPct" = EXCLUDED."winPct", "gamesPlayed" = EXCLUDED."gamesPlayed", "gamesRemaining" = EXCLUDED."gamesRemaining", "conferenceRank" = EXCLUDED."conferenceRank", "gamesBack" = EXCLUDED."gamesBack", "playoffPosition" = EXCLUDED."playoffPosition", "motivationScore" = EXCLUDED."motivationScore", "motivationType" = EXCLUDED."motivationType", "updatedAt" = NOW() ''', ( league, league, config['season_games'], config['playoff_teams'], config['playoff_teams'], config['playoff_teams'], config['playoff_teams'], config['season_games'], config['season_games'], config['playoff_teams'], config['playoff_teams'], config['playoff_teams'], config['playoff_teams'], config['season_games'], config['season_games'], config['playoff_teams'], config['playoff_teams'] )) count = cur.rowcount conn.commit() print(f" {league.upper()}: {count} team standings") # Compute streaks print("\nComputing win/loss streaks...") cur.execute(''' WITH recent_results AS ( SELECT league, "homeTeam" as team, "gameDate", CASE WHEN "homeScore" > "awayScore" THEN 'W' ELSE 'L' END as result FROM "SportsGame" WHERE "homeScore" IS NOT NULL AND "gameDate" > NOW() - INTERVAL '30 days' UNION ALL SELECT league, "awayTeam" as team, "gameDate", CASE WHEN "awayScore" > "homeScore" THEN 'W' ELSE 'L' END as result FROM "SportsGame" WHERE "homeScore" IS NOT NULL AND "gameDate" > NOW() - INTERVAL '30 days' ), last_game AS ( SELECT DISTINCT ON (league, team) league, team, result as last_result FROM recent_results ORDER BY league, team, "gameDate" DESC ), last10 AS ( SELECT league, team, SUM(CASE WHEN result = 'W' THEN 1 ELSE 0 END) as l10_wins FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY league, team ORDER BY "gameDate" DESC) as rn FROM recent_results ) sub WHERE rn <= 10 GROUP BY league, team ) UPDATE "StandingsContext" sc SET "last10Wins" = l10.l10_wins, "streakType" = lg.last_result FROM last10 l10 JOIN last_game lg ON l10.league = lg.league AND l10.team = lg.team WHERE sc.league = l10.league AND sc.team = l10.team ''') conn.commit() # Summary cur.execute(''' SELECT league, "motivationType", COUNT(*) FROM "StandingsContext" GROUP BY league, "motivationType" ORDER BY league, COUNT(*) DESC ''') print("\n\nMotivation 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") cur.execute('SELECT COUNT(*) FROM "StandingsContext"') total = cur.fetchone()[0] cur.close() conn.close() print(f"\n✅ Standings context complete: {total} team records") if __name__ == '__main__': compute_standings()