-- ======================================================================
-- FashCognitive Global Size Management — Phase 6 (Downstream Module Integration)
-- Carries the global size_master link into the packing, warehouse, and
-- shipment modules so every size entry resolves against the catalog:
--   1. packing_order_lines     -> add size_master_id (garment size link)
--   2. shipment_manifest_items -> add size_master_id (manifest size link)
--   3. stock_reservations      -> add size_master_id (reservation size link)
--   4. shipment_allocations    -> add size_master_id (allocation size link)
--   5. inventory_serials       -> add size_master_id (serial size link)
-- Idempotent: safe to re-run (skips existing columns).
-- ======================================================================


-- ----------------------------------------------------------------------
-- 1. Packing order lines <-> size_master
-- ----------------------------------------------------------------------
SET @has_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'packing_order_lines' AND COLUMN_NAME = 'size_master_id');

SET @sql = IF(@has_col = 0,
  'ALTER TABLE `packing_order_lines` ADD COLUMN `size_master_id` int unsigned DEFAULT NULL AFTER `po_color_size_id`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_key = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'packing_order_lines' AND INDEX_NAME = 'size_master_id');

SET @sql = IF(@has_key = 0,
  'ALTER TABLE `packing_order_lines` ADD KEY `size_master_id` (`size_master_id`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ----------------------------------------------------------------------
-- 2. Shipment manifest items <-> size_master
-- ----------------------------------------------------------------------
SET @has_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'shipment_manifest_items' AND COLUMN_NAME = 'size_master_id');

SET @sql = IF(@has_col = 0,
  'ALTER TABLE `shipment_manifest_items` ADD COLUMN `size_master_id` int unsigned DEFAULT NULL AFTER `size`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_key = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'shipment_manifest_items' AND INDEX_NAME = 'size_master_id');

SET @sql = IF(@has_key = 0,
  'ALTER TABLE `shipment_manifest_items` ADD KEY `size_master_id` (`size_master_id`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ----------------------------------------------------------------------
-- 3. Stock reservations <-> size_master
-- ----------------------------------------------------------------------
SET @has_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'stock_reservations' AND COLUMN_NAME = 'size_master_id');

SET @sql = IF(@has_col = 0,
  'ALTER TABLE `stock_reservations` ADD COLUMN `size_master_id` int unsigned DEFAULT NULL AFTER `size`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_key = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'stock_reservations' AND INDEX_NAME = 'size_master_id');

SET @sql = IF(@has_key = 0,
  'ALTER TABLE `stock_reservations` ADD KEY `size_master_id` (`size_master_id`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ----------------------------------------------------------------------
-- 4. Shipment allocations <-> size_master
-- ----------------------------------------------------------------------
SET @has_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'shipment_allocations' AND COLUMN_NAME = 'size_master_id');

SET @sql = IF(@has_col = 0,
  'ALTER TABLE `shipment_allocations` ADD COLUMN `size_master_id` int unsigned DEFAULT NULL AFTER `size`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_key = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'shipment_allocations' AND INDEX_NAME = 'size_master_id');

SET @sql = IF(@has_key = 0,
  'ALTER TABLE `shipment_allocations` ADD KEY `size_master_id` (`size_master_id`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ----------------------------------------------------------------------
-- 5. Inventory serials <-> size_master
-- ----------------------------------------------------------------------
SET @has_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inventory_serials' AND COLUMN_NAME = 'size_master_id');

SET @sql = IF(@has_col = 0,
  'ALTER TABLE `inventory_serials` ADD COLUMN `size_master_id` int unsigned DEFAULT NULL AFTER `size`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_key = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inventory_serials' AND INDEX_NAME = 'size_master_id');

SET @sql = IF(@has_key = 0,
  'ALTER TABLE `inventory_serials` ADD KEY `size_master_id` (`size_master_id`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

