-- Phase 2: promote per-batch fields from the `data` JSON blob to real, indexable
-- columns on `stocks`. Additive and nullable — existing rows are unaffected until
-- backfilled (see src/scripts/backfill-stock-batch-columns.js). The `data` blob is
-- kept for alertQuantity/location/notes/IMEIs and is still mirrored for compatibility.

ALTER TABLE `stocks` ADD COLUMN `cost` DECIMAL(11,2) NULL DEFAULT NULL;
ALTER TABLE `stocks` ADD COLUMN `expiry_date` DATE NULL DEFAULT NULL;
ALTER TABLE `stocks` ADD COLUMN `batch_number` VARCHAR(255) NULL DEFAULT NULL;

-- FEFO (first-expiry-first-out) deduction and the expiring-soon report both order
-- by expiry, so index it.
ALTER TABLE `stocks` ADD INDEX `idx_stocks_expiry_date` (`expiry_date`);