#!/usr/bin/env python3
"""从 API-Football 批量抓取历史比赛赔率数据（补充 f-d.co.uk 未覆盖的联赛）"""
from __future__ import annotations
import json, math, os, re, sys, time
from collections import defaultdict
from datetime import datetime, timedelta, timezone
from pathlib import Path
from typing import Optional
import requests
import pandas as pd
import numpy as np

REPO_ROOT = Path(__file__).resolve().parents[1]
HIST_DIR = REPO_ROOT / 'historical_data'
FD_BASE = 'https://www.football-data.co.uk'
ARTIFACT_DIR = REPO_ROOT / 'artifacts' / 'api_football_cache'
BEIJING_TZ = timezone(timedelta(hours=8))

# 联赛映射：(api_country, api_league_id, 本地代码, 联赛名, f-d.co.uk覆盖)
LEAGUES = [
    # (本地代码, api联赛ID, 联赛名)
    ('n2', 89,  '荷兰乙级联赛 (Eerste Divisie)'),
    ('r1', 235, '俄罗斯超级联赛 (Premier League)'),
    ('a1', 218, '奥地利超级联赛 (Bundesliga)'),
    ('s1', 207, '瑞士超级联赛 (Super League)'),
    ('dk1', 119, '丹麦超级联赛 (Superliga)'),
    ('no1', 103, '挪威超级联赛 (Eliteserien)'),
    ('no2', 104, '挪威甲级联赛 (1. Division)'),
    ('sw1', 113, '瑞典超级联赛 (Allsvenskan)'),
    ('sw2', 114, '瑞典甲级联赛 (Superettan)'),
    ('pl1', 106, '波兰超级联赛 (Ekstraklasa)'),
    ('hu1', 271, '匈牙利甲级联赛 (NB I)'),
    ('ro1', 283, '罗马尼亚甲级联赛 (Liga I)'),
    ('rs1', 286, '塞尔维亚超级联赛 (Super Liga)'),
    ('bg1', 172, '保加利亚超级联赛 (First League)'),
    ('il1', 383, '以色列超级联赛 (Ligat Ha\'al)'),
    ('sk1', 332, '斯洛伐克超级联赛 (Super Liga)'),
    ('fi1', 244, '芬兰超级联赛 (Veikkausliiga)'),
    ('ir1', 357, '爱尔兰超级联赛 (Premier Division)'),
    ('is1', 164, '冰岛超级联赛 (Úrvalsdeild)'),
]

# 球队名称标准化缓存
TEAM_NAME_MAP = {}
TEAM_ALIASES = {
    'FK Krasnodar': 'Krasnodar',
    'Lokomotiv Moskva': 'Lokomotiv Moscow',
    'CSKA Moskva': 'CSKA Moscow',
    'Dinamo Moskva': 'Dinamo Moscow',
    'FK Zenit': 'Zenit',
    'FK Orenburg': 'Orenburg',
    'Nizhny Novgorod': 'Nizhny',
    'Rotor': 'Rotor Volgograd',
}


def normalize_team(name: str) -> str:
    if not name:
        return ''
    s = re.sub(r'[^a-zA-Z0-9 ]', ' ', str(name)).strip()
    s = re.sub(r'\s+', ' ', s)
    return s


def extract_total_value(raw: str):
    """解析 Over/Under 盘口值，如 'Over 2.5' -> ('over', 2.5)"""
    if not raw:
        return None
    m = re.match(r'^(Over|Under)\s+([0-9.]+)$', raw.strip(), re.IGNORECASE)
    if not m:
        return None
    return m.group(1).lower(), float(m.group(2))


def settle_binary(total_goals: float, line: float):
    """结算任意盘口线"""
    frac = round(line - math.floor(line), 2)
    base = math.floor(line)
    if frac == 0.0:
        if total_goals > line:
            return 1
        if total_goals < line:
            return 0
        return None
    if frac == 0.5:
        return 1 if total_goals > line else 0
    if frac == 0.25:
        if total_goals >= base + 1:
            return 1
        if total_goals <= base - 1:
            return 0
        return None
    if frac == 0.75:
        if total_goals >= base + 2:
            return 1
        if total_goals <= base:
            return 0
        return None
    return None


# ─── API 客户端 ────────────────────────────────────────────────────────────

class APIClient:
    def __init__(self):
        self.base_url = os.environ.get('API_FOOTBALL_BASE_URL', '').rstrip('/')
        self.api_key = os.environ.get('API_FOOTBALL_KEY', '')
        self.session = requests.Session()
        self.session.headers['x-apisports-key'] = self.api_key
        ARTIFACT_DIR.mkdir(parents=True, exist_ok=True)

    def _cache_path(self, key: str) -> Path:
        digest = str(abs(hash(key)))[:16]
        return ARTIFACT_DIR / f'{digest}.json'

    def _read_cache(self, path: Path):
        if path.exists():
            age = datetime.now() - datetime.fromtimestamp(path.stat().st_mtime)
            if age < timedelta(hours=24):
                return json.loads(path.read_text())
        return None

    def _get(self, endpoint: str, params: dict, cache: bool = True):
        key = f'{endpoint}_{json.dumps(params, sort_keys=True)}'
        cache_path = self._cache_path(key) if cache else None
        cached = self._read_cache(cache_path) if cache else None
        if cached is not None:
            return cached
        for attempt in range(3):
            try:
                r = self.session.get(self.base_url + endpoint, params=params, timeout=30)
                if r.status_code == 403 or r.status_code == 429:
                    wait = (attempt + 1) * 30
                    print(f'  [API] Rate limited, waiting {wait}s...')
                    time.sleep(wait)
                    continue
                r.raise_for_status()
                break
            except Exception:
                if attempt == 2:
                    raise
                time.sleep(10)
        resp = r.json()
        if cache and cache_path:
            cache_path.write_text(json.dumps(resp))
        return resp

    def get_league_seasons(self, league_id: int) -> list:
        resp = self._get('/leagues', {'id': league_id}, cache=True)
        seasons = []
        for item in resp.get('response', []):
            for season in item.get('seasons', []):
                y = season.get('year')
                if y:
                    seasons.append(y)
        return sorted(seasons)

    def get_fixtures_by_season(self, league_id: int, season: int) -> list:
        # 不带status过滤，避免API参数问题，返回后在本地过滤
        resp = self._get('/fixtures', {'league': league_id, 'season': season}, cache=True)
        return resp.get('response', [])

    def get_odds(self, fixture_id: int) -> list:
        resp = self._get('/odds', {'fixture': fixture_id}, cache=True)
        return resp.get('response', [])

    def get_league_info(self, league_id: int) -> dict:
        resp = self._get('/leagues', {'id': league_id}, cache=True)
        for item in resp.get('response', []):
            if str(item.get('league', {}).get('id')) == str(league_id):
                return item.get('league', {})
        return {}


def choose_best_ou(bookmakers: list) -> Optional[dict]:
    """从赔率数据中选出最佳 O/U 盘口（多线支持）"""
    per_line = defaultdict(lambda: {'over': [], 'under': [], 'count': 0})

    for bookmaker in bookmakers:
        for bet in bookmaker.get('bets', []):
            if bet.get('name') != 'Goals Over/Under':
                continue
            for item in bet.get('values', []):
                parsed = extract_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
                per_line[line][side].append(odd)

    results = []
    for line, sides in per_line.items():
        if 'over' not in sides or 'under' not in sides:
            continue
        over_odds = float(np.mean(sides['over']))
        under_odds = float(np.mean(sides['under']))
        over_prob = 1.0 / over_odds if over_odds > 1 else np.nan
        under_prob = 1.0 / under_odds if under_odds > 1 else np.nan
        total_prob = over_prob + under_prob
        fair_over = over_prob / total_prob if total_prob > 0 else np.nan
        results.append({
            'line': float(line),
            'over_odds': over_odds,
            'under_odds': under_odds,
            'over_prob': over_prob,
            'under_prob': under_prob,
            'fair_over': fair_over,
            'quote_count': len(sides['over']) + len(sides['under']),
        })

    if not results:
        return None

    # 优先选赔率最均衡的线
    results.sort(key=lambda x: abs((x['fair_over'] if not math.isnan(x['fair_over']) else 0.5) - 0.5), reverse=True)
    best = results[0]
    return {
        'ou_line': best['line'],
        'over_odds': best['over_odds'],
        'under_odds': best['under_odds'],
        'over_prob_raw': best['over_prob'],
        'under_prob_raw': best['under_prob'],
        'fair_over': best['fair_over'],
        'quote_count': best['quote_count'],
    }


def collect_league_history(client: APIClient, league_id: int, local_code: str, league_name: str) -> dict:
    """抓取单个联赛所有赛季的历史数据"""
    print(f'  获取赛季列表: {league_name} (ID={league_id})...')
    seasons = client.get_league_seasons(league_id)
    if not seasons:
        print(f'  ⚠️ 无法获取赛季列表')
        return {'status': 'FAILED', 'league': league_name}

    print(f'  找到 {len(seasons)} 个赛季: {seasons[0]}~{seasons[-1]}')
    local_path = HIST_DIR / f'{local_code}_history.csv'

    # 读取已有数据的 (Date, HomeTeam, AwayTeam) 集合用于去重
    existing_keys = set()
    existing_cols = set()
    if local_path.exists():
        try:
            df = pd.read_csv(local_path, encoding='utf-8-sig', low_memory=False, nrows=200)
            if 'Date' in df.columns and 'HomeTeam' in df.columns:
                for _, row in df.iterrows():
                    key = (str(row.get('Date', '')), str(row.get('HomeTeam', '')), str(row.get('AwayTeam', '')))
                    existing_keys.add(key)
                existing_cols = set(df.columns)
        except Exception:
            pass

    all_rows = []
    for si, season in enumerate(seasons):
        print(f'  [{si+1}/{len(seasons)}] 赛季 {season}...', end=' ', flush=True)
        fixtures = client.get_fixtures_by_season(league_id, season)
        print(f'{len(fixtures)} 场比赛', flush=True)

        for fi, fixture in enumerate(fixtures):
            fid = fixture['fixture']['id']
            status = fixture['fixture']['status']['short']
            if status not in ('FT', 'AET', 'PEN'):
                continue

            home_name = fixture['teams']['home']['name']
            away_name = fixture['teams']['away']['name']
            home_team = normalize_team(home_name)
            away_team = normalize_team(away_name)
            fthg = int(fixture['goals']['home'])
            ftag = int(fixture['goals']['away'])
            tg = fthg + ftag

            kickoff_str = fixture['fixture'].get('date', '')
            try:
                kickoff = datetime.fromisoformat(kickoff_str.replace('Z', '+00:00')).replace(tzinfo=timezone.utc).astimezone(BEIJING_TZ)
                date_str = kickoff.strftime('%Y-%m-%d')
            except Exception:
                date_str = kickoff_str[:10] if kickoff_str else ''

            key = (date_str, home_team, away_team)
            if key in existing_keys:
                continue

            # 获取赔率
            ou_data = None
            odds_resp = client.get_odds(fid)
            if odds_resp:
                for odds_item in odds_resp:
                    bms = odds_item.get('bookmakers', [])
                    if bms:
                        ou_data = choose_best_ou(bms)
                        if ou_data:
                            break

            row = {
                'Date': date_str,
                'HomeTeam': home_team,
                'AwayTeam': away_team,
                'FTHG': fthg,
                'FTAG': ftag,
                'FTR': 'H' if fthg > ftag else ('A' if fthg < ftag else 'D'),
                'season': season,
            }

            if ou_data:
                row['B365>2.5'] = ou_data['over_odds']
                row['B365<2.5'] = ou_data['under_odds']
                row['Avg>2.5'] = ou_data['over_odds']
                row['Avg<2.5'] = ou_data['under_odds']
                row['AvgC>2.5'] = ou_data['over_odds']
                row['AvgC<2.5'] = ou_data['under_odds']
                row['PC>2.5'] = ou_data['over_odds']
                row['PC<2.5'] = ou_data['under_odds']

            all_rows.append(row)

            # 限速：每分钟最多 10 个 fixture + 1 个 odds
            if (fi + 1) % 10 == 0:
                time.sleep(0.5)

        time.sleep(0.5)

    if not all_rows:
        return {'status': 'EMPTY', 'league': league_name, 'rows': 0}

    new_df = pd.DataFrame(all_rows)
    ou_cols = [c for c in new_df.columns if '>2.5' in c or '<2.5' in c]

    # 合并到本地
    if local_path.exists() and existing_cols:
        try:
            old_df = pd.read_csv(local_path, encoding='utf-8-sig', low_memory=False)
            combined = pd.concat([old_df, new_df], ignore_index=True)
        except Exception:
            combined = new_df
    else:
        combined = new_df

    combined.to_csv(local_path, index=False, encoding='utf-8-sig')
    print(f'  ✅ {league_name}: 新增 {len(new_df)} 行, 总计 {len(combined)} 行, OU列={len(ou_cols)}')

    return {
        'status': 'OK' if ou_cols else 'NO_OU',
        'league': league_name,
        'local_code': local_code,
        'new_rows': len(new_df),
        'total_rows': len(combined),
        'ou_cols': len(ou_cols),
    }


def main():
    client = APIClient()
    results = []

    for local_code, league_id, league_name in LEAGUES:
        print(f'\n[下载] {league_name} (ID={league_id})...')
        try:
            result = collect_league_history(client, league_id, local_code, league_name)
            results.append(result)
        except Exception as e:
            print(f'  ❌ 异常: {e}')
            results.append({'status': 'ERROR', 'league': league_name, 'error': str(e)})
        time.sleep(2)

    print('\n========== 汇总 ==========')
    ok = [r for r in results if r.get('status') == 'OK']
    no_ou = [r for r in results if r.get('status') == 'NO_OU']
    failed = [r for r in results if r.get('status') in ('FAILED', 'ERROR', 'EMPTY')]
    print(f'✅ 成功(含OU): {len(ok)}')
    print(f'⚠️  成功(无OU): {len(no_ou)}')
    print(f'❌ 失败: {len(failed)}')
    for r in ok + no_ou:
        print(f'  {"✅" if r.get("status")=="OK" else "⚠️"} {r["league"]}: 新增{r.get("new_rows",0)}行, 总{r.get("total_rows",0)}行, OU={r.get("ou_cols",0)}')
    for r in failed:
        print(f'  ❌ {r["league"]}: {r.get("status")} {r.get("error","")}')


if __name__ == '__main__':
    main()
