118 lines
3.5 KiB
Python
Executable File
118 lines
3.5 KiB
Python
Executable File
#!/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}")
|