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 {
173 // Plant the schema if soil is fresh (for new databases)
174 db.exec(`
175 CREATE TABLE IF NOT EXISTS rooms (
176 code TEXT PRIMARY KEY NOT NULL,
177 created_at INTEGER DEFAULT (unixepoch()),
178 title TEXT
179 );
180
181 CREATE TABLE IF NOT EXISTS items (
182 id TEXT PRIMARY KEY NOT NULL,
183 room_code TEXT NOT NULL,
184 text TEXT NOT NULL CHECK(LENGTH(text) BETWEEN 1 AND 200),
185 created_at INTEGER DEFAULT (unixepoch()),
186 FOREIGN KEY (room_code) REFERENCES rooms(code) ON DELETE CASCADE
187 );
188
189 CREATE TABLE IF NOT EXISTS votes (
190 item_id TEXT NOT NULL,
191 user_id TEXT NOT NULL,
192 vote_type TEXT NOT NULL CHECK(vote_type IN ('up', 'down', 'veto')),
193 created_at INTEGER DEFAULT (unixepoch()),
194 PRIMARY KEY (item_id, user_id),
195 FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
196 );
197 `);
198}
199
200export function getState(roomCode: string) {
201 const room = db.prepare('SELECT title FROM rooms WHERE code = ?').get(roomCode) as { title: string | null } | undefined;
202 const roomTitle = room?.title || null;
203
204 const items = db.prepare(`
205 SELECT i.id, i.text, i.created_at,
206 GROUP_CONCAT(v.user_id || ':' || v.vote_type) as votes
207 FROM items i
208 LEFT JOIN votes v ON i.id = v.item_id
209 WHERE i.room_code = ?
210 GROUP BY i.id
211 ORDER BY i.created_at
212 `).all(roomCode) as Array<{id: string, text: string, created_at: number, votes: string | null}>;
213
214 return {
215 roomTitle,
216 items: items.map(item => ({
217 id: item.id,
218 text: item.text,
219 votes: item.votes ? Object.fromEntries(
220 item.votes.split(',').map(v => {
221 const [userId, voteType] = v.split(':');
222 return [userId, voteType];
223 })
224 ) : {}
225 }))
226 };
227}
228
229export function itemBelongsToRoom(itemId: string, roomCode: string): boolean {
230 const result = db.prepare('SELECT 1 FROM items WHERE id = ? AND room_code = ?').get(itemId, roomCode);
231 return result !== undefined;
232}
233
234export function createRoom(code: string) {
235 db.prepare('INSERT INTO rooms (code) VALUES (?)').run(code);
236}
237
238export function roomExists(code: string): boolean {
239 const result = db.prepare('SELECT code FROM rooms WHERE code = ?').get(code);
240 return result !== undefined;
241}
242
243export function addItems(roomCode: string, items: Array<{id: string, text: string}>) {
244 try {
245 db.exec('BEGIN IMMEDIATE');
246 const stmt = db.prepare('INSERT INTO items (id, room_code, text) VALUES (?, ?, ?)');
247 for (const item of items) {
248 stmt.run(item.id, roomCode, item.text);
249 }
250 db.exec('COMMIT');
251 } catch (err) {
252 db.exec('ROLLBACK');
253 throw err;
254 }
255}
256
257export function upsertVote(itemId: string, userId: string, voteType: string) {
258 db.prepare(`
259 INSERT INTO votes (item_id, user_id, vote_type)
260 VALUES (?, ?, ?)
261 ON CONFLICT (item_id, user_id)
262 DO UPDATE SET vote_type = excluded.vote_type
263 `).run(itemId, userId, voteType);
264}
265
266export function deleteVote(itemId: string, userId: string) {
267 db.prepare('DELETE FROM votes WHERE item_id = ? AND user_id = ?').run(itemId, userId);
268}
269
270export function updateItemText(itemId: string, text: string) {
271 db.prepare('UPDATE items SET text = ? WHERE id = ?').run(text, itemId);
272}
273
274export function deleteItem(itemId: string) {
275 // Delete votes first (foreign key constraint)
276 db.prepare('DELETE FROM votes WHERE item_id = ?').run(itemId);
277 // Delete the item
278 db.prepare('DELETE FROM items WHERE id = ?').run(itemId);
279}
280
281export function resetVotes(roomCode: string) {
282 db.prepare(`
283 DELETE FROM votes
284 WHERE item_id IN (
285 SELECT id FROM items WHERE room_code = ?
286 )
287 `).run(roomCode);
288}
289
290export function updateRoomTitle(roomCode: string, title: string | null) {
291 db.prepare('UPDATE rooms SET title = ? WHERE code = ?').run(title, roomCode);
292}
293
294export function getItemsWithVotes(roomCode: string) {
295 return db.prepare(`
296 SELECT i.id, i.text,
297 GROUP_CONCAT(v.vote_type) as votes
298 FROM items i
299 LEFT JOIN votes v ON i.id = v.item_id
300 WHERE i.room_code = ?
301 GROUP BY i.id
302 `).all(roomCode) as Array<{id: string, text: string, votes: string | null}>;
303}