#!/usr/bin/env python3
"""Import European soccer data from football-data.co.uk into our database."""
import csv
import json
from pathlib import Path
from datetime import datetime

DATA_DIR = Path("/var/www/html/eventheodds/data/betting")
KAGGLE_DIR = Path("/var/www/html/eventheodds/data/kaggle")

LEAGUES = {
    "epl": "EPL",
    "laliga": "LaLiga", 
    "seriea": "SerieA",
    "bundesliga": "Bundesliga",
    "ligue1": "Ligue1"
}

def decimal_to_american(decimal_odds):
    """Convert decimal odds to American format."""
    if decimal_odds is None or decimal_odds <= 1:
        return None
    if decimal_odds >= 2.0:
        return int((decimal_odds - 1) * 100)
    else:
        return int(-100 / (decimal_odds - 1))

def import_league(league_prefix, sport_name):
    """Import a league from football-data.co.uk CSV files."""
    output_file = DATA_DIR / f"{league_prefix}_historical.json"
    
    # Load existing
    existing = []
    if output_file.exists():
        with open(output_file) as f:
            existing = json.load(f)
    
    existing_keys = set()
    for g in existing:
        key = (g.get("home_team","").lower(), g.get("away_team","").lower(), g.get("date","")[:10])
        existing_keys.add(key)
    
    print(f"\n{sport_name}: {len(existing)} existing records")
    
    # Find and import CSV files
    new_games = []
    for csv_file in sorted(KAGGLE_DIR.glob(f"{league_prefix}_*.csv")):
        with open(csv_file, newline="", encoding="utf-8", errors="ignore") as f:
            reader = csv.DictReader(f)
            for row in reader:
                home = row.get("HomeTeam", "").strip()
                away = row.get("AwayTeam", "").strip()
                date_str = row.get("Date", "").strip()
                
                if not home or not away or not date_str:
                    continue
                
                # Parse date
                try:
                    if "/" in date_str:
                        parts = date_str.split("/")
                        if len(parts[2]) == 2:
                            date = f"20{parts[2]}-{parts[1].zfill(2)}-{parts[0].zfill(2)}"
                        else:
                            date = f"{parts[2]}-{parts[1].zfill(2)}-{parts[0].zfill(2)}"
                    else:
                        date = date_str
                except:
                    continue
                
                key = (home.lower(), away.lower(), date[:10])
                if key in existing_keys:
                    continue
                
                try:
                    home_goals = int(row.get("FTHG", 0)) if row.get("FTHG") else 0
                    away_goals = int(row.get("FTAG", 0)) if row.get("FTAG") else 0
                    
                    # Betting odds (B365 = Bet365)
                    home_odds_dec = float(row.get("B365H", 0)) if row.get("B365H") else None
                    draw_odds_dec = float(row.get("B365D", 0)) if row.get("B365D") else None
                    away_odds_dec = float(row.get("B365A", 0)) if row.get("B365A") else None
                    
                    over_25_dec = float(row.get("B365>2.5", 0)) if row.get("B365>2.5") else None
                    under_25_dec = float(row.get("B365<2.5", 0)) if row.get("B365<2.5") else None
                except:
                    continue
                
                home_ml = decimal_to_american(home_odds_dec)
                away_ml = decimal_to_american(away_odds_dec)
                
                result = row.get("FTR", "")  # H=Home win, D=Draw, A=Away win
                
                record = {
                    "game_id": f"fdata_{league_prefix}_{home}_{away}_{date}".replace(" ", "_"),
                    "sport": sport_name,
                    "date": date,
                    "home_team": home,
                    "away_team": away,
                    "home_score": home_goals,
                    "away_score": away_goals,
                    "result": result,
                    "home_won": result == "H",
                    "draw": result == "D",
                    "away_won": result == "A",
                    "home_ml": home_ml,
                    "away_ml": away_ml,
                    "odds": {
                        "source": "football-data.co.uk",
                        "sportsbooks": {
                            "bet365": {
                                "home_ml": home_ml,
                                "draw_ml": decimal_to_american(draw_odds_dec),
                                "away_ml": away_ml,
                                "home_dec": home_odds_dec,
                                "draw_dec": draw_odds_dec,
                                "away_dec": away_odds_dec
                            }
                        }
                    }
                }
                new_games.append(record)
                existing_keys.add(key)
    
    print(f"  New matches: {len(new_games)}")
    
    # Merge and save
    all_data = existing + new_games
    with open(output_file, "w") as f:
        json.dump(all_data, f, indent=2)
    
    print(f"  Total: {len(all_data)}")
    return len(new_games)

def main():
    DATA_DIR.mkdir(parents=True, exist_ok=True)
    
    print("="*60)
    print("IMPORTING EUROPEAN SOCCER DATA FROM FOOTBALL-DATA.CO.UK")
    print("="*60)
    
    total = 0
    for prefix, name in LEAGUES.items():
        total += import_league(prefix, name)
    
    print("\n" + "="*60)
    print(f"TOTAL NEW MATCHES IMPORTED: {total}")
    print("="*60)

if __name__ == "__main__":
    main()