---

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


-- Сборочные участки
CREATE TABLE assembly_sections (
    section_id INT NOT NULL AUTO_INCREMENT,
    section_name VARCHAR(100) NOT NULL,
    location VARCHAR(150),
    description TEXT,
    
    PRIMARY KEY (section_id),
    UNIQUE (section_name)
) ENGINE=InnoDB;


-- Продукция
CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(150) NOT NULL,
    product_code VARCHAR(50) NOT NULL,
    description TEXT,
    
    PRIMARY KEY (product_id),
    UNIQUE (product_code)
) ENGINE=InnoDB;


-- Детали
CREATE TABLE parts (
    part_id INT NOT NULL AUTO_INCREMENT,
    part_name VARCHAR(150) NOT NULL,
    part_code VARCHAR(50) NOT NULL,
    unit VARCHAR(30) NOT NULL,
    description TEXT,
    
    PRIMARY KEY (part_id),
    UNIQUE (part_code)
) ENGINE=InnoDB;


-- Операции монтажа плат
CREATE TABLE assembly_operations (
    operation_id INT NOT NULL AUTO_INCREMENT,

    employee_id INT NOT NULL,
    section_id INT NOT NULL,
    product_id INT NOT NULL,
    part_id INT NOT NULL,

    operation_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    quantity INT NOT NULL,
    operation_type VARCHAR(100) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'Выполнена',
    description TEXT,

    PRIMARY KEY (operation_id),

    CONSTRAINT fk_operation_employee
        FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id),

    CONSTRAINT fk_operation_section
        FOREIGN KEY (section_id)
        REFERENCES assembly_sections(section_id),

    CONSTRAINT fk_operation_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id),

    CONSTRAINT fk_operation_part
        FOREIGN KEY (part_id)
        REFERENCES parts(part_id),

    CHECK (quantity > 0)
) ENGINE=InnoDB;