headless_lms_models/

pages.rs

use std::collections::{HashMap, hash_map};

use futures::future::{BoxFuture, OptionFuture};
use headless_lms_utils::document_schema_processor::{
    GutenbergBlock, contains_blocks_not_allowed_in_top_level_pages, filter_lock_chapter_blocks,
    replace_duplicate_client_ids,
};
use itertools::Itertools;
use percent_encoding::{AsciiSet, CONTROLS, percent_decode_str, utf8_percent_encode};
use serde_json::{Value, json};
use sqlx::{AssertSqlSafe, Postgres, QueryBuilder, Row};
use url::Url;
use utoipa::ToSchema;

use crate::{
    CourseOrExamId, SpecFetcher,
    chapters::{
        self, Chapter, DatabaseChapter, course_chapters, get_chapter, get_chapter_by_page_id,
    },
    course_instances::{self, CourseInstance},
    courses::{self, Course, CourseContextData, CourseMaterialCourse},
    exercise_service_info::{self, ExerciseServiceInfoApi},
    exercise_services::{get_internal_public_spec_url, get_model_solution_url},
    exercise_slides::ExerciseSlide,
    exercise_tasks::ExerciseTask,
    exercises::Exercise,
    organizations::Organization,
    page_history::{self, HistoryChangeReason, PageHistoryContent},
    peer_or_self_review_configs::CmsPeerOrSelfReviewConfig,
    peer_or_self_review_questions::{
        CmsPeerOrSelfReviewQuestion, normalize_cms_peer_or_self_review_questions,
    },
    prelude::*,
    user_chapter_locking_statuses,
    user_course_settings::{self, UserCourseSettings},
    user_exercise_states,
};

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

pub struct Page {
    pub id: Uuid,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
    pub course_id: Option<Uuid>,
    pub exam_id: Option<Uuid>,
    pub chapter_id: Option<Uuid>,
    pub url_path: String,
    pub title: String,
    pub deleted_at: Option<DateTime<Utc>>,
    // should always be a Vec<GutenbergBlock>, but is more convenient to keep as Value for sqlx
    pub content: serde_json::Value,
    pub order_number: i32,
    pub copied_from: Option<Uuid>,
    pub hidden: bool,
    pub page_language_group_id: Option<Uuid>,
}

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

pub struct PageInfo {
    pub page_id: Uuid,
    pub page_title: String,
    pub course_id: Option<Uuid>,
    pub course_name: Option<String>,
    pub course_slug: Option<String>,
    pub organization_slug: Option<String>,
}

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

pub struct PageAudioFiles {
    pub id: Uuid,
    pub page_id: Uuid,
    pub created_at: DateTime<Utc>,
    pub deleted_at: Option<DateTime<Utc>>,
    pub path: String,
    pub mime_type: String,
}

impl Page {
    pub fn blocks_cloned(&self) -> ModelResult<Vec<GutenbergBlock>> {
        serde_json::from_value(self.content.clone()).map_err(Into::into)
    }
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, Copy, ToSchema)]
#[serde(rename_all = "snake_case")]
pub enum LockChapterContentState {
    NotLocked,
    WaitingTeacherReview,
    Visible,
}

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

pub struct CoursePageWithUserData {
    pub page: Page,
    pub instance: Option<CourseInstance>,
    pub settings: Option<UserCourseSettings>,
    pub course: Option<CourseMaterialCourse>,
    pub organization: Option<Organization>,
    pub lock_chapter_content_state: Option<LockChapterContentState>,
    /// If true, the frontend needs to update the url in the browser to match the path in the page object without reloading the page.
    pub was_redirected: bool,
    pub is_test_mode: bool,
}

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

pub struct PageWithExercises {
    #[serde(flatten)]
    pub page: Page,
    pub exercises: Vec<Exercise>,
}

#[derive(Default)]
struct CourseMaterialPageContentFilterCache {
    chapter_lock_content_states: HashMap<Uuid, LockChapterContentState>,
    course_chapter_locking_enabled: HashMap<Uuid, bool>,
}

const URL_PATH_ENCODE_SET: &AsciiSet = &CONTROLS
    .add(b' ')
    .add(b'!')
    .add(b'"')
    .add(b'#')
    .add(b'$')
    .add(b'%')
    .add(b'&')
    .add(b'\'')
    .add(b'(')
    .add(b')')
    .add(b'*')
    .add(b'+')
    .add(b',')
    .add(b':')
    .add(b';')
    .add(b'<')
    .add(b'=')
    .add(b'>')
    .add(b'?')
    .add(b'@')
    .add(b'[')
    .add(b'\\')
    .add(b']')
    .add(b'^')
    .add(b'`')
    .add(b'{')
    .add(b'|')
    .add(b'}');

/// Percent-encodes URL path characters that are not part of the safe path alphabet.
fn normalize_url_path_for_storage(url_path: &str) -> String {
    let decoded = percent_decode_str(url_path).decode_utf8_lossy();
    utf8_percent_encode(decoded.trim(), URL_PATH_ENCODE_SET).to_string()
}

async fn get_lock_chapter_content_state_for_page(
    conn: &mut PgConnection,
    user_id: Option<Uuid>,
    page: &Page,
    cache: &mut CourseMaterialPageContentFilterCache,
) -> ModelResult<Option<LockChapterContentState>> {
    let Some(chapter_id) = page.chapter_id else {
        return Ok(None);
    };

    if let Some(state) = cache.chapter_lock_content_states.get(&chapter_id).copied() {
        return Ok(Some(state));
    }

    let state = match (user_id, page.course_id) {
        (Some(user_id), Some(course_id)) => {
            let course_chapter_locking_enabled = if let Some(enabled) = cache
                .course_chapter_locking_enabled
                .get(&course_id)
                .copied()
            {
                enabled
            } else {
                let enabled = courses::get_course(conn, course_id)
                    .await?
                    .chapter_locking_enabled;
                cache
                    .course_chapter_locking_enabled
                    .insert(course_id, enabled);
                enabled
            };

            if !course_chapter_locking_enabled {
                LockChapterContentState::NotLocked
            } else {
                let is_locked = matches!(
                    user_chapter_locking_statuses::get_or_init_status(
                        conn,
                        user_id,
                        chapter_id,
                        Some(course_id),
                        Some(course_chapter_locking_enabled),
                    )
                    .await?,
                    Some(user_chapter_locking_statuses::ChapterLockingStatus::CompletedAndLocked)
                );

                if !is_locked {
                    LockChapterContentState::NotLocked
                } else if user_exercise_states::has_pending_manual_reviews_in_chapter(
                    conn, user_id, chapter_id,
                )
                .await?
                {
                    LockChapterContentState::WaitingTeacherReview
                } else {
                    LockChapterContentState::Visible
                }
            }
        }
        _ => LockChapterContentState::NotLocked,
    };

    cache.chapter_lock_content_states.insert(chapter_id, state);
    Ok(Some(state))
}

async fn filter_course_material_page_with_cache(
    conn: &mut PgConnection,
    user_id: Option<Uuid>,
    page: Page,
    cache: &mut CourseMaterialPageContentFilterCache,
) -> ModelResult<(Page, Option<LockChapterContentState>)> {
    let mut blocks = match page.blocks_cloned() {
        Ok(blocks) => blocks,
        Err(e) => {
            tracing::warn!(
                "Failed to deserialize page content for page {}: {}. Falling back to empty blocks.",
                page.id,
                e
            );
            vec![]
        }
    };

    blocks = replace_duplicate_client_ids(blocks);

    let lock_chapter_content_state =
        get_lock_chapter_content_state_for_page(conn, user_id, &page, cache).await?;
    if let Some(lock_state) = lock_chapter_content_state {
        blocks = filter_lock_chapter_blocks(
            blocks,
            matches!(lock_state, LockChapterContentState::Visible),
        );
    }

    let mut filtered_page = page;
    filtered_page.content = serde_json::to_value(blocks)?;
    Ok((filtered_page, lock_chapter_content_state))
}

pub async fn filter_course_material_page(
    conn: &mut PgConnection,
    user_id: Option<Uuid>,
    page: Page,
) -> ModelResult<Page> {
    let mut cache = CourseMaterialPageContentFilterCache::default();
    Ok(
        filter_course_material_page_with_cache(conn, user_id, page, &mut cache)
            .await?
            .0,
    )
}

pub async fn filter_course_material_pages(
    conn: &mut PgConnection,
    user_id: Option<Uuid>,
    pages: Vec<Page>,
) -> ModelResult<Vec<Page>> {
    let mut cache = CourseMaterialPageContentFilterCache::default();
    let mut filtered_pages = Vec::with_capacity(pages.len());
    for page in pages {
        filtered_pages.push(
            filter_course_material_page_with_cache(conn, user_id, page, &mut cache)
                .await?
                .0,
        );
    }
    Ok(filtered_pages)
}

pub async fn filter_course_material_pages_with_exercises(
    conn: &mut PgConnection,
    user_id: Option<Uuid>,
    pages_with_exercises: Vec<PageWithExercises>,
) -> ModelResult<Vec<PageWithExercises>> {
    let mut cache = CourseMaterialPageContentFilterCache::default();
    let mut filtered_pages = Vec::with_capacity(pages_with_exercises.len());
    for page_with_exercises in pages_with_exercises {
        let (page, _) = filter_course_material_page_with_cache(
            conn,
            user_id,
            page_with_exercises.page,
            &mut cache,
        )
        .await?;
        filtered_pages.push(PageWithExercises {
            page,
            exercises: page_with_exercises.exercises,
        });
    }
    Ok(filtered_pages)
}

/// Represents the subset of page fields that are required to create a new page.
#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]

pub struct NewPage {
    pub exercises: Vec<CmsPageExercise>,
    pub exercise_slides: Vec<CmsPageExerciseSlide>,
    pub exercise_tasks: Vec<CmsPageExerciseTask>,
    pub content: Vec<GutenbergBlock>,
    pub url_path: String,
    pub title: String,
    pub course_id: Option<Uuid>,
    pub exam_id: Option<Uuid>,
    pub chapter_id: Option<Uuid>,
    /// If set, set this page to be the front page of this course part.
    pub front_page_of_chapter_id: Option<Uuid>,
    /// Read from the course's settings if None. If course_id is None as well, defaults to "simple"
    pub content_search_language: Option<String>,
}

/// Represents the subset of page fields that can be updated from the main frontend dialog "Edit page details".
#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]

pub struct PageDetailsUpdate {
    pub title: String,
    pub url_path: String,
}

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

pub struct NormalizedCmsExerciseTask {
    pub id: Uuid,
    pub exercise_type: String,
    pub assignment: serde_json::Value,
    pub private_spec: Option<serde_json::Value>,
}

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

pub struct PageRoutingData {
    pub url_path: String,
    pub title: String,
    pub page_id: Uuid,
    pub chapter_number: i32,
    pub chapter_id: Uuid,
    pub chapter_opens_at: Option<DateTime<Utc>>,
    pub chapter_front_page_id: Option<Uuid>,
}

#[derive(Debug, Serialize, Deserialize, FromRow, PartialEq, Clone)]
pub struct PageMetadata {
    pub page_id: Uuid,
    pub order_number: i32,
    pub chapter_id: Option<Uuid>,
    pub chapter_number: Option<i32>,
    pub course_id: Option<Uuid>,
    pub exam_id: Option<Uuid>,
}

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

pub struct PageChapterAndCourseInformation {
    pub chapter_name: Option<String>,
    pub chapter_number: Option<i32>,
    pub course_name: Option<String>,
    pub course_slug: Option<String>,
    pub chapter_front_page_id: Option<Uuid>,
    pub chapter_front_page_url_path: Option<String>,
    pub organization_slug: Option<String>,
}

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

pub struct PageSearchResult {
    pub id: Uuid,
    pub title_headline: Option<String>,
    pub rank: Option<f32>,
    pub content_headline: Option<String>,
    pub url_path: String,
    pub chapter_name: Option<String>,
}

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

pub struct ContentManagementPage {
    pub page: Page,
    pub exercises: Vec<CmsPageExercise>,
    pub exercise_slides: Vec<CmsPageExerciseSlide>,
    pub exercise_tasks: Vec<CmsPageExerciseTask>,
    pub peer_or_self_review_configs: Vec<CmsPeerOrSelfReviewConfig>,
    pub peer_or_self_review_questions: Vec<CmsPeerOrSelfReviewQuestion>,
    pub organization_id: Uuid,
}

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

pub struct SearchRequest {
    pub query: String,
}
#[derive(Debug, Serialize, Deserialize, FromRow, PartialEq, Clone, ToSchema)]

pub struct PageNavigationInformation {
    pub chapter_front_page: Option<PageRoutingData>,
    pub next_page: Option<PageRoutingData>,
    pub previous_page: Option<PageRoutingData>,
}

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

pub struct ExerciseWithExerciseTasks {
    id: Uuid,
    created_at: DateTime<Utc>,
    updated_at: DateTime<Utc>,
    course_id: Uuid,
    deleted_at: Option<DateTime<Utc>>,
    name: String,
    deadline: Option<DateTime<Utc>>,
    page_id: Uuid,
    exercise_tasks: Vec<ExerciseTask>,
    score_maximum: i32,
}

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

pub struct IsChapterFrontPage {
    pub is_chapter_front_page: bool,
}

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

pub struct HistoryRestoreData {
    pub history_id: Uuid,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct NewCoursePage<'a> {
    pub content: Vec<GutenbergBlock>,
    pub course_id: Uuid,
    pub order_number: i32,
    pub title: &'a str,
    pub hidden: bool,
    pub url_path: &'a str,
}

impl<'a> NewCoursePage<'a> {
    /// Creates `NewCoursePage` with provided values that is public by default.
    pub fn new(course_id: Uuid, order_number: i32, url_path: &'a str, title: &'a str) -> Self {
        Self {
            content: Default::default(),
            course_id,
            order_number,
            title,
            hidden: false,
            url_path,
        }
    }

    /// Creates a new `NewCoursePage` for the same course as this one and increments the page number.
    pub fn followed_by(&self, url_path: &'a str, title: &'a str) -> Self {
        Self::new(self.course_id, self.order_number + 1, url_path, title)
    }

    /// Sets the content of this page.
    pub fn set_content(mut self, content: Vec<GutenbergBlock>) -> Self {
        self.content = content;
        self
    }

    /// Sets the hidden status of this page.
    pub fn set_hidden(mut self, hidden: bool) -> Self {
        self.hidden = hidden;
        self
    }
}

pub async fn insert_course_page(
    conn: &mut PgConnection,
    new_course_page: &NewCoursePage<'_>,
    author: Uuid,
) -> ModelResult<(Uuid, Uuid)> {
    let course = crate::courses::get_course(&mut *conn, new_course_page.course_id).await?;
    let page_language_group_id = crate::page_language_groups::insert(
        &mut *conn,
        crate::PKeyPolicy::Generate,
        course.course_language_group_id,
    )
    .await?;

    let mut tx = conn.begin().await?;
    let page_res = sqlx::query!(
        "
INSERT INTO pages (
    course_id,
    content,
    url_path,
    title,
    order_number,
    hidden,
    page_language_group_id
  )
VALUES ($1, $2, $3, $4, $5, $6, $7)
RETURNING id
",
        new_course_page.course_id,
        serde_json::to_value(new_course_page.content.clone())?,
        new_course_page.url_path,
        new_course_page.title,
        new_course_page.order_number,
        new_course_page.hidden,
        page_language_group_id,
    )
    .fetch_one(&mut *tx)
    .await?;
    let history_id = crate::page_history::insert(
        &mut tx,
        PKeyPolicy::Generate,
        page_res.id,
        new_course_page.title,
        &PageHistoryContent {
            content: serde_json::Value::Array(vec![]),
            exercises: vec![],
            exercise_slides: vec![],
            exercise_tasks: vec![],
            peer_or_self_review_configs: Vec::new(),
            peer_or_self_review_questions: Vec::new(),
        },
        HistoryChangeReason::PageSaved,
        author,
        None,
    )
    .await?;
    tx.commit().await?;
    Ok((page_res.id, history_id))
}

pub async fn insert_exam_page(
    conn: &mut PgConnection,
    exam_id: Uuid,
    page: NewPage,
    author: Uuid,
) -> ModelResult<(Uuid, Uuid)> {
    let mut tx = conn.begin().await?;
    let page_res = sqlx::query!(
        "
INSERT INTO pages (
    exam_id,
    content,
    url_path,
    title,
    order_number
  )
VALUES ($1, $2, $3, $4, $5)
RETURNING id
",
        exam_id,
        serde_json::Value::Array(vec![]),
        page.url_path,
        page.title,
        0
    )
    .fetch_one(&mut *tx)
    .await?;

    let history_id = crate::page_history::insert(
        &mut tx,
        PKeyPolicy::Generate,
        page_res.id,
        page.title.as_str(),
        &PageHistoryContent {
            content: serde_json::Value::Array(vec![]),
            exercises: vec![],
            exercise_slides: vec![],
            exercise_tasks: vec![],
            peer_or_self_review_configs: Vec::new(),
            peer_or_self_review_questions: Vec::new(),
        },
        HistoryChangeReason::PageSaved,
        author,
        None,
    )
    .await?;
    tx.commit().await?;
    Ok((page_res.id, history_id))
}

pub async fn set_chapter(
    conn: &mut PgConnection,
    page_id: Uuid,
    chapter_id: Uuid,
) -> ModelResult<()> {
    sqlx::query!(
        "UPDATE pages SET chapter_id = $1 WHERE id = $2",
        chapter_id,
        page_id
    )
    .execute(conn)
    .await?;
    Ok(())
}

pub async fn get_course_and_exam_id(
    conn: &mut PgConnection,
    id: Uuid,
) -> ModelResult<CourseOrExamId> {
    let res = sqlx::query!(
        "
SELECT course_id, exam_id
FROM pages
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)
}

#[derive(Copy, Clone, Debug, PartialEq, Eq)]
pub enum PageVisibility {
    Any,
    Public,
    Hidden,
}

impl PageVisibility {
    /// Hacky way to implement a nullable boolean filter. Based on the idea that
    /// `null IS DISTINCT FROM anything` in PostgreSQL.
    ///
    /// More information at: <https://www.postgresql.org/docs/current/functions-comparison.html>
    ///
    /// # Examples
    ///
    /// ```ignore
    /// # use headless_lms_models::{ModelResult, pages::PageVisibility};
    /// # use sqlx::PgConnection;
    /// # async fn random_function_1(conn: &mut PgConnection) -> ModelResult<()> {
    /// // Evaluates to "hidden <> NULL"
    /// let visibility = PageVisibility::Any;
    /// sqlx::query!(
    ///     "SELECT id FROM pages WHERE hidden IS DISTINCT FROM $1",
    ///     visibility.get_inverse_visibility_filter(),
    /// )
    /// .fetch_all(conn)
    /// .await?;
    /// # Ok(())
    /// # }
    ///
    /// # async fn random_function_2(conn: &mut PgConnection) -> ModelResult<()> {
    /// // Evaluates to "hidden <> true"
    /// let visibility = PageVisibility::Public;
    /// sqlx::query!(
    ///     "SELECT id FROM pages WHERE hidden IS DISTINCT FROM $1",
    ///     visibility.get_inverse_visibility_filter(),
    /// )
    /// .fetch_all(conn)
    /// .await?;
    /// # Ok(())
    /// # }
    /// ```
    fn get_inverse_visibility_filter(&self) -> Option<bool> {
        match self {
            PageVisibility::Any => None,
            PageVisibility::Public => Some(true),
            PageVisibility::Hidden => Some(false),
        }
    }
}

/// Gets all pages that belong to the given course that match the visibility filter.
pub async fn get_all_by_course_id_and_visibility(
    conn: &mut PgConnection,
    course_id: Uuid,
    page_visibility: PageVisibility,
) -> ModelResult<Vec<Page>> {
    let inverse_visibility_filter = page_visibility.get_inverse_visibility_filter();
    let res = sqlx::query_as!(
        Page,
        "
SELECT id,
  created_at,
  updated_at,
  course_id,
  exam_id,
  chapter_id,
  url_path,
  title,
  deleted_at,
  content,
  order_number,
  copied_from,
  hidden,
  page_language_group_id
FROM pages
WHERE course_id = $1
  AND hidden IS DISTINCT FROM $2
  AND deleted_at IS NULL
    ",
        course_id,
        inverse_visibility_filter,
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

/// Gets all pages that belong to the given course but not in any chapter.
pub async fn get_course_top_level_pages_by_course_id_and_visibility(
    conn: &mut PgConnection,
    course_id: Uuid,
    page_visibility: PageVisibility,
) -> ModelResult<Vec<Page>> {
    let inverse_visibility_filter = page_visibility.get_inverse_visibility_filter();
    let pages = sqlx::query_as!(
        Page,
        "
SELECT id,
  created_at,
  updated_at,
  course_id,
  exam_id,
  chapter_id,
  url_path,
  title,
  deleted_at,
  content,
  order_number,
  copied_from,
  hidden,
  page_language_group_id
FROM pages p
WHERE course_id = $1
  AND hidden IS DISTINCT FROM $2
  AND p.chapter_id IS NULL
  AND p.deleted_at IS NULL
        ",
        course_id,
        inverse_visibility_filter,
    )
    .fetch_all(conn)
    .await?;
    Ok(pages)
}

/// Gets all pages that belong to the given chapter that match the visibility filter.
pub async fn get_course_pages_by_chapter_id_and_visibility(
    conn: &mut PgConnection,
    chapter_id: Uuid,
    page_visibility: PageVisibility,
) -> ModelResult<Vec<Page>> {
    let inverse_visibility_filter = page_visibility.get_inverse_visibility_filter();
    let res = sqlx::query_as!(
        Page,
        "
SELECT id,
  created_at,
  updated_at,
  course_id,
  exam_id,
  chapter_id,
  url_path,
  title,
  deleted_at,
  content,
  order_number,
  copied_from,
  hidden,
  page_language_group_id
FROM pages
WHERE chapter_id = $1
  AND hidden IS DISTINCT FROM $2
  AND deleted_at IS NULL
    ",
        chapter_id,
        inverse_visibility_filter,
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_page(conn: &mut PgConnection, page_id: Uuid) -> ModelResult<Page> {
    let pages = sqlx::query_as!(
        Page,
        "
SELECT id,
  created_at,
  updated_at,
  course_id,
  exam_id,
  chapter_id,
  url_path,
  title,
  deleted_at,
  content,
  order_number,
  copied_from,
  hidden,
  page_language_group_id
FROM pages
WHERE id = $1;
",
        page_id
    )
    .fetch_one(conn)
    .await?;
    Ok(pages)
}

pub async fn get_front_page_by_chapter_id(
    conn: &mut PgConnection,
    chapter_id: Uuid,
) -> ModelResult<Option<Page>> {
    let page = sqlx::query_as!(
        Page,
        r#"
SELECT p.id,
  p.created_at,
  p.updated_at,
  p.course_id,
  p.exam_id,
  p.chapter_id,
  p.url_path,
  p.title,
  p.deleted_at,
  p.content,
  p.order_number,
  p.copied_from,
  p.hidden,
  p.page_language_group_id
FROM chapters c
  JOIN pages p ON p.id = c.front_page_id
WHERE c.id = $1
  AND c.deleted_at IS NULL
  AND p.deleted_at IS NULL
        "#,
        chapter_id
    )
    .fetch_optional(conn)
    .await?;
    Ok(page)
}

pub async fn get_page_info(conn: &mut PgConnection, page_id: Uuid) -> ModelResult<PageInfo> {
    let res = sqlx::query_as!(
        PageInfo,
        r#"
    SELECT
        p.id as page_id,
        p.title as page_title,
        c.id as "course_id?",
        c.name as "course_name?",
        c.slug as "course_slug?",
        o.slug as "organization_slug?"
    FROM pages p
    LEFT JOIN courses c
        on c.id = p.course_id
    LEFT JOIN organizations o
        on o.id = c.organization_id
    WHERE p.id = $1;
        "#,
        page_id
    )
    .fetch_one(conn)
    .await?;

    Ok(res)
}

async fn get_page_by_stored_path(
    conn: &mut PgConnection,
    course_id: Uuid,
    url_path: &str,
) -> ModelResult<Option<Page>> {
    let page = sqlx::query_as!(
        Page,
        "
SELECT pages.id,
  pages.created_at,
  pages.updated_at,
  pages.course_id,
  pages.exam_id,
  pages.chapter_id,
  pages.url_path,
  pages.title,
  pages.deleted_at,
  pages.content,
  pages.order_number,
  pages.copied_from,
  pages.hidden,
  pages.page_language_group_id
FROM pages
WHERE pages.course_id = $1
  AND url_path = $2
  AND pages.deleted_at IS NULL;
        ",
        course_id,
        url_path
    )
    .fetch_optional(conn)
    .await?;
    Ok(page)
}

pub async fn get_page_with_user_data_by_path(
    conn: &mut PgConnection,
    user_id: Option<Uuid>,
    course_data: &CourseContextData,
    url_path: &str,
    file_store: &dyn FileStore,
    app_conf: &ApplicationConfiguration,
) -> ModelResult<CoursePageWithUserData> {
    let normalized_url_path = normalize_url_path_for_storage(url_path);
    let page_option = get_page_by_stored_path(conn, course_data.id, &normalized_url_path).await?;

    if let Some(page) = page_option {
        return get_course_page_with_user_data_from_selected_page(
            conn,
            user_id,
            page,
            false,
            course_data.is_test_mode,
            file_store,
            app_conf,
        )
        .await;
    } else {
        let potential_redirect =
            try_to_find_redirected_page_by_stored_path(conn, course_data.id, &normalized_url_path)
                .await?;
        if let Some(redirected_page) = potential_redirect {
            return get_course_page_with_user_data_from_selected_page(
                conn,
                user_id,
                redirected_page,
                true,
                course_data.is_test_mode,
                file_store,
                app_conf,
            )
            .await;
        }
    }

    Err(model_err!(NotFound, "Page not found".to_string()))
}

pub async fn try_to_find_redirected_page(
    conn: &mut PgConnection,
    course_id: Uuid,
    url_path: &str,
) -> ModelResult<Option<Page>> {
    let normalized_url_path = normalize_url_path_for_storage(url_path);
    try_to_find_redirected_page_by_stored_path(conn, course_id, &normalized_url_path).await
}

async fn try_to_find_redirected_page_by_stored_path(
    conn: &mut PgConnection,
    course_id: Uuid,
    url_path: &str,
) -> ModelResult<Option<Page>> {
    let page = sqlx::query_as!(
        Page,
        "
SELECT pages.id,
  pages.created_at,
  pages.updated_at,
  pages.course_id,
  pages.exam_id,
  pages.chapter_id,
  pages.url_path,
  pages.title,
  pages.deleted_at,
  pages.content,
  pages.order_number,
  pages.copied_from,
  pages.hidden,
  pages.page_language_group_id
FROM url_redirections
  JOIN pages on pages.id = url_redirections.destination_page_id
WHERE url_redirections.course_id = $1
  AND old_url_path = $2
  AND url_redirections.deleted_at IS NULL
  AND pages.deleted_at IS NULL;
    ",
        course_id,
        url_path
    )
    .fetch_optional(conn)
    .await?;
    Ok(page)
}

pub async fn get_course_page_with_user_data_from_selected_page(
    conn: &mut PgConnection,
    user_id: Option<Uuid>,
    page: Page,
    was_redirected: bool,
    is_test_mode: bool,
    file_store: &dyn FileStore,
    app_conf: &ApplicationConfiguration,
) -> ModelResult<CoursePageWithUserData> {
    let mut content_filter_cache = CourseMaterialPageContentFilterCache::default();
    let (filtered_page, lock_chapter_content_state) =
        filter_course_material_page_with_cache(conn, user_id, page, &mut content_filter_cache)
            .await?;

    if let Some(course_id) = filtered_page.course_id
        && let Some(user_id) = user_id
    {
        let instance =
            course_instances::current_course_instance_of_user(conn, user_id, course_id).await?;
        let settings =
            user_course_settings::get_user_course_settings_by_course_id(conn, user_id, course_id)
                .await?;
        let course = courses::get_course(conn, course_id).await?;
        let organization = Organization::from_database_organization(
            crate::organizations::get_organization(conn, course.organization_id).await?,
            file_store,
            app_conf,
        );
        return Ok(CoursePageWithUserData {
            page: filtered_page,
            instance,
            settings,
            course: Some(course.into()),
            was_redirected,
            is_test_mode,
            organization: Some(organization),
            lock_chapter_content_state,
        });
    }
    Ok(CoursePageWithUserData {
        page: filtered_page,
        instance: None,
        settings: None,
        course: None,
        was_redirected,
        is_test_mode,
        organization: None,
        lock_chapter_content_state,
    })
}

pub async fn get_page_with_exercises(
    conn: &mut PgConnection,
    page_id: Uuid,
) -> ModelResult<ContentManagementPage> {
    let page = get_page(&mut *conn, page_id).await?;

    let peer_or_self_review_configs =
        crate::peer_or_self_review_configs::get_peer_reviews_by_page_id(conn, page.id)
            .await?
            .into_iter()
            .flat_map(|pr| pr.exercise_id.map(|id| (id, pr)))
            .collect::<HashMap<_, _>>();

    let peer_or_self_review_questions =
        crate::peer_or_self_review_questions::get_by_page_id(conn, page.id)
            .await?
            .into_iter()
            .into_group_map_by(|prq| prq.peer_or_self_review_config_id)
            .into_iter()
            .collect::<HashMap<_, _>>();

    let exercises = crate::exercises::get_exercises_by_page_id(&mut *conn, page.id)
        .await?
        .into_iter()
        .map(|exercise| {
            let (a, b) = if exercise.needs_peer_review
                && exercise.use_course_default_peer_or_self_review_config
            {
                (None, None)
            } else {
                let peer_or_self_review_config =
                    peer_or_self_review_configs.get(&exercise.id).cloned();
                let peer_or_self_review_questions = peer_or_self_review_config
                    .as_ref()
                    .and_then(|prc| peer_or_self_review_questions.get(&prc.id).cloned());
                (peer_or_self_review_config, peer_or_self_review_questions)
            };

            Ok(CmsPageExercise::from_exercise_and_peer_review_data(
                exercise, a, b,
            ))
        })
        .collect::<ModelResult<Vec<_>>>()?;

    let exercise_slides: Vec<CmsPageExerciseSlide> =
        crate::exercise_slides::get_exercise_slides_by_exercise_ids(
            &mut *conn,
            &exercises.iter().map(|x| x.id).collect::<Vec<_>>(),
        )
        .await?
        .into_iter()
        .map(|x| x.into())
        .collect();

    let exercise_tasks: Vec<CmsPageExerciseTask> =
        crate::exercise_tasks::get_exercise_tasks_by_exercise_slide_ids(
            &mut *conn,
            &exercise_slides.iter().map(|x| x.id).collect::<Vec<Uuid>>(),
        )
        .await?
        .into_iter()
        .map(|x| x.into())
        .collect();

    let organization_id = get_organization_id(&mut *conn, page_id).await?;
    Ok(ContentManagementPage {
        page,
        exercises,
        exercise_slides,
        exercise_tasks,
        peer_or_self_review_configs: peer_or_self_review_configs.into_values().collect(),
        peer_or_self_review_questions: peer_or_self_review_questions
            .into_values()
            .flatten()
            .collect(),
        organization_id,
    })
}

/// Gets the page that belongs to the given exam. For exams, the page visibility is ignored.
pub async fn get_by_exam_id(conn: &mut PgConnection, exam_id: Uuid) -> ModelResult<Page> {
    let res = sqlx::query_as!(
        Page,
        "
SELECT pages.id,
  pages.created_at,
  pages.updated_at,
  pages.course_id,
  pages.exam_id,
  pages.chapter_id,
  pages.url_path,
  pages.title,
  pages.deleted_at,
  pages.content,
  pages.order_number,
  pages.copied_from,
  pages.hidden,
  pages.page_language_group_id
FROM pages
WHERE exam_id = $1
AND pages.deleted_at IS NULL
",
        exam_id
    )
    .fetch_one(conn)
    .await?;
    Ok(res)
}

pub async fn get_pages_by_course_id(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> ModelResult<Vec<Page>> {
    let res = sqlx::query_as!(
        Page,
        "
SELECT pages.id,
  pages.created_at,
  pages.updated_at,
  pages.course_id,
  pages.exam_id,
  pages.chapter_id,
  pages.url_path,
  pages.title,
  pages.deleted_at,
  pages.content,
  pages.order_number,
  pages.copied_from,
  pages.hidden,
  pages.page_language_group_id
FROM pages
WHERE course_id = $1
AND pages.deleted_at IS NULL
",
        course_id
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

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

pub struct CmsPageExercise {
    pub id: Uuid,
    pub name: String,
    pub order_number: i32,
    pub score_maximum: i32,
    pub max_tries_per_slide: Option<i32>,
    pub limit_number_of_tries: bool,
    pub deadline: Option<DateTime<Utc>>,
    pub needs_peer_review: bool,
    pub needs_self_review: bool,
    pub peer_or_self_review_config: Option<CmsPeerOrSelfReviewConfig>,
    pub peer_or_self_review_questions: Option<Vec<CmsPeerOrSelfReviewQuestion>>,
    pub use_course_default_peer_or_self_review_config: bool,
    pub teacher_reviews_answer_after_locking: bool,
}

impl CmsPageExercise {
    fn from_exercise_and_peer_review_data(
        exercise: Exercise,
        peer_or_self_review_config: Option<CmsPeerOrSelfReviewConfig>,
        peer_or_self_review_questions: Option<Vec<CmsPeerOrSelfReviewQuestion>>,
    ) -> Self {
        Self {
            id: exercise.id,
            name: exercise.name,
            order_number: exercise.order_number,
            score_maximum: exercise.score_maximum,
            max_tries_per_slide: exercise.max_tries_per_slide,
            limit_number_of_tries: exercise.limit_number_of_tries,
            deadline: exercise.deadline,
            needs_peer_review: exercise.needs_peer_review,
            needs_self_review: exercise.needs_self_review,
            use_course_default_peer_or_self_review_config: exercise
                .use_course_default_peer_or_self_review_config,
            teacher_reviews_answer_after_locking: exercise.teacher_reviews_answer_after_locking,
            peer_or_self_review_config,
            peer_or_self_review_questions,
        }
    }
}

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

pub struct CmsPageExerciseSlide {
    pub id: Uuid,
    pub exercise_id: Uuid,
    pub order_number: i32,
}

impl From<ExerciseSlide> for CmsPageExerciseSlide {
    fn from(slide: ExerciseSlide) -> Self {
        Self {
            id: slide.id,
            exercise_id: slide.exercise_id,
            order_number: slide.order_number,
        }
    }
}

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

pub struct CmsPageExerciseTask {
    pub id: Uuid,
    pub exercise_slide_id: Uuid,
    pub assignment: serde_json::Value,
    pub exercise_type: String,
    pub private_spec: Option<serde_json::Value>,
    pub order_number: i32,
}

impl From<ExerciseTask> for CmsPageExerciseTask {
    fn from(task: ExerciseTask) -> Self {
        CmsPageExerciseTask {
            id: task.id,
            exercise_slide_id: task.exercise_slide_id,
            assignment: task.assignment,
            exercise_type: task.exercise_type,
            private_spec: task.private_spec,
            order_number: task.order_number,
        }
    }
}

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

pub struct CmsPageUpdate {
    pub content: Vec<GutenbergBlock>,
    pub exercises: Vec<CmsPageExercise>,
    pub exercise_slides: Vec<CmsPageExerciseSlide>,
    pub exercise_tasks: Vec<CmsPageExerciseTask>,
    pub url_path: String,
    pub title: String,
    pub chapter_id: Option<Uuid>,
}

impl CmsPageUpdate {
    /// Checks that each exercise has at least one slide and each slide has at least one task.
    pub fn validate_exercise_data(&self) -> ModelResult<()> {
        let mut exercise_ids: HashMap<Uuid, bool> =
            self.exercises.iter().map(|x| (x.id, false)).collect();
        let mut slide_ids = self
            .exercise_slides
            .iter()
            .map(|x| {
                if let hash_map::Entry::Occupied(mut e) = exercise_ids.entry(x.exercise_id) {
                    e.insert(true);
                    Ok((x.id, false))
                } else {
                    Err(model_err!(
                        PreconditionFailed,
                        "Exercide ids in slides don't match.".to_string()
                    ))
                }
            })
            .collect::<ModelResult<HashMap<Uuid, bool>>>()?;

        if let Some((exercise_id, _)) = exercise_ids.into_iter().find(|(_, x)| !x) {
            return Err(model_err!(
                PreconditionFailedWithCMSAnchorBlockId {
                    id: exercise_id,
                    description: "Exercise must have at least one slide.",
                },
                "Exercise must have at least one slide.".to_string()
            ));
        }

        for task in self.exercise_tasks.iter() {
            if let hash_map::Entry::Occupied(mut e) = slide_ids.entry(task.exercise_slide_id) {
                e.insert(true);
            } else {
                return Err(model_err!(
                    PreconditionFailed,
                    "Exercise slide ids in tasks don't match.".to_string()
                ));
            }
        }
        if let Some((slide_id, _)) = slide_ids.into_iter().find(|(_, x)| !x) {
            return Err(model_err!(
                PreconditionFailedWithCMSAnchorBlockId {
                    id: slide_id,
                    description: "Exercise slide must have at least one task.",
                },
                "Exercise slide must have at least one task.".to_string()
            ));
        }
        Ok(())
    }
}

#[derive(Debug, Clone)]
pub struct PageUpdateArgs {
    pub page_id: Uuid,
    pub author: Uuid,
    pub cms_page_update: CmsPageUpdate,
    pub retain_ids: bool,
    pub history_change_reason: HistoryChangeReason,
    pub is_exam_page: bool,
}

pub async fn update_page(
    conn: &mut PgConnection,
    page_update: PageUpdateArgs,
    spec_fetcher: impl SpecFetcher,
    fetch_service_info: impl Fn(Url) -> BoxFuture<'static, ModelResult<ExerciseServiceInfoApi>>,
) -> ModelResult<ContentManagementPage> {
    let mut cms_page_update = page_update.cms_page_update;
    cms_page_update.validate_exercise_data()?;

    for exercise in cms_page_update.exercises.iter_mut() {
        if let Some(peer_or_self_review_questions) = exercise.peer_or_self_review_questions.as_mut()
        {
            normalize_cms_peer_or_self_review_questions(peer_or_self_review_questions);
        }
    }

    let content = replace_duplicate_client_ids(cms_page_update.content.clone());
    let normalized_url_path = normalize_url_path_for_storage(&cms_page_update.url_path);

    if !page_update.is_exam_page
        && cms_page_update.chapter_id.is_none()
        && contains_blocks_not_allowed_in_top_level_pages(&content)
    {
        return Err(model_err!(Generic, "Top level non-exam pages cannot contain exercises, exercise tasks or list of exercises in the chapter".to_string()));
    }

    let mut tx = conn.begin().await?;

    // Updating page
    let page = sqlx::query_as!(
        Page,
        r"
UPDATE pages
SET content = $2,
  url_path = $3,
  title = $4,
  chapter_id = $5
WHERE id = $1
RETURNING id,
  created_at,
  updated_at,
  course_id,
  exam_id,
  chapter_id,
  url_path,
  title,
  deleted_at,
  content,
  order_number,
  copied_from,
  pages.hidden,
  pages.page_language_group_id
        ",
        page_update.page_id,
        serde_json::to_value(&content)?,
        normalized_url_path,
        cms_page_update.title.trim(),
        cms_page_update.chapter_id
    )
    .fetch_one(&mut *tx)
    .await?;

    // Exercises
    let existing_exercise_ids =
        crate::exercises::delete_exercises_by_page_id(&mut tx, page.id).await?;
    let remapped_exercises = upsert_exercises(
        &mut tx,
        &page,
        &existing_exercise_ids,
        &cms_page_update.exercises,
        page_update.retain_ids,
    )
    .await?;

    // Exercise slides
    let existing_exercise_slide_ids =
        crate::exercise_slides::delete_exercise_slides_by_exercise_ids(
            &mut tx,
            &existing_exercise_ids,
        )
        .await?;
    let remapped_exercise_slides = upsert_exercise_slides(
        &mut tx,
        &remapped_exercises,
        &existing_exercise_slide_ids,
        &cms_page_update.exercise_slides,
        page_update.retain_ids,
    )
    .await?;

    // Peer reviews
    let existing_peer_or_self_review_config_ids =
        crate::peer_or_self_review_configs::delete_peer_reviews_by_exrcise_ids(
            &mut tx,
            &existing_exercise_ids,
        )
        .await?;

    let (peer_or_self_review_configs, peer_or_self_review_questions) = cms_page_update
        .exercises
        .into_iter()
        .filter(|e| !e.use_course_default_peer_or_self_review_config)
        .flat_map(|e| {
            e.peer_or_self_review_config
                .zip(e.peer_or_self_review_questions)
        })
        .fold((vec![], vec![]), |(mut a, mut b), (pr, prq)| {
            a.push(pr);
            b.extend(prq);
            (a, b)
        });

    let remapped_peer_or_self_review_configs = upsert_peer_or_self_review_configs(
        &mut tx,
        &existing_peer_or_self_review_config_ids,
        &peer_or_self_review_configs,
        &remapped_exercises,
        page_update.retain_ids,
    )
    .await?;

    // Peer review questions
    let existing_peer_or_self_review_questions =
        crate::peer_or_self_review_questions::delete_peer_or_self_review_questions_by_peer_or_self_review_config_ids(
            &mut tx,
            &existing_peer_or_self_review_config_ids,
        )
        .await?;

    let remapped_peer_or_self_review_questions = upsert_peer_or_self_review_questions(
        &mut tx,
        &existing_peer_or_self_review_questions,
        &peer_or_self_review_questions,
        &remapped_peer_or_self_review_configs,
        page_update.retain_ids,
    )
    .await?;

    // Set as deleted and get existing specs
    let existing_exercise_task_specs = sqlx::query_as!(
        ExerciseTaskIdAndSpec,
        "
UPDATE exercise_tasks
SET deleted_at = now()
WHERE exercise_slide_id = ANY($1)
AND deleted_at IS NULL
RETURNING id,
  private_spec,
  public_spec,
  model_solution_spec;
        ",
        &existing_exercise_slide_ids,
    )
    .fetch_all(&mut *tx)
    .await?;
    let final_tasks = upsert_exercise_tasks(
        &mut tx,
        &remapped_exercise_slides,
        &existing_exercise_task_specs,
        &cms_page_update.exercise_tasks,
        page_update.retain_ids,
        &spec_fetcher,
        fetch_service_info,
    )
    .await?;

    // Now, we might have changed some of the exercise ids and need to do the same changes in the page content as well
    let new_content = headless_lms_utils::document_schema_processor::remap_ids_in_content(
        &page.content,
        remapped_exercises
            .iter()
            .map(|(id, e)| (*id, e.id))
            .collect::<HashMap<Uuid, Uuid>>(),
    )?;

    let page = sqlx::query_as!(
        Page,
        "
UPDATE pages
SET content = $1
WHERE id = $2
RETURNING id,
  created_at,
  updated_at,
  course_id,
  exam_id,
  chapter_id,
  url_path,
  title,
  deleted_at,
  content,
  order_number,
  copied_from,
  hidden,
  page_language_group_id
        ",
        new_content,
        page.id
    )
    .fetch_one(&mut *tx)
    .await?;

    let x = remapped_exercises.into_values().collect::<Vec<_>>();
    let final_exercises = x
        .iter()
        .map(|e| {
            let peer_or_self_review_config = remapped_peer_or_self_review_configs
                .values()
                .find(|prc| prc.exercise_id == Some(e.id));
            if let Some(prc) = peer_or_self_review_config {
                let peer_or_self_review_questions = remapped_peer_or_self_review_questions
                    .values()
                    .filter(|prq| prq.peer_or_self_review_config_id == prc.id)
                    .cloned()
                    .collect::<Vec<_>>();
                return CmsPageExercise::from_exercise_and_peer_review_data(
                    e.clone(),
                    Some(prc.clone()),
                    Some(peer_or_self_review_questions),
                );
            }
            CmsPageExercise::from_exercise_and_peer_review_data(e.clone(), None, None)
        })
        .collect();
    let final_slides: Vec<CmsPageExerciseSlide> = remapped_exercise_slides.into_values().collect();
    let final_peer_reviews: Vec<CmsPeerOrSelfReviewConfig> =
        remapped_peer_or_self_review_configs.into_values().collect();
    let final_peer_or_self_review_questions: Vec<CmsPeerOrSelfReviewQuestion> =
        remapped_peer_or_self_review_questions
            .into_values()
            .collect();
    let history_content = PageHistoryContent {
        content: page.content.clone(),
        exercises: final_exercises,
        exercise_slides: final_slides,
        exercise_tasks: final_tasks,
        peer_or_self_review_configs: final_peer_reviews,
        peer_or_self_review_questions: final_peer_or_self_review_questions,
    };
    crate::page_history::insert(
        &mut tx,
        PKeyPolicy::Generate,
        page_update.page_id,
        &cms_page_update.title,
        &history_content,
        page_update.history_change_reason,
        page_update.author,
        None,
    )
    .await?;
    let organization_id = get_organization_id(&mut tx, page.id).await?;

    tx.commit().await?;

    Ok(ContentManagementPage {
        page,
        exercises: history_content.exercises,
        exercise_slides: history_content.exercise_slides,
        exercise_tasks: history_content.exercise_tasks,
        peer_or_self_review_configs: history_content.peer_or_self_review_configs,
        peer_or_self_review_questions: history_content.peer_or_self_review_questions,
        organization_id,
    })
}

pub async fn update_by_id_in_parent_context(
    conn: &mut PgConnection,
    page_update: PageUpdateArgs,
    expected_course_id: Option<Uuid>,
    expected_exam_id: Option<Uuid>,
    spec_fetcher: impl SpecFetcher,
    fetch_service_info: impl Fn(Url) -> BoxFuture<'static, ModelResult<ExerciseServiceInfoApi>>,
) -> ModelResult<ContentManagementPage> {
    let mut tx = conn.begin().await?;
    sqlx::query!(
        r#"
SELECT p.id
FROM pages p
LEFT JOIN chapters c
  ON c.id = $4
WHERE p.id = $1
  AND p.deleted_at IS NULL
  AND (p.course_id = $2 OR p.exam_id = $3)
  AND (
    $4::uuid IS NULL
    OR (
      p.course_id IS NOT NULL
      AND c.id = $4
      AND c.course_id = p.course_id
      AND c.deleted_at IS NULL
    )
  )
 FOR UPDATE OF p
        "#,
        page_update.page_id,
        expected_course_id,
        expected_exam_id,
        page_update.cms_page_update.chapter_id
    )
    .fetch_one(&mut *tx)
    .await?;

    let updated = update_page(&mut tx, page_update, spec_fetcher, fetch_service_info).await?;
    tx.commit().await?;
    Ok(updated)
}

/// Remaps ids from updates to exercises that may have their ids regenerated.
async fn upsert_exercises(
    conn: &mut PgConnection,
    page: &Page,
    existing_exercise_ids: &[Uuid],
    exercise_updates: &[CmsPageExercise],
    retain_exercise_ids: bool,
) -> ModelResult<HashMap<Uuid, Exercise>> {
    let mut remapped_exercises = HashMap::new();
    for exercise_update in exercise_updates.iter() {
        let exercise_exists = existing_exercise_ids.contains(&exercise_update.id);
        let safe_for_db_exercise_id = if retain_exercise_ids || exercise_exists {
            exercise_update.id
        } else {
            Uuid::new_v4()
        };

        // check if exercise exits
        let db_exercise = crate::exercises::get_by_id(&mut *conn, safe_for_db_exercise_id)
            .await
            .optional()?;

        let mut exercise_language_group_id = None;

        if let Some(db_exercise) = db_exercise {
            exercise_language_group_id = db_exercise.exercise_language_group_id;
        }
        if let Some(course_id) = page.course_id {
            let course = crate::courses::get_course(&mut *conn, course_id).await?;

            exercise_language_group_id = Some(
                crate::exercise_language_groups::insert(
                    &mut *conn,
                    PKeyPolicy::Generate,
                    course.course_language_group_id,
                )
                .await?,
            );
        }

        let exercise = sqlx::query_as!(
            Exercise,
            "
INSERT INTO exercises(
    id,
    course_id,
    name,
    order_number,
    page_id,
    chapter_id,
    exam_id,
    score_maximum,
    max_tries_per_slide,
    limit_number_of_tries,
    deadline,
    needs_peer_review,
    needs_self_review,
    use_course_default_peer_or_self_review_config,
    exercise_language_group_id,
    teacher_reviews_answer_after_locking
  )
VALUES (
    $1,
    $2,
    $3,
    $4,
    $5,
    $6,
    $7,
    $8,
    $9,
    $10,
    $11,
    $12,
    $13,
    $14,
    $15,
    $16
  ) ON CONFLICT (id) DO
UPDATE
SET course_id = $2,
  name = $3,
  order_number = $4,
  page_id = $5,
  chapter_id = $6,
  exam_id = $7,
  score_maximum = $8,
  max_tries_per_slide = $9,
  limit_number_of_tries = $10,
  deadline = $11,
  needs_peer_review = $12,
  needs_self_review = $13,
  use_course_default_peer_or_self_review_config = $14,
  exercise_language_group_id = $15,
  teacher_reviews_answer_after_locking = $16,
  deleted_at = NULL
RETURNING *;
            ",
            safe_for_db_exercise_id,
            page.course_id,
            exercise_update.name,
            exercise_update.order_number,
            page.id,
            page.chapter_id,
            page.exam_id,
            exercise_update.score_maximum,
            exercise_update.max_tries_per_slide,
            exercise_update.limit_number_of_tries,
            exercise_update.deadline,
            exercise_update.needs_peer_review,
            exercise_update.needs_self_review,
            exercise_update.use_course_default_peer_or_self_review_config,
            exercise_language_group_id,
            exercise_update.teacher_reviews_answer_after_locking,
        )
        .fetch_one(&mut *conn)
        .await?;

        remapped_exercises.insert(exercise_update.id, exercise);
    }
    Ok(remapped_exercises)
}

/// Remaps ids from updates to exercise slides that may have their ids changed.
async fn upsert_exercise_slides(
    conn: &mut PgConnection,
    remapped_exercises: &HashMap<Uuid, Exercise>,
    existing_slide_ids: &[Uuid],
    slide_updates: &[CmsPageExerciseSlide],
    retain_exercise_ids: bool,
) -> ModelResult<HashMap<Uuid, CmsPageExerciseSlide>> {
    let mut remapped_exercise_slides = HashMap::new();
    for slide_update in slide_updates.iter() {
        let slide_exists = existing_slide_ids.contains(&slide_update.id);
        let safe_for_db_slide_id = if retain_exercise_ids || slide_exists {
            slide_update.id
        } else {
            Uuid::new_v4()
        };
        let safe_for_db_exercise_id = remapped_exercises
            .get(&slide_update.exercise_id)
            .ok_or_else(|| {
                model_err!(
                    InvalidRequest,
                    "Illegal exercise id for exercise slide.".to_string()
                )
            })?
            .id;

        let exercise_slide = sqlx::query_as!(
            CmsPageExerciseSlide,
            "
INSERT INTO exercise_slides (id, exercise_id, order_number)
VALUES ($1, $2, $3) ON CONFLICT (id) DO
UPDATE
SET exercise_id = $2,
  order_number = $3,
  deleted_at = NULL
RETURNING id,
  exercise_id,
  order_number;
            ",
            safe_for_db_slide_id,
            safe_for_db_exercise_id,
            slide_update.order_number,
        )
        .fetch_one(&mut *conn)
        .await?;

        remapped_exercise_slides.insert(slide_update.id, exercise_slide);
    }
    Ok(remapped_exercise_slides)
}

/// Remaps ids from updates to exercise tasks that may have their ids changed.
async fn upsert_exercise_tasks(
    conn: &mut PgConnection,
    remapped_slides: &HashMap<Uuid, CmsPageExerciseSlide>,
    existing_task_specs: &[ExerciseTaskIdAndSpec],
    task_updates: &[CmsPageExerciseTask],
    retain_exercise_ids: bool,
    spec_fetcher: impl SpecFetcher,
    fetch_service_info: impl Fn(Url) -> BoxFuture<'static, ModelResult<ExerciseServiceInfoApi>>,
) -> ModelResult<Vec<CmsPageExerciseTask>> {
    // For generating public specs for exercises.
    let exercise_types: Vec<String> = task_updates
        .iter()
        .map(|task| task.exercise_type.clone())
        .unique()
        .collect();
    let exercise_service_hashmap = exercise_service_info::get_selected_exercise_services_by_type(
        &mut *conn,
        &exercise_types,
        fetch_service_info,
    )
    .await?;
    let public_spec_urls_by_exercise_type = exercise_service_hashmap
        .iter()
        .map(|(key, (service, info))| Ok((key, get_internal_public_spec_url(service, info)?)))
        .collect::<ModelResult<HashMap<&String, Url>>>()?;
    let model_solution_urls_by_exercise_type = exercise_service_hashmap
        .iter()
        .map(|(key, (service, info))| Ok((key, get_model_solution_url(service, info)?)))
        .collect::<ModelResult<HashMap<&String, Url>>>()?;

    let mut remapped_exercise_tasks = Vec::new();
    for task_update in task_updates.iter() {
        let existing_exercise_task = existing_task_specs.iter().find(|o| o.id == task_update.id);
        let safe_for_db_exercise_task_id = match existing_exercise_task {
            Some(_) => task_update.id,
            _ if retain_exercise_ids => task_update.id,
            None => Uuid::new_v4(),
        };

        let task_exists = existing_task_specs
            .iter()
            .any(|task| task.id == task_update.id);
        let safe_for_db_task_id = if retain_exercise_ids || task_exists {
            task_update.id
        } else {
            Uuid::new_v4()
        };
        let normalized_task = NormalizedCmsExerciseTask {
            id: safe_for_db_task_id,
            assignment: task_update.assignment.clone(),
            exercise_type: task_update.exercise_type.clone(),
            private_spec: task_update.private_spec.clone(),
        };
        let model_solution_spec = fetch_derived_spec(
            existing_exercise_task,
            &normalized_task,
            &model_solution_urls_by_exercise_type,
            &spec_fetcher,
            existing_exercise_task.and_then(|value| value.model_solution_spec.clone()),
            task_update.id,
        )
        .await?;
        let public_spec: Option<serde_json::Value> = fetch_derived_spec(
            existing_exercise_task,
            &normalized_task,
            &public_spec_urls_by_exercise_type,
            &spec_fetcher,
            existing_exercise_task.and_then(|value| value.public_spec.clone()),
            task_update.id,
        )
        .await?;
        let safe_for_db_exercise_slide_id = remapped_slides
            .get(&task_update.exercise_slide_id)
            .ok_or_else(|| {
                model_err!(
                    InvalidRequest,
                    "Illegal exercise slide id for exercise task.".to_string()
                )
            })?
            .id;

        // Upsert
        let exercise_task = sqlx::query_as!(
            CmsPageExerciseTask,
            "
INSERT INTO exercise_tasks(
    id,
    exercise_slide_id,
    exercise_type,
    assignment,
    public_spec,
    private_spec,
    model_solution_spec,
    order_number
  )
VALUES ($1, $2, $3, $4, $5, $6, $7, $8) ON CONFLICT (id) DO
UPDATE
SET exercise_slide_id = $2,
  exercise_type = $3,
  assignment = $4,
  public_spec = $5,
  private_spec = $6,
  model_solution_spec = $7,
  order_number = $8,
  deleted_at = NULL
RETURNING id,
  exercise_slide_id,
  assignment,
  exercise_type,
  private_spec,
  order_number
                ",
            safe_for_db_exercise_task_id,
            safe_for_db_exercise_slide_id,
            task_update.exercise_type,
            task_update.assignment,
            public_spec,
            task_update.private_spec,
            model_solution_spec,
            task_update.order_number,
        )
        .fetch_one(&mut *conn)
        .await?;
        remapped_exercise_tasks.push(exercise_task)
    }
    Ok(remapped_exercise_tasks)
}

pub async fn upsert_peer_or_self_review_configs(
    conn: &mut PgConnection,
    existing_peer_reviews: &[Uuid],
    peer_reviews: &[CmsPeerOrSelfReviewConfig],
    remapped_exercises: &HashMap<Uuid, Exercise>,
    retain_ids: bool,
) -> ModelResult<HashMap<Uuid, CmsPeerOrSelfReviewConfig>> {
    if peer_reviews.is_empty() {
        Ok(HashMap::new())
    } else {
        let mut new_peer_or_self_review_config_id_to_old_id = HashMap::new();

        let mut sql: QueryBuilder<Postgres> = QueryBuilder::new(
            "INSERT INTO peer_or_self_review_configs (
        id,
        course_id,
        exercise_id,
        peer_reviews_to_give,
        peer_reviews_to_receive,
        processing_strategy,
        accepting_threshold,
        points_are_all_or_nothing,
        reset_answer_if_zero_points_from_review,
        review_instructions,
        deleted_at
      ) ",
        );

        // No way to return from push_values, we can use this to detect an error after the push_values
        let mut illegal_exercise_id = None;

        sql.push_values(peer_reviews.iter().take(1000), |mut x, pr| {
            let peer_review_exists = existing_peer_reviews.contains(&pr.id);
            let safe_for_db_peer_or_self_review_config_id = if retain_ids || peer_review_exists {
                pr.id
            } else {
                Uuid::new_v4()
            };
            new_peer_or_self_review_config_id_to_old_id
                .insert(safe_for_db_peer_or_self_review_config_id, pr.id);

            let safe_for_db_exercise_id = pr.exercise_id.and_then(|id| {
                let res = remapped_exercises.get(&id).map(|e| e.id);
                if res.is_none() {
                    error!("Illegal exercise id {:?}", id);
                    illegal_exercise_id = Some(id);
                }
                res
            });

            x.push_bind(safe_for_db_peer_or_self_review_config_id)
                .push_bind(pr.course_id)
                .push_bind(safe_for_db_exercise_id)
                .push_bind(pr.peer_reviews_to_give)
                .push_bind(pr.peer_reviews_to_receive)
                .push_bind(pr.processing_strategy)
                .push_bind(pr.accepting_threshold)
                .push_bind(pr.points_are_all_or_nothing)
                .push_bind(pr.reset_answer_if_zero_points_from_review)
                .push_bind(pr.review_instructions.clone())
                .push("NULL");
        });

        if let Some(illegal_exercise_id) = illegal_exercise_id {
            return Err(model_err!(
                InvalidRequest,
                format!("Illegal exercise id {:?}", illegal_exercise_id)
            ));
        }

        sql.push(
            " ON CONFLICT (id) DO
UPDATE
SET course_id = excluded.course_id,
  exercise_id = excluded.exercise_id,
  peer_reviews_to_give = excluded.peer_reviews_to_give,
  peer_reviews_to_receive = excluded.peer_reviews_to_receive,
  processing_strategy = excluded.processing_strategy,
  accepting_threshold = excluded.accepting_threshold,
  points_are_all_or_nothing = excluded.points_are_all_or_nothing,
  review_instructions = excluded.review_instructions,
  deleted_at = NULL
RETURNING id;
",
        );

        let ids = sql
            .build()
            .fetch_all(&mut *conn)
            .await?
            .iter()
            .map(|x| x.get(0))
            .collect::<Vec<_>>();

        let prs = sqlx::query_as!(
            CmsPeerOrSelfReviewConfig,
            r#"
SELECT id as "id!",
  course_id as "course_id!",
  exercise_id,
  peer_reviews_to_give as "peer_reviews_to_give!",
  peer_reviews_to_receive as "peer_reviews_to_receive!",
  processing_strategy AS "processing_strategy!",
  accepting_threshold "accepting_threshold!",
  points_are_all_or_nothing "points_are_all_or_nothing!",
  reset_answer_if_zero_points_from_review,
  review_instructions
FROM peer_or_self_review_configs
WHERE id IN (
    SELECT UNNEST($1::uuid [])
  )
  AND deleted_at IS NULL;
    "#,
            &ids
        )
        .fetch_all(&mut *conn)
        .await?;

        let mut remapped_peer_reviews = HashMap::new();

        for pr in prs {
            let old_id = new_peer_or_self_review_config_id_to_old_id
                .get(&pr.id)
                .ok_or_else(|| {
                    model_err!(Generic, "Inserted peer reviews not found".to_string())
                })?;
            remapped_peer_reviews.insert(*old_id, pr);
        }

        Ok(remapped_peer_reviews)
    }
}

pub async fn upsert_peer_or_self_review_questions(
    conn: &mut PgConnection,
    existing_peer_or_self_review_questions: &[Uuid],
    peer_or_self_review_questions: &[CmsPeerOrSelfReviewQuestion],
    remapped_peer_or_self_review_config_ids: &HashMap<Uuid, CmsPeerOrSelfReviewConfig>,
    retain_ids: bool,
) -> ModelResult<HashMap<Uuid, CmsPeerOrSelfReviewQuestion>> {
    if peer_or_self_review_questions.is_empty() {
        Ok(HashMap::new())
    } else {
        let mut new_peer_or_self_review_question_id_to_old_id = HashMap::new();

        let mut sql: QueryBuilder<Postgres> = QueryBuilder::new(
            "INSERT INTO peer_or_self_review_questions (
        id,
        peer_or_self_review_config_id,
        order_number,
        question,
        question_type,
        answer_required,
        weight,
        deleted_at
      ) ",
        );

        let peer_or_self_review_questions = peer_or_self_review_questions
            .iter()
            .take(1000)
            .map(|prq| {
                remapped_peer_or_self_review_config_ids
                    .get(&prq.peer_or_self_review_config_id)
                    .map(|r| (prq, r.id))
                    .ok_or_else(|| {
                        model_err!(
                            Generic,
                            "No peer review found for peer review questions".to_string()
                        )
                    })
            })
            .collect::<Result<Vec<_>, _>>()?;

        sql.push_values(
            peer_or_self_review_questions,
            |mut x, (prq, peer_or_self_review_config_id)| {
                let peer_review_question_exists =
                    existing_peer_or_self_review_questions.contains(&prq.id);
                let safe_for_db_peer_or_self_review_question_id =
                    if retain_ids || peer_review_question_exists {
                        prq.id
                    } else {
                        Uuid::new_v4()
                    };
                new_peer_or_self_review_question_id_to_old_id
                    .insert(safe_for_db_peer_or_self_review_question_id, prq.id);

                x.push_bind(safe_for_db_peer_or_self_review_question_id)
                    .push_bind(peer_or_self_review_config_id)
                    .push_bind(prq.order_number)
                    .push_bind(prq.question.as_str())
                    .push_bind(prq.question_type)
                    .push_bind(prq.answer_required)
                    .push_bind(prq.weight)
                    .push("NULL");
            },
        );

        sql.push(
            " ON CONFLICT (id) DO
UPDATE
SET peer_or_self_review_config_id = excluded.peer_or_self_review_config_id,
    order_number = excluded.order_number,
    question = excluded.question,
    question_type = excluded.question_type,
    answer_required = excluded.answer_required,
    weight = excluded.weight,
    deleted_at = NULL
RETURNING id;
",
        );

        let ids = sql
            .build()
            .fetch_all(&mut *conn)
            .await?
            .iter()
            .map(|x| x.get(0))
            .collect::<Vec<_>>();

        let prqs = sqlx::query_as!(
            CmsPeerOrSelfReviewQuestion,
            r#"
SELECT id AS "id!",
  answer_required AS "answer_required!",
  order_number AS "order_number!",
  peer_or_self_review_config_id AS "peer_or_self_review_config_id!",
  question AS "question!",
  question_type AS "question_type!",
  weight AS "weight!"
FROM peer_or_self_review_questions
WHERE id IN (
    SELECT UNNEST($1::uuid [])
  )
  AND deleted_at is null;
        "#,
            &ids
        )
        .fetch_all(&mut *conn)
        .await?;

        let mut remapped_peer_or_self_review_questions = HashMap::new();

        for prq in prqs {
            let old_id = new_peer_or_self_review_question_id_to_old_id
                .get(&prq.id)
                .ok_or_else(|| {
                    model_err!(Generic, "Inserted peer reviews not found".to_string())
                })?;
            remapped_peer_or_self_review_questions.insert(*old_id, prq);
        }

        Ok(remapped_peer_or_self_review_questions)
    }
}

/// Only used when testing.
pub async fn update_page_content(
    conn: &mut PgConnection,
    page_id: Uuid,
    content: &serde_json::Value,
) -> ModelResult<()> {
    sqlx::query!(
        "
UPDATE pages
SET content = $1
WHERE id = $2;
",
        content,
        page_id
    )
    .execute(conn)
    .await?;
    Ok(())
}

#[derive(Debug)]
struct ExerciseTaskIdAndSpec {
    pub id: Uuid,
    pub private_spec: Option<serde_json::Value>,
    pub public_spec: Option<serde_json::Value>,
    pub model_solution_spec: Option<serde_json::Value>,
}

async fn fetch_derived_spec(
    existing_exercise_task: Option<&ExerciseTaskIdAndSpec>,
    task_update: &NormalizedCmsExerciseTask,
    urls_by_exercise_type: &HashMap<&String, Url>,
    spec_fetcher: impl SpecFetcher,
    previous_spec: Option<serde_json::Value>,
    cms_block_id: Uuid,
) -> Result<Option<serde_json::Value>, ModelError> {
    let result_spec: Option<serde_json::Value> = match existing_exercise_task {
        Some(exercise_task) if exercise_task.private_spec == task_update.private_spec => {
            // Skip generating public spec for an existing exercise again if private spec is still the same.
            previous_spec
        }
        _ => {
            let url = urls_by_exercise_type
                .get(&task_update.exercise_type)
                .ok_or_else(|| {
                    model_err!(
                        PreconditionFailedWithCMSAnchorBlockId {
                            id: cms_block_id,
                            description: "Missing exercise type for exercise task.",
                        },
                        "Missing exercise type for exercise task.".to_string()
                    )
                })?
                .clone();
            let res = spec_fetcher(
                url,
                &task_update.exercise_type,
                task_update.private_spec.as_ref(),
            )
            .await?;
            Some(res)
        }
    };
    Ok(result_spec)
}

pub async fn insert_new_content_page(
    conn: &mut PgConnection,
    new_page: NewPage,
    user: Uuid,
    spec_fetcher: impl SpecFetcher,
    fetch_service_info: impl Fn(Url) -> BoxFuture<'static, ModelResult<ExerciseServiceInfoApi>>,
) -> ModelResult<Page> {
    let mut tx = conn.begin().await?;

    let course_material_content = vec![GutenbergBlock::hero_section(new_page.title.trim(), "")];

    let content_page = NewPage {
        chapter_id: new_page.chapter_id,
        content: course_material_content,
        course_id: new_page.course_id,
        exam_id: None,
        front_page_of_chapter_id: None,
        title: new_page.title,
        url_path: new_page.url_path,
        exercises: vec![],
        exercise_slides: vec![],
        exercise_tasks: vec![],
        content_search_language: None,
    };
    let page = crate::pages::insert_page(
        &mut tx,
        content_page,
        user,
        spec_fetcher,
        fetch_service_info,
    )
    .await?;

    tx.commit().await?;
    Ok(page)
}

pub async fn insert_page(
    conn: &mut PgConnection,
    new_page: NewPage,
    author: Uuid,
    spec_fetcher: impl SpecFetcher,
    fetch_service_info: impl Fn(Url) -> BoxFuture<'static, ModelResult<ExerciseServiceInfoApi>>,
) -> ModelResult<Page> {
    let normalized_url_path = normalize_url_path_for_storage(&new_page.url_path);
    let mut page_language_group_id = None;
    if let Some(course_id) = new_page.course_id {
        // insert language group
        let course = crate::courses::get_course(&mut *conn, course_id).await?;
        let new_language_group_id = crate::page_language_groups::insert(
            &mut *conn,
            crate::PKeyPolicy::Generate,
            course.course_language_group_id,
        )
        .await?;
        page_language_group_id = Some(new_language_group_id);
    }

    let next_order_number = match (new_page.chapter_id, new_page.course_id) {
        (Some(id), _) => get_next_page_order_number_in_chapter(conn, id).await?,
        (None, Some(course_id)) => {
            get_next_order_number_for_courses_top_level_pages(conn, course_id).await?
        }
        (None, None) => 1,
    };

    let course: OptionFuture<_> = new_page
        .course_id
        .map(|id| crate::courses::get_course(conn, id))
        .into();
    let course = course.await.transpose()?;

    let content = replace_duplicate_client_ids(new_page.content.clone());

    let mut tx = conn.begin().await?;

    let content_search_language = course
        .and_then(|c| c.content_search_language)
        .or(new_page.content_search_language)
        .unwrap_or_else(|| "simple".to_string());
    let page = sqlx::query_as!(
        Page,
        r#"
INSERT INTO pages(
    course_id,
    exam_id,
    content,
    url_path,
    title,
    order_number,
    chapter_id,
    content_search_language,
    page_language_group_id
  )
VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9)
RETURNING id,
  created_at,
  updated_at,
  course_id,
  exam_id,
  chapter_id,
  url_path,
  title,
  deleted_at,
  content,
  order_number,
  copied_from,
  pages.hidden,
  page_language_group_id
          "#,
        new_page.course_id,
        new_page.exam_id,
        serde_json::to_value(content)?,
        normalized_url_path,
        new_page.title.trim(),
        next_order_number,
        new_page.chapter_id,
        content_search_language as _,
        page_language_group_id,
    )
    .fetch_one(&mut *tx)
    .await?;

    let parsed_content: Vec<GutenbergBlock> = serde_json::from_value(page.content)?;

    let cms_page = update_page(
        &mut tx,
        PageUpdateArgs {
            page_id: page.id,
            author,
            cms_page_update: CmsPageUpdate {
                content: parsed_content,
                exercises: new_page.exercises,
                exercise_slides: new_page.exercise_slides,
                exercise_tasks: new_page.exercise_tasks,
                url_path: page.url_path,
                title: page.title,
                chapter_id: page.chapter_id,
            },
            retain_ids: false,
            history_change_reason: HistoryChangeReason::PageSaved,
            is_exam_page: new_page.exam_id.is_some(),
        },
        spec_fetcher,
        fetch_service_info,
    )
    .await?;

    if let Some(front_page_of_chapter_id) = new_page.front_page_of_chapter_id {
        let _res = sqlx::query_as!(
            DatabaseChapter,
            r#"
UPDATE chapters
SET front_page_id = $1
WHERE id = $2
RETURNING *;
        "#,
            page.id,
            front_page_of_chapter_id
        )
        // this should fail if no rows returned
        .fetch_one(&mut *tx)
        .await?;
    }

    tx.commit().await?;
    Ok(Page {
        content: cms_page.page.content,
        course_id: page.course_id,
        exam_id: page.exam_id,
        created_at: page.created_at,
        updated_at: page.updated_at,
        deleted_at: page.deleted_at,
        id: page.id,
        title: cms_page.page.title,
        url_path: cms_page.page.url_path,
        order_number: page.order_number,
        chapter_id: page.chapter_id,
        copied_from: page.copied_from,
        hidden: page.hidden,
        page_language_group_id: page.page_language_group_id,
    })
}

pub async fn create_for_course_id(
    conn: &mut PgConnection,
    course_id: Uuid,
    new_page: NewPage,
    author: Uuid,
    spec_fetcher: impl SpecFetcher,
    fetch_service_info: impl Fn(Url) -> BoxFuture<'static, ModelResult<ExerciseServiceInfoApi>>,
) -> ModelResult<Page> {
    if new_page.course_id != Some(course_id) || new_page.exam_id.is_some() {
        return Err(model_err!(
            PreconditionFailed,
            "Page must be created for the expected course".to_string()
        ));
    }

    let mut tx = conn.begin().await?;

    if let Some(chapter_id) = new_page.chapter_id {
        let chapter = get_chapter(&mut tx, chapter_id).await?;
        if chapter.course_id != course_id {
            return Err(model_err!(
                PreconditionFailed,
                "Chapter must belong to the expected course".to_string()
            ));
        }
    }

    if let Some(front_page_of_chapter_id) = new_page.front_page_of_chapter_id {
        let chapter = get_chapter(&mut tx, front_page_of_chapter_id).await?;
        if chapter.course_id != course_id {
            return Err(model_err!(
                PreconditionFailed,
                "Chapter must belong to the expected course".to_string()
            ));
        }
        if new_page.chapter_id.is_none() || new_page.chapter_id != new_page.front_page_of_chapter_id
        {
            return Err(model_err!(
                PreconditionFailed,
                "Page chapter_id must match front_page_of_chapter_id".to_string()
            ));
        }
    }

    let mut new_page = new_page;
    if new_page.content.is_empty() {
        new_page.content = vec![GutenbergBlock::hero_section(new_page.title.trim(), "")];
    }

    let page = insert_page(&mut tx, new_page, author, spec_fetcher, fetch_service_info).await?;
    tx.commit().await?;
    Ok(page)
}

pub async fn delete_page_and_exercises(
    conn: &mut PgConnection,
    page_id: Uuid,
    author: Uuid,
) -> ModelResult<Page> {
    let mut tx = conn.begin().await?;
    let page = sqlx::query_as!(
        Page,
        r#"
UPDATE pages
SET deleted_at = now()
WHERE id = $1
AND deleted_at IS NULL
RETURNING id,
  created_at,
  updated_at,
  course_id,
  exam_id,
  chapter_id,
  url_path,
  title,
  deleted_at,
  content,
  order_number,
  copied_from,
  hidden,
  page_language_group_id
          "#,
        page_id,
    )
    .fetch_one(&mut *tx)
    .await?;

    sqlx::query!(
        r#"
  UPDATE exercises
  SET deleted_at = now()
  WHERE page_id = $1
  AND deleted_at IS NULL
          "#,
        page_id,
    )
    .execute(&mut *tx)
    .await?;

    sqlx::query!(
        "
UPDATE exercise_slides
SET deleted_at = now()
WHERE exercise_id IN (
    SELECT id
    FROM exercises
    WHERE page_id = $1
  )
  AND deleted_at IS NULL;
        ",
        page.id
    )
    .execute(&mut *tx)
    .await?;

    sqlx::query!(
        r#"
UPDATE exercise_tasks
SET deleted_at = now()
WHERE exercise_slide_id IN (
    SELECT s.id
    FROM exercise_slides s
      JOIN exercises e ON (s.exercise_id = e.id)
    WHERE e.page_id = $1
  )
  AND deleted_at IS NULL;
            "#,
        page.id
    )
    .execute(&mut *tx)
    .await?;

    let history_content = PageHistoryContent {
        content: json!(null),
        exercises: Vec::new(),
        exercise_slides: Vec::new(),
        exercise_tasks: Vec::new(),
        peer_or_self_review_configs: Vec::new(),
        peer_or_self_review_questions: Vec::new(),
    };
    crate::page_history::insert(
        &mut tx,
        PKeyPolicy::Generate,
        page.id,
        &page.title,
        &history_content,
        HistoryChangeReason::PageDeleted,
        author,
        None,
    )
    .await?;

    tx.commit().await?;
    Ok(page)
}

pub async fn get_chapters_pages_with_exercises(
    conn: &mut PgConnection,
    chapters_id: Uuid,
) -> ModelResult<Vec<PageWithExercises>> {
    let chapter_pages = sqlx::query_as!(
        Page,
        r#"
SELECT id,
  created_at,
  updated_at,
  course_id,
  exam_id,
  chapter_id,
  url_path,
  title,
  deleted_at,
  content,
  order_number,
  copied_from,
  hidden,
  page_language_group_id
FROM pages
WHERE chapter_id = $1
  AND deleted_at IS NULL
        "#,
        chapters_id
    )
    .fetch_all(&mut *conn)
    .await?;
    let page_ids: Vec<Uuid> = chapter_pages.iter().map(|page| page.id).collect();
    let pages_exercises = sqlx::query_as!(
        Exercise,
        r#"
SELECT *
FROM exercises
WHERE page_id IN (
    SELECT UNNEST($1::uuid [])
  )
  AND deleted_at IS NULL
        "#,
        &page_ids
    )
    .fetch_all(&mut *conn)
    .await?;

    let mut page_to_exercises: HashMap<Uuid, Vec<Exercise>> = pages_exercises
        .into_iter()
        .into_group_map_by(|exercise| exercise.page_id);
    let mut chapter_pages_with_exercises: Vec<PageWithExercises> = chapter_pages
        .into_iter()
        .map(|page| {
            let page_id = page.id;
            let mut exercises = page_to_exercises.remove(&page_id).unwrap_or_default();

            exercises.sort_by_key(|a| a.order_number);
            PageWithExercises { page, exercises }
        })
        .collect();

    chapter_pages_with_exercises.sort_by_key(|a| a.page.order_number);

    Ok(chapter_pages_with_exercises)
}

pub async fn get_next_page(
    conn: &mut PgConnection,
    page_id: Uuid,
    visibility: PageVisibility,
) -> ModelResult<Option<PageRoutingData>> {
    let page_metadata = get_current_page_metadata(conn, page_id).await?;
    let next_page = get_next_page_by_order_number(conn, &page_metadata, visibility).await?;

    match next_page {
        Some(next_page) => Ok(Some(next_page)),
        None => {
            let first_page =
                get_next_page_by_chapter_number(conn, &page_metadata, visibility).await?;
            Ok(first_page)
        }
    }
}

async fn get_current_page_metadata(
    conn: &mut PgConnection,
    page_id: Uuid,
) -> ModelResult<PageMetadata> {
    let page_metadata = sqlx::query_as!(
        PageMetadata,
        r#"
SELECT p.id as page_id,
  p.order_number as order_number,
  p.course_id as course_id,
  p.exam_id as exam_id,
  c.id as "chapter_id?",
  c.chapter_number as "chapter_number?"
FROM pages p
  LEFT JOIN chapters c ON p.chapter_id = c.id
WHERE p.id = $1;
"#,
        page_id
    )
    .fetch_one(conn)
    .await?;

    if page_metadata.chapter_number.is_none() {
        return Err(model_err!(
            InvalidRequest,
            "Page is not related to any chapter".to_string()
        ));
    }

    Ok(page_metadata)
}

async fn get_next_page_by_order_number(
    conn: &mut PgConnection,
    current_page_metadata: &PageMetadata,
    visibility: PageVisibility,
) -> ModelResult<Option<PageRoutingData>> {
    let inverse_visibility_filter = visibility.get_inverse_visibility_filter();
    let next_page = sqlx::query_as!(
        PageRoutingData,
        r#"
SELECT p.url_path as url_path,
  p.title as title,
  p.id as page_id,
  c.chapter_number as chapter_number,
  c.id as chapter_id,
  c.opens_at as chapter_opens_at,
  c.front_page_id as chapter_front_page_id
FROM pages p
  LEFT JOIN chapters c ON p.chapter_id = c.id
WHERE p.order_number = (
    SELECT MIN(pa.order_number)
    FROM pages pa
    WHERE pa.order_number > $1
      AND pa.deleted_at IS NULL
      AND pa.hidden IS DISTINCT FROM $4
      AND pa.course_id = p.course_id
      AND pa.chapter_id IS NOT DISTINCT FROM p.chapter_id
  )
  AND p.course_id = $2
  AND c.chapter_number = $3
  AND p.deleted_at IS NULL
  AND p.hidden IS DISTINCT FROM $4;
        "#,
        current_page_metadata.order_number,
        current_page_metadata.course_id,
        current_page_metadata.chapter_number,
        inverse_visibility_filter,
    )
    .fetch_optional(conn)
    .await?;

    Ok(next_page)
}

async fn get_next_page_by_chapter_number(
    conn: &mut PgConnection,
    current_page_metadata: &PageMetadata,
    visibility: PageVisibility,
) -> ModelResult<Option<PageRoutingData>> {
    let inverse_visibility_filter = visibility.get_inverse_visibility_filter();
    let next_page = sqlx::query_as!(
        PageRoutingData,
        r#"
SELECT p.url_path as url_path,
  p.title as title,
  p.id as page_id,
  c.chapter_number as chapter_number,
  c.id as chapter_id,
  c.opens_at as chapter_opens_at,
  c.front_page_id as chapter_front_page_id
FROM chapters c
  INNER JOIN pages p on c.id = p.chapter_id
WHERE c.chapter_number = (
    SELECT MIN(ca.chapter_number)
    FROM chapters ca
    WHERE ca.chapter_number > $1
      AND ca.deleted_at IS NULL
  )
  AND c.course_id = $2
  AND p.deleted_at IS NULL
  AND p.hidden IS DISTINCT FROM $3
ORDER BY p.order_number
LIMIT 1;
        "#,
        current_page_metadata.chapter_number,
        current_page_metadata.course_id,
        inverse_visibility_filter,
    )
    .fetch_optional(conn)
    .await?;

    Ok(next_page)
}

async fn get_next_page_order_number_in_chapter(
    conn: &mut PgConnection,
    chapter_id: Uuid,
) -> ModelResult<i32> {
    let next_order_number = sqlx::query!(
        "
select max(p.order_number) as order_number
from pages p
where p.chapter_id = $1
  and p.deleted_at is null;
",
        chapter_id
    )
    .fetch_one(conn)
    .await?;

    match next_order_number.order_number {
        Some(order_number) => Ok(order_number + 1),
        None => Ok(0),
    }
}

pub async fn get_page_navigation_data(
    conn: &mut PgConnection,
    page_id: Uuid,
    visibility: PageVisibility,
) -> ModelResult<PageNavigationInformation> {
    let previous_page_data = get_previous_page(conn, page_id, visibility).await?;

    let next_page_data = get_next_page(conn, page_id, visibility).await?;

    let chapter_front_page = get_chapter_front_page_by_page_id(conn, page_id).await?;
    // This may be different from the chapter of the previous page and the chapter of the next page so we need to fetch it to be sure.
    let chapter_front_page_chapter = OptionFuture::from(
        chapter_front_page
            .clone()
            .and_then(|front_page| front_page.chapter_id)
            .map(|chapter_id| chapters::get_chapter(conn, chapter_id)),
    )
    .await
    .transpose()?;

    let chapter_front_page = chapter_front_page
        .map(|front_page| -> ModelResult<_> {
            if let Some(chapter_front_page_chapter) = chapter_front_page_chapter {
                Ok(PageRoutingData {
                    url_path: front_page.url_path,
                    title: front_page.title,
                    page_id: front_page.id,
                    chapter_number: chapter_front_page_chapter.chapter_number,
                    chapter_id: chapter_front_page_chapter.id,
                    chapter_opens_at: chapter_front_page_chapter.opens_at,
                    chapter_front_page_id: Some(front_page.id),
                })
            } else {
                Err(model_err!(
                    InvalidRequest,
                    "Chapter front page chapter not found".to_string()
                ))
            }
        })
        .transpose()?;
    Ok(PageNavigationInformation {
        chapter_front_page,
        next_page: next_page_data,
        previous_page: previous_page_data,
    })
}

pub async fn get_previous_page(
    conn: &mut PgConnection,
    page_id: Uuid,
    visibility: PageVisibility,
) -> ModelResult<Option<PageRoutingData>> {
    let page_metadata = get_current_page_metadata(conn, page_id).await?;
    let previous_page = get_previous_page_by_order_number(conn, &page_metadata, visibility).await?;

    match previous_page {
        Some(previous_page) => Ok(Some(previous_page)),
        None => {
            let first_page =
                get_previous_page_by_chapter_number(conn, &page_metadata, visibility).await?;
            Ok(first_page)
        }
    }
}

pub async fn get_chapter_front_page_by_page_id(
    conn: &mut PgConnection,
    page_id: Uuid,
) -> ModelResult<Option<Page>> {
    let page_metadata = get_current_page_metadata(conn, page_id).await?;
    let chapter = chapters::get_chapter_info_by_page_metadata(conn, &page_metadata).await?;
    let page_option_future: OptionFuture<_> = chapter
        .chapter_front_page_id
        .map(|chapter_front_page_id| get_page(conn, chapter_front_page_id))
        .into();
    let page = page_option_future.await.transpose()?;
    Ok(page)
}

async fn get_previous_page_by_order_number(
    conn: &mut PgConnection,
    current_page_metadata: &PageMetadata,
    visibility: PageVisibility,
) -> ModelResult<Option<PageRoutingData>> {
    let inverse_visibility_filter = visibility.get_inverse_visibility_filter();
    let previous_page = sqlx::query_as!(
        PageRoutingData,
        r#"
SELECT p.url_path as url_path,
  p.title as title,
  c.chapter_number as chapter_number,
  p.id as page_id,
  c.id as chapter_id,
  c.opens_at as chapter_opens_at,
  c.front_page_id as chapter_front_page_id
FROM pages p
  LEFT JOIN chapters c ON p.chapter_id = c.id
WHERE p.order_number = (
    SELECT MAX(pa.order_number)
    FROM pages pa
    WHERE pa.order_number < $1
      AND pa.deleted_at IS NULL
      AND pa.hidden IS DISTINCT FROM $4
      AND pa.course_id = p.course_id
      AND pa.chapter_id IS NOT DISTINCT FROM p.chapter_id
  )
  AND p.course_id = $2
  AND c.chapter_number = $3
  AND p.deleted_at IS NULL
  AND p.hidden IS DISTINCT FROM $4;
        "#,
        current_page_metadata.order_number,
        current_page_metadata.course_id,
        current_page_metadata.chapter_number,
        inverse_visibility_filter,
    )
    .fetch_optional(conn)
    .await?;

    Ok(previous_page)
}

async fn get_previous_page_by_chapter_number(
    conn: &mut PgConnection,
    current_page_metadata: &PageMetadata,
    visibility: PageVisibility,
) -> ModelResult<Option<PageRoutingData>> {
    let inverse_visibility_filter = visibility.get_inverse_visibility_filter();
    let previous_page = sqlx::query_as!(
        PageRoutingData,
        r#"
SELECT p.url_path AS url_path,
  p.title AS title,
  p.id AS page_id,
  c.chapter_number AS chapter_number,
  c.id AS chapter_id,
  c.opens_at AS chapter_opens_at,
  c.front_page_id AS chapter_front_page_id
FROM chapters c
  INNER JOIN pages p ON c.id = p.chapter_id
WHERE c.chapter_number = (
    SELECT MAX(ca.chapter_number)
    FROM chapters ca
    WHERE ca.chapter_number < $1
      AND ca.deleted_at IS NULL
  )
  AND c.course_id = $2
  AND p.deleted_at IS NULL
  AND p.hidden IS DISTINCT FROM $3
ORDER BY p.order_number DESC
LIMIT 1;
        "#,
        current_page_metadata.chapter_number,
        current_page_metadata.course_id,
        inverse_visibility_filter,
    )
    .fetch_optional(conn)
    .await?;

    Ok(previous_page)
}

async fn get_next_order_number_for_courses_top_level_pages(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> ModelResult<i32> {
    let next_order_number = sqlx::query!(
        "
select max(p.order_number) as order_number
from pages p
where p.course_id = $1
  and p.chapter_id is null
  and p.deleted_at is null;
",
        course_id
    )
    .fetch_one(conn)
    .await?;

    match next_order_number.order_number {
        Some(order_number) => Ok(order_number + 1),
        None => Ok(0),
    }
}

pub async fn get_chapter_pages(
    conn: &mut PgConnection,
    chapter_id: Uuid,
) -> ModelResult<Vec<Page>> {
    let pages = sqlx::query_as!(
        Page,
        "
SELECT id,
  created_at,
  updated_at,
  course_id,
  exam_id,
  chapter_id,
  url_path,
  title,
  deleted_at,
  content,
  order_number,
  copied_from,
  hidden,
  page_language_group_id
FROM pages p
WHERE p.chapter_id = $1
  AND p.deleted_at IS NULL;
    ",
        chapter_id
    )
    .fetch_all(conn)
    .await?;

    Ok(pages)
}

pub async fn get_chapters_visible_pages_exclude_main_frontpage(
    conn: &mut PgConnection,
    chapter_id: Uuid,
) -> ModelResult<Vec<Page>> {
    let pages = sqlx::query_as!(
        Page,
        "
SELECT id,
  created_at,
  updated_at,
  course_id,
  exam_id,
  chapter_id,
  url_path,
  title,
  deleted_at,
  content,
  order_number,
  copied_from,
  hidden,
  page_language_group_id
FROM pages p
WHERE p.chapter_id = $1
  AND p.deleted_at IS NULL
  AND p.hidden IS FALSE
  AND p.url_path IS NOT NULL
  AND p.id NOT IN (
    SELECT front_page_id
    FROM chapters c
    WHERE c.front_page_id = p.id
  );
    ",
        chapter_id
    )
    .fetch_all(conn)
    .await?;
    Ok(pages)
}

/**
Returns search results for a phrase i.e. looks for matches where the words come up right after each other
*/
pub async fn get_page_search_results_for_phrase(
    conn: &mut PgConnection,
    course_id: Uuid,
    page_search_request: &SearchRequest,
) -> ModelResult<Vec<PageSearchResult>> {
    get_page_search_results(
        conn,
        course_id,
        page_search_request,
        PageSearchQueryType::Phrase,
    )
    .await
}

/**
Returns search results for the given words. The words can appear in the source document in any order.
*/
pub async fn get_page_search_results_for_words(
    conn: &mut PgConnection,
    course_id: Uuid,
    page_search_request: &SearchRequest,
) -> ModelResult<Vec<PageSearchResult>> {
    get_page_search_results(
        conn,
        course_id,
        page_search_request,
        PageSearchQueryType::Words,
    )
    .await
}

#[derive(Debug, sqlx::FromRow)]
struct RawPageSearchResult {
    id: Uuid,
    title_headline: Option<String>,
    rank: Option<f32>,
    url_path: String,
    chapter_name: Option<String>,
    content: Value,
}

#[derive(Debug, sqlx::FromRow)]
struct SearchContentHeadlineRow {
    content_headline: Option<String>,
}

#[derive(Clone, Copy)]
enum PageSearchQueryType {
    Phrase,
    Words,
}

fn extract_searchable_text_from_content_value(content: &Value) -> String {
    let mut searchable_parts = Vec::new();
    collect_searchable_text_from_content_value(content, None, &mut searchable_parts);
    searchable_parts.join(" ")
}

fn collect_searchable_text_from_content_value(
    value: &Value,
    current_key: Option<&str>,
    searchable_parts: &mut Vec<String>,
) {
    match value {
        Value::Object(map) => {
            for (key, value) in map {
                collect_searchable_text_from_content_value(value, Some(key), searchable_parts);
            }
        }
        Value::Array(values) => {
            for value in values {
                collect_searchable_text_from_content_value(value, None, searchable_parts);
            }
        }
        Value::String(value) if matches!(current_key, Some("content" | "title" | "subtitle")) => {
            searchable_parts.push(value.clone());
        }
        _ => {}
    }
}

fn sanitized_searchable_text_for_public_page(content: &Value) -> String {
    let Ok(blocks) = serde_json::from_value::<Vec<GutenbergBlock>>(content.clone()) else {
        tracing::warn!(
            "Failed to deserialize page content for search snippet sanitization. Falling back to raw searchable text."
        );
        return extract_searchable_text_from_content_value(content);
    };

    let filtered_blocks = filter_lock_chapter_blocks(blocks, false);
    match serde_json::to_value(filtered_blocks) {
        Ok(filtered_content) => extract_searchable_text_from_content_value(&filtered_content),
        Err(error) => {
            tracing::warn!(
                "Failed to serialize filtered page content for search snippet sanitization: {}. Falling back to raw searchable text.",
                error
            );
            extract_searchable_text_from_content_value(content)
        }
    }
}

async fn get_page_search_results(
    conn: &mut PgConnection,
    course_id: Uuid,
    page_search_request: &SearchRequest,
    query_type: PageSearchQueryType,
) -> ModelResult<Vec<PageSearchResult>> {
    let course = crate::courses::get_course(&mut *conn, course_id).await?;
    let content_search_language = course
        .content_search_language
        .as_deref()
        .unwrap_or("simple");

    // Last word of the search term needed so that the sql statement can change it to a prefix match.
    // Allows the last word to not be fully typed.
    let last_word = if let Some(last) = page_search_request
        .query
        .trim()
        .split_ascii_whitespace()
        .last()
    {
        last.to_string()
    } else {
        return Ok(Vec::new());
    };

    let query_builder = match query_type {
        PageSearchQueryType::Phrase => "phraseto_tsquery",
        PageSearchQueryType::Words => "plainto_tsquery",
    };
    let query_builder_comment = match query_type {
        PageSearchQueryType::Phrase =>
            "-- Converts the search term to a phrase search with phraseto_tsquery but appends ':*' to the last word so that it
    -- becomes a prefix match. This way the search will also contain results when the last word in the search term
    -- is only partially typed. Note that if to_tsquery($4) decides to stem the word, the replacement will be skipped.",
        PageSearchQueryType::Words =>
            "-- Converts the search term to a word search with ands between the words with plainto_tsquery but appends ':*' to the
    -- last word so that it becomes a prefix match. This way the search will also contain results when the last word in
    -- the search term is only partially typed. Note that if to_tsquery($4) decides to stem the word, the replacement
    -- will be skipped.",
    };

    let search_results_sql = format!(
        r#"
WITH cte as (
    {query_builder_comment}
    SELECT ts_rewrite(
        {query_builder}($2::regconfig, $3),
        to_tsquery($4),
        to_tsquery($4 || ':*')
    ) as query
)
SELECT p.id,
    ts_rank(
        p.content_search,
        (
            SELECT query
            from cte
        )
    ) as rank,
    ts_headline(
        $2::regconfig,
        p.title,
        (
            SELECT query
            from cte
        ),
        'MaxFragments=1, MaxWords=20, MinWords=1'
    ) as title_headline,
    COALESCE(p.url_path, '') as url_path,
    c.name as chapter_name,
    p.content
FROM pages p
LEFT JOIN chapters c ON p.chapter_id = c.id
WHERE p.course_id = $1
    AND p.deleted_at IS NULL
    AND p.hidden IS FALSE
    AND p.url_path IS NOT NULL
    AND p.content_search @@ (
        SELECT query
        from cte
    )
ORDER BY rank DESC
LIMIT 50;
        "#
    );

    // The formatted fragments are fixed strings selected from PageSearchQueryType; all request data
    // remains passed through bind parameters below.
    let raw_results = sqlx::query_as::<_, RawPageSearchResult>(AssertSqlSafe(search_results_sql))
        .bind(course_id)
        .bind(content_search_language)
        .bind(&page_search_request.query)
        .bind(&last_word)
        .fetch_all(&mut *conn)
        .await?;

    let sanitized_search_texts = raw_results
        .iter()
        .map(|result| sanitized_searchable_text_for_public_page(&result.content))
        .collect::<Vec<_>>();
    let content_headlines = build_public_search_content_headlines(
        conn,
        content_search_language,
        &page_search_request.query,
        &last_word,
        query_type,
        &sanitized_search_texts,
    )
    .await?;

    let search_results = raw_results
        .into_iter()
        .zip(content_headlines)
        .map(|(result, content_headline)| PageSearchResult {
            id: result.id,
            title_headline: result.title_headline,
            rank: result.rank,
            content_headline,
            url_path: result.url_path,
            chapter_name: result.chapter_name,
        })
        .collect::<Vec<_>>();

    Ok(add_course_url_prefix_to_search_results(
        search_results,
        &course,
    ))
}

async fn build_public_search_content_headlines(
    conn: &mut PgConnection,
    content_search_language: &str,
    query: &str,
    last_word: &str,
    query_type: PageSearchQueryType,
    sanitized_search_texts: &[String],
) -> ModelResult<Vec<Option<String>>> {
    if sanitized_search_texts.is_empty() {
        return Ok(Vec::new());
    }

    let query_builder = match query_type {
        PageSearchQueryType::Phrase => "phraseto_tsquery",
        PageSearchQueryType::Words => "plainto_tsquery",
    };
    let query_builder_comment = match query_type {
        PageSearchQueryType::Phrase =>
            "-- Converts the search term to a phrase search with phraseto_tsquery but appends ':*' to the last word so that it
    -- becomes a prefix match. This way the search will also contain results when the last word in the search term
    -- is only partially typed. Note that if to_tsquery($3) decides to stem the word, the replacement will be skipped.",
        PageSearchQueryType::Words =>
            "-- Converts the search term to a word search with ands between the words with plainto_tsquery but appends ':*' to the
    -- last word so that it becomes a prefix match. This way the search will also contain results when the last word in
    -- the search term is only partially typed. Note that if to_tsquery($3) decides to stem the word, the replacement
    -- will be skipped.",
    };

    let content_headline_sql = format!(
        r#"
WITH cte as (
    {query_builder_comment}
    SELECT ts_rewrite(
        {query_builder}($1::regconfig, $2),
        to_tsquery($3),
        to_tsquery($3 || ':*')
    ) as query
)
SELECT ts_headline(
    $1::regconfig,
    input.content,
    (
        SELECT query
        from cte
    ),
    'MaxFragments=1, MaxWords=30, MinWords=10'
) as content_headline
FROM unnest($4::text[]) WITH ORDINALITY AS input(content, ord)
ORDER BY ord;
        "#
    );

    Ok(
        // The formatted fragments are fixed strings selected from PageSearchQueryType; all request data
        // remains passed through bind parameters below.
        sqlx::query_as::<_, SearchContentHeadlineRow>(AssertSqlSafe(content_headline_sql))
            .bind(content_search_language)
            .bind(query)
            .bind(last_word)
            .bind(sanitized_search_texts.to_vec())
            .fetch_all(&mut *conn)
            .await?
            .into_iter()
            .map(|row| row.content_headline)
            .collect(),
    )
}

fn add_course_url_prefix_to_search_results(
    search_results: Vec<PageSearchResult>,
    course: &Course,
) -> Vec<PageSearchResult> {
    search_results
        .into_iter()
        .map(|mut sr| {
            let optional_slash = if sr.url_path.starts_with('/') {
                ""
            } else {
                "/"
            };
            sr.url_path = format!("/{}{}{}", course.slug, optional_slash, sr.url_path);
            sr
        })
        .collect()
}

/// Restore page contents and exercises to a previous revision
pub async fn restore(
    conn: &mut PgConnection,
    page_id: Uuid,
    history_id: Uuid,
    author: Uuid,
    spec_fetcher: impl SpecFetcher,
    fetch_service_info: impl Fn(Url) -> BoxFuture<'static, ModelResult<ExerciseServiceInfoApi>>,
) -> ModelResult<Uuid> {
    // fetch old content
    let page = get_page(conn, page_id).await?;
    let history_data = page_history::get_history_data(conn, history_id).await?;

    let parsed_content: Vec<GutenbergBlock> = serde_json::from_value(history_data.content.content)?;

    update_page(
        conn,
        PageUpdateArgs {
            page_id: page.id,
            author,
            cms_page_update: CmsPageUpdate {
                content: parsed_content,
                exercises: history_data.content.exercises,
                exercise_slides: history_data.content.exercise_slides,
                exercise_tasks: history_data.content.exercise_tasks,
                url_path: page.url_path,
                title: history_data.title,
                chapter_id: page.chapter_id,
            },
            retain_ids: true,
            history_change_reason: HistoryChangeReason::HistoryRestored,
            is_exam_page: history_data.exam_id.is_some(),
        },
        spec_fetcher,
        fetch_service_info,
    )
    .await?;

    Ok(history_id)
}

#[allow(clippy::too_many_arguments)]
pub async fn restore_from_history_for_page_id(
    conn: &mut PgConnection,
    page_id: Uuid,
    history_id: Uuid,
    authorized_source_page_id: Option<Uuid>,
    expected_course_id: Option<Uuid>,
    expected_exam_id: Option<Uuid>,
    author: Uuid,
    spec_fetcher: impl SpecFetcher,
    fetch_service_info: impl Fn(Url) -> BoxFuture<'static, ModelResult<ExerciseServiceInfoApi>>,
) -> ModelResult<Uuid> {
    let mut tx = conn.begin().await?;
    let validated_source_page_id = if let Some(source_page_id) = authorized_source_page_id {
        let source_page = sqlx::query!(
            r#"
SELECT p.id
FROM pages p
WHERE p.id = $1
  AND p.deleted_at IS NULL
  AND (p.course_id = $2 OR p.exam_id = $3)
FOR UPDATE
            "#,
            source_page_id,
            expected_course_id,
            expected_exam_id
        )
        .fetch_optional(&mut *tx)
        .await?;
        source_page.map(|row| row.id)
    } else {
        None
    };

    sqlx::query!(
        r#"
SELECT p.id
FROM pages p
WHERE p.id = $1
  AND p.deleted_at IS NULL
  AND (p.course_id = $2 OR p.exam_id = $3)
FOR UPDATE
        "#,
        page_id,
        expected_course_id,
        expected_exam_id
    )
    .fetch_one(&mut *tx)
    .await?;

    sqlx::query!(
        r#"
SELECT ph.id
FROM page_history ph
WHERE ph.id = $1
  AND ph.deleted_at IS NULL
  AND (
    ph.page_id = $2
    OR ph.page_id = $3
  )
FOR UPDATE
        "#,
        history_id,
        page_id,
        validated_source_page_id
    )
    .fetch_one(&mut *tx)
    .await?;

    let restored = restore(
        &mut tx,
        page_id,
        history_id,
        author,
        spec_fetcher,
        fetch_service_info,
    )
    .await?;
    tx.commit().await?;
    Ok(restored)
}

pub async fn get_organization_id(conn: &mut PgConnection, page_id: Uuid) -> ModelResult<Uuid> {
    let res = sqlx::query!(
        "
SELECT organizations.id
FROM pages
  LEFT OUTER JOIN courses ON courses.id = pages.course_id
  LEFT OUTER JOIN exams ON exams.id = pages.exam_id
  JOIN organizations ON organizations.id = courses.organization_id
  OR organizations.id = exams.organization_id
WHERE pages.id = $1
",
        page_id,
    )
    .fetch_one(&mut *conn)
    .await?;
    Ok(res.id)
}

pub async fn get_page_chapter_and_course_information(
    conn: &mut PgConnection,
    page_id: Uuid,
) -> ModelResult<PageChapterAndCourseInformation> {
    let res = sqlx::query_as!(
        PageChapterAndCourseInformation,
        r#"
SELECT chapters.name as "chapter_name?",
  chapters.chapter_number as "chapter_number?",
  courses.name as "course_name?",
  courses.slug as "course_slug?",
  chapters.front_page_id as "chapter_front_page_id?",
  p2.url_path as "chapter_front_page_url_path?",
  organizations.slug as "organization_slug?"
FROM pages
  LEFT JOIN chapters on pages.chapter_id = chapters.id
  LEFT JOIN courses on pages.course_id = courses.id
  LEFT JOIN pages p2 ON chapters.front_page_id = p2.id
  LEFT JOIN organizations on courses.organization_id = organizations.id
WHERE pages.id = $1
"#,
        page_id,
    )
    .fetch_one(&mut *conn)
    .await?;
    Ok(res)
}

pub async fn get_page_by_course_id_and_language_group(
    conn: &mut PgConnection,
    course_id: Uuid,
    page_language_group_id: Uuid,
) -> ModelResult<Page> {
    let page = sqlx::query_as!(
        Page,
        "
SELECT id,
    created_at,
    updated_at,
    course_id,
    exam_id,
    chapter_id,
    url_path,
    title,
    deleted_at,
    content,
    order_number,
    copied_from,
    hidden,
    page_language_group_id
FROM pages p
WHERE p.course_id = $1
    AND p.page_language_group_id = $2
    AND p.deleted_at IS NULL
    ",
        course_id,
        page_language_group_id
    )
    .fetch_one(&mut *conn)
    .await?;
    Ok(page)
}

/// Makes the order numbers and chapter ids to match in the db what's in the page objects
/// Assumes that all pages belong to the given course id
/// Also assumes the list of pages includes all nondeleted pages in the course, except chapter front pages, which cannot be moved, otherwise we will end up with random order numbers
pub async fn reorder_pages(
    conn: &mut PgConnection,
    pages: &[Page],
    course_id: Uuid,
) -> ModelResult<()> {
    let db_pages =
        get_all_by_course_id_and_visibility(conn, course_id, PageVisibility::Any).await?;
    let chapters = course_chapters(conn, course_id).await?;

    let mut chapter_pages: HashMap<Option<Uuid>, Vec<&Page>> = HashMap::new();

    for page in pages {
        chapter_pages.entry(page.chapter_id).or_default().push(page);
    }

    let mut normalized_pages = Vec::with_capacity(pages.len());

    for (_, chapter_pages) in chapter_pages.iter() {
        // Sort by order_number and then by id for consistency
        let mut sorted_pages = chapter_pages.to_vec();
        sorted_pages.sort_by(|a, b| {
            a.order_number
                .cmp(&b.order_number)
                .then_with(|| a.id.cmp(&b.id))
        });

        // Create normalized pages with sequential order numbers
        for (idx, &page) in sorted_pages.iter().enumerate() {
            let mut normalized_page = page.clone();
            normalized_page.order_number = (idx as i32) + 1; // Start at 1, zero is reserved for chapter front pages which cannot be moved and are not passed to this function
            normalized_pages.push(normalized_page);
        }
    }

    let mut tx = conn.begin().await?;

    // First, randomize ALL page order numbers to avoid conflicts (except chapter front pages, which cannot be moved)
    // This is necessary because unique indexes cannot be deferred in PostgreSQL
    // The random numbers are temporary and will be replaced with the correct order numbers
    sqlx::query!(
        "
UPDATE pages
SET order_number = floor(random() * (2000000 -200000 + 1) + 200000)
WHERE course_id = $1
  AND order_number != 0
  AND deleted_at IS NULL
        ",
        course_id
    )
    .execute(&mut *tx)
    .await?;

    // Now update each page to its corrected order_number, using the normalized pages
    for page in normalized_pages {
        if let Some(matching_db_page) = db_pages.iter().find(|p| p.id == page.id) {
            if matching_db_page.chapter_id == page.chapter_id {
                // Chapter not changing - just set the corrected order number
                sqlx::query!(
                    "UPDATE pages SET order_number = $2 WHERE id = $1",
                    page.id,
                    page.order_number
                )
                .execute(&mut *tx)
                .await?;
            } else {
                // Chapter changes - handle URL paths and redirections
                if let Some(old_chapter_id) = matching_db_page.chapter_id {
                    if let Some(new_chapter_id) = page.chapter_id {
                        // Moving page to another chapter
                        if let Some(old_chapter) = chapters.iter().find(|o| o.id == old_chapter_id)
                        {
                            if let Some(new_chapter) =
                                chapters.iter().find(|o| o.id == new_chapter_id)
                            {
                                let old_path = &page.url_path;
                                let new_path = old_path.replacen(
                                    &old_chapter.chapter_number.to_string(),
                                    &new_chapter.chapter_number.to_string(),
                                    1,
                                );
                                sqlx::query!(
                                    "UPDATE pages SET url_path = $2, chapter_id = $3, order_number = $4 WHERE pages.id = $1",
                                    page.id,
                                    new_path,
                                    new_chapter.id,
                                    page.order_number
                                )
                                .execute(&mut *tx)
                                .await?;
                                sqlx::query!(
                                    "INSERT INTO url_redirections(destination_page_id, old_url_path, course_id) VALUES ($1, $2, $3)",
                                    page.id,
                                    old_path,
                                    course_id
                                )
                                .execute(&mut *tx)
                                .await?;
                            } else {
                                return Err(model_err!(
                                    InvalidRequest,
                                    "New chapter not found".to_string()
                                ));
                            }
                        } else {
                            return Err(model_err!(
                                InvalidRequest,
                                "Old chapter not found".to_string()
                            ));
                        }
                    } else {
                        // Moving page from a chapter to a top level page
                        return Err(model_err!(
                            InvalidRequest,
                            "Making a chapter page a top level page is not supported yet"
                                .to_string()
                        ));
                    }
                } else {
                    error!(
                        "Cannot move a top level page to a chapter. matching_db_page.chapter_id: {:?} page.chapter_id: {:?}",
                        matching_db_page.chapter_id, page.chapter_id
                    );
                    // Moving page from the top level to a chapter
                    return Err(model_err!(
                        InvalidRequest,
                        "Moving a top level page to a chapter is not supported yet".to_string()
                    ));
                }
            }
        } else {
            return Err(model_err!(
                InvalidRequest,
                format!("Page {} does exist in course {}", page.id, course_id)
            ));
        }
    }
    tx.commit().await?;
    Ok(())
}

pub async fn reorder_chapters(
    conn: &mut PgConnection,
    chapters: &[Chapter],
    course_id: Uuid,
) -> ModelResult<()> {
    let db_chapters = course_chapters(conn, course_id).await?;
    let mut tx = conn.begin().await?;
    // Look for the modified chapter in the existing database

    // TODO USE CHAPTER ID FOR THE LOOP
    for chapter in chapters {
        if let Some(matching_db_chapter) = db_chapters.iter().find(|c| c.id == chapter.id)
            && let Some(old_chapter) = db_chapters.iter().find(|o| o.id == matching_db_chapter.id)
        {
            // to avoid conflicting chapter_number when chapter is modified
            //Assign random number to modified chapters
            sqlx::query!(
                "UPDATE chapters
                SET chapter_number = floor(random() * (20000000 - 2000000 + 1) + 200000)
                WHERE chapters.id = $1
                  AND chapters.course_id = $2
                  AND deleted_at IS NULL",
                matching_db_chapter.id,
                course_id
            )
            .execute(&mut *tx)
            .await?;

            // get newly modified chapter
            let chapter_with_randomized_chapter_number =
                get_chapter(&mut tx, matching_db_chapter.id).await?;
            let random_chapter_number = chapter_with_randomized_chapter_number.chapter_number;
            let pages =
                get_chapter_pages(&mut tx, chapter_with_randomized_chapter_number.id).await?;

            for page in pages {
                let old_path = &page.url_path;
                let new_path = old_path.replacen(
                    &old_chapter.chapter_number.to_string(),
                    &random_chapter_number.to_string(),
                    1,
                );

                // update each page path associated with a random chapter number
                sqlx::query!(
                    "UPDATE pages SET url_path = $2 WHERE pages.id = $1",
                    page.id,
                    new_path
                )
                .execute(&mut *tx)
                .await?;
            }
        }
    }

    for chapter in chapters {
        if let Some(matching_db_chapter) = db_chapters.iter().find(|c| c.id == chapter.id) {
            if let Some(new_chapter) = chapters.iter().find(|o| o.id == matching_db_chapter.id) {
                let new_chapter_number = &new_chapter.chapter_number;

                let randomized_chapter = get_chapter(&mut tx, chapter.id).await?;

                let randomized_chapter_number = randomized_chapter.chapter_number;

                // update chapter_number
                sqlx::query!(
                    "UPDATE chapters SET chapter_number = $2 WHERE chapters.id = $1",
                    chapter.id,
                    new_chapter_number
                )
                .execute(&mut *tx)
                .await?;

                // update all pages url in the modified chapter
                let pages = get_chapter_pages(&mut tx, chapter.id).await?;

                for page in pages {
                    let path_with_temp_random_number = &page.url_path;
                    let new_path = path_with_temp_random_number.replacen(
                        &randomized_chapter_number.to_string(),
                        &new_chapter_number.to_string(),
                        1,
                    );
                    let old_path = path_with_temp_random_number.replacen(
                        &randomized_chapter_number.to_string(),
                        &chapter.chapter_number.to_string(),
                        1,
                    );
                    // update each page path associated with the modified chapter
                    sqlx::query!(
                        "UPDATE pages SET url_path = $2 WHERE pages.id = $1",
                        page.id,
                        new_path
                    )
                    .execute(&mut *tx)
                    .await?;

                    crate::url_redirections::upsert(
                        &mut tx,
                        PKeyPolicy::Generate,
                        page.id,
                        &old_path,
                        course_id,
                    )
                    .await?;
                }
            } else {
                return Err(model_err!(
                    InvalidRequest,
                    "New chapter not found".to_string()
                ));
            }
        } else {
            return Err(model_err!(
                InvalidRequest,
                "Matching DB chapters not found".to_string()
            ));
        }
    }

    tx.commit().await?;
    Ok(())
}

pub async fn is_chapter_front_page(
    conn: &mut PgConnection,
    page_id: Uuid,
) -> ModelResult<IsChapterFrontPage> {
    let chapter = get_chapter_by_page_id(conn, page_id).await?;

    Ok(chapter.front_page_id.map_or(
        IsChapterFrontPage {
            is_chapter_front_page: false,
        },
        |id| IsChapterFrontPage {
            is_chapter_front_page: id == page_id,
        },
    ))
}

pub async fn update_page_details(
    conn: &mut PgConnection,
    page_id: Uuid,
    page_details_update: &PageDetailsUpdate,
) -> ModelResult<()> {
    let normalized_url_path = normalize_url_path_for_storage(&page_details_update.url_path);
    let mut tx = conn.begin().await?;
    let page_before_update = get_page(&mut tx, page_id).await?;
    sqlx::query!(
        "
UPDATE pages
SET title = $2,
  url_path = $3
WHERE id = $1
",
        page_id,
        page_details_update.title,
        normalized_url_path,
    )
    .execute(&mut *tx)
    .await?;

    if let Some(course_id) = page_before_update.course_id
        && page_before_update.url_path != normalized_url_path
    {
        // Some students might be trying to reach the page with the old url path, so let's redirect them to the new one
        crate::url_redirections::upsert(
            &mut tx,
            PKeyPolicy::Generate,
            page_id,
            &page_before_update.url_path,
            course_id,
        )
        .await?;
    }

    tx.commit().await?;
    Ok(())
}

pub async fn get_by_ids_and_visibility(
    conn: &mut PgConnection,
    ids: &[Uuid],
    page_visibility: PageVisibility,
) -> ModelResult<Vec<Page>> {
    let inverse_visibility_filter = page_visibility.get_inverse_visibility_filter();
    let pages = sqlx::query_as!(
        Page,
        "
SELECT id,
    created_at,
    updated_at,
    course_id,
    exam_id,
    chapter_id,
    url_path,
    title,
    deleted_at,
    content,
    order_number,
    copied_from,
    hidden,
    page_language_group_id
FROM pages
WHERE id = ANY($1)
    AND hidden IS DISTINCT FROM $2
    AND deleted_at IS NULL
    ",
        ids,
        inverse_visibility_filter
    )
    .fetch_all(conn)
    .await?;
    Ok(pages)
}

pub async fn get_by_ids_deleted_and_visibility(
    conn: &mut PgConnection,
    ids: &[Uuid],
    page_visibility: PageVisibility,
) -> ModelResult<Vec<Page>> {
    let inverse_visibility_filter = page_visibility.get_inverse_visibility_filter();
    let pages = sqlx::query_as!(
        Page,
        "
SELECT id,
    created_at,
    updated_at,
    course_id,
    exam_id,
    chapter_id,
    url_path,
    title,
    deleted_at,
    content,
    order_number,
    copied_from,
    hidden,
    page_language_group_id
FROM pages
WHERE id = ANY($1)
    AND hidden IS DISTINCT FROM $2
    AND deleted_at IS NOT NULL
    ",
        ids,
        inverse_visibility_filter
    )
    .fetch_all(conn)
    .await?;
    Ok(pages)
}

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

    use super::*;
    use crate::{exams::NewExam, test_helper::*};

    #[test]
    fn normalizes_decoded_page_paths_for_storage_and_lookup() {
        assert_eq!(
            normalize_url_path_for_storage("/foo bar#part"),
            "/foo%20bar%23part"
        );
        assert_eq!(
            normalize_url_path_for_storage("/foo%20bar%23part"),
            "/foo%20bar%23part"
        );
        assert_eq!(
            normalize_url_path_for_storage(" /literal%percent "),
            "/literal%25percent"
        );
        assert_eq!(
            normalize_url_path_for_storage(" /literal%25percent "),
            "/literal%25percent"
        );
    }

    #[tokio::test]
    async fn gets_organization_id() {
        insert_data!(:tx, :user, :org, :course, instance: _instance, :course_module, chapter: _chapter, :page);

        let course_page_org = get_organization_id(tx.as_mut(), page).await.unwrap();
        assert_eq!(org, course_page_org);

        let new_exam_id = crate::exams::insert(
            tx.as_mut(),
            PKeyPolicy::Generate,
            &NewExam {
                name: "name".to_string(),
                starts_at: None,
                ends_at: None,
                time_minutes: 120,
                organization_id: org,
                minimum_points_treshold: 24,
                grade_manually: false,
            },
        )
        .await
        .unwrap();
        let page = crate::pages::insert_page(
            tx.as_mut(),
            NewPage {
                exercises: vec![],
                exercise_slides: vec![],
                exercise_tasks: vec![],
                content: vec![],
                url_path: "url".to_string(),
                title: "title".to_string(),
                course_id: None,
                exam_id: Some(new_exam_id),
                chapter_id: None,
                front_page_of_chapter_id: None,
                content_search_language: None,
            },
            user,
            |_, _, _| unimplemented!(),
            |_| unimplemented!(),
        )
        .await
        .unwrap();
        let exam_page_org = get_organization_id(tx.as_mut(), page.id).await.unwrap();
        assert_eq!(org, exam_page_org);
    }

    #[tokio::test]
    async fn page_update_validation_works() {
        let e1 = CmsPageExercise {
            id: Uuid::parse_str("0c9dca80-5904-4d35-a945-8c080446f667").unwrap(),
            name: "".to_string(),
            order_number: 1,
            score_maximum: 1,
            max_tries_per_slide: None,
            limit_number_of_tries: false,
            deadline: Some(Utc.with_ymd_and_hms(2125, 1, 1, 23, 59, 59).unwrap()),
            needs_peer_review: false,
            needs_self_review: false,
            peer_or_self_review_config: None,
            peer_or_self_review_questions: None,
            use_course_default_peer_or_self_review_config: false,
            teacher_reviews_answer_after_locking: true,
        };
        let e1_s1 = CmsPageExerciseSlide {
            id: Uuid::parse_str("43380e81-6ff2-4f46-9f38-af0ac6a8421a").unwrap(),
            exercise_id: e1.id,
            order_number: 1,
        };
        let e1_s1_t1 = CmsPageExerciseTask {
            id: Uuid::parse_str("6fb19c22-bca0-42cf-8be5-4141e21cc7a9").unwrap(),
            exercise_slide_id: e1_s1.id,
            assignment: serde_json::json!([]),
            exercise_type: "exercise".to_string(),
            private_spec: None,
            order_number: 1,
        };

        // Works without exercises
        assert!(
            create_update(vec![], vec![], vec![])
                .validate_exercise_data()
                .is_ok()
        );

        // Works with single valid exercise
        assert!(
            create_update(
                vec![e1.clone()],
                vec![e1_s1.clone()],
                vec![e1_s1_t1.clone()],
            )
            .validate_exercise_data()
            .is_ok()
        );

        // Fails with missing slide
        assert!(
            create_update(vec![e1.clone()], vec![], vec![e1_s1_t1],)
                .validate_exercise_data()
                .is_err()
        );

        // Fails with missing task
        assert!(
            create_update(vec![e1], vec![e1_s1], vec![],)
                .validate_exercise_data()
                .is_err()
        );
    }

    fn create_update(
        exercises: Vec<CmsPageExercise>,
        exercise_slides: Vec<CmsPageExerciseSlide>,
        exercise_tasks: Vec<CmsPageExerciseTask>,
    ) -> CmsPageUpdate {
        CmsPageUpdate {
            content: vec![],
            exercises,
            exercise_slides,
            exercise_tasks,
            url_path: "".to_string(),
            title: "".to_string(),
            chapter_id: None,
        }
    }

    fn lock_chapter_test_block(hidden_text: &str) -> GutenbergBlock {
        GutenbergBlock::block_with_name_attributes_and_inner_blocks(
            "moocfi/lock-chapter",
            headless_lms_utils::attributes! {},
            vec![GutenbergBlock::paragraph(hidden_text)],
        )
    }

    fn first_lock_chapter_inner_block_count(page: &Page) -> usize {
        page.blocks_cloned().unwrap()[0].inner_blocks.len()
    }

    /// Updates chapter locking on a course for test setup.
    async fn set_course_chapter_locking_enabled(
        tx: &mut PgConnection,
        course_id: Uuid,
        chapter_locking_enabled: bool,
    ) {
        let course_before_update = courses::get_course(tx, course_id).await.unwrap();
        courses::update_course(
            tx,
            course_id,
            courses::CourseUpdate {
                chapter_locking_enabled,
                name: course_before_update.name,
                description: course_before_update.description,
                is_draft: course_before_update.is_draft,
                is_test_mode: course_before_update.is_test_mode,
                can_add_chatbot: course_before_update.can_add_chatbot,
                is_unlisted: course_before_update.is_unlisted,
                is_joinable_by_code_only: course_before_update.is_joinable_by_code_only,
                ask_marketing_consent: course_before_update.ask_marketing_consent,
                flagged_answers_threshold: course_before_update
                    .flagged_answers_threshold
                    .unwrap_or_default(),
                flagged_answers_skip_manual_review_and_allow_retry: course_before_update
                    .flagged_answers_skip_manual_review_and_allow_retry,
                closed_at: course_before_update.closed_at,
                closed_additional_message: course_before_update.closed_additional_message,
                closed_course_successor_id: course_before_update.closed_course_successor_id,
                ai_policy: course_before_update.ai_policy,
                course_material_ai_instructions: course_before_update
                    .course_material_ai_instructions,
            },
        )
        .await
        .unwrap();
    }

    #[test]
    fn sanitized_searchable_text_for_public_page_hides_lock_chapter_inner_blocks() {
        let content = serde_json::to_value(vec![
            GutenbergBlock::paragraph("Visible introduction"),
            lock_chapter_test_block("Model solution"),
        ])
        .unwrap();

        let searchable_text = sanitized_searchable_text_for_public_page(&content);

        assert!(searchable_text.contains("Visible introduction"));
        assert!(!searchable_text.contains("Model solution"));
    }

    #[tokio::test]
    async fn filter_course_material_pages_hides_lock_chapter_inner_blocks_until_reviews_complete() {
        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
        );
        set_course_chapter_locking_enabled(tx.as_mut(), course, true).await;

        update_page_content(
            tx.as_mut(),
            page_id,
            &serde_json::to_value(vec![lock_chapter_test_block("Model solution")]).unwrap(),
        )
        .await
        .unwrap();

        crate::exercises::update_teacher_reviews_answer_after_locking(
            tx.as_mut(),
            exercise_id,
            true,
        )
        .await
        .unwrap();
        crate::user_exercise_states::get_or_create_user_exercise_state(
            tx.as_mut(),
            user,
            exercise_id,
            Some(course),
            None,
        )
        .await
        .unwrap();
        crate::user_chapter_locking_statuses::complete_and_lock_chapter(
            tx.as_mut(),
            user,
            chapter_id,
            course,
        )
        .await
        .unwrap();
        crate::user_exercise_states::update_reviewing_stage(
            tx.as_mut(),
            user,
            CourseOrExamId::Course(course),
            exercise_id,
            crate::user_exercise_states::ReviewingStage::WaitingForManualGrading,
        )
        .await
        .unwrap();

        let page = get_page(tx.as_mut(), page_id).await.unwrap();
        let filtered_pages = filter_course_material_pages(tx.as_mut(), Some(user), vec![page])
            .await
            .unwrap();
        assert_eq!(first_lock_chapter_inner_block_count(&filtered_pages[0]), 0);

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

        let page = get_page(tx.as_mut(), page_id).await.unwrap();
        let filtered_pages = filter_course_material_pages(tx.as_mut(), Some(user), vec![page])
            .await
            .unwrap();
        assert_eq!(first_lock_chapter_inner_block_count(&filtered_pages[0]), 1);
    }

    #[tokio::test]
    async fn filter_course_material_pages_with_exercises_hides_lock_chapter_inner_blocks() {
        insert_data!(
            :tx,
            :user,
            :org,
            :course,
            instance: _instance,
            :course_module,
            chapter: _chapter_id,
            page: page_id
        );

        update_page_content(
            tx.as_mut(),
            page_id,
            &serde_json::to_value(vec![lock_chapter_test_block("Model solution")]).unwrap(),
        )
        .await
        .unwrap();

        let page = get_page(tx.as_mut(), page_id).await.unwrap();
        let filtered_pages = filter_course_material_pages_with_exercises(
            tx.as_mut(),
            None,
            vec![PageWithExercises {
                page,
                exercises: vec![],
            }],
        )
        .await
        .unwrap();

        assert_eq!(
            first_lock_chapter_inner_block_count(&filtered_pages[0].page),
            0
        );
    }

    #[tokio::test]
    async fn filter_course_material_pages_does_not_initialize_lock_status_when_course_locking_is_disabled()
     {
        insert_data!(
            :tx,
            :user,
            :org,
            :course,
            instance: _instance,
            :course_module,
            chapter: chapter_id,
            page: page_id
        );

        set_course_chapter_locking_enabled(tx.as_mut(), course, false).await;

        update_page_content(
            tx.as_mut(),
            page_id,
            &serde_json::to_value(vec![lock_chapter_test_block("Model solution")]).unwrap(),
        )
        .await
        .unwrap();

        let status = user_chapter_locking_statuses::get_or_init_status(
            tx.as_mut(),
            user,
            chapter_id,
            Some(course),
            Some(false),
        )
        .await
        .unwrap();

        let page = get_page(tx.as_mut(), page_id).await.unwrap();
        let filtered_pages = filter_course_material_pages(tx.as_mut(), Some(user), vec![page])
            .await
            .unwrap();

        assert_eq!(first_lock_chapter_inner_block_count(&filtered_pages[0]), 0);

        assert!(status.is_none());
    }

    #[tokio::test]
    async fn page_search_results_do_not_leak_hidden_lock_chapter_snippet_text() {
        insert_data!(
            :tx,
            :user,
            :org,
            :course,
            instance: _instance,
            :course_module,
            chapter: _chapter_id,
            page: page_id
        );

        update_page_content(
            tx.as_mut(),
            page_id,
            &serde_json::to_value(vec![
                GutenbergBlock::paragraph("Visible introduction"),
                lock_chapter_test_block("Model solution"),
            ])
            .unwrap(),
        )
        .await
        .unwrap();

        let results = get_page_search_results_for_words(
            tx.as_mut(),
            course,
            &SearchRequest {
                query: "Visible".to_string(),
            },
        )
        .await
        .unwrap();

        let result = results
            .iter()
            .find(|result| result.id == page_id)
            .expect("page should match visible content query");
        let content_headline = result.content_headline.as_deref().unwrap_or_default();

        assert!(!content_headline.contains("Model solution"));
    }

    #[tokio::test]
    async fn page_upsert_peer_reviews_work() {
        insert_data!(:tx, :user, :org, :course, instance: _instance, :course_module, chapter: _chapter, page: _page, exercise: exercise_id);
        let pr_id = Uuid::parse_str("9b69dc5e-0eca-4fcd-8fd2-031a3a65da82").unwrap();
        let prq_id = Uuid::parse_str("de18fa14-4ac6-4b57-b9f8-4843fa52d948").unwrap();
        let exercise = crate::exercises::get_by_id(tx.as_mut(), exercise_id)
            .await
            .unwrap();

        let pr1 = CmsPeerOrSelfReviewConfig {
            id:pr_id,
            exercise_id: Some(exercise_id),
            course_id: course,
            processing_strategy: crate::peer_or_self_review_configs::PeerReviewProcessingStrategy::AutomaticallyGradeOrManualReviewByAverage,
            accepting_threshold: 0.5,
            peer_reviews_to_give: 2,
            peer_reviews_to_receive: 1,
            points_are_all_or_nothing: false,
            reset_answer_if_zero_points_from_review: false,
            review_instructions: None,
        };
        let prq = CmsPeerOrSelfReviewQuestion {
            id: prq_id,
            peer_or_self_review_config_id: pr_id,
            answer_required: true,
            order_number: 0,
            question: "juu".to_string(),
            question_type:
                crate::peer_or_self_review_questions::PeerOrSelfReviewQuestionType::Essay,
            weight: 0.31,
        };
        let mut remapped_exercises = HashMap::new();
        remapped_exercises.insert(exercise_id, exercise);
        let pr_res = upsert_peer_or_self_review_configs(
            tx.as_mut(),
            &[],
            &[pr1],
            &remapped_exercises,
            false,
        )
        .await
        .unwrap();
        let prq_res =
            upsert_peer_or_self_review_questions(tx.as_mut(), &[], &[prq], &pr_res, false)
                .await
                .unwrap();

        assert!(pr_res.get(&pr_id).unwrap().accepting_threshold == 0.5);

        assert!(prq_res.get(&prq_id).unwrap().question == *"juu");
        assert!(!pr_res.get(&pr_id).unwrap().points_are_all_or_nothing);
        assert_eq!(prq_res.get(&prq_id).unwrap().weight, 0.31);
    }

    #[tokio::test]
    async fn page_upsert_peer_reviews_work_retain_ids() {
        insert_data!(:tx, :user, :org, :course, instance: _instance, :course_module, chapter: _chapter, page: _page, exercise: exercise_id);
        let exercise = crate::exercises::get_by_id(tx.as_mut(), exercise_id)
            .await
            .unwrap();
        let pr_id = Uuid::parse_str("9b69dc5e-0eca-4fcd-8fd2-031a3a65da82").unwrap();
        let prq_id = Uuid::parse_str("de18fa14-4ac6-4b57-b9f8-4843fa52d948").unwrap();
        let pr1 = CmsPeerOrSelfReviewConfig {
            id:pr_id,
            exercise_id: Some(exercise_id),
            course_id: course,
            processing_strategy: crate::peer_or_self_review_configs::PeerReviewProcessingStrategy::AutomaticallyGradeOrManualReviewByAverage,
            accepting_threshold: 0.5,
            peer_reviews_to_give: 2,
            peer_reviews_to_receive: 1,
            points_are_all_or_nothing: true,
            reset_answer_if_zero_points_from_review: false,
            review_instructions: None,
        };
        let prq = CmsPeerOrSelfReviewQuestion {
            id: prq_id,
            peer_or_self_review_config_id: pr_id,
            answer_required: true,
            order_number: 0,
            question: "juu".to_string(),
            question_type:
                crate::peer_or_self_review_questions::PeerOrSelfReviewQuestionType::Essay,
            weight: 0.0,
        };
        let mut remapped_exercises = HashMap::new();
        remapped_exercises.insert(exercise_id, exercise);
        let pr_res =
            upsert_peer_or_self_review_configs(tx.as_mut(), &[], &[pr1], &remapped_exercises, true)
                .await
                .unwrap();
        let prq_res = upsert_peer_or_self_review_questions(tx.as_mut(), &[], &[prq], &pr_res, true)
            .await
            .unwrap();

        assert!(pr_res.get(&pr_id).unwrap().accepting_threshold == 0.5);
        assert!(pr_res.get(&pr_id).unwrap().id == pr_id);

        assert!(prq_res.get(&prq_id).unwrap().id == prq_id);
        assert!(prq_res.get(&prq_id).unwrap().question == *"juu");
    }

    #[tokio::test]
    async fn page_upsert_peer_reviews_work_empty() {
        insert_data!(:tx, :user, :org, :course, instance: _instance, :course_module, chapter: _chapter, page: _page, exercise: exercise_id);
        let exercise = crate::exercises::get_by_id(tx.as_mut(), exercise_id)
            .await
            .unwrap();
        let mut remapped_exercises = HashMap::new();
        remapped_exercises.insert(exercise_id, exercise);
        let pr_res =
            upsert_peer_or_self_review_configs(tx.as_mut(), &[], &[], &remapped_exercises, true)
                .await
                .unwrap();
        let prq_res = upsert_peer_or_self_review_questions(tx.as_mut(), &[], &[], &pr_res, true)
            .await
            .unwrap();

        assert!(pr_res.is_empty());
        assert!(prq_res.is_empty());
    }

    #[tokio::test]
    async fn reorder_top_level_pages_works() {
        insert_data!(:tx, :user, :org, :course);

        // First, delete any existing pages in this course to ensure a clean slate
        let existing_pages =
            get_all_by_course_id_and_visibility(tx.as_mut(), course, PageVisibility::Any)
                .await
                .unwrap();
        for page in &existing_pages {
            delete_page_and_exercises(tx.as_mut(), page.id, user)
                .await
                .unwrap();
        }

        // Create our test pages
        let page1 = NewCoursePage::new(course, 0, "top-page-1", "Top Page 1");
        let (page1_id, _) = insert_course_page(tx.as_mut(), &page1, user).await.unwrap();
        let page2 = NewCoursePage::new(course, 1, "top-page-2", "Top Page 2");
        let (page2_id, _) = insert_course_page(tx.as_mut(), &page2, user).await.unwrap();
        let page3 = NewCoursePage::new(course, 2, "top-page-3", "Top Page 3");
        let (page3_id, _) = insert_course_page(tx.as_mut(), &page3, user).await.unwrap();

        let mut pages =
            get_all_by_course_id_and_visibility(tx.as_mut(), course, PageVisibility::Any)
                .await
                .unwrap();

        let page1_index = pages.iter().position(|p| p.id == page1_id).unwrap();
        let page2_index = pages.iter().position(|p| p.id == page2_id).unwrap();
        let page3_index = pages.iter().position(|p| p.id == page3_id).unwrap();

        pages[page1_index].order_number = 2;
        pages[page3_index].order_number = 1;
        pages[page2_index].order_number = 3;

        // Apply the reordering
        reorder_pages(tx.as_mut(), &pages, course).await.unwrap();

        // Check that the reordering took effect
        let page1_updated = get_page(tx.as_mut(), page1_id).await.unwrap();
        let page2_updated = get_page(tx.as_mut(), page2_id).await.unwrap();
        let page3_updated = get_page(tx.as_mut(), page3_id).await.unwrap();

        assert_eq!(page1_updated.order_number, 2);
        assert_eq!(page2_updated.order_number, 3);
        assert_eq!(page3_updated.order_number, 1);
    }
}