馃 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
28/**
29 * Get all classes for a user (either enrolled or admin sees all)
30 */
31export function getClassesForUser(userId: number, isAdmin: boolean): Class[] {
32 if (isAdmin) {
33 return db
34 .query<Class, []>(
35 "SELECT * FROM classes ORDER BY year DESC, semester DESC, course_code ASC",
36 )
37 .all();
38 }
39
40 return db
41 .query<Class, [number]>(
42 `SELECT c.* FROM classes c
43 INNER JOIN class_members cm ON c.id = cm.class_id
44 WHERE cm.user_id = ?
45 ORDER BY c.year DESC, c.semester DESC, c.course_code ASC`,
46 )
47 .all(userId);
48}
49
50/**
51 * Get a single class by ID
52 */
53export function getClassById(classId: string): Class | null {
54 const result = db
55 .query<Class, [string]>("SELECT * FROM classes WHERE id = ?")
56 .get(classId);
57 return result ?? null;
58}
59
60/**
61 * Check if user is enrolled in a class
62 */
63export function isUserEnrolledInClass(
64 userId: number,
65 classId: string,
66): boolean {
67 const result = db
68 .query<{ count: number }, [string, number]>(
69 "SELECT COUNT(*) as count FROM class_members WHERE class_id = ? AND user_id = ?",
70 )
71 .get(classId, userId);
72 return (result?.count ?? 0) > 0;
73}
74
75/**
76 * Create a new class
77 */
78export function createClass(data: {
79 course_code: string;
80 name: string;
81 professor: string;
82 semester: string;
83 year: number;
84 meeting_times?: string[];
85}): Class {
86 const id = nanoid();
87 const now = Math.floor(Date.now() / 1000);
88
89 db.run(
90 "INSERT INTO classes (id, course_code, name, professor, semester, year, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
91 [
92 id,
93 data.course_code,
94 data.name,
95 data.professor,
96 data.semester,
97 data.year,
98 now,
99 ],
100 );
101
102 // Create meeting times if provided
103 if (data.meeting_times && data.meeting_times.length > 0) {
104 for (const label of data.meeting_times) {
105 createMeetingTime(id, label);
106 }
107 }
108
109 return {
110 id,
111 course_code: data.course_code,
112 name: data.name,
113 professor: data.professor,
114 semester: data.semester,
115 year: data.year,
116 archived: false,
117 created_at: now,
118 };
119}
120
121/**
122 * Archive or unarchive a class
123 */
124export function toggleClassArchive(classId: string, archived: boolean): void {
125 db.run("UPDATE classes SET archived = ? WHERE id = ?", [
126 archived ? 1 : 0,
127 classId,
128 ]);
129}
130
131/**
132 * Delete a class (cascades to members, meeting times, and transcriptions)
133 */
134export function deleteClass(classId: string): void {
135 db.run("DELETE FROM classes WHERE id = ?", [classId]);
136}
137
138/**
139 * Enroll a user in a class
140 */
141export function enrollUserInClass(userId: number, classId: string): void {
142 const now = Math.floor(Date.now() / 1000);
143 db.run(
144 "INSERT OR IGNORE INTO class_members (class_id, user_id, enrolled_at) VALUES (?, ?, ?)",
145 [classId, userId, now],
146 );
147}
148
149/**
150 * Remove a user from a class
151 */
152export function removeUserFromClass(userId: number, classId: string): void {
153 db.run("DELETE FROM class_members WHERE class_id = ? AND user_id = ?", [
154 classId,
155 userId,
156 ]);
157}
158
159/**
160 * Get all members of a class
161 */
162export function getClassMembers(classId: string) {
163 return db
164 .query<
165 {
166 user_id: number;
167 email: string;
168 name: string | null;
169 avatar: string;
170 enrolled_at: number;
171 },
172 [string]
173 >(
174 `SELECT cm.user_id, u.email, u.name, u.avatar, cm.enrolled_at
175 FROM class_members cm
176 INNER JOIN users u ON cm.user_id = u.id
177 WHERE cm.class_id = ?
178 ORDER BY cm.enrolled_at DESC`,
179 )
180 .all(classId);
181}
182
183/**
184 * Create a meeting time for a class
185 */
186export function createMeetingTime(classId: string, label: string): MeetingTime {
187 const id = nanoid();
188 const now = Math.floor(Date.now() / 1000);
189
190 db.run(
191 "INSERT INTO meeting_times (id, class_id, label, created_at) VALUES (?, ?, ?, ?)",
192 [id, classId, label, now],
193 );
194
195 return {
196 id,
197 class_id: classId,
198 label,
199 created_at: now,
200 };
201}
202
203/**
204 * Get all meeting times for a class
205 */
206export function getMeetingTimesForClass(classId: string): MeetingTime[] {
207 return db
208 .query<MeetingTime, [string]>(
209 "SELECT * FROM meeting_times WHERE class_id = ? ORDER BY created_at ASC",
210 )
211 .all(classId);
212}
213
214/**
215 * Update a meeting time label
216 */
217export function updateMeetingTime(meetingId: string, label: string): void {
218 db.run("UPDATE meeting_times SET label = ? WHERE id = ?", [label, meetingId]);
219}
220
221/**
222 * Delete a meeting time
223 */
224export function deleteMeetingTime(meetingId: string): void {
225 db.run("DELETE FROM meeting_times WHERE id = ?", [meetingId]);
226}
227
228/**
229 * Get transcriptions for a class
230 */
231export function getTranscriptionsForClass(classId: string) {
232 return db
233 .query<
234 {
235 id: string;
236 user_id: number;
237 meeting_time_id: string | null;
238 filename: string;
239 original_filename: string;
240 status: string;
241 progress: number;
242 error_message: string | null;
243 created_at: number;
244 updated_at: number;
245 },
246 [string]
247 >(
248 `SELECT id, user_id, meeting_time_id, filename, original_filename, status, progress, error_message, created_at, updated_at
249 FROM transcriptions
250 WHERE class_id = ?
251 ORDER BY created_at DESC`,
252 )
253 .all(classId);
254}
255
256/**
257 * Search for classes by course code
258 */
259export function searchClassesByCourseCode(courseCode: string): Class[] {
260 return db
261 .query<Class, [string]>(
262 `SELECT * FROM classes
263 WHERE UPPER(course_code) LIKE UPPER(?)
264 AND archived = 0
265 ORDER BY year DESC, semester DESC, professor ASC`,
266 )
267 .all(`%${courseCode}%`);
268}
269
270/**
271 * Join a class by class ID
272 */
273export function joinClass(
274 classId: string,
275 userId: number,
276): { success: boolean; error?: string } {
277 // Find class by ID
278 const cls = db
279 .query<Class, [string]>("SELECT * FROM classes WHERE id = ?")
280 .get(classId);
281
282 if (!cls) {
283 return { success: false, error: "Class not found" };
284 }
285
286 if (cls.archived) {
287 return { success: false, error: "This class is archived" };
288 }
289
290 // Check if already enrolled
291 const existing = db
292 .query<ClassMember, [string, number]>(
293 "SELECT * FROM class_members WHERE class_id = ? AND user_id = ?",
294 )
295 .get(cls.id, userId);
296
297 if (existing) {
298 return { success: false, error: "Already enrolled in this class" };
299 }
300
301 // Enroll user
302 db.query(
303 "INSERT INTO class_members (class_id, user_id, enrolled_at) VALUES (?, ?, ?)",
304 ).run(cls.id, userId, Math.floor(Date.now() / 1000));
305
306 return { success: true };
307}
308
309/**
310 * Waitlist entry interface
311 */
312export interface WaitlistEntry {
313 id: string;
314 user_id: number;
315 course_code: string;
316 course_name: string;
317 professor: string;
318 semester: string;
319 year: number;
320 additional_info: string | null;
321 meeting_times: string | null;
322 created_at: number;
323}
324
325/**
326 * Add a class to the waitlist
327 */
328export function addToWaitlist(
329 userId: number,
330 courseCode: string,
331 courseName: string,
332 professor: string,
333 semester: string,
334 year: number,
335 additionalInfo: string | null,
336 meetingTimes: string[] | null,
337): string {
338 const id = nanoid();
339 const meetingTimesJson = meetingTimes ? JSON.stringify(meetingTimes) : null;
340
341 db.query(
342 `INSERT INTO class_waitlist
343 (id, user_id, course_code, course_name, professor, semester, year, additional_info, meeting_times, created_at)
344 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
345 ).run(
346 id,
347 userId,
348 courseCode,
349 courseName,
350 professor,
351 semester,
352 year,
353 additionalInfo,
354 meetingTimesJson,
355 Math.floor(Date.now() / 1000),
356 );
357 return id;
358}
359
360/**
361 * Get all waitlist entries
362 */
363export function getAllWaitlistEntries(): WaitlistEntry[] {
364 return db
365 .query<WaitlistEntry, []>(
366 "SELECT * FROM class_waitlist ORDER BY created_at DESC",
367 )
368 .all();
369}
370
371/**
372 * Delete a waitlist entry
373 */
374export function deleteWaitlistEntry(id: string): void {
375 db.query("DELETE FROM class_waitlist WHERE id = ?").run(id);
376}