-- Создание базы данных
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(30) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    login VARCHAR(30) UNIQUE NOT NULL,
    password VARCHAR(255) 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
);

-- Таблица Shifts
CREATE TABLE Shifts (
    shift_id INT AUTO_INCREMENT PRIMARY KEY,
    shift_name VARCHAR(50) NOT NULL UNIQUE,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL
);

-- Вставка фиксированных смен
INSERT INTO Shifts (shift_name, start_time, end_time) VALUES
('Ночь', '00:00:00', '08:00:00'),
('Утро', '08:00:00', '16:00:00'),
('Вечер', '16:00:00', '00:00:00');

-- Таблица 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('Свободен', 'Занят') DEFAULT 'Свободен',
    shift_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (shift_id) REFERENCES Shifts(shift_id) ON DELETE RESTRICT ON UPDATE CASCADE
);


-- Таблица Rooms
CREATE TABLE Rooms (
    room_id INT AUTO_INCREMENT PRIMARY KEY,
    room_number INT UNIQUE NOT NULL,
    status ENUM('Доступен', 'Недоступен') DEFAULT 'Доступен',
    price DECIMAL(10,2) NOT NULL,
    bed_type ENUM('Одноместный', 'Двухместный', 'Трехместный', 'Четырехместный') NOT NULL,
    room_type ENUM('Стандарт', 'Люкс', 'Делюкс') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Таблица 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('Назначено', 'В процессе', 'Завершено') DEFAULT 'Назначено',
    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('Мужской', 'Женский'),
    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('Забронирован', 'Заселён', 'Выселен', 'Отменён') DEFAULT 'Забронирован',
    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('Наличные', 'Кредитная карта', 'Дебетовая карта', 'Онлайн перевод') 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('Ручное назначение', 'Автоматическое назначение') NOT NULL,
    scheduled_time DATETIME NOT NULL,
    status ENUM('Запланировано', 'В процессе', 'Завершено') DEFAULT 'Запланировано',
    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
);






DELIMITER $$

CREATE TRIGGER trg_update_room_availability
AFTER UPDATE ON Reservations
FOR EACH ROW
BEGIN
    IF NEW.status = 'Забронирован' OR NEW.status = 'Заселён' THEN
        UPDATE Rooms
        SET available = FALSE,
            status = 'Недоступен'
        WHERE room_id = NEW.room_id;
    ELSEIF NEW.status = 'Отменён' OR NEW.status = 'Выселен' THEN
        UPDATE Rooms
        SET available = TRUE,
            status = 'Доступен'
        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 ('Отменён')
          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 ;





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 ;




DELIMITER $$

CREATE TRIGGER trg_update_cleaner_status_after_action_complete
AFTER UPDATE ON Cleaning_Actions
FOR EACH ROW
BEGIN
    IF NEW.status = 'Завершено' THEN
        UPDATE Cleaners
        SET status = 'Свободен'
        WHERE cleaner_id = NEW.cleaner_id;
    ELSEIF NEW.status = 'В процессе' THEN
        UPDATE Cleaners
        SET status = 'Занят'
        WHERE cleaner_id = NEW.cleaner_id;
    END IF;
END$$

DELIMITER ;

















-- Добавление администратора
INSERT INTO Administrators (
    full_name, 
    passport_number, 
    address, 
    phone_number, 
    email, 
    login, 
    password
) VALUES (
    'Иванов Иван Иванович',
    '4510 123456',
    'г. Москва, ул. Ленина, д. 1, кв. 1',
    '+7(999)123-45-67',
    'ivanov@hotel.ru',
    'admin',
    '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewandQ3REH3P5DOK' -- хешированный пароль "admin123"
);












-- Добавление комнат
INSERT INTO Rooms (room_number, price, bed_type, room_type) VALUES
(101, 3000.00, 'Одноместный', 'Стандарт'),
(102, 3500.00, 'Двухместный', 'Стандарт'),
(201, 4000.00, 'Двухместный', 'Стандарт'),
(202, 4500.00, 'Двухместный', 'Делюкс'),
(301, 5000.00, 'Двухместный', 'Люкс'),
(302, 5500.00, 'Трехместный', 'Люкс'),
(401, 6000.00, 'Четырехместный', 'Люкс'),
(402, 4200.00, 'Двухместный', 'Делюкс'),
(501, 3800.00, 'Двухместный', 'Стандарт'),
(502, 7000.00, 'Четырехместный', 'Люкс');











-- Добавление уборщиков
INSERT INTO Cleaners (full_name, phone_number, email, shift_id) VALUES
('Петрова Анна Сергеевна', '+7(999)111-22', 'petrova@hotel.ru', 1),
('Сидорова Елена Павловна', '+7(999)222-33', 'sidorova@hotel.ru', 2),
('Козлова Мария Ивановна', '+7(999)333-44', 'kozlova@hotel.ru', 3),
('Николаева Ольга Дмитриевна', '+7(999)444-55', 'nikolaeva@hotel.ru', 1),
('Морозова Татьяна Александровна', '+7(999)555-66', 'morozova@hotel.ru', 2);












-- Добавление изображений для номеров
INSERT INTO Room_Images (room_id, image_url, is_primary) VALUES
(1, '/images/rooms/101/main.jpg', TRUE),
(2, '/images/rooms/101/bathroom.jpg', TRUE),
(3, '/images/rooms/101/view.jpg', TRUE),
(4, '/images/rooms/102/main.jpg', TRUE),
(5, '/images/rooms/102/bathroom.jpg', TRUE),
(6, '/images/rooms/201/main.jpg', TRUE),
(7, '/images/rooms/201/living.jpg', TRUE),
(8, '/images/rooms/202/main.jpg', TRUE),
(9, '/images/rooms/202/bathroom.jpg', TRUE),
(10, '/images/rooms/301/main.jpg', TRUE);




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