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