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

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

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

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

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

def score_cheval_v2(p, all_participants, today):
    score = 0
    details = {}
    
    # 1. COTE - Essaye PMU API, sinon DB
    horse_name = p.get('nom', '')
    cote = 0
    
    # Essayer d'abord depuis l'API PMU
    rapport = p.get('dernierRapportDirect', {})
    if rapport:
        cote = rapport.get('rapport', 0)
    if not cote:
        rapport_ref = p.get('dernierRapportReference', {})
        cote = rapport_ref.get('rapport', 0) if rapport_ref else 0
    
    # Fallback: aller chercher dans la DB
    if not cote or cote == 0:
        cote = get_cote_from_db(horse_name, today)
    
    # Si toujours pas de cote, utiliser 99 comme valeur par defaut
    if not cote or cote == 0:
        cote = 99.0
        
    score_cote = max(2, min(10, 20 / (1 + cote * 0.15))) if cote > 0 else 2
    score += score_cote
    details['cote'] = round(cote, 1)
    details['score_cote'] = round(score_cote, 1)
    
    # 2. FORME - AUGMENTE a 30 pts
    musique_stats = parse_musique(p.get('musique', ''))
    forme = musique_stats.get('forme_recente', 99)
    score_forme = 30 if forme <= 1 else 25 if forme <= 2 else 20 if forme <= 3 else 15 if forme <= 5 else 8 if forme <= 8 else 0
    score += score_forme
    details['forme_recente'] = forme
    details['score_forme'] = score_forme
    
    # 3. TAUX VICTOIRE (15 pts)
    nb_courses_total = p.get('nombreCourses', 0)
    nb_victoires_total = p.get('nombreVictoires', 0)
    tx_vic = (nb_victoires_total / nb_courses_total * 100) if nb_courses_total else 0
    score_vic = min(15, tx_vic * 0.5)
    score += score_vic
    details['tx_victoire'] = round(tx_vic, 1)
    details['score_victoire'] = round(score_vic, 1)
    
    # 4. TAUX PLACE (15 pts)
    nb_places_total = p.get('nombrePlaces', 0)
    tx_place = (nb_places_total / nb_courses_total * 100) if nb_courses_total else 0
    score_place = min(15, tx_place * 0.2)
    score += score_place
    details['tx_place'] = round(tx_place, 1)
    details['score_place'] = round(score_place, 1)
    
    # 5. REDUCTION KM (10 pts)
    rk = p.get('reductionKilometrique', 0)
    all_rk = [x.get('reductionKilometrique', 0) for x in all_participants if x.get('reductionKilometrique', 0) > 0]
    if rk > 0 and all_rk:
        score_rk = 10 * (1 - (rk - min(all_rk)) / (max(all_rk) - min(all_rk))) if max(all_rk) > min(all_rk) else 5
    else:
        score_rk = 0
    score += score_rk
    details['rk'] = rk
    details['score_rk'] = round(score_rk, 1)
    
    # 6. TENDANCE (10 pts)
    tendance = musique_stats.get('tendance', 0)
    score_tendance = min(10, max(0, 5 + tendance))
    score += score_tendance
    details['tendance'] = tendance
    details['score_tendance'] = round(score_tendance, 1)
    
    # 7. AVIS ENTRAINEUR (5 pts)
    avis = p.get('avisEntraineur', 'NEUTRE')
    score_avis = {'POSITIF': 5, 'TRES_POSITIF': 5, 'NEUTRE': 2, 'NEGATIF': 0, 'TRES_NEGATIF': 0}.get(avis, 2)
    score += score_avis
    details['avis_entraineur'] = avis
    details['score_avis'] = score_avis
    
    # 8. BONUS OUTSIDER (5 pts)
    bonus_outsider = 5 if forme <= 3 and cote >= 10 else 0
    score += bonus_outsider
    details['bonus_outsider'] = bonus_outsider
    
    # Driver change penalty
    if p.get('driverChange', False):
        score -= 3
        details['driver_change'] = True
    
    details['score_total'] = round(score, 1)
    details['musique'] = p.get('musique', '')
    details['nb_victoires'] = nb_victoires_total
    details['nb_places'] = nb_places_total
    details['nb_courses'] = nb_courses_total
    
    return round(score, 1), details

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

def build_recommendations_v2(scored_horses):
    ranked = sorted(scored_horses, key=lambda x: x['score'], reverse=True)
    if len(ranked) < 4:
        return None
    
    top1, top2, top3, top4 = ranked[0], ranked[1], ranked[2], ranked[3]
    top4_list = ranked[:4]
    
    def confiance(s):
        return "FORTE" if s >= 55 else "BONNE" if s >= 45 else "MOYENNE" if s >= 35 else "FAIBLE"
    
    ze2_combinaisons = get_ze2sur4_combinaisons(top4_list)
    mise_ze2 = len(ze2_combinaisons) * 1.0
    
    return {
        'simple_gagnant': {
            'cheval': top1['nom'], 'numero': top1['numero'], 'cote': top1['details']['cote'],
            'score': top1['score'], 'confiance': confiance(top1['score']),
            'mise_suggeree': 2.0, 'gain_potentiel': round(2.0 * top1['details']['cote'], 2)
        },
        'ze2_sur_4': {
            'top4': [{'nom': h['nom'], 'numero': h['numero']} for h in top4_list],
            'combinaisons': ze2_combinaisons,
            'mise_totale': mise_ze2,
            'nb_combinaisons': len(ze2_combinaisons),
            'confiance': confiance((top1['score'] + top2['score'] + top3['score'] + top4['score']) / 4),
            'explication': 'Jouer les 6 combinaisons de 2 chevaux parmi les 4 premiers'
        },
        'outsider': _find_outsider(ranked),
        'budget_total': 2.0 + mise_ze2,
    }

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

def save_to_db(scored_horses, date_course, hippodrome, libelle):
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute("DELETE FROM scoring WHERE date = ?", (date_course,))
    
    for i, h in enumerate(scored_horses, 1):
        d = h['details']
        cursor.execute("""
            INSERT INTO scoring (date, race_name, horse_number, horse_name, score,
                score_cote, score_forme, score_victoire, score_place, score_rk,
                score_tendance, score_avis, cote, forme_recente, tx_victoire, tx_place,
                avis_entraineur, musique, rang_scoring, scoring_version)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'v2')
        """, (date_course, libelle, h['numero'], h['nom'], h['score'],
               d.get('score_cote', 0), d.get('score_forme', 0), d.get('score_victoire', 0),
               d.get('score_place', 0), d.get('score_rk', 0), d.get('score_tendance', 0),
               d.get('score_avis', 0), d.get('cote', 0), d.get('forme_recente', 0),
               d.get('tx_victoire', 0), d.get('tx_place', 0), d.get('avis_entraineur', ''),
               d.get('musique', ''), i))
    
    conn.commit()
    conn.close()
    print(f"💾 {len(scored_horses)} scores enregistres en BDD pour {date_course}")

def main():
    today = datetime.now().strftime('%Y-%m-%d')
    date_pmu = datetime.now().strftime('%d%m%Y')
    print(f"=== SCORING V2 - ZE2 SUR4 OPTIMISE === {datetime.now().strftime('%d/%m/%Y %H:%M')} ===")
    
    try:
        url = f"https://turfinfo.api.pmu.fr/rest/client/1/programme/{date_pmu}/reunions"
        r = requests.get(url, headers=HEADERS, timeout=15)
        reunions = r.json().get('programme', {}).get('reunions', [])
    except Exception as e:
        print(f"Erreur: {e}")
        return
    
    quinte = None
    for reunion in reunions:
        for course in reunion.get('courses', []):
            paris_types = [p["typePari"] for p in course.get("paris", [])]
            if any("QUINTE" in p for p in paris_types) or "PARIS-TURF" in course.get('libelle', ''):
                quinte = (reunion['numOfficiel'], course['numOrdre'], course.get('libelle', ''),
                          reunion['hippodrome']['libelleCourt'], course.get('heureDepart', 0))
                break
        if quinte:
            break
    
    if not quinte:
        # Fallback: utiliser la premiere reunion francaise avec predictions
        conn = sqlite3.connect(DB_PATH)
        conn.row_factory = sqlite3.Row
        r = conn.execute("""
            SELECT r.num_reunion, r.hippodrome_court, c.num_course, c.libelle
            FROM pmu_courses c
            JOIN pmu_reunions r ON r.date_programme=c.date_programme AND r.num_reunion=c.num_reunion
            WHERE c.date_programme=? AND r.pays_code='FRA' AND c.num_course=1
            AND EXISTS (SELECT 1 FROM predictions p WHERE p.date=? AND p.source='canalturf_partants' 
                       AND p.race_name LIKE '%' || c.libelle || '%')
            ORDER BY c.heure_depart_str ASC LIMIT 1
        """, (today, today)).fetchone()
        conn.close()
        if r:
            quinte = (r['num_reunion'], r['num_course'], r['libelle'], r['hippodrome_court'], 0)
        else:
            print("Aucune course trouvee")
            return
    
    num_r, num_c, libelle, hippodrome, heure_ts = quinte
    heure = datetime.fromtimestamp(heure_ts/1000).strftime('%H:%M') if heure_ts else '13:55'
    print(f"Course: {libelle} - {hippodrome} {heure}")
    
    try:
        url = f"https://turfinfo.api.pmu.fr/rest/client/1/programme/{date_pmu}/R{num_r}/C{num_c}/participants"
        r = requests.get(url, headers=HEADERS, timeout=15)
        participants = [p for p in r.json().get('participants', []) if p.get('statut') == 'PARTANT']
    except Exception as e:
        print(f"Erreur: {e}")
        return
    
    scored_horses = []
    for p in participants:
        score, details = score_cheval_v2(p, participants, today)
        scored_horses.append({'nom': p['nom'], 'numero': p['numPmu'], 'score': score, 'details': details})
    
    ranked = sorted(scored_horses, key=lambda x: x['score'], reverse=True)
    print(f"\n=== TOP 4 ===")
    for i, h in enumerate(ranked[:4], 1):
        d = h['details']
        print(f"{i}. #{h['numero']:>2} {h['nom']:<20} Score:{h['score']:.1f} Cote:{d['cote']:.1f}")
    
    save_to_db(ranked, today, hippodrome, libelle)
    
    reco = build_recommendations_v2(scored_horses)
    if reco:
        print(f"\n=== RECOMMANDATIONS ===")
        sg = reco['simple_gagnant']
        print(f"\n🎯 SIMPLE GAGNANT:")
        print(f"   #{sg['numero']} {sg['cheval']} @ {sg['cote']}/1 (mise {sg['mise_suggeree']}EUR)")
        
        ze2 = reco['ze2_sur_4']
        print(f"\n🎰 ZE 2 SUR 4 (TOP 4: {', '.join([h['nom'] for h in ze2['top4']])}")
        print(f"   Mise totale: {ze2['mise_totale']}EUR ({ze2['nb_combinaisons']} combis x 1EUR)")
        print(f"   Confiance: {ze2['confiance']}")
        print(f"   Combinaisons:")
        for c in ze2['combinaisons']:
            print(f"      {c['numero1']}-{c['cheval1']} + {c['numero2']}-{c['cheval2']}")
        
        print(f"\n💰 BUDGET TOTAL: {reco['budget_total']}EUR")
        print(f"   - Simple Gagnant: 2EUR")
        print(f"   - ZE 2 sur 4: {ze2['mise_totale']}EUR")

if __name__ == "__main__":
    main()