1use std::collections::HashSet;
2
3use futures::{Stream, future::BoxFuture};
4use serde_json::Value;
5use url::Url;
6
7use crate::{
8 CourseOrExamId,
9 exercise_service_info::{self, ExerciseServiceInfoApi},
10 exercise_services, exercise_slide_submissions,
11 exercise_tasks::{CourseMaterialExerciseTask, ExerciseTask},
12 library::custom_view_exercises::{CustomViewExerciseTaskSubmission, CustomViewExerciseTasks},
13 peer_or_self_review_question_submissions::PeerOrSelfReviewQuestionSubmission,
14 peer_or_self_review_questions::PeerOrSelfReviewQuestion,
15 peer_or_self_review_submissions::PeerOrSelfReviewSubmission,
16 prelude::*,
17};
18
19#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
20#[cfg_attr(feature = "ts_rs", derive(TS))]
21pub struct ExerciseTaskSubmission {
22 pub id: Uuid,
23 pub created_at: DateTime<Utc>,
24 pub updated_at: DateTime<Utc>,
25 pub deleted_at: Option<DateTime<Utc>>,
26 pub exercise_slide_submission_id: Uuid,
27 pub exercise_task_id: Uuid,
28 pub exercise_slide_id: Uuid,
29 pub data_json: Option<serde_json::Value>,
30 pub exercise_task_grading_id: Option<Uuid>,
31 pub metadata: Option<serde_json::Value>,
32}
33
34#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
35#[cfg_attr(feature = "ts_rs", derive(TS))]
36pub struct PeerOrSelfReviewsReceived {
37 pub peer_or_self_review_questions: Vec<PeerOrSelfReviewQuestion>,
38 pub peer_or_self_review_question_submissions: Vec<PeerOrSelfReviewQuestionSubmission>,
39 pub peer_or_self_review_submissions: Vec<PeerOrSelfReviewSubmission>,
40}
41
42#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
43#[cfg_attr(feature = "ts_rs", derive(TS))]
44pub struct SubmissionData {
45 pub exercise_id: Uuid,
46 pub course_id: Uuid,
47 pub exercise_slide_submission_id: Uuid,
48 pub exercise_slide_id: Uuid,
49 pub exercise_task_id: Uuid,
50 pub user_id: Uuid,
51 pub course_instance_id: Uuid,
52 pub data_json: Value,
53 pub id: Uuid,
54}
55
56#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
57#[cfg_attr(feature = "ts_rs", derive(TS))]
58pub struct ExportedSubmission {
59 pub id: Uuid,
60 pub user_id: Uuid,
61 pub created_at: DateTime<Utc>,
62 pub exercise_id: Uuid,
63 pub exercise_task_id: Uuid,
64 pub score_given: Option<f32>,
65 pub data_json: Option<serde_json::Value>,
66}
67
68#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
69#[cfg_attr(feature = "ts_rs", derive(TS))]
70pub struct ExportedCourseSubmission {
71 pub exercise_slide_submission_id: Uuid,
72 pub id: Uuid,
73 pub user_id: Uuid,
74 pub created_at: DateTime<Utc>,
75 pub course_instance_id: Option<Uuid>,
76 pub exercise_id: Uuid,
77 pub exercise_task_id: Uuid,
78 pub score_given: Option<f32>,
79 pub data_json: Option<serde_json::Value>,
80}
81
82pub async fn get_submission(
83 conn: &mut PgConnection,
84 submission_id: Uuid,
85) -> ModelResult<ExerciseTaskSubmission> {
86 let res = sqlx::query_as!(
87 ExerciseTaskSubmission,
88 "
89SELECT *
90FROM exercise_task_submissions
91WHERE id = $1
92",
93 submission_id
94 )
95 .fetch_one(conn)
96 .await?;
97 Ok(res)
98}
99
100pub async fn insert_with_id(
102 conn: &mut PgConnection,
103 submission_data: &SubmissionData,
104) -> ModelResult<Uuid> {
105 let res = sqlx::query!(
106 "
107INSERT INTO exercise_task_submissions (
108 id,
109 exercise_slide_submission_id,
110 exercise_slide_id,
111 exercise_task_id,
112 data_json
113 )
114VALUES ($1, $2, $3, $4, $5)
115RETURNING id
116 ",
117 submission_data.id,
118 submission_data.exercise_slide_submission_id,
119 submission_data.exercise_slide_id,
120 submission_data.exercise_task_id,
121 submission_data.data_json,
122 )
123 .fetch_one(conn)
124 .await?;
125 Ok(res.id)
126}
127
128pub async fn insert(
129 conn: &mut PgConnection,
130 pkey_policy: PKeyPolicy<Uuid>,
131 exercise_slide_submission_id: Uuid,
132 exercise_slide_id: Uuid,
133 exercise_task_id: Uuid,
134 data_json: &Value,
135) -> ModelResult<Uuid> {
136 let res = sqlx::query!(
137 "
138INSERT INTO exercise_task_submissions (
139 id,
140 exercise_slide_submission_id,
141 exercise_slide_id,
142 exercise_task_id,
143 data_json
144 )
145 VALUES ($1, $2, $3, $4, $5)
146 RETURNING id
147 ",
148 pkey_policy.into_uuid(),
149 exercise_slide_submission_id,
150 exercise_slide_id,
151 exercise_task_id,
152 data_json,
153 )
154 .fetch_one(conn)
155 .await?;
156 Ok(res.id)
157}
158
159pub async fn get_by_id(conn: &mut PgConnection, id: Uuid) -> ModelResult<ExerciseTaskSubmission> {
160 let submission = sqlx::query_as!(
161 ExerciseTaskSubmission,
162 "
163SELECT *
164FROM exercise_task_submissions
165WHERE id = $1
166",
167 id
168 )
169 .fetch_one(conn)
170 .await?;
171 Ok(submission)
172}
173
174pub async fn get_by_exercise_slide_submission_id(
175 conn: &mut PgConnection,
176 exercise_slide_submission_id: Uuid,
177) -> ModelResult<Vec<ExerciseTaskSubmission>> {
178 let submissions = sqlx::query_as!(
179 ExerciseTaskSubmission,
180 "
181SELECT *
182FROM exercise_task_submissions
183WHERE exercise_slide_submission_id = $1
184 AND deleted_at IS NULL
185 ",
186 exercise_slide_submission_id
187 )
188 .fetch_all(conn)
189 .await?;
190 Ok(submissions)
191}
192
193pub async fn get_users_latest_exercise_task_submissions_for_exercise_slide(
194 conn: &mut PgConnection,
195 exercise_slide_id: Uuid,
196 user_id: Uuid,
197) -> ModelResult<Option<Vec<ExerciseTaskSubmission>>> {
198 let exercise_slide_submission =
199 exercise_slide_submissions::try_to_get_users_latest_exercise_slide_submission(
200 conn,
201 exercise_slide_id,
202 user_id,
203 )
204 .await?;
205 if let Some(exercise_slide_submission) = exercise_slide_submission {
206 let task_submissions = sqlx::query_as!(
207 ExerciseTaskSubmission,
208 "
209SELECT *
210FROM exercise_task_submissions
211WHERE exercise_slide_submission_id = $1
212 AND deleted_at IS NULL
213 ",
214 exercise_slide_submission.id
215 )
216 .fetch_all(conn)
217 .await?;
218 Ok(Some(task_submissions))
219 } else {
220 Ok(None)
221 }
222}
223
224pub async fn get_course_and_exam_id(
225 conn: &mut PgConnection,
226 id: Uuid,
227) -> ModelResult<CourseOrExamId> {
228 let res = sqlx::query!(
229 "
230SELECT ess.course_id,
231 ess.exam_id
232FROM exercise_task_submissions ets
233 JOIN exercise_slide_submissions ess ON ets.exercise_slide_submission_id = ess.id
234WHERE ets.id = $1
235 AND ets.deleted_at IS NULL
236 AND ess.deleted_at IS NULL
237 ",
238 id
239 )
240 .fetch_one(conn)
241 .await?;
242 CourseOrExamId::from(res.course_id, res.exam_id)
243}
244
245pub async fn get_peer_reviews_received(
246 conn: &mut PgConnection,
247 exercise_id: Uuid,
248 exercise_slide_submission_id: Uuid,
249 user_id: Uuid,
250) -> ModelResult<PeerOrSelfReviewsReceived> {
251 let exercise = crate::exercises::get_by_id(&mut *conn, exercise_id).await?;
252 let peer_or_self_review_config =
253 crate::peer_or_self_review_configs::get_by_exercise_or_course_id(
254 &mut *conn,
255 &exercise,
256 exercise.course_id.ok_or_else(|| {
257 ModelError::new(
258 ModelErrorType::InvalidRequest,
259 "Peer reviews work only on courses (and not, for example, on exams)"
260 .to_string(),
261 None,
262 )
263 })?,
264 )
265 .await?;
266 let peer_or_self_review_questions =
267 crate::peer_or_self_review_questions::get_by_peer_or_self_review_configs_id(
268 &mut *conn,
269 peer_or_self_review_config.id,
270 )
271 .await?;
272
273 let peer_or_self_review_question_ids = peer_or_self_review_questions
274 .iter()
275 .map(|x| (x.id))
276 .collect::<Vec<_>>();
277
278 let peer_or_self_review_submissions =
279 crate::peer_or_self_review_submissions::get_received_peer_or_self_review_submissions_for_user_by_peer_or_self_review_config_id_and_exercise_slide_submission(
280 &mut *conn,
281 user_id,
282 exercise_slide_submission_id,
283 peer_or_self_review_config.id,
284 )
285 .await?;
286
287 let peer_or_self_review_question_submissions =
288 crate::peer_or_self_review_question_submissions::get_by_peer_reviews_question_ids(
289 &mut *conn,
290 &peer_or_self_review_question_ids,
291 user_id,
292 exercise_slide_submission_id,
293 )
294 .await?;
295
296 Ok(PeerOrSelfReviewsReceived {
297 peer_or_self_review_questions,
298 peer_or_self_review_question_submissions,
299 peer_or_self_review_submissions,
300 })
301}
302
303pub async fn set_grading_id(
304 conn: &mut PgConnection,
305 grading_id: Uuid,
306 submission_id: Uuid,
307) -> ModelResult<ExerciseTaskSubmission> {
308 let res = sqlx::query_as!(
309 ExerciseTaskSubmission,
310 "
311UPDATE exercise_task_submissions
312SET exercise_task_grading_id = $1
313WHERE id = $2
314RETURNING *
315",
316 grading_id,
317 submission_id
318 )
319 .fetch_one(conn)
320 .await?;
321 Ok(res)
322}
323
324pub fn stream_exam_submissions(
325 conn: &mut PgConnection,
326 exam_id: Uuid,
327) -> impl Stream<Item = sqlx::Result<ExportedSubmission>> + '_ {
328 sqlx::query_as!(
329 ExportedSubmission,
330 "
331SELECT exercise_task_submissions.id,
332 user_id,
333 exercise_task_submissions.created_at,
334 exercise_slide_submissions.exercise_id,
335 exercise_task_submissions.exercise_task_id,
336 exercise_task_gradings.score_given,
337 exercise_task_submissions.data_json
338FROM exercise_task_submissions
339 JOIN exercise_slide_submissions ON exercise_task_submissions.exercise_slide_submission_id = exercise_slide_submissions.id
340 JOIN exercise_task_gradings on exercise_task_submissions.exercise_task_grading_id = exercise_task_gradings.id
341 JOIN exercises on exercise_slide_submissions.exercise_id = exercises.id
342WHERE exercise_slide_submissions.exam_id = $1
343 AND exercise_task_submissions.deleted_at IS NULL
344 AND exercise_task_gradings.deleted_at IS NULL
345 AND exercises.deleted_at IS NULL;
346 ",
347 exam_id
348 )
349 .fetch(conn)
350}
351
352pub fn stream_course_submissions(
353 conn: &mut PgConnection,
354 course_id: Uuid,
355) -> impl Stream<Item = sqlx::Result<ExportedCourseSubmission>> + '_ {
356 sqlx::query_as!(
357 ExportedCourseSubmission,
358 "
359SELECT exercise_task_submissions.exercise_slide_submission_id,
360 exercise_task_submissions.id,
361 user_id,
362 exercise_task_submissions.created_at,
363 exercise_slide_submissions.course_instance_id,
364 exercise_slide_submissions.exercise_id,
365 exercise_task_submissions.exercise_task_id,
366 exercise_task_gradings.score_given,
367 exercise_task_submissions.data_json
368FROM exercise_task_submissions
369 JOIN exercise_slide_submissions ON exercise_task_submissions.exercise_slide_submission_id = exercise_slide_submissions.id
370 JOIN exercise_task_gradings ON exercise_task_submissions.exercise_task_grading_id = exercise_task_gradings.id
371 JOIN exercises ON exercise_slide_submissions.exercise_id = exercises.id
372WHERE exercise_slide_submissions.course_id = $1
373 AND exercise_slide_submissions.deleted_at IS NULL
374 AND exercise_task_submissions.deleted_at IS NULL
375 AND exercise_task_gradings.deleted_at IS NULL
376 AND exercises.deleted_at IS NULL;
377 ",
378 course_id
379 )
380 .fetch(conn)
381}
382
383pub async fn get_exercise_task_submission_info_by_exercise_slide_submission_id(
385 conn: &mut PgConnection,
386 exercise_slide_submission_id: Uuid,
387 viewer_user_id: Uuid,
388 fetch_service_info: impl Fn(Url) -> BoxFuture<'static, ModelResult<ExerciseServiceInfoApi>>,
389) -> ModelResult<Vec<CourseMaterialExerciseTask>> {
390 let task_submisssions = crate::exercise_task_submissions::get_by_exercise_slide_submission_id(
391 &mut *conn,
392 exercise_slide_submission_id,
393 )
394 .await?;
395 let exercise_task_gradings =
396 crate::exercise_task_gradings::get_all_gradings_by_exercise_slide_submission_id(
397 &mut *conn,
398 exercise_slide_submission_id,
399 )
400 .await?;
401
402 let exercise_tasks = crate::exercise_tasks::get_exercise_tasks_by_exercise_slide_id::<
403 Vec<ExerciseTask>,
404 >(&mut *conn, &task_submisssions[0].exercise_slide_id)
405 .await?;
406 let mut res = Vec::with_capacity(task_submisssions.len());
407
408 let unique_exercise_service_slugs = exercise_tasks
409 .iter()
410 .cloned()
411 .map(|et| et.exercise_type)
412 .collect::<HashSet<_>>()
413 .into_iter()
414 .collect::<Vec<_>>();
415 let exercise_service_slug_to_service_and_info =
416 exercise_service_info::get_selected_exercise_services_by_type(
417 &mut *conn,
418 &unique_exercise_service_slugs,
419 fetch_service_info,
420 )
421 .await?;
422
423 for ts in task_submisssions {
424 let grading = exercise_task_gradings
425 .iter()
426 .find(|g| Some(g.id) == ts.exercise_task_grading_id)
427 .ok_or_else(|| {
428 ModelError::new(
429 ModelErrorType::NotFound,
430 "Grading not found".to_string(),
431 None,
432 )
433 })?;
434 let task = exercise_tasks
435 .iter()
436 .find(|t| t.id == ts.exercise_task_id)
437 .ok_or_else(|| {
438 ModelError::new(
439 ModelErrorType::NotFound,
440 "Exercise task not found".to_string(),
441 None,
442 )
443 })?;
444 let (exercise_service, service_info) = exercise_service_slug_to_service_and_info
445 .get(&task.exercise_type)
446 .ok_or_else(|| {
447 ModelError::new(
448 ModelErrorType::InvalidRequest,
449 "Exercise service not found".to_string(),
450 None,
451 )
452 })?;
453 let mut exercise_iframe_url =
454 exercise_services::get_exercise_service_externally_preferred_baseurl(exercise_service)?;
455 exercise_iframe_url.set_path(&service_info.user_interface_iframe_path);
456 let course_material_exercise_task = CourseMaterialExerciseTask {
457 id: task.id,
458 exercise_service_slug: task.exercise_type.clone(),
459 exercise_slide_id: task.exercise_slide_id,
460 exercise_iframe_url: Some(exercise_iframe_url.to_string()),
461 pseudonumous_user_id: Some(Uuid::new_v5(
462 &service_info.exercise_service_id,
463 viewer_user_id.as_bytes(),
464 )),
465 assignment: task.assignment.clone(),
466 public_spec: task.public_spec.clone(),
467 model_solution_spec: task.model_solution_spec.clone(),
468 previous_submission: Some(ts),
469 previous_submission_grading: Some(grading.clone()),
470 order_number: task.order_number,
471 };
472 res.push(course_material_exercise_task);
473 }
474 Ok(res)
475}
476
477pub async fn get_user_custom_view_exercise_tasks_by_module_and_exercise_type(
478 conn: &mut PgConnection,
479 exercise_type: &str,
480 course_module_id: Uuid,
481 user_id: Uuid,
482 course_instance_id: Uuid,
483) -> ModelResult<CustomViewExerciseTasks> {
484 let task_submissions =
485 crate::exercise_task_submissions::get_user_latest_exercise_task_submissions_by_course_module_and_exercise_type(
486 &mut *conn,
487 user_id,
488 exercise_type,
489 course_module_id,
490 course_instance_id,
491 )
492 .await?;
493 let task_gradings =
494 crate::exercise_task_gradings::get_user_exercise_task_gradings_by_module_and_exercise_type(
495 &mut *conn,
496 user_id,
497 exercise_type,
498 course_module_id,
499 course_instance_id,
500 )
501 .await?;
502
503 let exercise_tasks = crate::exercise_tasks::get_all_exercise_tasks_by_module_and_exercise_type(
504 &mut *conn,
505 exercise_type,
506 course_module_id,
507 )
508 .await?;
509 let res: CustomViewExerciseTasks = CustomViewExerciseTasks {
510 exercise_tasks,
511 task_submissions,
512 task_gradings,
513 };
514 Ok(res)
515}
516
517pub async fn get_user_latest_exercise_task_submissions_by_course_module_and_exercise_type(
519 conn: &mut PgConnection,
520 user_id: Uuid,
521 exercise_type: &str,
522 module_id: Uuid,
523 course_instance_id: Uuid,
524) -> ModelResult<Vec<CustomViewExerciseTaskSubmission>> {
525 let res: Vec<CustomViewExerciseTaskSubmission> = sqlx::query_as!(
526 CustomViewExerciseTaskSubmission,
527 r#"
528 SELECT DISTINCT ON (g.exercise_task_id)
529 g.id,
530 g.created_at,
531 g.exercise_slide_submission_id,
532 g.exercise_slide_id,
533 g.exercise_task_id,
534 g.exercise_task_grading_id,
535 g.data_json
536 FROM exercise_task_submissions g
537 JOIN exercise_tasks et ON et.id = g.exercise_task_id
538 JOIN exercise_slide_submissions ess ON ess.id = g.exercise_slide_submission_id
539 JOIN exercises e ON e.id = ess.exercise_id
540 JOIN chapters c ON c.id = e.chapter_id
541 WHERE ess.user_id = $1
542 AND ess.course_instance_id = $2
543 AND et.exercise_type = $3
544 AND c.course_module_id = $4
545 AND g.deleted_at IS NULL
546 AND et.deleted_at IS NULL
547 AND ess.deleted_at IS NULL
548 AND e.deleted_at IS NULL
549 AND c.deleted_at IS NULL
550 ORDER BY g.exercise_task_id, g.created_at DESC
551 "#,
552 user_id,
553 course_instance_id,
554 exercise_type,
555 module_id
556 )
557 .fetch_all(conn)
558 .await?;
559 Ok(res)
560}
561
562pub async fn get_ids_by_exercise_id(
563 conn: &mut PgConnection,
564 exercise_id: Uuid,
565) -> ModelResult<Vec<Uuid>> {
566 let res = sqlx::query!(
567 "
568SELECT id
569FROM exercise_task_submissions
570WHERE exercise_slide_submission_id IN (
571 SELECT id
572 FROM exercise_slide_submissions
573 WHERE exercise_id = $1
574)
575AND deleted_at IS NULL
576",
577 &exercise_id
578 )
579 .fetch_all(conn)
580 .await?;
581 Ok(res.iter().map(|x| x.id).collect())
582}
583
584pub async fn get_latest_submission_ids_by_exercise_id(
586 conn: &mut PgConnection,
587 exercise_id: Uuid,
588) -> ModelResult<Vec<Uuid>> {
589 let res = sqlx::query!(
590 "
591SELECT id
592FROM exercise_task_submissions
593WHERE exercise_slide_submission_id IN (SELECT id
594 FROM (SELECT DISTINCT ON (user_id, exercise_id) *
595 FROM exercise_slide_submissions
596 WHERE exercise_id = $1
597 AND deleted_at IS NULL
598 ORDER BY user_id, exercise_id, created_at DESC) a )
599 AND deleted_at IS NULL
600",
601 &exercise_id
602 )
603 .fetch_all(conn)
604 .await?;
605 Ok(res.iter().map(|x| x.id).collect())
606}