#!/usr/bin/env python3
"""
Track Player Prop Line Movements
Captures opening lines and calculates movement for CLV analysis.

Run: Every 4 hours via cron
"""
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 track_prop_movements():
    """Track opening lines and movements for player props"""
    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("TRACK PROP LINE MOVEMENTS")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    # 1. Set opening lines for props that don't have one yet
    cur.execute('''
        UPDATE "PlayerPropLine"
        SET "openingLineValue" = "lineValue",
            "openingOdds" = "oddsAmerican",
            "firstSeenAt" = "createdAt"
        WHERE "openingLineValue" IS NULL
          AND "lineValue" IS NOT NULL
    ''')
    opening_set = cur.rowcount
    print(f"Set opening lines for {opening_set} props")

    # 2. Calculate line movement for props with opening lines
    cur.execute('''
        UPDATE "PlayerPropLine"
        SET "lineMovement" = "lineValue" - "openingLineValue"
        WHERE "openingLineValue" IS NOT NULL
          AND "lineMovement" IS NULL
          AND "lineValue" != "openingLineValue"
    ''')
    movement_calc = cur.rowcount
    print(f"Calculated movement for {movement_calc} props")

    conn.commit()

    # 3. Show stats on line movements
    cur.execute('''
        SELECT
            league,
            COUNT(*) as props,
            COUNT("openingLineValue") as with_opening,
            COUNT("lineMovement") as with_movement,
            ROUND(AVG(ABS("lineMovement"))::numeric, 2) as avg_move
        FROM "PlayerPropLine"
        GROUP BY league
        ORDER BY props DESC
    ''')

    print("\nProp Movement Stats by League:")
    for row in cur.fetchall():
        league, props, with_opening, with_movement, avg_move = row
        print(f"  {league}: {props} props, {with_opening} with opening, {with_movement} moved (avg: {avg_move})")

    # 4. Show biggest movers
    cur.execute('''
        SELECT
            league, "propType", "lineValue", "openingLineValue", "lineMovement"
        FROM "PlayerPropLine"
        WHERE ABS("lineMovement") > 0
        ORDER BY ABS("lineMovement") DESC
        LIMIT 10
    ''')

    print("\nTop 10 Line Movements:")
    for row in cur.fetchall():
        league, prop_type, current, opening, movement = row
        direction = "UP" if movement > 0 else "DOWN"
        print(f"  {league}: {prop_type[:40]} {opening} -> {current} ({direction} {abs(movement)})")

    conn.close()
    print("\n" + "=" * 60)
    print("Movement tracking complete")


if __name__ == '__main__':
    track_prop_movements()