-- ======================================================================
-- FashCognitive Global Size Management — Phase 2 (Buyer / Brand / Country mappings)
-- Adds: buyer_size_mapping, brand_size_mapping, country_size_mapping.
-- Idempotent: safe to re-run.
-- ======================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------------
-- 1. Buyer Size Mapping
-- Maps a buyer's own size labels to the internal series sizes.
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `buyer_size_mapping` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `buyer_id` int unsigned DEFAULT NULL,
  `buyer_name` varchar(255) DEFAULT NULL,
  `buyer_size` varchar(50) NOT NULL,
  `size_master_id` int unsigned DEFAULT NULL,
  `size_code` varchar(50) DEFAULT NULL,
  `display_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`),
  KEY `size_series_id` (`size_series_id`),
  KEY `buyer_id` (`buyer_id`),
  KEY `size_master_id` (`size_master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------
-- 2. Brand Size Mapping
-- Maps external brand size labels to the internal series sizes.
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `brand_size_mapping` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `brand_name` varchar(255) NOT NULL,
  `brand_size` varchar(50) NOT NULL,
  `size_master_id` int unsigned DEFAULT NULL,
  `size_code` varchar(50) DEFAULT NULL,
  `display_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`),
  KEY `size_series_id` (`size_series_id`),
  KEY `size_master_id` (`size_master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------
-- 3. Country Size Mapping
-- Cross-standard lookup: internal size -> size label per country/standard.
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `country_size_mapping` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `size_master_id` int unsigned DEFAULT NULL,
  `size_code` varchar(50) DEFAULT NULL,
  `country_code` varchar(50) NOT NULL,
  `country_size` varchar(50) NOT NULL,
  `display_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`),
  KEY `size_series_id` (`size_series_id`),
  KEY `size_master_id` (`size_master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;
