CREATE DEFINER=CURRENT_USER PROCEDURE `expiry_rpt`(IN `threshold_days` INT, IN `store_id_filter` INT, IN `category_id_filter` INT, IN `status_filter` VARCHAR(20))
BEGIN
                SELECT
                    ROW_NUMBER() OVER (ORDER BY store_inventories.expiration_date ASC, store_inventories.id ASC) AS Sqn,
                    scm_products.name AS Product,
                    phar_stores.name AS Store,
                    CONCAT('Batch ', store_inventories.batch) AS Batch,
                    FORMAT(store_inventories.balance, 2) AS Qty,
                    DATE_FORMAT(store_inventories.expiration_date, '%d %b, %Y') AS `Expiration date`,
                    DATEDIFF(store_inventories.expiration_date, CURDATE()) AS `Days remaining`,
                    CASE
                        WHEN DATEDIFF(store_inventories.expiration_date, CURDATE()) < 0 THEN 'Expired'
                        WHEN DATEDIFF(store_inventories.expiration_date, CURDATE()) <= threshold_days THEN 'Expiring soon'
                        ELSE 'OK'
                    END AS Status
                FROM store_inventories
                JOIN scm_products ON scm_products.id = store_inventories.product_id
                JOIN phar_stores ON phar_stores.id = store_inventories.store_id
                WHERE scm_products.track_expiration = 'yes'
                    AND store_inventories.balance > 0
                    AND store_inventories.expiration_date IS NOT NULL
                    AND (store_id_filter = 0 OR store_inventories.store_id = store_id_filter)
                    AND (category_id_filter = 0 OR scm_products.category_id = category_id_filter)
                    AND (
                        status_filter IS NULL OR status_filter = ''
                        OR (status_filter = 'expired' AND store_inventories.expiration_date < CURDATE())
                        OR (status_filter = 'expiring' AND store_inventories.expiration_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL threshold_days DAY))
                        OR (status_filter = 'ok' AND store_inventories.expiration_date > DATE_ADD(CURDATE(), INTERVAL threshold_days DAY))
                    )
                ORDER BY store_inventories.expiration_date ASC, store_inventories.id ASC;
            END
