02_procedures.sql

USE hotel_hk;

-- =========================================
-- Procedure: Create a new booking
-- =========================================
DROP PROCEDURE IF EXISTS sp_create_booking;

DELIMITER $$

CREATE PROCEDURE sp_create_booking(
  IN p_CustomerId BIGINT,
  IN p_RoomId     BIGINT,
  IN p_CheckIn    DATE,
  IN p_CheckOut   DATE
)
BEGIN
  INSERT INTO BOOKING(CustomerId, RoomId, CheckIn, CheckOut, Status)
  VALUES (p_CustomerId, p_RoomId, p_CheckIn, p_CheckOut, 'RESERVED');
END$$

DELIMITER ;

-- =========================================
-- Procedure: Check out a booking
-- (The trigger will automatically create a CLEANING_TASK and set ROOM status to DIRTY)
-- =========================================
DROP PROCEDURE IF EXISTS sp_checkout;

DELIMITER $$

CREATE PROCEDURE sp_checkout(
  IN p_BookingId BIGINT
)
BEGIN
  UPDATE BOOKING
  SET Status = 'CHECKED_OUT'
  WHERE BookingId = p_BookingId;
END$$

DELIMITER ;