#!/usr/bin/env python3
"""从 football-data.co.uk 批量下载所有联赛历史数据（含OU赔率）"""
from __future__ import annotations
import io, time, zipfile
from pathlib import Path
import requests
import pandas as pd

REPO_ROOT = Path(__file__).resolve().parents[1]
HIST_DIR = REPO_ROOT / 'historical_data'
FD_BASE = 'https://www.football-data.co.uk'

LEAGUES = [
    ('E0', 'e0', '英格兰超级联赛'),
    ('E1', 'e1', '英格兰冠军联赛'),
    ('E2', 'e2', '英格兰足球联赛'),
    ('E3', 'e3', '英格兰足球联赛第四级'),
    ('EC', 'e2n', '英格兰全国联赛'),
    ('D1', 'd1', '德国甲级联赛'),
    ('D2', 'd2', '德国乙级联赛'),
    ('I1', 'i1', '意大利甲级联赛'),
    ('I2', 'i2', '意大利乙级联赛'),
    ('SP1', 'sp1', '西班牙甲级联赛'),
    ('SP2', 'sp2', '西班牙乙级联赛'),
    ('F1', 'f1', '法国甲级联赛'),
    ('F2', 'f2', '法国乙级联赛'),
    ('N1', 'n1', '荷兰甲级联赛'),
    ('N2', 'n2', '荷兰乙级联赛'),
    ('B1', 'b1', '比利时甲级联赛'),
    ('P1', 'p1', '葡萄牙超级联赛'),
    ('T1', 'tr1', '土耳其超级联赛'),
    ('G1', 'g1', '希腊超级联赛'),
    ('SC0', 'sc1', '苏格兰超级联赛'),
    ('R1', 'r1', '俄罗斯超级联赛'),
    ('A1', 'a1', '奥地利超级联赛'),
    ('S1', 's1', '瑞士超级联赛'),
    ('DK1', 'dk1', '丹麦超级联赛'),
    ('NO1', 'no1', '挪威超级联赛'),
    ('NO2', 'no2', '挪威甲级联赛'),
    ('SW1', 'sw1', '瑞典超级联赛'),
    ('SW2', 'sw2', '瑞典甲级联赛'),
    ('PL1', 'pl1', '波兰超级联赛'),
    ('HU1', 'hu1', '匈牙利甲级联赛'),
    ('RO1', 'ro1', '罗马尼亚甲级联赛'),
    ('RS1', 'rs1', '塞尔维亚超级联赛'),
    ('BG1', 'bg1', '保加利亚超级联赛'),
    ('IL1', 'il1', '以色列超级联赛'),
    ('SK1', 'sk1', '斯洛伐克超级联赛'),
    ('FI1', 'fi1', '芬兰超级联赛'),
    ('IR1', 'ir1', '爱尔兰超级联赛'),
    ('IS1', 'is1', '冰岛超级联赛'),
]

LOCAL_SKIP = {
    'e1', 'e2', 'e2n', 'e3', 'd1', 'd2', 'f1', 'f2',
    'i1', 'sp1', 'tr1', 'p1', 'sc1', 'cz1',
}


def get_seasons():
    r = requests.get(f'{FD_BASE}/downloadm.php', timeout=30)
    r.raise_for_status()
    seasons = set()
    parts = r.text.split('"')
    for p in parts:
        if p.startswith('mmz4281/') and p.endswith('/data.zip'):
            season = p.split('/')[1]
            seasons.add(season)
    return sorted(seasons)


def check_local_has_ou(code):
    p = HIST_DIR / f'{code}_history.csv'
    if not p.exists():
        return False
    try:
        df = pd.read_csv(p, nrows=1, encoding='utf-8-sig')
        for c in df.columns:
            if '>2.5' in c or '<2.5' in c:
                return True
    except Exception:
        pass
    return False


def get_local_row_count(code):
    p = HIST_DIR / f'{code}_history.csv'
    try:
        return sum(1 for _ in open(p)) - 1
    except Exception:
        return 0


def download_one(league_csv, local_code, seasons):
    local_path = HIST_DIR / f'{local_code}_history.csv'
    existing_keys = set()
    if local_path.exists():
        try:
            df = pd.read_csv(local_path, encoding='utf-8-sig', low_memory=False, nrows=300)
            if 'Date' in df.columns and 'HomeTeam' in df.columns:
                existing_keys = set()
                for _, row in df.iterrows():
                    key = (str(row.get('Date', '')), str(row.get('HomeTeam', '')), str(row.get('AwayTeam', '')))
                    existing_keys.add(key)
        except Exception:
            pass

    all_dfs = []
    total_new = 0
    for season in seasons:
        url = f'{FD_BASE}/mmz4281/{season}/data.zip'
        try:
            r = requests.get(url, timeout=30)
            if r.status_code != 200:
                continue
            z = zipfile.ZipFile(io.BytesIO(r.content))
            csv_file = f'{league_csv}.csv'
            if csv_file not in z.namelist():
                z.close()
                continue
            df = pd.read_csv(z.open(csv_file), encoding='utf-8-sig', low_memory=False)
            z.close()
            if df.empty:
                continue
            df['season'] = season
            if existing_keys:
                before = len(df)
                new_rows = []
                for _, row in df.iterrows():
                    key = (str(row.get('Date', '')), str(row.get('HomeTeam', '')), str(row.get('AwayTeam', '')))
                    if key not in existing_keys:
                        new_rows.append(row)
                df = pd.DataFrame(new_rows)
                total_new += before - len(df)
            all_dfs.append(df)
        except Exception as e:
            continue

    if not all_dfs:
        return {'status': 'FAILED', 'total': 0, 'new': 0, 'ou_cols': 0}

    if local_path.exists() and not all_dfs:
        combined = pd.read_csv(local_path, encoding='utf-8-sig', low_memory=False)
    else:
        combined = pd.concat(all_dfs, ignore_index=True, sort=False)
        combined.to_csv(local_path, index=False, encoding='utf-8-sig')

    ou_cols = 0
    for c in combined.columns:
        if '>2.5' in c or '<2.5' in c:
            ou_cols += 1

    return {
        'status': 'OK' if ou_cols > 0 else 'NO_OU',
        'total': len(combined),
        'new': total_new,
        'ou_cols': ou_cols,
    }


def main():
    HIST_DIR.mkdir(parents=True, exist_ok=True)
    seasons = get_seasons()
    print(f'发现 {len(seasons)} 个赛季: {seasons[0]} ~ {seasons[-1]}')
    print()

    results = []
    total = len(LEAGUES)
    for i, (csv_name, local_code, name) in enumerate(LEAGUES):
        print(f'[{i+1}/{total}] {name} ({local_code})...', end=' ', flush=True)

        if local_code in LOCAL_SKIP and check_local_has_ou(local_code):
            rows = get_local_row_count(local_code)
            print(f'跳过(本地已有 {rows} 行)')
            results.append({'league': name, 'local_code': local_code, 'status': 'SKIP', 'total': rows})
            continue

        result = download_one(csv_name, local_code, seasons)
        result['league'] = name
        result['local_code'] = local_code
        results.append(result)
        print(f'{result["status"]} | 总={result["total"]} 新增={result["new"]} OU={result["ou_cols"]}')
        time.sleep(0.3)

    print()
    print('========== 下载汇总 ==========')
    ok = [r for r in results if r['status'] == 'OK']
    no_ou = [r for r in results if r['status'] == 'NO_OU']
    failed = [r for r in results if r['status'] == 'FAILED']
    skip = [r for r in results if r['status'] == 'SKIP']
    print(f'✅ 下载成功: {len(ok)} 联赛')
    print(f'⏭️  跳过(本地已有): {len(skip)} 联赛')
    print(f'⚠️  无OU数据: {len(no_ou)} 联赛')
    print(f'❌ 下载失败: {len(failed)} 联赛')
    for r in ok:
        print(f'  ✅ {r["league"]} ({r["local_code"]}): {r["total"]}行, 新增{r["new"]}行, {r["ou_cols"]}个OU列')
    for r in skip:
        print(f'  ⏭️  {r["league"]} ({r["local_code"]}): 跳过(本地已有{r["total"]}行)')
    if no_ou:
        print(f'  ⚠️ 无OU: {[r["local_code"] for r in no_ou]}')
    if failed:
        print(f'  ❌ 失败: {[r["local_code"] for r in failed]}')


if __name__ == '__main__':
    main()
