DatabaseProgramming: functions.sql

CREATE OR REPLACE FUNCTION fn_export_exam_results(
    p_exam_id    integer,
    p_teacher_id integer
)
    RETURNS integer
    LANGUAGE plpgsql AS $$
DECLARE
    v_edition_id  integer;
    v_finished_at timestamp;
    v_data        jsonb;
    v_new_id      integer;
BEGIN

    SELECT
        course_edition_id,
        scheduled_at + (duration_minutes * INTERVAL '1 minute')
    INTO v_edition_id, v_finished_at
    FROM Exam
    WHERE id = p_exam_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Exam % does not exist.', p_exam_id;
    END IF;


    IF NOT EXISTS (
        SELECT 1 FROM Teaches
        WHERE teacher_id       = p_teacher_id
          AND course_edition_id = v_edition_id
    ) THEN
        RAISE EXCEPTION
            'Teacher % does not teach course edition %.',
            p_teacher_id, v_edition_id;
    END IF;

    IF v_finished_at > CURRENT_TIMESTAMP THEN
        RAISE EXCEPTION
            'Exam % has not yet finished (ends at %).', p_exam_id, v_finished_at;
    END IF;

    IF EXISTS (
        SELECT 1 FROM Exam_Results
        WHERE exam_id                   = p_exam_id
          AND teaches_teacher_id        = p_teacher_id
          AND teaches_course_edition_id = v_edition_id
          AND exported_at > CURRENT_TIMESTAMP - INTERVAL '60 seconds'
    ) THEN
        RAISE EXCEPTION
            'An export for exam % by teacher % was already created within the last 60 seconds.',
            p_exam_id, p_teacher_id;
    END IF;

    SELECT jsonb_agg(
                   jsonb_build_object(
                           'index',  s.index,
                           'points', COALESCE(ea.total_points, 0)
                   )
                   ORDER BY s.index
           )
    INTO v_data
    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
             LEFT JOIN Exam_Attempt      ea
                       ON ea.exam_id              = p_exam_id
                           AND ea.course_enrollment_id = ce.id
    WHERE ce.course_edition_id = v_edition_id;

    IF v_data IS NULL OR jsonb_array_length(v_data) = 0 THEN
        RAISE EXCEPTION
            'No enrolled students found for course edition %. Nothing to export.',
            v_edition_id;
    END IF;


    INSERT INTO Exam_Results (
        exported_at,
        teaches_teacher_id,
        teaches_course_edition_id,
        exam_id,
        data
    )
    VALUES (
                       CURRENT_TIMESTAMP,
                       p_teacher_id,
                       v_edition_id,
                       p_exam_id,
                       v_data
           )
    RETURNING id INTO v_new_id;

    RETURN v_new_id;
END;
$$;





CREATE OR REPLACE FUNCTION fn_export_aggregated_results(
    p_teacher_id integer,
    p_edition_id integer
)
    RETURNS integer
    LANGUAGE plpgsql AS $$
DECLARE
    v_data    jsonb;
    v_new_id  integer;
    v_sem_mid date;
    v_sem_start date;
    v_sem_end   date;
BEGIN

    IF NOT EXISTS (
        SELECT 1 FROM Teaches
        WHERE teacher_id = p_teacher_id AND course_edition_id = p_edition_id
    ) THEN
        RAISE EXCEPTION
            'Teacher % does not teach course edition %.', p_teacher_id, p_edition_id;
    END IF;



    IF NOT EXISTS (
        SELECT 1
        FROM Exam e
        WHERE e.course_edition_id = p_edition_id
          AND e.type = 'partial_2'
          AND e.scheduled_at + (e.duration_minutes * INTERVAL '1 minute')
            <= CURRENT_TIMESTAMP
    ) THEN
        RAISE EXCEPTION
            'Cannot export aggregated results for edition % — partial_2 has not yet finished.',
            p_edition_id;
    END IF;


    SELECT
        CASE WHEN ed.semester::int = 1
                 THEN make_date(ed.academic_year, 10, 1)
             ELSE make_date(ed.academic_year + 1, 2, 15)
            END,
        CASE WHEN ed.semester::int = 1
                 THEN make_date(ed.academic_year + 1, 2, 14)
             ELSE make_date(ed.academic_year + 1, 7, 15)
            END
    INTO v_sem_start, v_sem_end
    FROM Course_Edition ed
    WHERE ed.id = p_edition_id;

    v_sem_mid := v_sem_start + (v_sem_end - v_sem_start) / 2;


    WITH


        enrolled AS (
            SELECT DISTINCT
                s.id            AS student_id,
                s.index,
                ce.id           AS ce_id
            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
            WHERE ce.course_edition_id = p_edition_id
        ),


        exam_max AS (
            SELECT
                e.type,
                COALESCE(SUM(ep.points), 0) AS max_pts
            FROM Exam e
                     JOIN Exam_Problem ep ON ep.exam_id = e.id
            WHERE e.course_edition_id = p_edition_id
            GROUP BY e.type
        ),


        p1 AS (
            SELECT ea.course_enrollment_id AS ce_id, ea.total_points
            FROM Exam_Attempt ea
                     JOIN Exam e ON e.id = ea.exam_id
            WHERE e.course_edition_id = p_edition_id
              AND e.type = 'partial_1'
        ),


        p2 AS (
            SELECT ea.course_enrollment_id AS ce_id, ea.total_points
            FROM Exam_Attempt ea
                     JOIN Exam e ON e.id = ea.exam_id
            WHERE e.course_edition_id = p_edition_id
              AND e.type = 'partial_2'
        ),



        sess AS (
            SELECT
                ea.course_enrollment_id AS ce_id,
                MAX(ea.total_points)    AS total_points
            FROM Exam_Attempt ea
                     JOIN Exam e ON e.id = ea.exam_id
            WHERE e.course_edition_id = p_edition_id
              AND e.type IN ('session_jan', 'session_jun', 'session_sep')
              AND e.scheduled_at <= CURRENT_TIMESTAMP
            GROUP BY ea.course_enrollment_id
        ),


        sess_max AS (
            SELECT MAX(max_pts) AS max_pts
            FROM exam_max
            WHERE type IN ('session_jan', 'session_jun', 'session_sep')
        ),


        lab AS (
            SELECT
                esub.course_enrollment_id AS ce_id,
                SUM(esub.grade)           AS lab_pts
            FROM Exercise_Submission esub
                     JOIN Exercise ex ON ex.id = esub.exercise_id
            WHERE ex.course_edition_id = p_edition_id
              AND ex.created_at < v_sem_mid
              AND esub.grade IS NOT NULL
            GROUP BY esub.course_enrollment_id
        ),


        lab_max AS (
            SELECT COALESCE(SUM(ex.max_grade), 0) AS max_pts
            FROM Exercise ex
            WHERE ex.course_edition_id = p_edition_id
              AND ex.created_at < v_sem_mid
        ),


        proj AS (
            SELECT
                esub.course_enrollment_id AS ce_id,
                SUM(esub.grade)           AS proj_pts
            FROM Exercise_Submission esub
                     JOIN Exercise ex ON ex.id = esub.exercise_id
            WHERE ex.course_edition_id = p_edition_id
              AND ex.created_at >= v_sem_mid
              AND esub.grade IS NOT NULL
            GROUP BY esub.course_enrollment_id
        ),


        proj_max AS (
            SELECT COALESCE(SUM(ex.max_grade), 0) AS max_pts
            FROM Exercise ex
            WHERE ex.course_edition_id = p_edition_id
              AND ex.created_at >= v_sem_mid
        ),


        total_max AS (
            SELECT
                COALESCE((SELECT max_pts FROM exam_max WHERE type = 'partial_1'), 0)
                    + COALESCE((SELECT max_pts FROM exam_max WHERE type = 'partial_2'), 0)
                    + COALESCE((SELECT max_pts FROM sess_max), 0)
                    + COALESCE((SELECT max_pts FROM lab_max), 0)
                    + COALESCE((SELECT max_pts FROM proj_max), 0)
                    AS max_pts
        ),


        per_student AS (
            SELECT
                en.index,
                ROUND(COALESCE(p1.total_points, 0)::numeric,  2) AS partial1_pts,
                ROUND(COALESCE(p2.total_points, 0)::numeric,  2) AS partial2_pts,
                ROUND(COALESCE(sess.total_points, NULL)::numeric, 2) AS session_pts,
                ROUND(COALESCE(lab.lab_pts, 0)::numeric,      2) AS lab_pts,
                ROUND(COALESCE(proj.proj_pts, NULL)::numeric, 2) AS project_pts,
                ROUND((
                          COALESCE(p1.total_points,   0)
                              + COALESCE(p2.total_points, 0)
                              + COALESCE(sess.total_points, 0)
                              + COALESCE(lab.lab_pts,     0)
                              + COALESCE(proj.proj_pts,   0)
                          )::numeric, 2)                                      AS total_pts,
                (SELECT max_pts FROM total_max)                     AS max_pts,
                ROUND(
                        100.0 * (
                            COALESCE(p1.total_points,   0)
                                + COALESCE(p2.total_points, 0)
                                + COALESCE(sess.total_points, 0)
                                + COALESCE(lab.lab_pts,     0)
                                + COALESCE(proj.proj_pts,   0)
                            )
                            / NULLIF((SELECT max_pts FROM total_max), 0)
                    , 1)                                                AS percentage
            FROM enrolled en
                     LEFT JOIN p1   ON p1.ce_id   = en.ce_id
                     LEFT JOIN p2   ON p2.ce_id   = en.ce_id
                     LEFT JOIN sess ON sess.ce_id  = en.ce_id
                     LEFT JOIN lab  ON lab.ce_id   = en.ce_id
                     LEFT JOIN proj ON proj.ce_id  = en.ce_id
        )

    SELECT jsonb_agg(
                   jsonb_build_object(
                           'index',        ps.index,
                           'partial1_pts', ps.partial1_pts,
                           'partial2_pts', ps.partial2_pts,
                           'session_pts',  ps.session_pts,
                           'lab_pts',      ps.lab_pts,
                           'project_pts',  ps.project_pts,
                           'total_pts',    ps.total_pts,
                           'max_pts',      ps.max_pts,
                           'percentage',   ps.percentage,
                           'passed',       ps.percentage >= 51,
                           'grade',        fn_percentage_to_grade(ps.percentage)
                   )
                   ORDER BY ps.index
           )
    INTO v_data
    FROM per_student ps;

    IF v_data IS NULL OR jsonb_array_length(v_data) = 0 THEN
        RAISE EXCEPTION
            'No enrolled students found for edition %. Nothing to export.', p_edition_id;
    END IF;

    INSERT INTO Aggregated_Course_Edition_Results (
        exported_at,
        teaches_teacher_id,
        teaches_course_edition_id,
        data
    )
    VALUES (
                       CURRENT_TIMESTAMP,
                       p_teacher_id,
                       p_edition_id,
                       v_data
           )
    RETURNING id INTO v_new_id;

    RETURN v_new_id;
END;
$$;