#!/usr/bin/env python3
"""
Scoring Engine V2 - ZE 2 sur 4 Optimise
Cote: 10%, Forme: 30%, Bonus outsider
"""

import requests
import sqlite3
import json
import re
from datetime import datetime

DB_PATH = "/home/h3r7/turf_saas/turf_saas.db"
HEADERS = {"User-Agent": "Mozilla/5.0", "Accept": "application/json"}


def get_cote_from_db(horse_name, date_course):
    """Recupere la cote depuis la table predictions (plus recente et non nulle)"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    c = conn.execute(
        """
        SELECT odds FROM predictions 
        WHERE date=? AND horse_name LIKE ? AND odds > 0 
        ORDER BY created_at DESC LIMIT 1
    """,
        (date_course, f"%{horse_name}%"),
    )
    r = c.fetchone()
    conn.close()
    return r["odds"] if r else 0


def parse_musique(musique):
    if not musique:
        return {}
    clean = re.sub(r"\(\d+\)", "", musique)
    resultats = re.findall(r"(\d+|D|0)([amphsc]?)", clean)
    positions = []
    for pos, disc in resultats[:10]:
        positions.append(99 if pos == "D" else int(pos))
    if not positions:
        return {}
    nb_courses = len(positions)
    nb_victoires = positions.count(1)
    nb_places = sum(1 for p in positions if 1 <= p <= 3)
    recentes = [p for p in positions[:3] if p != 99]
    forme_recente = sum(recentes) / len(recentes) if recentes else 99
    tendance = (
        (sum(positions[-4:]) / 4 - sum(positions[:4]) / 4) if len(positions) >= 4 else 0
    )
    return {
        "forme_recente": round(forme_recente, 1),
        "tendance": round(tendance, 1),
        "tx_victoire": round(nb_victoires / nb_courses * 100, 1) if nb_courses else 0,
        "tx_place": round(nb_places / nb_courses * 100, 1) if nb_courses else 0,
    }


def get_terrain_condition(penetrometre_intitule: str | None) -> str:
    """Normalise le pénétromètre PMU en condition terrain standardisée."""
    if not penetrometre_intitule:
        return "inconnu"
    val = penetrometre_intitule.upper()
    if any(k in val for k in ("TRES BON", "TRÈS BON", "FERME", "FIRM")):
        return "bon"
    if any(k in val for k in ("BON", "GOOD", "STANDARD")):
        return "bon"
    if any(k in val for k in ("SOUPLE", "YIELDING", "COLLANT")):
        return "souple"
    if any(k in val for k in ("LOURD", "HEAVY", "TRES SOUPLE", "TRÈS SOUPLE")):
        return "lourd"
    if any(k in val for k in ("SOFT", "MOU")):
        return "souple"
    return "inconnu"


def compute_weather_impact(weather_data: dict | None, terrain_condition: str) -> float:
    """
    Calcule un score d'impact météo/terrain sur [−5, +5].
    weather_data keys attendues : nebulositecode, temperature, force_vent
    terrain_condition : 'bon' | 'souple' | 'lourd' | 'inconnu'
    Retourne un delta de score ML (positif = favorable, négatif = défavorable).
    """
    if not weather_data:
        return 0.0

    delta = 0.0

    # Terrain
    if terrain_condition == "lourd":
        delta -= 3.0
    elif terrain_condition == "souple":
        delta -= 1.5
    elif terrain_condition == "bon":
        delta += 1.0
    # inconnu → 0

    # Vent
    force_vent = weather_data.get("force_vent") or 0
    try:
        force_vent = float(force_vent)
    except (TypeError, ValueError):
        force_vent = 0.0
    if force_vent >= 50:
        delta -= 2.0
    elif force_vent >= 30:
        delta -= 1.0

    # Températures extrêmes
    temperature = weather_data.get("temperature")
    try:
        temperature = float(temperature) if temperature is not None else None
    except (TypeError, ValueError):
        temperature = None
    if temperature is not None:
        if temperature <= 0:
            delta -= 1.0
        elif temperature >= 35:
            delta -= 1.0

    return round(max(-5.0, min(5.0, delta)), 2)


def score_cheval_v2(p, all_participants, today, weather_data=None):
    """
    Score un cheval pour le modèle V2.
    weather_data (optionnel) : dict issu de pmu_meteo pour cette réunion.
    Backward-compatible : weather_data=None → comportement identique à avant HRT-83.
    """
    score = 0
    details = {}

    # 1. COTE - Essaye PMU API, sinon DB
    horse_name = p.get("nom", "")
    cote = 0

    # Essayer d'abord depuis l'API PMU
    rapport = p.get("dernierRapportDirect", {})
    if rapport:
        cote = rapport.get("rapport", 0)
    if not cote:
        rapport_ref = p.get("dernierRapportReference", {})
        cote = rapport_ref.get("rapport", 0) if rapport_ref else 0

    # Fallback: aller chercher dans la DB
    if not cote or cote == 0:
        cote = get_cote_from_db(horse_name, today)

    # Si toujours pas de cote, utiliser 99 comme valeur par defaut
    if not cote or cote == 0:
        cote = 99.0

    score_cote = max(2, min(10, 20 / (1 + cote * 0.15))) if cote > 0 else 2
    score += score_cote
    details["cote"] = round(cote, 1)
    details["score_cote"] = round(score_cote, 1)

    # 2. FORME - AUGMENTE a 30 pts
    musique_stats = parse_musique(p.get("musique", ""))
    forme = musique_stats.get("forme_recente", 99)
    score_forme = (
        30
        if forme <= 1
        else 25
        if forme <= 2
        else 20
        if forme <= 3
        else 15
        if forme <= 5
        else 8
        if forme <= 8
        else 0
    )
    score += score_forme
    details["forme_recente"] = forme
    details["score_forme"] = score_forme

    # 3. TAUX VICTOIRE (15 pts)
    nb_courses_total = p.get("nombreCourses", 0)
    nb_victoires_total = p.get("nombreVictoires", 0)
    tx_vic = (nb_victoires_total / nb_courses_total * 100) if nb_courses_total else 0
    score_vic = min(15, tx_vic * 0.5)
    score += score_vic
    details["tx_victoire"] = round(tx_vic, 1)
    details["score_victoire"] = round(score_vic, 1)

    # 4. TAUX PLACE (15 pts)
    nb_places_total = p.get("nombrePlaces", 0)
    tx_place = (nb_places_total / nb_courses_total * 100) if nb_courses_total else 0
    score_place = min(15, tx_place * 0.2)
    score += score_place
    details["tx_place"] = round(tx_place, 1)
    details["score_place"] = round(score_place, 1)

    # 5. REDUCTION KM (10 pts)
    rk = p.get("reductionKilometrique", 0)
    all_rk = [
        x.get("reductionKilometrique", 0)
        for x in all_participants
        if x.get("reductionKilometrique", 0) > 0
    ]
    if rk > 0 and all_rk:
        score_rk = (
            10 * (1 - (rk - min(all_rk)) / (max(all_rk) - min(all_rk)))
            if max(all_rk) > min(all_rk)
            else 5
        )
    else:
        score_rk = 0
    score += score_rk
    details["rk"] = rk
    details["score_rk"] = round(score_rk, 1)

    # 6. TENDANCE (10 pts)
    tendance = musique_stats.get("tendance", 0)
    score_tendance = min(10, max(0, 5 + tendance))
    score += score_tendance
    details["tendance"] = tendance
    details["score_tendance"] = round(score_tendance, 1)

    # 7. AVIS ENTRAINEUR (5 pts)
    avis = p.get("avisEntraineur", "NEUTRE")
    score_avis = {
        "POSITIF": 5,
        "TRES_POSITIF": 5,
        "NEUTRE": 2,
        "NEGATIF": 0,
        "TRES_NEGATIF": 0,
    }.get(avis, 2)
    score += score_avis
    details["avis_entraineur"] = avis
    details["score_avis"] = score_avis

    # 8. BONUS OUTSIDER (5 pts)
    bonus_outsider = 5 if forme <= 3 and cote >= 10 else 0
    score += bonus_outsider
    details["bonus_outsider"] = bonus_outsider

    # Driver change penalty
    if p.get("driverChange", False):
        score -= 3
        details["driver_change"] = True

    # 9. METEO & TERRAIN (HRT-83) — premium feature, weather_data=None → skip
    penetrometre = p.get("penetrometre_intitule", "") or ""
    terrain_condition = (
        get_terrain_condition(penetrometre) if penetrometre else "inconnu"
    )
    weather_impact = 0.0
    if weather_data is not None:
        weather_impact = compute_weather_impact(weather_data, terrain_condition)
        score += weather_impact
    details["terrain_condition"] = terrain_condition
    details["weather_impact"] = weather_impact

    details["score_total"] = round(score, 1)
    details["musique"] = p.get("musique", "")
    details["nb_victoires"] = nb_victoires_total
    details["nb_places"] = nb_places_total
    details["nb_courses"] = nb_courses_total

    return round(score, 1), details


def get_ze2sur4_combinaisons(top4):
    combinaisons = []
    for i in range(4):
        for j in range(i + 1, 4):
            c1 = top4[i]
            c2 = top4[j]
            combinaisons.append(
                {
                    "cheval1": c1["nom"],
                    "numero1": c1["numero"],
                    "cheval2": c2["nom"],
                    "numero2": c2["numero"],
                    "mise": 1.0,
                }
            )
    return combinaisons


def build_recommendations_v2(scored_horses):
    ranked = sorted(scored_horses, key=lambda x: x["score"], reverse=True)
    if len(ranked) < 4:
        return None

    top1, top2, top3, top4 = ranked[0], ranked[1], ranked[2], ranked[3]
    top4_list = ranked[:4]

    def confiance(s):
        return (
            "FORTE"
            if s >= 55
            else "BONNE"
            if s >= 45
            else "MOYENNE"
            if s >= 35
            else "FAIBLE"
        )

    ze2_combinaisons = get_ze2sur4_combinaisons(top4_list)
    mise_ze2 = len(ze2_combinaisons) * 1.0

    return {
        "simple_gagnant": {
            "cheval": top1["nom"],
            "numero": top1["numero"],
            "cote": top1["details"]["cote"],
            "score": top1["score"],
            "confiance": confiance(top1["score"]),
            "mise_suggeree": 2.0,
            "gain_potentiel": round(2.0 * top1["details"]["cote"], 2),
        },
        "ze2_sur_4": {
            "top4": [{"nom": h["nom"], "numero": h["numero"]} for h in top4_list],
            "combinaisons": ze2_combinaisons,
            "mise_totale": mise_ze2,
            "nb_combinaisons": len(ze2_combinaisons),
            "confiance": confiance(
                (top1["score"] + top2["score"] + top3["score"] + top4["score"]) / 4
            ),
            "explication": "Jouer les 6 combinaisons de 2 chevaux parmi les 4 premiers",
        },
        "outsider": _find_outsider(ranked),
        "budget_total": 2.0 + mise_ze2,
    }


def _find_outsider(ranked):
    for h in ranked[3:7]:
        d = h["details"]
        if d["cote"] >= 12 and d["forme_recente"] <= 4 and d["bonus_outsider"] == 5:
            return {
                "cheval": h["nom"],
                "numero": h["numero"],
                "cote": d["cote"],
                "mise_suggeree": 1.0,
                "gain_potentiel": round(1.0 * d["cote"], 2),
            }
    return None


def save_to_db(scored_horses, date_course, hippodrome, libelle):
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    cursor.execute("DELETE FROM scoring WHERE date = ?", (date_course,))

    for i, h in enumerate(scored_horses, 1):
        d = h["details"]
        cursor.execute(
            """
            INSERT INTO scoring (date, race_name, horse_number, horse_name, score,
                score_cote, score_forme, score_victoire, score_place, score_rk,
                score_tendance, score_avis, cote, forme_recente, tx_victoire, tx_place,
                avis_entraineur, musique, rang_scoring, scoring_version)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'v2')
        """,
            (
                date_course,
                libelle,
                h["numero"],
                h["nom"],
                h["score"],
                d.get("score_cote", 0),
                d.get("score_forme", 0),
                d.get("score_victoire", 0),
                d.get("score_place", 0),
                d.get("score_rk", 0),
                d.get("score_tendance", 0),
                d.get("score_avis", 0),
                d.get("cote", 0),
                d.get("forme_recente", 0),
                d.get("tx_victoire", 0),
                d.get("tx_place", 0),
                d.get("avis_entraineur", ""),
                d.get("musique", ""),
                i,
            ),
        )

    conn.commit()
    conn.close()
    print(f"💾 {len(scored_horses)} scores enregistres en BDD pour {date_course}")


def main():
    today = datetime.now().strftime("%Y-%m-%d")
    date_pmu = datetime.now().strftime("%d%m%Y")
    print(
        f"=== SCORING V2 - ZE2 SUR4 OPTIMISE === {datetime.now().strftime('%d/%m/%Y %H:%M')} ==="
    )

    try:
        url = f"https://turfinfo.api.pmu.fr/rest/client/1/programme/{date_pmu}/reunions"
        r = requests.get(url, headers=HEADERS, timeout=15)
        reunions = r.json().get("programme", {}).get("reunions", [])
    except Exception as e:
        print(f"Erreur: {e}")
        return

    quinte = None
    for reunion in reunions:
        for course in reunion.get("courses", []):
            paris_types = [p["typePari"] for p in course.get("paris", [])]
            if any("QUINTE" in p for p in paris_types) or "PARIS-TURF" in course.get(
                "libelle", ""
            ):
                quinte = (
                    reunion["numOfficiel"],
                    course["numOrdre"],
                    course.get("libelle", ""),
                    reunion["hippodrome"]["libelleCourt"],
                    course.get("heureDepart", 0),
                )
                break
        if quinte:
            break

    if not quinte:
        # Fallback: utiliser la premiere reunion francaise avec predictions
        conn = sqlite3.connect(DB_PATH)
        conn.row_factory = sqlite3.Row
        r = conn.execute(
            """
            SELECT r.num_reunion, r.hippodrome_court, c.num_course, c.libelle
            FROM pmu_courses c
            JOIN pmu_reunions r ON r.date_programme=c.date_programme AND r.num_reunion=c.num_reunion
            WHERE c.date_programme=? AND r.pays_code='FRA' AND c.num_course=1
            AND EXISTS (SELECT 1 FROM predictions p WHERE p.date=? AND p.source='canalturf_partants' 
                       AND p.race_name LIKE '%' || c.libelle || '%')
            ORDER BY c.heure_depart_str ASC LIMIT 1
        """,
            (today, today),
        ).fetchone()
        conn.close()
        if r:
            quinte = (
                r["num_reunion"],
                r["num_course"],
                r["libelle"],
                r["hippodrome_court"],
                0,
            )
        else:
            print("Aucune course trouvee")
            return

    num_r, num_c, libelle, hippodrome, heure_ts = quinte
    heure = (
        datetime.fromtimestamp(heure_ts / 1000).strftime("%H:%M")
        if heure_ts
        else "13:55"
    )
    print(f"Course: {libelle} - {hippodrome} {heure}")

    try:
        url = f"https://turfinfo.api.pmu.fr/rest/client/1/programme/{date_pmu}/R{num_r}/C{num_c}/participants"
        r = requests.get(url, headers=HEADERS, timeout=15)
        participants = [
            p for p in r.json().get("participants", []) if p.get("statut") == "PARTANT"
        ]
    except Exception as e:
        print(f"Erreur: {e}")
        return

    scored_horses = []
    for p in participants:
        score, details = score_cheval_v2(p, participants, today)
        scored_horses.append(
            {"nom": p["nom"], "numero": p["numPmu"], "score": score, "details": details}
        )

    ranked = sorted(scored_horses, key=lambda x: x["score"], reverse=True)
    print(f"\n=== TOP 4 ===")
    for i, h in enumerate(ranked[:4], 1):
        d = h["details"]
        print(
            f"{i}. #{h['numero']:>2} {h['nom']:<20} Score:{h['score']:.1f} Cote:{d['cote']:.1f}"
        )

    save_to_db(ranked, today, hippodrome, libelle)

    reco = build_recommendations_v2(scored_horses)
    if reco:
        print(f"\n=== RECOMMANDATIONS ===")
        sg = reco["simple_gagnant"]
        print(f"\n🎯 SIMPLE GAGNANT:")
        print(
            f"   #{sg['numero']} {sg['cheval']} @ {sg['cote']}/1 (mise {sg['mise_suggeree']}EUR)"
        )

        ze2 = reco["ze2_sur_4"]
        print(f"\n🎰 ZE 2 SUR 4 (TOP 4: {', '.join([h['nom'] for h in ze2['top4']])}")
        print(
            f"   Mise totale: {ze2['mise_totale']}EUR ({ze2['nb_combinaisons']} combis x 1EUR)"
        )
        print(f"   Confiance: {ze2['confiance']}")
        print(f"   Combinaisons:")
        for c in ze2["combinaisons"]:
            print(
                f"      {c['numero1']}-{c['cheval1']} + {c['numero2']}-{c['cheval2']}"
            )

        print(f"\n💰 BUDGET TOTAL: {reco['budget_total']}EUR")
        print(f"   - Simple Gagnant: 2EUR")
        print(f"   - ZE 2 sur 4: {ze2['mise_totale']}EUR")


if __name__ == "__main__":
    main()
