DatabaseCreation: views_najdi_mentor.sql

-- VIEW 1
CREATE OR REPLACE VIEW vw_student_advanced_portfolio AS
WITH StudentStats AS (
    SELECT
        m.StudentID,
        COUNT(DISTINCT m.ID) AS Total_Mentorships,
        COUNT(DISTINCT CASE WHEN t.Status = 1 THEN t.ID END) AS Total_Completed_Tasks,
        COUNT(DISTINCT CASE WHEN t.Status != 1 AND t.EndDate < CURRENT_DATE THEN t.ID END) AS Total_Overdue_Tasks
    FROM Mentorship m
    LEFT JOIN Task t ON m.ID = t.MentorshipID
    GROUP BY m.StudentID
),
SubjectStats AS (
    SELECT us.UserID, COUNT(DISTINCT us.SubjectID) AS Enrolled_Subjects_Count
    FROM User_Subject us
    GROUP BY us.UserID
)
SELECT
    u.ID AS StudentID,
    u.Name::text || ' ' || u.Surname::text AS Student_FullName,
    f.Name AS Faculty_Name,
    COALESCE(ss.Enrolled_Subjects_Count, 0) AS Enrolled_Subjects_Count,
    COALESCE(st.Total_Mentorships, 0) AS Total_Mentorships,
    COALESCE(st.Total_Completed_Tasks, 0) AS Total_Completed_Tasks,
    COALESCE(st.Total_Overdue_Tasks, 0) AS Total_Overdue_Tasks
FROM "User" u
JOIN Faculty f ON u.FacultyID = f.ID
JOIN StudentStats st ON u.ID = st.StudentID
LEFT JOIN SubjectStats ss ON u.ID = ss.UserID;


-- VIEW 2
CREATE OR REPLACE VIEW vw_mentor_conversion_and_success_matrix AS
WITH MentorTaskStats AS (
    SELECT
        m.MentorID,
        COUNT(DISTINCT m.ID) AS Total_Mentorships,
        COUNT(t.ID) AS Total_Tasks_Assigned,
        SUM(CASE WHEN t.Status = 1 THEN 1 ELSE 0 END) AS Successful_Mentorships
    FROM Mentorship m
    LEFT JOIN Task t ON m.ID = t.MentorshipID
    GROUP BY m.MentorID
),
MentorTopicStats AS (
    SELECT
        ts.MentorID,
        COUNT(ts.ID) AS Total_Proposed_Topics,
        COUNT(CASE WHEN ts.isAvailable = false THEN 1 END) AS Accepted_Topics
    FROM TopicSuggestion ts
    GROUP BY ts.MentorID
)
SELECT
    mks.MentorID,
    u.Name::text || ' ' || u.Surname::text AS Mentor_Name,
    COALESCE(mts.Total_Proposed_Topics, 0) AS Total_Proposed_Topics,
    COALESCE(mts.Accepted_Topics, 0) AS Accepted_Topics,
    -- Фалбак 1 при пресметка
    ROUND((COALESCE(mts.Accepted_Topics, 0)::numeric / COALESCE(NULLIF(mts.Total_Proposed_Topics, 0), 1)) * 100, 2) AS Topics_Acceptance_Percentage,
    COALESCE(mks.Total_Mentorships, 0) AS Total_Mentorships,
    COALESCE(mks.Total_Tasks_Assigned, 0) AS Total_Tasks_Assigned,
    COALESCE(mks.Successful_Mentorships, 0) AS Successful_Mentorships,
    RANK() OVER(
        ORDER BY 
            COALESCE(mks.Total_Mentorships, 0) DESC, 
            -- Фалбак 1 при рангирање
            ROUND((COALESCE(mts.Accepted_Topics, 0)::numeric / COALESCE(NULLIF(mts.Total_Proposed_Topics, 0), 1)) * 100, 2) DESC NULLS LAST
    ) AS Mentor_Rank_Position
FROM MentorTaskStats mks
JOIN "User" u ON mks.MentorID = u.ID
LEFT JOIN MentorTopicStats mts ON mks.MentorID = mts.MentorID;


-- VIEW 3
CREATE OR REPLACE VIEW vw_student_progress_and_critical_alerts AS
WITH TaskAggregations AS (
    SELECT
        t.MentorshipID,
        COUNT(t.ID) AS Total_Tasks,
        COUNT(CASE WHEN t.Status = 1 THEN t.ID END) AS Completed_Tasks,
        COUNT(CASE WHEN t.EndDate < CURRENT_DATE AND t.Status != 1 THEN t.ID END) AS Critical_Overdue_Tasks_Count,
        STRING_AGG(
            CASE WHEN t.EndDate < CURRENT_DATE AND t.Status != 1 THEN 
                t.Description::text || ' (' || 
                CASE 
                    WHEN t.Status = 0 THEN 'Активна'
                    WHEN t.Status = 2 THEN 'Прегледана'
                    WHEN t.Status = 3 THEN 'Одбиена'
                    ELSE 'Непознат Статус'
                END || ')'
            ELSE NULL END, ' | '
        ) AS Overdue_Tasks_List
    FROM Task t
    GROUP BY t.MentorshipID
)
SELECT
    m.ID AS MentorshipID,
    stu.Name::text || ' ' || stu.Surname::text AS Student_Name,
    men.Name::text || ' ' || men.Surname::text AS Mentor_Name,
    ts.Name AS Topic_Name,
    COALESCE(ta.Total_Tasks, 0) AS Total_Tasks,
    COALESCE(ta.Completed_Tasks, 0) AS Completed_Tasks,
    -- Фалбак 1 при делење
    ROUND((COALESCE(ta.Completed_Tasks, 0)::numeric / COALESCE(NULLIF(ta.Total_Tasks, 0), 1)) * 100, 2) AS Progress_Percentage,
    COALESCE(ta.Critical_Overdue_Tasks_Count, 0) AS Critical_Overdue_Tasks_Count,
    COALESCE(ta.Overdue_Tasks_List, 'Нема задоцнети задачи') AS Overdue_Tasks_List
FROM Mentorship m
JOIN "User" stu ON m.StudentID = stu.ID
JOIN "User" men ON m.MentorID = men.ID
JOIN TopicSuggestion ts ON m.TopicSuggestionID = ts.ID
LEFT JOIN TaskAggregations ta ON m.ID = ta.MentorshipID;


-- VIEW 4
CREATE OR REPLACE VIEW vw_university_academic_hierarchy AS
WITH SubjectStats AS (
    SELECT
        ts.SubjectID,
        COUNT(DISTINCT us.UserID) AS Enrolled_Students_In_Subject,
        COUNT(DISTINCT ts.ID) AS Total_Topics_Suggested,
        COUNT(DISTINCT m.ID) AS Active_Mentorships_Generated
    FROM TopicSuggestion ts
    LEFT JOIN User_Subject us ON ts.SubjectID = us.SubjectID
    LEFT JOIN Mentorship m ON ts.ID = m.TopicSuggestionID
    GROUP BY ts.SubjectID
)
SELECT
    un.Name AS University_Name,
    f.Name AS Faculty_Name,
    sp.Name AS StudyProgram_Name,
    sub.Name AS Subject_Name,
    COALESCE(ss.Enrolled_Students_In_Subject, 0) AS Enrolled_Students_In_Subject,
    COALESCE(ss.Total_Topics_Suggested, 0) AS Total_Topics_Suggested,
    COALESCE(ss.Active_Mentorships_Generated, 0) AS Active_Mentorships_Generated
FROM University un
JOIN Faculty f ON un.ID = f.UniversityID
JOIN StudyProgram sp ON f.ID = sp.FacultyID
JOIN Subject_StudyProgram ssp ON sp.ID = ssp.StudyProgramID
JOIN Subject sub ON ssp.SubjectID = sub.ID
LEFT JOIN SubjectStats ss ON sub.ID = ss.SubjectID;


-- VIEW 5
CREATE OR REPLACE VIEW vw_system_mentorship_integrity_audit AS
WITH TaskCount AS (
    SELECT t.MentorshipID, COUNT(t.ID) AS Total_Tasks_Generated 
    FROM Task t GROUP BY t.MentorshipID
)
SELECT 
    m.ID AS MentorshipID,
    COALESCE(stu.Name::text || ' ' || stu.Surname::text, 'ИЗБРИШAН СТУДЕНТ') AS Student_Details,
    CASE WHEN stu.ID IS NOT NULL THEN 'Valid' ELSE 'Missing' END AS Student_Exists,
    COALESCE(men.Name::text || ' ' || men.Surname::text, 'ИЗБРИШAН МЕНТОР') AS Mentor_Details,
    CASE WHEN men.ID IS NOT NULL THEN 'Valid' ELSE 'Missing' END AS Mentor_Exists,
    COALESCE(ts.Name, 'Нема доделено tema') AS Topic_Name,
    COALESCE(sub.Name, 'Непознат Предмет') AS Subject_Name,
    COALESCE(tc.Total_Tasks_Generated, 0) AS Total_Tasks_Generated,
    CASE 
        WHEN stu.ID IS NULL AND men.ID IS NULL THEN 'CRITICAL: Целосно изолиран запис (Ghost Record)'
        WHEN stu.ID IS NULL THEN 'ERROR: Недостасува студент'
        WHEN men.ID IS NULL THEN 'ERROR: Недостасува ментор'
        WHEN COALESCE(tc.Total_Tasks_Generated, 0) = 0 THEN 'WARNING: Валидни корисници, но неактивно менторство (0 задачи)'
        ELSE 'HEALTHY: Системот е стабилен'
    END AS System_Health_Status
FROM Mentorship m
LEFT JOIN "User" stu ON m.StudentID = stu.ID
LEFT JOIN "User" men ON m.MentorID = men.ID
LEFT JOIN TopicSuggestion ts ON m.TopicSuggestionID = ts.ID
LEFT JOIN Subject sub ON ts.SubjectID = sub.ID
LEFT JOIN TaskCount tc ON m.ID = tc.MentorshipID;


-- 6
--CREATE OR REPLACE VIEW vw_mentorship_typology_analytics AS
--WITH MentorshipTaskCounts AS (
--    SELECT m.MentorshipTypeID, COUNT(t.ID) AS Total_Tasks_Generated
--    FROM Mentorship m
--    JOIN Task t ON m.ID = t.MentorshipID
--    GROUP BY m.MentorshipTypeID
--)
--SELECT
--    mtp.Type AS Mentorship_Type,
--    ma.Name AS Defining_Attribute,
--    COUNT(DISTINCT m.ID) AS Total_Active_Mentorships,
--    COUNT(DISTINCT m.StudentID) AS Unique_Students_Involved,
--    COUNT(DISTINCT m.MentorID) AS Unique_Mentors_Engaged,
--    COALESCE(mtc.Total_Tasks_Generated, 0) AS Total_Tasks_Generated
--FROM MentorshipType mtp
--JOIN MentorshipAttribute ma ON mtp.MentorshipAttributeID = ma.ID
--LEFT JOIN Mentorship m ON mtp.ID = m.MentorshipTypeID
--LEFT JOIN MentorshipTaskCounts mtc ON mtp.ID = mtc.MentorshipTypeID
--GROUP BY mtp.ID, mtp.Type, ma.Name, mtc.Total_Tasks_Generated;


-- VIEW 6 
CREATE OR REPLACE VIEW vw_chat_analytics AS
SELECT
    c.ID AS chat_id,

    c.Title,

    st.Name || ' ' || st.Surname
        AS student_name,

    mt.Name || ' ' || mt.Surname
        AS mentor_name,

    lm.Content AS last_message,

    lm.Timestamp AS last_message_time,

    unread.unread_count,

    total.total_messages

FROM Chat c

JOIN "User" st
    ON c.StudentID = st.ID

JOIN "User" mt
    ON c.MentorID = mt.ID

LEFT JOIN LATERAL (

    SELECT
        m.Content,
        m.Timestamp

    FROM Message m

    WHERE m.ChatID = c.ID

    ORDER BY m.Timestamp DESC

    LIMIT 1

) lm ON true

LEFT JOIN (

    SELECT
        ChatID,
        COUNT(*) AS unread_count

    FROM Message

    WHERE isRead = false

    GROUP BY ChatID

) unread

ON unread.ChatID = c.ID

LEFT JOIN (

    SELECT
        ChatID,
        COUNT(*) AS total_messages

    FROM Message

    GROUP BY ChatID

) total

ON total.ChatID = c.ID;



-- VIEW 7
CREATE OR REPLACE VIEW vw_mentor_workload_summary AS
WITH PendingTasks AS (
    SELECT m.MentorID, COUNT(t.ID) AS Pending_Tasks
    FROM Mentorship m
    JOIN Task t ON m.ID = t.MentorshipID
    WHERE t.Status = 0
    GROUP BY m.MentorID
)
SELECT
    m.ID AS MentorID,
    m.Name::text || ' ' || m.Surname::text AS Mentor_Name,
    (SELECT COUNT(*) FROM Mentorship m2 WHERE m2.MentorID = m.ID) AS Total_Mentorships,
    COALESCE(pt.Pending_Tasks, 0) AS Pending_Tasks
FROM "User" m
LEFT JOIN PendingTasks pt ON m.ID = pt.MentorID
GROUP BY m.ID, m.Name, m.Surname, pt.Pending_Tasks;


-- VIEW 8
CREATE OR REPLACE VIEW vw_subject_topic_conversion AS
WITH TopicCounts AS (
    SELECT 
        ts.SubjectID,
        COUNT(ts.ID) AS Total_Suggestions,
        COUNT(CASE WHEN ts.isAvailable = false THEN ts.ID END) AS Accepted_Topics
    FROM TopicSuggestion ts
    GROUP BY ts.SubjectID
)
SELECT
    s.Name AS Subject_Name,
    COALESCE(tc.Total_Suggestions, 0) AS Total_Suggestions,
    COALESCE(tc.Accepted_Topics, 0) AS Accepted_Topics,
    ROUND((COALESCE(tc.Accepted_Topics, 0)::numeric / COALESCE(NULLIF(tc.Total_Suggestions, 0), 1)) * 100, 2) AS Conversion_Percentage
FROM Subject s
LEFT JOIN TopicCounts tc ON s.ID = tc.SubjectID;


-- VIEW 9
CREATE OR REPLACE VIEW vw_university_faculty_basic_stats AS
WITH ProgramCounts AS (
    SELECT sp.FacultyID, COUNT(DISTINCT sp.ID) AS Total_Study_Programs
    FROM StudyProgram sp
    GROUP BY sp.FacultyID
)
SELECT
    un.Name AS University_Name,
    f.Name AS Faculty_Name,
    COALESCE(pc.Total_Study_Programs, 0) AS Total_Study_Programs
FROM Faculty f
JOIN University un ON f.UniversityID = un.ID
LEFT JOIN ProgramCounts pc ON f.ID = pc.FacultyID;




-- VIEW 10

CREATE OR REPLACE VIEW vw_notification_center AS
SELECT
    n.ID,
    n.Content,
    n.Timestamp,
    n.isRead,

    u.Name || ' ' || u.Surname
        AS notified_user,

    nt.Type,

    msg.Content AS message_content,

    co.Comment AS opinion_comment

FROM Notification n

JOIN "User" u
    ON n.UserToNotifyID = u.ID

LEFT JOIN Notification_Type nt
    ON n.ID = nt.NotificationID

LEFT JOIN Message msg
    ON n.MessageID = msg.ID

LEFT JOIN CommentOpinion co
    ON n.CommentOpinionID = co.ID;



-- TESTING VIEWS
SELECT * FROM vw_student_advanced_portfolio LIMIT 100;
SELECT * FROM vw_mentor_conversion_and_success_matrix LIMIT 10;
SELECT * FROM vw_student_progress_and_critical_alerts LIMIT 10;
SELECT * FROM vw_university_academic_hierarchy LIMIT 10;
SELECT * FROM vw_system_mentorship_integrity_audit LIMIT 10;
--SELECT * FROM vw_mentorship_typology_analytics LIMIT 10;
SELECT * FROM vw_chat_analytics  LIMIT 10;
SELECT * FROM vw_mentor_workload_summary LIMIT 10;
SELECT * FROM vw_subject_topic_conversion LIMIT 10;
SELECT * FROM vw_university_faculty_basic_stats LIMIT 10;
SELECT * FROM vw_notification_center LIMIT 50;

-- VIEW 2: Ќе ги подреди по ранг на успешни теми 
SELECT * FROM vw_mentor_conversion_and_success_matrix LIMIT 50;

-- VIEW 3: Ќе ги извади само оние што имаат КРИТИЧНИ доцнења 
SELECT * FROM vw_student_progress_and_critical_alerts ORDER BY critical_overdue_tasks_count DESC LIMIT 10;

-- VIEW 5: Ќе  ги извади прво оние каде што СИСТЕМОТ Е СТАБИЛЕН или има ГРЕШКА
SELECT * FROM vw_system_mentorship_integrity_audit ORDER BY system_health_status DESC LIMIT 10;


-- ANALYSIS
-- 1
EXPLAIN ANALYZE 
SELECT * FROM vw_student_advanced_portfolio WHERE studentid = 5446279;
-- 2
EXPLAIN ANALYZE 
SELECT * FROM vw_mentor_conversion_and_success_matrix WHERE mentorid = 2082114;

-- 3
EXPLAIN ANALYZE 
SELECT * FROM vw_student_progress_and_critical_alerts WHERE mentorshipid = 1;

-- 4 
EXPLAIN ANALYZE 
SELECT * FROM vw_university_academic_hierarchy WHERE subject_name = 'Математика 1';