"""
Tribunale dei tipster — scoring.

Regola le pick (win/loss/void) in modo DETERMINISTICO dai risultati in matches,
poi calcola per ogni tipster:
  - ROI alla quota consigliata (flat stake 1)
  - hit rate vs probabilità implicita media delle quote consigliate
  - CLV 1X2: per le pick 1X2, confronto tra la quota consigliata e la quota
    di chiusura della stessa selezione (quota consigliata > closing = il
    tipster ha preso valore prima del mercato)

Le pick su mercati per cui non abbiamo quote storiche (multigol, ecc.) hanno
solo ROI/hit rate. Prova anche a ri-agganciare le pick senza match_id.

Uso:
    python scripts/score_tipsters.py
"""

import argparse
import sys

import numpy as np
import pandas as pd
import pymysql

import config


def settle(row) -> str | None:
    """Esito deterministico di una pick dai gol (None = non regolabile)."""
    hg, ag = row["hg"], row["ag"]
    if pd.isna(hg) or pd.isna(ag):
        return None
    hg, ag = int(hg), int(ag)
    total = hg + ag
    market, sel = row["market"], (row["selection"] or "").strip().lower()

    if market == "1x2":
        outcome = "1" if hg > ag else ("2" if ag > hg else "x")
        return "win" if sel == outcome else "loss"
    if market == "double_chance":
        outcome = "1" if hg > ag else ("2" if ag > hg else "x")
        ok = {"1x": outcome in "1x", "x2": outcome in "x2", "12": outcome in "12"}
        return ("win" if ok.get(sel) else "loss") if sel in ok else None
    if market == "over_under":
        line = row["line"]
        if pd.isna(line):
            return None
        if total == line:
            return "void"
        hit = total > line
        return "win" if (hit and sel == "over") or (not hit and sel == "under") else "loss"
    if market == "btts":
        both = hg > 0 and ag > 0
        return "win" if (both and sel == "gol") or (not both and sel == "nogol") else "loss"
    if market in ("multigol_total", "multigol_home", "multigol_away"):
        m = pd.Series(sel).str.extract(r"(\d+)\s*-\s*(\d+)").iloc[0]
        if m.isna().any():
            return None
        lo, hi = int(m[0]), int(m[1])
        value = {"multigol_total": total, "multigol_home": hg,
                 "multigol_away": ag}[market]
        return "win" if lo <= value <= hi else "loss"
    return None


def main() -> int:
    parser = argparse.ArgumentParser(description="Scoring del tribunale dei tipster")
    config.add_db_args(parser)
    args = parser.parse_args()

    conn = pymysql.connect(host=args.host, port=args.port, user=args.user,
                           password=args.password, database="tigertips")
    cur = conn.cursor()

    # Ri-aggancio best-effort delle pick orfane (partite arrivate dopo la pick)
    cur.execute("""
        UPDATE tips t
        JOIN matches m ON m.match_date = DATE(t.kickoff)
        JOIN teams h ON h.id = m.home_team_id
        JOIN teams a ON a.id = m.away_team_id
        SET t.match_id = m.id
        WHERE t.match_id IS NULL AND t.kickoff IS NOT NULL
          AND (h.name = t.home_team OR t.home_team LIKE CONCAT('%%', h.name, '%%'))
          AND (a.name = t.away_team OR t.away_team LIKE CONCAT('%%', a.name, '%%'))
    """)
    conn.commit()

    df = pd.read_sql("""
        SELECT t.id, ts.name AS tipster, t.market, t.selection, t.line, t.odds,
               t.outcome, m.home_goals AS hg, m.away_goals AS ag,
               m.avgc_home, m.avgc_draw, m.avgc_away
        FROM tips t
        JOIN tipsters ts ON ts.id = t.tipster_id
        LEFT JOIN matches m ON m.id = t.match_id
    """, conn)
    for c in ("line", "odds", "hg", "ag", "avgc_home", "avgc_draw", "avgc_away"):
        df[c] = pd.to_numeric(df[c], errors="coerce")

    if df.empty:
        print("Nessuna pick nel tribunale.")
        return 0

    # Regolamento esiti
    df["new_outcome"] = df.apply(settle, axis=1)
    to_update = df[df["new_outcome"].notna() & (df["outcome"] == "unsettled")]
    for r in to_update.itertuples(index=False):
        cur.execute("UPDATE tips SET outcome = %s WHERE id = %s",
                    (r.new_outcome, int(r.id)))
    conn.commit()
    df.loc[to_update.index, "outcome"] = to_update["new_outcome"]
    print(f"Pick totali: {len(df)} | regolate ora: {len(to_update)} | "
          f"ancora aperte: {(df['outcome'] == 'unsettled').sum()}")

    settled = df[df["outcome"].isin(["win", "loss", "void"])].copy()
    if settled.empty:
        print("Nessuna pick regolata: il verdetto arriva con le prime partite giocate.")
        return 0

    print("\n=== Verdetto per tipster ===")
    for tipster, grp in settled.groupby("tipster"):
        bets = grp[grp["outcome"] != "void"]
        profit = np.where(bets["outcome"] == "win", bets["odds"] - 1, -1.0)
        valid = bets["odds"].notna()
        roi = profit[valid].mean() if valid.any() else np.nan
        hit = (bets["outcome"] == "win").mean()
        implied = (1 / bets.loc[valid, "odds"]).mean() if valid.any() else np.nan
        print(f"\n{tipster}: {len(grp)} pick regolate")
        print(f"  hit rate: {hit:.1%} (implicita media delle quote: {implied:.1%})")
        print(f"  ROI a stake fisso: {roi:+.1%}")

        # CLV solo per pick 1X2 con closing disponibile
        m1x2 = grp[(grp["market"] == "1x2") & grp["odds"].notna()].copy()
        closing_col = {"1": "avgc_home", "x": "avgc_draw", "2": "avgc_away"}
        clvs = []
        for r in m1x2.itertuples(index=False):
            col = closing_col.get((r.selection or "").lower())
            closing = getattr(r, col) if col else None
            if closing and closing > 1:
                clvs.append(r.odds / closing - 1)
        if clvs:
            print(f"  CLV 1X2 medio: {np.mean(clvs):+.1%} su {len(clvs)} pick "
                  f"(>0 = batte la chiusura)")
        else:
            print("  CLV 1X2: nessuna pick 1X2 con closing disponibile")

    n = len(settled[settled["outcome"] != "void"])
    if n < 100:
        print(f"\nNota: campione ancora piccolo ({n} pick). "
              f"Verdetto affidabile da ~150-200 pick.")
    conn.close()
    return 0


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