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

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

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

Системот мора да обезбеди дека:

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

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

Тригери

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

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)

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,
    cv.active,
    e.enrollment_purchase_date,
    e.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)

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

Тригери

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();

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
      AND completion_date IS NULL;
    
    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)

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)

CREATE DOMAIN rating_scale AS INTEGER
CHECK (VALUE >= 1 AND VALUE <= 5);

ALTER TABLE review ALTER COLUMN rating TYPE rating_scale;

Тригери

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();

CREATE OR REPLACE FUNCTION audit_review_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO review_audit (review_id, new_rating, new_comment, action)
        VALUES (NEW.id, NEW.rating, NEW.comment, 'INSERT');
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO review_audit (review_id, old_rating, new_rating, old_comment, new_comment, action)
        VALUES (NEW.id, OLD.rating, NEW.rating, OLD.comment, NEW.comment, 'UPDATE');
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO review_audit (review_id, old_rating, old_comment, action)
        VALUES (OLD.id, OLD.rating, OLD.comment, 'DELETE');
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_review
AFTER INSERT OR UPDATE OR DELETE ON review
FOR EACH ROW
EXECUTE FUNCTION audit_review_changes();

Погледи (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;

Помошни табели (Audit)

CREATE TABLE review_audit (
    id SERIAL PRIMARY KEY,
    review_id INTEGER NOT NULL REFERENCES review(id),
    old_rating INTEGER,
    new_rating INTEGER,
    old_comment TEXT,
    new_comment TEXT,
    changed_at TIMESTAMP DEFAULT NOW(),
    action VARCHAR(20)
);

CREATE INDEX idx_review_audit_review ON review_audit(review_id);
CREATE INDEX idx_review_audit_changed_at ON review_audit(changed_at);

---

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

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

Системот мора да обезбеди дека:

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

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

Тригери

CREATE OR REPLACE FUNCTION check_free_consultation_eligibility()
RETURNS TRIGGER AS $$
DECLARE
    v_used_free_consultation BOOLEAN;
BEGIN
    SELECT user_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();

CREATE OR REPLACE FUNCTION mark_free_consultation_as_used()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE "user"
    SET user_used_free_consultation = TRUE
    WHERE id = NEW.user_id
      AND user_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;
    v_pending_meetings INTEGER;
BEGIN
    SELECT user_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;
    
    SELECT COUNT(*) INTO v_pending_meetings
    FROM meeting_reminder
    WHERE user_id = p_user_id
      AND meeting_at > NOW();
    
    IF v_pending_meetings > 0 THEN
        RETURN QUERY SELECT FALSE, 'Already has pending meeting scheduled';
        RETURN;
    END IF;
    
    RETURN QUERY SELECT TRUE, 'Eligible for free consultation';
END;
$$ LANGUAGE plpgsql;