-- Создание базы данных
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
);

добавь триггера которые необходимы