#!/usr/bin/env python3
"""
合并 fixture 数据与赔率数据，生成 {code}_full_odds.csv
优先级：B365 > Pinnacle
"""
import os
from pathlib import Path
import numpy as np
import pandas as pd

REPO = Path('/root/.openclaw/workspace-flj_bot1/football-predictor-repro')
ODDS_DIR = REPO / 'artifacts' / 'odds_api_history'

# 有 B365 的联赛（B365 优先）
B365_LEAGUES = {'a1', 'br1', 'm1'}  # n1 的 B365AHA 全为 NaN，用 Pinnacle
ALL_LEAGUES = [
    'e0','e1','e2','e2n','e3',
    'd1','d2','f1','f2',
    'i1','i2','sp1','sp2',
    'tr1','p1','b1','g1',
    'n1','n2','sc1','cz1',
    'a1','r1','br1','m1','j1',
]

def merge_league(code):
    # 1. 读取 fixture 数据
    fixture_file = ODDS_DIR / f'{code}_fixtures.csv'
    if not fixture_file.exists():
        print(f"  {code}: 无 fixture 文件，跳过")
        return 0
    
    fixtures = pd.read_csv(fixture_file, low_memory=False)
    fixtures['Date'] = pd.to_datetime(fixtures['Date'], errors='coerce')
    fixtures = fixtures[fixtures['Date'].notna()].copy()
    print(f"  {code}: {len(fixtures)} fixtures")

    # 2. 读取 B365 数据（优先）
    b365_file = ODDS_DIR / f'{code}_odds_api_merged.csv'
    b365_df = pd.DataFrame()
    if b365_file.exists() and code in B365_LEAGUES:
        try:
            b365_df = pd.read_csv(b365_file, low_memory=False)
            b365_df['Date'] = pd.to_datetime(b365_df['Date'], errors='coerce')
            print(f"    B365: {len(b365_df)} 条赔率")
        except:
            b365_df = pd.DataFrame()

    # 3. 读取 Pinnacle 数据
    pinn_file = ODDS_DIR / f'{code}_pinnacle_merged.csv'
    pinn_df = pd.DataFrame()
    if pinn_file.exists():
        try:
            pinn_df = pd.read_csv(pinn_file, low_memory=False)
            pinn_df['Date'] = pd.to_datetime(pinn_df['Date'], errors='coerce')
            print(f"    Pinnacle: {len(pinn_df)} 条赔率")
        except:
            pinn_df = pd.DataFrame()

    if b365_df.empty and pinn_df.empty:
        print(f"  {code}: 无任何赔率数据，跳过")
        return 0

    # 4. 合并：B365 优先，Pinnacle 兜底
    # 先按 Date+HomeTeam+AwayTeam 匹配 B365
    merged = fixtures.copy()
    
    if not b365_df.empty:
        b365_sel = b365_df[['Date','HomeTeam','AwayTeam','AHh','B365AHH','B365AHA']].copy()
        b365_sel = b365_sel.dropna(subset=['Date'])
        merged = merged.merge(b365_sel, on=['Date','HomeTeam','AwayTeam'], how='left', suffixes=('','_b365'))

    if not pinn_df.empty:
        pinn_sel = pinn_df[['Date','HomeTeam','AwayTeam','AHh','B365AHH','B365AHA']].copy()
        pinn_sel = pinn_sel.dropna(subset=['Date'])
        # Pinnacle 列名相同，需要区分
        merged = merged.merge(
            pinn_sel.rename(columns={'AHh':'AHh_p','B365AHH':'B365AHH_p','B365AHA':'B365AHA_p'}),
            on=['Date','HomeTeam','AwayTeam'], how='left'
        )
        # 填充空值：B365 优先，Pinnacle 兜底
        if 'AHh_b365' in merged.columns:
            merged['AHh'] = merged['AHh'].fillna(merged['AHh_p'])
            merged['B365AHH'] = merged['B365AHH'].fillna(merged['B365AHH_p'])
            merged['B365AHA'] = merged['B365AHA'].fillna(merged['B365AHA_p'])
            merged = merged.drop(columns=[c for c in merged.columns if c.endswith('_p') or c.endswith('_b365') and c not in ['AHh','B365AHH','B365AHA']])
        else:
            merged['AHh'] = merged.get('AHh_p', pd.Series(dtype=float))
            merged['B365AHH'] = merged.get('B365AHH_p', pd.Series(dtype=float))
            merged['B365AHA'] = merged.get('B365AHA_p', pd.Series(dtype=float))
            for c in [c for c in merged.columns if '_p' in c]:
                del merged[c]
    
    # 清理
    for c in ['AHh_p','B365AHH_p','B365AHA_p','AHh_b365','B365AHH_b365','B365AHA_b365']:
        if c in merged.columns:
            del merged[c]

    # 5. 过滤有效数据
    before = len(merged)
    merged = merged[merged['FTHG'].notna() & merged['FTAG'].notna()]
    merged = merged[merged['B365AHH'].notna() & (merged['B365AHH'] > 1)]
    merged = merged[merged['B365AHA'].notna() & (merged['B365AHA'] > 1)]
    merged = merged[merged['AHh'].notna()]
    merged = merged.sort_values('Date').reset_index(drop=True)

    # 6. 保存到两个文件（pro_v5 和 ah_1year_v4 共用）
    out_file = ODDS_DIR / f'{code}_full_odds.csv'
    merged.to_csv(out_file, index=False, encoding='utf-8')
    
    # also create odds_api_merged for ah_1year model (same content, different name)
    odds_api_file = ODDS_DIR / f'{code}_odds_api_merged.csv'
    merged.to_csv(odds_api_file, index=False, encoding='utf-8')
    
    print(f"  → {out_file.name}: {before} → {len(merged)} 条有效数据")
    return len(merged)

print('=' * 60)
print('合并 Fixture + 赔率数据')
print('=' * 60)
total = 0
for code in ALL_LEAGUES:
    n = merge_league(code)
    total += n

print(f'\n✅ 完成！共 {total} 条有效数据')
print('文件: {code}_full_odds.csv (26个联赛)')
