Changes between Version 1 and Version 2 of AdvancedDatabaseDevelopment

Timestamp:

05/24/26 17:49:21 (34 hours ago)

Author:

202033

Comment:

--

AdvancedDatabaseDevelopment

@@ -1 @@
-== Валидација на термини (Appointment Validation) ==
+= Напреден развој на базата
+
+== 1. Валидација на термини (Appointment Validation) ==
 
 === Опис на барањата за податочни ограничувања ===
@@ -300 +302 @@
 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 е невалиден.
+
+{{{
+#!sql
+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.
+
+{{{
+#!sql
+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.
+
+''извадок од функцијата''
+
+{{{
+#!sql
+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`.
+
+{{{
+#!sql
+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.
+
+{{{
+#!sql
+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`
+ * '''Бизнис правило:''' единствена картичка и не-негативни поени.
+
+{{{
+#!sql
+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 ако недостига.
+
+{{{
+#!sql
+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, еднаш по термин.
+
+{{{
+#!sql
+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.
+
+''извадок од функцијата''
+
+{{{
+#!sql
+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`.
+
+{{{
+#!sql
+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.
+
+{{{
+#!sql
+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`).
+
+''извадок од функцијата''
+
+{{{
+#!sql
+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 при финализација.
+
+{{{
+#!sql
+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 за ист датум/опсег.
+
+{{{
+#!sql
+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''.
+
+{{{
+#!sql
+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.
+
+{{{
+#!sql
+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 само на постоечки запис, без временски конфликт.
+
+{{{
+#!sql
+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 само ако записот постои.
+
+{{{
+#!sql
+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`.
+
+{{{
+#!sql
+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 за кориснички термини.
+
+{{{
+#!sql
+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`.
+
+{{{
+#!sql
+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 контекст.
+
+{{{
+#!sql
+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 екран.
+
+{{{
+#!sql
+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`
+ * '''Бизнис правило/сценарио:''' активни пакети и вклучени услуги.
+
+{{{
+#!sql
+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'' термини.
+
+{{{
+#!sql
+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 без рачна интервенција.
+
+{{{
+#!sql
+SELECT sp_auto_mark_no_show($1::int);
+}}}