#!/usr/bin/env python3
"""
Fetch REAL Line Movement from OddsSnapshot Table

This script computes REAL line movement by comparing opening vs closing snapshots
from the OddsSnapshot table, which contains actual data from TheOddsAPI.

Unlike the synthetic approach which used random variances, this extracts actual
recorded opening and closing lines to compute true market movements.

Run: Every hour (after odds snapshots captured)
Cron: 0 * * * * python3 /var/www/html/eventheodds/scripts/fetch_real_line_movement.py
"""
import psycopg2
from datetime import datetime, timezone, timedelta

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 compute_real_line_movement():
    """
    Compute REAL line movement from OddsSnapshot opening/closing data.

    This uses actual captured odds from TheOddsAPI stored in OddsSnapshot,
    not synthetic/random values.
    """
    db_url = load_db_url()
    if not db_url:
        raise RuntimeError('SPORTS_DATABASE_URL not set')

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

    now = datetime.now(timezone.utc)
    print("=" * 80)
    print("COMPUTING REAL LINE MOVEMENT FROM ODDSSNAPSHOT")
    print(f"Time: {now.isoformat()}")
    print("Source: OddsSnapshot table (TheOddsAPI)")
    print("=" * 80)

    # Get games with both opening and closing snapshots
    cur.execute('''
        WITH opening_lines AS (
            SELECT DISTINCT ON (league, "externalGameId")
                league, "externalGameId", "gameDate", "homeTeam", "awayTeam",
                "spreadHome" as open_spread,
                "moneylineHome" as open_ml_home,
                "moneylineAway" as open_ml_away,
                total as open_total,
                "snapshotAt" as open_time
            FROM "OddsSnapshot"
            WHERE "snapshotType" = 'opening'
              AND "spreadHome" IS NOT NULL
            ORDER BY league, "externalGameId", "snapshotAt" ASC
        ),
        closing_lines AS (
            SELECT DISTINCT ON (league, "externalGameId")
                league, "externalGameId",
                "spreadHome" as close_spread,
                "moneylineHome" as close_ml_home,
                "moneylineAway" as close_ml_away,
                total as close_total,
                "snapshotAt" as close_time
            FROM "OddsSnapshot"
            WHERE "snapshotType" IN ('closing', 'periodic')
              AND "spreadHome" IS NOT NULL
            ORDER BY league, "externalGameId", "snapshotAt" DESC
        ),
        game_results AS (
            SELECT
                "externalGameId", "homeScore", "awayScore"
            FROM "SportsGame"
            WHERE "homeScore" IS NOT NULL
        )
        SELECT
            o.league, o."externalGameId", o."gameDate", o."homeTeam", o."awayTeam",
            o.open_spread, c.close_spread,
            o.open_ml_home, c.close_ml_home,
            o.open_ml_away, c.close_ml_away,
            o.open_total, c.close_total,
            o.open_time, c.close_time,
            g."homeScore", g."awayScore"
        FROM opening_lines o
        JOIN closing_lines c ON o.league = c.league AND o."externalGameId" = c."externalGameId"
        LEFT JOIN game_results g ON o."externalGameId" = g."externalGameId"
        WHERE o.open_spread IS NOT NULL
          AND c.close_spread IS NOT NULL
          AND o.open_time < c.close_time
    ''')

    movements = cur.fetchall()
    print(f"\nFound {len(movements)} games with opening + closing lines")

    inserted = 0
    updated = 0

    for row in movements:
        (league, ext_id, game_date, home, away,
         open_spread, close_spread,
         open_ml_home, close_ml_home,
         open_ml_away, close_ml_away,
         open_total, close_total,
         open_time, close_time,
         home_score, away_score) = row

        # SPREAD MOVEMENT
        if open_spread is not None and close_spread is not None:
            spread_move = float(close_spread) - float(open_spread)
            abs_move = abs(spread_move)

            # Determine direction
            if spread_move > 0.5:
                direction = 'TOWARD_AWAY'  # Line moved against home team
            elif spread_move < -0.5:
                direction = 'TOWARD_HOME'  # Line moved toward home team
            else:
                direction = 'STABLE'

            # Steam move = 1+ point movement
            steam_move = abs_move >= 1.0

            # Sharp action inference based on movement
            if spread_move > 0.5:
                sharp = 'AWAY'
            elif spread_move < -0.5:
                sharp = 'HOME'
            else:
                sharp = 'NONE'

            # Cover result (if game completed)
            cover_result = None
            final_score = None
            if home_score is not None and away_score is not None:
                actual_margin = float(home_score) - float(away_score)
                if actual_margin > float(close_spread):
                    cover_result = 'HOME'
                elif actual_margin < float(close_spread):
                    cover_result = 'AWAY'
                else:
                    cover_result = 'PUSH'
                final_score = f"{int(home_score)}-{int(away_score)}"

            # Insert/update spread movement
            try:
                cur.execute('''
                    INSERT INTO "LineMovement" (
                        "gameExternalId", league, "gameDate", "homeTeam", "awayTeam",
                        "marketType", "openLine", "currentLine", "closingLine",
                        "lineMovement", "movementDirection", "steamMove", "reverseLineMove",
                        "sharpAction", "coverResult", "finalScore", "recordedAt", "gameStartTime"
                    ) VALUES (%s, %s, %s, %s, %s, 'SPREAD', %s, %s, %s, %s, %s, %s, FALSE, %s, %s, %s, NOW(), %s)
                    ON CONFLICT ("gameExternalId", "marketType", "recordedAt")
                    DO UPDATE SET
                        "openLine" = EXCLUDED."openLine",
                        "closingLine" = EXCLUDED."closingLine",
                        "lineMovement" = EXCLUDED."lineMovement",
                        "movementDirection" = EXCLUDED."movementDirection",
                        "steamMove" = EXCLUDED."steamMove",
                        "sharpAction" = EXCLUDED."sharpAction",
                        "coverResult" = EXCLUDED."coverResult",
                        "finalScore" = EXCLUDED."finalScore"
                ''', (
                    ext_id, league, game_date, home, away,
                    float(open_spread), float(close_spread), float(close_spread),
                    spread_move, direction, steam_move, sharp,
                    cover_result, final_score, close_time
                ))
                if cur.rowcount > 0:
                    inserted += 1
            except Exception as e:
                print(f"  Error inserting spread movement: {e}")

        # TOTAL MOVEMENT
        if open_total is not None and close_total is not None:
            total_move = float(close_total) - float(open_total)
            abs_total_move = abs(total_move)

            if total_move > 0.5:
                total_direction = 'TOWARD_OVER'
            elif total_move < -0.5:
                total_direction = 'TOWARD_UNDER'
            else:
                total_direction = 'STABLE'

            steam_total = abs_total_move >= 1.5

            # Total result
            total_result = None
            if home_score is not None and away_score is not None:
                actual_total = float(home_score) + float(away_score)
                if actual_total > float(close_total):
                    total_result = 'OVER'
                elif actual_total < float(close_total):
                    total_result = 'UNDER'
                else:
                    total_result = 'PUSH'

            try:
                cur.execute('''
                    INSERT INTO "LineMovement" (
                        "gameExternalId", league, "gameDate", "homeTeam", "awayTeam",
                        "marketType", "openLine", "currentLine", "closingLine",
                        "lineMovement", "movementDirection", "steamMove", "reverseLineMove",
                        "sharpAction", "coverResult", "finalScore", "recordedAt", "gameStartTime"
                    ) VALUES (%s, %s, %s, %s, %s, 'TOTAL', %s, %s, %s, %s, %s, %s, FALSE, %s, %s, %s, NOW(), %s)
                    ON CONFLICT ("gameExternalId", "marketType", "recordedAt")
                    DO UPDATE SET
                        "openLine" = EXCLUDED."openLine",
                        "closingLine" = EXCLUDED."closingLine",
                        "lineMovement" = EXCLUDED."lineMovement",
                        "movementDirection" = EXCLUDED."movementDirection",
                        "steamMove" = EXCLUDED."steamMove",
                        "coverResult" = EXCLUDED."coverResult",
                        "finalScore" = EXCLUDED."finalScore"
                ''', (
                    ext_id, league, game_date, home, away,
                    float(open_total), float(close_total), float(close_total),
                    total_move, total_direction, steam_total,
                    'OVER' if total_move > 0 else 'UNDER' if total_move < 0 else 'NONE',
                    total_result, final_score, close_time
                ))
                if cur.rowcount > 0:
                    inserted += 1
            except Exception as e:
                print(f"  Error inserting total movement: {e}")

    conn.commit()

    # Count movements by type
    cur.execute('''
        SELECT
            league,
            COUNT(*) FILTER (WHERE "steamMove" = TRUE) as steam_moves,
            COUNT(*) FILTER (WHERE "movementDirection" = 'TOWARD_HOME') as toward_home,
            COUNT(*) FILTER (WHERE "movementDirection" = 'TOWARD_AWAY') as toward_away,
            COUNT(*) FILTER (WHERE "movementDirection" = 'STABLE') as stable,
            COUNT(*) as total
        FROM "LineMovement"
        WHERE "marketType" = 'SPREAD'
        GROUP BY league
        ORDER BY league
    ''')

    print("\n" + "=" * 80)
    print("REAL LINE MOVEMENT SUMMARY (Spread)")
    print("=" * 80)
    print(f"{'League':<10} {'Steam':<8} {'→Home':<8} {'→Away':<8} {'Stable':<8} {'Total':<8}")
    print("-" * 50)

    for row in cur.fetchall():
        league, steam, home, away, stable, total = row
        print(f"{league.upper():<10} {steam or 0:<8} {home or 0:<8} {away or 0:<8} {stable or 0:<8} {total:<8}")

    # Total movements with results
    cur.execute('''
        SELECT COUNT(*) FROM "LineMovement"
        WHERE "coverResult" IS NOT NULL
    ''')
    with_results = cur.fetchone()[0]

    cur.execute('SELECT COUNT(*) FROM "LineMovement"')
    total_movements = cur.fetchone()[0]

    print("\n" + "=" * 80)
    print("RESULTS")
    print("=" * 80)
    print(f"  Games processed: {len(movements)}")
    print(f"  Line movements inserted: {inserted}")
    print(f"  Total LineMovement records: {total_movements}")
    print(f"  With game results: {with_results}")
    print(f"  Source: REAL (OddsSnapshot from TheOddsAPI)")
    print("=" * 80)

    cur.close()
    conn.close()

    return {
        'games_processed': len(movements),
        'movements_inserted': inserted,
        'total_records': total_movements,
        'with_results': with_results
    }


def main():
    try:
        result = compute_real_line_movement()
        print(f"\n✅ Real line movement computation complete")
    except Exception as e:
        print(f"ERROR: {e}")
        import traceback
        traceback.print_exc()


if __name__ == '__main__':
    main()