-- ============================================================
-- FashCognitive Style Development + Product Costing Module
-- SAP Fashion Management inspired 360° Style Workspace
-- ============================================================


-- ============================================================
-- 1. Extend master_styles with full style workspace fields
-- ============================================================
ALTER TABLE `master_styles`
  ADD COLUMN `buyer_style_no` varchar(100) DEFAULT NULL AFTER `style_no`,
  ADD COLUMN `brand` varchar(100) DEFAULT NULL AFTER `buyer_style_no`,
  ADD COLUMN `division` varchar(100) DEFAULT NULL AFTER `brand`,
  ADD COLUMN `department` varchar(100) DEFAULT NULL AFTER `division`,
  ADD COLUMN `product_type` varchar(100) DEFAULT NULL AFTER `department`,
  ADD COLUMN `gender` enum('Mens','Womens','Kids','Unisex','Other') DEFAULT NULL AFTER `product_type`,
  ADD COLUMN `season` varchar(50) DEFAULT NULL AFTER `gender`,
  ADD COLUMN `collection` varchar(100) DEFAULT NULL AFTER `season`,
  ADD COLUMN `year` smallint DEFAULT NULL AFTER `collection`,
  ADD COLUMN `factory_id` int unsigned DEFAULT NULL AFTER `year`,
  ADD COLUMN `merchandiser` varchar(255) DEFAULT NULL AFTER `factory_id`,
  ADD COLUMN `product_developer` varchar(255) DEFAULT NULL AFTER `merchandiser`,
  ADD COLUMN `dev_status` enum('development','sampling','approved','production','archived') NOT NULL DEFAULT 'development' AFTER `status`,
  ADD COLUMN `version` varchar(20) DEFAULT 'V1' AFTER `dev_status`,
  ADD COLUMN `fob_target` decimal(12,2) DEFAULT NULL AFTER `version`,
  ADD COLUMN `margin_target` decimal(5,2) DEFAULT NULL AFTER `fob_target`,
  ADD COLUMN `fit` varchar(100) DEFAULT NULL AFTER `margin_target`,
  ADD COLUMN `sleeve` varchar(100) DEFAULT NULL AFTER `fit`,
  ADD COLUMN `construction` varchar(255) DEFAULT NULL AFTER `sleeve`,
  ADD COLUMN `wash` varchar(100) DEFAULT NULL AFTER `construction`,
  ADD COLUMN `print_type` varchar(100) DEFAULT NULL AFTER `wash`,
  ADD COLUMN `embroidery` varchar(255) DEFAULT NULL AFTER `print_type`,
  ADD COLUMN `front_image` varchar(255) DEFAULT NULL AFTER `embroidery`,
  ADD COLUMN `back_image` varchar(255) DEFAULT NULL AFTER `front_image`,
  ADD COLUMN `side_image` varchar(255) DEFAULT NULL AFTER `back_image`,
  ADD COLUMN `model_3d` varchar(255) DEFAULT NULL AFTER `side_image`,
  ADD COLUMN `total_smv` decimal(8,2) DEFAULT NULL AFTER `model_3d`,
  ADD COLUMN `total_material_cost` decimal(12,2) DEFAULT NULL AFTER `total_smv`,
  ADD COLUMN `total_mfg_cost` decimal(12,2) DEFAULT NULL AFTER `total_material_cost`,
  ADD COLUMN `total_overhead` decimal(12,2) DEFAULT NULL AFTER `total_mfg_cost`,
  ADD COLUMN `total_cost` decimal(12,2) DEFAULT NULL AFTER `total_overhead`,
  ADD COLUMN `fob_price` decimal(12,2) DEFAULT NULL AFTER `total_cost`,
  ADD COLUMN `currency_code` varchar(5) DEFAULT 'USD' AFTER `fob_price`,
  ADD COLUMN `approval_status` enum('draft','pending','approved','rejected') NOT NULL DEFAULT 'draft' AFTER `currency_code`,
  ADD COLUMN `approved_by` int unsigned DEFAULT NULL AFTER `approval_status`,
  ADD COLUMN `approved_at` timestamp NULL DEFAULT NULL AFTER `approved_by`,
  ADD KEY `factory_id` (`factory_id`),
  ADD KEY `dev_status` (`dev_status`);

-- ============================================================
-- 2. Style Measurement Points (per size)
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_measurements` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `point_name` varchar(100) NOT NULL COMMENT 'e.g. Chest, Body Length, Sleeve',
  `sequence` int unsigned NOT NULL DEFAULT 0,
  `tolerance` decimal(5,2) DEFAULT NULL,
  `spec_type` enum('buyer','factory','standard') DEFAULT 'buyer',
  `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;

-- ============================================================
-- 3. Style Measurement Values (per point per size)
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_measurement_values` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `measurement_id` int unsigned NOT NULL,
  `size_label` varchar(50) NOT NULL,
  `value` decimal(8,2) NOT NULL DEFAULT 0,
  `revision` int unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`),
  KEY `measurement_id` (`measurement_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 4. Style Colorways
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_colorways` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `color_name` varchar(100) NOT NULL,
  `pantone` varchar(50) DEFAULT NULL,
  `fabric` varchar(255) DEFAULT NULL,
  `artwork` varchar(255) DEFAULT NULL,
  `trim_color` varchar(100) DEFAULT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `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;

-- ============================================================
-- 5. Style Bill of Materials
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_bom` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `colorway_id` int unsigned DEFAULT NULL,
  `bom_type` enum('fabric','trim','accessory','packaging','other') NOT NULL DEFAULT 'fabric',
  `item_name` varchar(255) NOT NULL,
  `supplier` varchar(255) DEFAULT NULL,
  `gsm` decimal(8,2) DEFAULT NULL,
  `width` decimal(8,2) DEFAULT NULL COMMENT 'inches',
  `consumption` decimal(10,4) NOT NULL DEFAULT 0,
  `consumption_uom` varchar(20) DEFAULT 'Kg' COMMENT 'Kg, Mtr, Pcs, Yds',
  `rate` decimal(12,2) NOT NULL DEFAULT 0,
  `currency_code` varchar(5) DEFAULT 'USD',
  `wastage_pct` decimal(5,2) DEFAULT 0,
  `notes` text,
  `sequence` int unsigned NOT NULL DEFAULT 0,
  `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`),
  KEY `colorway_id` (`colorway_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 6. Style Consumption Calculator
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_consumption` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `marker_efficiency` decimal(5,2) DEFAULT 85.00 COMMENT 'percent',
  `fabric_width` decimal(8,2) DEFAULT NULL COMMENT 'inches',
  `gsm` decimal(8,2) DEFAULT NULL,
  `fabric_shrinkage` decimal(5,2) DEFAULT 0 COMMENT 'percent',
  `wastage_pct` decimal(5,2) DEFAULT 5 COMMENT 'percent',
  `relaxation_pct` decimal(5,2) DEFAULT 0 COMMENT 'percent',
  `fabric_repeat` decimal(8,2) DEFAULT NULL COMMENT 'inches',
  `matching_allowance` decimal(5,2) DEFAULT 0 COMMENT 'percent',
  `net_consumption` decimal(10,4) DEFAULT NULL,
  `final_consumption` decimal(10,4) DEFAULT NULL,
  `consumption_uom` varchar(20) DEFAULT 'Kg',
  `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;

-- ============================================================
-- 7. Style Operations / Routing (SMV)
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_operations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `operation_name` varchar(255) NOT NULL,
  `machine_type` varchar(100) DEFAULT NULL COMMENT 'Overlock, Flatlock, Single Needle, etc.',
  `smv` decimal(8,2) NOT NULL DEFAULT 0 COMMENT 'Standard Minute Value',
  `sam` decimal(8,2) DEFAULT NULL COMMENT 'Standard Allowed Minutes (= SMV)',
  `operation_type` enum('cutting','sewing','finishing','packing','quality','other') DEFAULT 'sewing',
  `difficulty` enum('easy','medium','hard') DEFAULT 'medium',
  `sequence` int unsigned NOT NULL DEFAULT 0,
  `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;

-- ============================================================
-- 8. Style Costing Header
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_costing` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `version` int unsigned NOT NULL DEFAULT 1,
  `material_cost` decimal(12,2) NOT NULL DEFAULT 0,
  `manufacturing_cost` decimal(12,2) NOT NULL DEFAULT 0,
  `overhead_cost` decimal(12,2) NOT NULL DEFAULT 0,
  `compliance_cost` decimal(12,2) NOT NULL DEFAULT 0,
  `packaging_cost` decimal(12,2) NOT NULL DEFAULT 0,
  `total_cost` decimal(12,2) NOT NULL DEFAULT 0,
  `margin_pct` decimal(5,2) DEFAULT 0,
  `fob_price` decimal(12,2) DEFAULT 0,
  `currency_code` varchar(5) DEFAULT 'USD',
  `status` enum('draft','final') NOT NULL DEFAULT 'draft',
  `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;

-- ============================================================
-- 9. Style Cost Components (detailed line items)
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_cost_components` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `costing_id` int unsigned NOT NULL,
  `category` enum('material','manufacturing','overhead','compliance','packaging') NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `cost` decimal(12,2) NOT NULL DEFAULT 0,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`),
  KEY `costing_id` (`costing_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 10. Style Packaging
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_packaging` (
  `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,
  `quantity` decimal(10,4) NOT NULL DEFAULT 1,
  `rate` decimal(12,2) NOT NULL DEFAULT 0,
  `uom` varchar(20) DEFAULT 'Pcs',
  `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;

-- ============================================================
-- 11. Style Compliance
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_compliance` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `category` enum('testing','audit','sustainability','certification','other') NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `cost` decimal(12,2) NOT NULL DEFAULT 0,
  `required` tinyint(1) NOT NULL DEFAULT 1,
  `status` enum('pending','in_progress','completed','not_required') NOT NULL DEFAULT 'pending',
  `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;

-- ============================================================
-- 12. Style Documents
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_documents` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `doc_type` enum('tech_pack','spec_sheet','image','3d_model','pdf','excel','other') NOT NULL DEFAULT 'other',
  `doc_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `uploaded_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 13. Style AI Analysis
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_ai_analysis` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `analysis_type` enum('cost_optimization','supplier_alternative','risk_assessment','profitability','consumption','other') NOT NULL,
  `summary` text,
  `recommendation` text,
  `potential_saving` decimal(12,2) DEFAULT NULL,
  `risk_level` enum('low','medium','high') DEFAULT 'low',
  `confidence` decimal(5,2) DEFAULT NULL COMMENT '0-100',
  `raw_data` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 14. Style Approvals
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_approvals` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `approval_level` int unsigned NOT NULL DEFAULT 1,
  `role_name` varchar(100) NOT NULL COMMENT 'Product Developer, Merchandiser, Costing Manager, etc.',
  `user_id` int unsigned DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `remarks` text,
  `acted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 15. Style Versions (version history)
-- ============================================================
CREATE TABLE IF NOT EXISTS `style_versions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `version_label` varchar(20) NOT NULL COMMENT 'V1, V2, V3...',
  `change_description` text,
  `changed_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

