---

-- Таблица клиентов
CREATE TABLE client (
    id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20)
);

-- Таблица автомобилей
CREATE TABLE car (
    id INT PRIMARY KEY,
    brand VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    license_plate VARCHAR(20),
    vin VARCHAR(50),
    client_id INT NOT NULL,
    FOREIGN KEY (client_id) REFERENCES client(id)
);

-- Таблица заказ-нарядов
CREATE TABLE repair_order (
    id INT PRIMARY KEY,
    date DATE NOT NULL,
    problem_description TEXT,
    status VARCHAR(50),
    car_id INT NOT NULL,
    FOREIGN KEY (car_id) REFERENCES car(id)
);

-- Таблица услуг
CREATE TABLE service (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    cost DECIMAL(10,2)
);

-- Таблица запчастей
CREATE TABLE part (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2)
);

-- Связующая таблица "Заказ-Услуга" (many-to-many)
CREATE TABLE order_service (
    order_id INT NOT NULL,
    service_id INT NOT NULL,
    PRIMARY KEY (order_id, service_id),
    FOREIGN KEY (order_id) REFERENCES repair_order(id),
    FOREIGN KEY (service_id) REFERENCES service(id)
);

-- Связующая таблица "Заказ-Запчасть" (many-to-many)
CREATE TABLE order_part (
    order_id INT NOT NULL,
    part_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, part_id),
    FOREIGN KEY (order_id) REFERENCES repair_order(id),
    FOREIGN KEY (part_id) REFERENCES part(id)
);