headless_lms_models/
courses.rs

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