Changes between Initial Version and Version 1 of AdvancedDatabaseDevelopment

Timestamp:

05/24/26 17:35:04 (43 hours ago)

Author:

202033

Comment:

--

AdvancedDatabaseDevelopment

@@ +1 @@
+== Валидација на термини (Appointment Validation) ==
+
+=== Опис на барањата за податочни ограничувања ===
+
+Имплементираните правила се:
+
+ * термин не смее да биде во минато,
+ * `end_time` мора да е после `appointment_time`,
+ * терминот мора да е во availability прозорец (`is_closed = false`),
+ * не е дозволено преклопување со друг ''SCHEDULED'' термин,
+ * при креирање мора да има барем една услуга,
+ * `end_time` и `total_price` автоматски се пресметуваат од услуги,
+ * валидацијата се извршува на INSERT/UPDATE.
+
+=== Имплементација ===
+
+==== `fn_validate_appointment` ====
+
+ * '''Име:''' `fn_validate_appointment`
+ * '''Работи над:''' `appointment`, `availability`, `status`
+ * '''Бизнис правило:''' временска валидност, availability валидација, anti-overlap.
+
+{{{
+#!sql
+CREATE OR REPLACE FUNCTION fn_validate_appointment(p_appointment_id int)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_start timestamp;
+    v_end timestamp;
+    v_date date;
+    v_overlap_count int;
+    v_avail_count int;
+BEGIN
+
+    SELECT appointment_time, end_time
+    INTO v_start, v_end
+    FROM appointment
+    WHERE appointment_id = p_appointment_id;
+
+    IF v_start IS NULL OR v_end IS NULL THEN
+        RAISE EXCEPTION 'Appointment must have a start and end time';
+    END IF;
+
+    IF v_end <= v_start THEN
+        RAISE EXCEPTION 'Appointment end time must be after start time';
+    END IF;
+
+    IF v_start < now() THEN
+        RAISE EXCEPTION 'Appointment time must be in the future';
+    END IF;
+
+    v_date := v_start::date;
+
+    SELECT COUNT(*)
+    INTO v_avail_count
+    FROM availability a
+    WHERE a.date = v_date
+      AND a.is_closed = false
+      AND v_start >= (a.date + a.start_time)
+      AND v_end <= (a.date + a.end_time);
+
+    IF v_avail_count = 0 THEN
+        RAISE EXCEPTION 'Appointment is outside availability';
+    END IF;
+
+    SELECT COUNT(*)
+    INTO v_overlap_count
+    FROM appointment ap
+    JOIN status st
+      ON st.status_id = ap.status_id
+    WHERE ap.appointment_id <> p_appointment_id
+      AND UPPER(st.name) = 'SCHEDULED'
+      AND v_start < ap.end_time
+      AND v_end > ap.appointment_time;
+
+    IF v_overlap_count > 0 THEN
+        RAISE EXCEPTION 'Appointment overlaps an existing booking';
+    END IF;
+
+END;
+$$;
+}}}
+
+==== `fn_recalculate_appointment` ====
+
+ * '''Име:''' `fn_recalculate_appointment`
+ * '''Работи над:''' `appointment`, `appointmentservice`, `service`
+ * '''Бизнис правило:''' автоматско пресметување на `total_price` и `end_time`.
+
+{{{
+#!sql
+CREATE OR REPLACE FUNCTION fn_recalculate_appointment(p_appointment_id int)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_total_minutes int;
+    v_total_price numeric;
+    v_start timestamp;
+BEGIN
+
+    SELECT appointment_time
+    INTO v_start
+    FROM appointment
+    WHERE appointment_id = p_appointment_id
+    FOR UPDATE;
+
+    IF v_start IS NULL THEN
+        RETURN;
+    END IF;
+
+    SELECT
+        COALESCE(SUM(s.duration_minutes), 0)::int,
+        COALESCE(SUM(s.price), 0)::numeric
+    INTO
+        v_total_minutes,
+        v_total_price
+    FROM appointmentservice aps
+    JOIN service s
+      ON s.service_id = aps.service_id
+    WHERE aps.appointment_id = p_appointment_id;
+
+    UPDATE appointment
+    SET
+        total_price = v_total_price,
+        end_time = v_start + make_interval(mins => v_total_minutes)
+    WHERE appointment_id = p_appointment_id;
+
+END;
+$$;
+}}}
+
+==== `sp_create_appointment` ====
+
+ * '''Име:''' `sp_create_appointment`
+ * '''Работи над:''' `service`, `status`, `appointment`, `appointmentservice`
+ * '''Бизнис правило:''' креирање термин со целосна DB валидација и пресметка.
+
+''извадок од функцијата''
+
+{{{
+#!sql
+CREATE OR REPLACE FUNCTION sp_create_appointment(
+    p_user_id int,
+    p_appointment_time timestamp,
+    p_service_ids int[],
+    p_notes text
+)
+RETURNS int
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_appointment_id int;
+    v_status_id int;
+    v_service_count int;
+    v_total_minutes int;
+    v_total_price numeric;
+    v_end_time timestamp;
+    v_enum_type text;
+    v_enum_label text;
+BEGIN
+
+    IF p_service_ids IS NULL
+       OR array_length(p_service_ids, 1) IS NULL THEN
+        RAISE EXCEPTION 'At least one service is required';
+    END IF;
+
+    SELECT COUNT(*)
+    INTO v_service_count
+    FROM service
+    WHERE service_id = ANY(p_service_ids);
+
+    IF v_service_count <> array_length(p_service_ids, 1) THEN
+        RAISE EXCEPTION 'One or more services not found';
+    END IF;
+
+    v_total_minutes := fn_service_total_minutes(p_service_ids);
+    v_total_price := fn_service_total_price(p_service_ids);
+
+    v_end_time :=
+        p_appointment_time
+        + make_interval(mins => v_total_minutes);
+
+    SELECT status_id
+    INTO v_status_id
+    FROM status
+    WHERE UPPER(name) = 'SCHEDULED'
+    LIMIT 1;
+
+    EXECUTE format(
+        'INSERT INTO appointment
+        (
+            appointment_time,
+            end_time,
+            total_price,
+            notes,
+            status_id,
+            user_id,
+            "type"
+        )
+        VALUES ($1,$2,$3,$4,$5,$6,$7::%I)
+        RETURNING appointment_id',
+        v_enum_type
+    )
+    INTO v_appointment_id
+    USING
+        p_appointment_time,
+        v_end_time,
+        v_total_price,
+        p_notes,
+        v_status_id,
+        p_user_id,
+        v_enum_label;
+
+    INSERT INTO appointmentservice
+    (
+        appointment_id,
+        service_id
+    )
+    SELECT
+        v_appointment_id,
+        unnest(p_service_ids);
+
+    PERFORM fn_validate_appointment(v_appointment_id);
+
+    RETURN v_appointment_id;
+
+END;
+$$;
+}}}
+
+==== `trg_validate_appointment` + `appointment_validate` ====
+
+ * '''Имиња:''' `trg_validate_appointment`, `appointment_validate`
+ * '''Работи над:''' `appointment`
+ * '''Бизнис правило:''' задолжителна валидација и на insert и на update.
+
+{{{
+#!sql
+CREATE OR REPLACE FUNCTION trg_validate_appointment()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+    PERFORM fn_validate_appointment(NEW.appointment_id);
+
+    RETURN NEW;
+
+END;
+$$;
+
+DROP TRIGGER IF EXISTS appointment_validate
+ON appointment;
+
+CREATE CONSTRAINT TRIGGER appointment_validate
+AFTER INSERT OR UPDATE
+ON appointment
+DEFERRABLE INITIALLY DEFERRED
+FOR EACH ROW
+EXECUTE FUNCTION trg_validate_appointment();
+}}}
+
+==== `trg_appointmentservice_recalculate` + `appointmentservice_recalculate` ====
+
+ * '''Имиња:''' `trg_appointmentservice_recalculate`, `appointmentservice_recalculate`
+ * '''Работи над:''' `appointmentservice`, `appointment`
+ * '''Бизнис правило:''' секоја промена на услуги во термин автоматски ги рефрешира цена/траење.
+
+{{{
+#!sql
+CREATE OR REPLACE FUNCTION trg_appointmentservice_recalculate()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+    IF TG_OP IN ('INSERT', 'UPDATE') THEN
+        PERFORM fn_recalculate_appointment(NEW.appointment_id);
+    END IF;
+
+    IF TG_OP IN ('DELETE', 'UPDATE') THEN
+        PERFORM fn_recalculate_appointment(OLD.appointment_id);
+    END IF;
+
+    RETURN NULL;
+
+END;
+$$;
+
+DROP TRIGGER IF EXISTS appointmentservice_recalculate
+ON appointmentservice;
+
+CREATE TRIGGER appointmentservice_recalculate
+AFTER INSERT OR UPDATE OR DELETE
+ON appointmentservice
+FOR EACH ROW
+EXECUTE FUNCTION trg_appointmentservice_recalculate();
+}}}