CREATE DATABASE hotel
USE hotel;
CREATE TABLE Guests (
    guest_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone_number VARCHAR(15),
    address TEXT
);
CREATE TABLE Rooms (
    room_id INT AUTO_INCREMENT PRIMARY KEY,
    room_number VARCHAR(10) NOT NULL,
    room_type VARCHAR(50) NOT NULL,
    capacity INT NOT NULL,
    price_per_night DECIMAL(10, 2) NOT NULL,
    status ENUM('доступен', 'занят', 'уборка') DEFAULT 'доступен'
);
CREATE TABLE Reservations (
    reservation_id INT AUTO_INCREMENT PRIMARY KEY,
    guest_id INT,
    room_id INT,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    total_cost DECIMAL(10, 2),
    FOREIGN KEY (guest_id) REFERENCES Guests(guest_id) ON DELETE CASCADE,
    FOREIGN KEY (room_id) REFERENCES Rooms(room_id) ON DELETE CASCADE
);
CREATE TABLE Payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    reservation_id INT,
    amount DECIMAL(10, 2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method VARCHAR(50),
    FOREIGN KEY (reservation_id) REFERENCES Reservations(reservation_id) ON DELETE CASCADE
);
CREATE TABLE RoomServices (
    service_id INT AUTO_INCREMENT PRIMARY KEY,
    reservation_id INT,
    service_type ENUM('уборка', 'доставка еды', 'техническое обслуживание') NOT NULL,
    service_date DATE NOT NULL,
    cost DECIMAL(10, 2),
    FOREIGN KEY (reservation_id) REFERENCES Reservations(reservation_id) ON DELETE CASCADE
);
DELIMITER //
CREATE TRIGGER calc_total_cost
BEFORE INSERT ON Reservations
FOR EACH ROW
BEGIN
    DECLARE nights INT;
    DECLARE room_price DECIMAL(10, 2);
    SET nights = DATEDIFF(NEW.check_out_date, NEW.check_in_date);
    SELECT price_per_night INTO room_price FROM Rooms WHERE room_id = NEW.room_id;
    SET NEW.total_cost = nights * room_price;
END//
DELIMITER ;
DELIMITER //
CREATE TRIGGER update_room_status
AFTER INSERT ON Reservations
FOR EACH ROW
BEGIN
    UPDATE Rooms
    SET status = 'occupied'
    WHERE room_id = NEW.room_id;
END//
DELIMITER ;
DELIMITER //
CREATE TRIGGER free_room_on_cancel
AFTER DELETE ON Reservations
FOR EACH ROW
BEGIN
    UPDATE Rooms
    SET status = 'available'
    WHERE room_id = OLD.room_id;
END//
DELIMITER ;
DELIMITER //
CREATE TRIGGER check_room_capacity
BEFORE INSERT ON Reservations
FOR EACH ROW
BEGIN
    DECLARE room_capacity INT;
    SELECT capacity INTO room_capacity FROM Rooms WHERE room_id = NEW.room_id;
    IF room_capacity < (SELECT COUNT(*) FROM Guests WHERE guest_id = NEW.guest_id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Room capacity exceeded';
    END IF;
END//
DELIMITER ;
DELIMITER //
CREATE TRIGGER default_payment_amount
BEFORE INSERT ON Payments
FOR EACH ROW
BEGIN
    IF NEW.amount IS NULL THEN
        SET NEW.amount = (SELECT total_cost FROM Reservations WHERE reservation_id = NEW.reservation_id);
    END IF;
END//
DELIMITER ;

это код для базы данных который у меня есть сейчас думаю нужно добавить в таблицу гостей для каждого гостя значения логин и пароль чтобы при регистрации можно будет выбрать окна либо гость либо администратор
для этого нужно будет создать новую таблицу с администраторами там
будут значения логин, пароль, полное имя, паспортные данные, адрес проживания, номер телефона, электронная почта и уникальный код который нужен для регистрации в качестве администратора 

нужно убрать значение адрес у гостей кстати и убрать таблицу roomservice