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

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

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

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

 * термин не смее да биде во минато,
 * `end_time` мора да е после `appointment_time`,
 * терминот мора да е во availability прозорец (`is_closed = false`),
 * не е дозволено преклопување со друг ''SCHEDULED'' термин,
 * при креирање мора да има барем една услуга,
 * `end_time` и `total_price` автоматски се пресметуваат од услуги,
 * валидацијата се извршува на INSERT/UPDATE.

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

==== `fn_validate_appointment` ====

 * '''Име:''' `fn_validate_appointment`
 * '''Работи над:''' `appointment`, `availability`, `status`
 * '''Бизнис правило:''' временска валидност, availability валидација, anti-overlap.

{{{
#!sql
CREATE OR REPLACE FUNCTION fn_validate_appointment(p_appointment_id int)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_start timestamp;
    v_end timestamp;
    v_date date;
    v_overlap_count int;
    v_avail_count int;
BEGIN

    SELECT appointment_time, end_time
    INTO v_start, v_end
    FROM appointment
    WHERE appointment_id = p_appointment_id;

    IF v_start IS NULL OR v_end IS NULL THEN
        RAISE EXCEPTION 'Appointment must have a start and end time';
    END IF;

    IF v_end <= v_start THEN
        RAISE EXCEPTION 'Appointment end time must be after start time';
    END IF;

    IF v_start < now() THEN
        RAISE EXCEPTION 'Appointment time must be in the future';
    END IF;

    v_date := v_start::date;

    SELECT COUNT(*)
    INTO v_avail_count
    FROM availability a
    WHERE a.date = v_date
      AND a.is_closed = false
      AND v_start >= (a.date + a.start_time)
      AND v_end <= (a.date + a.end_time);

    IF v_avail_count = 0 THEN
        RAISE EXCEPTION 'Appointment is outside availability';
    END IF;

    SELECT COUNT(*)
    INTO v_overlap_count
    FROM appointment ap
    JOIN status st
      ON st.status_id = ap.status_id
    WHERE ap.appointment_id <> p_appointment_id
      AND UPPER(st.name) = 'SCHEDULED'
      AND v_start < ap.end_time
      AND v_end > ap.appointment_time;

    IF v_overlap_count > 0 THEN
        RAISE EXCEPTION 'Appointment overlaps an existing booking';
    END IF;

END;
$$;
}}}

==== `fn_recalculate_appointment` ====

 * '''Име:''' `fn_recalculate_appointment`
 * '''Работи над:''' `appointment`, `appointmentservice`, `service`
 * '''Бизнис правило:''' автоматско пресметување на `total_price` и `end_time`.

{{{
#!sql
CREATE OR REPLACE FUNCTION fn_recalculate_appointment(p_appointment_id int)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_total_minutes int;
    v_total_price numeric;
    v_start timestamp;
BEGIN

    SELECT appointment_time
    INTO v_start
    FROM appointment
    WHERE appointment_id = p_appointment_id
    FOR UPDATE;

    IF v_start IS NULL THEN
        RETURN;
    END IF;

    SELECT
        COALESCE(SUM(s.duration_minutes), 0)::int,
        COALESCE(SUM(s.price), 0)::numeric
    INTO
        v_total_minutes,
        v_total_price
    FROM appointmentservice aps
    JOIN service s
      ON s.service_id = aps.service_id
    WHERE aps.appointment_id = p_appointment_id;

    UPDATE appointment
    SET
        total_price = v_total_price,
        end_time = v_start + make_interval(mins => v_total_minutes)
    WHERE appointment_id = p_appointment_id;

END;
$$;
}}}

==== `sp_create_appointment` ====

 * '''Име:''' `sp_create_appointment`
 * '''Работи над:''' `service`, `status`, `appointment`, `appointmentservice`
 * '''Бизнис правило:''' креирање термин со целосна DB валидација и пресметка.

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

{{{
#!sql
CREATE OR REPLACE FUNCTION sp_create_appointment(
    p_user_id int,
    p_appointment_time timestamp,
    p_service_ids int[],
    p_notes text
)
RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
    v_appointment_id int;
    v_status_id int;
    v_service_count int;
    v_total_minutes int;
    v_total_price numeric;
    v_end_time timestamp;
    v_enum_type text;
    v_enum_label text;
BEGIN

    IF p_service_ids IS NULL
       OR array_length(p_service_ids, 1) IS NULL THEN
        RAISE EXCEPTION 'At least one service is required';
    END IF;

    SELECT COUNT(*)
    INTO v_service_count
    FROM service
    WHERE service_id = ANY(p_service_ids);

    IF v_service_count <> array_length(p_service_ids, 1) THEN
        RAISE EXCEPTION 'One or more services not found';
    END IF;

    v_total_minutes := fn_service_total_minutes(p_service_ids);
    v_total_price := fn_service_total_price(p_service_ids);

    v_end_time :=
        p_appointment_time
        + make_interval(mins => v_total_minutes);

    SELECT status_id
    INTO v_status_id
    FROM status
    WHERE UPPER(name) = 'SCHEDULED'
    LIMIT 1;

    EXECUTE format(
        'INSERT INTO appointment
        (
            appointment_time,
            end_time,
            total_price,
            notes,
            status_id,
            user_id,
            "type"
        )
        VALUES ($1,$2,$3,$4,$5,$6,$7::%I)
        RETURNING appointment_id',
        v_enum_type
    )
    INTO v_appointment_id
    USING
        p_appointment_time,
        v_end_time,
        v_total_price,
        p_notes,
        v_status_id,
        p_user_id,
        v_enum_label;

    INSERT INTO appointmentservice
    (
        appointment_id,
        service_id
    )
    SELECT
        v_appointment_id,
        unnest(p_service_ids);

    PERFORM fn_validate_appointment(v_appointment_id);

    RETURN v_appointment_id;

END;
$$;
}}}

==== `trg_validate_appointment` + `appointment_validate` ====

 * '''Имиња:''' `trg_validate_appointment`, `appointment_validate`
 * '''Работи над:''' `appointment`
 * '''Бизнис правило:''' задолжителна валидација и на insert и на update.

{{{
#!sql
CREATE OR REPLACE FUNCTION trg_validate_appointment()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN

    PERFORM fn_validate_appointment(NEW.appointment_id);

    RETURN NEW;

END;
$$;

DROP TRIGGER IF EXISTS appointment_validate
ON appointment;

CREATE CONSTRAINT TRIGGER appointment_validate
AFTER INSERT OR UPDATE
ON appointment
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION trg_validate_appointment();
}}}

==== `trg_appointmentservice_recalculate` + `appointmentservice_recalculate` ====

 * '''Имиња:''' `trg_appointmentservice_recalculate`, `appointmentservice_recalculate`
 * '''Работи над:''' `appointmentservice`, `appointment`
 * '''Бизнис правило:''' секоја промена на услуги во термин автоматски ги рефрешира цена/траење.

{{{
#!sql
CREATE OR REPLACE FUNCTION trg_appointmentservice_recalculate()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN

    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        PERFORM fn_recalculate_appointment(NEW.appointment_id);
    END IF;

    IF TG_OP IN ('DELETE', 'UPDATE') THEN
        PERFORM fn_recalculate_appointment(OLD.appointment_id);
    END IF;

    RETURN NULL;

END;
$$;

DROP TRIGGER IF EXISTS appointmentservice_recalculate
ON appointmentservice;

CREATE TRIGGER appointmentservice_recalculate
AFTER INSERT OR UPDATE OR DELETE
ON appointmentservice
FOR EACH ROW
EXECUTE FUNCTION trg_appointmentservice_recalculate();
}}}

== 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);
}}}