DatabaseProgramming: procedures.sql

CREATE OR REPLACE PROCEDURE sp_enroll_student_in_course(
    p_student_id INTEGER,
    p_course_code TEXT,
    p_academic_year SMALLINT,
    p_semester SMALLINT,
    OUT success BOOLEAN,
    OUT message TEXT
)
    LANGUAGE plpgsql AS $$
DECLARE
    v_pe_id INTEGER;
    v_se_id INTEGER;
    v_edition_id INTEGER;
    v_current_credits INTEGER;
    v_course_credits INTEGER;
BEGIN

    SELECT id INTO v_pe_id
    FROM Program_Enrollment
    WHERE student_id = p_student_id
      AND date_disenrollment IS NULL
      AND finished IS NOT TRUE;

    IF v_pe_id IS NULL THEN
        success := FALSE;
        message := 'No active program enrollment found for this student.';
        RETURN;
    END IF;


    SELECT id INTO v_se_id
    FROM Semester_Enrollment
    WHERE program_enrollment_id = v_pe_id
      AND academic_year = p_academic_year
      AND semester::int = p_semester::int;

    IF v_se_id IS NULL THEN
        success := FALSE;
        message := format('Student is not enrolled in semester %s/%s.', p_academic_year, p_semester);
        RETURN;
    END IF;


    SELECT id INTO v_edition_id
    FROM Course_Edition
    WHERE course_code = p_course_code
      AND academic_year = p_academic_year
      AND semester = p_semester;

    IF v_edition_id IS NULL THEN
        success := FALSE;
        message := format('Course edition %s (%s/%s) does not exist.', p_course_code, p_academic_year, p_semester);
        RETURN;
    END IF;


    IF EXISTS (
        SELECT 1 FROM Course_Enrollment
        WHERE semester_enrollment_id = v_se_id
          AND course_edition_id = v_edition_id
    ) THEN
        success := FALSE;
        message := 'Student is already enrolled in this course for this semester.';
        RETURN;
    END IF;



    IF EXISTS (
        SELECT 1 FROM v_student_registration_state
        WHERE student_id = p_student_id
          AND course_code = p_course_code
          AND can_enroll = FALSE
    ) THEN
        SELECT enrollment_status INTO message
        FROM v_student_registration_state
        WHERE student_id = p_student_id
          AND course_code = p_course_code;

        success := FALSE;
        message := 'Prerequisite or other restriction: ' || COALESCE(message, 'unknown');
        RETURN;
    END IF;


    INSERT INTO Course_Enrollment (semester_enrollment_id, course_edition_id)
    VALUES (v_se_id, v_edition_id);

    success := TRUE;
    message := 'Enrollment successful.';
END;
$$;



CREATE OR REPLACE PROCEDURE sp_submit_exam_attempt(
    p_exam_id INTEGER,
    p_course_enrollment_id INTEGER,
    p_answers JSONB,
    OUT success BOOLEAN,
    OUT message TEXT
)
    LANGUAGE plpgsql AS $$
DECLARE
    v_attempt_number SMALLINT;
    v_total_points FLOAT := 0;
    v_max_points FLOAT;
    v_answer_record RECORD;
    v_exam_scheduled TIMESTAMP;
    v_exam_duration SMALLINT;
    v_exam_end TIMESTAMP;
    v_already_submitted BOOLEAN;
BEGIN

    SELECT scheduled_at, duration_minutes INTO v_exam_scheduled, v_exam_duration
    FROM Exam WHERE id = p_exam_id;

    IF v_exam_scheduled IS NULL THEN
        success := FALSE;
        message := 'Exam not found.';
        RETURN;
    END IF;

    v_exam_end := v_exam_scheduled + (v_exam_duration * INTERVAL '1 minute');

    IF CURRENT_TIMESTAMP < v_exam_scheduled THEN
        success := FALSE;
        message := 'Exam has not started yet.';
        RETURN;
    END IF;

    IF CURRENT_TIMESTAMP > v_exam_end + INTERVAL '5 minutes' THEN
        success := FALSE;
        message := 'Exam submission deadline has passed.';
        RETURN;
    END IF;


    SELECT COUNT(*) > 0 INTO v_already_submitted
    FROM Exam_Attempt
    WHERE exam_id = p_exam_id
      AND course_enrollment_id = p_course_enrollment_id;

    IF v_already_submitted THEN
        success := FALSE;
        message := 'You have already submitted an attempt for this exam.';
        RETURN;
    END IF;


    SELECT SUM(points) INTO v_max_points
    FROM Exam_Problem
    WHERE exam_id = p_exam_id;


    FOR v_answer_record IN SELECT * FROM jsonb_to_recordset(p_answers) AS x(problem_id INTEGER, answer TEXT, points_acquired FLOAT)
        LOOP

            IF NOT EXISTS (SELECT 1 FROM Exam_Problem WHERE exam_id = p_exam_id AND id = v_answer_record.problem_id) THEN
                success := FALSE;
                message := format('Problem id %s does not belong to exam %s.', v_answer_record.problem_id, p_exam_id);
                RETURN;
            END IF;
            IF v_answer_record.points_acquired < 0 THEN
                success := FALSE;
                message := 'Points acquired cannot be negative.';
                RETURN;
            END IF;
            v_total_points := v_total_points + v_answer_record.points_acquired;
        END LOOP;


    v_attempt_number := 1;


    INSERT INTO Exam_Attempt (
        exam_id, course_enrollment_id, attempt_number, submitted_at, total_points
    )
    VALUES (
               p_exam_id, p_course_enrollment_id, v_attempt_number, CURRENT_TIMESTAMP, v_total_points
           );


    FOR v_answer_record IN SELECT * FROM jsonb_to_recordset(p_answers) AS x(problem_id INTEGER, answer TEXT, points_acquired FLOAT)
        LOOP
            INSERT INTO Student_Answer (
                exam_id, exam_problem_id, exam_attempt_ceid, exam_attempt_attempt_number,
                points_acquired, answer
            )
            VALUES (
                       p_exam_id, v_answer_record.problem_id, p_course_enrollment_id, v_attempt_number,
                       v_answer_record.points_acquired, v_answer_record.answer
                   );
        END LOOP;

    success := TRUE;
    message := format('Exam submitted. Total points: %s / %s', v_total_points, v_max_points);
END;
$$;




CREATE OR REPLACE PROCEDURE sp_export_aggregated_results(
    p_teacher_id INTEGER,
    p_course_edition_id INTEGER,
    p_data JSONB,
    OUT success BOOLEAN,
    OUT new_result_id INTEGER,
    OUT message TEXT
)
    LANGUAGE plpgsql AS $$
BEGIN

    IF NOT EXISTS (
        SELECT 1 FROM Teaches
        WHERE teacher_id = p_teacher_id AND course_edition_id = p_course_edition_id
    ) THEN
        success := FALSE;
        message := 'Teacher does not teach this course edition.';
        RETURN;
    END IF;


    IF NOT fn_validate_aggregated_data(p_data) THEN
        success := FALSE;
        message := 'Invalid aggregated data format. See fn_validate_aggregated_data for schema.';
        RETURN;
    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_course_edition_id, p_data
           )
    RETURNING id INTO new_result_id;




    success := TRUE;
    message := format('Aggregated results exported with id = %s.', new_result_id);
END;
$$;




CREATE OR REPLACE PROCEDURE sp_graduate_student(
    p_program_enrollment_id INTEGER,
    OUT success BOOLEAN,
    OUT message TEXT
)
    LANGUAGE plpgsql AS $$
DECLARE
    v_outstanding INTEGER;
BEGIN

    IF NOT EXISTS (
        SELECT 1 FROM Program_Enrollment
        WHERE id = p_program_enrollment_id
          AND date_disenrollment IS NULL
          AND finished IS NOT TRUE
    ) THEN
        success := FALSE;
        message := 'Program enrollment not found or already finished.';
        RETURN;
    END IF;


    SELECT outstanding_mandatory_courses INTO v_outstanding
    FROM v_program_completion_status
    WHERE program_enrollment_id = p_program_enrollment_id;

    IF v_outstanding IS NULL THEN
        success := FALSE;
        message := 'Could not compute completion status.';
        RETURN;
    END IF;

    IF v_outstanding > 0 THEN
        success := FALSE;
        message := format('Cannot graduate: %s mandatory course(s) still outstanding.', v_outstanding);
        RETURN;
    END IF;


    UPDATE Program_Enrollment
    SET finished = TRUE, date_disenrollment = CURRENT_DATE
    WHERE id = p_program_enrollment_id;

    success := TRUE;
    message := 'Student has graduated. Program enrollment marked as finished.';
END;
$$;