DatabaseProgramming: triggers.sql

CREATE OR REPLACE FUNCTION fn_trg_academic_program_defaults()
    RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN

    IF NEW.updated_at IS NULL THEN
        NEW.updated_at := NEW.created_at;
    END IF;

    IF TG_OP = 'UPDATE' AND NEW.updated_at < OLD.updated_at THEN
        RAISE EXCEPTION
            'updated_at (%) cannot be set earlier than current updated_at (%).',
            NEW.updated_at, OLD.updated_at;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_academic_program_defaults
    BEFORE INSERT OR UPDATE ON Academic_Program
    FOR EACH ROW
EXECUTE FUNCTION fn_trg_academic_program_defaults();






CREATE OR REPLACE FUNCTION fn_trg_prevent_prereq_cycle()
    RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
    v_cycle_exists boolean;
BEGIN


    WITH RECURSIVE reachable AS (

        SELECT successor_course_code AS node
        FROM Course_Prerequisite
        WHERE predecessor_course_code = NEW.successor_course_code

        UNION

        SELECT cp.successor_course_code
        FROM Course_Prerequisite cp
                 JOIN reachable r ON r.node = cp.predecessor_course_code
    )
    SELECT EXISTS (
        SELECT 1 FROM reachable WHERE node = NEW.predecessor_course_code
    ) INTO v_cycle_exists;

    IF v_cycle_exists THEN
        RAISE EXCEPTION
            'Adding prerequisite % -> % would create a circular dependency.',
            NEW.predecessor_course_code, NEW.successor_course_code;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_prevent_prereq_cycle
    BEFORE INSERT OR UPDATE ON Course_Prerequisite
    FOR EACH ROW
EXECUTE FUNCTION fn_trg_prevent_prereq_cycle();










CREATE OR REPLACE FUNCTION fn_trg_prevent_equiv_duplicate()
    RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN

    IF EXISTS (
        SELECT 1 FROM Course_Equivalence
        WHERE course1_code = NEW.course2_code
          AND course2_code = NEW.course1_code
    ) THEN
        RAISE EXCEPTION
            'Equivalence (%, %) already exists as (%, %) — reverse pair not allowed.',
            NEW.course1_code, NEW.course2_code,
            NEW.course2_code, NEW.course1_code;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_prevent_equiv_duplicate
    BEFORE INSERT ON Course_Equivalence
    FOR EACH ROW
EXECUTE FUNCTION fn_trg_prevent_equiv_duplicate();





CREATE OR REPLACE FUNCTION fn_trg_validate_submission_grade()
    RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
    v_max_grade integer;
BEGIN

    IF NEW.grade IS NULL THEN
        RETURN NEW;
    END IF;

    SELECT max_grade INTO v_max_grade
    FROM Exercise
    WHERE id = NEW.exercise_id;

    IF NEW.grade > v_max_grade THEN
        RAISE EXCEPTION
            'Submission grade (%) exceeds exercise max_grade (%) for exercise %.',
            NEW.grade, v_max_grade, NEW.exercise_id;
    END IF;

    IF NEW.grade < 0 THEN
        RAISE EXCEPTION
            'Submission grade (%) cannot be negative.', NEW.grade;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_validate_submission_grade
    BEFORE INSERT OR UPDATE ON Exercise_Submission
    FOR EACH ROW
EXECUTE FUNCTION fn_trg_validate_submission_grade();





CREATE OR REPLACE FUNCTION fn_trg_validate_survey_response_option()
    RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM Survey_Option
        WHERE survey_id = NEW.survey_id
          AND option    = NEW.option_text
    ) THEN
        RAISE EXCEPTION
            'Response option "%" is not a valid option for survey %.',
            NEW.option_text, NEW.survey_id;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_validate_survey_response_option
    BEFORE INSERT OR UPDATE ON Survey_Response
    FOR EACH ROW
EXECUTE FUNCTION fn_trg_validate_survey_response_option();





CREATE OR REPLACE FUNCTION fn_trg_validate_survey_response_timing()
    RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
    v_open_until timestamp without time zone;
BEGIN
    SELECT open_until INTO v_open_until
    FROM Survey
    WHERE id = NEW.survey_id;


    IF v_open_until IS NOT NULL AND NEW.responded_at > v_open_until THEN
        RAISE EXCEPTION
            'Survey % closed at %. Response submitted at % is not allowed.',
            NEW.survey_id, v_open_until, NEW.responded_at;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_validate_survey_response_timing
    BEFORE INSERT ON Survey_Response
    FOR EACH ROW
EXECUTE FUNCTION fn_trg_validate_survey_response_timing();





CREATE OR REPLACE FUNCTION fn_trg_validate_exam_attempt_timing()
    RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
    v_scheduled_at  timestamp without time zone;
    v_finishes_at   timestamp without time zone;
BEGIN
    SELECT
        scheduled_at,
        scheduled_at + (duration_minutes * INTERVAL '1 minute')
    INTO v_scheduled_at, v_finishes_at
    FROM Exam
    WHERE id = NEW.exam_id;


    IF NEW.submitted_at < v_scheduled_at THEN
        RAISE EXCEPTION
            'Exam attempt submitted_at (%) is before exam scheduled_at (%) for exam %.',
            NEW.submitted_at, v_scheduled_at, NEW.exam_id;
    END IF;



    IF NEW.submitted_at > v_finishes_at + INTERVAL '5 minutes' THEN
        RAISE EXCEPTION
            'Exam attempt submitted_at (%) is more than 5 minutes after exam end (%) for exam %.',
            NEW.submitted_at, v_finishes_at, NEW.exam_id;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_validate_exam_attempt_timing
    BEFORE INSERT OR UPDATE ON Exam_Attempt
    FOR EACH ROW
EXECUTE FUNCTION fn_trg_validate_exam_attempt_timing();





CREATE OR REPLACE FUNCTION fn_trg_validate_course_edition_year()
    RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
    v_course_created_at date;
    v_current_year      smallint;
    v_edition_start     date;
BEGIN
    v_current_year := EXTRACT(YEAR FROM CURRENT_DATE)::smallint;



    IF NEW.academic_year > v_current_year THEN
        RAISE EXCEPTION
            'Course edition academic_year (%) is in the future. Current year: %.',
            NEW.academic_year, v_current_year;
    END IF;


    SELECT created_at INTO v_course_created_at
    FROM Course
    WHERE code = NEW.course_code;


    v_edition_start := CASE NEW.semester::int
                           WHEN 1 THEN make_date(NEW.academic_year::int, 10, 1)
                           WHEN 2 THEN make_date(NEW.academic_year::int + 1, 2, 15)
        END;

    IF v_edition_start < v_course_created_at THEN
        RAISE EXCEPTION
            'Course edition for % (starting %) cannot be before course created_at (%).',
            NEW.course_code, v_edition_start, v_course_created_at;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_validate_course_edition_year
    BEFORE INSERT OR UPDATE ON Course_Edition
    FOR EACH ROW
EXECUTE FUNCTION fn_trg_validate_course_edition_year();

















CREATE OR REPLACE FUNCTION fn_trg_sync_student_grades()
    RETURNS trigger
    LANGUAGE plpgsql AS $$
DECLARE
    v_elem          jsonb;
    v_student_id    integer;
    v_ce_id         integer;
    v_grade         integer;
    v_passed        boolean;
BEGIN

    FOR v_elem IN SELECT * FROM jsonb_array_elements(NEW.data) LOOP

            v_passed := (v_elem->>'passed')::boolean;
            v_grade  := CASE
                            WHEN v_elem->'grade' IS NOT NULL
                                AND jsonb_typeof(v_elem->'grade') <> 'null'
                                THEN (v_elem->>'grade')::integer
                            ELSE NULL
                END;


            CONTINUE WHEN NOT v_passed OR v_grade IS NULL;


            SELECT id INTO v_student_id
            FROM Student
            WHERE index = v_elem->>'index';

            IF v_student_id IS NULL THEN

                CONTINUE;
            END IF;


            SELECT ce.id INTO v_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
            WHERE pe.student_id          = v_student_id
              AND ce.course_edition_id   = NEW.teaches_course_edition_id

            ORDER BY se.academic_year DESC, se.semester::int DESC
            LIMIT 1;

            IF v_ce_id IS NULL THEN
                CONTINUE;
            END IF;


            INSERT INTO Student_Grade (
                course_enrollment_id,
                graded_at,
                grade,
                aggr_course_edition_res_id
            )
            VALUES (
                       v_ce_id,
                       CURRENT_DATE,
                       v_grade::char,
                       NEW.id
                   )
            ON CONFLICT (course_enrollment_id) DO UPDATE
                SET grade                      = EXCLUDED.grade,
                    graded_at                  = EXCLUDED.graded_at,
                    aggr_course_edition_res_id = EXCLUDED.aggr_course_edition_res_id


            WHERE Student_Grade.grade::int <= EXCLUDED.grade::int;

        END LOOP;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_sync_student_grades
    AFTER INSERT
    ON Aggregated_Course_Edition_Results
    FOR EACH ROW
EXECUTE FUNCTION fn_trg_sync_student_grades();