馃 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 * Get a single meeting time by ID
342 */
343export function getMeetingById(meetingId: string): MeetingTime | null {
344 const result = db
345 .query<MeetingTime, [string]>("SELECT * FROM meeting_times WHERE id = ?")
346 .get(meetingId);
347 return result ?? null;
348}
349
350/**
351 * Update a meeting time label
352 */
353export function updateMeetingTime(meetingId: string, label: string): void {
354 db.run("UPDATE meeting_times SET label = ? WHERE id = ?", [label, meetingId]);
355}
356
357/**
358 * Delete a meeting time
359 */
360export function deleteMeetingTime(meetingId: string): void {
361 db.run("DELETE FROM meeting_times WHERE id = ?", [meetingId]);
362}
363
364/**
365 * Get transcriptions for a class
366 */
367export function getTranscriptionsForClass(classId: string) {
368 return db
369 .query<
370 {
371 id: string;
372 user_id: number;
373 meeting_time_id: string | null;
374 filename: string;
375 original_filename: string;
376 status: string;
377 progress: number;
378 error_message: string | null;
379 created_at: number;
380 updated_at: number;
381 },
382 [string]
383 >(
384 `SELECT id, user_id, meeting_time_id, filename, original_filename, status, progress, error_message, created_at, updated_at
385 FROM transcriptions
386 WHERE class_id = ?
387 ORDER BY created_at DESC`,
388 )
389 .all(classId);
390}
391
392/**
393 * Search for classes by course code
394 */
395export function searchClassesByCourseCode(courseCode: string): Class[] {
396 return db
397 .query<Class, [string]>(
398 `SELECT * FROM classes
399 WHERE UPPER(course_code) LIKE UPPER(?)
400 AND archived = 0
401 ORDER BY year DESC, semester DESC, professor ASC`,
402 )
403 .all(`%${courseCode}%`);
404}
405
406/**
407 * Join a class by class ID
408 */
409export function joinClass(
410 classId: string,
411 userId: number,
412): { success: boolean; error?: string } {
413 // Find class by ID
414 const cls = db
415 .query<Class, [string]>("SELECT * FROM classes WHERE id = ?")
416 .get(classId);
417
418 if (!cls) {
419 return { success: false, error: "Class not found" };
420 }
421
422 if (cls.archived) {
423 return { success: false, error: "This class is archived" };
424 }
425
426 // Check if already enrolled
427 const existing = db
428 .query<ClassMember, [string, number]>(
429 "SELECT * FROM class_members WHERE class_id = ? AND user_id = ?",
430 )
431 .get(cls.id, userId);
432
433 if (existing) {
434 return { success: false, error: "Already enrolled in this class" };
435 }
436
437 // Enroll user
438 db.query(
439 "INSERT INTO class_members (class_id, user_id, enrolled_at) VALUES (?, ?, ?)",
440 ).run(cls.id, userId, Math.floor(Date.now() / 1000));
441
442 return { success: true };
443}
444
445/**
446 * Waitlist entry interface
447 */
448export interface WaitlistEntry {
449 id: string;
450 user_id: number;
451 course_code: string;
452 course_name: string;
453 professor: string;
454 semester: string;
455 year: number;
456 additional_info: string | null;
457 meeting_times: string | null;
458 created_at: number;
459}
460
461/**
462 * Add a class to the waitlist
463 */
464export function addToWaitlist(
465 userId: number,
466 courseCode: string,
467 courseName: string,
468 professor: string,
469 semester: string,
470 year: number,
471 additionalInfo: string | null,
472 meetingTimes: string[] | null,
473): string {
474 const id = nanoid();
475 const meetingTimesJson = meetingTimes ? JSON.stringify(meetingTimes) : null;
476
477 db.query(
478 `INSERT INTO class_waitlist
479 (id, user_id, course_code, course_name, professor, semester, year, additional_info, meeting_times, created_at)
480 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
481 ).run(
482 id,
483 userId,
484 courseCode,
485 courseName,
486 professor,
487 semester,
488 year,
489 additionalInfo,
490 meetingTimesJson,
491 Math.floor(Date.now() / 1000),
492 );
493 return id;
494}
495
496/**
497 * Get all waitlist entries
498 */
499export function getAllWaitlistEntries(): WaitlistEntry[] {
500 return db
501 .query<WaitlistEntry, []>(
502 "SELECT * FROM class_waitlist ORDER BY created_at DESC",
503 )
504 .all();
505}
506
507/**
508 * Delete a waitlist entry
509 */
510export function deleteWaitlistEntry(id: string): void {
511 db.query("DELETE FROM class_waitlist WHERE id = ?").run(id);
512}