#!/usr/bin/env python3 """ Compute Rolling Averages for Teams Creates TeamRollingStats table with last 5, 10 game averages for key metrics. Answers Q2: "Has this team been playing better or worse over last 5 games?" """ 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_rolling_averages(): db_url = load_db_url() conn = psycopg2.connect(db_url) cur = conn.cursor() print("=" * 60) print("COMPUTE ROLLING AVERAGES") print(f"Time: {datetime.now(timezone.utc).isoformat()}") print("=" * 60) # Create table for rolling stats cur.execute(''' CREATE TABLE IF NOT EXISTS "TeamRollingStats" ( id BIGSERIAL PRIMARY KEY, league VARCHAR(20), season INT, team VARCHAR(100), "gameDate" TIMESTAMP, "gameId" BIGINT, -- Last 5 game metrics "last5_margin" FLOAT, "last5_points_for" FLOAT, "last5_points_against" FLOAT, "last5_wins" INT, "last5_ats_wins" INT, "last5_over_hits" INT, -- Last 10 game metrics "last10_margin" FLOAT, "last10_points_for" FLOAT, "last10_points_against" FLOAT, "last10_wins" INT, "last10_ats_wins" INT, "last10_over_hits" INT, -- Season averages for comparison "season_margin" FLOAT, "season_points_for" FLOAT, "season_points_against" FLOAT, -- Trend indicators "form_vs_season" FLOAT, -- last5_margin - season_margin "updatedAt" TIMESTAMP DEFAULT NOW(), UNIQUE(league, team, "gameId") ) ''') cur.execute('CREATE INDEX IF NOT EXISTS "TeamRollingStats_lookup" ON "TeamRollingStats" (league, team, "gameDate" DESC)') total_records = 0 for league in ['nba', 'nfl', 'nhl']: print(f"\n[{league.upper()}] Computing rolling averages...") # Get all games for each team cur.execute(''' SELECT id, "homeTeam", "awayTeam", "gameDate", "homeScore", "awayScore", "spreadHome", total FROM "SportsGame" WHERE league = %s AND season >= 2024 AND "homeScore" IS NOT NULL ORDER BY "gameDate" ''', (league,)) games = cur.fetchall() print(f" Found {len(games)} games") # Build team game history team_games = defaultdict(list) for g in games: game_id, home, away, gdate, hscore, ascore, spread, total = g margin_home = hscore - ascore home_covered = (margin_home + (spread or 0)) > 0 if spread else None over_hit = (hscore + ascore) > total if total else None team_games[home].append({ 'id': game_id, 'date': gdate, 'is_home': True, 'pf': hscore, 'pa': ascore, 'margin': margin_home, 'won': hscore > ascore, 'covered': home_covered, 'over': over_hit }) team_games[away].append({ 'id': game_id, 'date': gdate, 'is_home': False, 'pf': ascore, 'pa': hscore, 'margin': -margin_home, 'won': ascore > hscore, 'covered': not home_covered if home_covered is not None else None, 'over': over_hit }) # Compute rolling stats for each team for team, games_list in team_games.items(): games_sorted = sorted(games_list, key=lambda x: x['date']) for i, game in enumerate(games_sorted): if i < 4: # Need at least 5 games for rolling continue # Last 5 games last5 = games_sorted[max(0, i-4):i+1] l5_margin = sum(g['margin'] for g in last5) / len(last5) l5_pf = sum(g['pf'] for g in last5) / len(last5) l5_pa = sum(g['pa'] for g in last5) / len(last5) l5_wins = sum(1 for g in last5 if g['won']) l5_ats = sum(1 for g in last5 if g['covered']) l5_over = sum(1 for g in last5 if g['over']) # Last 10 games last10 = games_sorted[max(0, i-9):i+1] l10_margin = sum(g['margin'] for g in last10) / len(last10) l10_pf = sum(g['pf'] for g in last10) / len(last10) l10_pa = sum(g['pa'] for g in last10) / len(last10) l10_wins = sum(1 for g in last10 if g['won']) l10_ats = sum(1 for g in last10 if g['covered']) l10_over = sum(1 for g in last10 if g['over']) # Season average (all games up to this point) all_prior = games_sorted[:i+1] szn_margin = sum(g['margin'] for g in all_prior) / len(all_prior) szn_pf = sum(g['pf'] for g in all_prior) / len(all_prior) szn_pa = sum(g['pa'] for g in all_prior) / len(all_prior) # Form vs season form_vs_szn = l5_margin - szn_margin season = game['date'].year if game['date'].month >= 8 else game['date'].year - 1 cur.execute(''' INSERT INTO "TeamRollingStats" ( league, season, team, "gameDate", "gameId", "last5_margin", "last5_points_for", "last5_points_against", "last5_wins", "last5_ats_wins", "last5_over_hits", "last10_margin", "last10_points_for", "last10_points_against", "last10_wins", "last10_ats_wins", "last10_over_hits", "season_margin", "season_points_for", "season_points_against", "form_vs_season" ) 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, team, "gameId") DO UPDATE SET "last5_margin" = EXCLUDED."last5_margin", "form_vs_season" = EXCLUDED."form_vs_season", "updatedAt" = NOW() ''', ( league, season, team, game['date'], game['id'], l5_margin, l5_pf, l5_pa, l5_wins, l5_ats, l5_over, l10_margin, l10_pf, l10_pa, l10_wins, l10_ats, l10_over, szn_margin, szn_pf, szn_pa, form_vs_szn )) total_records += 1 conn.commit() print(f" Processed {len(team_games)} teams") cur.close() conn.close() print(f"\n✅ Total rolling stats records: {total_records}") return total_records if __name__ == '__main__': compute_rolling_averages()