---

CREATE DATABASE IF NOT EXISTS production_accounting;
USE production_accounting;

-- 1. Сборочные участки
CREATE TABLE assembly_sections (
    section_id INT PRIMARY KEY AUTO_INCREMENT,
    section_name VARCHAR(100) NOT NULL
);

-- 2. Сотрудники
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(150) NOT NULL,
    position VARCHAR(100) NOT NULL
);

-- 3. Продукция
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    serial_number VARCHAR(100) NOT NULL UNIQUE,
    product_type VARCHAR(100) NOT NULL,
    production_date DATE NOT NULL
);

-- 4. Детали
CREATE TABLE parts (
    part_id INT PRIMARY KEY AUTO_INCREMENT,
    batch_number VARCHAR(100) NOT NULL,
    part_name VARCHAR(150) NOT NULL
);

-- 5. Операции монтажа
CREATE TABLE assembly_operations (
    operation_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    section_id INT NOT NULL,
    employee_id INT NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME,
    status VARCHAR(50) NOT NULL,
    result VARCHAR(255),

    FOREIGN KEY (product_id)
        REFERENCES products(product_id),

    FOREIGN KEY (section_id)
        REFERENCES assembly_sections(section_id),

    FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id),

    CHECK (end_time IS NULL OR end_time >= start_time)
);

-- 6. Состав продукции
-- Связывает продукцию и детали (N:M)
CREATE TABLE product_parts (
    product_id INT NOT NULL,
    part_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,

    PRIMARY KEY (product_id, part_id),

    FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON DELETE CASCADE,

    FOREIGN KEY (part_id)
        REFERENCES parts(part_id)
        ON DELETE CASCADE,

    CHECK (quantity > 0)
);