Changes between Version 2 and Version 3 of AdvancedDB

Timestamp:

03/28/26 12:56:12 (2 weeks ago)

Author:

221296

Comment:

--

AdvancedDB

@@ -175 +175 @@
    AND CURRENT_DATE BETWEEN us.start_date AND us.end_date;
 }}}
-== Data constraints requirements: Appointment owner consistency, no overlap scheduling
+== Data constraints requirements: Certificate generation consistency + one certificate per completed enrollment
 === Data requirements description
-Appointments must obey real scheduling constraints:
-* The responsible_owner_id for an appointment must match the owner of the animal.
-* The same owner cannot have overlapping appointments.
-* The same animal cannot have overlapping appointments.
-* Confirming appointments in the past is blocked.
+Certificates must obey completion and consistency rules:
+* A certificate can only be issued for an enrollment whose completion status is COMPLETED.
+* The same enrollment cannot have more than one certificate.
+* A certificate must always refer to a valid enrollment.
+* Issuing a certificate before course completion is blocked.
 === Implementation
 {{{Triggers}}}
 {{{
-CREATE OR REPLACE FUNCTION petify_trg_appointments_enforce()
+CREATE OR REPLACE FUNCTION olpms_trg_certificate_enforce()
 RETURNS trigger
 LANGUAGE plpgsql
 AS $$
 DECLARE
-  v_animal_owner bigint;
-BEGIN
-  SELECT owner_id INTO v_animal_owner
-  FROM animals
-  WHERE animal_id = NEW.animal_id;
-
-  IF v_animal_owner IS NULL THEN
-    RAISE EXCEPTION 'Animal % not found for appointment', NEW.animal_id;
-  END IF;
-
-  IF NEW.responsible_owner_id <> v_animal_owner THEN
-    RAISE EXCEPTION
-      'Appointment responsible_owner_id (%) must match animals.owner_id (%) for animal %',
-      NEW.responsible_owner_id, v_animal_owner, NEW.animal_id;
-  END IF;
-
-  IF NEW.status = 'CONFIRMED' AND NEW.date_time < now() THEN
-    RAISE EXCEPTION 'Cannot CONFIRM an appointment in the past (date_time=%)', NEW.date_time;
-  END IF;
-
-  IF NEW.status = 'DONE' AND NEW.date_time > now() THEN
-    RAISE EXCEPTION 'Cannot mark DONE for an appointment that is in the future (date_time=%)', NEW.date_time;
-  END IF;
-
-  RETURN NEW;
-END;
-$$;
-
-DO $$
-BEGIN
-  IF NOT EXISTS (
-    SELECT 1
-    FROM information_schema.columns
-    WHERE table_name = 'appointments'
-      AND column_name = 'slot'
-  ) THEN
-    ALTER TABLE appointments
-      ADD COLUMN slot tstzrange
-      GENERATED ALWAYS AS (
-        tstzrange(date_time, date_time + interval '30 minutes', '[)')
-      ) STORED;
-  END IF;
-END $$;
-
-CREATE OR REPLACE FUNCTION petify_trg_appointments_no_overlap()
-RETURNS trigger
-LANGUAGE plpgsql
-AS $$
-BEGIN
-  
-  IF NEW.status NOT IN ('CONFIRMED','DONE') THEN
+    v_completion_status VARCHAR(30);
+BEGIN
+    -- find the status of the referenced enrollment
+    SELECT e.completion_status
+    INTO v_completion_status
+    FROM enrollment e
+    WHERE e.enrollment_id = NEW.enrollment_id;
+
+    IF v_completion_status IS NULL THEN
+        RAISE EXCEPTION 'Enrollment % does not exist or has no completion status', NEW.enrollment_id;
+    END IF;
+
+    -- certificate can only be issued for completed enrollments
+    IF v_completion_status <> 'COMPLETED' THEN
+        RAISE EXCEPTION
+            'Certificate cannot be issued because enrollment % is not COMPLETED',
+            NEW.enrollment_id;
+    END IF;
+
+    -- prevent more than one certificate for the same enrollment
+    IF EXISTS (
+        SELECT 1
+        FROM certificate c
+        WHERE c.enrollment_id = NEW.enrollment_id
+          AND (TG_OP <> 'UPDATE' OR c.certificate_id <> NEW.certificate_id)
+    ) THEN
+        RAISE EXCEPTION
+            'Enrollment % already has a certificate',
+            NEW.enrollment_id;
+    END IF;
+
     RETURN NEW;
-  END IF;
-
-
-  IF EXISTS (
-    SELECT 1
-    FROM appointments a
-    WHERE a.responsible_owner_id = NEW.responsible_owner_id
-      AND a.status IN ('CONFIRMED','DONE')
-      AND a.slot && NEW.slot
-      AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id)
-  ) THEN
-    RAISE EXCEPTION
-      'Overlapping appointment for owner % at %',
-      NEW.responsible_owner_id, NEW.date_time;
-  END IF;
-
- 
-  IF EXISTS (
-    SELECT 1
-    FROM appointments a
-    WHERE a.animal_id = NEW.animal_id
-      AND a.status IN ('CONFIRMED','DONE')
-      AND a.slot && NEW.slot
-      AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id)
-  ) THEN
-    RAISE EXCEPTION
-      'Overlapping appointment for animal % at %',
-      NEW.animal_id, NEW.date_time;
-  END IF;
-
-  RETURN NEW;
-END;
-$$;
-
-
-DROP TRIGGER IF EXISTS trg_appointments_enforce ON appointments;
-CREATE TRIGGER trg_appointments_enforce
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_certificate_enforce ON certificate;
+
+CREATE TRIGGER trg_certificate_enforce
 BEFORE INSERT OR UPDATE
-ON appointments
-FOR EACH ROW
-EXECUTE FUNCTION petify_trg_appointments_enforce();
-
-DROP TRIGGER IF EXISTS trg_appointments_no_overlap ON appointments;
-CREATE TRIGGER trg_appointments_no_overlap
-BEFORE INSERT OR UPDATE
-ON appointments
-FOR EACH ROW
-EXECUTE FUNCTION petify_trg_appointments_no_overlap();
+ON certificate
+FOR EACH ROW
+EXECUTE FUNCTION olpms_trg_certificate_enforce();
 
 }}}
@@ -295 +237 @@
 {{{Views}}}
 {{{
-CREATE OR REPLACE VIEW v_clinic_appointments_monthly AS
+CREATE OR REPLACE VIEW v_certificates_enriched AS
 SELECT
-  clinic_id,
-  date_trunc('month', date_time) AS month,
-  COUNT(*) FILTER (WHERE status='CONFIRMED') AS confirmed_cnt,
-  COUNT(*) FILTER (WHERE status='DONE')      AS done_cnt,
-  COUNT(*) FILTER (WHERE status='NO_SHOW')   AS no_show_cnt,
-  COUNT(*) FILTER (WHERE status='CANCELLED') AS cancelled_cnt,
-  COUNT(*) AS total_cnt
-FROM appointments
-GROUP BY clinic_id, date_trunc('month', date_time)
-ORDER BY month DESC;
+    c.certificate_id,
+    c.enrollment_id,
+    c.issue_date,
+    c.certificate_code,
+    c.status AS certificate_status,
+    e.user_id,
+    ue.first_name,
+    ue.last_name,
+    ue.email,
+    e.course_id,
+    cr.name AS course_name,
+    e.enroll_date,
+    e.completion_status,
+    e.progress_percentage
+FROM certificate c
+JOIN enrollment e
+    ON e.enrollment_id = c.enrollment_id
+JOIN users u
+    ON u.id = e.user_id
+JOIN user_entity ue
+    ON ue.id = u.id
+JOIN course cr
+    ON cr.course_id = e.course_id;
 
 }}}