---

CREATE TABLE Client (
    client_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    address VARCHAR(200)
);

CREATE TABLE Car (
    car_id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    brand VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    license_plate VARCHAR(20) NOT NULL,
    vin VARCHAR(17),
    year INT,
    FOREIGN KEY (client_id) REFERENCES Client(client_id)
);

CREATE TABLE Mechanic (
    mechanic_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    specialization VARCHAR(100),
    phone VARCHAR(20) NOT NULL,
    rating DECIMAL(3,2) DEFAULT 0.00
);

CREATE TABLE Service (
    service_id INT PRIMARY KEY AUTO_INCREMENT,
    service_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    base_price DECIMAL(10,2) NOT NULL,
    duration_hours DECIMAL(4,2)
);

CREATE TABLE Request (
    request_id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    car_id INT NOT NULL,
    mechanic_id INT,
    request_date DATETIME NOT NULL,
    status VARCHAR(20) NOT NULL,
    description TEXT,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (client_id) REFERENCES Client(client_id),
    FOREIGN KEY (car_id) REFERENCES Car(car_id),
    FOREIGN KEY (mechanic_id) REFERENCES Mechanic(mechanic_id)
);

CREATE TABLE RequestService (
    request_service_id INT PRIMARY KEY AUTO_INCREMENT,
    request_id INT NOT NULL,
    service_id INT NOT NULL,
    quantity INT NOT NULL,
    price_at_moment DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (request_id) REFERENCES Request(request_id),
    FOREIGN KEY (service_id) REFERENCES Service(service_id)
);

CREATE TABLE SparePart (
    part_id INT PRIMARY KEY AUTO_INCREMENT,
    part_name VARCHAR(100) NOT NULL,
    article VARCHAR(50),
    sale_price DECIMAL(10,2) NOT NULL
);


CREATE TABLE UsedPart (
    used_part_id INT PRIMARY KEY AUTO_INCREMENT,
    request_id INT NOT NULL,
    part_id INT NOT NULL,
    quantity INT NOT NULL,
    cost DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (request_id) REFERENCES Request(request_id),
    FOREIGN KEY (part_id) REFERENCES SparePart(part_id)
);


CREATE VIEW Vibor AS
SELECT 
    r.request_id,
    c.full_name AS client_name,
    car.brand,
    car.model,
    r.request_date,
    r.status,
    r.description
FROM Request r
JOIN Client c ON r.client_id = c.client_id
JOIN Car car ON r.car_id = car.car_id
WHERE r.status = 'В работе';

CREATE VIEW Rezult AS
SELECT 
    COUNT(*) AS total_requests,
    AVG(total_amount) AS avg_amount,
    MAX(total_amount) AS max_amount,
    MIN(total_amount) AS min_amount,
    SUM(total_amount) AS total_revenue
FROM Request
WHERE status = 'Выполнена';


CREATE VIEW Itog AS
SELECT 
    r.request_id,
    c.full_name AS client_name,
    car.brand,
    car.model,
    s.service_name,
    rs.quantity,
    rs.price_at_moment,
    (rs.quantity * rs.price_at_moment) AS service_total
FROM Request r
INNER JOIN Client c ON r.client_id = c.client_id
INNER JOIN Car car ON r.car_id = car.car_id
INNER JOIN RequestService rs ON r.request_id = rs.request_id
INNER JOIN Service s ON rs.service_id = s.service_id;