馃 distributed transcription service
thistle.dunkirk.sh
dunkirk.sh
1import { nanoid } from "nanoid";
2import db from "../db/schema";
3
4export interface Class {
5 id: string;
6 course_code: string;
7 name: string;
8 professor: string;
9 semester: string;
10 year: number;
11 archived: boolean;
12 created_at: number;
13}
14
15export interface MeetingTime {
16 id: string;
17 class_id: string;
18 label: string;
19 created_at: number;
20}
21
22export interface ClassMember {
23 class_id: string;
24 user_id: number;
25 enrolled_at: number;
26}
27
28export interface ClassWithStats extends Class {
29 student_count?: number;
30 transcript_count?: number;
31}
32
33/**
34 * Get all classes for a user (either enrolled or admin sees all)
35 */
36export function getClassesForUser(
37 userId: number,
38 isAdmin: boolean,
39 limit = 50,
40 cursor?: string,
41): {
42 data: ClassWithStats[];
43 pagination: {
44 limit: number;
45 hasMore: boolean;
46 nextCursor: string | null;
47 };
48} {
49 let classes: ClassWithStats[];
50
51 if (isAdmin) {
52 if (cursor) {
53 const { decodeClassCursor } = require("./cursor");
54 const { year, semester, courseCode, id } = decodeClassCursor(cursor);
55
56 classes = db
57 .query<ClassWithStats, [number, string, string, string, number]>(
58 `SELECT
59 c.*,
60 (SELECT COUNT(*) FROM class_members WHERE class_id = c.id) as student_count,
61 (SELECT COUNT(*) FROM transcriptions WHERE class_id = c.id) as transcript_count
62 FROM classes c
63 WHERE (c.year < ? OR
64 (c.year = ? AND c.semester < ?) OR
65 (c.year = ? AND c.semester = ? AND c.course_code > ?) OR
66 (c.year = ? AND c.semester = ? AND c.course_code = ? AND c.id > ?))
67 ORDER BY c.year DESC, c.semester DESC, c.course_code ASC, c.id ASC
68 LIMIT ?`,
69 )
70 .all(
71 year,
72 year,
73 semester,
74 year,
75 semester,
76 courseCode,
77 year,
78 semester,
79 courseCode,
80 id,
81 limit + 1,
82 );
83 } else {
84 classes = db
85 .query<ClassWithStats, [number]>(
86 `SELECT
87 c.*,
88 (SELECT COUNT(*) FROM class_members WHERE class_id = c.id) as student_count,
89 (SELECT COUNT(*) FROM transcriptions WHERE class_id = c.id) as transcript_count
90 FROM classes c
91 ORDER BY c.year DESC, c.semester DESC, c.course_code ASC, c.id ASC
92 LIMIT ?`,
93 )
94 .all(limit + 1);
95 }
96 } else {
97 if (cursor) {
98 const { decodeClassCursor } = require("./cursor");
99 const { year, semester, courseCode, id } = decodeClassCursor(cursor);
100
101 classes = db
102 .query<
103 ClassWithStats,
104 [number, number, string, string, string, number]
105 >(
106 `SELECT c.* FROM classes c
107 INNER JOIN class_members cm ON c.id = cm.class_id
108 WHERE cm.user_id = ? AND
109 (c.year < ? OR
110 (c.year = ? AND c.semester < ?) OR
111 (c.year = ? AND c.semester = ? AND c.course_code > ?) OR
112 (c.year = ? AND c.semester = ? AND c.course_code = ? AND c.id > ?))
113 ORDER BY c.year DESC, c.semester DESC, c.course_code ASC, c.id ASC
114 LIMIT ?`,
115 )
116 .all(
117 userId,
118 year,
119 year,
120 semester,
121 year,
122 semester,
123 courseCode,
124 year,
125 semester,
126 courseCode,
127 id,
128 limit + 1,
129 );
130 } else {
131 classes = db
132 .query<ClassWithStats, [number, number]>(
133 `SELECT c.* FROM classes c
134 INNER JOIN class_members cm ON c.id = cm.class_id
135 WHERE cm.user_id = ?
136 ORDER BY c.year DESC, c.semester DESC, c.course_code ASC, c.id ASC
137 LIMIT ?`,
138 )
139 .all(userId, limit + 1);
140 }
141 }
142
143 const hasMore = classes.length > limit;
144 if (hasMore) {
145 classes.pop();
146 }
147
148 let nextCursor: string | null = null;
149 if (hasMore && classes.length > 0) {
150 const { encodeClassCursor } = require("./cursor");
151 const last = classes[classes.length - 1];
152 if (last) {
153 nextCursor = encodeClassCursor(
154 last.year,
155 last.semester,
156 last.course_code,
157 last.id,
158 );
159 }
160 }
161
162 return {
163 data: classes,
164 pagination: {
165 limit,
166 hasMore,
167 nextCursor,
168 },
169 };
170}
171
172/**
173 * Get a single class by ID
174 */
175export function getClassById(classId: string): Class | null {
176 const result = db
177 .query<Class, [string]>("SELECT * FROM classes WHERE id = ?")
178 .get(classId);
179 return result ?? null;
180}
181
182/**
183 * Check if user is enrolled in a class
184 */
185export function isUserEnrolledInClass(
186 userId: number,
187 classId: string,
188): boolean {
189 const result = db
190 .query<{ count: number }, [string, number]>(
191 "SELECT COUNT(*) as count FROM class_members WHERE class_id = ? AND user_id = ?",
192 )
193 .get(classId, userId);
194 return (result?.count ?? 0) > 0;
195}
196
197/**
198 * Create a new class
199 */
200export function createClass(data: {
201 course_code: string;
202 name: string;
203 professor: string;
204 semester: string;
205 year: number;
206 meeting_times?: string[];
207}): Class {
208 const id = nanoid();
209 const now = Math.floor(Date.now() / 1000);
210
211 db.run(
212 "INSERT INTO classes (id, course_code, name, professor, semester, year, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
213 [
214 id,
215 data.course_code,
216 data.name,
217 data.professor,
218 data.semester,
219 data.year,
220 now,
221 ],
222 );
223
224 // Create meeting times if provided
225 if (data.meeting_times && data.meeting_times.length > 0) {
226 for (const label of data.meeting_times) {
227 createMeetingTime(id, label);
228 }
229 }
230
231 return {
232 id,
233 course_code: data.course_code,
234 name: data.name,
235 professor: data.professor,
236 semester: data.semester,
237 year: data.year,
238 archived: false,
239 created_at: now,
240 };
241}
242
243/**
244 * Archive or unarchive a class
245 */
246export function toggleClassArchive(classId: string, archived: boolean): void {
247 const result = db.run("UPDATE classes SET archived = ? WHERE id = ?", [
248 archived ? 1 : 0,
249 classId,
250 ]);
251
252 if (result.changes === 0) {
253 throw new Error("Class not found");
254 }
255}
256
257/**
258 * Delete a class (cascades to members, meeting times, and transcriptions)
259 */
260export function deleteClass(classId: string): void {
261 db.run("DELETE FROM classes WHERE id = ?", [classId]);
262}
263
264/**
265 * Enroll a user in a class
266 */
267export function enrollUserInClass(userId: number, classId: string): void {
268 const now = Math.floor(Date.now() / 1000);
269 db.run(
270 "INSERT OR IGNORE INTO class_members (class_id, user_id, enrolled_at) VALUES (?, ?, ?)",
271 [classId, userId, now],
272 );
273}
274
275/**
276 * Remove a user from a class
277 */
278export function removeUserFromClass(userId: number, classId: string): void {
279 db.run("DELETE FROM class_members WHERE class_id = ? AND user_id = ?", [
280 classId,
281 userId,
282 ]);
283}
284
285/**
286 * Get all members of a class
287 */
288export function getClassMembers(classId: string) {
289 return db
290 .query<
291 {
292 user_id: number;
293 email: string;
294 name: string | null;
295 avatar: string;
296 enrolled_at: number;
297 },
298 [string]
299 >(
300 `SELECT cm.user_id, u.email, u.name, u.avatar, cm.enrolled_at
301 FROM class_members cm
302 INNER JOIN users u ON cm.user_id = u.id
303 WHERE cm.class_id = ?
304 ORDER BY cm.enrolled_at DESC`,
305 )
306 .all(classId);
307}
308
309/**
310 * Create a meeting time for a class
311 */
312export function createMeetingTime(classId: string, label: string): MeetingTime {
313 const id = nanoid();
314 const now = Math.floor(Date.now() / 1000);
315
316 db.run(
317 "INSERT INTO meeting_times (id, class_id, label, created_at) VALUES (?, ?, ?, ?)",
318 [id, classId, label, now],
319 );
320
321 return {
322 id,
323 class_id: classId,
324 label,
325 created_at: now,
326 };
327}
328
329/**
330 * Get all meeting times for a class
331 */
332export function getMeetingTimesForClass(classId: string): MeetingTime[] {
333 return db
334 .query<MeetingTime, [string]>(
335 "SELECT * FROM meeting_times WHERE class_id = ? ORDER BY created_at ASC",
336 )
337 .all(classId);
338}
339
340/**
341 * Update a meeting time label
342 */
343export function updateMeetingTime(meetingId: string, label: string): void {
344 db.run("UPDATE meeting_times SET label = ? WHERE id = ?", [label, meetingId]);
345}
346
347/**
348 * Delete a meeting time
349 */
350export function deleteMeetingTime(meetingId: string): void {
351 db.run("DELETE FROM meeting_times WHERE id = ?", [meetingId]);
352}
353
354/**
355 * Get transcriptions for a class
356 */
357export function getTranscriptionsForClass(classId: string) {
358 return db
359 .query<
360 {
361 id: string;
362 user_id: number;
363 meeting_time_id: string | null;
364 filename: string;
365 original_filename: string;
366 status: string;
367 progress: number;
368 error_message: string | null;
369 created_at: number;
370 updated_at: number;
371 },
372 [string]
373 >(
374 `SELECT id, user_id, meeting_time_id, filename, original_filename, status, progress, error_message, created_at, updated_at
375 FROM transcriptions
376 WHERE class_id = ?
377 ORDER BY created_at DESC`,
378 )
379 .all(classId);
380}
381
382/**
383 * Search for classes by course code
384 */
385export function searchClassesByCourseCode(courseCode: string): Class[] {
386 return db
387 .query<Class, [string]>(
388 `SELECT * FROM classes
389 WHERE UPPER(course_code) LIKE UPPER(?)
390 AND archived = 0
391 ORDER BY year DESC, semester DESC, professor ASC`,
392 )
393 .all(`%${courseCode}%`);
394}
395
396/**
397 * Join a class by class ID
398 */
399export function joinClass(
400 classId: string,
401 userId: number,
402): { success: boolean; error?: string } {
403 // Find class by ID
404 const cls = db
405 .query<Class, [string]>("SELECT * FROM classes WHERE id = ?")
406 .get(classId);
407
408 if (!cls) {
409 return { success: false, error: "Class not found" };
410 }
411
412 if (cls.archived) {
413 return { success: false, error: "This class is archived" };
414 }
415
416 // Check if already enrolled
417 const existing = db
418 .query<ClassMember, [string, number]>(
419 "SELECT * FROM class_members WHERE class_id = ? AND user_id = ?",
420 )
421 .get(cls.id, userId);
422
423 if (existing) {
424 return { success: false, error: "Already enrolled in this class" };
425 }
426
427 // Enroll user
428 db.query(
429 "INSERT INTO class_members (class_id, user_id, enrolled_at) VALUES (?, ?, ?)",
430 ).run(cls.id, userId, Math.floor(Date.now() / 1000));
431
432 return { success: true };
433}
434
435/**
436 * Waitlist entry interface
437 */
438export interface WaitlistEntry {
439 id: string;
440 user_id: number;
441 course_code: string;
442 course_name: string;
443 professor: string;
444 semester: string;
445 year: number;
446 additional_info: string | null;
447 meeting_times: string | null;
448 created_at: number;
449}
450
451/**
452 * Add a class to the waitlist
453 */
454export function addToWaitlist(
455 userId: number,
456 courseCode: string,
457 courseName: string,
458 professor: string,
459 semester: string,
460 year: number,
461 additionalInfo: string | null,
462 meetingTimes: string[] | null,
463): string {
464 const id = nanoid();
465 const meetingTimesJson = meetingTimes ? JSON.stringify(meetingTimes) : null;
466
467 db.query(
468 `INSERT INTO class_waitlist
469 (id, user_id, course_code, course_name, professor, semester, year, additional_info, meeting_times, created_at)
470 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
471 ).run(
472 id,
473 userId,
474 courseCode,
475 courseName,
476 professor,
477 semester,
478 year,
479 additionalInfo,
480 meetingTimesJson,
481 Math.floor(Date.now() / 1000),
482 );
483 return id;
484}
485
486/**
487 * Get all waitlist entries
488 */
489export function getAllWaitlistEntries(): WaitlistEntry[] {
490 return db
491 .query<WaitlistEntry, []>(
492 "SELECT * FROM class_waitlist ORDER BY created_at DESC",
493 )
494 .all();
495}
496
497/**
498 * Delete a waitlist entry
499 */
500export function deleteWaitlistEntry(id: string): void {
501 db.query("DELETE FROM class_waitlist WHERE id = ?").run(id);
502}