#!/usr/bin/env python3
"""
pro_v5 & ah_1year_v4 数据下载脚本
策略：
1. 用 /fixtures 的 fixture_ids（无需 odds）
2. 对于有赔率的 fixture，单独调用 /odds（付费版可快速批量）

先下载所有 fixture_ids，再批量获取 odds。
"""
import os, sys, time, json, warnings
from pathlib import Path
from datetime import datetime, timezone, timedelta
from concurrent.futures import ThreadPoolExecutor, as_completed
import numpy as np
import pandas as pd
import requests

warnings.filterwarnings('ignore')
np.random.seed(42)

REPO = Path('/root/.openclaw/workspace-flj_bot1/football-predictor-repro')
ODDS_DIR = REPO / 'artifacts' / 'odds_api_history'
HIST_DIR = REPO / 'historical_data'
ODDS_DIR.mkdir(parents=True, exist_ok=True)

API_KEY = os.environ.get('API_FOOTBALL_KEY', '57b64e857ebeffdc156f0c41d43c7894')
BASE = 'https://v3.football.api-sports.io'
HEADERS = {'x-apisports-key': API_KEY}
TZ = timezone(timedelta(hours=8))

LEAGUE_IDS = {
    'e0':39,'e1':40,'e2':41,'e2n':42,'e3':183,
    'd1':78,'d2':79,'f1':61,'f2':62,
    'i1':135,'i2':136,'sp1':140,'sp2':141,
    'tr1':203,'p1':94,'b1':145,'g1':197,
    'n1':88,'n2':89,'sc1':180,'cz1':237,
    'a1':279,'r1':164,'br1':71,'m1':253,'j1':98,
}
SEASONS = [2023, 2024, 2025]

# ═══════════════════════════════════════════════════════════════════
# STEP 1: 下载所有 fixture 基础数据（不含 odds，很快）
# ═══════════════════════════════════════════════════════════════════

def get_fixtures_for_league_season(league_code, league_id, season):
    """下载联赛赛季所有 fixture"""
    cache_file = ODDS_DIR / f'{league_code}_S{season}_fixtures.csv'
    if cache_file.exists():
        existing = pd.read_csv(cache_file, usecols=['FixtureID'])
        if len(existing) > 0:
            print(f"  {league_code} S{season}: 已缓存 {len(existing)} fixtures，跳过")
            return cache_file
    
    print(f"  {league_code} S{season}: 下载 fixtures...", end=' ', flush=True)
    all_rows = []
    page = 1
    pages_total = 1
    
    while page <= pages_total:
        time.sleep(0.15)  # 付费版可快速请求
        try:
            r = requests.get(f'{BASE}/fixtures', params={
                'league': league_id, 'season': season,
                'page': page, 'timezone': 'Asia/Shanghai',
            }, headers=HEADERS, timeout=30)
            
            if r.status_code != 200:
                print(f"HTTP {r.status_code}")
                break
            
            data = r.json()
            fixtures = data.get('response', [])
            if not fixtures:
                break
            
            paging = data.get('paging', {})
            pages_total = int(paging.get('total', 1))
            
            for fix in fixtures:
                fi = fix['fixture']
                teams = fix['teams']
                goals = fix.get('goals', {})
                
                kickoff = datetime.fromtimestamp(fi['timestamp'], tz=TZ).strftime('%Y-%m-%d')
                status = fi.get('status', {}).get('short', '')
                
                all_rows.append({
                    'Date': kickoff,
                    'LeagueCode': league_code,
                    'LeagueID': league_id,
                    'Season': season,
                    'HomeTeam': teams['home']['name'],
                    'AwayTeam': teams['away']['name'],
                    'HomeTeamID': teams['home'].get('id'),
                    'AwayTeamID': teams['away'].get('id'),
                    'FTHG': goals.get('home'),
                    'FTAG': goals.get('away'),
                    'Status': status,
                    'FixtureID': fi['id'],
                    'HasOdds': False,
                    'B365AHH': None, 'B365AHA': None, 'AHh': None,
                })
            
            print(f"页{page}/{pages_total}✓", end=' ', flush=True)
            page += 1
            
        except Exception as e:
            print(f"错误: {e}")
            time.sleep(1)
            continue
    
    if all_rows:
        df = pd.DataFrame(all_rows)
        df.to_csv(cache_file, index=False, encoding='utf-8')
        print(f"\n  → {len(df)} fixtures → {cache_file.name}")
        return cache_file
    return None


# ═══════════════════════════════════════════════════════════════════
# STEP 2: 批量获取赔率（付费版可高并发）
# ═══════════════════════════════════════════════════════════════════

BET365_ID = 6  # Bet365 bookmaker ID

def fetch_odds_for_fixture(fixture_id):
    """获取单个 fixture 的赔率"""
    time.sleep(0.08)  # 付费版可高频请求
    try:
        r = requests.get(f'{BASE}/odds', params={
            'fixture': fixture_id,
            'bookmaker': BET365_ID,
        }, headers=HEADERS, timeout=15)
        
        if r.status_code != 200:
            return None
        
        data = r.json()
        odds_list = data.get('response', [])
        if not odds_list:
            return None
        
        # Bet365 让球盘口
        bookmakers = odds_list[0].get('bookmakers', [])
        for bm in bookmakers:
            if bm.get('id') == BET365_ID:
                for oo in bm.get('odds', []):
                    label = oo.get('label', '')
                    val = oo.get('value')
                    if label == 'AH':
                        handicap = float(val) if val else None
                        # 找对应的赔率
                        for home_odd in bm.get('odds', []):
                            if home_odd.get('label') == 'Home':
                                b365hh = float(home_odd.get('value')) if home_odd.get('value') else None
                            elif home_odd.get('label') == 'Away':
                                b365ha = float(home_odd.get('value')) if home_odd.get('value') else None
                        return {
                            'FixtureID': fixture_id,
                            'B365AHH': b365hh,
                            'B365AHA': b365ha,
                            'AHh': handicap,
                        }
        return None
    except:
        return None


def process_league_odds(league_code, season):
    """处理单个联赛赛季的赔率获取"""
    fixtures_file = ODDS_DIR / f'{league_code}_S{season}_fixtures.csv'
    if not fixtures_file.exists():
        return 0
    
    df = pd.read_csv(fixtures_file, low_memory=False)
    print(f"\n  {league_code} S{season}: 处理 {len(df)} fixtures 获取赔率...")
    
    # 已有效赔率的 fixture 跳过
    already_have = df['B365AHH'].notna() & (df['B365AHH'] > 1.0)
    need_odds = df[~already_have]['FixtureID'].tolist()
    print(f"  已有赔率: {already_have.sum()}, 需要获取: {len(need_odds)}")
    
    if not need_odds:
        return int(already_have.sum())
    
    # 并发获取赔率（付费版可用高并发）
    odds_results = {}
    with ThreadPoolExecutor(max_workers=10) as executor:
        futures = {executor.submit(fetch_odds_for_fixture, fid): fid for fid in need_odds}
        done = 0
        for future in as_completed(futures):
            result = future.result()
            fid = futures[future]
            if result:
                odds_results[fid] = result
            done += 1
            if done % 50 == 0:
                print(f"  进度: {done}/{len(need_odds)} ({len(odds_results)} 条赔率)")
    
    # 更新 DataFrame
    for fid, odds in odds_results.items():
        mask = df['FixtureID'] == fid
        if mask.any():
            for col in ['B365AHH', 'B365AHA', 'AHh']:
                if odds.get(col) is not None:
                    df.loc[mask, col] = odds[col]
            df.loc[mask, 'HasOdds'] = True
    
    # 保存
    df.to_csv(fixtures_file, index=False, encoding='utf-8')
    valid = int((df['B365AHH'].notna() & (df['B365AHH'] > 1.0)).sum())
    print(f"  {league_code} S{season}: B365有效 {valid} 条")
    return valid


def merge_league_all_seasons(league_code):
    """合并联赛所有赛季数据"""
    season_files = sorted(ODDS_DIR.glob(f'{league_code}_S*_fixtures.csv'))
    if not season_files:
        return 0
    
    dfs = []
    for f in season_files:
        try:
            df = pd.read_csv(f, low_memory=False)
            dfs.append(df)
        except:
            pass
    
    if not dfs:
        return 0
    
    combined = pd.concat(dfs, ignore_index=True)
    combined = combined.drop_duplicates(subset=['FixtureID'])
    combined['Date'] = pd.to_datetime(combined['Date'], errors='coerce')
    combined = combined.sort_values('Date').reset_index(drop=True)
    
    out_file = ODDS_DIR / f'{league_code}_full_odds.csv'
    combined.to_csv(out_file, index=False, encoding='utf-8')
    
    valid = int((combined['B365AHH'].notna() & (combined['B365AHH'] > 1.0)).sum())
    return valid


# ═══════════════════════════════════════════════════════════════════
# 主流程
# ═══════════════════════════════════════════════════════════════════

print("=" * 60)
print("STEP 1: 下载所有 fixture 数据（快速，无赔率）")
print("=" * 60)

for league_code in sorted(LEAGUE_IDS.keys()):
    league_id = LEAGUE_IDS[league_code]
    for season in SEASONS:
        get_fixtures_for_league_season(league_code, league_id, season)

print("\n" + "=" * 60)
print("STEP 2: 获取 B365 让球盘口赔率")
print("=" * 60)

results = {}
for league_code in sorted(LEAGUE_IDS.keys()):
    total_valid = 0
    for season in SEASONS:
        valid = process_league_odds(league_code, season)
        total_valid += valid
    # 合并
    merged = merge_league_all_seasons(league_code)
    results[league_code] = merged

print("\n" + "=" * 60)
print("数据下载完成 - 各联赛B365有效数据量")
print("=" * 60)
print(f"{'联赛':<8} {'有效B365数据':<12} {'状态'}")
for code, valid in sorted(results.items()):
    status = '✅充足' if valid > 200 else ('⚠️不足' if valid > 0 else '❌无')
    print(f"{code:<8} {valid:<12} {status}")
