| 58 | } |
| 59 | |
| 60 | func (d *DB) initTables() error { |
| 61 | _, err := d.conn.Exec(` |
| 62 | CREATE TABLE IF NOT EXISTS query_history ( |
| 63 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 64 | providers TEXT NOT NULL, |
| 65 | regions TEXT NOT NULL, |
| 66 | categories TEXT NOT NULL, |
| 67 | product_families TEXT NOT NULL, |
| 68 | attributes TEXT NOT NULL DEFAULT '', |
| 69 | prices TEXT NOT NULL DEFAULT '', |
| 70 | result_count INTEGER NOT NULL, |
| 71 | cache_key TEXT NOT NULL DEFAULT '', |
| 72 | created_at TEXT NOT NULL |
| 73 | )`) |
| 74 | if err != nil { |
| 75 | return err |
| 76 | } |
| 77 | // Migrations for older DBs (ignore errors — columns may already exist) |
| 78 | d.conn.Exec("ALTER TABLE query_history ADD COLUMN cache_key TEXT NOT NULL DEFAULT ''") |
| 79 | d.conn.Exec("ALTER TABLE query_history ADD COLUMN attributes TEXT NOT NULL DEFAULT ''") |
| 80 | d.conn.Exec("ALTER TABLE query_history ADD COLUMN prices TEXT NOT NULL DEFAULT ''") |
| 81 | |
| 82 | _, err = d.conn.Exec(` |
| 83 | CREATE TABLE IF NOT EXISTS pricing_cache ( |
| 84 | cache_key TEXT PRIMARY KEY, |
| 85 | response_json TEXT NOT NULL, |
| 86 | created_at TEXT NOT NULL |
| 87 | )`) |
| 88 | if err != nil { |
| 89 | return err |
| 90 | } |
| 91 | d.conn.Exec("CREATE INDEX IF NOT EXISTS idx_cache_created ON pricing_cache(created_at)") |
| 92 | |
| 93 | _, err = d.conn.Exec(` |
| 94 | CREATE TABLE IF NOT EXISTS metadata_cache ( |
| 95 | id INTEGER PRIMARY KEY CHECK (id = 1), |
| 96 | metadata_json TEXT NOT NULL, |
| 97 | created_at TEXT NOT NULL |
| 98 | )`) |
| 99 | if err != nil { |
| 100 | return err |
| 101 | } |
| 102 | |
| 103 | _, err = d.conn.Exec(` |
| 104 | CREATE TABLE IF NOT EXISTS estimates ( |
| 105 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 106 | name TEXT NOT NULL UNIQUE, |
| 107 | estimate_json TEXT NOT NULL, |
| 108 | created_at TEXT NOT NULL, |
| 109 | updated_at TEXT NOT NULL |
| 110 | )`) |
| 111 | return err |
| 112 | } |
| 113 | |
| 114 | // AddHistory records a pricing query in history. |
| 115 | func (d *DB) AddHistory(providers, regions, categories, productFamilies, attributes, prices []string, resultCount int64, cacheKey string) (int64, error) { |