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


[[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}}}.

Пребарува во табелата {{{AvailabilitySlot}}} дали постои слот со статус {{{AVAILABLE}}} за дадениот артист на бараниот датум. Резултатот од {{{COUNT(*)}}} се споредува со {{{0}}} и се враќа соодветна булова вредност.
{{{
CREATE OR REPLACE FUNCTION fn_check_artist_availability(
    p_bookable_id INT,
    p_date DATE
)
RETURNS BOOLEAN
AS
$$
DECLARE
    v_exists INT;
BEGIN

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

    RETURN v_exists > 0;

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 од страна на клиент.

Прима ги сите потребни параметри — ID на клиент, траење на настанот, тип на настан, датум и локација — и ги вметнува во табелата {{{BookingRequest}}}. Преку {{{FOREIGN KEY}}} ограничувањата во базата се обезбедува дека само валидни вредности можат да бидат вметнати.
{{{
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

    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}}}.

Прима го ID на booking-от и врши {{{UPDATE}}} врз табелата {{{Booking}}}. Со промената на статусот системот го евидентира откажувањето, а тригерот {{{trg_booking_status_history}}} автоматски ја запишува промената во историјатот.
{{{
CREATE OR REPLACE PROCEDURE sp_cancel_booking(
    p_booking_id INT
)
LANGUAGE plpgsql
AS
$$
BEGIN

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

END;
$$;
}}}

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

Прима го ID на booking-от и износот на плаќањето и ги вметнува во табелата {{{Payment}}}. Плаќањето се креира со статус {{{PAID}}}, со што се евидентира успешно извршена трансакција.
{{{
CREATE OR REPLACE PROCEDURE sp_create_payment(
    p_booking_id INT,
    p_amount NUMERIC(10,2)
)
LANGUAGE plpgsql
AS
$$
BEGIN

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

END;
$$;
}}}

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

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

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

    UPDATE AvailabilitySlot s
    SET status = 'BOOKED'
    WHERE s.bookable_id = (
              SELECT o.bookable_id
              FROM Offer o
              WHERE o.offer_id = NEW.offer_id
          )
      AND DATE(s.start_datetime) = (
              SELECT br.event_date
              FROM BookingRequest br
              JOIN Offer o2 ON o2.request_id = br.request_id
              WHERE o2.offer_id = NEW.offer_id
          );

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

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

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

    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 = (
              SELECT bookable_id
              FROM Offer
              WHERE offer_id = NEW.offer_id
          )
      AND br.event_date = (
              SELECT br2.event_date
              FROM BookingRequest br2
              JOIN Offer o2 ON o2.request_id = br2.request_id
              WHERE o2.offer_id = NEW.offer_id
          )
      AND b.booking_status = 'CONFIRMED';

    IF v_count > 0 THEN
        RAISE EXCEPTION 'Artist already booked on that 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();
}}}

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