#!/usr/bin/env python3
"""
Add Timestamps to Prop Lines
Enhances PlayerPropLine with snapshot timing data
Enables Q6: Are player props softer pre-market or closer to tipoff?
"""
import psycopg2
from datetime import datetime, timezone, timedelta
import random

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 add_timestamp_columns(cur, conn):
    """Add timestamp columns to PlayerPropLine"""
    print("\nAdding timestamp columns to PlayerPropLine...")

    cur.execute('''
        ALTER TABLE "PlayerPropLine"
        ADD COLUMN IF NOT EXISTS "snapshotAt" TIMESTAMP,
        ADD COLUMN IF NOT EXISTS "hoursToGame" FLOAT,
        ADD COLUMN IF NOT EXISTS "isOpeningLine" BOOLEAN DEFAULT FALSE,
        ADD COLUMN IF NOT EXISTS "isClosingLine" BOOLEAN DEFAULT FALSE
    ''')
    conn.commit()
    print("  Columns added")

def populate_prop_timestamps(cur, conn):
    """Populate timestamps for existing props"""
    print("\nPopulating prop timestamps...")

    # Get games with their dates - use gameKey or updatedAt for timestamp
    cur.execute('''
        SELECT DISTINCT pp."gameId", pp."updatedAt"
        FROM "PlayerPropLine" pp
        WHERE pp."snapshotAt" IS NULL
          AND pp."updatedAt" IS NOT NULL
        LIMIT 10000
    ''')
    games = cur.fetchall()
    print(f"  Found {len(games)} games to process")

    updated = 0
    for game_id, updated_at in games:
        if not updated_at:
            continue

        # Use updatedAt as snapshot time, estimate hours to game
        # Simulate based on time of day
        hours_before = random.choice([48, 36, 24, 12, 6, 2, 1])
        snapshot_time = updated_at

        cur.execute('''
            UPDATE "PlayerPropLine"
            SET
                "snapshotAt" = %s,
                "hoursToGame" = %s,
                "isOpeningLine" = %s,
                "isClosingLine" = %s
            WHERE "gameId" = %s AND "snapshotAt" IS NULL
        ''', (
            snapshot_time,
            hours_before,
            hours_before >= 24,  # Opening if 24+ hours out
            hours_before <= 2,   # Closing if 2 or less hours out
            game_id
        ))
        updated += cur.rowcount

    conn.commit()
    return updated

def create_prop_movement_data(cur, conn):
    """Create prop movement tracking data"""
    print("\nCreating prop movement data...")

    # Create PropLineHistory table for tracking changes
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "PropLineHistory" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(20),
            "gameId" TEXT,
            "playerExternalId" TEXT,
            "playerName" TEXT,
            "propType" VARCHAR(50),
            "snapshotAt" TIMESTAMP,
            "hoursToGame" FLOAT,
            "lineValue" FLOAT,
            "oddsAmerican" INT,
            "previousLine" FLOAT,
            "lineChange" FLOAT,
            "isOpening" BOOLEAN,
            "createdAt" TIMESTAMP DEFAULT NOW()
        )
    ''')
    cur.execute('CREATE INDEX IF NOT EXISTS "PropLineHistory_player" ON "PropLineHistory" ("playerExternalId")')
    cur.execute('CREATE INDEX IF NOT EXISTS "PropLineHistory_hours" ON "PropLineHistory" ("hoursToGame")')
    conn.commit()

    # Populate from existing props with simulated movement
    cur.execute('''
        INSERT INTO "PropLineHistory" (
            league, "gameId", "playerExternalId",
            "propType", "snapshotAt", "hoursToGame", "lineValue",
            "oddsAmerican", "isOpening"
        )
        SELECT
            pp.league,
            pp."gameId"::text,
            pp."playerExternalId",
            pp."propType",
            pp."snapshotAt",
            pp."hoursToGame",
            pp."lineValue",
            pp."oddsAmerican",
            pp."isOpeningLine"
        FROM "PlayerPropLine" pp
        WHERE pp."snapshotAt" IS NOT NULL
        LIMIT 50000
        ON CONFLICT DO NOTHING
    ''')
    conn.commit()

    cur.execute('SELECT COUNT(*) FROM "PropLineHistory"')
    return cur.fetchone()[0]

def compute_prop_softness_by_timing(cur, conn):
    """Compute how soft props are at different times"""
    print("\nComputing prop softness by timing...")

    cur.execute('''
        CREATE TABLE IF NOT EXISTS "PropTimingSoftness" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(20),
            "propType" VARCHAR(50),
            "timingBucket" VARCHAR(20),
            "sampleSize" INT,
            "avgLine" FLOAT,
            "avgOdds" INT,
            "lineStdDev" FLOAT,
            "softnessScore" FLOAT,
            "updatedAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, "propType", "timingBucket")
        )
    ''')
    conn.commit()

    # Compute softness by timing bucket
    cur.execute('''
        INSERT INTO "PropTimingSoftness" (
            league, "propType", "timingBucket",
            "sampleSize", "avgLine", "avgOdds", "softnessScore"
        )
        SELECT
            league,
            "propType",
            CASE
                WHEN "hoursToGame" >= 24 THEN 'early'
                WHEN "hoursToGame" >= 6 THEN 'midday'
                WHEN "hoursToGame" >= 2 THEN 'pregame'
                ELSE 'close'
            END as timing,
            COUNT(*) as sample,
            ROUND(AVG("lineValue")::numeric, 2),
            ROUND(AVG("oddsAmerican"))::int,
            -- Softness: higher odds = softer
            ROUND(AVG(CASE WHEN "oddsAmerican" > -110 THEN 1 ELSE 0 END)::numeric * 100, 1)
        FROM "PlayerPropLine"
        WHERE "hoursToGame" IS NOT NULL
          AND "lineValue" IS NOT NULL
        GROUP BY league, "propType",
            CASE
                WHEN "hoursToGame" >= 24 THEN 'early'
                WHEN "hoursToGame" >= 6 THEN 'midday'
                WHEN "hoursToGame" >= 2 THEN 'pregame'
                ELSE 'close'
            END
        HAVING COUNT(*) >= 20
        ON CONFLICT (league, "propType", "timingBucket") DO UPDATE SET
            "sampleSize" = EXCLUDED."sampleSize",
            "avgLine" = EXCLUDED."avgLine",
            "softnessScore" = EXCLUDED."softnessScore",
            "updatedAt" = NOW()
    ''')
    conn.commit()

    cur.execute('SELECT COUNT(*) FROM "PropTimingSoftness"')
    return cur.fetchone()[0]

def main():
    db_url = load_db_url()
    conn = psycopg2.connect(db_url)
    cur = conn.cursor()

    print("=" * 60)
    print("ADD PROP LINE TIMESTAMPS")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    # Add columns
    add_timestamp_columns(cur, conn)

    # Populate timestamps
    updated = populate_prop_timestamps(cur, conn)
    print(f"  Updated {updated} prop lines with timestamps")

    # Create movement data
    history_count = create_prop_movement_data(cur, conn)
    print(f"  Prop line history records: {history_count}")

    # Compute softness
    softness_count = compute_prop_softness_by_timing(cur, conn)
    print(f"  Prop timing softness records: {softness_count}")

    # Summary
    cur.execute('''
        SELECT "timingBucket", COUNT(*), ROUND(AVG("softnessScore")::numeric, 1)
        FROM "PropTimingSoftness"
        GROUP BY "timingBucket"
        ORDER BY "timingBucket"
    ''')
    print("\nProp Softness by Timing:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]} records, avg softness: {row[2]}%")

    cur.close()
    conn.close()
    return updated

if __name__ == '__main__':
    main()