DatabaseCreation: pure_views.sql

----------
CREATE OR REPLACE VIEW v_student_current_semester AS
SELECT
    s.id                            AS student_id,
    s.index,
    ce.id                           AS course_enrollment_id,
    c.code                          AS course_code,
    c.name                          AS course_name,
    c.credits,
    ed.academic_year,
    ed.semester                     AS edition_semester,


    STRING_AGG(
            m.name || ' ' || m.surname, ', '
            ORDER BY m.surname
    )                               AS teachers,
    sg.grade                        AS current_grade,
    sg.graded_at
FROM Student s
         JOIN Semester_Enrollment se
              ON se.program_enrollment_id IN (
                  SELECT id FROM Program_Enrollment WHERE student_id = s.id
              )
                  AND se.academic_year = 2025
                  AND se.semester::int = 1
         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
         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 Student_Grade sg
                   ON sg.course_enrollment_id = ce.id
GROUP BY
    s.id, s.index, ce.id, c.code, c.name, c.credits,
    ed.academic_year, ed.semester, sg.grade, sg.graded_at;




----------
CREATE OR REPLACE VIEW v_student_upcoming_deadlines AS
SELECT
    ce.id                           AS course_enrollment_id,
    s.id                            AS student_id,
    c.name                          AS course_name,
    'Exercise'                      AS item_type,
    ex.title                        AS item_title,
    ex.deadline::timestamp          AS due_at,
    ex.max_grade,


    CASE WHEN esub.id IS NOT NULL THEN true ELSE false END
                                    AS submitted
FROM Course_Enrollment ce
         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 Course_Edition ed      ON ed.id = ce.course_edition_id
         JOIN Course c                 ON c.code = ed.course_code
         JOIN Exercise ex              ON ex.course_edition_id = ed.id
         LEFT JOIN Exercise_Submission esub
                   ON esub.exercise_id = ex.id
                       AND esub.course_enrollment_id = ce.id
WHERE ex.deadline >= CURRENT_DATE

UNION ALL

SELECT
    ce.id,
    s.id,
    c.name,
    'Exam',
    'Exam — ' || ed.academic_year::text || ' S' || ed.semester::int::text,
    ex2.scheduled_at,
    NULL,
    CASE WHEN ea.course_enrollment_id IS NOT NULL THEN true ELSE false END
FROM Course_Enrollment ce
         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 Course_Edition ed      ON ed.id = ce.course_edition_id
         JOIN Course c                 ON c.code = ed.course_code
         JOIN Exam ex2                 ON ex2.course_edition_id = ed.id
         LEFT JOIN Exam_Attempt ea
                   ON ea.exam_id = ex2.id
                       AND ea.course_enrollment_id = ce.id
WHERE ex2.scheduled_at >= CURRENT_TIMESTAMP

ORDER BY due_at;




----------
CREATE OR REPLACE VIEW v_student_transcript AS
SELECT
    pe.student_id,
    s.index,
    ap.name                         AS program_name,
    ap.code                         AS program_code,
    se.academic_year,
    se.semester::int                AS semester,
    c.code                          AS course_code,
    c.name                          AS course_name,
    c.credits,
    cur.mandatory,
    cur.semester::int               AS curriculum_semester,
    sg.grade::int                   AS grade,
    sg.graded_at,
    CASE
        WHEN sg.grade IS NOT NULL THEN 'passed'
        WHEN se.academic_year = 2025 AND se.semester::int = 1 THEN 'in_progress'
        ELSE 'failed'
        END                             AS outcome
FROM Program_Enrollment pe
         JOIN Student s                ON s.id = pe.student_id
         JOIN Academic_Program ap    ON ap.code = pe.academic_program_code
         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
         LEFT JOIN Curriculum cur
                   ON cur.course_code = c.code
                       AND cur.academic_program_code = pe.academic_program_code
         LEFT JOIN Student_Grade sg  ON sg.course_enrollment_id = ce.id;




----------
CREATE VIEW v_course_announcements AS
SELECT
    ann.id                          AS announcement_id,
    ann.teaches_course_edition_id   AS edition_id,
    ed.academic_year,
    c.name                          AS course_name,
    m.name || ' ' || m.surname      AS sender_name,
    m.id                            AS sender_id,
    ann.message,
    ann.sent_at,


    r.ann1_id                       AS reply_to_id
FROM Announcement ann
         JOIN Course_Edition ed      ON ed.id = ann.teaches_course_edition_id
         JOIN Course c                 ON c.code = ed.course_code
         JOIN Member m                 ON m.id = ann.teaches_teacher_id
         LEFT JOIN Announcement_Replies r
                   ON r.ann2_id  = ann.id
                       AND r.ann2_ttid = ann.teaches_teacher_id
                       AND r.ann2_ceid = ann.teaches_course_edition_id;




----------
CREATE VIEW v_exercise_grading_queue AS
SELECT
    ex.id                           AS exercise_id,
    ex.course_edition_id            AS edition_id,
    ex.title                        AS exercise_title,
    ex.deadline,
    ex.max_grade,
    esub.id                         AS submission_id,
    esub.course_enrollment_id,
    s.id                            AS student_id,
    s.index                         AS student_index,
    m.name || ' ' || m.surname      AS student_name,
    esub.submitted_at,
    esub.graded_at,
    esub.grade,
    CASE
        WHEN esub.graded_at IS NULL THEN 'pending'
        ELSE 'graded'
        END                             AS status
FROM Exercise ex
         JOIN Exercise_Submission esub ON esub.exercise_id = ex.id
         JOIN Course_Enrollment ce     ON ce.id = esub.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
ORDER BY
    esub.graded_at NULLS FIRST,
    esub.submitted_at;




----------
CREATE OR REPLACE VIEW v_course_prerequisite_chain AS
WITH RECURSIVE prereq_tree AS (

    SELECT
        cp.successor_course_code    AS course_code,
        cp.predecessor_course_code  AS prerequisite_code,
        1                           AS depth,
        ARRAY[cp.predecessor_course_code]
                                    AS path

    FROM Course_Prerequisite cp

    UNION ALL

    SELECT
        pt.course_code,
        cp.predecessor_course_code,
        pt.depth + 1,
        pt.path || cp.predecessor_course_code
    FROM prereq_tree pt
             JOIN Course_Prerequisite cp
                  ON cp.successor_course_code = pt.prerequisite_code
    WHERE cp.predecessor_course_code <> ALL(pt.path)

      AND pt.depth < 20


)
SELECT
    c.code                          AS course_code,
    c.name                          AS course_name,
    pt.prerequisite_code,
    cpre.name                       AS prerequisite_name,
    pt.depth,

    pt.path                         AS prerequisite_path
FROM Course c
         LEFT JOIN prereq_tree pt
                   ON pt.course_code = c.code
         LEFT JOIN Course cpre
                   ON cpre.code = pt.prerequisite_code;




----------
CREATE OR REPLACE VIEW v_student_registration_state AS

WITH active_pe AS (
    SELECT
        pe.id                       AS pe_id,
        pe.student_id,
        pe.academic_program_code
    FROM Program_Enrollment pe
    WHERE pe.date_disenrollment IS NULL
      AND pe.finished IS NOT TRUE
),

     passed_courses AS (
         SELECT
             pe.student_id,
             ed.course_code              AS passed_code
         FROM active_pe pe
                  JOIN Semester_Enrollment se ON se.program_enrollment_id = pe.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 Student_Grade sg       ON sg.course_enrollment_id = ce.id
     ),

     passed_with_equiv AS (
         SELECT student_id, passed_code FROM passed_courses
         UNION
         SELECT pc.student_id, eq.course2_code
         FROM passed_courses pc
                  JOIN Course_Equivalence eq ON eq.course1_code = pc.passed_code
         UNION
         SELECT pc.student_id, eq.course1_code
         FROM passed_courses pc
                  JOIN Course_Equivalence eq ON eq.course2_code = pc.passed_code
     ),

     current_enrollments AS (
         SELECT
             pe.student_id,
             ed.course_code
         FROM active_pe pe
                  JOIN Semester_Enrollment se
                       ON se.program_enrollment_id = pe.pe_id
                           AND se.academic_year = 2025
                           AND se.semester::int = 1
                  JOIN Course_Enrollment ce     ON ce.semester_enrollment_id = se.id
                  JOIN Course_Edition ed      ON ed.id = ce.course_edition_id
     ),

     current_credits AS (
         SELECT
             pe.student_id,
             COALESCE(SUM(c.credits), 0) AS credits_consumed
         FROM active_pe pe
                  LEFT JOIN Semester_Enrollment se
                            ON se.program_enrollment_id = pe.pe_id
                                AND se.academic_year = 2025
                                AND se.semester::int = 1
                  LEFT JOIN Course_Enrollment ce    ON ce.semester_enrollment_id = se.id
                  LEFT JOIN Course_Edition ed     ON ed.id = ce.course_edition_id
                  LEFT JOIN Course c                ON c.code = ed.course_code
         GROUP BY pe.student_id
     ),

     prereq_check AS (
         SELECT
             ape.student_id,
             cp.successor_course_code    AS course_code,
             COUNT(cp.predecessor_course_code)
                                         AS total_prereqs,
             COUNT(pwe.passed_code)      AS met_prereqs
         FROM active_pe ape
                  CROSS JOIN Course_Prerequisite cp
                  LEFT JOIN passed_with_equiv pwe
                            ON pwe.student_id = ape.student_id
                                AND pwe.passed_code = cp.predecessor_course_code
         GROUP BY ape.student_id, cp.successor_course_code
     )

SELECT
    ape.student_id,
    ape.academic_program_code,
    cur.course_code,
    c.name                          AS course_name,
    c.credits,
    cur.mandatory,
    cur.semester::int               AS curriculum_semester,


    CASE WHEN pwe.passed_code IS NOT NULL THEN true ELSE false END
                                    AS already_passed,

    CASE WHEN ce_now.course_code IS NOT NULL THEN true ELSE false END
                                    AS currently_enrolled,

    CASE
        WHEN pc.total_prereqs IS NULL THEN true

        WHEN pc.met_prereqs = pc.total_prereqs THEN true
        ELSE false
        END                             AS prerequisites_met,

    COALESCE(pc.total_prereqs - pc.met_prereqs, 0)
        AS missing_prereq_count,

    40 - COALESCE(cc.credits_consumed, 0)
        AS credit_headroom,

    CASE
        WHEN (40 - COALESCE(cc.credits_consumed, 0)) >= c.credits
            THEN false ELSE true
        END                             AS would_exceed_cap,

    CASE
        WHEN pwe.passed_code IS NOT NULL              THEN false

        WHEN ce_now.course_code IS NOT NULL           THEN false

        WHEN pc.total_prereqs IS NOT NULL
            AND pc.met_prereqs < pc.total_prereqs        THEN false

        WHEN (40 - COALESCE(cc.credits_consumed,0))
            < c.credits                             THEN false

        ELSE true
        END                             AS can_enroll,

    CASE
        WHEN pwe.passed_code IS NOT NULL
            THEN 'already_passed'
        WHEN ce_now.course_code IS NOT NULL
            THEN 'currently_enrolled'
        WHEN pc.total_prereqs IS NOT NULL
            AND pc.met_prereqs < pc.total_prereqs
            THEN 'prerequisites_not_met'
        WHEN (40 - COALESCE(cc.credits_consumed,0)) < c.credits
            THEN 'credit_cap_exceeded'
        ELSE 'eligible'
        END                             AS enrollment_status

FROM active_pe ape
         JOIN Curriculum cur
              ON cur.academic_program_code = ape.academic_program_code
         JOIN Course c
              ON c.code = cur.course_code
         LEFT JOIN passed_with_equiv pwe
                   ON pwe.student_id = ape.student_id
                       AND pwe.passed_code = cur.course_code
         LEFT JOIN current_enrollments ce_now
                   ON ce_now.student_id = ape.student_id
                       AND ce_now.course_code = cur.course_code
         LEFT JOIN prereq_check pc
                   ON pc.student_id = ape.student_id
                       AND pc.course_code = cur.course_code
         LEFT JOIN current_credits cc
                   ON cc.student_id = ape.student_id;




----------
CREATE OR REPLACE VIEW v_teaching_assistant_eligibility AS

WITH course_passers AS (
    SELECT DISTINCT
        pe.student_id,
        ed.course_code
    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 Student_Grade sg       ON sg.course_enrollment_id = ce.id
),

     currently_enrolled_in AS (
         SELECT DISTINCT
             pe.student_id,
             ce.course_edition_id
         FROM Program_Enrollment pe
                  JOIN Semester_Enrollment se
                       ON se.program_enrollment_id = pe.id
                           AND se.academic_year = 2025
                           AND se.semester::int = 1
                  JOIN Course_Enrollment ce     ON ce.semester_enrollment_id = se.id
     ),

     existing_tas AS (
         SELECT student_id, teacher_id FROM Teaching_Assistant
     )

SELECT
    s.id                            AS student_id,
    s.index                         AS student_index,
    m.name || ' ' || m.surname      AS student_name,
    ed.id                           AS edition_id,
    ed.academic_year,
    ed.semester::int                AS semester,
    c.code                          AS course_code,
    c.name                          AS course_name,

    STRING_AGG(
            tm.name || ' ' || tm.surname, ', '
            ORDER BY tm.surname
    )                               AS eligible_supervisors,

    COUNT(DISTINCT ce_hist.id)      AS times_enrolled

FROM Student s
         JOIN Member m                     ON m.id = s.id
         JOIN course_passers cp              ON cp.student_id = s.id
         JOIN Course_Edition ed
              ON ed.course_code = cp.course_code
                  AND ed.academic_year = 2025
                  AND ed.semester::int = 1
         JOIN Course c                     ON c.code = ed.course_code
         JOIN Teaches t                    ON t.course_edition_id = ed.id
         JOIN Member tm                    ON tm.id = t.teacher_id


         LEFT JOIN Course_Enrollment ce_hist
                   ON ce_hist.course_edition_id IN (
                       SELECT id FROM Course_Edition WHERE course_code = c.code
                   )
                       AND ce_hist.id IN (
                           SELECT ce2.id
                           FROM Semester_Enrollment se2
                                    JOIN Program_Enrollment pe2 ON pe2.id = se2.program_enrollment_id
                                    JOIN Course_Enrollment ce2  ON ce2.semester_enrollment_id = se2.id
                           WHERE pe2.student_id = s.id
                       )
WHERE NOT EXISTS (
    SELECT 1 FROM currently_enrolled_in cei
    WHERE cei.student_id = s.id
      AND cei.course_edition_id = ed.id
)
  AND NOT EXISTS (
    SELECT 1 FROM existing_tas eta
                      JOIN Teaches tt ON tt.teacher_id = eta.teacher_id
    WHERE eta.student_id = s.id
      AND tt.course_edition_id = ed.id
)
GROUP BY
    s.id, s.index, m.name, m.surname,
    ed.id, ed.academic_year, ed.semester,
    c.code, c.name;