-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Schema Pack K — Purchase order receipt v1.0
--
--  Apply AFTER pack H (reorder).
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  ORDERS COULD BE RAISED AND NEVER CLOSED
--  =================================================================
--
--  purchase_order.status already had PART_RECEIVED, RECEIVED and
--  CANCELLED, and the reorder engine reads them to net off stock that is
--  already on its way. Nothing could set any of them.
--
--  The consequence is quiet and compounding: every purchase order stays
--  open forever, on-order quantities only ever grow, and the shop is
--  gradually told to reorder less and less because the software believes
--  everything is already coming. It would look like the reorder feature
--  simply going cold.
--
--  Found by grepping the test suites for hand-written status updates. A
--  test that moves a workflow along with raw SQL is standing in for a
--  method nobody wrote.
-- =====================================================================

SET NAMES utf8mb4;

--  NOTE: no received_at column.
--
--  I added one, and then found learnLeadTime() already reads closed_at
--  for exactly that fact. Two columns meaning "when did it arrive" would
--  disagree the first time one of them was updated and the other was
--  not — and the one that decides reorder lead times is the one that
--  would be wrong. Third time in this project I have duplicated a column
--  that existed; the lesson is to read the table before extending it.
ALTER TABLE purchase_order
  ADD COLUMN received_by   CHAR(26)     NULL AFTER status,
  ADD COLUMN cancel_reason VARCHAR(300) NULL AFTER received_by;
-- ix_po_open already exists from the reorder pack; adding it again is a
-- duplicate-key error, which is what the migrator reported. The index is
-- what matters, not which pack created it.

-- What actually turned up, per line. Short delivery is the norm rather
-- than the exception — a distributor short-ships constantly — so the
-- ordered quantity must survive alongside the received one. Overwriting
-- it would erase the evidence for the credit note.
ALTER TABLE purchase_order_line
  ADD COLUMN qty_received  DECIMAL(14,3) NOT NULL DEFAULT 0 AFTER qty_units;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. A received order stops counting as on-order.
--   2. A short receipt stays PART_RECEIVED so the shortfall is still
--      expected, unless it is explicitly closed short.
--   3. Receiving more than was ordered is refused.
--   4. A received order cannot be cancelled.
--   5. Cancelling releases the quantity so it is suggested again.
--   6. The ordered quantity survives receipt.
-- =====================================================================