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

== Фунцкии

Функцијата `fn_eligibility_matrix` е дизајнирана да ја пресмета матрицата на компатибилност помеѓу одреден студент и ментор, враќајќи ги резултатите во структуриран JSONB формат. По почетната проверка за постоење на корисниците во системот, таа го анализира бројот на заеднички предмети кои ги слушаат или предаваат, како и бројот на заеднички интереси кои ги делат, преку кои се одредува компатибилност. Исто така, функцијата утврдува дали двајцата корисници припаѓаат на ист факултет. Финалната одлука за подобност се носи врз основа на тоа дали корисниците имаат барем еден заеднички предмет или најмалку два заеднички интереси. Сите овие пресметани метрики се подредени во прегледен објект за понатамошна обработка во апликацискиот слој.

{{{
CREATE OR REPLACE FUNCTION fn_eligibility_matrix(p_student_id BIGINT, p_mentor_id BIGINT)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_shared_subjects INT;
    v_shared_interests INT;
    v_same_faculty BOOLEAN;
    v_student_fac BIGINT;
    v_mentor_fac BIGINT;
    v_is_eligible BOOLEAN;
BEGIN
    -- Проверки дали постојат
    IF NOT EXISTS (SELECT 1 FROM "User" WHERE ID = p_student_id) OR NOT EXISTS (SELECT 1 FROM "User" WHERE ID = p_mentor_id) THEN
        RETURN jsonb_build_object('error', 'Корисниците не постојат');
    END IF;

    -- Заеднички предмети
    SELECT COUNT(*) INTO v_shared_subjects FROM User_Subject us1
    JOIN User_Subject us2 ON us1.SubjectID = us2.SubjectID
    WHERE us1.UserID = p_student_id AND us2.UserID = p_mentor_id;

    -- Заеднички интереси
    SELECT COUNT(*) INTO v_shared_interests FROM User_Interest ui1
    JOIN User_Interest ui2 ON ui1.InterestID = ui2.InterestID
    WHERE ui1.UserID = p_student_id AND ui2.UserID = p_mentor_id;

    -- Факултет
    SELECT FacultyID INTO v_student_fac FROM "User" WHERE ID = p_student_id;
    SELECT FacultyID INTO v_mentor_fac FROM "User" WHERE ID = p_mentor_id;
    v_same_faculty := (v_student_fac = v_mentor_fac);

    -- Логика за подобност
    v_is_eligible := (v_shared_subjects > 0 OR v_shared_interests >= 2);

    RETURN jsonb_build_object(
        'student_id', p_student_id,
        'mentor_id', p_mentor_id,
        'is_eligible', v_is_eligible,
        'metrics', jsonb_build_object(
            'shared_subjects', v_shared_subjects,
            'shared_interests', v_shared_interests,
            'same_faculty', v_same_faculty
        )
    );
END;
$$;

SELECT fn_eligibility_matrix(1, 2);
}}}

----

Функцијата `fn_mentor_bottleneck_score` служи за пресметување на нумерички индекс кој го рефлектира просечното доцнење на одреден ментор при реализација на задачите со неговите студенти. Таа ги анализира сите активни задачи за кои е задолжен менторот, а чиј краен рок е веќе поминат, па го собира вкупниот број на денови на доцнење во однос на тековниот датум. Доколку менторот нема активни задачи кои доцнат, функцијата веднаш враќа вредност нула. Во спротивно, таа го пресметува просечниот број на денови на доцнење по задача и го заокружува овој резултат на две децимали, овозможувајќи лесна детекција на ментори кои претставуваат тесно грло во процесот.

{{{
CREATE OR REPLACE FUNCTION fn_mentor_bottleneck_score(p_mentor_id BIGINT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_total_overdue_days NUMERIC;
    v_active_tasks INT;
BEGIN
    SELECT 
        COALESCE(SUM(EXTRACT(DAY FROM (CURRENT_DATE - t.EndDate))), 0), 
        COUNT(t.ID)
    INTO v_total_overdue_days, v_active_tasks
    FROM Task t
    JOIN Mentorship m ON t.MentorshipID = m.ID
    WHERE m.MentorID = p_mentor_id AND t.Status = 0 AND t.EndDate < CURRENT_DATE;

    IF v_active_tasks = 0 THEN
        RETURN 0.00;
    END IF;

    -- Формула: Просечно доцнење во денови по задача
    RETURN ROUND(v_total_overdue_days / v_active_tasks, 2);
END;
$$;

SELECT fn_mentor_bottleneck_score(2);
}}}

----

Функцијата `fn_predict_completion_date` користи едноставен алгоритам за предвидување на точниот датум и време кога би завршило одредено менторство врз основа на досегашната динамика на работа. Таа го извлекува бројот на завршени и преостанати задачи за менторството, како и датумот кога е започната првата задача. Доколку нема доволно историски податоци, односно ако нема ниту една завршена или преостаната задача, функцијата враќа празна (NULL) вредност. Ако податоците се достапни, таа го пресметува просечното време потребно за извршување на една задача и го множи со бројот на преостанати задачи, додавајќи го овој временски интервал на тековното време за да пресмета крајна временска прогноза.

{{{
CREATE OR REPLACE FUNCTION fn_predict_completion_date(p_mentorship_id BIGINT)
RETURNS TIMESTAMP
LANGUAGE plpgsql
AS $$
DECLARE
    v_completed INT;
    v_pending INT;
    v_avg_days_per_task NUMERIC;
    v_start_date TIMESTAMP;
BEGIN
    SELECT COUNT(*) FILTER (WHERE Status = 1), COUNT(*) FILTER (WHERE Status = 0), MIN(StartDate)
    INTO v_completed, v_pending, v_start_date
    FROM Task WHERE MentorshipID = p_mentorship_id;

    -- Нема доволно податоци
    IF v_completed = 0 OR v_pending = 0 THEN
        RETURN NULL;
    END IF;

    -- Колку денови му требале во просек за една задача
    v_avg_days_per_task := EXTRACT(DAY FROM (CURRENT_TIMESTAMP - v_start_date)) / v_completed;
    
    -- Прогноза: Денес + (Просек * Останати Задачи)
    RETURN CURRENT_TIMESTAMP + (v_avg_days_per_task * v_pending || ' days')::INTERVAL;
END;
$$;

SELECT fn_predict_completion_date(9887604);
}}}

== Тригери