---

-- =====================================================================
--  5 БАЗ ДАННЫХ — ЕДИНЫЙ СКРИПТ (MySQL Workbench)
--  СУБД: MySQL 8.x, InnoDB, utf8mb4
--  В каждой БД ≥6 таблиц, ≥10 строк в каждой, явные именованные FK
-- =====================================================================


-- #####################################################################
-- #                    1) КАРШЕРИНГ                                    #
-- #####################################################################
DROP DATABASE IF EXISTS carsharing_db;
CREATE DATABASE carsharing_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE carsharing_db;

CREATE TABLE users (
    user_id    INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name  VARCHAR(50),
    phone      VARCHAR(30),
    email      VARCHAR(100),
    license_no VARCHAR(30),
    birth_date DATE,
    registered_at DATE,
    status     VARCHAR(20) DEFAULT 'active',
    UNIQUE KEY uq_user_phone (phone),
    UNIQUE KEY uq_user_license (license_no)
) ENGINE=InnoDB;

CREATE TABLE cars (
    car_id     INT AUTO_INCREMENT PRIMARY KEY,
    brand      VARCHAR(50),
    model      VARCHAR(50),
    plate      VARCHAR(15),
    year       INT,
    fuel_type  VARCHAR(20),
    transmission VARCHAR(20),
    status     VARCHAR(20) DEFAULT 'available',
    mileage    INT DEFAULT 0,
    UNIQUE KEY uq_car_plate (plate)
) ENGINE=InnoDB;

CREATE TABLE tariffs (
    tariff_id  INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(50),
    price_per_min DECIMAL(8,2),
    price_per_km  DECIMAL(8,2),
    day_rate      DECIMAL(10,2),
    description   VARCHAR(200),
    UNIQUE KEY uq_tariff_name (name)
) ENGINE=InnoDB;

CREATE TABLE parking_locations (
    location_id INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100),
    address     VARCHAR(200),
    city        VARCHAR(60),
    latitude    DECIMAL(9,6),
    longitude   DECIMAL(9,6)
) ENGINE=InnoDB;

CREATE TABLE rentals (
    rental_id    INT AUTO_INCREMENT PRIMARY KEY,
    user_id      INT NOT NULL,
    car_id       INT NOT NULL,
    tariff_id    INT NOT NULL,
    start_location_id INT,
    end_location_id   INT,
    start_time   DATETIME,
    end_time     DATETIME,
    total_cost   DECIMAL(10,2),
    status       VARCHAR(20) DEFAULT 'active',
    CONSTRAINT fk_rental_user  FOREIGN KEY (user_id)  REFERENCES users(user_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_rental_car   FOREIGN KEY (car_id)   REFERENCES cars(car_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_rental_tariff FOREIGN KEY (tariff_id) REFERENCES tariffs(tariff_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_rental_start FOREIGN KEY (start_location_id) REFERENCES parking_locations(location_id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_rental_end   FOREIGN KEY (end_location_id)   REFERENCES parking_locations(location_id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    rental_id  INT NOT NULL,
    amount     DECIMAL(10,2),
    method     VARCHAR(30),
    paid_at    DATETIME,
    status     VARCHAR(20) DEFAULT 'paid',
    CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rentals(rental_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE maintenance (
    maint_id   INT AUTO_INCREMENT PRIMARY KEY,
    car_id     INT NOT NULL,
    maint_date DATE,
    type       VARCHAR(50),
    cost       DECIMAL(10,2),
    description VARCHAR(200),
    CONSTRAINT fk_maint_car FOREIGN KEY (car_id) REFERENCES cars(car_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO users (first_name,last_name,phone,email,license_no,birth_date,registered_at,status) VALUES
('Иван','Петров','+79001111111','petrov@mail.ru','4501 111111','1990-05-12','2023-01-10','active'),
('Анна','Сидорова','+79002222222','sidorova@mail.ru','4502 222222','1992-08-23','2023-02-15','active'),
('Олег','Кузнецов','+79003333333','kuznetsov@mail.ru','4503 333333','1988-02-14','2023-03-20','active'),
('Мария','Иванова','+79004444444','ivanova@mail.ru','4504 444444','1995-11-03','2023-04-05','active'),
('Дмитрий','Смирнов','+79005555555','smirnov@mail.ru','4505 555555','1985-07-19','2023-05-11','blocked'),
('Елена','Попова','+79006666666','popova@mail.ru','4506 666666','1998-01-25','2023-06-18','active'),
('Сергей','Васильев','+79007777777','vasiliev@mail.ru','4507 777777','2000-03-11','2023-07-22','active'),
('Ольга','Новикова','+79008888888','novikova@mail.ru','4508 888888','1993-09-30','2023-08-30','active'),
('Павел','Морозов','+79009999999','morozov@mail.ru','4509 999999','1980-12-05','2023-09-14','active'),
('Ирина','Зайцева','+79000000000','zaytseva@mail.ru','4510 000000','1997-06-18','2023-10-01','active');

INSERT INTO cars (brand,model,plate,year,fuel_type,transmission,status,mileage) VALUES
('Kia','Rio','А001АА',2022,'petrol','auto','available',15000),
('Hyundai','Solaris','А002АА',2021,'petrol','auto','available',35000),
('Skoda','Octavia','А003АА',2023,'petrol','auto','rented',8000),
('Toyota','Camry','А004АА',2020,'petrol','auto','available',60000),
('BMW','320i','А005АА',2022,'petrol','auto','maintenance',20000),
('Mercedes','C200','А006АА',2021,'diesel','auto','available',45000),
('Lada','Vesta','А007АА',2023,'petrol','manual','available',5000),
('Renault','Logan','А008АА',2020,'petrol','manual','available',80000),
('Volkswagen','Polo','А009АА',2022,'petrol','auto','available',25000),
('Nissan','Qashqai','А010АА',2021,'petrol','auto','rented',40000);

INSERT INTO tariffs (name,price_per_min,price_per_km,day_rate,description) VALUES
('Минута',9.00,0.00,NULL,'Поминутная оплата'),
('Стандарт',7.00,8.00,NULL,'Поминутно + км'),
('Эконом',5.00,6.00,NULL,'Дешёвый тариф'),
('Сутки',0.00,0.00,2500.00,'Аренда на сутки'),
('Выходные',0.00,0.00,3500.00,'Пт-Вс'),
('Неделя',0.00,0.00,14000.00,'7 дней'),
('Комфорт',12.00,12.00,NULL,'Бизнес-класс'),
('Доставка',15.00,0.00,NULL,'Доставка авто'),
('Корпоратив',6.00,5.00,NULL,'Для юрлиц'),
('Пробная',1.00,0.00,NULL,'Первая поездка');

INSERT INTO parking_locations (name,address,city,latitude,longitude) VALUES
('Парк-Москва-1','Тверская 1','Москва',55.755826,37.617300),
('Парк-Москва-2','Арбат 10','Москва',55.752000,37.592000),
('Парк-СПб-1','Невский 5','СПб',59.934280,30.335098),
('Парк-СПб-2','Дворцовая 1','СПб',59.939100,30.315700),
('Парк-Казань','Баумана 10','Казань',55.788700,49.122100),
('Парк-Екб','Мира 3','Екб',56.838900,60.605700),
('Парк-Нск','Ленина 20','Нск',55.030200,82.920400),
('Парк-Сочи','Курортный 5','Сочи',43.585500,39.723100),
('Парк-Ростов','Садовая 12','Ростов',47.222500,39.718700),
('Парк-Уфа','Ленина 8','Уфа',54.738800,55.972100);

INSERT INTO rentals (user_id,car_id,tariff_id,start_location_id,end_location_id,start_time,end_time,total_cost,status) VALUES
(1,1,1,1,2,'2024-05-01 08:00','2024-05-01 09:30',810.00,'completed'),
(2,2,2,2,3,'2024-05-02 10:00','2024-05-02 12:00',1200.00,'completed'),
(3,3,3,3,4,'2024-05-03 14:00','2024-05-03 16:30',1500.00,'completed'),
(4,4,4,4,5,'2024-05-04 09:00','2024-05-04 11:00',700.00,'completed'),
(5,5,5,5,6,'2024-05-05 12:00','2024-05-05 18:00',3500.00,'completed'),
(6,6,6,6,7,'2024-05-06 07:00','2024-05-06 09:00',600.00,'completed'),
(7,7,7,7,8,'2024-05-07 15:00','2024-05-07 17:00',800.00,'completed'),
(8,8,8,8,9,'2024-05-08 11:00','2024-05-08 13:00',450.00,'completed'),
(9,9,9,9,10,'2024-05-09 16:00',NULL,0.00,'active'),
(10,10,10,10,1,'2024-05-10 19:00',NULL,0.00,'active');

INSERT INTO payments (rental_id,amount,method,paid_at,status) VALUES
(1,810.00,'card','2024-05-01 09:35','paid'),
(2,1200.00,'card','2024-05-02 12:05','paid'),
(3,1500.00,'sbp','2024-05-03 16:35','paid'),
(4,700.00,'card','2024-05-04 11:05','paid'),
(5,3500.00,'card','2024-05-05 18:05','paid'),
(6,600.00,'sbp','2024-05-06 09:05','paid'),
(7,800.00,'card','2024-05-07 17:05','paid'),
(8,450.00,'cash','2024-05-08 13:05','paid'),
(9,0.00,'card',NULL,'pending'),
(10,0.00,'card',NULL,'pending');

INSERT INTO maintenance (car_id,maint_date,type,cost,description) VALUES
(1,'2024-04-01','ТО',5000,'Плановое ТО'),
(2,'2024-04-05','Замена масла',3000,'Масло 5W-30'),
(3,'2024-04-10','Шины',15000,'Летняя резина'),
(4,'2024-04-15','Тормоза',8000,'Колодки'),
(5,'2024-04-20','Кузов',25000,'Покраска'),
(6,'2024-04-25','ТО',6000,'Плановое'),
(7,'2024-05-01','Стекло',12000,'Замена лобового'),
(8,'2024-05-05','Двигатель',20000,'Диагностика'),
(9,'2024-05-10','Электрика',4000,'Лампы, предохранители'),
(10,'2024-05-15','ТО',5500,'Плановое');


-- #####################################################################
-- #                    2) ОБМЕН ВАЛЮТЫ                                 #
-- #####################################################################
DROP DATABASE IF EXISTS exchange_db;
CREATE DATABASE exchange_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE exchange_db;

CREATE TABLE currencies (
    currency_id INT AUTO_INCREMENT PRIMARY KEY,
    code        VARCHAR(3),
    name        VARCHAR(60),
    symbol      VARCHAR(5),
    UNIQUE KEY uq_currency_code (code)
) ENGINE=InnoDB;

CREATE TABLE branches (
    branch_id  INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100),
    city       VARCHAR(60),
    address    VARCHAR(200),
    phone      VARCHAR(30)
) ENGINE=InnoDB;

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    branch_id   INT NOT NULL,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    position    VARCHAR(50),
    phone       VARCHAR(30),
    hired_at    DATE,
    CONSTRAINT fk_emp_branch FOREIGN KEY (branch_id) REFERENCES branches(branch_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE clients (
    client_id   INT AUTO_INCREMENT PRIMARY KEY,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    phone       VARCHAR(30),
    email       VARCHAR(100),
    passport    VARCHAR(20),
    type        VARCHAR(20) DEFAULT 'individual',
    UNIQUE KEY uq_client_passport (passport)
) ENGINE=InnoDB;

CREATE TABLE rates (
    rate_id      INT AUTO_INCREMENT PRIMARY KEY,
    currency_id  INT NOT NULL,
    rate_date    DATE,
    buy_rate     DECIMAL(12,4),
    sell_rate    DECIMAL(12,4),
    cbr_rate     DECIMAL(12,4),
    CONSTRAINT fk_rate_currency FOREIGN KEY (currency_id) REFERENCES currencies(currency_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE operations (
    operation_id INT AUTO_INCREMENT PRIMARY KEY,
    client_id    INT NOT NULL,
    employee_id  INT NOT NULL,
    branch_id    INT NOT NULL,
    from_currency INT NOT NULL,
    to_currency   INT NOT NULL,
    amount_from   DECIMAL(14,2),
    amount_to     DECIMAL(14,2),
    rate_used     DECIMAL(12,4),
    commission    DECIMAL(10,2),
    op_date       DATETIME,
    CONSTRAINT fk_op_client   FOREIGN KEY (client_id)   REFERENCES clients(client_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_op_employee FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_op_branch   FOREIGN KEY (branch_id)   REFERENCES branches(branch_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_op_from     FOREIGN KEY (from_currency) REFERENCES currencies(currency_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_op_to       FOREIGN KEY (to_currency)   REFERENCES currencies(currency_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE commissions (
    commission_id INT AUTO_INCREMENT PRIMARY KEY,
    operation_id  INT NOT NULL,
    type          VARCHAR(30),
    percent       DECIMAL(5,2),
    fixed_amount  DECIMAL(10,2),
    CONSTRAINT fk_comm_op FOREIGN KEY (operation_id) REFERENCES operations(operation_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO currencies (code,name,symbol) VALUES
('RUB','Российский рубль','₽'),
('USD','Доллар США','$'),
('EUR','Евро','€'),
('GBP','Фунт стерлингов','£'),
('CNY','Китайский юань','¥'),
('JPY','Японская йена','¥'),
('CHF','Швейцарский франк','Fr'),
('TRY','Турецкая лира','₺'),
('KZT','Казахстанский тенге','₸'),
('BYN','Белорусский рубль','Br');

INSERT INTO branches (name,city,address,phone) VALUES
('Центральный','Москва','Тверская 1','+7(495)100-10-10'),
('Арбатский','Москва','Арбат 10','+7(495)200-20-20'),
('Невский','СПб','Невский 5','+7(812)300-30-30'),
('Казанский','Казань','Баумана 10','+7(843)400-40-40'),
('Уральский','Екб','Мира 3','+7(343)500-50-50'),
('Сибирский','Нск','Ленина 20','+7(383)600-60-60'),
('Южный','Сочи','Курортный 5','+7(862)700-70-70'),
('Ростовский','Ростов','Садовая 12','+7(863)800-80-80'),
('Башкирский','Уфа','Ленина 8','+7(347)900-90-90'),
('Волжский','Самара','Победы 4','+7(846)111-11-11');

INSERT INTO employees (branch_id,first_name,last_name,position,phone,hired_at) VALUES
(1,'Иван','Петров','Кассир','+79001111111','2020-09-01'),
(1,'Анна','Сидорова','Управляющий','+79002222222','2018-09-01'),
(2,'Олег','Кузнецов','Кассир','+79003333333','2021-09-01'),
(3,'Мария','Иванова','Кассир','+79004444444','2019-09-01'),
(4,'Дмитрий','Смирнов','Управляющий','+79005555555','2017-09-01'),
(5,'Елена','Попова','Кассир','+79006666666','2022-09-01'),
(6,'Сергей','Васильев','Кассир','+79007777777','2019-09-01'),
(7,'Ольга','Новикова','Управляющий','+79008888888','2016-09-01'),
(8,'Павел','Морозов','Кассир','+79009999999','2020-09-01'),
(9,'Ирина','Зайцева','Кассир','+79000000000','2021-09-01');

INSERT INTO clients (first_name,last_name,phone,email,passport,type) VALUES
('Алексей','Александров','+79111111111','a@mail.ru','4501 111111','individual'),
('Екатерина','Борисова','+79112222222','b@mail.ru','4502 222222','individual'),
('Сергей','Волков','+79113333333','v@mail.ru','4503 333333','individual'),
('Ольга','Григорьева','+79114444444','g@mail.ru','4504 444444','individual'),
('ООО Ромашка','-','+74951001010','romashka@mail.ru','7701234567','company'),
('Татьяна','Егорова','+79116666666','e@mail.ru','4506 666666','individual'),
('Игорь','Жуков','+79117777777','j@mail.ru','4507 777777','individual'),
('ООО Лютик','-','+74952002020','lutik@mail.ru','7702345678','company'),
('Роман','Ильин','+79119999999','i@mail.ru','4509 999999','individual'),
('Анастасия','Крылова','+79110000000','k@mail.ru','4510 000000','individual');

INSERT INTO rates (currency_id,rate_date,buy_rate,sell_rate,cbr_rate) VALUES
(2,'2024-05-01',90.5000,92.0000,91.2000),
(3,'2024-05-01',98.5000,100.5000,99.3000),
(4,'2024-05-01',114.0000,117.0000,115.5000),
(5,'2024-05-01',12.5000,13.2000,12.8000),
(6,'2024-05-01',0.5900,0.6300,0.6100),
(7,'2024-05-01',101.0000,104.0000,102.5000),
(8,'2024-05-01',2.7000,2.9000,2.8000),
(9,'2024-05-01',0.1950,0.2100,0.2020),
(10,'2024-05-01',27.5000,29.0000,28.2000),
(2,'2024-05-02',90.7000,92.3000,91.5000);

INSERT INTO operations (client_id,employee_id,branch_id,from_currency,to_currency,amount_from,amount_to,rate_used,commission,op_date) VALUES
(1,1,1,2,1,1000,90500,90.50,452.50,'2024-05-01 10:00'),
(2,3,2,3,1,500,49250,98.50,246.25,'2024-05-01 11:00'),
(3,4,3,1,2,92000,1000,92.00,460.00,'2024-05-01 12:00'),
(4,5,4,4,1,200,22800,114.00,114.00,'2024-05-01 13:00'),
(5,6,5,1,3,100500,1000,100.50,502.50,'2024-05-01 14:00'),
(6,7,6,5,1,10000,125000,12.50,625.00,'2024-05-01 15:00'),
(7,8,7,6,1,100000,59000,0.59,295.00,'2024-05-01 16:00'),
(8,9,8,1,7,104000,1000,104.00,520.00,'2024-05-01 17:00'),
(9,10,9,8,1,1000,2700,2.70,13.50,'2024-05-01 18:00'),
(10,1,1,9,1,100000,19500,0.195,97.50,'2024-05-01 19:00');

INSERT INTO commissions (operation_id,type,percent,fixed_amount) VALUES
(1,'percent',0.50,0.00),
(2,'percent',0.50,0.00),
(3,'percent',0.50,0.00),
(4,'percent',0.50,0.00),
(5,'percent',0.50,0.00),
(6,'percent',0.50,0.00),
(7,'percent',0.50,0.00),
(8,'percent',0.50,0.00),
(9,'percent',0.50,0.00),
(10,'fixed',0.00,100.00);


-- #####################################################################
-- #                    3) СТРОИТЕЛЬНАЯ КОМПАНИЯ                        #
-- #####################################################################
DROP DATABASE IF EXISTS construction_db;
CREATE DATABASE construction_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE construction_db;

CREATE TABLE clients (
    client_id INT AUTO_INCREMENT PRIMARY KEY,
    name      VARCHAR(100),
    type      VARCHAR(20) DEFAULT 'individual',
    phone     VARCHAR(30),
    email     VARCHAR(100),
    address   VARCHAR(200),
    inn       VARCHAR(20)
) ENGINE=InnoDB;

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    position    VARCHAR(50),
    specialization VARCHAR(80),
    phone       VARCHAR(30),
    hired_at    DATE,
    salary      DECIMAL(10,2)
) ENGINE=InnoDB;

CREATE TABLE brigades (
    brigade_id INT AUTO_INCREMENT PRIMARY KEY,
    foreman_id INT NOT NULL,
    name       VARCHAR(80),
    specialization VARCHAR(80),
    CONSTRAINT fk_brig_foreman FOREIGN KEY (foreman_id) REFERENCES employees(employee_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE brigade_members (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    brigade_id INT NOT NULL,
    employee_id INT NOT NULL,
    role        VARCHAR(50),
    CONSTRAINT fk_bm_brigade FOREIGN KEY (brigade_id) REFERENCES brigades(brigade_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_bm_employee FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE KEY uq_brigade_employee (brigade_id, employee_id)
) ENGINE=InnoDB;

CREATE TABLE projects (
    project_id  INT AUTO_INCREMENT PRIMARY KEY,
    client_id   INT NOT NULL,
    brigade_id  INT,
    name        VARCHAR(150),
    address     VARCHAR(200),
    start_date  DATE,
    end_date    DATE,
    budget      DECIMAL(14,2),
    status      VARCHAR(20) DEFAULT 'planned',
    CONSTRAINT fk_proj_client  FOREIGN KEY (client_id)  REFERENCES clients(client_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_proj_brigade FOREIGN KEY (brigade_id) REFERENCES brigades(brigade_id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE suppliers (
    supplier_id INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100),
    contact     VARCHAR(100),
    phone       VARCHAR(30),
    email       VARCHAR(100),
    UNIQUE KEY uq_supplier_name (name)
) ENGINE=InnoDB;

CREATE TABLE materials (
    material_id INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100),
    unit        VARCHAR(20),
    price       DECIMAL(10,2),
    supplier_id INT,
    CONSTRAINT fk_mat_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE supplies (
    supply_id   INT AUTO_INCREMENT PRIMARY KEY,
    project_id  INT NOT NULL,
    material_id INT NOT NULL,
    supplier_id INT NOT NULL,
    quantity    DECIMAL(10,2),
    total_cost  DECIMAL(12,2),
    supply_date DATE,
    CONSTRAINT fk_sup_project  FOREIGN KEY (project_id)  REFERENCES projects(project_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_sup_material FOREIGN KEY (material_id) REFERENCES materials(material_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_sup_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO clients (name,type,phone,email,address,inn) VALUES
('ООО СтройИнвест','company','+74951001010','si@mail.ru','Москва, Тверская 1','7701234567'),
('Иванов И.И.','individual','+79001111111','ivanov@mail.ru','Москва, Ленина 1',NULL),
('ООО Мегаполис','company','+74952002020','megapolis@mail.ru','СПб, Невский 5','7802345678'),
('Петров П.П.','individual','+79002222222','petrov@mail.ru','СПб, Мира 2',NULL),
('ООО Аквамарин','company','+74953003030','aqua@mail.ru','Казань, Баумана 10','7703456789'),
('Сидорова А.С.','individual','+79003333333','sidorova@mail.ru','Казань, Мира 3',NULL),
('ООО Гелиос','company','+74954004040','gelios@mail.ru','Екб, Мира 3','7704567890'),
('Кузнецов О.В.','individual','+79004444444','kuznetsov@mail.ru','Нск, Ленина 20',NULL),
('ООО Титан','company','+74955005050','titan@mail.ru','Сочи, Курортный 5','7705678901'),
('Смирнова М.А.','individual','+79005555555','smirnova@mail.ru','Ростов, Садовая 12',NULL);

INSERT INTO employees (first_name,last_name,position,specialization,phone,hired_at,salary) VALUES
('Иван','Петров','Прораб','Каменные работы','+79001111111','2015-09-01',80000),
('Анна','Сидорова','Инженер','Проектирование','+79002222222','2018-09-01',70000),
('Олег','Кузнецов','Бригадир','Электрика','+79003333333','2016-09-01',65000),
('Мария','Иванова','Сметчик','Сметы','+79004444444','2019-09-01',60000),
('Дмитрий','Смирнов','Каменщик','Кладка','+79005555555','2014-09-01',55000),
('Елена','Попова','Архитектор','Проектирование','+79006666666','2020-09-01',75000),
('Сергей','Васильев','Плотник','Дерево','+79007777777','2013-09-01',50000),
('Ольга','Новикова','Маляр','Отделка','+79008888888','2021-09-01',45000),
('Павел','Морозов','Сварщик','Металл','+79009999999','2017-09-01',55000),
('Ирина','Зайцева','Дизайнер','Дизайн','+79000000000','2019-09-01',65000);

INSERT INTO brigades (foreman_id,name,specialization) VALUES
(1,'Бригада №1','Общестроительные'),
(3,'Бригада №2','Электромонтаж'),
(5,'Бригада №3','Каменные работы'),
(7,'Бригада №4','Деревообработка'),
(8,'Бригада №5','Отделка'),
(9,'Бригада №6','Сварка'),
(10,'Бригада №7','Дизайн'),
(2,'Бригада №8','Проектирование'),
(4,'Бригада №9','Сметы'),
(6,'Бригада №10','Архитектура');

INSERT INTO brigade_members (brigade_id,employee_id,role) VALUES
(1,1,'Прораб'),(1,5,'Каменщик'),
(2,3,'Бригадир'),(2,9,'Сварщик'),
(3,5,'Каменщик'),(3,7,'Плотник'),
(4,7,'Бригадир'),(4,8,'Маляр'),
(5,8,'Бригадир'),(5,10,'Дизайнер'),
(6,9,'Бригадир'),(6,1,'Прораб'),
(7,10,'Дизайнер'),(7,6,'Архитектор'),
(8,2,'Инженер'),(8,6,'Архитектор'),
(9,4,'Сметчик'),(9,2,'Инженер'),
(10,6,'Архитектор'),(10,10,'Дизайнер');

INSERT INTO projects (client_id,brigade_id,name,address,start_date,end_date,budget,status) VALUES
(1,1,'ЖК Тверской','Москва, Тверская 10','2024-01-01','2025-12-31',50000000,'in_progress'),
(2,3,'Дом на Ленина','Москва, Ленина 5','2024-02-01','2024-12-31',5000000,'in_progress'),
(3,2,'Офис Невский','СПб, Невский 20','2024-03-01','2025-06-30',15000000,'in_progress'),
(4,4,'Дача Петрова','СПб, Пригород 1','2024-04-01','2024-10-31',3000000,'planned'),
(5,5,'ТЦ Аквамарин','Казань, Баумана 30','2024-05-01','2026-05-01',80000000,'planned'),
(6,6,'Квартира Сидоровой','Казань, Мира 5','2024-06-01','2024-11-30',2000000,'planned'),
(7,7,'Склад Гелиос','Екб, Мира 50','2024-07-01','2025-03-31',12000000,'planned'),
(8,8,'Гараж Кузнецова','Нск, Ленина 100','2024-08-01','2024-12-31',1500000,'planned'),
(9,9,'Отель Титан','Сочи, Курортный 50','2024-09-01','2026-09-01',120000000,'planned'),
(10,10,'Квартира Смирновой','Ростов, Садовая 50','2024-10-01','2025-04-30',2500000,'planned');

INSERT INTO suppliers (name,contact,phone,email) VALUES
('ООО СтройМат','Иванов','+79001111111','stroymat@mail.ru'),
('ООО Цемент+','Петров','+79002222222','cement@mail.ru'),
('ООО КирпичТорг','Сидоров','+79003333333','kirpich@mail.ru'),
('ООО ЛесПром','Кузнецов','+79004444444','lesprom@mail.ru'),
('ООО МеталлТорг','Смирнов','+79005555555','metall@mail.ru'),
('ООО ЭлектроСнаб','Попова','+79006666666','electro@mail.ru'),
('ООО Сантехника','Васильев','+79007777777','santeh@mail.ru'),
('ООО Отделка+','Новикова','+79008888888','otdelka@mail.ru'),
('ООО Краска','Морозов','+79009999999','kraska@mail.ru'),
('ООО Инструмент','Зайцева','+79000000000','instrument@mail.ru');

INSERT INTO materials (name,unit,price,supplier_id) VALUES
('Цемент М500','кг',15.00,2),
('Кирпич красный','шт',25.00,3),
('Песок','м3',500.00,1),
('Доска 50х150','м3',12000.00,4),
('Арматура 12мм','м',65.00,5),
('Кабель ВВГ','м',80.00,6),
('Труба ПП','м',120.00,7),
('Штукатурка','кг',45.00,8),
('Краска белая','л',350.00,9),
('Дрель','шт',5000.00,10);

INSERT INTO supplies (project_id,material_id,supplier_id,quantity,total_cost,supply_date) VALUES
(1,1,2,5000,75000,'2024-01-05'),
(1,2,3,10000,250000,'2024-01-10'),
(2,3,1,50,25000,'2024-02-05'),
(3,4,4,20,240000,'2024-03-10'),
(4,5,5,500,32500,'2024-04-15'),
(5,6,6,2000,160000,'2024-05-20'),
(6,7,7,200,24000,'2024-06-25'),
(7,8,8,500,22500,'2024-07-30'),
(8,9,9,100,35000,'2024-08-15'),
(9,10,10,10,50000,'2024-09-20');


-- #####################################################################
-- #                    4) ХИМЧИСТКА                                    #
-- #####################################################################
DROP DATABASE IF EXISTS drycleaning_db;
CREATE DATABASE drycleaning_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE drycleaning_db;

CREATE TABLE clients (
    client_id  INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name  VARCHAR(50),
    phone      VARCHAR(30),
    email      VARCHAR(100),
    address    VARCHAR(200),
    UNIQUE KEY uq_client_phone (phone)
) ENGINE=InnoDB;

CREATE TABLE services (
    service_id INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100),
    category   VARCHAR(50),
    price      DECIMAL(10,2),
    duration_hours INT,
    description VARCHAR(200)
) ENGINE=InnoDB;

CREATE TABLE branches (
    branch_id  INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100),
    address    VARCHAR(200),
    city       VARCHAR(60),
    phone      VARCHAR(30)
) ENGINE=InnoDB;

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    branch_id   INT NOT NULL,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    position    VARCHAR(50),
    phone       VARCHAR(30),
    hired_at    DATE,
    CONSTRAINT fk_emp_branch FOREIGN KEY (branch_id) REFERENCES branches(branch_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE orders (
    order_id    INT AUTO_INCREMENT PRIMARY KEY,
    client_id   INT NOT NULL,
    employee_id INT NOT NULL,
    branch_id   INT NOT NULL,
    order_date  DATETIME,
    ready_date  DATETIME,
    status      VARCHAR(20) DEFAULT 'new',
    total_cost  DECIMAL(10,2),
    CONSTRAINT fk_ord_client   FOREIGN KEY (client_id)   REFERENCES clients(client_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_ord_employee FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_ord_branch   FOREIGN KEY (branch_id)   REFERENCES branches(branch_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE order_items (
    item_id    INT AUTO_INCREMENT PRIMARY KEY,
    order_id   INT NOT NULL,
    service_id INT NOT NULL,
    quantity   INT DEFAULT 1,
    price      DECIMAL(10,2),
    notes      VARCHAR(200),
    CONSTRAINT fk_oi_order   FOREIGN KEY (order_id)   REFERENCES orders(order_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_oi_service FOREIGN KEY (service_id) REFERENCES services(service_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id   INT NOT NULL,
    amount     DECIMAL(10,2),
    method     VARCHAR(30),
    paid_at    DATETIME,
    status     VARCHAR(20) DEFAULT 'paid',
    CONSTRAINT fk_pay_order FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO clients (first_name,last_name,phone,email,address) VALUES
('Иван','Петров','+79001111111','petrov@mail.ru','ул. Ленина 1'),
('Анна','Сидорова','+79002222222','sidorova@mail.ru','ул. Мира 2'),
('Олег','Кузнецов','+79003333333','kuznetsov@mail.ru','ул. Кирова 3'),
('Мария','Иванова','+79004444444','ivanova@mail.ru','ул. Пушкина 4'),
('Дмитрий','Смирнов','+79005555555','smirnov@mail.ru','пр. Свободный 5'),
('Елена','Попова','+79006666666','popova@mail.ru','ул. Гагарина 6'),
('Сергей','Васильев','+79007777777','vasiliev@mail.ru','ул. Садовая 7'),
('Ольга','Новикова','+79008888888','novikova@mail.ru','ул. Советская 8'),
('Павел','Морозов','+79009999999','morozov@mail.ru','ул. Победы 9'),
('Ирина','Зайцева','+79000000000','zaytseva@mail.ru','ул. Партизан 10');

INSERT INTO services (name,category,price,duration_hours,description) VALUES
('Химчистка пальто','Верхняя одежда',1500.00,48,'Сухая чистка'),
('Стирка рубашки','Рубашки',300.00,24,'Аква-стирка'),
('Химчистка платья','Платья',1200.00,48,'Деликатная'),
('Химчистка костюма','Костюмы',1800.00,48,'Пара: пиджак+брюки'),
('Чистка ковра','Ковры',2500.00,72,'За м²'),
('Чистка пуховика','Верхняя одежда',2000.00,72,'Сухая'),
('Глажка','Услуги',200.00,2,'За единицу'),
('Ремонт одежды','Услуги',500.00,24,'Мелкий ремонт'),
('Химчистка штор','Шторы',1800.00,72,'За комплект'),
('Удаление пятен','Услуги',700.00,12,'Локальная');

INSERT INTO branches (name,address,city,phone) VALUES
('Центральная','Тверская 1','Москва','+7(495)100-10-10'),
('Арбатская','Арбат 10','Москва','+7(495)200-20-20'),
('Невская','Невский 5','СПб','+7(812)300-30-30'),
('Казанская','Баумана 10','Казань','+7(843)400-40-40'),
('Уральская','Мира 3','Екб','+7(343)500-50-50'),
('Сибирская','Ленина 20','Нск','+7(383)600-60-60'),
('Южная','Курортный 5','Сочи','+7(862)700-70-70'),
('Ростовская','Садовая 12','Ростов','+7(863)800-80-80'),
('Башкирская','Ленина 8','Уфа','+7(347)900-90-90'),
('Волжская','Победы 4','Самара','+7(846)111-11-11');

INSERT INTO employees (branch_id,first_name,last_name,position,phone,hired_at) VALUES
(1,'Иван','Петров','Приёмщик','+79001111111','2020-09-01'),
(1,'Анна','Сидорова','Технолог','+79002222222','2018-09-01'),
(2,'Олег','Кузнецов','Приёмщик','+79003333333','2021-09-01'),
(3,'Мария','Иванова','Приёмщик','+79004444444','2019-09-01'),
(4,'Дмитрий','Смирнов','Управляющий','+79005555555','2017-09-01'),
(5,'Елена','Попова','Приёмщик','+79006666666','2022-09-01'),
(6,'Сергей','Васильев','Приёмщик','+79007777777','2019-09-01'),
(7,'Ольга','Новикова','Технолог','+79008888888','2016-09-01'),
(8,'Павел','Морозов','Приёмщик','+79009999999','2020-09-01'),
(9,'Ирина','Зайцева','Приёмщик','+79000000000','2021-09-01');

INSERT INTO orders (client_id,employee_id,branch_id,order_date,ready_date,status,total_cost) VALUES
(1,1,1,'2024-06-01 10:00','2024-06-03 10:00','done',1500.00),
(2,2,1,'2024-06-02 11:00','2024-06-03 11:00','done',600.00),
(3,3,2,'2024-06-03 12:00','2024-06-05 12:00','done',1200.00),
(4,4,3,'2024-06-04 13:00','2024-06-06 13:00','ready',1800.00),
(5,5,4,'2024-06-05 14:00','2024-06-07 14:00','ready',2500.00),
(6,6,5,'2024-06-06 15:00','2024-06-08 15:00','in_progress',2000.00),
(7,7,6,'2024-06-07 16:00','2024-06-08 16:00','in_progress',500.00),
(8,8,7,'2024-06-08 17:00','2024-06-09 17:00','in_progress',700.00),
(9,9,8,'2024-06-09 18:00','2024-06-10 18:00','new',1800.00),
(10,10,9,'2024-06-10 19:00','2024-06-11 19:00','new',500.00);

INSERT INTO order_items (order_id,service_id,quantity,price,notes) VALUES
(1,1,1,1500.00,'Пальто мужское'),
(2,2,2,300.00,'Две рубашки'),
(3,3,1,1200.00,'Платье вечернее'),
(4,4,1,1800.00,'Костюм'),
(5,5,5,2500.00,'Ковёр 5 м²'),
(6,6,1,2000.00,'Пуховик'),
(7,8,1,500.00,'Замена молнии'),
(8,10,1,700.00,'Пятно на блузке'),
(9,9,1,1800.00,'Комплект штор'),
(10,7,1,200.00,'Глажка костюма');

INSERT INTO payments (order_id,amount,method,paid_at,status) VALUES
(1,1500.00,'card','2024-06-01 10:05','paid'),
(2,600.00,'cash','2024-06-02 11:05','paid'),
(3,1200.00,'card','2024-06-03 12:05','paid'),
(4,1800.00,'card','2024-06-04 13:05','paid'),
(5,2500.00,'sbp','2024-06-05 14:05','paid'),
(6,2000.00,'card','2024-06-06 15:05','paid'),
(7,500.00,'cash','2024-06-07 16:05','paid'),
(8,700.00,'card','2024-06-08 17:05','paid'),
(9,0.00,'card',NULL,'pending'),
(10,0.00,'card',NULL,'pending');


-- #####################################################################
-- #                    5) МУЗЕЙ                                        #
-- #####################################################################
DROP DATABASE IF EXISTS museum_db;
CREATE DATABASE museum_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE museum_db;

CREATE TABLE halls (
    hall_id    INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(80),
    floor      INT,
    area_sqm   DECIMAL(8,2),
    theme      VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    position    VARCHAR(50),
    specialization VARCHAR(80),
    phone       VARCHAR(30),
    hired_at    DATE
) ENGINE=InnoDB;

CREATE TABLE exhibits (
    exhibit_id INT AUTO_INCREMENT PRIMARY KEY,
    hall_id    INT NOT NULL,
    name       VARCHAR(150),
    era        VARCHAR(80),
    origin     VARCHAR(100),
    year_created INT,
    value_estimate DECIMAL(14,2),
    description VARCHAR(300),
    CONSTRAINT fk_exhibit_hall FOREIGN KEY (hall_id) REFERENCES halls(hall_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE exhibitions (
    exhibition_id INT AUTO_INCREMENT PRIMARY KEY,
    hall_id       INT NOT NULL,
    curator_id    INT NOT NULL,
    name          VARCHAR(150),
    start_date    DATE,
    end_date      DATE,
    ticket_price  DECIMAL(10,2),
    CONSTRAINT fk_exh_hall    FOREIGN KEY (hall_id)    REFERENCES halls(hall_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_exh_curator FOREIGN KEY (curator_id) REFERENCES employees(employee_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE visitors (
    visitor_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name  VARCHAR(50),
    phone      VARCHAR(30),
    email      VARCHAR(100),
    birth_date DATE,
    category   VARCHAR(20) DEFAULT 'adult'
) ENGINE=InnoDB;

CREATE TABLE excursions (
    excursion_id INT AUTO_INCREMENT PRIMARY KEY,
    exhibition_id INT NOT NULL,
    guide_id      INT NOT NULL,
    excursion_date DATETIME,
    max_people    INT,
    price         DECIMAL(10,2),
    CONSTRAINT fk_exc_exhibition FOREIGN KEY (exhibition_id) REFERENCES exhibitions(exhibition_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_exc_guide      FOREIGN KEY (guide_id)      REFERENCES employees(employee_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE tickets (
    ticket_id   INT AUTO_INCREMENT PRIMARY KEY,
    visitor_id  INT NOT NULL,
    excursion_id INT,
    exhibition_id INT,
    issue_date  DATE,
    price       DECIMAL(10,2),
    ticket_type VARCHAR(20),
    CONSTRAINT fk_ticket_visitor   FOREIGN KEY (visitor_id)   REFERENCES visitors(visitor_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_ticket_excursion FOREIGN KEY (excursion_id) REFERENCES excursions(excursion_id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_ticket_exhibition FOREIGN KEY (exhibition_id) REFERENCES exhibitions(exhibition_id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO halls (name,floor,area_sqm,theme) VALUES
('Зал Древностей',1,200.00,'Античность'),
('Зал Живописи',1,300.00,'Живопись'),
('Зал Скульптур',2,250.00,'Скульптура'),
('Зал Средневековья',2,180.00,'Средневековье'),
('Зал Возрождения',2,220.00,'Ренессанс'),
('Зал Импрессионизма',3,180.00,'Импрессионизм'),
('Зал Современного',3,250.00,'Современное'),
('Зал Нумизматики',1,120.00,'Монеты'),
('Зал Оружия',1,150.00,'Оружие'),
('Зал Египта',2,220.00,'Древний Египет');

INSERT INTO employees (first_name,last_name,position,specialization,phone,hired_at) VALUES
('Иван','Петров','Куратор','Живопись','+79001111111','2015-09-01'),
('Анна','Сидорова','Куратор','Скульптура','+79002222222','2018-09-01'),
('Олег','Кузнецов','Экскурсовод','Античность','+79003333333','2016-09-01'),
('Мария','Иванова','Экскурсовод','Средневековье','+79004444444','2019-09-01'),
('Дмитрий','Смирнов','Реставратор','Живопись','+79005555555','2014-09-01'),
('Елена','Попова','Экскурсовод','Ренессанс','+79006666666','2020-09-01'),
('Сергей','Васильев','Куратор','Оружие','+79007777777','2013-09-01'),
('Ольга','Новикова','Экскурсовод','Импрессионизм','+79008888888','2021-09-01'),
('Павел','Морозов','Хранитель','Нумизматика','+79009999999','2017-09-01'),
('Ирина','Зайцева','Реставратор','Скульптура','+79000000000','2019-09-01');

INSERT INTO exhibits (hall_id,name,era,origin,year_created,value_estimate,description) VALUES
(1,'Амфора греческая','Античность','Греция',-500,500000,'Керамика'),
(1,'Статуя Аполлона','Античность','Рим',100,2000000,'Мрамор'),
(2,'Портрет неизвестного','18 век','Россия',1750,800000,'Холст, масло'),
(2,'Пейзаж','19 век','Россия',1850,1200000,'Холст, масло'),
(3,'Бюст Наполеона','18 век','Франция',1800,600000,'Мрамор'),
(4,'Меч рыцарский','Средневековье','Европа',1350,1500000,'Сталь'),
(5,'Мадонна с младенцем','Возрождение','Италия',1500,5000000,'Холст, масло'),
(6,'Пейзаж Моне','Импрессионизм','Франция',1890,8000000,'Холст, масло'),
(7,'Инсталляция','Современное','Россия',2020,100000,'Смешанная техника'),
(10,'Саркофаг','Древний Египет','Египет',-2000,3000000,'Камень');

INSERT INTO exhibitions (hall_id,curator_id,name,start_date,end_date,ticket_price) VALUES
(1,1,'Античное наследие','2024-01-01','2024-06-30',500.00),
(2,1,'Русская живопись','2024-02-01','2024-07-31',600.00),
(3,2,'Скульптуры мира','2024-03-01','2024-08-31',550.00),
(4,7,'Рыцарский зал','2024-04-01','2024-09-30',700.00),
(5,1,'Эпоха Возрождения','2024-05-01','2024-10-31',800.00),
(6,8,'Импрессионизм','2024-06-01','2024-11-30',900.00),
(7,8,'Современное искусство','2024-07-01','2024-12-31',400.00),
(10,3,'Древний Египет','2024-08-01','2025-01-31',650.00),
(1,7,'Оружие веков','2024-09-01','2025-02-28',550.00),
(3,2,'Античные скульптуры','2024-10-01','2025-03-31',600.00);

INSERT INTO visitors (first_name,last_name,phone,email,birth_date,category) VALUES
('Алексей','Александров','+79111111111','a@mail.ru','1990-05-12','adult'),
('Екатерина','Борисова','+79112222222','b@mail.ru','1992-08-23','adult'),
('Сергей','Волков','+79113333333','v@mail.ru','1988-02-14','adult'),
('Ольга','Григорьева','+79114444444','g@mail.ru','1995-11-03','student'),
('Николай','Дмитриев','+79115555555','d@mail.ru','1985-07-19','adult'),
('Татьяна','Егорова','+79116666666','e@mail.ru','1998-01-25','student'),
('Игорь','Жуков','+79117777777','j@mail.ru','2000-03-11','student'),
('Ксения','Зимина','+79118888888','z@mail.ru','2010-09-30','child'),
('Роман','Ильин','+79119999999','i@mail.ru','1980-12-05','adult'),
('Анастасия','Крылова','+79110000000','k@mail.ru','1997-06-18','pensioner');

INSERT INTO excursions (exhibition_id,guide_id,excursion_date,max_people,price) VALUES
(1,3,'2024-01-15 11:00',20,300.00),
(2,1,'2024-02-15 12:00',20,400.00),
(3,4,'2024-03-15 13:00',15,350.00),
(4,5,'2024-04-15 14:00',25,500.00),
(5,6,'2024-05-15 15:00',20,450.00),
(6,8,'2024-06-15 16:00',30,600.00),
(7,8,'2024-07-15 17:00',20,300.00),
(8,3,'2024-08-15 18:00',15,400.00),
(9,7,'2024-09-15 19:00',20,350.00),
(10,2,'2024-10-15 20:00',20,400.00);

INSERT INTO tickets (visitor_id,excursion_id,exhibition_id,issue_date,price,ticket_type) VALUES
(1,1,1,'2024-01-15',300.00,'excursion'),
(2,2,2,'2024-02-15',400.00,'excursion'),
(3,3,3,'2024-03-15',350.00,'excursion'),
(4,4,4,'2024-04-15',500.00,'excursion'),
(5,5,5,'2024-05-15',450.00,'excursion'),
(6,6,6,'2024-06-15',600.00,'excursion'),
(7,7,7,'2024-07-15',300.00,'excursion'),
(8,8,8,'2024-08-15',400.00,'excursion'),
(9,9,9,'2024-09-15',350.00,'excursion'),
(10,10,10,'2024-10-15',400.00,'excursion');


-- =====================================================================
--  ГОТОВО. Для ER-диаграмм: Database → Reverse Engineer (Ctrl+R)
--  → выбрать нужную БД → галочка "Place imported objects on a diagram"
-- =====================================================================