1 import type { Database } from 'bun:sqlite';
2 3 export const SCHEMA_VERSION = 9;
4 5 export function applySchema(db: Database) {
6 db.run('PRAGMA journal_mode = WAL');
7 db.run('PRAGMA foreign_keys = ON');
8 9 const v = (db.query('PRAGMA user_version').get() as { user_version: number }).user_version;
10 if (v < 1) {
11 db.run('DROP TABLE IF EXISTS orders');
12 db.run('DROP TABLE IF EXISTS sessions');
13 db.run('DROP TABLE IF EXISTS products');
14 const hasUsers = db.query(`SELECT name FROM sqlite_master WHERE type='table' AND name='users'`).get();
15 if (hasUsers) {
16 const cols = db.query('PRAGMA table_info(users)').all() as { name: string }[];
17 if (!cols.some(c => c.name === 'is_admin')) {
18 db.run('ALTER TABLE users ADD COLUMN is_admin INTEGER NOT NULL DEFAULT 0');
19 }
20 }
21 }
22 23 db.run(`CREATE TABLE IF NOT EXISTS products (
24 id INTEGER PRIMARY KEY AUTOINCREMENT,
25 name TEXT NOT NULL,
26 name_en TEXT NOT NULL DEFAULT '',
27 slug TEXT NOT NULL UNIQUE,
28 description TEXT NOT NULL DEFAULT '',
29 description_en TEXT NOT NULL DEFAULT '',
30 price_cents INTEGER NOT NULL,
31 unit_size INTEGER NOT NULL DEFAULT 100,
32 unit TEXT NOT NULL DEFAULT 'g',
33 images TEXT NOT NULL DEFAULT '[]',
34 category TEXT NOT NULL DEFAULT 'bilki',
35 stock_count INTEGER NOT NULL DEFAULT 0,
36 harvest_year INTEGER,
37 usage TEXT NOT NULL DEFAULT '',
38 usage_en TEXT NOT NULL DEFAULT '',
39 ord INTEGER NOT NULL DEFAULT 0,
40 created_at TEXT NOT NULL DEFAULT (datetime('now'))
41 )`);
42 43 if (v < 3) {
44 const cols = db.query('PRAGMA table_info(products)').all() as { name: string }[];
45 if (!cols.some(c => c.name === 'unit_size')) db.run('ALTER TABLE products ADD COLUMN unit_size INTEGER NOT NULL DEFAULT 100');
46 if (!cols.some(c => c.name === 'stock_count')) {
47 db.run('ALTER TABLE products ADD COLUMN stock_count INTEGER NOT NULL DEFAULT 0');
48 }
49 if (!cols.some(c => c.name === 'harvest_year')) db.run('ALTER TABLE products ADD COLUMN harvest_year INTEGER');
50 if (!cols.some(c => c.name === 'images')) {
51 if (cols.some(c => c.name === 'image')) {
52 db.run(`UPDATE products SET images = '[\"' || image || '\"]' WHERE image IS NOT NULL AND image != ''`);
53 }
54 db.run('ALTER TABLE products ADD COLUMN images TEXT NOT NULL DEFAULT \'[]\'');
55 }
56 db.run("UPDATE products SET unit = 'g', unit_size = 100 WHERE unit = 'kg'");
57 }
58 59 if (v < 4) {
60 const cols = db.query('PRAGMA table_info(products)').all() as { name: string }[];
61 if (!cols.some(c => c.name === 'ord')) {
62 db.run('ALTER TABLE products ADD COLUMN ord INTEGER NOT NULL DEFAULT 0');
63 const rows = db.query('SELECT id FROM products ORDER BY id').all() as { id: number }[];
64 rows.forEach((r, i) => db.query('UPDATE products SET ord = ? WHERE id = ?').run(i, r.id));
65 }
66 }
67 68 db.run(`CREATE TABLE IF NOT EXISTS users (
69 id TEXT PRIMARY KEY,
70 email TEXT UNIQUE,
71 password_hash TEXT,
72 nostr_pubkey TEXT UNIQUE,
73 name TEXT,
74 phone TEXT,
75 is_admin INTEGER NOT NULL DEFAULT 0,
76 delivery_prefs TEXT,
77 created_at TEXT NOT NULL DEFAULT (datetime('now'))
78 )`);
79 80 if (v < 2) {
81 const cols = db.query('PRAGMA table_info(users)').all() as { name: string }[];
82 if (!cols.some(c => c.name === 'delivery_prefs')) db.run('ALTER TABLE users ADD COLUMN delivery_prefs TEXT');
83 if (!cols.some(c => c.name === 'phone')) db.run('ALTER TABLE users ADD COLUMN phone TEXT');
84 }
85 86 db.run(`CREATE TABLE IF NOT EXISTS orders (
87 id TEXT PRIMARY KEY,
88 view_token TEXT NOT NULL,
89 user_id TEXT,
90 items TEXT NOT NULL,
91 subtotal_cents INTEGER NOT NULL,
92 shipping_cents INTEGER NOT NULL DEFAULT 0,
93 total_cents INTEGER NOT NULL,
94 status TEXT NOT NULL DEFAULT 'pending',
95 payment_method TEXT NOT NULL,
96 payment_status TEXT NOT NULL DEFAULT 'pending',
97 payment_id TEXT,
98 courier TEXT,
99 shipping_method TEXT,
100 shipping_address TEXT,
101 customer_name TEXT NOT NULL,
102 customer_email TEXT NOT NULL,
103 customer_phone TEXT,
104 notes TEXT,
105 created_at TEXT NOT NULL DEFAULT (datetime('now')),
106 updated_at TEXT NOT NULL DEFAULT (datetime('now')),
107 FOREIGN KEY (user_id) REFERENCES users(id)
108 )`);
109 110 db.run(`CREATE TABLE IF NOT EXISTS sessions (
111 token TEXT PRIMARY KEY,
112 user_id TEXT NOT NULL,
113 expires_at INTEGER NOT NULL,
114 FOREIGN KEY (user_id) REFERENCES users(id)
115 )`);
116 117 db.run(`CREATE TABLE IF NOT EXISTS offices (
118 id TEXT PRIMARY KEY,
119 courier TEXT NOT NULL,
120 name TEXT NOT NULL,
121 city TEXT NOT NULL,
122 address TEXT NOT NULL,
123 updated_at TEXT NOT NULL DEFAULT (datetime('now'))
124 )`);
125 126 db.run(`CREATE TABLE IF NOT EXISTS nostr_challenges (
127 challenge TEXT PRIMARY KEY,
128 created_at INTEGER NOT NULL
129 )`);
130 131 db.run(`CREATE TABLE IF NOT EXISTS content (
132 key TEXT PRIMARY KEY,
133 value TEXT NOT NULL DEFAULT ''
134 )`);
135 136 if (v < 5) {
137 db.run('INSERT OR IGNORE INTO content (key, value) VALUES (?, ?)', ['terms_bg', '']);
138 db.run('INSERT OR IGNORE INTO content (key, value) VALUES (?, ?)', ['terms_en', '']);
139 }
140 141 if (v < 6) {
142 db.run(`CREATE TABLE IF NOT EXISTS contact_messages (
143 id INTEGER PRIMARY KEY AUTOINCREMENT,
144 name TEXT NOT NULL,
145 email TEXT NOT NULL,
146 subject TEXT NOT NULL DEFAULT 'general',
147 message TEXT NOT NULL,
148 created_at TEXT NOT NULL DEFAULT (datetime('now'))
149 )`);
150 }
151 152 if (v < 9) {
153 const cols = db.query('PRAGMA table_info(products)').all() as { name: string }[];
154 if (!cols.some(c => c.name === 'usage')) db.run('ALTER TABLE products ADD COLUMN usage TEXT NOT NULL DEFAULT \'\'');
155 if (!cols.some(c => c.name === 'usage_en')) db.run('ALTER TABLE products ADD COLUMN usage_en TEXT NOT NULL DEFAULT \'\'');
156 }
157 158 db.run(`PRAGMA user_version = ${SCHEMA_VERSION}`);
159 }
160