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, 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, get_chapter, get_chapter_by_page_id, get_course_chapters,
    },
    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 PageInfoSpecial {
    pub page_id: Uuid,
    pub url_path: String,
    pub page_title: String,
    pub order_number: i32,
    pub chapter_title: Option<String>,
    pub chapter_number: Option<i32>,
    pub module_name: Option<String>,
    pub module_number: Option<i32>,
    pub content: serde_json::Value,
}

impl PageInfoSpecial {
    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)]

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'}');

/// Decode each maximal run of `%XX` escapes as one UTF-8 unit, dropping a run that isn't valid
/// UTF-8 (rather than emitting U+FFFD). Non-escape characters pass through. Mirrors the migration's
/// `safe_percent_decode`, so storage and lookup agree on inputs like `/%FF`.
fn decode_percent_runs(input: &str) -> String {
    let chars: Vec<char> = input.chars().collect();
    let mut out = String::with_capacity(input.len());
    let mut i = 0;
    while i < chars.len() {
        if percent_escape_byte_at(&chars, i).is_some() {
            let mut bytes = Vec::new();
            while let Some(byte) = percent_escape_byte_at(&chars, i) {
                bytes.push(byte);
                i += 3;
            }
            if let Ok(decoded) = std::str::from_utf8(&bytes) {
                out.push_str(decoded);
            }
        } else {
            out.push(chars[i]);
            i += 1;
        }
    }
    out
}

/// The byte a `%XX` escape at `chars[i]` decodes to, or `None` if there isn't one there.
fn percent_escape_byte_at(chars: &[char], i: usize) -> Option<u8> {
    if chars.get(i) != Some(&'%') {
        return None;
    }
    let hi = chars.get(i + 1)?.to_digit(16)?;
    let lo = chars.get(i + 2)?.to_digit(16)?;
    Some((hi * 16 + lo) as u8)
}

/// Canonical storage/lookup form: decode `%xx`, then per `/`-segment turn whitespace into `-`,
/// strip the unsafe ASCII in `URL_PATH_ENCODE_SET`, and collapse/trim dashes. Case, non-ASCII and
/// `/`, `-`, `.`, `_`, `~` are kept. Unsafe chars are removed, not `%xx`-encoded, so every save
/// funnels through here and paths can't re-accumulate escapes. Mirrors `clean_url_path` (page
/// migration) and the frontend `cleanUrlPath`; keep the three in agreement.
pub(crate) fn normalize_url_path_for_storage(url_path: &str) -> String {
    decode_percent_runs(url_path)
        .split('/')
        .map(clean_url_path_segment)
        .collect::<Vec<_>>()
        .join("/")
}

/// Whether `ch` is an ASCII char that `URL_PATH_ENCODE_SET` would encode. The set's membership
/// test is crate-private, so probe it via `utf8_percent_encode` (which encodes an ASCII char iff
/// it's in the set). ASCII-only: the caller keeps non-ASCII verbatim.
fn is_unsafe_ascii(ch: char) -> bool {
    let mut buf = [0u8; 4];
    let encoded = ch.encode_utf8(&mut buf);
    utf8_percent_encode(encoded, URL_PATH_ENCODE_SET).next() != Some(encoded)
}

/// Cleans a single path segment (no `/`): whitespace runs become a single `-`, unsafe ASCII is
/// dropped, runs of `-` collapse to one, and leading/trailing `-` are trimmed.
fn clean_url_path_segment(segment: &str) -> String {
    let mut out = String::with_capacity(segment.len());
    for ch in segment.chars() {
        // Whitespace becomes a dash; unsafe ASCII is dropped; everything else is kept.
        let mapped = if ch.is_whitespace() {
            Some('-')
        } else if ch.is_ascii() && is_unsafe_ascii(ch) {
            None
        } else {
            Some(ch)
        };
        match mapped {
            // Collapse a run of dashes, whether from whitespace or a literal '-'.
            Some('-') if out.ends_with('-') => {}
            Some(c) => out.push(c),
            None => {}
        }
    }
    out.trim_matches('-').to_string()
}

/// Legacy lookup candidate: unsafe ASCII percent-encoded, non-ASCII kept decoded — the form used
/// before paths were stored stripped. Lets pages written that way (notably exam pages, which the
/// cleanup migration skips) still resolve.
fn legacy_partially_encoded_url_path(url_path: &str) -> String {
    let decoded = decode_percent_runs(url_path);
    let trimmed = decoded.trim();
    let mut normalized = String::with_capacity(trimmed.len());
    let mut buf = [0u8; 4];
    for ch in trimmed.chars() {
        if ch.is_ascii() {
            // For an ASCII char, utf8_percent_encode encodes it iff it is in the set.
            normalized.extend(utf8_percent_encode(
                ch.encode_utf8(&mut buf),
                URL_PATH_ENCODE_SET,
            ));
        } else {
            normalized.push(ch);
        }
    }
    normalized
}

/// Legacy lookup candidate: the oldest form, with non-ASCII percent-encoded too. Lets pages stored
/// that way still resolve.
fn legacy_fully_encoded_url_path(url_path: &str) -> String {
    let decoded = decode_percent_runs(url_path);
    utf8_percent_encode(decoded.trim(), URL_PATH_ENCODE_SET).to_string()
}

/// Stored `url_path` forms to try for a requested path: the strip-canonical form plus the two
/// legacy encoded forms, deduped (an ASCII-only safe path yields a single candidate).
fn url_path_lookup_candidates(url_path: &str) -> Vec<String> {
    let mut candidates = vec![normalize_url_path_for_storage(url_path)];
    for form in [
        legacy_partially_encoded_url_path(url_path),
        legacy_fully_encoded_url_path(url_path),
    ] {
        if !candidates.contains(&form) {
            candidates.push(form);
        }
    }
    candidates
}

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>,
    pub hidden: bool,
}

/// 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 (
    content,
    url_path,
    title,
    exam_id,
    content_search_language,
    hidden,
    order_number
  )
VALUES ($1, $2, $3, $4, $5, $6, $7)
RETURNING id
",
        serde_json::to_value(page.content.clone())?,
        page.url_path,
        page.title,
        exam_id,
        page.content_search_language
            .unwrap_or_else(|| "simple".to_string()),
        page.hidden,
        0_i32
    )
    .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::to_value(page.content)?,
            exercises: page.exercises,
            exercise_slides: page.exercise_slides,
            exercise_tasks: page.exercise_tasks,
            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)
}

/// Titles for a set of pages, for annotating rows that only carry a page id.
pub async fn get_titles_by_ids(
    conn: &mut PgConnection,
    page_ids: &[Uuid],
) -> ModelResult<HashMap<Uuid, String>> {
    let rows = sqlx::query!(
        "
SELECT id,
  title
FROM pages
WHERE id = ANY($1)
  AND deleted_at IS NULL
        ",
        page_ids,
    )
    .fetch_all(conn)
    .await?;
    Ok(rows.into_iter().map(|r| (r.id, r.title)).collect())
}

pub async fn get_page(conn: &mut PgConnection, page_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
WHERE id = $1;
",
        page_id
    )
    .fetch_one(conn)
    .await?;
    Ok(page)
}

/// The fields the chatbot needs to describe a page: enough to build the page-context message,
/// none of the Gutenberg `content` JSONB.
pub struct PageChatbotContext {
    pub id: Uuid,
    pub title: String,
    pub chapter_name: Option<String>,
    pub course_id: Option<Uuid>,
    pub hidden: bool,
    pub deleted_at: Option<DateTime<Utc>>,
}

/// Like [get_page], but projects only the columns the chatbot needs and folds in the chapter name,
/// so the caller does not also have to fetch the whole page (including `content`) or the chapter.
///
/// `chapter_name` is `None` when the page has no chapter or when its chapter has been deleted, the
/// same as looking up the chapter separately and discarding a `NotFound` error would give.
pub async fn get_page_chatbot_context(
    conn: &mut PgConnection,
    page_id: Uuid,
) -> ModelResult<PageChatbotContext> {
    let page = sqlx::query_as!(
        PageChatbotContext,
        r#"
SELECT pages.id AS "id!",
  pages.title AS "title!",
  chapters.name AS "chapter_name?",
  pages.course_id,
  pages.hidden AS "hidden!",
  pages.deleted_at
FROM pages
LEFT JOIN chapters ON chapters.id = pages.chapter_id AND chapters.deleted_at IS NULL
WHERE pages.id = $1;
"#,
        page_id
    )
    .fetch_one(conn)
    .await?;
    Ok(page)
}

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)
}

pub async fn get_page_info_special_for_course(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> ModelResult<Vec<PageInfoSpecial>> {
    let inverse_visibility_filter = PageVisibility::Public.get_inverse_visibility_filter();
    // if chapter_id is null then chapter_number and title should be null
    let res = sqlx::query_as!(
        PageInfoSpecial,
        r#"
    SELECT
        p.id AS "page_id!",
        p.url_path AS "url_path!",
        p.title AS "page_title!",
        p.order_number AS "order_number!",
        p.content AS "content!",
        c.name AS "chapter_title?",
        c.chapter_number AS "chapter_number?",
        m.name AS "module_name?",
        m.order_number AS "module_number?"
    FROM pages p
    LEFT JOIN chapters c
        ON c.id = p.chapter_id
    LEFT JOIN course_modules m
        ON m.id = c.course_module_id
    WHERE p.course_id = $1
    AND p.deleted_at IS NULL
    AND c.deleted_at IS NULL
    AND m.deleted_at IS NULL
    AND p.hidden IS DISTINCT FROM $2
        "#,
        course_id,
        inverse_visibility_filter,
    )
    .fetch_all(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)
}

/// Looks up a non-deleted page in `course_id` whose stored `url_path` matches `url_path` in
/// either the decoded-canonical or the legacy fully-encoded form (see
/// [`url_path_lookup_candidates`]). Shared by the page lookup and its tests so both exercise
/// the same candidate-matching logic.
async fn find_page_by_requested_path(
    conn: &mut PgConnection,
    course_id: Uuid,
    url_path: &str,
) -> ModelResult<Option<Page>> {
    for candidate in url_path_lookup_candidates(url_path) {
        if let Some(page) = get_page_by_stored_path(conn, course_id, &candidate).await? {
            return Ok(Some(page));
        }
    }
    Ok(None)
}

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> {
    if let Some(page) = find_page_by_requested_path(conn, course_data.id, url_path).await? {
        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;
    }

    if let Some(redirected_page) =
        try_to_find_redirected_page(conn, course_data.id, url_path).await?
    {
        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>> {
    for candidate in url_path_lookup_candidates(url_path) {
        if let Some(page) =
            try_to_find_redirected_page_by_stored_path(conn, course_id, &candidate).await?
        {
            return Ok(Some(page));
        }
    }
    Ok(None)
}

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 tasks = crate::exercise_tasks::get_exercise_tasks_by_exercise_slide_ids(
        &mut *conn,
        &exercise_slides.iter().map(|x| x.id).collect::<Vec<Uuid>>(),
    )
    .await?;
    let mut spec_files_by_task = crate::exercise_task_spec_files::get_by_exercise_task_ids(
        &mut *conn,
        &tasks.iter().map(|task| task.id).collect::<Vec<Uuid>>(),
        crate::exercise_task_spec_files::SpecKind::Private,
    )
    .await?;
    let exercise_tasks: Vec<CmsPageExerciseTask> = tasks
        .into_iter()
        .map(|task| {
            let private_spec_files = spec_files_by_task.remove(&task.id).unwrap_or_default();
            CmsPageExerciseTask::from_task(task, private_spec_files)
        })
        .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,
    /// The stored files this task's private spec references, as the exercise service declared them
    /// in the editor. The host cannot read the spec, so this list is the only thing keeping the
    /// files from being reclaimed as abandoned uploads — and the CMS round-trips it through a block
    /// attribute, so a load that leaves it empty makes the next save drop the references.
    ///
    /// Only the private spec's. Each derived spec declares its own files in the response of the
    /// endpoint that produced it, since a derivation may upload files of its own.
    #[serde(default)]
    pub private_spec_files: Vec<Uuid>,
}

impl CmsPageExerciseTask {
    /// Deliberately not a `From<ExerciseTask>`: the declared spec files live in their own table, so
    /// a conversion that could not reach them would quietly produce an empty list and the next save
    /// would drop every reference.
    pub fn from_task(task: ExerciseTask, private_spec_files: Vec<Uuid>) -> 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,
            private_spec_files,
        }
    }
}

#[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>,
    pub hidden: bool,
}

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(())
    }

    /// Detects exercises that share the same id within this single page and gives the duplicate
    /// occurrences fresh ids so they become independent exercises (#148).
    ///
    /// Duplicating an exercise block in the CMS (Gutenberg) keeps the original block's id (and the
    /// ids of its nested slides/tasks), so a saved page can contain several exercises that reference
    /// the same backend exercise. Without this, the upsert collapses them into a single row through
    /// `ON CONFLICT (id) DO UPDATE` and editing one secretly edits the others.
    ///
    /// The same id legitimately appearing once per page is how exercises move between pages, so only
    /// repeats *within the same page* are remapped. The duplicate's whole subtree (slides, tasks and
    /// any custom peer-review config/questions) is remapped consistently. Occurrences are matched in
    /// document order, which `normalizeDocument` guarantees is identical across the content and the
    /// flat exercise/slide/task lists.
    ///
    /// Returns a map from each regenerated duplicate's new exercise id to its original id, so callers
    /// can recover data still keyed by the original id (e.g. custom peer-review config/questions).
    pub fn regenerate_duplicate_exercise_ids(&mut self) -> HashMap<Uuid, Uuid> {
        // For every original exercise id, the new id to use for each occurrence (index 0 is the
        // first occurrence and keeps the original id).
        let mut exercise_ids_by_occurrence: HashMap<Uuid, Vec<Uuid>> = HashMap::new();
        // Maps each regenerated duplicate's new id back to its original exercise id.
        let mut original_exercise_id_by_new_id: HashMap<Uuid, Uuid> = HashMap::new();
        let mut found_duplicate = false;
        for exercise in self.exercises.iter_mut() {
            let original_id = exercise.id;
            let occurrences = exercise_ids_by_occurrence.entry(original_id).or_default();
            let new_id = if occurrences.is_empty() {
                original_id
            } else {
                found_duplicate = true;
                Uuid::new_v4()
            };
            occurrences.push(new_id);

            if new_id != original_id {
                exercise.id = new_id;
                original_exercise_id_by_new_id.insert(new_id, original_id);
                // Remap the duplicate's inline custom peer-review config/questions so they don't
                // collapse onto the original exercise's config.
                if let Some(config) = exercise.peer_or_self_review_config.as_mut() {
                    let questions = exercise
                        .peer_or_self_review_questions
                        .as_deref_mut()
                        .unwrap_or(&mut []);
                    fork_peer_or_self_review_config(config, questions, new_id);
                }
            }
        }

        if !found_duplicate {
            return original_exercise_id_by_new_id;
        }

        // Remap slides. The m-th occurrence of a slide id belongs to the m-th occurrence of its
        // exercise, because each duplicated exercise carries an identical copy of its slides.
        let mut slide_ids_by_occurrence: HashMap<Uuid, Vec<Uuid>> = HashMap::new();
        for slide in self.exercise_slides.iter_mut() {
            let original_slide_id = slide.id;
            let occurrence = slide_ids_by_occurrence
                .get(&original_slide_id)
                .map_or(0, |occurrences| occurrences.len());

            let new_exercise_id = exercise_ids_by_occurrence
                .get(&slide.exercise_id)
                .and_then(|occurrences| occurrences.get(occurrence))
                .copied()
                .unwrap_or(slide.exercise_id);
            let new_slide_id = if occurrence == 0 {
                original_slide_id
            } else {
                Uuid::new_v4()
            };
            slide_ids_by_occurrence
                .entry(original_slide_id)
                .or_default()
                .push(new_slide_id);
            slide.id = new_slide_id;
            slide.exercise_id = new_exercise_id;
        }

        // Remap tasks the same way against the slide occurrences.
        let mut task_ids_by_occurrence: HashMap<Uuid, usize> = HashMap::new();
        for task in self.exercise_tasks.iter_mut() {
            let original_task_id = task.id;
            let occurrence = task_ids_by_occurrence.entry(original_task_id).or_insert(0);

            let new_slide_id = slide_ids_by_occurrence
                .get(&task.exercise_slide_id)
                .and_then(|occurrences| occurrences.get(*occurrence))
                .copied()
                .unwrap_or(task.exercise_slide_id);
            if *occurrence > 0 {
                task.id = Uuid::new_v4();
            }
            task.exercise_slide_id = new_slide_id;
            *occurrence += 1;
        }

        // Finally rewrite the exercise ids embedded in the page content so the duplicated block
        // points at its newly created exercise. Exercise blocks only exist at the top level.
        let mut content_occurrence: HashMap<Uuid, usize> = HashMap::new();
        for block in self.content.iter_mut() {
            if block.name != "moocfi/exercise" {
                continue;
            }
            let Some(id) = block
                .attributes
                .get("id")
                .and_then(|value| value.as_str())
                .and_then(|value| Uuid::parse_str(value).ok())
            else {
                continue;
            };
            let occurrence = content_occurrence.entry(id).or_insert(0);
            if let Some(new_id) = exercise_ids_by_occurrence
                .get(&id)
                .and_then(|occurrences| occurrences.get(*occurrence))
                && *new_id != id
            {
                block
                    .attributes
                    .insert("id".to_string(), Value::String(new_id.to_string()));
            }
            *occurrence += 1;
        }

        original_exercise_id_by_new_id
    }
}

/// Gives `questions` fresh ids pointing at `target_config_id` when it differs from `source_config_id`
/// (i.e. they are being cloned onto a different config); otherwise returns them untouched so the
/// upsert reuses the existing rows.
fn rekey_peer_or_self_review_questions(
    questions: Vec<CmsPeerOrSelfReviewQuestion>,
    source_config_id: Uuid,
    target_config_id: Uuid,
) -> Vec<CmsPeerOrSelfReviewQuestion> {
    if source_config_id == target_config_id {
        return questions;
    }
    questions
        .into_iter()
        .map(|mut question| {
            question.id = Uuid::new_v4();
            question.peer_or_self_review_config_id = target_config_id;
            question
        })
        .collect()
}

/// Re-keys a custom peer-review config and its questions onto `new_exercise_id` with a fresh config id
/// and fresh question ids, producing an independent copy for a within-page exercise duplicate so it
/// does not collapse onto the original exercise's config (#148). All `questions` must belong to
/// `config`.
fn fork_peer_or_self_review_config(
    config: &mut CmsPeerOrSelfReviewConfig,
    questions: &mut [CmsPeerOrSelfReviewQuestion],
    new_exercise_id: Uuid,
) {
    let new_config_id = Uuid::new_v4();
    config.id = new_config_id;
    config.exercise_id = Some(new_exercise_id);
    for question in questions.iter_mut() {
        question.id = Uuid::new_v4();
        question.peer_or_self_review_config_id = new_config_id;
    }
}

#[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;
    // Give a fresh id to any exercise duplicated within this page so duplicates don't collapse into
    // a single backend exercise (#148). Must run before validation and the upsert below.
    let original_exercise_id_by_new_id = cms_page_update.regenerate_duplicate_exercise_ids();
    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,
  hidden = $6
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,
        cms_page_update.hidden
    )
    .fetch_one(&mut *tx)
    .await?;

    // Fetch the existing custom peer-review configs/questions before deleting exercises below. Both
    // queries join `exercises` with `deleted_at IS NULL`, so they must run before
    // `delete_exercises_by_page_id` or they would return nothing. They are used as a fallback to
    // preserve custom peer-review settings for exercises whose payload does not explicitly include
    // them (e.g. an inconsistent editor state) (#129).
    let existing_peer_or_self_review_configs_by_exercise_id =
        crate::peer_or_self_review_configs::get_peer_reviews_by_page_id(&mut tx, page.id)
            .await?
            .into_iter()
            .filter_map(|pr| pr.exercise_id.map(|id| (id, pr)))
            .collect::<HashMap<Uuid, CmsPeerOrSelfReviewConfig>>();
    let existing_peer_or_self_review_questions_by_config_id =
        crate::peer_or_self_review_questions::get_by_page_id(&mut tx, page.id)
            .await?
            .into_iter()
            .into_group_map_by(|prq| prq.peer_or_self_review_config_id);

    // 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?;

    // Configs present in this save's payload, keyed by exercise id. Lets a regenerated within-page
    // duplicate that omits its own config recover its original's *current* config — preferring the
    // edit the user just made over the pre-save DB snapshot (#148).
    let payload_peer_or_self_review_by_exercise_id: HashMap<
        Uuid,
        (
            CmsPeerOrSelfReviewConfig,
            Option<Vec<CmsPeerOrSelfReviewQuestion>>,
        ),
    > = cms_page_update
        .exercises
        .iter()
        .filter(|e| !e.use_course_default_peer_or_self_review_config)
        .filter_map(|e| {
            e.peer_or_self_review_config.as_ref().map(|config| {
                (
                    e.id,
                    (config.clone(), e.peer_or_self_review_questions.clone()),
                )
            })
        })
        .collect();

    // Recovers the custom config + questions stored/sent for `exercise_id`, preferring this save's
    // payload over the pre-save DB snapshot and resolving omitted questions from the snapshot.
    let recover_peer_or_self_review = |exercise_id: Uuid| -> Option<(
        CmsPeerOrSelfReviewConfig,
        Vec<CmsPeerOrSelfReviewQuestion>,
    )> {
        if let Some((config, questions)) =
            payload_peer_or_self_review_by_exercise_id.get(&exercise_id)
        {
            let questions = match questions {
                Some(questions) => questions.clone(),
                None => existing_peer_or_self_review_questions_by_config_id
                    .get(&config.id)
                    .cloned()
                    .unwrap_or_default(),
            };
            return Some((config.clone(), questions));
        }
        let config = existing_peer_or_self_review_configs_by_exercise_id
            .get(&exercise_id)?
            .clone();
        let questions = existing_peer_or_self_review_questions_by_config_id
            .get(&config.id)
            .cloned()
            .unwrap_or_default();
        Some((config, questions))
    };

    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)
        .filter_map(|e| {
            let exercise_id = e.id;
            // For a within-page duplicate that was regenerated a fresh id (#148), its settings still
            // live under the original exercise/config id; otherwise this is the exercise's own id.
            let original_exercise_id = original_exercise_id_by_new_id.get(&exercise_id).copied();
            // Prefer the config/questions sent in the payload. If the payload omits them (e.g. an
            // inconsistent editor state where a custom exercise is saved without its config or
            // questions), fall back to the existing custom settings so they are not lost (#129). An
            // exercise switching to the course default is filtered out above, so its config is
            // correctly left deleted.
            match e.peer_or_self_review_config {
                Some(config) => {
                    // The config is present, but the questions may be omitted. `None` means "keep the
                    // existing questions"; `Some(vec![])` is an explicit "remove all questions". The
                    // existing questions live under the source config id — this exercise's own for a
                    // normal save, or the original's for a regenerated duplicate whose config id was
                    // freshly minted, so they are re-keyed onto the new config id.
                    let questions = match e.peer_or_self_review_questions {
                        Some(questions) => questions,
                        None => {
                            recover_peer_or_self_review(original_exercise_id.unwrap_or(exercise_id))
                                .map(|(source_config, source_questions)| {
                                    rekey_peer_or_self_review_questions(
                                        source_questions,
                                        source_config.id,
                                        config.id,
                                    )
                                })
                                .unwrap_or_default()
                        }
                    };
                    Some((config, questions))
                }
                None => {
                    // The whole config is omitted. Recover this exercise's own stored config (#129);
                    // if it is a regenerated duplicate with nothing of its own, recover the original's
                    // current config and fork it onto this duplicate with fresh ids (#148).
                    if let Some((config, questions)) = recover_peer_or_self_review(exercise_id) {
                        return Some((config, questions));
                    }
                    let (mut config, mut questions) =
                        original_exercise_id.and_then(&recover_peer_or_self_review)?;
                    fork_peer_or_self_review_config(&mut config, &mut questions, exercise_id);
                    Some((config, 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 declares_spec_files = exercise_service_hashmap
            .get(&task_update.exercise_type)
            .is_some_and(|(_service, info)| info.declares_spec_files);
        let model_solution = 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,
            declares_spec_files,
        )
        .await?;
        let public = 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,
            declares_spec_files,
        )
        .await?;
        let model_solution_spec = model_solution.spec;
        let public_spec = public.spec;
        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 upserted = sqlx::query!(
            "
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?;
        // The exercise service declares these; the host cannot read them out of the specs it just
        // stored. Rewritten in full per kind, so dropping a file from a spec releases it.
        crate::exercise_task_spec_files::replace_for_exercise_task(
            &mut *conn,
            upserted.id,
            crate::exercise_task_spec_files::SpecKind::Private,
            &task_update.private_spec_files,
        )
        .await?;
        // Only when this save actually re-derived and the service declared: a skipped derivation
        // leaves the previous spec in place, so its declarations must stay in place too.
        if let Some(declared) = public.declared_files.as_ref() {
            crate::exercise_task_spec_files::replace_for_exercise_task(
                &mut *conn,
                upserted.id,
                crate::exercise_task_spec_files::SpecKind::Public,
                declared,
            )
            .await?;
        }
        if let Some(declared) = model_solution.declared_files.as_ref() {
            crate::exercise_task_spec_files::replace_for_exercise_task(
                &mut *conn,
                upserted.id,
                crate::exercise_task_spec_files::SpecKind::ModelSolution,
                declared,
            )
            .await?;
        }
        remapped_exercise_tasks.push(CmsPageExerciseTask {
            id: upserted.id,
            exercise_slide_id: upserted.exercise_slide_id,
            assignment: upserted.assignment,
            exercise_type: upserted.exercise_type,
            private_spec: upserted.private_spec,
            order_number: upserted.order_number,
            private_spec_files: task_update.private_spec_files.clone(),
        })
    }
    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>,
}

/// A derived spec, with the stored files the exercise service declared it references.
#[derive(Debug)]
struct DerivedSpec {
    spec: Option<serde_json::Value>,
    /// `None` when this save declared nothing about the spec: either it was not re-derived, so the
    /// stored declarations still stand, or the service does not declare its spec files at all.
    /// Distinct from `Some(vec![])`, which says the spec references no files and releases whatever
    /// it referenced before.
    declared_files: Option<Vec<Uuid>>,
}

/// The body a service that declares its spec files returns from the public-spec and
/// model-solution endpoints, in place of the bare spec.
///
/// Both keys are required on purpose: serde reads a missing field of either an `Option` or a
/// `#[serde(default)]` type without complaint, so a lenient envelope would swallow a bare spec
/// object as "no spec, no files" and store `NULL` over a working exercise.
#[derive(Deserialize)]
struct DerivedSpecResponse {
    /// `null` for a kind the service has no spec for, such as a model solution it cannot show.
    spec: serde_json::Value,
    files: Vec<Uuid>,
}

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,
    declares_spec_files: bool,
) -> Result<DerivedSpec, ModelError> {
    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.
            Ok(DerivedSpec {
                spec: previous_spec,
                declared_files: None,
            })
        }
        _ => {
            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?;
            if !declares_spec_files {
                return Ok(DerivedSpec {
                    spec: Some(res),
                    declared_files: None,
                });
            }
            // A declaring service answers with an envelope, so the same body cannot be read both
            // ways: failing loudly beats storing an envelope as if it were the spec.
            let response: DerivedSpecResponse = serde_json::from_value(res).map_err(|err| {
                model_err!(
                    PreconditionFailedWithCMSAnchorBlockId {
                        id: cms_block_id,
                        description: "Exercise service declares spec files but did not answer with a spec and its files.",
                    },
                    format!(
                        "Exercise service {} declares spec files, so its derived spec must be a spec and its files: {err}",
                        task_update.exercise_type
                    )
                )
            })?;
            Ok(DerivedSpec {
                spec: match response.spec {
                    serde_json::Value::Null => None,
                    spec => Some(spec),
                },
                declared_files: Some(response.files),
            })
        }
    }
}

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,
        hidden: new_page.hidden,
    };
    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,
    hidden
  )
VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
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,
        new_page.hidden,
    )
    .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,
                hidden: new_page.hidden,
            },
            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#"
-- Nullability is stated per column rather than inferred: sqlx-cli 0.9.0 derives a reversed
-- nullable array for this query, which makes every offline build of it fail.
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 struct PageIdAndUrlPath {
    pub id: Uuid,
    pub url_path: String,
}

/// Like [get_chapter_pages], projected down to `id` and `url_path` for callers that only rewrite
/// paths and would otherwise pull every page's `content` JSONB for nothing.
pub async fn get_chapter_page_paths(
    conn: &mut PgConnection,
    chapter_id: Uuid,
) -> ModelResult<Vec<PageIdAndUrlPath>> {
    let pages = sqlx::query_as!(
        PageIdAndUrlPath,
        "
SELECT id,
  url_path
FROM pages
WHERE chapter_id = $1
  AND 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,
                hidden: page.hidden,
            },
            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 = get_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(())
}

/// Writes pages' url paths as given, without normalizing them or adding redirections.
///
/// [reorder_chapters] rewrites paths in two passes and creates the redirections itself, so neither
/// belongs here. Use [insert_page] or [update_page] for a path that comes from a user, since those
/// canonicalize it.
///
/// Does not filter on `deleted_at`: a deleted page's path is written too.
async fn set_url_paths(
    conn: &mut PgConnection,
    ids: &[Uuid],
    url_paths: &[String],
) -> ModelResult<()> {
    if ids.is_empty() {
        return Ok(());
    }
    sqlx::query!(
        "
UPDATE pages
SET url_path = v.path
FROM unnest($1::uuid[], $2::text[]) AS v(id, path)
WHERE pages.id = v.id
",
        ids,
        url_paths
    )
    .execute(conn)
    .await?;
    Ok(())
}

pub async fn reorder_chapters(
    conn: &mut PgConnection,
    chapters: &[Chapter],
    course_id: Uuid,
) -> ModelResult<()> {
    let db_chapters = get_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_page_paths(&mut tx, chapter_with_randomized_chapter_number.id).await?;

            let mut ids = Vec::with_capacity(pages.len());
            let mut new_paths = Vec::with_capacity(pages.len());
            for page in pages {
                let new_path = page.url_path.replacen(
                    &old_chapter.chapter_number.to_string(),
                    &random_chapter_number.to_string(),
                    1,
                );
                ids.push(page.id);
                new_paths.push(new_path);
            }
            set_url_paths(&mut tx, &ids, &new_paths).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_page_paths(&mut tx, chapter.id).await?;

                let mut ids = Vec::with_capacity(pages.len());
                let mut new_paths = Vec::with_capacity(pages.len());
                let mut old_paths = Vec::with_capacity(pages.len());
                for page in pages {
                    let new_path = page.url_path.replacen(
                        &randomized_chapter_number.to_string(),
                        &new_chapter_number.to_string(),
                        1,
                    );
                    let old_path = page.url_path.replacen(
                        &randomized_chapter_number.to_string(),
                        &chapter.chapter_number.to_string(),
                        1,
                    );
                    ids.push(page.id);
                    new_paths.push(new_path);
                    old_paths.push(old_path);
                }
                set_url_paths(&mut tx, &ids, &new_paths).await?;

                for (page_id, old_path) in ids.iter().zip(old_paths.iter()) {
                    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() {
        // Unsafe ASCII is stripped (not percent-encoded); whitespace becomes a dash.
        assert_eq!(
            normalize_url_path_for_storage("/foo bar#part"),
            "/foo-barpart"
        );
        assert_eq!(
            normalize_url_path_for_storage("/foo%20bar%23part"),
            "/foo-barpart"
        );
        // A literal (undecodable) percent is part of the unsafe set, so it is stripped too.
        assert_eq!(
            normalize_url_path_for_storage(" /literal%percent "),
            "/literalpercent"
        );
        assert_eq!(
            normalize_url_path_for_storage(" /literal%25percent "),
            "/literalpercent"
        );
    }

    #[test]
    fn keeps_non_ascii_characters_decoded_for_storage() {
        // Valid non-ASCII (Cyrillic) is kept verbatim, regardless of whether the input
        // arrives decoded or percent-encoded.
        assert_eq!(
            normalize_url_path_for_storage("/chapter-1/як"),
            "/chapter-1/як"
        );
        assert_eq!(
            normalize_url_path_for_storage("/chapter-1/%D1%8F%D0%BA"),
            "/chapter-1/як"
        );
        // Unsafe ASCII mixed with non-ASCII: the space becomes a dash, the non-ASCII is kept.
        assert_eq!(
            normalize_url_path_for_storage("/chapter 1/як"),
            "/chapter-1/як"
        );
    }

    #[test]
    fn drops_invalid_percent_escape_runs_like_the_migration() {
        // A `%xx` run that isn't valid UTF-8 is dropped whole (matching safe_percent_decode), not
        // turned into U+FFFD, so storage and lookup stay aligned.
        assert_eq!(normalize_url_path_for_storage("/%FF"), "/");
        assert_eq!(normalize_url_path_for_storage("/%41%FF"), "/");
    }

    #[test]
    fn legacy_fully_encoded_form_encodes_non_ascii() {
        assert_eq!(
            legacy_fully_encoded_url_path("/chapter-1/як"),
            "/chapter-1/%D1%8F%D0%BA"
        );
        assert_eq!(
            legacy_fully_encoded_url_path("/chapter-1/%D1%8F%D0%BA"),
            "/chapter-1/%D1%8F%D0%BA"
        );
    }

    #[test]
    fn legacy_partially_encoded_form_encodes_only_unsafe_ascii() {
        // Unsafe ASCII percent-encoded, non-ASCII kept decoded — the pre-strip storage form.
        assert_eq!(
            legacy_partially_encoded_url_path("/chapter 1/як"),
            "/chapter%201/як"
        );
    }

    #[test]
    fn lookup_candidates_cover_stored_forms() {
        // ASCII-only safe paths have a single candidate (all forms are identical).
        assert_eq!(
            url_path_lookup_candidates("/chapter-1/foo"),
            vec!["/chapter-1/foo".to_string()]
        );
        // Pure non-ASCII paths: strip-canonical (== partially-encoded) plus the fully-encoded
        // legacy form.
        assert_eq!(
            url_path_lookup_candidates("/chapter-1/як"),
            vec![
                "/chapter-1/як".to_string(),
                "/chapter-1/%D1%8F%D0%BA".to_string(),
            ]
        );
        // A path mixing unsafe ASCII and non-ASCII has three distinct stored forms to try.
        assert_eq!(
            url_path_lookup_candidates("/chapter 1/як"),
            vec![
                "/chapter-1/як".to_string(),
                "/chapter%201/як".to_string(),
                "/chapter%201/%D1%8F%D0%BA".to_string(),
            ]
        );
    }

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

        let new_page = |url_path: &str, title: &str| NewPage {
            exercises: vec![],
            exercise_slides: vec![],
            exercise_tasks: vec![],
            content: vec![],
            url_path: url_path.to_string(),
            title: title.to_string(),
            course_id: Some(course),
            exam_id: None,
            chapter_id: Some(chapter),
            front_page_of_chapter_id: None,
            content_search_language: None,
            hidden: false,
        };

        // Stored in the decoded-canonical form (raw Cyrillic), as insert_page now stores it.
        let raw_page = crate::pages::insert_page(
            tx.as_mut(),
            new_page("/chapter-1/як", "Raw"),
            user,
            |_, _, _| unimplemented!(),
            |_| unimplemented!(),
        )
        .await
        .unwrap();

        // Stored in the legacy fully-encoded form, simulating data written before the
        // decoded-canonical form was adopted (insert_page normalizes, so update directly).
        let legacy_page = crate::pages::insert_page(
            tx.as_mut(),
            new_page("/chapter-1/іспит", "Legacy"),
            user,
            |_, _, _| unimplemented!(),
            |_| unimplemented!(),
        )
        .await
        .unwrap();
        set_url_paths(
            tx.as_mut(),
            &[legacy_page.id],
            &["/chapter-1/%D1%96%D1%81%D0%BF%D0%B8%D1%82".to_string()],
        )
        .await
        .unwrap();

        // Looks the page up through the same helper the production lookup uses, so a
        // regression in the candidate matching is caught here.
        async fn lookup(
            conn: &mut sqlx::PgConnection,
            course_id: uuid::Uuid,
            requested: &str,
        ) -> Option<uuid::Uuid> {
            super::find_page_by_requested_path(conn, course_id, requested)
                .await
                .unwrap()
                .map(|page| page.id)
        }

        // The raw-Cyrillic page resolves whether the request arrives decoded or encoded.
        assert_eq!(
            lookup(tx.as_mut(), course, "/chapter-1/як").await,
            Some(raw_page.id)
        );
        assert_eq!(
            lookup(tx.as_mut(), course, "/chapter-1/%D1%8F%D0%BA").await,
            Some(raw_page.id)
        );

        // The legacy fully-encoded page resolves via the legacy lookup candidate, whether
        // the request arrives decoded or encoded.
        assert_eq!(
            lookup(tx.as_mut(), course, "/chapter-1/іспит").await,
            Some(legacy_page.id)
        );
        assert_eq!(
            lookup(
                tx.as_mut(),
                course,
                "/chapter-1/%D1%96%D1%81%D0%BF%D0%B8%D1%82"
            )
            .await,
            Some(legacy_page.id)
        );
    }

    #[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,
                hidden: false,
            },
            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,
            private_spec_files: vec![],
        };

        // 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,
            hidden: false,
        }
    }

    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 app_config = init_app_conf().expect("Application Configuration initialization failed");
        let course_before_update = courses::get_course(tx, course_id).await.unwrap();
        courses::update_course(
            tx,
            &app_config,
            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);
    }

    fn declaring_task() -> NormalizedCmsExerciseTask {
        NormalizedCmsExerciseTask {
            id: Uuid::new_v4(),
            assignment: serde_json::Value::Null,
            exercise_type: "declaring-service".to_string(),
            private_spec: Some(serde_json::json!({ "prompt": "hi" })),
        }
    }

    async fn derive_from_declaring_service(body: serde_json::Value) -> ModelResult<DerivedSpec> {
        let exercise_type = "declaring-service".to_string();
        let mut urls = HashMap::new();
        urls.insert(
            &exercise_type,
            Url::parse("http://example.com/spec").unwrap(),
        );
        fetch_derived_spec(
            None,
            &declaring_task(),
            &urls,
            move |_url, _slug, _spec| {
                let body = body.clone();
                Box::pin(async move { Ok(body) })
            },
            None,
            Uuid::new_v4(),
            true,
        )
        .await
    }

    #[tokio::test]
    async fn reads_the_spec_and_its_files_a_declaring_service_answers_with() {
        let file = Uuid::new_v4();
        let derived = derive_from_declaring_service(serde_json::json!({
            "spec": { "prompt": "hi" },
            "files": [file],
        }))
        .await
        .unwrap();

        assert_eq!(derived.spec, Some(serde_json::json!({ "prompt": "hi" })));
        assert_eq!(derived.declared_files, Some(vec![file]));
    }

    #[tokio::test]
    async fn reads_a_null_spec_a_declaring_service_has_nothing_to_show_for() {
        let derived = derive_from_declaring_service(serde_json::json!({
            "spec": null,
            "files": [],
        }))
        .await
        .unwrap();

        assert_eq!(derived.spec, None);
        assert_eq!(derived.declared_files, Some(vec![]));
    }

    #[tokio::test]
    async fn refuses_a_bare_spec_from_a_declaring_service() {
        // The dangerous shape: a lenient parse reads a bare spec as "no spec, no files".
        derive_from_declaring_service(serde_json::json!({ "prompt": "hi", "options": [] }))
            .await
            .expect_err("a bare spec is not an envelope");
        derive_from_declaring_service(serde_json::json!({ "files": [] }))
            .await
            .expect_err("an envelope without its spec key is not an envelope");
    }
}