#!/usr/bin/env python3
"""
Ingest Kaggle WNBA odds into SportsDB.SportsGame.
Matches by team names + date, updates odds fields where NULL.
"""

import csv
import os
import re
from datetime import datetime, timedelta
from pathlib import Path

import psycopg2

DATA_DIR = Path('/var/www/html/eventheodds/data/kaggle')
WNBA_FILES = [
    DATA_DIR / 'wnba_main_lines.csv',
    DATA_DIR / 'wnba_main_lines_history.csv',
    DATA_DIR / 'zachht__wnba-odds-history' / 'wnba_main_lines.csv',
    DATA_DIR / 'zachht__wnba-odds-history' / 'wnba_main_lines_history.csv',
]

# Team name mapping
TEAM_MAP = {
    'las vegas aces': 'LVA',
    'aces': 'LVA',
    'phoenix mercury': 'PHO',
    'mercury': 'PHO',
    'seattle storm': 'SEA',
    'storm': 'SEA',
    'new york liberty': 'NY',
    'liberty': 'NY',
    'minnesota lynx': 'MIN',
    'lynx': 'MIN',
    'connecticut sun': 'CONN',
    'sun': 'CONN',
    'chicago sky': 'CHI',
    'sky': 'CHI',
    'indiana fever': 'IND',
    'fever': 'IND',
    'washington mystics': 'WAS',
    'mystics': 'WAS',
    'atlanta dream': 'ATL',
    'dream': 'ATL',
    'los angeles sparks': 'LA',
    'sparks': 'LA',
    'dallas wings': 'DAL',
    'wings': 'DAL',
}

def get_db_connection():
    url = os.environ.get('SPORTS_DATABASE_URL')
    if not url:
        raise RuntimeError("SPORTS_DATABASE_URL not set")
    url_clean = url.split('?')[0]
    m = re.match(r'postgresql://([^:]+):([^@]+)@([^:]+):(\d+)/(.+)', url_clean)
    if not m:
        raise RuntimeError(f"Cannot parse DB URL: {url}")
    return psycopg2.connect(
        user=m.group(1),
        password=m.group(2),
        host=m.group(3),
        port=int(m.group(4)),
        database=m.group(5)
    )

def decimal_to_american(dec):
    if dec is None or dec <= 1:
        return None
    if dec >= 2.0:
        return int((dec - 1) * 100)
    else:
        return int(-100 / (dec - 1))

def parse_float(s):
    if not s or str(s).strip() == '':
        return None
    try:
        return float(s)
    except:
        return None

def normalize_team(name):
    n = name.lower().strip()
    return TEAM_MAP.get(n, n.upper()[:3])

def main():
    conn = get_db_connection()
    cur = conn.cursor()

    all_rows = []
    for f in WNBA_FILES:
        if f.exists():
            with open(f, newline='', encoding='utf-8') as csvf:
                reader = csv.DictReader(csvf)
                for row in reader:
                    all_rows.append(row)
            print(f"Loaded {f.name}")

    print(f"Total WNBA odds rows: {len(all_rows)}")

    # Dedupe
    seen = set()
    unique = []
    for row in all_rows:
        team1 = row.get('team1', '').strip()
        team2 = row.get('team2', '').strip()
        ts = row.get('timestamp', '')[:10]
        key = (team1, team2, ts)
        if key not in seen:
            seen.add(key)
            unique.append(row)

    print(f"Unique games: {len(unique)}")

    matched = 0
    updated = 0

    for row in unique:
        team1 = row.get('team1', '').strip()
        team2 = row.get('team2', '').strip()
        timestamp = row.get('timestamp', '')

        if not team1 or not team2 or not timestamp:
            continue

        # Parse odds
        spread1 = parse_float(row.get('team1_spread'))
        spread2 = parse_float(row.get('team2_spread'))
        total = parse_float(row.get('over_total'))
        ml1_dec = parse_float(row.get('team1_moneyline'))
        ml2_dec = parse_float(row.get('team2_moneyline'))

        ml1 = decimal_to_american(ml1_dec)
        ml2 = decimal_to_american(ml2_dec)

        try:
            game_date = datetime.strptime(timestamp[:10], '%Y-%m-%d')
        except:
            continue

        # Normalize team names
        team1_norm = normalize_team(team1)
        team2_norm = normalize_team(team2)

        date_start = (game_date - timedelta(days=1)).strftime('%Y-%m-%d')
        date_end = (game_date + timedelta(days=1)).strftime('%Y-%m-%d')

        # Try to find game
        cur.execute("""
            SELECT id, "homeTeam", "awayTeam", "moneylineHome", "spreadHome", total
            FROM "SportsGame"
            WHERE league = 'wnba'
              AND "gameDate"::date >= %s AND "gameDate"::date <= %s
              AND (
                (UPPER("homeTeam") = %s AND UPPER("awayTeam") = %s)
                OR (UPPER("homeTeam") = %s AND UPPER("awayTeam") = %s)
                OR ("homeTeam" ILIKE %s AND "awayTeam" ILIKE %s)
                OR ("homeTeam" ILIKE %s AND "awayTeam" ILIKE %s)
              )
            LIMIT 1
        """, (
            date_start, date_end,
            team1_norm, team2_norm,
            team2_norm, team1_norm,
            f'%{team1[:4]}%', f'%{team2[:4]}%',
            f'%{team2[:4]}%', f'%{team1[:4]}%'
        ))

        result = cur.fetchone()
        if result:
            matched += 1
            game_id, home_team, away_team, existing_ml, existing_spread, existing_total = result

            # Determine orientation
            if team1_norm == home_team.upper() or team1[:4].lower() in home_team.lower():
                final_ml_home, final_ml_away = ml1, ml2
                final_spread_home, final_spread_away = spread1, spread2
            else:
                final_ml_home, final_ml_away = ml2, ml1
                final_spread_home, final_spread_away = spread2, spread1

            # Update only NULL fields
            updates = []
            params = []

            if existing_ml is None and final_ml_home is not None:
                updates.append('"moneylineHome" = %s')
                params.append(final_ml_home)
                updates.append('"moneylineAway" = %s')
                params.append(final_ml_away)
            if existing_spread is None and final_spread_home is not None:
                updates.append('"spreadHome" = %s')
                params.append(final_spread_home)
                updates.append('"spreadAway" = %s')
                params.append(final_spread_away)
            if existing_total is None and total is not None:
                updates.append('total = %s')
                params.append(total)

            if updates:
                updates.append('"oddsSource" = %s')
                params.append('kaggle_wnba')
                updates.append('"oddsUpdatedAt" = NOW()')
                params.append(game_id)

                sql = f'UPDATE "SportsGame" SET {", ".join(updates)} WHERE id = %s'
                cur.execute(sql, params)
                updated += 1

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

    print(f"Matched: {matched}, Updated: {updated}")

if __name__ == '__main__':
    main()