#!/usr/bin/env python3
"""
Q7: Compute in-game pace tracking from live odds snapshots
Tracks scoring pace changes and total adjustments during games
"""
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 compute_pace_tracking():
    db_url = load_db_url()
    if not db_url:
        raise RuntimeError('SPORTS_DATABASE_URL not set')

    conn = psycopg2.connect(db_url)
    cur = conn.cursor()

    # Create GamePaceTracking table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "GamePaceTracking" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            "gameId" TEXT NOT NULL,
            "gameDate" DATE,
            "homeTeam" VARCHAR(100),
            "awayTeam" VARCHAR(100),
            period VARCHAR(20),
            "gameTime" VARCHAR(50),
            "homeScore" INTEGER,
            "awayScore" INTEGER,
            "totalScore" INTEGER,
            "projectedTotal" NUMERIC,
            "currentTotal" NUMERIC,
            "paceRatio" NUMERIC,
            "scoringPace" VARCHAR(20),
            "snapshotAt" TIMESTAMP WITH TIME ZONE,
            "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, "gameId", "snapshotAt")
        )
    ''')

    # Create PaceTendencies table (team-level pace patterns)
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "PaceTendencies" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            team VARCHAR(100) NOT NULL,
            "avgPaceRatio" NUMERIC,
            "fastStartRate" NUMERIC,
            "slowStartRate" NUMERIC,
            "consistencyScore" NUMERIC,
            "overtimeRate" NUMERIC,
            "sampleSize" INTEGER,
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, team)
        )
    ''')

    conn.commit()
    print("Tables created/verified")

    # First check if we have LiveOddsSnapshot with scores
    cur.execute('''
        SELECT COUNT(*) FROM information_schema.tables
        WHERE table_name = 'LiveOddsSnapshot'
    ''')
    has_live = cur.fetchone()[0] > 0

    if has_live:
        cur.execute('SELECT COUNT(*) FROM "LiveOddsSnapshot" WHERE "homeScore" IS NOT NULL')
        live_count = cur.fetchone()[0]
    else:
        live_count = 0

    if live_count > 0:
        print(f"Using LiveOddsSnapshot ({live_count} records with scores)")

        # Compute pace from live snapshots
        cur.execute('''
            INSERT INTO "GamePaceTracking"
            (league, "gameId", "gameDate", "homeTeam", "awayTeam", period, "gameTime",
             "homeScore", "awayScore", "totalScore", "currentTotal", "snapshotAt")
            SELECT DISTINCT ON (league, COALESCE("externalGameId", "gameId"::text), "snapshotAt")
                league,
                COALESCE("externalGameId", "gameId"::text),
                "gameDate",
                "homeTeam",
                "awayTeam",
                COALESCE("currentPeriod", "gameStatus") as period,
                "gameTime",
                "homeScore",
                "awayScore",
                "homeScore" + "awayScore" as total_score,
                "liveTotal" as current_total,
                "snapshotAt"
            FROM "LiveOddsSnapshot"
            WHERE "homeScore" IS NOT NULL
              AND "awayScore" IS NOT NULL
              AND (COALESCE("externalGameId", "gameId"::text)) IS NOT NULL
            ORDER BY league, COALESCE("externalGameId", "gameId"::text), "snapshotAt"
            ON CONFLICT (league, "gameId", "snapshotAt") DO NOTHING
        ''')
        pace_records = cur.rowcount
        conn.commit()
    else:
        print("No live score data found. Deriving pace from period performance...")

        # Use LiveOddsByPeriod if available
        cur.execute('''
            SELECT COUNT(*) FROM information_schema.tables
            WHERE table_name = 'LiveOddsByPeriod'
        ''')
        has_period = cur.fetchone()[0] > 0

        if has_period:
            cur.execute('''
                INSERT INTO "GamePaceTracking"
                (league, "gameId", "gameDate", "homeTeam", "awayTeam", period,
                 "homeScore", "awayScore", "totalScore", "currentTotal")
                SELECT
                    league,
                    "gameId",
                    "gameDate"::date,
                    "homeTeam",
                    "awayTeam",
                    period,
                    "homeScoreAtPeriod",
                    "awayScoreAtPeriod",
                    COALESCE("homeScoreAtPeriod", 0) + COALESCE("awayScoreAtPeriod", 0),
                    "avgLiveTotal"
                FROM "LiveOddsByPeriod"
                WHERE "homeScoreAtPeriod" IS NOT NULL OR "awayScoreAtPeriod" IS NOT NULL
                ON CONFLICT (league, "gameId", "snapshotAt") DO NOTHING
            ''')
            pace_records = cur.rowcount
            conn.commit()
        else:
            # Fallback: derive from SportsGame final scores + TeamGameMetric period data
            cur.execute('''
                INSERT INTO "GamePaceTracking"
                (league, "gameId", "gameDate", "homeTeam", "awayTeam",
                 "homeScore", "awayScore", "totalScore")
                SELECT
                    sg.league,
                    sg."gameId"::text,
                    sg."gameDate",
                    sg."homeTeam",
                    sg."awayTeam",
                    sg."homeScore",
                    sg."awayScore",
                    sg."homeScore" + sg."awayScore" as total_score
                FROM "SportsGame" sg
                WHERE sg."homeScore" IS NOT NULL
                  AND sg."awayScore" IS NOT NULL
                  AND sg.status = 'final'
                ON CONFLICT (league, "gameId", "snapshotAt") DO NOTHING
            ''')
            pace_records = cur.rowcount
            conn.commit()

    print(f"GamePaceTracking populated: {pace_records} records")

    # Calculate projected totals based on game progress
    # NBA: 48 min, NFL: 60 min, NHL: 60 min
    cur.execute('''
        UPDATE "GamePaceTracking"
        SET "projectedTotal" = CASE
            WHEN league IN ('nba', 'ncaab') AND period = 'Q1' THEN "totalScore" * 4.0
            WHEN league IN ('nba', 'ncaab') AND period = 'Q2' THEN "totalScore" * 2.0
            WHEN league IN ('nba', 'ncaab') AND period = 'HALFTIME' THEN "totalScore" * 2.0
            WHEN league IN ('nba', 'ncaab') AND period = 'Q3' THEN "totalScore" * 1.33
            WHEN league IN ('nfl', 'ncaaf') AND period = 'Q1' THEN "totalScore" * 4.0
            WHEN league IN ('nfl', 'ncaaf') AND period = 'Q2' THEN "totalScore" * 2.0
            WHEN league IN ('nfl', 'ncaaf') AND period = 'HALFTIME' THEN "totalScore" * 2.0
            WHEN league IN ('nhl') AND period = 'P1' THEN "totalScore" * 3.0
            WHEN league IN ('nhl') AND period = 'P2' THEN "totalScore" * 1.5
            ELSE "totalScore"
        END
        WHERE "totalScore" IS NOT NULL AND "totalScore" > 0
    ''')
    conn.commit()

    # Calculate pace ratio (projected vs pregame total)
    cur.execute('''
        UPDATE "GamePaceTracking" gpt
        SET "paceRatio" = ROUND(("projectedTotal" / NULLIF("currentTotal", 0))::numeric, 2)
        WHERE "projectedTotal" IS NOT NULL AND "currentTotal" IS NOT NULL AND "currentTotal" > 0
    ''')
    conn.commit()

    # Categorize scoring pace
    cur.execute('''
        UPDATE "GamePaceTracking"
        SET "scoringPace" = CASE
            WHEN "paceRatio" > 1.15 THEN 'fast'
            WHEN "paceRatio" < 0.85 THEN 'slow'
            ELSE 'normal'
        END
        WHERE "paceRatio" IS NOT NULL
    ''')
    conn.commit()

    # Compute team pace tendencies
    cur.execute('''
        INSERT INTO "PaceTendencies"
        (league, team, "avgPaceRatio", "fastStartRate", "slowStartRate", "consistencyScore", "sampleSize")
        SELECT
            league,
            team,
            ROUND(AVG("paceRatio")::numeric, 2) as avg_pace,
            ROUND(100.0 * COUNT(CASE WHEN "scoringPace" = 'fast' THEN 1 END) / NULLIF(COUNT(*), 0), 1) as fast_rate,
            ROUND(100.0 * COUNT(CASE WHEN "scoringPace" = 'slow' THEN 1 END) / NULLIF(COUNT(*), 0), 1) as slow_rate,
            ROUND(100 - STDDEV("paceRatio")::numeric * 100, 1) as consistency,
            COUNT(*) as sample_size
        FROM (
            SELECT league, "homeTeam" as team, "paceRatio", "scoringPace" FROM "GamePaceTracking" WHERE "paceRatio" IS NOT NULL
            UNION ALL
            SELECT league, "awayTeam" as team, "paceRatio", "scoringPace" FROM "GamePaceTracking" WHERE "paceRatio" IS NOT NULL
        ) t
        GROUP BY league, team
        HAVING COUNT(*) >= 3
        ON CONFLICT (league, team) DO UPDATE SET
            "avgPaceRatio" = EXCLUDED."avgPaceRatio",
            "fastStartRate" = EXCLUDED."fastStartRate",
            "slowStartRate" = EXCLUDED."slowStartRate",
            "consistencyScore" = EXCLUDED."consistencyScore",
            "sampleSize" = EXCLUDED."sampleSize",
            "updatedAt" = NOW()
    ''')
    tendency_records = cur.rowcount
    conn.commit()

    print(f"PaceTendencies computed: {tendency_records} teams")

    # Print summary
    cur.execute('''
        SELECT league, COUNT(*), ROUND(AVG("avgPaceRatio")::numeric, 2)
        FROM "PaceTendencies"
        GROUP BY league
        ORDER BY league
    ''')
    print("\nPace Tendencies by League:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]} teams, avg pace ratio: {row[2]}")

    cur.close()
    conn.close()

    print(f"\n✅ Pace tracking complete: {pace_records} snapshots, {tendency_records} team tendencies")
    return {'pace_records': pace_records, 'tendency_records': tendency_records}

if __name__ == '__main__':
    compute_pace_tracking()