Changes between Initial Version and Version 1 of AdvancedApplicationDevelopment

Timestamp:

09/15/25 01:39:39 (6 weeks ago)

Author:

215010

Comment:

--

AdvancedApplicationDevelopment

@@ +1 @@
+== Индекси
+
+Индексите ги забрзуваат пребарувањата на често пребарувани колони и странски клучеви.
+{{{#!sql
+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);
+}}}
+
+== Погледи
+
+===  Вкупна цена на резервации
+{{{#!sql
+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;
+}}}
+
+=== Активни резервации по клиент
+{{{#!sql
+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;
+}}}
+
+=== Услуги обезбедени од персоналот
+{{{#!sql
+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;
+}}}
+
+=== Преглед за динамичко пресметување на вкупната цена на резервацијата
+{{{#!sql
+CREATE OR REPLACE VIEW reservation_total AS
+SELECT r.reservation_id,
+       r.start_date,
+       r.end_date,
+       rm.price_per_night,
+       (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;
+}}}
+
+== Процедури
+
+=== Креирање резервација
+{{{#!sql
+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;
+$$;
+}}}
+
+=== Одобри резервација
+{{{#!sql
+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;
+$$;
+}}}
+
+=== Регистрирај плаќање
+{{{#!sql
+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;
+$$;
+}}}
+
+== Функции
+
+=== Ноќи помеѓу датуми
+{{{#!sql
+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));
+$$;
+}}}
+
+=== Пресметка на трошокот за резервација
+{{{#!sql
+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;
+$$;
+}}}
+
+== Тригери
+
+=== Автоматско ажурирање на достапноста на собата по одобрувањето
+{{{#!sql
+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();
+}}}
+
+=== Спречување на  бришење корисници со активни резервации
+{{{#!sql
+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();
+}}}