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

faza4.sql

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

Во оваа фаза беа имплементирани функции, процедури и тригери кои ја поддржуваат основната бизнис логика на системот за 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();