馃 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 const result = db.run("UPDATE classes SET archived = ? WHERE id = ?", [
139 archived ? 1 : 0,
140 classId,
141 ]);
142
143 if (result.changes === 0) {
144 throw new Error("Class not found");
145 }
146}
147
148/**
149 * Delete a class (cascades to members, meeting times, and transcriptions)
150 */
151export function deleteClass(classId: string): void {
152 db.run("DELETE FROM classes WHERE id = ?", [classId]);
153}
154
155/**
156 * Enroll a user in a class
157 */
158export function enrollUserInClass(userId: number, classId: string): void {
159 const now = Math.floor(Date.now() / 1000);
160 db.run(
161 "INSERT OR IGNORE INTO class_members (class_id, user_id, enrolled_at) VALUES (?, ?, ?)",
162 [classId, userId, now],
163 );
164}
165
166/**
167 * Remove a user from a class
168 */
169export function removeUserFromClass(userId: number, classId: string): void {
170 db.run("DELETE FROM class_members WHERE class_id = ? AND user_id = ?", [
171 classId,
172 userId,
173 ]);
174}
175
176/**
177 * Get all members of a class
178 */
179export function getClassMembers(classId: string) {
180 return db
181 .query<
182 {
183 user_id: number;
184 email: string;
185 name: string | null;
186 avatar: string;
187 enrolled_at: number;
188 },
189 [string]
190 >(
191 `SELECT cm.user_id, u.email, u.name, u.avatar, cm.enrolled_at
192 FROM class_members cm
193 INNER JOIN users u ON cm.user_id = u.id
194 WHERE cm.class_id = ?
195 ORDER BY cm.enrolled_at DESC`,
196 )
197 .all(classId);
198}
199
200/**
201 * Create a meeting time for a class
202 */
203export function createMeetingTime(classId: string, label: string): MeetingTime {
204 const id = nanoid();
205 const now = Math.floor(Date.now() / 1000);
206
207 db.run(
208 "INSERT INTO meeting_times (id, class_id, label, created_at) VALUES (?, ?, ?, ?)",
209 [id, classId, label, now],
210 );
211
212 return {
213 id,
214 class_id: classId,
215 label,
216 created_at: now,
217 };
218}
219
220/**
221 * Get all meeting times for a class
222 */
223export function getMeetingTimesForClass(classId: string): MeetingTime[] {
224 return db
225 .query<MeetingTime, [string]>(
226 "SELECT * FROM meeting_times WHERE class_id = ? ORDER BY created_at ASC",
227 )
228 .all(classId);
229}
230
231/**
232 * Update a meeting time label
233 */
234export function updateMeetingTime(meetingId: string, label: string): void {
235 db.run("UPDATE meeting_times SET label = ? WHERE id = ?", [label, meetingId]);
236}
237
238/**
239 * Delete a meeting time
240 */
241export function deleteMeetingTime(meetingId: string): void {
242 db.run("DELETE FROM meeting_times WHERE id = ?", [meetingId]);
243}
244
245/**
246 * Get transcriptions for a class
247 */
248export function getTranscriptionsForClass(classId: string) {
249 return db
250 .query<
251 {
252 id: string;
253 user_id: number;
254 meeting_time_id: string | null;
255 filename: string;
256 original_filename: string;
257 status: string;
258 progress: number;
259 error_message: string | null;
260 created_at: number;
261 updated_at: number;
262 },
263 [string]
264 >(
265 `SELECT id, user_id, meeting_time_id, filename, original_filename, status, progress, error_message, created_at, updated_at
266 FROM transcriptions
267 WHERE class_id = ?
268 ORDER BY created_at DESC`,
269 )
270 .all(classId);
271}
272
273/**
274 * Search for classes by course code
275 */
276export function searchClassesByCourseCode(courseCode: string): Class[] {
277 return db
278 .query<Class, [string]>(
279 `SELECT * FROM classes
280 WHERE UPPER(course_code) LIKE UPPER(?)
281 AND archived = 0
282 ORDER BY year DESC, semester DESC, professor ASC`,
283 )
284 .all(`%${courseCode}%`);
285}
286
287/**
288 * Join a class by class ID
289 */
290export function joinClass(
291 classId: string,
292 userId: number,
293): { success: boolean; error?: string } {
294 // Find class by ID
295 const cls = db
296 .query<Class, [string]>("SELECT * FROM classes WHERE id = ?")
297 .get(classId);
298
299 if (!cls) {
300 return { success: false, error: "Class not found" };
301 }
302
303 if (cls.archived) {
304 return { success: false, error: "This class is archived" };
305 }
306
307 // Check if already enrolled
308 const existing = db
309 .query<ClassMember, [string, number]>(
310 "SELECT * FROM class_members WHERE class_id = ? AND user_id = ?",
311 )
312 .get(cls.id, userId);
313
314 if (existing) {
315 return { success: false, error: "Already enrolled in this class" };
316 }
317
318 // Enroll user
319 db.query(
320 "INSERT INTO class_members (class_id, user_id, enrolled_at) VALUES (?, ?, ?)",
321 ).run(cls.id, userId, Math.floor(Date.now() / 1000));
322
323 return { success: true };
324}
325
326/**
327 * Waitlist entry interface
328 */
329export interface WaitlistEntry {
330 id: string;
331 user_id: number;
332 course_code: string;
333 course_name: string;
334 professor: string;
335 semester: string;
336 year: number;
337 additional_info: string | null;
338 meeting_times: string | null;
339 created_at: number;
340}
341
342/**
343 * Add a class to the waitlist
344 */
345export function addToWaitlist(
346 userId: number,
347 courseCode: string,
348 courseName: string,
349 professor: string,
350 semester: string,
351 year: number,
352 additionalInfo: string | null,
353 meetingTimes: string[] | null,
354): string {
355 const id = nanoid();
356 const meetingTimesJson = meetingTimes ? JSON.stringify(meetingTimes) : null;
357
358 db.query(
359 `INSERT INTO class_waitlist
360 (id, user_id, course_code, course_name, professor, semester, year, additional_info, meeting_times, created_at)
361 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
362 ).run(
363 id,
364 userId,
365 courseCode,
366 courseName,
367 professor,
368 semester,
369 year,
370 additionalInfo,
371 meetingTimesJson,
372 Math.floor(Date.now() / 1000),
373 );
374 return id;
375}
376
377/**
378 * Get all waitlist entries
379 */
380export function getAllWaitlistEntries(): WaitlistEntry[] {
381 return db
382 .query<WaitlistEntry, []>(
383 "SELECT * FROM class_waitlist ORDER BY created_at DESC",
384 )
385 .all();
386}
387
388/**
389 * Delete a waitlist entry
390 */
391export function deleteWaitlistEntry(id: string): void {
392 db.query("DELETE FROM class_waitlist WHERE id = ?").run(id);
393}