---

-- ============================================
-- БД: Учет продукции на участке монтажа плат
-- ============================================

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


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


-- 3. Продукция
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    product_code VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);


-- 4. Детали
CREATE TABLE parts (
    part_id SERIAL PRIMARY KEY,
    part_name VARCHAR(150) NOT NULL,
    part_code VARCHAR(50) NOT NULL UNIQUE,
    unit VARCHAR(30) NOT NULL,
    description TEXT
);


-- 5. Операции монтажа плат
-- Центральная таблица, к которой идут связи
CREATE TABLE assembly_operations (
    operation_id SERIAL PRIMARY KEY,

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

    operation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    quantity INT NOT NULL CHECK (quantity > 0),
    operation_type VARCHAR(100) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'Выполнена',

    description TEXT,

    -- Связь с сотрудниками
    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)
);