turf_saas/populate_analytics.py

#!/usr/bin/env python3
"""
Populate Analytics Tables
Remplit les tables de statistiques depuis les données existantes
"""

import sqlite3
import os
from datetime import datetime, timedelta

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


def get_db():
    return sqlite3.connect(DB_PATH)


def populate_bet_results():
    """Remplit bet_results depuis recommendations"""
    conn = get_db()
    c = conn.cursor()
    
    # Vérifier si déjà rempli
    c.execute("SELECT COUNT(*) FROM bet_results")
    if c.fetchone()[0] > 0:
        print("✅ bet_results déjà rempli")
        conn.close()
        return
    
    # Récupérer depuis recommendations
    c.execute("""
        SELECT date, race_name, type_pari, cheval1, numero1, cote, mise, resultat
        FROM recommendations
        WHERE resultat IS NOT NULL AND resultat != ''
        ORDER BY date
    """)
    
    rows = c.fetchall()
    
    for row in rows:
        date, race_name, type_pari, cheval1, numero1, cote, mise, resultat = row
        
        gain = 0
        if resultat == 'GAGNE':
            gain = float(cote or 1) * float(mise or 1)
        
        c2 = conn.cursor()
        c2.execute("""
            INSERT INTO bet_results (date, race_name, type_pari, horse_name, horse_number, cote, mise, resultat, gain, model_source)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (date, race_name, type_pari, cheval1, numero1, cote, mise, resultat, gain, source_reco or 'manual'))
    
    conn.commit()
    print(f"✅ {len(rows)} paris ajoutés dans bet_results")
    conn.close()


def populate_daily_stats():
    """Remplit daily_stats depuis bet_results"""
    conn = get_db()
    c = conn.cursor()
    
    # Vérifier si déjà rempli
    c.execute("SELECT COUNT(*) FROM daily_stats")
    if c.fetchone()[0] > 0:
        print("✅ daily_stats déjà rempli")
        conn.close()
        return
    
    # Grouper par date
    c.execute("""
        SELECT 
            date,
            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
        GROUP BY date
        ORDER BY date
    """)
    
    rows = c.fetchall()
    
    for row in rows:
        date, total, gagne, mise, gain = row
        
        precision = (gagne / total * 100) if total > 0 else 0
        roi = ((gain - mise) / mise * 100) if mise > 0 else 0
        
        c2 = conn.cursor()
        c2.execute("""
            INSERT INTO daily_stats (date, total_bets, bets_gagne, bets_perdu, mise_totale, gain_total, precision_pct, roi_pct)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """, (date, total, gagne, total - gagne, mise, gain, precision, roi))
    
    conn.commit()
    print(f"✅ {len(rows)} jours ajoutés dans daily_stats")
    conn.close()


def populate_stats_by_type():
    """Remplit stats_by_type depuis bet_results"""
    conn = get_db()
    c = conn.cursor()
    
    # Vérifier si déjà rempli
    c.execute("SELECT COUNT(*) FROM stats_by_type")
    if c.fetchone()[0] > 0:
        print("✅ stats_by_type déjà rempli")
        conn.close()
        return
    
    # Grouper par date et type
    c.execute("""
        SELECT 
            date,
            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
        GROUP BY date, type_pari
        ORDER BY date, type_pari
    """)
    
    rows = c.fetchall()
    
    for row in rows:
        date, type_pari, total, gagne, mise, gain = row
        
        precision = (gagne / total * 100) if total > 0 else 0
        roi = ((gain - mise) / mise * 100) if mise > 0 else 0
        
        c2 = conn.cursor()
        c2.execute("""
            INSERT INTO stats_by_type (date, type_pari, total_bets, gagne, perdu, mise_totale, gain_total, precision_pct, roi_pct)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (date, type_pari, total, gagne, total - gagne, mise, gain, precision, roi))
    
    conn.commit()
    print(f"✅ {len(rows)} lignes ajoutées dans stats_by_type")
    conn.close()


def update_daily_stats():
    """Met à jour les statistiques quotidiennes (à appeler après chaque nouveau résultat)"""
    conn = get_db()
    c = conn.cursor()
    
    # Récupérer les dates sans stats ou avec nouveaux paris
    c.execute("""
        SELECT DISTINCT date FROM bet_results
        WHERE date NOT IN (SELECT date FROM daily_stats)
        ORDER BY date
    """)
    
    dates = [r[0] for r in c.fetchall()]
    
    for date in dates:
        c.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 = ?
        """, (date,))
        
        row = c.fetchone()
        if row:
            total, gagne, mise, gain = row
            precision = (gagne / total * 100) if total > 0 else 0
            roi = ((gain - mise) / mise * 100) if mise > 0 else 0
            
            c.execute("""
                INSERT OR REPLACE INTO daily_stats (date, total_bets, bets_gagne, bets_perdu, mise_totale, gain_total, precision_pct, roi_pct)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """, (date, total, gagne, total - gagne, mise, gain, precision, roi))
    
    conn.commit()
    print(f"✅ {len(dates)} dates mises à jour dans daily_stats")
    conn.close()


def get_stats_from_db(start_date=None, end_date=None):
    """Récupère les stats depuis la base"""
    conn = get_db()
    c = conn.cursor()
    
    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')
    
    # Daily stats
    c.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))
    
    daily = []
    for row in c.fetchall():
        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]
        })
    
    # Summary
    c.execute("""
        SELECT 
            SUM(total_bets) as total,
            SUM(bets_gagne) as gagne,
            SUM(mise_totale) as mise,
            SUM(gain_total) as gain
        FROM daily_stats
        WHERE date BETWEEN ? AND ?
    """, (start_date, end_date))
    
    row = c.fetchone()
    total, gagne, mise, gain = row
    
    summary = {
        'total_bets': total or 0,
        'gagne': gagne or 0,
        'perdu': (total or 0) - (gagne or 0),
        'precision': (gagne / total * 100) if total and total > 0 else 0,
        'mise_totale': mise or 0,
        'gain_total': gain or 0,
        'roi': ((gain - mise) / mise * 100) if mise and mise > 0 else 0
    }
    
    # By type
    c.execute("""
        SELECT 
            type_pari,
            SUM(total_bets) as total,
            SUM(gagne) as gagne,
            SUM(mise_totale) as mise,
            SUM(gain_total) as gain
        FROM stats_by_type
        WHERE date BETWEEN ? AND ?
        GROUP BY type_pari
    """, (start_date, end_date))
    
    by_type = {}
    for row in c.fetchall():
        type_pari, total, gagne, mise, gain = row
        roi = ((gain - mise) / mise * 100) if mise > 0 else 0
        precision = (gagne / total * 100) if total > 0 else 0
        
        by_type[type_pari] = {
            'count': total,
            'gagne': gagne,
            'mise': mise,
            'gain': gain,
            'roi': roi,
            'precision': precision
        }
    
    conn.close()
    
    return {
        'period': {'start': start_date, 'end': end_date},
        'summary': summary,
        'daily': daily,
        'by_type': by_type
    }


if __name__ == "__main__":
    print("="*50)
    print("Population des tables analytics")
    print("="*50)
    
    populate_bet_results()
    populate_daily_stats()
    populate_stats_by_type()
    
    print("\n✅ Tables analytics populées!")
    
    # Test
    stats = get_stats_from_db()
    print(f"\nRésumé: {stats['summary']}")