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

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

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

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

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

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

BEFORE INSERT тригер на enrollment за автоматско доделување на активната верзија на curse (course version).
{{{
CREATE OR REPLACE FUNCTION set_active_course_version_on_enrollment()
RETURNS TRIGGER 
AS $$
DECLARE
    v_course_version_id INTEGER;
BEGIN
    SELECT cv.id
    INTO v_course_version_id
    FROM course_version cv
    WHERE cv.course_id = NEW.course_id
      AND cv.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=%', NEW.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 INTEGER, p_course_id INTEGER)
RETURNS INTEGER 
AS $$
DECLARE
    v_course_version_id INTEGER;
    v_enrollment_id INTEGER;
BEGIN
    SELECT cv.id
    INTO v_course_version_id
    FROM course_version cv
    WHERE cv.course_id = p_course_id
      AND cv.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_id, course_version_id, enrollment_purchase_date, status)
    VALUES (p_user_id, p_course_id, v_course_version_id, NOW(), 'PENDING')
    RETURNING 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.id AS enrollment_id,
    u.id AS user_id,
    u.name AS user_name,
    c.id AS course_id,
    ct.title_short AS course_title,
    cv.id AS course_version_id,
    cv.version_number AS course_version_number,
    cv.active AS is_version_active,
    e.enrollment_purchase_date AS purchase_date,
    e.status AS enrollment_status
FROM enrollment e
JOIN user u ON e.user_id = u.id
JOIN course c ON e.course_id = c.id
JOIN course_version cv ON e.course_version_id = cv.id
JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'mk';
}}}

----

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

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

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

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

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

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

CREATE TRIGGER trg_ensure_single_active_version
BEFORE UPDATE OF active ON course_version
FOR EACH ROW
WHEN (NEW.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.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.id AS course_id,
    ct.title_short AS course_title,
    cv.id AS version_id,
    cv.version_number,
    cv.active,
    cv.version_creation_date,
    COUNT(DISTINCT e.id) FILTER (WHERE e.completion_date IS NULL) AS active_enrollments,
    COUNT(DISTINCT e.id) AS total_enrollments
FROM course c
JOIN course_version cv ON c.id = cv.course_id
JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'mk'
LEFT JOIN enrollment e ON cv.id = e.course_version_id
WHERE cv.version_number = (
    SELECT MAX(version_number)
    FROM course_version
    WHERE course_id = c.id
)
GROUP BY c.id, ct.title_short, cv.id, cv.version_number, cv.active, cv.version_creation_date;
}}}

----

== Валидација на рецензии и рејтинг (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 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 TIMESTAMP;
    v_existing_review_count INTEGER;
BEGIN
    SELECT completion_date INTO v_completion_date
    FROM enrollment
    WHERE 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.id AS course_id,
    ct.title_short AS course_title,
    COUNT(r.id) AS total_reviews,
    AVG(r.rating)::NUMERIC(3,2) AS average_rating,
    COUNT(r.id) FILTER (WHERE r.rating = 5) AS five_star_count,
    COUNT(r.id) FILTER (WHERE r.rating = 4) AS four_star_count,
    COUNT(r.id) FILTER (WHERE r.rating = 3) AS three_star_count,
    COUNT(r.id) FILTER (WHERE r.rating = 2) AS two_star_count,
    COUNT(r.id) FILTER (WHERE r.rating = 1) AS one_star_count
FROM course c
JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'mk'
LEFT JOIN course_version cv ON c.id = cv.course_id
LEFT JOIN enrollment e ON cv.id = e.course_version_id
LEFT JOIN review r ON e.id = r.enrollment_id
GROUP BY c.id, ct.title_short;
}}}

----

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

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

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

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

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

BEFORE INSERT тригер на meeting reminder, каде не смее да се креира нов митинг и потсетување по емаил доколку корисникот веќе имал бесплатна консултација.
{{{
CREATE OR REPLACE FUNCTION check_free_consultation_eligibility()
RETURNS TRIGGER AS $$
DECLARE
    v_used_free_consultation BOOLEAN;
BEGIN
    SELECT used_free_consultation INTO v_used_free_consultation
    FROM _user
    WHERE id = NEW.user_id;
    
    IF v_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_reminder
FOR EACH ROW
EXECUTE FUNCTION check_free_consultation_eligibility();
}}}

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

CREATE TRIGGER trg_mark_free_consultation_used
AFTER INSERT ON meeting_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 INTEGER)
RETURNS TABLE(
    can_schedule BOOLEAN,
    reason TEXT
) 
AS $$
DECLARE
    v_used_free_consultation BOOLEAN;
BEGIN
    SELECT used_free_consultation INTO v_used_free_consultation
    FROM _user
    WHERE id = p_user_id;
    
    IF v_used_free_consultation = TRUE THEN
        RETURN QUERY SELECT FALSE, 'Free consultation already used';
        RETURN;
    END IF;
    
    RETURN QUERY SELECT TRUE, 'Eligible for free consultation';
END;
$$ 
LANGUAGE plpgsql;
}}}