DatabaseProgramming: faza4.sql

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;


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;


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;


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;


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;
$$;


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;
$$;


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;
$$;


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


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


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