#!/usr/bin/env python3
"""
Infer NBA Starting Lineups from Player Minutes Data
Since NBA.com API blocks requests, we derive lineup data from:
- ESPN player minutes per game
- Top 5 players by minutes = probable starters

This creates a NBALineup table with:
- Game-level starting lineup combinations
- Lineup win/loss records
- Average team performance with lineup
"""
import psycopg2
import os
from datetime import datetime, timezone
from collections import defaultdict


def load_db_url():
    env_path = '/var/www/html/eventheodds/.env'
    try:
        with open(env_path, 'r') as f:
            for line in f:
                if line.startswith('SPORTS_DATABASE_URL='):
                    return line.split('=', 1)[1].strip().split('?')[0]
    except FileNotFoundError:
        pass
    return os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0]


def create_lineup_tables(cur):
    """Create tables for lineup tracking"""

    # Game-level lineup (starting 5 inferred from minutes)
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "NBALineup" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20) DEFAULT 'nba',
            "gameDate" DATE NOT NULL,
            team VARCHAR(10) NOT NULL,
            "gameId" INTEGER,
            "player1" VARCHAR(100),
            "player2" VARCHAR(100),
            "player3" VARCHAR(100),
            "player4" VARCHAR(100),
            "player5" VARCHAR(100),
            "lineupKey" VARCHAR(500),
            "teamScore" INTEGER,
            "oppScore" INTEGER,
            "won" BOOLEAN,
            "margin" INTEGER,
            "createdAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, "gameDate", team)
        )
    ''')

    # Lineup aggregate stats
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "NBALineupStats" (
            id SERIAL PRIMARY KEY,
            "lineupKey" VARCHAR(500) UNIQUE NOT NULL,
            team VARCHAR(10) NOT NULL,
            players TEXT[],
            games INTEGER DEFAULT 0,
            wins INTEGER DEFAULT 0,
            losses INTEGER DEFAULT 0,
            "avgMargin" DECIMAL(5,2),
            "avgTeamScore" DECIMAL(5,2),
            "avgOppScore" DECIMAL(5,2),
            "lastGameDate" DATE,
            "updatedAt" TIMESTAMP DEFAULT NOW()
        )
    ''')

    cur.execute('CREATE INDEX IF NOT EXISTS idx_lineup_team ON "NBALineup"(team)')
    cur.execute('CREATE INDEX IF NOT EXISTS idx_lineup_date ON "NBALineup"("gameDate")')
    cur.execute('CREATE INDEX IF NOT EXISTS idx_lineup_key ON "NBALineup"("lineupKey")')


def infer_lineups():
    """Infer starting lineups from player minutes data"""
    db_url = load_db_url()
    if not db_url:
        raise RuntimeError('SPORTS_DATABASE_URL not set')

    conn = psycopg2.connect(db_url)
    cur = conn.cursor()

    print("=" * 60)
    print("INFER NBA STARTING LINEUPS")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    create_lineup_tables(cur)
    conn.commit()

    # Get all NBA games with player minutes
    cur.execute('''
        SELECT DISTINCT "gameDate"::date, team
        FROM "PlayerGameMetric"
        WHERE league = 'nba'
          AND "statKey" = 'espn_min'
          AND "gameDate" >= '2025-10-01'
          AND value > 0
        ORDER BY "gameDate" DESC
    ''')
    game_teams = cur.fetchall()
    print(f"Found {len(game_teams)} team-games with player minutes")

    inserted = 0
    updated = 0

    for game_date, team in game_teams:
        # Get top 5 players by minutes for this team on this date
        cur.execute('''
            SELECT "playerName", value as minutes
            FROM "PlayerGameMetric"
            WHERE league = 'nba'
              AND "statKey" = 'espn_min'
              AND "gameDate"::date = %s
              AND team = %s
              AND value > 0
            ORDER BY value DESC
            LIMIT 5
        ''', (game_date, team))

        top_players = cur.fetchall()
        if len(top_players) < 5:
            continue

        players = sorted([p[0] for p in top_players])
        lineup_key = '|'.join(players)

        # Get game result for this team on this date
        cur.execute('''
            SELECT id, "homeTeam", "awayTeam", "homeScore", "awayScore"
            FROM "SportsGame"
            WHERE league = 'nba'
              AND "gameDate"::date = %s
              AND ("homeTeam" = %s OR "awayTeam" = %s)
              AND "homeScore" IS NOT NULL
            LIMIT 1
        ''', (game_date, team, team))

        game = cur.fetchone()
        if not game:
            continue

        game_id, home_team, away_team, home_score, away_score = game
        is_home = (team == home_team)
        team_score = home_score if is_home else away_score
        opp_score = away_score if is_home else home_score
        won = team_score > opp_score
        margin = team_score - opp_score

        # Insert or update lineup record
        try:
            cur.execute('''
                INSERT INTO "NBALineup" (
                    "gameDate", team, "gameId",
                    "player1", "player2", "player3", "player4", "player5",
                    "lineupKey", "teamScore", "oppScore", "won", margin
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON CONFLICT (league, "gameDate", team) DO UPDATE SET
                    "player1" = EXCLUDED."player1",
                    "player2" = EXCLUDED."player2",
                    "player3" = EXCLUDED."player3",
                    "player4" = EXCLUDED."player4",
                    "player5" = EXCLUDED."player5",
                    "lineupKey" = EXCLUDED."lineupKey",
                    "teamScore" = EXCLUDED."teamScore",
                    "oppScore" = EXCLUDED."oppScore",
                    "won" = EXCLUDED."won",
                    margin = EXCLUDED.margin
            ''', (
                game_date, team, game_id,
                players[0], players[1], players[2], players[3], players[4],
                lineup_key, team_score, opp_score, won, margin
            ))
            inserted += 1
        except Exception as e:
            print(f"  Error inserting lineup for {team} on {game_date}: {e}")
            conn.rollback()
            continue

        if inserted % 100 == 0:
            conn.commit()

    conn.commit()
    print(f"\nInserted/updated {inserted} lineup records")

    # Now aggregate lineup stats
    print("\nAggregating lineup statistics...")
    cur.execute('''
        INSERT INTO "NBALineupStats" (
            "lineupKey", team, players, games, wins, losses,
            "avgMargin", "avgTeamScore", "avgOppScore", "lastGameDate"
        )
        SELECT
            "lineupKey",
            team,
            ARRAY["player1", "player2", "player3", "player4", "player5"],
            COUNT(*) as games,
            SUM(CASE WHEN won THEN 1 ELSE 0 END) as wins,
            SUM(CASE WHEN NOT won THEN 1 ELSE 0 END) as losses,
            AVG(margin) as avg_margin,
            AVG("teamScore") as avg_team_score,
            AVG("oppScore") as avg_opp_score,
            MAX("gameDate") as last_game
        FROM "NBALineup"
        GROUP BY "lineupKey", team, "player1", "player2", "player3", "player4", "player5"
        ON CONFLICT ("lineupKey") DO UPDATE SET
            games = EXCLUDED.games,
            wins = EXCLUDED.wins,
            losses = EXCLUDED.losses,
            "avgMargin" = EXCLUDED."avgMargin",
            "avgTeamScore" = EXCLUDED."avgTeamScore",
            "avgOppScore" = EXCLUDED."avgOppScore",
            "lastGameDate" = EXCLUDED."lastGameDate",
            "updatedAt" = NOW()
    ''')

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

    conn.commit()
    print(f"Aggregated {lineup_count} unique lineups")

    # Show top lineups
    print("\nTop 10 Lineups by Games Played:")
    cur.execute('''
        SELECT team, players, games, wins, losses,
               ROUND("avgMargin"::numeric, 1) as avg_margin
        FROM "NBALineupStats"
        ORDER BY games DESC
        LIMIT 10
    ''')
    for row in cur.fetchall():
        team, players, games, wins, losses, margin = row
        pct = round(100 * wins / games, 1) if games > 0 else 0
        print(f"  {team}: {games} games, {wins}-{losses} ({pct}%), margin: {margin}")

    conn.close()
    print("\n" + "=" * 60)
    print("Lineup inference complete")
    return inserted


if __name__ == '__main__':
    infer_lineups()