---

CREATE DATABASE IF NOT EXISTS product_expiration;
USE product_expiration;

-- 1. Product
CREATE TABLE Product (
    ProductCode       VARCHAR(20) PRIMARY KEY,
    Name              VARCHAR(150) NOT NULL,
    ShelfLifeDays     INT NOT NULL,
    Characteristics   TEXT,
    Price             DECIMAL(12,2) NOT NULL DEFAULT 0.00
);

-- 2. Supplier
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)
);

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

-- 4. ProductBatch
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)
);

-- 5. ProductOnWarehouse
CREATE TABLE ProductOnWarehouse (
    ProductCode         VARCHAR(20) NOT NULL,
    WarehouseCode       VARCHAR(20) NOT NULL,
    TotalQuantity       INT UNSIGNED NOT NULL DEFAULT 0,
    ExpiredQuantity     INT UNSIGNED NOT NULL DEFAULT 0,
    
    PRIMARY KEY (ProductCode, WarehouseCode),
    FOREIGN KEY (ProductCode)   REFERENCES Product(ProductCode),
    FOREIGN KEY (WarehouseCode) REFERENCES Warehouse(WarehouseCode)
);

-- 6. Notification
CREATE TABLE Notification (
    NotificationID    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    BatchCode         VARCHAR(30) NOT NULL,
    NotificationDate  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    Type              VARCHAR(50) NOT NULL,        -- Warning / Expired
    Message           TEXT NOT NULL,
    Sent              BOOLEAN DEFAULT FALSE,
    
    FOREIGN KEY (BatchCode) REFERENCES ProductBatch(BatchCode)
);