"""Load martj42 international results into SQLite, reconstruct WC2026 groups, and seed the teams table (groups, confederation, Elo).""" from __future__ import annotations import json import networkx as nx import pandas as pd import config from db import init_db, connect from elo import compute_elo # Confederation of each 2026 qualifier (for UI grouping / sanity only). CONFED = { "Argentina": "CONMEBOL", "Brazil": "CONMEBOL", "Uruguay": "CONMEBOL", "Colombia": "CONMEBOL", "Ecuador": "CONMEBOL", "Paraguay": "CONMEBOL", "Spain": "UEFA", "France": "UEFA", "England": "UEFA", "Portugal": "UEFA", "Germany": "UEFA", "Netherlands": "UEFA", "Belgium": "UEFA", "Croatia": "UEFA", "Switzerland": "UEFA", "Austria": "UEFA", "Turkey": "UEFA", "Scotland": "UEFA", "Norway": "UEFA", "Czech Republic": "UEFA", "Sweden": "UEFA", "Bosnia and Herzegovina": "UEFA", "Morocco": "CAF", "Senegal": "CAF", "Egypt": "CAF", "Algeria": "CAF", "Tunisia": "CAF", "Ivory Coast": "CAF", "Ghana": "CAF", "Cape Verde": "CAF", "South Africa": "CAF", "DR Congo": "CAF", "Japan": "AFC", "South Korea": "AFC", "Iran": "AFC", "Australia": "AFC", "Saudi Arabia": "AFC", "Qatar": "AFC", "Jordan": "AFC", "Uzbekistan": "AFC", "Iraq": "AFC", "United States": "CONCACAF", "Mexico": "CONCACAF", "Canada": "CONCACAF", "Panama": "CONCACAF", "Haiti": "CONCACAF", "Curaçao": "CONCACAF", "New Zealand": "OFC", } def load() -> dict: init_db() df = pd.read_csv(config.RAW / "results.csv") df["date"] = pd.to_datetime(df["date"]) # --- matches table (full history + 2026 fixtures) --- rows = df.copy() rows["stage"] = None wc26_mask = (rows.tournament == config.WC_TOURNAMENT) & ( rows.date >= config.WC_2026_FROM ) rows.loc[wc26_mask, "stage"] = "group" with connect() as conn: conn.execute("DELETE FROM matches") conn.executemany( """INSERT INTO matches(date,home_team,away_team,home_score,away_score, tournament,city,country,neutral,stage) VALUES(?,?,?,?,?,?,?,?,?,?)""", [ ( r.date.strftime("%Y-%m-%d"), r.home_team, r.away_team, None if pd.isna(r.home_score) else int(r.home_score), None if pd.isna(r.away_score) else int(r.away_score), r.tournament, None if pd.isna(r.city) else r.city, None if pd.isna(r.country) else r.country, int(bool(r.neutral)), r.stage, ) for r in rows.itertuples(index=False) ], ) # --- official 2026 group draw (FIFA final draw, 2025-12-05) --- # Letters MUST match the official draw because bracket.py's knockout # template references group letters (W-E = winner of group E, etc.). OFFICIAL_GROUPS = { "A": ["Mexico", "South Korea", "South Africa", "Czech Republic"], "B": ["Canada", "Switzerland", "Qatar", "Bosnia and Herzegovina"], "C": ["Brazil", "Morocco", "Haiti", "Scotland"], "D": ["United States", "Paraguay", "Australia", "Turkey"], "E": ["Germany", "Curaçao", "Ivory Coast", "Ecuador"], "F": ["Netherlands", "Japan", "Sweden", "Tunisia"], "G": ["Belgium", "Egypt", "Iran", "New Zealand"], "H": ["Spain", "Cape Verde", "Saudi Arabia", "Uruguay"], "I": ["France", "Senegal", "Iraq", "Norway"], "J": ["Argentina", "Algeria", "Austria", "Jordan"], "K": ["Portugal", "DR Congo", "Uzbekistan", "Colombia"], "L": ["England", "Croatia", "Ghana", "Panama"], } groups = OFFICIAL_GROUPS # cross-check: every WC2026 fixture team must appear in exactly one group fixture_teams = set(df[wc26_mask].home_team) | set(df[wc26_mask].away_team) grouped = {t for ts in groups.values() for t in ts} missing = fixture_teams - grouped if missing: print(f"WARNING: 赛程中有队不在官方分组里(队名不匹配?): {sorted(missing)}") # --- Elo ratings from full history --- elo = compute_elo(df.dropna(subset=["home_score", "away_score"])) # --- teams table (48 qualifiers) --- team_to_group = {t: g for g, ts in groups.items() for t in ts} with connect() as conn: conn.execute("DELETE FROM teams") for team, grp in sorted(team_to_group.items()): conn.execute( "INSERT INTO teams(name,confederation,elo,group_label) VALUES(?,?,?,?)", (team, CONFED.get(team), round(elo.get(team, 1500.0), 1), grp), ) (config.PROCESSED / "groups.json").write_text( json.dumps(groups, ensure_ascii=False, indent=2), encoding="utf-8" ) summary = { "total_matches": len(df), "played": int(df.home_score.notna().sum()), "wc2026_fixtures": int(wc26_mask.sum()), "groups": len(groups), "teams": len(team_to_group), } print(json.dumps(summary, ensure_ascii=False)) return {"groups": groups, "summary": summary} if __name__ == "__main__": load()