= Напреден развој на базата =

== Правила за запишување на курс (Enrollment) ==

=== Опис на барањата за податочни ограничувања ===

Системот мора да обезбеди дека:
* Кога корисникот се запишува на курс по дадена верзија на курс, автоматски се пренасочува кон моментално активната верзија на тој курс (course_version.is_active = true).
* Не е дозволено запишување на курс за кој воопшто нема активна верзија.
* Секоја верзија се третира независно: корисникот може да има повеќе запишувања на различни верзии на истиот курс, без разлика дали претходните се завршени или не.

=== Имплементација ===

==== Тригери ====

BEFORE INSERT тригер на enrollment за автоматско доделување на активната верзија на курсот (course version).
{{{
CREATE OR REPLACE FUNCTION set_active_course_version_on_enrollment()
RETURNS TRIGGER 
AS $$
DECLARE
    v_course_id BIGINT;
    v_course_version_id BIGINT;
BEGIN
    SELECT cv.course_id
    INTO v_course_id
    FROM course_version cv
    WHERE cv.course_version_id = NEW.course_version_id;

    IF v_course_id IS NULL THEN
        RAISE EXCEPTION 'Course version % does not exist', NEW.course_version_id;
    END IF;

    SELECT cv.course_version_id
    INTO v_course_version_id
    FROM course_version cv
    WHERE cv.course_id = v_course_id
      AND cv.is_active = TRUE
    ORDER BY cv.version_number DESC
    LIMIT 1;
    
    IF v_course_version_id IS NULL THEN
        RAISE EXCEPTION 'No active course version found for course_id=%', v_course_id;
    END IF;
    
    NEW.course_version_id := v_course_version_id;
    
    RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER trg_set_active_course_version_on_enrollment
BEFORE INSERT ON enrollment
FOR EACH ROW
EXECUTE FUNCTION set_active_course_version_on_enrollment();
}}}

==== Функции / Stored Procedures ====

Функција за креирање на нов enrollment. При креирање се зема најновата активна верзија на курсот.
{{{
CREATE OR REPLACE FUNCTION create_enrollment_for_active_version(p_user_id BIGINT, p_course_id BIGINT)
RETURNS BIGINT 
AS $$
DECLARE
    v_course_version_id BIGINT;
    v_enrollment_id BIGINT;
BEGIN
    SELECT cv.course_version_id
    INTO v_course_version_id
    FROM course_version cv
    WHERE cv.course_id = p_course_id
      AND cv.is_active = TRUE
    ORDER BY cv.version_number DESC
    LIMIT 1;
    
    IF v_course_version_id IS NULL THEN
        RAISE EXCEPTION 'No active course version found for course_id=%', p_course_id;
    END IF;
    
    INSERT INTO enrollment (user_id, course_version_id, purchase_date, enrollment_status)
    VALUES (p_user_id, v_course_version_id, CURRENT_DATE, 'pending')
    RETURNING enrollment_id INTO v_enrollment_id;
    
    RETURN v_enrollment_id;
END;
$$ 
LANGUAGE plpgsql;
}}}

==== Погледи (Views) ====

Поглед за генерирање на основни информации за кои enrollments имаат застарена верзија од курсот, а кои најнова. 
{{{
CREATE OR REPLACE VIEW enrollments_with_active_version AS
SELECT
    e.enrollment_id AS enrollment_id,
    u.user_id AS user_id,
    u.name AS user_name,
    c.course_id AS course_id,
    ct.title_short AS course_title,
    cv.course_version_id AS course_version_id,
    cv.version_number AS course_version_number,
    cv.is_active AS is_version_active,
    e.purchase_date AS purchase_date,
    e.enrollment_status AS enrollment_status
FROM enrollment e
JOIN "user" u ON e.user_id = u.user_id
JOIN course_version cv ON e.course_version_id = cv.course_version_id
JOIN course c ON cv.course_id = c.course_id
JOIN course_translate ct ON c.course_id = ct.course_id
JOIN language l ON l.id = ct.language_id AND l.value = 'mk';
}}}

----

== Менаџирање на верзии на курс (Course Version Management) ==

=== Опис на барањата за податочни ограничувања ===

Системот мора да обезбеди дека:
* Само една верзија може да биде активна (is_active = true) по курс во исто време
* Кога се активира нова верзија, претходните активни верзии на истиот курс автоматски се деактивираат
* Не смее да се избрише верзија која има активни (незавршени) запишувања (enrollments)

=== Имплементација ===

==== Тригери ====

BEFORE INSERT тригер во course_version, каде само таа верзија што се креира е is_active, останатите не се. Дополнително се ажурираат и уште некои атрибути.
{{{
CREATE OR REPLACE FUNCTION ensure_single_active_version_when_created()
RETURNS TRIGGER 
AS $$
DECLARE
    v_num_new_version INTEGER;
BEGIN
    UPDATE course_version
    SET is_active = FALSE
    WHERE course_id = NEW.course_id
      AND is_active = TRUE;

    SELECT COALESCE(MAX(version_number), 0) + 1 INTO v_num_new_version
    FROM course_version
    WHERE course_id = NEW.course_id;

    NEW.is_active := TRUE;
    NEW.created_at := CURRENT_DATE;
    NEW.version_number := v_num_new_version;
    
    RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER trg_ensure_single_active_version_when_created
BEFORE INSERT ON course_version
FOR EACH ROW
EXECUTE FUNCTION ensure_single_active_version_when_created();
}}}

BEFORE UPDATE тригер за атрибутот is_active во course_version, со цел осигурување дека постои само една активна верзија од курсот.
{{{
CREATE OR REPLACE FUNCTION ensure_single_active_version()
RETURNS TRIGGER 
AS $$
BEGIN
    IF NEW.is_active = TRUE THEN
        UPDATE course_version
        SET is_active = FALSE
        WHERE course_id = NEW.course_id
          AND course_version_id != NEW.course_version_id
          AND is_active = TRUE;
    END IF;
    
    RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER trg_ensure_single_active_version
BEFORE UPDATE OF is_active ON course_version
FOR EACH ROW
WHEN (NEW.is_active = TRUE)
EXECUTE FUNCTION ensure_single_active_version();
}}}

BEFORE DELETE тригер за course version, со цел осигурување дека course version не може да биде избришано доколку веќе постојат enrollments за него.
{{{
CREATE OR REPLACE FUNCTION prevent_version_deletion_with_active_enrollments()
RETURNS TRIGGER 
AS $$
DECLARE
    v_active_enrollments INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_active_enrollments
    FROM enrollment
    WHERE course_version_id = OLD.course_version_id;
    
    IF v_active_enrollments > 0 THEN
        RAISE EXCEPTION 'Cannot delete course version with % active enrollments', v_active_enrollments;
    END IF;
    
    RETURN OLD;
END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_version_deletion
BEFORE DELETE ON course_version
FOR EACH ROW
EXECUTE FUNCTION prevent_version_deletion_with_active_enrollments();
}}}

==== Погледи (Views) ====

Поглед за преглед на курсевите со нивните најнови верзии и enrollments.
{{{
CREATE OR REPLACE VIEW course_latest_versions AS
SELECT
    c.course_id AS course_id,
    ct.title_short AS course_title,
    cv.course_version_id AS version_id,
    cv.version_number,
    cv.is_active,
    cv.created_at,
    COUNT(DISTINCT e.enrollment_id) FILTER (WHERE e.completion_date IS NULL) AS active_enrollments,
    COUNT(DISTINCT e.enrollment_id) AS total_enrollments
FROM course c
JOIN course_version cv ON c.course_id = cv.course_id
JOIN course_translate ct ON c.course_id = ct.course_id
JOIN language l ON l.id = ct.language_id AND l.value = 'mk'
LEFT JOIN enrollment e ON cv.course_version_id = e.course_version_id
WHERE cv.version_number = (
    SELECT MAX(version_number)
    FROM course_version
    WHERE course_id = c.course_id
)
GROUP BY c.course_id, ct.title_short, cv.course_version_id, cv.version_number, cv.is_active, cv.created_at;
}}}

----

== Валидација на рецензии и рејтинг (Review Validation & Rating) ==

=== Опис на барањата за податочни ограничувања ===

Системот мора да обезбеди дека:
* Корисникот може да остави рецензија само ако enrollment е завршен (completion_date IS NOT NULL)
* Еден корисник може да остави само една рецензија по enrollment
* Рејтингот мора да биде валиден број од 1 до 5
* Сите промени на рецензии се евидентираат во audit табела

=== Имплементација ===

==== Прилагодени домени (Custom Domains) ====

Ограничување дека рејтинг мора да биде во доменот [1, 5]
{{{
CREATE DOMAIN rating_scale AS INTEGER
CHECK (VALUE >= 1 AND VALUE <= 5);

ALTER TABLE review DROP CONSTRAINT IF EXISTS ck_review_rating_range;
ALTER TABLE review ALTER COLUMN rating TYPE rating_scale;
}}}

==== Тригери ====

BEFORE INSERT тригер за review, каде се прекинува внесувањето на review доколку курсот (enrollment) не е завршен.
{{{
CREATE OR REPLACE FUNCTION validate_review_before_insert()
RETURNS TRIGGER AS $$
DECLARE
    v_completion_date DATE;
    v_existing_review_count INTEGER;
BEGIN
    SELECT completion_date INTO v_completion_date
    FROM enrollment
    WHERE enrollment_id = NEW.enrollment_id;
    
    IF v_completion_date IS NULL THEN
        RAISE EXCEPTION 'Cannot create review for incomplete enrollment';
    END IF;
    
    SELECT COUNT(*) INTO v_existing_review_count
    FROM review
    WHERE enrollment_id = NEW.enrollment_id;
    
    IF v_existing_review_count > 0 THEN
        RAISE EXCEPTION 'Review already exists for this enrollment';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_review
BEFORE INSERT ON review
FOR EACH ROW
EXECUTE FUNCTION validate_review_before_insert();
}}}

==== Погледи (Views) ====

Поглед за преглед на секој курс со неговата просечна оценка и дистрибуција на оценки.
{{{
CREATE OR REPLACE VIEW course_average_ratings AS
SELECT
    c.course_id AS course_id,
    ct.title_short AS course_title,
    COUNT(r.review_id) AS total_reviews,
    AVG(r.rating)::NUMERIC(3,2) AS average_rating,
    COUNT(r.review_id) FILTER (WHERE r.rating = 5) AS five_star_count,
    COUNT(r.review_id) FILTER (WHERE r.rating = 4) AS four_star_count,
    COUNT(r.review_id) FILTER (WHERE r.rating = 3) AS three_star_count,
    COUNT(r.review_id) FILTER (WHERE r.rating = 2) AS two_star_count,
    COUNT(r.review_id) FILTER (WHERE r.rating = 1) AS one_star_count
FROM course c
JOIN course_translate ct ON c.course_id = ct.course_id
JOIN language l ON l.id = ct.language_id AND l.value = 'mk'
LEFT JOIN course_version cv ON c.course_id = cv.course_id
LEFT JOIN enrollment e ON cv.course_version_id = e.course_version_id
LEFT JOIN review r ON e.enrollment_id = r.enrollment_id
GROUP BY c.course_id, ct.title_short;
}}}

----

== Следење и ограничување на бесплатни консултации ==

=== Опис на барањата за податочни ограничувања ===

Системот мора да обезбеди дека:
* Корисникот може да користи само една бесплатна консултација
* Може да се креира meeting_email_reminder за бесплатна консултација само ако корисникот сè уште не ја искористил
* После креирање на meeting_email_reminder за бесплатна консултација, флагот has_used_free_consultation автоматски се поставува на TRUE

=== Имплементација ===

==== Тригери ====

BEFORE INSERT тригер на meeting email reminder, каде не смее да се креира нов митинг и потсетување по емаил доколку корисникот веќе имал бесплатна консултација.
{{{
CREATE OR REPLACE FUNCTION check_free_consultation_eligibility()
RETURNS TRIGGER AS $$
DECLARE
    v_has_used_free_consultation BOOLEAN;
BEGIN
    SELECT has_used_free_consultation INTO v_has_used_free_consultation
    FROM "user"
    WHERE user_id = NEW.user_id;
    
    IF v_has_used_free_consultation IS NULL THEN
        RAISE EXCEPTION 'User % does not exist', NEW.user_id;
    END IF;

    IF v_has_used_free_consultation = TRUE THEN
        RAISE EXCEPTION 'User has already used their free consultation';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_free_consultation_before_meeting
BEFORE INSERT ON meeting_email_reminder
FOR EACH ROW
EXECUTE FUNCTION check_free_consultation_eligibility();
}}}

AFTER INSERT тригер на meeting email reminder, каде одкако ќе се закаже состанокот да се маркира дека тој корисник го има искористено своето право за бесплатна консултативна сесија со експерт.
{{{
CREATE OR REPLACE FUNCTION mark_free_consultation_as_used()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE "user"
    SET has_used_free_consultation = TRUE
    WHERE user_id = NEW.user_id
      AND has_used_free_consultation = FALSE;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_mark_free_consultation_used
AFTER INSERT ON meeting_email_reminder
FOR EACH ROW
EXECUTE FUNCTION mark_free_consultation_as_used();
}}}

==== Функции / Stored Procedures ====

Функција која кажува дали корисникот може да закаже бесплатна консултативна сесија со експерт.
{{{
CREATE OR REPLACE FUNCTION can_user_schedule_free_consultation(p_user_id BIGINT)
RETURNS TABLE(
    can_schedule BOOLEAN,
    reason TEXT
) 
AS $$
DECLARE
    v_has_used_free_consultation BOOLEAN;
BEGIN
    SELECT has_used_free_consultation INTO v_has_used_free_consultation
    FROM "user"
    WHERE user_id = p_user_id;
    
    IF v_has_used_free_consultation IS NULL THEN
        RETURN QUERY SELECT FALSE, 'User does not exist'::TEXT;
        RETURN;
    END IF;

    IF v_has_used_free_consultation = TRUE THEN
        RETURN QUERY SELECT FALSE, 'Free consultation already used'::TEXT;
        RETURN;
    END IF;
    
    RETURN QUERY SELECT TRUE, 'Eligible for free consultation'::TEXT;
END;
$$ 
LANGUAGE plpgsql;
}}}