-- ======================================================================
-- FashCognitive Global Size Management — Phase 3 (Measurements & Grade Rules)
-- Adds: measurement_points (catalog), size_series_measurements (per-series
-- measurement grid), grade_rules (per-series per-size grading increments).
-- Idempotent: safe to re-run.
-- ======================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------------
-- 1. Measurement Points (global catalog)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `measurement_points` (
  `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,
  `unit` varchar(20) NOT NULL DEFAULT 'cm',
  `category_id` int unsigned DEFAULT NULL,
  `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_mp_company_code` (`company_id`, `code`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `measurement_points` (`id`, `company_id`, `code`, `name`, `description`, `unit`, `category_id`, `sort_order`, `status`) VALUES
(1, 1, 'CHEST', 'Chest Width', 'Width across chest 1 inch below armhole', 'cm', NULL, 1, 'active'),
(2, 1, 'BUST', 'Bust', 'Bust circumference at fullest point', 'cm', NULL, 2, 'active'),
(3, 1, 'WAIST', 'Waist', 'Waist circumference at natural waist', 'cm', NULL, 3, 'active'),
(4, 1, 'HIP', 'Hip', 'Hip circumference at fullest point', 'cm', NULL, 4, 'active'),
(5, 1, 'SHOULDER', 'Shoulder Width', 'Shoulder point to shoulder point', 'cm', NULL, 5, 'active'),
(6, 1, 'SLEEVE_LEN', 'Sleeve Length', 'Shoulder point to sleeve hem', 'cm', NULL, 6, 'active'),
(7, 1, 'BODY_LEN', 'Body Length', 'HPS to bottom hem', 'cm', NULL, 7, 'active'),
(8, 1, 'NECK', 'Neck Width', 'Neck circumference', 'cm', NULL, 8, 'active'),
(9, 1, 'ARMHOLE', 'Armhole', 'Armhole circumference', 'cm', NULL, 9, 'active'),
(10, 1, 'CROSS_BACK', 'Cross Back', 'Across back at mid armhole level', 'cm', NULL, 10, 'active'),
(11, 1, 'INSEAM', 'Inseam Length', 'Crotch to bottom hem', 'cm', NULL, 11, 'active'),
(12, 1, 'THIGH', 'Thigh Width', 'Thigh circumference 2 inches below crotch', 'cm', NULL, 12, 'active'),
(13, 1, 'KNEE', 'Knee Width', 'Knee circumference', 'cm', NULL, 13, 'active'),
(14, 1, 'HEM', 'Bottom Hem Width', 'Bottom hem circumference', 'cm', NULL, 14, 'active'),
(15, 1, 'CUFF', 'Cuff Opening', 'Cuff circumference', 'cm', NULL, 15, 'active'),
(16, 1, 'COLLAR_H', 'Collar Height', 'Collar band height', 'cm', NULL, 16, 'active'),
(17, 1, 'CENTER_FRONT', 'Center Front Length', 'Center front from neck to hem', 'cm', NULL, 17, 'active'),
(18, 1, 'BACK_LEN', 'Back Length', 'Center back from HPS to hem', 'cm', NULL, 18, 'active'),
(19, 1, 'RISE', 'Body Rise', 'Waist front to crotch', 'cm', NULL, 19, 'active'),
(20, 1, 'SWEEP', 'Sweep', 'Garment sweep at bottom', 'cm', NULL, 20, 'active');

-- ----------------------------------------------------------------------
-- 2. Size Series Measurements (per-series point x size values)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `size_series_measurements` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `measurement_point_id` int unsigned NOT NULL,
  `size_code` varchar(50) NOT NULL,
  `value` decimal(10,2) 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 `measurement_point_id` (`measurement_point_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------
-- 3. Grade Rules (per-series per-size grading increments)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `grade_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `measurement_point_id` int unsigned DEFAULT NULL,
  `rule_code` varchar(50) DEFAULT NULL,
  `rule_name` varchar(255) DEFAULT NULL,
  `grade_value` decimal(10,2) DEFAULT NULL,
  `unit` varchar(20) NOT NULL DEFAULT 'cm',
  `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 `measurement_point_id` (`measurement_point_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;
