turf_saas/update_paris_results.py

#!/usr/bin/env python3
"""
Update paris results - Intègre les résultats PMU dans la table paris
Usage: python3 update_paris_results.py [--date YYYY-MM-DD]
"""

import sqlite3
import sys
from datetime import datetime, timedelta

DB_PATH = "/home/h3r7/turf_scraper/turf.db"


def create_paris_from_recommendations(conn, date: str):
    """Crée les paris depuis les recommandations du jour"""
    cursor = conn.cursor()

    # Récupère les recommandations du jour
    cursor.execute(
        """
        SELECT id, race_name, type_pari, cheval1, numero1, cote, confiance
        FROM recommendations 
        WHERE date = ?
        """,
        (date,),
    )

    recs = cursor.fetchall()

    for rec in recs:
        rec_id, race_name, type_pari, cheval1, numero1, cote, confiance = rec

        # Extrait le code course du race_name
        race_label = race_name.split(" - ")[0] if " - " in race_name else race_name

        # Vérifie si le pari existe déjà
        cursor.execute(
            """
            SELECT id FROM paris 
            WHERE date_course = ? AND race_label = ? AND type_pari = ? AND numero1 = ?
            """,
            (date, race_label, type_pari, numero1),
        )

        if cursor.fetchone():
            continue

        # Crée le pari
        cursor.execute(
            """
            INSERT INTO paris 
            (date_pari, date_course, race_name, race_label, type_pari, cheval1, numero1, cote, mise, statut, source_reco, model_source)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'EN_ATTENTE', 'agent_turf', 'scoring_v1')
            """,
            (date, date, race_name, race_label, type_pari, cheval1, numero1, cote, 2.0),
        )

    conn.commit()
    return len(recs)


def get_course_results(conn, date: str):
    """Récupère les résultats des courses pour une date"""
    cursor = conn.cursor()

    cursor.execute(
        """
        SELECT 
            p.date_programme,
            p.num_reunion,
            p.num_course,
            r.hippodrome_court,
            c.libelle,
            p.num_pmu,
            p.nom,
            p.ordre_arrivee
        FROM pmu_partants p
        JOIN pmu_courses c ON c.date_programme = p.date_programme 
            AND c.num_reunion = p.num_reunion 
            AND c.num_course = p.num_course
        JOIN pmu_reunions r ON r.date_programme = p.date_programme 
            AND r.num_reunion = p.num_reunion
        WHERE p.date_programme = ? 
            AND p.ordre_arrivee > 0
        ORDER BY p.num_reunion, p.num_course, p.ordre_arrivee
        """,
        (date,),
    )

    return cursor.fetchall()


def update_paris_status(conn, date: str):
    """Met à jour le statut des paris pour une date"""
    cursor = conn.cursor()

    # Récupère les paris en attente pour cette date
    cursor.execute(
        """
        SELECT id, race_label, type_pari, numero1, numero2, numero3
        FROM paris 
        WHERE date_course = ? AND statut = 'EN_ATTENTE'
        """,
        (date,),
    )

    paris_en_attente = cursor.fetchall()

    if not paris_en_attente:
        return 0

    results = get_course_results(conn, date)

    if not results:
        return 0

    # Construit un dict des résultats par nom de course
    results_by_course = {}
    for row in results:
        # Utilise le nom de la course comme clé
        key = row[4]  # c.libelle
        if key not in results_by_course:
            results_by_course[key] = []
        if row[7] <= 4:  # TOP 4
            results_by_course[key].append(
                {"numero": row[5], "nom": row[6], "place": row[7]}
            )

    updated = 0

    for pari in paris_en_attente:
        pari_id, race_label, type_pari, num1, num2, num3 = pari

        # Trouve la course correspondante par nom
        course_key = None
        for key in results_by_course.keys():
            if key and race_label:
                if key in race_label or race_label in key:
                    course_key = key
                    break

        if not course_key:
            continue

        podium = results_by_course[course_key]
        podium_nums = [p["numero"] for p in podium]

        # Vérifie le résultat selon le type de pari
        statut = "PERDU"
        gain = 0.0

        if type_pari == "simple_gagnant":
            if num1 == podium_nums[0] if podium_nums else False:
                statut = "GAGNE"
                gain = 1.0

        elif type_pari == "simple_place":
            if num1 in podium_nums[:3] if len(podium_nums) >= 3 else False:
                statut = "GAGNE"
                gain = 1.0

        elif type_pari in ["tierce", "quarte", "quinte"]:
            nums_pari = [n for n in [num1, num2, num3] if n]
            nums_podium = podium_nums[: len(nums_pari)]

            if set(nums_pari) == set(nums_podium):
                statut = "GAGNE"
                gain = 1.0

        elif type_pari.startswith("top"):
            # TOP 1, TOP 2, TOP 3, TOP 4
            rang = int(type_pari.replace("top", ""))
            if num1 in podium_nums[:rang] if len(podium_nums) >= rang else False:
                statut = "GAGNE"
                gain = 1.0

        # Met à jour le pari
        cursor.execute(
            """
            UPDATE paris 
            SET statut = ?, gain = ?, commentaire = ?
            WHERE id = ?
            """,
            (
                statut,
                gain,
                f"Résultat intégré automatiquement - {datetime.now().strftime('%Y-%m-%d %H:%M')}",
                pari_id,
            ),
        )

        updated += 1

    conn.commit()
    return updated


def main():
    date = (
        sys.argv[2]
        if len(sys.argv) > 2 and sys.argv[1] == "--date"
        else datetime.now().strftime("%Y-%m-%d")
    )

    print(f"Processing paris for {date}...")

    conn = sqlite3.connect(DB_PATH)

    # 1. Crée les paris depuis les recommandations
    created = create_paris_from_recommendations(conn, date)
    print(f"Created {created} paris from recommendations")

    # 2. Met à jour les statuts avec les résultats
    updated = update_paris_status(conn, date)
    print(f"Updated {updated} paris with results")

    conn.close()


if __name__ == "__main__":
    main()