"""
Sincronizza le partite future: scarica fixtures.csv da football-data.co.uk
(prossimi turni con le quote correnti), inserisce le partite nel DB con
result NULL e salva uno snapshot delle quote in odds_snapshots.

Pensato per girare ogni giorno (Task Scheduler). Idempotente: le partite
esistenti vengono aggiornate, e lo snapshot quote viene aggiunto solo se
l'ultimo salvato è più vecchio di --min-snapshot-hours (default 6).

Uso:
    python scripts/sync_fixtures.py
"""

import argparse
import io
import sys
from datetime import datetime

import numpy as np
import pandas as pd
import pymysql

import config
import requests

FIXTURES_URL = "https://www.football-data.co.uk/fixtures.csv"
LEAGUES = ["I1", "I2", "E0", "SP1", "D1", "F1"]


def infer_season(d: pd.Timestamp) -> str:
    y = d.year if d.month >= 7 else d.year - 1
    return f"{y}/{str(y + 1)[2:]}"


def main() -> int:
    parser = argparse.ArgumentParser(description="Sync partite future + snapshot quote")
    parser.add_argument("--min-snapshot-hours", type=float, default=6.0)
    config.add_db_args(parser)
    args = parser.parse_args()

    resp = requests.get(FIXTURES_URL, timeout=30)
    resp.raise_for_status()
    raw = resp.content
    encoding = "utf-8-sig" if raw[:3] == b"\xef\xbb\xbf" else "latin-1"
    df = pd.read_csv(io.BytesIO(raw), encoding=encoding, on_bad_lines="skip")
    df.columns = df.columns.str.strip()
    df = df.dropna(subset=["Div", "Date", "HomeTeam", "AwayTeam"])
    df = df[df["Div"].isin(LEAGUES)]
    if df.empty:
        print("Nessuna partita futura per le nostre leghe in fixtures.csv "
              "(normale fuori stagione).")
        return 0

    df["Date"] = pd.to_datetime(df["Date"], dayfirst=True, format="mixed", errors="coerce")
    df = df.dropna(subset=["Date"])
    print(f"Fixtures trovati: {len(df)} partite "
          f"({df['Date'].min().date()} -> {df['Date'].max().date()})")

    conn = pymysql.connect(host=args.host, port=args.port, user=args.user,
                           password=args.password, database="tigertips")
    cur = conn.cursor()
    cur.execute("SELECT code, id FROM leagues")
    league_ids = dict(cur.fetchall())

    def get(row, col):
        v = row.get(col)
        if v is None or (isinstance(v, float) and np.isnan(v)) or pd.isna(v):
            return None
        return float(v) if isinstance(v, (int, float, np.floating)) else v

    n_new, n_snap = 0, 0
    for _, r in df.iterrows():
        for team_col in ("HomeTeam", "AwayTeam"):
            cur.execute("INSERT IGNORE INTO teams (name) VALUES (%s)", (r[team_col],))
        cur.execute("SELECT id FROM teams WHERE name = %s", (r["HomeTeam"],))
        home_id = cur.fetchone()[0]
        cur.execute("SELECT id FROM teams WHERE name = %s", (r["AwayTeam"],))
        away_id = cur.fetchone()[0]

        cur.execute("""
            INSERT INTO matches (league_id, season, match_date, match_time,
                                 home_team_id, away_team_id,
                                 b365_home, b365_draw, b365_away,
                                 pinnacle_home, pinnacle_draw, pinnacle_away,
                                 avg_home, avg_draw, avg_away,
                                 max_home, max_draw, max_away)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                season = VALUES(season), match_time = VALUES(match_time),
                b365_home = VALUES(b365_home), b365_draw = VALUES(b365_draw),
                b365_away = VALUES(b365_away),
                pinnacle_home = VALUES(pinnacle_home),
                pinnacle_draw = VALUES(pinnacle_draw),
                pinnacle_away = VALUES(pinnacle_away),
                avg_home = VALUES(avg_home), avg_draw = VALUES(avg_draw),
                avg_away = VALUES(avg_away),
                max_home = VALUES(max_home), max_draw = VALUES(max_draw),
                max_away = VALUES(max_away)
        """, (league_ids[r["Div"]], infer_season(r["Date"]),
              r["Date"].date(), get(r, "Time"), home_id, away_id,
              get(r, "B365H"), get(r, "B365D"), get(r, "B365A"),
              get(r, "PSH"), get(r, "PSD"), get(r, "PSA"),
              get(r, "AvgH"), get(r, "AvgD"), get(r, "AvgA"),
              get(r, "MaxH"), get(r, "MaxD"), get(r, "MaxA")))
        n_new += cur.rowcount == 1  # 1 = insert, 2 = update

        cur.execute("SELECT id FROM matches WHERE match_date = %s "
                    "AND home_team_id = %s AND away_team_id = %s",
                    (r["Date"].date(), home_id, away_id))
        match_id = cur.fetchone()[0]

        # Snapshot quote: priorità media di mercato > Pinnacle > Bet365
        for h, d, a, source in [("AvgH", "AvgD", "AvgA", "fd-avg"),
                                ("PSH", "PSD", "PSA", "fd-pinnacle"),
                                ("B365H", "B365D", "B365A", "fd-b365")]:
            oh, od, oa = get(r, h), get(r, d), get(r, a)
            if oh and od and oa and oh > 1 and od > 1 and oa > 1:
                cur.execute("""
                    SELECT MAX(taken_at) FROM odds_snapshots
                    WHERE match_id = %s AND stage = 'pre'
                """, (match_id,))
                last = cur.fetchone()[0]
                age_h = ((datetime.now() - last).total_seconds() / 3600
                         if last else None)
                if age_h is None or age_h >= args.min_snapshot_hours:
                    over = 1 / oh + 1 / od + 1 / oa
                    cur.execute("""
                        INSERT INTO odds_snapshots
                            (match_id, taken_at, stage, source,
                             odds_home, odds_draw, odds_away,
                             prob_home, prob_draw, prob_away)
                        VALUES (%s, NOW(), 'pre', %s, %s, %s, %s, %s, %s, %s)
                    """, (match_id, source, oh, od, oa,
                          round(1 / oh / over, 4), round(1 / od / over, 4),
                          round(1 / oa / over, 4)))
                    n_snap += 1
                break

    conn.commit()
    print(f"Partite nuove inserite: {n_new} | snapshot quote salvati: {n_snap}")
    conn.close()
    return 0


if __name__ == "__main__":
    sys.exit(main())