馃 distributed transcription service
thistle.dunkirk.sh
dunkirk.sh
1import { nanoid } from "nanoid";
2import db from "../db/schema";
3
4/**
5 * Vote for a recording
6 * Returns true if vote was recorded, false if already voted
7 */
8export function voteForRecording(
9 transcriptionId: string,
10 userId: number,
11): boolean {
12 try {
13 const voteId = nanoid();
14 db.run(
15 "INSERT INTO recording_votes (id, transcription_id, user_id) VALUES (?, ?, ?)",
16 [voteId, transcriptionId, userId],
17 );
18
19 // Increment vote count on transcription
20 db.run(
21 "UPDATE transcriptions SET vote_count = vote_count + 1 WHERE id = ?",
22 [transcriptionId],
23 );
24
25 return true;
26 } catch (error) {
27 // Unique constraint violation means user already voted
28 if (
29 error instanceof Error &&
30 error.message.includes("UNIQUE constraint failed")
31 ) {
32 return false;
33 }
34 throw error;
35 }
36}
37
38/**
39 * Remove vote for a recording
40 */
41export function removeVote(transcriptionId: string, userId: number): boolean {
42 const result = db.run(
43 "DELETE FROM recording_votes WHERE transcription_id = ? AND user_id = ?",
44 [transcriptionId, userId],
45 );
46
47 if (result.changes > 0) {
48 // Decrement vote count on transcription
49 db.run(
50 "UPDATE transcriptions SET vote_count = vote_count - 1 WHERE id = ?",
51 [transcriptionId],
52 );
53 return true;
54 }
55
56 return false;
57}
58
59/**
60 * Get user's vote for a specific class meeting time
61 */
62export function getUserVoteForMeeting(
63 userId: number,
64 classId: string,
65 meetingTimeId: string,
66): string | null {
67 const result = db
68 .query<
69 { transcription_id: string },
70 [number, string, string]
71 >(
72 `SELECT rv.transcription_id
73 FROM recording_votes rv
74 JOIN transcriptions t ON rv.transcription_id = t.id
75 WHERE rv.user_id = ?
76 AND t.class_id = ?
77 AND t.meeting_time_id = ?
78 AND t.status = 'pending'`,
79 )
80 .get(userId, classId, meetingTimeId);
81
82 return result?.transcription_id || null;
83}
84
85/**
86 * Get all pending recordings for a class meeting time (filtered by section)
87 */
88export function getPendingRecordings(
89 classId: string,
90 meetingTimeId: string,
91 sectionId?: string | null,
92) {
93 // Build query based on whether section filtering is needed
94 let query = `SELECT id, user_id, filename, original_filename, vote_count, created_at, section_id
95 FROM transcriptions
96 WHERE class_id = ?
97 AND meeting_time_id = ?
98 AND status = 'pending'`;
99
100 const params: (string | null)[] = [classId, meetingTimeId];
101
102 // Filter by section if provided (for voting - section-specific)
103 if (sectionId !== undefined) {
104 query += " AND (section_id = ? OR section_id IS NULL)";
105 params.push(sectionId);
106 }
107
108 query += " ORDER BY vote_count DESC, created_at ASC";
109
110 return db
111 .query<
112 {
113 id: string;
114 user_id: number;
115 filename: string;
116 original_filename: string;
117 vote_count: number;
118 created_at: number;
119 section_id: string | null;
120 },
121 (string | null)[]
122 >(query)
123 .all(...params);
124}
125
126/**
127 * Get total enrolled users count for a class
128 */
129export function getEnrolledUserCount(classId: string): number {
130 const result = db
131 .query<{ count: number }, [string]>(
132 "SELECT COUNT(*) as count FROM class_members WHERE class_id = ?",
133 )
134 .get(classId);
135
136 return result?.count || 0;
137}
138
139/**
140 * Check if recording should be auto-submitted
141 * Returns winning recording ID if ready, null otherwise
142 */
143export function checkAutoSubmit(
144 classId: string,
145 meetingTimeId: string,
146 sectionId?: string | null,
147): string | null {
148 const recordings = getPendingRecordings(classId, meetingTimeId, sectionId);
149
150 if (recordings.length === 0) {
151 return null;
152 }
153
154 const totalUsers = getEnrolledUserCount(classId);
155 const now = Date.now() / 1000; // Current time in seconds
156
157 // Get the recording with most votes
158 const topRecording = recordings[0];
159 if (!topRecording) return null;
160
161 const uploadedAt = topRecording.created_at;
162 const timeSinceUpload = now - uploadedAt;
163
164 // Auto-submit if:
165 // 1. 30 minutes have passed since first upload, OR
166 // 2. 40% of enrolled users have voted for the top recording
167 const thirtyMinutes = 30 * 60; // 30 minutes in seconds
168 const voteThreshold = Math.ceil(totalUsers * 0.4);
169
170 if (timeSinceUpload >= thirtyMinutes) {
171 console.log(
172 `[Voting] Auto-submitting ${topRecording.id} - 30 minutes elapsed`,
173 );
174 return topRecording.id;
175 }
176
177 if (topRecording.vote_count >= voteThreshold) {
178 console.log(
179 `[Voting] Auto-submitting ${topRecording.id} - reached ${topRecording.vote_count}/${voteThreshold} votes (40% threshold)`,
180 );
181 return topRecording.id;
182 }
183
184 return null;
185}
186
187/**
188 * Mark a recording as auto-submitted and start transcription
189 */
190export function markAsAutoSubmitted(transcriptionId: string): void {
191 db.run(
192 "UPDATE transcriptions SET auto_submitted = 1 WHERE id = ?",
193 [transcriptionId],
194 );
195}
196
197/**
198 * Delete a pending recording (only allowed by uploader or admin)
199 */
200export function deletePendingRecording(
201 transcriptionId: string,
202 userId: number,
203 isAdmin: boolean,
204): boolean {
205 // Check ownership if not admin
206 if (!isAdmin) {
207 const recording = db
208 .query<{ user_id: number; status: string }, [string]>(
209 "SELECT user_id, status FROM transcriptions WHERE id = ?",
210 )
211 .get(transcriptionId);
212
213 if (!recording || recording.user_id !== userId) {
214 return false;
215 }
216
217 // Only allow deleting pending recordings
218 if (recording.status !== "pending") {
219 return false;
220 }
221 }
222
223 // Delete the recording (cascades to votes)
224 db.run("DELETE FROM transcriptions WHERE id = ?", [transcriptionId]);
225
226 return true;
227}