#!/usr/bin/env python3
import sqlite3
import json
from datetime import datetime
from flask import Flask, jsonify

app = Flask(__name__)

def get_db():
    return sqlite3.connect('/home/h3r7/turf_scraper/turf.db')

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

@app.route('/api/vitesse')
def api_vitesse():
    try:
        conn = get_db()
        conn.row_factory = dict_factory
        today = datetime.now().strftime('%Y-%m-%d')
        
        predictions = {}
        for category in ['bases', 'chances', 'outsiders']:
            c = conn.execute(f"""
                SELECT horse_name, horse_number
                FROM predictions 
                WHERE date=? AND source=?
                GROUP BY horse_name
                ORDER BY MIN(prediction_rank)
            """, (today, f'canalturf_prono_{category}'))
            predictions[category] = []
            for row in c:
                predictions[category].append({
                    'horse_name': row[0],
                    'horse_number': row[1]
                })
        
        for category, horses in predictions.items():
            for horse in horses:
                horse_name = horse['horse_name']
                
                c = conn.execute("""
                    SELECT AVG(temps_obtenu) as avg_time, COUNT(*) as races
                    FROM historical_data 
                    WHERE horse_name = ? AND temps_obtenu > 0
                """, (horse_name,))
                speed_result = c.fetchone()
                
                if speed_result and speed_result[0] and speed_result[1] > 0:
                    avg_time = speed_result[0]
                    races = speed_result[1]
                    horse['speed_info'] = {
                        'avg_time_ms': round(avg_time, 0),
                        'races': races,
                        'avg_time_formatted': f"{int(avg_time//60000)}:{int((avg_time%60000)//1000):02d}"
                    }
                else:
                    horse['speed_info'] = {'avg_time_ms': None, 'races': 0, 'avg_time_formatted': 'N/A'}
        
        conn.close()
        return jsonify({
            'date': today, 
            'predictions': predictions,
            'status': 'success'
        })
        
    except Exception as e:
        return jsonify({'error': str(e), 'status': 'error'}), 500

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8767, debug=True)