#!/usr/bin/env python3
"""
Link Player Props to Actual Stats
Enhances PlayerMapping with metric source IDs and creates prop-vs-actual linkage.
Enables prop backtesting by matching prop lines to game outcomes.

Run: Weekly after player data syncs
"""
import psycopg2
import os
from datetime import datetime, timezone
from collections import defaultdict


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]


# Map prop types to stat keys (normalize naming differences)
PROP_TO_STAT_MAP = {
    # NBA
    'points': ['points', 'pts'],
    'assists': ['assists', 'ast'],
    'rebounds': ['rebounds', 'reb', 'total_rebounds'],
    'threes': ['threes', '3pm', 'fg3m', 'three_pointers_made'],
    'steals': ['steals', 'stl'],
    'blocks': ['blocks', 'blk'],
    'turnovers': ['turnovers', 'tov'],
    'pts_rebs_asts': ['pts_rebs_asts', 'pra'],
    'pts_rebs': ['pts_rebs', 'pr'],
    'pts_asts': ['pts_asts', 'pa'],
    'rebs_asts': ['rebs_asts', 'ra'],
    # NFL
    'passing_yards': ['passing_yards', 'pass_yds', 'passingyards'],
    'rushing_yards': ['rushing_yards', 'rush_yds', 'rushingyards'],
    'receiving_yards': ['receiving_yards', 'rec_yds', 'receivingyards'],
    'passing_touchdowns': ['passing_touchdowns', 'pass_td', 'passingtds'],
    'rushing_touchdowns': ['rushing_touchdowns', 'rush_td', 'rushingtds'],
    'receptions': ['receptions', 'rec'],
    'interceptions': ['interceptions', 'int'],
    # NHL
    'goals': ['goals'],
    'shots': ['shots', 'shots_on_goal', 'sog'],
    'saves': ['saves', 'sv'],
    'powerplay_points': ['powerplay_points', 'ppp'],
    # MLB
    'hits': ['hits', 'h'],
    'runs': ['runs', 'r'],
    'rbis': ['rbis', 'rbi'],
    'home_runs': ['home_runs', 'hr'],
    'strikeouts': ['strikeouts', 'k', 'so'],
    'walks': ['walks', 'bb'],
    'total_bases': ['total_bases', 'tb'],
}


def normalize_prop_type(prop_type):
    """Extract base stat name from prop type string"""
    # Remove common suffixes/prefixes
    pt = prop_type.lower()
    for suffix in ['-game-ou-over', '-game-ou-under', '-1h-ou-over', '-1h-ou-under',
                   '-1q-ou-over', '-1q-ou-under', '_over', '_under', '-over', '-under']:
        pt = pt.replace(suffix, '')

    # Extract player name prefix pattern (PLAYER_NAME_1_LEAGUE)
    parts = pt.split('-')
    if len(parts) > 0:
        # Last part before -game-ou etc is usually the stat
        for part in reversed(parts):
            if part in PROP_TO_STAT_MAP:
                return part

    return pt


def link_props_to_actuals():
    """Create linkages between props and actual stats"""
    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("LINK PROPS TO ACTUALS")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    # Step 1: Update PlayerMapping with metric source IDs based on name matching
    print("\n1. Updating PlayerMapping with metric source IDs...")

    cur.execute('''
        UPDATE "PlayerMapping" pm
        SET "bdlId" = COALESCE(pm."bdlId", (
            SELECT DISTINCT g."playerExternalId"
            FROM "PlayerGameMetric" g
            WHERE LOWER(g."playerName") = LOWER(pm."playerName")
              AND g.league = pm.league
            LIMIT 1
        ))
        WHERE pm."bdlId" IS NULL
          AND EXISTS (
            SELECT 1 FROM "PlayerGameMetric" g
            WHERE LOWER(g."playerName") = LOWER(pm."playerName")
              AND g.league = pm.league
          )
    ''')
    mappings_updated = cur.rowcount
    print(f"   Updated {mappings_updated} player mappings with metric IDs")

    # Step 2: Get counts of linkable props
    cur.execute('''
        SELECT pm.league, COUNT(DISTINCT pp.id) as props_linkable
        FROM "PlayerPropLine" pp
        JOIN "PlayerMapping" pm ON pp."playerExternalId" = pm."sgoId" AND pp.league = pm.league
        WHERE pm."bdlId" IS NOT NULL OR pm."espnId" IS NOT NULL
        GROUP BY pm.league
    ''')
    print("\n2. Linkable props by league:")
    for row in cur.fetchall():
        print(f"   {row[0]}: {row[1]} props can be linked to actuals")

    # Step 3: Create a sample backtest view (prop vs actual comparison)
    # Uses name+date matching since player IDs come from different sources
    print("\n3. Creating prop backtest materialized view...")

    # Drop and recreate
    cur.execute('DROP MATERIALIZED VIEW IF EXISTS "PropBacktest"')

    cur.execute('''
        CREATE MATERIALIZED VIEW "PropBacktest" AS
        SELECT
            pp.id as prop_id,
            pp.league,
            pp."gameId",
            pp."propType",
            pp."lineValue" as line,
            pp."oddsAmerican" as odds,
            pm."playerName",
            pm."sgoId",
            g."playerExternalId" as metric_player_id,
            sg."gameDate",
            g."statKey",
            g.value as actual_value,
            CASE
                WHEN g.value > pp."lineValue" THEN 'over'
                WHEN g.value < pp."lineValue" THEN 'under'
                ELSE 'push'
            END as result,
            CASE
                WHEN pp."propType" LIKE '%over%' AND g.value > pp."lineValue" THEN true
                WHEN pp."propType" LIKE '%under%' AND g.value < pp."lineValue" THEN true
                WHEN pp."propType" LIKE '%over%' AND g.value <= pp."lineValue" THEN false
                WHEN pp."propType" LIKE '%under%' AND g.value >= pp."lineValue" THEN false
                ELSE NULL
            END as bet_won
        FROM "PlayerPropLine" pp
        JOIN "PlayerMapping" pm ON pp."playerExternalId" = pm."sgoId" AND pp.league = pm.league
        JOIN "SportsGame" sg ON pp."gameId" = sg.id
        JOIN "PlayerGameMetric" g ON
            LOWER(g."playerName") = LOWER(pm."playerName")
            AND g.league = pm.league
            AND g."gameDate"::date = sg."gameDate"::date
        WHERE sg."gameDate" < NOW()
    ''')

    # Create index on the view
    cur.execute('CREATE INDEX IF NOT EXISTS "PropBacktest_league_idx" ON "PropBacktest" (league)')
    cur.execute('CREATE INDEX IF NOT EXISTS "PropBacktest_result_idx" ON "PropBacktest" (result)')

    # Get view count
    cur.execute('SELECT COUNT(*) FROM "PropBacktest"')
    view_count = cur.fetchone()[0]
    print(f"   Created PropBacktest view with {view_count} linkable records")

    # Step 4: Generate summary stats
    if view_count > 0:
        print("\n4. Backtest Summary:")
        cur.execute('''
            SELECT league, result, COUNT(*)
            FROM "PropBacktest"
            GROUP BY league, result
            ORDER BY league, result
        ''')
        results_by_league = defaultdict(lambda: {'over': 0, 'under': 0, 'push': 0})
        for row in cur.fetchall():
            results_by_league[row[0]][row[1]] = row[2]

        for league, results in results_by_league.items():
            total = sum(results.values())
            over_pct = results['over'] / total * 100 if total > 0 else 0
            print(f"   {league}: {total} props - Over:{results['over']} ({over_pct:.1f}%), Under:{results['under']}, Push:{results['push']}")

    conn.commit()
    cur.close()
    conn.close()

    print("\n" + "=" * 60)
    print("Linkage complete!")
    print("=" * 60)

    return {'mappings_updated': mappings_updated, 'backtest_records': view_count}


def main():
    try:
        result = link_props_to_actuals()
        print(f"\nSummary: {result['mappings_updated']} mappings updated, {result['backtest_records']} backtest records")
    except Exception as e:
        print(f"ERROR: {e}")
        import traceback
        traceback.print_exc()


if __name__ == '__main__':
    main()