wiki:AdvancedDatabaseDevelopment

Context Navigation

Напреден развој на базата

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.

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

2. Валидација на плаќања (Payment Validation)

Опис на барањата

Имплементирани правила:

payment мора да има target (appointment_id или package_purchase_id),
amount мора да е валиден во контекст на target-от,
дупли PAID не се дозволени (appointment/package purchase),
review е дозволен само за валиден PAID payment.

Имплементација

`payment_target_required` constraint

Име: payment_target_required
Работи над: payment
Бизнис правило: payment без target е невалиден.

ALTER TABLE payment
DROP CONSTRAINT IF EXISTS payment_target_required;

ALTER TABLE payment
ADD CONSTRAINT payment_target_required
CHECK
(
    appointment_id IS NOT NULL
    OR package_purchase_id IS NOT NULL
);

Partial unique indexes за PAID

Имиња: idx_payment_paid_unique, idx_payment_package_purchase_paid_unique
Работи над: payment
Бизнис правило: максимум едно PAID по appointment и едно upfront PAID по package purchase.

CREATE UNIQUE INDEX IF NOT EXISTS idx_payment_paid_unique
ON payment (appointment_id)
WHERE status = 'PAID';

CREATE UNIQUE INDEX IF NOT EXISTS idx_payment_package_purchase_paid_unique
ON payment (package_purchase_id)
WHERE status = 'PAID'
  AND appointment_id IS NULL;

`trg_payment_validate` + `payment_validate`

Имиња: trg_payment_validate, payment_validate
Работи над: payment, appointment, status, userpackagepurchase, package, packageservice, appointmentpackageusage
Бизнис правило: контекстуална валидација на amount/method/points и согласност со package usage.

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

CREATE OR REPLACE FUNCTION trg_payment_validate()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_total_price numeric;
    v_status_name text;
    v_expected numeric;
    v_package_total numeric;
    v_usage_purchase_id int;
BEGIN

    IF NEW.appointment_id IS NULL
       AND NEW.package_purchase_id IS NULL THEN
        RAISE EXCEPTION
            'Payment must reference appointment_id or package_purchase_id';
    END IF;

    IF NEW.appointment_id IS NULL
       AND NEW.package_purchase_id IS NOT NULL THEN

        SELECT
            COALESCE(
                p.total_price,
                package_price.calc_total_price
            )
        INTO v_package_total
        FROM userpackagepurchase upp
        JOIN package p
          ON p.package_id = upp.package_id
        LEFT JOIN LATERAL
        (
            SELECT
                COALESCE(
                    SUM(ps.discounted_price),
                    0
                )::numeric AS calc_total_price
            FROM packageservice ps
            WHERE ps.package_id = p.package_id
        ) package_price
          ON true
        WHERE upp.purchase_id = NEW.package_purchase_id;

        IF COALESCE(NEW.points_used, 0) <> 0 THEN
            RAISE EXCEPTION
                'Package purchase payment cannot redeem loyalty points';
        END IF;

        IF NEW.amount IS DISTINCT FROM v_package_total THEN
            RAISE EXCEPTION
                'Package purchase payment amount must equal package total';
        END IF;

        RETURN NEW;

    END IF;

    IF UPPER(COALESCE(NEW.method, '')) = 'PACKAGE' THEN

        IF NEW.amount IS DISTINCT FROM 0::numeric THEN
            RAISE EXCEPTION
                'PACKAGE appointment payment amount must be 0';
        END IF;

        SELECT apu.purchase_id
        INTO v_usage_purchase_id
        FROM appointmentpackageusage apu
        WHERE apu.appointment_id = NEW.appointment_id
        LIMIT 1;

        IF NEW.package_purchase_id IS NOT NULL
           AND NEW.package_purchase_id <> v_usage_purchase_id THEN
            RAISE EXCEPTION
                'payment.package_purchase_id does not match appointment package usage';
        END IF;

        RETURN NEW;

    END IF;

    SELECT a.total_price, s.name
    INTO v_total_price, v_status_name
    FROM appointment a
    JOIN status s
      ON s.status_id = a.status_id
    WHERE a.appointment_id = NEW.appointment_id;

    IF UPPER(v_status_name) <> 'SCHEDULED' THEN
        RAISE EXCEPTION
            'Payment allowed only for SCHEDULED appointments';
    END IF;

    v_expected :=
        v_total_price
        - COALESCE(NEW.points_used, 0);

    IF NEW.amount IS DISTINCT FROM v_expected THEN
        RAISE EXCEPTION
            'Payment amount must equal appointment total minus points';
    END IF;

    RETURN NEW;

END;
$$;

DROP TRIGGER IF EXISTS payment_validate
ON payment;

CREATE TRIGGER payment_validate
BEFORE INSERT OR UPDATE
ON payment
FOR EACH ROW
EXECUTE FUNCTION trg_payment_validate();

`trg_appointment_prevent_price_change` + `appointment_prevent_price_change`

Имиња: trg_appointment_prevent_price_change, appointment_prevent_price_change
Работи над: appointment, payment
Бизнис правило: после PAID payment не смее да се менува appointment.total_price.

CREATE OR REPLACE FUNCTION trg_appointment_prevent_price_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_has_paid int;
BEGIN

    IF NEW.total_price IS DISTINCT FROM OLD.total_price THEN

        SELECT 1
        INTO v_has_paid
        FROM payment
        WHERE appointment_id = OLD.appointment_id
          AND status = 'PAID'
        LIMIT 1;

        IF v_has_paid IS NOT NULL THEN
            RAISE EXCEPTION
                'Cannot change total_price after a PAID payment';
        END IF;

    END IF;

    RETURN NEW;

END;
$$;

DROP TRIGGER IF EXISTS appointment_prevent_price_change
ON appointment;

CREATE TRIGGER appointment_prevent_price_change
BEFORE UPDATE
ON appointment
FOR EACH ROW
EXECUTE FUNCTION trg_appointment_prevent_price_change();

`sp_create_review`

Име: sp_create_review
Работи над: payment, appointment, review
Бизнис правило: review само за PAID, завршен, сопствен payment, и една review по payment.

CREATE OR REPLACE FUNCTION sp_create_review(
    p_user_id int,
    p_payment_id int,
    p_rating int,
    p_comment text
)
RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
    v_review_id int;
    v_payment_status text;
    v_appointment_user int;
    v_appointment_time timestamp;
    v_end_time timestamp;
BEGIN

    SELECT
        p.status,
        a.user_id,
        a.appointment_time,
        a.end_time
    INTO
        v_payment_status,
        v_appointment_user,
        v_appointment_time,
        v_end_time
    FROM payment p
    JOIN appointment a
      ON a.appointment_id = p.appointment_id
    WHERE p.payment_id = p_payment_id;

    IF v_payment_status <> 'PAID' THEN
        RAISE EXCEPTION
            'Review allowed only for PAID payments';
    END IF;

    IF COALESCE(v_end_time, v_appointment_time) > now() THEN
        RAISE EXCEPTION
            'Review allowed only after appointment is completed';
    END IF;

    IF v_appointment_user <> p_user_id THEN
        RAISE EXCEPTION
            'Unauthorized access to payment';
    END IF;

    IF EXISTS
    (
        SELECT 1
        FROM review
        WHERE payment_id = p_payment_id
    ) THEN
        RAISE EXCEPTION
            'Review already exists for this payment';
    END IF;

    INSERT INTO review
    (
        rating,
        comment,
        payment_id
    )
    VALUES
    (
        p_rating,
        p_comment,
        p_payment_id
    )
    RETURNING review_id
    INTO v_review_id;

    RETURN v_review_id;

END;
$$;

3. Loyalty System

Опис на барањата за податочни ограничувања

еден корисник -> една loyalty картичка,
points не смеат да се негативни,
points се доделуваат само кога условите се исполнети (COMPLETED + PAID),
refund ја враќа состојбата на поени.

Имплементација

UNIQUE/CHECK constraints

Објекти: idx_loyaltycard_user_unique, loyaltycard_points_nonnegative, payment_points_used_nonnegative
Работи над: loyaltycard, payment
Бизнис правило: единствена картичка и не-негативни поени.

CREATE UNIQUE INDEX IF NOT EXISTS idx_loyaltycard_user_unique
ON loyaltycard (user_id);

ALTER TABLE loyaltycard
ADD CONSTRAINT loyaltycard_points_nonnegative
CHECK (points >= 0);

ALTER TABLE payment
ADD CONSTRAINT payment_points_used_nonnegative
CHECK (points_used >= 0);

`sp_ensure_loyalty_card`

Име: sp_ensure_loyalty_card
Работи над: loyaltycard
Бизнис правило: автоматско креирање loyalty card ако недостига.

CREATE OR REPLACE FUNCTION sp_ensure_loyalty_card(p_user_id int)
RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
    v_card_id int;
BEGIN
    INSERT INTO loyaltycard (user_id, points)
    VALUES (p_user_id, 0)
    ON CONFLICT (user_id) DO NOTHING;

    SELECT card_id
    INTO v_card_id
    FROM loyaltycard
    WHERE user_id = p_user_id;

    RETURN v_card_id;
END;
$$;

`sp_award_loyalty_points`

Име: sp_award_loyalty_points
Работи над: appointment, status, payment, loyaltycard
Бизнис правило: award само ако терминот е COMPLETED и има PAID payment, еднаш по термин.

CREATE OR REPLACE FUNCTION sp_award_loyalty_points(p_appointment_id int)
RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
    v_user_id int;
    v_total numeric;
    v_status text;
    v_points_awarded boolean;
    v_paid_status text;
    v_completed boolean;
    v_paid boolean;
    v_earned int;
BEGIN
    SELECT a.user_id, a.total_price, s.name, a.points_awarded
    INTO v_user_id, v_total, v_status, v_points_awarded
    FROM appointment a
    JOIN status s ON s.status_id = a.status_id
    WHERE a.appointment_id = p_appointment_id
    FOR UPDATE;

    v_completed := (UPPER(v_status) = 'COMPLETED');

    SELECT p.status
    INTO v_paid_status
    FROM payment p
    WHERE p.appointment_id = p_appointment_id
      AND UPPER(p.status) = 'PAID'
    LIMIT 1;

    v_paid := (v_paid_status IS NOT NULL);

    IF NOT v_completed OR NOT v_paid THEN
        RETURN 0;
    END IF;

    IF v_points_awarded THEN
        RETURN 0;
    END IF;

    PERFORM sp_ensure_loyalty_card(v_user_id);

    v_earned := FLOOR(COALESCE(v_total, 0) * 0.05);

    UPDATE loyaltycard
    SET points = points + v_earned
    WHERE user_id = v_user_id;

    UPDATE appointment
    SET points_awarded = true
    WHERE appointment_id = p_appointment_id;

    RETURN v_earned;
END;
$$;

`sp_mark_payment_paid`

Име: sp_mark_payment_paid
Работи над: payment, appointment, userpackagepurchase, package, loyaltycard
Бизнис правило: централен PAID flow за appointment payment и package purchase payment.

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

CREATE OR REPLACE FUNCTION sp_mark_payment_paid(
    p_payment_id int
)
RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
    v_appointment_id int;
    v_package_purchase_id int;
    v_package_id int;
    v_user_id int;
    v_total_price numeric;
    v_requested_points int;
    v_has_paid int;
    v_used int := 0;
    v_payment_status text;
    v_payment_method text;
    v_points_earned int;
BEGIN
    SELECT appointment_id, package_purchase_id, points_used, status, method
    INTO v_appointment_id, v_package_purchase_id, v_requested_points, v_payment_status, v_payment_method
    FROM payment
    WHERE payment_id = p_payment_id
    FOR UPDATE;

    IF UPPER(COALESCE(v_payment_status, '')) = 'PAID' THEN
        RETURN COALESCE(v_requested_points, 0);
    END IF;

    IF v_appointment_id IS NOT NULL THEN

        IF UPPER(COALESCE(v_payment_method, '')) = 'PACKAGE' THEN
            UPDATE payment
            SET status = 'PAID',
                points_used = 0,
                amount = 0
            WHERE payment_id = p_payment_id;

            RETURN 0;
        END IF;

        SELECT a.user_id, a.total_price
        INTO v_user_id, v_total_price
        FROM appointment a
        WHERE a.appointment_id = v_appointment_id;

        v_used := sp_redeem_loyalty_points(
            v_user_id,
            v_total_price,
            v_requested_points
        );

        UPDATE payment
        SET status = 'PAID',
            points_used = v_used,
            amount = v_total_price - v_used
        WHERE payment_id = p_payment_id;

        RETURN v_used;
    END IF;

    SELECT upp.user_id, upp.package_id
    INTO v_user_id, v_package_id
    FROM userpackagepurchase upp
    WHERE upp.purchase_id = v_package_purchase_id
    FOR UPDATE;

    UPDATE payment
    SET status = 'PAID',
        points_used = 0,
        amount = v_total_price
    WHERE payment_id = p_payment_id;

    PERFORM sp_ensure_loyalty_card(v_user_id);

    v_points_earned := FLOOR(COALESCE(v_total_price, 0) * 0.05);

    IF v_points_earned > 0 THEN
        UPDATE loyaltycard
        SET points = points + v_points_earned
        WHERE user_id = v_user_id;
    END IF;

    RETURN 0;
END;
$$;

`sp_refund_payment_for_appointment`

Име: sp_refund_payment_for_appointment
Работи над: payment, appointment, loyaltycard
Бизнис правило: refund + враќање на points_used + rollback на points_awarded.

CREATE OR REPLACE FUNCTION sp_refund_payment_for_appointment(p_appointment_id int)
RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
    v_payment_id int;
    v_user_id int;
    v_total numeric;
    v_points_used int;
    v_points_earned int;
BEGIN
    SELECT p.payment_id, p.points_used
    INTO v_payment_id, v_points_used
    FROM payment p
    WHERE p.appointment_id = p_appointment_id
      AND p.status = 'PAID'
    ORDER BY p."timestamp" DESC
    LIMIT 1
    FOR UPDATE;

    IF v_payment_id IS NULL THEN
        RETURN 0;
    END IF;

    SELECT a.user_id, a.total_price
    INTO v_user_id, v_total
    FROM appointment a
    WHERE a.appointment_id = p_appointment_id
    FOR UPDATE;

    UPDATE payment
    SET status = 'REFUNDED'
    WHERE payment_id = v_payment_id;

    IF v_points_used > 0 THEN
        PERFORM sp_ensure_loyalty_card(v_user_id);

        UPDATE loyaltycard
        SET points = points + v_points_used
        WHERE user_id = v_user_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM appointment
        WHERE appointment_id = p_appointment_id
          AND points_awarded = true
    ) THEN
        v_points_earned := FLOOR(COALESCE(v_total, 0) * 0.05);

        PERFORM sp_ensure_loyalty_card(v_user_id);

        UPDATE loyaltycard
        SET points = GREATEST(points - v_points_earned, 0)
        WHERE user_id = v_user_id;

        UPDATE appointment
        SET points_awarded = false
        WHERE appointment_id = p_appointment_id;
    END IF;

    RETURN v_payment_id;
END;
$$;

Валидација на користење пакети (Package Usage Validation)

Опис на барањата

package purchase мора да е ACTIVE,
remaining_uses мора да е позитивен,
услуги во appointment мора да се покриени од packageservice,
финализација намалува remaining_uses,
двојна финализација се пресекува со finalized_at.

Имплементација

`appointmentpackageusage` ограничувања

Објект: appointmentpackageusage
Работи над: appointmentpackageusage
Бизнис правило: валидна usage релација и позитивни units.

CREATE TABLE appointmentpackageusage (
    appointment_id integer PRIMARY KEY,
    purchase_id integer NOT NULL,
    service_id integer NOT NULL,
    used_units integer NOT NULL DEFAULT 1,
    finalized_at timestamp NULL,

    CONSTRAINT appointmentpackageusage_appointment_id_fkey
        FOREIGN KEY (appointment_id)
        REFERENCES appointment(appointment_id)
        ON DELETE CASCADE,

    CONSTRAINT appointmentpackageusage_purchase_id_fkey
        FOREIGN KEY (purchase_id)
        REFERENCES userpackagepurchase(purchase_id)
        ON DELETE RESTRICT,

    CONSTRAINT appointmentpackageusage_service_id_fkey
        FOREIGN KEY (service_id)
        REFERENCES service(service_id)
        ON DELETE RESTRICT,

    CONSTRAINT appointmentpackageusage_used_units_check
        CHECK (used_units > 0)
);

`sp_apply_package_to_appointment`

Име: sp_apply_package_to_appointment
Работи над: appointment, appointmentservice, userpackagepurchase, packageservice, appointmentpackageusage, payment
Бизнис правило: само валиден пакет да покрие термин; терминот станува prepaid (total_price = 0).

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

CREATE OR REPLACE FUNCTION sp_apply_package_to_appointment(
    p_appointment_id INT,
    p_purchase_id INT
)
RETURNS VOID
AS $$
DECLARE
    v_appt_user_id INT;
    v_purchase_user_id INT;
    v_purchase_status TEXT;
    v_purchase_remaining INT;
    v_purchase_package_id INT;
    v_first_service_id INT;
    v_missing_service_id INT;
BEGIN
    SELECT a.user_id
    INTO v_appt_user_id
    FROM appointment a
    WHERE a.appointment_id = p_appointment_id
    FOR UPDATE;

    SELECT upp.user_id, upp.status, upp.remaining_uses, upp.package_id
    INTO v_purchase_user_id, v_purchase_status, v_purchase_remaining, v_purchase_package_id
    FROM userpackagepurchase upp
    WHERE upp.purchase_id = p_purchase_id
    FOR UPDATE;

    IF v_purchase_status <> 'ACTIVE' THEN
        RAISE EXCEPTION 'Purchase % is not ACTIVE', p_purchase_id;
    END IF;

    IF v_purchase_remaining IS NULL OR v_purchase_remaining <= 0 THEN
        RAISE EXCEPTION 'Purchase % has no remaining uses', p_purchase_id;
    END IF;

    SELECT aps.service_id
    INTO v_missing_service_id
    FROM appointmentservice aps
    LEFT JOIN packageservice ps
      ON ps.package_id = v_purchase_package_id
     AND ps.service_id = aps.service_id
    WHERE aps.appointment_id = p_appointment_id
      AND ps.service_id IS NULL
    LIMIT 1;

    IF v_missing_service_id IS NOT NULL THEN
        RAISE EXCEPTION
            'Appointment service % is not included in selected package',
            v_missing_service_id;
    END IF;

    INSERT INTO appointmentpackageusage (
        appointment_id,
        purchase_id,
        service_id,
        used_units,
        finalized_at
    )
    VALUES (
        p_appointment_id,
        p_purchase_id,
        v_first_service_id,
        1,
        NULL
    )
    ON CONFLICT (appointment_id)
    DO UPDATE SET
        purchase_id = EXCLUDED.purchase_id,
        service_id = EXCLUDED.service_id,
        used_units = EXCLUDED.used_units,
        finalized_at = NULL;

    UPDATE appointment
    SET total_price = 0
    WHERE appointment_id = p_appointment_id;
END;
$$ LANGUAGE plpgsql;

`sp_finalize_package_usage_on_appointment`

Име: sp_finalize_package_usage_on_appointment
Работи над: appointmentpackageusage, userpackagepurchase
Бизнис правило: еднократно трошење на package units при финализација.

CREATE OR REPLACE FUNCTION sp_finalize_package_usage_on_appointment(
    p_appointment_id INT
)
RETURNS VOID
AS $$
DECLARE
    v_purchase_id INT;
    v_used_units INT;
    v_finalized_at TIMESTAMP;
    v_remaining INT;
BEGIN
    SELECT apu.purchase_id, apu.used_units, apu.finalized_at
    INTO v_purchase_id, v_used_units, v_finalized_at
    FROM appointmentpackageusage apu
    WHERE apu.appointment_id = p_appointment_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RETURN;
    END IF;

    IF v_finalized_at IS NOT NULL THEN
        RETURN;
    END IF;

    SELECT upp.remaining_uses
    INTO v_remaining
    FROM userpackagepurchase upp
    WHERE upp.purchase_id = v_purchase_id
    FOR UPDATE;

    IF v_remaining < v_used_units THEN
        RAISE EXCEPTION
            'Insufficient remaining uses on purchase %',
            v_purchase_id;
    END IF;

    UPDATE userpackagepurchase
    SET remaining_uses = remaining_uses - v_used_units
    WHERE purchase_id = v_purchase_id;

    UPDATE appointmentpackageusage
    SET finalized_at = now()
    WHERE appointment_id = p_appointment_id;
END;
$$ LANGUAGE plpgsql;

5. Валидација на достапност (Availability Validation)

Опис на барањата

дупликат прозорци за ист датум/време не се дозволени,
end_time > start_time е задолжително,
availability се користи за runtime генерација на слотови.

Имплементација

Unique index на availability прозорец

Име: idx_availability_unique_window
Работи над: availability
Бизнис правило: нема duplicate window за ист датум/опсег.

CREATE UNIQUE INDEX IF NOT EXISTS idx_availability_unique_window
ON availability (date, start_time, end_time);

`fn_available_slots`

Име: fn_available_slots
Работи над: availability, appointment, status, service
Бизнис правило: слотови само во отворен прозорец и без преклоп со SCHEDULED.

CREATE OR REPLACE FUNCTION fn_available_slots(p_date date, p_service_ids int[])
RETURNS TABLE (start_time timestamp, end_time timestamp)
LANGUAGE sql
AS $$
WITH svc AS (
    SELECT fn_service_total_minutes(p_service_ids) AS total_minutes
),
windows AS (
    SELECT
        (p_date + a.start_time) AS window_start,
        (p_date + a.end_time) AS window_end,
        s.total_minutes
    FROM availability a
    CROSS JOIN svc s
    WHERE a.date = p_date
      AND a.is_closed = false
      AND s.total_minutes > 0
),
candidates AS (
    SELECT
        gs AS start_time,
        gs + make_interval(mins => w.total_minutes) AS end_time
    FROM windows w
    JOIN LATERAL generate_series(
        w.window_start,
        w.window_end - make_interval(mins => w.total_minutes),
        interval '15 minutes'
    ) AS gs
    ON true
)
SELECT c.start_time, c.end_time
FROM candidates c
WHERE NOT EXISTS (
    SELECT 1
    FROM appointment ap
    JOIN status st
      ON st.status_id = ap.status_id
    WHERE UPPER(st.name) = 'SCHEDULED'
      AND c.start_time < ap.end_time
      AND c.end_time > ap.appointment_time
)
ORDER BY c.start_time;
$$;

`sp_admin_add_availability`

Име: sp_admin_add_availability
Работи над: availability
Бизнис правило: валидна временска рамка + no duplicate.

CREATE OR REPLACE FUNCTION sp_admin_add_availability(
    p_date date,
    p_start time,
    p_end time,
    p_is_closed boolean DEFAULT false
)
RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
    v_availability_id int;
    v_exists int;
BEGIN
    IF p_end <= p_start THEN
        RAISE EXCEPTION 'End time must be after start time';
    END IF;

    SELECT 1
    INTO v_exists
    FROM availability
    WHERE date = p_date
      AND start_time = p_start
      AND end_time = p_end
    LIMIT 1;

    IF v_exists IS NOT NULL THEN
        RAISE EXCEPTION
            'Availability window already exists for this date and time';
    END IF;

    INSERT INTO availability
    (
        date,
        start_time,
        end_time,
        is_closed
    )
    VALUES
    (
        p_date,
        p_start,
        p_end,
        p_is_closed
    )
    RETURNING availability_id
    INTO v_availability_id;

    RETURN v_availability_id;
END;
$$;

`sp_admin_update_availability`

Име: sp_admin_update_availability
Работи над: availability
Бизнис правило: update само на постоечки запис, без временски конфликт.

CREATE OR REPLACE FUNCTION sp_admin_update_availability(
    p_availability_id int,
    p_date date,
    p_start time,
    p_end time,
    p_is_closed boolean
)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_exists int;
    v_duplicate int;
BEGIN
    IF p_end <= p_start THEN
        RAISE EXCEPTION 'End time must be after start time';
    END IF;

    SELECT 1
    INTO v_exists
    FROM availability
    WHERE availability_id = p_availability_id
    LIMIT 1;

    IF v_exists IS NULL THEN
        RAISE EXCEPTION 'Availability window not found';
    END IF;

    SELECT 1
    INTO v_duplicate
    FROM availability
    WHERE date = p_date
      AND start_time = p_start
      AND end_time = p_end
      AND availability_id <> p_availability_id
    LIMIT 1;

    IF v_duplicate IS NOT NULL THEN
        RAISE EXCEPTION
            'Availability window already exists for this date and time';
    END IF;

    UPDATE availability
    SET
        date = p_date,
        start_time = p_start,
        end_time = p_end,
        is_closed = p_is_closed
    WHERE availability_id = p_availability_id;
END;
$$;

`sp_admin_delete_availability`

Име: sp_admin_delete_availability
Работи над: availability
Бизнис правило: delete само ако записот постои.

CREATE OR REPLACE FUNCTION sp_admin_delete_availability(
    p_availability_id int
)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_deleted int;
BEGIN
    DELETE FROM availability
    WHERE availability_id = p_availability_id;

    GET DIAGNOSTICS v_deleted = ROW_COUNT;

    IF v_deleted = 0 THEN
        RAISE EXCEPTION 'Availability window not found';
    END IF;
END;
$$;

6. Напредни погледи (Advanced Views)

`v_services_grouped_by_category`

Име: v_services_grouped_by_category
Работи над: category, service, appointmentservice, appointment, payment, review, "User"
Бизнис правило/сценарио: агрегиран services каталог со ratings/reviews за /services и /book.

CREATE OR REPLACE VIEW v_services_grouped_by_category AS
SELECT
    c.category_id,
    c.name AS category_name,
    COALESCE(
        jsonb_agg(
            jsonb_build_object(
                'service_id', s.service_id,
                'name', s.name,
                'price', s.price,
                'duration_minutes', s.duration_minutes,
                'avg_rating', COALESCE(svc.avg_rating, 0),
                'review_count', COALESCE(svc.review_count, 0),
                'reviews', COALESCE(svc.reviews, '[]'::jsonb)
            )
            ORDER BY s.service_id
        ) FILTER (WHERE s.service_id IS NOT NULL),
        '[]'::jsonb
    ) AS services
FROM category c
LEFT JOIN service s
  ON s.category_id = c.category_id
LEFT JOIN LATERAL (
    SELECT
        ROUND(AVG(r.rating)::numeric, 1) AS avg_rating,
        COUNT(r.review_id) AS review_count,
        COALESCE(
            jsonb_agg(
                jsonb_build_object(
                    'review_id', r.review_id,
                    'rating', r.rating,
                    'comment', r.comment,
                    'created_at', r.created_at,
                    'reviewer', u.full_name
                )
                ORDER BY r.created_at DESC
            ) FILTER (WHERE r.review_id IS NOT NULL),
            '[]'::jsonb
        ) AS reviews
    FROM appointmentservice aps
    JOIN appointment a
      ON a.appointment_id = aps.appointment_id
    JOIN payment p
      ON p.appointment_id = a.appointment_id
    JOIN review r
      ON r.payment_id = p.payment_id
    JOIN "User" u
      ON u.user_id = a.user_id
    WHERE aps.service_id = s.service_id
      AND p.status = 'PAID'
) svc
ON true
GROUP BY c.category_id, c.name;

`v_user_appointments`

Име: v_user_appointments
Работи над: appointment, status, appointmentservice, service
Бизнис правило/сценарио: read-model за кориснички термини.

CREATE OR REPLACE VIEW v_user_appointments AS
SELECT
    a.appointment_id,
    a.user_id,
    a.appointment_time,
    a.end_time,
    a.total_price,
    a.notes,
    a.status_id,
    st.name AS status_name,
    a."type"::text AS appointment_type,
    COALESCE(
        jsonb_agg(
            jsonb_build_object(
                'service_id', s.service_id,
                'name', s.name,
                'price', s.price,
                'duration_minutes', s.duration_minutes
            )
            ORDER BY s.service_id
        ) FILTER (WHERE s.service_id IS NOT NULL),
        '[]'::jsonb
    ) AS services
FROM appointment a
LEFT JOIN status st
  ON st.status_id = a.status_id
LEFT JOIN appointmentservice aps
  ON aps.appointment_id = a.appointment_id
LEFT JOIN service s
  ON s.service_id = aps.service_id
GROUP BY
    a.appointment_id,
    a.user_id,
    a.appointment_time,
    a.end_time,
    a.total_price,
    a.notes,
    a.status_id,
    st.name,
    a."type";

`v_user_appointments_payment_state`

Име: v_user_appointments_payment_state
Работи над: appointment, status, appointmentservice, service, payment, review
Бизнис правило/сценарио: кориснички payment/review state + can_pay, can_review.

CREATE VIEW v_user_appointments_payment_state AS
SELECT
    a.appointment_id,
    a.user_id,
    a.appointment_time,
    a.end_time,
    a.total_price,
    st.name AS status_name,
    svc.services AS services,
    lp.payment_id AS payment_id,
    lp.status AS payment_status,
    lp.method AS payment_method,
    lp."timestamp" AS payment_timestamp,
    pp.payment_id AS paid_payment_id,
    rv.review_id AS review_id,
    rv.rating AS review_rating,
    rv.comment AS review_comment,
    rv.created_at AS review_created_at,
    (
        UPPER(st.name) = 'SCHEDULED'
        AND pp.payment_id IS NULL
    ) AS can_pay,
    (
        pp.payment_id IS NOT NULL
        AND rv.review_id IS NULL
        AND COALESCE(a.end_time, a.appointment_time) <= now()
    ) AS can_review
FROM appointment a
JOIN status st
  ON st.status_id = a.status_id
LEFT JOIN LATERAL (
    SELECT
        COALESCE(
            jsonb_agg(
                jsonb_build_object(
                    'service_id', s.service_id,
                    'name', s.name,
                    'price', s.price,
                    'duration_minutes', s.duration_minutes
                )
                ORDER BY s.service_id
            ) FILTER (WHERE s.service_id IS NOT NULL),
            '[]'::jsonb
        ) AS services
    FROM appointmentservice aps
    JOIN service s
      ON s.service_id = aps.service_id
    WHERE aps.appointment_id = a.appointment_id
) svc
ON true
LEFT JOIN LATERAL (
    SELECT
        p.payment_id,
        p.status,
        p.method,
        p."timestamp"
    FROM payment p
    WHERE p.appointment_id = a.appointment_id
    ORDER BY p."timestamp" DESC
    LIMIT 1
) lp
ON true
LEFT JOIN LATERAL (
    SELECT
        p.payment_id,
        p."timestamp"
    FROM payment p
    WHERE p.appointment_id = a.appointment_id
      AND p.status = 'PAID'
    ORDER BY p."timestamp" DESC
    LIMIT 1
) pp
ON true
LEFT JOIN review rv
  ON rv.payment_id = pp.payment_id;

`v_admin_appointments_payment_state`

Име: v_admin_appointments_payment_state
Работи над: appointment, "User", status, appointmentservice, service, payment, review
Бизнис правило/сценарио: админ преглед на термини со payment/review контекст.

CREATE VIEW v_admin_appointments_payment_state AS
SELECT
    a.appointment_id,
    a.user_id,
    u.full_name,
    u.email,
    u.phone,
    a.appointment_time,
    a.end_time,
    a.total_price,
    st.name AS status_name,
    svc.services AS services,
    lp.payment_id AS payment_id,
    lp.status AS payment_status,
    lp.method AS payment_method,
    lp."timestamp" AS payment_timestamp,
    pp.payment_id AS paid_payment_id,
    rv.review_id AS review_id,
    rv.rating AS review_rating,
    rv.comment AS review_comment,
    rv.created_at AS review_created_at
FROM appointment a
JOIN "User" u
  ON u.user_id = a.user_id
JOIN status st
  ON st.status_id = a.status_id
LEFT JOIN LATERAL (
    SELECT
        COALESCE(
            jsonb_agg(
                jsonb_build_object(
                    'service_id', s.service_id,
                    'name', s.name,
                    'price', s.price,
                    'duration_minutes', s.duration_minutes
                )
                ORDER BY s.service_id
            ) FILTER (WHERE s.service_id IS NOT NULL),
            '[]'::jsonb
        ) AS services
    FROM appointmentservice aps
    JOIN service s
      ON s.service_id = aps.service_id
    WHERE aps.appointment_id = a.appointment_id
) svc
ON true
LEFT JOIN LATERAL (
    SELECT
        p.payment_id,
        p.status,
        p.method,
        p."timestamp"
    FROM payment p
    WHERE p.appointment_id = a.appointment_id
    ORDER BY p."timestamp" DESC
    LIMIT 1
) lp
ON true
LEFT JOIN LATERAL (
    SELECT
        p.payment_id,
        p."timestamp"
    FROM payment p
    WHERE p.appointment_id = a.appointment_id
      AND p.status = 'PAID'
    ORDER BY p."timestamp" DESC
    LIMIT 1
) pp
ON true
LEFT JOIN review rv
  ON rv.payment_id = pp.payment_id;

`v_admin_availability_by_date`

Име: v_admin_availability_by_date
Работи над: availability
Бизнис правило/сценарио: read-model за админ schedule екран.

CREATE OR REPLACE VIEW v_admin_availability_by_date AS
SELECT
    date,
    availability_id,
    start_time,
    end_time,
    is_closed
FROM availability
ORDER BY date ASC, start_time ASC;

`v_user_active_packages`

Име: v_user_active_packages
Работи над: userpackagepurchase, package, packageservice, service
Бизнис правило/сценарио: активни пакети и вклучени услуги.

CREATE OR REPLACE VIEW v_user_active_packages AS
SELECT
    upp.user_id,
    upp.purchase_id,
    upp.package_id,
    p.name AS package_name,
    upp.remaining_uses,
    upp.purchased_at,
    array_agg(s.name ORDER BY s.name) AS services
FROM UserPackagePurchase upp
JOIN Package p
  ON p.package_id = upp.package_id
JOIN PackageService ps
  ON ps.package_id = p.package_id
JOIN Service s
  ON s.service_id = ps.service_id
WHERE upp.status = 'ACTIVE'
GROUP BY
    upp.user_id,
    upp.purchase_id,
    upp.package_id,
    p.name,
    upp.remaining_uses,
    upp.purchased_at;

7. Автоматизација и background jobs

`sp_auto_mark_no_show`

Име: sp_auto_mark_no_show
Работи над: appointment, status
Бизнис правило: автоматско означување NO_SHOW за задоцнети SCHEDULED термини.

CREATE OR REPLACE FUNCTION sp_auto_mark_no_show(
    p_grace_minutes int DEFAULT 30
)
RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
    v_no_show_id int;
    v_count int;
BEGIN
    v_no_show_id := fn_status_id(ARRAY['no_show']);

    IF v_no_show_id IS NULL THEN
        RETURN 0;
    END IF;

    WITH candidates AS (
        SELECT a.appointment_id
        FROM appointment a
        JOIN status s
          ON s.status_id = a.status_id
        WHERE UPPER(s.name) = 'SCHEDULED'
          AND a.end_time IS NOT NULL
          AND a.end_time <= now() - make_interval(mins => p_grace_minutes)
    )
    UPDATE appointment
    SET status_id = v_no_show_id
    WHERE appointment_id IN (
        SELECT appointment_id
        FROM candidates
    );

    GET DIAGNOSTICS v_count = ROW_COUNT;

    RETURN v_count;
END;
$$;

Апликациски scheduler повик

Објект: периодичен повик од апликациски слој
Бизнис правило: автоматизација на lifecycle без рачна интервенција.

SELECT sp_auto_mark_no_show($1::int);

Last modified 30 hours ago Last modified on 05/24/26 17:49:21

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text

Context Navigation

Напреден развој на базата

1. Валидација на термини (Appointment Validation)

Опис на барањата за податочни ограничувања

Имплементација

fn_validate_appointment

fn_recalculate_appointment

sp_create_appointment

trg_validate_appointment + appointment_validate

trg_appointmentservice_recalculate + appointmentservice_recalculate

2. Валидација на плаќања (Payment Validation)

Опис на барањата

Имплементација

payment_target_required constraint

Partial unique indexes за PAID

trg_payment_validate + payment_validate

trg_appointment_prevent_price_change + appointment_prevent_price_change

sp_create_review

3. Loyalty System

Опис на барањата за податочни ограничувања

Имплементација

UNIQUE/CHECK constraints

sp_ensure_loyalty_card

sp_award_loyalty_points

sp_mark_payment_paid

sp_refund_payment_for_appointment

Валидација на користење пакети (Package Usage Validation)

Опис на барањата

Имплементација

appointmentpackageusage ограничувања

sp_apply_package_to_appointment

sp_finalize_package_usage_on_appointment

5. Валидација на достапност (Availability Validation)

Опис на барањата

Имплементација

Unique index на availability прозорец

fn_available_slots

sp_admin_add_availability

sp_admin_update_availability

sp_admin_delete_availability

6. Напредни погледи (Advanced Views)

v_services_grouped_by_category

v_user_appointments

v_user_appointments_payment_state

v_admin_appointments_payment_state

v_admin_availability_by_date

v_user_active_packages

7. Автоматизација и background jobs

sp_auto_mark_no_show

Апликациски scheduler повик

Download in other formats:

`fn_validate_appointment`

`fn_recalculate_appointment`

`sp_create_appointment`

`trg_validate_appointment` + `appointment_validate`

`trg_appointmentservice_recalculate` + `appointmentservice_recalculate`

`payment_target_required` constraint

`trg_payment_validate` + `payment_validate`

`trg_appointment_prevent_price_change` + `appointment_prevent_price_change`

`sp_create_review`

`sp_ensure_loyalty_card`

`sp_award_loyalty_points`

`sp_mark_payment_paid`

`sp_refund_payment_for_appointment`

`appointmentpackageusage` ограничувања

`sp_apply_package_to_appointment`

`sp_finalize_package_usage_on_appointment`

`fn_available_slots`

`sp_admin_add_availability`

`sp_admin_update_availability`

`sp_admin_delete_availability`

`v_services_grouped_by_category`

`v_user_appointments`

`v_user_appointments_payment_state`

`v_admin_appointments_payment_state`

`v_admin_availability_by_date`

`v_user_active_packages`

`sp_auto_mark_no_show`