#!/usr/bin/env python3
"""
Compute NHL Goalie Rest Performance
Since game-by-game goalie data isn't available, this computes:
1. Team-level rest performance from GameScheduleMeta
2. Individual goalie season baselines from PlayerGameMetric
Enables Q17: Is this NHL goalie/team worse on short rest?
"""
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_goalie_rest():
    db_url = load_db_url()
    conn = psycopg2.connect(db_url)
    cur = conn.cursor()

    print("=" * 60)
    print("COMPUTE NHL GOALIE REST PERFORMANCE")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    # Create GoalieRestPerformance table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "GoalieRestPerformance" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(20) DEFAULT 'nhl',
            season INT,
            "playerName" VARCHAR(100),
            "playerExternalId" VARCHAR(100),
            team VARCHAR(100),
            "restCategory" VARCHAR(20),
            "gamesPlayed" INT,
            "avgSavePct" FLOAT,
            "avgGoalsAgainst" FLOAT,
            "wins" INT,
            "losses" INT,
            "winPct" FLOAT,
            "teamWinPctWithGoalie" FLOAT,
            "teamATSWithGoalie" FLOAT,
            "updatedAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(season, "playerExternalId", "restCategory")
        )
    ''')
    cur.execute('CREATE INDEX IF NOT EXISTS "GoalieRestPerf_player" ON "GoalieRestPerformance" ("playerName")')
    cur.execute('CREATE INDEX IF NOT EXISTS "GoalieRestPerf_rest" ON "GoalieRestPerformance" ("restCategory")')

    # Clear existing data
    cur.execute('TRUNCATE "GoalieRestPerformance"')

    total_records = 0

    # Part 1: Compute team-level rest performance from GameScheduleMeta
    print("\nComputing team-level rest impact on NHL games...")

    # Categorize rest days and compute performance
    cur.execute('''
        WITH rest_games AS (
            SELECT
                gsm."gameId",
                gsm.team,
                gsm."restDays",
                CASE
                    WHEN gsm."restDays" <= 1 THEN 'back-to-back'
                    WHEN gsm."restDays" = 2 THEN '2-days-rest'
                    WHEN gsm."restDays" >= 3 AND gsm."restDays" <= 4 THEN '3-4-days-rest'
                    ELSE 'extended-rest'
                END as rest_category,
                EXTRACT(YEAR FROM gsm."gameDate")::int as season,
                go."homeOdds",
                go."awayOdds"
            FROM "GameScheduleMeta" gsm
            LEFT JOIN "GameOdds" go ON gsm."gameId"::text = go."gameId" AND go.league = 'nhl'
            WHERE gsm.league = 'nhl'
              AND gsm."restDays" >= 0
              AND EXTRACT(YEAR FROM gsm."gameDate") >= 2024
        )
        SELECT
            season,
            team,
            rest_category,
            COUNT(*) as games,
            AVG(CASE WHEN "homeOdds" < "awayOdds" THEN 1 ELSE 0 END) as favored_pct
        FROM rest_games
        GROUP BY season, team, rest_category
        HAVING COUNT(*) >= 3
        ORDER BY team, rest_category
    ''')

    team_rest_data = cur.fetchall()
    print(f"  Found {len(team_rest_data)} team-rest category combinations")

    # Insert team-level data (using team name as player placeholder)
    for row in team_rest_data:
        season, team, rest_cat, games, favored_pct = row
        cur.execute('''
            INSERT INTO "GoalieRestPerformance" (
                league, season, "playerName", "playerExternalId", team,
                "restCategory", "gamesPlayed", "teamWinPctWithGoalie"
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            ON CONFLICT (season, "playerExternalId", "restCategory") DO UPDATE SET
                "gamesPlayed" = EXCLUDED."gamesPlayed",
                "teamWinPctWithGoalie" = EXCLUDED."teamWinPctWithGoalie",
                "updatedAt" = NOW()
        ''', (
            'nhl', season, f'{team} (team avg)', f'team_{team}_{season}', team,
            rest_cat, games, (favored_pct or 0) * 100
        ))
        total_records += 1

    # Part 2: Insert individual goalie season baselines
    print("\nFetching individual goalie season stats...")

    cur.execute('''
        SELECT
            "playerExternalId",
            "playerName",
            team,
            season,
            MAX(CASE WHEN "statKey" = 'nhl_g_games' THEN value END) as games,
            MAX(CASE WHEN "statKey" = 'nhl_g_sv_pct' THEN value END) as sv_pct,
            MAX(CASE WHEN "statKey" = 'nhl_g_gaa' THEN value END) as gaa,
            MAX(CASE WHEN "statKey" = 'nhl_g_gsax' THEN value END) as gsax
        FROM "PlayerGameMetric"
        WHERE league = 'nhl'
          AND position = 'G'
          AND season >= 2024
        GROUP BY "playerExternalId", "playerName", team, season
        HAVING MAX(CASE WHEN "statKey" = 'nhl_g_games' THEN value END) >= 5
    ''')

    goalie_seasons = cur.fetchall()
    print(f"  Found {len(goalie_seasons)} goalie seasons with 5+ games")

    for row in goalie_seasons:
        goalie_id, goalie_name, team, season, games, sv_pct, gaa, gsax = row

        # Insert as 'season-total' rest category (baseline)
        cur.execute('''
            INSERT INTO "GoalieRestPerformance" (
                league, season, "playerName", "playerExternalId", team,
                "restCategory", "gamesPlayed", "avgSavePct", "avgGoalsAgainst"
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON CONFLICT (season, "playerExternalId", "restCategory") DO UPDATE SET
                "gamesPlayed" = EXCLUDED."gamesPlayed",
                "avgSavePct" = EXCLUDED."avgSavePct",
                "avgGoalsAgainst" = EXCLUDED."avgGoalsAgainst",
                "updatedAt" = NOW()
        ''', (
            'nhl', season, goalie_name, goalie_id, team,
            'season-total', int(games) if games else 0, sv_pct, gaa
        ))
        total_records += 1

    conn.commit()

    # Part 3: League-wide rest performance summary
    print("\nNHL Team Performance by Rest Category:")
    cur.execute('''
        SELECT
            CASE
                WHEN "restDays" <= 1 THEN 'back-to-back'
                WHEN "restDays" = 2 THEN '2-days-rest'
                WHEN "restDays" >= 3 AND "restDays" <= 4 THEN '3-4-days-rest'
                ELSE 'extended-rest'
            END as rest_category,
            COUNT(*) as games,
            ROUND(AVG(CASE WHEN sg."homeScore" > sg."awayScore" THEN 100 ELSE 0 END)::numeric, 1) as home_win_pct,
            ROUND(AVG(CASE WHEN (sg."homeScore" - sg."awayScore") > COALESCE(sg."spreadHome", 0) THEN 100
                       WHEN (sg."homeScore" - sg."awayScore") < COALESCE(sg."spreadHome", 0) THEN 0
                       ELSE 50 END)::numeric, 1) as cover_pct
        FROM "GameScheduleMeta" gsm
        JOIN "SportsGame" sg ON gsm."gameId" = sg.id
        WHERE gsm.league = 'nhl'
          AND sg."homeScore" IS NOT NULL
          AND gsm."restDays" >= 0
        GROUP BY
            CASE
                WHEN "restDays" <= 1 THEN 'back-to-back'
                WHEN "restDays" = 2 THEN '2-days-rest'
                WHEN "restDays" >= 3 AND "restDays" <= 4 THEN '3-4-days-rest'
                ELSE 'extended-rest'
            END
        ORDER BY rest_category
    ''')

    for r in cur.fetchall():
        print(f"  {r[0]}: {r[1]} games, Win: {r[2]}%, Cover: {r[3]}%")

    # Summary
    print("\nGoalie Rest Performance Summary:")
    cur.execute('''
        SELECT "restCategory",
               COUNT(*) as entries,
               SUM("gamesPlayed") as total_games,
               ROUND(AVG("avgSavePct")::numeric, 2) as avg_sv_pct,
               ROUND(AVG("avgGoalsAgainst")::numeric, 2) as avg_gaa
        FROM "GoalieRestPerformance"
        GROUP BY "restCategory"
        ORDER BY "restCategory"
    ''')
    for r in cur.fetchall():
        sv_pct = r[3] if r[3] else 'N/A'
        gaa = r[4] if r[4] else 'N/A'
        print(f"  {r[0]}: {r[1]} entries, {r[2]} games, SV%: {sv_pct}, GAA: {gaa}")

    cur.close()
    conn.close()
    print(f"\nTotal goalie rest records: {total_records}")
    return total_records

if __name__ == '__main__':
    compute_goalie_rest()