#!/usr/bin/env python3 """ Create Meta/Self-Awareness Tables (Tier 10) - PredictionHistory: Track all predictions and outcomes - LineMovement: Track line movements for market behavior - ConfidenceCalibration: Track confidence vs actual accuracy """ 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 '' conn = psycopg2.connect(load_db_url()) cur = conn.cursor() print("=" * 80) print("CREATING META/SELF-AWARENESS TABLES (Tier 10)") print(f"Time: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}") print("=" * 80) # 1. PredictionHistory - Track all predictions print("\n1. Creating PredictionHistory table...") cur.execute(''' CREATE TABLE IF NOT EXISTS "PredictionHistory" ( id SERIAL PRIMARY KEY, "predictionType" VARCHAR(50) NOT NULL, league VARCHAR(20) NOT NULL, team VARCHAR(100), player VARCHAR(200), "gameExternalId" VARCHAR(100), "gameDate" DATE, "predictedOutcome" VARCHAR(200), "predictedValue" NUMERIC(10,2), "confidenceScore" INTEGER, "confidenceTier" VARCHAR(20), "confidenceFlags" JSONB, "dataQualityScore" NUMERIC(5,2), "modelConsensusScore" NUMERIC(5,2), "historicalAccuracyScore" NUMERIC(5,2), "actualOutcome" VARCHAR(200), "actualValue" NUMERIC(10,2), "wasCorrect" BOOLEAN, "profitLoss" NUMERIC(10,2), "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), "resolvedAt" TIMESTAMP WITH TIME ZONE, "notes" TEXT ) ''') conn.commit() print(" PredictionHistory table created") # Create indexes cur.execute(''' CREATE INDEX IF NOT EXISTS idx_prediction_history_league ON "PredictionHistory" (league, "createdAt" DESC) ''') cur.execute(''' CREATE INDEX IF NOT EXISTS idx_prediction_history_tier ON "PredictionHistory" ("confidenceTier", "wasCorrect") ''') cur.execute(''' CREATE INDEX IF NOT EXISTS idx_prediction_history_type ON "PredictionHistory" ("predictionType", league) ''') conn.commit() print(" Indexes created") # 2. LineMovement - Track line movements (Tier 7 Market Behavior) print("\n2. Creating LineMovement table...") cur.execute(''' CREATE TABLE IF NOT EXISTS "LineMovement" ( id SERIAL PRIMARY KEY, "gameExternalId" VARCHAR(100) NOT NULL, league VARCHAR(20) NOT NULL, "gameDate" DATE, "homeTeam" VARCHAR(100), "awayTeam" VARCHAR(100), "marketType" VARCHAR(50) NOT NULL, "openLine" NUMERIC(10,2), "currentLine" NUMERIC(10,2), "closingLine" NUMERIC(10,2), "lineMovement" NUMERIC(10,2), "movementDirection" VARCHAR(20), "openOdds" INTEGER, "currentOdds" INTEGER, "closingOdds" INTEGER, "publicPctHome" NUMERIC(5,2), "publicPctAway" NUMERIC(5,2), "sharpAction" VARCHAR(50), "steamMove" BOOLEAN DEFAULT FALSE, "reverseLineMove" BOOLEAN DEFAULT FALSE, "consensusPlay" VARCHAR(50), "recordedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), "gameStartTime" TIMESTAMP WITH TIME ZONE, "finalScore" VARCHAR(20), "coverResult" VARCHAR(20), UNIQUE("gameExternalId", "marketType", "recordedAt") ) ''') conn.commit() print(" LineMovement table created") cur.execute(''' CREATE INDEX IF NOT EXISTS idx_line_movement_game ON "LineMovement" ("gameExternalId", "marketType") ''') cur.execute(''' CREATE INDEX IF NOT EXISTS idx_line_movement_steam ON "LineMovement" ("steamMove", "reverseLineMove") WHERE "steamMove" OR "reverseLineMove" ''') conn.commit() print(" Indexes created") # 3. ConfidenceCalibration - Track confidence vs actual accuracy print("\n3. Creating ConfidenceCalibration table...") cur.execute(''' CREATE TABLE IF NOT EXISTS "ConfidenceCalibration" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, "predictionType" VARCHAR(50) NOT NULL, "confidenceBucket" INTEGER NOT NULL, "totalPredictions" INTEGER DEFAULT 0, "correctPredictions" INTEGER DEFAULT 0, "actualAccuracy" NUMERIC(5,4), "expectedAccuracy" NUMERIC(5,4), "calibrationError" NUMERIC(5,4), "brierScore" NUMERIC(5,4), "periodStart" DATE, "periodEnd" DATE, "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, "predictionType", "confidenceBucket", "periodStart") ) ''') conn.commit() print(" ConfidenceCalibration table created") # 4. LiveGameState - Track live game momentum (Tier 9) print("\n4. Creating LiveGameState table...") cur.execute(''' CREATE TABLE IF NOT EXISTS "LiveGameState" ( id SERIAL PRIMARY KEY, "gameExternalId" VARCHAR(100) NOT NULL, league VARCHAR(20) NOT NULL, "gameDate" DATE, "period" INTEGER, "timeRemaining" VARCHAR(20), "homeScore" INTEGER, "awayScore" INTEGER, "scoreDifferential" INTEGER, "pace" NUMERIC(5,2), "momentum" VARCHAR(50), "momentumScore" NUMERIC(5,2), "last5MinScoring" JSONB, "foulTrouble" JSONB, "timeoutsRemaining" JSONB, "rotationStatus" JSONB, "liveSpread" NUMERIC(5,2), "liveTotal" NUMERIC(5,2), "liveMoneyline" JSONB, "recordedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE("gameExternalId", "recordedAt") ) ''') conn.commit() print(" LiveGameState table created") # 5. RivalryContext - Track rivalries and motivation (Tier 8) print("\n5. Creating RivalryContext table...") cur.execute(''' CREATE TABLE IF NOT EXISTS "RivalryContext" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, "team1" VARCHAR(100) NOT NULL, "team2" VARCHAR(100) NOT NULL, "rivalryName" VARCHAR(200), "rivalryIntensity" INTEGER, "historicalGames" INTEGER DEFAULT 0, "team1Wins" INTEGER DEFAULT 0, "team2Wins" INTEGER DEFAULT 0, "avgPointDiff" NUMERIC(5,2), "avgTotalPoints" NUMERIC(5,2), "overtimeGames" INTEGER DEFAULT 0, "lastMeeting" DATE, "lastWinner" VARCHAR(100), "currentStreak" INTEGER, "streakHolder" VARCHAR(100), "isConferenceRivalry" BOOLEAN DEFAULT FALSE, "isDivisionRivalry" BOOLEAN DEFAULT FALSE, "notes" TEXT, "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, "team1", "team2") ) ''') conn.commit() print(" RivalryContext table created") # 6. StandingsSnapshot - Track standings for motivation context print("\n6. Creating StandingsSnapshot table...") cur.execute(''' CREATE TABLE IF NOT EXISTS "StandingsSnapshot" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, season VARCHAR(10) NOT NULL, team VARCHAR(100) NOT NULL, conference VARCHAR(50), division VARCHAR(50), "snapshotDate" DATE NOT NULL, wins INTEGER DEFAULT 0, losses INTEGER DEFAULT 0, ties INTEGER DEFAULT 0, "winPct" NUMERIC(5,4), "gamesBack" NUMERIC(5,1), "conferenceRank" INTEGER, "divisionRank" INTEGER, "playoffPosition" INTEGER, "playoffProbability" NUMERIC(5,4), "eliminationNumber" INTEGER, "clinchNumber" INTEGER, "remainingScheduleStrength" NUMERIC(5,3), "last10" VARCHAR(20), streak VARCHAR(20), "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, season, team, "snapshotDate") ) ''') conn.commit() print(" StandingsSnapshot table created") cur.execute(''' CREATE INDEX IF NOT EXISTS idx_standings_snapshot_team ON "StandingsSnapshot" (league, team, "snapshotDate" DESC) ''') conn.commit() # Summary print("\n" + "=" * 80) print("META TABLES CREATED SUCCESSFULLY") print("=" * 80) tables_created = [ ("PredictionHistory", "Track predictions and outcomes for self-assessment"), ("LineMovement", "Track line movements for market behavior analysis"), ("ConfidenceCalibration", "Calibrate confidence scores vs actual accuracy"), ("LiveGameState", "Track live game momentum and pace"), ("RivalryContext", "Track team rivalries and historical matchups"), ("StandingsSnapshot", "Track standings for motivation context"), ] for table, desc in tables_created: cur.execute(f'SELECT COUNT(*) FROM "{table}"') count = cur.fetchone()[0] print(f" {table}: {count} records - {desc}") cur.close() conn.close() print("\n✅ All meta tables ready for Tier 7-10 improvements")