#!/usr/bin/env python3
"""
Backfill Opening/Closing Lines from GameOdds to SportsGame
Copies opening and current (closing) odds data from GameOdds table
to the new SportsGame opening/closing fields for CLV analysis.

Run: Once after schema migration, then periodically to update closing lines
"""
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().split('?')[0]
    except FileNotFoundError:
        pass
    return os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0]


def backfill_opening_closing():
    """Backfill opening/closing lines from GameOdds to SportsGame"""
    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("BACKFILL OPENING/CLOSING LINES")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    # Get spread data from GameOdds (use best line or first bookmaker)
    cur.execute('''
        SELECT DISTINCT ON (league, "gameId")
            league, "gameId", "lineValue", "homeOdds", "awayOdds",
            "openingLineValue", "openingHomeOdds", "openingAwayOdds",
            "fetchedAt"
        FROM "GameOdds"
        WHERE market = 'spreads'
        ORDER BY league, "gameId", "isBestLine" DESC NULLS LAST, "fetchedAt" DESC
    ''')
    spread_data = {(r[0], r[1]): r for r in cur.fetchall()}
    print(f"Found {len(spread_data)} games with spread data")

    # Get moneyline data
    cur.execute('''
        SELECT DISTINCT ON (league, "gameId")
            league, "gameId", "homeOdds", "awayOdds",
            "openingHomeOdds", "openingAwayOdds",
            "fetchedAt"
        FROM "GameOdds"
        WHERE market = 'h2h'
        ORDER BY league, "gameId", "isBestLine" DESC NULLS LAST, "fetchedAt" DESC
    ''')
    ml_data = {(r[0], r[1]): r for r in cur.fetchall()}
    print(f"Found {len(ml_data)} games with moneyline data")

    # Get totals data
    cur.execute('''
        SELECT DISTINCT ON (league, "gameId")
            league, "gameId", "lineValue", "overOdds", "underOdds",
            "openingLineValue", "openingOverOdds", "openingUnderOdds",
            "fetchedAt"
        FROM "GameOdds"
        WHERE market = 'totals'
        ORDER BY league, "gameId", "isBestLine" DESC NULLS LAST, "fetchedAt" DESC
    ''')
    total_data = {(r[0], r[1]): r for r in cur.fetchall()}
    print(f"Found {len(total_data)} games with totals data")

    # Get all SportsGame records that could have odds
    cur.execute('''
        SELECT id, league, "externalGameId"
        FROM "SportsGame"
        WHERE "externalGameId" IS NOT NULL
    ''')
    games = cur.fetchall()
    print(f"Found {len(games)} SportsGame records with externalGameId")

    updated = 0
    skipped = 0

    for game_id, league, ext_id in games:
        key = (league, ext_id)

        spread = spread_data.get(key)
        ml = ml_data.get(key)
        total = total_data.get(key)

        if not spread and not ml and not total:
            skipped += 1
            continue

        # Build update values
        updates = []
        params = []

        # Opening spread
        if spread and spread[5] is not None:  # openingLineValue
            updates.append('"openingSpreadHome" = %s')
            params.append(spread[5])
            updates.append('"openingSpreadAway" = %s')
            params.append(-spread[5] if spread[5] else None)

        # Current spread (as closing until game starts)
        if spread and spread[2] is not None:  # lineValue
            updates.append('"closingSpreadHome" = %s')
            params.append(spread[2])
            updates.append('"closingSpreadAway" = %s')
            params.append(-spread[2] if spread[2] else None)
            updates.append('"spreadHome" = %s')
            params.append(spread[2])
            updates.append('"spreadAway" = %s')
            params.append(-spread[2] if spread[2] else None)

        # Opening moneyline
        if ml and ml[4] is not None:  # openingHomeOdds
            updates.append('"openingMoneylineHome" = %s')
            params.append(ml[4])
        if ml and ml[5] is not None:  # openingAwayOdds
            updates.append('"openingMoneylineAway" = %s')
            params.append(ml[5])

        # Current moneyline
        if ml and ml[2] is not None:  # homeOdds
            updates.append('"closingMoneylineHome" = %s')
            params.append(ml[2])
            updates.append('"moneylineHome" = %s')
            params.append(ml[2])
        if ml and ml[3] is not None:  # awayOdds
            updates.append('"closingMoneylineAway" = %s')
            params.append(ml[3])
            updates.append('"moneylineAway" = %s')
            params.append(ml[3])

        # Opening total
        if total and total[5] is not None:  # openingLineValue
            updates.append('"openingTotal" = %s')
            params.append(total[5])

        # Current total
        if total and total[2] is not None:  # lineValue
            updates.append('"closingTotal" = %s')
            params.append(total[2])
            updates.append('"total" = %s')
            params.append(total[2])

        # Set captured timestamps
        if spread and spread[8]:
            updates.append('"closingCapturedAt" = %s')
            params.append(spread[8])
        if spread and spread[5] is not None:
            updates.append('"openingCapturedAt" = COALESCE("openingCapturedAt", %s)')
            params.append(spread[8] if spread else None)

        if updates:
            updates.append('"updatedAt" = NOW()')
            params.append(game_id)

            sql = f'UPDATE "SportsGame" SET {", ".join(updates)} WHERE id = %s'
            try:
                cur.execute(sql, params)
                updated += 1
            except Exception as e:
                print(f"  Error updating game {game_id}: {e}")
                conn.rollback()

    conn.commit()
    cur.close()
    conn.close()

    print(f"\nResults:")
    print(f"  Updated: {updated}")
    print(f"  Skipped (no odds): {skipped}")
    print("=" * 60)

    return {'updated': updated, 'skipped': skipped}


def main():
    try:
        result = backfill_opening_closing()
        print(f"\nBackfill complete: {result['updated']} games updated")
    except Exception as e:
        print(f"ERROR: {e}")
        import traceback
        traceback.print_exc()


if __name__ == '__main__':
    main()