---

-- =============================================
-- База данных: Сроки годности товаров
-- =============================================

CREATE DATABASE IF NOT EXISTS `goods_expiration` 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

USE `goods_expiration`;

-- =============================================
-- 1. Справочник товаров
-- =============================================
CREATE TABLE `Товар` (
    `Код_товара`       VARCHAR(20)     NOT NULL,
    `Название`         VARCHAR(150)    NOT NULL,
    `Единица_измерения` VARCHAR(20)     NOT NULL DEFAULT 'шт',
    `Срок_годности_дни` INT             NOT NULL,
    `Характеристики`   TEXT            NULL,
    `Цена`             DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    
    PRIMARY KEY (`Код_товара`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- 2. Поставщики
-- =============================================
CREATE TABLE `Поставщик` (
    `Код_поставщика`   VARCHAR(20)     NOT NULL,
    `Название_фирмы`   VARCHAR(120)    NOT NULL,
    `Директор`         VARCHAR(100)    NULL,
    `Телефон`          VARCHAR(30)     NULL,
    `Адрес`            VARCHAR(255)    NULL,
    `Email`            VARCHAR(100)    NULL,
    
    PRIMARY KEY (`Код_поставщика`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- 3. Склады
-- =============================================
CREATE TABLE `Склад` (
    `Код_склада`       VARCHAR(20)     NOT NULL,
    `Название`         VARCHAR(100)    NOT NULL,
    `Адрес`            VARCHAR(255)    NULL,
    `Телефон`          VARCHAR(30)     NULL,
    `Ответственный`    VARCHAR(100)    NULL,
    
    PRIMARY KEY (`Код_склада`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- 4. Партии товаров (главная таблица модуля)
-- =============================================
CREATE TABLE `Партия_товара` (
    `Код_партии`       VARCHAR(30)     NOT NULL,
    `Код_товара`       VARCHAR(20)     NOT NULL,
    `Дата_производства` DATE            NULL,
    `Дата_поступления` DATE            NOT NULL,
    `Дата_истечения`   DATE            NOT NULL,
    `Количество`       INT UNSIGNED    NOT NULL DEFAULT 0,
    `Код_склада`       VARCHAR(20)     NOT NULL,
    `Код_поставщика`   VARCHAR(20)     NOT NULL,
    `Статус`           ENUM('В_норме', 'Близко_к_окончанию', 'Просрочен') 
                       NOT NULL DEFAULT 'В_норме',
    
    PRIMARY KEY (`Код_партии`),
    FOREIGN KEY (`Код_товара`)     REFERENCES `Товар`(`Код_товара`) ON DELETE RESTRICT,
    FOREIGN KEY (`Код_склада`)     REFERENCES `Склад`(`Код_склада`) ON DELETE RESTRICT,
    FOREIGN KEY (`Код_поставщика`) REFERENCES `Поставщик`(`Код_поставщика`) ON DELETE RESTRICT,
    
    INDEX idx_дата_истечения (`Дата_истечения`),
    INDEX idx_статус (`Статус`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- 5. Уведомления
-- =============================================
CREATE TABLE `Уведомление` (
    `ID_уведомления`   INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `Код_партии`       VARCHAR(30)     NOT NULL,
    `Дата_уведомления` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `Тип`              ENUM('Предупреждение', 'Просрочка', 'Информация') 
                       NOT NULL,
    `Сообщение`        TEXT            NOT NULL,
    `Отправлено`       TINYINT(1)      NOT NULL DEFAULT 0,
    
    PRIMARY KEY (`ID_уведомления`),
    FOREIGN KEY (`Код_партии`) REFERENCES `Партия_товара`(`Код_партии`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- 6. Представление (View) для удобного отчёта
-- =============================================
CREATE OR REPLACE VIEW `Товары_на_складе_с_сроками` AS
SELECT 
    t.`Название` AS Товар,
    p.`Код_партии`,
    p.`Дата_поступления`,
    p.`Дата_истечения`,
    DATEDIFF(p.`Дата_истечения`, CURDATE()) AS Дней_до_истечения,
    p.`Количество`,
    p.`Статус`,
    s.`Название` AS Склад,
    sup.`Название_фирмы` AS Поставщик
FROM `Партия_товара` p
JOIN `Товар` t ON p.`Код_товара` = t.`Код_товара`
JOIN `Склад` s ON p.`Код_склада` = s.`Код_склада`
JOIN `Поставщик` sup ON p.`Код_поставщика` = sup.`Код_поставщика`
ORDER BY p.`Дата_истечения`;