headless_lms_models/

user_exercise_states.rs

use derive_more::Display;
use std::collections::HashMap;

use futures::Stream;
use headless_lms_utils::numbers::option_f32_to_f32_two_decimals_with_none_as_zero;
use serde_json::Value;
use utoipa::ToSchema;

use crate::{
    course_modules::{self, CourseModule},
    courses,
    exercises::{ActivityProgress, Exercise, GradingProgress},
    prelude::*,
};

#[derive(Debug, Serialize, Deserialize, PartialEq, Eq, Clone, Copy, Type, Display, ToSchema)]
#[sqlx(type_name = "reviewing_stage", rename_all = "snake_case")]
/**
Tells what stage of reviewing the user is currently in. Used for for peer review, self review, and manual review. If an exercise does not involve reviewing, the value of this stage will always be `NotStarted`.
*/
pub enum ReviewingStage {
    /**
    In this stage the user submits answers to the exercise. If the exercise allows it, the user can answer the exercise multiple times. If the exercise is not in this stage, the user cannot answer the exercise. Most exercises will never leave this stage because other stages are reseverved for situations when we cannot give the user points just based on the automatic gradings.
    */
    NotStarted,
    /// In this stage the student is instructed to give peer reviews to other students.
    PeerReview,
    /// In this stage the student is instructed to review their own answer.
    SelfReview,
    /// In this stage the student has completed the neccessary peer and self reviews but is waiting for other students to peer review their answer before we can give points for this exercise.
    WaitingForPeerReviews,
    /**
    In this stage the student has completed everything they need to do, but before we can give points for this exercise, we need a manual grading from the teacher.

    Reasons for ending up in this stage may be one of these:

    1. The exercise is configured to require all answers to be reviewed by the teacher.
    2. The answer has received poor reviews from the peers, and the exercise has been configured so that the teacher has to double-check whether it is justified to not give full points to the student.
    */
    WaitingForManualGrading,
    /**
    In this stage the the reviews have been completed and the points have been awarded to the student. However, since the answer had to go though the review process, the student may no longer answer the exercise since because

    1. It is likely that we revealed the model solution to the student during the review process.
    2. In case of peer review, a new answer would have to be reviewed by other students again, and that would be unreasonable extra work for others.

    If the teacher for some reasoon feels bad for the student and wants to give them a new chance, the answers for this exercise should be reset, the reason should be recorded somewhere in the database, and the value of this column should be set to `NotStarted`. Deleting the whole user_exercise_state may also be wise. However, if we end up doing this for a teacher, we should make sure that the teacher realizes that they should not give an unfair advantage to anyone.
    */
    ReviewedAndLocked,
    /// In this stage the exercise has been locked due to chapter locking, but no review has been performed.
    Locked,
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
pub struct UserExerciseState {
    pub id: Uuid,
    pub user_id: Uuid,
    pub exercise_id: Uuid,
    pub course_id: Option<Uuid>,
    pub exam_id: Option<Uuid>,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
    pub deleted_at: Option<DateTime<Utc>>,
    pub score_given: Option<f32>,
    pub grading_progress: GradingProgress,
    pub activity_progress: ActivityProgress,
    pub reviewing_stage: ReviewingStage,
    pub selected_exercise_slide_id: Option<Uuid>,
}

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

    pub fn get_selected_exercise_slide_id(&self) -> ModelResult<Uuid> {
        self.selected_exercise_slide_id.ok_or_else(|| {
            ModelError::new(
                ModelErrorType::Generic,
                "No exercise slide selected.".to_string(),
                None,
            )
        })
    }
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
pub struct UserExerciseStateUpdate {
    pub id: Uuid,
    pub score_given: Option<f32>,
    pub activity_progress: ActivityProgress,
    pub reviewing_stage: ReviewingStage,
    pub grading_progress: GradingProgress,
}

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

pub struct UserCourseProgress {
    pub course_module_id: Uuid,
    pub course_module_name: String,
    pub course_module_order_number: i32,
    pub score_given: f32,
    pub score_required: Option<i32>,
    pub score_maximum: Option<u32>,
    pub total_exercises: Option<u32>,
    pub attempted_exercises: Option<i32>,
    pub attempted_exercises_required: Option<i32>,
}

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

pub struct UserCourseChapterExerciseProgress {
    pub exercise_id: Uuid,
    pub score_given: f32,
}

#[derive(Debug, Serialize, Deserialize, FromRow, PartialEq, Clone)]
pub struct DatabaseUserCourseChapterExerciseProgress {
    pub exercise_id: Uuid,
    pub score_given: Option<f32>,
}

#[derive(Debug, Serialize, Deserialize, FromRow, PartialEq, Clone)]
pub struct UserChapterMetrics {
    pub score_given: Option<f32>,
    pub attempted_exercises: Option<i64>,
}

#[derive(Debug, Serialize, Deserialize, FromRow, PartialEq, Clone)]
pub struct UserCourseMetrics {
    pub course_module_id: Uuid,
    pub score_given: Option<f32>,
    pub attempted_exercises: Option<i64>,
}

#[derive(Debug, Serialize, Deserialize, FromRow, PartialEq, Clone)]
pub struct CourseExerciseMetrics {
    course_module_id: Uuid,
    total_exercises: Option<i64>,
    score_maximum: Option<i64>,
}

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

pub struct ExerciseUserCounts {
    exercise_name: String,
    exercise_order_number: i32,
    page_order_number: i32,
    chapter_number: i32,
    exercise_id: Uuid,

    n_users_attempted: Option<i64>,

    n_users_with_some_points: Option<i64>,

    n_users_with_max_points: Option<i64>,
}

pub async fn get_course_metrics(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> ModelResult<Vec<CourseExerciseMetrics>> {
    let res = sqlx::query_as!(
        CourseExerciseMetrics,
        r"
SELECT chapters.course_module_id,
  COUNT(exercises.id) AS total_exercises,
  SUM(exercises.score_maximum) AS score_maximum
FROM courses c
  LEFT JOIN exercises ON (c.id = exercises.course_id)
  LEFT JOIN chapters ON (exercises.chapter_id = chapters.id)
WHERE exercises.deleted_at IS NULL
  AND c.id = $1
  AND chapters.course_module_id IS NOT NULL
GROUP BY chapters.course_module_id
        ",
        course_id
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_course_metrics_open_chapters(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> ModelResult<Vec<CourseExerciseMetrics>> {
    let res = sqlx::query_as!(
        CourseExerciseMetrics,
        r"
SELECT chapters.course_module_id,
  COUNT(exercises.id) AS total_exercises,
  SUM(exercises.score_maximum) AS score_maximum
FROM courses c
  LEFT JOIN exercises ON (c.id = exercises.course_id)
  LEFT JOIN chapters ON (exercises.chapter_id = chapters.id)
WHERE exercises.deleted_at IS NULL
  AND c.id = $1
  AND chapters.course_module_id IS NOT NULL
  AND chapters.deleted_at IS NULL
  AND ((chapters.opens_at < now()) OR chapters.opens_at IS NULL)
GROUP BY chapters.course_module_id
        ",
        course_id
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_course_metrics_indexed_by_module_id(
    conn: &mut PgConnection,
    course_id: Uuid,
    only_open_chapters: bool,
) -> ModelResult<HashMap<Uuid, CourseExerciseMetrics>> {
    let res = if only_open_chapters {
        get_course_metrics_open_chapters(conn, course_id)
            .await?
            .into_iter()
            .map(|x| (x.course_module_id, x))
            .collect()
    } else {
        get_course_metrics(conn, course_id)
            .await?
            .into_iter()
            .map(|x| (x.course_module_id, x))
            .collect()
    };
    Ok(res)
}

/// Gets course metrics for a single module.
pub async fn get_single_module_metrics(
    conn: &mut PgConnection,
    course_id: Uuid,
    course_module_id: Uuid,
    user_id: Uuid,
) -> ModelResult<UserCourseMetrics> {
    let res = sqlx::query!(
        "
SELECT COUNT(ues.exercise_id) AS attempted_exercises,
  COALESCE(SUM(ues.score_given), 0) AS score_given
FROM user_exercise_states AS ues
  LEFT JOIN exercises ON (ues.exercise_id = exercises.id)
  LEFT JOIN chapters ON (exercises.chapter_id = chapters.id)
WHERE chapters.course_module_id = $1
  AND ues.course_id = $2
  AND ues.activity_progress IN ('completed', 'submitted')
  AND ues.user_id = $3
  AND ues.deleted_at IS NULL
        ",
        course_module_id,
        course_id,
        user_id,
    )
    .map(|x| UserCourseMetrics {
        course_module_id,
        score_given: x.score_given,
        attempted_exercises: x.attempted_exercises,
    })
    .fetch_one(conn)
    .await?;
    Ok(res)
}

pub async fn get_user_course_metrics(
    conn: &mut PgConnection,
    course_id: Uuid,
    user_id: Uuid,
) -> ModelResult<Vec<UserCourseMetrics>> {
    let res = sqlx::query_as!(
        UserCourseMetrics,
        r"
SELECT chapters.course_module_id,
  COUNT(ues.exercise_id) AS attempted_exercises,
  COALESCE(SUM(ues.score_given), 0) AS score_given
FROM user_exercise_states AS ues
  LEFT JOIN exercises ON (ues.exercise_id = exercises.id)
  LEFT JOIN chapters ON (exercises.chapter_id = chapters.id)
WHERE ues.course_id = $1
  AND ues.activity_progress IN ('completed', 'submitted')
  AND ues.user_id = $2
  AND ues.deleted_at IS NULL
GROUP BY chapters.course_module_id;
        ",
        course_id,
        user_id,
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_user_course_metrics_only_open_chapters(
    conn: &mut PgConnection,
    course_id: Uuid,
    user_id: Uuid,
) -> ModelResult<Vec<UserCourseMetrics>> {
    let res = sqlx::query_as!(
        UserCourseMetrics,
        r"
SELECT chapters.course_module_id,
  COUNT(ues.exercise_id) AS attempted_exercises,
  COALESCE(SUM(ues.score_given), 0) AS score_given
FROM user_exercise_states AS ues
  LEFT JOIN exercises ON (ues.exercise_id = exercises.id)
  LEFT JOIN chapters ON (exercises.chapter_id = chapters.id)
WHERE ues.course_id = $1
  AND ues.activity_progress IN ('completed', 'submitted')
  AND ues.user_id = $2
  AND ues.deleted_at IS NULL
  AND chapters.deleted_at IS NULL
  AND ((chapters.opens_at < now()) OR chapters.opens_at IS NULL)
GROUP BY chapters.course_module_id;
        ",
        course_id,
        user_id,
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_user_course_metrics_indexed_by_module_id(
    conn: &mut PgConnection,
    course_id: Uuid,
    user_id: Uuid,
    only_open_chapters: bool,
) -> ModelResult<HashMap<Uuid, UserCourseMetrics>> {
    let res = if only_open_chapters {
        get_user_course_metrics_only_open_chapters(conn, course_id, user_id)
            .await?
            .into_iter()
            .map(|x| (x.course_module_id, x))
            .collect()
    } else {
        get_user_course_metrics(conn, course_id, user_id)
            .await?
            .into_iter()
            .map(|x| (x.course_module_id, x))
            .collect()
    };
    Ok(res)
}

pub async fn get_user_course_chapter_metrics(
    conn: &mut PgConnection,
    course_id: Uuid,
    exercise_ids: &[Uuid],
    user_id: Uuid,
) -> ModelResult<UserChapterMetrics> {
    let res = sqlx::query_as!(
        UserChapterMetrics,
        r#"
SELECT COUNT(ues.exercise_id) AS attempted_exercises,
  COALESCE(SUM(ues.score_given), 0) AS score_given
FROM user_exercise_states AS ues
WHERE ues.exercise_id IN (
    SELECT UNNEST($1::uuid [])
  )
  AND ues.deleted_at IS NULL
  AND ues.activity_progress IN ('completed', 'submitted')
  AND ues.user_id = $2
  AND ues.course_id = $3;
                "#,
        &exercise_ids,
        user_id,
        course_id
    )
    .fetch_one(conn)
    .await?;
    Ok(res)
}

pub async fn get_user_course_progress(
    conn: &mut PgConnection,
    course_id: Uuid,
    user_id: Uuid,
    only_open_chapters: bool,
) -> ModelResult<Vec<UserCourseProgress>> {
    let course_metrics =
        get_course_metrics_indexed_by_module_id(&mut *conn, course_id, only_open_chapters).await?;
    let user_metrics =
        get_user_course_metrics_indexed_by_module_id(conn, course_id, user_id, only_open_chapters)
            .await?;
    let course_name = courses::get_course(conn, course_id).await?.name;
    let course_modules = if only_open_chapters {
        course_modules::get_by_course_id_only_with_open_chapters(conn, course_id).await?
    } else {
        course_modules::get_by_course_id(conn, course_id).await?
    };
    merge_modules_with_metrics(course_modules, &course_metrics, &user_metrics, &course_name)
}

/// Gets the total amount of points that the user has received from an exam.
///
/// The caller should take into consideration that for an ongoing exam the result will be volatile.
pub async fn get_user_total_exam_points(
    conn: &mut PgConnection,
    user_id: Uuid,
    exam_id: Uuid,
) -> ModelResult<Option<f32>> {
    let res = sqlx::query!(
        r#"
SELECT SUM(score_given) AS "points"
FROM user_exercise_states
WHERE user_id = $2
  AND exam_id = $1
  AND deleted_at IS NULL
        "#,
        exam_id,
        user_id,
    )
    .map(|x| x.points)
    .fetch_one(conn)
    .await?;
    Ok(res)
}

fn merge_modules_with_metrics(
    course_modules: Vec<CourseModule>,
    course_metrics_by_course_module_id: &HashMap<Uuid, CourseExerciseMetrics>,
    user_metrics_by_course_module_id: &HashMap<Uuid, UserCourseMetrics>,
    default_course_module_name_placeholder: &str,
) -> ModelResult<Vec<UserCourseProgress>> {
    course_modules
        .into_iter()
        .map(|course_module| {
            let user_metrics = user_metrics_by_course_module_id.get(&course_module.id);
            let course_metrics = course_metrics_by_course_module_id.get(&course_module.id);
            let requirements = course_module.completion_policy.automatic();
            let progress = UserCourseProgress {
                course_module_id: course_module.id,
                // Only default course module doesn't have a name.
                course_module_name: course_module
                    .name
                    .unwrap_or_else(|| default_course_module_name_placeholder.to_string()),
                course_module_order_number: course_module.order_number,
                score_given: option_f32_to_f32_two_decimals_with_none_as_zero(
                    user_metrics.and_then(|x| x.score_given),
                ),
                score_required: requirements.and_then(|x| x.number_of_points_treshold),
                score_maximum: course_metrics
                    .and_then(|x| x.score_maximum)
                    .map(TryInto::try_into)
                    .transpose()?,
                total_exercises: course_metrics
                    .and_then(|x| x.total_exercises)
                    .map(TryInto::try_into)
                    .transpose()?,
                attempted_exercises: user_metrics
                    .and_then(|x| x.attempted_exercises)
                    .map(TryInto::try_into)
                    .transpose()?,
                attempted_exercises_required: requirements
                    .and_then(|x| x.number_of_exercises_attempted_treshold),
            };
            Ok(progress)
        })
        .collect::<ModelResult<_>>()
}

pub async fn get_user_course_chapter_exercises_progress(
    conn: &mut PgConnection,
    course_id: Uuid,
    exercise_ids: &[Uuid],
    user_id: Uuid,
) -> ModelResult<Vec<DatabaseUserCourseChapterExerciseProgress>> {
    let res = sqlx::query_as!(
        DatabaseUserCourseChapterExerciseProgress,
        r#"
SELECT COALESCE(ues.score_given, 0) AS score_given,
  ues.exercise_id AS exercise_id
FROM user_exercise_states AS ues
WHERE ues.deleted_at IS NULL
  AND ues.exercise_id IN (
    SELECT UNNEST($1::uuid [])
  )
  AND ues.course_id = $2
  AND ues.user_id = $3;
        "#,
        exercise_ids,
        course_id,
        user_id,
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_or_create_user_exercise_state(
    conn: &mut PgConnection,
    user_id: Uuid,
    exercise_id: Uuid,
    course_id: Option<Uuid>,
    exam_id: Option<Uuid>,
) -> ModelResult<UserExerciseState> {
    let existing = sqlx::query_as!(
        UserExerciseState,
        r#"
SELECT id,
  user_id,
  exercise_id,
  course_id,
  exam_id,
  created_at,
  updated_at,
  deleted_at,
  score_given,
  grading_progress AS "grading_progress: _",
  activity_progress AS "activity_progress: _",
  reviewing_stage AS "reviewing_stage: _",
  selected_exercise_slide_id
FROM user_exercise_states
WHERE user_id = $1
  AND exercise_id = $2
  AND (course_id = $3 OR exam_id = $4)
  AND deleted_at IS NULL
"#,
        user_id,
        exercise_id,
        course_id,
        exam_id
    )
    .fetch_optional(&mut *conn)
    .await?;

    let res = if let Some(existing) = existing {
        existing
    } else {
        sqlx::query_as!(
            UserExerciseState,
            r#"
    INSERT INTO user_exercise_states (user_id, exercise_id, course_id, exam_id)
    VALUES ($1, $2, $3, $4)
    RETURNING id,
      user_id,
      exercise_id,
      course_id,
      exam_id,
      created_at,
      updated_at,
      deleted_at,
      score_given,
      grading_progress as "grading_progress: _",
      activity_progress as "activity_progress: _",
      reviewing_stage AS "reviewing_stage: _",
      selected_exercise_slide_id
      "#,
            user_id,
            exercise_id,
            course_id,
            exam_id
        )
        .fetch_one(&mut *conn)
        .await?
    };
    Ok(res)
}

pub async fn get_or_create_user_exercise_state_for_users(
    conn: &mut PgConnection,
    user_ids: &[Uuid],
    exercise_id: Uuid,
    course_id: Option<Uuid>,
    exam_id: Option<Uuid>,
) -> ModelResult<HashMap<Uuid, UserExerciseState>> {
    let existing = sqlx::query_as!(
        UserExerciseState,
        r#"
SELECT id,
  user_id,
  exercise_id,
  course_id,
  exam_id,
  created_at,
  updated_at,
  deleted_at,
  score_given,
  grading_progress AS "grading_progress: _",
  activity_progress AS "activity_progress: _",
  reviewing_stage AS "reviewing_stage: _",
  selected_exercise_slide_id
FROM user_exercise_states
WHERE user_id IN (
    SELECT UNNEST($1::uuid [])
  )
  AND exercise_id = $2
  AND (course_id = $3 OR exam_id = $4)
  AND deleted_at IS NULL
"#,
        user_ids,
        exercise_id,
        course_id,
        exam_id
    )
    .fetch_all(&mut *conn)
    .await?;

    let mut res = HashMap::with_capacity(user_ids.len());
    for item in existing.into_iter() {
        res.insert(item.user_id, item);
    }

    let missing_user_ids = user_ids
        .iter()
        .filter(|user_id| !res.contains_key(user_id))
        .copied()
        .collect::<Vec<_>>();

    let created = sqlx::query_as!(
        UserExerciseState,
        r#"
    INSERT INTO user_exercise_states (user_id, exercise_id, course_id, exam_id)
    SELECT UNNEST($1::uuid []), $2, $3, $4
    RETURNING id,
      user_id,
      exercise_id,
      course_id,
      exam_id,
      created_at,
      updated_at,
      deleted_at,
      score_given,
      grading_progress as "grading_progress: _",
      activity_progress as "activity_progress: _",
      reviewing_stage AS "reviewing_stage: _",
      selected_exercise_slide_id
      "#,
        &missing_user_ids,
        exercise_id,
        course_id,
        exam_id
    )
    .fetch_all(&mut *conn)
    .await?;

    for item in created.into_iter() {
        res.insert(item.user_id, item);
    }
    Ok(res)
}

pub async fn get_by_id(conn: &mut PgConnection, id: Uuid) -> ModelResult<UserExerciseState> {
    let res = sqlx::query_as!(
        UserExerciseState,
        r#"
SELECT id,
  user_id,
  exercise_id,
  course_id,
  exam_id,
  created_at,
  updated_at,
  deleted_at,
  score_given,
  grading_progress AS "grading_progress: _",
  activity_progress AS "activity_progress: _",
  reviewing_stage AS "reviewing_stage: _",
  selected_exercise_slide_id
FROM user_exercise_states
WHERE id = $1
  AND deleted_at IS NULL
        "#,
        id,
    )
    .fetch_one(conn)
    .await?;
    Ok(res)
}

pub async fn get_by_ids(
    conn: &mut PgConnection,
    ids: &[Uuid],
) -> ModelResult<Vec<UserExerciseState>> {
    let res = sqlx::query_as!(
        UserExerciseState,
        r#"
SELECT id,
  user_id,
  exercise_id,
  course_id,
  exam_id,
  created_at,
  updated_at,
  deleted_at,
  score_given,
  grading_progress AS "grading_progress: _",
  activity_progress AS "activity_progress: _",
  reviewing_stage AS "reviewing_stage: _",
  selected_exercise_slide_id
FROM user_exercise_states
WHERE id = ANY($1)
AND deleted_at IS NULL
"#,
        &ids
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_user_total_course_points(
    conn: &mut PgConnection,
    user_id: Uuid,
    course_id: Uuid,
) -> ModelResult<Option<f32>> {
    let res = sqlx::query!(
        r#"
SELECT SUM(score_given) AS "total_points"
FROM user_exercise_states
WHERE user_id = $1
  AND course_id = $2
  AND deleted_at IS NULL
  GROUP BY user_id
        "#,
        user_id,
        course_id,
    )
    .map(|x| x.total_points)
    .fetch_one(conn)
    .await?;
    Ok(res)
}

pub async fn get_users_current_by_exercise(
    conn: &mut PgConnection,
    user_id: Uuid,
    exercise: &Exercise,
) -> ModelResult<UserExerciseState> {
    let course_or_exam_id =
        CourseOrExamId::from_course_and_exam_ids(exercise.course_id, exercise.exam_id)?;

    let user_exercise_state =
        get_user_exercise_state_if_exists(conn, user_id, exercise.id, course_or_exam_id)
            .await?
            .ok_or_else(|| {
                ModelError::new(
                    ModelErrorType::PreconditionFailed,
                    "Missing user exercise state.".to_string(),
                    None,
                )
            })?;
    Ok(user_exercise_state)
}

pub async fn get_user_exercise_state_if_exists(
    conn: &mut PgConnection,
    user_id: Uuid,
    exercise_id: Uuid,
    course_or_exam_id: CourseOrExamId,
) -> ModelResult<Option<UserExerciseState>> {
    let (course_id, exam_id) = course_or_exam_id.to_course_and_exam_ids();
    let res = sqlx::query_as!(
        UserExerciseState,
        r#"
SELECT id,
  user_id,
  exercise_id,
  course_id,
  exam_id,
  created_at,
  updated_at,
  deleted_at,
  score_given,
  grading_progress AS "grading_progress: _",
  activity_progress AS "activity_progress: _",
  reviewing_stage AS "reviewing_stage: _",
  selected_exercise_slide_id
FROM user_exercise_states
WHERE user_id = $1
  AND exercise_id = $2
  AND (course_id = $3 OR exam_id = $4)
  AND deleted_at IS NULL
      "#,
        user_id,
        exercise_id,
        course_id,
        exam_id
    )
    .fetch_optional(conn)
    .await?;
    Ok(res)
}

/// Returns true when user has chapter exercises pending teacher review.
pub async fn has_pending_manual_reviews_in_chapter(
    conn: &mut PgConnection,
    user_id: Uuid,
    chapter_id: Uuid,
) -> ModelResult<bool> {
    struct PendingManualReviewsInChapterRow {
        exists: bool,
    }

    let pending_manual_reviews = sqlx::query_as!(
        PendingManualReviewsInChapterRow,
        r#"
SELECT EXISTS (
    SELECT 1
    FROM user_exercise_states ues
    JOIN exercises e ON e.id = ues.exercise_id
    WHERE ues.user_id = $1
      AND e.chapter_id = $2
      AND ues.reviewing_stage = 'waiting_for_manual_grading'::reviewing_stage
      AND ues.deleted_at IS NULL
      AND e.deleted_at IS NULL
 ) as "exists!"
        "#,
        user_id,
        chapter_id
    )
    .fetch_one(conn)
    .await?
    .exists;
    Ok(pending_manual_reviews)
}

pub async fn get_all_for_user_and_course_or_exam(
    conn: &mut PgConnection,
    user_id: Uuid,
    course_or_exam_id: CourseOrExamId,
) -> ModelResult<Vec<UserExerciseState>> {
    let (course_id, exam_id) = course_or_exam_id.to_course_and_exam_ids();
    let res = sqlx::query_as!(
        UserExerciseState,
        r#"
SELECT id,
  user_id,
  exercise_id,
  course_id,
  exam_id,
  created_at,
  updated_at,
  deleted_at,
  score_given,
  grading_progress AS "grading_progress: _",
  activity_progress AS "activity_progress: _",
  reviewing_stage AS "reviewing_stage: _",
  selected_exercise_slide_id
FROM user_exercise_states
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(res)
}

pub async fn upsert_selected_exercise_slide_id(
    conn: &mut PgConnection,
    user_id: Uuid,
    exercise_id: Uuid,
    course_id: Option<Uuid>,
    exam_id: Option<Uuid>,
    selected_exercise_slide_id: Option<Uuid>,
) -> ModelResult<()> {
    let existing = sqlx::query!(
        "
SELECT
FROM user_exercise_states
WHERE user_id = $1
  AND exercise_id = $2
  AND (course_id = $3 OR exam_id = $4)
  AND deleted_at IS NULL
",
        user_id,
        exercise_id,
        course_id,
        exam_id
    )
    .fetch_optional(&mut *conn)
    .await?;
    if existing.is_some() {
        sqlx::query!(
            "
UPDATE user_exercise_states
SET selected_exercise_slide_id = $4
WHERE user_id = $1
  AND exercise_id = $2
  AND (course_id = $3 OR exam_id = $5)
  AND deleted_at IS NULL
    ",
            user_id,
            exercise_id,
            course_id,
            selected_exercise_slide_id,
            exam_id
        )
        .execute(&mut *conn)
        .await?;
    } else {
        sqlx::query!(
            "
    INSERT INTO user_exercise_states (
        user_id,
        exercise_id,
        course_id,
        selected_exercise_slide_id,
        exam_id
      )
    VALUES ($1, $2, $3, $4, $5)
    ",
            user_id,
            exercise_id,
            course_id,
            selected_exercise_slide_id,
            exam_id
        )
        .execute(&mut *conn)
        .await?;
    }
    Ok(())
}

/// TODO: should be moved to the user_exercise_state_updater as a private module so that this cannot be called outside of that module
pub async fn update(
    conn: &mut PgConnection,
    user_exercise_state_update: UserExerciseStateUpdate,
) -> ModelResult<UserExerciseState> {
    let res = sqlx::query_as!(
        UserExerciseState,
        r#"
UPDATE user_exercise_states
SET score_given = $1,
  activity_progress = $2,
  reviewing_stage = $3,
  grading_progress = $4
WHERE id = $5
  AND deleted_at IS NULL
RETURNING id,
  user_id,
  exercise_id,
  course_id,
  exam_id,
  created_at,
  updated_at,
  deleted_at,
  score_given,
  grading_progress AS "grading_progress: _",
  activity_progress AS "activity_progress: _",
  reviewing_stage AS "reviewing_stage: _",
  selected_exercise_slide_id
        "#,
        user_exercise_state_update.score_given,
        user_exercise_state_update.activity_progress as ActivityProgress,
        user_exercise_state_update.reviewing_stage as ReviewingStage,
        user_exercise_state_update.grading_progress as GradingProgress,
        user_exercise_state_update.id,
    )
    .fetch_one(conn)
    .await?;
    Ok(res)
}

pub async fn update_reviewing_stage(
    conn: &mut PgConnection,
    user_id: Uuid,
    course_or_exam_id: CourseOrExamId,
    exercise_id: Uuid,
    new_reviewing_stage: ReviewingStage,
) -> ModelResult<UserExerciseState> {
    let (course_id, exam_id) = course_or_exam_id.to_course_and_exam_ids();
    let res = sqlx::query_as!(
        UserExerciseState,
        r#"
UPDATE user_exercise_states
SET reviewing_stage = $5
WHERE user_id = $1
AND (course_id = $2 OR exam_id = $3)
AND exercise_id = $4
RETURNING id,
  user_id,
  exercise_id,
  course_id,
  exam_id,
  created_at,
  updated_at,
  deleted_at,
  score_given,
  grading_progress AS "grading_progress: _",
  activity_progress AS "activity_progress: _",
  reviewing_stage AS "reviewing_stage: _",
  selected_exercise_slide_id
        "#,
        user_id,
        course_id,
        exam_id,
        exercise_id,
        new_reviewing_stage as ReviewingStage
    )
    .fetch_one(conn)
    .await?;
    Ok(res)
}

/// TODO: should be removed
pub async fn update_exercise_progress(
    conn: &mut PgConnection,
    id: Uuid,
    reviewing_stage: ReviewingStage,
) -> ModelResult<UserExerciseState> {
    let res = sqlx::query_as!(
        UserExerciseState,
        r#"
UPDATE user_exercise_states
SET reviewing_stage = $1
WHERE id = $2
  AND deleted_at IS NULL
RETURNING id,
  user_id,
  exercise_id,
  course_id,
  exam_id,
  created_at,
  updated_at,
  deleted_at,
  score_given,
  grading_progress AS "grading_progress: _",
  activity_progress AS "activity_progress: _",
  reviewing_stage AS "reviewing_stage: _",
  selected_exercise_slide_id
        "#,
        reviewing_stage as ReviewingStage,
        id
    )
    .fetch_one(conn)
    .await?;
    Ok(res)
}

/// Convenience struct that combines user state to the exercise.
///
/// Many operations require information about both the user state and the exercise. However, because
/// exercises can either belong to a course or an exam it can get difficult to track the proper context.
pub struct ExerciseWithUserState {
    exercise: Exercise,
    user_exercise_state: UserExerciseState,
    type_data: EwusCourseOrExam,
}

impl ExerciseWithUserState {
    pub fn new(exercise: Exercise, user_exercise_state: UserExerciseState) -> ModelResult<Self> {
        let state = EwusCourseOrExam::from_exercise_and_user_exercise_state(
            &exercise,
            &user_exercise_state,
        )?;
        Ok(Self {
            exercise,
            user_exercise_state,
            type_data: state,
        })
    }

    /// Provides a reference to the inner `Exercise`.
    pub fn exercise(&self) -> &Exercise {
        &self.exercise
    }

    /// Provides a reference to the inner `UserExerciseState`.
    pub fn user_exercise_state(&self) -> &UserExerciseState {
        &self.user_exercise_state
    }

    pub fn exercise_context(&self) -> &EwusCourseOrExam {
        &self.type_data
    }

    pub fn set_user_exercise_state(
        &mut self,
        user_exercise_state: UserExerciseState,
    ) -> ModelResult<()> {
        self.type_data = EwusCourseOrExam::from_exercise_and_user_exercise_state(
            &self.exercise,
            &user_exercise_state,
        )?;
        self.user_exercise_state = user_exercise_state;
        Ok(())
    }

    pub fn is_exam_exercise(&self) -> bool {
        match self.type_data {
            EwusCourseOrExam::Course(_) => false,
            EwusCourseOrExam::Exam(_) => true,
        }
    }
}

pub struct EwusCourse {
    pub course_id: Uuid,
}

pub struct EwusExam {
    pub exam_id: Uuid,
}

pub enum EwusContext<C, E> {
    Course(C),
    Exam(E),
}

pub enum EwusCourseOrExam {
    Course(EwusCourse),
    Exam(EwusExam),
}

impl EwusCourseOrExam {
    pub fn from_exercise_and_user_exercise_state(
        exercise: &Exercise,
        user_exercise_state: &UserExerciseState,
    ) -> ModelResult<Self> {
        if exercise.id == user_exercise_state.exercise_id {
            let course_id = exercise.course_id;
            let exam_id = exercise.exam_id;
            match (course_id, exam_id) {
                (None, Some(exam_id)) => Ok(Self::Exam(EwusExam { exam_id })),
                (Some(course_id), None) => Ok(Self::Course(EwusCourse { course_id })),
                _ => Err(ModelError::new(
                    ModelErrorType::Generic,
                    "Invalid initializer data.".to_string(),
                    None,
                )),
            }
        } else {
            Err(ModelError::new(
                ModelErrorType::Generic,
                "Exercise doesn't match the state.".to_string(),
                None,
            ))
        }
    }
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
pub struct CourseUserPoints {
    pub user_id: Uuid,
    pub points_for_each_chapter: Vec<CourseUserPointsInner>,
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
pub struct CourseUserPointsInner {
    pub chapter_number: i32,
    pub points_for_chapter: f32,
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
pub struct ExamUserPoints {
    pub user_id: Uuid,
    pub email: String,
    pub points_for_exercise: Vec<ExamUserPointsInner>,
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
pub struct ExamUserPointsInner {
    pub exercise_id: Uuid,
    pub score_given: f32,
}

pub fn stream_course_points(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> impl Stream<Item = sqlx::Result<CourseUserPoints>> + '_ {
    sqlx::query!(
        "
SELECT user_id,
  to_jsonb(array_agg(to_jsonb(uue) - 'email' - 'user_id')) AS points_for_each_chapter
FROM (
    SELECT ud.email,
      u.id AS user_id,
      c.chapter_number,
      COALESCE(SUM(ues.score_given), 0) AS points_for_chapter
    FROM user_exercise_states ues
      JOIN users u ON u.id = ues.user_id
      JOIN user_details ud ON ud.user_id = u.id
      JOIN exercises e ON e.id = ues.exercise_id
      JOIN chapters c on e.chapter_id = c.id
    WHERE ues.course_id = $1
      AND ues.deleted_at IS NULL
      AND c.deleted_at IS NULL
      AND u.deleted_at IS NULL
      AND e.deleted_at IS NULL
    GROUP BY ud.email,
      u.id,
      c.chapter_number
  ) as uue
GROUP BY user_id

",
        course_id
    )
    .try_map(|i| {
        let user_id = i.user_id;
        let points_for_each_chapter = i.points_for_each_chapter.unwrap_or(Value::Null);
        serde_json::from_value(points_for_each_chapter)
            .map(|points_for_each_chapter| CourseUserPoints {
                user_id,
                points_for_each_chapter,
            })
            .map_err(|e| sqlx::Error::Decode(Box::new(e)))
    })
    .fetch(conn)
}

pub fn stream_exam_points(
    conn: &mut PgConnection,
    exam_id: Uuid,
) -> impl Stream<Item = sqlx::Result<ExamUserPoints>> + '_ {
    sqlx::query!(
        "
SELECT user_id,
  email,
  to_jsonb(array_agg(to_jsonb(uue) - 'email' - 'user_id')) AS points_for_exercises
FROM (
    SELECT u.id AS user_id,
      ud.email,
      exercise_id,
      COALESCE(score_given, 0) as score_given
    FROM user_exercise_states ues
      JOIN users u ON u.id = ues.user_id
      JOIN user_details ud ON ud.user_id = u.id
      JOIN exercises e ON e.id = ues.exercise_id
    WHERE ues.exam_id = $1
      AND ues.deleted_at IS NULL
      AND u.deleted_at IS NULL
      AND e.deleted_at IS NULL
  ) as uue
GROUP BY user_id,
  email
",
        exam_id
    )
    .try_map(|i| {
        let user_id = i.user_id;
        let points_for_exercises = i.points_for_exercises.unwrap_or(Value::Null);
        serde_json::from_value(points_for_exercises)
            .map(|points_for_exercise| ExamUserPoints {
                user_id,
                points_for_exercise,
                email: i.email,
            })
            .map_err(|e| sqlx::Error::Decode(Box::new(e)))
    })
    .fetch(conn)
}

pub async fn get_course_users_counts_by_exercise(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> ModelResult<Vec<ExerciseUserCounts>> {
    let res = sqlx::query_as!(
        ExerciseUserCounts,
        r#"
SELECT exercises.name as exercise_name,
  exercises.order_number as exercise_order_number,
  pages.order_number as page_order_number,
  chapters.chapter_number,
  stat_data.*
FROM (
    SELECT exercise_id,
      COUNT(DISTINCT user_id) FILTER (
        WHERE ues.activity_progress = 'completed'
      ) as n_users_attempted,
      COUNT(DISTINCT user_id) FILTER (
        WHERE ues.score_given IS NOT NULL
          and ues.score_given > 0
          AND ues.activity_progress = 'completed'
      ) as n_users_with_some_points,
      COUNT(DISTINCT user_id) FILTER (
        WHERE ues.score_given IS NOT NULL
          and ues.score_given >= exercises.score_maximum
          and ues.activity_progress = 'completed'
      ) as n_users_with_max_points
    FROM exercises
      JOIN user_exercise_states ues on exercises.id = ues.exercise_id
    WHERE exercises.course_id = $1
      AND exercises.deleted_at IS NULL
      AND ues.deleted_at IS NULL
    GROUP BY exercise_id
  ) as stat_data
  JOIN exercises ON stat_data.exercise_id = exercises.id
  JOIN pages on exercises.page_id = pages.id
  JOIN chapters on pages.chapter_id = chapters.id
WHERE exercises.deleted_at IS NULL
  AND pages.deleted_at IS NULL
  AND chapters.deleted_at IS NULL
          "#,
        course_id
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

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

pub struct ExportedUserExerciseState {
    pub id: Uuid,
    pub user_id: Uuid,
    pub exercise_id: Uuid,
    pub course_id: Option<Uuid>,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
    pub score_given: Option<f32>,
    pub grading_progress: GradingProgress,
    pub activity_progress: ActivityProgress,
    pub reviewing_stage: ReviewingStage,
    pub selected_exercise_slide_id: Option<Uuid>,
}

pub fn stream_user_exercise_states_for_course<'a>(
    conn: &'a mut PgConnection,
    course_ids: &'a [Uuid],
) -> impl Stream<Item = sqlx::Result<ExportedUserExerciseState>> + 'a {
    sqlx::query_as!(
        ExportedUserExerciseState,
        r#"
SELECT id,
  user_id,
  exercise_id,
  course_id,
  created_at,
  updated_at,
  score_given,
  grading_progress AS "grading_progress: _",
  activity_progress AS "activity_progress: _",
  reviewing_stage AS "reviewing_stage: _",
  selected_exercise_slide_id
FROM user_exercise_states
WHERE course_id = ANY($1)
  AND deleted_at IS NULL
        "#,
        course_ids
    )
    .fetch(conn)
}

pub async fn get_all_for_course(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> ModelResult<Vec<UserExerciseState>> {
    let res = sqlx::query_as!(
        UserExerciseState,
        r#"
SELECT id,
  user_id,
  exercise_id,
  course_id,
  exam_id,
  created_at,
  updated_at,
  deleted_at,
  score_given,
  grading_progress AS "grading_progress: _",
  activity_progress AS "activity_progress: _",
  reviewing_stage AS "reviewing_stage: _",
  selected_exercise_slide_id
FROM user_exercise_states
WHERE course_id = $1
  AND deleted_at IS NULL
"#,
        course_id,
    )
    .fetch_all(&mut *conn)
    .await?;
    Ok(res)
}

pub async fn get_returned_exercise_ids_for_user_and_course(
    conn: &mut PgConnection,
    exercise_ids: &[Uuid],
    user_id: Uuid,
    course_id: Uuid,
) -> ModelResult<Vec<Uuid>> {
    #[derive(sqlx::FromRow)]
    struct ExerciseIdRow {
        exercise_id: Uuid,
    }

    let returned_exercise_ids: Vec<ExerciseIdRow> = sqlx::query_as::<_, ExerciseIdRow>(
        r#"
        SELECT DISTINCT exercise_id
        FROM user_exercise_states
        WHERE exercise_id = ANY($1::uuid[])
          AND user_id = $2
          AND course_id = $3
          AND deleted_at IS NULL
          AND activity_progress IN ('completed', 'submitted')
        "#,
    )
    .bind(exercise_ids)
    .bind(user_id)
    .bind(course_id)
    .fetch_all(conn)
    .await?;

    Ok(returned_exercise_ids
        .into_iter()
        .map(|r| r.exercise_id)
        .collect())
}

#[cfg(test)]
mod tests {
    use chrono::TimeZone;

    use super::*;
    use crate::{
        chapters::NewChapter,
        exercise_slides, exercises,
        library::content_management::create_new_chapter,
        pages::{NewPage, insert_page},
        test_helper::*,
    };

    mod getting_single_module_course_metrics {
        use super::*;

        #[tokio::test]
        async fn works_without_any_user_exercise_states() {
            insert_data!(:tx, :user, :org, :course, instance: _instance, :course_module);
            let res = get_single_module_metrics(tx.as_mut(), course, course_module.id, user).await;
            assert!(res.is_ok())
        }
    }

    #[test]
    fn merges_course_modules_with_metrics() {
        let timestamp = Utc.with_ymd_and_hms(2022, 6, 22, 0, 0, 0).unwrap();
        let module_id = Uuid::parse_str("9e831ecc-9751-42f1-ae7e-9b2f06e523e8").unwrap();
        let course_modules = vec![
            CourseModule::new(
                module_id,
                Uuid::parse_str("3fa4bee6-7390-415e-968f-ecdc5f28330e").unwrap(),
            )
            .set_timestamps(timestamp, timestamp, None)
            .set_registration_info(None, Some(5.0), None, false),
        ];
        let course_metrics_by_course_module_id = HashMap::from([(
            module_id,
            CourseExerciseMetrics {
                course_module_id: module_id,
                total_exercises: Some(4),
                score_maximum: Some(10),
            },
        )]);
        let user_metrics_by_course_module_id = HashMap::from([(
            module_id,
            UserCourseMetrics {
                course_module_id: module_id,
                score_given: Some(1.0),
                attempted_exercises: Some(3),
            },
        )]);
        let metrics = merge_modules_with_metrics(
            course_modules,
            &course_metrics_by_course_module_id,
            &user_metrics_by_course_module_id,
            "Default module",
        )
        .unwrap();
        assert_eq!(metrics.len(), 1);
        let metric = metrics.first().unwrap();
        assert_eq!(metric.attempted_exercises, Some(3));
        assert_eq!(&metric.course_module_name, "Default module");
        assert_eq!(metric.score_given, 1.0);
        assert_eq!(metric.score_maximum, Some(10));
        assert_eq!(metric.total_exercises, Some(4));
    }

    #[tokio::test]
    async fn get_user_course_progress_open_closed_chapters() {
        insert_data!(:tx, :user, :org, :course, instance: _instance, :course_module, chapter: _chapter, page: _page);
        // creating a new course inserts one empty default module.
        // there will be one empty module and one module with two chapters
        // one of which is open
        let (new_chapter, _) = create_new_chapter(
            tx.as_mut(),
            PKeyPolicy::Generate,
            &NewChapter {
                name: "best chapter 1".to_string(),
                color: None,
                course_id: course,
                chapter_number: 2,
                front_page_id: None,
                deadline: None,
                opens_at: Some(
                    DateTime::parse_from_str(
                        // chapter is not open yet
                        "2983 Apr 13 12:09:14 +0000",
                        "%Y %b %d %H:%M:%S %z",
                    )
                    .unwrap()
                    .to_utc(),
                ),
                course_module_id: Some(course_module.id),
            },
            user,
            |_, _, _| unimplemented!(),
            |_| unimplemented!(),
        )
        .await
        .unwrap();

        // insert a page with an exercise to the not-open chapter
        let page = insert_page(
            tx.as_mut(),
            NewPage {
                exercises: vec![],
                exercise_slides: vec![],
                exercise_tasks: vec![],
                content: vec![],
                url_path: "/page1".to_string(),
                title: "title".to_string(),
                course_id: Some(course),
                exam_id: None,
                chapter_id: Some(new_chapter.id),
                front_page_of_chapter_id: Some(new_chapter.id),
                content_search_language: None,
            },
            user,
            |_, _, _| unimplemented!(),
            |_| unimplemented!(),
        )
        .await
        .unwrap();
        let ex = exercises::insert(
            tx.as_mut(),
            PKeyPolicy::Generate,
            course,
            "ex 1",
            page.id,
            new_chapter.id,
            1,
        )
        .await
        .unwrap();
        exercise_slides::insert(tx.as_mut(), PKeyPolicy::Generate, ex, 1)
            .await
            .unwrap();
        // another chapter
        let (new_chapter2, _) = create_new_chapter(
            tx.as_mut(),
            PKeyPolicy::Generate,
            &NewChapter {
                name: "best chapter 2".to_string(),
                color: None,
                course_id: course,
                chapter_number: 3,
                front_page_id: None,
                deadline: None,
                opens_at: Some(
                    DateTime::parse_from_str(
                        // chapter is open yet
                        "1983 Apr 13 12:09:14 +0000",
                        "%Y %b %d %H:%M:%S %z",
                    )
                    .unwrap()
                    .to_utc(),
                ),
                course_module_id: Some(course_module.id),
            },
            user,
            |_, _, _| unimplemented!(),
            |_| unimplemented!(),
        )
        .await
        .unwrap();

        // insert a page with an exercise to the not-open chapter
        let page2 = insert_page(
            tx.as_mut(),
            NewPage {
                exercises: vec![],
                exercise_slides: vec![],
                exercise_tasks: vec![],
                content: vec![],
                url_path: "/page2".to_string(),
                title: "title".to_string(),
                course_id: Some(course),
                exam_id: None,
                chapter_id: Some(new_chapter2.id),
                front_page_of_chapter_id: Some(new_chapter2.id),
                content_search_language: None,
            },
            user,
            |_, _, _| unimplemented!(),
            |_| unimplemented!(),
        )
        .await
        .unwrap();
        let ex = exercises::insert(
            tx.as_mut(),
            PKeyPolicy::Generate,
            course,
            "ex 1",
            page2.id,
            new_chapter2.id,
            1,
        )
        .await
        .unwrap();
        exercise_slides::insert(tx.as_mut(), PKeyPolicy::Generate, ex, 1)
            .await
            .unwrap();

        // should list all modules and exercises
        let progress_all = get_user_course_progress(tx.as_mut(), course, user, false)
            .await
            .unwrap();
        // should only list modules with chapters and the exercises from open chapters
        let progress_open_chapters = get_user_course_progress(tx.as_mut(), course, user, true)
            .await
            .unwrap();

        assert_ne!(progress_all, progress_open_chapters);
        assert_eq!(progress_all.len(), 2);
        assert_eq!(progress_open_chapters.len(), 1);
        assert_eq!(
            progress_all[1].course_module_id,
            progress_open_chapters[0].course_module_id
        );
        assert_eq!(progress_all[1].total_exercises, Some(2));
        assert_eq!(progress_open_chapters[0].total_exercises, Some(1));
    }

    #[tokio::test]
    async fn get_user_course_progress_filter_out_closed_module() {
        insert_data!(:tx, :user, :org, :course, instance: _instance, :course_module);
        // creating a new course inserts one empty default module.
        // there will be one other module with one closed chapter only
        let (new_chapter, _) = create_new_chapter(
            tx.as_mut(),
            PKeyPolicy::Generate,
            &NewChapter {
                name: "best chapter".to_string(),
                color: None,
                course_id: course,
                chapter_number: 2,
                front_page_id: None,
                deadline: None,
                opens_at: Some(
                    DateTime::parse_from_str(
                        // chapter is not open yet
                        "2983 Apr 13 12:09:14 +0000",
                        "%Y %b %d %H:%M:%S %z",
                    )
                    .unwrap()
                    .to_utc(),
                ),
                course_module_id: Some(course_module.id),
            },
            user,
            |_, _, _| unimplemented!(),
            |_| unimplemented!(),
        )
        .await
        .unwrap();

        // insert a page with an exercise to the not-open chapter
        let page = insert_page(
            tx.as_mut(),
            NewPage {
                exercises: vec![],
                exercise_slides: vec![],
                exercise_tasks: vec![],
                content: vec![],
                url_path: "/page2".to_string(),
                title: "title".to_string(),
                course_id: Some(course),
                exam_id: None,
                chapter_id: Some(new_chapter.id),
                front_page_of_chapter_id: Some(new_chapter.id),
                content_search_language: None,
            },
            user,
            |_, _, _| unimplemented!(),
            |_| unimplemented!(),
        )
        .await
        .unwrap();
        let ex = exercises::insert(
            tx.as_mut(),
            PKeyPolicy::Generate,
            course,
            "ex 1",
            page.id,
            new_chapter.id,
            1,
        )
        .await
        .unwrap();
        exercise_slides::insert(tx.as_mut(), PKeyPolicy::Generate, ex, 1)
            .await
            .unwrap();

        // should list one empty module and one module with only one chapter
        // which is closed
        let progress_all = get_user_course_progress(tx.as_mut(), course, user, false)
            .await
            .unwrap();
        // should be empty
        let progress_open_chapters_modules =
            get_user_course_progress(tx.as_mut(), course, user, true)
                .await
                .unwrap();

        assert_ne!(progress_all, progress_open_chapters_modules);
        assert_eq!(progress_all.len(), 2);
        assert_eq!(progress_open_chapters_modules.len(), 0);
        assert_eq!(progress_all[1].total_exercises, Some(1));
    }

    #[tokio::test]
    async fn has_pending_manual_reviews_in_chapter_reflects_review_state() {
        insert_data!(
            :tx,
            :user,
            :org,
            :course,
            instance: _instance,
            :course_module,
            chapter: chapter_id,
            page: _page_id,
            exercise: exercise_id,
            slide: _exercise_slide_id,
            task: _exercise_task_id
        );

        exercises::update_teacher_reviews_answer_after_locking(tx.as_mut(), exercise_id, true)
            .await
            .unwrap();
        get_or_create_user_exercise_state(tx.as_mut(), user, exercise_id, Some(course), None)
            .await
            .unwrap();

        update_reviewing_stage(
            tx.as_mut(),
            user,
            CourseOrExamId::Course(course),
            exercise_id,
            ReviewingStage::WaitingForManualGrading,
        )
        .await
        .unwrap();

        let has_pending = has_pending_manual_reviews_in_chapter(tx.as_mut(), user, chapter_id)
            .await
            .unwrap();
        assert!(has_pending);

        update_reviewing_stage(
            tx.as_mut(),
            user,
            CourseOrExamId::Course(course),
            exercise_id,
            ReviewingStage::ReviewedAndLocked,
        )
        .await
        .unwrap();

        let has_pending = has_pending_manual_reviews_in_chapter(tx.as_mut(), user, chapter_id)
            .await
            .unwrap();
        assert!(!has_pending);
    }

    #[tokio::test]
    async fn has_pending_manual_reviews_in_chapter_counts_self_review_manual_flows() {
        insert_data!(
            :tx,
            :user,
            :org,
            :course,
            instance: _instance,
            :course_module,
            chapter: chapter_id,
            page: _page_id,
            exercise: exercise_id,
            slide: _exercise_slide_id,
            task: _exercise_task_id
        );

        exercises::set_exercise_to_use_exercise_specific_peer_or_self_review_config(
            tx.as_mut(),
            exercise_id,
            false,
            true,
            false,
        )
        .await
        .unwrap();
        get_or_create_user_exercise_state(tx.as_mut(), user, exercise_id, Some(course), None)
            .await
            .unwrap();

        update_reviewing_stage(
            tx.as_mut(),
            user,
            CourseOrExamId::Course(course),
            exercise_id,
            ReviewingStage::WaitingForManualGrading,
        )
        .await
        .unwrap();

        let has_pending = has_pending_manual_reviews_in_chapter(tx.as_mut(), user, chapter_id)
            .await
            .unwrap();
        assert!(has_pending);
    }
}