-- ===========================================================================
-- Migration: Inventory batch / expiry tracking (opt-in per item)
-- Date: 2026-07-29
--
-- An inventory item can opt in to batch tracking. A batched item's stock is the
-- SUM of its batches (each a lot with its own expiry), so item.qty becomes a
-- derived roll-up kept in step whenever batches change. Non-batched items keep
-- today's simple single-quantity behaviour untouched.
--
-- Consuming a batched item (a recorded loss) draws first-expiry-first-out; the
-- exact per-batch draw is stored on the usage row (batch_breakdown) so editing
-- or deleting that loss restores the very lots it took from. Receiving a batched
-- item via a GRN creates a batch (linked by grn_id so reversing the GRN removes
-- exactly it). Idempotent.
-- ===========================================================================

ALTER TABLE inventory_items
  ADD COLUMN IF NOT EXISTS track_batches TINYINT(1) NOT NULL DEFAULT 0 AFTER unit;

CREATE TABLE IF NOT EXISTS inventory_batches (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id     INT UNSIGNED  NOT NULL,
  item_id     INT UNSIGNED  NOT NULL,
  lot_no      VARCHAR(60)   NULL,
  qty         DECIMAL(12,2) NOT NULL DEFAULT 0,
  expiry_date DATE          NULL,
  received_on DATE          NULL,
  supplier    VARCHAR(120)  NULL,
  -- when a goods-received note created this batch, its id — so reversing that
  -- GRN removes exactly the batch it added (SET NULL if the GRN is later gone).
  grn_id      INT UNSIGNED  NULL,
  note        VARCHAR(255)  NULL,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_invb_item (item_id),
  KEY idx_invb_farm_expiry (farm_id, expiry_date),
  KEY idx_invb_grn (grn_id),
  CONSTRAINT fk_invb_item FOREIGN KEY (item_id) REFERENCES inventory_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_invb_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- per-batch breakdown of a loss against a batched item, e.g.
-- [{"lot":"L1","expiry":"2026-09-01","receivedOn":null,"supplier":null,"qty":5}]
ALTER TABLE inventory_usage
  ADD COLUMN IF NOT EXISTS batch_breakdown JSON NULL AFTER note;