Валидација на термини (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.

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.

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 валидација и пресметка.

извадок од функцијата

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.

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
• Бизнис правило: секоја промена на услуги во термин автоматски ги рефрешира цена/траење.

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();