DatabaseCreation: analytical_views.sql

CREATE VIEW v_course_edition_overview AS
SELECT
    ed.id                           AS edition_id,
    ed.academic_year,
    ed.semester::int                AS semester,
    c.code                          AS course_code,
    c.name                          AS course_name,
    c.credits,
    ap.code                         AS program_code,
    ap.name                         AS program_name,
    cur.mandatory,
    cur.semester::int               AS curriculum_semester,
    COUNT(DISTINCT ce.id)           AS enrolled_students,
    COUNT(DISTINCT t.teacher_id)    AS n_teachers,
    STRING_AGG(
            DISTINCT m.name || ' ' || m.surname, ', '
            ORDER BY m.name || ' ' || m.surname
    )                               AS teacher_names,
    COUNT(DISTINCT lec.id)          AS n_lectures,
    COUNT(DISTINCT ex.id)           AS n_exercises,
    COUNT(DISTINCT exam.id)         AS n_exams,
    COUNT(DISTINCT sv.id)           AS n_surveys
FROM Course_Edition ed
         JOIN Course c                 ON c.code = ed.course_code
         LEFT JOIN Curriculum cur    ON cur.course_code = c.code
         LEFT JOIN Academic_Program ap ON ap.code = cur.academic_program_code
         LEFT JOIN Course_Enrollment ce ON ce.course_edition_id = ed.id
         LEFT JOIN Teaches t           ON t.course_edition_id = ed.id
         LEFT JOIN Teacher tr          ON tr.id = t.teacher_id
         LEFT JOIN Member m            ON m.id = tr.id
         LEFT JOIN Lecture lec         ON lec.course_edition_id = ed.id
         LEFT JOIN Exercise ex         ON ex.course_edition_id = ed.id
         LEFT JOIN Exam exam           ON exam.course_edition_id = ed.id
         LEFT JOIN Survey sv           ON sv.course_edition_id = ed.id
GROUP BY
    ed.id, ed.academic_year, ed.semester,
    c.code, c.name, c.credits,
    ap.code, ap.name, cur.mandatory, cur.semester;






CREATE VIEW v_survey_status AS
SELECT
    sv.id                           AS survey_id,
    sv.course_edition_id            AS edition_id,
    c.name                          AS course_name,
    sv.title                        AS survey_title,
    sv.open_until,
    sv.open_until > CURRENT_TIMESTAMP
                                    AS is_open,
    so.option,
    so.capacity,
    COUNT(sr.course_enrollment_id)  AS response_count,
    so.capacity - COUNT(sr.course_enrollment_id)
                                    AS spots_remaining
FROM Survey sv
         JOIN Course_Edition ed      ON ed.id = sv.course_edition_id
         JOIN Course c                 ON c.code = ed.course_code
         JOIN Survey_Option so         ON so.survey_id = sv.id
         LEFT JOIN Survey_Response sr
                   ON sr.survey_id = sv.id
                       AND sr.option_text = so.option
GROUP BY
    sv.id, sv.course_edition_id, c.name,
    sv.title, sv.open_until, so.option, so.capacity;




CREATE VIEW v_teacher_dashboard AS
SELECT
    tr.id                           AS teacher_id,
    m.name || ' ' || m.surname      AS teacher_name,
    c.code                          AS course_code,
    c.name                          AS course_name,
    ed.id                           AS edition_id,
    ed.academic_year,
    ed.semester::int                AS semester,
    COUNT(DISTINCT ce.id)           AS enrolled_students,

    COUNT(DISTINCT esub.id)
    FILTER (WHERE esub.graded_at IS NULL)
                                    AS ungraded_submissions,

    COUNT(DISTINCT exam.id)
    FILTER (WHERE exam.scheduled_at > CURRENT_TIMESTAMP)
                                    AS upcoming_exams,

    COUNT(DISTINCT sv.id)
    FILTER (WHERE sv.open_until > CURRENT_TIMESTAMP)
                                    AS open_surveys
FROM Teacher tr
         JOIN Member m                 ON m.id = tr.id
         JOIN Teaches t                ON t.teacher_id = tr.id
         JOIN Course_Edition ed      ON ed.id = t.course_edition_id
    AND ed.academic_year = 2025
    AND ed.semester::int = 1
         JOIN Course c                 ON c.code = ed.course_code
         LEFT JOIN Course_Enrollment ce ON ce.course_edition_id = ed.id
         LEFT JOIN Exercise ex         ON ex.course_edition_id = ed.id
         LEFT JOIN Exercise_Submission esub
                   ON esub.exercise_id = ex.id
         LEFT JOIN Exam exam           ON exam.course_edition_id = ed.id
         LEFT JOIN Survey sv           ON sv.course_edition_id = ed.id
GROUP BY
    tr.id, m.name, m.surname,
    c.code, c.name, ed.id, ed.academic_year, ed.semester;



CREATE OR REPLACE VIEW v_exam_results_summary AS
SELECT
    exam.id                         AS exam_id,
    exam.course_edition_id          AS edition_id,
    exam.type                       AS exam_type,
    c.name                          AS course_name,
    exam.scheduled_at,
    exam.duration_minutes,
    ep_totals.max_points,
    ep_totals.problem_count,
    COUNT(DISTINCT ea.course_enrollment_id)
                                    AS attempts,
    ROUND(AVG(ea.total_points)::numeric, 2)
                                    AS avg_score,
    ROUND(MIN(ea.total_points)::numeric, 2)
                                    AS min_score,
    ROUND(MAX(ea.total_points)::numeric, 2)
                                    AS max_score,
    COUNT(DISTINCT ea.course_enrollment_id)
    FILTER (WHERE ea.total_points >= 0.5 * ep_totals.max_points)
                                    AS passed_count,
    ROUND(
            100.0 * COUNT(DISTINCT ea.course_enrollment_id)
                    FILTER (WHERE ea.total_points >= 0.5 * ep_totals.max_points)
                / NULLIF(COUNT(DISTINCT ea.course_enrollment_id), 0)
        , 1)                            AS pass_rate_pct
FROM Exam exam
         JOIN Course_Edition ed          ON ed.id = exam.course_edition_id
         JOIN Course c                     ON c.code = ed.course_code

         JOIN LATERAL (
    SELECT
        SUM(points)  AS max_points,
        COUNT(*)     AS problem_count
    FROM Exam_Problem
    WHERE exam_id = exam.id
    ) ep_totals ON true
         LEFT JOIN Exam_Attempt ea         ON ea.exam_id = exam.id
GROUP BY exam.id, exam.course_edition_id, exam.type, c.name,
         exam.scheduled_at, exam.duration_minutes,
         ep_totals.max_points, ep_totals.problem_count;







CREATE OR REPLACE VIEW v_member_inbox AS
SELECT
    msg.member_id2 AS partner_id,
    mp.name || ' ' || mp.surname AS partner_name,
    m.id AS member_id,
    COUNT(*) AS total_messages,
    MAX(msg.sent_at) AS last_message_at,
    SUBSTRING((ARRAY_AGG(msg.message ORDER BY msg.sent_at DESC))[1] FOR 120) AS last_message_preview,
    COUNT(*) FILTER (WHERE msg.sent_at > CURRENT_TIMESTAMP - INTERVAL '7 days') AS recent_messages
FROM Member m
         JOIN Member_Message msg ON msg.member_id1 = m.id
         JOIN Member mp ON mp.id = msg.member_id2
GROUP BY m.id, partner_id, mp.name, mp.surname

UNION ALL

SELECT
    msg.member_id1 AS partner_id,
    mp.name || ' ' || mp.surname AS partner_name,
    m.id AS member_id,
    COUNT(*) AS total_messages,
    MAX(msg.sent_at) AS last_message_at,
    SUBSTRING((ARRAY_AGG(msg.message ORDER BY msg.sent_at DESC))[1] FOR 120) AS last_message_preview,
    COUNT(*) FILTER (WHERE msg.sent_at > CURRENT_TIMESTAMP - INTERVAL '7 days') AS recent_messages
FROM Member m
         JOIN Member_Message msg ON msg.member_id2 = m.id
         JOIN Member mp ON mp.id = msg.member_id1
GROUP BY m.id, partner_id, mp.name, mp.surname;





CREATE VIEW v_program_catalog AS
SELECT
    ap.code,
    ap.name,
    ap.duration_years::int,
    ap.numeric_id,
    COUNT(DISTINCT pe.student_id)
    FILTER (WHERE pe.finished IS NOT TRUE
        AND pe.date_disenrollment IS NULL)
                                    AS active_students,
    COUNT(DISTINCT cur.course_code) AS total_courses,
    COUNT(DISTINCT cur.course_code)
    FILTER (WHERE cur.mandatory)
                                    AS mandatory_courses,
    SUM(DISTINCT c.credits)
    FILTER (WHERE cur.mandatory)
                                    AS mandatory_credits
FROM Academic_Program ap
         LEFT JOIN Program_Enrollment pe   ON pe.academic_program_code = ap.code
         LEFT JOIN Curriculum cur        ON cur.academic_program_code = ap.code
         LEFT JOIN Course c                ON c.code = cur.course_code
GROUP BY ap.code, ap.name, ap.duration_years, ap.numeric_id;





CREATE VIEW v_course_catalog AS
SELECT
    c.code,
    c.name,
    c.credits,

    STRING_AGG(DISTINCT cpre.name, ', ' ORDER BY cpre.name)
                                    AS prerequisites,

    STRING_AGG(DISTINCT ap.name, '; ' ORDER BY ap.name)
                                    AS programs,
    COUNT(DISTINCT ed.id)           AS editions_count,
    MAX(ed.academic_year)           AS last_offered_year
FROM Course c
         LEFT JOIN Course_Prerequisite pr  ON pr.successor_course_code = c.code
         LEFT JOIN Course cpre             ON cpre.code = pr.predecessor_course_code
         LEFT JOIN Curriculum cur        ON cur.course_code = c.code
         LEFT JOIN Academic_Program ap   ON ap.code = cur.academic_program_code
         LEFT JOIN Course_Edition ed     ON ed.course_code = c.code
GROUP BY c.code, c.name, c.credits;















CREATE OR REPLACE VIEW v_exam_attempt_detail AS


WITH exam_max AS (
    SELECT
        exam_id,
        SUM(points)                 AS max_points,
        COUNT(*)                    AS problem_count
    FROM Exam_Problem
    GROUP BY exam_id
),


     attempt_ranks AS (
         SELECT
             exam_id,
             course_enrollment_id,
             attempt_number,
             total_points,
             RANK() OVER (
                 PARTITION BY exam_id
                 ORDER BY total_points DESC
                 )                           AS rank_in_exam,
             COUNT(*) OVER (
                 PARTITION BY exam_id
                 )                           AS total_attempts,
             ROUND(
                     100.0 * PERCENT_RANK() OVER (
                         PARTITION BY exam_id
                         ORDER BY total_points
                         )
             )                           AS percentile
         FROM Exam_Attempt
     )

SELECT

    ea.exam_id,
    exam.scheduled_at,
    exam.duration_minutes,
    ed.course_code,
    c.name                          AS course_name,
    ed.academic_year,
    ed.semester::int                AS edition_semester,


    ea.course_enrollment_id,
    ea.attempt_number,
    ea.submitted_at,


    pe.student_id,
    s.index                         AS student_index,
    m.name || ' ' || m.surname      AS student_name,



    JSON_AGG(
            JSON_BUILD_OBJECT(
                    'problem_id',        sa.exam_problem_id,
                    'points_available',  ep.points,
                    'points_acquired',   sa.points_acquired,
                    'answer_excerpt',    LEFT(sa.answer, 200)
            )
            ORDER BY sa.exam_problem_id
    )                               AS problem_breakdown,


    ea.total_points,
    em.max_points,
    em.problem_count,
    ROUND(
            (100.0 * ea.total_points / NULLIF(em.max_points, 0))::numeric, 1
    ) AS percentage,


    CASE
        WHEN ea.total_points >= 0.5 * em.max_points THEN true
        ELSE false
        END                             AS passed,


    ar.rank_in_exam,
    ar.total_attempts,
    ar.percentile

FROM Exam_Attempt ea
         JOIN Exam exam                    ON exam.id = ea.exam_id
         JOIN Course_Edition ed          ON ed.id = exam.course_edition_id
         JOIN Course c                     ON c.code = ed.course_code
         JOIN exam_max em                    ON em.exam_id = ea.exam_id
         JOIN attempt_ranks ar
              ON ar.exam_id            = ea.exam_id
                  AND ar.course_enrollment_id = ea.course_enrollment_id
                  AND ar.attempt_number    = ea.attempt_number

         JOIN Course_Enrollment ce         ON ce.id = ea.course_enrollment_id
         JOIN Semester_Enrollment se     ON se.id = ce.semester_enrollment_id
         JOIN Program_Enrollment pe        ON pe.id = se.program_enrollment_id
         JOIN Student s                    ON s.id = pe.student_id
         JOIN Member m                     ON m.id = s.id

         LEFT JOIN Student_Answer sa
                   ON sa.exam_id                       = ea.exam_id
                       AND sa.exam_attempt_ceid            = ea.course_enrollment_id
                       AND sa.exam_attempt_attempt_number  = ea.attempt_number
         LEFT JOIN Exam_Problem ep
                   ON ep.exam_id = ea.exam_id
                       AND ep.id     = sa.exam_problem_id
GROUP BY
    ea.exam_id, exam.scheduled_at, exam.duration_minutes,
    ed.course_code, c.name, ed.academic_year, ed.semester,
    ea.course_enrollment_id, ea.attempt_number, ea.submitted_at,
    pe.student_id, s.index, m.name, m.surname,
    ea.total_points, em.max_points, em.problem_count,
    ar.rank_in_exam, ar.total_attempts, ar.percentile;


















CREATE OR REPLACE VIEW v_program_completion_status AS


WITH passed_base AS (
    SELECT
        pe.student_id,
        pe.id                       AS pe_id,
        ed.course_code,
        c.credits
    FROM Program_Enrollment pe
             JOIN Semester_Enrollment se ON se.program_enrollment_id = pe.id
             JOIN Course_Enrollment ce     ON ce.semester_enrollment_id = se.id
             JOIN Course_Edition ed      ON ed.id = ce.course_edition_id
             JOIN Course c                 ON c.code = ed.course_code
             JOIN Student_Grade sg       ON sg.course_enrollment_id = ce.id
),

     passed_expanded AS (

         SELECT student_id, pe_id, course_code, credits FROM passed_base
         UNION

         SELECT pb.student_id, pb.pe_id, eq.course2_code, c2.credits
         FROM passed_base pb
                  JOIN Course_Equivalence eq ON eq.course1_code = pb.course_code
                  JOIN Course c2             ON c2.code = eq.course2_code
         UNION

         SELECT pb.student_id, pb.pe_id, eq.course1_code, c1.credits
         FROM passed_base pb
                  JOIN Course_Equivalence eq ON eq.course2_code = pb.course_code
                  JOIN Course c1             ON c1.code = eq.course1_code
     ),


     outstanding_mandatory AS (
         SELECT
             pe.id                       AS pe_id,
             pe.student_id,
             cur.course_code,
             c.credits,
             cur.semester::int           AS curriculum_semester
         FROM Program_Enrollment pe
                  JOIN Curriculum cur
                       ON cur.academic_program_code = pe.academic_program_code
                           AND cur.mandatory = true
                  JOIN Course c
                       ON c.code = cur.course_code

         WHERE NOT EXISTS (
             SELECT 1 FROM passed_expanded pex
             WHERE pex.student_id = pe.student_id
               AND pex.course_code = cur.course_code
         )
     ),


     earned AS (
         SELECT
             pe_id,
             student_id,

             SUM(DISTINCT credits)       AS credits_earned,
             COUNT(DISTINCT course_code) AS courses_passed
         FROM passed_expanded
         GROUP BY pe_id, student_id
     ),


     program_totals AS (
         SELECT
             ap.code                     AS program_code,
             ap.duration_years::int * 2  AS total_semesters,
             SUM(c.credits)              AS total_credits,
             SUM(c.credits) FILTER (WHERE cur.mandatory)
                                         AS mandatory_credits,
             COUNT(cur.course_code)      AS total_courses,
             COUNT(cur.course_code) FILTER (WHERE cur.mandatory)
                                         AS mandatory_courses
         FROM Academic_Program ap
                  JOIN Curriculum cur         ON cur.academic_program_code = ap.code
                  JOIN Course c                 ON c.code = cur.course_code
         GROUP BY ap.code, ap.duration_years
     ),


     outstanding_summary AS (
         SELECT
             pe_id,
             student_id,
             COUNT(*)                    AS outstanding_mandatory_courses,
             SUM(credits)                AS outstanding_mandatory_credits,

             MIN(curriculum_semester)    AS earliest_outstanding_semester
         FROM outstanding_mandatory
         GROUP BY pe_id, student_id
     ),


     current_sem AS (
         SELECT
             se.program_enrollment_id    AS pe_id,
             MAX(se.academic_year * 2 + se.semester::int - 1)
                                         AS current_sem_offset
         FROM Semester_Enrollment se
         GROUP BY se.program_enrollment_id
     )

SELECT
    pe.id                           AS program_enrollment_id,
    pe.student_id,
    s.index                         AS student_index,
    m.name || ' ' || m.surname      AS student_name,
    pe.academic_program_code,
    ap.name                         AS program_name,
    ap.duration_years::int,
    pe.date_enrollment,


    COALESCE(e.credits_earned, 0)   AS credits_earned,
    pt.total_credits                AS credits_required,
    pt.mandatory_credits            AS mandatory_credits_required,
    GREATEST(0,
             pt.mandatory_credits - COALESCE(e.credits_earned, 0)
    )                               AS credits_remaining,


    COALESCE(e.courses_passed, 0)   AS courses_passed,
    pt.total_courses                AS courses_required,
    COALESCE(os.outstanding_mandatory_courses, 0)
                                    AS outstanding_mandatory_courses,
    COALESCE(os.outstanding_mandatory_credits, 0)
                                    AS outstanding_mandatory_credits,


    LEAST(100, ROUND(
            100.0 * COALESCE(e.credits_earned, 0)
                / NULLIF(pt.mandatory_credits, 0)
        , 1))                           AS completion_pct,



    CASE
        WHEN cs.current_sem_offset IS NULL THEN NULL
        WHEN COALESCE(e.credits_earned, 0) >=
             ROUND(
                     pt.mandatory_credits::numeric
                         * cs.current_sem_offset
                         / NULLIF(pt.total_semesters, 0)
             )
            THEN true
        ELSE false
        END                             AS on_track,




    CASE
        WHEN COALESCE(os.outstanding_mandatory_credits, 0) = 0
            THEN 'eligible_to_graduate'
        ELSE (
                 2025
                     + CEIL(
                         CEIL(
                                 COALESCE(os.outstanding_mandatory_credits, 0)::numeric / 20
                         ) / 2.0
                       )
                 )::text || ' (approx.)'
        END                             AS projected_graduation_year,



    COALESCE(os.earliest_outstanding_semester, pt.total_semesters + 1)
        AS effective_study_semester,


    ARRAY(
            SELECT om.course_code
            FROM outstanding_mandatory om
            WHERE om.pe_id = pe.id
            ORDER BY om.curriculum_semester, om.course_code
    )                               AS outstanding_mandatory_course_codes

FROM Program_Enrollment pe
         JOIN Student s                    ON s.id = pe.student_id
         JOIN Member m                     ON m.id = s.id
         JOIN Academic_Program ap        ON ap.code = pe.academic_program_code
         JOIN program_totals pt              ON pt.program_code = pe.academic_program_code
         LEFT JOIN earned e                  ON e.pe_id = pe.id
         LEFT JOIN outstanding_summary os    ON os.pe_id = pe.id
         LEFT JOIN current_sem cs            ON cs.pe_id = pe.id
WHERE pe.date_disenrollment IS NULL
  AND pe.finished IS NOT TRUE;