-- Optional extra protection: database triggers that refuse double-bookings.
-- Import AFTER zues-database.sql. If it fails with "#1419 ... SUPER privilege",
-- your host does not allow triggers; the app still refuses double-bookings itself.

DROP TRIGGER IF EXISTS room_blocks_before_update;
DROP TRIGGER IF EXISTS room_blocks_before_insert;
DROP TRIGGER IF EXISTS bookings_before_update;
DROP TRIGGER IF EXISTS bookings_before_insert;
DROP PROCEDURE IF EXISTS assert_room_bookable;

DELIMITER $$

CREATE PROCEDURE assert_room_bookable(
    IN p_room_id BIGINT UNSIGNED,
    IN p_check_in DATE,
    IN p_check_out DATE,
    IN p_booking_id BIGINT UNSIGNED
)
BEGIN
    DECLARE v_room_id BIGINT UNSIGNED;
    DECLARE v_hits INT;

    SELECT id INTO v_room_id FROM rooms WHERE id = p_room_id FOR UPDATE;

    SELECT COUNT(*) INTO v_hits FROM bookings
    WHERE room_id = p_room_id
      AND status <> 'cancelled'
      AND (p_booking_id IS NULL OR id <> p_booking_id)
      AND check_in < p_check_out
      AND check_out > p_check_in
    LOCK IN SHARE MODE;

    IF v_hits > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'room_double_booked';
    END IF;

    SELECT COUNT(*) INTO v_hits FROM room_blocks
    WHERE room_id = p_room_id
      AND start_date < p_check_out
      AND end_date >= p_check_in
    LOCK IN SHARE MODE;

    IF v_hits > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'room_blocked';
    END IF;
END$$

CREATE TRIGGER bookings_before_insert BEFORE INSERT ON bookings
FOR EACH ROW
BEGIN
    IF NEW.status <> 'cancelled' THEN
        CALL assert_room_bookable(NEW.room_id, NEW.check_in, NEW.check_out, NULL);
    END IF;
END$$

CREATE TRIGGER bookings_before_update BEFORE UPDATE ON bookings
FOR EACH ROW
BEGIN
    IF NEW.status <> 'cancelled' AND (
        OLD.status = 'cancelled'
        OR NEW.room_id <> OLD.room_id
        OR NEW.check_in <> OLD.check_in
        OR NEW.check_out <> OLD.check_out
    ) THEN
        CALL assert_room_bookable(NEW.room_id, NEW.check_in, NEW.check_out, OLD.id);
    END IF;
END$$

CREATE TRIGGER room_blocks_before_insert BEFORE insert ON room_blocks
FOR EACH ROW
BEGIN
    DECLARE v_room_id BIGINT UNSIGNED;
    DECLARE v_hits INT;

    SELECT id INTO v_room_id FROM rooms WHERE id = NEW.room_id FOR UPDATE;

    SELECT COUNT(*) INTO v_hits FROM bookings
    WHERE room_id = NEW.room_id
      AND status <> 'cancelled'
      AND check_in <= NEW.end_date
      AND check_out > NEW.start_date
    LOCK IN SHARE MODE;

    IF v_hits > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'room_has_bookings';
    END IF;
END$$

CREATE TRIGGER room_blocks_before_update BEFORE update ON room_blocks
FOR EACH ROW
BEGIN
    DECLARE v_room_id BIGINT UNSIGNED;
    DECLARE v_hits INT;

    SELECT id INTO v_room_id FROM rooms WHERE id = NEW.room_id FOR UPDATE;

    SELECT COUNT(*) INTO v_hits FROM bookings
    WHERE room_id = NEW.room_id
      AND status <> 'cancelled'
      AND check_in <= NEW.end_date
      AND check_out > NEW.start_date
    LOCK IN SHARE MODE;

    IF v_hits > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'room_has_bookings';
    END IF;
END$$

DELIMITER ;