---

create database auto_service;
use auto_service;

create table contract(
id int primary key auto_increment,
contract_num varchar(20),
date_start date,
period int,
termination varchar(16) default 'Не расторгнут',
term_date date);

create table employes(
id int primary key auto_increment,
f_name varchar(60),
s_name varchar(60),
l_name varchar(60),
passport varchar(10),
adress varchar(100),
phone varchar(16),
contract_id int unique,
foreign key(contract_id) references contract(id));



create table remont(
id int primary key auto_increment,
name varchar(80),
complexity int,
cost_price decimal(10,2),
client_price decimal(10,2),
fond_price decimal(10,2));

create table clients(
id int primary key auto_increment,
f_name varchar(60),
s_name varchar(60),
l_name varchar(60),
adress varchar(100),
phone varchar(16),
car_num varchar(12));

create table orders(
id int primary key auto_increment,
date_zayavki date,
date_end date,
cnt int,
client_id int,
remont_id int,
foreign key (remont_id) references remont(id),
foreign key (client_id) references clients(id));

create table brigades(
id_emp int,
id_rem int,
hours int,
primary key(id_emp, id_rem),
foreign key (id_emp) references employes(id),
foreign key (id_rem) references remont(id));

-- 1. Вставляем трудовые договоры (4 договора с механиками)
INSERT INTO contract (contract_num, date_start, period, termination, term_date) VALUES
('Д-2024-001', '2024-01-15', 3, 'Не расторгнут', NULL),
('Д-2023-045', '2023-06-01', 2, 'Не расторгнут', NULL),
('Д-2024-012', '2024-03-20', 1, 'Не расторгнут', NULL),
('Д-2022-089', '2022-09-10', 5, 'Расторгнут', '2024-12-01');

-- 2. Вставляем автомехаников (5 человек)
INSERT INTO employes (f_name, s_name, l_name, passport, adress, phone, contract_id) VALUES
('Иван', 'Петр', 'Сидоров', '1234567890', 'г. Москва, ул. Ленина 15, кв. 25', '8-900-123-45-67', 1),
('Анна', 'Михайловна', 'Новикова', '2345678901', 'г. Москва, пр. Мира 42, кв. 108', '8-900-234-56-78', 2),
('Дмитрий', 'Алексей', 'Кузнецов', '3456789012', 'г. Москва, ул. Арбат 7, кв. 33', '8-900-345-67-89', 3),
('Елена', 'Сергей', 'Васильева', '4567890123', 'г. Москва, ул. Тверская 22, кв. 5', '8-900-456-78-90', NULL),
('Олег', 'Николай', 'Морозов', '5678901234', 'г. Москва, Кутузовский 31, кв. 77', '8-900-567-89-01', 4);

-- 3. Вставляем виды ремонтных работ (6 работ)
INSERT INTO remont (name, complexity, cost_price, client_price, fond_price) VALUES
('Замена масла в двигателе', 2, 500.00, 1500.00, 450.00),
('Диагностика подвески', 4, 1200.00, 3500.00, 1050.00),
('Замена тормозных колодок', 3, 800.00, 2200.00, 660.00),
('Ремонт двигателя', 10, 15000.00, 45000.00, 13500.00),
('Замена шин (сезонная)', 1, 300.00, 1000.00, 300.00),
('Химчистка салона', 3, 2000.00, 6000.00, 1800.00);

-- 4. Вставляем клиентов (4 клиента: 3 физлица + 1 юрлицо)
INSERT INTO clients (f_name, s_name, l_name, adress, phone, car_num) VALUES
('Алексей', 'Владимир', 'Петров', 'г. Москва, ул. Пушкина 10, кв. 15', '8-901-111-22-33', 'А123БВ 777'),
('Мария', 'Игорь', 'Соколова', 'г. Москва, ул. Гагарина 25, кв. 88', '8-902-222-33-44', 'Б456CD 777'),
('ООО', 'ТрансЛогист', '', 'г. Москва, пр. Industrial 50', '8-495-333-44-55', 'В789ЕF 77'),
('Николай', 'Павел', 'Орлов', 'г. Москва, ул. Советская 33, кв. 42', '8-903-333-44-55', 'Г012НI 777');

-- 5. Вставляем заказ-наряды (8 заказов)
INSERT INTO orders (date_zayavki, date_end, cnt, client_id, remont_id) VALUES
('2024-06-01', '2024-06-01', 1, 1, 1),  -- Петров - замена масла
('2024-06-02', '2024-06-03', 1, 2, 2),  -- Соколова - диагностика подвески
('2024-06-03', '2024-06-03', 2, 1, 3),  -- Петров - замена колодок (2 машины)
('2024-06-05', '2024-06-10', 1, 4, 4),  -- Орлов - ремонт двигателя
('2024-06-06', '2024-06-06', 4, 3, 5),  -- ООО - замена шин (4 машины)
('2024-06-07', '2024-06-07', 1, 2, 6),  -- Соколова - химчистка
('2024-06-08', '2024-06-09', 1, 1, 2),  -- Петров - диагностика подвески
('2024-06-10', '2024-06-10', 1, 4, 1);  -- Орлов - замена масла
-- 6. Вставляем бригады (связь механиков с выполненными работами)
INSERT INTO brigades (id_emp, id_rem, hours) VALUES
(1, 1, 2),   -- Сидоров - замена масла (2 часа)
(2, 2, 4),   -- Новикова - диагностика подвески (4 часа)
(1, 3, 2),   -- Сидоров - замена колодок (2 часа)
(3, 3, 2),   -- Кузнецов - замена колодок (2 часа) - бригада
(3, 4, 8),   -- Кузнецов - ремонт двигателя (8 часов)
(1, 4, 3),   -- Сидоров - ремонт двигателя (3 часа) - бригада
(2, 4, 2),   -- Новикова - ремонт двигателя (2 часа) - бригада
(4, 5, 1),   -- Васильева - замена шин (1 час)
(5, 5, 1),   -- Морозов - замена шин (1 час)
(2, 6, 3),   -- Новикова - химчистка (3 часа)
(1, 2, 3),   -- Сидоров - диагностика подвески (3 часа)
(4, 1, 1);   -- Васильева - замена масла (1 час)