---

```sql
-- Практическая работа № 6: Создание базы данных информационной системы в MySQL
-- База данных: repair_service

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP DATABASE IF EXISTS repair_service;

CREATE DATABASE repair_service
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE repair_service;

-- 1. Таблица ролей пользователей (roles)
CREATE TABLE roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Таблица пользователей и клиентов системы (users)
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id INT UNSIGNED NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_role
        FOREIGN KEY (role_id)
        REFERENCES roles(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Таблица сотрудников и мастеров сервисного центра (employees)
CREATE TABLE employees (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    specialization VARCHAR(150),
    phone VARCHAR(20),
    email VARCHAR(150)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Справочник статусов заявок (request_statuses)
CREATE TABLE request_statuses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Основная таблица заявок на ремонт (repair_requests)
CREATE TABLE repair_requests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    employee_id INT UNSIGNED NULL,
    status_id INT UNSIGNED NOT NULL,
    device_type VARCHAR(100) NOT NULL,
    device_model VARCHAR(150),
    problem_description TEXT NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_requests_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_requests_employee
        FOREIGN KEY (employee_id)
        REFERENCES employees(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_requests_status
        FOREIGN KEY (status_id)
        REFERENCES request_statuses(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Таблица комментариев к заявкам (comments)
CREATE TABLE comments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id INT UNSIGNED NOT NULL,
    text TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_comments_request
        FOREIGN KEY (request_id)
        REFERENCES repair_requests(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Таблица уведомлений пользователей (notifications)
CREATE TABLE notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    text VARCHAR(255) NOT NULL,
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_notifications_request
        FOREIGN KEY (request_id)
        REFERENCES repair_requests(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_notifications_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- Заполнение справочника ролей
INSERT INTO roles (name)
VALUES
('Клиент'),
('Мастер'),
('Администратор');

-- Заполнение справочника статусов
INSERT INTO request_statuses (name)
VALUES
('Новая'),
('Принята'),
('Диагностика'),
('В ремонте'),
('Ожидание запчастей'),
('Готова'),
('Выдана'),
('Отменена');

-- Добавление тестовых пользователей
INSERT INTO users (role_id, full_name, phone, email, password)
VALUES
(1, 'Иванов Михаил Сергеевич', '+79990000001', 'ivanov@example.ru', 'demo_password'),
(1, 'Петров Алексей Иванович', '+79990000002', 'petrov@example.ru', 'demo_password'),
(3, 'Смирнов Андрей Олегович', '+79990000003', 'admin@example.ru', 'demo_password');

-- Добавление сотрудников
INSERT INTO employees (full_name, specialization, phone, email)
VALUES
('Козлов Максим Игоревич', 'Ремонт ноутбуков', '+79991111111', 'kozlov@service.ru'),
('Соколов Дмитрий Андреевич', 'Ремонт персональных компьютеров', '+79992222222', 'sokolov@service.ru'),
('Орлов Роман Сергеевич', 'Диагностика оборудования', '+79993333333', 'orlov@service.ru');

-- Создание тестовых заявок
INSERT INTO repair_requests (user_id, employee_id, status_id, device_type, device_model, problem_description, price)
VALUES
(1, 1, 1, 'Ноутбук', 'ASUS VivoBook 15', 'Ноутбук не включается после нажатия кнопки питания', 0.00),
(2, NULL, 1, 'Персональный компьютер', 'Сборный ПК', 'Компьютер включается, но изображение на мониторе отсутствует', 0.00);

-- Добавление комментариев
INSERT INTO comments (request_id, text)
VALUES
(1, 'Устройство принято на диагностику.'),
(1, 'Предварительно обнаружена неисправность блока питания.');

-- Добавление уведомлений
INSERT INTO notifications (request_id, user_id, text)
VALUES
(1, 1, 'Ваша заявка №1 принята в работу.'),
(2, 2, 'Ваша заявка №2 зарегистрирована в системе.');

-- Изменение данных в первой заявке (статус «Диагностика» и стоимость 3500.00)
UPDATE repair_requests
SET status_id = 3,
    price = 3500.00
WHERE id = 1;

```