#!/usr/bin/env python3
"""
Compute Lineup Late Performance
Duo/trio late-game stats and effectiveness
Answers: "Tatum-Brown duo in close games?" "How does closing lineup perform?"
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_lineup_late_performance():
    conn = psycopg2.connect(load_db_url())
    cur = conn.cursor()

    # Create LineupLatePerformance table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "LineupLatePerformance" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) NOT NULL,
            season VARCHAR(20),
            team VARCHAR(100) NOT NULL,
            "lineupKey" TEXT,
            "player1Name" VARCHAR(200),
            "player2Name" VARCHAR(200),
            "player3Name" VARCHAR(200),
            "lineupSize" INTEGER DEFAULT 2,
            -- Usage stats
            "minutesTogether" NUMERIC,
            "gamesPlayed" INTEGER DEFAULT 0,
            -- Performance
            "netRating" NUMERIC,
            "offRating" NUMERIC,
            "defRating" NUMERIC,
            "plusMinus" NUMERIC,
            -- Late game estimation (based on net rating and games)
            "estimatedClutchMinutes" NUMERIC,
            "lateGameTrust" NUMERIC,
            -- Classification
            "lineupRole" VARCHAR(30),
            "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(league, team, "lineupKey", season)
        )
    ''')
    conn.commit()
    print("LineupLatePerformance table created/verified")

    # NBA lineup analysis from NBALineupStats
    print("\nComputing NBA lineup late performance...")
    cur.execute('''
        WITH lineup_with_names AS (
            SELECT
                ls.league,
                ls.season::text as season,
                ls.team,
                ls."lineupKey",
                ls.player1,
                ls.player2,
                ls.player3,
                ls.player4,
                ls.player5,
                ls."minutesTogether",
                ls."gamesPlayed",
                ls."netRating",
                ls."offRating",
                ls."defRating",
                ls."plusMinus",
                -- Get player names
                p1.name as player1_name,
                p2.name as player2_name,
                p3.name as player3_name,
                -- Determine lineup size (count non-null players)
                CASE
                    WHEN ls.player5 IS NOT NULL THEN 5
                    WHEN ls.player4 IS NOT NULL THEN 4
                    WHEN ls.player3 IS NOT NULL THEN 3
                    WHEN ls.player2 IS NOT NULL THEN 2
                    ELSE 1
                END as lineup_size
            FROM "NBALineupStats" ls
            LEFT JOIN "Player" p1 ON ls.player1 = p1."externalPlayerId"
            LEFT JOIN "Player" p2 ON ls.player2 = p2."externalPlayerId"
            LEFT JOIN "Player" p3 ON ls.player3 = p3."externalPlayerId"
            WHERE ls."minutesTogether" >= 20
              AND ls."gamesPlayed" >= 3
        ),
        ranked_lineups AS (
            SELECT
                *,
                -- Estimate late game trust based on net rating and minutes
                CASE
                    WHEN "netRating" > 5 AND "minutesTogether" > 50 THEN 0.9
                    WHEN "netRating" > 0 AND "minutesTogether" > 30 THEN 0.7
                    WHEN "netRating" > -5 THEN 0.5
                    ELSE 0.3
                END as late_game_trust,
                -- Estimate clutch minutes (higher for better lineups)
                CASE
                    WHEN "netRating" > 5 THEN "minutesTogether" * 0.15
                    WHEN "netRating" > 0 THEN "minutesTogether" * 0.10
                    ELSE "minutesTogether" * 0.05
                END as est_clutch_min
            FROM lineup_with_names
        )
        INSERT INTO "LineupLatePerformance"
        (league, season, team, "lineupKey", "player1Name", "player2Name", "player3Name",
         "lineupSize", "minutesTogether", "gamesPlayed",
         "netRating", "offRating", "defRating", "plusMinus",
         "estimatedClutchMinutes", "lateGameTrust", "lineupRole")
        SELECT
            league, season, team, "lineupKey",
            player1_name, player2_name, player3_name,
            lineup_size,
            ROUND("minutesTogether"::numeric, 1),
            "gamesPlayed",
            ROUND("netRating"::numeric, 1),
            ROUND("offRating"::numeric, 1),
            ROUND("defRating"::numeric, 1),
            ROUND("plusMinus"::numeric, 1),
            ROUND(est_clutch_min::numeric, 1),
            ROUND(late_game_trust::numeric, 2),
            CASE
                WHEN "netRating" > 10 AND "minutesTogether" > 100 THEN 'CLOSING_LINEUP'
                WHEN "netRating" > 5 AND "gamesPlayed" >= 10 THEN 'TRUSTED_COMBO'
                WHEN "offRating" > 115 THEN 'OFFENSIVE_SPARK'
                WHEN "defRating" < 105 THEN 'DEFENSIVE_ANCHOR'
                WHEN "netRating" < -5 THEN 'AVOID_LATE'
                ELSE 'ROTATION_PIECE'
            END
        FROM ranked_lineups
        ON CONFLICT (league, team, "lineupKey", season) DO UPDATE SET
            "player1Name" = EXCLUDED."player1Name",
            "player2Name" = EXCLUDED."player2Name",
            "player3Name" = EXCLUDED."player3Name",
            "lineupSize" = EXCLUDED."lineupSize",
            "minutesTogether" = EXCLUDED."minutesTogether",
            "gamesPlayed" = EXCLUDED."gamesPlayed",
            "netRating" = EXCLUDED."netRating",
            "offRating" = EXCLUDED."offRating",
            "defRating" = EXCLUDED."defRating",
            "plusMinus" = EXCLUDED."plusMinus",
            "estimatedClutchMinutes" = EXCLUDED."estimatedClutchMinutes",
            "lateGameTrust" = EXCLUDED."lateGameTrust",
            "lineupRole" = EXCLUDED."lineupRole",
            "updatedAt" = NOW()
    ''')
    nba_count = cur.rowcount
    conn.commit()
    print(f"  NBA: {nba_count} lineup late performance records")

    # Summary
    cur.execute('''
        SELECT "lineupRole", COUNT(*),
               ROUND(AVG("netRating")::numeric, 1) as avg_net,
               ROUND(AVG("lateGameTrust")::numeric, 2) as avg_trust
        FROM "LineupLatePerformance"
        WHERE "lineupRole" IS NOT NULL
        GROUP BY "lineupRole"
        ORDER BY COUNT(*) DESC
    ''')
    print("\nLineup Role Distribution:")
    for row in cur.fetchall():
        net = row[2] if row[2] else 0
        trust = row[3] if row[3] else 0
        print(f"  {row[0]}: {row[1]} lineups (avg net: {net:+.1f}, trust: {trust:.2f})")

    # Show top closing lineups
    cur.execute('''
        SELECT team, "player1Name", "player2Name", "player3Name",
               "netRating", "minutesTogether", "lineupRole"
        FROM "LineupLatePerformance"
        WHERE "lineupRole" IN ('CLOSING_LINEUP', 'TRUSTED_COMBO')
          AND "player1Name" IS NOT NULL
        ORDER BY "netRating" DESC NULLS LAST
        LIMIT 10
    ''')
    results = cur.fetchall()
    if results:
        print("\nTop Late-Game Lineups:")
        for row in results:
            players = ', '.join([p for p in [row[1], row[2], row[3]] if p])
            print(f"  {row[0]}: {players} ({row[4]:+.1f} net, {row[5]:.0f} min) - {row[6]}")

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

    cur.close()
    conn.close()
    print(f"\n✅ Lineup late performance complete: {total} records")

if __name__ == '__main__':
    compute_lineup_late_performance()