#!/usr/bin/env python3
"""
Closing Line Lock — Captures final pre-game odds as closing lines
==================================================================
Runs every 5 minutes via cron. For games starting in the next 10 minutes:

1. Snapshots current GameOdds values as closing lines on SportsGame
2. Marks the latest PlayerPropLine row per prop group as isClosingLine = TRUE
3. Catches up on any games that started in the last 2 hours without closing marks

This ensures we always have closing lines even if the regular snapshot worker
misses the narrow window.

Cron: */5 * * * * flock -n /tmp/lock-closing-lines.lock python3 /var/www/html/eventheodds/scripts/lock_closing_lines.py
"""
import os
import sys
import time
import psycopg2
from datetime import datetime, timezone, timedelta

sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
from dotenv import load_dotenv
load_dotenv('/var/www/html/eventheodds/.env')

DB_URL = os.getenv('SPORTS_DATABASE_URL',
                    'postgresql://eventheodds:eventheodds_dev_password@127.0.0.1:5433/eventheodds_sports').split('?')[0]


def lock_game_odds_closing(cur, conn):
    """Copy current GameOdds values to SportsGame closing fields for imminent games."""
    now = datetime.now(timezone.utc)

    # Games starting in the next 10 minutes that don't have closing lines yet
    cur.execute('''
        UPDATE "SportsGame" sg
        SET
            "closingSpreadHome" = COALESCE(sg."closingSpreadHome", go_spread."lineValue"),
            "closingMoneylineHome" = COALESCE(sg."closingMoneylineHome", go_ml."homeOdds"),
            "closingMoneylineAway" = COALESCE(sg."closingMoneylineAway", go_ml."awayOdds"),
            "closingTotal" = COALESCE(sg."closingTotal", go_total."lineValue"),
            "closingCapturedAt" = COALESCE(sg."closingCapturedAt", NOW()),
            "updatedAt" = NOW()
        FROM (
            SELECT DISTINCT ON (league, "gameId") "gameId", league, "lineValue"
            FROM "GameOdds"
            WHERE market = 'spreads'
            ORDER BY league, "gameId", "fetchedAt" DESC NULLS LAST
        ) go_spread
        LEFT JOIN (
            SELECT DISTINCT ON (league, "gameId") "gameId", league, "homeOdds", "awayOdds"
            FROM "GameOdds"
            WHERE market = 'h2h'
            ORDER BY league, "gameId", "fetchedAt" DESC NULLS LAST
        ) go_ml ON go_spread."gameId" = go_ml."gameId" AND go_spread.league = go_ml.league
        LEFT JOIN (
            SELECT DISTINCT ON (league, "gameId") "gameId", league, "lineValue"
            FROM "GameOdds"
            WHERE market = 'totals'
            ORDER BY league, "gameId", "fetchedAt" DESC NULLS LAST
        ) go_total ON go_spread."gameId" = go_total."gameId" AND go_spread.league = go_total.league
        WHERE sg."externalGameId" = 'theoddsapi:' || go_spread."gameId"
          AND sg."gameDate" BETWEEN %s AND %s
          AND (sg."closingSpreadHome" IS NULL OR sg."closingMoneylineHome" IS NULL)
    ''', (now - timedelta(hours=2), now + timedelta(minutes=10)))

    game_updated = cur.rowcount
    conn.commit()
    return game_updated


def lock_ppl_closing(cur, conn):
    """Mark the latest PPL snapshot as isClosingLine for recently started games."""
    now = datetime.now(timezone.utc)

    # Find games that started in the last 2 hours
    # Mark the latest PPL row per (player, game, propType) as closing
    cur.execute('''
        WITH latest_per_prop AS (
            SELECT DISTINCT ON (ppl."playerExternalId", ppl."gameId", ppl."propType")
                ppl.id
            FROM "PlayerPropLine" ppl
            JOIN "SportsGame" sg ON ppl."gameId" = sg.id
            WHERE sg."gameDate" BETWEEN %s AND %s
              AND ppl."oddsAmerican" IS NOT NULL
              AND (ppl."isClosingLine" IS NULL OR ppl."isClosingLine" = FALSE)
            ORDER BY ppl."playerExternalId", ppl."gameId", ppl."propType",
                     COALESCE(ppl."snapshotAt", ppl."createdAt") DESC
        )
        UPDATE "PlayerPropLine" ppl
        SET "isClosingLine" = TRUE
        FROM latest_per_prop lpp
        WHERE ppl.id = lpp.id
    ''', (now - timedelta(hours=2), now + timedelta(minutes=10)))

    ppl_marked = cur.rowcount
    conn.commit()
    return ppl_marked


def main():
    start = time.time()
    now = datetime.now(timezone.utc)
    print(f"[{now.strftime('%Y-%m-%d %H:%M:%S UTC')}] Closing line lock running...")

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

    try:
        game_updated = lock_game_odds_closing(cur, conn)
        ppl_marked = lock_ppl_closing(cur, conn)

        elapsed = time.time() - start
        print(f"  GameOdds→SportsGame closing: {game_updated} games")
        print(f"  PPL isClosingLine marked: {ppl_marked} rows")
        print(f"  Done in {elapsed:.1f}s")

    except Exception as e:
        conn.rollback()
        print(f"  ERROR: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)
    finally:
        cur.close()
        conn.close()


if __name__ == '__main__':
    main()