#!/usr/bin/env python3
"""
Compute Player Home/Away and Game State Splits
Fixes: Q13 (road usage), Q14 (losing aggression), Q20 (close game reliability)
Works across all sports with player data
"""
import psycopg2

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_player_splits():
    conn = psycopg2.connect(load_db_url())
    cur = conn.cursor()

    # Create PlayerSplits table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "PlayerSplits" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            "playerExternalId" TEXT,
            "playerName" VARCHAR(200),
            team VARCHAR(100),
            -- Home/Away Splits
            "homeGames" INTEGER DEFAULT 0,
            "awayGames" INTEGER DEFAULT 0,
            "homeAvgPoints" NUMERIC,
            "awayAvgPoints" NUMERIC,
            "homeAvgMinutes" NUMERIC,
            "awayAvgMinutes" NUMERIC,
            "homeAwayPointsDiff" NUMERIC,
            "homeAwayMinutesDiff" NUMERIC,
            "locationImpact" VARCHAR(20),
            -- Game State Splits (close vs blowout)
            "closeGameCount" INTEGER DEFAULT 0,
            "blowoutGameCount" INTEGER DEFAULT 0,
            "closeGameAvgPoints" NUMERIC,
            "blowoutAvgPoints" NUMERIC,
            "closeGameReliability" VARCHAR(20),
            -- Win/Loss Splits
            "winGameAvgPoints" NUMERIC,
            "lossGameAvgPoints" NUMERIC,
            "winLossPointsDiff" NUMERIC,
            "trailingAggression" VARCHAR(20),
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, "playerExternalId")
        )
    ''')
    conn.commit()
    print("PlayerSplits table created/verified")

    # Compute Home/Away splits for NBA
    print("\nComputing NBA player home/away splits...")
    cur.execute('''
        WITH player_game_stats AS (
            SELECT
                pgm."playerExternalId",
                MAX(pgm."playerName") as player_name,
                MAX(pgm.team) as team,
                pgm."gameKey",
                pgm."gameDate",
                sg."homeTeam",
                sg."awayTeam",
                sg."homeScore",
                sg."awayScore",
                CASE WHEN pgm.team = sg."homeTeam" THEN 'HOME' ELSE 'AWAY' END as location,
                CASE
                    WHEN ABS(sg."homeScore" - sg."awayScore") <= 5 THEN 'CLOSE'
                    WHEN ABS(sg."homeScore" - sg."awayScore") >= 15 THEN 'BLOWOUT'
                    ELSE 'NORMAL'
                END as game_type,
                CASE
                    WHEN (pgm.team = sg."homeTeam" AND sg."homeScore" > sg."awayScore")
                      OR (pgm.team = sg."awayTeam" AND sg."awayScore" > sg."homeScore")
                    THEN 'WIN' ELSE 'LOSS'
                END as result,
                MAX(CASE WHEN pgm."statKey" IN ('points', 'espn_pts', 'nba_pts') THEN pgm.value END) as points,
                MAX(CASE WHEN pgm."statKey" IN ('minutes', 'espn_min', 'nba_min') THEN pgm.value END) as minutes
            FROM "PlayerGameMetric" pgm
            JOIN "SportsGame" sg ON pgm."gameDate" = sg."gameDate"
                AND pgm.league = sg.league
                AND (pgm.team = sg."homeTeam" OR pgm.team = sg."awayTeam")
            WHERE pgm.league = 'nba'
              AND sg."homeScore" IS NOT NULL
              AND pgm."gameDate" > '2024-01-01'
            GROUP BY pgm."playerExternalId", pgm."gameKey", pgm."gameDate", sg."homeTeam", sg."awayTeam",
                     sg."homeScore", sg."awayScore", pgm.team
        ),
        player_splits AS (
            SELECT
                "playerExternalId",
                MAX(player_name) as player_name,
                MAX(team) as team,
                -- Home/Away
                COUNT(*) FILTER (WHERE location = 'HOME') as home_games,
                COUNT(*) FILTER (WHERE location = 'AWAY') as away_games,
                ROUND(AVG(points) FILTER (WHERE location = 'HOME')::numeric, 1) as home_avg_pts,
                ROUND(AVG(points) FILTER (WHERE location = 'AWAY')::numeric, 1) as away_avg_pts,
                ROUND(AVG(minutes) FILTER (WHERE location = 'HOME')::numeric, 1) as home_avg_min,
                ROUND(AVG(minutes) FILTER (WHERE location = 'AWAY')::numeric, 1) as away_avg_min,
                -- Game State
                COUNT(*) FILTER (WHERE game_type = 'CLOSE') as close_games,
                COUNT(*) FILTER (WHERE game_type = 'BLOWOUT') as blowout_games,
                ROUND(AVG(points) FILTER (WHERE game_type = 'CLOSE')::numeric, 1) as close_avg_pts,
                ROUND(AVG(points) FILTER (WHERE game_type = 'BLOWOUT')::numeric, 1) as blowout_avg_pts,
                -- Win/Loss
                ROUND(AVG(points) FILTER (WHERE result = 'WIN')::numeric, 1) as win_avg_pts,
                ROUND(AVG(points) FILTER (WHERE result = 'LOSS')::numeric, 1) as loss_avg_pts
            FROM player_game_stats
            WHERE points IS NOT NULL
            GROUP BY "playerExternalId"
            HAVING COUNT(*) >= 5
        )
        INSERT INTO "PlayerSplits"
        (league, "playerExternalId", "playerName", team,
         "homeGames", "awayGames", "homeAvgPoints", "awayAvgPoints",
         "homeAvgMinutes", "awayAvgMinutes", "homeAwayPointsDiff", "homeAwayMinutesDiff",
         "locationImpact",
         "closeGameCount", "blowoutGameCount", "closeGameAvgPoints", "blowoutAvgPoints",
         "closeGameReliability",
         "winGameAvgPoints", "lossGameAvgPoints", "winLossPointsDiff", "trailingAggression")
        SELECT
            'nba',
            "playerExternalId",
            player_name,
            team,
            home_games,
            away_games,
            home_avg_pts,
            away_avg_pts,
            home_avg_min,
            away_avg_min,
            ROUND((COALESCE(home_avg_pts, 0) - COALESCE(away_avg_pts, 0))::numeric, 1),
            ROUND((COALESCE(home_avg_min, 0) - COALESCE(away_avg_min, 0))::numeric, 1),
            CASE
                WHEN COALESCE(home_avg_pts, 0) - COALESCE(away_avg_pts, 0) > 3 THEN 'HOME_BOOST'
                WHEN COALESCE(away_avg_pts, 0) - COALESCE(home_avg_pts, 0) > 3 THEN 'ROAD_WARRIOR'
                ELSE 'NEUTRAL'
            END,
            close_games,
            blowout_games,
            close_avg_pts,
            blowout_avg_pts,
            CASE
                WHEN close_games >= 3 AND close_avg_pts >= COALESCE(blowout_avg_pts, 0) THEN 'CLUTCH'
                WHEN close_games >= 3 AND close_avg_pts < COALESCE(blowout_avg_pts, 0) - 5 THEN 'GARBAGE_TIME'
                ELSE 'CONSISTENT'
            END,
            win_avg_pts,
            loss_avg_pts,
            ROUND((COALESCE(loss_avg_pts, 0) - COALESCE(win_avg_pts, 0))::numeric, 1),
            CASE
                WHEN COALESCE(loss_avg_pts, 0) > COALESCE(win_avg_pts, 0) + 3 THEN 'MORE_AGGRESSIVE'
                WHEN COALESCE(win_avg_pts, 0) > COALESCE(loss_avg_pts, 0) + 3 THEN 'FRONT_RUNNER'
                ELSE 'CONSISTENT'
            END
        FROM player_splits
        ON CONFLICT (league, "playerExternalId") DO UPDATE SET
            "playerName" = EXCLUDED."playerName",
            team = EXCLUDED.team,
            "homeGames" = EXCLUDED."homeGames",
            "awayGames" = EXCLUDED."awayGames",
            "homeAvgPoints" = EXCLUDED."homeAvgPoints",
            "awayAvgPoints" = EXCLUDED."awayAvgPoints",
            "homeAvgMinutes" = EXCLUDED."homeAvgMinutes",
            "awayAvgMinutes" = EXCLUDED."awayAvgMinutes",
            "homeAwayPointsDiff" = EXCLUDED."homeAwayPointsDiff",
            "homeAwayMinutesDiff" = EXCLUDED."homeAwayMinutesDiff",
            "locationImpact" = EXCLUDED."locationImpact",
            "closeGameCount" = EXCLUDED."closeGameCount",
            "blowoutGameCount" = EXCLUDED."blowoutGameCount",
            "closeGameAvgPoints" = EXCLUDED."closeGameAvgPoints",
            "blowoutAvgPoints" = EXCLUDED."blowoutAvgPoints",
            "closeGameReliability" = EXCLUDED."closeGameReliability",
            "winGameAvgPoints" = EXCLUDED."winGameAvgPoints",
            "lossGameAvgPoints" = EXCLUDED."lossGameAvgPoints",
            "winLossPointsDiff" = EXCLUDED."winLossPointsDiff",
            "trailingAggression" = EXCLUDED."trailingAggression",
            "updatedAt" = NOW()
    ''')
    nba_count = cur.rowcount
    conn.commit()
    print(f"  NBA player splits computed: {nba_count}")

    # Summary
    cur.execute('''
        SELECT "locationImpact", COUNT(*) FROM "PlayerSplits"
        WHERE league = 'nba' GROUP BY "locationImpact"
    ''')
    print("\nLocation Impact Distribution:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]} players")

    cur.execute('''
        SELECT "closeGameReliability", COUNT(*) FROM "PlayerSplits"
        WHERE league = 'nba' GROUP BY "closeGameReliability"
    ''')
    print("\nClose Game Reliability:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]} players")

    cur.execute('''
        SELECT "trailingAggression", COUNT(*) FROM "PlayerSplits"
        WHERE league = 'nba' GROUP BY "trailingAggression"
    ''')
    print("\nTrailing Aggression:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]} players")

    # Show some examples
    cur.execute('''
        SELECT "playerName", "homeAwayPointsDiff", "locationImpact"
        FROM "PlayerSplits"
        WHERE league = 'nba' AND "locationImpact" != 'NEUTRAL'
        ORDER BY ABS("homeAwayPointsDiff") DESC
        LIMIT 5
    ''')
    print("\nTop Location-Dependent Players:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]:+.1f} pts ({row[2]})")

    cur.close()
    conn.close()
    print(f"\n✅ Player splits complete: {nba_count} NBA players")

if __name__ == '__main__':
    compute_player_splits()