---

CREATE DATABASE Склад;

USE Склад;

CREATE TABLE Единицы_измерения (
    ИД_единицы INT PRIMARY KEY,
    Название VARCHAR(20) NOT NULL
);

CREATE TABLE Товары (
    ИД_товара INT PRIMARY KEY,
    Наименование VARCHAR(150) NOT NULL,
    ИД_единицы INT REFERENCES Единицы_измерения(ИД_единицы)
);

CREATE TABLE Накладные (
    ИД_накладной INT PRIMARY KEY,
    Номер_накладной VARCHAR(20),
    Дата_время DATETIME,
    Тип VARCHAR(20)
);

CREATE TABLE Строки_накладных (
    ИД_строки INT PRIMARY KEY,
    ИД_накладной INT REFERENCES Накладные(ИД_накладной),
    ИД_товара INT REFERENCES Товары(ИД_товара),
    Количество DECIMAL(12,3)
);




USE Склад;

INSERT INTO Единицы_измерения VALUES
(1, 'кг'),
(2, 'л'),
(3, 'шт.');

INSERT INTO Товары VALUES
(1, 'Кофе в зёрнах, арабика', 1),
(2, 'Чай зелёный листовой', 1),
(3, 'Какао-порошок', 1),
(4, 'Мука пшеничная высшего сорта', 1),
(5, 'Сахар-песок', 1),
(6, 'Молоко пастеризованное 3,2 %', 2),
(7, 'Сливки 20 %', 2),
(8, 'Сметана 20 %', 1),
(9, 'Яйца куриные, категория С1', 3),
(10, 'Масло сливочное 82,5 %', 1),
(11, 'Хлеб пшеничный формовой', 3),
(12, 'Батон нарезной', 3),
(13, 'Булочка с маком', 3),
(14, 'Творог 5 %', 1),
(15, 'Йогурт питьевой клубника', 3),
(16, 'Картофель мытый', 1),
(17, 'Морковь мытая', 1),
(18, 'Лук репчатый', 1),
(19, 'Вода питьевая 0,5 л', 3),
(20, 'Сок яблочный 1 л', 2);

INSERT INTO Накладные VALUES
(1, 'НП-001', '2024-10-10 00:00:00', 'Поступление'),
(2, 'НП-002', '2024-10-11 00:00:00', 'Поступление'),
(3, 'НП-003', '2024-10-12 00:00:00', 'Поступление'),
(4, 'НП-004', '2024-10-13 00:00:00', 'Поступление'),
(5, 'НП-005', '2024-10-14 00:00:00', 'Поступление'),
(6, 'РП-001', '2024-10-15 00:00:00', 'Продажа'),
(7, 'РП-002', '2024-10-16 00:00:00', 'Продажа'),
(8, 'РП-003', '2024-10-17 00:00:00', 'Продажа'),
(9, 'РП-004', '2024-10-18 00:00:00', 'Продажа');

INSERT INTO Строки_накладных VALUES
(1, 1, 1, 20),
(2, 1, 2, 15),
(3, 1, 3, 10),
(4, 2, 4, 100),
(5, 2, 5, 50),
(6, 3, 6, 60),
(7, 3, 7, 20),
(8, 3, 8, 30),
(9, 4, 9, 360),
(10, 4, 10, 40),
(11, 5, 11, 100),
(12, 5, 12, 60),
(13, 5, 13, 80),
(14, 5, 14, 40),
(15, 5, 15, 200),
(16, 5, 16, 300),
(17, 5, 17, 150),
(18, 5, 18, 150),
(19, 5, 19, 300),
(20, 5, 20, 100),
(21, 6, 11, 50),
(22, 6, 12, 30),
(23, 6, 13, 40),
(24, 7, 14, 20),
(25, 7, 15, 120),
(26, 8, 16, 150),
(27, 8, 17, 80),
(28, 8, 18, 70),
(29, 9, 19, 200),
(30, 9, 20, 60);




DELIMITER //

CREATE TRIGGER Проверка_остатка
BEFORE INSERT ON Строки_накладных
FOR EACH ROW
BEGIN
    IF (SELECT Тип FROM Накладные WHERE ИД_накладной = NEW.ИД_накладной) = 'Продажа' THEN
        IF (SELECT IFNULL(SUM(CASE WHEN Н.Тип = 'Поступление' THEN С.Количество ELSE -С.Количество END), 0)
            FROM Строки_накладных С
            JOIN Накладные Н ON Н.ИД_накладной = С.ИД_накладной
            WHERE С.ИД_товара = NEW.ИД_товара) < NEW.Количество THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Нельзя продать в минус';
        END IF;
    END IF;
END//

DELIMITER ;




CREATE TRIGGER Проверка_остатка ON Строки_накладных
AFTER INSERT
AS
BEGIN
    IF EXISTS (
        SELECT 1
        FROM Строки_накладных С
        JOIN Накладные Н ON Н.ИД_накладной = С.ИД_накладной
        WHERE С.ИД_товара IN (SELECT ИД_товара FROM inserted)
        GROUP BY С.ИД_товара
        HAVING SUM(CASE WHEN Н.Тип = 'Поступление' THEN С.Количество ELSE -С.Количество END) < 0
    )
    BEGIN
        RAISERROR('Нельзя продать в минус', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;







USE Склад;

SELECT Т.Наименование, Е.Название
FROM Товары Т
JOIN Единицы_измерения Е ON Е.ИД_единицы = Т.ИД_единицы;

SELECT Номер_накладной, Дата_время
FROM Накладные
WHERE Тип = 'Поступление'
ORDER BY Дата_время;

SELECT Т.Наименование
FROM Товары Т
JOIN Единицы_измерения Е ON Е.ИД_единицы = Т.ИД_единицы
WHERE Е.Название = 'кг';

SELECT Т.Наименование, С.Количество
FROM Строки_накладных С
JOIN Товары Т ON Т.ИД_товара = С.ИД_товара
JOIN Накладные Н ON Н.ИД_накладной = С.ИД_накладной
WHERE Н.Номер_накладной = 'НП-001';

SELECT Н.Номер_накладной, COUNT(*) AS Количество_строк
FROM Накладные Н
JOIN Строки_накладных С ON С.ИД_накладной = Н.ИД_накладной
GROUP BY Н.Номер_накладной;

SELECT Т.Наименование, SUM(С.Количество) AS Всего_поступило
FROM Строки_накладных С
JOIN Товары Т ON Т.ИД_товара = С.ИД_товара
JOIN Накладные Н ON Н.ИД_накладной = С.ИД_накладной
WHERE Н.Тип = 'Поступление'
GROUP BY Т.Наименование;

SELECT Т.Наименование, SUM(С.Количество) AS Всего_продано
FROM Строки_накладных С
JOIN Товары Т ON Т.ИД_товара = С.ИД_товара
JOIN Накладные Н ON Н.ИД_накладной = С.ИД_накладной
WHERE Н.Тип = 'Продажа'
GROUP BY Т.Наименование
HAVING SUM(С.Количество) > 100;

SELECT Т.Наименование AS Товар,
       SUM(CASE WHEN Н.Тип = 'Поступление' THEN С.Количество ELSE -С.Количество END) AS Остаток
FROM Строки_накладных С
JOIN Товары Т ON Т.ИД_товара = С.ИД_товара
JOIN Накладные Н ON Н.ИД_накладной = С.ИД_накладной
WHERE Н.Дата_время < '2024-10-16'
GROUP BY Т.Наименование;

SELECT Наименование
FROM Товары
WHERE ИД_товара NOT IN (
    SELECT С.ИД_товара
    FROM Строки_накладных С
    JOIN Накладные Н ON Н.ИД_накладной = С.ИД_накладной
    WHERE Н.Тип = 'Продажа'
);

SELECT Т.Наименование, П.Поступило, Пр.Продано
FROM Товары Т
JOIN (SELECT С.ИД_товара, SUM(С.Количество) AS Поступило
      FROM Строки_накладных С
      JOIN Накладные Н ON Н.ИД_накладной = С.ИД_накладной
      WHERE Н.Тип = 'Поступление'
      GROUP BY С.ИД_товара) П ON П.ИД_товара = Т.ИД_товара
JOIN (SELECT С.ИД_товара, SUM(С.Количество) AS Продано
      FROM Строки_накладных С
      JOIN Накладные Н ON Н.ИД_накладной = С.ИД_накладной
      WHERE Н.Тип = 'Продажа'
      GROUP BY С.ИД_товара) Пр ON Пр.ИД_товара = Т.ИД_товара
WHERE Пр.Продано * 2 > П.Поступило;