-- ======================================================================
-- FashCognitive Global Size Management — Phase 5 (Cross-Module Integration)
-- Links the size series master into the Style Master and Buyer PO modules:
--   1. master_styles            -> add size_series_id (style 1..1 series)
--   2. master_buyer_po_color_sizes -> add size_master_id (validated size link)
-- Idempotent: safe to re-run (skips existing columns).
-- ======================================================================


-- ----------------------------------------------------------------------
-- 1. Style Master <-> Size Series (assign a series to a style)
-- ----------------------------------------------------------------------
SET @has_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'master_styles' AND COLUMN_NAME = 'size_series_id');

SET @sql = IF(@has_col = 0,
  'ALTER TABLE `master_styles` ADD COLUMN `size_series_id` int unsigned DEFAULT NULL AFTER `size_range`',
  '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 = 'master_styles' AND INDEX_NAME = 'size_series_id');

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

-- ----------------------------------------------------------------------
-- 2. Buyer PO Color-Sizes <-> size_master (validated size link)
-- ----------------------------------------------------------------------
SET @has_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'master_buyer_po_color_sizes' AND COLUMN_NAME = 'size_master_id');

SET @sql = IF(@has_col = 0,
  'ALTER TABLE `master_buyer_po_color_sizes` 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 = 'master_buyer_po_color_sizes' AND INDEX_NAME = 'size_master_id');

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

