= Функции, Процедури и Тригери =


[[html(<a href="https://develop.finki.ukim.mk/projects/LMB/attachment/wiki/DatabaseProgramming/faza4.sql">faza4.sql</a>)]]


Во оваа фаза беа имплементирани функции, процедури и тригери кои ја поддржуваат основната бизнис логика на системот за booking на артисти и бендови. Секоја компонента е одговорна за одредена операција — од пресметување статистики, преку управување со bookings, до автоматска заштита од грешки.

== Функции ==
Функциите се користат за пресметување и враќање на вредности кои се потребни на повеќе места во апликацијата. Секоја функција прима параметри, извршува логика врз базата и враќа резултат.

=== 1. fn_artist_average_rating ===
Функцијата {{{fn_artist_average_rating}}} го пресметува просечниот рејтинг на артист или бенд врз основа на сите reviews оставени за нивните завршени bookings.

Ги поврзува табелите {{{Review}}}, {{{Booking}}} и {{{Offer}}} за да го пронајде просекот на рејтинзите за конкретниот артист. Ако артистот сè уште нема reviews, функцијата враќа {{{0}}} наместо {{{NULL}}} преку употреба на {{{COALESCE}}}.
{{{
CREATE OR REPLACE FUNCTION fn_artist_average_rating(
    p_bookable_id INT
)
RETURNS NUMERIC(3,2)
AS
$$
DECLARE
    v_average_rating NUMERIC(3,2);
BEGIN

    SELECT
        ROUND(AVG(r.rating), 2)
    INTO v_average_rating
    FROM Review r
    JOIN Booking bk
        ON r.booking_id = bk.booking_id
    JOIN Offer o
        ON bk.offer_id = o.offer_id
    WHERE o.bookable_id = p_bookable_id;

    RETURN COALESCE(v_average_rating, 0);

END;
$$
LANGUAGE plpgsql;
}}}

=== 2. fn_artist_total_earnings ===
Функцијата {{{fn_artist_total_earnings}}} ја пресметува вкупната заработка на артист или бенд земајќи ги предвид само успешно завршените плаќања со статус {{{PAID}}}.

Ги поврзува табелите {{{Payment}}}, {{{Booking}}} и {{{Offer}}} и го сумира износот на сите потврдени плаќања. Ако артистот сè уште нема реализирани плаќања, функцијата враќа {{{0}}} наместо {{{NULL}}}.
{{{
CREATE OR REPLACE FUNCTION fn_artist_total_earnings(
    p_bookable_id INT
)
RETURNS NUMERIC(10,2)
AS
$$
DECLARE
    v_total NUMERIC(10,2);
BEGIN

    SELECT
        SUM(p.amount)
    INTO v_total
    FROM Payment p
    JOIN Booking bk
        ON p.booking_id = bk.booking_id
    JOIN Offer o
        ON bk.offer_id = o.offer_id
    WHERE o.bookable_id = p_bookable_id
      AND p.payment_status = 'PAID';

    RETURN COALESCE(v_total, 0);

END;
$$
LANGUAGE plpgsql;
}}}

=== 3. fn_check_artist_availability ===
Функцијата {{{fn_check_artist_availability}}} проверува дали артист или бенд има слободен термин на одреден датум и враќа {{{TRUE}}} или {{{FALSE}}}.

Наместо {{{COUNT(*)}}} кој ги брои сите редици, функцијата користи {{{EXISTS}}} кој застанува веднаш при првиот пронајден слободен слот — со што е значително поефикасна.
{{{
CREATE OR REPLACE FUNCTION fn_check_artist_availability(
    p_bookable_id INT,
    p_date        DATE
)
RETURNS BOOLEAN
AS
$$
DECLARE
    v_exists BOOLEAN;
BEGIN

    SELECT EXISTS(
        SELECT 1
        FROM AvailabilitySlot
        WHERE bookable_id = p_bookable_id
          AND DATE(start_datetime) = p_date
          AND status = 'AVAILABLE'
    )
    INTO v_exists;

    RETURN v_exists;

END;
$$
LANGUAGE plpgsql;
}}}

=== 4. fn_total_client_bookings ===
Функцијата {{{fn_total_client_bookings}}} го враќа вкупниот број на bookings направени од одреден клиент.

Ги поврзува табелите {{{BookingRequest}}}, {{{Offer}}} и {{{Booking}}} за да го пресмета бројот на сите bookings поврзани со клиентот, без разлика на нивниот статус.
{{{
CREATE OR REPLACE FUNCTION fn_total_client_bookings(
    p_client_id INT
)
RETURNS INT
AS
$$
DECLARE
    v_total INT;
BEGIN

    SELECT COUNT(*)
    INTO v_total
    FROM BookingRequest br
    JOIN Offer o
        ON br.request_id = o.request_id
    JOIN Booking bk
        ON bk.offer_id = o.offer_id
    WHERE br.client_id = p_client_id;

    RETURN v_total;

END;
$$
LANGUAGE plpgsql;
}}}

== Процедури ==
Процедурите се користат за извршување на акции врз базата — за разлика од функциите, тие не враќаат вредност туку вршат промени. Секоја процедура содржи валидација пред да изврши каква било промена, со цел да се спречат невалидни состојби во базата.

=== 1. sp_create_booking_request ===
Процедурата {{{sp_create_booking_request}}} креира нов барање за booking од страна на клиент.

Пред да го изврши вметнувањето, процедурата врши три валидации:
 * Проверува дали клиентот постои во табелата {{{ClientProfile}}}
 * Проверува дали локацијата постои во табелата {{{Location}}}
 * Проверува дали датумот на настанот е во иднина

Ако некоја од валидациите не помине, се фрла исклучок со јасна порака за грешка.
{{{
CREATE OR REPLACE PROCEDURE sp_create_booking_request(
    p_client_id   INT,
    p_duration_id INT,
    p_event_type  VARCHAR,
    p_event_date  DATE,
    p_location_id INT
)
LANGUAGE plpgsql
AS
$$
BEGIN

    IF NOT EXISTS (
        SELECT 1 FROM ClientProfile WHERE client_id = p_client_id
    ) THEN
        RAISE EXCEPTION 'Client with ID % does not exist!', p_client_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM Location WHERE location_id = p_location_id
    ) THEN
        RAISE EXCEPTION 'Location with ID % does not exist!', p_location_id;
    END IF;

    IF p_event_date <= CURRENT_DATE THEN
        RAISE EXCEPTION 'Event date must be in the future!';
    END IF;

    INSERT INTO BookingRequest(
        client_id,
        duration_id,
        event_type,
        event_date,
        location_id
    )
    VALUES (
        p_client_id,
        p_duration_id,
        p_event_type,
        p_event_date,
        p_location_id
    );

END;
$$;
}}}

=== 2. sp_cancel_booking ===
Процедурата {{{sp_cancel_booking}}} го откажува постоечки booking со менување на неговиот статус во {{{CANCELLED}}}.

Пред да го изврши откажувањето, процедурата врши три проверки:
 * Проверува дали booking со дадениот ID воопшто постои
 * Проверува дали booking не е веќе откажан
 * Проверува дали booking не е веќе завршен — завршените bookings не можат да се откажат

Без овие проверки, повикот со непостоечки ID би поминал тивко без никаква промена или грешка.
{{{
CREATE OR REPLACE PROCEDURE sp_cancel_booking(
    p_booking_id INT
)
LANGUAGE plpgsql
AS
$$
DECLARE
    v_current_status VARCHAR(30);
BEGIN

    SELECT booking_status
    INTO v_current_status
    FROM Booking
    WHERE booking_id = p_booking_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Booking with ID % does not exist!', p_booking_id;
    END IF;

    IF v_current_status = 'CANCELLED' THEN
        RAISE EXCEPTION 'Booking % is already cancelled!', p_booking_id;
    END IF;

    IF v_current_status = 'COMPLETED' THEN
        RAISE EXCEPTION 'Booking % is already completed and cannot be cancelled!', p_booking_id;
    END IF;

    UPDATE Booking
    SET booking_status = 'CANCELLED'
    WHERE booking_id = p_booking_id;

END;
$$;
}}}

=== 3. sp_create_payment ===
Процедурата {{{sp_create_payment}}} креира ново плаќање поврзано со одреден booking.

Клучна поправка во однос на оригиналната верзија е дека плаќањето се креира со статус {{{PENDING}}} наместо веднаш {{{PAID}}}. Ова одговара на реалниот тек — плаќањето прво се иницира, па дури потоа се потврдува.

Процедурата врши три валидации:
 * Проверува дали booking постои
 * Проверува дали износот е позитивен број
 * Проверува дали веќе не постои плаќање за истиот booking
{{{
CREATE OR REPLACE PROCEDURE sp_create_payment(
    p_booking_id INT,
    p_amount     NUMERIC(10,2)
)
LANGUAGE plpgsql
AS
$$
BEGIN

    IF NOT EXISTS (
        SELECT 1 FROM Booking WHERE booking_id = p_booking_id
    ) THEN
        RAISE EXCEPTION 'Booking with ID % does not exist!', p_booking_id;
    END IF;

    IF p_amount <= 0 THEN
        RAISE EXCEPTION 'Payment amount must be greater than 0!';
    END IF;

    IF EXISTS (
        SELECT 1 FROM Payment WHERE booking_id = p_booking_id
    ) THEN
        RAISE EXCEPTION 'Payment for booking % already exists!', p_booking_id;
    END IF;

    INSERT INTO Payment(
        booking_id,
        amount,
        payment_status
    )
    VALUES (
        p_booking_id,
        p_amount,
        'PENDING'
    );

END;
$$;
}}}

== Тригери ==
Тригерите се специјални функции кои се извршуваат автоматски при одредени настани во базата — без потреба апликацијата да ги повикува рачно. Во системот се имплементирани три тригери кои обезбедуваат конзистентност и интегритет на податоците.

=== 1. trg_prevent_double_booking ===
Тригерот {{{trg_prevent_double_booking}}} спречува двоен booking — ситуација каде ист артист или бенд би бил потврден за два различни настани на ист датум.

Се извршува автоматски '''пред''' секој нов запис во табелата {{{Booking}}} ({{{BEFORE INSERT}}}). Доколку за истиот артист веќе постои {{{CONFIRMED}}} booking на истиот датум, тригерот фрла исклучок и го спречува вметнувањето.
{{{
CREATE OR REPLACE FUNCTION fn_prevent_double_booking()
RETURNS TRIGGER
AS
$$
DECLARE
    v_count      INT;
    v_bookable   INT;
    v_event_date DATE;
BEGIN

    SELECT o.bookable_id, br.event_date
    INTO v_bookable, v_event_date
    FROM Offer o
    JOIN BookingRequest br ON br.request_id = o.request_id
    WHERE o.offer_id = NEW.offer_id;

    SELECT COUNT(*)
    INTO v_count
    FROM Booking b
    JOIN Offer o
        ON b.offer_id = o.offer_id
    JOIN BookingRequest br
        ON br.request_id = o.request_id
    WHERE o.bookable_id = v_bookable
      AND br.event_date = v_event_date
      AND b.booking_status = 'CONFIRMED';

    IF v_count > 0 THEN
        RAISE EXCEPTION 'Artist/Band with ID % is already booked on %!',
            v_bookable, v_event_date;
    END IF;

    RETURN NEW;

END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_prevent_double_booking
BEFORE INSERT ON Booking
FOR EACH ROW
EXECUTE FUNCTION fn_prevent_double_booking();
}}}

=== 2. trg_update_slot_status ===
Тригерот {{{trg_update_slot_status}}} автоматски го означува слободниот термин на артистот како {{{BOOKED}}} во моментот кога се креира нов booking.

Се извршува автоматски '''по''' секој нов запис во табелата {{{Booking}}} ({{{AFTER INSERT}}}). Го пронаоѓа соодветниот {{{AvailabilitySlot}}} според артистот и датумот на настанот и го менува неговиот статус — со тоа се спречува двојна резервација на ист термин.
{{{
CREATE OR REPLACE FUNCTION fn_update_slot_status()
RETURNS TRIGGER
AS
$$
DECLARE
    v_bookable   INT;
    v_event_date DATE;
BEGIN

    SELECT o.bookable_id, br.event_date
    INTO v_bookable, v_event_date
    FROM Offer o
    JOIN BookingRequest br ON br.request_id = o.request_id
    WHERE o.offer_id = NEW.offer_id;

    UPDATE AvailabilitySlot
    SET status = 'BOOKED'
    WHERE bookable_id = v_bookable
      AND DATE(start_datetime) = v_event_date
      AND status = 'AVAILABLE';

    RETURN NEW;

END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_update_slot_status
AFTER INSERT ON Booking
FOR EACH ROW
EXECUTE FUNCTION fn_update_slot_status();
}}}

=== 3. trg_booking_status_history ===
Тригерот {{{trg_booking_status_history}}} автоматски го запишува секој пат кога статусот на booking се менува, формирајќи целосен историјат на промени.

Се извршува автоматски '''по''' секој {{{UPDATE}}} на табелата {{{Booking}}}, но само кога вредноста на {{{booking_status}}} навистина се променила — ова се постигнува со условот {{{WHEN (OLD.booking_status IS DISTINCT FROM NEW.booking_status)}}}. Со тоа се избегнува непотребно запишување при update-и кои не го менуваат статусот.
{{{
CREATE OR REPLACE FUNCTION fn_booking_status_history()
RETURNS TRIGGER
AS
$$
BEGIN

    INSERT INTO BookingStatusHistory(
        booking_id,
        new_status,
        changed_at
    )
    VALUES (
        NEW.booking_id,
        NEW.booking_status,
        NOW()
    );

    RETURN NEW;

END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_booking_status_history
AFTER UPDATE ON Booking
FOR EACH ROW
WHEN (OLD.booking_status IS DISTINCT FROM NEW.booking_status)
EXECUTE FUNCTION fn_booking_status_history();
}}}