DatabaseProgramming: meta-functions.sql

-- Validation function for Exam_Results.data
CREATE OR REPLACE FUNCTION fn_validate_exam_data(p_data jsonb)
    RETURNS boolean
    LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
    RETURN (
        jsonb_typeof(p_data) = 'array'
            AND jsonb_array_length(p_data) > 0
            AND NOT EXISTS (
            SELECT 1
            FROM jsonb_array_elements(p_data) AS elem
            WHERE
                elem->>'index' IS NULL
               OR elem->>'index' !~ '^\d{10}$'
               OR (elem->>'points') IS NULL
               OR (elem->>'points')::numeric < 0
        )
        );
END;
$$;


-- Validation function for Aggregated_Course_Edition_Results.data
CREATE OR REPLACE FUNCTION fn_validate_aggregated_data(p_data jsonb)
    RETURNS boolean
    LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
    RETURN (
        jsonb_typeof(p_data) = 'array'
            AND jsonb_array_length(p_data) > 0
            AND NOT EXISTS (
            SELECT 1
            FROM jsonb_array_elements(p_data) AS elem
            WHERE
                elem->>'index' IS NULL
               OR elem->>'index' !~ '^\d{10}$'

               OR (elem->>'total_pts') IS NULL
               OR (elem->>'total_pts')::numeric < 0

               OR (elem->>'max_pts') IS NULL
               OR (elem->>'max_pts')::numeric <= 0

               OR (elem->>'percentage') IS NULL
               OR (elem->>'percentage')::numeric < 0
               OR (elem->>'percentage')::numeric > 100

               OR (elem->'passed') IS NULL
               OR jsonb_typeof(elem->'passed') <> 'boolean'

               OR (
                elem->'grade' IS NOT NULL
                    AND jsonb_typeof(elem->'grade') <> 'null'
                    AND (
                    jsonb_typeof(elem->'grade') <> 'number'
                        OR (elem->>'grade')::int NOT BETWEEN 6 AND 10
                    )
                )
        )
        );
END;
$$;



CREATE OR REPLACE FUNCTION fn_percentage_to_grade(p_pct numeric)
    RETURNS integer
    LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
    RETURN CASE
               WHEN p_pct >= 91 THEN 10
               WHEN p_pct >= 81 THEN 9
               WHEN p_pct >= 71 THEN 8
               WHEN p_pct >= 61 THEN 7
               WHEN p_pct >= 51 THEN 6
               ELSE NULL
        END;
END;
$$;





-- FOR v_student_registration_state VIEW
CREATE OR REPLACE FUNCTION fn_student_registration_state(p_student_id INTEGER)
    RETURNS TABLE (
                      student_id INTEGER,
                      academic_program_code TEXT,
                      course_code TEXT,
                      course_name TEXT,
                      credits INTEGER,
                      mandatory BOOLEAN,
                      curriculum_semester SMALLINT,
                      already_passed BOOLEAN,
                      currently_enrolled BOOLEAN,
                      prerequisites_met BOOLEAN,
                      missing_prereq_count BIGINT,
                      credit_headroom BIGINT,
                      would_exceed_cap BOOLEAN,
                      can_enroll BOOLEAN,
                      enrollment_status TEXT
                  )
    LANGUAGE plpgsql STABLE
AS $$
BEGIN
    RETURN QUERY
        WITH
            active_pe AS (
                SELECT pe.id AS pe_id, pe.academic_program_code
                FROM Program_Enrollment pe
                WHERE pe.student_id = p_student_id
                  AND pe.date_disenrollment IS NULL
                  AND pe.finished IS NOT TRUE
            ),
            passed_courses AS (
                SELECT DISTINCT ed.course_code AS passed_code
                FROM active_pe ape
                         JOIN Semester_Enrollment se ON se.program_enrollment_id = ape.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 passed_code FROM passed_courses
                UNION
                SELECT eq.course2_code
                FROM passed_courses pc
                         JOIN Course_Equivalence eq ON eq.course1_code = pc.passed_code
                UNION
                SELECT eq.course1_code
                FROM passed_courses pc
                         JOIN Course_Equivalence eq ON eq.course2_code = pc.passed_code
            ),
            current_enrollments AS (
                SELECT ed.course_code AS curr_code
                FROM active_pe ape
                         JOIN Semester_Enrollment se ON se.program_enrollment_id = ape.pe_id
                    AND se.academic_year = 2025 AND (se.semester::int) = 1   -- cast to int, compare to 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 COALESCE(SUM(c.credits), 0) AS credits_consumed
                FROM active_pe ape
                         LEFT JOIN Semester_Enrollment se ON se.program_enrollment_id = ape.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
            ),
            prereq_check AS (
                SELECT
                    cur.course_code AS prereq_course,
                    COUNT(cp.predecessor_course_code) AS total_prereqs,
                    COUNT(pwe.passed_code) AS met_prereqs
                FROM active_pe ape
                         CROSS JOIN Curriculum cur
                         LEFT JOIN Course_Prerequisite cp ON cp.successor_course_code = cur.course_code
                         LEFT JOIN passed_with_equiv pwe ON pwe.passed_code = cp.predecessor_course_code
                WHERE cur.academic_program_code = ape.academic_program_code
                GROUP BY cur.course_code
            )
        SELECT
            p_student_id,
            ape.academic_program_code,
            cur.course_code,
            c.name,
            c.credits,
            cur.mandatory,
            cur.semester,
            (pwe.passed_code IS NOT NULL) AS already_passed,
            (ce_now.curr_code IS NOT NULL) AS currently_enrolled,
            COALESCE(pc.total_prereqs IS NULL OR pc.met_prereqs = pc.total_prereqs, TRUE) AS prerequisites_met,
            COALESCE(pc.total_prereqs - pc.met_prereqs, 0) AS missing_prereq_count,
            40 - cc.credits_consumed AS credit_headroom,
            (40 - cc.credits_consumed) < c.credits AS would_exceed_cap,
            CASE
                WHEN pwe.passed_code IS NOT NULL THEN FALSE
                WHEN ce_now.curr_code IS NOT NULL THEN FALSE
                WHEN pc.total_prereqs IS NOT NULL AND pc.met_prereqs < pc.total_prereqs THEN FALSE
                WHEN (40 - cc.credits_consumed) < c.credits THEN FALSE
                ELSE TRUE
                END AS can_enroll,
            CASE
                WHEN pwe.passed_code IS NOT NULL THEN 'already_passed'
                WHEN ce_now.curr_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 - cc.credits_consumed) < c.credits THEN 'credit_cap_exceeded'
                ELSE 'eligible'
                END AS enrollment_status
        FROM active_pe ape
                 CROSS JOIN  Curriculum cur
                 JOIN Course c ON c.code = cur.course_code
                 LEFT JOIN passed_with_equiv pwe ON pwe.passed_code = cur.course_code
                 LEFT JOIN current_enrollments ce_now ON ce_now.curr_code = cur.course_code
                 LEFT JOIN prereq_check pc ON pc.prereq_course = cur.course_code
                 CROSS JOIN current_credits cc
        WHERE cur.academic_program_code = ape.academic_program_code;
END;
$$;





-- FOR v_teaching_assistant_eligibility VIEW
CREATE OR REPLACE FUNCTION fn_teaching_assistant_eligibility(p_edition_id INTEGER)
    RETURNS TABLE (
                      student_id INTEGER,
                      student_index TEXT,
                      student_name TEXT,
                      edition_id INTEGER,
                      academic_year SMALLINT,
                      semester SMALLINT,
                      course_code TEXT,
                      course_name TEXT,
                      eligible_supervisors TEXT,
                      times_enrolled BIGINT
                  )
    LANGUAGE plpgsql STABLE
AS $$
DECLARE
    var_course_code TEXT;
    var_academic_year SMALLINT;
    var_semester SMALLINT;
BEGIN
    SELECT ed.course_code, ed.academic_year, ed.semester
    INTO var_course_code, var_academic_year, var_semester
    FROM  Course_Edition ed
    WHERE ed.id = p_edition_id;

    IF NOT FOUND THEN
        RETURN;
    END IF;

    RETURN QUERY
        WITH
            course_passers AS (
                SELECT DISTINCT pe.student_id
                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
                WHERE ed.course_code = var_course_code
            ),
            currently_enrolled AS (
                SELECT DISTINCT pe.student_id
                FROM Program_Enrollment pe
                         JOIN  Semester_Enrollment se ON se.program_enrollment_id = pe.id
                    AND se.academic_year = var_academic_year
                    AND (se.semester::int) = var_semester   -- compare to SMALLINT variable, cast semester to int
                         JOIN Course_Enrollment ce ON ce.semester_enrollment_id = se.id
                WHERE ce.course_edition_id = p_edition_id
            ),
            existing_tas AS (
                SELECT ta.student_id
                FROM Teaching_Assistant ta
                         JOIN Teaches tt ON tt.teacher_id = ta.teacher_id
                WHERE tt.course_edition_id = p_edition_id
            ),
            supervisors AS (
                SELECT STRING_AGG(m.name || ' ' || m.surname, ', ' ORDER BY m.surname) AS names
                FROM Teaches t
                         JOIN Member m ON m.id = t.teacher_id
                WHERE t.course_edition_id = p_edition_id
            ),
            times_taken AS (
                SELECT
                    pe.student_id,
                    COUNT(DISTINCT ce.id) AS cnt
                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
                WHERE ed.course_code = var_course_code
                GROUP BY pe.student_id
            )
        SELECT
            s.id,
            s.index,
            m.name || ' ' || m.surname,
            p_edition_id,
            var_academic_year,
            var_semester,
            c.code,
            c.name,
            supervisors.names,
            COALESCE(times_taken.cnt, 0)::BIGINT
        FROM course_passers cp
                 JOIN Student s ON s.id = cp.student_id
                 JOIN Member m ON m.id = s.id
                 CROSS JOIN Course c
                 CROSS JOIN supervisors
                 LEFT JOIN currently_enrolled ce ON ce.student_id = cp.student_id
                 LEFT JOIN existing_tas et ON et.student_id = cp.student_id
                 LEFT JOIN times_taken ON times_taken.student_id = cp.student_id
        WHERE ce.student_id IS NULL
          AND et.student_id IS NULL
          AND c.code = var_course_code;
END;
$$;