1// SPDX-FileCopyrightText: Amolith <amolith@secluded.site>
2//
3// SPDX-License-Identifier: AGPL-3.0-or-later
4
5import { Database } from "https://deno.land/x/sqlite3@0.11.1/mod.ts";
6
7const db = new Database("lists.db");
8
9// Enable foreign key constraints
10db.exec("PRAGMA foreign_keys = ON;");
11
12// Check schema version and migrate if needed
13const currentVersion = (db.prepare('PRAGMA user_version').get() as { user_version: number }).user_version;
14
15if (currentVersion < 1) {
16 console.log('🔄 Migrating database schema to version 1...');
17
18 try {
19 db.exec('BEGIN');
20
21 // Temporarily disable foreign keys for migration
22 db.exec('PRAGMA foreign_keys = OFF');
23
24 // Create new tables with constraints
25 db.exec(`
26 CREATE TABLE rooms_new (
27 code TEXT PRIMARY KEY NOT NULL,
28 created_at INTEGER DEFAULT (unixepoch())
29 );
30
31 CREATE TABLE items_new (
32 id TEXT PRIMARY KEY NOT NULL,
33 room_code TEXT NOT NULL,
34 text TEXT NOT NULL CHECK(LENGTH(text) BETWEEN 1 AND 200),
35 created_at INTEGER DEFAULT (unixepoch()),
36 FOREIGN KEY (room_code) REFERENCES rooms_new(code) ON DELETE CASCADE
37 );
38
39 CREATE TABLE votes_new (
40 item_id TEXT NOT NULL,
41 user_id TEXT NOT NULL,
42 vote_type TEXT NOT NULL CHECK(vote_type IN ('up', 'down', 'veto')),
43 created_at INTEGER DEFAULT (unixepoch()),
44 PRIMARY KEY (item_id, user_id),
45 FOREIGN KEY (item_id) REFERENCES items_new(id) ON DELETE CASCADE
46 );
47 `);
48
49 // Copy data if old tables exist, filtering/truncating as needed
50 const tableExists = (name: string) => {
51 const result = db.prepare(`
52 SELECT name FROM sqlite_master
53 WHERE type='table' AND name=?
54 `).get(name);
55 return result !== undefined;
56 };
57
58 if (tableExists('rooms')) {
59 db.exec(`
60 INSERT INTO rooms_new (code, created_at)
61 SELECT code, created_at
62 FROM rooms
63 `);
64 }
65
66 if (tableExists('items')) {
67 db.exec(`
68 INSERT INTO items_new (id, room_code, text, created_at)
69 SELECT i.id, i.room_code,
70 SUBSTR(TRIM(REPLACE(REPLACE(i.text, char(13)||char(10), char(10)), char(13), char(10))), 1, 200) as text,
71 i.created_at
72 FROM items i
73 WHERE i.room_code IN (SELECT code FROM rooms_new)
74 AND LENGTH(TRIM(i.text)) > 0
75 `);
76 }
77
78 if (tableExists('votes')) {
79 db.exec(`
80 INSERT INTO votes_new (item_id, user_id, vote_type, created_at)
81 SELECT v.item_id, v.user_id, v.vote_type, v.created_at
82 FROM votes v
83 WHERE v.item_id IN (SELECT id FROM items_new)
84 AND v.vote_type IN ('up', 'down', 'veto')
85 `);
86 }
87
88 // Drop old tables if they exist
89 if (tableExists('votes')) db.exec('DROP TABLE votes');
90 if (tableExists('items')) db.exec('DROP TABLE items');
91 if (tableExists('rooms')) db.exec('DROP TABLE rooms');
92
93 // Rename new tables
94 db.exec('ALTER TABLE rooms_new RENAME TO rooms');
95 db.exec('ALTER TABLE items_new RENAME TO items');
96 db.exec('ALTER TABLE votes_new RENAME TO votes');
97
98 // Create indexes
99 db.exec(`
100 CREATE INDEX idx_items_room_code ON items(room_code);
101 CREATE INDEX idx_votes_item_id ON votes(item_id);
102 CREATE INDEX idx_votes_user_id ON votes(user_id);
103 `);
104
105 // Re-enable foreign keys
106 db.exec('PRAGMA foreign_keys = ON');
107
108 // Set schema version
109 db.exec('PRAGMA user_version = 2');
110
111 db.exec('COMMIT');
112 console.log('✅ Migration complete');
113 } catch (err) {
114 db.exec('ROLLBACK');
115 console.error('❌ Migration failed:', err);
116 throw err;
117 }
118} else if (currentVersion === 1) {
119 console.log('🔄 Migrating database schema from version 1 to 2...');
120
121 try {
122 db.exec('BEGIN');
123 db.exec('PRAGMA foreign_keys = OFF');
124
125 // Create new rooms table without last_activity and length constraint
126 db.exec(`
127 CREATE TABLE rooms_new (
128 code TEXT PRIMARY KEY NOT NULL,
129 created_at INTEGER DEFAULT (unixepoch())
130 );
131 `);
132
133 // Copy existing rooms
134 db.exec(`
135 INSERT INTO rooms_new (code, created_at)
136 SELECT code, created_at
137 FROM rooms
138 `);
139
140 // Drop old and rename
141 db.exec('DROP TABLE rooms');
142 db.exec('ALTER TABLE rooms_new RENAME TO rooms');
143
144 db.exec('PRAGMA foreign_keys = ON');
145 db.exec('PRAGMA user_version = 2');
146 db.exec('COMMIT');
147
148 console.log('✅ Migration from v1 to v2 complete');
149 } catch (err) {
150 db.exec('ROLLBACK');
151 console.error('❌ Migration failed:', err);
152 throw err;
153 }
154} else if (currentVersion === 2) {
155 console.log('🔄 Migrating database schema from version 2 to 3...');
156
157 try {
158 db.exec('BEGIN');
159
160 // Add title column to rooms table
161 db.exec('ALTER TABLE rooms ADD COLUMN title TEXT');
162
163 db.exec('PRAGMA user_version = 3');
164 db.exec('COMMIT');
165
166 console.log('✅ Migration from v2 to v3 complete');
167 } catch (err) {
168 db.exec('ROLLBACK');
169 console.error('❌ Migration failed:', err);
170 throw err;
171 }
172} else if (currentVersion === 3) {
173 console.log('🔄 Migrating database schema from version 3 to 4...');
174
175 try {
176 db.exec('BEGIN');
177
178 // Add last_activity column to rooms table, default to created_at
179 db.exec('ALTER TABLE rooms ADD COLUMN last_activity INTEGER');
180 db.exec('UPDATE rooms SET last_activity = created_at');
181
182 db.exec('PRAGMA user_version = 4');
183 db.exec('COMMIT');
184
185 console.log('✅ Migration from v3 to v4 complete');
186 } catch (err) {
187 db.exec('ROLLBACK');
188 console.error('❌ Migration failed:', err);
189 throw err;
190 }
191} else {
192 // Plant the schema if soil is fresh (for new databases)
193 db.exec(`
194 CREATE TABLE IF NOT EXISTS rooms (
195 code TEXT PRIMARY KEY NOT NULL,
196 created_at INTEGER DEFAULT (unixepoch()),
197 title TEXT,
198 last_activity INTEGER DEFAULT (unixepoch())
199 );
200
201 CREATE TABLE IF NOT EXISTS items (
202 id TEXT PRIMARY KEY NOT NULL,
203 room_code TEXT NOT NULL,
204 text TEXT NOT NULL CHECK(LENGTH(text) BETWEEN 1 AND 200),
205 created_at INTEGER DEFAULT (unixepoch()),
206 FOREIGN KEY (room_code) REFERENCES rooms(code) ON DELETE CASCADE
207 );
208
209 CREATE TABLE IF NOT EXISTS votes (
210 item_id TEXT NOT NULL,
211 user_id TEXT NOT NULL,
212 vote_type TEXT NOT NULL CHECK(vote_type IN ('up', 'down', 'veto')),
213 created_at INTEGER DEFAULT (unixepoch()),
214 PRIMARY KEY (item_id, user_id),
215 FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
216 );
217 `);
218}
219
220export function getState(roomCode: string) {
221 const room = db.prepare('SELECT title FROM rooms WHERE code = ?').get(roomCode) as { title: string | null } | undefined;
222 const roomTitle = room?.title || null;
223
224 const items = db.prepare(`
225 SELECT i.id, i.text, i.created_at,
226 GROUP_CONCAT(v.user_id || ':' || v.vote_type) as votes
227 FROM items i
228 LEFT JOIN votes v ON i.id = v.item_id
229 WHERE i.room_code = ?
230 GROUP BY i.id
231 ORDER BY i.created_at
232 `).all(roomCode) as Array<{id: string, text: string, created_at: number, votes: string | null}>;
233
234 return {
235 roomTitle,
236 items: items.map(item => ({
237 id: item.id,
238 text: item.text,
239 votes: item.votes ? Object.fromEntries(
240 item.votes.split(',').map(v => {
241 const [userId, voteType] = v.split(':');
242 return [userId, voteType];
243 })
244 ) : {}
245 }))
246 };
247}
248
249export function itemBelongsToRoom(itemId: string, roomCode: string): boolean {
250 const result = db.prepare('SELECT 1 FROM items WHERE id = ? AND room_code = ?').get(itemId, roomCode);
251 return result !== undefined;
252}
253
254export function createRoom(code: string) {
255 db.prepare('INSERT INTO rooms (code) VALUES (?)').run(code);
256}
257
258export function roomExists(code: string): boolean {
259 const result = db.prepare('SELECT code FROM rooms WHERE code = ?').get(code);
260 return result !== undefined;
261}
262
263export function addItems(roomCode: string, items: Array<{id: string, text: string}>) {
264 try {
265 db.exec('BEGIN IMMEDIATE');
266 const stmt = db.prepare('INSERT INTO items (id, room_code, text) VALUES (?, ?, ?)');
267 for (const item of items) {
268 stmt.run(item.id, roomCode, item.text);
269 }
270 db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode);
271 db.exec('COMMIT');
272 } catch (err) {
273 db.exec('ROLLBACK');
274 throw err;
275 }
276}
277
278export function upsertVote(itemId: string, userId: string, voteType: string) {
279 const roomCode = db.prepare('SELECT room_code FROM items WHERE id = ?').get(itemId) as { room_code: string } | undefined;
280 db.prepare(`
281 INSERT INTO votes (item_id, user_id, vote_type)
282 VALUES (?, ?, ?)
283 ON CONFLICT (item_id, user_id)
284 DO UPDATE SET vote_type = excluded.vote_type
285 `).run(itemId, userId, voteType);
286 if (roomCode) {
287 db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode.room_code);
288 }
289}
290
291export function deleteVote(itemId: string, userId: string) {
292 const roomCode = db.prepare('SELECT room_code FROM items WHERE id = ?').get(itemId) as { room_code: string } | undefined;
293 db.prepare('DELETE FROM votes WHERE item_id = ? AND user_id = ?').run(itemId, userId);
294 if (roomCode) {
295 db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode.room_code);
296 }
297}
298
299export function updateItemText(itemId: string, text: string) {
300 const roomCode = db.prepare('SELECT room_code FROM items WHERE id = ?').get(itemId) as { room_code: string } | undefined;
301 db.prepare('UPDATE items SET text = ? WHERE id = ?').run(text, itemId);
302 if (roomCode) {
303 db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode.room_code);
304 }
305}
306
307export function deleteItem(itemId: string) {
308 const roomCode = db.prepare('SELECT room_code FROM items WHERE id = ?').get(itemId) as { room_code: string } | undefined;
309 // Delete votes first (foreign key constraint)
310 db.prepare('DELETE FROM votes WHERE item_id = ?').run(itemId);
311 // Delete the item
312 db.prepare('DELETE FROM items WHERE id = ?').run(itemId);
313 if (roomCode) {
314 db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode.room_code);
315 }
316}
317
318export function resetVotes(roomCode: string) {
319 db.prepare(`
320 DELETE FROM votes
321 WHERE item_id IN (
322 SELECT id FROM items WHERE room_code = ?
323 )
324 `).run(roomCode);
325 db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode);
326}
327
328export function updateRoomTitle(roomCode: string, title: string | null) {
329 db.prepare('UPDATE rooms SET title = ?, last_activity = unixepoch() WHERE code = ?').run(title, roomCode);
330}
331
332export function touchRoomActivity(roomCode: string) {
333 db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode);
334}
335
336export function getItemsWithVotes(roomCode: string) {
337 return db.prepare(`
338 SELECT i.id, i.text,
339 GROUP_CONCAT(v.vote_type) as votes
340 FROM items i
341 LEFT JOIN votes v ON i.id = v.item_id
342 WHERE i.room_code = ?
343 GROUP BY i.id
344 `).all(roomCode) as Array<{id: string, text: string, votes: string | null}>;
345}
346
347export function deleteInactiveRooms(daysInactive: number = 30): number {
348 const cutoffTimestamp = Math.floor(Date.now() / 1000) - (daysInactive * 24 * 60 * 60);
349
350 const result = db.prepare(`
351 DELETE FROM rooms
352 WHERE last_activity < ?
353 `).run(cutoffTimestamp);
354
355 return result.changes || 0;
356}