#!/usr/bin/env python3
"""
从API-Football下载缺失联赛数据 - 使用日期范围模式
因为 league+season 参数被限速，改用逐天查询fixture
"""
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

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}

# 缺失数据的联赛
MISSING_LEAGUES = {
    'a1':  {'id': 2,   'name': 'A-League'},
    'br1': {'id': 71,  'name': 'Serie A'},
    'd2':  {'id': 59,  'name': '2. Bundesliga'},
    'e0':  {'id': 39,  'name': 'Premier League'},
    'f2':  {'id': 61,  'name': 'Ligue 2'},
    'g1':  {'id': 197, 'name': 'Super League 1'},
    'j1':  {'id': 98,  'name': 'J1 League'},
    'm1':  {'id': 13,  'name': 'MLS'},
    'r1':  {'id': 235, 'name': 'Premier League'},
    'uz1': {'id': 1023,'name': 'Super League'},
    'k2':  {'id': 292, 'name': 'K League 2'},
}

# 联赛赛季日期范围（估计）
SEASON_RANGES = {
    'a1':  [(date(2023, 10, 1), date(2024, 5, 1)), (date(2024, 10, 1), date(2025, 5, 1)), (date(2025, 10, 1), date(2026, 5, 1))],
    'br1': [(date(2023, 4, 1), date(2023, 12, 1)), (date(2024, 4, 1), date(2024, 12, 1)), (date(2025, 4, 1), date(2025, 12, 1))],
    'd2':  [(date(2023, 7, 1), date(2024, 5, 15)), (date(2024, 7, 1), date(2025, 5, 15)), (date(2025, 7, 1), date(2026, 5, 15))],
    'e0':  [(date(2023, 8, 1), date(2024, 5, 15)), (date(2024, 8, 1), date(2025, 5, 15)), (date(2025, 8, 1), date(2026, 5, 15))],
    'f2':  [(date(2023, 8, 1), date(2024, 5, 15)), (date(2024, 8, 1), date(2025, 5, 15)), (date(2025, 8, 1), date(2026, 5, 15))],
    'g1':  [(date(2023, 8, 1), date(2024, 5, 15)), (date(2024, 8, 1), date(2025, 5, 15)), (date(2025, 8, 1), date(2026, 5, 15))],
    'j1':  [(date(2023, 2, 1), date(2023, 12, 1)), (date(2024, 2, 1), date(2024, 12, 1)), (date(2025, 2, 1), date(2025, 12, 1))],
    'm1':  [(date(2023, 2, 1), date(2023, 10, 15)), (date(2024, 2, 1), date(2024, 10, 15)), (date(2025, 2, 1), date(2025, 10, 15))],
    'r1':  [(date(2023, 7, 1), date(2024, 5, 31)), (date(2024, 7, 1), date(2025, 5, 31)), (date(2025, 7, 1), date(2026, 5, 31))],
    'uz1': [(date(2023, 2, 1), date(2023, 11, 1)), (date(2024, 2, 1), date(2024, 11, 1)), (date(2025, 2, 1), date(2025, 11, 1))],
    'k2':  [(date(2023, 2, 1), date(2023, 11, 1)), (date(2024, 2, 1), date(2024, 11, 1)), (date(2025, 2, 1), date(2025, 11, 1))],
}

SEASON_YEARS = [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):
    for bm in bookmakers:
        for bet in bm.get('bets', []):
            if bet.get('name') != 'Asian Handicap': continue
            home_odd, away_odd, handicap = None, None, 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_odd = odd; handicap = hcap
                    elif side == 'Away':
                        away_odd = odd
                except: pass
            if home_odd is not None and away_odd is not None:
                return handicap, home_odd, away_odd
    return None, None, None


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

    # Step 1: 收集该联赛所有赛季的fixtures（按日期）
    target_league_ids = {lid}
    all_fixtures = {}
    seasons = SEASON_YEARS

    for season in seasons:
        ranges = SEASON_RANGES.get(code, [])
        for start_date, end_date in ranges:
            print(f"  查询 {start_date} ~ {end_date}...")
            current = start_date
            days_with_fixtures = 0
            while current <= end_date:
                try:
                    data = api_get('/fixtures', {'date': current.isoformat()})
                    found = False
                    for item in data.get('response', []):
                        if item['league']['id'] != lid:
                            continue
                        fid = int(item['fixture']['id'])
                        if fid in all_fixtures:
                            continue
                        st = item['fixture']['status']['short']
                        hg = item['goals'].get('home')
                        ag = item['goals'].get('away')
                        all_fixtures[fid] = {
                            'fixture_id': fid,
                            '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,
                        }
                        found = True
                    if found:
                        days_with_fixtures += 1
                except Exception as e:
                    print(f"    ERROR {current}: {e}")
                current += timedelta(days=1)
            print(f"    → {days_with_fixtures} 个有该联赛的日期")

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

    fixtures_df = pd.DataFrame(all_fixtures.values())
    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)}")

    # Step 2: 获取赔率
    print(f"  获取赔率中...")
    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)")

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

    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下载 - 日期范围模式")
    print("=" * 50)

    results = {}
    for code, info in sorted(MISSING_LEAGUES.items(), key=lambda x: x[1]['id']):
        try:
            result = download_league(code, info)
            results[code] = result
        except Exception as e:
            import traceback; traceback.print_exc()
            results[code] = {'code': code, 'status': 'error', 'error': str(e)}

    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}")
