#!/usr/bin/env python3
"""Combined API - Turf + Ideas"""

from flask import Flask, jsonify, request, send_file, send_from_directory
from flask_cors import CORS
import sqlite3
import json
import os
import requests
from datetime import datetime, timedelta

app = Flask(__name__)
CORS(app, origins=["*"], methods=["GET", "POST", "OPTIONS"])
DB_PATH = "/home/h3r7/turf_saas/turf_saas.db"
IDEAS_FILE = "/home/h3r7/boite_a_idees/idees.json"


# Basic Auth decorator
def require_auth(f):
    from functools import wraps
    from flask import request

    @wraps(f)
    def decorated(*args, **kwargs):
        from flask import make_response
        import base64

        auth = request.headers.get("Authorization", "")
        expected = "Basic " + base64.b64encode("h3r7:h3r7".encode("utf-8")).decode(
            "utf-8"
        )
        if not auth or auth != expected:
            return make_response("Unauthorized", 401)
        return f(*args, **kwargs)

    return decorated


LLM_API_KEY = os.environ.get(
    "OPENROUTER_API_KEY",
    os.environ.get("OPENAI_API_KEY", os.environ.get("1MINAI_API_KEY", "")),
)
LLM_BASE_URL = os.environ.get("LLM_BASE_URL", "https://openrouter.ai/v1")
LLM_MODEL = os.environ.get("LLM_MODEL", "liquid/lfm-2.5-1.2b-instruct:free")

# === API KEYS ===
RESEND_API_KEY = os.environ.get("RESEND_API", "")
BRAVE_SEARCH_API_KEY = os.environ.get("BRAVE_SEARCH_API", "")

SQL_SCHEMA = """
Tables:
- 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, oeilleres, supplement, handicap_valeur, handicap_poids, musique, nombre_courses, nombre_victoires, nombre_places, cote_direct, cote_reference, tendance_cote, favoris, ordre_arrivee, tx_victoire, tx_place, forme_recente
- pmu_courses: date_programme, num_reunion, num_course, libelle, discipline, distance, hippodrome,px_type

Tables relations:
- pmu_partants.date_programme = pmu_courses.date_programme
- pmu_partants.num_reunion = pmu_courses.num_reunion  
- pmu_partants.num_course = pmu_courses.num_course

Key fields:
- ordre_arrivee: 1=winner, 2=2nd, 3=3rd, 0=not finished
- favoris: 1 if favorite (cote < 5), 0 otherwise
- date_programme: YYYY-MM-DD format
- discipline: 'Plat', 'Trot', 'Attele', 'Monte'
"""


def generate_sql_with_llm(question: str) -> str:
    """Generate SQL query from natural language using LLM"""
    if not LLM_API_KEY:
        return None

    try:
        import litellm

        litellm.drop_params = True

        system_prompt = f"""Tu es un expert SQL. Génère SEULEMENT la requête SQL (pas d'explication).

Schéma STRICT:
- Table pmu_partants: date_programme, num_reunion, num_course, nom, driver, entraineur, cote_direct, favoris, ordre_arrivee, tx_victoire, tx_place, forme_recente
- Table pmu_courses: date_programme, num_reunion, num_course, libelle, discipline, distance, hippodrome

Règles OBLIGATOIRES:
- Utilise les noms de colonnes EXACTS ci-dessus
- Utilise date('now', '-X days') pour les dates relatives
- Pour "aujourd'hui", utilise date('now')
- Pour "hier", utilise date('now', '-1 day')
- Pour "cette semaine", utilise date('now', '-7 days')
- Pour "ce mois", utilise date('now', '-30 days')
- JOIN requis entre pmu_partants et pmu_courses
- Limite à 15 résultats
- Only SELECT queries allowed

Question: {question}

SQL:"""

        response = litellm.completion(
            model=f"openrouter/{LLM_MODEL}",
            messages=[
                {"role": "system", "content": system_prompt},
                {"role": "user", "content": question},
            ],
            api_key=LLM_API_KEY,
            max_tokens=300,
        )

        sql = response.choices[0].message.content.strip()

        if sql.upper().startswith("SQL:"):
            sql = sql[4:].strip()
        if sql.upper().startswith("```SQL"):
            sql = sql[6:].strip()
        if sql.upper().startswith("```"):
            sql = sql[3:].strip()
        if sql.endswith("```"):
            sql = sql[:-3].strip()

        return sql if sql.upper().startswith("SELECT") else None

    except Exception as e:
        print(f"LLM SQL generation error: {e}")
        return None


# === TURF API ===
def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


@app.route("/health")
@app.route("/turf/health")
def health():
    """Health check endpoint for Docker/load balancer. Returns 200 if app is running."""
    import sqlite3 as _sqlite3

    db_ok = True
    try:
        conn = _sqlite3.connect(DB_PATH, timeout=2)
        conn.execute("SELECT 1")
        conn.close()
    except Exception:
        db_ok = False
    status = "ok" if db_ok else "degraded"
    http_code = 200 if db_ok else 503
    return {"status": status, "service": "combined-api", "db": db_ok}, http_code


@app.route("/")
def index():
    return send_file("/home/h3r7/turf_saas/dashboard.html")


@app.route("/dashboard")
def dashboard():
    return send_file("/home/h3r7/turf_saas/dashboard.html")


@app.route("/n8n-chat")
@app.route("/turf/n8n-chat")
def n8n_chat():
    return send_file("/home/h3r7/turf_saas/n8n-chat.html")


@app.route("/turf/api/n8n-proxy", methods=["GET", "POST", "OPTIONS"])
@app.route("/api/n8n-proxy", methods=["GET", "POST", "OPTIONS"])
def n8n_proxy():
    """Proxy pour éviter CORS vers n8n interne"""
    if request.method == "OPTIONS":
        response = make_response("", 200)
        response.headers["Access-Control-Allow-Origin"] = "*"
        response.headers["Access-Control-Allow-Methods"] = "GET, POST, OPTIONS"
        response.headers["Access-Control-Allow-Headers"] = "Content-Type"
        return response

    data = request.json or {}
    try:
        r = requests.post(
            "http://10.0.1.7:5678/webhook/openclaw",
            json=data,
            timeout=30,
            headers={"Content-Type": "application/json"},
        )
        try:
            return jsonify(r.json()), 200
        except:
            return jsonify({"message": r.text}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route("/api")
@app.route("/turf/api")
@app.route("/api/today")
def api_today():
    conn = get_db()
    today = datetime.now().strftime("%Y-%m-%d")
    yesterday = (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d")

    # Parametre de selection des courses
    race_filter = request.args.get("race", "")  # Nom de la course ou vide pour toutes

    data = {
        "date": today,
        "races": [],
        "predictions": {},
        "results": [],
        "scores": {},
        "weather": {},
    }

    # Construire la condition de filtre - si un nom de course est fourni, l'utiliser directement
    if race_filter:
        race_condition = "AND race_name = ?"  # Utilise le nom exact de la course
        race_params = (race_filter,)
    else:
        race_condition = ""
        race_params = ()

    # Filtre France uniquement - matcher avec LIKE sur nom hippodrome (désactivé car pas de données aujourd'hui)
    # france_condition = "AND EXISTS (SELECT 1 FROM pmu_reunions r WHERE r.pays_code='FRA' AND r.date_programme=date AND (r.hippodrome_long LIKE '%' || race_hippodrome || '%' OR r.hippodrome_court LIKE '%' || race_hippodrome || '%' OR r.hippodrome_code LIKE '%' || race_hippodrome || '%'))"
    france_condition = ""

    # Filtre données suffisantes (min 10 partants)
    min_partants_condition = "AND (SELECT COUNT(*) FROM predictions p2 WHERE p2.race_name=predictions.race_name AND p2.date=predictions.date AND p2.source='canalturf_partants') >= 10"

    # Recuperer toutes les courses du jour
    try:
        # Get full race name format: R1 ANGERS - C1 Grand prix ... (13:55)
        full_race_name = get_full_race_name(conn, today)

        c = conn.execute(
            f"""
            SELECT DISTINCT
                c.num_reunion,
                c.num_course,
                c.libelle,
                c.libelle_court,
                c.heure_depart_str,
                r.hippodrome_court,
                r.hippodrome_long,
                r.nature
            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=? {france_condition}
            ORDER BY c.num_reunion ASC, c.num_course ASC
        """,
            (today,),
        )
        races = c.fetchall()

        if not races:
            c = conn.execute(
                f"""
                SELECT DISTINCT race_name, race_hippodrome, race_time
                FROM predictions
                WHERE date=? AND source='canalturf_partants' {france_condition}
                ORDER BY race_time ASC
            """,
                (today,),
            )
            races = c.fetchall()

        def infer_race_type(libelle="", libelle_court="", nature=""):
            text = f"{libelle or ''} {libelle_court or ''} {nature or ''}".upper()
            if "QUINTE" in text:
                return "Quinté+"
            if "TROT" in text:
                return "Trot"
            if "PLAT" in text:
                return "Plat"
            return (nature or "").strip().title()

        def fmt_race(row):
            if "num_reunion" not in row.keys():
                race_name = row["race_name"] or ""
                hippo = row["race_hippodrome"] or ""
                time = row["race_time"] or ""
                race_type = infer_race_type(race_name)
                display = f"{hippo} - {race_name} - {time}".strip()
                if race_type:
                    display = f"{display} : {race_type}"
                return {
                    "name": race_name,
                    "filter": race_name,
                    "display_label": display,
                    "hippodrome": hippo,
                    "time": time,
                    "num_reunion": None,
                    "num_course": None,
                    "race_type": "",
                }
            num_reunion = row["num_reunion"]
            num_course = row["num_course"]
            hippo = row["hippodrome_court"] or row["hippodrome_long"] or ""
            course = row["libelle"] or row["libelle_court"] or ""
            time = row["heure_depart_str"] or ""
            race_type = infer_race_type(
                row["libelle"], row["libelle_court"], row["nature"]
            )
            display = f"R{num_reunion} {hippo} - {course} - {time}".strip(" -")
            if race_type:
                display = f"{display} : {race_type}"
            return {
                "name": row["libelle"] or course,
                "filter": row["libelle"] or course,
                "display_label": display,
                "hippodrome": hippo,
                "time": time,
                "num_reunion": num_reunion,
                "num_course": num_course,
                "race_type": row["nature"] or "",
            }

        data["races"] = [fmt_race(r) for r in races]

        # Première course comme course principale, ou course correspondant au filtre
        if races:
            selected_race = None
            if race_filter:
                import re

                race_filter_name = race_filter
                match = re.search(r"-\s+C\d+\s+(.+?)(?:\s*\(|$)", race_filter)
                if match:
                    race_filter_name = match.group(1).strip()
                selected_race = next(
                    (
                        fmt_race(r)
                        for r in races
                        if (r["libelle"] or "") == race_filter_name
                        or (r["libelle_court"] or "") == race_filter_name
                    ),
                    None,
                )
            first_race = selected_race or fmt_race(races[0])
            data["race"] = {
                "name": first_race["display_label"] or full_race_name,
                "display_label": first_race["display_label"] or full_race_name,
                "filter_name": first_race["filter"],
                "hippodrome": first_race["hippodrome"],
                "time": first_race["time"],
                "race_type": first_race["race_type"],
            }
    except Exception as e:
        print(f"Erreur races: {e}")
        data["race"] = {}

    # Partants avec cotes — 1 ligne par cheval (dédoublonnage)
    try:
        if race_filter:
            # Extract just the race name part (after time) for better matching
            race_name_part = race_filter
            # Try to extract the actual race name (after " - HH:MM ")
            import re

            match = re.search(r"-\s+\d{1,2}:\d{2}\s+(.+)$", race_filter)
            if match:
                race_name_part = match.group(1).strip()

            c = conn.execute(
                """
                SELECT horse_name, horse_number, AVG(odds) as odds, prediction_rank, jockey
                FROM predictions
                WHERE date=? AND source='canalturf_partants' AND odds > 0 AND (race_name LIKE ? OR ? LIKE '%' || race_name || '%')
                GROUP BY horse_name
                ORDER BY odds ASC
            """,
                (today, "%" + race_name_part + "%", race_filter),
            )
        else:
            c = conn.execute(
                """
                SELECT horse_name, horse_number, AVG(odds) as odds, prediction_rank, jockey
                FROM predictions
                WHERE date=? AND source='canalturf_partants' AND odds > 0
                GROUP BY horse_name
                ORDER BY odds ASC
            """,
                (today,),
            )
        data["predictions"]["partants"] = [dict(r) for r in c.fetchall()]
    except:
        data["predictions"]["partants"] = []

    # Pronostic bases/chances/outsiders - aussi filtré France
    for cat, src in [
        ("bases", "canalturf_prono_bases"),
        ("chances", "canalturf_prono_chances"),
        ("outsiders", "canalturf_prono_outsiders"),
    ]:
        try:
            if race_filter:
                # Extract just the race name part (after time) for better matching
                import re

                match = re.search(r"-\s+\d{1,2}:\d{2}\s+(.+)$", race_filter)
                race_name_part = race_filter
                if match:
                    race_name_part = match.group(1).strip()

                c = conn.execute(
                    """
                    SELECT horse_name, horse_number, MIN(prediction_rank) as prediction_rank
                    FROM predictions WHERE date=? AND source=? AND (race_name LIKE ? OR ? LIKE '%' || race_name || '%')
                    GROUP BY horse_name
                    ORDER BY prediction_rank
                """,
                    (today, src, "%" + race_name_part + "%", race_filter),
                )
            else:
                c = conn.execute(
                    """
                    SELECT horse_name, horse_number, MIN(prediction_rank) as prediction_rank
                    FROM predictions WHERE date=? AND source=?
                    GROUP BY horse_name
                    ORDER BY prediction_rank
                """,
                    (today, src),
                )
            data["predictions"][cat] = [dict(r) for r in c.fetchall()]
        except:
            data["predictions"][cat] = []

    # Resultats du jour - depuis pmu_partants (toutes les courses France terminées)
    try:
        # Add race filter if provided
        race_condition = ""
        race_params = [today]
        if race_filter:
            import re

            match = re.search(r"-\s+\d{1,2}:\d{2}\s+(.+)$", race_filter)
            race_name_part = race_filter
            if match:
                race_name_part = match.group(1).strip()
            race_condition = "AND (c.libelle LIKE ? OR ? LIKE '%' || c.libelle || '%')"
            race_params.append("%" + race_name_part + "%")
            race_params.append(race_filter)

        c = conn.execute(
            f"""
            SELECT 
                pp.nom as horse_name, 
                pp.ordre_arrivee as position, 
                pp.cote_direct as odds,
                pr.num_reunion,
                pr.hippodrome_court,
                c.heure_depart_str,
                c.libelle as race_name,
                c.num_course
            FROM pmu_partants pp
            JOIN pmu_reunions pr ON pr.date_programme=pp.date_programme AND pr.num_reunion=pp.num_reunion
            JOIN pmu_courses c ON c.date_programme=pp.date_programme AND c.num_reunion=pp.num_reunion AND c.num_course=pp.num_course
            WHERE pp.date_programme=? AND pp.ordre_arrivee > 0 AND pp.ordre_arrivee <= 5
                AND pr.pays_code='FRA' {race_condition}
            ORDER BY c.heure_depart_str, pp.ordre_arrivee
        """,
            race_params,
        )
        results = c.fetchall()
        if results:
            # Grouper par reunion
            current_reunion = None
            grouped = []
            for r in results:
                rd = dict(r)
                reunion_key = f"R{rd['num_reunion']} {rd['hippodrome_court']}"
                rd["race_label"] = reunion_key
                rd["race_type"] = rd["race_name"][:30] if rd["race_name"] else ""
                grouped.append(rd)
            data["results"] = grouped
        else:
            c = conn.execute(
                "SELECT horse_name, position, odds FROM results WHERE date=? ORDER BY position LIMIT 5",
                (today,),
            )
            data["results"] = [dict(r) for r in c.fetchall()]
    except Exception as e:
        print(f"Erreur résultats: {e}")
        data["results"] = []

    # Fallback: utiliser v_resultats_complets si results table est vide
    if not data["results"]:
        try:
            c = conn.execute(
                """
                SELECT date_programme as date, course as race_name, cheval as horse_name, 
                       position_finale as position, cote_direct as odds,
                       num_reunion, num_course
                FROM v_resultats_complets 
                WHERE date_programme=? AND position_finale IS NOT NULL AND position_finale > 0
                ORDER BY course, position_finale
            """,
                (today,),
            )
            rows = c.fetchall()
            if rows:
                data["results"] = [dict(r) for r in rows]
        except Exception as e:
            print(f"Erreur fallback résultats: {e}")

    # Fallback 2: afficher les résultats d'hier si pas de résultats aujourd'hui
    if not data["results"]:
        try:
            c = conn.execute(
                """
                SELECT date_programme as date, course as race_name, cheval as horse_name, 
                       position_finale as position, cote_direct as odds,
                       num_reunion, num_course
                FROM v_resultats_complets 
                WHERE date_programme=? AND position_finale IS NOT NULL AND position_finale > 0
                ORDER BY course, position_finale
            """,
                (yesterday,),
            )
            rows = c.fetchall()
            if rows:
                data["results"] = [dict(r) for r in rows]
        except:
            pass

    # Score hier
    try:
        c = conn.execute(
            "SELECT horse_name FROM results WHERE date=? AND position<=3", (yesterday,)
        )
        result_names = [r[0] for r in c.fetchall()]
        c = conn.execute(
            "SELECT DISTINCT horse_name FROM predictions WHERE date=? AND source='canalturf_prono_bases'",
            (yesterday,),
        )
        bases_yest = [r[0] for r in c.fetchall()]
        score = sum(
            1
            for p in bases_yest
            if any(
                p.upper() in r.upper() or r.upper() in p.upper() for r in result_names
            )
        )
        data["scores"] = {
            "bases": f"{score}/{len(bases_yest)}" if bases_yest else "-",
            "date": yesterday,
        }
    except:
        data["scores"] = {}

    # Weather
    try:
        c = conn.execute("SELECT * FROM weather ORDER BY id DESC LIMIT 1")
        w = c.fetchone()
        if w:
            data["weather"] = dict(w)
    except:
        pass

    conn.close()
    return jsonify(data)


@app.route("/api/races")
@app.route("/turf/api/races")
def api_races():
    """Liste des courses du jour avec selection"""
    conn = get_db()
    today = datetime.now().strftime("%Y-%m-%d")
    race_filter = request.args.get(
        "filter", "quinte"
    )  # 'quinte', 'all', 'trot', 'plat'

    if race_filter == "quinte":
        condition = "AND (race_name LIKE '%Quinte%' OR race_name LIKE '%Quinté%')"
    elif race_filter == "trot":
        condition = "AND race_name LIKE '%TROT%'"
    elif race_filter == "plat":
        condition = "AND race_name LIKE '%PLAT%'"
    else:
        condition = ""

    try:
        c = conn.execute(
            f"""
            SELECT DISTINCT race_name, race_hippodrome, race_time
            FROM predictions 
            WHERE date=? AND source='canalturf_partants' {condition}
            ORDER BY race_time ASC
        """,
            (today,),
        )

        races = []
        for r in c.fetchall():
            # Compter les partants
            c2 = conn.execute(
                """
                SELECT COUNT(DISTINCT horse_name) 
                FROM predictions 
                WHERE date=? AND race_name=? AND source='canalturf_partants'
            """,
                (today, r[0]),
            )
            partants = c2.fetchone()[0]

            races.append(
                {
                    "name": r[0],
                    "hippodrome": r[1],
                    "time": r[2],
                    "partants": partants,
                    "is_quinte": "Quinte" in (r[0] or ""),
                }
            )

        return jsonify({"date": today, "races": races, "filter": race_filter})
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        conn.close()


# === PREDICTIONS PAR COURSE ===
@app.route("/api/race/<race_name>")
@app.route("/turf/api/race/<race_name>")
def api_race_predictions(race_name):
    """Predictions pour une course specifique"""
    conn = get_db()
    today = datetime.now().strftime("%Y-%m-%d")
    yesterday = (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d")

    # Decoder le nom de la course
    import urllib.parse

    race_name = urllib.parse.unquote(race_name)

    data = {
        "date": today,
        "race": {"name": race_name},
        "predictions": {},
        "results": [],
        "scores": {},
    }

    # Infos de la course
    try:
        c = conn.execute(
            """
            SELECT DISTINCT race_hippodrome, race_time
            FROM predictions 
            WHERE date=? AND race_name=? AND source='canalturf_partants'
        """,
            (today, race_name),
        )
        row = c.fetchone()
        if row:
            data["race"]["hippodrome"] = row[0]
            data["race"]["time"] = row[1]
    except:
        pass

    # Partants
    try:
        c = conn.execute(
            """
            SELECT horse_name, horse_number, AVG(odds) as odds, prediction_rank, jockey
            FROM predictions
            WHERE date=? AND race_name=? AND source='canalturf_partants' AND odds > 0
            GROUP BY horse_name
            ORDER BY odds ASC
        """,
            (today, race_name),
        )
        data["predictions"]["partants"] = [dict(r) for r in c.fetchall()]
    except:
        data["predictions"]["partants"] = []

    # Pronostics
    for cat, src in [
        ("bases", "canalturf_prono_bases"),
        ("chances", "canalturf_prono_chances"),
        ("outsiders", "canalturf_prono_outsiders"),
    ]:
        try:
            c = conn.execute(
                """
                SELECT horse_name, horse_number, MIN(prediction_rank) as prediction_rank
                FROM predictions 
                WHERE date=? AND race_name=? AND source=?
                GROUP BY horse_name
                ORDER BY prediction_rank
            """,
                (today, race_name, src),
            )
            data["predictions"][cat] = [dict(r) for r in c.fetchall()]
        except:
            data["predictions"][cat] = []

    # Resultats
    try:
        c = conn.execute(
            """
            SELECT horse_name, position, odds 
            FROM results 
            WHERE date=? AND race_name LIKE ?
            ORDER BY position
        """,
            (today, f"%{race_name[:20]}%"),
        )
        data["results"] = [dict(r) for r in c.fetchall()]
    except:
        data["results"] = []

    conn.close()
    return jsonify(data)

    # Partants avec cotes — 1 ligne par cheval (dédoublonnage)
    try:
        c = conn.execute(
            """
            SELECT horse_name, horse_number, AVG(odds) as odds, prediction_rank, jockey
            FROM predictions
            WHERE date=? AND source='canalturf_partants' AND odds > 0
            GROUP BY horse_name
            ORDER BY odds ASC
        """,
            (today,),
        )
        data["predictions"]["partants"] = [dict(r) for r in c.fetchall()]
    except:
        data["predictions"]["partants"] = []

    # Pronostic bases/chances/outsiders — 1 ligne par cheval
    for cat, src in [
        ("bases", "canalturf_prono_bases"),
        ("chances", "canalturf_prono_chances"),
        ("outsiders", "canalturf_prono_outsiders"),
    ]:
        try:
            c = conn.execute(
                """
                SELECT horse_name, horse_number, MIN(prediction_rank) as prediction_rank
                FROM predictions WHERE date=? AND source=?
                GROUP BY horse_name
                ORDER BY prediction_rank
            """,
                (today, src),
            )
            data["predictions"][cat] = [dict(r) for r in c.fetchall()]
        except:
            data["predictions"][cat] = []

    # Legacy ours (compatibilité)
    try:
        c = conn.execute(
            "SELECT horse_name, odds, prediction_rank FROM predictions WHERE date=?",
            (today,),
        )
        data["predictions"]["ours"] = [
            {"horse_name": r[0], "odds": r[1], "prediction_rank": r[2]}
            for r in c.fetchall()
        ]
    except:
        data["predictions"]["ours"] = []

    # Résultats du jour
    try:
        c = conn.execute(
            "SELECT horse_name, position, odds FROM results WHERE date=? ORDER BY position LIMIT 5",
            (today,),
        )
        data["results"] = [dict(r) for r in c.fetchall()]
    except:
        data["results"] = []

    # Score hier
    try:
        c = conn.execute(
            "SELECT horse_name FROM results WHERE date=? AND position<=3", (yesterday,)
        )
        result_names = [r[0] for r in c.fetchall()]
        c = conn.execute(
            "SELECT DISTINCT horse_name FROM predictions WHERE date=? AND source='canalturf_prono_bases'",
            (yesterday,),
        )
        bases_yest = [r[0] for r in c.fetchall()]
        score = sum(
            1
            for p in bases_yest
            if any(
                p.upper() in r.upper() or r.upper() in p.upper() for r in result_names
            )
        )
        data["scores"] = {
            "bases": f"{score}/{len(bases_yest)}" if bases_yest else "-",
            "date": yesterday,
        }
    except:
        data["scores"] = {}

    # Weather
    try:
        c = conn.execute("SELECT * FROM weather ORDER BY id DESC LIMIT 1")
        w = c.fetchone()
        if w:
            data["weather"] = dict(w)
    except:
        pass

    conn.close()
    return jsonify(data)


@app.route("/api/odds_history")
@app.route("/turf/api/odds_history")
def api_odds_history():
    conn = get_db()
    today = datetime.now().strftime("%Y-%m-%d")

    try:
        c = conn.execute(
            """
            SELECT horse_name, horse_number, odds, MIN(scraped_at) as scraped_at
            FROM odds_history WHERE date=?
            GROUP BY horse_name, DATE(scraped_at), SUBSTR(scraped_at, 12, 5)
            ORDER BY horse_name, scraped_at ASC
        """,
            (today,),
        )
        rows = c.fetchall()
    except:
        conn.close()
        return jsonify({"date": today, "horses": []})

    horses = {}
    for row in rows:
        h = row["horse_name"]
        if h not in horses:
            horses[h] = {
                "horse_name": h,
                "horse_number": row["horse_number"],
                "snapshots": [],
            }
        horses[h]["snapshots"].append(
            {"odds": row["odds"], "time": row["scraped_at"][11:16]}
        )

    result = []
    for h, data in horses.items():
        snaps = data["snapshots"]
        debut = snaps[0]["odds"] if snaps else 0
        actuel = snaps[-1]["odds"] if snaps else 0
        evol = (
            round(((actuel - debut) / debut) * 100, 1)
            if debut > 0 and len(snaps) > 1
            else 0
        )
        result.append(
            {
                "horse_name": h,
                "horse_number": data["horse_number"],
                "odds_debut": debut,
                "odds_actuel": actuel,
                "evol_pct": evol,
                "nb_snapshots": len(snaps),
                "snapshots": snaps,
                "tendance": "baisse"
                if evol < -5
                else "hausse"
                if evol > 5
                else "stable",
            }
        )

    result.sort(key=lambda x: x["odds_actuel"])
    conn.close()
    return jsonify({"date": today, "horses": result})


@app.route("/api/weather")
def api_weather():
    conn = get_db()
    c = conn.execute("SELECT * FROM weather ORDER BY id DESC LIMIT 4")
    weather = [dict(row) for row in c.fetchall()]
    conn.close()
    return jsonify(weather)


# === IDEAS API ===
def load_ideas():
    if not os.path.exists(IDEAS_FILE):
        os.makedirs(os.path.dirname(IDEAS_FILE), exist_ok=True)
        default = {
            "categories": [
                {"id": "tech", "name": "Tech & IA", "color": "#7b2cbf"},
                {"id": "saas", "name": "SaaS", "color": "#2ec4b6"},
                {"id": "service", "name": "Service", "color": "#e94560"},
                {"id": "produit", "name": "Produit", "color": "#ff9f1c"},
                {"id": "invest", "name": "Investissement", "color": "#00d9ff"},
            ],
            "ideas": [],
            "next_id": 1,
        }
        with open(IDEAS_FILE, "w") as f:
            json.dump(default, f, indent=2)
        return default
    with open(IDEAS_FILE, "r") as f:
        return json.load(f)


def save_ideas(data):
    os.makedirs(os.path.dirname(IDEAS_FILE), exist_ok=True)
    with open(IDEAS_FILE, "w") as f:
        json.dump(data, f, indent=2)


@app.route("/api/ideas")
def get_ideas():
    return jsonify(load_ideas())


@app.route("/api/ideas", methods=["POST"])
def add_idea():
    data = load_ideas()
    new_idea = request.json
    new_idea["id"] = data["next_id"]
    new_idea["created"] = datetime.now().strftime("%Y-%m-%d")
    data["ideas"].append(new_idea)
    data["next_id"] += 1
    save_ideas(data)
    return jsonify({"success": True, "id": new_idea["id"]})


@app.route("/api/ideas/<int:idea_id>", methods=["GET"])
def get_idea(idea_id):
    data = load_ideas()
    for i in data["ideas"]:
        if i["id"] == idea_id:
            return jsonify(i)
    return jsonify({"error": "Not found"}), 404


@app.route("/api/ideas/<int:idea_id>", methods=["PUT"])
def update_idea(idea_id):
    data = load_ideas()
    for i, idea in enumerate(data["ideas"]):
        if idea["id"] == idea_id:
            data["ideas"][i].update(request.json)
            save_ideas(data)
            return jsonify({"success": True})
    return jsonify({"error": "Not found"}), 404


@app.route("/api/ideas/<int:idea_id>", methods=["DELETE"])
def delete_idea(idea_id):
    data = load_ideas()
    data["ideas"] = [i for i in data["ideas"] if i["id"] != idea_id]
    save_ideas(data)
    return jsonify({"success": True})


@app.route("/idees")
@app.route("/idees/")
def idees_page():
    return send_from_directory("/home/h3r7/turf_saas", "idees_final.html")


@app.route("/H3R7Tech_logo.png")
@app.route("/turf/H3R7Tech_logo.png")
def serve_logo():
    return send_from_directory("/home/h3r7/turf_saas", "H3R7Tech_logo.png")


@app.route("/turf/<path:filename>")
def serve_static_turf(filename):
    if filename.endswith((".html", ".md", ".png", ".svg", ".jpg", ".css", ".js")):
        return send_from_directory("/home/h3r7/turf_saas", filename)
    return "Not found", 404


@app.route("/<path:filename>")
def serve_static(filename):
    if filename.endswith((".html", ".md", ".png", ".svg", ".jpg")):
        return send_from_directory("/home/h3r7/turf_saas", filename)
    return "Not found", 404


def ensure_scoring_tables(conn):
    conn.execute("""
        CREATE TABLE IF NOT EXISTS scoring (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT NOT NULL,
            race_name TEXT,
            horse_name TEXT,
            horse_number INTEGER,
            score REAL,
            score_cote REAL,
            score_forme REAL,
            score_victoire REAL,
            score_place REAL,
            score_rk REAL,
            score_tendance REAL,
            score_avis REAL,
            cote REAL,
            forme_recente REAL,
            tx_victoire REAL,
            tx_place REAL,
            avis_entraineur TEXT,
            musique TEXT,
            rang_scoring INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS recommendations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT NOT NULL,
            race_name TEXT,
            type_pari TEXT,
            cheval1 TEXT,
            numero1 INTEGER,
            cheval2 TEXT,
            numero2 INTEGER,
            cote REAL,
            mise REAL,
            gain_potentiel REAL,
            confiance TEXT,
            justification TEXT,
            resultat TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)


def build_scoring_fallback(conn, date_str):
    try:
        import sys

        if "/home/h3r7/turf_saas" not in sys.path:
            sys.path.insert(0, "/home/h3r7/turf_saas")
        from scoring import build_recommendations, score_cheval
    except Exception:
        return [], {}, "R1C1"

    def find_race(for_date):
        return conn.execute(
            """
        SELECT c.num_reunion, c.num_course, c.libelle, c.libelle_court, c.heure_depart_str
        FROM pmu_courses c
        LEFT JOIN pmu_reunions r
            ON r.date_programme = c.date_programme AND r.num_reunion = c.num_reunion
        WHERE c.date_programme = ? AND COALESCE(r.pays_code, 'FRA') = 'FRA'
        ORDER BY
            CASE WHEN UPPER(COALESCE(c.libelle, '')) LIKE '%QUINTE%' THEN 0 ELSE 1 END,
            c.num_reunion,
            c.num_course
        LIMIT 1
    """,
            (for_date,),
        ).fetchone()

    race = find_race(date_str)
    if not race:
        fallback_date = conn.execute(
            "SELECT MAX(date_programme) FROM pmu_courses"
        ).fetchone()[0]
        if not fallback_date:
            fallback_date = conn.execute(
                "SELECT MAX(date_programme) FROM pmu_partants"
            ).fetchone()[0]
        if fallback_date:
            date_str = fallback_date
            race = find_race(date_str)

    if not race:
        return [], {}, "R1C1"

    race_label = f"R{race['num_reunion']}C{race['num_course']}"
    race_name = race["libelle"] or race["libelle_court"] or race_label

    rows = conn.execute(
        """
        SELECT p.num_pmu, p.nom, p.musique, p.nombre_courses, p.nombre_victoires,
               p.nombre_places, p.cote_direct, p.cote_reference, p.driver_change,
               p.entraineur, p.tx_victoire, p.tx_place, p.forme_recente,
               p.oeilleres, c.distance, c.discipline, c.nb_declares_partants
        FROM pmu_partants p
        LEFT 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
        WHERE p.date_programme = ? AND p.num_reunion = ? AND p.num_course = ?
        ORDER BY p.num_pmu
    """,
        (date_str, race["num_reunion"], race["num_course"]),
    ).fetchall()

    participants = []
    for row in rows:
        r = dict(row)
        participants.append(
            {
                "nom": r["nom"],
                "numero": r["num_pmu"],
                "musique": r.get("musique", ""),
                "nombreCourses": r.get("nombre_courses", 0) or 0,
                "nombreVictoires": r.get("nombre_victoires", 0) or 0,
                "nombrePlaces": r.get("nombre_places", 0) or 0,
                "reductionKilometrique": 0,
                "avisEntraineur": "NEUTRE",
                "driverChange": bool(r.get("driver_change", 0)),
                "dernierRapportDirect": {"rapport": r.get("cote_direct", 0) or 0},
                "dernierRapportReference": {
                    "rapport": r.get("cote_reference", 0)
                    or r.get("cote_direct", 0)
                    or 0
                },
                "partants": r.get("nb_declares_partants", 0) or 0,
                "distance": r.get("distance", 0) or 0,
                "discipline": r.get("discipline", "PLAT") or "PLAT",
            }
        )

    if not participants:
        return [], {}, race_label

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

    scored_horses = sorted(scored_horses, key=lambda x: x["score"], reverse=True)
    recos = build_recommendations(scored_horses)
    scores = []
    for rank, h in enumerate(scored_horses, start=1):
        d = h["details"]
        scores.append(
            {
                "race_label": race_label,
                "horse_name": h["nom"],
                "horse_number": h["numero"],
                "score": h["score"],
                "cote": d.get("cote", 0),
                "forme_recente": d.get("forme_recente", 0),
                "tx_victoire": d.get("tx_victoire", 0),
                "avis_entraineur": d.get("avis_entraineur", "NEUTRE"),
                "musique": d.get("musique", ""),
                "rang_scoring": rank,
            }
        )

    return scores, recos, race_name


@app.route("/api/scoring")
@app.route("/turf/api/scoring")
def api_scoring():
    conn = get_db()
    today = datetime.now().strftime("%Y-%m-%d")
    race_filter = request.args.get("race", "").strip()
    ensure_scoring_tables(conn)

    def infer_race_type(libelle="", libelle_court="", nature=""):
        text = f"{libelle or ''} {libelle_court or ''} {nature or ''}".upper()
        if "QUINTE" in text:
            return "Quinté+"
        if "TROT" in text:
            return "Trot"
        if "PLAT" in text:
            return "Plat"
        return (nature or "").strip().title()

    def format_race_label(row):
        reunion = row["num_reunion"]
        course = row["num_course"]
        hippodrome = row["hippodrome_court"] or row["hippodrome_long"] or ""
        name = row["libelle"] or row["libelle_court"] or ""
        time = row["heure_depart_str"] or ""
        race_type = infer_race_type(row["libelle"], row["libelle_court"], row["nature"])
        label = f"R{reunion} {hippodrome} - {name} - {time}".strip(" -")
        if race_type:
            label = f"{label} : {race_type}"
        return {
            "race_label": f"R{reunion}C{course}",
            "full_race_name": label,
            "race_type": race_type,
            "race_time": time,
            "hippodrome": hippodrome,
            "name": name,
        }

    race_info = None
    try:
        if race_filter:
            race_info = conn.execute(
                """
                SELECT c.num_reunion, c.num_course, c.libelle, c.libelle_court, c.heure_depart_str,
                       r.hippodrome_court, r.hippodrome_long, r.nature
                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 (
                    c.libelle = ? OR c.libelle_court = ? OR c.libelle LIKE ? OR c.libelle_court LIKE ?
                )
                ORDER BY c.heure_depart_str ASC, c.num_reunion ASC, c.num_course ASC
                LIMIT 1
            """,
                (
                    today,
                    race_filter,
                    race_filter,
                    f"%{race_filter}%",
                    f"%{race_filter}%",
                ),
            ).fetchone()
        if not race_info:
            # Essayer de trouver la course qui correspond au scoring du jour
            scored_race = conn.execute(
                "SELECT race_name FROM scoring WHERE date=? LIMIT 1", (today,)
            ).fetchone()
            if scored_race and scored_race["race_name"]:
                race_name_db = scored_race["race_name"]
                race_info = conn.execute(
                    """
                    SELECT c.num_reunion, c.num_course, c.libelle, c.libelle_court, c.heure_depart_str,
                           r.hippodrome_court, r.hippodrome_long, r.nature
                    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 (
                        c.libelle = ? OR c.libelle LIKE ? OR c.libelle_court LIKE ?
                    )
                    LIMIT 1
                """,
                    (today, race_name_db, f"%{race_name_db}%", f"%{race_name_db}%"),
                ).fetchone()
        if not race_info:
            race_info = conn.execute(
                """
                SELECT c.num_reunion, c.num_course, c.libelle, c.libelle_court, c.heure_depart_str,
                       r.hippodrome_court, r.hippodrome_long, r.nature
                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 COALESCE(r.pays_code, 'FRA') = 'FRA'
                ORDER BY c.heure_depart_str ASC, c.num_reunion ASC, c.num_course ASC
                LIMIT 1
            """,
                (today,),
            ).fetchone()
    except Exception:
        race_info = None

    if race_info:
        race_meta = format_race_label(race_info)
        race_label = race_meta["race_label"]
        full_race_name = race_meta["full_race_name"]
    else:
        race_label = "R1C1"
        full_race_name = "R1C1"

    # Scores du jour
    try:
        c = conn.execute(
            """
            SELECT horse_name, horse_number, score, COALESCE(cote,0) as cote, forme_recente,
                   tx_victoire, avis_entraineur, musique, rang_scoring
            FROM scoring WHERE date=?
            ORDER BY rang_scoring ASC
        """,
            (today,),
        )
        scores = [dict(r) for r in c.fetchall()]
        # Add race label and full race name to each score
        for s in scores:
            s["race_label"] = race_label
            s["full_race_name"] = full_race_name
    except:
        scores = []

    if not scores:
        scores, recos, fallback_race_name = build_scoring_fallback(conn, today)
        if scores and fallback_race_name:
            for s in scores:
                s["race_label"] = fallback_race_name
                s["full_race_name"] = (
                    full_race_name if full_race_name != "R1C1" else fallback_race_name
                )
    else:
        # Recommandations du jour - Focus B4/B3 (ZE5)
        recos = generate_ze5_recommendations(conn, today)

    # Sauvegarder les recommandations ZE5 dans la DB
    save_ze5_recommendations(conn, today, recos)

    # Ajouter les recommandations depuis la table recommendations
    try:
        c = conn.execute(
            """
            SELECT type_pari, cheval1, numero1, cheval2, numero2,
                   cote, mise, gain_potentiel, confiance, justification
            FROM recommendations WHERE date=?
            ORDER BY type_pari
        """,
            (today,),
        )
        for r in c.fetchall():
            recos[r["type_pari"]] = dict(r)
    except:
        pass

    conn.close()
    return jsonify(
        {
            "date": today,
            "scores": scores,
            "recommendations": recos,
            "full_race_name": full_race_name,
            "race_label": race_label,
        }
    )


def generate_ze5_recommendations(conn, today):
    """
    Génère des recommandations ZE5 avec focus B4/B3
    Stratégie: Bases + Chances + 1 Outsider = bonne couverture pour B4/B3
    """
    recos = {}

    try:
        # Récupérer nos prédictions
        bases = [
            r["horse_name"]
            for r in conn.execute(
                """
            SELECT DISTINCT horse_name FROM predictions 
            WHERE date=? AND source='canalturf_prono_bases'
            ORDER BY prediction_rank LIMIT 2
        """,
                (today,),
            ).fetchall()
        ]

        chances = [
            r["horse_name"]
            for r in conn.execute(
                """
            SELECT DISTINCT horse_name FROM predictions 
            WHERE date=? AND source='canalturf_prono_chances'
            ORDER BY prediction_rank LIMIT 3
        """,
                (today,),
            ).fetchall()
        ]

        outsiders = [
            r["horse_name"]
            for r in conn.execute(
                """
            SELECT DISTINCT horse_name FROM predictions 
            WHERE date=? AND source='canalturf_prono_outsiders'
            ORDER BY prediction_rank LIMIT 2
        """,
                (today,),
            ).fetchall()
        ]

        if not bases or not chances:
            return recos

        # Get horse numbers too
        def get_horse_num(name):
            row = conn.execute(
                "SELECT horse_number FROM predictions WHERE date=? AND horse_name=? LIMIT 1",
                (today, name),
            ).fetchone()
            return str(row[0]) if row else ""

        # Format: "NUMERO NOM"
        def format_horse(name):
            num = get_horse_num(name)
            return f"{num} {name}" if num else name

        # Stratégie B4/B3: 4-5 chevaux couvrant bases + chances
        all_horses = bases[:2] + chances[:2]
        if outsiders:
            all_horses.append(outsiders[0])

        if len(all_horses) >= 4:
            combo = "-".join([format_horse(h) for h in all_horses[:5]])
            recos["ze5_b4b3"] = {
                "type_pari": "ZE5 B4/B3",
                "cheval1": combo,
                "cote": 15.0,
                "mise": 3,
                "gain_potentiel": 45,
                "confiance": "haute",
                "justification": "Bases + Chances + Outsider. Optimise pour B4/B3",
                "strategy": "b4b3",
            }

        # ZE5 Conservateur: Top 2 bases + Top 2 chances
        if len(bases) >= 1 and len(chances) >= 2:
            combo_list = [
                format_horse(bases[0]),
                format_horse(chances[0]),
                format_horse(chances[1]),
            ]
            if len(bases) > 1:
                combo_list.append(format_horse(bases[1]))
            else:
                combo_list.append(format_horse(chances[0]))
            combo_conservative = "-".join(combo_list)
            recos["ze5_conservateur"] = {
                "type_pari": "ZE5 Conservateur",
                "cheval1": combo_conservative,
                "cote": 8.0,
                "mise": 3,
                "gain_potentiel": 24,
                "confiance": "moyenne-haute",
                "justification": "Favori + 2 Chances - Plus securise",
                "strategy": "conservateur",
            }

        # ZE5 Audacieux: outsiders + 1 base
        if len(outsiders) >= 2 and bases:
            combo_audacieux_list = [
                format_horse(bases[0]),
                format_horse(outsiders[0]),
                format_horse(outsiders[1]),
            ]
            if chances:
                combo_audacieux_list.append(format_horse(chances[0]))
            combo_audacieux = "-".join(combo_audacieux_list)
            recos["ze5_audacieux"] = {
                "type_pari": "ZE5 Audacieux",
                "cheval1": combo_audacieux,
                "cote": 50.0,
                "mise": 1,
                "gain_potentiel": 50,
                "confiance": "basse",
                "justification": "Base + 2 Outsiders + Chance. Rapport élevé mais moins probable",
                "strategy": "audacieux",
            }

        # ZE4 (équivalent Multi): Top 4 bases + chances
        # Trouver les 4 premiers
        if len(bases) >= 2 and len(chances) >= 2:
            ze4_horses = [
                format_horse(bases[0]),
                format_horse(bases[1]),
                format_horse(chances[0]),
                format_horse(chances[1]),
            ]
            combo_ze4 = "-".join(ze4_horses)
            recos["ze4"] = {
                "type_pari": "ZE4 (Multi 4)",
                "cheval1": combo_ze4,
                "cote": 20.0,
                "mise": 3,
                "gain_potentiel": 60,
                "confiance": "haute",
                "justification": "Top 2 Bases + Top 2 Chances. Trouver 4 premiers",
                "strategy": "ze4",
            }

    except Exception as e:
        print(f"Erreur génération recommandations: {e}")

    return recos


def get_full_race_name(conn, date):
    """Construit le nom complet de la course: R1 MARSEILLE BORELY - 13h55 Grand National du Trot - Prix ..."""
    # Try to get from pmu_reunions first
    r = conn.execute(
        """
        SELECT r.num_reunion, r.hippodrome_court, c.heure_depart_str, 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
        ORDER BY r.num_reunion
        LIMIT 1
    """,
        (date,),
    ).fetchone()

    if r:
        return f"R{r[0]} {r[1]} - {r[2]} {r[3]}"

    # Fallback: get from predictions table
    r2 = conn.execute(
        """
        SELECT DISTINCT race_name, race_hippodrome, race_time
        FROM predictions
        WHERE date=? AND source='canalturf_partants'
        ORDER BY race_time ASC
        LIMIT 1
    """,
        (date,),
    ).fetchone()

    if r2:
        return f"{r2[1]} - {r2[2]} {r2[0]}"

    return "Course du jour"


def save_ze5_recommendations(conn, today, recos):
    """Sauvegarde les recommandations ZE5 dans la base de données"""
    try:
        race_name = get_full_race_name(conn, today)

        saved = 0
        for key, r in recos.items():
            if not (key.startswith("ze5") or key == "ze4"):
                continue

            conn.execute(
                """
                INSERT OR REPLACE INTO recommendations 
                (date, race_name, type_pari, cheval1, numero1, cote, mise, gain_potentiel, confiance, justification, scoring_version)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
                (
                    today,
                    race_name,
                    r["type_pari"],
                    r["cheval1"],
                    0,
                    r.get("cote", 0),
                    r.get("mise", 0),
                    r.get("gain_potentiel", 0),
                    r.get("confiance", ""),
                    r.get("justification", ""),
                    key,
                ),
            )
            saved += 1
        conn.commit()
        print(
            f"✅ {len([k for k in recos if k.startswith('ze5')])} recommandations ZE5 sauvegardées"
        )
    except Exception as e:
        print(f"Erreur sauvegarde ZE5: {e}")


# === ML PREDICTIONS ===
import pickle
import pandas as pd
import numpy as np
from sklearn.preprocessing import LabelEncoder

ml_models = None
MODEL_PATH = "/home/h3r7/turf_saas/xgboost_models.pkl"


def load_models():
    global ml_models
    if ml_models is None and os.path.exists(MODEL_PATH):
        try:
            with open(MODEL_PATH, "rb") as f:
                ml_models = pickle.load(f)
        except:
            ml_models = False
    return ml_models


def table_exists(conn, table_name):
    c = conn.execute(
        "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?", (table_name,)
    )
    return c.fetchone() is not None


def ensure_analytics_tables():
    conn = sqlite3.connect(DB_PATH)
    tables = [
        (
            "bet_results",
            """
            CREATE TABLE IF NOT EXISTS bet_results (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                date TEXT,
                race_name TEXT,
                type_pari TEXT,
                horse_name TEXT,
                horse_number INTEGER,
                cote REAL,
                mise REAL,
                resultat TEXT,
                gain REAL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """,
        ),
        (
            "daily_stats",
            """
            CREATE TABLE IF NOT EXISTS daily_stats (
                date TEXT PRIMARY KEY,
                total_bets INTEGER,
                bets_gagne INTEGER,
                bets_perdu INTEGER,
                mise_totale REAL,
                gain_total REAL,
                precision_pct REAL,
                roi_pct REAL
            )
        """,
        ),
        (
            "stats_by_type",
            """
            CREATE TABLE IF NOT EXISTS stats_by_type (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                date TEXT,
                type_pari TEXT,
                total_bets INTEGER,
                gagne INTEGER,
                perdu INTEGER,
                mise_totale REAL,
                gain_total REAL,
                precision_pct REAL,
                roi_pct REAL
            )
        """,
        ),
        (
            "race_scores",
            """
            CREATE TABLE IF NOT EXISTS race_scores (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                date TEXT,
                race_name TEXT,
                race_time TEXT,
                hippodrome TEXT,
                top5_cotes TEXT,
                top5_cotes_hits INTEGER,
                top5_bc TEXT,
                top5_bc_hits INTEGER,
                top5_bo TEXT,
                top5_bo_hits INTEGER,
                results_top5 TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """,
        ),
        (
            "paris",
            """
            CREATE TABLE IF NOT EXISTS paris (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                date_pari TEXT NOT NULL,
                date_course TEXT NOT NULL,
                race_name TEXT,
                race_label TEXT,
                hippodrome TEXT,
                type_pari TEXT,
                chevaux TEXT,
                cheval1 TEXT,
                numero1 INTEGER,
                cheval2 TEXT,
                numero2 INTEGER,
                cheval3 TEXT,
                numero3 INTEGER,
                cote REAL,
                mise REAL DEFAULT 1.0,
                statut TEXT DEFAULT 'EN_ATTENTE',
                gain REAL DEFAULT 0,
                commentaire TEXT,
                source_reco TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """,
        ),
    ]
    for name, ddl in tables:
        conn.execute(ddl)
    conn.close()


ensure_analytics_tables()


def load_ml_horses(conn, today):
    """Load horses for ML predictions, with a fallback when historical_data is absent."""
    course_info = {}

    c = conn.execute(
        """
        SELECT num_reunion, num_course, libelle, libelle_court, discipline, distance, heure_depart_str
        FROM pmu_courses
        WHERE date_programme = ?
        ORDER BY num_reunion, num_course
    """,
        (today,),
    )
    for row in c.fetchall():
        course_info[f"{row['num_reunion']}_{row['num_course']}"] = dict(row)

    if table_exists(conn, "historical_data"):
        c = conn.execute(
            """
            SELECT DISTINCT p.horse_name, p.horse_number, p.odds,
                   h.age, h.sexe, h.nb_courses, h.nb_victoires, h.nb_places,
                   h.tx_victoire, h.tx_place, h.forme_recente, h.reduction_km,
                   h.gains_annee, h.cote_directe, h.distance, h.discipline,
                   h.avis_entraineur, h.oeilleres, h.deferre, h.nb_partants,
                   h.rang_cote, h.ratio_cote_field, h.musique
            FROM predictions p
            LEFT JOIN historical_data h ON h.horse_name = p.horse_name
            WHERE p.date = ? AND p.source = 'canalturf_partants' AND p.odds > 0
        """,
            (today,),
        )
        horses = [dict(row) for row in c.fetchall()]
        return today, horses, course_info

    def fetch_fallback(date_str):
        c = conn.execute(
            """
            SELECT
                p.date_programme AS date,
                p.num_reunion,
                p.num_course,
                p.num_pmu AS horse_number,
                p.nom AS horse_name,
                p.age,
                p.sexe,
                p.musique,
                p.nombre_courses AS nb_courses,
                p.nombre_victoires AS nb_victoires,
                p.nombre_places AS nb_places,
                p.gains_annee_en_cours AS gains_annee,
                COALESCE(p.cote_direct, 0) AS cote_directe,
                COALESCE(c.distance, 0) AS distance,
                COALESCE(c.discipline, 'PLAT') AS discipline,
                COALESCE(c.nb_declares_partants, 0) AS nb_partants,
                COALESCE(p.oeilleres, 'SANS_OEILLERES') AS oeilleres,
                COALESCE(p.tx_victoire, 0) AS tx_victoire,
                COALESCE(p.tx_place, 0) AS tx_place,
                COALESCE(p.forme_recente, 0) AS forme_recente,
                0 AS reduction_km,
                'NEUTRE' AS avis_entraineur,
                'NON' AS deferre,
                0 AS rang_cote,
                0 AS ratio_cote_field
            FROM pmu_partants p
            LEFT 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
            WHERE p.date_programme = ?
            ORDER BY p.num_reunion, p.num_course, p.num_pmu
        """,
            (date_str,),
        )
        horses = [dict(row) for row in c.fetchall()]
        return horses

    horses = fetch_fallback(today)
    if horses:
        return today, horses, course_info

    c = conn.execute("SELECT MAX(date_programme) FROM pmu_partants")
    fallback_date = c.fetchone()[0]
    if fallback_date:
        return fallback_date, fetch_fallback(fallback_date), {}

    return today, [], {}


def enrich_ml_horses(horses):
    """Fill missing ML fields and derive odds-based features."""
    races = {}
    for horse in horses:
        race_key = (
            horse.get("date") or horse.get("date_programme"),
            horse.get("num_reunion"),
            horse.get("num_course"),
        )
        races.setdefault(race_key, []).append(horse)

    for group in races.values():
        odds_values = []
        for horse in group:
            raw_odds = horse.get("odds", horse.get("cote_directe", 0))
            try:
                odds = float(raw_odds or 0)
            except (TypeError, ValueError):
                odds = 0.0
            horse["odds"] = odds
            horse["cote_directe"] = float(horse.get("cote_directe", odds) or odds or 0)
            if odds > 0:
                odds_values.append(odds)

        avg_odds = sum(odds_values) / len(odds_values) if odds_values else 0
        ranked = sorted(
            group, key=lambda h: h.get("odds", h.get("cote_directe", 0)) or 999999
        )

        for idx, horse in enumerate(ranked, start=1):
            horse.setdefault("horse_number", horse.get("num_pmu"))
            horse.setdefault("horse_name", horse.get("nom"))
            horse.setdefault("age", 0)
            horse.setdefault("sexe", "U")
            horse.setdefault("nb_courses", 0)
            horse.setdefault("nb_victoires", 0)
            horse.setdefault("nb_places", 0)
            horse.setdefault("tx_victoire", 0)
            horse.setdefault("tx_place", 0)
            horse.setdefault("forme_recente", 0)
            horse.setdefault("reduction_km", 0)
            horse.setdefault("gains_annee", 0)
            horse.setdefault("distance", 0)
            horse.setdefault("discipline", "PLAT")
            horse.setdefault("avis_entraineur", "NEUTRE")
            horse.setdefault("oeilleres", "SANS")
            horse.setdefault("deferre", "NON")
            horse.setdefault("nb_partants", len(group))
            horse.setdefault("musique", "")
            horse.setdefault("rang_cote", idx)
            if not horse.get("ratio_cote_field"):
                horse["ratio_cote_field"] = (
                    round(horse.get("odds", 0) / avg_odds, 3) if avg_odds > 0 else 0
                )

    return horses


@app.route("/api/ml_predictions")
@app.route("/turf/api/ml_predictions")
def api_ml_predictions():
    models = load_models()
    if not models:
        return jsonify({"error": "Models not loaded"})

    conn = get_db()
    today = datetime.now().strftime("%Y-%m-%d")
    date_used, horses, course_info = load_ml_horses(conn, today)
    horses = enrich_ml_horses(horses)

    if not horses:
        conn.close()
        return jsonify(
            {
                "date": date_used,
                "predictions": [],
                "message": "No predictions available",
            }
        )

    feature_cols = [
        "age",
        "sexe_enc",
        "nb_courses",
        "nb_victoires",
        "nb_places",
        "tx_victoire",
        "tx_place",
        "forme_recente",
        "reduction_km",
        "gains_annee",
        "cote_directe",
        "distance",
        "nb_partants",
        "discipline_enc",
        "avis_enc",
        "oeilleres_enc",
        "deferre_enc",
        "form_1",
        "form_2",
        "form_3",
        "form_4",
        "form_5",
        "form_avg",
        "win_rate_adj",
        "place_rate_adj",
        "implied_prob",
        "victories_per_race",
        "places_per_race",
        "earnings_per_race",
        "age_win_interact",
        "distance_cat",
        "is_favorite",
        "rang_cote",
        "ratio_cote_field",
    ]

    all_sexes = set(h.get("sexe", "U") or "U" for h in horses)
    all_avis = set(h.get("avis_entraineur", "NEUTRE") or "NEUTRE" for h in horses)
    all_oeilleres = set(h.get("oeilleres", "SANS") or "SANS" for h in horses)
    all_deferre = set(h.get("deferre", "NON") or "NON" for h in horses)
    all_discipline = set(h.get("discipline", "PLAT") or "PLAT" for h in horses)

    le_sexe = LabelEncoder()
    le_sexe.fit(list(all_sexes) + ["U"])
    le_avis = LabelEncoder()
    le_avis.fit(list(all_avis) + ["NEUTRE"])
    le_oeilleres = LabelEncoder()
    le_oeilleres.fit(list(all_oeilleres) + ["SANS"])
    le_deferre = LabelEncoder()
    le_deferre.fit(list(all_deferre) + ["NON"])
    le_discipline = LabelEncoder()
    le_discipline.fit(list(all_discipline) + ["PLAT"])

    predictions = []
    import re

    for horse in horses:
        features = {}
        for col in [
            "age",
            "nb_courses",
            "nb_victoires",
            "nb_places",
            "tx_victoire",
            "tx_place",
            "forme_recente",
            "reduction_km",
            "gains_annee",
            "cote_directe",
            "distance",
            "nb_partants",
            "rang_cote",
            "ratio_cote_field",
        ]:
            features[col] = float(horse.get(col, 0) or 0)

        features["sexe_enc"] = le_sexe.transform([horse.get("sexe", "U") or "U"])[0]
        features["avis_enc"] = le_avis.transform(
            [horse.get("avis_entraineur", "NEUTRE") or "NEUTRE"]
        )[0]
        features["oeilleres_enc"] = le_oeilleres.transform(
            [horse.get("oeilleres", "SANS") or "SANS"]
        )[0]
        features["deferre_enc"] = le_deferre.transform(
            [horse.get("deferre", "NON") or "NON"]
        )[0]
        features["discipline_enc"] = le_discipline.transform(
            [horse.get("discipline", "PLAT") or "PLAT"]
        )[0]

        musique = horse.get("musique", "")
        form_nums = re.findall(r"\d+", str(musique))[:5]
        for i, fn in enumerate(form_nums):
            features[f"form_{i + 1}"] = float(fn)
        for i in range(len(form_nums) + 1, 6):
            features[f"form_{i}"] = 0.0
        features["form_avg"] = sum(features[f"form_{i}"] for i in range(1, 6)) / 5

        features["implied_prob"] = (
            1 / features["cote_directe"] if features["cote_directe"] > 0 else 0
        )
        features["win_rate_adj"] = features["tx_victoire"] * np.log1p(
            features["nb_courses"]
        )
        features["place_rate_adj"] = features["tx_place"] * np.log1p(
            features["nb_courses"]
        )
        features["victories_per_race"] = features["nb_victoires"] / max(
            features["nb_courses"], 1
        )
        features["places_per_race"] = features["nb_places"] / max(
            features["nb_courses"], 1
        )
        features["earnings_per_race"] = features["gains_annee"] / max(
            features["nb_courses"], 1
        )
        features["age_win_interact"] = features["age"] * features["tx_victoire"]
        features["distance_cat"] = (
            2.0
            if 1500 < features["distance"] <= 2000
            else (3.0 if 2000 < features["distance"] <= 2500 else 1.0)
        )
        features["is_favorite"] = 1 if features["cote_directe"] < 5 else 0

        try:
            X = pd.DataFrame([features])[feature_cols]
            X = X.fillna(0)
            prob_top1 = float(models["model_top1"].predict_proba(X)[0][1])
            prob_top3 = float(models["model_top3"].predict_proba(X)[0][1])

            predictions.append(
                {
                    "horse_name": horse["horse_name"],
                    "horse_number": horse["horse_number"],
                    "odds": float(horse["odds"]),
                    "prob_top1": round(prob_top1 * 100, 1),
                    "prob_top3": round(prob_top3 * 100, 1),
                    "ml_score": round((prob_top1 * 0.6 + prob_top3 * 0.4) * 100, 1),
                    "recommendation": "top1"
                    if prob_top1 > 0.15
                    else ("top3" if prob_top3 > 0.35 else "pass"),
                    "is_value_bet": 1
                    if (prob_top3 > 0.35 and float(horse.get("odds", 0)) > 10)
                    else 0,
                    "is_outlier": 1
                    if (
                        float(horse.get("odds", 0)) <= 5
                        and (prob_top1 < 0.1 and prob_top3 < 0.25)
                    )
                    else 0,
                    "num_reunion": horse.get("num_reunion"),
                    "num_course": horse.get("num_course"),
                }
            )
        except Exception as e:
            predictions.append(
                {
                    "horse_name": horse["horse_name"],
                    "horse_number": horse["horse_number"],
                    "odds": float(horse["odds"]),
                    "error": str(e),
                }
            )

    predictions.sort(key=lambda x: x.get("ml_score", 0), reverse=True)

    for pred in predictions:
        course_key = f"{pred.get('num_reunion', 1)}_{pred.get('num_course', 1)}"
        if course_key in course_info:
            cinfo = course_info[course_key]
            pred["race_label"] = (
                f"R{pred.get('num_reunion', 1)}C{pred.get('num_course', 1)}"
            )
            pred["race_name"] = cinfo.get("libelle", "")
            pred["hippodrome"] = cinfo.get("libelle_court", "")
            pred["discipline"] = cinfo.get("discipline", "")
            pred["distance"] = cinfo.get("distance", 0)
            pred["heure"] = cinfo.get("heure_depart_str", "")

    conn.close()

    return jsonify(
        {
            "date": date_used,
            "model_version": "xgboost_v1",
            "predictions": predictions,
            "courses": course_info,
        }
    )


# === VITESSE ===
@app.route("/api/vitesse")
@app.route("/turf/api/vitesse")
def api_vitesse():
    try:
        conn = get_db()
        today = datetime.now().strftime("%Y-%m-%d")
        categories = ["bases", "chances", "outsiders"]
        result = {"date": today, "predictions": {}}

        for category in categories:
            c = conn.execute(
                f"""
                SELECT horse_name, horse_number
                FROM predictions 
                WHERE date=? AND source=? AND horse_name IS NOT NULL
                GROUP BY horse_name
                ORDER BY MIN(prediction_rank)
            """,
                (today, f"canalturf_prono_{category}"),
            )

            horses = []
            for row in c.fetchall():
                horse_name = row[0]
                horse_number = row[1]

                latest = conn.execute(
                    """
                    SELECT odds, odds_prev, jockey, created_at
                    FROM predictions 
                    WHERE date = ? AND horse_name = ? AND source = 'canalturf_partants'
                    ORDER BY created_at DESC LIMIT 1
                """,
                    (today, horse_name),
                ).fetchone()

                pmu = conn.execute(
                    """
                    SELECT forme_recente, tx_victoire, tx_place, nombre_courses
                    FROM pmu_partants 
                    WHERE nom LIKE ? || '%'
                    ORDER BY date_programme DESC
                    LIMIT 1
                """,
                    (horse_name.split()[0],),
                ).fetchone()

                if latest:
                    odds_change = ""
                    if latest[1] and latest[0]:
                        diff = latest[0] - latest[1]
                        if diff < 0:
                            odds_change = " 🔻"
                        elif diff > 0:
                            odds_change = " 🔺"

                    if pmu and pmu[0] is not None:
                        forme_str = f"{pmu[0]:.1f}" if pmu[0] else "N/A"
                        speed_info = {
                            "avg_time_ms": None,
                            "races": pmu[3] or 0,
                            "avg_time_formatted": f"F:{forme_str} | {pmu[1]:.0f}%V/{pmu[2]:.0f}%P",
                            "odds": latest[0],
                            "odds_prev": latest[1],
                            "jockey": latest[2] or "",
                            "forme": pmu[0],
                            "tx_victoire": pmu[1],
                            "tx_place": pmu[2],
                            "source": "predictions+pmu",
                        }
                    else:
                        trend = (
                            f" ({latest[1]:.1f}→{latest[0]:.1f})" if latest[1] else ""
                        )
                        speed_info = {
                            "avg_time_ms": None,
                            "races": 0,
                            "avg_time_formatted": f"🚴 {latest[2][:12] if latest[2] else ''} | {latest[0]:.1f}{odds_change}",
                            "odds": latest[0],
                            "odds_prev": latest[1],
                            "jockey": latest[2] or "",
                            "source": "predictions",
                        }
                else:
                    speed_info = {
                        "avg_time_ms": None,
                        "races": 0,
                        "avg_time_formatted": "—",
                        "source": "none",
                    }

                horses.append(
                    {
                        "horse_name": horse_name,
                        "horse_number": horse_number,
                        "speed_info": speed_info,
                    }
                )

            result["predictions"][category] = horses

        conn.close()
        return jsonify(result)

    except Exception as e:
        return jsonify({"error": str(e), "status": "error"}), 500


# === BACKTEST ===
from datetime import timedelta


def calculate_backtest_data(start_date=None, end_date=None):
    if not start_date:
        start_date = (datetime.now() - timedelta(days=30)).strftime("%Y-%m-%d")
    if not end_date:
        end_date = datetime.now().strftime("%Y-%m-%d")

    conn = get_db()

    query = """
        SELECT 
            r.date,
            r.race_name,
            r.type_pari,
            r.cheval1,
            r.cote,
            r.mise,
            r.resultat
        FROM recommendations r
        WHERE r.resultat IS NOT NULL 
            AND r.resultat != ''
            AND r.date BETWEEN ? AND ?
    """

    try:
        cursor = conn.execute(query, (start_date, end_date))
        rows = cursor.fetchall()
    except sqlite3.OperationalError as e:
        conn.close()
        return {
            "summary": {
                "total_bets": 0,
                "message": "Données backtest indisponibles",
                "error": str(e),
            }
        }

    if not rows:
        conn.close()
        return {"summary": {"total_bets": 0, "message": "Aucune donnée"}}

    stats = {
        "total_bets": len(rows),
        "gagne": 0,
        "perdu": 0,
        "mise_totale": 0,
        "gain_total": 0,
    }
    details = []
    by_type = {}

    for date, race_name, type_pari, cheval1, cote, mise, resultat in rows:
        mise = float(mise or 1)
        cote = float(cote or 1)

        stats["mise_totale"] += mise

        if type_pari not in by_type:
            by_type[type_pari] = {"count": 0, "gagne": 0, "mise": 0, "gain": 0}
        by_type[type_pari]["count"] += 1
        by_type[type_pari]["mise"] += mise

        if resultat == "GAGNE":
            stats["gagne"] += 1
            stats["gain_total"] += mise * cote
            by_type[type_pari]["gagne"] += 1
            by_type[type_pari]["gain"] += mise * cote
        else:
            stats["perdu"] += 1

        details.append(
            {
                "date": date,
                "race_name": (race_name or "")[:30],
                "type_pari": type_pari,
                "cheval": cheval1,
                "cote": cote,
                "mise": mise,
                "resultat": resultat,
                "gain": mise * cote if resultat == "GAGNE" else 0,
            }
        )

    roi = (
        ((stats["gain_total"] - stats["mise_totale"]) / stats["mise_totale"] * 100)
        if stats["mise_totale"] > 0
        else 0
    )
    precision = (
        (stats["gagne"] / stats["total_bets"] * 100) if stats["total_bets"] > 0 else 0
    )

    # Par type
    by_type_results = {}
    for pari_type, data in by_type.items():
        pari_roi = (
            ((data["gain"] - data["mise"]) / data["mise"] * 100)
            if data["mise"] > 0
            else 0
        )
        pari_precision = (
            (data["gagne"] / data["count"] * 100) if data["count"] > 0 else 0
        )
        by_type_results[pari_type] = {
            "count": data["count"],
            "gagne": data["gagne"],
            "mise": round(data["mise"], 2),
            "gain": round(data["gain"], 2),
            "roi": round(pari_roi, 1),
            "precision": round(pari_precision, 1),
        }

    conn.close()

    return {
        "period": {"start": start_date, "end": end_date},
        "summary": {
            "total_bets": stats["total_bets"],
            "gagne": stats["gagne"],
            "perdu": stats["perdu"],
            "precision": round(precision, 1),
            "mise_totale": round(stats["mise_totale"], 2),
            "gain_total": round(stats["gain_total"], 2),
            "roi": round(roi, 1),
        },
        "by_type": by_type_results,
        "details": details[-50:],
    }


@app.route("/api/backtest")
@app.route("/turf/api/backtest")
def api_backtest():
    """Backtest - lit depuis la base de données"""
    start = request.args.get("start")
    end = request.args.get("end")

    if not start:
        start = (datetime.now() - timedelta(days=30)).strftime("%Y-%m-%d")
    if not end:
        end = datetime.now().strftime("%Y-%m-%d")

    conn = get_db()

    # Récupérer les détails depuis bet_results
    cursor = conn.execute(
        """
        SELECT b.date, b.race_name, b.type_pari, 
               b.horse_name, b.horse_number, 
               COALESCE(b.cote, 0) as cote, 
               b.mise, b.resultat, b.gain
        FROM bet_results b
        WHERE b.date BETWEEN ? AND ?
        ORDER BY b.date DESC, b.id DESC
        LIMIT 50
    """,
        (start, end),
    )

    rows = cursor.fetchall()

    # Add race_label to each row
    details = []
    import re

    for r in rows:
        d = dict(r)
        race_label = "R1C1"
        if d.get("race_name") and "R" in str(d["race_name"]):
            match = re.search(r"R(\d+)C(\d+)", str(d["race_name"]))
            if match:
                race_label = f"R{match.group(1)}C{match.group(2)}"
        d["race_label"] = race_label
        details.append(d)

    if not details:
        conn.close()
        return jsonify({"summary": {"total_bets": 0, "message": "Aucune donnée"}})

    # Calculer résumé depuis la DB
    cursor = conn.execute(
        """
        SELECT 
            COUNT(*) as total,
            SUM(CASE WHEN resultat = 'GAGNE' THEN 1 ELSE 0 END) as gagne,
            SUM(mise) as mise,
            SUM(gain) as gain
        FROM bet_results
        WHERE date BETWEEN ? AND ?
    """,
        (start, end),
    )

    row = cursor.fetchone()
    total, gagne, mise, gain = row

    roi = ((gain - mise) / mise * 100) if mise > 0 else 0
    precision = (gagne / total * 100) if total > 0 else 0

    # Par type
    cursor = conn.execute(
        """
        SELECT 
            type_pari,
            COUNT(*) as total,
            SUM(CASE WHEN resultat = 'GAGNE' THEN 1 ELSE 0 END) as gagne,
            SUM(mise) as mise,
            SUM(gain) as gain
        FROM bet_results
        WHERE date BETWEEN ? AND ?
        GROUP BY type_pari
    """,
        (start, end),
    )

    by_type = {}
    for row in cursor.fetchall():
        type_pari, total, gagne, mise, gain = row
        pari_roi = ((gain - mise) / mise * 100) if mise > 0 else 0
        pari_precision = (gagne / total * 100) if total > 0 else 0
        by_type[type_pari] = {
            "count": total,
            "gagne": gagne,
            "mise": round(mise or 0, 2),
            "gain": round(gain or 0, 2),
            "roi": round(pari_roi, 1),
            "precision": round(pari_precision, 1),
        }

    # Details - already prepared above with race_label

    conn.close()

    return jsonify(
        {
            "period": {"start": start, "end": end},
            "summary": {
                "total_bets": total,
                "gagne": gagne,
                "perdu": total - gagne,
                "precision": round(precision, 1),
                "mise_totale": round(mise or 0, 2),
                "gain_total": round(gain or 0, 2),
                "roi": round(roi, 1),
            },
            "by_type": by_type,
            "details": details,
        }
    )


@app.route("/api/stats")
@app.route("/turf/api/stats")
def api_stats():
    """Statistiques quotidiennes - lit depuis la base"""
    days = int(request.args.get("days", 30))
    end_date = datetime.now().strftime("%Y-%m-%d")
    start_date = (datetime.now() - timedelta(days=days)).strftime("%Y-%m-%d")

    conn = get_db()

    # Lire depuis daily_stats
    if not table_exists(conn, "daily_stats"):
        conn.close()
        return jsonify({"daily": [], "period": {"start": start_date, "end": end_date}})
    cursor = conn.execute(
        """
        SELECT date, total_bets, bets_gagne, bets_perdu, mise_totale, gain_total, precision_pct, roi_pct
        FROM daily_stats
        WHERE date BETWEEN ? AND ?
        ORDER BY date DESC
    """,
        (start_date, end_date),
    )

    rows = cursor.fetchall()

    daily = []
    for row in rows:
        daily.append(
            {
                "date": row[0],
                "total_bets": row[1],
                "gagne": row[2],
                "perdu": row[3],
                "precision": row[6],
                "mises": row[4],
                "gains": row[5],
                "roi": row[7],
            }
        )

    conn.close()
    return jsonify({"daily": daily, "period": {"start": start_date, "end": end_date}})


def generate_sql_from_question(question):
    """Generate SQL query from natural language question - keyword fallback first, then LLM"""

    q = question.lower()

    # Keyword-based queries - prioritize common questions
    keyword_patterns = [
        ("taux", "favori"),
        ("taux", "victoire"),
        ("taux", "reussite"),
        ("jockey",),
        ("driver",),
        ("entraineur",),
        ("aujourd",),
        ("hier",),
        ("programme",),
        ("resultat",),
        ("resultats",),
        ("arrivee",),
        ("gagn", "victoire", "vainqueur"),
        ("cote", "evolu"),
        ("cote", "chang"),
        ("cote", "vari"),
        ("roi",),
        ("profit",),
        ("gain",),
        ("distance",),
        ("vincennes",),
        ("performances",),
        ("statistiques",),
        ("historique",),
    ]

    # Check if question matches keyword patterns
    is_keyword_query = any(
        all(kw in q for kw in pattern) for pattern in keyword_patterns
    )

    # Try keyword first for common questions
    if is_keyword_query:
        sql = generate_sql_from_keywords(question, q)
        if sql:
            return sql

    # Try LLM only for complex/niche questions
    sql = generate_sql_with_llm(question)
    if sql:
        return sql

    # Final fallback to keyword
    return generate_sql_from_keywords(question, q)


def generate_sql_from_keywords(question, q):
    """Generate SQL from keywords - extracted from generate_sql_from_question"""

    # Today's races

    # Today's races (or most recent if no today)
    if "aujourd" in q or "aujourd'hui" in q or "ce jour" in q or "programme" in q:
        return """SELECT c.libelle as course, p.nom as cheval, p.cote_direct as cote, p.favoris, c.discipline, p.date_programme
        FROM pmu_partants p
        JOIN pmu_courses c ON p.date_programme = c.date_programme AND p.num_reunion = c.num_reunion AND p.num_course = c.num_course
        WHERE p.date_programme >= date('now', '-1 day')
        ORDER BY p.date_programme DESC, c.num_course, p.cote_direct
        LIMIT 15"""

    # Today's results (arrived horses)
    if "resultat" in q or "résultat" in q or "arrivee" in q:
        return """SELECT c.libelle as course, c.discipline, c.distance,
        p.nom as cheval, p.ordre_arrivee as position, p.cote_direct as cote, p.driver
        FROM pmu_partants p
        JOIN pmu_courses c ON p.date_programme = c.date_programme AND p.num_reunion = c.num_reunion AND p.num_course = c.num_course
        WHERE p.ordre_arrivee > 0 AND p.date_programme >= date('now', '-2 days')
        ORDER BY p.date_programme DESC, c.num_course, p.ordre_arrivee
        LIMIT 20"""

    # Yesterday's results
    if "hier" in q and ("resultat" in q or "résultat" in q):
        return """SELECT c.libelle as course, p.nom as cheval, p.ordre_arrivee as position, p.cote_direct as cote, c.discipline
        FROM pmu_partants p
        JOIN pmu_courses c ON p.date_programme = c.date_programme AND p.num_reunion = c.num_reunion AND p.num_course = c.num_course
        WHERE p.date_programme = date('now', '-1 day') AND p.ordre_arrivee > 0 AND p.ordre_arrivee <= 3
        ORDER BY c.num_course, p.ordre_arrivee
        LIMIT 15"""

    # Winners today
    if "gagn" in q or "victoire" in q or "vainqueur" in q:
        return """SELECT c.libelle as course, p.nom as cheval, p.cote_direct as cote
        FROM pmu_partants p
        JOIN pmu_courses c ON p.date_programme = c.date_programme AND p.num_reunion = c.num_reunion AND p.num_course = c.num_course
        WHERE p.date_programme >= date('now', '-3 days') AND p.ordre_arrivee = 1
        ORDER BY p.date_programme DESC, c.num_course
        LIMIT 10"""

    # Favorites win rate
    if "taux" in q and ("favori" in q or "favoris" in q):
        return """SELECT 
            COUNT(*) as total_races,
            SUM(CASE WHEN ordre_arrivee = 1 THEN 1 ELSE 0 END) as wins,
            ROUND(CAST(SUM(CASE WHEN ordre_arrivee <= 3 THEN 1 ELSE 0 END) AS FLOAT) / NULLIF(SUM(CASE WHEN favoris = 1 THEN 1 ELSE 0 END), 0) * 100, 1) as podium_rate,
            ROUND(CAST(SUM(CASE WHEN ordre_arrivee = 1 THEN 1 ELSE 0 END) AS FLOAT) / NULLIF(SUM(CASE WHEN favoris = 1 THEN 1 ELSE 0 END), 0) * 100, 1) as win_rate
        FROM pmu_partants
        WHERE favoris = 1 AND ordre_arrivee > 0 AND date_programme >= date('now', '-7 days')"""

    # Odds changes
    if "cote" in q and ("evolu" in q or "chang" in q or "vari" in q):
        return """SELECT date_programme, nom as cheval, cote_direct, cote_reference, tendance_cote
        FROM pmu_partants
        WHERE date_programme >= date('now', '-2 days')
        ORDER BY date_programme DESC, ABS(cote_direct - cote_reference) DESC
        LIMIT 10"""

    if "taux" in q and "favori" in q:
        return """SELECT 
            COUNT(*) as total_races,
            SUM(CASE WHEN ordre_arrivee = 1 THEN 1 ELSE 0 END) as wins,
            ROUND(CAST(SUM(CASE WHEN ordre_arrivee <= 3 THEN 1 ELSE 0 END) AS FLOAT) / NULLIF(SUM(CASE WHEN favoris = 1 THEN 1 ELSE 0 END), 0) * 100, 1) as podium_rate,
            ROUND(CAST(SUM(CASE WHEN ordre_arrivee = 1 THEN 1 ELSE 0 END) AS FLOAT) / NULLIF(SUM(CASE WHEN favoris = 1 THEN 1 ELSE 0 END), 0) * 100, 1) as win_rate
        FROM pmu_partants
        WHERE favoris = 1 AND ordre_arrivee > 0 AND date_programme >= date('now', '-7 days')"""

    # ROI calculation
    if "roi" in q or "profit" in q or "gain" in q:
        return """SELECT 
            date_programme,
            COUNT(*) as nb_partants,
            SUM(CASE WHEN ordre_arrivee = 1 THEN (cote_direct - 1) ELSE -1 END) as profit_total,
            ROUND(AVG(CASE WHEN ordre_arrivee = 1 THEN (cote_direct - 1) ELSE -1 END), 2) as profit_moyen,
            SUM(CASE WHEN ordre_arrivee = 1 THEN 1 ELSE 0 END) as nb_victoires,
            ROUND(CAST(SUM(CASE WHEN ordre_arrivee = 1 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) * 100, 1) as tx_victoire
        FROM pmu_partants
        WHERE ordre_arrivee > 0 AND date_programme >= date('now', '-30 days')
        GROUP BY date_programme
        ORDER BY date_programme DESC
        LIMIT 15"""

    if "jockey" in q or "driver" in q or "driver" in q:
        return """SELECT 
            driver as jockey,
            COUNT(*) as total_races,
            SUM(CASE WHEN ordre_arrivee = 1 THEN 1 ELSE 0 END) as victories,
            ROUND(CAST(SUM(CASE WHEN ordre_arrivee <= 3 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) * 100, 1) as podium_rate
        FROM pmu_partants
        WHERE driver IS NOT NULL AND driver != '' AND ordre_arrivee > 0
        GROUP BY driver
        ORDER BY victories DESC
        LIMIT 10"""

    if "entraineur" in q:
        return """SELECT 
            entraineur,
            COUNT(*) as total_races,
            SUM(CASE WHEN ordre_arrivee = 1 THEN 1 ELSE 0 END) as victories,
            ROUND(CAST(SUM(CASE WHEN ordre_arrivee <= 3 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) * 100, 1) as podium_rate
        FROM pmu_partants
        WHERE entraineur IS NOT NULL AND entraineur != '' AND ordre_arrivee > 0
        GROUP BY entraineur
        ORDER BY victories DESC
        LIMIT 10"""

    if "distance" in q and "vincennes" in q:
        return """SELECT 
            c.discipline,
            COUNT(*) as races,
            ROUND(AVG(c.distance), 0) as avg_distance
        FROM pmu_courses c
        WHERE c.libelle LIKE '%Vincennes%'
        GROUP BY c.discipline
        ORDER BY races DESC"""

    # Horse name extraction - try various patterns
    if any(
        kw in q
        for kw in ["performances", "statistiques", "historique", "cheval", "stats"]
    ):
        import re

        # Match sequences of uppercase words (horse names are all caps in DB)
        # Looking for patterns like "TORTISAMBERT" or "TORTISAMBERT Z"
        match = re.search(r"\b([A-Z]{4,}(?:\s+[A-Z]{4,})?)\b", question)
        if match:
            horse = match.group(1).strip()
            return (
                """SELECT 
                nom as cheval,
                COUNT(*) as total_races,
                SUM(CASE WHEN ordre_arrivee = 1 THEN 1 ELSE 0 END) as victoires,
                ROUND(AVG(cote_direct), 1) as cote_moyenne,
                ROUND(CAST(SUM(CASE WHEN ordre_arrivee <= 3 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) * 100, 1) as taux_place
            FROM pmu_partants
            WHERE nom LIKE ? AND ordre_arrivee > 0
            GROUP BY nom""",
                (f"%{horse}%",),
            )

    # Default: recent races with results
    return """SELECT c.libelle as course, c.discipline, p.nom as cheval, 
        p.cote_direct as cote, p.favoris, p.ordre_arrivee as position
        FROM pmu_partants p
        JOIN pmu_courses c ON p.date_programme = c.date_programme 
            AND p.num_reunion = c.num_reunion AND p.num_course = c.num_course
        WHERE p.date_programme >= date('now', '-3 days')
        ORDER BY p.date_programme DESC, c.num_course, p.ordre_arrivee
        LIMIT 15"""


@app.route("/turf/api/execute-sql", methods=["POST", "OPTIONS"])
@app.route("/api/execute-sql", methods=["POST", "OPTIONS"])
@app.route("/turf/api/ask", methods=["GET"])
@app.route("/api/ask", methods=["GET"])
def execute_sql():
    """Endpoint pour exécuter des requêtes SQL ou recevoir des questions via GET"""
    if request.method == "OPTIONS":
        return "", 200

    # Pour GET, générer la requête SQL depuis la question
    if request.method == "GET":
        question = request.args.get("question", "")
        result = generate_sql_from_question(question)
        if not result:
            return jsonify({"error": "No question provided"}), 400
        if isinstance(result, tuple):
            query, sql_params = result
        else:
            query, sql_params = result, ()
    else:
        data = request.get_json()
        query = data.get("query", "") if data else ""
        sql_params = ()

    if not query:
        return jsonify({"error": "No query provided"}), 400

    # Limiter les requêtes SELECT uniquement pour la sécurité
    query_upper = query.strip().upper()
    if not query_upper.startswith("SELECT"):
        return jsonify({"error": "Only SELECT queries allowed"}), 403

    try:
        conn = sqlite3.connect(DB_PATH)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute(query, sql_params)
        rows = cursor.fetchall()
        results = [dict(row) for row in rows]
        conn.close()
        return jsonify({"results": results, "count": len(results)})
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route("/call-workflow", methods=["POST"])
def call_workflow():
    """Endpoint pour exécuter un workflow n8n via MCP"""
    import requests

    data = request.get_json()
    workflow_id = data.get("workflow_id", "sHVEK4hwyUmAww3F")
    question = data.get("question", "")

    if not question:
        return jsonify({"error": "No question provided"}), 400

    try:
        # Call n8n MCP server
        mcp_url = "https://kolifee.duckdns.org/mcp-server/http"
        mcp_token = os.environ.get("MCP_TOKEN", "n8n_mcp_token_2025")

        response = requests.post(
            mcp_url,
            json={
                "json": {
                    "tool": "execute_workflow",
                    "arguments": {
                        "workflowId": workflow_id,
                        "data": {"question": question},
                    },
                }
            },
            headers={
                "Content-Type": "application/json",
                "Authorization": f"Bearer {mcp_token}",
            },
            timeout=60,
        )

        if response.status_code == 200:
            result = response.json()
            return jsonify(
                {"response": result.get("text", result.get("output", str(result)))}
            )
        else:
            return jsonify(
                {"error": f"MCP error: {response.status_code}"}
            ), response.status_code

    except Exception as e:
        return jsonify({"error": str(e)}), 500


# === NOUVEAUX ENDPOINTS LLM & ANALYTICS ===

try:
    from prompts_llm import (
        get_system_prompt,
        get_sql_prompt,
        get_keyword_sql,
        get_suggestions_prompt,
    )
    from llm_cache import get_llm_cache, get_sql_cache

    HAS_LLM_MODULES = True
except ImportError:
    HAS_LLM_MODULES = False

try:
    from analytics_reports import (
        get_daily_report,
        get_weekly_report,
        get_monthly_report,
        format_report_markdown,
    )

    HAS_ANALYTICS = True
except ImportError:
    HAS_ANALYTICS = False


@app.route("/turf/api/report/daily", methods=["GET"])
@app.route("/api/report/daily", methods=["GET"])
def daily_report():
    """Rapport quotidien de performance"""
    if not HAS_ANALYTICS:
        return jsonify({"error": "analytics_reports module not available"}), 500

    date = request.args.get("date")
    report = get_daily_report(date)
    return jsonify(report)


@app.route("/turf/api/report/weekly", methods=["GET"])
@app.route("/api/report/weekly", methods=["GET"])
def weekly_report():
    """Rapport hebdomadaire de performance"""
    if not HAS_ANALYTICS:
        return jsonify({"error": "analytics_reports module not available"}), 500

    start_date = request.args.get("start")
    end_date = request.args.get("end")
    report = get_weekly_report(start_date, end_date)
    return jsonify(report)


@app.route("/turf/api/report/monthly", methods=["GET"])
@app.route("/api/report/monthly", methods=["GET"])
def monthly_report():
    """Rapport mensuel de performance"""
    if not HAS_ANALYTICS:
        return jsonify({"error": "analytics_reports module not available"}), 500

    year = request.args.get("year", type=int)
    month = request.args.get("month", type=int)
    report = get_monthly_report(year, month)
    return jsonify(report)


@app.route("/turf/api/report/markdown/<report_type>", methods=["GET"])
def report_markdown(report_type):
    """Retourne le rapport en format Markdown"""
    if not HAS_ANALYTICS:
        return jsonify({"error": "analytics_reports module not available"}), 500

    if report_type == "daily":
        date = request.args.get("date")
        report = get_daily_report(date)
        markdown = format_report_markdown(report, "daily")
    elif report_type == "weekly":
        start = request.args.get("start")
        end = request.args.get("end")
        report = get_weekly_report(start, end)
        markdown = format_report_markdown(report, "weekly")
    elif report_type == "monthly":
        year = request.args.get("year", type=int)
        month = request.args.get("month", type=int)
        report = get_monthly_report(year, month)
        markdown = format_report_markdown(report, "monthly")
    else:
        return jsonify({"error": "Invalid report type"}), 400

    return jsonify({"markdown": markdown, "type": report_type})


@app.route("/turf/api/suggestions", methods=["GET"])
@app.route("/api/suggestions", methods=["GET"])
def suggestions():
    """Retourne des suggestions de questions basées sur les données"""
    if not HAS_LLM_MODULES:
        return jsonify(
            {
                "suggestions": [
                    "Quel est mon taux de réussite cette semaine?",
                    "Liste les 5 meilleurs jockeys",
                    "Performances du cheval TORTISAMBERT",
                    "Évolution des cotes",
                    "Résultats d'hier",
                ]
            }
        )

    conn = get_db()
    c = conn.cursor()

    suggestions = []

    try:
        c.execute(
            "SELECT COUNT(*) as cnt FROM pmu_partants WHERE date_programme >= date('now', '-7 days')"
        )
        recent = c.fetchone()["cnt"]

        if recent > 0:
            suggestions = [
                "Quel est mon taux de réussite cette semaine?",
                "Liste les 5 meilleurs jockeys",
                "Quel est le ROI du mois?",
                "Résultats d'hier",
                "Programme du jour",
            ]
        else:
            suggestions = [
                "Derniers gagnants",
                "Meilleurs entraîneurs",
                "Performances à Vincennes",
                "Évolution des cotes",
            ]

    except:
        suggestions = [
            "Quel est le taux de réussite des favoris?",
            "Liste les meilleurs jockeys",
            "Résultats d'hier",
        ]
    finally:
        conn.close()

    return jsonify({"suggestions": suggestions})


@app.route("/turf/api/ask-enhanced", methods=["GET", "POST"])
def ask_enhanced():
    """Version améliorée du endpoint /ask avec cache et fallback keywords"""
    if not HAS_LLM_MODULES:
        return jsonify({"error": "prompts_llm module not available"}), 500

    if request.method == "GET":
        question = request.args.get("question", "")
    else:
        data = request.get_json()
        question = data.get("question", "") if data else ""

    if not question:
        return jsonify({"error": "No question provided"}), 400

    sql_cache = get_sql_cache()

    cached_sql = sql_cache.get_sql(question)
    if cached_sql:
        query = cached_sql
    else:
        query = get_keyword_sql(question)

        if not query and LLM_API_KEY:
            try:
                import litellm

                litellm.drop_params = True

                prompt = get_sql_prompt(question)
                response = litellm.completion(
                    model=f"openrouter/{LLM_MODEL}",
                    messages=[
                        {"role": "system", "content": get_system_prompt()},
                        {"role": "user", "content": prompt},
                    ],
                    api_key=LLM_API_KEY,
                    max_tokens=300,
                )

                sql = response.choices[0].message.content.strip()
                if sql.upper().startswith("SELECT"):
                    query = sql
                    sql_cache.set_sql(question, sql, success=True)

            except Exception as e:
                print(f"LLM error: {e}")

    if not query:
        return jsonify({"error": "Could not generate SQL for this question"}), 400

    if not query.upper().startswith("SELECT"):
        return jsonify({"error": "Invalid query generated"}), 400

    try:
        conn = get_db()
        c = conn.cursor()
        c.execute(query)
        rows = c.fetchall()
        results = [dict(row) for row in rows]
        conn.close()

        return jsonify(
            {
                "question": question,
                "sql": query,
                "results": results,
                "count": len(results),
            }
        )

    except Exception as e:
        return jsonify({"error": str(e), "sql": query}), 500


@app.route("/turf/api/scores", methods=["POST"])
@require_auth
def save_race_scores():
    data = request.json
    if not data:
        return jsonify({"error": "No data provided"}), 400

    conn = get_db()
    try:
        conn.execute(
            """
            INSERT OR REPLACE INTO race_scores 
            (date, race_name, race_time, hippodrome, top5_cotes, top5_cotes_hits, 
             top5_bc, top5_bc_hits, top5_bo, top5_bo_hits, results_top5)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """,
            (
                data.get("date"),
                data.get("race_name"),
                data.get("race_time"),
                data.get("hippodrome"),
                json.dumps(data.get("top5_cotes", [])),
                data.get("top5_cotes_hits", 0),
                json.dumps(data.get("top5_bc", [])),
                data.get("top5_bc_hits", 0),
                json.dumps(data.get("top5_bo", [])),
                data.get("top5_bo_hits", 0),
                json.dumps(data.get("results_top5", [])),
            ),
        )
        conn.commit()
        return jsonify({"status": "ok", "message": "Scores saved"})
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        conn.close()


@app.route("/turf/api/scores/history", methods=["GET"])
@require_auth
def get_scores_history():
    limit = request.args.get("limit", 30)
    conn = get_db()
    try:
        if not table_exists(conn, "race_scores"):
            return jsonify({"results": [], "count": 0})
        c = conn.execute(
            """
            SELECT date, race_name, race_time, hippodrome,
                   top5_cotes, top5_cotes_hits, 
                   top5_bc, top5_bc_hits,
                   top5_bo, top5_bo_hits,
                   results_top5
            FROM race_scores
            ORDER BY date DESC, race_time DESC
            LIMIT ?
        """,
            (limit,),
        )
        rows = c.fetchall()
        results = []
        for r in rows:
            d = dict(r)
            d["top5_cotes"] = json.loads(d["top5_cotes"]) if d.get("top5_cotes") else []
            d["top5_bc"] = json.loads(d["top5_bc"]) if d.get("top5_bc") else []
            d["top5_bo"] = json.loads(d["top5_bo"]) if d.get("top5_bo") else []
            d["results_top5"] = (
                json.loads(d["results_top5"]) if d.get("results_top5") else []
            )
            results.append(d)
        return jsonify({"results": results, "count": len(results)})
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        conn.close()


# ============================================================
#  PARIS / ROI - Gestion des paris manuels
# ============================================================


@app.route("/api/parisroi", methods=["GET"])
@app.route("/turf/api/parisroi", methods=["GET"])
@require_auth
def api_parisroi():
    """Dashboard complet Paris/ROI"""
    conn = get_db()
    today = datetime.now().strftime("%Y-%m-%d")
    try:
        # Resume global depuis table paris
        stats = conn.execute("""
            SELECT COUNT(*) as total_bets,
                   SUM(CASE WHEN statut='GAGNE' THEN 1 ELSE 0 END) as gagne,
                   SUM(CASE WHEN statut='PERDU' THEN 1 ELSE 0 END) as perdu,
                   COALESCE(SUM(mise),0) as mise_totale,
                   COALESCE(SUM(gain),0) as gain_total
            FROM paris
            WHERE statut != 'EN_ATTENTE'
        """).fetchone()
        stats = (
            dict(stats)
            if stats
            else {
                "total_bets": 0,
                "gagne": 0,
                "perdu": 0,
                "mise_totale": 0,
                "gain_total": 0,
            }
        )
        mise = stats["mise_totale"] or 0
        gain = stats["gain_total"] or 0
        roi = ((gain - mise) / mise * 100) if mise > 0 else 0
        precision = (
            (stats["gagne"] / stats["total_bets"] * 100)
            if stats["total_bets"] > 0
            else 0
        )
        stats["roi"] = round(roi, 1)
        stats["precision"] = round(precision, 1)

        # Paris du jour (EN_ATTENTE)
        paris_jour = conn.execute("""
            SELECT * FROM paris
            WHERE statut = 'EN_ATTENTE'
            ORDER BY created_at DESC
        """).fetchall()
        paris_jour = [dict(r) for r in paris_jour]

        # Historique des 30 derniers paris
        historique = conn.execute("""
            SELECT * FROM paris
            WHERE statut != 'EN_ATTENTE'
            ORDER BY created_at DESC
            LIMIT 30
        """).fetchall()
        historique = [dict(r) for r in historique]

        # Stats par type
        by_type = conn.execute("""
            SELECT type_pari,
                   COUNT(*) as total,
                   SUM(CASE WHEN statut='GAGNE' THEN 1 ELSE 0 END) as gagne,
                   SUM(mise) as mise,
                   SUM(gain) as gain
            FROM paris
            WHERE statut != 'EN_ATTENTE'
            GROUP BY type_pari
        """).fetchall()
        by_type = {
            r["type_pari"]: {
                "total": r["total"],
                "gagne": r["gagne"],
                "mise": round(r["mise"] or 0, 2),
                "gain": round(r["gain"] or 0, 2),
                "roi": round(
                    ((r["gain"] or 0) - (r["mise"] or 0)) / (r["mise"] or 1) * 100, 1
                ),
            }
            for r in by_type
        }

        # ROI curve (30 derniers jours)
        roi_curve = conn.execute("""
            SELECT date, roi_pct, gain_total, mise_totale
            FROM daily_stats
            ORDER BY date DESC
            LIMIT 30
        """).fetchall()
        roi_curve = [dict(r) for r in roi_curve]

        return jsonify(
            {
                "summary": stats,
                "paris_jour": paris_jour,
                "historique": historique,
                "by_type": by_type,
                "roi_curve": roi_curve,
            }
        )
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        conn.close()


@app.route("/api/paris", methods=["GET"])
@app.route("/turf/api/paris", methods=["GET"])
@require_auth
def api_get_paris():
    """Liste des paris avec filtres"""
    conn = get_db()
    date = request.args.get("date")
    statut = request.args.get("statut")
    try:
        query = "SELECT * FROM paris WHERE 1=1"
        params = []
        if date:
            query += " AND date_pari = ?"
            params.append(date)
        if statut:
            query += " AND statut = ?"
            params.append(statut)
        query += " ORDER BY created_at DESC"
        rows = conn.execute(query, params).fetchall()
        return jsonify({"paris": [dict(r) for r in rows], "count": len(rows)})
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        conn.close()


@app.route("/api/paris", methods=["POST"])
@app.route("/turf/api/paris", methods=["POST"])
@require_auth
def api_add_pari():
    """Ajouter un nouveau pari"""
    data = request.json
    if not data:
        return jsonify({"error": "Donnees requises"}), 400
    conn = get_db()
    try:
        today = datetime.now().strftime("%Y-%m-%d")
        chevaux = data.get("chevaux", "")
        c = conn.execute(
            """
            INSERT INTO paris (date_pari, date_course, race_name, race_label, hippodrome,
                               type_pari, chevaux, cheval1, numero1, cheval2, numero2,
                               cheval3, numero3, cote, mise, statut, commentaire, source_reco)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'EN_ATTENTE', ?, ?)
        """,
            (
                today,
                data.get("date_course", today),
                data.get("race_name", ""),
                data.get("race_label", ""),
                data.get("hippodrome", ""),
                data.get("type_pari", "tierce"),
                chevaux,
                data.get("cheval1", ""),
                data.get("numero1"),
                data.get("cheval2", ""),
                data.get("numero2"),
                data.get("cheval3", ""),
                data.get("numero3"),
                data.get("cote", 0),
                data.get("mise", 1.0),
                data.get("commentaire", ""),
                data.get("source_reco", "manuel"),
            ),
        )
        conn.commit()
        return jsonify({"status": "ok", "id": c.lastrowid})
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        conn.close()


@app.route("/api/paris/<int:pari_id>", methods=["PUT"])
@app.route("/turf/api/paris/<int:pari_id>", methods=["PUT"])
@require_auth
def api_update_pari(pari_id):
    """Mettre a jour un pari (resultat/gain)"""
    data = request.json
    if not data:
        return jsonify({"error": "Donnees requises"}), 400
    conn = get_db()
    try:
        statut = data.get("statut", "EN_ATTENTE")
        gain = data.get("gain", 0)
        commentaire = data.get("commentaire", "")
        conn.execute(
            """
            UPDATE paris SET statut = ?, gain = ?, commentaire = ?
            WHERE id = ?
        """,
            (statut, gain, commentaire, pari_id),
        )
        conn.commit()
        # Mettre a jour bet_results en cascade
        pari = conn.execute("SELECT * FROM paris WHERE id = ?", (pari_id,)).fetchone()
        if pari and statut != "EN_ATTENTE":
            conn.execute(
                """
                INSERT INTO bet_results (date, race_name, type_pari, horse_name, horse_number,
                                         cote, mise, resultat, gain)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
                (
                    pari["date_pari"],
                    pari["race_name"],
                    pari["type_pari"],
                    pari["cheval1"],
                    pari["numero1"],
                    pari["cote"],
                    pari["mise"],
                    statut,
                    gain,
                ),
            )
            conn.commit()
        return jsonify({"status": "ok"})
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        conn.close()


@app.route("/api/paris/<int:pari_id>", methods=["DELETE"])
@app.route("/turf/api/paris/<int:pari_id>", methods=["DELETE"])
@require_auth
def api_delete_pari(pari_id):
    """Supprimer un pari"""
    conn = get_db()
    try:
        conn.execute(
            "DELETE FROM paris WHERE id = ? AND statut = 'EN_ATTENTE'", (pari_id,)
        )
        conn.commit()
        return jsonify({"status": "ok"})
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        conn.close()


@app.route("/api/paris/auto-settle", methods=["POST"])
@app.route("/turf/api/paris/auto-settle", methods=["POST"])
@require_auth
def api_auto_settle():
    """Regler automatiquement les paris du jour via resultats PMU"""
    conn = get_db()
    today = datetime.now().strftime("%Y-%m-%d")
    try:
        paris = conn.execute(
            """
            SELECT * FROM paris
            WHERE date_course = ? AND statut = 'EN_ATTENTE'
        """,
            (today,),
        ).fetchall()
        settled = 0
        for pari in paris:
            p = dict(pari)
            chevaux_paris = [p.get("numero1"), p.get("numero2"), p.get("numero3")]
            chevaux_paris = [c for c in chevaux_paris if c]
            if not chevaux_paris:
                continue
            # Recuperer resultats
            results = conn.execute(
                """
                SELECT nom, ordre_arrivee FROM pmu_partants
                WHERE date_programme = ? AND ordre_arrivee > 0
                ORDER BY ordre_arrivee ASC
            """,
                (today,),
            ).fetchall()
            top3 = [
                r["ordre_arrivee"] for r in results if r["ordre_arrivee"] in [1, 2, 3]
            ]
            if not top3:
                continue
            # Verifier si les numeros paris correspondent
            # Simplifie: on verifie si tous les numeros sont dans le top3
            is_gagne = all(n in top3 for n in chevaux_paris)
            statut = "GAGNE" if is_gagne else "PERDU"
            gain = 0
            if is_gagne and p.get("cote"):
                gain = p["mise"] * p["cote"]
            conn.execute(
                """
                UPDATE paris SET statut = ?, gain = ? WHERE id = ?
            """,
                (statut, gain, p["id"]),
            )
            settled += 1
        conn.commit()
        return jsonify({"status": "ok", "settled": settled})
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        conn.close()


# === EMAIL SENDING (Resend) ===


@app.route("/api/send-email", methods=["POST", "OPTIONS"])
@app.route("/turf/api/send-email", methods=["POST", "OPTIONS"])
def send_email():
    """Envoyer un email via Resend API"""
    if request.method == "OPTIONS":
        return "", 200

    if not RESEND_API_KEY:
        return jsonify({"error": "RESEND_API key not configured in environment"}), 500

    data = request.get_json()
    if not data:
        return jsonify({"error": "JSON body required"}), 400

    to = data.get("to")
    subject = data.get("subject")
    html = data.get("html")
    text = data.get("text")
    from_addr = data.get("from", "H3R7Tech <onboarding@resend.dev>")

    if not to or not subject:
        return jsonify({"error": "Missing required fields: to, subject"}), 400
    if not html and not text:
        return jsonify({"error": "Missing email body: provide html or text"}), 400

    try:
        payload = {
            "from": from_addr,
            "to": [to] if isinstance(to, str) else to,
            "subject": subject,
        }
        if html:
            payload["html"] = html
        if text:
            payload["text"] = text

        resp = requests.post(
            "https://api.resend.com/emails",
            headers={
                "Authorization": f"Bearer {RESEND_API_KEY}",
                "Content-Type": "application/json",
            },
            json=payload,
            timeout=15,
        )

        if resp.status_code in (200, 201):
            result = resp.json()
            return jsonify(
                {
                    "success": True,
                    "id": result.get("id"),
                    "message": "Email sent successfully",
                }
            )
        else:
            return jsonify(
                {"error": f"Resend API error: {resp.status_code}", "details": resp.text}
            ), resp.status_code

    except Exception as e:
        return jsonify({"error": str(e)}), 500


# === BRAVE SEARCH ===


@app.route("/api/brave-search", methods=["GET", "POST", "OPTIONS"])
@app.route("/turf/api/brave-search", methods=["GET", "POST", "OPTIONS"])
def brave_search():
    """Recherche web via Brave Search API"""
    if request.method == "OPTIONS":
        return "", 200

    if not BRAVE_SEARCH_API_KEY:
        return jsonify(
            {"error": "BRAVE_SEARCH_API key not configured in environment"}
        ), 500

    if request.method == "GET":
        q = request.args.get("q", "")
        count = int(request.args.get("count", 10))
        offset = int(request.args.get("offset", 0))
        search_type = request.args.get("type", "web")
    else:
        data = request.get_json() or {}
        q = data.get("q", data.get("query", ""))
        count = int(data.get("count", 10))
        offset = int(data.get("offset", 0))
        search_type = data.get("type", "web")

    if not q:
        return jsonify({"error": "Missing query parameter: q"}), 400

    count = min(count, 20)  # Limit to 20 results max

    try:
        # Choose endpoint based on type
        if search_type == "news":
            url = "https://api.search.brave.com/res/v1/news/search"
        else:
            url = "https://api.search.brave.com/res/v1/web/search"

        resp = requests.get(
            url,
            headers={
                "X-Subscription-Token": BRAVE_SEARCH_API_KEY,
                "Accept": "application/json",
            },
            params={
                "q": q,
                "count": count,
                "offset": offset,
                "search_lang": "fr",
            },
            timeout=10,
        )

        if resp.status_code == 200:
            result = resp.json()
            # Normalize response
            if search_type == "news":
                items = result.get("results", [])
                normalized = [
                    {
                        "title": r.get("title", ""),
                        "url": r.get("url", ""),
                        "description": r.get("description", ""),
                        "age": r.get("age", ""),
                        "source": r.get("source", {}).get("title", ""),
                    }
                    for r in items
                ]
            else:
                items = result.get("web", {}).get("results", [])
                normalized = [
                    {
                        "title": r.get("title", ""),
                        "url": r.get("url", ""),
                        "description": r.get("description", ""),
                        "age": r.get("age", ""),
                        "language": r.get("language", ""),
                    }
                    for r in items
                ]

            return jsonify(
                {
                    "query": q,
                    "type": search_type,
                    "count": len(normalized),
                    "results": normalized,
                }
            )
        else:
            return jsonify(
                {
                    "error": f"Brave Search API error: {resp.status_code}",
                    "details": resp.text,
                }
            ), resp.status_code

    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route("/turf/api/predictions_analysis", methods=["GET"])
def api_predictions_analysis():
    """Analyse des predictions vs resultats reels"""
    from datetime import datetime, timedelta

    days = int(request.args.get("days", 30))
    end_date = datetime.now().strftime("%Y-%m-%d")
    start_date = (datetime.now() - timedelta(days=days)).strftime("%Y-%m-%d")

    conn = get_db()
    cursor = conn.cursor()

    stats = {
        "canalturf": {
            "total": 0,
            "top1_pct": 0,
            "top3_pct": 0,
            "top5_pct": 0,
            "ze2_pct": 0,
        },
        "scoring": {
            "total": 0,
            "top1_pct": 0,
            "top3_pct": 0,
            "top5_pct": 0,
            "ze2_pct": 0,
        },
    }

    for source in ["canalturf", "scoring"]:
        pred_table = "predictions" if source == "canalturf" else "scoring"
        pred_col = "predicted_1" if source == "canalturf" else "horse_number"
        try:
            cursor.execute(
                f"""
                SELECT c.libelle, c.ordre_arrivee, c.numero, p.{pred_col}
                FROM pmu_courses c
                LEFT JOIN {pred_table} p ON c.date_programme = p.date_programme
                    AND c.num_course = p.num_course
                WHERE c.date_course BETWEEN ? AND ?
                    AND c.ordre_arrivee IS NOT NULL
                ORDER BY c.date_course DESC
                """,
                (start_date, end_date),
            )
            races = {}
            for row in cursor.fetchall():
                race, ordre, numero, pred = row
                if race not in races:
                    races[race] = {"actual": [], "predicted": []}
                if ordre and ordre > 0:
                    races[race]["actual"].append(str(numero))
                if pred:
                    races[race]["predicted"].append(str(pred))

            top1_hit = top3_hit = 0
            total = len(races)
            for race, data in races.items():
                actual = set(data["actual"][:3])
                pred_top1 = data["predicted"][0] if data["predicted"] else None
                actual_top1 = data["actual"][0] if data["actual"] else None
                if pred_top1 and actual_top1 and pred_top1 == actual_top1:
                    top1_hit += 1
                if len(set(data["predicted"][:3]) & actual) >= 1:
                    top3_hit += 1

            if total > 0:
                stats[source]["total"] = total
                stats[source]["top1_pct"] = round(top1_hit / total * 100, 1)
                stats[source]["top3_pct"] = round(top3_hit / total * 100, 1)
        except Exception as e:
            print(f"Erreur {source}: {e}")

    conn.close()
    return jsonify({"stats": stats, "period": {"start": start_date, "end": end_date}})


# ─────────────────────────────────────────────────────────────────────────────
# /api/v1/predictions  — Ensemble model endpoint (Sprint 6-7 ML Upgrade)
# ─────────────────────────────────────────────────────────────────────────────
_predict_v2 = None


def _load_predict_v2():
    """Lazy import of predict_v2 module (ensemble model)."""
    global _predict_v2
    if _predict_v2 is None:
        try:
            import importlib.util, sys

            spec = importlib.util.spec_from_file_location(
                "predict_v2", "/home/h3r7/turf_saas/predict_v2.py"
            )
            mod = importlib.util.module_from_spec(spec)
            spec.loader.exec_module(mod)
            _predict_v2 = mod
        except Exception as e:
            import logging

            logging.error(f"[v1/predictions] predict_v2 import failed: {e}")
    return _predict_v2


@app.route("/api/v1/predictions", methods=["GET"])
@app.route("/turf/api/v1/predictions", methods=["GET"])
def api_v1_predictions():
    """
    Ensemble ML predictions using XGBoost + LightGBM + MLP (Optuna-tuned).
    Query params:
      - date: YYYY-MM-DD (default: today / latest available)
      - reunion: int (default: all)
      - course: int (default: all)
    """
    import time as _time

    t0 = _time.perf_counter()

    mod = _load_predict_v2()
    if mod is None:
        # Graceful fallback: redirect to legacy ml_predictions
        return jsonify(
            {
                "error": "Ensemble model not available yet",
                "fallback": "/api/ml_predictions",
                "message": "Model is still training. Use /api/ml_predictions for legacy XGBoost predictions.",
            }
        ), 503

    ensemble = mod.load_ensemble()
    if ensemble is None:
        return jsonify(
            {
                "error": "Ensemble model file not found",
                "model_path": str(mod.ENSEMBLE_PATH),
                "message": "Run train_ensemble.py to generate the model.",
                "fallback": "/api/ml_predictions",
            }
        ), 503

    date_param = request.args.get("date", None)
    reunion_param = request.args.get("reunion", None)
    course_param = request.args.get("course", None)

    conn = sqlite3.connect("/home/h3r7/turf_saas/turf.db")
    conn.row_factory = sqlite3.Row

    # Determine date to use
    if date_param:
        date_used = date_param
    else:
        row = conn.execute(
            "SELECT MAX(date_programme) as d FROM pmu_partants"
        ).fetchone()
        date_used = (
            row["d"] if row and row["d"] else datetime.now().strftime("%Y-%m-%d")
        )

    # Build query
    where_clauses = ["p.date_programme = ?"]
    params = [date_used]
    if reunion_param:
        where_clauses.append("p.num_reunion = ?")
        params.append(int(reunion_param))
    if course_param:
        where_clauses.append("p.num_course = ?")
        params.append(int(course_param))

    query = f"""
        SELECT p.*, c.distance, c.discipline, c.specialite,
               c.nb_declares_partants, c.montant_prix, c.penetrometre_intitule,
               c.libelle as course_libelle, c.libelle_court as hippodrome,
               c.heure_depart_str, c.parcours
        FROM pmu_partants p
        LEFT JOIN pmu_courses c ON p.date_programme = c.date_programme
            AND p.num_reunion = c.num_reunion AND p.num_course = c.num_course
        WHERE {" AND ".join(where_clauses)}
        ORDER BY p.num_reunion, p.num_course, p.num_pmu
    """
    rows = conn.execute(query, params).fetchall()
    conn.close()

    if not rows:
        return jsonify(
            {
                "date": date_used,
                "model_version": mod.get_model_version(),
                "predictions": [],
                "message": f"No partants found for date {date_used}",
            }
        )

    # Convert to list of dicts
    partants = [dict(r) for r in rows]

    # Run ensemble prediction
    preds = mod.predict_top3(partants, model=ensemble)

    # Group by race
    races = {}
    for pred in preds:
        key = f"R{pred.get('num_reunion', 0)}C{pred.get('num_course', 0)}"
        if key not in races:
            # Find race metadata from partants
            for p in partants:
                if p.get("num_reunion") == pred.get("num_reunion") and p.get(
                    "num_course"
                ) == pred.get("num_course"):
                    races[key] = {
                        "reunion": pred.get("num_reunion"),
                        "course": pred.get("num_course"),
                        "label": key,
                        "race_name": p.get("course_libelle", ""),
                        "hippodrome": p.get("hippodrome", ""),
                        "heure": p.get("heure_depart_str", ""),
                        "discipline": p.get("discipline", ""),
                        "distance": p.get("distance", 0),
                        "horses": [],
                    }
                    break
        if key in races:
            races[key]["horses"].append(pred)

    latency_ms = (_time.perf_counter() - t0) * 1000

    return jsonify(
        {
            "date": date_used,
            "model_version": mod.get_model_version(),
            "latency_ms": round(latency_ms, 1),
            "total_horses": len(preds),
            "races": list(races.values()),
        }
    )


@app.route("/api/v1/model/invalidate-cache", methods=["POST"])
@app.route("/turf/api/v1/model/invalidate-cache", methods=["POST"])
def api_v1_invalidate_cache():
    """Force reload of ensemble model on next prediction call."""
    mod = _load_predict_v2()
    if mod:
        mod.invalidate_model_cache()
        return jsonify({"status": "ok", "message": "Model cache invalidated"})
    return jsonify({"status": "error", "message": "predict_v2 module not loaded"}), 500


@app.route("/api/v1/model/status", methods=["GET"])
@app.route("/turf/api/v1/model/status", methods=["GET"])
def api_v1_model_status():
    """Return ensemble model status and version."""
    import os as _os
    from pathlib import Path as _Path

    ensemble_path = _Path("/home/h3r7/turf_saas/models/ensemble_top3.pkl")
    benchmark_path = _Path("/home/h3r7/turf_saas/models/benchmark_report.json")

    status = {
        "ensemble_available": ensemble_path.exists(),
        "ensemble_path": str(ensemble_path),
    }
    if ensemble_path.exists():
        mtime = _os.path.getmtime(str(ensemble_path))
        status["last_trained"] = datetime.fromtimestamp(mtime).isoformat()

    if benchmark_path.exists():
        try:
            with open(benchmark_path) as f:
                import json as _json

                report = _json.load(f)
            status["benchmark"] = {
                "baseline_precision_at3": report.get("baseline", {}).get(
                    "precision_at3"
                ),
                "ensemble_precision_at3": report.get("ensemble", {}).get(
                    "precision_at3"
                ),
                "delta": report.get("delta_precision_at3"),
                "deployed": report.get("deploy"),
                "run_date": report.get("run_date"),
            }
        except Exception:
            pass

    mod = _load_predict_v2()
    if mod and ensemble_path.exists():
        status["model_version"] = mod.get_model_version()

    return jsonify(status)


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=8790, debug=False)
