CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_low_stock`(IN `store` INT)
BEGIN
-- Low stock alerts: products at or below their reorder level in a store
-- (out of stock first). The reorder level is set on the product.
SELECT pr.name AS `Product`, s.name AS `Store`,
       CONCAT(FORMAT(IFNULL(b.balance, 0), 2), ' ', COALESCE(m.name, '')) AS `In stock`,
       FORMAT(pr.reorder_level, 2) AS `Reorder level`,
       FORMAT(GREATEST(pr.reorder_level - IFNULL(b.balance, 0), 0), 2) AS `Short by`,
       IF(IFNULL(b.balance, 0) <= 0, '<span style="color:#b42318;font-weight:600">Out of stock</span>', 'Low') AS `Status`
FROM scm_products pr
JOIN phar_stores s ON s.status = 'enabled' AND (store = 0 OR s.id = store)
LEFT JOIN (SELECT store_id, product_id, SUM(balance) balance FROM store_inventories GROUP BY store_id, product_id) b ON b.product_id = pr.id AND b.store_id = s.id
LEFT JOIN scm_product_measurements m ON m.id = pr.purchase_measurement
WHERE pr.status = 'enabled' AND IFNULL(pr.reorder_level, 0) > 0 AND IFNULL(b.balance, 0) <= pr.reorder_level
  AND (b.product_id IS NOT NULL OR EXISTS (SELECT 1 FROM store_inventories si WHERE si.product_id = pr.id AND si.store_id = s.id) OR store <> 0)
ORDER BY IFNULL(b.balance, 0) > 0, s.name, pr.name;
END
