CREATE DEFINER=CURRENT_USER PROCEDURE `stock_batches_rpt`(IN `product_id_filter` INT, IN `store_id_filter` INT)
BEGIN
                SELECT
                    ROW_NUMBER() OVER (ORDER BY store_inventories.created_at DESC, store_inventories.id DESC) AS Sqn,
                    scm_products.name AS Product,
                    phar_stores.name AS Store,
                    CONCAT('Batch ', store_inventories.batch) AS Batch,
                    CONCAT(FORMAT(store_inventories.balance, 2), ' ', scm_product_measurements.name) AS Balance,
                    CONCAT('$ ', FORMAT(store_inventories.cost, 2)) AS `Unit cost`,
                    IFNULL(DATE_FORMAT(store_inventories.expiration_date, '%d %b, %Y'), '-') AS Expiration,
                    DATE_FORMAT(store_inventories.created_at, '%d %b, %Y') AS Received
                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
                JOIN scm_product_measurements ON scm_product_measurements.id = store_inventories.purchase_measurement
                WHERE (product_id_filter = 0 OR store_inventories.product_id = product_id_filter)
                    AND (store_id_filter = 0 OR store_inventories.store_id = store_id_filter)
                ORDER BY store_inventories.created_at DESC, store_inventories.id DESC;
            END
