CREATE DEFINER=CURRENT_USER PROCEDURE `stock_on_hand_summary_rpt`(IN `store_id_filter` INT, IN `category_id_filter` INT, IN `product_id_filter` INT, IN `show_zero_filter` INT)
BEGIN
                SET @cost_mode = (SELECT value FROM settings WHERE name = 'cost_calculation' LIMIT 1);

                SELECT
                    ROW_NUMBER() OVER (ORDER BY phar_stores.name, scm_products.name) AS Sqn,
                    scm_products.name AS Product,
                    IFNULL(scm_categories.name, 'Uncategorized') AS Category,
                    phar_stores.name AS Store,
                    FORMAT(SUM(store_inventories.balance), 2) AS `Qty on hand`,
                    CONCAT('$ ', FORMAT(
                        CASE WHEN @cost_mode = 'Fifo'
                            THEN IFNULL(SUM(store_inventories.balance * store_inventories.cost), 0) / NULLIF(SUM(store_inventories.balance), 0)
                            ELSE MAX(scm_products.cost_price)
                        END, 2)) AS `Unit cost`,
                    CONCAT('$ ', FORMAT(
                        CASE WHEN @cost_mode = 'Fifo'
                            THEN IFNULL(SUM(store_inventories.balance * store_inventories.cost), 0)
                            ELSE SUM(store_inventories.balance) * MAX(scm_products.cost_price)
                        END, 2)) AS `Total value`,
                    IFNULL(DATE_FORMAT(MIN(CASE WHEN store_inventories.balance > 0 THEN store_inventories.expiration_date END), '%d %b, %Y'), '-') AS `Nearest expiry`,
                    COUNT(CASE WHEN store_inventories.balance > 0 THEN 1 END) AS Batches
                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
                LEFT JOIN scm_categories ON scm_categories.id = scm_products.category_id
                WHERE (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 (product_id_filter = 0 OR scm_products.id = product_id_filter)
                GROUP BY scm_products.id, scm_products.name, scm_categories.name, store_inventories.store_id, phar_stores.name
                HAVING (show_zero_filter = 1 OR SUM(store_inventories.balance) > 0)
                ORDER BY phar_stores.name, scm_products.name;
            END
