-- Создание базы данных
CREATE DATABASE IF NOT EXISTS hotel;
USE hotel;

-- Таблица Administrators
CREATE TABLE Administrators (
    admin_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    passport_number VARCHAR(20) UNIQUE NOT NULL,
    address TEXT NOT NULL,
    phone_number VARCHAR(15) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    login VARCHAR(20) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    registration_code VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Таблица Receptionists
CREATE TABLE Receptionists (
    receptionist_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    passport_number VARCHAR(20) UNIQUE NOT NULL,
    address TEXT NOT NULL,
    phone_number VARCHAR(15) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    login VARCHAR(20) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    admin_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES Administrators(admin_id) ON DELETE CASCADE
);

-- Таблица Cleaners
CREATE TABLE Cleaners (
    cleaner_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    phone_number VARCHAR(15) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    status ENUM('Free', 'Busy') DEFAULT 'Free',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Таблица Rooms
CREATE TABLE Rooms (
    room_id INT AUTO_INCREMENT PRIMARY KEY,
    room_number INT UNIQUE NOT NULL,
    available BOOLEAN DEFAULT TRUE,
    status ENUM('Available', 'Occupied', 'Maintenance') DEFAULT 'Available',
    price DECIMAL(10,2) NOT NULL,
    bed_type ENUM('Single', 'Double', 'Queen', 'King', 'Twin') NOT NULL,
    room_type VARCHAR(50),
    admin_id INT,  -- Changed to allow NULL
    receptionist_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES Administrators(admin_id) ON DELETE SET NULL,
    FOREIGN KEY (receptionist_id) REFERENCES Receptionists(receptionist_id) ON DELETE SET NULL
);

-- Таблица Cleaning_Schedules
CREATE TABLE Cleaning_Schedules (
    schedule_id INT AUTO_INCREMENT PRIMARY KEY,
    room_id INT NOT NULL,
    cleaner_id INT NOT NULL,
    scheduled_time DATETIME NOT NULL,
    status ENUM('Scheduled', 'In Progress', 'Completed') DEFAULT 'Scheduled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (room_id) REFERENCES Rooms(room_id) ON DELETE CASCADE,
    FOREIGN KEY (cleaner_id) REFERENCES Cleaners(cleaner_id) ON DELETE CASCADE
);

-- Таблица Room_Images
CREATE TABLE Room_Images (
    image_id INT AUTO_INCREMENT PRIMARY KEY,
    room_id INT NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (room_id) REFERENCES Rooms(room_id) ON DELETE CASCADE
);

-- Таблица Customers
CREATE TABLE Customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(15) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE,
    gender ENUM('Male', 'Female', 'Other'),
    country VARCHAR(50),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    receptionist_id INT,
    FOREIGN KEY (receptionist_id) REFERENCES Receptionists(receptionist_id) ON DELETE SET NULL
);

-- Таблица Reservations
CREATE TABLE Reservations (
    reservation_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    room_id INT NOT NULL,
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    status ENUM('Booked', 'Checked-in', 'Checked-out', 'Cancelled') DEFAULT 'Booked',
    total_amount DECIMAL(10,2),
    deposit DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    receptionist_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE,
    FOREIGN KEY (room_id) REFERENCES Rooms(room_id) ON DELETE CASCADE,
    FOREIGN KEY (receptionist_id) REFERENCES Receptionists(receptionist_id) ON DELETE SET NULL
);

-- Таблица Payments
CREATE TABLE Payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    reservation_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method ENUM('Cash', 'Credit Card', 'Debit Card', 'Online Transfer') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reservation_id) REFERENCES Reservations(reservation_id) ON DELETE CASCADE
);

-- Таблица Cleaning_Actions
CREATE TABLE Cleaning_Actions (
    action_id INT AUTO_INCREMENT PRIMARY KEY,
    reservation_id INT NOT NULL,
    room_id INT NOT NULL,
    cleaner_id INT NOT NULL,
    action_type ENUM('Manual Assignment', 'Auto Assignment') NOT NULL,
    scheduled_time DATETIME NOT NULL,
    status ENUM('Scheduled', 'In Progress', 'Completed') DEFAULT 'Scheduled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (reservation_id) REFERENCES Reservations(reservation_id) ON DELETE CASCADE,
    FOREIGN KEY (room_id) REFERENCES Rooms(room_id) ON DELETE CASCADE,
    FOREIGN KEY (cleaner_id) REFERENCES Cleaners(cleaner_id) ON DELETE CASCADE
);






 Триггер для обновления статуса номера при изменении статуса бронирования
Этот триггер автоматически обновляет статус номера в таблице Rooms в зависимости от статуса бронирования в таблице Reservations.

DELIMITER $$

CREATE TRIGGER trg_update_room_availability
AFTER UPDATE ON Reservations
FOR EACH ROW
BEGIN
    IF NEW.status = 'Booked' OR NEW.status = 'Checked-in' THEN
        UPDATE Rooms
        SET available = FALSE,
            status = 'Occupied'
        WHERE room_id = NEW.room_id;
    ELSEIF NEW.status = 'Cancelled' OR NEW.status = 'Checked-out' THEN
        UPDATE Rooms
        SET available = TRUE,
            status = 'Available'
        WHERE room_id = NEW.room_id;
    END IF;
END$$

DELIMITER ;




Триггер для предотвращения перекрывающихся бронирований
Этот триггер предотвращает создание или обновление бронирования, если выбранный номер уже забронирован на указанные даты.

DELIMITER $$

CREATE TRIGGER trg_prevent_overlapping_reservations
BEFORE INSERT ON Reservations
FOR EACH ROW
BEGIN
    IF EXISTS (
        SELECT 1 FROM Reservations
        WHERE room_id = NEW.room_id
          AND status NOT IN ('Cancelled')
          AND (
              (NEW.check_in BETWEEN check_in AND check_out) OR
              (NEW.check_out BETWEEN check_in AND check_out) OR
              (check_in BETWEEN NEW.check_in AND NEW.check_out)
          )
    ) THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Номер уже забронирован на указанные даты.';
    END IF;
END$$

DELIMITER ;





Триггер для автоматического расчета общей суммы бронирования
Этот триггер автоматически рассчитывает общую сумму бронирования (total_amount) на основе цены номера и длительности пребывания при вставке или обновлении бронирования.
DELIMITER $$

CREATE TRIGGER trg_calculate_total_amount_before_insert
BEFORE INSERT ON Reservations
FOR EACH ROW
BEGIN
    DECLARE roomPrice DECIMAL(10,2);
    DECLARE days INT;

    SELECT price INTO roomPrice FROM Rooms WHERE room_id = NEW.room_id;
    SET days = DATEDIFF(NEW.check_out, NEW.check_in);
    IF days <= 0 THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Дата выезда должна быть позже даты заезда.';
    END IF;
    SET NEW.total_amount = roomPrice * days;
END$$

CREATE TRIGGER trg_calculate_total_amount_before_update
BEFORE UPDATE ON Reservations
FOR EACH ROW
BEGIN
    DECLARE roomPrice DECIMAL(10,2);
    DECLARE days INT;

    SELECT price INTO roomPrice FROM Rooms WHERE room_id = NEW.room_id;
    SET days = DATEDIFF(NEW.check_out, NEW.check_in);
    IF days <= 0 THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Дата выезда должна быть позже даты заезда.';
    END IF;
    SET NEW.total_amount = roomPrice * days;
END$$

DELIMITER ;
Триггер для обновления статуса уборщика при завершении действия уборки

Этот триггер автоматически обновляет статус уборщика в таблице `Cleaners`, когда действие уборки завершается.

DELIMITER $$

CREATE TRIGGER trg_update_cleaner_status_after_action_complete
AFTER UPDATE ON Cleaning_Actions
FOR EACH ROW
BEGIN
    IF NEW.status = 'Completed' THEN
        UPDATE Cleaners
        SET status = 'Free'
        WHERE cleaner_id = NEW.cleaner_id;
    ELSIF NEW.status = 'In Progress' THEN
        UPDATE Cleaners
        SET status = 'Busy'
        WHERE cleaner_id = NEW.cleaner_id;
    END IF;
END$$

DELIMITER ;


### Примечания

- **DELIMITER**: В MySQL по умолчанию точка с запятой (`;`) используется как разделитель команд. Однако, при создании триггеров, которые содержат несколько SQL-команд внутри, необходимо временно изменить разделитель на другой, например, `$$`, чтобы избежать преждевременного завершения триггера.
  
- **Проверки и ограничения**: Триггеры содержат проверки, такие как обеспечение корректности дат бронирования и предотвращение перекрытия бронирований. Это помогает поддерживать целостность данных на уровне базы.

- **Сообщения об ошибках**: Использование `SIGNAL SQLSTATE` позволяет возвращать понятные сообщения об ошибках, которые можно обработать на уровне приложения.

Эти триггеры помогут автоматизировать основные бизнес-процессы в системе управления гостиницей и обеспечить целостность данных. При необходимости вы можете добавлять дополнительные триггеры в соответствии с другими требованиями вашей системы.



напиши ТЗ для приложения которое можно разработать по данной базе данных 
я хочу чтобы это приложение было таким будто ресепшионист стоит за стойкой  и управляет заселением клиентов и прочими действиями доступными для ресепшиониста и при необходимости в приложение может заходить администратор и добавлять допустим новых ресепшионистов и уборщиков, удалять их или редактировать их данные. также удалять редактировать и добавлять новые номера.
уборщик это не роль для пользования приложением он просто автоматически или при назначении ресепшиониста делает уборку в номерах