Database Programming

Во оваа фаза се изработени функции, процедури и тригери за основните операции потребни за правилно функционирање на системот.

Процедури

Процедурата sp_request_mentorship служи за процесирање и одобрување на барања за менторство помеѓу студент и ментор. На почеток, таа врши валидација за тоа дали постојат корисниците и избраниот тип на менторство, а истовремено проверува дали предложената тема е слободна и дали му припаѓа на соодветниот ментор. Процедурата осигурува дека студентот навистина го слуша предметот на кој припаѓа темата и наметнува строги лимити, така што му дозволува на студентот да има само едно активно менторство, а на менторот максимум пет активни менторства во исто време. Доколку сите овие услови се исполнети, процедурата го запишува менторството, ја означува темата како зафатена, автоматски отвора нов чет помеѓу учесниците и испраќа системска нотификација до менторот.

CREATE OR REPLACE PROCEDURE sp_request_mentorship(
    p_student_id BIGINT,
    p_mentor_id BIGINT,
    p_topic_id BIGINT,
    p_type_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_subject_id BIGINT;
    v_mentor_active_count INT;
    v_student_active_count INT;
    v_new_mentorship_id BIGINT;
    v_new_chat_id BIGINT;
BEGIN
    -- 1. Валидација на ентитети
    IF NOT EXISTS (SELECT 1 FROM "User" WHERE ID = p_student_id) THEN
        RAISE EXCEPTION 'ГРЕШКА: Студент со ID % не постои!', p_student_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM "User" WHERE ID = p_mentor_id) THEN
        RAISE EXCEPTION 'ГРЕШКА: Ментор со ID % не постои!', p_mentor_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM MentorshipType WHERE ID = p_type_id) THEN
        RAISE EXCEPTION 'ГРЕШКА: Типот на менторство (%) е невалиден!', p_type_id;
    END IF;

    -- 2. Валидација на темата
    SELECT SubjectID INTO v_subject_id FROM TopicSuggestion WHERE ID = p_topic_id AND isAvailable = true AND MentorID = p_mentor_id;
    IF v_subject_id IS NULL THEN
        RAISE EXCEPTION 'ГРЕШКА: Темата % не е слободна, не постои, или не припаѓа на овој ментор!', p_topic_id;
    END IF;

    -- 3. Дали студентот го слуша предметот?
    IF NOT EXISTS (SELECT 1 FROM User_Subject WHERE UserID = p_student_id AND SubjectID = v_subject_id) THEN
        RAISE EXCEPTION 'ОДБИЕНО: Студентот мора да биде запишан на предметот за да ја земе темата!';
    END IF;

    -- 4. Лимит за Студент (макс 1 активно менторство)
    SELECT COUNT(*) INTO v_student_active_count FROM Mentorship m 
    WHERE m.StudentID = p_student_id AND EXISTS (SELECT 1 FROM Task t WHERE t.MentorshipID = m.ID AND t.Status != 1);
    IF v_student_active_count > 0 THEN
        RAISE EXCEPTION 'ОДБИЕНО: Студентот веќе има активно менторство. Мора прво да го заврши!';
    END IF;

    -- 5. Лимит за Ментор (макс 5 активни менторства)
    SELECT COUNT(*) INTO v_mentor_active_count FROM Mentorship m 
    WHERE m.MentorID = p_mentor_id AND EXISTS (SELECT 1 FROM Task t WHERE t.MentorshipID = m.ID AND t.Status != 1);
    IF v_mentor_active_count >= 5 THEN
        RAISE EXCEPTION 'ОДБИЕНО: Менторот го има достигнато максимумот од 5 активни студенти!';
    END IF;

    -- Извршување
    INSERT INTO Mentorship (StudentID, MentorID, TopicSuggestionID, MentorshipTypeID)
    VALUES (p_student_id, p_mentor_id, p_topic_id, p_type_id)
    RETURNING ID INTO v_new_mentorship_id;

    UPDATE TopicSuggestion SET isAvailable = false WHERE ID = p_topic_id;

    -- Автоматско отворање на разговор
    INSERT INTO Chat (Title, Topic, Status, "Date", StudentID, MentorID)
    VALUES ('Официјален чет за менторство #' || v_new_mentorship_id, 'Комуникација за тема ' || p_topic_id, 1, NOW(), p_student_id, p_mentor_id)
    RETURNING ID INTO v_new_chat_id;

    -- Системска нотификација
    INSERT INTO Message (Content, isRead, Timestamp, ChatID, UserID)
    VALUES ('Нотификација: Менторството е успешно започнато!', false, NOW(), v_new_chat_id, p_mentor_id);
   
END;
$$;


CALL sp_request_mentorship(1, 2, 3, 1);

---

Процедурата sp_submit_mentorship_evaluation овозможува поднесување на оцена и коментар од страна на студентот за неговиот ментор по завршување на менторскиот процес. Таа прво ја контролира внесената оцена која мора да биде во опсег од 1 до 5, по што проверува дали наведеното менторство воопшто постои и дали студентот е дел од него. Клучен услов за извршување е сите задачи поврзани со тоа менторство да бидат комплетно завршени, со што се спречува предвремено оценување. На крај, со цел да се зачува интегритетот на податоците, процедурата проверува дали студентот веќе оставил оцена за тој ментор, и доколку нема дупликат, успешно ја запишува новата евалуација во табелата за мислења и коментари.

CREATE OR REPLACE PROCEDURE sp_submit_mentorship_evaluation(
    p_mentorship_id BIGINT,
    p_student_id BIGINT,
    p_rating INT,
    p_comment TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_mentor_id BIGINT;
    v_incomplete_tasks INT;
BEGIN
    -- 1. Валидација на оцена
    IF p_rating < 1 OR p_rating > 5 THEN
        RAISE EXCEPTION 'ГРЕШКА: Оцената на менторот мора да биде помеѓу 1 и 5.';
    END IF;

    -- 2. Валидација на менторството
    SELECT MentorID INTO v_mentor_id FROM Mentorship WHERE ID = p_mentorship_id AND StudentID = p_student_id;
    IF v_mentor_id IS NULL THEN
        RAISE EXCEPTION 'ГРЕШКА: Менторството не постои или студентот не е дел од него!';
    END IF;

    -- 3. Дали е навистина завршено? (Сите задачи мора да се Status = 1)
    SELECT COUNT(*) INTO v_incomplete_tasks FROM Task WHERE MentorshipID = p_mentorship_id AND Status != 1;
    IF v_incomplete_tasks > 0 THEN
        RAISE EXCEPTION 'ОДБИЕНО: Не можете да оцените менторство додека има % незавршени задачи!', v_incomplete_tasks;
    END IF;

    -- 4. Спречување дупликат оценки
    IF EXISTS (SELECT 1 FROM CommentOpinion WHERE StudentID = p_student_id AND MentorID = v_mentor_id) THEN
        RAISE EXCEPTION 'ОДБИЕНО: Веќе имате оставено оцена за овој ментор!';
    END IF;

    -- Внес на оцената
    INSERT INTO CommentOpinion (Comment, MentorRating, Timestamp, Status, StudentID, MentorID)
    VALUES (p_comment, p_rating, NOW(), 1, p_student_id, v_mentor_id);
END;
$$;


CALL sp_submit_mentorship_evaluation(1,1, 5, 'Одличен ментор, премногу ми помогна во целиот процес.');

---

Процедурата sp_audit_inactive_tasks служи за автоматска ревизија на неактивните и задоцнетите задачи во базата на податоци. Таа поминува низ сите задачи кои се сè уште во почетен статус, но чиј краен рок е надминат за повеќе од 30 дена во однос на тековниот датум. За секоја пронајдена задоцнета задача, процедурата автоматски го менува нејзиниот статус во пропадната или истечена и истовремено генерира и испраќа предупредувачка нотификација до соодветниот ментор за затворањето на задачата. По завршувањето на целиот циклус, процедурата печати системски извештај во форма на известување кој го прикажува вкупниот број на успешно ревидирани и затворени задачи.

CREATE OR REPLACE PROCEDURE sp_audit_inactive_tasks()
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
    v_count INT := 0;
BEGIN
    FOR rec IN 
        SELECT t.ID, t.MentorshipID, m.MentorID, m.StudentID 
        FROM Task t
        JOIN Mentorship m ON t.MentorshipID = m.ID
        WHERE t.Status = 0 AND t.EndDate < (CURRENT_DATE - INTERVAL '30 days')
    LOOP
        -- Означи како пропадната
        UPDATE Task SET Status = 3 WHERE ID = rec.ID;
        
        -- Прати предупредување до менторот
        INSERT INTO Notification (Content, Timestamp, isRead, UserToNotifyID, MessageID, CommentOpinionID)
        VALUES ('ПРЕДУПРЕДУВАЊЕ: Задачата #' || rec.ID || ' доцни повеќе од 30 дена и е автоматски затворена!', NOW(), false, rec.MentorID, 1, 1);
        
        v_count := v_count + 1;
    END LOOP;
    
    RAISE NOTICE 'СИСТЕМСКИ ИЗВЕШТАЈ: % задачи се означени како истечени поради пречекорување на дозволеното време.', v_count;
END;
$$;


CALL sp_audit_inactive_tasks();

Фунцкии

Тригери