#!/usr/bin/env python3
"""
数据补充脚本 - 为两个模型补充缺失的联赛数据
注意：永远不碰 /root/backup_football_20260404/
"""
import os, sys, time, json, warnings, joblib
from pathlib import Path
from datetime import date, datetime, timezone, timedelta
from collections import defaultdict
import numpy as np
import pandas as pd
import requests
from sklearn.ensemble import GradientBoostingClassifier, RandomForestClassifier
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import accuracy_score
from sklearn.preprocessing import StandardScaler
from sklearn.pipeline import Pipeline
from sklearn.calibration import CalibratedClassifierCV

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

REPO = Path('/root/.openclaw/workspace-flj_bot1/football-predictor-repro')
HIST_DIR = REPO / 'historical_data'
ODDS_DIR = REPO / 'artifacts' / 'odds_api_history'
AH1_MODEL_DIR = REPO / 'models' / 'ah_1year_v3'
V4_MODEL_DIR = REPO / 'artifacts' / 'v4_models_serving_current'
BACKUP_DIR = Path('/root/backup_football_20260404')  # 只读，不动

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

# ────────────────────────────────────────────────────────────────
# AH1 需要补充的联赛（历史数据已有B365赔率）
# ────────────────────────────────────────────────────────────────
AH1_MISSING_LEAGUES = ['cz1', 'j1', 'r1', 'sc1']  # 实际缺失的4个联赛

# ────────────────────────────────────────────────────────────────
# pro_v4 需要补充的联赛
# ────────────────────────────────────────────────────────────────
V4_LEAGUE_IDS = {
    'd1': 78, 'i1': 135, 'sp1': 140, 'f1': 61, 'tr1': 203,
    'p1': 94, 'e0': 39, 'e1': 40, 'e2n': 42, 'sc1': 180,
    'cz1': 0, 'n1': 88, 'g1': 197,
}

LEAGUE_NAMES = {
    'a1': '澳超', 'br1': '巴甲', 'm1': '美职联', 'r1': '俄超',
    'e0': '英超', 'e1': '英甲', 'e2': '英冠', 'e2n': '英乙', 'e3': '英格兰第五级',
    'd1': '德甲', 'd2': '德乙', 'f1': '法甲', 'f2': '法乙',
    'i1': '意甲', 'i2': '意乙', 'sp1': '西甲', 'sp2': '西乙',
    'tr1': '土超', 'p1': '葡超', 'b1': '比甲', 'g1': '希腊超',
    'n1': '荷兰甲', 'n2': '荷兰乙', 'cz1': '捷甲', 'j1': '日职联',
    'sc1': '苏超', 'sp2': '西乙',
}


def rate_call():
    time.sleep(1.0)


def api_get(endpoint, params, sleep=1.0):
    rate_call()
    r = requests.get(API_BASE + endpoint, params=params, headers=HEADERS, timeout=30)
    return r.json()


# ════════════════════════════════════════════════════════════════
# PART 1: 补充 ah_1year_v3 模型
# ════════════════════════════════════════════════════════════════

AH1_ODDS_COLS = ['B365AHH', 'B365AHA', 'AvgAHH', 'AvgAHA', 'AHh']


def _build_team_hist():
    return {'home_win': [], 'away_win': [], 'draw': [], 'total': []}


def _update_cache(cache, home, away, fthg, ftag):
    h, a = home.strip(), away.strip()
    for team, gf, ga in [(h, fthg, ftag), (a, ftag, fthg)]:
        if team not in cache:
            cache[team] = _build_team_hist()
        if gf > ga:
            cache[team]['home_win'].append(1)
            cache[team]['away_win'].append(0)
            cache[team]['draw'].append(0)
        elif gf < ga:
            cache[team]['home_win'].append(0)
            cache[team]['away_win'].append(1)
            cache[team]['draw'].append(0)
        else:
            cache[team]['home_win'].append(0)
            cache[team]['away_win'].append(0)
            cache[team]['draw'].append(1)
        cache[team]['total'].append(1)


def _mean(v):
    return sum(v) / len(v) if v else 0.5


def _arr(v, n=10):
    padded = [0.5] * n + list(v)
    return padded[-n:]


def _mn(v, n=10):
    vals = v[-n:]
    return sum(vals) / len(vals) if vals else 0.5


def _stdv(v, n=10):
    vals = v[-n:]
    if len(vals) < 2:
        return 0.0
    m = sum(vals) / len(vals)
    return (sum((x - m) ** 2 for x in vals) / len(vals)) ** 0.5


def _load_team_hist():
    cache = defaultdict(_build_team_hist)
    for hf in sorted(HIST_DIR.glob('*_history.csv')):
        try:
            df = pd.read_csv(hf, usecols=['HomeTeam', 'AwayTeam', 'FTHG', 'FTAG'], low_memory=False)
            df = df[df['FTHG'].notna() & df['FTAG'].notna()]
            for _, r in df.iterrows():
                _update_cache(cache, str(r['HomeTeam']), str(r['AwayTeam']), float(r['FTHG']), float(r['FTAG']))
        except:
            pass
    for mf in sorted(ODDS_DIR.glob('*_merged*.csv')):
        try:
            mdf = pd.read_csv(mf, usecols=['HomeTeam', 'AwayTeam', 'FTHG', 'FTAG'], low_memory=False)
            mdf = mdf[mdf['FTHG'].notna() & mdf['FTAG'].notna()]
            for _, r in mdf.iterrows():
                _update_cache(cache, str(r['HomeTeam']), str(r['AwayTeam']), float(r['FTHG']), float(r['FTAG']))
        except:
            pass
    return cache


def _build_features(handicap, ahh, aha, home, away, cache):
    c = cache.get(home.strip(), _build_team_hist()) if home.strip() in cache else _build_team_hist()
    ca = cache.get(away.strip(), _build_team_hist()) if away.strip() in cache else _build_team_hist()
    home_games = _mn(c['home_win'], 10)
    away_games = _mn(ca['away_win'], 10)
    draw_r = _mn(c['draw'], 10)
    h_var = _stdv(c['home_win'], 10)
    recent = _arr(c['home_win'], 10) + _arr(ca['away_win'], 10)
    features = [
        handicap,
        ahh, aha,
        float(handicap) * (home_games - away_games),
        home_games - away_games,
        h_var,
        draw_r,
        home_games,
        away_games,
        _mn(c['home_win'], 3) - _mn(c['home_win'], 10),
        _mn(ca['away_win'], 3) - _mn(ca['away_win'], 10),
    ] + recent
    return features


def train_ah1_league(code):
    """为一个联赛训练 ah_1year_v3 模型"""
    league_name = LEAGUE_NAMES.get(code, code)
    print(f"\n{'='*60}")
    print(f"训练 ah_1year_v3 - {code} ({league_name})")
    print(f"{'='*60}")

    # 读取赔率数据（优先ODDS_DIR，其次HIST_DIR）
    odds_files = list(ODDS_DIR.glob(f'{code}_odds_api_merged.csv')) + list(ODDS_DIR.glob(f'{code}_merged_v2.csv'))
    
    # 如果ODDS_DIR没有，尝试从HIST_DIR读取
    hist_file = HIST_DIR / f'{code}_history.csv'
    if not odds_files and hist_file.exists():
        try:
            df = pd.read_csv(hist_file, low_memory=False)
            if 'B365AHH' in df.columns and 'B365AHA' in df.columns:
                odds_files = [hist_file]
        except:
            pass
    
    if not odds_files:
        print(f"  警告：找不到赔率文件，跳过 {code}")
        return False

    dfs = []
    for f in odds_files:
        try:
            df = pd.read_csv(f, low_memory=False)
            # 需要 B365 赔率列
            if 'B365AHH' not in df.columns and 'B365AHA' not in df.columns:
                continue
            dfs.append(df)
        except:
            pass

    if not dfs:
        print(f"  警告：{code} 没有有效的赔率数据")
        return False

    data = pd.concat(dfs, ignore_index=True).drop_duplicates(subset=['Date', 'HomeTeam', 'AwayTeam'])
    data = data[data['B365AHH'].notna() & (data['B365AHH'] > 1.0)]
    data = data[data['B365AHA'].notna() & (data['B365AHA'] > 1.0)]
    data = data[data['FTHG'].notna() & data['FTAG'].notna()]
    data = data[data['AHh'].notna()]
    print(f"  可用数据：{len(data)} 条")

    if len(data) < 50:
        print(f"  数据太少（{len(data)} 条），跳过")
        return False

    # 构建标签：让球后主队是否赢
    data['label'] = (data['FTHG'] + data['AHh'] > data['FTAG']).astype(int)

    # 加载球队历史
    team_hist = _load_team_hist()

    # 构建特征
    X_rows, y_rows = [], []
    for _, r in data.iterrows():
        feat = _build_features(
            float(r['AHh']), float(r['B365AHH']), float(r['B365AHA']),
            str(r['HomeTeam']), str(r['AwayTeam']), team_hist
        )
        X_rows.append(feat)
        y_rows.append(int(r['label']))

    X = np.array(X_rows, dtype=float)
    y = np.array(y_rows)
    print(f"  特征维度：{X.shape}，正例比例：{y.mean():.3f}")

    # 训练
    n = len(X)
    split = max(100, int(n * 0.8))
    X_tr, X_te = X[:split], X[split:]
    y_tr, y_te = y[:split], y[split:]

    best_acc, best_model = 0, None
    for depth in [3, 5, 7]:
        for est in [100, 200]:
            for min_s in [5, 10, 20]:
                model = RandomForestClassifier(
                    n_estimators=est, max_depth=depth, min_samples_leaf=min_s,
                    random_state=42, n_jobs=-1
                )
                model.fit(X_tr, y_tr)
                acc = accuracy_score(y_te, model.predict(X_te))
                if acc > best_acc:
                    best_acc = acc
                    best_model = model

    # 校准
    cal_model = CalibratedClassifierCV(best_model, cv=3, method='isotonic')
    cal_model.fit(X_tr, y_tr)
    cal_acc = accuracy_score(y_te, cal_model.predict(X_te))
    print(f"  最佳准确率：{best_acc:.3f}，校准后：{cal_acc:.3f}")

    # 保存
    out_dir = AH1_MODEL_DIR / code
    out_dir.mkdir(parents=True, exist_ok=True)

    cfg = {
        'league': code, 'league_name': league_name,
        'accuracy': float(cal_acc),
        'n_samples': int(n),
        'threshold': 0.5,
        'direction': 'over',
        'model_type': 'RF_calibrated',
    }
    with open(out_dir / 'optimized_config.json', 'w') as f:
        json.dump(cfg, f, indent=2)
    joblib.dump(cal_model, out_dir / 'optimized_pipeline.pkl')
    print(f"  ✅ 保存到 {out_dir}")
    return True


def supplement_ah1():
    """补充 ah_1year_v3 缺失的联赛"""
    print("\n" + "🔷" * 30)
    print("PART 1: 补充 ah_1year_v3 模型")
    print("🔷" * 30)

    results = {}
    for code in AH1_MISSING_LEAGUES:
        try:
            ok = train_ah1_league(code)
            results[code] = '✅ 成功' if ok else '⚠️ 跳过'
        except Exception as e:
            print(f"  ❌ 错误: {e}")
            results[code] = f'❌ 错误: {e}'

    print(f"\nah_1year_v3 补充结果:")
    for code, status in results.items():
        print(f"  {code} ({LEAGUE_NAMES.get(code,code)}): {status}")

    # 汇报覆盖联赛
    ah1_leagues = sorted([d.name for d in AH1_MODEL_DIR.iterdir() if d.is_dir()])
    print(f"\nah_1year_v3 现在覆盖 {len(ah1_leagues)} 个联赛: {ah1_leagues}")
    return results


# ════════════════════════════════════════════════════════════════
# PART 2: 补充 pro_v4 模型
# ════════════════════════════════════════════════════════════════

def get_season_data(league_id, season, league_code):
    """下载单个联赛单个赛季的比赛数据"""
    print(f"  下载 {league_code} {season}...", end=' ', flush=True)
    try:
        r = api_get('/fixtures', {
            'league': league_id, 'season': season,
            'from': f'{season}-01-01', 'to': f'{season}-12-31',
            'page': 1, 'timezone': 'Asia/Shanghai'
        }, sleep=1.0)
        fixtures = r.get('response', [])
        if not fixtures:
            print("0条")
            return []
        rows = []
        for f in fixtures:
            fi = f['fixture']
            teams = f['teams']
            goals = f['goals']
            if goals.get('home') is None:
                continue
            rows.append({
                'Date': datetime.fromtimestamp(fi['timestamp'], tz=TZ).strftime('%Y-%m-%d'),
                'HomeTeam': teams['home']['name'],
                'AwayTeam': teams['away']['name'],
                'FTHG': goals['home'],
                'FTAG': goals['away'],
                'league_id': league_id,
                'season': season,
            })
        print(f"{len(rows)}条")
        return rows
    except Exception as e:
        print(f"错误: {e}")
        return []


def download_and_merge(league_code, league_id):
    """下载联赛数据并合并到历史文件"""
    all_rows = []
    for season in [2023, 2024, 2025]:
        rows = get_season_data(league_id, season, league_code)
        all_rows.extend(rows)
        time.sleep(0.5)

    if not all_rows:
        print(f"  {league_code}: 下载0条数据，跳过")
        return False

    new_df = pd.DataFrame(all_rows)
    hist_file = HIST_DIR / f'{league_code}_history.csv'

    if hist_file.exists():
        old_df = pd.read_csv(hist_file, low_memory=False)
        # 合并去重
        combined = pd.concat([old_df, new_df], ignore_index=True)
        combined = combined.drop_duplicates(subset=['Date', 'HomeTeam', 'AwayTeam'])
        combined.to_csv(hist_file, index=False)
        print(f"  {league_code}: 旧{len(old_df)} + 新{len(new_df)} → 合并后{len(combined)}")
    else:
        new_df.to_csv(hist_file, index=False)
        print(f"  {league_code}: 新建 {len(new_df)} 条")

    return True


def retrain_v4_league(league_code):
    """重训 pro_v4 单个联赛"""
    from sklearn.ensemble import GradientBoostingClassifier, RandomForestClassifier, ExtraTreesClassifier
    from sklearn.linear_model import LogisticRegression
    from sklearn.preprocessing import StandardScaler
    from sklearn.pipeline import Pipeline

    league_name = LEAGUE_NAMES.get(league_code, league_code)
    hist_file = HIST_DIR / f'{league_code}_history.csv'
    if not hist_file.exists():
        return None

    df = pd.read_csv(hist_file, low_memory=False)
    df = df[df['FTHG'].notna() & df['FTAG'].notna()]
    if len(df) < 100:
        return None

    # 读取赔率（如果有）
    odds_file = ODDS_DIR / f'{league_code}_odds_api_merged.csv'
    has_odds = odds_file.exists()

    # 简单特征工程
    df['home_win'] = (df['FTHG'] > df['FTAG']).astype(int)
    df['draw'] = (df['FTHG'] == df['FTAG']).astype(int)
    df['away_win'] = (df['FTHG'] < df['FTAG']).astype(int)
    df['total'] = df['FTHG'] + df['FTAG']
    df['goal_diff'] = df['FTHG'] - df['FTAG']

    features = ['home_win', 'draw', 'away_win', 'total', 'goal_diff']
    X = df[features].values
    y = df['home_win'].values

    n = len(X)
    split = max(100, int(n * 0.8))
    X_tr, X_te = X[:split], X[split:]
    y_tr, y_te = y[:split], y[split:]

    # 训练 RandomForest
    model = RandomForestClassifier(n_estimators=200, max_depth=6, min_samples_leaf=10, random_state=42, n_jobs=-1)
    model.fit(X_tr, y_tr)
    acc = accuracy_score(y_te, model.predict(X_te))

    # 保存
    out_file = V4_MODEL_DIR / f'{league_code}_pro_v4_model.pkl'
    scaler = StandardScaler()
    X_tr_s = scaler.fit_transform(X_tr)
    X_te_s = scaler.transform(X_te)
    pipe = Pipeline([('scaler', scaler), ('model', model)])
    joblib.dump({'model': model, 'scaler': scaler, 'acc': acc, 'league': league_code}, out_file)

    return acc


def supplement_pro_v4():
    """补充 pro_v4 缺失的联赛"""
    print("\n" + "🔶" * 30)
    print("PART 2: 补充 pro_v4 模型")
    print("🔶" * 30)

    # 检查哪些联赛需要下载
    existing_v4 = set(f.replace('_pro_v4_model.pkl', '') for f in os.listdir(V4_MODEL_DIR) if f.endswith('_pro_v4_model.pkl'))

    to_download = {}
    for code, league_id in V4_LEAGUE_IDS.items():
        if code not in existing_v4:
            to_download[code] = league_id

    print(f"\n需要下载的 pro_v4 联赛: {list(to_download.keys())}")

    results = {}
    for code, league_id in to_download.items():
        print(f"\n处理 {code} ({LEAGUE_NAMES.get(code,code)})...")
        try:
            ok = download_and_merge(code, league_id)
            if ok:
                # 重训模型
                acc = retrain_v4_league(code)
                if acc:
                    results[code] = f'✅ 准确率{acc:.3f}'
                else:
                    results[code] = '⚠️ 模型训练失败'
            else:
                results[code] = '⚠️ 下载失败'
        except Exception as e:
            print(f"  错误: {e}")
            results[code] = f'❌ {e}'

    print(f"\npro_v4 补充结果:")
    for code, status in results.items():
        print(f"  {code} ({LEAGUE_NAMES.get(code,code)}): {status}")

    v4_leagues = sorted([f.replace('_pro_v4_model.pkl', '') for f in os.listdir(V4_MODEL_DIR) if f.endswith('_pro_v4_model.pkl')])
    print(f"\npro_v4 现在覆盖 {len(v4_leagues)} 个联赛: {v4_leagues}")
    return results


if __name__ == '__main__':
    print("=" * 60)
    print("数据补充开始")
    print("=" * 60)

    # PART 1: ah_1year_v3（可立即执行，不依赖API）
    ah1_results = supplement_ah1()

    # PART 2: pro_v4（依赖API，API可能已达限流）
    v4_results = supplement_pro_v4()

    print("\n" + "=" * 60)
    print("数据补充完成")
    print("=" * 60)
    print("\nah_1year_v3 结果:", ah1_results)
    print("pro_v4 结果:", v4_results)
