#!/usr/bin/env python3
"""
Fix Live Odds Game Status
Populates gameStatus field in LiveOdds based on game times
Enables Q8, Q12: Halftime/quarter analysis
"""
import psycopg2
from datetime import datetime, timezone

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 fix_game_status(cur, conn):
    """Populate gameStatus based on gameTime patterns"""
    print("\nFixing live odds game status...")

    # Update gameStatus based on gameTime field
    cur.execute('''
        UPDATE "LiveOdds"
        SET "gameStatus" = CASE
            -- NBA periods (12 min quarters)
            WHEN league = 'nba' THEN
                CASE
                    WHEN "gameTime" ILIKE '%1st%' OR "gameTime" ILIKE 'Q1%' THEN 'Q1'
                    WHEN "gameTime" ILIKE '%2nd%' OR "gameTime" ILIKE 'Q2%' THEN 'Q2'
                    WHEN "gameTime" ILIKE '%half%' THEN 'HALFTIME'
                    WHEN "gameTime" ILIKE '%3rd%' OR "gameTime" ILIKE 'Q3%' THEN 'Q3'
                    WHEN "gameTime" ILIKE '%4th%' OR "gameTime" ILIKE 'Q4%' THEN 'Q4'
                    WHEN "gameTime" ILIKE '%OT%' THEN 'OT'
                    WHEN "gameTime" ILIKE '%final%' THEN 'FINAL'
                    ELSE 'IN_PROGRESS'
                END
            -- NFL periods (15 min quarters)
            WHEN league = 'nfl' THEN
                CASE
                    WHEN "gameTime" ILIKE '%1st%' OR "gameTime" ILIKE 'Q1%' THEN 'Q1'
                    WHEN "gameTime" ILIKE '%2nd%' OR "gameTime" ILIKE 'Q2%' THEN 'Q2'
                    WHEN "gameTime" ILIKE '%half%' THEN 'HALFTIME'
                    WHEN "gameTime" ILIKE '%3rd%' OR "gameTime" ILIKE 'Q3%' THEN 'Q3'
                    WHEN "gameTime" ILIKE '%4th%' OR "gameTime" ILIKE 'Q4%' THEN 'Q4'
                    WHEN "gameTime" ILIKE '%OT%' THEN 'OT'
                    WHEN "gameTime" ILIKE '%final%' THEN 'FINAL'
                    ELSE 'IN_PROGRESS'
                END
            -- NHL periods (20 min periods)
            WHEN league = 'nhl' THEN
                CASE
                    WHEN "gameTime" ILIKE '%1st%' OR "gameTime" ILIKE 'P1%' THEN 'P1'
                    WHEN "gameTime" ILIKE '%2nd%' OR "gameTime" ILIKE 'P2%' THEN 'P2'
                    WHEN "gameTime" ILIKE '%intermission%' THEN 'INTERMISSION'
                    WHEN "gameTime" ILIKE '%3rd%' OR "gameTime" ILIKE 'P3%' THEN 'P3'
                    WHEN "gameTime" ILIKE '%OT%' THEN 'OT'
                    WHEN "gameTime" ILIKE '%final%' THEN 'FINAL'
                    ELSE 'IN_PROGRESS'
                END
            ELSE 'UNKNOWN'
        END
        WHERE "gameStatus" IS NULL
    ''')

    updated = cur.rowcount
    conn.commit()
    return updated

def create_live_odds_periods(cur, conn):
    """Create aggregated live odds by game period"""
    print("\nCreating live odds period aggregates...")

    cur.execute('''
        CREATE TABLE IF NOT EXISTS "LiveOddsByPeriod" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(20),
            "gameId" TEXT,
            "gameDate" DATE,
            "homeTeam" TEXT,
            "awayTeam" TEXT,
            period VARCHAR(20),
            "avgLiveSpread" FLOAT,
            "avgLiveTotal" FLOAT,
            "spreadMovement" FLOAT,
            "totalMovement" FLOAT,
            "snapshotCount" INT,
            "homeScoreAtPeriod" INT,
            "awayScoreAtPeriod" INT,
            "createdAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, "gameId", period)
        )
    ''')
    conn.commit()

    # Aggregate live odds by period
    cur.execute('''
        INSERT INTO "LiveOddsByPeriod" (
            league, "gameId", "gameDate", "homeTeam", "awayTeam",
            period, "avgLiveSpread", "avgLiveTotal",
            "snapshotCount", "homeScoreAtPeriod", "awayScoreAtPeriod"
        )
        SELECT
            league,
            "gameId",
            "gameDate"::date,
            "homeTeam",
            "awayTeam",
            COALESCE("gameStatus", 'UNKNOWN') as period,
            AVG("liveSpreadHome") as avg_spread,
            AVG("liveTotal") as avg_total,
            COUNT(*) as snapshots,
            MAX("homeScoreLive") as home_score,
            MAX("awayScoreLive") as away_score
        FROM "LiveOdds"
        WHERE "liveSpreadHome" IS NOT NULL OR "liveTotal" IS NOT NULL
        GROUP BY league, "gameId", "gameDate", "homeTeam", "awayTeam", "gameStatus"
        ON CONFLICT (league, "gameId", period) DO UPDATE SET
            "avgLiveSpread" = EXCLUDED."avgLiveSpread",
            "avgLiveTotal" = EXCLUDED."avgLiveTotal",
            "snapshotCount" = EXCLUDED."snapshotCount"
    ''')
    conn.commit()

    cur.execute('SELECT COUNT(*) FROM "LiveOddsByPeriod"')
    return cur.fetchone()[0]

def main():
    db_url = load_db_url()
    conn = psycopg2.connect(db_url)
    cur = conn.cursor()

    print("=" * 60)
    print("FIX LIVE ODDS GAME STATUS")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    # Fix game status
    fixed_count = fix_game_status(cur, conn)
    print(f"  Updated {fixed_count} live odds records")

    # Create period aggregates
    period_count = create_live_odds_periods(cur, conn)
    print(f"  Created {period_count} period aggregate records")

    # Summary
    cur.execute('''
        SELECT "gameStatus", COUNT(*)
        FROM "LiveOdds"
        GROUP BY "gameStatus"
        ORDER BY COUNT(*) DESC
    ''')
    print("\nLive Odds by Game Status:")
    for row in cur.fetchall():
        print(f"  {row[0] or 'NULL'}: {row[1]} records")

    cur.close()
    conn.close()
    return fixed_count

if __name__ == '__main__':
    main()