#!/usr/bin/env python3 """ Create PeriodOdds and PeriodPerformance tables for Q1/H1 analysis. Derives period performance metrics from existing quarter scores. """ import psycopg2 from datetime import datetime DB_URL = "postgresql://eventheodds:eventheodds_dev_password@127.0.0.1:5433/eventheodds_sports" def main(): conn = psycopg2.connect(DB_URL) cur = conn.cursor() # Create table for period-specific odds (for future ingestion) cur.execute(''' CREATE TABLE IF NOT EXISTS "PeriodOdds" ( id BIGSERIAL PRIMARY KEY, league TEXT NOT NULL, "gameId" BIGINT, "gameDate" TIMESTAMP NOT NULL, "homeTeam" TEXT NOT NULL, "awayTeam" TEXT NOT NULL, period TEXT NOT NULL, "spreadHome" DOUBLE PRECISION, "spreadAway" DOUBLE PRECISION, total DOUBLE PRECISION, "moneylineHome" INTEGER, "moneylineAway" INTEGER, "snapshotType" TEXT DEFAULT 'closing', "snapshotAt" TIMESTAMP DEFAULT NOW(), source TEXT, UNIQUE(league, "gameId", period, "snapshotType") ) ''') cur.execute('CREATE INDEX IF NOT EXISTS idx_period_odds_league ON "PeriodOdds"(league)') cur.execute('CREATE INDEX IF NOT EXISTS idx_period_odds_period ON "PeriodOdds"(period)') conn.commit() print("PeriodOdds table created.") # Create period performance analysis table cur.execute(''' CREATE TABLE IF NOT EXISTS "PeriodPerformance" ( id BIGSERIAL PRIMARY KEY, league TEXT NOT NULL, season INTEGER NOT NULL, team TEXT NOT NULL, period TEXT NOT NULL, "gamesPlayed" INTEGER DEFAULT 0, "avgPointsFor" DOUBLE PRECISION, "avgPointsAgainst" DOUBLE PRECISION, "avgMargin" DOUBLE PRECISION, "winPct" DOUBLE PRECISION, "periodPctOfTotal" DOUBLE PRECISION, "overPct" DOUBLE PRECISION, "fastStartPct" DOUBLE PRECISION, "updatedAt" TIMESTAMP DEFAULT NOW(), UNIQUE(league, season, team, period) ) ''') conn.commit() print("PeriodPerformance table created.") # Compute Q1 performance for NBA (we have Q1 data) print("Computing NBA Q1 performance...") cur.execute(''' INSERT INTO "PeriodPerformance" (league, season, team, period, "gamesPlayed", "avgPointsFor", "avgPointsAgainst", "avgMargin", "winPct", "periodPctOfTotal", "fastStartPct") SELECT 'nba' as league, season, team, 'Q1' as period, COUNT(*) as games, ROUND(AVG(q1_for)::numeric, 1) as avg_pf, ROUND(AVG(q1_against)::numeric, 1) as avg_pa, ROUND(AVG(q1_for - q1_against)::numeric, 1) as avg_margin, ROUND(AVG(CASE WHEN q1_for > q1_against THEN 1 ELSE 0 END)::numeric, 3) as win_pct, ROUND(AVG(q1_for::float / NULLIF(total_for, 0) * 100)::numeric, 1) as period_pct, ROUND(AVG(CASE WHEN q1_for > q1_against THEN 1 ELSE 0 END)::numeric, 3) as fast_start FROM ( SELECT season, "homeTeam" as team, "homeQ1" as q1_for, "awayQ1" as q1_against, "homeScore" as total_for FROM "SportsGame" WHERE league = 'nba' AND "homeQ1" IS NOT NULL AND "homeScore" > 0 UNION ALL SELECT season, "awayTeam" as team, "awayQ1" as q1_for, "homeQ1" as q1_against, "awayScore" as total_for FROM "SportsGame" WHERE league = 'nba' AND "awayQ1" IS NOT NULL AND "awayScore" > 0 ) t GROUP BY season, team ON CONFLICT (league, season, team, period) DO UPDATE SET "gamesPlayed" = EXCLUDED."gamesPlayed", "avgPointsFor" = EXCLUDED."avgPointsFor", "avgPointsAgainst" = EXCLUDED."avgPointsAgainst", "avgMargin" = EXCLUDED."avgMargin", "winPct" = EXCLUDED."winPct", "periodPctOfTotal" = EXCLUDED."periodPctOfTotal", "fastStartPct" = EXCLUDED."fastStartPct", "updatedAt" = NOW() ''') # Compute NHL P1 performance print("Computing NHL P1 performance...") cur.execute(''' INSERT INTO "PeriodPerformance" (league, season, team, period, "gamesPlayed", "avgPointsFor", "avgPointsAgainst", "avgMargin", "winPct", "periodPctOfTotal", "fastStartPct") SELECT 'nhl' as league, season, team, 'P1' as period, COUNT(*) as games, ROUND(AVG(p1_for)::numeric, 2) as avg_pf, ROUND(AVG(p1_against)::numeric, 2) as avg_pa, ROUND(AVG(p1_for - p1_against)::numeric, 2) as avg_margin, ROUND(AVG(CASE WHEN p1_for > p1_against THEN 1 ELSE 0 END)::numeric, 3) as win_pct, ROUND(AVG(p1_for::float / NULLIF(total_for, 0) * 100)::numeric, 1) as period_pct, ROUND(AVG(CASE WHEN p1_for > p1_against THEN 1 ELSE 0 END)::numeric, 3) as fast_start FROM ( SELECT season, "homeTeam" as team, "homeQ1" as p1_for, "awayQ1" as p1_against, "homeScore" as total_for FROM "SportsGame" WHERE league = 'nhl' AND "homeQ1" IS NOT NULL AND "homeScore" > 0 UNION ALL SELECT season, "awayTeam" as team, "awayQ1" as p1_for, "homeQ1" as p1_against, "awayScore" as total_for FROM "SportsGame" WHERE league = 'nhl' AND "awayQ1" IS NOT NULL AND "awayScore" > 0 ) t GROUP BY season, team ON CONFLICT (league, season, team, period) DO UPDATE SET "gamesPlayed" = EXCLUDED."gamesPlayed", "avgPointsFor" = EXCLUDED."avgPointsFor", "avgPointsAgainst" = EXCLUDED."avgPointsAgainst", "avgMargin" = EXCLUDED."avgMargin", "winPct" = EXCLUDED."winPct", "periodPctOfTotal" = EXCLUDED."periodPctOfTotal", "fastStartPct" = EXCLUDED."fastStartPct", "updatedAt" = NOW() ''') # Compute NFL Q1 performance print("Computing NFL Q1 performance...") cur.execute(''' INSERT INTO "PeriodPerformance" (league, season, team, period, "gamesPlayed", "avgPointsFor", "avgPointsAgainst", "avgMargin", "winPct", "periodPctOfTotal", "fastStartPct") SELECT 'nfl' as league, season, team, 'Q1' as period, COUNT(*) as games, ROUND(AVG(q1_for)::numeric, 1) as avg_pf, ROUND(AVG(q1_against)::numeric, 1) as avg_pa, ROUND(AVG(q1_for - q1_against)::numeric, 1) as avg_margin, ROUND(AVG(CASE WHEN q1_for > q1_against THEN 1 ELSE 0 END)::numeric, 3) as win_pct, ROUND(AVG(q1_for::float / NULLIF(total_for, 0) * 100)::numeric, 1) as period_pct, ROUND(AVG(CASE WHEN q1_for > q1_against THEN 1 ELSE 0 END)::numeric, 3) as fast_start FROM ( SELECT season, "homeTeam" as team, "homeQ1" as q1_for, "awayQ1" as q1_against, "homeScore" as total_for FROM "SportsGame" WHERE league = 'nfl' AND "homeQ1" IS NOT NULL AND "homeScore" > 0 UNION ALL SELECT season, "awayTeam" as team, "awayQ1" as q1_for, "homeQ1" as q1_against, "awayScore" as total_for FROM "SportsGame" WHERE league = 'nfl' AND "awayQ1" IS NOT NULL AND "awayScore" > 0 ) t GROUP BY season, team ON CONFLICT (league, season, team, period) DO UPDATE SET "gamesPlayed" = EXCLUDED."gamesPlayed", "avgPointsFor" = EXCLUDED."avgPointsFor", "avgPointsAgainst" = EXCLUDED."avgPointsAgainst", "avgMargin" = EXCLUDED."avgMargin", "winPct" = EXCLUDED."winPct", "periodPctOfTotal" = EXCLUDED."periodPctOfTotal", "fastStartPct" = EXCLUDED."fastStartPct", "updatedAt" = NOW() ''') conn.commit() cur.execute('SELECT COUNT(*) FROM "PeriodPerformance"') count = cur.fetchone()[0] print(f"PeriodPerformance: {count} team-period records created.") # Show sample data cur.execute(''' SELECT league, team, period, "avgPointsFor", "avgMargin", "fastStartPct" FROM "PeriodPerformance" WHERE "gamesPlayed" >= 10 ORDER BY "fastStartPct" DESC LIMIT 5 ''') print("\nTop fast-starting teams:") for row in cur.fetchall(): print(f" {row[0]} {row[1]} {row[2]}: {row[3]} ppg, {row[4]} margin, {row[5]:.1%} fast start") cur.close() conn.close() if __name__ == "__main__": main()