= DatabaseProgramming =

== Overview ==

The database application logic is implemented in PostgreSQL using functions, triggers, and procedures. This approach keeps business logic close to the data, ensuring consistency and reducing application complexity.

== Meta Functions (Schema Validation) ==

=== `fn_validate_exam_data` ===

Validates the JSON structure of `Exam_Results.data`. Ensures the data is an array of objects with required fields (`index`, `points`) and proper types.

=== `fn_validate_aggregated_data` ===

Validates the JSON structure of `Aggregated_Course_Edition_Results.data`. Checks for required fields (`index`, `total_pts`, `max_pts`, `percentage`, `passed`) and validates grade ranges.

These functions are used in `CHECK` constraints on the respective tables:

{{{
#!sql
CONSTRAINT ck_aggregated_results_data CHECK (fn_validate_aggregated_data(data))
}}}

=== `fn_percentage_to_grade` ===

Converts a percentage score to the 6‑10 grading scale:

- 91‑100% → 10
- 81‑90% → 9
- 71‑80% → 8
- 61‑70% → 7
- 51‑60% → 6
- <51% → NULL (fail)

== Triggers ==

=== Data Integrity Triggers ===
|| Trigger || Purpose ||
|| `trg_academic_program_defaults` || Manages `updated_at` timestamps; prevents backward updates
|| `trg_prevent_prereq_cycle` || Prevents circular prerequisite dependencies using a recursive CTE
|| `trg_prevent_equiv_duplicate` || Prevents duplicate symmetric course equivalences
|| `trg_validate_submission_grade` || Ensures submission grade does not exceed exercise `max_grade` and is not negative
|| `trg_validate_survey_response_option` || Ensures survey responses reference valid options
|| `trg_validate_survey_response_timing` || Ensures responses are submitted before the survey closes
|| `trg_validate_exam_attempt_timing` || Validates exam attempt is not before start and not more than 5 min after end
|| `trg_validate_course_edition_year` || Prevents future editions and ensures edition starts after course creation

The `trg_prevent_prereq_cycle` trigger uses a recursive query to detect cycles:

{{{
#!sql
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;
}}}

=== Business Logic Trigger ===

==== `trg_sync_student_grades` ====

Automatically updates student grades when aggregated results are inserted. This trigger:

1. Iterates over the JSON array in data
2. For each student that passed and has a grade, finds the correct `Course_Enrollment`
3. Inserts or updates the `Student_Grade` record (only if the new grade is higher)

This ensures that final grades are propagated from the aggregated results to the individual student grade records.

== Functions (Business Logic) ==

=== `fn_export_exam_results` ===

Exports exam results for a teacher. Performs the following validations:

- Exam exists
- Teacher teaches the corresponding course edition
- Exam has finished
- No re‑export within the last 60 seconds (cooldown)

Then builds a JSON array of student indices and total points (or 0 if no attempt) and inserts into `Exam_Results`. Returns the new ID.

=== `fn_export_aggregated_results` ===

Exports aggregated course results including partial exams, session exams, labs, and projects. It:

- Validates that the teacher teaches the edition
- Checks that partial_2 has finished (prerequisite for aggregation)
- Calculates semester boundaries and mid‑point
- Uses multiple CTEs to compute scores for each student:
  - `partial1_pts` – from `Exam_Attempt` on exams of type `partial_1`
  - `partial2_pts` – from `Exam_Attempt` on exams of type `partial_2`
  - `session_pts` – best of session exams (jan, jun, sep)
  - `lab_pts` – sum of exercise submissions before semester mid‑point
  - `project_pts` – sum of exercise submissions from mid‑point onward

- Computes total points, percentage, pass/fail, and grade via `fn_percentage_to_grade`
- Inserts the aggregated data into `Aggregated_Course_Edition_Results`

== Procedures (Transactional Operations) ==

=== `sp_enroll_student_in_course` ===

Enrolls a student in a course with comprehensive validation:

- Checks for an active program enrollment
- Checks that the student is enrolled in the specified semester
- Verifies that the course edition exists
- Prevents duplicate enrollment
- Uses `fn_student_registration_state` to check prerequisites, credit cap, and other restrictions
- Inserts the `Course_Enrollment` record

Parameters: `p_student_id`, `p_course_code`, `p_academic_year`, `p_semester`; returns `success` and `message`.

=== `sp_submit_exam_attempt` ===

Allows a student to submit an exam attempt with answer validation:

- Validates exam timing (not started, not past deadline + 5 min grace)
- Prevents duplicate submissions
- Validates each answer: problem belongs to the exam, points non‑negative
- Computes total points
- Inserts `Exam_Attempt` and each `Student_Answer` in a transaction

Parameters: `p_exam_id`, `p_course_enrollment_id`, `p_answers` (JSON), returns `success` and `message`.

=== `sp_export_aggregated_results` ===

A wrapper procedure for `fn_export_aggregated_results` that additionally validates the input JSON using `fn_validate_aggregated_data`. Returns `success`, `new_result_id`, and `message`.

=== `sp_graduate_student` ===

Graduates a student by marking their program enrollment as finished:

- Validates that the enrollment is active
- Queries `v_program_completion_status` to count outstanding mandatory courses
- If zero, updates `Program_Enrollment` to set `finished = TRUE` and `date_disenrollment = CURRENT_DATE`

Parameters: `p_program_enrollment_id`, returns `success` and `message`.

== Summary ==

|| '''Type''' || '''Count''' || '''Purpose''' ||
|| Meta Functions || 3 || JSON validation, grade conversion ||
|| View Replacement Functions || 2 || Performance optimization for complex views ||
|| Business Functions || 2 || Exam and aggregated results export ||
|| Data Integrity Triggers || 8 || Enforce business rules at the database level ||
|| Business Logic Trigger || 1 || Auto‑sync grades from aggregated results ||
|| Procedures || 4 || Transactional operations with business logic ||

The trigger‑based approach ensures data integrity is enforced consistently, while functions and procedures encapsulate reusable business logic that can be called from the application layer.