CREATE DEFINER=CURRENT_USER PROCEDURE `inventory_valuation_rpt`(IN `store_id_filter` INT, IN `category_id_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_categories.name, scm_products.name) AS Sqn,
                    scm_products.name AS Product,
                    IFNULL(scm_categories.name, 'Uncategorized') AS Category,
                    phar_stores.name AS Store,
                    TRIM(CONCAT(FORMAT(SUM(store_inventories.balance), 2), ' ', IFNULL(scm_product_measurements.name, ''))) 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`
                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
                LEFT JOIN scm_product_measurements ON scm_product_measurements.id = scm_products.purchase_measurement
                WHERE store_inventories.balance > 0
                    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)
                GROUP BY scm_products.id, scm_products.name, scm_categories.name, store_inventories.store_id, phar_stores.name, scm_product_measurements.name
                ORDER BY phar_stores.name, scm_categories.name, scm_products.name;
            END
