#!/usr/bin/env python3
"""
Recalculate NBA standings from actual game results in SportsGame table.
This fixes the corrupted cumulative standings data.
"""
import psycopg2
from datetime import datetime
import sys

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

def get_current_season():
    """NBA season: Oct-Jun. If Jan-Jun, season started last year."""
    now = datetime.now()
    if now.month >= 10:
        return now.year
    else:
        return now.year - 1

def recalculate_standings(conn, league='nba', season=None):
    """Recalculate standings from SportsGame results."""
    cur = conn.cursor()
    season = season or get_current_season()
    
    print(f"Recalculating {league.upper()} standings for {season}-{season+1} season...")
    
    # Calculate standings from completed games
    sql = """
    WITH game_results AS (
        SELECT 
            "homeTeam" as team,
            CASE WHEN "homeScore" > "awayScore" THEN 1 ELSE 0 END as win,
            CASE WHEN "homeScore" < "awayScore" THEN 1 ELSE 0 END as loss
        FROM "SportsGame"
        WHERE league = %s
          AND season = %s
          AND "homeScore" IS NOT NULL 
          AND "awayScore" IS NOT NULL
          AND "homeScore" != 107  -- Exclude placeholder scores
        UNION ALL
        SELECT 
            "awayTeam" as team,
            CASE WHEN "awayScore" > "homeScore" THEN 1 ELSE 0 END as win,
            CASE WHEN "awayScore" < "homeScore" THEN 1 ELSE 0 END as loss
        FROM "SportsGame"
        WHERE league = %s
          AND season = %s
          AND "homeScore" IS NOT NULL 
          AND "awayScore" IS NOT NULL
          AND "homeScore" != 107
    ),
    team_records AS (
        SELECT 
            team,
            SUM(win) as wins,
            SUM(loss) as losses
        FROM game_results
        GROUP BY team
    )
    SELECT team, wins, losses
    FROM team_records
    ORDER BY wins DESC, losses ASC
    """
    
    cur.execute(sql, (league, season, league, season))
    standings = cur.fetchall()
    
    if not standings:
        print(f"No completed games found for {league.upper()} {season}-{season+1} season")
        return 0
    
    print(f"Found standings for {len(standings)} teams:")
    
    # Clear old standings for this league/season
    cur.execute(
        'DELETE FROM "TeamStandings" WHERE league = %s AND season = %s',
        (league, season)
    )
    print(f"Cleared {cur.rowcount} old standing records")
    
    # Insert new standings
    for team, wins, losses in standings:
        win_pct = wins / (wins + losses) if (wins + losses) > 0 else 0
        
        cur.execute("""
            INSERT INTO "TeamStandings" (league, season, team, wins, losses, "winPct", "lastUpdated")
            VALUES (%s, %s, %s, %s, %s, %s, NOW())
            ON CONFLICT (league, season, team) 
            DO UPDATE SET wins = EXCLUDED.wins, losses = EXCLUDED.losses, 
                         "winPct" = EXCLUDED."winPct", "lastUpdated" = NOW()
        """, (league, season, team, wins, losses, win_pct))
        
        print(f"  {team}: {wins}-{losses} ({win_pct:.3f})")
    
    return len(standings)

def main():
    db_url = load_db_url()
    if not db_url:
        print('ERROR: Database URL not found')
        sys.exit(1)
    
    conn = psycopg2.connect(db_url)
    
    try:
        season = get_current_season()
        print(f"=== Standings Fix - {datetime.now().isoformat()} ===")
        print(f"Current NBA season: {season}-{season+1}\n")
        
        count = recalculate_standings(conn, 'nba', season)
        
        conn.commit()
        print(f"\nUpdated {count} team standings")
        
    finally:
        conn.close()

if __name__ == '__main__':
    main()