| 33 | } |
| 34 | |
| 35 | function initSchema(db: Database.Database) { |
| 36 | db.exec(` |
| 37 | CREATE TABLE IF NOT EXISTS events ( |
| 38 | id TEXT PRIMARY KEY, |
| 39 | market_id TEXT, |
| 40 | title TEXT, |
| 41 | slug TEXT, |
| 42 | category TEXT, |
| 43 | volume REAL, |
| 44 | volume_24h REAL, |
| 45 | prob REAL, |
| 46 | change REAL, |
| 47 | recent_change REAL, |
| 48 | location TEXT, |
| 49 | lat REAL, |
| 50 | lng REAL, |
| 51 | markets_json TEXT DEFAULT '[]', |
| 52 | created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')), |
| 53 | updated_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')) |
| 54 | ); |
| 55 | |
| 56 | CREATE INDEX IF NOT EXISTS idx_events_category ON events(category); |
| 57 | CREATE INDEX IF NOT EXISTS idx_events_volume_24h ON events(volume_24h DESC); |
| 58 | CREATE INDEX IF NOT EXISTS idx_events_updated_at ON events(updated_at); |
| 59 | CREATE INDEX IF NOT EXISTS idx_events_location ON events(location); |
| 60 | |
| 61 | CREATE TABLE IF NOT EXISTS price_snapshots ( |
| 62 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 63 | event_id TEXT, |
| 64 | prob REAL, |
| 65 | volume_24h REAL, |
| 66 | change REAL, |
| 67 | recorded_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')) |
| 68 | ); |
| 69 | |
| 70 | CREATE INDEX IF NOT EXISTS idx_snapshots_event_time ON price_snapshots(event_id, recorded_at); |
| 71 | |
| 72 | CREATE TABLE IF NOT EXISTS market_snapshots ( |
| 73 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 74 | event_id TEXT, |
| 75 | market_id TEXT, |
| 76 | label TEXT, |
| 77 | prob REAL, |
| 78 | recorded_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')) |
| 79 | ); |
| 80 | |
| 81 | CREATE INDEX IF NOT EXISTS idx_market_snapshots_event_time ON market_snapshots(event_id, recorded_at); |
| 82 | |
| 83 | CREATE TABLE IF NOT EXISTS sync_log ( |
| 84 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 85 | started_at TEXT, |
| 86 | finished_at TEXT, |
| 87 | event_count INTEGER, |
| 88 | status TEXT, |
| 89 | error_msg TEXT |
| 90 | ); |
| 91 | |
| 92 | CREATE TABLE IF NOT EXISTS ai_summaries ( |