-- ======================================================================
-- FashCognitive Global Size Management — Phase 1 (Foundation)
-- Adds: size_categories, size_standards, size_master tables,
-- extends master_size_series with category/standard/code fields, and
-- links master_size_series_sizes rows to size_master.
-- Idempotent: safe to re-run.
-- ======================================================================


-- ----------------------------------------------------------------------
-- 1. Size Categories
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `size_categories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `code` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `sort_order` int unsigned NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_cat_company_code` (`company_id`, `code`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `size_categories` (`id`, `company_id`, `code`, `name`, `description`, `sort_order`, `status`) VALUES
(1, 1, 'ADULT', 'Adult', 'Adult garments', 1, 'active'),
(2, 1, 'MENS', 'Mens', 'Men''s garments', 2, 'active'),
(3, 1, 'LADIES', 'Ladies', 'Women''s garments', 3, 'active'),
(4, 1, 'UNISEX', 'Unisex', 'Unisex garments', 4, 'active'),
(5, 1, 'BOYS', 'Boys', 'Boys clothing', 5, 'active'),
(6, 1, 'GIRLS', 'Girls', 'Girls clothing', 6, 'active'),
(7, 1, 'KIDS', 'Kids', 'Kids clothing', 7, 'active'),
(8, 1, 'BABY', 'Baby', 'Baby clothing', 8, 'active'),
(9, 1, 'INFANT', 'Infant', 'Infant clothing', 9, 'active'),
(10, 1, 'FOOTWEAR', 'Footwear', 'Shoes and footwear', 10, 'active'),
(11, 1, 'ACCESSORIES', 'Accessories', 'Accessories', 11, 'active'),
(12, 1, 'ONE_SIZE', 'One Size', 'One size fits all', 12, 'active'),
(13, 1, 'PLUS_SIZE', 'Plus Size', 'Plus size garments', 13, 'active'),
(14, 1, 'PET_APPAREL', 'Pet Apparel', 'Pet clothing', 14, 'active');

-- ----------------------------------------------------------------------
-- 2. Size Standards
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `size_standards` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `code` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `sort_order` int unsigned NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_std_company_code` (`company_id`, `code`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `size_standards` (`id`, `company_id`, `code`, `name`, `description`, `sort_order`, `status`) VALUES
(1, 1, 'US', 'US', 'US sizing', 1, 'active'),
(2, 1, 'UK', 'UK', 'UK sizing', 2, 'active'),
(3, 1, 'EU', 'EU', 'European sizing', 3, 'active'),
(4, 1, 'ASIA', 'Asia', 'Asian sizing', 4, 'active'),
(5, 1, 'JP', 'Japan', 'Japanese sizing', 5, 'active'),
(6, 1, 'CN', 'China', 'Chinese sizing', 6, 'active'),
(7, 1, 'AU', 'Australia', 'Australian sizing', 7, 'active'),
(8, 1, 'IN', 'India', 'Indian sizing', 8, 'active'),
(9, 1, 'INTL', 'International', 'International sizing', 9, 'active'),
(10, 1, 'CUSTOM', 'Custom', 'Custom sizing', 10, 'active');

-- ----------------------------------------------------------------------
-- 3. Size Master
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `size_master` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_code` varchar(50) NOT NULL,
  `size_name` varchar(255) NOT NULL,
  `short_name` varchar(50) DEFAULT NULL,
  `category_id` int unsigned DEFAULT NULL,
  `standard_id` int unsigned DEFAULT NULL,
  `display_sequence` int unsigned NOT NULL DEFAULT 0,
  `sort_order` int unsigned NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sz_dim` (`company_id`, `category_id`, `standard_id`, `size_code`),
  KEY `company_id` (`company_id`),
  KEY `category_id` (`category_id`),
  KEY `standard_id` (`standard_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Alpha sizes (Adult)
INSERT IGNORE INTO `size_master` (`id`, `company_id`, `size_code`, `size_name`, `short_name`, `category_id`, `standard_id`, `display_sequence`, `sort_order`, `status`) VALUES
(1, 1, 'XXS', 'Extra Extra Small', 'XXS', 1, NULL, 1, 1, 'active'),
(2, 1, 'XS', 'Extra Small', 'XS', 1, NULL, 2, 2, 'active'),
(3, 1, 'S', 'Small', 'S', 1, NULL, 3, 3, 'active'),
(4, 1, 'M', 'Medium', 'M', 1, NULL, 4, 4, 'active'),
(5, 1, 'L', 'Large', 'L', 1, NULL, 5, 5, 'active'),
(6, 1, 'XL', 'Extra Large', 'XL', 1, NULL, 6, 6, 'active'),
(7, 1, 'XXL', 'Double Extra Large', 'XXL', 1, NULL, 7, 7, 'active'),
(8, 1, '3XL', 'Triple Extra Large', '3XL', 1, NULL, 8, 8, 'active'),
(9, 1, '4XL', 'Quadruple Extra Large', '4XL', 1, NULL, 9, 9, 'active'),
(10, 1, '5XL', 'Quintuple Extra Large', '5XL', 1, NULL, 10, 10, 'active');

-- Kids
INSERT IGNORE INTO `size_master` (`id`, `company_id`, `size_code`, `size_name`, `short_name`, `category_id`, `standard_id`, `display_sequence`, `sort_order`, `status`) VALUES
(11, 1, '2Y', '2 Years', '2Y', 7, NULL, 11, 11, 'active'),
(12, 1, '3Y', '3 Years', '3Y', 7, NULL, 12, 12, 'active'),
(13, 1, '4Y', '4 Years', '4Y', 7, NULL, 13, 13, 'active'),
(14, 1, '5Y', '5 Years', '5Y', 7, NULL, 14, 14, 'active'),
(15, 1, '6Y', '6 Years', '6Y', 7, NULL, 15, 15, 'active'),
(16, 1, '7Y', '7 Years', '7Y', 7, NULL, 16, 16, 'active'),
(17, 1, '8Y', '8 Years', '8Y', 7, NULL, 17, 17, 'active'),
(18, 1, '9Y', '9 Years', '9Y', 7, NULL, 18, 18, 'active'),
(19, 1, '10Y', '10 Years', '10Y', 7, NULL, 19, 19, 'active'),
(20, 1, '12Y', '12 Years', '12Y', 7, NULL, 20, 20, 'active'),
(21, 1, '14Y', '14 Years', '14Y', 7, NULL, 21, 21, 'active'),
(22, 1, '16Y', '16 Years', '16Y', 7, NULL, 22, 22, 'active');

-- Baby
INSERT IGNORE INTO `size_master` (`id`, `company_id`, `size_code`, `size_name`, `short_name`, `category_id`, `standard_id`, `display_sequence`, `sort_order`, `status`) VALUES
(23, 1, 'NB', 'Newborn', 'NB', 8, NULL, 23, 23, 'active'),
(24, 1, '0-3M', '0-3 Months', '0-3M', 8, NULL, 24, 24, 'active'),
(25, 1, '3-6M', '3-6 Months', '3-6M', 8, NULL, 25, 25, 'active'),
(26, 1, '6-9M', '6-9 Months', '6-9M', 8, NULL, 26, 26, 'active'),
(27, 1, '9-12M', '9-12 Months', '9-12M', 8, NULL, 27, 27, 'active'),
(28, 1, '12-18M', '12-18 Months', '12-18M', 8, NULL, 28, 28, 'active'),
(29, 1, '18-24M', '18-24 Months', '18-24M', 8, NULL, 29, 29, 'active');

-- Jeans (waist, US)
INSERT IGNORE INTO `size_master` (`id`, `company_id`, `size_code`, `size_name`, `short_name`, `category_id`, `standard_id`, `display_sequence`, `sort_order`, `status`) VALUES
(30, 1, '26', 'Waist 26', '26', 1, 1, 30, 30, 'active'),
(31, 1, '28', 'Waist 28', '28', 1, 1, 31, 31, 'active'),
(32, 1, '30', 'Waist 30', '30', 1, 1, 32, 32, 'active'),
(33, 1, '32', 'Waist 32', '32', 1, 1, 33, 33, 'active'),
(34, 1, '34', 'Waist 34', '34', 1, 1, 34, 34, 'active'),
(35, 1, '36', 'Waist 36', '36', 1, 1, 35, 35, 'active'),
(36, 1, '38', 'Waist 38', '38', 1, 1, 36, 36, 'active'),
(37, 1, '40', 'Waist 40', '40', 1, 1, 37, 37, 'active'),
(38, 1, '42', 'Waist 42', '42', 1, 1, 38, 38, 'active'),
(39, 1, '44', 'Waist 44', '44', 1, 1, 39, 39, 'active'),
(40, 1, '46', 'Waist 46', '46', 1, 1, 40, 40, 'active');

-- Footwear (EU)
INSERT IGNORE INTO `size_master` (`id`, `company_id`, `size_code`, `size_name`, `short_name`, `category_id`, `standard_id`, `display_sequence`, `sort_order`, `status`) VALUES
(41, 1, '35', 'EU 35', '35', 10, 3, 41, 41, 'active'),
(42, 1, '36', 'EU 36', '36', 10, 3, 42, 42, 'active'),
(43, 1, '37', 'EU 37', '37', 10, 3, 43, 43, 'active'),
(44, 1, '38', 'EU 38', '38', 10, 3, 44, 44, 'active'),
(45, 1, '39', 'EU 39', '39', 10, 3, 45, 45, 'active'),
(46, 1, '40', 'EU 40', '40', 10, 3, 46, 46, 'active'),
(47, 1, '41', 'EU 41', '41', 10, 3, 47, 47, 'active'),
(48, 1, '42', 'EU 42', '42', 10, 3, 48, 48, 'active'),
(49, 1, '43', 'EU 43', '43', 10, 3, 49, 49, 'active'),
(50, 1, '44', 'EU 44', '44', 10, 3, 50, 50, 'active'),
(51, 1, '45', 'EU 45', '45', 10, 3, 51, 51, 'active'),
(52, 1, '46', 'EU 46', '46', 10, 3, 52, 52, 'active'),
(53, 1, '47', 'EU 47', '47', 10, 3, 53, 53, 'active'),
(54, 1, '48', 'EU 48', '48', 10, 3, 54, 54, 'active');

-- One Size
INSERT IGNORE INTO `size_master` (`id`, `company_id`, `size_code`, `size_name`, `short_name`, `category_id`, `standard_id`, `display_sequence`, `sort_order`, `status`) VALUES
(55, 1, 'OS', 'One Size', 'OS', 12, NULL, 55, 55, 'active');

-- ----------------------------------------------------------------------
-- 4. Extend master_size_series (series_code, category, standard, short_name)
-- ----------------------------------------------------------------------
SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'master_size_series' AND COLUMN_NAME = 'series_code');
SET @sql := IF(@col = 0,
  'ALTER TABLE master_size_series ADD COLUMN series_code varchar(50) DEFAULT NULL AFTER name',
  'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'master_size_series' AND COLUMN_NAME = 'category_id');
SET @sql := IF(@col = 0,
  'ALTER TABLE master_size_series ADD COLUMN category_id int unsigned DEFAULT NULL AFTER series_code',
  'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'master_size_series' AND COLUMN_NAME = 'standard_id');
SET @sql := IF(@col = 0,
  'ALTER TABLE master_size_series ADD COLUMN standard_id int unsigned DEFAULT NULL AFTER category_id',
  'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'master_size_series' AND COLUMN_NAME = 'short_name');
SET @sql := IF(@col = 0,
  'ALTER TABLE master_size_series ADD COLUMN short_name varchar(50) DEFAULT NULL AFTER standard_id',
  'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- Backfill series codes for existing rows
UPDATE master_size_series SET series_code = CONCAT('SS-', LPAD(id, 3, '0'))
  WHERE series_code IS NULL OR series_code = '';

-- ----------------------------------------------------------------------
-- 5. Link series sizes to size_master
-- ----------------------------------------------------------------------
SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'master_size_series_sizes' AND COLUMN_NAME = 'size_master_id');
SET @sql := IF(@col = 0,
  'ALTER TABLE master_size_series_sizes ADD COLUMN size_master_id int unsigned DEFAULT NULL AFTER size_value',
  'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- Backfill links where an exact size_master code exists (same company)
UPDATE master_size_series_sizes mss
  LEFT JOIN master_size_series ms ON ms.id = mss.size_series_id
  JOIN size_master sm ON sm.company_id = ms.company_id AND sm.size_code = mss.size_value
  SET mss.size_master_id = sm.id
  WHERE mss.size_master_id IS NULL;

