#!/usr/bin/env python3
"""
Extract First-Half Lines from SportsGame raw JSON
Extracts 1H spreads and totals from SportsGameOdds data for half efficiency analysis.

Run: After SGO syncs
"""
import psycopg2
import json
import os
import re
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 ensure_half_lines_table(cur):
    """Create GameHalfLine table if needed"""
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "GameHalfLine" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(50) NOT NULL,
            "gameId" BIGINT NOT NULL,
            "gameDate" TIMESTAMP,
            "homeTeam" VARCHAR(20),
            "awayTeam" VARCHAR(20),
            period VARCHAR(10) NOT NULL,  -- '1h', '1q', '2h', etc.
            market VARCHAR(50) NOT NULL,  -- 'spread', 'total', 'ml'
            side VARCHAR(20),  -- 'home', 'away', 'over', 'under'
            "lineValue" FLOAT,
            "bookOdds" INT,
            "openLineValue" FLOAT,
            "openBookOdds" INT,
            "closeLineValue" FLOAT,
            "closeBookOdds" INT,
            bookmaker VARCHAR(50),
            "createdAt" TIMESTAMP DEFAULT NOW(),
            "updatedAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, "gameId", period, market, side)
        )
    ''')
    cur.execute('CREATE INDEX IF NOT EXISTS "GameHalfLine_league_game_idx" ON "GameHalfLine" (league, "gameId")')
    cur.execute('CREATE INDEX IF NOT EXISTS "GameHalfLine_period_market_idx" ON "GameHalfLine" (period, market)')


def parse_american_odds(odds_str):
    """Convert odds string to integer"""
    if not odds_str:
        return None
    try:
        # Remove + sign if present
        s = str(odds_str).replace('+', '')
        return int(float(s))
    except:
        return None


def extract_half_lines():
    """Extract first-half lines from raw JSON"""
    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("EXTRACT FIRST-HALF LINES")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    ensure_half_lines_table(cur)
    conn.commit()

    # Get games with SGO raw data
    cur.execute('''
        SELECT id, league, "gameDate", "homeTeam", "awayTeam", raw
        FROM "SportsGame"
        WHERE raw IS NOT NULL
          AND raw::text LIKE '%1h%'
        ORDER BY "gameDate" DESC
        LIMIT 5000
    ''')

    games = cur.fetchall()
    print(f"Found {len(games)} games with potential 1H data")

    total_inserted = 0
    games_with_lines = 0

    for game_id, league, game_date, home_team, away_team, raw_json in games:
        if not raw_json:
            continue

        sgo = raw_json.get('sgo', {})
        odds = sgo.get('odds', {})

        if not odds:
            continue

        lines_for_game = []

        for odd_key, odd_data in odds.items():
            # Parse the odd key: {stat}-{entity}-{period}-{betType}-{side}
            # e.g., points-all-1h-ou-over, points-home-1h-spread-home
            parts = odd_key.split('-')
            if len(parts) < 4:
                continue

            # Check for period indicators
            period = None
            for p in parts:
                if p in ['1h', '2h', '1q', '2q', '3q', '4q']:
                    period = p
                    break

            if not period:
                continue

            # Determine market type and side
            market = None
            side = None

            if 'ou' in parts or 'total' in odd_key.lower():
                market = 'total'
                if 'over' in parts:
                    side = 'over'
                elif 'under' in parts:
                    side = 'under'
            elif 'spread' in parts or 'handicap' in odd_key.lower():
                market = 'spread'
                if 'home' in parts:
                    side = 'home'
                elif 'away' in parts:
                    side = 'away'
            elif 'ml' in parts or 'moneyline' in odd_key.lower():
                market = 'ml'
                if 'home' in parts:
                    side = 'home'
                elif 'away' in parts:
                    side = 'away'

            if not market:
                continue

            # Extract values
            line_value = odd_data.get('line') or odd_data.get('handicap')
            if line_value is None and market == 'total':
                # For totals, line might be in the key
                pass

            book_odds = parse_american_odds(odd_data.get('bookOdds'))
            open_book_odds = parse_american_odds(odd_data.get('openBookOdds'))
            close_book_odds = parse_american_odds(odd_data.get('closeBookOdds'))

            # Get bookmaker from byBookmaker if available
            bookmaker = None
            by_book = odd_data.get('byBookmaker', {})
            if by_book:
                bookmaker = list(by_book.keys())[0] if by_book else None

            lines_for_game.append({
                'period': period,
                'market': market,
                'side': side,
                'line_value': float(line_value) if line_value else None,
                'book_odds': book_odds,
                'open_book_odds': open_book_odds,
                'close_book_odds': close_book_odds,
                'bookmaker': bookmaker
            })

        if lines_for_game:
            games_with_lines += 1

            for line in lines_for_game:
                try:
                    cur.execute('''
                        INSERT INTO "GameHalfLine" (
                            league, "gameId", "gameDate", "homeTeam", "awayTeam",
                            period, market, side, "lineValue", "bookOdds",
                            "openLineValue", "openBookOdds", "closeLineValue", "closeBookOdds",
                            bookmaker
                        )
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                        ON CONFLICT (league, "gameId", period, market, side) DO UPDATE SET
                            "lineValue" = COALESCE(EXCLUDED."lineValue", "GameHalfLine"."lineValue"),
                            "bookOdds" = COALESCE(EXCLUDED."bookOdds", "GameHalfLine"."bookOdds"),
                            "openLineValue" = COALESCE(EXCLUDED."openLineValue", "GameHalfLine"."openLineValue"),
                            "openBookOdds" = COALESCE(EXCLUDED."openBookOdds", "GameHalfLine"."openBookOdds"),
                            "closeLineValue" = COALESCE(EXCLUDED."closeLineValue", "GameHalfLine"."closeLineValue"),
                            "closeBookOdds" = COALESCE(EXCLUDED."closeBookOdds", "GameHalfLine"."closeBookOdds"),
                            "updatedAt" = NOW()
                    ''', (
                        league, game_id, game_date, home_team, away_team,
                        line['period'], line['market'], line['side'],
                        line['line_value'], line['book_odds'],
                        line['line_value'], line['open_book_odds'],
                        line['line_value'], line['close_book_odds'],
                        line['bookmaker']
                    ))
                    total_inserted += 1
                except Exception as e:
                    pass

        if games_with_lines % 100 == 0 and games_with_lines > 0:
            print(f"  Processed {games_with_lines} games with 1H lines...")
            conn.commit()

    conn.commit()

    # Summary
    cur.execute('''
        SELECT period, market, COUNT(*)
        FROM "GameHalfLine"
        GROUP BY period, market
        ORDER BY period, market
    ''')
    print(f"\n1H/1Q Line Summary:")
    for row in cur.fetchall():
        print(f"  {row[0]} {row[1]}: {row[2]} lines")

    cur.close()
    conn.close()

    print(f"\n{'='*60}")
    print(f"TOTAL: {total_inserted} lines from {games_with_lines} games")
    print("=" * 60)

    return {'lines': total_inserted, 'games': games_with_lines}


def main():
    try:
        result = extract_half_lines()
        print(f"\nExtraction complete: {result['lines']} lines from {result['games']} games")
    except Exception as e:
        print(f"ERROR: {e}")
        import traceback
        traceback.print_exc()


if __name__ == '__main__':
    main()