#!/usr/bin/env python3
"""
Capture Player Prop Line Snapshots
Tracks prop line movement over time for CLV and softness analysis.
Unlocks: Q6 (prop softness timing)

Run: Every 2-4 hours via cron
"""
import psycopg2
import os
from datetime import datetime, timezone, timedelta

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 capture_snapshots():
    print("=" * 60)
    print("CAPTURE PROP LINE SNAPSHOTS")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

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

    now = datetime.now(timezone.utc)

    # Get all props for upcoming games (next 48 hours)
    cur.execute('''
        SELECT
            p.id,
            p.league,
            p."gameId",
            p."playerExternalId",
            p."propType",
            p."lineValue",
            p."oddsAmerican",
            p.vendor,
            g."gameDate"
        FROM "PlayerPropLine" p
        LEFT JOIN "SportsGame" g ON p."gameId" = g.id
        WHERE g."gameDate" > NOW()
          AND g."gameDate" < NOW() + INTERVAL '48 hours'
          AND p."lineValue" IS NOT NULL
    ''')

    props = cur.fetchall()
    print(f"\nFound {len(props)} props for upcoming games")

    snapshots_created = 0
    movements_detected = 0

    for prop in props:
        prop_id, league, game_id, player_id, prop_type, line_value, odds, vendor, game_date = prop

        # Calculate hours to game
        if game_date:
            hours_to_game = (game_date.replace(tzinfo=timezone.utc) - now).total_seconds() / 3600
        else:
            hours_to_game = None

        # Check if we already have a recent snapshot (within 1 hour)
        cur.execute('''
            SELECT "lineValue", "oddsAmerican"
            FROM "PlayerPropSnapshot"
            WHERE "propLineId" = %s
            ORDER BY "snapshotAt" DESC
            LIMIT 1
        ''', (prop_id,))

        last_snapshot = cur.fetchone()

        # Only create snapshot if line changed or no recent snapshot
        should_snapshot = False
        if not last_snapshot:
            should_snapshot = True
        else:
            last_line, last_odds = last_snapshot
            if last_line != line_value or last_odds != odds:
                should_snapshot = True
                movements_detected += 1

                # Update lineMovement on PlayerPropLine
                if last_line and line_value:
                    movement = float(line_value) - float(last_line)
                    cur.execute('''
                        UPDATE "PlayerPropLine"
                        SET "lineMovement" = COALESCE("lineMovement", 0) + %s,
                            "updatedAt" = NOW()
                        WHERE id = %s
                    ''', (movement, prop_id))

        if should_snapshot:
            cur.execute('''
                INSERT INTO "PlayerPropSnapshot"
                ("propLineId", league, "gameId", "playerExternalId", "propType",
                 "lineValue", "oddsAmerican", "hoursToGame", source, "snapshotAt")
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
            ''', (prop_id, league, game_id, player_id, prop_type,
                  line_value, odds, hours_to_game, vendor))
            snapshots_created += 1

    conn.commit()

    # Report by league
    print("\nSnapshots by league:")
    cur.execute('''
        SELECT league, COUNT(*)
        FROM "PlayerPropSnapshot"
        WHERE "snapshotAt" > NOW() - INTERVAL '1 hour'
        GROUP BY league
        ORDER BY COUNT(*) DESC
    ''')
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]}")

    cur.close()
    conn.close()

    print(f"\n" + "=" * 60)
    print(f"Snapshots created: {snapshots_created}")
    print(f"Line movements detected: {movements_detected}")
    print("=" * 60)

    return snapshots_created, movements_detected


if __name__ == '__main__':
    capture_snapshots()