headless_lms_models/
courses.rs

1use headless_lms_utils::{file_store::FileStore, language_tag_to_name::LANGUAGE_TAG_TO_NAME};
2use utoipa::ToSchema;
3
4use crate::{
5    chapters::{Chapter, course_chapters},
6    course_modules::CourseModule,
7    pages::Page,
8    pages::{PageVisibility, get_all_by_course_id_and_visibility},
9    prelude::*,
10};
11
12pub struct CourseInfo {
13    pub id: Uuid,
14    pub is_draft: bool,
15}
16
17#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, Eq, ToSchema)]
18
19pub struct CourseCount {
20    pub count: u32,
21}
22
23pub struct CourseContextData {
24    pub id: Uuid,
25    pub is_test_mode: bool,
26}
27
28#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
29
30pub struct Course {
31    pub id: Uuid,
32    pub slug: String,
33    pub created_at: DateTime<Utc>,
34    pub updated_at: DateTime<Utc>,
35    pub name: String,
36    pub description: Option<String>,
37    pub organization_id: Uuid,
38    pub deleted_at: Option<DateTime<Utc>>,
39    pub language_code: String,
40    pub copied_from: Option<Uuid>,
41    pub content_search_language: Option<String>,
42    pub course_language_group_id: Uuid,
43    pub is_draft: bool,
44    pub is_test_mode: bool,
45    pub is_unlisted: bool,
46    pub base_module_completion_requires_n_submodule_completions: i32,
47    pub can_add_chatbot: bool,
48    pub is_joinable_by_code_only: bool,
49    pub join_code: Option<String>,
50    pub ask_marketing_consent: bool,
51    pub flagged_answers_threshold: Option<i32>,
52    pub flagged_answers_skip_manual_review_and_allow_retry: bool,
53    pub closed_at: Option<DateTime<Utc>>,
54    pub closed_additional_message: Option<String>,
55    pub closed_course_successor_id: Option<Uuid>,
56    pub chapter_locking_enabled: bool,
57}
58
59/** A subset of the `Course` struct that contains the fields that are allowed to be shown to all students on the course materials. */
60#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
61
62pub struct CourseMaterialCourse {
63    pub id: Uuid,
64    pub slug: String,
65    pub name: String,
66    pub description: Option<String>,
67    pub organization_id: Uuid,
68    pub language_code: String,
69    pub copied_from: Option<Uuid>,
70    pub content_search_language: Option<String>,
71    pub course_language_group_id: Uuid,
72    pub is_draft: bool,
73    pub is_test_mode: bool,
74    pub is_unlisted: bool,
75    pub base_module_completion_requires_n_submodule_completions: i32,
76    pub is_joinable_by_code_only: bool,
77    pub ask_marketing_consent: bool,
78    pub closed_at: Option<DateTime<Utc>>,
79    pub closed_additional_message: Option<String>,
80    pub closed_course_successor_id: Option<Uuid>,
81    pub chapter_locking_enabled: bool,
82}
83
84impl From<Course> for CourseMaterialCourse {
85    fn from(course: Course) -> Self {
86        CourseMaterialCourse {
87            id: course.id,
88            slug: course.slug,
89            name: course.name,
90            description: course.description,
91            organization_id: course.organization_id,
92            language_code: course.language_code,
93            copied_from: course.copied_from,
94            content_search_language: course.content_search_language,
95            course_language_group_id: course.course_language_group_id,
96            is_draft: course.is_draft,
97            is_test_mode: course.is_test_mode,
98            is_unlisted: course.is_unlisted,
99            base_module_completion_requires_n_submodule_completions: course
100                .base_module_completion_requires_n_submodule_completions,
101            is_joinable_by_code_only: course.is_joinable_by_code_only,
102            ask_marketing_consent: course.ask_marketing_consent,
103            closed_at: course.closed_at,
104            closed_additional_message: course.closed_additional_message,
105            closed_course_successor_id: course.closed_course_successor_id,
106            chapter_locking_enabled: course.chapter_locking_enabled,
107        }
108    }
109}
110
111/** All the necessary info that can be used to switch the user's browser to a different language version of the course. */
112#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
113
114pub struct CourseLanguageVersionNavigationInfo {
115    pub course_language_group_id: Uuid,
116    pub course_id: Uuid,
117    pub language_code: String,
118    pub course_slug: String,
119    pub page_path: String,
120    pub is_draft: bool,
121    pub current_page_unavailable_in_this_language: bool,
122}
123
124impl CourseLanguageVersionNavigationInfo {
125    /// Creates a new `CourseLanguageVersionNavigationInfo` from a course and page language group navigation info.
126    pub fn from_course_and_page_info(
127        course: &Course,
128        page_info: Option<&crate::page_language_groups::PageLanguageGroupNavigationInfo>,
129    ) -> Self {
130        Self {
131            course_language_group_id: course.course_language_group_id,
132            course_id: course.id,
133            language_code: course.language_code.clone(),
134            course_slug: course.slug.clone(),
135            page_path: page_info
136                .map(|p| p.page_path.clone())
137                .unwrap_or_else(|| "/".to_string()),
138            is_draft: course.is_draft,
139            current_page_unavailable_in_this_language: page_info.is_none(),
140        }
141    }
142}
143
144#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
145
146pub struct CourseBreadcrumbInfo {
147    pub course_id: Uuid,
148    pub course_name: String,
149    pub course_slug: String,
150    pub organization_slug: String,
151    pub organization_name: String,
152}
153
154/// Represents the subset of page fields that are required to create a new course.
155#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
156
157pub struct NewCourse {
158    pub name: String,
159    pub slug: String,
160    pub organization_id: Uuid,
161    pub language_code: String,
162    /// Name of the teacher who is responsible for the course. Must be a valid name.
163    pub teacher_in_charge_name: String,
164    /// Email of the teacher who is responsible for the course. Must be a valid email.
165    pub teacher_in_charge_email: String,
166    pub description: String,
167    pub is_draft: bool,
168    pub is_test_mode: bool,
169    pub is_unlisted: bool,
170    /// If true, copies all user permissions from the original course to the new one.
171    pub copy_user_permissions: bool,
172    pub is_joinable_by_code_only: bool,
173    pub join_code: Option<String>,
174    pub ask_marketing_consent: bool,
175    pub flagged_answers_threshold: Option<i32>,
176    pub can_add_chatbot: bool,
177}
178
179pub async fn insert(
180    conn: &mut PgConnection,
181    pkey_policy: PKeyPolicy<Uuid>,
182    course_language_group_id: Uuid,
183    new_course: &NewCourse,
184) -> ModelResult<Uuid> {
185    let res = sqlx::query!(
186        "
187INSERT INTO courses(
188    id,
189    name,
190    description,
191    slug,
192    organization_id,
193    language_code,
194    course_language_group_id,
195    is_draft,
196    is_test_mode,
197    is_joinable_by_code_only,
198    join_code,
199    can_add_chatbot
200  )
201VALUES(
202    $1,
203    $2,
204    $3,
205    $4,
206    $5,
207    $6,
208    $7,
209    $8,
210    $9,
211    $10,
212    $11,
213    $12
214  )
215RETURNING id
216        ",
217        pkey_policy.into_uuid(),
218        new_course.name,
219        new_course.description,
220        new_course.slug,
221        new_course.organization_id,
222        new_course.language_code,
223        course_language_group_id,
224        new_course.is_draft,
225        new_course.is_test_mode,
226        new_course.is_joinable_by_code_only,
227        new_course.join_code,
228        new_course.can_add_chatbot,
229    )
230    .fetch_one(conn)
231    .await?;
232    Ok(res.id)
233}
234
235#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
236
237pub struct CourseStructure {
238    pub course: Course,
239    pub pages: Vec<Page>,
240    pub chapters: Vec<Chapter>,
241    pub modules: Vec<CourseModule>,
242}
243
244pub async fn all_courses(conn: &mut PgConnection) -> ModelResult<Vec<Course>> {
245    let courses = sqlx::query_as!(
246        Course,
247        r#"
248SELECT id,
249  name,
250  created_at,
251  updated_at,
252  organization_id,
253  deleted_at,
254  slug,
255  content_search_language::text,
256  language_code,
257  copied_from,
258  course_language_group_id,
259  description,
260  is_draft,
261  is_test_mode,
262  base_module_completion_requires_n_submodule_completions,
263  can_add_chatbot,
264  is_unlisted,
265  is_joinable_by_code_only,
266  join_code,
267  ask_marketing_consent,
268  flagged_answers_threshold,
269  flagged_answers_skip_manual_review_and_allow_retry,
270  closed_at,
271  closed_additional_message,
272  closed_course_successor_id,
273  chapter_locking_enabled
274FROM courses
275WHERE deleted_at IS NULL;
276"#
277    )
278    .fetch_all(conn)
279    .await?;
280    Ok(courses)
281}
282
283pub async fn all_courses_user_enrolled_to(
284    conn: &mut PgConnection,
285    user_id: Uuid,
286) -> ModelResult<Vec<Course>> {
287    let courses = sqlx::query_as!(
288        Course,
289        r#"
290SELECT id,
291  name,
292  created_at,
293  updated_at,
294  organization_id,
295  deleted_at,
296  slug,
297  content_search_language::text,
298  language_code,
299  copied_from,
300  course_language_group_id,
301  description,
302  is_draft,
303  is_test_mode,
304  is_unlisted,
305  base_module_completion_requires_n_submodule_completions,
306  can_add_chatbot,
307  is_joinable_by_code_only,
308  join_code,
309  ask_marketing_consent,
310  flagged_answers_threshold,
311  flagged_answers_skip_manual_review_and_allow_retry,
312  closed_at,
313  closed_additional_message,
314  closed_course_successor_id,
315  chapter_locking_enabled
316FROM courses
317WHERE courses.deleted_at IS NULL
318  AND id IN (
319    SELECT current_course_id
320    FROM user_course_settings
321    WHERE deleted_at IS NULL
322      AND user_id = $1
323  )
324"#,
325        user_id
326    )
327    .fetch_all(conn)
328    .await?;
329    Ok(courses)
330}
331
332pub async fn all_courses_with_roles_for_user(
333    conn: &mut PgConnection,
334    user_id: Uuid,
335) -> ModelResult<Vec<Course>> {
336    let courses = sqlx::query_as!(
337        Course,
338        r#"
339SELECT id,
340  name,
341  created_at,
342  updated_at,
343  organization_id,
344  deleted_at,
345  slug,
346  content_search_language::text,
347  language_code,
348  copied_from,
349  course_language_group_id,
350  description,
351  is_draft,
352  is_test_mode,
353  can_add_chatbot,
354  is_unlisted,
355  base_module_completion_requires_n_submodule_completions,
356  is_joinable_by_code_only,
357  join_code,
358  ask_marketing_consent,
359  flagged_answers_threshold,
360  flagged_answers_skip_manual_review_and_allow_retry,
361  closed_at,
362  closed_additional_message,
363  closed_course_successor_id,
364  chapter_locking_enabled
365FROM courses
366WHERE courses.deleted_at IS NULL
367  AND (
368    id IN (
369      SELECT course_id
370      FROM roles
371      WHERE deleted_at IS NULL
372        AND user_id = $1
373        AND course_id IS NOT NULL
374    )
375    OR (
376      id IN (
377        SELECT ci.course_id
378        FROM course_instances ci
379          JOIN ROLES r ON r.course_instance_id = ci.id
380        WHERE r.user_id = $1
381          AND r.deleted_at IS NULL
382          AND ci.deleted_at IS NULL
383      )
384    )
385  ) "#,
386        user_id
387    )
388    .fetch_all(conn)
389    .await?;
390    Ok(courses)
391}
392
393pub async fn get_all_language_versions_of_course(
394    conn: &mut PgConnection,
395    course: &Course,
396) -> ModelResult<Vec<Course>> {
397    let courses = sqlx::query_as!(
398        Course,
399        "
400SELECT id,
401  name,
402  created_at,
403  updated_at,
404  organization_id,
405  deleted_at,
406  slug,
407  content_search_language::text,
408  language_code,
409  copied_from,
410  course_language_group_id,
411  description,
412  is_draft,
413  is_test_mode,
414  base_module_completion_requires_n_submodule_completions,
415  can_add_chatbot,
416  is_unlisted,
417  is_joinable_by_code_only,
418  join_code,
419  ask_marketing_consent,
420  flagged_answers_threshold,
421  flagged_answers_skip_manual_review_and_allow_retry,
422  closed_at,
423  closed_additional_message,
424  closed_course_successor_id,
425  chapter_locking_enabled
426FROM courses
427WHERE course_language_group_id = $1
428AND deleted_at IS NULL
429        ",
430        course.course_language_group_id,
431    )
432    .fetch_all(conn)
433    .await?;
434    Ok(courses)
435}
436
437pub async fn get_active_courses_for_organization(
438    conn: &mut PgConnection,
439    organization_id: Uuid,
440    pagination: Pagination,
441) -> ModelResult<Vec<Course>> {
442    let course_instances = sqlx::query_as!(
443        Course,
444        r#"
445SELECT
446    DISTINCT(c.id),
447    c.name,
448    c.created_at,
449    c.updated_at,
450    c.organization_id,
451    c.deleted_at,
452    c.slug,
453    c.content_search_language::text,
454    c.language_code,
455    c.copied_from,
456    c.course_language_group_id,
457    c.description,
458    c.is_draft,
459    c.is_test_mode,
460    c.base_module_completion_requires_n_submodule_completions,
461    c.can_add_chatbot,
462    c.is_unlisted,
463    c.is_joinable_by_code_only,
464    c.join_code,
465    c.ask_marketing_consent,
466    c.flagged_answers_threshold,
467    c.flagged_answers_skip_manual_review_and_allow_retry,
468    c.closed_at,
469    c.closed_additional_message,
470    c.closed_course_successor_id,
471    c.chapter_locking_enabled
472FROM courses as c
473    LEFT JOIN course_instances as ci on c.id = ci.course_id
474WHERE
475    c.organization_id = $1 AND
476    ci.starts_at < NOW() AND ci.ends_at > NOW() AND
477    c.deleted_at IS NULL AND ci.deleted_at IS NULL
478    LIMIT $2 OFFSET $3;
479        "#,
480        organization_id,
481        pagination.limit(),
482        pagination.offset()
483    )
484    .fetch_all(conn)
485    .await?;
486    Ok(course_instances)
487}
488
489pub async fn get_active_courses_for_organization_count(
490    conn: &mut PgConnection,
491    organization_id: Uuid,
492) -> ModelResult<CourseCount> {
493    let result = sqlx::query!(
494        r#"
495SELECT
496    COUNT(DISTINCT c.id) as count
497FROM courses as c
498    LEFT JOIN course_instances as ci on c.id = ci.course_id
499WHERE
500    c.organization_id = $1 AND
501    ci.starts_at < NOW() AND ci.ends_at > NOW() AND
502    c.deleted_at IS NULL AND ci.deleted_at IS NULL;
503        "#,
504        organization_id
505    )
506    .fetch_one(conn)
507    .await?;
508    Ok(CourseCount {
509        count: result.count.unwrap_or_default().try_into()?,
510    })
511}
512
513pub async fn get_course(conn: &mut PgConnection, course_id: Uuid) -> ModelResult<Course> {
514    let course = sqlx::query_as!(
515        Course,
516        r#"
517SELECT id,
518  name,
519  created_at,
520  updated_at,
521  organization_id,
522  deleted_at,
523  slug,
524  content_search_language::text,
525  language_code,
526  copied_from,
527  course_language_group_id,
528  description,
529  is_draft,
530  is_test_mode,
531  can_add_chatbot,
532  is_unlisted,
533  base_module_completion_requires_n_submodule_completions,
534  is_joinable_by_code_only,
535  join_code,
536  ask_marketing_consent,
537  flagged_answers_threshold,
538  flagged_answers_skip_manual_review_and_allow_retry,
539  closed_at,
540  closed_additional_message,
541  closed_course_successor_id,
542  chapter_locking_enabled
543FROM courses
544WHERE id = $1;
545    "#,
546        course_id
547    )
548    .fetch_one(conn)
549    .await?;
550    Ok(course)
551}
552
553pub async fn get_by_id_and_join_code(
554    conn: &mut PgConnection,
555    course_id: Uuid,
556    join_code: &str,
557) -> ModelResult<Course> {
558    let course = sqlx::query_as!(
559        Course,
560        r#"
561SELECT id,
562  name,
563  created_at,
564  updated_at,
565  organization_id,
566  deleted_at,
567  slug,
568  content_search_language::text,
569  language_code,
570  copied_from,
571  course_language_group_id,
572  description,
573  is_draft,
574  is_test_mode,
575  can_add_chatbot,
576  is_unlisted,
577  base_module_completion_requires_n_submodule_completions,
578  is_joinable_by_code_only,
579  join_code,
580  ask_marketing_consent,
581  flagged_answers_threshold,
582  flagged_answers_skip_manual_review_and_allow_retry,
583  closed_at,
584  closed_additional_message,
585  closed_course_successor_id,
586  chapter_locking_enabled
587FROM courses
588WHERE id = $1
589  AND join_code = $2
590  AND deleted_at IS NULL;
591    "#,
592        course_id,
593        join_code,
594    )
595    .fetch_one(conn)
596    .await?;
597    Ok(course)
598}
599
600pub async fn get_course_breadcrumb_info(
601    conn: &mut PgConnection,
602    course_id: Uuid,
603) -> ModelResult<CourseBreadcrumbInfo> {
604    let res = sqlx::query_as!(
605        CourseBreadcrumbInfo,
606        r#"
607SELECT courses.id as course_id,
608  courses.name as course_name,
609  courses.slug as course_slug,
610  organizations.slug as organization_slug,
611  organizations.name as organization_name
612FROM courses
613  JOIN organizations ON (courses.organization_id = organizations.id)
614WHERE courses.id = $1;
615    "#,
616        course_id
617    )
618    .fetch_one(conn)
619    .await?;
620    Ok(res)
621}
622
623pub async fn get_nondeleted_course_id_by_slug(
624    conn: &mut PgConnection,
625    slug: &str,
626) -> ModelResult<CourseContextData> {
627    let data = sqlx::query_as!(
628        CourseContextData,
629        "SELECT id, is_test_mode FROM courses WHERE slug = $1 AND deleted_at IS NULL",
630        slug
631    )
632    .fetch_one(conn)
633    .await?;
634    Ok(data)
635}
636
637pub async fn get_organization_id(conn: &mut PgConnection, id: Uuid) -> ModelResult<Uuid> {
638    let organization_id = sqlx::query!("SELECT organization_id FROM courses WHERE id = $1", id)
639        .fetch_one(conn)
640        .await?
641        .organization_id;
642    Ok(organization_id)
643}
644
645/// Gets full course structure including all the pages.
646pub async fn get_course_structure(
647    conn: &mut PgConnection,
648    course_id: Uuid,
649    file_store: &dyn FileStore,
650    app_conf: &ApplicationConfiguration,
651) -> ModelResult<CourseStructure> {
652    let course = get_course(conn, course_id).await?;
653    let pages = get_all_by_course_id_and_visibility(conn, course_id, PageVisibility::Any).await?;
654    let chapters = course_chapters(conn, course_id)
655        .await?
656        .iter()
657        .map(|chapter| Chapter::from_database_chapter(chapter, file_store, app_conf))
658        .collect();
659    let modules = crate::course_modules::get_by_course_id(conn, course_id).await?;
660    Ok(CourseStructure {
661        course,
662        pages,
663        chapters,
664        modules,
665    })
666}
667
668pub async fn organization_courses_visible_to_user_paginated(
669    conn: &mut PgConnection,
670    organization_id: Uuid,
671    user: Option<Uuid>,
672    pagination: Pagination,
673) -> ModelResult<Vec<Course>> {
674    let courses = sqlx::query_as!(
675        Course,
676        r#"
677SELECT courses.id,
678  courses.name,
679  courses.created_at,
680  courses.updated_at,
681  courses.organization_id,
682  courses.deleted_at,
683  courses.slug,
684  courses.content_search_language::text,
685  courses.language_code,
686  courses.copied_from,
687  courses.course_language_group_id,
688  courses.description,
689  courses.is_draft,
690  courses.is_test_mode,
691  base_module_completion_requires_n_submodule_completions,
692  can_add_chatbot,
693  courses.is_unlisted,
694  courses.is_joinable_by_code_only,
695  courses.join_code,
696  courses.ask_marketing_consent,
697  courses.flagged_answers_threshold,
698  courses.flagged_answers_skip_manual_review_and_allow_retry,
699  courses.closed_at,
700  courses.closed_additional_message,
701  courses.closed_course_successor_id,
702  courses.chapter_locking_enabled
703FROM courses
704WHERE courses.organization_id = $1
705  AND (
706    (
707      courses.is_draft IS FALSE
708      AND courses.is_unlisted IS FALSE
709    )
710    OR EXISTS (
711      SELECT id
712      FROM roles
713      WHERE user_id = $2
714        AND (
715          course_id = courses.id
716          OR roles.organization_id = courses.organization_id
717          OR roles.is_global IS TRUE
718        )
719    )
720  )
721  AND courses.deleted_at IS NULL
722ORDER BY courses.name
723LIMIT $3 OFFSET $4;
724"#,
725        organization_id,
726        user,
727        pagination.limit(),
728        pagination.offset()
729    )
730    .fetch_all(conn)
731    .await?;
732    Ok(courses)
733}
734
735pub async fn organization_course_count(
736    conn: &mut PgConnection,
737    organization_id: Uuid,
738) -> ModelResult<CourseCount> {
739    let course_count = sqlx::query!(
740        r#"
741SELECT
742    COUNT(DISTINCT id) as count
743FROM courses
744WHERE organization_id = $1
745    AND deleted_at IS NULL;
746        "#,
747        organization_id,
748    )
749    .fetch_one(conn)
750    .await?;
751    Ok(CourseCount {
752        count: course_count.count.unwrap_or_default().try_into()?,
753    })
754}
755// Represents the subset of page fields that one is allowed to update in a course
756#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, Default, ToSchema)]
757
758pub struct CourseUpdate {
759    pub name: String,
760    pub description: Option<String>,
761    pub is_draft: bool,
762    pub is_test_mode: bool,
763    pub can_add_chatbot: bool,
764    pub is_unlisted: bool,
765    pub is_joinable_by_code_only: bool,
766    pub ask_marketing_consent: bool,
767    pub flagged_answers_threshold: i32,
768    pub flagged_answers_skip_manual_review_and_allow_retry: bool,
769    pub closed_at: Option<DateTime<Utc>>,
770    pub closed_additional_message: Option<String>,
771    pub closed_course_successor_id: Option<Uuid>,
772    pub chapter_locking_enabled: bool,
773}
774
775pub async fn update_course(
776    conn: &mut PgConnection,
777    course_id: Uuid,
778    course_update: CourseUpdate,
779) -> ModelResult<Course> {
780    let res = sqlx::query_as!(
781        Course,
782        r#"
783UPDATE courses
784SET name = $1,
785  description = $2,
786  is_draft = $3,
787  is_test_mode = $4,
788  can_add_chatbot = $5,
789  is_unlisted = $6,
790  is_joinable_by_code_only = $7,
791  ask_marketing_consent = $8,
792  flagged_answers_threshold = $9,
793  flagged_answers_skip_manual_review_and_allow_retry = $10,
794  closed_at = $11,
795  closed_additional_message = $12,
796  closed_course_successor_id = $13,
797  chapter_locking_enabled = $14
798WHERE id = $15
799  AND deleted_at IS NULL
800RETURNING id,
801  name,
802  created_at,
803  updated_at,
804  organization_id,
805  deleted_at,
806  slug,
807  content_search_language::text,
808  language_code,
809  copied_from,
810  course_language_group_id,
811  description,
812  is_draft,
813  is_test_mode,
814  can_add_chatbot,
815  is_unlisted,
816  base_module_completion_requires_n_submodule_completions,
817  is_joinable_by_code_only,
818  join_code,
819  ask_marketing_consent,
820  flagged_answers_threshold,
821  flagged_answers_skip_manual_review_and_allow_retry,
822  closed_at,
823  closed_additional_message,
824  closed_course_successor_id,
825  chapter_locking_enabled
826    "#,
827        course_update.name,
828        course_update.description,
829        course_update.is_draft,
830        course_update.is_test_mode,
831        course_update.can_add_chatbot,
832        course_update.is_unlisted,
833        course_update.is_joinable_by_code_only,
834        course_update.ask_marketing_consent,
835        course_update.flagged_answers_threshold,
836        course_update.flagged_answers_skip_manual_review_and_allow_retry,
837        course_update.closed_at,
838        course_update.closed_additional_message,
839        course_update.closed_course_successor_id,
840        course_update.chapter_locking_enabled,
841        course_id
842    )
843    .fetch_one(conn)
844    .await?;
845    Ok(res)
846}
847
848pub async fn update_course_base_module_completion_count_requirement(
849    conn: &mut PgConnection,
850    id: Uuid,
851    base_module_completion_requires_n_submodule_completions: i32,
852) -> ModelResult<bool> {
853    let res = sqlx::query!(
854        "
855UPDATE courses
856SET base_module_completion_requires_n_submodule_completions = $1
857WHERE id = $2
858  AND deleted_at IS NULL
859        ",
860        base_module_completion_requires_n_submodule_completions,
861        id,
862    )
863    .execute(conn)
864    .await?;
865    Ok(res.rows_affected() > 0)
866}
867
868pub async fn delete_course(conn: &mut PgConnection, course_id: Uuid) -> ModelResult<Course> {
869    let deleted = sqlx::query_as!(
870        Course,
871        r#"
872UPDATE courses
873SET deleted_at = now()
874WHERE id = $1
875AND deleted_at IS NULL
876RETURNING id,
877  name,
878  created_at,
879  updated_at,
880  organization_id,
881  deleted_at,
882  slug,
883  content_search_language::text,
884  language_code,
885  copied_from,
886  course_language_group_id,
887  description,
888  is_draft,
889  is_test_mode,
890  can_add_chatbot,
891  is_unlisted,
892  base_module_completion_requires_n_submodule_completions,
893  is_joinable_by_code_only,
894  join_code,
895  ask_marketing_consent,
896  flagged_answers_threshold,
897  flagged_answers_skip_manual_review_and_allow_retry,
898  closed_at,
899  closed_additional_message,
900  closed_course_successor_id,
901  chapter_locking_enabled
902    "#,
903        course_id
904    )
905    .fetch_one(conn)
906    .await?;
907    Ok(deleted)
908}
909
910pub async fn get_course_by_slug(conn: &mut PgConnection, course_slug: &str) -> ModelResult<Course> {
911    let course = sqlx::query_as!(
912        Course,
913        "
914SELECT id,
915  name,
916  created_at,
917  updated_at,
918  organization_id,
919  deleted_at,
920  slug,
921  content_search_language::text,
922  language_code,
923  copied_from,
924  course_language_group_id,
925  description,
926  is_draft,
927  is_test_mode,
928  can_add_chatbot,
929  is_unlisted,
930  base_module_completion_requires_n_submodule_completions,
931  is_joinable_by_code_only,
932  join_code,
933  ask_marketing_consent,
934  flagged_answers_threshold,
935  flagged_answers_skip_manual_review_and_allow_retry,
936  closed_at,
937  closed_additional_message,
938  closed_course_successor_id,
939  chapter_locking_enabled
940FROM courses
941WHERE slug = $1
942  AND deleted_at IS NULL
943",
944        course_slug,
945    )
946    .fetch_one(conn)
947    .await?;
948    Ok(course)
949}
950
951pub async fn get_cfgname_by_tag(
952    conn: &mut PgConnection,
953    ietf_language_tag: String,
954) -> ModelResult<String> {
955    let tag = ietf_language_tag
956        .split('-')
957        .next()
958        .unwrap_or_else(|| &ietf_language_tag[..]);
959
960    let lang_name = LANGUAGE_TAG_TO_NAME.get(&tag);
961
962    let name = sqlx::query!(
963        "SELECT cfgname::text FROM pg_ts_config WHERE cfgname = $1",
964        lang_name
965    )
966    .fetch_optional(conn)
967    .await?;
968
969    let res = name
970        .and_then(|n| n.cfgname)
971        .unwrap_or_else(|| "simple".to_string());
972
973    Ok(res)
974}
975
976pub async fn is_draft(conn: &mut PgConnection, id: Uuid) -> ModelResult<bool> {
977    let res = sqlx::query!(
978        "
979SELECT is_draft
980FROM courses
981WHERE id = $1
982",
983        id
984    )
985    .fetch_one(conn)
986    .await?;
987    Ok(res.is_draft)
988}
989
990pub async fn is_joinable_by_code_only(conn: &mut PgConnection, id: Uuid) -> ModelResult<bool> {
991    let res = sqlx::query!(
992        "
993SELECT is_joinable_by_code_only
994FROM courses
995WHERE id = $1
996",
997        id
998    )
999    .fetch_one(conn)
1000    .await?;
1001    Ok(res.is_joinable_by_code_only)
1002}
1003
1004pub(crate) async fn get_by_ids(
1005    conn: &mut PgConnection,
1006    course_ids: &[Uuid],
1007) -> ModelResult<Vec<Course>> {
1008    let courses = sqlx::query_as!(
1009        Course,
1010        "
1011SELECT id,
1012  name,
1013  created_at,
1014  updated_at,
1015  organization_id,
1016  deleted_at,
1017  slug,
1018  content_search_language::text,
1019  language_code,
1020  copied_from,
1021  course_language_group_id,
1022  description,
1023  is_draft,
1024  is_test_mode,
1025  can_add_chatbot,
1026  is_unlisted,
1027  base_module_completion_requires_n_submodule_completions,
1028  is_joinable_by_code_only,
1029  join_code,
1030  ask_marketing_consent,
1031  flagged_answers_threshold,
1032  flagged_answers_skip_manual_review_and_allow_retry,
1033  closed_at,
1034  closed_additional_message,
1035  closed_course_successor_id,
1036  chapter_locking_enabled
1037FROM courses
1038WHERE id IN (SELECT * FROM UNNEST($1::uuid[]))
1039  AND deleted_at IS NULL
1040        ",
1041        course_ids
1042    )
1043    .fetch_all(conn)
1044    .await?;
1045    Ok(courses)
1046}
1047
1048pub async fn get_by_organization_id(
1049    conn: &mut PgConnection,
1050    organization_id: Uuid,
1051) -> ModelResult<Vec<Course>> {
1052    let courses = sqlx::query_as!(
1053        Course,
1054        r#"
1055SELECT id,
1056  name,
1057  created_at,
1058  updated_at,
1059  organization_id,
1060  deleted_at,
1061  slug,
1062  content_search_language::text,
1063  language_code,
1064  copied_from,
1065  course_language_group_id,
1066  description,
1067  is_draft,
1068  is_test_mode,
1069  can_add_chatbot,
1070  is_unlisted,
1071  base_module_completion_requires_n_submodule_completions,
1072  is_joinable_by_code_only,
1073  join_code,
1074  ask_marketing_consent,
1075  flagged_answers_threshold,
1076  flagged_answers_skip_manual_review_and_allow_retry,
1077  closed_at,
1078  closed_additional_message,
1079  closed_course_successor_id,
1080  chapter_locking_enabled
1081FROM courses
1082WHERE organization_id = $1
1083  AND deleted_at IS NULL
1084ORDER BY name
1085        "#,
1086        organization_id
1087    )
1088    .fetch_all(conn)
1089    .await?;
1090    Ok(courses)
1091}
1092
1093pub async fn set_join_code_for_course(
1094    conn: &mut PgConnection,
1095    course_id: Uuid,
1096    join_code: String,
1097) -> ModelResult<()> {
1098    sqlx::query!(
1099        "
1100UPDATE courses
1101SET join_code = $2
1102WHERE id = $1
1103",
1104        course_id,
1105        join_code
1106    )
1107    .execute(conn)
1108    .await?;
1109    Ok(())
1110}
1111
1112pub async fn get_course_with_join_code(
1113    conn: &mut PgConnection,
1114    join_code: String,
1115) -> ModelResult<Course> {
1116    let course = sqlx::query_as!(
1117        Course,
1118        r#"
1119SELECT id,
1120  name,
1121  created_at,
1122  updated_at,
1123  organization_id,
1124  deleted_at,
1125  slug,
1126  content_search_language::text,
1127  language_code,
1128  copied_from,
1129  course_language_group_id,
1130  description,
1131  is_draft,
1132  is_test_mode,
1133  can_add_chatbot,
1134  is_unlisted,
1135  base_module_completion_requires_n_submodule_completions,
1136  is_joinable_by_code_only,
1137  join_code,
1138  ask_marketing_consent,
1139  flagged_answers_threshold,
1140  flagged_answers_skip_manual_review_and_allow_retry,
1141  closed_at,
1142  closed_additional_message,
1143  closed_course_successor_id,
1144  chapter_locking_enabled
1145FROM courses
1146WHERE join_code = $1
1147  AND deleted_at IS NULL;
1148    "#,
1149        join_code,
1150    )
1151    .fetch_one(conn)
1152    .await?;
1153    Ok(course)
1154}
1155
1156#[cfg(test)]
1157mod test {
1158    use super::*;
1159    use crate::{course_language_groups, courses, test_helper::*};
1160
1161    mod language_code_validation {
1162        use super::*;
1163
1164        #[tokio::test]
1165        async fn allows_valid_language_code() {
1166            insert_data!(:tx, user: _user, :org);
1167            let course_language_group_id = course_language_groups::insert(
1168                tx.as_mut(),
1169                PKeyPolicy::Fixed(Uuid::parse_str("8e40c36c-835b-479c-8f07-863ad408f181").unwrap()),
1170                "test-clg-allows-valid",
1171            )
1172            .await
1173            .unwrap();
1174            let new_course = create_new_course(org, "en-US");
1175            let res = courses::insert(
1176                tx.as_mut(),
1177                PKeyPolicy::Fixed(Uuid::parse_str("95d8ab4d-073c-4794-b8c5-f683f0856356").unwrap()),
1178                course_language_group_id,
1179                &new_course,
1180            )
1181            .await;
1182            assert!(res.is_ok());
1183        }
1184
1185        #[tokio::test]
1186        async fn disallows_empty_language_code() {
1187            insert_data!(:tx, user: _user, :org);
1188            let course_language_group_id = course_language_groups::insert(
1189                tx.as_mut(),
1190                PKeyPolicy::Fixed(Uuid::parse_str("8e40c36c-835b-479c-8f07-863ad408f181").unwrap()),
1191                "test-clg-disallows-empty",
1192            )
1193            .await
1194            .unwrap();
1195            let new_course = create_new_course(org, "");
1196            let res = courses::insert(
1197                tx.as_mut(),
1198                PKeyPolicy::Fixed(Uuid::parse_str("95d8ab4d-073c-4794-b8c5-f683f0856356").unwrap()),
1199                course_language_group_id,
1200                &new_course,
1201            )
1202            .await;
1203            assert!(res.is_err());
1204        }
1205
1206        #[tokio::test]
1207        async fn disallows_wrong_case_language_code() {
1208            insert_data!(:tx, user: _user, :org);
1209            let course_language_group_id = course_language_groups::insert(
1210                tx.as_mut(),
1211                PKeyPolicy::Fixed(Uuid::parse_str("8e40c36c-835b-479c-8f07-863ad408f181").unwrap()),
1212                "test-clg-disallows-wrong-case",
1213            )
1214            .await
1215            .unwrap();
1216            let new_course = create_new_course(org, "en-us");
1217            let res = courses::insert(
1218                tx.as_mut(),
1219                PKeyPolicy::Fixed(Uuid::parse_str("95d8ab4d-073c-4794-b8c5-f683f0856356").unwrap()),
1220                course_language_group_id,
1221                &new_course,
1222            )
1223            .await;
1224            assert!(res.is_err());
1225        }
1226
1227        #[tokio::test]
1228        async fn disallows_underscore_in_language_code() {
1229            insert_data!(:tx, user: _user, :org);
1230            let course_language_group_id = course_language_groups::insert(
1231                tx.as_mut(),
1232                PKeyPolicy::Fixed(Uuid::parse_str("8e40c36c-835b-479c-8f07-863ad408f181").unwrap()),
1233                "test-clg-disallows-underscore",
1234            )
1235            .await
1236            .unwrap();
1237            let new_course = create_new_course(org, "en_US");
1238            let res = courses::insert(
1239                tx.as_mut(),
1240                PKeyPolicy::Fixed(Uuid::parse_str("95d8ab4d-073c-4794-b8c5-f683f0856356").unwrap()),
1241                course_language_group_id,
1242                &new_course,
1243            )
1244            .await;
1245            assert!(res.is_err());
1246        }
1247
1248        fn create_new_course(organization_id: Uuid, language_code: &str) -> NewCourse {
1249            NewCourse {
1250                name: "".to_string(),
1251                slug: "".to_string(),
1252                organization_id,
1253                language_code: language_code.to_string(),
1254                teacher_in_charge_name: "teacher".to_string(),
1255                teacher_in_charge_email: "teacher@example.com".to_string(),
1256                description: "description".to_string(),
1257                is_draft: false,
1258                is_test_mode: false,
1259                is_unlisted: false,
1260                copy_user_permissions: false,
1261                is_joinable_by_code_only: false,
1262                join_code: None,
1263                ask_marketing_consent: false,
1264                flagged_answers_threshold: Some(3),
1265                can_add_chatbot: false,
1266            }
1267        }
1268    }
1269}
headless_lms_models/courses.rs

headless_lms_models/
courses.rs
