Something went wrong. Try again.
Collaborative lists with real‑time voting and ranking
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357// SPDX-FileCopyrightText: Amolith <amolith@secluded.site>//// SPDX-License-Identifier: AGPL-3.0-or-later
import { Database } from "https://deno.land/x/sqlite3@0.11.1/mod.ts";
const db = new Database("lists.db");
// Enable foreign key constraintsdb.exec("PRAGMA foreign_keys = ON;");
// Check schema version and migrate if neededconst currentVersion = (db.prepare('PRAGMA user_version').get() as { user_version: number }).user_version;
if (currentVersion < 1) { console.log('🔄 Migrating database schema to version 1...'); try { db.exec('BEGIN'); // Temporarily disable foreign keys for migration db.exec('PRAGMA foreign_keys = OFF'); // Create new tables with constraints db.exec(` CREATE TABLE rooms_new ( code TEXT PRIMARY KEY NOT NULL, created_at INTEGER DEFAULT (unixepoch()) ); CREATE TABLE items_new ( id TEXT PRIMARY KEY NOT NULL, room_code TEXT NOT NULL, text TEXT NOT NULL CHECK(LENGTH(text) BETWEEN 1 AND 200), created_at INTEGER DEFAULT (unixepoch()), FOREIGN KEY (room_code) REFERENCES rooms_new(code) ON DELETE CASCADE ); CREATE TABLE votes_new ( item_id TEXT NOT NULL, user_id TEXT NOT NULL, vote_type TEXT NOT NULL CHECK(vote_type IN ('up', 'down', 'veto')), created_at INTEGER DEFAULT (unixepoch()), PRIMARY KEY (item_id, user_id), FOREIGN KEY (item_id) REFERENCES items_new(id) ON DELETE CASCADE ); `); // Copy data if old tables exist, filtering/truncating as needed const tableExists = (name: string) => { const result = db.prepare(` SELECT name FROM sqlite_master WHERE type='table' AND name=? `).get(name); return result !== undefined; }; if (tableExists('rooms')) { db.exec(` INSERT INTO rooms_new (code, created_at) SELECT code, created_at FROM rooms `); } if (tableExists('items')) { db.exec(` INSERT INTO items_new (id, room_code, text, created_at) SELECT i.id, i.room_code, SUBSTR(TRIM(REPLACE(REPLACE(i.text, char(13)||char(10), char(10)), char(13), char(10))), 1, 200) as text, i.created_at FROM items i WHERE i.room_code IN (SELECT code FROM rooms_new) AND LENGTH(TRIM(i.text)) > 0 `); } if (tableExists('votes')) { db.exec(` INSERT INTO votes_new (item_id, user_id, vote_type, created_at) SELECT v.item_id, v.user_id, v.vote_type, v.created_at FROM votes v WHERE v.item_id IN (SELECT id FROM items_new) AND v.vote_type IN ('up', 'down', 'veto') `); } // Drop old tables if they exist if (tableExists('votes')) db.exec('DROP TABLE votes'); if (tableExists('items')) db.exec('DROP TABLE items'); if (tableExists('rooms')) db.exec('DROP TABLE rooms'); // Rename new tables db.exec('ALTER TABLE rooms_new RENAME TO rooms'); db.exec('ALTER TABLE items_new RENAME TO items'); db.exec('ALTER TABLE votes_new RENAME TO votes'); // Create indexes db.exec(` CREATE INDEX idx_items_room_code ON items(room_code); CREATE INDEX idx_votes_item_id ON votes(item_id); CREATE INDEX idx_votes_user_id ON votes(user_id); `); // Re-enable foreign keys db.exec('PRAGMA foreign_keys = ON'); // Set schema version db.exec('PRAGMA user_version = 2'); db.exec('COMMIT'); console.log('✅ Migration complete'); } catch (err) { db.exec('ROLLBACK'); console.error('❌ Migration failed:', err); throw err; }} else if (currentVersion === 1) { console.log('🔄 Migrating database schema from version 1 to 2...'); try { db.exec('BEGIN'); db.exec('PRAGMA foreign_keys = OFF'); // Create new rooms table without last_activity and length constraint db.exec(` CREATE TABLE rooms_new ( code TEXT PRIMARY KEY NOT NULL, created_at INTEGER DEFAULT (unixepoch()) ); `); // Copy existing rooms db.exec(` INSERT INTO rooms_new (code, created_at) SELECT code, created_at FROM rooms `); // Drop old and rename db.exec('DROP TABLE rooms'); db.exec('ALTER TABLE rooms_new RENAME TO rooms'); db.exec('PRAGMA foreign_keys = ON'); db.exec('PRAGMA user_version = 2'); db.exec('COMMIT'); console.log('✅ Migration from v1 to v2 complete'); } catch (err) { db.exec('ROLLBACK'); console.error('❌ Migration failed:', err); throw err; }} else if (currentVersion === 2) { console.log('🔄 Migrating database schema from version 2 to 3...'); try { db.exec('BEGIN'); // Add title column to rooms table db.exec('ALTER TABLE rooms ADD COLUMN title TEXT'); db.exec('PRAGMA user_version = 3'); db.exec('COMMIT'); console.log('✅ Migration from v2 to v3 complete'); } catch (err) { db.exec('ROLLBACK'); console.error('❌ Migration failed:', err); throw err; }} else if (currentVersion === 3) { console.log('🔄 Migrating database schema from version 3 to 4...'); try { db.exec('BEGIN'); // Add last_activity column to rooms table, default to created_at db.exec('ALTER TABLE rooms ADD COLUMN last_activity INTEGER'); db.exec('UPDATE rooms SET last_activity = created_at'); db.exec('PRAGMA user_version = 4'); db.exec('COMMIT'); console.log('✅ Migration from v3 to v4 complete'); } catch (err) { db.exec('ROLLBACK'); console.error('❌ Migration failed:', err); throw err; }} else { // Plant the schema if soil is fresh (for new databases) db.exec(` CREATE TABLE IF NOT EXISTS rooms ( code TEXT PRIMARY KEY NOT NULL, created_at INTEGER DEFAULT (unixepoch()), title TEXT, last_activity INTEGER DEFAULT (unixepoch()) ); CREATE TABLE IF NOT EXISTS items ( id TEXT PRIMARY KEY NOT NULL, room_code TEXT NOT NULL, text TEXT NOT NULL CHECK(LENGTH(text) BETWEEN 1 AND 200), created_at INTEGER DEFAULT (unixepoch()), FOREIGN KEY (room_code) REFERENCES rooms(code) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS votes ( item_id TEXT NOT NULL, user_id TEXT NOT NULL, vote_type TEXT NOT NULL CHECK(vote_type IN ('up', 'down', 'veto')), created_at INTEGER DEFAULT (unixepoch()), PRIMARY KEY (item_id, user_id), FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE ); `);}
export function getState(roomCode: string) { const room = db.prepare('SELECT title FROM rooms WHERE code = ?').get(roomCode) as { title: string | null } | undefined; const roomTitle = room?.title || null; const items = db.prepare(` SELECT i.id, i.text, i.created_at, GROUP_CONCAT(v.user_id || ':' || v.vote_type) as votes FROM items i LEFT JOIN votes v ON i.id = v.item_id WHERE i.room_code = ? GROUP BY i.id ORDER BY i.created_at `).all(roomCode) as Array<{id: string, text: string, created_at: number, votes: string | null}>;
return { roomTitle, items: items.map(item => ({ id: item.id, text: item.text, votes: item.votes ? Object.fromEntries( item.votes.split(',').map(v => { const [userId, voteType] = v.split(':'); return [userId, voteType]; }) ) : {} })) };}
export function itemBelongsToRoom(itemId: string, roomCode: string): boolean { const result = db.prepare('SELECT 1 FROM items WHERE id = ? AND room_code = ?').get(itemId, roomCode); return result !== undefined;}
export function createRoom(code: string) { db.prepare('INSERT INTO rooms (code) VALUES (?)').run(code);}
export function roomExists(code: string): boolean { const result = db.prepare('SELECT code FROM rooms WHERE code = ?').get(code); return result !== undefined;}
export function addItems(roomCode: string, items: Array<{id: string, text: string}>) { try { db.exec('BEGIN IMMEDIATE'); const stmt = db.prepare('INSERT INTO items (id, room_code, text) VALUES (?, ?, ?)'); for (const item of items) { stmt.run(item.id, roomCode, item.text); } db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode); db.exec('COMMIT'); } catch (err) { db.exec('ROLLBACK'); throw err; }}
export function upsertVote(itemId: string, userId: string, voteType: string) { const roomCode = db.prepare('SELECT room_code FROM items WHERE id = ?').get(itemId) as { room_code: string } | undefined; db.prepare(` INSERT INTO votes (item_id, user_id, vote_type) VALUES (?, ?, ?) ON CONFLICT (item_id, user_id) DO UPDATE SET vote_type = excluded.vote_type `).run(itemId, userId, voteType); if (roomCode) { db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode.room_code); }}
export function deleteVote(itemId: string, userId: string) { const roomCode = db.prepare('SELECT room_code FROM items WHERE id = ?').get(itemId) as { room_code: string } | undefined; db.prepare('DELETE FROM votes WHERE item_id = ? AND user_id = ?').run(itemId, userId); if (roomCode) { db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode.room_code); }}
export function updateItemText(itemId: string, text: string) { const roomCode = db.prepare('SELECT room_code FROM items WHERE id = ?').get(itemId) as { room_code: string } | undefined; db.prepare('UPDATE items SET text = ? WHERE id = ?').run(text, itemId); if (roomCode) { db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode.room_code); }}
export function deleteItem(itemId: string) { const roomCode = db.prepare('SELECT room_code FROM items WHERE id = ?').get(itemId) as { room_code: string } | undefined; // Delete votes first (foreign key constraint) db.prepare('DELETE FROM votes WHERE item_id = ?').run(itemId); // Delete the item db.prepare('DELETE FROM items WHERE id = ?').run(itemId); if (roomCode) { db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode.room_code); }}
export function resetVotes(roomCode: string) { db.prepare(` DELETE FROM votes WHERE item_id IN ( SELECT id FROM items WHERE room_code = ? ) `).run(roomCode); db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode);}
export function updateRoomTitle(roomCode: string, title: string | null) { db.prepare('UPDATE rooms SET title = ?, last_activity = unixepoch() WHERE code = ?').run(title, roomCode);}
export function touchRoomActivity(roomCode: string) { db.prepare('UPDATE rooms SET last_activity = unixepoch() WHERE code = ?').run(roomCode);}
export function getItemsWithVotes(roomCode: string) { return db.prepare(` SELECT i.id, i.text, GROUP_CONCAT(v.vote_type) as votes FROM items i LEFT JOIN votes v ON i.id = v.item_id WHERE i.room_code = ? GROUP BY i.id `).all(roomCode) as Array<{id: string, text: string, votes: string | null}>;}
export function deleteInactiveRooms(daysInactive: number = 30): number { const cutoffTimestamp = Math.floor(Date.now() / 1000) - (daysInactive * 24 * 60 * 60); const result = db.prepare(` DELETE FROM rooms WHERE last_activity < ? `).run(cutoffTimestamp); return result.changes || 0;}