馃 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(
32 userId: number,
33 isAdmin: boolean,
34): Class[] {
35 if (isAdmin) {
36 return db
37 .query<Class, []>(
38 "SELECT * FROM classes ORDER BY year DESC, semester DESC, course_code ASC",
39 )
40 .all();
41 }
42
43 return db
44 .query<Class, [number]>(
45 `SELECT c.* FROM classes c
46 INNER JOIN class_members cm ON c.id = cm.class_id
47 WHERE cm.user_id = ?
48 ORDER BY c.year DESC, c.semester DESC, c.course_code ASC`,
49 )
50 .all(userId);
51}
52
53/**
54 * Get a single class by ID
55 */
56export function getClassById(classId: string): Class | null {
57 const result = db
58 .query<Class, [string]>("SELECT * FROM classes WHERE id = ?")
59 .get(classId);
60 return result ?? null;
61}
62
63/**
64 * Check if user is enrolled in a class
65 */
66export function isUserEnrolledInClass(
67 userId: number,
68 classId: string,
69): boolean {
70 const result = db
71 .query<{ count: number }, [string, number]>(
72 "SELECT COUNT(*) as count FROM class_members WHERE class_id = ? AND user_id = ?",
73 )
74 .get(classId, userId);
75 return (result?.count ?? 0) > 0;
76}
77
78/**
79 * Create a new class
80 */
81export function createClass(data: {
82 course_code: string;
83 name: string;
84 professor: string;
85 semester: string;
86 year: number;
87}): Class {
88 const id = nanoid();
89 const now = Math.floor(Date.now() / 1000);
90
91 db.run(
92 "INSERT INTO classes (id, course_code, name, professor, semester, year, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
93 [
94 id,
95 data.course_code,
96 data.name,
97 data.professor,
98 data.semester,
99 data.year,
100 now,
101 ],
102 );
103
104 return {
105 id,
106 course_code: data.course_code,
107 name: data.name,
108 professor: data.professor,
109 semester: data.semester,
110 year: data.year,
111 archived: false,
112 created_at: now,
113 };
114}
115
116/**
117 * Archive or unarchive a class
118 */
119export function toggleClassArchive(classId: string, archived: boolean): void {
120 db.run("UPDATE classes SET archived = ? WHERE id = ?", [
121 archived ? 1 : 0,
122 classId,
123 ]);
124}
125
126/**
127 * Delete a class (cascades to members, meeting times, and transcriptions)
128 */
129export function deleteClass(classId: string): void {
130 db.run("DELETE FROM classes WHERE id = ?", [classId]);
131}
132
133/**
134 * Enroll a user in a class
135 */
136export function enrollUserInClass(userId: number, classId: string): void {
137 const now = Math.floor(Date.now() / 1000);
138 db.run(
139 "INSERT OR IGNORE INTO class_members (class_id, user_id, enrolled_at) VALUES (?, ?, ?)",
140 [classId, userId, now],
141 );
142}
143
144/**
145 * Remove a user from a class
146 */
147export function removeUserFromClass(userId: number, classId: string): void {
148 db.run("DELETE FROM class_members WHERE class_id = ? AND user_id = ?", [
149 classId,
150 userId,
151 ]);
152}
153
154/**
155 * Get all members of a class
156 */
157export function getClassMembers(classId: string) {
158 return db
159 .query<
160 {
161 user_id: number;
162 email: string;
163 name: string | null;
164 avatar: string;
165 enrolled_at: number;
166 },
167 [string]
168 >(
169 `SELECT cm.user_id, u.email, u.name, u.avatar, cm.enrolled_at
170 FROM class_members cm
171 INNER JOIN users u ON cm.user_id = u.id
172 WHERE cm.class_id = ?
173 ORDER BY cm.enrolled_at DESC`,
174 )
175 .all(classId);
176}
177
178/**
179 * Create a meeting time for a class
180 */
181export function createMeetingTime(classId: string, label: string): MeetingTime {
182 const id = nanoid();
183 const now = Math.floor(Date.now() / 1000);
184
185 db.run(
186 "INSERT INTO meeting_times (id, class_id, label, created_at) VALUES (?, ?, ?, ?)",
187 [id, classId, label, now],
188 );
189
190 return {
191 id,
192 class_id: classId,
193 label,
194 created_at: now,
195 };
196}
197
198/**
199 * Get all meeting times for a class
200 */
201export function getMeetingTimesForClass(classId: string): MeetingTime[] {
202 return db
203 .query<MeetingTime, [string]>(
204 "SELECT * FROM meeting_times WHERE class_id = ? ORDER BY created_at ASC",
205 )
206 .all(classId);
207}
208
209/**
210 * Update a meeting time label
211 */
212export function updateMeetingTime(meetingId: string, label: string): void {
213 db.run("UPDATE meeting_times SET label = ? WHERE id = ?", [label, meetingId]);
214}
215
216/**
217 * Delete a meeting time
218 */
219export function deleteMeetingTime(meetingId: string): void {
220 db.run("DELETE FROM meeting_times WHERE id = ?", [meetingId]);
221}
222
223/**
224 * Get transcriptions for a class
225 */
226export function getTranscriptionsForClass(classId: string) {
227 return db
228 .query<
229 {
230 id: string;
231 user_id: number;
232 meeting_time_id: string | null;
233 filename: string;
234 original_filename: string;
235 status: string;
236 progress: number;
237 error_message: string | null;
238 created_at: number;
239 updated_at: number;
240 },
241 [string]
242 >(
243 `SELECT id, user_id, meeting_time_id, filename, original_filename, status, progress, error_message, created_at, updated_at
244 FROM transcriptions
245 WHERE class_id = ?
246 ORDER BY created_at DESC`,
247 )
248 .all(classId);
249}