#!/usr/bin/env python3 """ Compute Broadcast ATS Splits Team ATS by broadcast type (national TV, primetime, regional) Answers: "Lakers overrated on national TV?" "Cowboys underperform in primetime?" Supports: NBA, NFL, NHL """ 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_broadcast_ats(): conn = psycopg2.connect(load_db_url()) cur = conn.cursor() # Create BroadcastATSSplits table cur.execute(''' CREATE TABLE IF NOT EXISTS "BroadcastATSSplits" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, season VARCHAR(20), team VARCHAR(100) NOT NULL, -- National TV splits "nationalTVGames" INTEGER DEFAULT 0, "nationalTVWins" INTEGER DEFAULT 0, "nationalTVCovers" INTEGER DEFAULT 0, "nationalTVWinPct" NUMERIC, "nationalTVATSPct" NUMERIC, -- Non-national splits "nonNationalGames" INTEGER DEFAULT 0, "nonNationalCovers" INTEGER DEFAULT 0, "nonNationalATSPct" NUMERIC, -- Primetime specific (NFL) "primetimeGames" INTEGER DEFAULT 0, "primetimeCovers" INTEGER DEFAULT 0, "primetimeATSPct" NUMERIC, -- Differential "nationalVsNonATSDiff" NUMERIC, "publicBiasScore" INTEGER, "broadcastImpact" VARCHAR(30), "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, team, season) ) ''') conn.commit() print("BroadcastATSSplits table created/verified") # NBA broadcast analysis print("\nComputing NBA broadcast ATS splits...") cur.execute(''' WITH game_broadcasts AS ( SELECT DISTINCT ON (sg.id) sg.league, sg."gameDate", sg."homeTeam", sg."awayTeam", sg."homeScore", sg."awayScore", go."lineValue" as spread, gb.network, gb."isNationalTV", -- Determine if national TV CASE WHEN gb."isNationalTV" = true THEN true WHEN gb.network IN ('ESPN', 'TNT', 'ABC', 'NBA TV', 'FOX', 'NBC', 'CBS') THEN true ELSE false END as is_national FROM "SportsGame" sg LEFT JOIN "GameBroadcast" gb ON gb."gameId"::text = sg."externalGameId" LEFT JOIN "GameOdds" go ON go."gameId" = sg."externalGameId" AND go.market = 'spreads' WHERE sg.league = 'nba' AND sg."homeScore" IS NOT NULL AND go."lineValue" IS NOT NULL ), team_broadcast_stats AS ( -- Home perspective SELECT league, "homeTeam" as team, EXTRACT(YEAR FROM "gameDate")::text as season, is_national, CASE WHEN "homeScore" > "awayScore" THEN 1 ELSE 0 END as won, CASE WHEN "homeScore" - "awayScore" > -spread THEN 1 ELSE 0 END as covered FROM game_broadcasts UNION ALL -- Away perspective SELECT league, "awayTeam" as team, EXTRACT(YEAR FROM "gameDate")::text as season, is_national, CASE WHEN "awayScore" > "homeScore" THEN 1 ELSE 0 END as won, CASE WHEN "awayScore" - "homeScore" > spread THEN 1 ELSE 0 END as covered FROM game_broadcasts ), aggregated AS ( SELECT league, team, season, -- National TV COUNT(CASE WHEN is_national THEN 1 END) as nat_games, SUM(CASE WHEN is_national THEN won END) as nat_wins, SUM(CASE WHEN is_national THEN covered END) as nat_covers, -- Non-national COUNT(CASE WHEN NOT is_national OR is_national IS NULL THEN 1 END) as non_nat_games, SUM(CASE WHEN NOT is_national OR is_national IS NULL THEN covered END) as non_nat_covers FROM team_broadcast_stats GROUP BY league, team, season HAVING COUNT(*) >= 5 ) INSERT INTO "BroadcastATSSplits" (league, season, team, "nationalTVGames", "nationalTVWins", "nationalTVCovers", "nationalTVWinPct", "nationalTVATSPct", "nonNationalGames", "nonNationalCovers", "nonNationalATSPct", "nationalVsNonATSDiff", "publicBiasScore", "broadcastImpact") SELECT league, season, team, nat_games, nat_wins, nat_covers, ROUND((nat_wins::numeric / NULLIF(nat_games, 0))::numeric, 3), ROUND((nat_covers::numeric / NULLIF(nat_games, 0))::numeric, 3), non_nat_games, non_nat_covers, ROUND((non_nat_covers::numeric / NULLIF(non_nat_games, 0))::numeric, 3), -- Differential ROUND(( (nat_covers::numeric / NULLIF(nat_games, 0)) - (non_nat_covers::numeric / NULLIF(non_nat_games, 0)) )::numeric, 3), -- Public bias score (negative = underperforms on TV) CASE WHEN nat_games >= 5 THEN ROUND(((nat_covers::numeric / NULLIF(nat_games, 0)) - (non_nat_covers::numeric / NULLIF(non_nat_games, 0))) * 100)::int ELSE NULL END, CASE WHEN nat_games >= 5 AND (nat_covers::numeric / NULLIF(nat_games, 0)) < (non_nat_covers::numeric / NULLIF(non_nat_games, 0)) - 0.1 THEN 'OVERRATED_ON_TV' WHEN nat_games >= 5 AND (nat_covers::numeric / NULLIF(nat_games, 0)) > (non_nat_covers::numeric / NULLIF(non_nat_games, 0)) + 0.1 THEN 'UNDERRATED_ON_TV' ELSE 'NEUTRAL' END FROM aggregated ON CONFLICT (league, team, season) DO UPDATE SET "nationalTVGames" = EXCLUDED."nationalTVGames", "nationalTVWins" = EXCLUDED."nationalTVWins", "nationalTVCovers" = EXCLUDED."nationalTVCovers", "nationalTVWinPct" = EXCLUDED."nationalTVWinPct", "nationalTVATSPct" = EXCLUDED."nationalTVATSPct", "nonNationalGames" = EXCLUDED."nonNationalGames", "nonNationalCovers" = EXCLUDED."nonNationalCovers", "nonNationalATSPct" = EXCLUDED."nonNationalATSPct", "nationalVsNonATSDiff" = EXCLUDED."nationalVsNonATSDiff", "publicBiasScore" = EXCLUDED."publicBiasScore", "broadcastImpact" = EXCLUDED."broadcastImpact", "updatedAt" = NOW() ''') nba_count = cur.rowcount conn.commit() print(f" NBA: {nba_count} team broadcast records") # NFL broadcast analysis with primetime print("\nComputing NFL broadcast ATS splits...") cur.execute(''' WITH game_broadcasts AS ( SELECT sg.league, sg."gameDate", sg."homeTeam", sg."awayTeam", sg."homeScore", sg."awayScore", sg.spread, gb.network, -- National TV check CASE WHEN gb.network IN ('ESPN', 'ABC', 'FOX', 'CBS', 'NBC', 'NFL Network', 'Amazon Prime') THEN true ELSE false END as is_national, -- Primetime check (MNF, SNF, TNF) CASE WHEN gb.network IN ('ESPN', 'ABC') AND EXTRACT(DOW FROM sg."gameDate") = 1 THEN true WHEN gb.network = 'NBC' AND EXTRACT(DOW FROM sg."gameDate") = 0 THEN true WHEN gb.network IN ('NFL Network', 'Amazon Prime') AND EXTRACT(DOW FROM sg."gameDate") = 4 THEN true ELSE false END as is_primetime FROM "SportsGame" sg LEFT JOIN "GameBroadcast" gb ON gb."gameId"::text = sg."externalGameId" WHERE sg.league = 'nfl' AND sg."homeScore" IS NOT NULL AND sg.spread IS NOT NULL ), team_stats AS ( SELECT league, "homeTeam" as team, EXTRACT(YEAR FROM "gameDate")::text as season, is_national, is_primetime, CASE WHEN "homeScore" - "awayScore" > -spread THEN 1 ELSE 0 END as covered FROM game_broadcasts UNION ALL SELECT league, "awayTeam" as team, EXTRACT(YEAR FROM "gameDate")::text as season, is_national, is_primetime, CASE WHEN "awayScore" - "homeScore" > spread THEN 1 ELSE 0 END as covered FROM game_broadcasts ), aggregated AS ( SELECT league, team, season, COUNT(CASE WHEN is_national THEN 1 END) as nat_games, SUM(CASE WHEN is_national THEN covered END) as nat_covers, COUNT(CASE WHEN NOT is_national OR is_national IS NULL THEN 1 END) as non_nat_games, SUM(CASE WHEN NOT is_national OR is_national IS NULL THEN covered END) as non_nat_covers, COUNT(CASE WHEN is_primetime THEN 1 END) as prime_games, SUM(CASE WHEN is_primetime THEN covered END) as prime_covers FROM team_stats GROUP BY league, team, season HAVING COUNT(*) >= 3 ) INSERT INTO "BroadcastATSSplits" (league, season, team, "nationalTVGames", "nationalTVCovers", "nationalTVATSPct", "nonNationalGames", "nonNationalCovers", "nonNationalATSPct", "primetimeGames", "primetimeCovers", "primetimeATSPct", "nationalVsNonATSDiff", "publicBiasScore", "broadcastImpact") SELECT league, season, team, nat_games, nat_covers, ROUND((nat_covers::numeric / NULLIF(nat_games, 0))::numeric, 3), non_nat_games, non_nat_covers, ROUND((non_nat_covers::numeric / NULLIF(non_nat_games, 0))::numeric, 3), prime_games, prime_covers, ROUND((prime_covers::numeric / NULLIF(prime_games, 0))::numeric, 3), ROUND(( (nat_covers::numeric / NULLIF(nat_games, 0)) - (non_nat_covers::numeric / NULLIF(non_nat_games, 0)) )::numeric, 3), CASE WHEN nat_games >= 3 THEN ROUND(((nat_covers::numeric / NULLIF(nat_games, 0)) - (non_nat_covers::numeric / NULLIF(non_nat_games, 0))) * 100)::int ELSE NULL END, CASE WHEN nat_games >= 3 AND (nat_covers::numeric / NULLIF(nat_games, 0)) < (non_nat_covers::numeric / NULLIF(non_nat_games, 0)) - 0.15 THEN 'OVERRATED_ON_TV' WHEN nat_games >= 3 AND (nat_covers::numeric / NULLIF(nat_games, 0)) > (non_nat_covers::numeric / NULLIF(non_nat_games, 0)) + 0.15 THEN 'UNDERRATED_ON_TV' ELSE 'NEUTRAL' END FROM aggregated ON CONFLICT (league, team, season) DO UPDATE SET "nationalTVGames" = EXCLUDED."nationalTVGames", "nationalTVCovers" = EXCLUDED."nationalTVCovers", "nationalTVATSPct" = EXCLUDED."nationalTVATSPct", "nonNationalGames" = EXCLUDED."nonNationalGames", "nonNationalCovers" = EXCLUDED."nonNationalCovers", "nonNationalATSPct" = EXCLUDED."nonNationalATSPct", "primetimeGames" = EXCLUDED."primetimeGames", "primetimeCovers" = EXCLUDED."primetimeCovers", "primetimeATSPct" = EXCLUDED."primetimeATSPct", "nationalVsNonATSDiff" = EXCLUDED."nationalVsNonATSDiff", "publicBiasScore" = EXCLUDED."publicBiasScore", "broadcastImpact" = EXCLUDED."broadcastImpact", "updatedAt" = NOW() ''') nfl_count = cur.rowcount conn.commit() print(f" NFL: {nfl_count} team broadcast records") # Summary cur.execute(''' SELECT league, "broadcastImpact", COUNT(*), ROUND(AVG("publicBiasScore")::numeric, 0) FROM "BroadcastATSSplits" WHERE "broadcastImpact" IS NOT NULL GROUP BY league, "broadcastImpact" ORDER BY league, COUNT(*) DESC ''') print("\nBroadcast Impact 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 (avg bias: {row[3]:+})") cur.execute('SELECT COUNT(*) FROM "BroadcastATSSplits"') total = cur.fetchone()[0] cur.close() conn.close() print(f"\n✅ Broadcast ATS splits complete: {total} records") if __name__ == '__main__': compute_broadcast_ats()