---

-- ============================================================
-- СОЗДАНИЕ БАЗЫ ДАННЫХ
-- ============================================================
DROP DATABASE IF EXISTS production_db;
CREATE DATABASE production_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
USE production_db;

-- ============================================================
-- ТАБЛИЦА 1: УЧАСТКИ
-- ============================================================
CREATE TABLE uchastki (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    nazvanie      VARCHAR(100) NOT NULL UNIQUE,
    mestopolozhenie VARCHAR(150) NOT NULL,
    nachalnik     VARCHAR(150) NOT NULL
) ENGINE=InnoDB;

-- ============================================================
-- ТАБЛИЦА 2: СОТРУДНИКИ
-- ============================================================
CREATE TABLE sotrudniki (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    familiya      VARCHAR(50) NOT NULL,
    imya          VARCHAR(50) NOT NULL,
    otchestvo     VARCHAR(50),
    dolzhnost     VARCHAR(80) NOT NULL,
    uchastok_id   INT NOT NULL,
    CONSTRAINT fk_sotr_uchastok
        FOREIGN KEY (uchastok_id) REFERENCES uchastki(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- ТАБЛИЦА 3: СМЕНЫ
-- ============================================================
CREATE TABLE smeny (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    data_smeny    DATE NOT NULL,
    tip_smeny     ENUM('дневная','ночная') NOT NULL,
    uchastok_id   INT NOT NULL,
    CONSTRAINT fk_smena_uchastok
        FOREIGN KEY (uchastok_id) REFERENCES uchastki(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT uq_smena UNIQUE (data_smeny, tip_smeny, uchastok_id)
) ENGINE=InnoDB;

-- ============================================================
-- ТАБЛИЦА 4: НОМЕНКЛАТУРА
-- ============================================================
CREATE TABLE nomenklatura (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    naimenovanie  VARCHAR(150) NOT NULL,
    artikul       VARCHAR(50)  NOT NULL UNIQUE,
    edinica_izm   VARCHAR(20)  NOT NULL
) ENGINE=InnoDB;

-- ============================================================
-- ТАБЛИЦА 5: ВЫПУСК ПРОДУКЦИИ
-- ============================================================
CREATE TABLE vypusk_produkcii (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    smena_id        INT NOT NULL,
    produkciya_id   INT NOT NULL,
    otvetstvennyy_id INT NOT NULL,
    kolichestvo     DECIMAL(12,3) NOT NULL CHECK (kolichestvo > 0),
    data_vremya     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_vyp_smena
        FOREIGN KEY (smena_id) REFERENCES smeny(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_vyp_prod
        FOREIGN KEY (produkciya_id) REFERENCES nomenklatura(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_vyp_sotr
        FOREIGN KEY (otvetstvennyy_id) REFERENCES sotrudniki(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- ТЕСТОВЫЕ ДАННЫЕ
-- ============================================================
INSERT INTO uchastki (nazvanie, mestopolozhenie, nachalnik) VALUES
('Литейный цех',       'Корпус А, 1 этаж',  'Петров Пётр Петрович'),
('Сборочный цех',      'Корпус Б, 2 этаж',  'Иванов Иван Иванович'),
('Упаковочный участок','Корпус В, 1 этаж',  'Сидорова Анна Сергеевна');

INSERT INTO sotrudniki (familiya, imya, otchestvo, dolzhnost, uchastok_id) VALUES
('Смирнов','Алексей','Викторович','Оператор',1),
('Кузнецова','Мария','Ивановна','Мастер смены',1),
('Орлов','Дмитрий','Николаевич','Оператор',2),
('Волкова','Елена','Петровна','Мастер смены',2),
('Зайцев','Сергей','Андреевич','Упаковщик',3);

INSERT INTO smeny (data_smeny, tip_smeny, uchastok_id) VALUES
('2024-05-01','дневная',1),
('2024-05-01','ночная', 1),
('2024-05-02','дневная',2),
('2024-05-02','ночная', 2),
('2024-05-03','дневная',3);

INSERT INTO nomenklatura (naimenovanie, artikul, edinica_izm) VALUES
('Втулка стальная 40мм','VT-040','шт'),
('Корпус редуктора',    'KR-100','шт'),
('Прокладка резиновая', 'PR-010','шт'),
('Масло индустриальное','MI-205','л');

INSERT INTO vypusk_produkcii
    (smena_id, produkciya_id, otvetstvennyy_id, kolichestvo, data_vremya) VALUES
(1,1,2, 1500, '2024-05-01 09:30:00'),
(1,3,1,  800, '2024-05-01 11:15:00'),
(2,1,2, 1200, '2024-05-01 22:00:00'),
(3,2,4,  200, '2024-05-02 10:00:00'),
(4,2,3,  180, '2024-05-02 23:45:00'),
(5,4,5,  500, '2024-05-03 09:00:00');

-- ============================================================
-- ПРЕДСТАВЛЕНИЯ (VIEW)
-- ============================================================

-- 1. Полная информация о выпуске продукции
CREATE OR REPLACE VIEW v_vypusk_polnyy AS
SELECT
    v.id                AS id_zapisi,
    s.data_smeny,
    s.tip_smeny,
    u.nazvanie          AS uchastok,
    n.naimenovanie      AS produkciya,
    n.artikul,
    n.edinica_izm,
    v.kolichestvo,
    CONCAT(sot.familiya,' ',sot.imya,' ',
           COALESCE(sot.otchestvo,'')) AS otvetstvennyy,
    v.data_vremya
FROM vypusk_produkcii v
JOIN smeny        s   ON s.id = v.smena_id
JOIN uchastki     u   ON u.id = s.uchastok_id
JOIN nomenklatura n   ON n.id = v.produkciya_id
JOIN sotrudniki   sot ON sot.id = v.otvetstvennyy_id;

-- 2. Сводка выпуска по участкам
CREATE OR REPLACE VIEW v_svodka_po_uchastkam AS
SELECT
    u.id                AS uchastok_id,
    u.nazvanie          AS uchastok,
    COUNT(v.id)         AS kolichestvo_zapisey,
    SUM(v.kolichestvo)  AS obshchiy_vyPusk
FROM uchastki u
LEFT JOIN smeny s            ON s.uchastok_id = u.id
LEFT JOIN vypusk_produkcii v ON v.smena_id   = s.id
GROUP BY u.id, u.nazvanie;

-- 3. Топ продукции по выпуску
CREATE OR REPLACE VIEW v_top_produkcii AS
SELECT
    n.id,
    n.naimenovanie,
    n.artikul,
    n.edinica_izm,
    COALESCE(SUM(v.kolichestvo),0) AS vsego_vypushcheno
FROM nomenklatura n
LEFT JOIN vypusk_produkcii v ON v.produkciya_id = n.id
GROUP BY n.id, n.naimenovanie, n.artikul, n.edinica_izm
ORDER BY vsego_vypushcheno DESC;

-- ============================================================
-- ТРИГГЕРЫ
-- ============================================================

-- Лог-таблица для триггеров
CREATE TABLE log_operaciy (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    tablica     VARCHAR(50),
    deystvie    VARCHAR(20),
    opisanie    VARCHAR(255),
    vremya      DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

DELIMITER $$

-- 1. Запрет выпуска на будущую дату
CREATE TRIGGER trg_vypusk_before_insert
BEFORE INSERT ON vypusk_produkcii
FOR EACH ROW
BEGIN
    IF NEW.data_vremya > NOW() THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Дата выпуска не может быть в будущем';
    END IF;
    INSERT INTO log_operaciy (tablica, deystvie, opisanie)
    VALUES ('vypusk_produkcii','INSERT',
            CONCAT('Новая запись: смена=',NEW.smena_id,
                   ', продукция=',NEW.produkciya_id,
                   ', кол-во=',NEW.kolichestvo));
END$$

-- 2. Проверка соответствия участка смены и сотрудника
CREATE TRIGGER trg_vypusk_check_uchastok
BEFORE INSERT ON vypusk_produkcii
FOR EACH ROW
BEGIN
    DECLARE v_uch_smeny INT;
    DECLARE v_uch_sotr  INT;
    SELECT uchastok_id INTO v_uch_smeny FROM smeny      WHERE id = NEW.smena_id;
    SELECT uchastok_id INTO v_uch_sotr  FROM sotrudniki WHERE id = NEW.otvetstvennyy_id;
    IF v_uch_smeny <> v_uch_sotr THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Ответственный сотрудник не относится к участку смены';
    END IF;
END$$

-- 3. Лог удаления записи о выпуске
CREATE TRIGGER trg_vypusk_after_delete
AFTER DELETE ON vypusk_produkcii
FOR EACH ROW
BEGIN
    INSERT INTO log_operaciy (tablica, deystvie, opisanie)
    VALUES ('vypusk_produkcii','DELETE',
            CONCAT('Удалена запись id=',OLD.id,
                   ', кол-во=',OLD.kolichestvo));
END$$

-- 4. Запрет удаления участка, если по нему есть смены
CREATE TRIGGER trg_uchastok_before_delete
BEFORE DELETE ON uchastki
FOR EACH ROW
BEGIN
    DECLARE cnt INT;
    SELECT COUNT(*) INTO cnt FROM smeny WHERE uchastok_id = OLD.id;
    IF cnt > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Нельзя удалить участок: есть связанные смены';
    END IF;
END$$

DELIMITER ;

-- ============================================================
-- ТРАНЗАКЦИИ
-- ============================================================

-- ТРАНЗАКЦИЯ 1: Добавление сотрудника и его смены
START TRANSACTION;
    INSERT INTO sotrudniki (familiya, imya, otchestvo, dolzhnost, uchastok_id)
    VALUES ('Белов','Артём','Сергеевич','Оператор',1);
    SET @new_sotr := LAST_INSERT_ID();

    INSERT INTO smeny (data_smeny, tip_smeny, uchastok_id)
    VALUES ('2024-05-04','дневная',1);
COMMIT;

-- ТРАНЗАКЦИЯ 2: Выпуск продукции (успешная)
START TRANSACTION;
    INSERT INTO vypusk_produkcii
        (smena_id, produkciya_id, otvetstvennyy_id, kolichestvo)
    VALUES (5, 3, 5, 700);
COMMIT;

-- ТРАНЗАКЦИЯ 3: Откат при ошибке
START TRANSACTION;
    UPDATE nomenklatura SET edinica_izm = 'кг' WHERE artikul = 'VT-040';
    -- Предположим, возникла ошибка бизнес-логики
    ROLLBACK;
-- Изменения НЕ применятся

-- ТРАНЗАКЦИЯ 4: Массовое добавление выпуска
START TRANSACTION;
    INSERT INTO vypusk_produkcii
        (smena_id, produkciya_id, otvetstvennyy_id, kolichestvo) VALUES
        (1, 4, 1, 100),
        (1, 2, 2,  50);
COMMIT;

-- ТРАНЗАКЦИЯ 5: SAVEPOINT — частичный откат
START TRANSACTION;
    INSERT INTO nomenklatura (naimenovanie, artikul, edinica_izm)
    VALUES ('Шайба М8','SH-008','шт');
    SAVEPOINT sp1;

    INSERT INTO nomenklatura (naimenovanie, artikul, edinica_izm)
    VALUES ('Болт М8','BL-008','шт');
    SAVEPOINT sp2;

    -- Откатываем только второй INSERT
    ROLLBACK TO SAVEPOINT sp1;
COMMIT;
-- Вставится только "Шайба М8"

-- ============================================================
-- ПРИМЕРЫ ЗАПРОСОВ К ПРЕДСТАВЛЕНИЯМ
-- ============================================================
-- SELECT * FROM v_vypusk_polnyy;
-- SELECT * FROM v_svodka_po_uchastkam;
-- SELECT * FROM v_top_produkcii;
-- SELECT * FROM log_operaciy ORDER BY vremya DESC;