#!/usr/bin/env python3 """ Compute Broadcast Performance Splits Team win rates by broadcast type (national TV, primetime, regional) Answers: "Lakers overperform 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_performance(): conn = psycopg2.connect(load_db_url()) cur = conn.cursor() # Create BroadcastPerformance table cur.execute(''' CREATE TABLE IF NOT EXISTS "BroadcastPerformance" ( 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, "nationalTVWinPct" NUMERIC, "nationalTVAvgMargin" NUMERIC, -- Non-national splits "nonNationalGames" INTEGER DEFAULT 0, "nonNationalWins" INTEGER DEFAULT 0, "nonNationalWinPct" NUMERIC, "nonNationalAvgMargin" NUMERIC, -- Primetime specific (NFL: MNF, SNF, TNF) "primetimeGames" INTEGER DEFAULT 0, "primetimeWins" INTEGER DEFAULT 0, "primetimeWinPct" NUMERIC, -- Differential "nationalVsNonWinDiff" NUMERIC, "publicBiasScore" INTEGER, "broadcastImpact" VARCHAR(30), "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, team, season) ) ''') conn.commit() print("BroadcastPerformance table created/verified") # NBA broadcast analysis print("\nComputing NBA broadcast performance...") cur.execute(''' WITH game_broadcasts AS ( SELECT sg.league, sg."gameDate", sg."homeTeam", sg."awayTeam", sg."homeScore", sg."awayScore", gb.network, gb."isNationalTV", 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" WHERE sg.league = 'nba' AND sg."homeScore" IS NOT NULL ), team_broadcast_stats AS ( -- Home team 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, "homeScore" - "awayScore" as margin FROM game_broadcasts UNION ALL -- Away team 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, "awayScore" - "homeScore" as margin 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 won END) as nat_wins, AVG(CASE WHEN is_national THEN margin END) as nat_margin, 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 won END) as non_nat_wins, AVG(CASE WHEN NOT is_national OR is_national IS NULL THEN margin END) as non_nat_margin FROM team_broadcast_stats GROUP BY league, team, season HAVING COUNT(*) >= 5 ) INSERT INTO "BroadcastPerformance" (league, season, team, "nationalTVGames", "nationalTVWins", "nationalTVWinPct", "nationalTVAvgMargin", "nonNationalGames", "nonNationalWins", "nonNationalWinPct", "nonNationalAvgMargin", "nationalVsNonWinDiff", "publicBiasScore", "broadcastImpact") SELECT league, season, team, nat_games, nat_wins, ROUND((nat_wins::numeric / NULLIF(nat_games, 0))::numeric, 3), ROUND(nat_margin::numeric, 1), non_nat_games, non_nat_wins, ROUND((non_nat_wins::numeric / NULLIF(non_nat_games, 0))::numeric, 3), ROUND(non_nat_margin::numeric, 1), ROUND(( (nat_wins::numeric / NULLIF(nat_games, 0)) - (non_nat_wins::numeric / NULLIF(non_nat_games, 0)) )::numeric, 3), CASE WHEN nat_games >= 3 THEN ROUND(((nat_wins::numeric / NULLIF(nat_games, 0)) - (non_nat_wins::numeric / NULLIF(non_nat_games, 0))) * 100)::int ELSE NULL END, CASE WHEN nat_games >= 3 AND (nat_wins::numeric / NULLIF(nat_games, 0)) < (non_nat_wins::numeric / NULLIF(non_nat_games, 0)) - 0.1 THEN 'WORSE_ON_TV' WHEN nat_games >= 3 AND (nat_wins::numeric / NULLIF(nat_games, 0)) > (non_nat_wins::numeric / NULLIF(non_nat_games, 0)) + 0.1 THEN 'BETTER_ON_TV' ELSE 'NEUTRAL' END FROM aggregated ON CONFLICT (league, team, season) DO UPDATE SET "nationalTVGames" = EXCLUDED."nationalTVGames", "nationalTVWins" = EXCLUDED."nationalTVWins", "nationalTVWinPct" = EXCLUDED."nationalTVWinPct", "nationalTVAvgMargin" = EXCLUDED."nationalTVAvgMargin", "nonNationalGames" = EXCLUDED."nonNationalGames", "nonNationalWins" = EXCLUDED."nonNationalWins", "nonNationalWinPct" = EXCLUDED."nonNationalWinPct", "nonNationalAvgMargin" = EXCLUDED."nonNationalAvgMargin", "nationalVsNonWinDiff" = EXCLUDED."nationalVsNonWinDiff", "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 print("\nComputing NFL broadcast performance...") cur.execute(''' WITH game_broadcasts AS ( SELECT sg.league, sg."gameDate", sg."homeTeam", sg."awayTeam", sg."homeScore", sg."awayScore", gb.network, CASE WHEN gb.network IN ('ESPN', 'ABC', 'FOX', 'CBS', 'NBC', 'NFL Network', 'Amazon Prime') THEN true ELSE false END as is_national, 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 ), team_stats AS ( SELECT league, "homeTeam" as team, EXTRACT(YEAR FROM "gameDate")::text as season, is_national, is_primetime, CASE WHEN "homeScore" > "awayScore" THEN 1 ELSE 0 END as won 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" THEN 1 ELSE 0 END as won 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 won END) as nat_wins, 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 won END) as non_nat_wins, COUNT(CASE WHEN is_primetime THEN 1 END) as prime_games, SUM(CASE WHEN is_primetime THEN won END) as prime_wins FROM team_stats GROUP BY league, team, season HAVING COUNT(*) >= 3 ) INSERT INTO "BroadcastPerformance" (league, season, team, "nationalTVGames", "nationalTVWins", "nationalTVWinPct", "nonNationalGames", "nonNationalWins", "nonNationalWinPct", "primetimeGames", "primetimeWins", "primetimeWinPct", "nationalVsNonWinDiff", "broadcastImpact") SELECT league, season, team, nat_games, nat_wins, ROUND((nat_wins::numeric / NULLIF(nat_games, 0))::numeric, 3), non_nat_games, non_nat_wins, ROUND((non_nat_wins::numeric / NULLIF(non_nat_games, 0))::numeric, 3), prime_games, prime_wins, ROUND((prime_wins::numeric / NULLIF(prime_games, 0))::numeric, 3), ROUND(( (nat_wins::numeric / NULLIF(nat_games, 0)) - (non_nat_wins::numeric / NULLIF(non_nat_games, 0)) )::numeric, 3), CASE WHEN nat_games >= 2 AND (nat_wins::numeric / NULLIF(nat_games, 0)) < (non_nat_wins::numeric / NULLIF(non_nat_games, 0)) - 0.15 THEN 'WORSE_ON_TV' WHEN nat_games >= 2 AND (nat_wins::numeric / NULLIF(nat_games, 0)) > (non_nat_wins::numeric / NULLIF(non_nat_games, 0)) + 0.15 THEN 'BETTER_ON_TV' ELSE 'NEUTRAL' END FROM aggregated ON CONFLICT (league, team, season) DO UPDATE SET "nationalTVGames" = EXCLUDED."nationalTVGames", "nationalTVWins" = EXCLUDED."nationalTVWins", "nationalTVWinPct" = EXCLUDED."nationalTVWinPct", "nonNationalGames" = EXCLUDED."nonNationalGames", "nonNationalWins" = EXCLUDED."nonNationalWins", "nonNationalWinPct" = EXCLUDED."nonNationalWinPct", "primetimeGames" = EXCLUDED."primetimeGames", "primetimeWins" = EXCLUDED."primetimeWins", "primetimeWinPct" = EXCLUDED."primetimeWinPct", "nationalVsNonWinDiff" = EXCLUDED."nationalVsNonWinDiff", "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("nationalVsNonWinDiff")::numeric, 3) FROM "BroadcastPerformance" 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()}:") diff = row[3] if row[3] else 0 print(f" {row[1]}: {row[2]} teams (avg diff: {diff:+.3f})") cur.execute('SELECT COUNT(*) FROM "BroadcastPerformance"') total = cur.fetchone()[0] cur.close() conn.close() print(f"\n✅ Broadcast performance complete: {total} records") if __name__ == '__main__': compute_broadcast_performance()