Купување на курс

Актери

• Најавен корисник

Чекор 1

На најавен корисник му се прикажува листа од сите достапни курсеви од каде корисникот селектира еден.

SELECT
    c.course_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.course_id
    ) AS translated_languages,
    (
        SELECT
            COUNT(*)
        FROM
            course_content cc
            JOIN course_version cv
                ON cc.course_version_id = cv.course_version_id
        WHERE
            cv.course_id = c.course_id
            AND cv.is_active
    ) AS course_content_count,
    (
        SELECT
            COUNT(*)
        FROM
            course_lecture cl
            JOIN course_content cc
                ON cl.course_content_id = cc.course_content_id
            JOIN course_version cv
                ON cc.course_version_id = cv.course_version_id
        WHERE
            cv.course_id = c.course_id
            AND cv.is_active
    ) AS course_lecture_count,
    (
        SELECT
            AVG(r.rating)
        FROM
            review r
            JOIN enrollment e
                ON e.enrollment_id = r.enrollment_id
            JOIN course_version cv
                ON cv.course_version_id = e.course_version_id
        WHERE
            cv.course_id = c.course_id
    ) AS avg_rating,
    (
        SELECT
            JSON_AGG(tt.value)
        FROM
            course_tag ctag
            JOIN tag t
                ON t.tag_id = ctag.tag_id
            JOIN tag_translate tt
                ON tt.tag_id = t.tag_id
            JOIN language l
                ON l.id = tt.language_id
        WHERE
            ctag.course_id = c.course_id
            AND l.value = 'en'
            AND t.tag_type = 'skill'
    ) AS skills,
    (
        SELECT
            JSON_AGG(tt.value)
        FROM
            course_tag ctag
            JOIN tag t
                ON t.tag_id = ctag.tag_id
            JOIN tag_translate tt
                ON tt.tag_id = t.tag_id
            JOIN language l
                ON l.id = tt.language_id
        WHERE
            ctag.course_id = c.course_id
            AND l.value = 'en'
            AND t.tag_type = 'topic'
    ) AS topics
FROM
    course c
    JOIN course_translate ct
        ON ct.course_id = c.course_id
    JOIN language lang
        ON lang.id = ct.language_id
WHERE
    lang.value = 'en'
    AND EXISTS (
        SELECT 1
        FROM course_version cv
        WHERE cv.course_id = c.course_id
          AND cv.is_active
    );

Чекор 2

Откако корисникот кликнал на курс, му се прикажуваат детални информации за курсот.

SELECT
    c.course_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.course_id
    ) AS translated_languages,

    (
        SELECT
            COUNT(*)
        FROM
            course_content cc
            JOIN course_version cv
                ON cc.course_version_id = cv.course_version_id
        WHERE
            cv.course_id = c.course_id
            AND cv.is_active
    ) AS course_content_count,

    (
        SELECT
            COUNT(*)
        FROM
            course_lecture cl
            JOIN course_content cc
                ON cl.course_content_id = cc.course_content_id
            JOIN course_version cv
                ON cc.course_version_id = cv.course_version_id
        WHERE
            cv.course_id = c.course_id
            AND cv.is_active
    ) AS course_lecture_count,

    (
        SELECT
            AVG(r.rating)
        FROM
            review r
            JOIN enrollment e
                ON e.enrollment_id = r.enrollment_id
            JOIN course_version cv
                ON cv.course_version_id = e.course_version_id
        WHERE
            cv.course_id = c.course_id
    ) AS avg_rating,

    (
        SELECT
            JSON_AGG(tt.value)
        FROM
            course_tag ctag
            JOIN tag t
                ON t.tag_id = ctag.tag_id
            JOIN tag_translate tt
                ON tt.tag_id = t.tag_id
            JOIN language l
                ON l.id = tt.language_id
        WHERE
            ctag.course_id = c.course_id
            AND l.value = 'en'
            AND t.tag_type = 'skill'
    ) AS skills,

    (
        SELECT
            JSON_AGG(tt.value)
        FROM
            course_tag ctag
            JOIN tag t
                ON t.tag_id = ctag.tag_id
            JOIN tag_translate tt
                ON tt.tag_id = t.tag_id
            JOIN language l
                ON l.id = tt.language_id
        WHERE
            ctag.course_id = c.course_id
            AND l.value = 'en'
            AND t.tag_type = 'topic'
    ) AS topics,

    -- what_will_be_learned е text[] колона во course_translate.
    -- Ако се врати нормализираната табела course_translate_what_will_be_learned,
    -- ова се заменува со корелиран подзапрос по ct.course_translate_id.
    TO_JSON(ct.what_will_be_learned) 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.course_lecture_id
                            JOIN language ll
                                ON ll.id = clt.language_id
                        WHERE
                            cl.course_content_id = cc.course_content_id
                            AND ll.value = 'en'
                    )
                )
                ORDER BY cc.position
            )
        FROM
            course_content cc
            JOIN course_content_translate cct
                ON cct.course_content_id = cc.course_content_id
            JOIN language lc
                ON lc.id = cct.language_id
            JOIN course_version cv
                ON cc.course_version_id = cv.course_version_id
        WHERE
            cv.course_id = c.course_id
            AND cv.is_active
            AND lc.value = 'en'
    ) AS course_content

FROM
    course c
    JOIN course_translate ct
        ON ct.course_id = c.course_id
    JOIN language lang
        ON lang.id = ct.language_id

WHERE
    lang.value = 'en'
    AND c.course_id = :course_id;

Чекор 3

Корисникот кликнува на копчето за купување на курс.

INSERT INTO enrollment (purchase_date, activation_date, completion_date,
                        enrollment_status, user_id, course_version_id)
VALUES (DATE '2026-01-25', NULL, NULL, 'pending', 1, 2)
RETURNING enrollment_id;

purchase_date -> датум на купување activation_date -> датум на активација (NULL додека курсот не е активиран) completion_date -> датум на завршување (NULL додека курсот не е завршен) enrollment_status -> статус за тоа дали не е почнат, е почнат или е завршен курсот (при купување се внесува 'pending' бидејќи курсот сè уште не е започнат) user_id -> ид на корисник course_version_id -> ид на верзија на курсот која се купува

Ограничувањето ck_enrollment_status_dates бара:

• 'pending' -> activation_date IS NULL И completion_date IS NULL
• 'active' -> activation_date IS NOT NULL И completion_date IS NULL
• 'completed' -> двете полиња се пополнети

Ограничувањето uq_enrollment_user_version не дозволува ист корисник да купи иста верзија од курс двапати.