---

CREATE DATABASE IF NOT EXISTS autoservice
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE autoservice;

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS MechanicWork;
DROP TABLE IF EXISTS Contracts;
DROP TABLE IF EXISTS Clients;
DROP TABLE IF EXISTS Works;
DROP TABLE IF EXISTS Mechanics;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE Mechanics (
    passport VARCHAR(20) PRIMARY KEY,
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),
    address VARCHAR(150),
    phone VARCHAR(20)
) ENGINE=InnoDB;

CREATE TABLE Contracts (
    contract_no INT PRIMARY KEY,
    passport VARCHAR(20) NOT NULL UNIQUE,
    sign_date DATE NOT NULL,
    term_years INT NOT NULL,
    is_terminated TINYINT NOT NULL DEFAULT 0,
    term_date DATE,
    FOREIGN KEY (passport) REFERENCES Mechanics(passport)
) ENGINE=InnoDB;

CREATE TABLE Works (
    work_code INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    norm_hours DECIMAL(6,2),
    cost DECIMAL(10,2),
    price DECIMAL(10,2),
    wage_fund DECIMAL(10,2)
) ENGINE=InnoDB;

CREATE TABLE MechanicWork (
    passport VARCHAR(20) NOT NULL,
    work_code INT NOT NULL,
    PRIMARY KEY (passport, work_code),
    FOREIGN KEY (passport) REFERENCES Mechanics(passport),
    FOREIGN KEY (work_code) REFERENCES Works(work_code)
) ENGINE=InnoDB;

CREATE TABLE Clients (
    client_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    address VARCHAR(150),
    phone VARCHAR(20),
    car VARCHAR(50),
    login VARCHAR(50) UNIQUE,
    password VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE Orders (
    order_no INT PRIMARY KEY,
    client_id INT NOT NULL,
    work_code INT NOT NULL,
    order_date DATE NOT NULL,
    done_date DATE,
    qty INT NOT NULL DEFAULT 1,
    FOREIGN KEY (client_id) REFERENCES Clients(client_id),
    FOREIGN KEY (work_code) REFERENCES Works(work_code)
) ENGINE=InnoDB;

INSERT INTO Mechanics VALUES
  ('4500111222','Иванов','Иван','Иванович','ул. Ленина, 1','79001112233'),
  ('4500333444','Петров','Пётр','Петрович','ул. Мира, 5','79004445566');

INSERT INTO Contracts (contract_no,passport,sign_date,term_years,is_terminated,term_date) VALUES
  (1,'4500111222','2024-01-10',2,0,NULL),
  (2,'4500333444','2024-03-01',1,0,NULL);

INSERT INTO Works VALUES
  (101,'Замена масла',1.0,300,1000,400),
  (102,'Ремонт тормозов',3.0,1500,4000,1600),
  (103,'Диагностика двигателя',2.0,800,2500,1000);

INSERT INTO MechanicWork VALUES
  ('4500111222',101),('4500111222',102),('4500333444',102),('4500333444',103);

INSERT INTO Clients (client_id,name,address,phone,car,login,password) VALUES
  (1,'Сидоров Алексей','ул. Садовая, 7','79007778899','Lada Vesta А123ВС','sidorov','1234'),
  (2,'ООО Логистика','пр. Победы, 20','78005550000','ГАЗель Х777ОР','logistika','5678');

INSERT INTO Orders VALUES
  (1,1,101,'2024-05-01','2024-05-01',1),
  (2,1,102,'2024-05-10',NULL,1),
  (3,2,103,'2024-05-12','2024-05-13',2),
  (4,2,101,'2024-05-15','2024-05-15',3);