#!/usr/bin/env python3
"""
Create NBALineupStats table for 5-man lineup analysis.
Derives lineup performance proxies from player combination data.
"""

import psycopg2
from datetime import datetime
from itertools import combinations

DB_URL = "postgresql://eventheodds:eventheodds_dev_password@127.0.0.1:5433/eventheodds_sports"

def main():
    conn = psycopg2.connect(DB_URL)
    cur = conn.cursor()

    # Create 5-man lineup table (for future detailed data)
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "NBALineupStats" (
            id BIGSERIAL PRIMARY KEY,
            league TEXT DEFAULT 'nba',
            season INTEGER NOT NULL,
            team TEXT NOT NULL,
            "lineupKey" TEXT NOT NULL,
            player1 TEXT,
            player2 TEXT,
            player3 TEXT,
            player4 TEXT,
            player5 TEXT,
            "minutesTogether" DOUBLE PRECISION DEFAULT 0,
            "gamesPlayed" INTEGER DEFAULT 0,
            "plusMinus" DOUBLE PRECISION,
            "offRating" DOUBLE PRECISION,
            "defRating" DOUBLE PRECISION,
            "netRating" DOUBLE PRECISION,
            pace DOUBLE PRECISION,
            "updatedAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, season, team, "lineupKey")
        )
    ''')

    cur.execute('CREATE INDEX IF NOT EXISTS idx_lineup_team ON "NBALineupStats"(team)')
    cur.execute('CREATE INDEX IF NOT EXISTS idx_lineup_season ON "NBALineupStats"(season)')

    conn.commit()
    print("NBALineupStats table created.")

    # Create lineup continuity analysis table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "NBALineupContinuity" (
            id BIGSERIAL PRIMARY KEY,
            league TEXT DEFAULT 'nba',
            season INTEGER NOT NULL,
            team TEXT NOT NULL,
            "totalPlayers" INTEGER DEFAULT 0,
            "corePlayersCount" INTEGER DEFAULT 0,
            "starterContinuity" DOUBLE PRECISION,
            "rotationDepth" INTEGER DEFAULT 0,
            "avgMinutesTop5" DOUBLE PRECISION,
            "avgMinutesTop8" DOUBLE PRECISION,
            "continuityScore" DOUBLE PRECISION,
            "teamWinPct" DOUBLE PRECISION,
            "teamATSPct" DOUBLE PRECISION,
            "updatedAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, season, team)
        )
    ''')

    conn.commit()
    print("NBALineupContinuity table created.")

    # Compute lineup continuity from player minutes data
    print("Computing NBA lineup continuity metrics...")
    cur.execute('''
        INSERT INTO "NBALineupContinuity"
        (league, season, team, "totalPlayers", "corePlayersCount", "avgMinutesTop5",
         "avgMinutesTop8", "rotationDepth", "continuityScore")
        SELECT
            'nba' as league,
            season,
            team,
            COUNT(DISTINCT "playerName") as total_players,
            SUM(CASE WHEN avg_min >= 20 THEN 1 ELSE 0 END) as core_count,
            ROUND(AVG(CASE WHEN rn <= 5 THEN avg_min END)::numeric, 1) as avg_min_top5,
            ROUND(AVG(CASE WHEN rn <= 8 THEN avg_min END)::numeric, 1) as avg_min_top8,
            SUM(CASE WHEN avg_min >= 10 THEN 1 ELSE 0 END) as rotation_depth,
            -- Continuity score: more minutes concentrated in fewer players = higher continuity
            ROUND((SUM(CASE WHEN rn <= 5 THEN avg_min ELSE 0 END) /
                   NULLIF(SUM(avg_min), 0) * 100)::numeric, 1) as continuity_score
        FROM (
            SELECT
                season,
                team,
                "playerName",
                AVG("minutesPerGame") as avg_min,
                ROW_NUMBER() OVER (PARTITION BY season, team ORDER BY AVG("minutesPerGame") DESC) as rn
            FROM "NBAPlayerOnOff"
            WHERE "gamesPlayed" >= 10 AND "minutesPerGame" > 0
            GROUP BY season, team, "playerName"
        ) ranked
        GROUP BY season, team
        HAVING COUNT(DISTINCT "playerName") >= 8
        ON CONFLICT (league, season, team) DO UPDATE SET
            "totalPlayers" = EXCLUDED."totalPlayers",
            "corePlayersCount" = EXCLUDED."corePlayersCount",
            "avgMinutesTop5" = EXCLUDED."avgMinutesTop5",
            "avgMinutesTop8" = EXCLUDED."avgMinutesTop8",
            "rotationDepth" = EXCLUDED."rotationDepth",
            "continuityScore" = EXCLUDED."continuityScore",
            "updatedAt" = NOW()
    ''')

    conn.commit()

    # Link continuity to ATS performance
    print("Linking continuity to ATS performance...")
    cur.execute('''
        UPDATE "NBALineupContinuity" lc
        SET
            "teamWinPct" = stats.win_pct,
            "teamATSPct" = stats.ats_pct
        FROM (
            SELECT
                season,
                team,
                ROUND(AVG(last5_wins)::numeric / 5 * 100, 1) as win_pct,
                ROUND(AVG(last5_ats_wins)::numeric / 5 * 100, 1) as ats_pct
            FROM "TeamRollingStats"
            WHERE league = 'nba'
            GROUP BY season, team
        ) stats
        WHERE lc.season = stats.season AND lc.team = stats.team
    ''')

    conn.commit()

    cur.execute('SELECT COUNT(*) FROM "NBALineupContinuity"')
    count = cur.fetchone()[0]
    print(f"NBALineupContinuity: {count} team-season records created.")

    # Show correlation between continuity and ATS
    cur.execute('''
        SELECT
            CASE WHEN "continuityScore" >= 60 THEN 'High Continuity'
                 WHEN "continuityScore" >= 50 THEN 'Medium Continuity'
                 ELSE 'Low Continuity' END as tier,
            COUNT(*) as teams,
            ROUND(AVG("teamATSPct")::numeric, 1) as avg_ats_pct,
            ROUND(AVG("continuityScore")::numeric, 1) as avg_continuity
        FROM "NBALineupContinuity"
        WHERE "teamATSPct" IS NOT NULL
        GROUP BY CASE WHEN "continuityScore" >= 60 THEN 'High Continuity'
                      WHEN "continuityScore" >= 50 THEN 'Medium Continuity'
                      ELSE 'Low Continuity' END
        ORDER BY avg_continuity DESC
    ''')
    print("\nLineup continuity vs ATS performance:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]} teams, {row[2]}% ATS, {row[3]} continuity score")

    # Also create common 2-man combinations analysis
    print("\nComputing top player combinations...")
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "NBAPlayerPairs" (
            id BIGSERIAL PRIMARY KEY,
            league TEXT DEFAULT 'nba',
            season INTEGER NOT NULL,
            team TEXT NOT NULL,
            player1 TEXT NOT NULL,
            player2 TEXT NOT NULL,
            "gamesTogeth" INTEGER DEFAULT 0,
            "combinedMinutes" DOUBLE PRECISION,
            "combinedImpact" DOUBLE PRECISION,
            "updatedAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, season, team, player1, player2)
        )
    ''')

    # Generate player pairs from on/off data
    cur.execute('''
        INSERT INTO "NBAPlayerPairs"
        (league, season, team, player1, player2, "gamesTogeth", "combinedMinutes", "combinedImpact")
        SELECT
            'nba',
            p1.season,
            p1.team,
            LEAST(p1."playerName", p2."playerName") as player1,
            GREATEST(p1."playerName", p2."playerName") as player2,
            LEAST(p1."gamesPlayed", p2."gamesPlayed") as games_together,
            ROUND((p1."minutesPerGame" + p2."minutesPerGame")::numeric, 1) as combined_min,
            ROUND(((p1."winPctWith" - 0.5) + (p2."winPctWith" - 0.5))::numeric * 100, 1) as combined_impact
        FROM "NBAPlayerOnOff" p1
        JOIN "NBAPlayerOnOff" p2 ON p1.team = p2.team AND p1.season = p2.season
            AND p1."playerName" < p2."playerName"
        WHERE p1."gamesPlayed" >= 20 AND p2."gamesPlayed" >= 20
            AND p1."minutesPerGame" >= 15 AND p2."minutesPerGame" >= 15
        ON CONFLICT (league, season, team, player1, player2) DO UPDATE SET
            "gamesTogeth" = EXCLUDED."gamesTogeth",
            "combinedMinutes" = EXCLUDED."combinedMinutes",
            "combinedImpact" = EXCLUDED."combinedImpact",
            "updatedAt" = NOW()
    ''')

    conn.commit()

    cur.execute('SELECT COUNT(*) FROM "NBAPlayerPairs"')
    pairs = cur.fetchone()[0]
    print(f"NBAPlayerPairs: {pairs} player combinations tracked.")

    cur.close()
    conn.close()

if __name__ == "__main__":
    main()