#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
pmu_results.py — Récupération complète des données PMU via API client/7
=========================================================================
Sources : https://online.turfinfo.api.pmu.fr/rest/client/7

Endpoints utilisés :
  /programme/DDMMYYYY?meteo=true          → programme + météo + ordreArrivee
  /programme/.../R{n}/C{n}/participants   → partants + cotes + stats + résultats
  /programme/.../R{n}/C{n}/rapports-definitifs → gains

Schéma DB (nouvelles tables, compatibles avec turf.db existant) :
  pmu_reunions      — une ligne par réunion du jour
  pmu_courses       — une ligne par course
  pmu_partants      — un cheval par course (stats + cotes + résultat)
  pmu_rapports      — gains par type de pari
  pmu_meteo         — météo par réunion

Usage :
  python3 pmu_results.py                  # aujourd'hui
  python3 pmu_results.py --date 23032026  # date en DDMMYYYY
  python3 pmu_results.py --show           # afficher résultats BDD sans scraper
  python3 pmu_results.py --bilan          # bilan prédictions vs arrivées
  python3 pmu_results.py --yesterday      # raccourci hier
"""

import sqlite3
import requests
import json
import base64
import time
import argparse
import re
from datetime import datetime, timedelta
from pathlib import Path

# ─────────────────────────────────────────────────────────
# CONFIG
# ─────────────────────────────────────────────────────────
DB_PATH    = "/home/h3r7/turf_saas/turf_saas.db"
OUTPUT_DIR = Path("/home/h3r7/turf_scraper")
API_BASE   = "https://online.turfinfo.api.pmu.fr/rest/client/7"

HEADERS = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36",
    "Accept": "application/json",
    "Accept-Language": "fr-FR,fr;q=0.9",
    "Referer": "https://www.pmu.fr/",
}

SESSION = requests.Session()
SESSION.headers.update(HEADERS)


# ─────────────────────────────────────────────────────────
# SCHÉMA BASE DE DONNÉES
# ─────────────────────────────────────────────────────────
SCHEMA = """
CREATE TABLE IF NOT EXISTS pmu_reunions (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    date_programme      TEXT    NOT NULL,
    num_reunion         INTEGER NOT NULL,
    num_externe         INTEGER,
    nature              TEXT,
    statut              TEXT,
    audience            TEXT,
    hippodrome_code     TEXT,
    hippodrome_court    TEXT,
    hippodrome_long     TEXT,
    pays_code           TEXT,
    pays_libelle        TEXT,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(date_programme, num_reunion)
);

CREATE TABLE IF NOT EXISTS pmu_meteo (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    date_programme      TEXT    NOT NULL,
    num_reunion         INTEGER NOT NULL,
    nebulositecode      TEXT,
    nebulosite_court    TEXT,
    nebulosite_long     TEXT,
    temperature         INTEGER,
    force_vent          INTEGER,
    direction_vent      TEXT,
    date_prevision      INTEGER,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(date_programme, num_reunion)
);

CREATE TABLE IF NOT EXISTS pmu_courses (
    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
    date_programme          TEXT    NOT NULL,
    num_reunion             INTEGER NOT NULL,
    num_course              INTEGER NOT NULL,
    num_externe             INTEGER,
    libelle                 TEXT,
    libelle_court           TEXT,
    heure_depart            INTEGER,
    heure_depart_str        TEXT,
    distance                INTEGER,
    distance_unit           TEXT,
    parcours                TEXT,
    discipline              TEXT,
    specialite              TEXT,
    type_piste              TEXT,
    corde                   TEXT,
    condition_age           TEXT,
    condition_sexe          TEXT,
    categorie_particularite TEXT,
    nb_declares_partants    INTEGER,
    montant_prix            INTEGER,
    montant_1er             INTEGER,
    montant_2eme            INTEGER,
    montant_3eme            INTEGER,
    montant_4eme            INTEGER,
    montant_5eme            INTEGER,
    penetrometre_intitule   TEXT,
    penetrometre_valeur     TEXT,
    statut                  TEXT,
    categorie_statut        TEXT,
    arrivee_definitive      INTEGER DEFAULT 0,
    rapports_disponibles    INTEGER DEFAULT 0,
    duree_course_ms         INTEGER,
    conditions              TEXT,
    ordre_arrivee_json      TEXT,
    incidents_json          TEXT,
    created_at              TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(date_programme, num_reunion, num_course)
);

CREATE TABLE IF NOT EXISTS pmu_partants (
    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
    date_programme          TEXT    NOT NULL,
    num_reunion             INTEGER NOT NULL,
    num_course              INTEGER NOT NULL,
    num_pmu                 INTEGER NOT NULL,
    id_cheval               TEXT,
    nom                     TEXT,
    age                     INTEGER,
    sexe                    TEXT,
    race                    TEXT,
    robe                    TEXT,
    pays                    TEXT,
    place_corde             INTEGER,
    nom_pere                TEXT,
    nom_mere                TEXT,
    nom_pere_mere           TEXT,
    driver                  TEXT,
    driver_change           INTEGER DEFAULT 0,
    entraineur              TEXT,
    proprietaire            TEXT,
    eleveur                 TEXT,
    pays_entrainement       TEXT,
    oeilleres               TEXT,
    supplement              INTEGER DEFAULT 0,
    handicap_valeur         REAL,
    handicap_poids          INTEGER,
    musique                 TEXT,
    nombre_courses          INTEGER DEFAULT 0,
    nombre_victoires        INTEGER DEFAULT 0,
    nombre_places           INTEGER DEFAULT 0,
    nombre_places_2eme      INTEGER DEFAULT 0,
    nombre_places_3eme      INTEGER DEFAULT 0,
    gains_carriere          INTEGER DEFAULT 0,
    gains_victoires         INTEGER DEFAULT 0,
    gains_place             INTEGER DEFAULT 0,
    gains_annee_en_cours    INTEGER DEFAULT 0,
    gains_annee_precedente  INTEGER DEFAULT 0,
    cote_direct             REAL,
    cote_reference          REAL,
    tendance_cote           TEXT,
    favoris                 INTEGER DEFAULT 0,
    ordre_arrivee           INTEGER DEFAULT 0,
    statut_partant          TEXT,
    distance_cheval_prec    TEXT,
    distance_cheval_prec_code INTEGER,
    commentaire_apres_course TEXT,
    indicateur_inedit       INTEGER DEFAULT 0,
    tx_victoire             REAL,
    tx_place                REAL,
    forme_recente           REAL,
    tendance_forme          REAL,
    created_at              TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(date_programme, num_reunion, num_course, num_pmu)
);

CREATE TABLE IF NOT EXISTS pmu_rapports (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    date_programme  TEXT    NOT NULL,
    num_reunion     INTEGER NOT NULL,
    num_course      INTEGER NOT NULL,
    type_pari       TEXT    NOT NULL,
    famille_pari    TEXT,
    mise_base       INTEGER,
    combinaison     TEXT    NOT NULL,
    dividende       INTEGER,
    dividende_euro  REAL,
    nb_gagnants     REAL,
    libelle         TEXT,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(date_programme, num_reunion, num_course, type_pari, combinaison)
);
"""

# Vues recréées séparément (DROP + CREATE)
VUES = [
    ("v_resultats_complets", """
CREATE VIEW v_resultats_complets AS
SELECT
    p.date_programme,
    p.num_reunion,
    p.num_course,
    c.libelle           AS course,
    c.heure_depart_str  AS heure,
    r.hippodrome_long   AS hippodrome,
    c.discipline,
    c.distance,
    c.type_piste,
    c.penetrometre_intitule AS penetrometre,
    m.temperature,
    m.nebulosite_court  AS meteo,
    m.force_vent,
    m.direction_vent,
    p.num_pmu,
    p.nom               AS cheval,
    p.age,
    p.sexe,
    p.race,
    p.driver,
    p.entraineur,
    p.oeilleres,
    p.handicap_valeur,
    p.musique,
    p.nombre_courses,
    p.nombre_victoires,
    p.nombre_places,
    p.gains_carriere,
    p.gains_annee_en_cours,
    p.cote_direct,
    p.cote_reference,
    p.tendance_cote,
    p.tx_victoire,
    p.tx_place,
    p.forme_recente,
    p.tendance_forme,
    p.ordre_arrivee     AS position_finale,
    p.distance_cheval_prec,
    p.commentaire_apres_course,
    p.nom_pere,
    p.nom_mere,
    p.pays_entrainement
FROM pmu_partants p
JOIN pmu_courses  c ON c.date_programme=p.date_programme
                   AND c.num_reunion=p.num_reunion
                   AND c.num_course=p.num_course
JOIN pmu_reunions r ON r.date_programme=p.date_programme
                   AND r.num_reunion=p.num_reunion
LEFT JOIN pmu_meteo m ON m.date_programme=p.date_programme
                     AND m.num_reunion=p.num_reunion
"""),
    ("v_bilan_predictions", """
CREATE VIEW v_bilan_predictions AS
SELECT
    pr.date,
    pr.race_name,
    pr.horse_name,
    pr.horse_number,
    pr.prediction_rank,
    pr.odds            AS cote_pred,
    pr.source,
    pa.ordre_arrivee   AS position_reelle,
    pa.cote_direct     AS cote_finale,
    pa.commentaire_apres_course,
    CASE
        WHEN pa.ordre_arrivee = 1            THEN 'GAGNANT'
        WHEN pa.ordre_arrivee <= 3           THEN 'PLACE'
        WHEN pa.ordre_arrivee <= 5           THEN 'TOP5'
        WHEN pa.ordre_arrivee  > 5           THEN 'HORS'
        ELSE 'INCONNU'
    END AS resultat
FROM predictions pr
LEFT JOIN pmu_partants pa
       ON pa.date_programme = pr.date
      AND pa.nom            = pr.horse_name
      AND pa.num_pmu        = pr.horse_number
"""),
]


# ─────────────────────────────────────────────────────────
# INITIALISATION DB
# ─────────────────────────────────────────────────────────
def init_db():
    conn = sqlite3.connect(DB_PATH)
    conn.executescript(SCHEMA)
    for vue_name, vue_sql in VUES:
        conn.execute(f"DROP VIEW IF EXISTS {vue_name}")
        conn.execute(vue_sql)
    conn.commit()
    conn.close()
    print("✅ Schéma DB initialisé (5 tables + 2 vues)")


# ─────────────────────────────────────────────────────────
# APPELS API
# ─────────────────────────────────────────────────────────
def api_get(path, params=None, retries=3):
    url = API_BASE + path
    for attempt in range(retries):
        try:
            r = SESSION.get(url, params=params, timeout=15)
            if r.status_code == 200:
                return r.json()
            elif r.status_code == 404:
                return None
            else:
                print(f"   ⚠️  HTTP {r.status_code}")
                time.sleep(1)
        except Exception as e:
            if attempt == retries - 1:
                print(f"   ❌ Erreur réseau : {e}")
    return None


def ts_to_hhmm(ts_ms):
    if not ts_ms:
        return ""
    try:
        return datetime.fromtimestamp(ts_ms / 1000).strftime("%H:%M")
    except Exception:
        return ""


# ─────────────────────────────────────────────────────────
# CALCUL STATS MUSIQUE
# ─────────────────────────────────────────────────────────
def parse_musique(musique):
    if not musique:
        return None, None, None, None
    clean     = re.sub(r'\(\d+\)', '', musique)
    resultats = re.findall(r'(\d+|D|0)([amphsc]?)', clean)
    positions = []
    for pos, _ in resultats[:10]:
        positions.append(99 if pos == 'D' else int(pos))
    if not positions:
        return None, None, None, None
    nb   = len(positions)
    vict = positions.count(1)
    plac = sum(1 for p in positions if 1 <= p <= 3)
    rec  = [p for p in positions[:3] if p != 99]
    forme_recente = round(sum(rec) / len(rec), 1) if rec else None
    if len(positions) >= 4:
        tendance = round(sum(positions[-4:]) / 4 - sum(positions[:4]) / 4, 1)
    else:
        tendance = 0.0
    return (
        round(vict / nb * 100, 1) if nb else None,
        round(plac / nb * 100, 1) if nb else None,
        forme_recente,
        tendance,
    )


# ─────────────────────────────────────────────────────────
# SAUVEGARDE RÉUNION
# ─────────────────────────────────────────────────────────
def save_reunion(conn, date_str, reunion):
    hipp = reunion.get("hippodrome", {})
    pays = reunion.get("pays", {})
    conn.execute("""
        INSERT OR REPLACE INTO pmu_reunions
        (date_programme, num_reunion, num_externe, nature, statut, audience,
         hippodrome_code, hippodrome_court, hippodrome_long, pays_code, pays_libelle)
        VALUES (?,?,?,?,?,?,?,?,?,?,?)
    """, (
        date_str,
        reunion.get("numOfficiel"),
        reunion.get("numExterne"),
        reunion.get("nature"),
        reunion.get("statut"),
        reunion.get("audience"),
        hipp.get("code"),
        hipp.get("libelleCourt"),
        hipp.get("libelleLong"),
        pays.get("code"),
        pays.get("libelle"),
    ))


# ─────────────────────────────────────────────────────────
# SAUVEGARDE MÉTÉO
# ─────────────────────────────────────────────────────────
def save_meteo(conn, date_str, num_reunion, meteo):
    if not meteo:
        return
    conn.execute("""
        INSERT OR REPLACE INTO pmu_meteo
        (date_programme, num_reunion, nebulositecode, nebulosite_court,
         nebulosite_long, temperature, force_vent, direction_vent, date_prevision)
        VALUES (?,?,?,?,?,?,?,?,?)
    """, (
        date_str, num_reunion,
        meteo.get("nebulositeCode"),
        meteo.get("nebulositeLibelleCourt"),
        meteo.get("nebulositeLibelleLong"),
        meteo.get("temperature"),
        meteo.get("forceVent"),
        meteo.get("directionVent"),
        meteo.get("datePrevision"),
    ))


# ─────────────────────────────────────────────────────────
# SAUVEGARDE COURSE
# ─────────────────────────────────────────────────────────
def save_course(conn, date_str, num_reunion, course):
    heure_ts  = course.get("heureDepart")
    penet     = course.get("penetrometre") or {}
    incidents = course.get("incidents", [])
    ordre_arr = course.get("ordreArrivee", [])

    conn.execute("""
        INSERT OR REPLACE INTO pmu_courses
        (date_programme, num_reunion, num_course, num_externe,
         libelle, libelle_court, heure_depart, heure_depart_str,
         distance, distance_unit, parcours, discipline, specialite,
         type_piste, corde, condition_age, condition_sexe,
         categorie_particularite, nb_declares_partants,
         montant_prix, montant_1er, montant_2eme, montant_3eme, montant_4eme, montant_5eme,
         penetrometre_intitule, penetrometre_valeur,
         statut, categorie_statut, arrivee_definitive, rapports_disponibles,
         duree_course_ms, conditions, ordre_arrivee_json, incidents_json)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    """, (
        date_str, num_reunion,
        course.get("numOrdre"),
        course.get("numExterne"),
        course.get("libelle"),
        course.get("libelleCourt"),
        heure_ts,
        ts_to_hhmm(heure_ts),
        course.get("distance"),
        course.get("distanceUnit"),
        course.get("parcours"),
        course.get("discipline"),
        course.get("specialite"),
        course.get("typePiste"),
        course.get("corde"),
        course.get("conditionAge"),
        course.get("conditionSexe"),
        course.get("categorieParticularite"),
        course.get("nombreDeclaresPartants"),
        course.get("montantPrix"),
        course.get("montantOffert1er"),
        course.get("montantOffert2eme"),
        course.get("montantOffert3eme"),
        course.get("montantOffert4eme"),
        course.get("montantOffert5eme"),
        penet.get("intitule"),
        penet.get("commentaire"),
        course.get("statut"),
        course.get("categorieStatut"),
        int(bool(course.get("arriveeDefinitive"))),
        int(bool(course.get("rapportsDefinitifsDisponibles"))),
        course.get("dureeCourse"),
        course.get("conditions"),
        json.dumps(ordre_arr) if ordre_arr else None,
        json.dumps(incidents) if incidents else None,
    ))


# ─────────────────────────────────────────────────────────
# SAUVEGARDE PARTANTS
# ─────────────────────────────────────────────────────────
def save_partants(conn, date_str, num_reunion, num_course, participants):
    saved = 0
    for p in participants:
        robe      = p.get("robe") or {}
        gains     = p.get("gainsParticipant") or {}
        rdr       = p.get("dernierRapportDirect") or {}
        rref      = p.get("dernierRapportReference") or {}
        dist_prec = p.get("distanceChevalPrecedent") or {}
        comm      = p.get("commentaireApresCourse") or {}
        musique   = p.get("musique", "")
        tx_vic, tx_plac, forme_rec, tendance_f = parse_musique(musique)

        conn.execute("""
            INSERT OR REPLACE INTO pmu_partants
            (date_programme, num_reunion, num_course,
             num_pmu, id_cheval, nom, age, sexe, race, robe, pays, place_corde,
             nom_pere, nom_mere, nom_pere_mere,
             driver, driver_change, entraineur, proprietaire, eleveur, pays_entrainement,
             oeilleres, supplement, handicap_valeur, handicap_poids,
             musique, nombre_courses, nombre_victoires, nombre_places,
             nombre_places_2eme, nombre_places_3eme,
             gains_carriere, gains_victoires, gains_place,
             gains_annee_en_cours, gains_annee_precedente,
             cote_direct, cote_reference, tendance_cote, favoris,
             ordre_arrivee, statut_partant,
             distance_cheval_prec, distance_cheval_prec_code,
             commentaire_apres_course, indicateur_inedit,
             tx_victoire, tx_place, forme_recente, tendance_forme)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                    ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        """, (
            date_str, num_reunion, num_course,
            p.get("numPmu"),
            p.get("idCheval"),
            p.get("nom"),
            p.get("age"),
            p.get("sexe"),
            p.get("race"),
            robe.get("libelleLong"),
            p.get("pays"),
            p.get("placeCorde"),
            p.get("nomPere"),
            p.get("nomMere"),
            p.get("nomPereMere"),
            p.get("driver"),
            int(bool(p.get("driverChange"))),
            p.get("entraineur"),
            p.get("proprietaire"),
            p.get("eleveur"),
            p.get("paysEntrainement"),
            p.get("oeilleres"),
            p.get("supplement", 0),
            p.get("handicapValeur"),
            p.get("handicapPoids"),
            musique,
            p.get("nombreCourses", 0),
            p.get("nombreVictoires", 0),
            p.get("nombrePlaces", 0),
            p.get("nombrePlacesSecond", 0),
            p.get("nombrePlacesTroisieme", 0),
            gains.get("gainsCarriere", 0),
            gains.get("gainsVictoires", 0),
            gains.get("gainsPlace", 0),
            gains.get("gainsAnneeEnCours", 0),
            gains.get("gainsAnneePrecedente", 0),
            rdr.get("rapport"),
            rref.get("rapport"),
            rref.get("indicateurTendance"),
            int(bool(rdr.get("favoris"))),
            p.get("ordreArrivee", 0),
            p.get("statut"),
            dist_prec.get("libelleLong"),
            dist_prec.get("code"),
            comm.get("texte"),
            int(bool(p.get("indicateurInedit"))),
            tx_vic, tx_plac, forme_rec, tendance_f,
        ))
        saved += 1
    return saved


# ─────────────────────────────────────────────────────────
# SAUVEGARDE RAPPORTS
# ─────────────────────────────────────────────────────────
def save_rapports(conn, date_str, num_reunion, num_course, data):
    saved = 0
    if not data or not isinstance(data, list):
        return 0
    for bloc in data:
        type_pari = bloc.get("typePari", "")
        famille   = bloc.get("famillePari", "")
        mise_base = bloc.get("miseBase", 0)
        for rap in bloc.get("rapports", []):
            combinaison = str(rap.get("combinaison", ""))
            dividende   = rap.get("dividende", 0) or 0
            try:
                conn.execute("""
                    INSERT OR IGNORE INTO pmu_rapports
                    (date_programme, num_reunion, num_course,
                     type_pari, famille_pari, mise_base,
                     combinaison, dividende, dividende_euro, nb_gagnants, libelle)
                    VALUES (?,?,?,?,?,?,?,?,?,?,?)
                """, (
                    date_str, num_reunion, num_course,
                    type_pari, famille, mise_base,
                    combinaison,
                    dividende,
                    round(dividende / 100, 2),
                    rap.get("nombreGagnants"),
                    rap.get("libelle"),
                ))
                saved += 1
            except Exception:
                pass
    return saved


# ─────────────────────────────────────────────────────────
# BILAN PRÉDICTIONS VS ARRIVÉES
# ─────────────────────────────────────────────────────────
def afficher_bilan(date_str):
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    rows = conn.execute(
        "SELECT * FROM v_bilan_predictions WHERE date=? ORDER BY race_name, prediction_rank",
        (date_str,)
    ).fetchall()
    conn.close()

    if not rows:
        print(f"Aucune donnée de bilan pour {date_str} (prédictions ou partants manquants)")
        return

    from collections import defaultdict
    par_course = defaultdict(list)
    for r in rows:
        par_course[r["race_name"]].append(dict(r))

    total_g = total_p = total_t = total = 0
    print(f"\n{'='*65}")
    print(f"  📊 BILAN PRÉDICTIONS vs ARRIVÉES — {date_str}")
    print(f"{'='*65}")

    for course, chevaux in sorted(par_course.items()):
        print(f"\n  🏇 {course}")
        print(f"  {'N°':<4} {'CHEVAL':<22} {'RANG PRÉDIT':<12} {'POSITION':<9} RÉSULTAT")
        print(f"  {'-'*60}")
        for ch in chevaux:
            pos = ch["position_reelle"] or "?"
            res = ch["resultat"] or "-"
            em  = {"GAGNANT": "🥇", "PLACE": "🥈", "TOP5": "✅", "HORS": "❌"}.get(res, "·")
            print(f"  {ch['horse_number']:<4} {ch['horse_name']:<22} #{ch['prediction_rank']:<11} {str(pos):<9} {em} {res}")
            total += 1
            if res == "GAGNANT": total_g += 1
            if res in ("GAGNANT", "PLACE"): total_p += 1
            if res in ("GAGNANT", "PLACE", "TOP5"): total_t += 1

    print(f"\n  TOTAL : {total} prédictions analysées")
    print(f"  🥇 Gagnants : {total_g}  |  🥈 Placés top3 : {total_p}  |  ✅ Top5 : {total_t}")
    print(f"{'='*65}\n")


# ─────────────────────────────────────────────────────────
# AFFICHAGE RÉSULTATS BDD
# ─────────────────────────────────────────────────────────
def afficher_resultats(date_str):
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row

    courses = conn.execute("""
        SELECT c.num_reunion, c.num_course, c.libelle, c.heure_depart_str,
               r.hippodrome_court, c.discipline, c.distance,
               c.penetrometre_intitule, c.nb_declares_partants,
               m.temperature, m.nebulosite_court, m.direction_vent, m.force_vent
        FROM pmu_courses c
        JOIN pmu_reunions r ON r.date_programme=c.date_programme AND r.num_reunion=c.num_reunion
        LEFT JOIN pmu_meteo m ON m.date_programme=c.date_programme AND m.num_reunion=c.num_reunion
        WHERE c.date_programme=? AND c.arrivee_definitive=1
        ORDER BY c.num_reunion, c.num_course
    """, (date_str,)).fetchall()

    if not courses:
        print(f"\nAucune course terminée en BDD pour {date_str}")
        conn.close()
        return

    print(f"\n{'='*65}")
    print(f"  RÉSULTATS PMU — {date_str}  ({len(courses)} course(s))")
    print(f"{'='*65}")

    for co in courses:
        print(f"\n  R{co['num_reunion']}C{co['num_course']} | {co['libelle']}")
        meteo_str = ""
        if co["temperature"]:
            meteo_str = f" | {co['temperature']}°C {co['nebulosite_court']} vent {co['direction_vent']} {co['force_vent']}km/h"
        print(f"  {co['hippodrome_court']} — {co['heure_depart_str']} — {co['discipline']} {co['distance']}m — {co['nb_declares_partants']} partants{meteo_str}")
        if co["penetrometre_intitule"]:
            print(f"  Terrain : {co['penetrometre_intitule']}")

        top5 = conn.execute("""
            SELECT num_pmu, nom, driver, ordre_arrivee,
                   cote_direct, distance_cheval_prec, commentaire_apres_course
            FROM pmu_partants
            WHERE date_programme=? AND num_reunion=? AND num_course=?
              AND ordre_arrivee BETWEEN 1 AND 5
            ORDER BY ordre_arrivee
        """, (date_str, co["num_reunion"], co["num_course"])).fetchall()

        if top5:
            print(f"  {'Pos':<4} {'N°':<4} {'CHEVAL':<22} {'Driver':<16} {'Cote':>6}  Ecart")
            print(f"  {'-'*64}")
            for ch in top5:
                cote  = f"{ch['cote_direct']:.1f}" if ch["cote_direct"] else "-"
                ecart = ch["distance_cheval_prec"] or ""
                print(f"  {ch['ordre_arrivee']:<4} {ch['num_pmu']:<4} {ch['nom']:<22} {ch['driver'] or '':<16} {cote:>6}  {ecart}")
                if ch["commentaire_apres_course"]:
                    print(f"       → {ch['commentaire_apres_course'][:80]}")

        # Rapports principaux
        raps = conn.execute("""
            SELECT type_pari, combinaison, dividende_euro, nb_gagnants
            FROM pmu_rapports
            WHERE date_programme=? AND num_reunion=? AND num_course=?
              AND type_pari IN ('SIMPLE_GAGNANT','SIMPLE_PLACE','COUPLE_GAGNANT',
                                'TIERCE','QUARTE_PLUS','QUINTE_PLUS')
            ORDER BY CASE type_pari
                WHEN 'SIMPLE_GAGNANT'  THEN 1
                WHEN 'SIMPLE_PLACE'    THEN 2
                WHEN 'COUPLE_GAGNANT'  THEN 3
                WHEN 'TIERCE'          THEN 4
                WHEN 'QUARTE_PLUS'     THEN 5
                WHEN 'QUINTE_PLUS'     THEN 6 END, combinaison
        """, (date_str, co["num_reunion"], co["num_course"])).fetchall()

        if raps:
            print(f"  Rapports :")
            for rap in raps:
                nb = f"({int(rap['nb_gagnants'])} gagnants)" if rap["nb_gagnants"] else ""
                print(f"    {rap['type_pari']:<22} {rap['combinaison']:<12} {rap['dividende_euro']:>8.2f}€  {nb}")

    conn.close()
    print()


# ─────────────────────────────────────────────────────────
# EXPORT JSON
# ─────────────────────────────────────────────────────────
def export_json(date_str, date_ddmmyyyy):
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row

    def rows(sql):
        return [dict(r) for r in conn.execute(sql, (date_str,)).fetchall()]

    out = {
        "date":         date_str,
        "generated_at": datetime.now().isoformat(),
        "reunions":     rows("SELECT * FROM pmu_reunions  WHERE date_programme=?"),
        "courses":      rows("SELECT * FROM pmu_courses   WHERE date_programme=?"),
        "partants":     rows("SELECT * FROM pmu_partants  WHERE date_programme=?"),
        "rapports":     rows("SELECT * FROM pmu_rapports  WHERE date_programme=?"),
    }
    conn.close()

    fname = OUTPUT_DIR / f"pmu_{date_ddmmyyyy}.json"
    with open(fname, "w", encoding="utf-8") as f:
        json.dump(out, f, indent=2, ensure_ascii=False, default=str)
    print(f"  📁 Export JSON : {fname}\n")


# ─────────────────────────────────────────────────────────
# ORCHESTRATEUR PRINCIPAL
# ─────────────────────────────────────────────────────────
def run(date_ddmmyyyy: str):
    d        = datetime.strptime(date_ddmmyyyy, "%d%m%Y")
    date_str = d.strftime("%Y-%m-%d")

    print(f"\n{'='*65}")
    print(f"  🏇 PMU SCRAPER — {date_str}  (API client/7)")
    print(f"{'='*65}\n")

    init_db()
    conn = sqlite3.connect(DB_PATH)

    # ── 1. Programme ─────────────────────────────────────
    print("📡 Récupération du programme...")
    prog = api_get(f"/programme/{date_ddmmyyyy}", params={"meteo": "true"})
    if not prog:
        print("❌ Programme indisponible.")
        conn.close()
        return

    reunions = prog.get("programme", {}).get("reunions", [])
    print(f"   ✅ {len(reunions)} réunion(s)\n")

    total_courses = total_partants = total_rapports = 0

    for reunion in reunions:
        r_num    = reunion.get("numOfficiel")
        hipp     = reunion.get("hippodrome", {}).get("libelleCourt", "?")
        statut_r = reunion.get("statut", "")
        courses  = reunion.get("courses", [])

        print(f"  ── R{r_num} {hipp} ({statut_r}) — {len(courses)} course(s)")

        save_reunion(conn, date_str, reunion)
        save_meteo(conn, date_str, r_num, reunion.get("meteo"))
        conn.commit()

        for course in courses:
            c_num    = course.get("numOrdre")
            libelle  = course.get("libelleCourt", f"C{c_num}")
            heure    = ts_to_hhmm(course.get("heureDepart"))
            terminee = bool(course.get("arriveeDefinitive"))

            print(f"     C{c_num} {libelle:<20} {heure}  ", end="", flush=True)

            save_course(conn, date_str, r_num, course)
            total_courses += 1

            # ── 2. Participants ───────────────────────────
            parts = api_get(
                f"/programme/{date_ddmmyyyy}/R{r_num}/C{c_num}/participants",
                params={"withCotes": "true"}
            )
            time.sleep(0.2)

            if parts and "participants" in parts:
                n = save_partants(conn, date_str, r_num, c_num, parts["participants"])
                total_partants += n
                print(f"{n} partants  ", end="", flush=True)
            else:
                print("(pas de partants)  ", end="", flush=True)

            # ── 3. Rapports (si terminée) ─────────────────
            if terminee:
                raps = api_get(
                    f"/programme/{date_ddmmyyyy}/R{r_num}/C{c_num}/rapports-definitifs"
                )
                time.sleep(0.2)
                if raps and isinstance(raps, list):
                    n = save_rapports(conn, date_str, r_num, c_num, raps)
                    total_rapports += n
                    print(f"✅ {n} rapports", flush=True)
                else:
                    print("(pas de rapports)", flush=True)
            else:
                print("(en attente)", flush=True)

            conn.commit()

    calculate_and_save_scores(conn, date_str)

    conn.close()

    print(f"\n{'='*65}")
    print(f"  ✅ TERMINÉ — Courses: {total_courses} | Partants: {total_partants} | Rapports: {total_rapports}")
    print(f"{'='*65}\n")

    export_json(date_str, date_ddmmyyyy)
    afficher_resultats(date_str)
    afficher_bilan(date_str)


def calculate_and_save_scores(conn, date_str):
    """Calcule et sauvegarde les scores Top 5 pour chaque course terminée"""
    conn.row_factory = sqlite3.Row
    courses = conn.execute("""
        SELECT date_programme, num_reunion, num_course, libelle, heure_depart_str,
               ordre_arrivee_json
        FROM pmu_courses
        WHERE date_programme = ? AND arrivee_definitive = 1
    """, (date_str,)).fetchall()
    
    if not courses:
        print("  Aucune course terminée pour calcul des scores")
        return
    
    for course in courses:
        if not course['ordre_arrivee_json']:
            continue
        
        arrivee = json.loads(course['ordre_arrivee_json'])
        if not arrivee:
            continue
        
        race_name = f"R{course['num_reunion']}C{course['num_course']} - {course['libelle']}"
        
        partants = conn.execute("""
            SELECT nom, num_pmu, ordre_arrivee, cote_direct
            FROM pmu_partants
            WHERE date_programme = ? AND num_reunion = ? AND num_course = ?
            ORDER BY cote_direct ASC
        """, (date_str, course['num_reunion'], course['num_course'])).fetchall()
        
        if not partants:
            continue
        
        arrived_names = [p['nom'] for p in partants if p['ordre_arrivee'] and 1 <= p['ordre_arrivee'] <= 5]
        
        top5_cotes = [p['nom'] for p in partants[:5] if p['nom']]
        
        bases = [p['nom'] for p in conn.execute("""
            SELECT DISTINCT pr.horse_name
            FROM predictions pr
            WHERE pr.date = ? AND pr.source = 'Bases'
        """, (date_str,)).fetchall()]
        
        chances = [p['nom'] for p in conn.execute("""
            SELECT DISTINCT pr.horse_name
            FROM predictions pr
            WHERE pr.date = ? AND pr.source = 'Chances'
        """, (date_str,)).fetchall()]
        
        outsiders = [p['nom'] for p in conn.execute("""
            SELECT DISTINCT pr.horse_name
            FROM predictions pr
            WHERE pr.date = ? AND pr.source = 'Outsiders'
        """, (date_str,)).fetchall()]
        
        top5_bc = (bases + chances)[:5]
        top5_bo = (bases + outsiders)[:5]
        
        hits_cotes = sum(1 for h in top5_cotes if h in arrived_names)
        hits_bc = sum(1 for h in top5_bc if h in arrived_names)
        hits_bo = sum(1 for h in top5_bo if h in arrived_names)
        
        hippodrome = conn.execute("""
            SELECT r.hippodrome_long
            FROM pmu_reunions r
            WHERE r.date_programme = ? AND r.num_reunion = ?
        """, (date_str, course['num_reunion'])).fetchone()
        
        hippodrome_name = hippodrome['hippodrome_long'] if hippodrome else ''
        
        data = {
            'date': date_str,
            'race_name': race_name,
            'race_time': course['heure_depart_str'],
            'hippodrome': hippodrome_name,
            'top5_cotes': top5_cotes,
            'top5_cotes_hits': hits_cotes,
            'top5_bc': top5_bc,
            'top5_bc_hits': hits_bc,
            'top5_bo': top5_bo,
            'top5_bo_hits': hits_bo,
            'results_top5': arrived_names
        }
        
        try:
            auth = base64.b64encode(b'h3r7:h3r7').decode('ascii')
            resp = requests.post(
                'http://localhost:8765/turf/api/scores',
                json=data,
                headers={'Authorization': f'Basic {auth}'},
                timeout=10
            )
            print(f"  💾 Scores sauvegardés: {race_name} - Hits: Cotes:{hits_cotes} BC:{hits_bc} BO:{hits_bo}")
        except Exception as e:
            print(f"  ⚠️ Erreur sauvegarde scores: {e}")


# ─────────────────────────────────────────────────────────
# POINT D'ENTRÉE
# ─────────────────────────────────────────────────────────
if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="PMU Results — API client/7")
    parser.add_argument("--date", "-d",
        default=datetime.now().strftime("%d%m%Y"),
        help="Date DDMMYYYY (défaut: aujourd'hui)")
    parser.add_argument("--yesterday", "-y", action="store_true")
    parser.add_argument("--show",  "-s", action="store_true",
        help="Afficher résultats BDD sans scraper")
    parser.add_argument("--bilan", "-b", action="store_true",
        help="Afficher bilan prédictions vs arrivées")
    args = parser.parse_args()

    if args.yesterday:
        date_fmt = (datetime.now() - timedelta(days=1)).strftime("%d%m%Y")
    else:
        date_fmt = args.date

    date_iso = datetime.strptime(date_fmt, "%d%m%Y").strftime("%Y-%m-%d")

    if args.show:
        init_db()
        afficher_resultats(date_iso)
    elif args.bilan:
        init_db()
        afficher_bilan(date_iso)
    else:
        run(date_fmt)
