#!/usr/bin/env python3 """ Compute Game State Tracking Adds lead/deficit tracking to pace data for Q5 (blowing leads), Q34 (panic when trailing) """ import psycopg2 import json 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_game_state(): conn = psycopg2.connect(load_db_url()) cur = conn.cursor() # Create GameStateTracking table cur.execute(''' CREATE TABLE IF NOT EXISTS "GameStateTracking" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, "gameId" TEXT, "gameDate" DATE, "homeTeam" VARCHAR(100), "awayTeam" VARCHAR(100), "finalHomeScore" INTEGER, "finalAwayScore" INTEGER, "maxHomeLead" INTEGER, "maxAwayLead" INTEGER, "leadChanges" INTEGER, "homeLeadQ1" INTEGER, "homeLeadQ2" INTEGER, "homeLeadQ3" INTEGER, "homeBlownLead" BOOLEAN DEFAULT FALSE, "awayBlownLead" BOOLEAN DEFAULT FALSE, "homeComeback" BOOLEAN DEFAULT FALSE, "awayComeback" BOOLEAN DEFAULT FALSE, "closeGame" BOOLEAN DEFAULT FALSE, "blowout" BOOLEAN DEFAULT FALSE, "gameType" VARCHAR(30), "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, "gameId") ) ''') conn.commit() print("GameStateTracking table created/verified") # Get games with period data from LiveOddsSnapshot (final games only) print("\nProcessing games with period data...") cur.execute(''' SELECT DISTINCT ON (league, "externalGameId") league, "externalGameId", "gameDate", "homeTeam", "awayTeam", "homeScore", "awayScore", "homePeriodScores", "awayPeriodScores" FROM "LiveOddsSnapshot" WHERE "gameStatus" ILIKE '%final%' AND "homePeriodScores" IS NOT NULL AND "awayPeriodScores" IS NOT NULL ORDER BY league, "externalGameId", "snapshotAt" DESC ''') games_processed = 0 for row in cur.fetchall(): league, game_id, game_date, home_team, away_team, final_home, final_away, home_periods, away_periods = row if not home_periods or not away_periods: continue # Parse period scores (stored as JSONB dict like {'1': 30, '2': 25, ...}) try: hp = home_periods if isinstance(home_periods, dict) else json.loads(home_periods) ap = away_periods if isinstance(away_periods, dict) else json.loads(away_periods) except: continue # Calculate running scores at end of each period home_running = 0 away_running = 0 leads = [] for p in ['1', '2', '3', '4']: home_running += hp.get(p, 0) away_running += ap.get(p, 0) leads.append(home_running - away_running) home_lead_q1 = leads[0] if len(leads) > 0 else None home_lead_q2 = leads[1] if len(leads) > 1 else None home_lead_q3 = leads[2] if len(leads) > 2 else None # Determine max leads max_home_lead = max(leads) if leads else 0 max_away_lead = -min(leads) if leads else 0 # Count lead changes lead_changes = 0 prev_leader = None for lead in leads: current_leader = 'home' if lead > 0 else ('away' if lead < 0 else 'tie') if prev_leader and current_leader != 'tie' and prev_leader != current_leader: lead_changes += 1 if current_leader != 'tie': prev_leader = current_leader # Final margin final_margin = (final_home or 0) - (final_away or 0) home_won = final_margin > 0 # Blown lead: had 10+ lead but lost home_blown = max_home_lead >= 10 and not home_won away_blown = max_away_lead >= 10 and home_won # Comeback: was down 10+ but won home_comeback = max_away_lead >= 10 and home_won away_comeback = max_home_lead >= 10 and not home_won close_game = abs(final_margin) <= 5 blowout = abs(final_margin) >= 20 # Game type if blowout: game_type = 'BLOWOUT' elif close_game: game_type = 'CLOSE' elif home_blown: game_type = 'HOME_COLLAPSE' elif away_blown: game_type = 'AWAY_COLLAPSE' elif home_comeback: game_type = 'HOME_COMEBACK' elif away_comeback: game_type = 'AWAY_COMEBACK' else: game_type = 'NORMAL' # Insert cur.execute(''' INSERT INTO "GameStateTracking" (league, "gameId", "gameDate", "homeTeam", "awayTeam", "finalHomeScore", "finalAwayScore", "maxHomeLead", "maxAwayLead", "leadChanges", "homeLeadQ1", "homeLeadQ2", "homeLeadQ3", "homeBlownLead", "awayBlownLead", "homeComeback", "awayComeback", "closeGame", "blowout", "gameType") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (league, "gameId") DO UPDATE SET "maxHomeLead" = EXCLUDED."maxHomeLead", "maxAwayLead" = EXCLUDED."maxAwayLead", "leadChanges" = EXCLUDED."leadChanges", "homeLeadQ1" = EXCLUDED."homeLeadQ1", "homeLeadQ2" = EXCLUDED."homeLeadQ2", "homeLeadQ3" = EXCLUDED."homeLeadQ3", "homeBlownLead" = EXCLUDED."homeBlownLead", "awayBlownLead" = EXCLUDED."awayBlownLead", "homeComeback" = EXCLUDED."homeComeback", "awayComeback" = EXCLUDED."awayComeback", "closeGame" = EXCLUDED."closeGame", "blowout" = EXCLUDED."blowout", "gameType" = EXCLUDED."gameType", "updatedAt" = NOW() ''', (league, game_id, game_date, home_team, away_team, final_home, final_away, max_home_lead, max_away_lead, lead_changes, home_lead_q1, home_lead_q2, home_lead_q3, home_blown, away_blown, home_comeback, away_comeback, close_game, blowout, game_type)) games_processed += 1 conn.commit() print(f" Games with period data: {games_processed}") # Also add basic game state from SportsGame (for games without period data) print("\nAdding basic game states from SportsGame...") cur.execute(''' INSERT INTO "GameStateTracking" (league, "gameId", "gameDate", "homeTeam", "awayTeam", "finalHomeScore", "finalAwayScore", "closeGame", "blowout", "gameType") SELECT league, id::text, "gameDate", "homeTeam", "awayTeam", "homeScore", "awayScore", ABS("homeScore" - "awayScore") <= 5, ABS("homeScore" - "awayScore") >= 20, CASE WHEN ABS("homeScore" - "awayScore") >= 20 THEN 'BLOWOUT' WHEN ABS("homeScore" - "awayScore") <= 5 THEN 'CLOSE' ELSE 'NORMAL' END FROM "SportsGame" WHERE "homeScore" IS NOT NULL AND "gameDate" > '2024-01-01' ON CONFLICT (league, "gameId") DO NOTHING ''') basic_count = cur.rowcount conn.commit() print(f" Basic game states added: {basic_count}") # Summary cur.execute(''' SELECT league, "gameType", COUNT(*) FROM "GameStateTracking" GROUP BY league, "gameType" ORDER BY league, COUNT(*) DESC ''') print("\nGame Type Summary:") 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]}") # Blown lead stats cur.execute(''' SELECT league, COUNT(*) FILTER (WHERE "homeBlownLead") as home_blown, COUNT(*) FILTER (WHERE "awayBlownLead") as away_blown, COUNT(*) FILTER (WHERE "homeComeback") as home_comeback, COUNT(*) FILTER (WHERE "awayComeback") as away_comeback FROM "GameStateTracking" WHERE "homeBlownLead" IS NOT NULL GROUP BY league ORDER BY league ''') results = cur.fetchall() if results: print("\n\nBlown Lead/Comeback Stats (10+ pt lead lost):") for row in results: print(f" {row[0].upper()}: Home blown {row[1]}, Away blown {row[2]}, Home comeback {row[3]}, Away comeback {row[4]}") cur.close() conn.close() print(f"\n✅ Game state tracking complete: {games_processed + basic_count} total games") if __name__ == '__main__': compute_game_state()