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