---

-- ============================================
-- БАЗА ДАННЫХ: УЧАСТОК МОНТАЖА ПЕЧАТНЫХ ПЛАТ
-- MySQL 8.0
-- ============================================

CREATE DATABASE IF NOT EXISTS montazh_plat
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE montazh_plat;

-- Удаление таблиц для повторного запуска скрипта
DROP TABLE IF EXISTS vypusk_produkcii;
DROP TABLE IF EXISTS partiya;
DROP TABLE IF EXISTS sotrudnik;
DROP TABLE IF EXISTS produkciya;
DROP TABLE IF EXISTS uchastok;

-- ============================================
-- 1. УЧАСТКИ
-- ============================================

CREATE TABLE uchastok (
    id_uchastka BIGINT PRIMARY KEY AUTO_INCREMENT,
    naimenovanie VARCHAR(100) NOT NULL,
    rukovoditel VARCHAR(150) NOT NULL,
    smena VARCHAR(20) NOT NULL
);

-- ============================================
-- 2. СОТРУДНИКИ
-- ============================================

CREATE TABLE sotrudnik (
    id_sotrudnika BIGINT PRIMARY KEY AUTO_INCREMENT,
    FIO VARCHAR(150) NOT NULL,
    tabelniy_nomer VARCHAR(20) NOT NULL UNIQUE,
    razryad INT NOT NULL,
    id_uchastka BIGINT NOT NULL,

    CONSTRAINT chk_razryad
        CHECK (razryad BETWEEN 1 AND 6),

    CONSTRAINT fk_sotrudnik_uchastok
        FOREIGN KEY (id_uchastka)
        REFERENCES uchastok(id_uchastka)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

-- ============================================
-- 3. ПРОДУКЦИЯ
-- ============================================

CREATE TABLE produkciya (
    id_produkcii BIGINT PRIMARY KEY AUTO_INCREMENT,
    naimenovanie VARCHAR(150) NOT NULL,
    artikul VARCHAR(50) NOT NULL UNIQUE,
    tip_platy VARCHAR(100) NOT NULL,
    norma_vremeni DECIMAL(6,2) NOT NULL,

    CONSTRAINT chk_norma_vremeni
        CHECK (norma_vremeni > 0)
);

-- ============================================
-- 4. ПАРТИИ ПРОДУКЦИИ
-- ============================================

CREATE TABLE partiya (
    id_partii BIGINT PRIMARY KEY AUTO_INCREMENT,
    nomer_partii VARCHAR(50) NOT NULL UNIQUE,
    data_zapуска DATE NOT NULL,
    id_produkcii BIGINT NOT NULL,
    plan_kolichestvo INT NOT NULL,
    fakt_kolichestvo INT NOT NULL DEFAULT 0,

    CONSTRAINT chk_plan
        CHECK (plan_kolichestvo > 0),

    CONSTRAINT chk_fakt
        CHECK (
            fakt_kolichestvo >= 0
            AND fakt_kolichestvo <= plan_kolichestvo
        ),

    CONSTRAINT fk_partiya_produkciya
        FOREIGN KEY (id_produkcii)
        REFERENCES produkciya(id_produkcii)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

-- ============================================
-- 5. ВЫПУСК ПРОДУКЦИИ
-- ============================================

CREATE TABLE vypusk_produkcii (
    id_vypuska BIGINT PRIMARY KEY AUTO_INCREMENT,
    id_produkcii BIGINT NOT NULL,
    id_sotrudnika BIGINT NOT NULL,
    id_partii BIGINT NOT NULL,
    data DATE NOT NULL,
    kolichestvo INT NOT NULL,
    brak INT NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'В работе',

    CONSTRAINT chk_kolichestvo
        CHECK (kolichestvo > 0),

    CONSTRAINT chk_brak
        CHECK (brak >= 0 AND brak <= kolichestvo),

    CONSTRAINT chk_status
        CHECK (
            status IN (
                'В работе',
                'Завершено',
                'На проверке'
            )
        ),

    CONSTRAINT fk_vypusk_produkciya
        FOREIGN KEY (id_produkcii)
        REFERENCES produkciya(id_produkcii)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,

    CONSTRAINT fk_vypusk_sotrudnik
        FOREIGN KEY (id_sotrudnika)
        REFERENCES sotrudnik(id_sotrudnika)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,

    CONSTRAINT fk_vypusk_partiya
        FOREIGN KEY (id_partii)
        REFERENCES partiya(id_partii)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

-- ============================================
-- ЗАПОЛНЕНИЕ ТАБЛИЦ
-- ПО 5 ЗАПИСЕЙ В КАЖДУЮ
-- ============================================

-- 1. Участки

INSERT INTO uchastok
    (naimenovanie, rukovoditel, smena)
VALUES
    ('Монтаж печатных плат', 'Иванов Сергей Петрович', 'Первая'),
    ('Поверхностный монтаж', 'Петров Алексей Иванович', 'Вторая'),
    ('Пайка компонентов', 'Сидоров Андрей Викторович', 'Первая'),
    ('Контроль качества', 'Смирнова Елена Олеговна', 'Вторая'),
    ('Финишная сборка', 'Кузнецов Павел Дмитриевич', 'Первая');

-- 2. Сотрудники

INSERT INTO sotrudnik
    (FIO, tabelniy_nomer, razryad, id_uchastka)
VALUES
    ('Александров Иван Сергеевич', 'Т001', 4, 1),
    ('Борисова Мария Андреевна', 'Т002', 5, 2),
    ('Васильев Дмитрий Олегович', 'Т003', 3, 3),
    ('Григорьева Анна Павловна', 'Т004', 4, 4),
    ('Дмитриев Максим Игоревич', 'Т005', 6, 5);

-- 3. Продукция

INSERT INTO produkciya
    (naimenovanie, artikul, tip_platy, norma_vremeni)
VALUES
    ('Контроллер управления', 'PCB-001', 'Двусторонняя', 12.50),
    ('Блок питания', 'PCB-002', 'Двусторонняя', 18.00),
    ('Плата датчика температуры', 'PCB-003', 'Односторонняя', 8.50),
    ('Модуль управления двигателем', 'PCB-004', 'Многослойная', 25.00),
    ('Коммуникационный модуль', 'PCB-005', 'Многослойная', 20.00);

-- 4. Партии

INSERT INTO partiya
    (nomer_partii, data_zapуска, id_produkcii,
     plan_kolichestvo, fakt_kolichestvo)
VALUES
    ('PART-001', '2026-10-01', 1, 500, 450),
    ('PART-002', '2026-10-02', 2, 300, 280),
    ('PART-003', '2026-10-03', 3, 700, 650),
    ('PART-004', '2026-10-04', 4, 200, 180),
    ('PART-005', '2026-10-05', 5, 250, 220);

-- 5. Выпуск продукции

INSERT INTO vypusk_produkcii
    (id_produkcii, id_sotrudnika, id_partii,
     data, kolichestvo, brak, status)
VALUES
    (1, 1, 1, '2026-10-06', 100, 2, 'Завершено'),
    (2, 2, 2, '2026-10-06', 80, 3, 'Завершено'),
    (3, 3, 3, '2026-10-07', 150, 4, 'На проверке'),
    (4, 4, 4, '2026-10-08', 60, 1, 'В работе'),
    (5, 5, 5, '2026-10-09', 70, 2, 'Завершено');

-- ============================================
-- ПРОВЕРКА ДАННЫХ
-- ============================================

SELECT * FROM uchastok;
SELECT * FROM sotrudnik;
SELECT * FROM produkciya;
SELECT * FROM partiya;
SELECT * FROM vypusk_produkcii;

-- ============================================
-- ПРОВЕРКА СВЯЗЕЙ МЕЖДУ ТАБЛИЦАМИ
-- ============================================

SELECT
    v.id_vypuska,
    p.nomer_partii,
    pr.naimenovanie AS produkciya,
    s.FIO AS sotrudnik,
    u.naimenovanie AS uchastok,
    v.data,
    v.kolichestvo,
    v.brak,
    v.status
FROM vypusk_produkcii v
JOIN partiya p
    ON v.id_partii = p.id_partii
JOIN produkciya pr
    ON v.id_produkcii = pr.id_produkcii
JOIN sotrudnik s
    ON v.id_sotrudnika = s.id_sotrudnika
JOIN uchastok u
    ON s.id_uchastka = u.id_uchastka
ORDER BY v.id_vypuska;