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

== Тригери

Тригерот `trg_anti_spam_chat`, кој ја извршува тригер функцијата `fn_enforce_chat_security`, дејствува пред секое внесување на нова порака во табелата со цел да обезбеди безбедност и заштита од несакани содржини. Првиот чекор на овој механизам е да провери дали корисникот кој се обидува да ја испрати пораката е всушност заведен како студент или ментор во конкретниот разговор, со што се спречува неовластен пристап и упад од надворешни лица. Вториот чекор претставува заштита против спам која пресметува колку пораки испратил истиот корисник во тој разговор во последните 5 минути. Доколку овој број го надмине лимитот од 10 пораки, тригерот автоматски го блокира внесувањето и исфрла безбедносен исклучок.

{{{
CREATE OR REPLACE FUNCTION fn_enforce_chat_security()
RETURNS TRIGGER AS $$
DECLARE
    v_chat_owner_student BIGINT;
    v_chat_owner_mentor BIGINT;
    v_recent_messages INT;
BEGIN
    -- 1. Валидација на пристап (Дали е овластен за овој чет?)
    SELECT StudentID, MentorID INTO v_chat_owner_student, v_chat_owner_mentor 
    FROM Chat WHERE ID = NEW.ChatID;

    IF NEW.UserID != v_chat_owner_student AND NEW.UserID != v_chat_owner_mentor THEN
        RAISE EXCEPTION 'ГРЕШКА: Безбедносен прекршок! Корисникот % не е учесник во чет %.', NEW.UserID, NEW.ChatID;
    END IF;

    -- 2. Anti-Spam Заштита (Макс 10 пораки во 5 минути)
    SELECT COUNT(*) INTO v_recent_messages FROM Message 
    WHERE UserID = NEW.UserID AND ChatID = NEW.ChatID AND Timestamp > (NOW() - INTERVAL '5 minutes');

    IF v_recent_messages >= 10 THEN
        RAISE EXCEPTION 'ПРЕДУПРЕДУВАЊЕ: Го надминавте лимитот за праќање пораки. Обидете се подоцна!';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_anti_spam_chat ON Message;
CREATE TRIGGER trg_anti_spam_chat
BEFORE INSERT ON Message
FOR EACH ROW EXECUTE FUNCTION fn_enforce_chat_security();


-- ТЕСТИРАЊЕ
INSERT INTO Message (Content, isRead, Timestamp, ChatID, UserID) VALUES ('Тест спам порака', false, NOW(), 5, 1);

--INSERT INTO Chat (Title, Topic, Status, "Date", StudentID, MentorID) VALUES ('Тест разговор за спам', 'Комуникација', 0, NOW(), 500, 300);

}}}

----

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

{{{
CREATE OR REPLACE FUNCTION fn_guard_task_timeline()
RETURNS TRIGGER AS $$
BEGIN
    -- Дали EndDate е пред StartDate?
    IF NEW.EndDate <= NEW.StartDate THEN
        RAISE EXCEPTION 'ГРЕШКА: Крајниот рок мора да биде по почетниот датум!';
    END IF;

    -- Ако е ВНЕСУВАЊЕ, не смее крајниот рок да е во минатото
    IF TG_OP = 'INSERT' AND NEW.EndDate < CURRENT_DATE THEN
        RAISE EXCEPTION 'ГРЕШКА: Не можете да креирате нова задача со краен рок во минатото!';
    END IF;

    -- Ако е АЖУРИРАЊЕ и задачата е веќе завршена
    IF TG_OP = 'UPDATE' AND OLD.Status = 1 AND NEW.EndDate != OLD.EndDate THEN
        RAISE EXCEPTION 'ОДБИЕНО: Задачата е веќе завршена. Временските рамки се заклучени!';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_strict_task_timeline ON Task;
CREATE TRIGGER trg_strict_task_timeline
BEFORE INSERT OR UPDATE ON Task
FOR EACH ROW EXECUTE FUNCTION fn_guard_task_timeline();


-- ТЕСТИРАЊЕ
INSERT INTO Task (MentorshipID, description , StartDate, EndDate, Status) VALUES (1, 'Невалидна Задача', NOW(), NOW() - INTERVAL '7 days', 0);

}}}

----

Тригерот `trg_topic_state_machine` ја извршува функцијата `fn_topic_state_guard` пред секое ажурирање на податоците во табелата за предложени теми за менторство со цел да го контролира и заштити животниот циклус на темите. При обид за менување на матичниот предмет на кој му припаѓа веќе креирана тема, овој механизам ја прекинува операцијата и исфрла грешка за да спречи конфузија во наставната програма. Дополнително, тригерот спречува рачно менување на статусот на темата од зафатена во слободна доколку таа тема сè уште е поврзана со некое активно менторство кое содржи незавршени задачи, со што се гарантира дека темите нема предвремено и нелогично да се ослободат во системот.

{{{

CREATE OR REPLACE FUNCTION fn_topic_state_guard()
RETURNS TRIGGER AS $$
DECLARE
    v_active_mentorships INT;
BEGIN
    -- Забрането менување на предмет откако темата е креирана
    IF TG_OP = 'UPDATE' AND OLD.SubjectID != NEW.SubjectID THEN
        RAISE EXCEPTION 'ПРЕДУПРЕДУВАЊЕ: Откако темата е предложена, забрането е менување на нејзиниот предмет!';
    END IF;

    -- Ако некој рачно пробува да ја ослободи темата (isAvailable -> true)
    IF TG_OP = 'UPDATE' AND OLD.isAvailable = false AND NEW.isAvailable = true THEN
        SELECT COUNT(*) INTO v_active_mentorships FROM Mentorship m 
        JOIN Task t ON m.ID = t.MentorshipID
        WHERE m.TopicSuggestionID = NEW.ID AND t.Status != 1;
        
        IF v_active_mentorships > 0 THEN
            RAISE EXCEPTION 'ПРЕДУПРЕДУВАЊЕ: Темата не може да се ослободи бидејќи сè уште е поврзана со активно менторство!';
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_topic_state_machine ON TopicSuggestion;
CREATE TRIGGER trg_topic_state_machine
BEFORE UPDATE ON TopicSuggestion
FOR EACH ROW EXECUTE FUNCTION fn_topic_state_guard();

-- ТЕСТИРАЊЕ 
UPDATE TopicSuggestion SET SubjectID = 99999 WHERE ID = (SELECT ID FROM TopicSuggestion LIMIT 1);
UPDATE TopicSuggestion SET isAvailable = true WHERE ID = (SELECT TopicSuggestionID FROM Mentorship LIMIT 1);
--UPDATE TopicSuggestion SET isAvailable = true WHERE ID = (SELECT m.TopicSuggestionID FROM Mentorship m JOIN Task t ON m.ID = t.MentorshipID WHERE t.Status != 1 LIMIT 1);

}}}