Индекси

Индексите ги забрзуваат пребарувањата на често пребарувани колони и странски клучеви.

CREATE INDEX idx_user_email ON Hotel_User(email);
CREATE INDEX idx_room_type_available ON Room(room_type, available);
CREATE INDEX idx_reservation_customer ON Reservation(customer_id);
CREATE INDEX idx_payment_reservation ON Payment(reservation_id);
CREATE INDEX idx_service_staff ON Service(staff_id);

Погледи

Вкупна цена на резервации

CREATE OR REPLACE VIEW reservation_total AS
SELECT r.reservation_id,
       (EXTRACT(DAY FROM (r.end_date - r.start_date)) * rm.price_per_night) AS total_cost
FROM Reservation r
JOIN Room rm ON r.room_number = rm.room_number
            AND r.building_id = rm.building_id;

Активни резервации по клиент

CREATE OR REPLACE VIEW customer_reservations AS
SELECT c.user_id, u.first_name, u.last_name, r.reservation_id,
       r.start_date, r.end_date, r.status
FROM Customer c
JOIN Hotel_User u ON u.user_id = c.user_id
JOIN Reservation r ON r.customer_id = c.user_id;

Услуги обезбедени од персоналот

CREATE OR REPLACE VIEW staff_services AS
SELECT s.service_id, s.service_type, s.service_date, s.service_status,
       st.user_id AS staff_id, u.first_name, u.last_name
FROM Service s
JOIN Staff st ON st.user_id = s.staff_id
JOIN Hotel_User u ON u.user_id = st.user_id;

Процедури

Креирање резервација

CREATE OR REPLACE PROCEDURE make_reservation(
    IN p_customer_id BIGINT,
    IN p_room_number INT,
    IN p_building_id VARCHAR(10),
    IN p_start DATE,
    IN p_end DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Prevent overlapping reservations
    IF EXISTS (
        SELECT 1 FROM Reservation
        WHERE room_number = p_room_number
          AND building_id = p_building_id
          AND status = 'Approved'
          AND daterange(start_date, end_date, '[]') && daterange(p_start, p_end, '[]')
    ) THEN
        RAISE EXCEPTION 'Room is already reserved for these dates';
    END IF;

    -- Insert reservation
    INSERT INTO Reservation (start_date, end_date, status, room_number, building_id, customer_id)
    VALUES (p_start, p_end, 'Pending', p_room_number, p_building_id, p_customer_id);
END;
$$;

Одобри резервација

CREATE OR REPLACE PROCEDURE approve_reservation(
    IN p_reservation_id BIGINT,
    IN p_manager_id BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE Reservation
    SET status = 'Approved',
        manager_id = p_manager_id
    WHERE reservation_id = p_reservation_id;
END;
$$;

Регистрирај плаќање

CREATE OR REPLACE PROCEDURE make_payment(
    IN p_reservation_id BIGINT,
    IN p_method VARCHAR(20)
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO Payment (p_method, reservation_id)
    VALUES (p_method, p_reservation_id);
END;
$$;

Функции

Ноќи помеѓу датуми

CREATE OR REPLACE FUNCTION nights_between(p_start DATE, p_end DATE)
RETURNS INT LANGUAGE sql
AS $$
    SELECT EXTRACT(DAY FROM (p_end - p_start));
$$;

Пресметка на трошокот за резервација

CREATE OR REPLACE FUNCTION reservation_cost(p_reservation_id BIGINT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    cost NUMERIC;
BEGIN
    SELECT (EXTRACT(DAY FROM (r.end_date - r.start_date)) * rm.price_per_night)
    INTO cost
    FROM Reservation r
    JOIN Room rm ON rm.room_number = r.room_number
                AND rm.building_id = r.building_id
    WHERE r.reservation_id = p_reservation_id;

    RETURN cost;
END;
$$;

Тригери

Автоматско ажурирање на достапноста на собата по одобрувањето

CREATE OR REPLACE FUNCTION update_room_availability()
RETURNS TRIGGER LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.status = 'Approved' THEN
        UPDATE Room
        SET available = FALSE
        WHERE room_number = NEW.room_number
          AND building_id = NEW.building_id;
    ELSIF NEW.status = 'Cancelled' THEN
        UPDATE Room
        SET available = TRUE
        WHERE room_number = NEW.room_number
          AND building_id = NEW.building_id;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_update_room_availability
AFTER UPDATE OF status ON Reservation
FOR EACH ROW
EXECUTE FUNCTION update_room_availability();

Спречување на бришење корисници со активни резервации

CREATE OR REPLACE FUNCTION prevent_user_delete()
RETURNS TRIGGER LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM Reservation WHERE customer_id = OLD.user_id AND status <> 'Cancelled') THEN
        RAISE EXCEPTION 'Cannot delete user with active reservations';
    END IF;
    RETURN OLD;
END;
$$;

CREATE TRIGGER trg_prevent_user_delete
BEFORE DELETE ON Hotel_User
FOR EACH ROW
EXECUTE FUNCTION prevent_user_delete();