#!/usr/bin/env python3
"""
通过API-Football下载缺失联赛的完整历史数据（2023-2025）
包含亚盘(AH)赔率，用于训练独立亚盘模型
"""
from __future__ import annotations
import json
import os
import re
import time
from collections import defaultdict
from pathlib import Path
from typing import Dict, List, Optional, Tuple
import warnings
warnings.filterwarnings('ignore')

import numpy as np
import pandas as pd
import requests

REPO_ROOT = Path('/root/.openclaw/workspace-flj_bot1/football-predictor-repro')
OUT_DIR = REPO_ROOT / 'historical_data'
OUT_DIR.mkdir(parents=True, exist_ok=True)

# API-Football联赛ID
LEAGUE_IDS: Dict[str, Dict] = {
    'a1':  {'id': 2,   'name': 'A-League',        'country': 'Australia',     'season_start': 2023},
    'br1': {'id': 71,  'name': 'Serie A',          'country': 'Brazil',        'season_start': 2023},
    'd2':  {'id': 59,  'name': '2. Bundesliga',     'country': 'Germany',      'season_start': 2023},
    'e0':  {'id': 39,  'name': 'Premier League',    'country': 'England',      'season_start': 2023},
    'f2':  {'id': 61,  'name': 'Ligue 2',          'country': 'France',       'season_start': 2023},
    'g1':  {'id': 197, 'name': 'Super League 1',    'country': 'Greece',      'season_start': 2023},
    'j1':  {'id': 98,  'name': 'J1 League',         'country': 'Japan',        'season_start': 2023},
    'm1':  {'id': 13,  'name': 'Major League Soccer','country': 'USA',         'season_start': 2023},
    'r1':  {'id': 235, 'name': 'Premier League',    'country': 'Russia',       'season_start': 2023},
    'uz1': {'id': 1023,'name': 'Super League',       'country': 'Uzbekistan',  'season_start': 2023},
    'k2':  {'id': 292, 'name': 'K League 2',        'country': 'South-Korea', 'season_start': 2023},
}

SEASONS = [2023, 2024, 2025]


def parse_total_value(raw: str | None) -> Optional[Tuple[str, float]]:
    m = re.match(r'^(Over|Under)\s+([0-9]+(?:\.[0-9]+)?)$', str(raw or '').strip(), re.I)
    if not m:
        return None
    return m.group(1).lower(), float(m.group(2))


def choose_main_total(bookmakers: List[dict]) -> Optional[dict]:
    """选择主要的O/U盘口"""
    per_line: Dict[float, dict] = {}
    for bookmaker in bookmakers:
        for bet in bookmaker.get('bets', []):
            if bet.get('name') != 'Goals Over/Under':
                continue
            local: Dict[float, dict] = defaultdict(dict)
            for item in bet.get('values', []):
                parsed = parse_total_value(item.get('value'))
                if not parsed:
                    continue
                side, line = parsed
                try:
                    odd = float(item.get('odd'))
                except Exception:
                    continue
                if odd <= 1:
                    continue
                local[line][side] = odd
            for line, sides in local.items():
                if 'over' not in sides or 'under' not in sides:
                    continue
                rec = per_line.setdefault(line, {'quotes': 0, 'over': [], 'under': []})
                rec['quotes'] += 1
                rec['over'].append(float(sides['over']))
                rec['under'].append(float(sides['under']))
    if not per_line:
        return None
    candidates = []
    for line, rec in per_line.items():
        over_odds = float(np.mean(rec['over']))
        under_odds = float(np.mean(rec['under']))
        over_prob = 1.0 / over_odds
        under_prob = 1.0 / under_odds
        fair_over = over_prob / (over_prob + under_prob) if (over_prob + under_prob) > 0 else np.nan
        candidates.append({
            'ou_line': float(line),
            'over_odds': over_odds,
            'under_odds': under_odds,
            'ou_quote_count': int(rec['quotes']),
            'fair_over_open': fair_over,
        })
    if not candidates:
        return None
    candidates.sort(key=lambda x: (-x['ou_quote_count'], abs(x['ou_line'] - 2.5)))
    chosen = candidates[0].copy()
    return chosen


def choose_main_1x2(bookmakers: List[dict]) -> dict:
    rows = []
    for bookmaker in bookmakers:
        for bet in bookmaker.get('bets', []):
            if bet.get('name') != 'Match Winner':
                continue
            values = {v.get('value'): v.get('odd') for v in bet.get('values', [])}
            try:
                home = float(values['Home'])
                draw = float(values['Draw'])
                away = float(values['Away'])
            except Exception:
                continue
            if min(home, draw, away) <= 1:
                continue
            rows.append((home, draw, away))
    if not rows:
        return {'home_odds': np.nan, 'draw_odds': np.nan, 'away_odds': np.nan}
    arr = np.array(rows, dtype=float)
    return {
        'home_odds': float(arr[:, 0].mean()),
        'draw_odds': float(arr[:, 1].mean()),
        'away_odds': float(arr[:, 2].mean()),
    }


def parse_asian_handicap(bookmakers: List[dict]) -> Tuple[Optional[float], Optional[float], Optional[float]]:
    """解析亚盘赔率"""
    PREFERRED = {'Pinnacle': 0, 'SBO': 1, '188Bet': 2, 'Bet365': 3, 'Marathonbet': 4, '1xBet': 5}
    candidates = []
    for bookmaker in bookmakers:
        bname = bookmaker.get('name', '')
        for bet in bookmaker.get('bets', []):
            if bet.get('name') != 'Asian Handicap':
                continue
            grouped: Dict[str, dict] = {}
            for item in bet.get('values', []):
                raw = item.get('value', '')
                odd = item.get('odd')
                parts = raw.split()
                if len(parts) < 2 or odd in (None, ''):
                    continue
                side = parts[0]
                try:
                    num = float(parts[1].replace('+', ''))
                    price = float(odd)
                except Exception:
                    continue
                grouped.setdefault(parts[1], {'handicap': num})[side] = price
            for row in grouped.values():
                if 'Home' not in row or 'Away' not in row:
                    continue
                home_odd = float(row['Home'])
                away_odd = float(row['Away'])
                handicap = float(row['handicap'])
                candidates.append({
                    'bookmaker': bname,
                    'handicap': 0.0 if abs(handicap) < 1e-9 else handicap,
                    'home_odd': home_odd,
                    'away_odd': away_odd,
                    'pref': PREFERRED.get(bname, 999),
                    'balance_gap': abs(home_odd - away_odd),
                })
    if not candidates:
        return None, None, None
    candidates.sort(key=lambda x: (x['pref'], x['balance_gap'], abs(x['handicap'])))
    best = candidates[0]
    return best['handicap'], best['home_odd'], best['away_odd']


def fetch_json(session: requests.Session, base_url: str, endpoint: str, params: dict, sleep: float = 0.15) -> dict:
    time.sleep(sleep)
    r = session.get(base_url + endpoint, params=params, timeout=120)
    r.raise_for_status()
    return r.json()


def collect_league(code: str, league_info: dict) -> dict:
    league_id = league_info['id']
    league_name = league_info['name']
    country = league_info['country']

    print(f"\n{'='*60}")
    print(f"下载 {league_name}({code}) 联赛数据...")
    print(f"{'='*60}")

    base_url = os.environ['API_FOOTBALL_BASE_URL'].rstrip('/')
    api_key = os.environ['API_FOOTBALL_KEY']
    session = requests.Session()
    session.headers.update({'x-apisports-key': api_key})

    # 1. 下载所有赛季的fixtures
    all_fixtures = []
    for season in SEASONS:
        page = 1
        total_pages = 1
        while page <= total_pages:
            payload = fetch_json(session, base_url, '/fixtures',
                                {'league': league_id, 'season': season, 'page': page},
                                sleep=0.1)
            response = payload.get('response', [])
            total_pages = int(payload.get('paging', {}).get('total', 1) or 1)
            print(f"  season={season} page={page}/{total_pages} rows={len(response)}")
            for item in response:
                status = item['fixture']['status']['short']
                hg = item['goals'].get('home')
                ag = item['goals'].get('away')
                all_fixtures.append({
                    'fixture_id': int(item['fixture']['id']),
                    'Date': str(item['fixture']['date'])[:10],
                    'timestamp': int(item['fixture']['timestamp']),
                    'status': status,
                    'HomeTeam': item['teams']['home']['name'],
                    'AwayTeam': item['teams']['away']['name'],
                    'FTHG': int(hg) if hg is not None else None,
                    'FTAG': int(ag) if ag is not None else None,
                    'FTR': None,  # will fill later
                    'season': season,
                })
            page += 1

    if not all_fixtures:
        print(f"  无fixture数据")
        return {'code': code, 'status': 'no_fixtures', 'fixtures': 0}

    fixtures_df = pd.DataFrame(all_fixtures)
    fixtures_df = fixtures_df.sort_values('Date').reset_index(drop=True)

    # 填充FTR
    def get_ftr(row):
        if row['FTHG'] is None or row['FTAG'] is None:
            return None
        if row['FTHG'] > row['FTAG']:
            return 'H'
        elif row['FTHG'] < row['FTAG']:
            return 'A'
        else:
            return 'D'
    fixtures_df['FTR'] = fixtures_df.apply(get_ftr, axis=1)

    print(f"  总fixtures: {len(fixtures_df)}")

    # 2. 获取已完成的比赛
    completed = fixtures_df[fixtures_df['status'].isin(['FT', 'AET', 'PEN'])].copy()
    completed_ids = set(completed['fixture_id'].tolist())
    print(f"  已完成比赛: {len(completed_ids)}")

    # 3. 下载赔率（分批按日期）
    fixture_dates = sorted(completed['Date'].dropna().unique().tolist())
    print(f"  需要获取赔率的日期数: {len(fixture_dates)}")

    odds_rows = []
    for i, date_str in enumerate(fixture_dates):
        page = 1
        total_pages = 1
        while page <= total_pages:
            try:
                payload = fetch_json(session, base_url, '/odds',
                                    {'date': date_str, 'page': page},
                                    sleep=0.1)
                total_pages = int(payload.get('paging', {}).get('total', 1) or 1)
                response = payload.get('response', [])
                for item in response:
                    fid = int(item['fixture']['id'])
                    if fid not in completed_ids:
                        continue
                    bookmakers = item.get('bookmakers', [])
                    if not bookmakers:
                        continue

                    chosen_total = choose_main_total(bookmakers)
                    chosen_1x2 = choose_main_1x2(bookmakers)
                    ah_h, ah_home_odd, ah_away_odd = parse_asian_handicap(bookmakers)

                    odds_rows.append({
                        'fixture_id': fid,
                        'date': date_str,
                        **{f'B365{k}': v for k, v in chosen_1x2.items()},
                        **{f'B365>2.5': chosen_total.get('over_odds') if chosen_total else np.nan,
                           f'B365<2.5': chosen_total.get('under_odds') if chosen_total else np.nan,
                           f'ou_line': chosen_total.get('ou_line') if chosen_total else np.nan},
                        'AHh': ah_h,
                        'B365AHH': ah_home_odd,
                        'B365AHA': ah_away_odd,
                    })
                print(f"  [{i+1}/{len(fixture_dates)}] {date_str} page={page} odds_rows={len(odds_rows)}")
            except Exception as e:
                print(f"  [{i+1}/{len(fixture_dates)}] {date_str} ERROR: {e}")
            page += 1

    # 4. 合并数据
    odds_df = pd.DataFrame(odds_rows).drop_duplicates(subset=['fixture_id'], keep='last')
    merged = fixtures_df.merge(odds_df[['fixture_id', 'B365>2.5', 'B365<2.5', 'AHh', 'B365AHH', 'B365AHA']],
                               on='fixture_id', how='left')

    # 5. 保存
    out_path = OUT_DIR / f'{code}_history.csv'
    cols_order = ['Date', 'HomeTeam', 'AwayTeam', 'FTHG', 'FTAG', 'FTR',
                   'B365>2.5', 'B365<2.5', 'AHh', 'B365AHH', 'B365AHA', 'season']
    existing_cols = [c for c in cols_order if c in merged.columns]
    merged[existing_cols].to_csv(out_path, index=False, encoding='utf-8-sig')

    has_ah = merged['AHh'].notna().sum()
    has_ou = merged['B365>2.5'].notna().sum()
    print(f"\n  保存到: {out_path}")
    print(f"  总行数: {len(merged)}")
    print(f"  有AH赔率: {has_ah}")
    print(f"  有O/U赔率: {has_ou}")

    return {
        'code': code,
        'status': 'ok',
        'fixtures': len(merged),
        'with_ah': int(has_ah),
        'with_ou': int(has_ou),
    }


def main():
    print("=" * 60)
    print("API-Football 历史数据下载")
    print("=" * 60)

    results = {}
    for code, info in sorted(LEAGUE_IDS.items(), key=lambda x: x[1]['id']):
        try:
            result = collect_league(code, info)
            results[code] = result
        except Exception as e:
            print(f"  失败: {e}")
            results[code] = {'code': code, 'status': 'error', 'error': str(e)}

    print("\n" + "=" * 60)
    print("下载完成汇总")
    print("=" * 60)
    print(f"{'联赛':<8} {'状态':<12} {'总行数':<10} {'AH数据':<10} {'OU数据':<10}")
    print("-" * 50)
    for code, r in sorted(results.items()):
        status = r.get('status', '?')
        fixtures = r.get('fixtures', 0)
        ah = r.get('with_ah', 0)
        ou = r.get('with_ou', 0)
        print(f"{code:<8} {status:<12} {fixtures:<10} {ah:<10} {ou:<10}")


if __name__ == '__main__':
    main()
