#!/usr/bin/env python3
"""
Turf Database Manager - SQLite
"""
import sqlite3
from datetime import datetime
import os

DB_PATH = "/home/h3r7/turf_saas/turf_saas.db"

def init_db():
    """Initialize database tables"""
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    
    # Predictions table
    c.execute('''
        CREATE TABLE IF NOT EXISTS predictions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT NOT NULL,
            race_name TEXT,
            race_hippodrome TEXT,
            race_time TEXT,
            horse_number INTEGER,
            horse_name TEXT,
            odds REAL,
            prediction_rank INTEGER,
            source TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # Results table
    c.execute('''
        CREATE TABLE IF NOT EXISTS results (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT NOT NULL,
            race_name TEXT,
            race_hippodrome TEXT,
            position INTEGER,
            horse_name TEXT,
            odds REAL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # Performance tracking
    c.execute('''
        CREATE TABLE IF NOT EXISTS performance (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            prediction_date TEXT,
            race_date TEXT,
            horse_name TEXT,
            predicted_rank INTEGER,
            actual_position INTEGER,
            hit BOOLEAN,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    conn.commit()
    conn.close()
    print(f"✅ Database initialized: {DB_PATH}")

def add_prediction(date, race_name, race_hippodrome, race_time, horse_number, horse_name, odds, prediction_rank, source):
    """Add a prediction"""
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('''
        INSERT INTO predictions (date, race_name, race_hippodrome, race_time, horse_number, horse_name, odds, prediction_rank, source)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', (date, race_name, race_hippodrome, race_time, horse_number, horse_name, odds, prediction_rank, source))
    conn.commit()
    conn.close()

def add_result(date, race_name, race_hippodrome, position, horse_name, odds):
    """Add a race result"""
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('''
        INSERT INTO results (date, race_name, race_hippodrome, position, horse_name, odds)
        VALUES (?, ?, ?, ?, ?, ?)
    ''', (date, race_name, race_hippodrome, position, horse_name, odds))
    conn.commit()
    conn.close()

def get_predictions(date=None):
    """Get predictions"""
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    if date:
        c.execute('SELECT * FROM predictions WHERE date = ? ORDER BY prediction_rank', (date,))
    else:
        c.execute('SELECT * FROM predictions ORDER BY date DESC, prediction_rank')
    results = c.fetchall()
    conn.close()
    return results

def get_performance():
    """Get performance stats"""
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('''
        SELECT 
            COUNT(*) as total,
            SUM(CASE WHEN hit = 1 THEN 1 ELSE 0 END) as hits,
            ROUND(CAST(SUM(CASE WHEN hit = 1 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) * 100, 1) as hit_rate
        FROM performance
    ''')
    result = c.fetchone()
    conn.close()
    return result

if __name__ == "__main__":
    init_db()
    print("\n📊 Database ready!")
    print(f"   Path: {DB_PATH}")