---

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;