headless_lms_models/

exercise_slide_submissions.rs

use std::collections::HashMap;

use chrono::NaiveDate;
use futures::future::BoxFuture;
use rand::prelude::SliceRandom;
use url::Url;
use utoipa::ToSchema;

use crate::{
    CourseOrExamId,
    courses::Course,
    exams::{self, ExamEnrollment},
    exercise_service_info::ExerciseServiceInfoApi,
    exercise_task_gradings::UserPointsUpdateStrategy,
    exercise_task_submissions::{AnswerData, StoredAnswer, attach_answer_data},
    exercise_tasks::CourseMaterialExerciseTask,
    exercises::{self, Exercise, GradingProgress},
    prelude::*,
    teacher_grading_decisions::{self, TeacherGradingDecision},
    user_exercise_states::{self, UserExerciseState},
};

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]

pub struct AnswerRequiringAttention {
    pub id: Uuid,
    pub user_id: Uuid,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
    pub deleted_at: Option<DateTime<Utc>>,
    pub answer: Option<AnswerData>,
    pub grading_progress: GradingProgress,
    pub score_given: Option<f32>,
    pub submission_id: Uuid,
    pub exercise_id: Uuid,
}
#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]

pub struct NewExerciseSlideSubmission {
    pub exercise_slide_id: Uuid,
    pub course_id: Option<Uuid>,
    pub exam_id: Option<Uuid>,
    pub user_id: Uuid,
    pub exercise_id: Uuid,
    pub user_points_update_strategy: UserPointsUpdateStrategy,
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]

pub struct ExerciseSlideSubmission {
    pub id: Uuid,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
    pub deleted_at: Option<DateTime<Utc>>,
    pub exercise_slide_id: Uuid,
    pub course_id: Option<Uuid>,
    pub exam_id: Option<Uuid>,
    pub exercise_id: Uuid,
    pub user_id: Uuid,
    pub user_points_update_strategy: UserPointsUpdateStrategy,
    pub flag_count: Option<i32>,
}

impl ExerciseSlideSubmission {
    pub fn get_course_id(&self) -> ModelResult<Uuid> {
        self.course_id.ok_or_else(|| {
            ModelError::new(
                ModelErrorType::Generic,
                "Submission is not related to a course.".to_string(),
                None,
            )
        })
    }
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]

pub struct ExerciseAnswersInCourseRequiringAttentionCount {
    pub id: Uuid,
    pub name: String,
    pub page_id: Uuid,
    pub chapter_id: Option<Uuid>,
    pub order_number: i32,
    pub count: Option<i32>,
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]

pub struct ExerciseSlideSubmissionCount {
    pub date: Option<NaiveDate>,
    pub count: Option<i32>,
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]

pub struct ExerciseSlideSubmissionCountByExercise {
    pub exercise_id: Uuid,
    pub count: Option<i32>,
    pub exercise_name: String,
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]

pub struct ExerciseSlideSubmissionCountByWeekAndHour {
    pub isodow: Option<i32>,
    pub hour: Option<i32>,
    pub count: Option<i32>,
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]

pub struct ExerciseSlideSubmissionInfo {
    pub tasks: Vec<CourseMaterialExerciseTask>,
    pub exercise: Exercise,
    pub exercise_slide_submission: ExerciseSlideSubmission,
    pub user_exercise_state: Option<UserExerciseState>,
}

impl ExerciseSlideSubmissionInfo {
    /// Redacts fields that must never reach a holder of a share token, who has no teacher or
    /// course role. Unlike the role-gated paths, the shared view always hides:
    ///
    /// - every task's model solution spec, regardless of grading state — otherwise
    ///   "submit → share → open my own link" is the cheapest path to a solution. (The
    ///   course-material path reveals it once the student has full points or is out of tries.)
    /// - the submitter's `user_id`, zeroed rather than removed so the wire shape stays the
    ///   same for other consumers of this type.
    /// - `user_exercise_state`, dropped whole rather than field by field: every one of its
    ///   members is the submitter's own progress (`user_id`, `score_given`,
    ///   `activity_progress`, `reviewing_stage`), a share-link holder needs none of it, and
    ///   nulling members individually would re-leak the moment one is added.
    /// - each task's `pseudonumous_user_id`, a stable per-service pseudonym that otherwise
    ///   links all of one user's shares to each other.
    pub fn strip_for_shared_view(&mut self) {
        for task in &mut self.tasks {
            task.model_solution_spec = None;
            task.pseudonumous_user_id = None;
        }
        self.exercise_slide_submission.user_id = Uuid::nil();
        self.user_exercise_state = None;
    }
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]

pub struct ExerciseSlideSubmissionAndUserExerciseState {
    pub exercise: Exercise,
    pub exercise_slide_submission: ExerciseSlideSubmission,
    pub user_exercise_state: UserExerciseState,
    pub teacher_grading_decision: Option<TeacherGradingDecision>,
    pub user_exam_enrollment: ExamEnrollment,
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]

pub struct ExerciseSlideSubmissionAndUserExerciseStateList {
    pub data: Vec<ExerciseSlideSubmissionAndUserExerciseState>,
    pub total_pages: u32,
}

pub async fn insert_exercise_slide_submission(
    conn: &mut PgConnection,
    exercise_slide_submission: NewExerciseSlideSubmission,
) -> ModelResult<ExerciseSlideSubmission> {
    let res = sqlx::query_as!(
        ExerciseSlideSubmission,
        r#"
INSERT INTO exercise_slide_submissions (
    exercise_slide_id,
    course_id,
    exam_id,
    exercise_id,
    user_id,
    user_points_update_strategy
  )
VALUES ($1, $2, $3, $4, $5, $6)
RETURNING *
        "#,
        exercise_slide_submission.exercise_slide_id,
        exercise_slide_submission.course_id,
        exercise_slide_submission.exam_id,
        exercise_slide_submission.exercise_id,
        exercise_slide_submission.user_id,
        exercise_slide_submission.user_points_update_strategy as UserPointsUpdateStrategy,
    )
    .fetch_one(conn)
    .await?;
    Ok(res)
}

pub async fn insert_exercise_slide_submission_with_id(
    conn: &mut PgConnection,
    id: Uuid,
    exercise_slide_submission: &NewExerciseSlideSubmission,
) -> ModelResult<ExerciseSlideSubmission> {
    let res = sqlx::query_as!(
        ExerciseSlideSubmission,
        r#"
INSERT INTO exercise_slide_submissions (
    id,
    exercise_slide_id,
    course_id,
    exam_id,
    exercise_id,
    user_id,
    user_points_update_strategy
  )
VALUES ($1, $2, $3, $4, $5, $6, $7)
RETURNING *
        "#,
        id,
        exercise_slide_submission.exercise_slide_id,
        exercise_slide_submission.course_id,
        exercise_slide_submission.exam_id,
        exercise_slide_submission.exercise_id,
        exercise_slide_submission.user_id,
        exercise_slide_submission.user_points_update_strategy as UserPointsUpdateStrategy,
    )
    .fetch_one(conn)
    .await?;
    Ok(res)
}

pub async fn get_by_id(conn: &mut PgConnection, id: Uuid) -> ModelResult<ExerciseSlideSubmission> {
    let exercise_slide_submission = sqlx::query_as!(
        ExerciseSlideSubmission,
        r#"
SELECT *
FROM exercise_slide_submissions
WHERE id = $1
  AND deleted_at IS NULL;
        "#,
        id
    )
    .fetch_one(conn)
    .await?;
    Ok(exercise_slide_submission)
}

pub async fn get_by_course_id_and_user_ids_and_exercise_ids(
    conn: &mut PgConnection,
    course_id: Uuid,
    user_ids: &[Uuid],
    exercise_ids: &[Uuid],
) -> ModelResult<Vec<ExerciseSlideSubmission>> {
    let submissions = sqlx::query_as!(
        ExerciseSlideSubmission,
        r#"
SELECT ess.id,
  ess.created_at,
  ess.updated_at,
  ess.deleted_at,
  ess.exercise_slide_id,
  ess.course_id,
  ess.exam_id,
  ess.exercise_id,
  ess.user_id,
  ess.user_points_update_strategy,
  ess.flag_count
FROM exercise_slide_submissions ess
  JOIN exercises e ON e.id = ess.exercise_id
WHERE ess.course_id = $1
  AND e.course_id = $1
  AND ess.user_id = ANY($2)
  AND ess.exercise_id = ANY($3)
  AND ess.deleted_at IS NULL
  AND e.deleted_at IS NULL
        "#,
        course_id,
        user_ids,
        exercise_ids
    )
    .fetch_all(conn)
    .await?;
    Ok(submissions)
}

/// Returns a map of exercise_slide_submission id -> user_id for the given submission ids.
pub async fn get_user_ids_by_submission_ids(
    conn: &mut PgConnection,
    submission_ids: &[Uuid],
) -> ModelResult<HashMap<Uuid, Uuid>> {
    if submission_ids.is_empty() {
        return Ok(HashMap::new());
    }
    let rows = sqlx::query!(
        r#"
SELECT *
FROM exercise_slide_submissions
WHERE id = ANY($1)
  AND deleted_at IS NULL
        "#,
        submission_ids
    )
    .fetch_all(conn)
    .await?;
    Ok(rows.into_iter().map(|r| (r.id, r.user_id)).collect())
}

/// Attempts to find a single random `ExerciseSlideSubmission` that is not related to the provided user.
///
/// This function is mostly provided for very specific peer review purposes. Used only as a last resort if no other candidates are found to be peer reviewed.
pub async fn try_to_get_random_filtered_by_user_and_submissions(
    conn: &mut PgConnection,
    exercise_id: Uuid,
    excluded_user_id: Uuid,
    excluded_ids: &[Uuid],
) -> ModelResult<Option<ExerciseSlideSubmission>> {
    let mut res = sqlx::query_as!(
        ExerciseSlideSubmission,
        r#"
SELECT DISTINCT ON (user_id)
  ess.id,
  ess.created_at,
  ess.updated_at,
  ess.deleted_at,
  ess.exercise_slide_id,
  ess.course_id,
  ess.exam_id,
  ess.exercise_id,
  ess.user_id,
  ess.user_points_update_strategy,
  ess.flag_count
FROM exercise_slide_submissions AS ess
JOIN courses AS c
  ON ess.course_id = c.id
WHERE ess.exercise_id = $1
  AND ess.id <> ALL($2)
  AND ess.user_id <> $3
  AND ess.deleted_at IS NULL
  AND ess.flag_count < c.flagged_answers_threshold
ORDER BY ess.user_id, ess.created_at DESC
        "#,
        exercise_id,
        excluded_ids,
        excluded_user_id,
    )
    .fetch_all(conn)
    .await?;
    // shuffle the res vec
    let mut rng = rand::rng();
    res.shuffle(&mut rng);
    Ok(res.into_iter().next())
}

pub async fn get_by_exercise_id(
    conn: &mut PgConnection,
    exercise_id: Uuid,
    pagination: Pagination,
) -> ModelResult<Vec<ExerciseSlideSubmission>> {
    let submissions = sqlx::query_as!(
        ExerciseSlideSubmission,
        r#"
SELECT *
FROM exercise_slide_submissions
WHERE exercise_id = $1
  AND deleted_at IS NULL
LIMIT $2 OFFSET $3;
        "#,
        exercise_id,
        pagination.limit(),
        pagination.offset(),
    )
    .fetch_all(conn)
    .await?;
    Ok(submissions)
}

/// A single submission's time, its exercise, and the module that exercise sits in (via its chapter).
/// Used to plot a user's submission activity within a course.
#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
pub struct UserCourseSubmissionTime {
    pub created_at: DateTime<Utc>,
    pub exercise_id: Uuid,
    /// Module the exercise's chapter belongs to; `None` for exercises not placed in a chapter.
    pub course_module_id: Option<Uuid>,
}

/// All of a user's submission times in a course, each tagged with its exercise and module, ordered
/// chronologically. Excludes submissions to soft-deleted exercises/chapters so this per-course timeline
/// agrees with the cross-course density view. Capped to a safe maximum, fetching one past the cap (5001)
/// so the caller can tell a genuine overflow from an exact-cap result.
pub async fn get_user_course_submission_times(
    conn: &mut PgConnection,
    user_id: Uuid,
    course_id: Uuid,
) -> ModelResult<Vec<UserCourseSubmissionTime>> {
    let submissions = sqlx::query_as!(
        UserCourseSubmissionTime,
        r#"
SELECT ess.created_at,
  ess.exercise_id,
  c.course_module_id AS "course_module_id?"
FROM exercise_slide_submissions ess
  JOIN exercises e ON e.id = ess.exercise_id AND e.deleted_at IS NULL
  LEFT JOIN chapters c ON c.id = e.chapter_id AND c.deleted_at IS NULL
WHERE ess.user_id = $1
  AND ess.course_id = $2
  AND ess.deleted_at IS NULL
ORDER BY ess.created_at
LIMIT 5001
        "#,
        user_id,
        course_id,
    )
    .fetch_all(conn)
    .await?;
    Ok(submissions)
}

pub async fn get_users_all_submissions_for_course_or_exam(
    conn: &mut PgConnection,
    user_id: Uuid,
    course_or_exam_id: CourseOrExamId,
) -> ModelResult<Vec<ExerciseSlideSubmission>> {
    let (course_id, exam_id) = course_or_exam_id.to_course_and_exam_ids();
    let submissions = sqlx::query_as!(
        ExerciseSlideSubmission,
        r#"
SELECT *
FROM exercise_slide_submissions
WHERE user_id = $1
  AND (course_id = $2 OR exam_id = $3)
  AND deleted_at IS NULL
        "#,
        user_id,
        course_id,
        exam_id,
    )
    .fetch_all(conn)
    .await?;
    Ok(submissions)
}

/// Of the given exercises, the ones the user has an existing answer to.
///
/// This is the predicate for "there is something to review": it matches the joins
/// get_all_answers_requiring_attention uses, so an exercise missing from the result has nothing a
/// teacher could grade. Prefer this over
/// user_exercise_states::get_returned_exercise_ids_for_user_and_course when deciding whether an
/// answer exists — that one keys off activity_progress, which stays InProgress for a submitted
/// peer review exercise and so under-reports answers.
///
/// Kept in sync with the repair in migration 20260806122511.
pub async fn get_exercise_ids_with_submissions_for_user(
    conn: &mut PgConnection,
    exercise_ids: &[Uuid],
    user_id: Uuid,
    course_id: Uuid,
) -> ModelResult<Vec<Uuid>> {
    let exercise_ids = sqlx::query_scalar!(
        r#"
        SELECT DISTINCT exercise_id
        FROM exercise_slide_submissions
        WHERE exercise_id = ANY($1)
          AND user_id = $2
          AND course_id = $3
          AND deleted_at IS NULL
        "#,
        exercise_ids,
        user_id,
        course_id,
    )
    .fetch_all(conn)
    .await?;

    Ok(exercise_ids)
}

/// An exercise of a course that has at least one submission, with the page it is on.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct ExerciseWithSubmissions {
    pub exercise_id: Uuid,
    pub page_id: Uuid,
}

/// The course's exercises that have at least one submission from any user.
pub async fn get_exercises_with_submissions_by_course_id(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> ModelResult<Vec<ExerciseWithSubmissions>> {
    let res = sqlx::query_as!(
        ExerciseWithSubmissions,
        r#"
SELECT e.id AS exercise_id,
  e.page_id
FROM exercises e
WHERE e.course_id = $1
  AND e.deleted_at IS NULL
  AND EXISTS (
    SELECT 1
    FROM exercise_slide_submissions ess
    WHERE ess.exercise_id = e.id
      AND ess.deleted_at IS NULL
  )
        "#,
        course_id,
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

/// [`get_exercises_with_submissions_by_course_id`] limited to `exercise_ids`, without pages.
pub async fn get_exercise_ids_with_submissions_by_course_id(
    conn: &mut PgConnection,
    course_id: Uuid,
    exercise_ids: &[Uuid],
) -> ModelResult<Vec<Uuid>> {
    let res = sqlx::query_scalar!(
        r#"
SELECT e.id
FROM exercises e
WHERE e.course_id = $1
  AND e.id = ANY($2)
  AND e.deleted_at IS NULL
  AND EXISTS (
    SELECT 1
    FROM exercise_slide_submissions ess
    WHERE ess.exercise_id = e.id
      AND ess.deleted_at IS NULL
  )
        "#,
        course_id,
        exercise_ids,
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

/// Of the given exercises, the ones with at least one existing submission from any user.
///
/// Used to warn a teacher before saving a page edit that would drop one of these exercises: the
/// exercise gets soft-deleted by `exercises::delete_exercises_by_page_id`, which orphans the
/// submissions behind the `deleted_at` filter instead of removing them (course-improvements-issues#146).
pub async fn get_exercise_ids_with_any_submissions(
    conn: &mut PgConnection,
    page_id: Uuid,
    exercise_ids: &[Uuid],
) -> ModelResult<Vec<Uuid>> {
    // Joins back to exercises.page_id so a caller can't probe submission existence for
    // exercise ids outside the page they were authorized to edit.
    let exercise_ids = sqlx::query_scalar!(
        r#"
        SELECT DISTINCT ess.exercise_id
        FROM exercise_slide_submissions ess
        JOIN exercises e ON e.id = ess.exercise_id
        WHERE ess.exercise_id = ANY($1)
          AND e.page_id = $2
          AND e.deleted_at IS NULL
          AND ess.deleted_at IS NULL
        "#,
        exercise_ids,
        page_id,
    )
    .fetch_all(conn)
    .await?;

    Ok(exercise_ids)
}

pub async fn get_users_submissions_for_exercise(
    conn: &mut PgConnection,
    user_id: Uuid,
    exercise_id: Uuid,
) -> ModelResult<Vec<ExerciseSlideSubmission>> {
    let submissions = sqlx::query_as!(
        ExerciseSlideSubmission,
        r#"
SELECT *
FROM exercise_slide_submissions
WHERE user_id = $1
  AND exercise_id = $2
  AND deleted_at IS NULL
ORDER BY created_at DESC, id DESC
        "#,
        user_id,
        exercise_id,
    )
    .fetch_all(conn)
    .await?;
    Ok(submissions)
}

/// One of the user's submissions to an exercise, with the grading of its task submission.
///
/// The exercise-services client API only serves single-task (editor) slides, so "the slide's
/// grading" is unambiguous; `DISTINCT ON` keeps that true for any slide that has more.
#[derive(Debug, Clone, PartialEq)]
pub struct UserExerciseSlideSubmissionWithGrading {
    pub id: Uuid,
    pub exercise_id: Uuid,
    pub created_at: DateTime<Utc>,
    pub score_given: Option<f32>,
    pub grading_progress: Option<GradingProgress>,
}

/// The user's submissions to an exercise, newest first, each joined to its grading.
///
/// One query rather than two per submission: a student who retries an exercise 200 times used to
/// cost 401 round trips for one listing.
pub async fn get_users_submissions_for_exercise_with_gradings(
    conn: &mut PgConnection,
    user_id: Uuid,
    exercise_id: Uuid,
) -> ModelResult<Vec<UserExerciseSlideSubmissionWithGrading>> {
    let submissions = sqlx::query_as!(
        UserExerciseSlideSubmissionWithGrading,
        r#"
SELECT DISTINCT ON (ess.created_at, ess.id)
  ess.id,
  ess.exercise_id,
  ess.created_at,
  etg.score_given,
  etg.grading_progress AS "grading_progress?: GradingProgress"
FROM exercise_slide_submissions AS ess
  LEFT JOIN exercise_task_submissions AS ets ON ets.exercise_slide_submission_id = ess.id
  AND ets.deleted_at IS NULL
  LEFT JOIN exercise_task_gradings AS etg ON etg.id = ets.exercise_task_grading_id
  AND etg.deleted_at IS NULL
WHERE ess.user_id = $1
  AND ess.exercise_id = $2
  AND ess.deleted_at IS NULL
ORDER BY ess.created_at DESC, ess.id DESC, ets.created_at
        "#,
        user_id,
        exercise_id,
    )
    .fetch_all(conn)
    .await?;
    Ok(submissions)
}

pub async fn get_users_latest_exercise_slide_submission(
    conn: &mut PgConnection,
    exercise_slide_id: Uuid,
    user_id: Uuid,
) -> ModelResult<ExerciseSlideSubmission> {
    let res = sqlx::query_as!(
        ExerciseSlideSubmission,
        r#"
SELECT *
FROM exercise_slide_submissions
WHERE exercise_slide_id = $1
  AND user_id = $2
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 1
        "#,
        exercise_slide_id,
        user_id
    )
    .fetch_one(conn)
    .await?;
    Ok(res)
}

pub async fn try_to_get_users_latest_exercise_slide_submission(
    conn: &mut PgConnection,
    exercise_slide_id: Uuid,
    user_id: Uuid,
) -> ModelResult<Option<ExerciseSlideSubmission>> {
    get_users_latest_exercise_slide_submission(conn, exercise_slide_id, user_id)
        .await
        .optional()
}

pub async fn get_course_and_exam_id(
    conn: &mut PgConnection,
    id: Uuid,
) -> ModelResult<CourseOrExamId> {
    let res = sqlx::query!(
        "
SELECT *
FROM exercise_slide_submissions
WHERE id = $1
  AND deleted_at IS NULL
        ",
        id
    )
    .fetch_one(conn)
    .await?;
    CourseOrExamId::from_course_and_exam_ids(res.course_id, res.exam_id)
}

pub async fn exercise_slide_submission_count(
    conn: &mut PgConnection,
    exercise_id: Uuid,
) -> ModelResult<u32> {
    let count = sqlx::query!(
        "
SELECT COUNT(*) as count
FROM exercise_slide_submissions
WHERE exercise_id = $1
AND deleted_at IS NULL
",
        exercise_id,
    )
    .fetch_one(conn)
    .await?;
    Ok(count.count.unwrap_or(0).try_into()?)
}

pub async fn exercise_slide_submissions(
    conn: &mut PgConnection,
    exercise_id: Uuid,
    pagination: Pagination,
) -> ModelResult<Vec<ExerciseSlideSubmission>> {
    let submissions = sqlx::query_as!(
        ExerciseSlideSubmission,
        r#"
SELECT *
FROM exercise_slide_submissions
WHERE exercise_id = $1
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT $2 OFFSET $3
        "#,
        exercise_id,
        pagination.limit(),
        pagination.offset(),
    )
    .fetch_all(conn)
    .await?;
    Ok(submissions)
}

pub async fn exercise_slide_submission_count_with_exam_id(
    conn: &mut PgConnection,
    exam_id: Uuid,
) -> ModelResult<u32> {
    let count = sqlx::query!(
        "
SELECT COUNT(*) as count
FROM exercise_slide_submissions
WHERE exam_id = $1
AND deleted_at IS NULL
",
        exam_id,
    )
    .fetch_one(conn)
    .await?;
    Ok(count.count.unwrap_or(0).try_into()?)
}

pub async fn exercise_slide_submission_count_with_exercise_id(
    conn: &mut PgConnection,
    exercise_id: Uuid,
) -> ModelResult<u32> {
    let count = sqlx::query!(
        "
SELECT COUNT(*) as count
FROM exercise_slide_submissions
WHERE exercise_id = $1
AND deleted_at IS NULL
",
        exercise_id,
    )
    .fetch_one(conn)
    .await?;
    Ok(count.count.unwrap_or(0).try_into()?)
}

pub async fn get_latest_exercise_slide_submissions_and_user_exercise_state_list_with_exercise_id(
    conn: &mut PgConnection,
    exercise_id: Uuid,
    pagination: Pagination,
) -> ModelResult<Vec<ExerciseSlideSubmissionAndUserExerciseState>> {
    let submissions = sqlx::query_as!(
        ExerciseSlideSubmission,
        r#"
    SELECT DISTINCT ON (user_id)
        id,
        created_at,
        updated_at,
        deleted_at,
        exercise_slide_id,
        course_id,
        exam_id,
        exercise_id,
        user_id,
        user_points_update_strategy,
  flag_count
FROM exercise_slide_submissions
WHERE exercise_id = $1
      AND deleted_at IS NULL
ORDER BY user_id, created_at DESC
LIMIT $2 OFFSET $3
        "#,
        exercise_id,
        pagination.limit(),
        pagination.offset(),
    )
    .fetch_all(&mut *conn)
    .await?;

    let user_ids = submissions
        .iter()
        .map(|sub| sub.user_id)
        .collect::<Vec<_>>();

    let exercise = exercises::get_by_id(conn, exercise_id).await?;
    let exam_id = exercise.exam_id;
    let course_id = exercise.course_id;

    let user_exercise_states_list =
        user_exercise_states::get_or_create_user_exercise_state_for_users(
            conn,
            &user_ids,
            exercise_id,
            course_id,
            exam_id,
        )
        .await?;

    let mut user_exercise_state_id_list: Vec<Uuid> = Vec::new();

    for (_key, value) in user_exercise_states_list.clone().into_iter() {
        user_exercise_state_id_list.push(value.id);
    }

    let exercise = exercises::get_by_id(conn, exercise_id).await?;
    let exam_id = exercise
        .exam_id
        .ok_or_else(|| ModelError::new(ModelErrorType::Generic, "No exam id found", None))?;

    let teacher_grading_decisions_list = teacher_grading_decisions::try_to_get_latest_grading_decision_by_user_exercise_state_id_for_users(conn, &user_exercise_state_id_list).await?;

    let user_exam_enrollments_list =
        exams::get_exam_enrollments_for_users(conn, exam_id, &user_ids).await?;

    let mut list: Vec<ExerciseSlideSubmissionAndUserExerciseState> = Vec::new();
    for sub in submissions {
        let user_exercise_state = user_exercise_states_list
            .get(&sub.user_id)
            .ok_or_else(|| ModelError::new(ModelErrorType::Generic, "No user found", None))?;

        let teacher_grading_decision = teacher_grading_decisions_list.get(&user_exercise_state.id);
        let user_exam_enrollment =
            user_exam_enrollments_list
                .get(&sub.user_id)
                .ok_or_else(|| {
                    ModelError::new(
                        ModelErrorType::Generic,
                        "No users exam_enrollment found",
                        None,
                    )
                })?;

        //Add submissions to the list only if the students exam time has ended
        if user_exam_enrollment.ended_at.is_some() {
            let data = ExerciseSlideSubmissionAndUserExerciseState {
                exercise: exercise.clone(),
                exercise_slide_submission: sub,
                user_exercise_state: user_exercise_state.clone(),
                teacher_grading_decision: teacher_grading_decision.cloned(),
                user_exam_enrollment: user_exam_enrollment.clone(),
            };
            list.push(data);
        }
    }

    Ok(list)
}

pub async fn get_course_daily_slide_submission_counts(
    conn: &mut PgConnection,
    course: &Course,
) -> ModelResult<Vec<ExerciseSlideSubmissionCount>> {
    let res = sqlx::query_as!(
        ExerciseSlideSubmissionCount,
        r#"
SELECT DATE(created_at) date, count(*)::integer
FROM exercise_slide_submissions
WHERE course_id = $1
AND deleted_at IS NULL
GROUP BY date
ORDER BY date;
          "#,
        course.id
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_course_daily_user_counts_with_submissions(
    conn: &mut PgConnection,
    course: &Course,
) -> ModelResult<Vec<ExerciseSlideSubmissionCount>> {
    let res = sqlx::query_as!(
        ExerciseSlideSubmissionCount,
        r#"
SELECT DATE(created_at) date, count(DISTINCT user_id)::integer
FROM exercise_slide_submissions
WHERE course_id = $1
AND deleted_at IS NULL
GROUP BY date
ORDER BY date;
          "#,
        course.id
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn answer_requiring_attention_count(
    conn: &mut PgConnection,
    exercise_id: Uuid,
) -> ModelResult<u32> {
    let count = sqlx::query!(
        r#"
        SELECT
        COUNT(DISTINCT us_state.user_id) as count
    FROM user_exercise_states AS us_state
    JOIN exercise_task_submissions AS t_submission
        ON us_state.selected_exercise_slide_id =
            t_submission.exercise_slide_id
    JOIN exercise_slide_submissions AS s_submission
            ON t_submission.exercise_slide_submission_id =
                s_submission.id
    WHERE us_state.selected_exercise_slide_id =
            t_submission.exercise_slide_id
    AND us_state.user_id = s_submission.user_id
    AND us_state.exercise_id = $1
    AND us_state.reviewing_stage = 'waiting_for_manual_grading'
    AND us_state.deleted_at IS NULL
    AND s_submission.deleted_at IS NULL
    AND t_submission.deleted_at IS NULL"#,
        exercise_id,
    )
    .fetch_one(conn)
    .await?;
    Ok(count.count.unwrap_or(0).try_into()?)
}

pub async fn get_count_of_answers_requiring_attention_in_exercise_by_course_id(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> ModelResult<Vec<ExerciseAnswersInCourseRequiringAttentionCount>> {
    let count_list = sqlx::query_as!(
        ExerciseAnswersInCourseRequiringAttentionCount,
        r#"
SELECT exercises.id,
  (
    SELECT COUNT(DISTINCT us_state.user_id)::integer AS COUNT
    FROM exercises AS exercises2
      LEFT JOIN user_exercise_states AS us_state ON us_state.exercise_id = exercises2.id
      LEFT JOIN exercise_slide_submissions AS s_submission ON us_state.selected_exercise_slide_id = s_submission.exercise_slide_id
      LEFT JOIN exercise_task_submissions AS t_submission ON s_submission.id = t_submission.exercise_slide_submission_id
    WHERE us_state.selected_exercise_slide_id = t_submission.exercise_slide_id
      AND us_state.user_id = s_submission.user_id
      AND us_state.reviewing_stage = 'waiting_for_manual_grading'
      AND us_state.deleted_at IS NULL
      AND s_submission.deleted_at IS NULL
      AND t_submission.deleted_at IS NULL
      AND exercises2.course_id = $1
      AND exercises.id = exercises2.id
    GROUP BY exercises2.id
  ),
  exercises.order_number,
  exercises.name,
  exercises.page_id,
  exercises.chapter_id
FROM exercises
WHERE exercises.course_id = $1
  AND exercises.deleted_at IS NULL
GROUP BY exercises.id;
"#,
        course_id,
    )
    .fetch_all(conn)
    .await?;
    Ok(count_list)
}

pub async fn exercise_slide_submissions_for_answers_requiring_attention(
    conn: &mut PgConnection,
    exercise_id: Uuid,
    pagination: Pagination,
) -> ModelResult<Vec<ExerciseSlideSubmission>> {
    let submissions = sqlx::query_as!(
        ExerciseSlideSubmission,
        r#"
SELECT *
FROM exercise_slide_submissions
WHERE exercise_id = $1
  AND deleted_at IS NULL
LIMIT $2 OFFSET $3
        "#,
        exercise_id,
        pagination.limit(),
        pagination.offset(),
    )
    .fetch_all(conn)
    .await?;
    Ok(submissions)
}

pub async fn get_all_answers_requiring_attention(
    conn: &mut PgConnection,
    exercise_id: Uuid,
    pagination: Pagination,
    file_store: &dyn FileStore,
    app_conf: &ApplicationConfiguration,
) -> ModelResult<Vec<AnswerRequiringAttention>> {
    let rows = sqlx::query!(
        r#"
        SELECT DISTINCT ON (us_state.user_id)
        us_state.id,
        us_state.user_id,
        us_state.exercise_id,
        us_state.score_given,
        us_state.grading_progress AS "grading_progress: GradingProgress",
        t_submission.id AS task_submission_id,
        t_submission.data_json,
        t_submission.answer_kind,
        s_submission.created_at,
        s_submission.updated_at,
        s_submission.deleted_at,
        s_submission.id AS submission_id
    FROM user_exercise_states AS us_state
    JOIN exercise_task_submissions AS t_submission
        ON us_state.selected_exercise_slide_id =
            t_submission.exercise_slide_id
    JOIN exercise_slide_submissions AS s_submission
            ON t_submission.exercise_slide_submission_id =
                s_submission.id
    WHERE us_state.selected_exercise_slide_id =
            t_submission.exercise_slide_id
    AND us_state.user_id = s_submission.user_id
    AND us_state.exercise_id = $1
    AND us_state.reviewing_stage = 'waiting_for_manual_grading'
    AND us_state.deleted_at IS NULL
    AND s_submission.deleted_at IS NULL
    AND t_submission.deleted_at IS NULL
    ORDER BY us_state.user_id, s_submission.created_at DESC
    LIMIT $2 OFFSET $3;"#,
        exercise_id,
        pagination.limit(),
        pagination.offset(),
    )
    .fetch_all(&mut *conn)
    .await?;
    let stored: Vec<StoredAnswer> = rows
        .iter()
        .map(|row| StoredAnswer {
            submission_id: row.task_submission_id,
            answer_kind: row.answer_kind,
            data_json: row.data_json.clone(),
        })
        .collect();
    let mut answers = attach_answer_data(conn, &stored, file_store, app_conf).await?;
    Ok(rows
        .into_iter()
        .map(|row| AnswerRequiringAttention {
            id: row.id,
            user_id: row.user_id,
            created_at: row.created_at,
            updated_at: row.updated_at,
            deleted_at: row.deleted_at,
            answer: answers.remove(&row.task_submission_id),
            grading_progress: row.grading_progress,
            score_given: row.score_given,
            submission_id: row.submission_id,
            exercise_id: row.exercise_id,
        })
        .collect())
}

pub async fn get_course_exercise_slide_submission_counts_by_weekday_and_hour(
    conn: &mut PgConnection,
    course: &Course,
) -> ModelResult<Vec<ExerciseSlideSubmissionCountByWeekAndHour>> {
    let res = sqlx::query_as!(
        ExerciseSlideSubmissionCountByWeekAndHour,
        r#"
SELECT date_part('isodow', created_at)::integer isodow,
  date_part('hour', created_at)::integer "hour",
  count(*)::integer
FROM exercise_slide_submissions
WHERE course_id = $1
AND deleted_at IS NULL
GROUP BY isodow,
  "hour"
ORDER BY isodow,
  hour;
          "#,
        course.id
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_course_exercise_slide_submission_counts_by_exercise(
    conn: &mut PgConnection,
    course: &Course,
) -> ModelResult<Vec<ExerciseSlideSubmissionCountByExercise>> {
    let res = sqlx::query_as!(
        ExerciseSlideSubmissionCountByExercise,
        r#"
SELECT counts.*, exercises.name exercise_name
    FROM (
        SELECT exercise_id, count(*)::integer count
        FROM exercise_slide_submissions
        WHERE course_id = $1
        AND deleted_at IS NULL
        GROUP BY exercise_id
    ) counts
    JOIN exercises ON (counts.exercise_id = exercises.id);
          "#,
        course.id
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_exercise_slide_submission_counts_for_exercise_user(
    conn: &mut PgConnection,
    exercise_id: Uuid,
    course_id_or_exam_id: CourseOrExamId,
    user_id: Uuid,
) -> ModelResult<HashMap<Uuid, i64>> {
    let ci_id_or_e_id = match course_id_or_exam_id {
        CourseOrExamId::Course(id) => id,
        CourseOrExamId::Exam(id) => id,
    };
    let res = sqlx::query!(
        r#"
SELECT exercise_slide_id,
  COUNT(*) as count
FROM exercise_slide_submissions
WHERE exercise_id = $1
  AND (course_id = $2 OR exam_id = $2)
  AND user_id = $3
  AND deleted_at IS NULL
GROUP BY exercise_slide_id;
    "#,
        exercise_id,
        ci_id_or_e_id,
        user_id
    )
    .fetch_all(conn)
    .await?
    .iter()
    .map(|row| (row.exercise_slide_id, row.count.unwrap_or(0)))
    .collect::<HashMap<Uuid, i64>>();

    Ok(res)
}

pub async fn get_exercise_slide_submission_info(
    conn: &mut PgConnection,
    exercise_slide_submission_id: Uuid,
    user_id: Uuid,
    fetch_service_info: impl Fn(Url) -> BoxFuture<'static, ModelResult<ExerciseServiceInfoApi>>,
    include_deleted_tasks: bool,
    file_store: &dyn FileStore,
    app_conf: &ApplicationConfiguration,
) -> ModelResult<ExerciseSlideSubmissionInfo> {
    let exercise_slide_submission = get_by_id(&mut *conn, exercise_slide_submission_id).await?;
    let exercise =
        crate::exercises::get_by_id(&mut *conn, exercise_slide_submission.exercise_id).await?;
    let tasks = crate::exercise_task_submissions::get_exercise_task_submission_info_by_exercise_slide_submission_id(&mut *conn, exercise_slide_submission_id, user_id, fetch_service_info, include_deleted_tasks, file_store, app_conf).await?;
    let user_exercise_state = crate::user_exercise_states::get_user_exercise_state_if_exists(
        &mut *conn,
        user_id,
        exercise_slide_submission.exercise_id,
        CourseOrExamId::from_course_and_exam_ids(
            exercise_slide_submission.course_id,
            exercise_slide_submission.exam_id,
        )?,
    )
    .await?;

    Ok(ExerciseSlideSubmissionInfo {
        exercise,
        tasks,
        exercise_slide_submission,
        user_exercise_state,
    })
}

pub async fn delete_exercise_submissions_with_exam_id_and_user_id(
    conn: &mut PgConnection,
    exam_id: Uuid,
    user_id: Uuid,
) -> ModelResult<()> {
    sqlx::query!(
        "
UPDATE exercise_slide_submissions
SET deleted_at = now()
WHERE exam_id = $1 AND user_id = $2
AND deleted_at IS NULL
    ",
        exam_id,
        user_id,
    )
    .execute(&mut *conn)
    .await?;
    Ok(())
}

/// How many live slide submissions a user has made to an exercise.
pub async fn exercise_slide_submission_count_with_exercise_and_user_ids(
    conn: &mut PgConnection,
    exercise_id: Uuid,
    user_id: Uuid,
) -> ModelResult<u32> {
    let count = sqlx::query_scalar!(
        r#"
SELECT count(*) AS "count!"
FROM exercise_slide_submissions
WHERE exercise_id = $1
  AND user_id = $2
  AND deleted_at IS NULL
"#,
        exercise_id,
        user_id
    )
    .fetch_one(conn)
    .await?;
    Ok(count.try_into()?)
}

/// Soft-deletes one slide submission, leaving its task submissions alone.
pub async fn delete_by_id(conn: &mut PgConnection, id: Uuid) -> ModelResult<()> {
    sqlx::query!(
        "UPDATE exercise_slide_submissions SET deleted_at = now() WHERE id = $1 AND deleted_at IS NULL",
        id
    )
    .execute(conn)
    .await?;
    Ok(())
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::exercise_tasks::CourseMaterialExerciseTask;
    use crate::exercises::ActivityProgress;
    use crate::user_exercise_states::ReviewingStage;

    fn dummy_exercise() -> Exercise {
        Exercise {
            id: Uuid::new_v4(),
            created_at: Utc::now(),
            updated_at: Utc::now(),
            name: "Test exercise".to_string(),
            course_id: Some(Uuid::new_v4()),
            exam_id: None,
            page_id: Uuid::new_v4(),
            chapter_id: None,
            deadline: None,
            deleted_at: None,
            score_maximum: 1,
            order_number: 0,
            copied_from: None,
            max_tries_per_slide: None,
            limit_number_of_tries: false,
            needs_peer_review: false,
            needs_self_review: false,
            use_course_default_peer_or_self_review_config: false,
            exercise_language_group_id: None,
            teacher_reviews_answer_after_locking: false,
        }
    }

    fn dummy_slide_submission(user_id: Uuid) -> ExerciseSlideSubmission {
        ExerciseSlideSubmission {
            id: Uuid::new_v4(),
            created_at: Utc::now(),
            updated_at: Utc::now(),
            deleted_at: None,
            exercise_slide_id: Uuid::new_v4(),
            course_id: Some(Uuid::new_v4()),
            exam_id: None,
            exercise_id: Uuid::new_v4(),
            user_id,
            user_points_update_strategy:
                UserPointsUpdateStrategy::CanAddPointsButCannotRemovePoints,
            flag_count: None,
        }
    }

    fn dummy_user_exercise_state(user_id: Uuid) -> UserExerciseState {
        UserExerciseState {
            id: Uuid::new_v4(),
            user_id,
            exercise_id: Uuid::new_v4(),
            course_id: Some(Uuid::new_v4()),
            exam_id: None,
            created_at: Utc::now(),
            updated_at: Utc::now(),
            deleted_at: None,
            score_given: Some(1.0),
            grading_progress: GradingProgress::FullyGraded,
            activity_progress: ActivityProgress::Completed,
            reviewing_stage: ReviewingStage::ReviewedAndLocked,
            selected_exercise_slide_id: Some(Uuid::new_v4()),
        }
    }

    /// Every field of the shared view that could name or fingerprint the submitter. A new
    /// identity-bearing field must be added here as well as to `strip_for_shared_view`.
    fn identifiers_in(info: &ExerciseSlideSubmissionInfo) -> Vec<String> {
        let mut found = Vec::new();
        if info.exercise_slide_submission.user_id != Uuid::nil() {
            found.push("exercise_slide_submission.user_id".to_string());
        }
        if info.user_exercise_state.is_some() {
            found.push("user_exercise_state".to_string());
        }
        for (index, task) in info.tasks.iter().enumerate() {
            if task.pseudonumous_user_id.is_some() {
                found.push(format!("tasks[{index}].pseudonumous_user_id"));
            }
        }
        found
    }

    fn dummy_task_with_model_solution() -> CourseMaterialExerciseTask {
        CourseMaterialExerciseTask {
            id: Uuid::new_v4(),
            exercise_service_slug: "tmc".to_string(),
            exercise_slide_id: Uuid::new_v4(),
            exercise_iframe_url: None,
            pseudonumous_user_id: Some(Uuid::new_v4()),
            assignment: serde_json::json!([]),
            public_spec: Some(serde_json::json!({ "public": true })),
            model_solution_spec: Some(serde_json::json!({ "solution": "the answer" })),
            previous_submission: None,
            previous_submission_grading: None,
            order_number: 0,
            deleted_at: None,
        }
    }

    /// The strip is unconditional, i.e. independent of grading state.
    #[test]
    fn strip_for_shared_view_removes_model_solution_and_user_id() {
        let user_id = Uuid::new_v4();
        let mut info = ExerciseSlideSubmissionInfo {
            tasks: vec![
                dummy_task_with_model_solution(),
                dummy_task_with_model_solution(),
            ],
            exercise: dummy_exercise(),
            exercise_slide_submission: dummy_slide_submission(user_id),
            user_exercise_state: Some(dummy_user_exercise_state(user_id)),
        };

        assert!(
            info.tasks.iter().all(|t| t.model_solution_spec.is_some()),
            "test fixture should start with model solutions present"
        );
        assert_eq!(info.exercise_slide_submission.user_id, user_id);

        info.strip_for_shared_view();

        assert!(
            info.tasks.iter().all(|t| t.model_solution_spec.is_none()),
            "model_solution_spec must be absent from every task in the shared view"
        );
        assert_eq!(
            info.exercise_slide_submission.user_id,
            Uuid::nil(),
            "submitter user_id must not be exposed in the shared view"
        );
    }

    /// A share link is forwardable, so nothing reachable through it may name or fingerprint the
    /// submitter — not their user id, not their progress row, not the per-service pseudonym that
    /// would link their shares together.
    #[test]
    fn strip_for_shared_view_leaves_no_user_identifier() {
        let user_id = Uuid::new_v4();
        let mut info = ExerciseSlideSubmissionInfo {
            tasks: vec![
                dummy_task_with_model_solution(),
                dummy_task_with_model_solution(),
            ],
            exercise: dummy_exercise(),
            exercise_slide_submission: dummy_slide_submission(user_id),
            user_exercise_state: Some(dummy_user_exercise_state(user_id)),
        };

        assert_eq!(
            identifiers_in(&info),
            vec![
                "exercise_slide_submission.user_id",
                "user_exercise_state",
                "tasks[0].pseudonumous_user_id",
                "tasks[1].pseudonumous_user_id",
            ],
            "the fixture must start with every identifier present, or this test proves nothing"
        );

        info.strip_for_shared_view();

        assert_eq!(
            identifiers_in(&info),
            Vec::<String>::new(),
            "no user identifier may survive the strip"
        );
    }

    #[test]
    fn strip_for_shared_view_keeps_public_spec() {
        let mut info = ExerciseSlideSubmissionInfo {
            tasks: vec![dummy_task_with_model_solution()],
            exercise: dummy_exercise(),
            exercise_slide_submission: dummy_slide_submission(Uuid::new_v4()),
            user_exercise_state: Some(dummy_user_exercise_state(Uuid::new_v4())),
        };

        info.strip_for_shared_view();

        assert!(
            info.tasks[0].public_spec.is_some(),
            "public_spec should remain available in the shared view"
        );
    }
}