import sqlite3
from datetime import datetime
conn = sqlite3.connect('/home/h3r7/turf_scraper/turf.db')
c = conn.cursor()

today = '2026-02-24'
now = datetime.now().strftime('%H:%M')

# Current odds from scraper
new_odds = {
    7: 4.0,
    1: 7.3,
    3: 6.7,
    8: 18.0,
    11: 19.0,
    15: 13.0,
    16: 30.0,
    14: 18.0
}

# Get old odds and save as prev, then update with new
for num, new_odd in new_odds.items():
    c.execute('SELECT odds FROM predictions WHERE date = ? AND horse_number = ?', (today, num))
    row = c.fetchone()
    old_odd = row[0] if row and row[0] else None
    
    if old_odd and old_odd != new_odd:
        c.execute('UPDATE predictions SET odds_prev = ?, odds = ?, odds_time = ? WHERE date = ? AND horse_number = ?',
                  (old_odd, new_odd, now, today, num))
    elif old_odd is None:
        c.execute('UPDATE predictions SET odds = ?, odds_time = ? WHERE date = ? AND horse_number = ?',
                  (new_odd, now, today, num))

conn.commit()
print("Odds updated with trend!")

c.execute('SELECT horse_number, horse_name, odds_prev, odds, odds_time FROM predictions WHERE date = ? ORDER BY prediction_rank', (today,))
for r in c.fetchall():
    trend = ''
    if r[2] and r[3]:
        if r[3] < r[2]:
            trend = '🔴'  # Down = favorite
        elif r[3] > r[2]:
            trend = '🟢'  # Up = underdog
    print(f"  {r[0]} - {r[1]}: {r[2] if r[2] else '-'} -> {r[3]}/1 {trend} ({r[4]})")

conn.close()