#!/usr/bin/env python3
"""
Create Playoff Context/Standings Data
Tracks playoff position, clinch scenarios, elimination status
Enables Q19: Late-season pricing when playoff incentives change
"""
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 create_standings_tables(cur, conn):
    """Create standings and playoff context tables"""
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "TeamStandings" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(20),
            season INT,
            team VARCHAR(100),
            conference VARCHAR(50),
            division VARCHAR(50),
            wins INT,
            losses INT,
            "otLosses" INT,
            "winPct" FLOAT,
            "gamesBack" FLOAT,
            "confRank" INT,
            "divRank" INT,
            "playoffPosition" INT,
            "magicNumber" INT,
            "eliminationNumber" INT,
            "clinchStatus" VARCHAR(50),
            "lastUpdated" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, season, team)
        )
    ''')

    cur.execute('''
        CREATE TABLE IF NOT EXISTS "PlayoffContext" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(20),
            "gameId" TEXT,
            "gameDate" DATE,
            "homeTeam" TEXT,
            "awayTeam" TEXT,
            "homePlayoffPosition" INT,
            "awayPlayoffPosition" INT,
            "homeGamesBack" FLOAT,
            "awayGamesBack" FLOAT,
            "homeClinchStatus" VARCHAR(50),
            "awayClinchStatus" VARCHAR(50),
            "gameImportance" VARCHAR(20),
            "importanceScore" FLOAT,
            "isMeaningless" BOOLEAN DEFAULT FALSE,
            "bothEliminated" BOOLEAN DEFAULT FALSE,
            "bothClinched" BOOLEAN DEFAULT FALSE,
            "createdAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, "gameId")
        )
    ''')

    cur.execute('CREATE INDEX IF NOT EXISTS "TeamStandings_team" ON "TeamStandings" (team)')
    cur.execute('CREATE INDEX IF NOT EXISTS "PlayoffContext_importance" ON "PlayoffContext" ("gameImportance")')
    conn.commit()

def populate_standings(cur, conn):
    """Populate team standings from game results"""
    print("\nPopulating team standings...")

    # Compute standings from SportsGame results
    for league in ['nba', 'nfl', 'nhl']:
        cur.execute('''
            WITH team_records AS (
                SELECT
                    team,
                    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
                FROM (
                    SELECT "homeTeam" as team,
                           CASE WHEN "homeScore" > "awayScore" THEN true ELSE false END as won
                    FROM "SportsGame"
                    WHERE league = %s AND season >= 2024 AND "homeScore" IS NOT NULL
                    UNION ALL
                    SELECT "awayTeam" as team,
                           CASE WHEN "awayScore" > "homeScore" THEN true ELSE false END as won
                    FROM "SportsGame"
                    WHERE league = %s AND season >= 2024 AND "awayScore" IS NOT NULL
                ) all_games
                GROUP BY team
                HAVING COUNT(*) >= 5
            )
            INSERT INTO "TeamStandings" (
                league, season, team, wins, losses, "winPct", "confRank"
            )
            SELECT
                %s as league,
                2025 as season,
                team,
                wins,
                losses,
                ROUND(wins::numeric / NULLIF(games, 0), 3) as win_pct,
                ROW_NUMBER() OVER (ORDER BY wins::float / NULLIF(games, 0) DESC) as conf_rank
            FROM team_records
            ON CONFLICT (league, season, team) DO UPDATE SET
                wins = EXCLUDED.wins,
                losses = EXCLUDED.losses,
                "winPct" = EXCLUDED."winPct",
                "confRank" = EXCLUDED."confRank",
                "lastUpdated" = NOW()
        ''', (league, league, league))

    conn.commit()

    # Update playoff positions and clinch status
    for league in ['nba', 'nfl', 'nhl']:
        playoff_spots = {'nba': 8, 'nfl': 7, 'nhl': 8}[league]

        cur.execute('''
            UPDATE "TeamStandings"
            SET
                "playoffPosition" = "confRank",
                "clinchStatus" = CASE
                    WHEN "confRank" <= %s THEN
                        CASE WHEN "winPct" >= 0.700 THEN 'CLINCHED'
                             WHEN "winPct" >= 0.550 THEN 'IN_POSITION'
                             ELSE 'BUBBLE'
                        END
                    ELSE
                        CASE WHEN "winPct" <= 0.300 THEN 'ELIMINATED'
                             WHEN "winPct" <= 0.400 THEN 'UNLIKELY'
                             ELSE 'OUTSIDE'
                        END
                END,
                "gamesBack" = CASE
                    WHEN "confRank" <= %s THEN 0
                    ELSE ("confRank" - %s) * 0.5
                END
            WHERE league = %s AND season = 2025
        ''', (playoff_spots, playoff_spots, playoff_spots, league))

    conn.commit()

    cur.execute('SELECT COUNT(*) FROM "TeamStandings"')
    return cur.fetchone()[0]

def populate_playoff_context(cur, conn):
    """Add playoff context to games"""
    print("\nPopulating playoff context for games...")

    cur.execute('''
        INSERT INTO "PlayoffContext" (
            league, "gameId", "gameDate", "homeTeam", "awayTeam",
            "homePlayoffPosition", "awayPlayoffPosition",
            "homeGamesBack", "awayGamesBack",
            "homeClinchStatus", "awayClinchStatus",
            "gameImportance", "importanceScore",
            "isMeaningless", "bothEliminated", "bothClinched"
        )
        SELECT DISTINCT ON (go.league, go."gameId")
            go.league,
            go."gameId",
            go."gameDate"::date,
            go."homeTeam",
            go."awayTeam",
            COALESCE(hs."playoffPosition", 99),
            COALESCE(aws."playoffPosition", 99),
            COALESCE(hs."gamesBack", 10),
            COALESCE(aws."gamesBack", 10),
            COALESCE(hs."clinchStatus", 'UNKNOWN'),
            COALESCE(aws."clinchStatus", 'UNKNOWN'),
            CASE
                WHEN hs."clinchStatus" = 'ELIMINATED' AND aws."clinchStatus" = 'ELIMINATED' THEN 'MEANINGLESS'
                WHEN hs."clinchStatus" = 'CLINCHED' AND aws."clinchStatus" = 'CLINCHED' THEN 'LOW'
                WHEN hs."clinchStatus" IN ('BUBBLE', 'IN_POSITION') OR aws."clinchStatus" IN ('BUBBLE', 'IN_POSITION') THEN 'HIGH'
                ELSE 'MEDIUM'
            END as game_importance,
            CASE
                WHEN hs."clinchStatus" = 'BUBBLE' OR aws."clinchStatus" = 'BUBBLE' THEN 90.0
                WHEN hs."clinchStatus" = 'IN_POSITION' OR aws."clinchStatus" = 'IN_POSITION' THEN 70.0
                WHEN hs."clinchStatus" = 'ELIMINATED' AND aws."clinchStatus" = 'ELIMINATED' THEN 10.0
                ELSE 50.0
            END as importance_score,
            (hs."clinchStatus" = 'ELIMINATED' AND aws."clinchStatus" = 'ELIMINATED'),
            (hs."clinchStatus" = 'ELIMINATED' AND aws."clinchStatus" = 'ELIMINATED'),
            (hs."clinchStatus" = 'CLINCHED' AND aws."clinchStatus" = 'CLINCHED')
        FROM "GameOdds" go
        LEFT JOIN "TeamStandings" hs ON go.league = hs.league AND go."homeTeam" ILIKE '%' || hs.team || '%'
        LEFT JOIN "TeamStandings" aws ON go.league = aws.league AND go."awayTeam" ILIKE '%' || aws.team || '%'
        WHERE go.league IN ('nba', 'nfl', 'nhl')
          AND go."gameDate" >= '2024-01-01'
        ON CONFLICT (league, "gameId") DO UPDATE SET
            "gameImportance" = EXCLUDED."gameImportance",
            "importanceScore" = EXCLUDED."importanceScore"
    ''')
    conn.commit()

    cur.execute('SELECT COUNT(*) FROM "PlayoffContext"')
    return cur.fetchone()[0]

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

    print("=" * 60)
    print("CREATE PLAYOFF CONTEXT DATA")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    # Create tables
    create_standings_tables(cur, conn)

    # Populate standings
    standings_count = populate_standings(cur, conn)
    print(f"  Team standings: {standings_count}")

    # Populate playoff context
    context_count = populate_playoff_context(cur, conn)
    print(f"  Playoff context records: {context_count}")

    # Summary
    cur.execute('''
        SELECT "gameImportance", COUNT(*)
        FROM "PlayoffContext"
        GROUP BY "gameImportance"
        ORDER BY "gameImportance"
    ''')
    print("\nGames by Importance:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]} games")

    cur.close()
    conn.close()
    return context_count

if __name__ == '__main__':
    main()