#!/usr/bin/env python3
"""合并 Fixture + Pinnacle/B365 让球盘数据，含球队名标准化"""
import os, re
from pathlib import Path
import pandas as pd
import numpy as np

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

# 有 B365 的联赛
B365_LEAGUES = {'a1', 'br1', 'm1'}

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',
]

TEAM_REPL = {
    'manchester utd': 'manchester united', 'man utd': 'manchester united',
    'man city': 'manchester city', 'manchester city': 'manchester city',
    'newcastle utd': 'newcastle united',
    'leeds utd': 'leeds united',
    'sheffield utd': 'sheffield united',
    'brighton': 'brighton and hove albion', 'brighton & ha': 'brighton and hove albion',
    'tottenham': 'tottenham hotspur', 'spurs': 'tottenham hotspur',
    'wolves': 'wolverhampton wanderers',
    'luton': 'luton town', 'luton town': 'luton town',
    'norwich': 'norwich city', 'norwich city': 'norwich city',
    'bournemouth': 'afc bournemouth', 'afc bournemouth': 'afc bournemouth',
    'leicester': 'leicester city', 'leicester city': 'leicester city',
    'west brom': 'west bromwich albion',
    'qpr': 'queens park rangers',
    'cardiff': 'cardiff city', 'birmingham': 'birmingham city',
    'derby': 'derby county', 'huddersfield': 'huddersfield town',
    'bristol': 'bristol city', 'swindon': 'swindon town',
    'portsmouth': 'portsmouth', 'oxford': 'oxford united',
    'plymouth': 'plymouth argyle', 'lincoln': 'lincoln city',
    'accrington': 'accrington stanley',
    'shrewsbury': 'shrewsbury town', 'morecambe': 'morecambe',
    'sunderland': 'sunderland', 'ipswich': 'ipswich town',
    'cheltenham': 'cheltenham town', 'exeter': 'exeter city',
    'wycombe': 'wycombe wanderers', 'fleetwood': 'fleetwood town',
    'mkdon': 'miltonkeynesdons', 'mk dons': 'miltonkeynesdons',
    'colchester': 'colchester united', 'oldham': 'oldham athletic',
    'crawley': 'crawley town', 'rochdale': 'rochdale',
    'newport': 'newport county', 'salisbury': 'salisbury city',
    'west ham': 'west ham united', 'chelsea': 'chelsea',
    'liverpool': 'liverpool', 'arsenal': 'arsenal',
    'everton': 'everton', 'fulham': 'fulham',
    'aston villa': 'aston villa', 'leicester city': 'leicester city',
    'wolverhampton': 'wolverhampton wanderers', 'wolverhampton wanderers': 'wolverhampton wanderers',
}

def norm_team(name):
    n = str(name).lower().strip()
    for old, new in TEAM_REPL.items():
        n = n.replace(old, new)
    n = re.sub(r'[^a-z0-9]', '', n)
    return n

def merge_league(code):
    fix_file = ODDS_DIR / f'{code}_fixtures.csv'
    if not fix_file.exists():
        print(f'  {code}: 无fixture文件')
        return 0
    
    fixtures = pd.read_csv(fix_file, low_memory=False)
    fixtures['Date'] = pd.to_datetime(fixtures['Date'], errors='coerce').dt.strftime('%Y-%m-%d')
    fixtures = fixtures[fixtures['Date'].notna() & fixtures['FTHG'].notna() & fixtures['FTAG'].notna()].copy()
    if len(fixtures) == 0:
        print(f'  {code}: 无有效fixture')
        return 0
    
    # 读取 B365（优先）
    b365_df = pd.DataFrame()
    if code in B365_LEAGUES:
        f = ODDS_DIR / f'{code}_odds_api_merged.csv'
        if f.exists():
            try:
                b365_df = pd.read_csv(f, low_memory=False)
                b365_df['Date'] = pd.to_datetime(b365_df['Date'], errors='coerce').dt.strftime('%Y-%m-%d')
            except: b365_df = pd.DataFrame()
    
    # 读取 Pinnacle
    pinn_df = pd.DataFrame()
    for fname in [f'{code}_pinn_ah.csv', f'{code}_pinnacle_ah.csv']:
        f = ODDS_DIR / fname
        if f.exists():
            try:
                pinn_df = pd.read_csv(f, low_memory=False)
                pinn_df['Date'] = pd.to_datetime(pinn_df['Date'], errors='coerce').dt.strftime('%Y-%m-%d')
                break
            except: pass
    
    if b365_df.empty and pinn_df.empty:
        print(f'  {code}: 无赔率数据')
        return 0
    
    # 准备标准化列
    fixtures['HomeN'] = fixtures['HomeTeam'].apply(norm_team)
    fixtures['AwayN'] = fixtures['AwayTeam'].apply(norm_team)
    
    all_merged = []
    
    # B365 直接匹配
    if not b365_df.empty:
        b365_sel = b365_df[['Date','HomeTeam','AwayTeam','AHh','B365AHH','B365AHA']].dropna(subset=['Date'])
        m1 = fixtures.merge(b365_sel, on=['Date','HomeTeam','AwayTeam'], how='inner')
        all_merged.append(m1)
    
    # Pinnacle 直接匹配
    if not pinn_df.empty:
        pinn_sel = pinn_df[['Date','HomeTeam','AwayTeam','AHh','B365AHH','B365AHA']].dropna(subset=['Date'])
        m2 = fixtures.merge(pinn_sel, on=['Date','HomeTeam','AwayTeam'], how='inner')
        all_merged.append(m2)
        # Pinnacle 标准化匹配
        pinn_sel = pinn_sel.copy()
        pinn_sel['HomeN'] = pinn_sel['HomeTeam'].apply(norm_team)
        pinn_sel['AwayN'] = pinn_sel['AwayTeam'].apply(norm_team)
        m3 = fixtures.merge(pinn_sel[['Date','HomeN','AwayN','AHh','B365AHH','B365AHA']],
                           on=['Date','HomeN','AwayN'], how='inner')
        all_merged.append(m3)
    
    if not all_merged:
        print(f'  {code}: 匹配0条')
        return 0
    
    # 合并去重
    merged = pd.concat(all_merged, ignore_index=True)
    merged = merged.drop_duplicates(subset=['Date','HomeTeam','AwayTeam'])
    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)
    
    # 清理临时列
    for c in ['HomeN','AwayN']:
        if c in merged.columns:
            del merged[c]
    
    out = ODDS_DIR / f'{code}_full_odds.csv'
    merged.to_csv(out, index=False, encoding='utf-8')
    odds_api_out = ODDS_DIR / f'{code}_odds_api_merged.csv'
    merged.to_csv(odds_api_out, index=False, encoding='utf-8')
    
    print(f'  {code}: {len(fixtures)} fixtures → {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} 条有效数据')
