02_procedure_test_and_reset.sql

/* ============================================================
   Procedure tests and reset (Q20)

   Five calls covering one success and four distinct failures:
   insufficient stock, unknown product, negative quantity and a
   duplicate order id. Each failure should raise an exception and
   leave the database untouched.

   The reset below restores every row the tests created, so the
   demonstration can be run repeatedly and the reconciliation
   baselines still hold afterwards.
   ============================================================ */


-- ============================================================
-- Test | Apple AirPods 3rd Gen, product 1, opening stock 55
-- Expected: the first call succeeds and stock falls to 15. The
--   remaining four each raise an exception and change nothing.
-- Note: test ids start at 25001, well clear of the highest real
--   id of 21,629, so no live row can be touched.
-- ============================================================

-- Opening state
SELECT stock FROM inventory WHERE product_id = 1;   -- 55

-- 1. Success. 40 units at 199.99, stock falls to 15.
CALL add_sales(25001, 2, 5, 25001, 1, 40);
SELECT stock FROM inventory WHERE product_id = 1;   -- 15

-- 2. Insufficient stock. 30 requested, 15 available.
CALL add_sales(25002, 2, 5, 25002, 1, 30);

-- 3. Product does not exist.
CALL add_sales(25003, 2, 5, 25003, 99999, 1);

-- 4. Negative quantity.
CALL add_sales(25004, 2, 5, 25004, 1, -5);

-- 5. Duplicate order id, caught by the primary key rather than
--    by a guard we wrote.
CALL add_sales(25001, 2, 5, 25001, 1, 1);

-- Closing state. Still 15, so none of the four failures changed anything.
SELECT stock FROM inventory WHERE product_id = 1;   -- 15

-- ============================================================
-- Reset | Restore the database to its pre-test state
-- Approach: delete the four test orders from all three tables
--   and set product 1 stock back to 55.
-- Note: deletions are bounded to the exact test ids rather than
--   a range, so a mistyped id cannot remove real data. Confirm
--   total_sale returns to 12,642,070.56 afterwards.
-- ============================================================

-- Children before parent, or the foreign keys block the delete.
DELETE FROM order_items WHERE order_id IN (25001, 25002, 25003, 25004);
DELETE FROM payments    WHERE order_id IN (25001, 25002, 25003, 25004);
DELETE FROM orders      WHERE order_id IN (25001, 25002, 25003, 25004);

UPDATE inventory SET stock = 55 WHERE product_id = 1;

-- Confirm the baselines hold again
SELECT stock FROM inventory WHERE product_id = 1;               -- 55
SELECT COUNT(*) AS test_orders FROM orders WHERE order_id >= 25000;  -- 0
SELECT ROUND(SUM(total_sale), 2) AS gross_baseline FROM order_items; -- 12642070.56