-- ============================================================
-- New sub-entity tables for Style Management workspace
-- Sizes, Tech Pack, Purchase, Production, QA, Sustainability
-- Also adds designer column to master_styles
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

-- Add designer column to master_styles (ignore if already exists)
-- ALTER TABLE `master_styles` ADD COLUMN `designer` varchar(255) DEFAULT NULL AFTER `product_developer`;

-- Style Sizes (size matrix)
CREATE TABLE IF NOT EXISTS `style_sizes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `size_label` varchar(50) NOT NULL,
  `size_code` varchar(20) DEFAULT NULL COMMENT 'e.g. XS, S, M, L, XL',
  `chest` decimal(8,2) DEFAULT NULL,
  `waist` decimal(8,2) DEFAULT NULL,
  `hip` decimal(8,2) DEFAULT NULL,
  `length` decimal(8,2) DEFAULT NULL,
  `sleeve` decimal(8,2) DEFAULT NULL,
  `shoulder` decimal(8,2) DEFAULT NULL,
  `custom_measurements` json DEFAULT NULL COMMENT 'Additional measurement points as key-value pairs',
  `sku_suffix` varchar(50) DEFAULT NULL COMMENT 'e.g. BLK-M, WHT-L',
  `sequence` 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 `company_id` (`company_id`),
  KEY `style_id` (`style_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Style Tech Pack
CREATE TABLE IF NOT EXISTS `style_tech_pack` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `construction_details` text COMMENT 'Garment construction description',
  `stitch_type` varchar(100) DEFAULT NULL COMMENT 'e.g. 4-Thread Overlock, Flatlock, Coverstitch',
  `stitch_density` varchar(50) DEFAULT NULL COMMENT 'e.g. 10-12 SPI',
  `sewing_instructions` text COMMENT 'Step-by-step sewing instructions',
  `print_placement` text COMMENT 'Print/artwork placement details',
  `embroidery_details` text COMMENT 'Embroidery specifications',
  `wash_instructions` text COMMENT 'Washing/dyeing instructions',
  `care_instructions` text COMMENT 'Care label text',
  `trim_details` text COMMENT 'Labels, hangtags, polybag specs',
  `packing_instructions` text COMMENT 'Folding, tagging, packing method',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Style Purchase Planning
CREATE TABLE IF NOT EXISTS `style_purchase` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `item_type` enum('fabric','trim','accessory','packaging','other') NOT NULL DEFAULT 'fabric',
  `supplier` varchar(255) DEFAULT NULL,
  `required_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `required_uom` varchar(20) DEFAULT 'Kg',
  `warehouse_qty` decimal(10,2) DEFAULT 0 COMMENT 'Available in warehouse',
  `reserved_qty` decimal(10,2) DEFAULT 0 COMMENT 'Reserved for other orders',
  `purchase_qty` decimal(10,2) DEFAULT 0 COMMENT 'Qty to purchase = required - warehouse + reserved',
  `lead_time_days` int unsigned DEFAULT NULL,
  `unit_cost` decimal(12,2) DEFAULT NULL,
  `currency_code` varchar(5) DEFAULT 'USD',
  `po_status` enum('not_started','indented','ordered','partial_received','received') NOT NULL DEFAULT 'not_started',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Style Production Planning
CREATE TABLE IF NOT EXISTS `style_production` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `total_smv` decimal(8,2) DEFAULT NULL COMMENT 'Standard Minute Value',
  `total_sam` decimal(8,2) DEFAULT NULL COMMENT 'Standard Allowed Minutes',
  `operators` int unsigned DEFAULT NULL,
  `machines` int unsigned DEFAULT NULL,
  `line_efficiency` decimal(5,2) DEFAULT 65 COMMENT 'Line efficiency percent',
  `daily_capacity` int unsigned DEFAULT NULL COMMENT 'Units per day',
  `estimated_days` int unsigned DEFAULT NULL COMMENT 'Estimated production days',
  `planned_qty` int unsigned DEFAULT NULL,
  `actual_output` int unsigned DEFAULT 0,
  `factory_id` int unsigned DEFAULT NULL,
  `line_id` int unsigned DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` enum('planned','in_progress','completed','on_hold') NOT NULL DEFAULT 'planned',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Style QA / Quality Inspection
CREATE TABLE IF NOT EXISTS `style_qa` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `stage` enum('fabric_inspection','inline','midline','endline','final','packing','shipment') NOT NULL,
  `aql_level` decimal(3,1) DEFAULT 2.5,
  `sample_size` int unsigned DEFAULT NULL,
  `accepted_qty` int unsigned DEFAULT NULL,
  `rejected_qty` int unsigned DEFAULT NULL,
  `defect_list` json DEFAULT NULL COMMENT 'Array of {defect_name, count, severity}',
  `major_defects` int unsigned DEFAULT 0,
  `minor_defects` int unsigned DEFAULT 0,
  `critical_defects` int unsigned DEFAULT 0,
  `result` enum('pass','fail','pending') NOT NULL DEFAULT 'pending',
  `inspector` varchar(255) DEFAULT NULL,
  `inspection_date` date DEFAULT NULL,
  `remarks` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Style Sustainability & Certifications
CREATE TABLE IF NOT EXISTS `style_sustainability` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `certification_name` varchar(255) NOT NULL COMMENT 'OEKO-TEX, GRS, GOTS, BSCI, HIGG, ZDHC, etc.',
  `cert_type` enum('organic','recycled','chemical','social','environmental','other') NOT NULL DEFAULT 'other',
  `status` enum('required','applied','certified','expired','not_required') NOT NULL DEFAULT 'required',
  `certificate_no` varchar(100) DEFAULT NULL,
  `issued_by` varchar(255) DEFAULT NULL,
  `issued_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;
