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