CREATE DEFINER=CURRENT_USER PROCEDURE `stock_movement_rpt`(IN `start_date_filter` DATE, IN `end_date_filter` DATE, IN `store_id_filter` INT, IN `product_id_filter` INT, IN `type_filter` VARCHAR(10))
BEGIN
                SELECT
                    ROW_NUMBER() OVER (ORDER BY d.created_at DESC, d.id DESC) AS Sqn,
                    DATE_FORMAT(d.created_at, '%d %b, %Y %H:%i') AS Date,
                    scm_products.name AS Product,
                    phar_stores.name AS Store,
                    CONCAT('Batch ', si.batch) AS Batch,
                    CASE WHEN d.in_quantity > 0
                        THEN CONCAT('IN ', FORMAT(d.in_quantity, 2))
                        ELSE CONCAT('OUT ', FORMAT(d.out_quantity, 2))
                    END AS Movement,
                    d.reference AS Reference,
                    FORMAT(SUM(d.in_quantity - d.out_quantity) OVER (
                        PARTITION BY si.store_id, si.batch ORDER BY d.created_at, d.id
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ), 2) AS `Running balance`
                FROM store_inventory_details d
                JOIN store_inventories si ON si.id = d.inv_id
                JOIN scm_products ON scm_products.id = si.product_id
                JOIN phar_stores ON phar_stores.id = si.store_id
                WHERE DATE(d.created_at) BETWEEN start_date_filter AND end_date_filter
                    AND (store_id_filter = 0 OR si.store_id = store_id_filter)
                    AND (product_id_filter = 0 OR si.product_id = product_id_filter)
                    AND (
                        type_filter IS NULL OR type_filter = ''
                        OR (type_filter = 'in' AND d.in_quantity > 0)
                        OR (type_filter = 'out' AND d.out_quantity > 0)
                    )
                ORDER BY d.created_at DESC, d.id DESC;
            END
