-- ============================================================
-- FashCognitive - Material Issue <-> Inventory Serial link
-- Links each material_issue_items line to the physical inventory
-- serial (FCINV-...) it consumes, so the serial's digital thread
-- shows the downstream 'issue' station.
-- ============================================================

SELECT COUNT(*) INTO @col_mii_serial FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'material_issue_items' AND COLUMN_NAME = 'inventory_serial_id';
SET @sql = IF(@col_mii_serial = 0,
  'ALTER TABLE `material_issue_items`
    ADD COLUMN `inventory_serial_id` int unsigned DEFAULT NULL AFTER `stock_id`,
    ADD KEY `idx_mii_serial` (`inventory_serial_id`)',
  'SET @dummy = 0');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
