#!/usr/bin/env python3
"""
Backfill Closing Lines from Current Spread Data

For completed games without closing lines, uses the most recent
spread data as the closing line (since that was the last known line).

Run: python3 scripts/backfill_closing_lines.py
"""
import psycopg2
import os
from datetime import datetime, timezone


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().strip('"').split('?')[0]
    except FileNotFoundError:
        pass
    return os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0]


def run_backfill():
    print("=" * 60)
    print("BACKFILL CLOSING LINES")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

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

    # Check current state
    print("\n[1] Current closing line coverage...")
    cur.execute('''
        SELECT
            league,
            COUNT(*) as completed,
            COUNT("closingSpreadHome") as has_closing,
            ROUND(100.0 * COUNT("closingSpreadHome") / COUNT(*), 1) as pct
        FROM "SportsGame"
        WHERE "homeScore" IS NOT NULL
          AND league IN ('nba', 'nfl', 'nhl')
        GROUP BY league
        ORDER BY league
    ''')
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[2]}/{row[1]} ({row[3]}%)")

    # Backfill closing lines from current spread for completed games
    print("\n[2] Backfilling closing lines from current spread...")

    cur.execute('''
        UPDATE "SportsGame"
        SET
            "closingSpreadHome" = "spreadHome",
            "closingSpreadAway" = "spreadAway",
            "closingTotal" = total,
            "closingMoneylineHome" = "moneylineHome",
            "closingMoneylineAway" = "moneylineAway",
            "closingCapturedAt" = "oddsUpdatedAt"
        WHERE "homeScore" IS NOT NULL
          AND "closingSpreadHome" IS NULL
          AND "spreadHome" IS NOT NULL
          AND league IN ('nba', 'nfl', 'nhl')
    ''')
    updated = cur.rowcount
    print(f"  Updated {updated} games with closing lines")

    conn.commit()

    # Check new state
    print("\n[3] New closing line coverage...")
    cur.execute('''
        SELECT
            league,
            COUNT(*) as completed,
            COUNT("closingSpreadHome") as has_closing,
            ROUND(100.0 * COUNT("closingSpreadHome") / COUNT(*), 1) as pct
        FROM "SportsGame"
        WHERE "homeScore" IS NOT NULL
          AND league IN ('nba', 'nfl', 'nhl')
        GROUP BY league
        ORDER BY league
    ''')
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[2]}/{row[1]} ({row[3]}%)")

    # Also set opening = closing for games that don't have opening
    # (This is a proxy - better than nothing for ATS analysis)
    print("\n[4] Setting opening = closing where opening is missing...")
    cur.execute('''
        UPDATE "SportsGame"
        SET
            "openingSpreadHome" = "closingSpreadHome",
            "openingSpreadAway" = "closingSpreadAway",
            "openingTotal" = "closingTotal",
            "openingMoneylineHome" = "closingMoneylineHome",
            "openingMoneylineAway" = "closingMoneylineAway",
            "openingCapturedAt" = "closingCapturedAt"
        WHERE "homeScore" IS NOT NULL
          AND "openingSpreadHome" IS NULL
          AND "closingSpreadHome" IS NOT NULL
          AND league IN ('nba', 'nfl', 'nhl')
    ''')
    updated_opening = cur.rowcount
    print(f"  Set opening lines for {updated_opening} games")

    conn.commit()

    # Final coverage
    print("\n[5] Final coverage...")
    cur.execute('''
        SELECT
            league,
            COUNT(*) as completed,
            COUNT("openingSpreadHome") as has_opening,
            COUNT("closingSpreadHome") as has_closing
        FROM "SportsGame"
        WHERE "homeScore" IS NOT NULL
          AND league IN ('nba', 'nfl', 'nhl')
        GROUP BY league
        ORDER BY league
    ''')
    print(f"  {'League':<8} {'Completed':>10} {'Opening':>10} {'Closing':>10}")
    print(f"  {'-'*8} {'-'*10} {'-'*10} {'-'*10}")
    for row in cur.fetchall():
        print(f"  {row[0]:<8} {row[1]:>10} {row[2]:>10} {row[3]:>10}")

    print("\n" + "=" * 60)
    print(f"Backfill complete: {updated} closing, {updated_opening} opening")
    print("=" * 60)

    cur.close()
    conn.close()


if __name__ == '__main__':
    run_backfill()