#!/usr/bin/env python3
"""
数据补充脚本 - 为 pro_v5 和 ah_1year_v4 补充22个联赛的B365让球盘数据
策略：
- 优先用 OddsPortal 历史数据（最快）
- 辅以 API-Football Odds 端点
- 先下载各联赛最新赛季（有fixture数据即可，不需要每场都赔率）
"""
import os, sys, time, json, warnings
from pathlib import Path
from datetime import datetime, timezone, timedelta
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'
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 = {
    'd1':78,'i1':135,'sp1':140,'f1':61,'tr1':203,
    'p1':94,'e0':39,'e1':40,'e2':41,'e2n':42,'sc1':180,
    'b1':145,'g1':197,'n1':88,'cz1':0,'n2':89,
    'i2':136,'sp2':141,'d2':79,'f2':61,
    'a1':279,'br1':71,'m1':253,'r1':164,'j1':98,
}

# ─── 当前有B365的联赛 ──────────────────────────────────────────
HAS_B365 = {'a1', 'br1', 'm1', 'r1'}

# ─── 需要补充的联赛 ────────────────────────────────────────────
NEED_B365 = [c for c in LEAGUE_IDS if c not in HAS_B365]

print(f"当前已有B365数据: {sorted(HAS_B365)}")
print(f"需要补充: {len(NEED_B365)} 个联赛")

# ═══════════════════════════════════════════════════════════════════
# 方法1: 从 OddsPortal 爬历史赔率（不耗API额度）
# ═══════════════════════════════════════════════════════════════════

def fetch_oddsportal(league_code, season):
    """
    从 OddsPortal 爬历史B365让球盘数据
    返回 DataFrame 或 None
    """
    try:
        from playwright.sync_api import sync_playwright
    except ImportError:
        return None

    url_map = {
        'e0': 'england/premier-league',
        'd1': 'germany/bundesliga',
        'i1': 'italy/serie-a',
        'sp1': 'spain/la-liga',
        'f1': 'france/ligue-1',
        'tr1': 'turkey/super-lig',
        'p1': 'portugal/primeira-liga',
        'sc1': 'scotland/premier-league',
        'e1': 'england/league-one',
        'e2': 'england/championship',
        'd2': 'germany/2-bundesliga',
        'f2': 'france/ligue-2',
        'i2': 'italy/serie-b',
        'sp2': 'spain/laliga2',
        'b1': 'belgium/jupiler-pro-league',
        'g1': 'greece/super-league',
        'n1': 'netherlands/eredivisie',
        'n2': 'netherlands/eerste-divisie',
        'cz1': 'czech-republic/1-liga',
        'j1': 'japan/j-league',
        'br1': 'brazil/serie-a',
        'a1': 'australia/a-league',
        'm1': 'usa/mls',
        'r1': 'russia/premier-liga',
    }
    path = url_map.get(league_code)
    if not path:
        return None

    results = []
    with sync_playwright() as p:
        browser = p.chromium.launch(headless=True, args=['--no-sandbox'])
        page = browser.new_page(viewport={'width':1280,'height':900})

        try:
            page.goto(f'https://www.oddsportal.com/football/{path}-{season}/results/', timeout=20000)
            page.wait_for_load_state('domcontentloaded', timeout=10000)
            page.wait_for_timeout(3000)

            html = page.inner_html('body')
            browser.close()
        except:
            browser.close()
            return None

    # 解析HTML
    import re
    rows = []
    # OddsPortal 表格结构
    events = re.findall(r'<tr[^>]*class="[^"]*odd[^"]*"[^>]*>(.*?)</tr>', html, re.DOTALL)
    for event in events[:200]:
        try:
            time_m = re.search(r'(\d{2}:\d{2})', event)
            home_m = re.search(r'class="name[^"]*"><a[^>]*>([^<]+)', event)
            away_m = re.search(r'class="name[^"]*">.*?<a[^>]*>([^<]+)', event)
            score_m = re.search(r'score[^>]*>(\d+)\s*-\s*(\d+)<', event)
            # B365 让球盘口
            ah_m = re.search(r'class="handicap[^"]*">([^<]+)<', event)
            b365h_m = re.search(r'B365[^>]*>(\d+\.\d+)<', event)

            if time_m and home_m and score_m:
                time_str = time_m.group(1)
                home = home_m.group(1).strip()
                away_m2 = re.search(r'</a>(.*?)</td>', event)
                away = away_m2.group(1).strip() if away_m2 else ''
                score = score_m.group(1, 2)
                handicap = ah_m.group(1).strip() if ah_m else ''
                b365h = b365h_m.group(1) if b365h_m else ''

                rows.append({
                    'Date': f'{season}-01-01',  # 需要解析实际日期
                    'HomeTeam': home,
                    'AwayTeam': away,
                    'FTHG': int(score[0]),
                    'FTAG': int(score[1]),
                    'AHh': float(handicap) if handicap and handicap.replace('-','').replace('+','').replace('.','').isdigit() else 0.0,
                    'B365H': float(b365h) if b365h and b365h.replace('.','').isdigit() else None,
                })
        except:
            continue

    if not rows:
        return None
    return pd.DataFrame(rows)


# ═══════════════════════════════════════════════════════════════════
# 方法2: 用 API-Football 获取 Odds（限流严重，每100请求/天）
# ═══════════════════════════════════════════════════════════════════

def get_league_season_ids(league_id):
    """获取联赛赛季信息"""
    time.sleep(1.0)
    r = requests.get(f'{BASE}/leagues', params={'id': league_id, 'season': 2025}, headers=HEADERS, timeout=30)
    if r.status_code != 200:
        return []
    data = r.json().get('response', [])
    return data


def get_fixtures_with_odds(league_id, season, league_code):
    """
    获取联赛某赛季所有有赔率的fixture
    每次请求可获30场（1页），需要翻页
    """
    all_rows = []
    page = 1
    while True:
        time.sleep(1.0)
        r = requests.get(f'{BASE}/fixtures', params={
            'league': league_id,
            'season': season,
            'from': f'{season}-01-01',
            'to': f'{season}-12-31',
            'page': page,
            'timezone': 'Asia/Shanghai',
        }, headers=HEADERS, timeout=30)
        if r.status_code != 200:
            break
        data = r.json()
        fixtures = data.get('response', [])
        if not fixtures:
            break

        for fix in fixtures:
            fi = fix['fixture']
            teams = fix['teams']
            goals = fix.get('goals', {})
            odds = fix.get('odds', {})

            if goals.get('home') is None:
                continue

            fthg = goals['home']
            ftag = goals['away']
            kickoff = datetime.fromtimestamp(fi['timestamp'], tz=TZ).strftime('%Y-%m-%d')
            home = teams['home']['name']
            away = teams['away']['name']

            # 找B365让球盘
            b365hh = b365ha = ahh = None
            bookmakers = odds.get('bookmakers', [])
            for bm in bookmakers:
                if bm.get('name') == 'Bovada':  # 改用 Bovada 或其他
                    for bm_odds in bm.get('odds', []):
                        label = bm_odds.get('label', '')
                        value = bm_odds.get('value')
                        if label == 'AH' or 'handicap' in str(bm_odds):
                            if value:
                                ahh = float(value)
                        elif label == 'Home' or 'home' in label.lower():
                            b365hh = float(value) if value else None
                        elif label == 'Away' or 'away' in label.lower():
                            b365ha = float(value) if value else None

            if b365hh and b365ha:
                all_rows.append({
                    'Date': kickoff,
                    'HomeTeam': home,
                    'AwayTeam': away,
                    'FTHG': fthg,
                    'FTAG': ftag,
                    'AHh': ahh or 0.0,
                    'B365AHH': b365hh,
                    'B365AHA': b365ha,
                })

        # 检查是否有下一页
        paging = data.get('paging', {})
        total_pages = int(paging.get('total', 1))
        if page >= total_pages:
            break
        page += 1

    return all_rows


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

def download_league(league_code):
    """下载单个联赛的赔率数据"""
    league_id = LEAGUE_IDS.get(league_code)
    if not league_id:
        print(f"  {league_code}: 未知联赛ID")
        return 0

    out_file = ODDS_DIR / f'{league_code}_odds_api_merged.csv'
    existing = 0
    if out_file.exists():
        df_ex = pd.read_csv(out_file, nrows=3)
        if 'B365AHH' in df_ex.columns:
            existing = len(pd.read_csv(out_file, usecols=['B365AHH']))
            existing = int((pd.read_csv(out_file, usecols=['B365AHH'])['B365AHH'].notna() & (pd.read_csv(out_file, 'B365AHH') > 1.0)).sum())

    print(f"\n{'='*50}")
    print(f"下载 {league_code} (league_id={league_id})，现有B365数据: {existing}条")
    print(f"{'='*50}")

    # 策略：优先爬 OddsPortal，不耗API额度
    # 如果失败，用 API-Football（每天100请求限额）

    scraped = 0
    for season in [2024, 2025]:
        rows = fetch_oddsportal(league_code, season)
        if rows and len(rows) > 0:
            new_count = len(rows)
            scraped += new_count
            print(f"  OddsPortal {season}: 爬取 {new_count} 条")

            if out_file.exists():
                old = pd.read_csv(out_file, low_memory=False)
                combined = pd.concat([old, rows], ignore_index=True).drop_duplicates(subset=['Date','HomeTeam','AwayTeam'])
            else:
                combined = rows
            combined.to_csv(out_file, index=False, encoding='utf-8')
            print(f"  已保存到 {out_file}，累计 {len(combined)} 条")

    if scraped > 0:
        return scraped

    # 如果 OddsPortal 失败，用 API
    print(f"  OddsPortal 失败，改用 API-Football...")
    total_new = 0
    for season in [2024, 2025]:
        rows = get_fixtures_with_odds(league_id, season, league_code)
        if rows:
            df_new = pd.DataFrame(rows)
            if out_file.exists():
                df_old = pd.read_csv(out_file, low_memory=False)
                df_combined = pd.concat([df_old, df_new], ignore_index=True).drop_duplicates(subset=['Date','HomeTeam','AwayTeam'])
            else:
                df_combined = df_new
            df_combined.to_csv(out_file, index=False, encoding='utf-8')
            total_new += len(rows)
            print(f"  赛季{season}: 新增 {len(rows)} 条，保存到 {out_file}")

    return total_new


def check_odds_file(code):
    """检查赔率文件是否有有效B365数据"""
    f = ODDS_DIR / f'{code}_odds_api_merged.csv'
    if not f.exists():
        return 0
    try:
        df = pd.read_csv(f, usecols=['B365AHH'], low_memory=False)
        return int((df['B365AHH'].notna() & (df['B365AHH'] > 1.0)).sum())
    except:
        return 0


def main():
    print("=" * 60)
    print("开始数据补充")
    print("=" * 60)

    results = {}
    for code in sorted(NEED_B365):
        before = check_odds_file(code)
        new_count = download_league(code)
        after = check_odds_file(code)
        results[code] = {'before': before, 'new': new_count, 'after': after}
        print(f"\n{code}: 补充前{before}条 → 新增{new_count}条 → 共{after}条")

    # 最终报告
    print("\n" + "=" * 60)
    print("数据补充完成报告")
    print("=" * 60)
    print(f"{'联赛':<8} {'补充前':<10} {'新增':<8} {'最终':<8} {'状态'}")
    print("-" * 50)
    for code, r in sorted(results.items()):
        status = '✅充足' if r['after'] > 100 else ('⚠️不足' if r['after'] > 0 else '❌失败')
        print(f"{code:<8} {r['before']:<10} {r['new']:<8} {r['after']:<8} {status}")

    # 保存报告
    with open(REPO / 'data_supplement_report.json', 'w') as f:
        json.dump(results, f, indent=2)
    print(f"\n报告已保存到 {REPO / 'data_supplement_report.json'}")


if __name__ == '__main__':
    # 只下载前3个联赛测试
    if '--test' in sys.argv:
        for code in NEED_B365[:3]:
            download_league(code)
    else:
        main()
