#!/usr/bin/env python3
"""
API-Football下载脚本 - 带速率控制
每天最多下载200个请求，3个联赛同时进行
"""
from __future__ import annotations
import os, sys, json, time, warnings
from pathlib import Path
from datetime import date, timedelta
from collections import defaultdict
import numpy as np
import pandas as pd
import requests
import threading

warnings.filterwarnings('ignore')

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_KEY = os.environ.get('API_FOOTBALL_KEY', '57b64e857ebeffdc156f0c41d43c7894')
API_BASE = os.environ.get('API_FOOTBALL_BASE_URL', 'https://v3.football.api-sports.io').rstrip('/')
HEADERS = {'x-apisports-key': API_KEY}

# 限速：每秒最多1个请求
_lock = threading.Lock()
_req_times = []

def rate_limited_get(url, params, max_per_second=1.0):
    global _req_times
    with _lock:
        now = time.time()
        _req_times = [t for t in _req_times if now - t < 1.0]
        if len(_req_times) >= max_per_second:
            sleep_time = 1.0 - (now - _req_times[0]) if _req_times else 0.1
            if sleep_time > 0:
                time.sleep(sleep_time)
                now = time.time()
                _req_times = [t for t in _req_times if now - t < 1.0]
        _req_times.append(now)
    r = requests.get(url, params=params, headers=HEADERS, timeout=120)
    r.raise_for_status()
    return r.json()


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

SEASONS = [2023, 2024, 2025]


def api_get(endpoint, params, sleep=0.3):
    time.sleep(sleep)
    r = requests.get(API_BASE + endpoint, params=params, headers=HEADERS, timeout=120)
    r.raise_for_status()
    return r.json()


def parse_total(bookmakers):
    for bm in bookmakers:
        for bet in bm.get('bets', []):
            if bet.get('name') != 'Goals Over/Under':
                continue
            local = defaultdict(dict)
            for item in bet.get('values', []):
                raw = item.get('value', '')
                try:
                    odd = float(item.get('odd'))
                    if odd <= 1:
                        continue
                    if raw.startswith('Over '):
                        local[float(raw.replace('Over ', ''))]['over'] = odd
                    elif raw.startswith('Under '):
                        local[float(raw.replace('Under ', ''))]['under'] = odd
                except:
                    pass
            for line, sides in local.items():
                if 'over' in sides and 'under' in sides:
                    return {'ou_line': line, 'over_odds': sides['over'], 'under_odds': sides['under']}
    return None


def parse_1x2(bookmakers):
    rows = []
    for bm in bookmakers:
        for bet in bm.get('bets', []):
            if bet.get('name') != 'Match Winner':
                continue
            vals = {v.get('value'): v.get('odd') for v in bet.get('values', [])}
            try:
                h, d, a = float(vals.get('Home', 0)), float(vals.get('Draw', 0)), float(vals.get('Away', 0))
                if min(h, a) > 1:
                    rows.append((h, d, a))
            except:
                pass
    if not rows:
        return None
    arr = np.array(rows)
    return {'home_odds': float(arr[:, 0].mean()), 'draw_odds': float(arr[:, 1].mean()), 'away_odds': float(arr[:, 2].mean())}


def parse_ah(bookmakers):
    """返回 (handicap, home_ah_odds, away_ah_odds)"""
    for bm in bookmakers:
        for bet in bm.get('bets', []):
            if bet.get('name') != 'Asian Handicap':
                continue
            home_ah, away_ah = None, None
            handicap = None
            for item in bet.get('values', []):
                raw = item.get('value', '')
                try:
                    odd = float(item.get('odd'))
                    if odd <= 1:
                        continue
                    parts = raw.split()
                    if len(parts) < 2:
                        continue
                    side = parts[0]
                    hcap = float(parts[1])
                    if side == 'Home':
                        home_ah = odd
                        handicap = hcap
                    elif side == 'Away':
                        away_ah = odd
                        if handicap is None:
                            handicap = -hcap
                except:
                    pass
            if home_ah is not None and away_ah is not None:
                return handicap, home_ah, away_ah
            elif home_ah is not None:
                return handicap, home_ah, None
            elif away_ah is not None:
                return None, None, away_ah
    return None, None, None


def download_league(code, info):
    lid = info['id']
    lname = info['name']
    print(f"\n{'='*50}\n{code} {lname} (id={lid})\n{'='*50}")

    all_fixtures = []
    for season in SEASONS:
        page = 1
        total_pages = 1
        while page <= total_pages:
            try:
                data = api_get('/fixtures', {'league': lid, 'season': season, 'page': page})
                resp = data.get('response', [])
                total_pages = int(data.get('paging', {}).get('total', 1) or 1)
                for item in resp:
                    st = 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': st,
                        '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,
                        'season': season,
                    })
                print(f"  season={season} page={page}/{total_pages} → {len(resp)} fixtures")
                page += 1
            except Exception as e:
                print(f"  ERROR season={season} page={page}: {e}")
                page += 1
                time.sleep(2)

    if not all_fixtures:
        print(f"  无数据"); return {'code': code, 'status': 'no_data'}

    fixtures_df = pd.DataFrame(all_fixtures)
    completed = fixtures_df[fixtures_df['status'].isin(['FT', 'AET', 'PEN'])].copy()
    completed_ids = sorted(completed['fixture_id'].tolist())
    print(f"  总fixtures: {len(all_fixtures)}, 已完成: {len(completed_ids)}")

    # 获取赔率
    odds_rows = []
    for i, fid in enumerate(completed_ids):
        try:
            data = api_get('/odds', {'fixture': fid})
            resp = data.get('response', [])
            if not resp:
                continue
            bookmakers = resp[0].get('bookmakers', [])
            if not bookmakers:
                continue

            ou = parse_total(bookmakers)
            odds_1x2 = parse_1x2(bookmakers)
            ah_h, ah_home_odd, ah_away_odd = parse_ah(bookmakers)

            row = {'fixture_id': fid}
            if odds_1x2:
                row.update(odds_1x2)
            if ou:
                row['B365>2.5'] = ou['over_odds']
                row['B365<2.5'] = ou['under_odds']
            row['AHh'] = ah_h
            row['B365AHH'] = ah_home_odd
            row['B365AHA'] = ah_away_odd
            odds_rows.append(row)
        except Exception as e:
            pass

        if (i + 1) % 20 == 0:
            print(f"  赔率进度: {i+1}/{len(completed_ids)} ({len(odds_rows)} rows with odds)")

    # 合并
    odds_df = pd.DataFrame(odds_rows).drop_duplicates(subset=['fixture_id'], keep='last')
    merged = fixtures_df.merge(odds_df, on='fixture_id', how='left')

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

    out_path = OUT_DIR / f'{code}_history.csv'
    cols = ['Date', 'HomeTeam', 'AwayTeam', 'FTHG', 'FTAG', 'FTR',
             'B365>2.5', 'B365<2.5', 'AHh', 'B365AHH', 'B365AHA', 'season']
    existing = [c for c in cols if c in merged.columns]
    merged[existing].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"  保存: {out_path.name} → {len(merged)}行")
    print(f"  AH数据: {has_ah}, OU数据: {has_ou}")

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


if __name__ == '__main__':
    print("=" * 50)
    print("API下载缺失联赛数据 - 限速版 (1 req/s)")
    print("=" * 50)

    results = {}
    for code, info in sorted(MISSING_LEAGUES.items(), key=lambda x: x[1]['id']):
        print(f"\n>>> 处理: {code}")
        result = download_league(code, info)
        results[code] = result
        print(f"  结果: {result}")

    print("\n" + "=" * 50)
    print("汇总")
    print("=" * 50)
    for code, r in sorted(results.items()):
        s = r.get('status', '?')
        t = r.get('total', 0)
        ah = r.get('with_ah', 0)
        ou = r.get('with_ou', 0)
        print(f"  {code}: {s} | {t}行 | AH:{ah} OU:{ou}")
