-- ============================================================
-- Schema Update v6
-- Run this AFTER schema_update_v5.sql.
-- Adds expiry-date tracking. Different deliveries of the same item can have
-- different expiry dates, so expiry is tracked per received BATCH, not on
-- the item itself. Item codes are now auto-generated by the app (e.g.
-- ITM-00001) - no schema change needed for that, the code column already exists.
-- ============================================================

ALTER TABLE items ADD COLUMN track_expiry TINYINT(1) NOT NULL DEFAULT 0 AFTER reorder_level;

CREATE TABLE IF NOT EXISTS item_batches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT NOT NULL,
  grn_id INT NULL,
  quantity_received DECIMAL(14,2) NOT NULL,
  quantity_remaining DECIMAL(14,2) NOT NULL,
  unit_cost DECIMAL(14,2) DEFAULT 0,
  expiry_date DATE NULL,
  received_date DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
  FOREIGN KEY (grn_id) REFERENCES goods_received_notes(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE INDEX idx_item_batches_expiry ON item_batches (expiry_date);
