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

Актери

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

Чекор 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
            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
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 -> ид на верзија на курсот која се купува