#!/usr/bin/env python3
"""
Compute Player Clutch Usage
Q4/late-game usage and performance estimates from clutch metrics
Answers: "LeBron usage in crunch time?" "Curry late-game shot volume?"
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_clutch_usage():
    conn = psycopg2.connect(load_db_url())
    cur = conn.cursor()

    # Create PlayerClutchUsage table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "PlayerClutchUsage" (
            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,
            "avgMinutes" NUMERIC,
            "avgPoints" NUMERIC,
            "avgUsage" NUMERIC,
            -- Clutch stats (final 5 min, margin <= 5)
            "clutchMinutes" NUMERIC,
            "clutchPoints" NUMERIC,
            "clutchFGPct" NUMERIC,
            "clutchFG3Pct" NUMERIC,
            "clutchFTPct" NUMERIC,
            "clutchPlusMinus" NUMERIC,
            -- Clutch vs overall comparison
            "clutchUsageRatio" NUMERIC,
            "clutchPointsRatio" NUMERIC,
            "clutchEfficiencyDiff" NUMERIC,
            -- Classification
            "clutchRole" VARCHAR(30),
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, "playerExternalId", team, season)
        )
    ''')
    conn.commit()
    print("PlayerClutchUsage table created/verified")

    # NBA clutch usage analysis
    print("\nComputing NBA player clutch usage...")
    cur.execute('''
        WITH player_game_stats AS (
            SELECT
                pgm.league,
                pgm."playerExternalId",
                pgm.team,
                pgm."gameDate",
                MAX(CASE WHEN pgm."statKey" = 'espn_min' THEN pgm.value END) as minutes,
                MAX(CASE WHEN pgm."statKey" = 'espn_pts' THEN pgm.value END) as points,
                MAX(CASE WHEN pgm."statKey" = 'nba_usg_pct' THEN pgm.value END) as usage_pct,
                MAX(CASE WHEN pgm."statKey" = 'nba_fg_pct' THEN pgm.value END) as fg_pct,
                -- Clutch-specific stats
                MAX(CASE WHEN pgm."statKey" = 'nba_clutch_min' THEN pgm.value END) as clutch_min,
                MAX(CASE WHEN pgm."statKey" = 'nba_clutch_pts' THEN pgm.value END) as clutch_pts,
                MAX(CASE WHEN pgm."statKey" = 'nba_clutch_fg_pct' THEN pgm.value END) as clutch_fg_pct,
                MAX(CASE WHEN pgm."statKey" = 'nba_clutch_fg3_pct' THEN pgm.value END) as clutch_fg3_pct,
                MAX(CASE WHEN pgm."statKey" = 'nba_clutch_ft_pct' THEN pgm.value END) as clutch_ft_pct,
                MAX(CASE WHEN pgm."statKey" = 'nba_clutch_plus_minus' THEN pgm.value END) as clutch_plus_minus
            FROM "PlayerGameMetric" pgm
            WHERE pgm.league = 'nba'
              AND pgm."statKey" IN ('espn_min', 'espn_pts', 'nba_usg_pct', 'nba_fg_pct',
                                    'nba_clutch_min', 'nba_clutch_pts', 'nba_clutch_fg_pct',
                                    'nba_clutch_fg3_pct', 'nba_clutch_ft_pct', 'nba_clutch_plus_minus')
            GROUP BY pgm.league, pgm."playerExternalId", pgm.team, pgm."gameDate"
            HAVING MAX(CASE WHEN pgm."statKey" = 'espn_min' THEN pgm.value END) > 10
        ),
        aggregated AS (
            SELECT
                league,
                "playerExternalId",
                team,
                EXTRACT(YEAR FROM "gameDate")::text as season,
                COUNT(*) as total_games,
                ROUND(AVG(minutes)::numeric, 1) as avg_minutes,
                ROUND(AVG(points)::numeric, 1) as avg_points,
                ROUND(AVG(usage_pct)::numeric, 1) as avg_usage,
                -- Clutch averages
                ROUND(AVG(clutch_min)::numeric, 1) as avg_clutch_min,
                ROUND(AVG(clutch_pts)::numeric, 1) as avg_clutch_pts,
                ROUND(AVG(clutch_fg_pct)::numeric, 1) as avg_clutch_fg_pct,
                ROUND(AVG(clutch_fg3_pct)::numeric, 1) as avg_clutch_fg3_pct,
                ROUND(AVG(clutch_ft_pct)::numeric, 1) as avg_clutch_ft_pct,
                ROUND(AVG(clutch_plus_minus)::numeric, 1) as avg_clutch_pm,
                -- Ratios
                ROUND(AVG(CASE WHEN clutch_min > 0 AND minutes > 0 THEN clutch_min / minutes END)::numeric, 3) as clutch_min_ratio,
                ROUND(AVG(CASE WHEN clutch_pts IS NOT NULL AND points > 0 THEN clutch_pts / points END)::numeric, 3) as clutch_pts_ratio,
                ROUND(AVG(CASE WHEN clutch_fg_pct IS NOT NULL AND fg_pct IS NOT NULL THEN clutch_fg_pct - fg_pct END)::numeric, 1) as clutch_eff_diff
            FROM player_game_stats
            WHERE points IS NOT NULL
            GROUP BY league, "playerExternalId", team, EXTRACT(YEAR FROM "gameDate")
            HAVING COUNT(*) >= 5
        )
        INSERT INTO "PlayerClutchUsage"
        (league, season, "playerExternalId", team,
         "totalGames", "avgMinutes", "avgPoints", "avgUsage",
         "clutchMinutes", "clutchPoints", "clutchFGPct", "clutchFG3Pct", "clutchFTPct", "clutchPlusMinus",
         "clutchUsageRatio", "clutchPointsRatio", "clutchEfficiencyDiff", "clutchRole")
        SELECT
            league, season, "playerExternalId", team,
            total_games, avg_minutes, avg_points, avg_usage,
            avg_clutch_min, avg_clutch_pts, avg_clutch_fg_pct, avg_clutch_fg3_pct, avg_clutch_ft_pct, avg_clutch_pm,
            clutch_min_ratio, clutch_pts_ratio, clutch_eff_diff,
            CASE
                WHEN clutch_pts_ratio > 0.2 AND clutch_eff_diff > 0 THEN 'CLUTCH_STAR'
                WHEN clutch_pts_ratio > 0.15 THEN 'GO_TO_OPTION'
                WHEN clutch_eff_diff > 5 THEN 'CLUTCH_EFFICIENT'
                WHEN clutch_eff_diff < -10 THEN 'CLUTCH_STRUGGLE'
                WHEN clutch_min_ratio IS NOT NULL AND clutch_min_ratio < 0.05 THEN 'BENCHED_LATE'
                ELSE 'ROLE_PLAYER'
            END
        FROM aggregated
        ON CONFLICT (league, "playerExternalId", team, season) DO UPDATE SET
            "totalGames" = EXCLUDED."totalGames",
            "avgMinutes" = EXCLUDED."avgMinutes",
            "avgPoints" = EXCLUDED."avgPoints",
            "avgUsage" = EXCLUDED."avgUsage",
            "clutchMinutes" = EXCLUDED."clutchMinutes",
            "clutchPoints" = EXCLUDED."clutchPoints",
            "clutchFGPct" = EXCLUDED."clutchFGPct",
            "clutchFG3Pct" = EXCLUDED."clutchFG3Pct",
            "clutchFTPct" = EXCLUDED."clutchFTPct",
            "clutchPlusMinus" = EXCLUDED."clutchPlusMinus",
            "clutchUsageRatio" = EXCLUDED."clutchUsageRatio",
            "clutchPointsRatio" = EXCLUDED."clutchPointsRatio",
            "clutchEfficiencyDiff" = EXCLUDED."clutchEfficiencyDiff",
            "clutchRole" = EXCLUDED."clutchRole",
            "updatedAt" = NOW()
    ''')
    nba_count = cur.rowcount
    conn.commit()
    print(f"  NBA: {nba_count} player clutch usage records")

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

    # Summary
    cur.execute('''
        SELECT "clutchRole", COUNT(*),
               ROUND(AVG("clutchPointsRatio")::numeric, 3) as avg_pts_ratio,
               ROUND(AVG("clutchEfficiencyDiff")::numeric, 1) as avg_eff_diff
        FROM "PlayerClutchUsage"
        WHERE "clutchRole" IS NOT NULL
        GROUP BY "clutchRole"
        ORDER BY COUNT(*) DESC
    ''')
    print("\nClutch Role Distribution:")
    for row in cur.fetchall():
        pts_ratio = row[2] if row[2] else 0
        eff_diff = row[3] if row[3] else 0
        print(f"  {row[0]}: {row[1]} players (pts ratio: {pts_ratio:.3f}, eff diff: {eff_diff:+.1f})")

    # Show top clutch players
    cur.execute('''
        SELECT "playerName", team, "clutchPoints", "clutchEfficiencyDiff", "clutchRole"
        FROM "PlayerClutchUsage"
        WHERE "playerName" IS NOT NULL
          AND "clutchRole" IN ('CLUTCH_STAR', 'GO_TO_OPTION')
          AND "totalGames" >= 10
        ORDER BY "clutchPoints" DESC NULLS LAST
        LIMIT 10
    ''')
    results = cur.fetchall()
    if results:
        print("\nTop Clutch Performers:")
        for row in results:
            eff_diff = row[3] if row[3] else 0
            print(f"  {row[0]} ({row[1]}): {row[2]} clutch pts, {eff_diff:+.1f}% eff diff - {row[4]}")

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

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

if __name__ == '__main__':
    compute_player_clutch_usage()