#!/usr/bin/env python3 """ Compute Prop Game State Accuracy Prop hit rates by game state (close vs blowout, leading vs trailing) Answers: "Tatum overs hit more in blowouts?" "Props less accurate in close games?" Supports: NBA, NFL """ import psycopg2 from datetime import datetime 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_prop_game_state_accuracy(): conn = psycopg2.connect(load_db_url()) cur = conn.cursor() # Create PropGameStateAccuracy table cur.execute(''' CREATE TABLE IF NOT EXISTS "PropGameStateAccuracy" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, season VARCHAR(20), "playerExternalId" TEXT, "playerName" VARCHAR(200), team VARCHAR(100), "propType" VARCHAR(50), -- Overall stats "totalProps" INTEGER DEFAULT 0, "overallHitRate" NUMERIC, "avgLine" NUMERIC, "avgActual" NUMERIC, -- By game state "closeGameProps" INTEGER DEFAULT 0, "closeGameHitRate" NUMERIC, "blowoutProps" INTEGER DEFAULT 0, "blowoutHitRate" NUMERIC, -- By team result "teamWinProps" INTEGER DEFAULT 0, "teamWinHitRate" NUMERIC, "teamLossProps" INTEGER DEFAULT 0, "teamLossHitRate" NUMERIC, -- Differential analysis "closeVsBlowoutDiff" NUMERIC, "winVsLossDiff" NUMERIC, "situationalBias" VARCHAR(30), "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, "playerExternalId", "propType", season) ) ''') conn.commit() print("PropGameStateAccuracy table created/verified") # NBA prop accuracy by game state print("\nComputing NBA prop game state accuracy...") cur.execute(''' WITH prop_results AS ( SELECT ppl.league, ppl."playerExternalId", ppl."propType", ppl."lineValue", pgm.team, pgm."gameDate", pgm.value as actual_value, CASE WHEN pgm.value > ppl."lineValue" THEN 1 ELSE 0 END as hit_over, -- Game state from GameStateTracking gst."closeGame", gst.blowout, -- Team result CASE WHEN pgm.team = gst."homeTeam" THEN CASE WHEN gst."finalHomeScore" > gst."finalAwayScore" THEN 1 ELSE 0 END ELSE CASE WHEN gst."finalAwayScore" > gst."finalHomeScore" THEN 1 ELSE 0 END END as team_won FROM "PlayerPropLine" ppl JOIN "PlayerGameMetric" pgm ON ppl.league = pgm.league AND ppl."playerExternalId" = pgm."playerExternalId" AND ppl."propType" ILIKE '%' || pgm."statKey" || '%' JOIN "SportsGame" sg ON sg.league = pgm.league AND sg."gameDate" = pgm."gameDate" AND (sg."homeTeam" = pgm.team OR sg."awayTeam" = pgm.team) JOIN "GameStateTracking" gst ON gst.league = pgm.league AND gst."gameDate" = pgm."gameDate" AND (gst."homeTeam" = pgm.team OR gst."awayTeam" = pgm.team) WHERE ppl.league = 'nba' AND ppl."propType" IN ('points', 'rebounds', 'assists', 'pts', 'reb', 'ast') AND pgm."statKey" IN ('espn_pts', 'espn_reb', 'espn_ast', 'points', 'rebounds', 'assists') AND gst."finalHomeScore" IS NOT NULL ), aggregated AS ( SELECT league, "playerExternalId", team, "propType", EXTRACT(YEAR FROM "gameDate")::text as season, -- Overall COUNT(*) as total_props, AVG(hit_over) as overall_hit_rate, ROUND(AVG("lineValue")::numeric, 1) as avg_line, ROUND(AVG(actual_value)::numeric, 1) as avg_actual, -- Close games COUNT(CASE WHEN "closeGame" THEN 1 END) as close_props, AVG(CASE WHEN "closeGame" THEN hit_over END) as close_hit_rate, -- Blowouts COUNT(CASE WHEN blowout THEN 1 END) as blowout_props, AVG(CASE WHEN blowout THEN hit_over END) as blowout_hit_rate, -- Team wins COUNT(CASE WHEN team_won = 1 THEN 1 END) as win_props, AVG(CASE WHEN team_won = 1 THEN hit_over END) as win_hit_rate, -- Team losses COUNT(CASE WHEN team_won = 0 THEN 1 END) as loss_props, AVG(CASE WHEN team_won = 0 THEN hit_over END) as loss_hit_rate FROM prop_results GROUP BY league, "playerExternalId", team, "propType", EXTRACT(YEAR FROM "gameDate") HAVING COUNT(*) >= 3 ) INSERT INTO "PropGameStateAccuracy" (league, season, "playerExternalId", team, "propType", "totalProps", "overallHitRate", "avgLine", "avgActual", "closeGameProps", "closeGameHitRate", "blowoutProps", "blowoutHitRate", "teamWinProps", "teamWinHitRate", "teamLossProps", "teamLossHitRate", "closeVsBlowoutDiff", "winVsLossDiff", "situationalBias") SELECT league, season, "playerExternalId", team, "propType", total_props, ROUND(overall_hit_rate::numeric, 3), avg_line, avg_actual, close_props, ROUND(close_hit_rate::numeric, 3), blowout_props, ROUND(blowout_hit_rate::numeric, 3), win_props, ROUND(win_hit_rate::numeric, 3), loss_props, ROUND(loss_hit_rate::numeric, 3), ROUND((close_hit_rate - blowout_hit_rate)::numeric, 3), ROUND((win_hit_rate - loss_hit_rate)::numeric, 3), CASE WHEN close_props >= 2 AND blowout_props >= 2 AND close_hit_rate > blowout_hit_rate + 0.15 THEN 'CLOSE_GAME_PLAYER' WHEN close_props >= 2 AND blowout_props >= 2 AND blowout_hit_rate > close_hit_rate + 0.15 THEN 'BLOWOUT_PADDER' WHEN win_props >= 2 AND loss_props >= 2 AND win_hit_rate > loss_hit_rate + 0.2 THEN 'BETTER_IN_WINS' WHEN win_props >= 2 AND loss_props >= 2 AND loss_hit_rate > win_hit_rate + 0.2 THEN 'GARBAGE_TIME_PLAYER' ELSE 'CONSISTENT' END FROM aggregated ON CONFLICT (league, "playerExternalId", "propType", season) DO UPDATE SET "totalProps" = EXCLUDED."totalProps", "overallHitRate" = EXCLUDED."overallHitRate", "avgLine" = EXCLUDED."avgLine", "avgActual" = EXCLUDED."avgActual", "closeGameProps" = EXCLUDED."closeGameProps", "closeGameHitRate" = EXCLUDED."closeGameHitRate", "blowoutProps" = EXCLUDED."blowoutProps", "blowoutHitRate" = EXCLUDED."blowoutHitRate", "teamWinProps" = EXCLUDED."teamWinProps", "teamWinHitRate" = EXCLUDED."teamWinHitRate", "teamLossProps" = EXCLUDED."teamLossProps", "teamLossHitRate" = EXCLUDED."teamLossHitRate", "closeVsBlowoutDiff" = EXCLUDED."closeVsBlowoutDiff", "winVsLossDiff" = EXCLUDED."winVsLossDiff", "situationalBias" = EXCLUDED."situationalBias", "updatedAt" = NOW() ''') nba_count = cur.rowcount conn.commit() print(f" NBA: {nba_count} prop accuracy records") # Add player names print("\nAdding player names...") cur.execute(''' UPDATE "PropGameStateAccuracy" pga SET "playerName" = p.name FROM "Player" p WHERE pga."playerExternalId" = p."externalPlayerId" AND pga."playerName" IS NULL ''') conn.commit() # Summary cur.execute(''' SELECT "situationalBias", COUNT(*), ROUND(AVG("closeVsBlowoutDiff")::numeric, 3) as avg_close_diff, ROUND(AVG("winVsLossDiff")::numeric, 3) as avg_win_diff FROM "PropGameStateAccuracy" WHERE "situationalBias" IS NOT NULL GROUP BY "situationalBias" ORDER BY COUNT(*) DESC ''') print("\nSituational Bias Distribution:") for row in cur.fetchall(): close_diff = row[2] if row[2] else 0 win_diff = row[3] if row[3] else 0 print(f" {row[0]}: {row[1]} records (close diff: {close_diff:+.3f}, win diff: {win_diff:+.3f})") cur.execute('SELECT COUNT(*) FROM "PropGameStateAccuracy"') total = cur.fetchone()[0] cur.close() conn.close() print(f"\n✅ Prop game state accuracy complete: {total} records") if __name__ == '__main__': compute_prop_game_state_accuracy()