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 ;




Код бэкенда:
require('dotenv').config();
const express = require('express');
const mysql = require('mysql2');
const bcrypt = require('bcryptjs');

const app = express();

// Middleware
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

// Создание подключения к базе данных
const db = mysql.createConnection({
    host: process.env.DB_HOST ,
    port: process.env.DB_PORT ,
    user: process.env.DB_USER ,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    multipleStatements: true
});

// Подключение к базе данных
db.connect((err) => {
    if (!err) {
        console.log('Connected to MySQL database');
    } else {
        console.log('Connection failed!', err);
    }
});

// Middleware для проверки JWT
const authenticateToken = (req, res, next) => {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];
    if (token == null) return res.sendStatus(401); // Если токен отсутствует

    jwt.verify(token, process.env.JWT_SECRET, (err, user) => {
        if (err) return res.sendStatus(403); // Если токен недействителен
        req.user = user;
        next();
    });
};

// Middleware для проверки ролей
const authorizeRoles = (roles) => {
    return (req, res, next) => {
        if (!roles.includes(req.user.role)) {
            return res.status(403).json({ message: 'Access denied.' });
        }
        next();
    };
};

// Роутинг

// Регистрация пользователя
app.post('/signup', async (req, res) => {
    const { username, password, role } = req.body;
    if (!username || !password) {
        return res.status(400).send('Username and password are required.');
    }

    // Проверка существования пользователя
    db.query('SELECT * FROM Users WHERE username = ?', [username], async (err, results) => {
        if (err) {
            return res.status(500).send('Server error.');
        }
        if (results.length > 0) {
            return res.status(400).send('User already exists.');
        }

        // Хеширование пароля
        const hashedPassword = await bcrypt.hash(password, 10);

        // Вставка нового пользователя
        db.query('INSERT INTO Users (username, password, role) VALUES (?, ?, ?)', [username, hashedPassword, role || 'Пользователь'], (err, result) => {
            if (err) {
                return res.status(500).send('Error creating user.');
            }
            res.status(201).send('User registered successfully.');
        });
    });
});

// Авторизация пользователя
app.post('/login', (req, res) => {
    const { username, password } = req.body;
    if (!username || !password) {
        return res.status(400).send('Username and password are required.');
    }

    // Поиск пользователя
    db.query('SELECT * FROM Users WHERE username = ?', [username], async (err, results) => {
        if (err) {
            return res.status(500).send('Server error.');
        }
        if (results.length === 0) {
            return res.status(400).send('Invalid credentials.');
        }

        const user = results[0];

        // Сравнение паролей
        const validPassword = await bcrypt.compare(password, user.password);
        if (!validPassword) {
            return res.status(400).send('Invalid credentials.');
        }

        // Генерация JWT
        const token = jwt.sign({ user_id: user.user_id, username: user.username, role: user.role }, process.env.JWT_SECRET, { expiresIn: '1h' });

        res.json({ token });
    });
});

// CRUD для Guests

// Получение всех гостей (доступно Администратору)
app.get('/guests', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    db.query('SELECT * FROM Guests', (err, results) => {
        if (err) {
            return res.status(500).send('Error fetching guests.');
        }
        res.json(results);
    });
});

// Добавление нового гостя (доступно Администратору и Пользователям)
app.post('/guests', authenticateToken, authorizeRoles(['Администратор', 'Пользователь']), (req, res) => {
    const { name, email, phone_number, address } = req.body;
    if (!name || !email) {
        return res.status(400).send('Name and email are required.');
    }

    db.query('INSERT INTO Guests (name, email, phone_number, address) VALUES (?, ?, ?, ?)', [name, email, phone_number, address], (err, result) => {
        if (err) {
            return res.status(500).send('Error adding guest.');
        }
        res.status(201).send('Guest added successfully.');
    });
});

// Обновление гостя (доступно Администратору)
app.put('/guests/:id', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const guestId = req.params.id;
    const { name, email, phone_number, address } = req.body;

    db.query('UPDATE Guests SET name = ?, email = ?, phone_number = ?, address = ? WHERE guest_id = ?', [name, email, phone_number, address, guestId], (err, result) => {
        if (err) {
            return res.status(500).send('Error updating guest.');
        }
        res.send('Guest updated successfully.');
    });
});

// Удаление гостя (доступно Администратору)
app.delete('/guests/:id', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const guestId = req.params.id;

    db.query('DELETE FROM Guests WHERE guest_id = ?', [guestId], (err, result) => {
        if (err) {
            return res.status(500).send('Error deleting guest.');
        }
        res.send('Guest deleted successfully.');
    });
});

// CRUD для Rooms

// Получение всех номеров (доступно Администратору и Пользователям)
app.get('/rooms', authenticateToken, authorizeRoles(['Администратор', 'Пользователь']), (req, res) => {
    db.query('SELECT * FROM Rooms', (err, results) => {
        if (err) {
            return res.status(500).send('Error fetching rooms.');
        }
        res.json(results);
    });
});

// Добавление нового номера (доступно Администратору)
app.post('/rooms', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const { room_number, room_type, capacity, price_per_night, status } = req.body;
    if (!room_number || !room_type || !capacity || !price_per_night) {
        return res.status(400).send('Missing required fields.');
    }

    db.query('INSERT INTO Rooms (room_number, room_type, capacity, price_per_night, status) VALUES (?, ?, ?, ?, ?)', [room_number, room_type, capacity, price_per_night, status || 'доступен'], (err, result) => {
        if (err) {
            return res.status(500).send('Error adding room.');
        }
        res.status(201).send('Room added successfully.');
    });
});

// Обновление номера (доступно Администратору)
app.put('/rooms/:id', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const roomId = req.params.id;
    const { room_number, room_type, capacity, price_per_night, status } = req.body;

    db.query('UPDATE Rooms SET room_number = ?, room_type = ?, capacity = ?, price_per_night = ?, status = ? WHERE room_id = ?', [room_number, room_type, capacity, price_per_night, status, roomId], (err, result) => {
        if (err) {
            return res.status(500).send('Error updating room.');
        }
        res.send('Room updated successfully.');
    });
});

// Удаление номера (доступно Администратору)
app.delete('/rooms/:id', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const roomId = req.params.id;

    db.query('DELETE FROM Rooms WHERE room_id = ?', [roomId], (err, result) => {
        if (err) {
            return res.status(500).send('Error deleting room.');
        }
        res.send('Room deleted successfully.');
    });
});

// CRUD для Reservations

// Получение всех бронирований (доступно Администратору)
app.get('/reservations', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const query = `
        SELECT r.reservation_id, g.name AS guest_name, rm.room_number, r.check_in_date, r.check_out_date, r.total_cost
        FROM Reservations r
        JOIN Guests g ON r.guest_id = g.guest_id
        JOIN Rooms rm ON r.room_id = rm.room_id
    `;

    db.query(query, (err, results) => {
        if (err) {
            return res.status(500).send('Error fetching reservations.');
        }
        res.json(results);
    });
});

// Добавление нового бронирования (доступно Администратору и Пользователям)
app.post('/reservations', authenticateToken, authorizeRoles(['Администратор', 'Пользователь']), (req, res) => {
    const { guest_id, room_id, check_in_date, check_out_date } = req.body;
    if (!guest_id || !room_id || !check_in_date || !check_out_date) {
        return res.status(400).send('Missing required fields.');
    }

    db.query('INSERT INTO Reservations (guest_id, room_id, check_in_date, check_out_date) VALUES (?, ?, ?, ?)', [guest_id, room_id, check_in_date, check_out_date], (err, result) => {
        if (err) {
            return res.status(500).send('Error creating reservation.');
        }
        res.status(201).send('Reservation created successfully.');
    });
});

// Обновление бронирования (доступно Администратору)
app.put('/reservations/:id', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const reservationId = req.params.id;
    const { guest_id, room_id, check_in_date, check_out_date } = req.body;

    db.query('UPDATE Reservations SET guest_id = ?, room_id = ?, check_in_date = ?, check_out_date = ? WHERE reservation_id = ?', [guest_id, room_id, check_in_date, check_out_date, reservationId], (err, result) => {
        if (err) {
            return res.status(500).send('Error updating reservation.');
        }
        res.send('Reservation updated successfully.');
    });
});

// Удаление бронирования (доступно Администратору)
app.delete('/reservations/:id', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const reservationId = req.params.id;

    db.query('DELETE FROM Reservations WHERE reservation_id = ?', [reservationId], (err, result) => {
        if (err) {
            return res.status(500).send('Error deleting reservation.');
        }
        res.send('Reservation deleted successfully.');
    });
});

// CRUD для Payments

// Получение всех платежей (доступно Администратору)
app.get('/payments', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const query = `
        SELECT p.payment_id, r.reservation_id, p.amount, p.payment_date, p.payment_method
        FROM Payments p
        JOIN Reservations r ON p.reservation_id = r.reservation_id
    `;

    db.query(query, (err, results) => {
        if (err) {
            return res.status(500).send('Error fetching payments.');
        }
        res.json(results);
    });
});

// Добавление нового платежа (доступно Администратору и Пользователям)
app.post('/payments', authenticateToken, authorizeRoles(['Администратор', 'Пользователь']), (req, res) => {
    const { reservation_id, amount, payment_date, payment_method } = req.body;
    if (!reservation_id || !amount || !payment_date) {
        return res.status(400).send('Missing required fields.');
    }

    db.query('INSERT INTO Payments (reservation_id, amount, payment_date, payment_method) VALUES (?, ?, ?, ?)', [reservation_id, amount, payment_date, payment_method], (err, result) => {
        if (err) {
            return res.status(500).send('Error adding payment.');
        }
        res.status(201).send('Payment added successfully.');
    });
});

// Обновление платежа (доступно Администратору)
app.put('/payments/:id', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const paymentId = req.params.id;
    const { reservation_id, amount, payment_date, payment_method } = req.body;

    db.query('UPDATE Payments SET reservation_id = ?, amount = ?, payment_date = ?, payment_method = ? WHERE payment_id = ?', [reservation_id, amount, payment_date, payment_method, paymentId], (err, result) => {
        if (err) {
            return res.status(500).send('Error updating payment.');
        }
        res.send('Payment updated successfully.');
    });
});

// Удаление платежа (доступно Администратору)
app.delete('/payments/:id', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const paymentId = req.params.id;

    db.query('DELETE FROM Payments WHERE payment_id = ?', [paymentId], (err, result) => {
        if (err) {
            return res.status(500).send('Error deleting payment.');
        }
        res.send('Payment deleted successfully.');
    });
});

// CRUD для RoomServices

// Получение всех услуг (доступно Администратору)
app.get('/room-services', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const query = `
        SELECT rs.service_id, rs.reservation_id, rs.service_type, rs.service_date, rs.cost
        FROM RoomServices rs
    `;

    db.query(query, (err, results) => {
        if (err) {
            return res.status(500).send('Error fetching room services.');
        }
        res.json(results);
    });
});

// Добавление новой услуги (доступно Администратору и Пользователям)
app.post('/room-services', authenticateToken, authorizeRoles(['Администратор', 'Пользователь']), (req, res) => {
    const { reservation_id, service_type, service_date, cost } = req.body;
    if (!reservation_id || !service_type || !service_date) {
        return res.status(400).send('Missing required fields.');
    }

    db.query('INSERT INTO RoomServices (reservation_id, service_type, service_date, cost) VALUES (?, ?, ?, ?)', [reservation_id, service_type, service_date, cost], (err, result) => {
        if (err) {
            return res.status(500).send('Error adding room service.');
        }
        res.status(201).send('Room service added successfully.');
    });
});

// Обновление услуги (доступно Администратору)
app.put('/room-services/:id', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const serviceId = req.params.id;
    const { reservation_id, service_type, service_date, cost } = req.body;

    db.query('UPDATE RoomServices SET reservation_id = ?, service_type = ?, service_date = ?, cost = ? WHERE service_id = ?', [reservation_id, service_type, service_date, cost, serviceId], (err, result) => {
        if (err) {
            return res.status(500).send('Error updating room service.');
        }
        res.send('Room service updated successfully.');
    });
});

// Удаление услуги (доступно Администратору)
app.delete('/room-services/:id', authenticateToken, authorizeRoles(['Администратор']), (req, res) => {
    const serviceId = req.params.id;

    db.query('DELETE FROM RoomServices WHERE service_id = ?', [serviceId], (err, result) => {
        if (err) {
            return res.status(500).send('Error deleting room service.');
        }
        res.send('Room service deleted successfully.');
    });
});

// Запуск сервера
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
    console.log(`Server is running on port ${PORT}`);
});



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

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