#!/usr/bin/env python3
"""
Update Recommendation Results - Compare recommendations with actual results
This script fills in the 'resultat' field in the recommendations table.
"""

import sqlite3
import os
from datetime import datetime

DB_PATH = os.environ.get("DB_PATH", "/home/h3r7/turf_scraper/turf.db")


def get_race_results(conn, date, race_name):
    """Get all race results for a specific date and race."""
    c = conn.cursor()
    c.execute('''
        SELECT horse_name, position
        FROM results
        WHERE date = ? AND race_name LIKE ?
        ORDER BY position
    ''', (date, f"%{race_name[:20]}%"))
    results = c.fetchall()
    
    if not results:
        return None
    
    return {
        'first': results[0][0] if len(results) > 0 else None,
        'second': results[1][0] if len(results) > 1 else None,
        'third': results[2][0] if len(results) > 2 else None,
        'top3': [r[0] for r in results[:3]],
        'top5': [r[0] for r in results[:5]],
        'all': {r[0]: r[1] for r in results}
    }


def evaluate_bet(type_pari, cheval1, cheval2, race_results):
    """Evaluate if a bet won based on type_pari and race results."""
    if not race_results:
        return None
    
    cheval1 = cheval1.strip().upper() if cheval1 else None
    cheval2 = cheval2.strip().upper() if cheval2 else None
    
    if type_pari == 'simple_gagnant':
        if race_results['first'] and cheval1 == race_results['first'].upper():
            return 'GAGNE'
        return 'PERDU'
    
    elif type_pari == 'simple_place':
        if cheval1 and cheval1 in [c.upper() for c in race_results['top3']]:
            return 'GAGNE'
        return 'PERDU'
    
    elif type_pari == 'couple_gagnant':
        if not cheval1 or not cheval2:
            return None
        top2 = [race_results['first'], race_results['second']]
        top2_upper = [c.upper() for c in top2 if c]
        if cheval1 in top2_upper and cheval2 in top2_upper:
            return 'GAGNE'
        return 'PERDU'
    
    elif type_pari == 'couple_place':
        if not cheval1 or not cheval2:
            return None
        if cheval1 in [c.upper() for c in race_results['top3']] and \
           cheval2 in [c.upper() for c in race_results['top3']]:
            return 'GAGNE'
        return 'PERDU'
    
    return None


def evaluate_ze5_bet(type_pari, cheval1, race_results):
    """
    Evaluate ZE5 bet (ZE5 B4/B3, ZE5 Conservateur, ZE5 Audacieux)
    Returns: 'B5', 'B4', 'B3', 'PERDU'
    """
    if not race_results or not cheval1:
        return None
    
    # Parse combo: "2 KROONER-7 JE TE CHERCHE-9 JOLIVERT..."
    horses = [h.strip().split(' ', 1)[-1].upper() for h in cheval1.split('-') if h.strip()]
    top5 = [c.upper() for c in race_results['top5']]
    
    # Count how many of our horses are in top 5
    matches = sum(1 for h in horses if h in top5)
    
    if matches >= 5:
        return 'B5'  # 5/5 - Ordre or Désordre
    elif matches >= 4:
        return 'B4'  # 4/5
    elif matches >= 3:
        return 'B3'  # 3/5
    else:
        return 'PERDU'


def evaluate_ze4_bet(type_pari, cheval1, race_results):
    """
    Evaluate ZE4 bet (Multi 4)
    Returns: 'G4', 'G3', 'G2', 'PERDU'
    """
    if not race_results or not cheval1:
        return None
    
    # Parse combo: "7 JE TE CHERCHE-15 IALTO-9 JOLIVERT-5 JENTIL"
    horses = [h.strip().split(' ', 1)[-1].upper() for h in cheval1.split('-') if h.strip()]
    top4 = [c.upper() for c in race_results['top5'][:4]]
    
    # Count how many of our horses are in top 4
    matches = sum(1 for h in horses if h in top4)
    
    if matches >= 4:
        return 'G4'  # 4/4 - Gagnant!
    elif matches >= 3:
        return 'G3'  # 3/4
    elif matches >= 2:
        return 'G2'  # 2/4
    else:
        return 'PERDU'


def get_ze5_results(conn, date, race_name=None):
    """Get race results from pmu_partants for ZE5 evaluation"""
    # Try to find Marseille/Borely race (course 1, reunion 1)
    c = conn.execute("""
        SELECT pp.nom as horse_name, pp.ordre_arrivee as position
        FROM pmu_partants pp
        JOIN pmu_reunions pr ON pr.date_programme=pp.date_programme AND pr.num_reunion=pp.num_reunion
        WHERE pp.date_programme=? AND pp.ordre_arrivee > 0 AND pp.ordre_arrivee <= 5
            AND pr.pays_code='FRA' AND pp.num_course=1
            AND (pr.hippodrome_court LIKE '%BOR%' OR pr.hippodrome_long LIKE '%MARSEILLE%')
        ORDER BY pp.num_reunion, pp.ordre_arrivee
    """, (date,))
    results = c.fetchall()
    
    if not results:
        # Fallback: first French race
        c = conn.execute("""
            SELECT pp.nom as horse_name, pp.ordre_arrivee as position
            FROM pmu_partants pp
            JOIN pmu_reunions pr ON pr.date_programme=pp.date_programme AND pr.num_reunion=pp.num_reunion
            WHERE pp.date_programme=? AND pp.ordre_arrivee > 0 AND pp.ordre_arrivee <= 5
                AND pr.pays_code='FRA' AND pp.num_course=1
            ORDER BY pp.num_reunion, pp.ordre_arrivee
            LIMIT 5
        """, (date,))
        results = c.fetchall()
    
    if not results:
        return None
    
    return {
        'first': results[0][0] if len(results) > 0 else None,
        'second': results[1][0] if len(results) > 1 else None,
        'third': results[2][0] if len(results) > 2 else None,
        'top3': [r[0] for r in results[:3]],
        'top5': [r[0] for r in results[:5]],
    }


def update_recommendations():
    """Main function to update recommendations with results."""
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    
    c.execute('''
        SELECT id, date, race_name, type_pari, cheval1, cheval2, resultat
        FROM recommendations
        WHERE resultat IS NULL OR resultat = ''
        ORDER BY date DESC
    ''')
    recommendations = c.fetchall()
    
    if not recommendations:
        print("No recommendations to update.")
        conn.close()
        return
    
    updated = 0
    skipped = 0
    
    for rec in recommendations:
        rec_id, date, race_name, type_pari, cheval1, cheval2, current_result = rec
        
        # Handle ZE5 and ZE4 bets differently
        if type_pari.startswith('ZE5') or 'ze5' in type_pari.lower():
            race_results = get_ze5_results(conn, date)
            if not race_results:
                skipped += 1
                continue
            new_result = evaluate_ze5_bet(type_pari, cheval1, race_results)
        elif 'ZE4' in type_pari or 'ze4' in type_pari.lower():
            race_results = get_ze5_results(conn, date)
            if not race_results:
                skipped += 1
                continue
            new_result = evaluate_ze4_bet(type_pari, cheval1, race_results)
        else:
            race_results = get_race_results(conn, date, race_name)
            if not race_results:
                print(f"⚠️  No results found for {date} - {race_name}")
                skipped += 1
                continue
            new_result = evaluate_bet(type_pari, cheval1, cheval2, race_results)
        
        if new_result:
            c.execute('''
                UPDATE recommendations
                SET resultat = ?
                WHERE id = ?
            ''', (new_result, rec_id))
            updated += 1
            status = "✅" if new_result in ["GAGNE", "B5", "B4", "B3"] else "❌"
            print(f"{status} {new_result} | {date} | {type_pari:20} | {cheval1[:40]}")
        else:
            skipped += 1
    
    conn.commit()
    
    print(f"\n{'='*50}")
    print(f"Mise à jour terminée:")
    print(f"  - Recommandations mises à jour: {updated}")
    print(f"  - Ignorées (pas de résultats): {skipped}")
    
    stats(conn)
    
    conn.close()


def stats(conn):
    """Display statistics about recommendations."""
    c = conn.cursor()
    
    c.execute('''
        SELECT 
            resultat,
            COUNT(*) as count
        FROM recommendations
        WHERE resultat IS NOT NULL AND resultat != ''
        GROUP BY resultat
    ''')
    
    print(f"\n{'='*50}")
    print("Statistiques des recommandations:")
    
    total = 0
    gagne = 0
    for row in c.fetchall():
        resultat, count = row
        print(f"  - {resultat}: {count}")
        total += count
        if resultat == 'GAGNE':
            gagne = count
    
    if total > 0:
        print(f"\n  ROI: {((gagne / total) * 100) - 100:.1f}%")
        print(f"  Taux de réussite: {(gagne / total) * 100:.1f}%")


if __name__ == "__main__":
    print(f"Starting recommendation results update at {datetime.now()}")
    print(f"Database: {DB_PATH}")
    print("="*50)
    update_recommendations()