---

CREATE DATABASE IF NOT EXISTS product_expiration;

USE product_expiration;

CREATE TABLE Product (
    ProductCode VARCHAR(20) PRIMARY KEY,
    Name VARCHAR(150) NOT NULL,
    Unit VARCHAR(20) DEFAULT 'pcs',
    ShelfLifeDays INT NOT NULL,
    Description TEXT,
    Price DECIMAL(12,2) DEFAULT 0.00
);

CREATE TABLE Supplier (
    SupplierCode VARCHAR(20) PRIMARY KEY,
    CompanyName VARCHAR(120) NOT NULL,
    Director VARCHAR(100),
    Phone VARCHAR(30),
    Address VARCHAR(255),
    Email VARCHAR(100)
);

CREATE TABLE Warehouse (
    WarehouseCode VARCHAR(20) PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Address VARCHAR(255),
    Phone VARCHAR(30),
    Responsible VARCHAR(100)
);

CREATE TABLE ProductBatch (
    BatchCode VARCHAR(30) PRIMARY KEY,
    ProductCode VARCHAR(20) NOT NULL,
    ProductionDate DATE,
    ReceiptDate DATE NOT NULL,
    ExpirationDate DATE NOT NULL,
    Quantity INT UNSIGNED NOT NULL DEFAULT 0,
    WarehouseCode VARCHAR(20) NOT NULL,
    SupplierCode VARCHAR(20) NOT NULL,
    Status ENUM('Normal', 'ExpiringSoon', 'Expired') DEFAULT 'Normal',
    
    FOREIGN KEY (ProductCode) REFERENCES Product(ProductCode),
    FOREIGN KEY (WarehouseCode) REFERENCES Warehouse(WarehouseCode),
    FOREIGN KEY (SupplierCode) REFERENCES Supplier(SupplierCode),
    
    INDEX idx_expiration (ExpirationDate),
    INDEX idx_status (Status)
);

CREATE TABLE Notification (
    NotificationID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    BatchCode VARCHAR(30) NOT NULL,
    NotificationDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    Type ENUM('Warning', 'Expired', 'Info') NOT NULL,
    Message TEXT NOT NULL,
    Sent BOOLEAN DEFAULT FALSE,
    
    FOREIGN KEY (BatchCode) REFERENCES ProductBatch(BatchCode) ON DELETE CASCADE
);

CREATE OR REPLACE VIEW ProductsWithExpiration AS
SELECT 
    p.Name AS Product,
    pb.BatchCode,
    pb.ReceiptDate,
    pb.ExpirationDate,
    DATEDIFF(pb.ExpirationDate, CURDATE()) AS DaysLeft,
    pb.Quantity,
    pb.Status,
    w.Name AS Warehouse,
    s.CompanyName AS Supplier
FROM ProductBatch pb
JOIN Product p ON pb.ProductCode = p.ProductCode
JOIN Warehouse w ON pb.WarehouseCode = w.WarehouseCode
JOIN Supplier s ON pb.SupplierCode = s.SupplierCode
ORDER BY pb.ExpirationDate;