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