---

CREATE OR REPLACE VIEW Vibor AS
SELECT 
    pb.BatchCode,
    p.Name AS ProductName,
    pb.ExpirationDate,
    DATEDIFF(pb.ExpirationDate, CURDATE()) AS DaysLeft,
    pb.Quantity,
    pb.Status
FROM ProductBatch pb
JOIN Product p ON pb.ProductCode = p.ProductCode
WHERE DATEDIFF(pb.ExpirationDate, CURDATE()) <= 30 
   OR pb.Status = 'ExpiringSoon'
ORDER BY pb.ExpirationDate;

CREATE OR REPLACE VIEW Result AS
SELECT 
    COUNT(*) AS TotalBatches,
    SUM(Quantity) AS TotalQuantity,
    SUM(CASE WHEN Status = 'Expired' THEN Quantity ELSE 0 END) AS ExpiredQuantity,
    COUNT(CASE WHEN Status = 'ExpiringSoon' THEN 1 END) AS ExpiringSoonCount,
    MIN(DATEDIFF(ExpirationDate, CURDATE())) AS MinDaysLeft,
    AVG(DATEDIFF(ExpirationDate, CURDATE())) AS AvgDaysLeft
FROM ProductBatch;

CREATE OR REPLACE VIEW Itog 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 ASC;