#!/usr/bin/env python3
"""
Compute Player Performance vs Opponent Styles
Player stats against different opponent archetypes
Answers: "Jokić vs small-ball?" "Edwards vs elite defense?" "Tatum vs fast pace?"
Supports: NBA
"""
import psycopg2
from datetime import datetime

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

    # Create PlayerVsStyle table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "PlayerVsStyle" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            season VARCHAR(20),
            "playerExternalId" TEXT,
            "playerName" VARCHAR(200),
            team VARCHAR(100) NOT NULL,
            -- Overall stats
            "totalGames" INTEGER DEFAULT 0,
            "avgPoints" NUMERIC,
            "avgRebounds" NUMERIC,
            "avgAssists" NUMERIC,
            -- vs Elite Defense
            "gamesVsEliteDefense" INTEGER DEFAULT 0,
            "ptsVsEliteDefense" NUMERIC,
            "ptsVsNonEliteDefense" NUMERIC,
            "eliteDefenseDiff" NUMERIC,
            -- vs Fast Pace teams
            "gamesVsFastPace" INTEGER DEFAULT 0,
            "ptsVsFastPace" NUMERIC,
            "ptsVsSlowPace" NUMERIC,
            "paceDiff" NUMERIC,
            -- vs Small-Ball teams
            "gamesVsSmallBall" INTEGER DEFAULT 0,
            "rebVsSmallBall" NUMERIC,
            "rebVsTraditional" NUMERIC,
            "smallBallRebDiff" NUMERIC,
            -- vs 3PT Heavy teams
            "gamesVsThreeHeavy" INTEGER DEFAULT 0,
            "ptsVsThreeHeavy" NUMERIC,
            -- Classification
            "eliteDefensePerformer" VARCHAR(20),
            "pacePerformer" VARCHAR(20),
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, "playerExternalId", team, season)
        )
    ''')
    conn.commit()
    print("PlayerVsStyle table created/verified")

    # First ensure TeamPlayStyle exists
    cur.execute('SELECT COUNT(*) FROM "TeamPlayStyle" WHERE league = \'nba\'')
    style_count = cur.fetchone()[0]
    if style_count == 0:
        print("  Warning: TeamPlayStyle has no NBA data. Run compute_opponent_style.py first.")
        return

    print(f"\nUsing {style_count} team style records for matchup analysis...")

    # Compute player vs style matchups
    print("Computing NBA player vs opponent style...")
    cur.execute('''
        WITH player_game_opponent AS (
            SELECT
                pgm.league,
                pgm."playerExternalId",
                pgm.team,
                pgm."gameDate",
                EXTRACT(YEAR FROM pgm."gameDate")::text as season,
                -- Player stats
                MAX(CASE WHEN pgm."statKey" IN ('espn_pts', 'nba_pts', 'points') THEN pgm.value END) as points,
                MAX(CASE WHEN pgm."statKey" IN ('espn_reb', 'nba_reb', 'rebounds') THEN pgm.value END) as rebounds,
                MAX(CASE WHEN pgm."statKey" IN ('espn_ast', 'nba_ast', 'assists') THEN pgm.value END) as assists,
                -- Opponent info
                CASE
                    WHEN pgm.team = sg."homeTeam" THEN sg."awayTeam"
                    ELSE sg."homeTeam"
                END as opponent
            FROM "PlayerGameMetric" pgm
            JOIN "SportsGame" sg ON
                sg.league = pgm.league
                AND sg."gameDate" = pgm."gameDate"
                AND (sg."homeTeam" = pgm.team OR sg."awayTeam" = pgm.team)
            WHERE pgm.league = 'nba'
              AND pgm."statKey" IN ('espn_pts', 'nba_pts', 'points', 'espn_reb', 'nba_reb', 'rebounds', 'espn_ast', 'nba_ast', 'assists')
            GROUP BY pgm.league, pgm."playerExternalId", pgm.team, pgm."gameDate",
                     sg."homeTeam", sg."awayTeam"
            HAVING MAX(CASE WHEN pgm."statKey" IN ('espn_pts', 'nba_pts', 'points') THEN pgm.value END) IS NOT NULL
        ),
        player_with_opp_style AS (
            SELECT
                pgo.*,
                tps."isEliteDefense",
                tps."isSmallBall",
                tps."paceStyle"
            FROM player_game_opponent pgo
            LEFT JOIN "TeamPlayStyle" tps ON
                tps.team = pgo.opponent
                AND tps.season = pgo.season
                AND tps.league = 'nba'
        ),
        aggregated AS (
            SELECT
                league,
                "playerExternalId",
                team,
                season,
                COUNT(*) as total_games,
                ROUND(AVG(points)::numeric, 1) as avg_pts,
                ROUND(AVG(rebounds)::numeric, 1) as avg_reb,
                ROUND(AVG(assists)::numeric, 1) as avg_ast,
                -- vs Elite Defense
                COUNT(CASE WHEN "isEliteDefense" THEN 1 END) as games_elite_d,
                AVG(CASE WHEN "isEliteDefense" THEN points END) as pts_elite_d,
                AVG(CASE WHEN NOT "isEliteDefense" OR "isEliteDefense" IS NULL THEN points END) as pts_non_elite_d,
                -- vs Fast Pace
                COUNT(CASE WHEN "paceStyle" = 'FAST' THEN 1 END) as games_fast,
                AVG(CASE WHEN "paceStyle" = 'FAST' THEN points END) as pts_fast,
                AVG(CASE WHEN "paceStyle" = 'SLOW' THEN points END) as pts_slow,
                -- vs Small-Ball
                COUNT(CASE WHEN "isSmallBall" THEN 1 END) as games_small,
                AVG(CASE WHEN "isSmallBall" THEN rebounds END) as reb_small,
                AVG(CASE WHEN NOT "isSmallBall" OR "isSmallBall" IS NULL THEN rebounds END) as reb_trad,
                -- vs 3PT Heavy
                COUNT(CASE WHEN "paceStyle" = 'FAST' AND "isSmallBall" THEN 1 END) as games_three_heavy,
                AVG(CASE WHEN "paceStyle" = 'FAST' AND "isSmallBall" THEN points END) as pts_three_heavy
            FROM player_with_opp_style
            GROUP BY league, "playerExternalId", team, season
            HAVING COUNT(*) >= 5
        )
        INSERT INTO "PlayerVsStyle"
        (league, season, "playerExternalId", team,
         "totalGames", "avgPoints", "avgRebounds", "avgAssists",
         "gamesVsEliteDefense", "ptsVsEliteDefense", "ptsVsNonEliteDefense", "eliteDefenseDiff",
         "gamesVsFastPace", "ptsVsFastPace", "ptsVsSlowPace", "paceDiff",
         "gamesVsSmallBall", "rebVsSmallBall", "rebVsTraditional", "smallBallRebDiff",
         "gamesVsThreeHeavy", "ptsVsThreeHeavy",
         "eliteDefensePerformer", "pacePerformer")
        SELECT
            league, season, "playerExternalId", team,
            total_games, avg_pts, avg_reb, avg_ast,
            games_elite_d,
            ROUND(pts_elite_d::numeric, 1),
            ROUND(pts_non_elite_d::numeric, 1),
            ROUND((pts_elite_d - pts_non_elite_d)::numeric, 1),
            games_fast,
            ROUND(pts_fast::numeric, 1),
            ROUND(pts_slow::numeric, 1),
            ROUND((pts_fast - pts_slow)::numeric, 1),
            games_small,
            ROUND(reb_small::numeric, 1),
            ROUND(reb_trad::numeric, 1),
            ROUND((reb_small - reb_trad)::numeric, 1),
            games_three_heavy,
            ROUND(pts_three_heavy::numeric, 1),
            -- Classifications
            CASE
                WHEN games_elite_d >= 2 AND pts_elite_d >= avg_pts THEN 'ELITE_D_PROOF'
                WHEN games_elite_d >= 2 AND pts_elite_d < avg_pts - 3 THEN 'ELITE_D_STRUGGLES'
                ELSE 'NEUTRAL'
            END,
            CASE
                WHEN games_fast >= 2 AND pts_fast > pts_slow + 3 THEN 'FAST_PACE_BENEFITS'
                WHEN games_fast >= 2 AND pts_slow > pts_fast + 3 THEN 'SLOW_PACE_BENEFITS'
                ELSE 'PACE_NEUTRAL'
            END
        FROM aggregated
        ON CONFLICT (league, "playerExternalId", team, season) DO UPDATE SET
            "totalGames" = EXCLUDED."totalGames",
            "avgPoints" = EXCLUDED."avgPoints",
            "avgRebounds" = EXCLUDED."avgRebounds",
            "avgAssists" = EXCLUDED."avgAssists",
            "gamesVsEliteDefense" = EXCLUDED."gamesVsEliteDefense",
            "ptsVsEliteDefense" = EXCLUDED."ptsVsEliteDefense",
            "ptsVsNonEliteDefense" = EXCLUDED."ptsVsNonEliteDefense",
            "eliteDefenseDiff" = EXCLUDED."eliteDefenseDiff",
            "gamesVsFastPace" = EXCLUDED."gamesVsFastPace",
            "ptsVsFastPace" = EXCLUDED."ptsVsFastPace",
            "ptsVsSlowPace" = EXCLUDED."ptsVsSlowPace",
            "paceDiff" = EXCLUDED."paceDiff",
            "gamesVsSmallBall" = EXCLUDED."gamesVsSmallBall",
            "rebVsSmallBall" = EXCLUDED."rebVsSmallBall",
            "rebVsTraditional" = EXCLUDED."rebVsTraditional",
            "smallBallRebDiff" = EXCLUDED."smallBallRebDiff",
            "gamesVsThreeHeavy" = EXCLUDED."gamesVsThreeHeavy",
            "ptsVsThreeHeavy" = EXCLUDED."ptsVsThreeHeavy",
            "eliteDefensePerformer" = EXCLUDED."eliteDefensePerformer",
            "pacePerformer" = EXCLUDED."pacePerformer",
            "updatedAt" = NOW()
    ''')
    nba_count = cur.rowcount
    conn.commit()
    print(f"  NBA: {nba_count} player vs style records")

    # Add player names
    print("\nAdding player names...")
    cur.execute('''
        UPDATE "PlayerVsStyle" pvs
        SET "playerName" = p.name
        FROM "Player" p
        WHERE pvs."playerExternalId" = p."externalPlayerId"
          AND pvs."playerName" IS NULL
    ''')
    conn.commit()

    # Summary
    cur.execute('''
        SELECT "eliteDefensePerformer", COUNT(*),
               ROUND(AVG("eliteDefenseDiff")::numeric, 1) as avg_diff
        FROM "PlayerVsStyle"
        WHERE "eliteDefensePerformer" IS NOT NULL
        GROUP BY "eliteDefensePerformer"
        ORDER BY COUNT(*) DESC
    ''')
    print("\nElite Defense Performance Distribution:")
    for row in cur.fetchall():
        diff = row[2] if row[2] else 0
        print(f"  {row[0]}: {row[1]} players (avg pts diff: {diff:+.1f})")

    # Show notable players
    cur.execute('''
        SELECT "playerName", team, "avgPoints",
               "ptsVsEliteDefense", "eliteDefenseDiff", "eliteDefensePerformer"
        FROM "PlayerVsStyle"
        WHERE "playerName" IS NOT NULL
          AND "gamesVsEliteDefense" >= 3
          AND "avgPoints" >= 15
        ORDER BY "eliteDefenseDiff" DESC
        LIMIT 10
    ''')
    results = cur.fetchall()
    if results:
        print("\nTop Performers vs Elite Defense:")
        for row in results:
            diff = row[4] if row[4] else 0
            print(f"  {row[0]} ({row[1]}): {row[2]:.1f} avg, {row[3]:.1f} vs elite D ({diff:+.1f})")

    cur.execute('SELECT COUNT(*) FROM "PlayerVsStyle"')
    total = cur.fetchone()[0]

    cur.close()
    conn.close()
    print(f"\n✅ Player vs style complete: {total} records")

if __name__ == '__main__':
    compute_player_vs_style()