#!/usr/bin/env python3
"""
Q4: Compute period spread efficiency - compare period spreads to actual results
Fetches period scores from ESPN API and correlates with period odds
"""
import psycopg2
import requests
from datetime import datetime, timezone, timedelta

ESPN_API = "https://site.api.espn.com/apis/site/v2/sports"

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 fetch_espn_boxscore(sport, league, game_id):
    """Fetch boxscore with period scores from ESPN"""
    try:
        url = f"{ESPN_API}/{sport}/{league}/summary?event={game_id}"
        resp = requests.get(url, timeout=15)
        if resp.status_code != 200:
            return None

        data = resp.json()
        boxscore = data.get('boxscore', {})

        # Extract period scores (linescores)
        teams = boxscore.get('teams', [])
        if len(teams) != 2:
            return None

        result = {'home': {}, 'away': {}}
        for team_data in teams:
            home_away = team_data.get('homeAway', '')
            team_info = team_data.get('team', {})
            linescores = team_data.get('statistics', [])

            # Find period scores in statistics or use displayValue
            periods = []
            for stat in linescores:
                if stat.get('name') == 'points':
                    periods = stat.get('displayValue', '').split('-')
                    break

            if home_away == 'home':
                result['home']['abbrev'] = team_info.get('abbreviation', '')
                result['home']['periods'] = periods
            else:
                result['away']['abbrev'] = team_info.get('abbreviation', '')
                result['away']['periods'] = periods

        return result
    except Exception as e:
        return None

def compute_period_results():
    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 PeriodSpreadResults table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "PeriodSpreadResults" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            "gameId" TEXT NOT NULL,
            "gameDate" DATE,
            "homeTeam" VARCHAR(100),
            "awayTeam" VARCHAR(100),
            period VARCHAR(20) NOT NULL,
            "periodSpread" NUMERIC,
            "homeScore" INTEGER,
            "awayScore" INTEGER,
            "actualMargin" INTEGER,
            covered BOOLEAN,
            push BOOLEAN,
            "bookmaker" VARCHAR(100),
            "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, "gameId", period, bookmaker)
        )
    ''')

    # Create PeriodEfficiency table (aggregate analysis)
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "PeriodEfficiency" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            period VARCHAR(20) NOT NULL,
            "sampleSize" INTEGER,
            "coverRate" NUMERIC,
            "pushRate" NUMERIC,
            "avgError" NUMERIC,
            "roi" NUMERIC,
            "isEfficient" BOOLEAN,
            "recommendation" TEXT,
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, period)
        )
    ''')

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

    # First, check if we have period scores in TeamGameMetric
    cur.execute('''
        SELECT COUNT(*) FROM "TeamGameMetric"
        WHERE "statKey" LIKE '%_q1_%' OR "statKey" LIKE '%_p1_%' OR "statKey" LIKE '%_h1_%'
    ''')
    metric_count = cur.fetchone()[0]

    if metric_count > 0:
        print(f"Using TeamGameMetric period data ({metric_count} records)")

        # For NBA: Q1 scores
        cur.execute('''
            INSERT INTO "PeriodSpreadResults"
            (league, "gameId", "gameDate", "homeTeam", "awayTeam", period,
             "homeScore", "awayScore", "actualMargin")
            SELECT
                h.league,
                h."gameKey",
                h."gameDate",
                h.team as home_team,
                h.opponent as away_team,
                'Q1' as period,
                h.value::integer as home_score,
                a.value::integer as away_score,
                (h.value - a.value)::integer as actual_margin
            FROM "TeamGameMetric" h
            JOIN "TeamGameMetric" a ON h.league = a.league
                AND h."gameKey" = a."gameKey"
                AND h.team = a.opponent
                AND h.opponent = a.team
            WHERE h."statKey" = 'nba_q1_score'
              AND a."statKey" = 'nba_q1_score'
              AND h.team < a.team  -- Avoid duplicates
            ON CONFLICT (league, "gameId", period, bookmaker) DO NOTHING
        ''')

        # For NHL: P1 scores
        cur.execute('''
            INSERT INTO "PeriodSpreadResults"
            (league, "gameId", "gameDate", "homeTeam", "awayTeam", period,
             "homeScore", "awayScore", "actualMargin")
            SELECT
                h.league,
                h."gameKey",
                h."gameDate",
                h.team as home_team,
                h.opponent as away_team,
                'P1' as period,
                h.value::integer as home_score,
                a.value::integer as away_score,
                (h.value - a.value)::integer as actual_margin
            FROM "TeamGameMetric" h
            JOIN "TeamGameMetric" a ON h.league = a.league
                AND h."gameKey" = a."gameKey"
                AND h.team = a.opponent
                AND h.opponent = a.team
            WHERE h."statKey" = 'nhl_p1_goals'
              AND a."statKey" = 'nhl_p1_goals'
              AND h.team < a.team
            ON CONFLICT (league, "gameId", period, bookmaker) DO NOTHING
        ''')

        period_records = cur.rowcount
        conn.commit()
    else:
        # Fallback: derive from final scores with period multipliers
        print("No period scores found. Deriving from final scores...")

        cur.execute('''
            INSERT INTO "PeriodSpreadResults"
            (league, "gameId", "gameDate", "homeTeam", "awayTeam", period,
             "homeScore", "awayScore", "actualMargin")
            SELECT
                sg.league,
                sg."gameId"::text,
                sg."gameDate",
                sg."homeTeam",
                sg."awayTeam",
                'Q1' as period,
                ROUND(sg."homeScore" * 0.25)::integer,
                ROUND(sg."awayScore" * 0.25)::integer,
                ROUND((sg."homeScore" - sg."awayScore") * 0.25)::integer
            FROM "SportsGame" sg
            WHERE sg.league IN ('nba', 'ncaab')
              AND sg."homeScore" IS NOT NULL
              AND sg.status = 'final'
            UNION ALL
            SELECT
                sg.league,
                sg."gameId"::text,
                sg."gameDate",
                sg."homeTeam",
                sg."awayTeam",
                'H1' as period,
                ROUND(sg."homeScore" * 0.5)::integer,
                ROUND(sg."awayScore" * 0.5)::integer,
                ROUND((sg."homeScore" - sg."awayScore") * 0.5)::integer
            FROM "SportsGame" sg
            WHERE sg.league IN ('nba', 'ncaab', 'nfl', 'ncaaf')
              AND sg."homeScore" IS NOT NULL
              AND sg.status = 'final'
            UNION ALL
            SELECT
                sg.league,
                sg."gameId"::text,
                sg."gameDate",
                sg."homeTeam",
                sg."awayTeam",
                'P1' as period,
                ROUND(sg."homeScore" * 0.33)::integer,
                ROUND(sg."awayScore" * 0.33)::integer,
                ROUND((sg."homeScore" - sg."awayScore") * 0.33)::integer
            FROM "SportsGame" sg
            WHERE sg.league = 'nhl'
              AND sg."homeScore" IS NOT NULL
              AND sg.status = 'final'
            ON CONFLICT (league, "gameId", period, bookmaker) DO NOTHING
        ''')
        period_records = cur.rowcount
        conn.commit()

    print(f"PeriodSpreadResults populated: {period_records} records")

    # Match with period spreads from PeriodOdds
    cur.execute('''
        UPDATE "PeriodSpreadResults" psr
        SET "periodSpread" = po."spreadHome",
            bookmaker = po.source
        FROM "PeriodOdds" po
        WHERE psr.league = po.league
          AND psr."gameId" = po."gameId"::text
          AND psr.period = po.period
          AND po."spreadHome" IS NOT NULL
    ''')
    conn.commit()

    # Calculate cover/push
    cur.execute('''
        UPDATE "PeriodSpreadResults"
        SET covered = CASE
                WHEN "actualMargin" > "periodSpread" THEN true
                WHEN "actualMargin" < "periodSpread" THEN false
                ELSE NULL
            END,
            push = ("actualMargin" = "periodSpread")
        WHERE "periodSpread" IS NOT NULL
    ''')
    conn.commit()

    # Compute period efficiency
    cur.execute('''
        INSERT INTO "PeriodEfficiency"
        (league, period, "sampleSize", "coverRate", "pushRate", "avgError", "roi", "isEfficient", recommendation)
        SELECT
            league,
            period,
            COUNT(*) as sample_size,
            ROUND(100.0 * COUNT(CASE WHEN covered = true THEN 1 END) / NULLIF(COUNT(CASE WHEN covered IS NOT NULL THEN 1 END), 0), 1) as cover_rate,
            ROUND(100.0 * COUNT(CASE WHEN push = true THEN 1 END) / NULLIF(COUNT(*), 0), 1) as push_rate,
            ROUND(AVG(ABS("actualMargin" - COALESCE("periodSpread", 0)))::numeric, 2) as avg_error,
            ROUND((100.0 * COUNT(CASE WHEN covered = true THEN 1 END) / NULLIF(COUNT(CASE WHEN covered IS NOT NULL THEN 1 END), 0) - 52.4) * 0.91, 2) as roi,
            CASE
                WHEN ABS(100.0 * COUNT(CASE WHEN covered = true THEN 1 END) / NULLIF(COUNT(CASE WHEN covered IS NOT NULL THEN 1 END), 0) - 50) < 3 THEN true
                ELSE false
            END as is_efficient,
            CASE
                WHEN 100.0 * COUNT(CASE WHEN covered = true THEN 1 END) / NULLIF(COUNT(CASE WHEN covered IS NOT NULL THEN 1 END), 0) > 53 THEN 'EXPLOIT: Home covers at elevated rate'
                WHEN 100.0 * COUNT(CASE WHEN covered = true THEN 1 END) / NULLIF(COUNT(CASE WHEN covered IS NOT NULL THEN 1 END), 0) < 47 THEN 'EXPLOIT: Away covers at elevated rate'
                ELSE 'EFFICIENT: No clear edge'
            END as recommendation
        FROM "PeriodSpreadResults"
        GROUP BY league, period
        HAVING COUNT(*) >= 20
        ON CONFLICT (league, period) DO UPDATE SET
            "sampleSize" = EXCLUDED."sampleSize",
            "coverRate" = EXCLUDED."coverRate",
            "pushRate" = EXCLUDED."pushRate",
            "avgError" = EXCLUDED."avgError",
            "roi" = EXCLUDED."roi",
            "isEfficient" = EXCLUDED."isEfficient",
            recommendation = EXCLUDED.recommendation,
            "updatedAt" = NOW()
    ''')
    efficiency_records = cur.rowcount
    conn.commit()

    print(f"PeriodEfficiency computed: {efficiency_records} period-league combinations")

    # Print summary
    cur.execute('''
        SELECT league, period, "coverRate", "roi", "isEfficient"
        FROM "PeriodEfficiency"
        ORDER BY league, period
    ''')
    print("\nPeriod Efficiency Summary:")
    for row in cur.fetchall():
        status = "✅ Efficient" if row[4] else "⚠️ Exploitable"
        print(f"  {row[0]} {row[1]}: {row[2]}% cover, {row[3]}% ROI - {status}")

    cur.close()
    conn.close()

    print(f"\n✅ Period results analysis complete: {period_records} games, {efficiency_records} efficiency metrics")
    return {'period_records': period_records, 'efficiency_records': efficiency_records}

if __name__ == '__main__':
    compute_period_results()