#!/usr/bin/env python3 """ Compute Trap Game Scoring Identifies trap game situations based on schedule context, opponent strength, rest. Fixes: Q10 "Is this a trap game or straightforward spot?" """ import psycopg2 import json 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_trap_games(): conn = psycopg2.connect(load_db_url()) cur = conn.cursor() # Create TrapGameScore table cur.execute(''' CREATE TABLE IF NOT EXISTS "TrapGameScore" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, "gameId" TEXT, "gameDate" DATE, "homeTeam" VARCHAR(100), "awayTeam" VARCHAR(100), "trapScoreHome" INTEGER DEFAULT 0, "trapScoreAway" INTEGER DEFAULT 0, "trapFactorsHome" JSONB, "trapFactorsAway" JSONB, "homeIsTrapGame" BOOLEAN DEFAULT FALSE, "awayIsTrapGame" BOOLEAN DEFAULT FALSE, "trapType" VARCHAR(50), "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, "gameId") ) ''') conn.commit() print("TrapGameScore table created/verified") # Get upcoming and recent games with schedule context print("\nComputing trap game scores...") # Process games by league leagues = ['nba', 'nfl', 'nhl', 'ncaab', 'ncaaf', 'mlb'] for league in leagues: print(f"\n Processing {league.upper()}...") # Get games with schedule metadata cur.execute(''' WITH game_schedule AS ( SELECT sg.id, sg.league, sg."gameDate", sg."homeTeam", sg."awayTeam", sg."homeScore", sg."awayScore", gsm_home."restDays" as home_rest, gsm_away."restDays" as away_rest, gsm_home."isBackToBack" as home_b2b, gsm_away."isBackToBack" as away_b2b, gsm_home."homeStandGame" as home_stand_game, gsm_away."roadTripGame" as away_road_game FROM "SportsGame" sg LEFT JOIN "GameScheduleMeta" gsm_home ON sg.id = gsm_home."gameId" AND sg."homeTeam" = gsm_home.team LEFT JOIN "GameScheduleMeta" gsm_away ON sg.id = gsm_away."gameId" AND sg."awayTeam" = gsm_away.team WHERE sg.league = %s AND sg."gameDate" >= '2024-01-01' ORDER BY sg."homeTeam", sg."gameDate" ), with_opponent_strength AS ( SELECT gs.*, opr."homeStrength", opr."awayStrength", opr."matchupType" FROM game_schedule gs LEFT JOIN "OpponentRating" opr ON gs.id::text = opr."gameId" AND gs.league = opr.league ), with_prev_next AS ( SELECT *, LAG("awayStrength") OVER (PARTITION BY "homeTeam" ORDER BY "gameDate") as prev_opp_strength, LEAD("awayStrength") OVER (PARTITION BY "homeTeam" ORDER BY "gameDate") as next_opp_strength, LAG("homeScore" - "awayScore") OVER (PARTITION BY "homeTeam" ORDER BY "gameDate") as prev_margin FROM with_opponent_strength ) SELECT * FROM with_prev_next WHERE "gameDate" > NOW() - INTERVAL '30 days' ''', (league,)) games = cur.fetchall() cols = [desc[0] for desc in cur.description] for row in games: game = dict(zip(cols, row)) home_trap_score = 0 away_trap_score = 0 home_factors = [] away_factors = [] # Factor 1: Letdown after big win (10+ points) if game.get('prev_margin') and game['prev_margin'] >= 10: home_trap_score += 15 home_factors.append("LETDOWN_AFTER_BIG_WIN") # Factor 2: Look-ahead to tough opponent if game.get('next_opp_strength') in ('ELITE', 'GOOD'): if game.get('awayStrength') in ('WEAK', 'AVERAGE'): home_trap_score += 20 home_factors.append("LOOKAHEAD_SPOT") # Factor 3: Short rest / Back-to-back if game.get('home_b2b'): home_trap_score += 15 home_factors.append("BACK_TO_BACK") elif game.get('home_rest') and game['home_rest'] <= 1: home_trap_score += 10 home_factors.append("SHORT_REST") # Factor 4: Long road trip (3+ games) if game.get('away_road_game') and game['away_road_game'] >= 3: away_trap_score += 15 away_factors.append("LONG_ROAD_TRIP") # Factor 5: Heavy favorite vs weak team (classic trap) if game.get('homeStrength') == 'ELITE' and game.get('awayStrength') == 'WEAK': home_trap_score += 10 home_factors.append("HEAVY_FAVORITE_TRAP") # Factor 6: Coming off loss to weak team (revenge game next, trap this one) if game.get('prev_margin') and game['prev_margin'] < -5: if game.get('prev_opp_strength') == 'WEAK': home_trap_score += 10 home_factors.append("POST_BAD_LOSS") # Determine if it's a trap game (score >= 25) home_is_trap = home_trap_score >= 25 away_is_trap = away_trap_score >= 25 # Determine trap type trap_type = None if home_is_trap: if "LOOKAHEAD_SPOT" in home_factors: trap_type = "LOOKAHEAD" elif "LETDOWN_AFTER_BIG_WIN" in home_factors: trap_type = "LETDOWN" elif "BACK_TO_BACK" in home_factors: trap_type = "FATIGUE" else: trap_type = "SCHEDULE" # Insert cur.execute(''' INSERT INTO "TrapGameScore" (league, "gameId", "gameDate", "homeTeam", "awayTeam", "trapScoreHome", "trapScoreAway", "trapFactorsHome", "trapFactorsAway", "homeIsTrapGame", "awayIsTrapGame", "trapType") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (league, "gameId") DO UPDATE SET "trapScoreHome" = EXCLUDED."trapScoreHome", "trapScoreAway" = EXCLUDED."trapScoreAway", "trapFactorsHome" = EXCLUDED."trapFactorsHome", "trapFactorsAway" = EXCLUDED."trapFactorsAway", "homeIsTrapGame" = EXCLUDED."homeIsTrapGame", "awayIsTrapGame" = EXCLUDED."awayIsTrapGame", "trapType" = EXCLUDED."trapType", "updatedAt" = NOW() ''', ( league, str(game['id']), game['gameDate'], game['homeTeam'], game['awayTeam'], home_trap_score, away_trap_score, json.dumps(home_factors) if home_factors else None, json.dumps(away_factors) if away_factors else None, home_is_trap, away_is_trap, trap_type )) conn.commit() # Summary cur.execute(''' SELECT league, COUNT(*) as total, COUNT(*) FILTER (WHERE "homeIsTrapGame" OR "awayIsTrapGame") as trap_games FROM "TrapGameScore" GROUP BY league ORDER BY league ''') print("\n\nTrap Game Summary:") print("-" * 50) for row in cur.fetchall(): pct = (row[2] / row[1] * 100) if row[1] > 0 else 0 print(f" {row[0].upper()}: {row[2]}/{row[1]} trap games ({pct:.1f}%)") # Show trap types cur.execute(''' SELECT "trapType", COUNT(*) FROM "TrapGameScore" WHERE "trapType" IS NOT NULL GROUP BY "trapType" ORDER BY COUNT(*) DESC ''') print("\nTrap Types:") for row in cur.fetchall(): print(f" {row[0]}: {row[1]}") cur.close() conn.close() print("\n✅ Trap game scoring complete") if __name__ == '__main__': compute_trap_games()