#!/usr/bin/env python3
"""
Compute Player Role/Usage Tracking
Adds usage%, target share, role changes to player analysis
Fixes Q13: Has player's role changed recently?
"""
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_player_usage():
    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 PlayerUsageTracking table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "PlayerUsageTracking" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            "playerExternalId" TEXT,
            "playerName" VARCHAR(200),
            team VARCHAR(100),
            "avgMinutes" NUMERIC,
            "avgPoints" NUMERIC,
            "avgAssists" NUMERIC,
            "avgRebounds" NUMERIC,
            "usageRate" NUMERIC,
            "scoringShare" NUMERIC,
            "last5AvgMinutes" NUMERIC,
            "last5AvgPoints" NUMERIC,
            "seasonAvgMinutes" NUMERIC,
            "seasonAvgPoints" NUMERIC,
            "minutesChange" NUMERIC,
            "pointsChange" NUMERIC,
            "roleChangeScore" NUMERIC,
            "roleStatus" VARCHAR(30),
            "gamesPlayed" INTEGER,
            "last5Games" INTEGER,
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, "playerExternalId")
        )
    ''')
    conn.commit()
    print("PlayerUsageTracking table created/verified")

    # Compute NBA player usage
    print("\nComputing NBA player usage...")
    cur.execute('''
        INSERT INTO "PlayerUsageTracking"
        (league, "playerExternalId", "playerName", team, "avgMinutes", "avgPoints", 
         "avgAssists", "avgRebounds", "gamesPlayed", "updatedAt")
        SELECT 
            'nba',
            "playerExternalId",
            MAX("playerName"),
            MAX(team),
            ROUND(AVG(CASE WHEN "statKey" IN ('minutes', 'espn_min', 'nba_min') THEN value END)::numeric, 1),
            ROUND(AVG(CASE WHEN "statKey" IN ('points', 'espn_pts', 'nba_pts') THEN value END)::numeric, 1),
            ROUND(AVG(CASE WHEN "statKey" IN ('assists', 'espn_ast', 'nba_ast') THEN value END)::numeric, 1),
            ROUND(AVG(CASE WHEN "statKey" IN ('rebounds', 'espn_reb', 'nba_reb') THEN value END)::numeric, 1),
            COUNT(DISTINCT "gameKey"),
            NOW()
        FROM "PlayerGameMetric"
        WHERE league = 'nba' AND "gameDate" > '2024-01-01'
        GROUP BY "playerExternalId"
        HAVING COUNT(DISTINCT "gameKey") >= 5
        ON CONFLICT (league, "playerExternalId") DO UPDATE SET
            "playerName" = EXCLUDED."playerName",
            team = EXCLUDED.team,
            "avgMinutes" = EXCLUDED."avgMinutes",
            "avgPoints" = EXCLUDED."avgPoints",
            "avgAssists" = EXCLUDED."avgAssists",
            "avgRebounds" = EXCLUDED."avgRebounds",
            "gamesPlayed" = EXCLUDED."gamesPlayed",
            "updatedAt" = NOW()
    ''')
    nba_count = cur.rowcount
    conn.commit()
    print(f"  NBA players: {nba_count}")

    # Compute last 5 games
    print("Computing last 5 games averages...")
    cur.execute('''
        WITH recent_games AS (
            SELECT 
                "playerExternalId",
                "gameKey",
                "gameDate",
                MAX(CASE WHEN "statKey" IN ('minutes', 'espn_min', 'nba_min') THEN value END) as minutes,
                MAX(CASE WHEN "statKey" IN ('points', 'espn_pts', 'nba_pts') THEN value END) as points,
                ROW_NUMBER() OVER (PARTITION BY "playerExternalId" ORDER BY "gameDate" DESC) as rn
            FROM "PlayerGameMetric"
            WHERE league = 'nba'
            GROUP BY "playerExternalId", "gameKey", "gameDate"
        ),
        last5 AS (
            SELECT "playerExternalId", AVG(minutes) as l5_min, AVG(points) as l5_pts
            FROM recent_games WHERE rn <= 5
            GROUP BY "playerExternalId"
        )
        UPDATE "PlayerUsageTracking" put
        SET 
            "last5AvgMinutes" = ROUND(l5.l5_min::numeric, 1),
            "last5AvgPoints" = ROUND(l5.l5_pts::numeric, 1),
            "seasonAvgMinutes" = put."avgMinutes",
            "seasonAvgPoints" = put."avgPoints",
            "minutesChange" = ROUND((COALESCE(l5.l5_min, 0) - COALESCE(put."avgMinutes", 0))::numeric, 1),
            "pointsChange" = ROUND((COALESCE(l5.l5_pts, 0) - COALESCE(put."avgPoints", 0))::numeric, 1),
            "last5Games" = 5
        FROM last5 l5
        WHERE put."playerExternalId" = l5."playerExternalId" AND put.league = 'nba'
    ''')
    conn.commit()

    # Calculate role change
    print("Calculating role change scores...")
    cur.execute('''
        UPDATE "PlayerUsageTracking"
        SET 
            "roleChangeScore" = CASE 
                WHEN "avgMinutes" > 0 THEN ROUND(ABS(COALESCE("minutesChange", 0)) / NULLIF("avgMinutes", 0) * 100, 1)
                ELSE 0 END,
            "roleStatus" = CASE
                WHEN COALESCE("minutesChange", 0) > 5 THEN 'EXPANDED'
                WHEN COALESCE("minutesChange", 0) < -5 THEN 'REDUCED'
                WHEN COALESCE("pointsChange", 0) > 5 THEN 'HOT'
                WHEN COALESCE("pointsChange", 0) < -5 THEN 'COLD'
                ELSE 'STABLE' END
        WHERE league = 'nba'
    ''')
    conn.commit()

    # Compute usage rate
    print("Computing usage rates...")
    cur.execute('''
        WITH team_totals AS (
            SELECT team, SUM(COALESCE("avgPoints", 0)) as team_pts
            FROM "PlayerUsageTracking" WHERE league = 'nba' AND team IS NOT NULL
            GROUP BY team
        )
        UPDATE "PlayerUsageTracking" put
        SET "usageRate" = ROUND((COALESCE(put."avgPoints", 0) / NULLIF(tt.team_pts, 0) * 100)::numeric, 1),
            "scoringShare" = ROUND((COALESCE(put."avgPoints", 0) / NULLIF(tt.team_pts, 0) * 100)::numeric, 1)
        FROM team_totals tt
        WHERE put.team = tt.team AND put.league = 'nba'
    ''')
    conn.commit()

    # NFL players - simpler approach
    print("\nComputing NFL player usage...")
    cur.execute('''
        WITH nfl_stats AS (
            SELECT 
                "playerExternalId",
                MAX("playerName") as player_name,
                MAX(team) as team,
                COUNT(DISTINCT "gameKey") as games,
                AVG(CASE WHEN "statKey" = 'passingYards' THEN value END) as pass_yds,
                AVG(CASE WHEN "statKey" = 'rushingYards' THEN value END) as rush_yds,
                AVG(CASE WHEN "statKey" = 'receivingYards' THEN value END) as rec_yds
            FROM "PlayerGameMetric"
            WHERE league = 'nfl' AND "gameDate" > '2024-01-01'
            GROUP BY "playerExternalId"
            HAVING COUNT(DISTINCT "gameKey") >= 3
        )
        INSERT INTO "PlayerUsageTracking"
        (league, "playerExternalId", "playerName", team, "avgPoints", "gamesPlayed", "updatedAt")
        SELECT 
            'nfl',
            "playerExternalId",
            player_name,
            team,
            ROUND((COALESCE(pass_yds, 0)/25.0 + COALESCE(rush_yds, 0)/10.0 + COALESCE(rec_yds, 0)/10.0)::numeric, 1),
            games,
            NOW()
        FROM nfl_stats
        ON CONFLICT (league, "playerExternalId") DO UPDATE SET
            "playerName" = EXCLUDED."playerName",
            team = EXCLUDED.team,
            "avgPoints" = EXCLUDED."avgPoints",
            "gamesPlayed" = EXCLUDED."gamesPlayed",
            "updatedAt" = NOW()
    ''')
    nfl_count = cur.rowcount
    conn.commit()
    print(f"  NFL players: {nfl_count}")

    # Summary
    cur.execute('''
        SELECT league, COUNT(*), 
               COUNT(CASE WHEN "roleStatus" = 'EXPANDED' THEN 1 END),
               COUNT(CASE WHEN "roleStatus" = 'REDUCED' THEN 1 END),
               COUNT(CASE WHEN "roleStatus" = 'HOT' THEN 1 END),
               COUNT(CASE WHEN "roleStatus" = 'COLD' THEN 1 END),
               COUNT(CASE WHEN "roleStatus" = 'STABLE' THEN 1 END)
        FROM "PlayerUsageTracking" GROUP BY league
    ''')
    print("\nRole Status Summary:")
    for row in cur.fetchall():
        print(f"  {row[0].upper()}: {row[1]} players - Expanded:{row[2]}, Reduced:{row[3]}, Hot:{row[4]}, Cold:{row[5]}, Stable:{row[6]}")

    cur.close()
    conn.close()
    print(f"\n✅ Player usage tracking complete")
    return {'nba': nba_count, 'nfl': nfl_count}

if __name__ == '__main__':
    compute_player_usage()