CREATE DATABASE `hotel`
USE hotel;



-- Таблица Guests (Гости)
CREATE TABLE Guests (
    guest_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    passport_number VARCHAR(20) UNIQUE NOT NULL,
    phone_number VARCHAR(15) UNIQUE NOT NULL,
    login VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    last_login TIMESTAMP
);


-- Таблица 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(20) 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
);

-- Таблица Rooms (Номера)
CREATE TABLE Rooms (
    room_id INT AUTO_INCREMENT PRIMARY KEY,
    room_number VARCHAR(10) NOT NULL,
    images JSON,
    room_type ENUM('стандартный', 'улучшенный', 'люкс') DEFAULT 'стандартный,
    capacity ENUM('одноместный', 'двухместный', 'двухместный', 'трёхместный', 'четырехместный') DEFAULT 'одноместный',
    price_per_night DECIMAL(10, 2) NOT NULL,
    status ENUM('доступен', 'занят') DEFAULT 'доступен'
);



-- Таблица Reservations (Бронирования)
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
);








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 ;




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