#!/usr/bin/env python3 """ Compute Weather ATS Splits Team ATS by weather conditions (cold, wind, rain/snow) Answers: "Chiefs overpriced in cold-weather road games?" Supports: NFL, MLB """ 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_weather_ats(): conn = psycopg2.connect(load_db_url()) cur = conn.cursor() # Create WeatherATSSplits table cur.execute(''' CREATE TABLE IF NOT EXISTS "WeatherATSSplits" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, season VARCHAR(20), team VARCHAR(100) NOT NULL, -- Cold weather (< 40F) "coldWeatherGames" INTEGER DEFAULT 0, "coldWeatherCovers" INTEGER DEFAULT 0, "coldWeatherATSPct" NUMERIC, -- Warm weather (> 70F) "warmWeatherGames" INTEGER DEFAULT 0, "warmWeatherCovers" INTEGER DEFAULT 0, "warmWeatherATSPct" NUMERIC, -- Wind (> 15mph) "windyGames" INTEGER DEFAULT 0, "windyCovers" INTEGER DEFAULT 0, "windyATSPct" NUMERIC, -- Precipitation "precipGames" INTEGER DEFAULT 0, "precipCovers" INTEGER DEFAULT 0, "precipATSPct" NUMERIC, -- Dome/indoor "indoorGames" INTEGER DEFAULT 0, "indoorCovers" INTEGER DEFAULT 0, "indoorATSPct" NUMERIC, -- Home vs away in bad weather "badWeatherHomeATS" NUMERIC, "badWeatherAwayATS" NUMERIC, -- Classification "weatherImpact" VARCHAR(30), "coldWeatherTeam" BOOLEAN DEFAULT false, "domeTeam" BOOLEAN DEFAULT false, "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, team, season) ) ''') conn.commit() print("WeatherATSSplits table created/verified") # NFL weather analysis (using win rates - spread not available in SportsGame) print("\nComputing NFL weather performance splits...") cur.execute(''' WITH game_weather AS ( SELECT sg.league, sg."gameDate", sg."homeTeam", sg."awayTeam", sg."homeScore", sg."awayScore", EXTRACT(YEAR FROM sg."gameDate")::text as season, gw.temperature, gw."windSpeed", gw.condition, -- Classify weather CASE WHEN gw.temperature < 40 THEN true ELSE false END as is_cold, CASE WHEN gw.temperature > 70 THEN true ELSE false END as is_warm, CASE WHEN gw."windSpeed" > 15 THEN true ELSE false END as is_windy, CASE WHEN gw.condition ILIKE '%rain%' OR gw.condition ILIKE '%snow%' THEN true ELSE false END as is_precip, CASE WHEN gw."isIndoor" = true OR gw.condition ILIKE '%dome%' OR gw.condition ILIKE '%indoor%' OR gw.temperature IS NULL THEN true ELSE false END as is_indoor FROM "SportsGame" sg LEFT JOIN "GameWeather" gw ON gw.league = sg.league AND gw."gameId"::text = sg."externalGameId" WHERE sg.league = 'nfl' AND sg."homeScore" IS NOT NULL ), team_weather_stats AS ( -- Home team SELECT league, "homeTeam" as team, season, is_cold, is_warm, is_windy, is_precip, is_indoor, true as is_home, CASE WHEN "homeScore" > "awayScore" THEN 1 ELSE 0 END as won FROM game_weather UNION ALL -- Away team SELECT league, "awayTeam" as team, season, is_cold, is_warm, is_windy, is_precip, is_indoor, false as is_home, CASE WHEN "awayScore" > "homeScore" THEN 1 ELSE 0 END as won FROM game_weather ), aggregated AS ( SELECT league, team, season, -- Cold COUNT(CASE WHEN is_cold THEN 1 END) as cold_games, SUM(CASE WHEN is_cold THEN won END) as cold_wins, -- Warm COUNT(CASE WHEN is_warm THEN 1 END) as warm_games, SUM(CASE WHEN is_warm THEN won END) as warm_wins, -- Wind COUNT(CASE WHEN is_windy THEN 1 END) as wind_games, SUM(CASE WHEN is_windy THEN won END) as wind_wins, -- Precip COUNT(CASE WHEN is_precip THEN 1 END) as precip_games, SUM(CASE WHEN is_precip THEN won END) as precip_wins, -- Indoor COUNT(CASE WHEN is_indoor THEN 1 END) as indoor_games, SUM(CASE WHEN is_indoor THEN won END) as indoor_wins, -- Bad weather home/away AVG(CASE WHEN (is_cold OR is_windy OR is_precip) AND is_home THEN won END) as bad_home_win_rate, AVG(CASE WHEN (is_cold OR is_windy OR is_precip) AND NOT is_home THEN won END) as bad_away_win_rate FROM team_weather_stats GROUP BY league, team, season HAVING COUNT(*) >= 3 ) INSERT INTO "WeatherATSSplits" (league, season, team, "coldWeatherGames", "coldWeatherCovers", "coldWeatherATSPct", "warmWeatherGames", "warmWeatherCovers", "warmWeatherATSPct", "windyGames", "windyCovers", "windyATSPct", "precipGames", "precipCovers", "precipATSPct", "indoorGames", "indoorCovers", "indoorATSPct", "badWeatherHomeATS", "badWeatherAwayATS", "weatherImpact", "coldWeatherTeam", "domeTeam") SELECT league, season, team, cold_games, cold_wins, ROUND((cold_wins::numeric / NULLIF(cold_games, 0))::numeric, 3), warm_games, warm_wins, ROUND((warm_wins::numeric / NULLIF(warm_games, 0))::numeric, 3), wind_games, wind_wins, ROUND((wind_wins::numeric / NULLIF(wind_games, 0))::numeric, 3), precip_games, precip_wins, ROUND((precip_wins::numeric / NULLIF(precip_games, 0))::numeric, 3), indoor_games, indoor_wins, ROUND((indoor_wins::numeric / NULLIF(indoor_games, 0))::numeric, 3), ROUND(bad_home_win_rate::numeric, 3), ROUND(bad_away_win_rate::numeric, 3), -- Weather impact classification (using win rate) CASE WHEN cold_games >= 2 AND (cold_wins::numeric / NULLIF(cold_games, 0)) > 0.6 THEN 'THRIVES_IN_COLD' WHEN cold_games >= 2 AND (cold_wins::numeric / NULLIF(cold_games, 0)) < 0.35 THEN 'STRUGGLES_IN_COLD' WHEN indoor_games >= 3 AND (indoor_wins::numeric / NULLIF(indoor_games, 0)) > 0.6 THEN 'DOME_TEAM' ELSE 'NEUTRAL' END, cold_games >= 2 AND (cold_wins::numeric / NULLIF(cold_games, 0)) > 0.55, indoor_games > warm_games + cold_games FROM aggregated ON CONFLICT (league, team, season) DO UPDATE SET "coldWeatherGames" = EXCLUDED."coldWeatherGames", "coldWeatherCovers" = EXCLUDED."coldWeatherCovers", "coldWeatherATSPct" = EXCLUDED."coldWeatherATSPct", "warmWeatherGames" = EXCLUDED."warmWeatherGames", "warmWeatherCovers" = EXCLUDED."warmWeatherCovers", "warmWeatherATSPct" = EXCLUDED."warmWeatherATSPct", "windyGames" = EXCLUDED."windyGames", "windyCovers" = EXCLUDED."windyCovers", "windyATSPct" = EXCLUDED."windyATSPct", "precipGames" = EXCLUDED."precipGames", "precipCovers" = EXCLUDED."precipCovers", "precipATSPct" = EXCLUDED."precipATSPct", "indoorGames" = EXCLUDED."indoorGames", "indoorCovers" = EXCLUDED."indoorCovers", "indoorATSPct" = EXCLUDED."indoorATSPct", "badWeatherHomeATS" = EXCLUDED."badWeatherHomeATS", "badWeatherAwayATS" = EXCLUDED."badWeatherAwayATS", "weatherImpact" = EXCLUDED."weatherImpact", "coldWeatherTeam" = EXCLUDED."coldWeatherTeam", "domeTeam" = EXCLUDED."domeTeam", "updatedAt" = NOW() ''') count = cur.rowcount conn.commit() print(f" NFL: {count} team weather records") # Summary cur.execute(''' SELECT "weatherImpact", COUNT(*), ROUND(AVG("coldWeatherATSPct")::numeric, 3) FROM "WeatherATSSplits" WHERE "weatherImpact" IS NOT NULL GROUP BY "weatherImpact" ORDER BY COUNT(*) DESC ''') print("\nWeather Impact Distribution:") for row in cur.fetchall(): cold_ats = f"{row[2]:.1%}" if row[2] else "N/A" print(f" {row[0]}: {row[1]} teams (avg cold ATS: {cold_ats})") cur.execute('SELECT COUNT(*) FROM "WeatherATSSplits"') total = cur.fetchone()[0] cur.close() conn.close() print(f"\n✅ Weather ATS splits complete: {total} records") if __name__ == '__main__': compute_weather_ats()