"""
Tribunale dei tipster — raccoglitore per mondopengwin.it.

Scarica gli articoli di pronostico (pubblici, uno per partita), estrae con
l'LLM la pick in formato strutturato e la salva nella tabella tips con il
NOSTRO timestamp di raccolta (scraped_at): è quello che rende la misurazione
onesta ("che quote c'erano quando potevamo agire sulla pick?").

L'LLM qui fa SOLO estrazione di testo -> JSON (nessuna ricerca web, nessuna
opinione): mercato, selezione, quota consigliata, partita, orario.

Uso:
    python scripts/tipster_pengwin.py                       # tutte le leghe
    python scripts/tipster_pengwin.py --leagues serie-a --max-articles 3
"""

import argparse
import json
import re
import sys
from datetime import datetime
from pathlib import Path

import anthropic
import pymysql

import config
import requests
from bs4 import BeautifulSoup

sys.path.insert(0, str(Path(__file__).resolve().parent))
from agent_formazioni import load_api_key  # riuso il caricamento della chiave

BASE = "https://www.mondopengwin.it"
LEAGUE_SLUGS = ["serie-a", "serie-b", "premier-league", "bundesliga",
                "la-liga", "ligue-1", "champions-league"]
DEFAULT_MODEL = "claude-opus-4-8"
TIPSTER_NAME = "Pengwin"

HEADERS = {"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
                         "AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0 Safari/537.36"}

SCHEMA = """
CREATE TABLE IF NOT EXISTS tipsters (
    id          SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50)  NOT NULL UNIQUE,
    source_url  VARCHAR(200) NULL,
    notes       VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tips (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tipster_id     SMALLINT UNSIGNED NOT NULL,
    match_id       INT UNSIGNED NULL,
    article_url    VARCHAR(300) NOT NULL,
    published_date DATE     NULL,
    scraped_at     DATETIME NOT NULL,
    home_team      VARCHAR(60) NULL,
    away_team      VARCHAR(60) NULL,
    kickoff        DATETIME NULL,
    market         VARCHAR(30) NULL,
    selection      VARCHAR(60) NULL,
    line           DECIMAL(4,1) NULL,
    odds           DECIMAL(7,3) NULL,
    raw_text       VARCHAR(300) NULL,
    outcome        ENUM('win','loss','void','unsettled') NOT NULL DEFAULT 'unsettled',
    UNIQUE KEY uq_tip (tipster_id, article_url),
    KEY idx_match (match_id),
    CONSTRAINT fk_tips_tipster FOREIGN KEY (tipster_id) REFERENCES tipsters (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""

EXTRACT_PROMPT = """Estrai dal seguente articolo di pronostico calcistico i dati
strutturati. Rispondi SOLO con un blocco ```json``` con questo schema:
{
  "home": "nome squadra di casa",
  "away": "nome squadra ospite",
  "kickoff": "YYYY-MM-DD HH:MM" oppure null se non deducibile,
  "published_date": "YYYY-MM-DD" oppure null,
  "tip": {
    "raw": "la frase esatta del pronostico consigliato",
    "market": "1x2" | "double_chance" | "over_under" | "btts" |
              "multigol_total" | "multigol_home" | "multigol_away" | "other",
    "selection": "1"|"X"|"2"|"1X"|"X2"|"12"|"over"|"under"|"gol"|"nogol"|"1-3" ecc.,
    "line": 2.5 (solo per over_under, altrimenti null),
    "odds": 1.30 (la quota consigliata, null se assente)
  }
}
Il pronostico consigliato è tipicamente nella sezione "ANALISI E PRONOSTICO".
Se l'articolo contiene più consigli, estrai SOLO quello principale/finale.
Se non c'è un pronostico chiaro, "tip" deve essere null. Non inventare quote.

ARTICOLO:
"""


def get_article_urls(session: requests.Session, slugs: list[str]) -> list[str]:
    urls = []
    for slug in slugs:
        try:
            resp = session.get(f"{BASE}/pronostici/calcio/{slug}/",
                               headers=HEADERS, timeout=30)
            if resp.status_code != 200:
                continue
            soup = BeautifulSoup(resp.text, "lxml")
            for a in soup.select("a[href]"):
                href = a["href"]
                if "statistiche-quote-e-pronostico" in href:
                    if href.startswith("/"):
                        href = BASE + href
                    urls.append(href.split("?")[0].rstrip("/"))
        except requests.RequestException as exc:
            print(f"  [errore indice {slug}] {exc}")
    return sorted(set(urls))


def extract_tip(client: anthropic.Anthropic, model: str, article_text: str) -> dict | None:
    response = client.messages.create(
        model=model, max_tokens=2000,
        messages=[{"role": "user", "content": EXTRACT_PROMPT + article_text[:12000]}])
    if response.stop_reason == "refusal":
        return None
    text = "".join(b.text for b in response.content if b.type == "text")
    m = re.search(r"```json\s*(\{.*?\})\s*```", text, re.DOTALL)
    raw = m.group(1) if m else text[text.find("{"):text.rfind("}") + 1]
    try:
        return json.loads(raw)
    except json.JSONDecodeError:
        return None


def find_match_id(cur, home: str, away: str, kickoff: str | None) -> int | None:
    """Aggancio best-effort alla tabella matches (nomi esatti o contenuti)."""
    if not (home and away):
        return None
    date_clause = "AND m.match_date = %s" if kickoff else ""
    params = [f"%{home[:12]}%", f"%{away[:12]}%"]
    if kickoff:
        params.append(kickoff[:10])
    cur.execute(f"""
        SELECT m.id FROM matches m
        JOIN teams h ON h.id = m.home_team_id
        JOIN teams a ON a.id = m.away_team_id
        WHERE (h.name = %s OR %s LIKE CONCAT('%%', h.name, '%%'))
          AND (a.name = %s OR %s LIKE CONCAT('%%', a.name, '%%'))
          {date_clause}
        ORDER BY m.match_date DESC LIMIT 1
    """, [home, home, away, away] + ([kickoff[:10]] if kickoff else []))
    row = cur.fetchone()
    return row[0] if row else None


def main() -> int:
    parser = argparse.ArgumentParser(description="Raccolta pick Pengwin")
    parser.add_argument("--leagues", nargs="+", default=LEAGUE_SLUGS)
    parser.add_argument("--max-articles", type=int, default=50)
    parser.add_argument("--model", default=DEFAULT_MODEL)
    config.add_db_args(parser)
    args = parser.parse_args()

    api_key = load_api_key()
    if not api_key:
        print("Manca ANTHROPIC_API_KEY")
        return 1
    client = anthropic.Anthropic(api_key=api_key)

    conn = pymysql.connect(host=args.host, port=args.port, user=args.user,
                           password=args.password, database="tigertips")
    cur = conn.cursor()
    for stmt in SCHEMA.split(";"):
        if stmt.strip():
            cur.execute(stmt)
    cur.execute("INSERT IGNORE INTO tipsters (name, source_url) VALUES (%s, %s)",
                (TIPSTER_NAME, BASE))
    conn.commit()
    cur.execute("SELECT id FROM tipsters WHERE name = %s", (TIPSTER_NAME,))
    tipster_id = cur.fetchone()[0]

    session = requests.Session()
    urls = get_article_urls(session, args.leagues)
    cur.execute("SELECT article_url FROM tips WHERE tipster_id = %s", (tipster_id,))
    seen = {r[0] for r in cur.fetchall()}
    new_urls = [u for u in urls if u not in seen][:args.max_articles]
    print(f"Articoli trovati: {len(urls)} | nuovi da processare: {len(new_urls)}")

    n_saved = 0
    for url in new_urls:
        try:
            resp = session.get(url, headers=HEADERS, timeout=30)
            resp.raise_for_status()
        except requests.RequestException as exc:
            print(f"  [errore] {url}: {exc}")
            continue
        soup = BeautifulSoup(resp.text, "lxml")
        text = soup.get_text(" ", strip=True)
        data = extract_tip(client, args.model, text)
        if not data or not data.get("tip"):
            print(f"  [senza pick] {url.rsplit('/', 1)[-1]}")
            continue
        tip = data["tip"]
        match_id = find_match_id(cur, data.get("home"), data.get("away"),
                                 data.get("kickoff"))
        cur.execute("""
            INSERT IGNORE INTO tips
                (tipster_id, match_id, article_url, published_date, scraped_at,
                 home_team, away_team, kickoff, market, selection, line, odds, raw_text)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """, (tipster_id, match_id, url, data.get("published_date"),
              datetime.now(), data.get("home"), data.get("away"),
              data.get("kickoff"), tip.get("market"), tip.get("selection"),
              tip.get("line"), tip.get("odds"), (tip.get("raw") or "")[:300]))
        conn.commit()
        n_saved += 1
        linked = f"match_id={match_id}" if match_id else "non agganciata"
        print(f"  [ok] {data.get('home')}-{data.get('away')}: "
              f"{tip.get('market')}/{tip.get('selection')} @ {tip.get('odds')} ({linked})")

    print(f"\nPick salvate: {n_saved}")
    conn.close()
    return 0


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