Changes between Initial Version and Version 1 of enrollInCourse

Timestamp:

01/25/26 16:58:20 (5 days ago)

Author:

231175

Comment:

--

enrollInCourse

@@ +1 @@
+== Купување на курс
+=== Актери
+- Најавен корисник
+=== Чекор 1 
+На најавен корисник му се прикажува листа од сите достапни курсеви од каде корисникот селектира еден.
+
+{{{ 
+SELECT
+    c.id,
+    c.image_url,
+    c.color,
+    c.difficulty,
+    c.duration_minutes,
+    c.price,
+    ct.title_short,
+    ct.title,
+    (
+        SELECT
+            COUNT(*)
+        FROM
+            course_translate
+        WHERE
+            course_id = c.id
+    ) AS translated_languages,
+    (
+        SELECT
+            COUNT(*)
+        FROM
+            course_content cc
+            JOIN course_version cv
+                ON cc.course_version_id = cv.id
+        WHERE
+            cv.course_id = c.id
+            AND cv.active = TRUE
+    ) AS course_content_count,
+    (
+        SELECT
+            COUNT(*)
+        FROM
+            course_lecture cl
+            JOIN course_content cc
+                ON cl.course_content_id = cc.id
+            JOIN course_version cv
+                ON cc.course_version_id = cv.id
+        WHERE
+            cv.course_id = c.id
+            AND cv.active = TRUE
+    ) AS course_lecture_count,
+    (
+        SELECT
+            AVG(r.rating)
+        FROM
+            review r
+            JOIN enrollment e
+                ON e.id = r.enrollment_id
+            JOIN course_version cv
+                ON cv.id = e.course_version_id
+        WHERE
+            cv.course_id = c.id
+    ) AS avg_rating,
+    (
+        SELECT
+            tt.value
+        FROM
+            course_tag ctag
+            JOIN tag t
+                ON t.id = ctag.tag_id
+            JOIN tag_translate tt
+                ON t.id = tt.tag_id
+        WHERE
+            ctag.course_id = c.id
+            AND tt.language = 'en'
+            AND t.type = 'skill'
+    ) AS skills,
+    (
+        SELECT
+            tt.value
+        FROM
+            course_tag ctag
+            JOIN tag t
+                ON t.id = ctag.tag_id
+            JOIN tag_translate tt
+                ON t.id = tt.tag_id
+        WHERE
+            ctag.course_id = c.id
+            AND tt.language = 'en'
+            AND t.type = 'interest'
+    ) AS topics
+FROM
+    course c
+    JOIN course_translate ct
+        ON c.id = ct.course_id
+WHERE
+    ct.language = 'en';
+}}}
+
+
+=== Чекор 2
+
+Откако корисникот кликнал на курс, му се прикажуваат детални информации за курсот.
+
+{{{
+SELECT
+    c.id,
+    c.image_url,
+    c.color,
+    c.difficulty,
+    c.duration_minutes,
+    c.price,
+    ct.title_short,
+    ct.title,
+    ct.description_short,
+    ct.description,
+    ct.description_long,
+
+    (
+        SELECT
+            COUNT(*)
+        FROM
+            course_translate
+        WHERE
+            course_id = c.id
+    ) AS translated_languages,
+
+    (
+        SELECT
+            COUNT(*)
+        FROM
+            course_content cc
+            JOIN course_version cv
+                ON cc.course_version_id = cv.id
+        WHERE
+            cv.course_id = c.id
+            AND cv.active = TRUE
+    ) AS course_content_count,
+
+    (
+        SELECT
+            COUNT(*)
+        FROM
+            course_lecture cl
+            JOIN course_content cc
+                ON cl.course_content_id = cc.id
+            JOIN course_version cv
+                ON cc.course_version_id = cv.id
+        WHERE
+            cv.course_id = c.id
+            AND cv.active = TRUE
+    ) AS course_lecture_count,
+
+    (
+        SELECT
+            AVG(r.rating)
+        FROM
+            review r
+            JOIN enrollment e
+                ON e.id = r.enrollment_id
+            JOIN course_version cv
+                ON cv.id = e.course_version_id
+        WHERE
+            cv.course_id = c.id
+    ) AS avg_rating,
+
+    (
+        SELECT
+            JSON_AGG(tt.value)
+        FROM
+            course_tag ctag
+            JOIN tag t
+                ON t.id = ctag.tag_id
+            JOIN tag_translate tt
+                ON t.id = tt.tag_id
+        WHERE
+            ctag.course_id = c.id
+            AND tt.language = 'en'
+            AND t.type = 'skill'
+    ) AS skills,
+
+    (
+        SELECT
+            JSON_AGG(tt.value)
+        FROM
+            course_tag ctag
+            JOIN tag t
+                ON t.id = ctag.tag_id
+            JOIN tag_translate tt
+                ON t.id = tt.tag_id
+        WHERE
+            ctag.course_id = c.id
+            AND tt.language = 'en'
+            AND t.type = 'interest'
+    ) AS topics,
+
+    (
+        SELECT
+            JSON_AGG(ctwwbl.what_will_be_learned)
+        FROM
+            course_translate_what_will_be_learned ctwwbl
+        WHERE
+            ctwwbl.course_translate_id = ct.id
+    ) AS what_will_be_learned,
+
+    (
+        SELECT
+            JSON_AGG(
+                JSONB_BUILD_OBJECT(
+                    'title', cct.title,
+                    'position', cc.position,
+                    'lectures', (
+                        SELECT
+                            JSON_AGG(
+                                JSONB_BUILD_OBJECT(
+                                    'title', clt.title,
+                                    'description', clt.description,
+                                    'duration_minutes', cl.duration_minutes,
+                                    'position', cl.position,
+                                    'content_type', cl.content_type
+                                )
+                                ORDER BY cl.position
+                            )
+                        FROM
+                            course_lecture cl
+                            JOIN course_lecture_translate clt
+                                ON clt.course_lecture_id = cl.id
+                        WHERE
+                            cl.course_content_id = cc.id
+                            AND clt.language = 'en'
+                    )
+                )
+                ORDER BY cc.position
+            )
+        FROM
+            course_content cc
+            JOIN course_content_translate cct
+                ON cct.course_content_id = cc.id
+            JOIN course_version cv
+                ON cc.course_version_id = cv.id
+        WHERE
+            cv.course_id = c.id
+            AND cv.active = TRUE
+            AND cct.language = 'en'
+    ) AS course_content
+
+FROM
+    course c
+    JOIN course_translate ct
+        ON c.id = ct.course_id
+
+WHERE
+    ct.language = 'en';
+}}}
+
+=== Чекори 3
+
+Корисникот кликнува на копчето за купување на курс
+
+{{{
+INSERT INTO enrollment (purchase_date, activation_date, completion_date, enrollment_status, user_id, course_version_id)
+VALUES ('2026-01-25 00:00:00', null, null, 'pending', 1, 2);
+}}}
+
+purchase_date -> датум на купување
+activation_date -> датум на активација
+completion_date -> датум на завршување
+enrollment_status -> статус за тоа дали не е почнат, е почнат или е завршен курсот (default е pending бидејќи при купување на курсот тој не е започнат)
+user_id -> ид на корисник
+course_version_id -> ид на верзија на курсот која се купува
+
+
+
+
+
+