-- ============================================================
-- FashCognitive Apparel MES - Level 1/2
-- Phase 3: Routing Library (Reusable Master Routings)
-- ============================================================
-- A Routing (工艺路线) is a reusable, versioned master sequence of
-- operations that can be applied to any production order. Applying
-- a routing copies its steps into production_order_operations.

-- ============================================================
-- 1. Production Routing (Master Header)
-- ============================================================
CREATE TABLE IF NOT EXISTS `production_routing` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `routing_code` varchar(30) NOT NULL,
  `routing_name` varchar(150) NOT NULL,
  `description` text,
  `version` int unsigned NOT NULL DEFAULT 1,
  `status` enum('draft','active','obsolete') NOT NULL DEFAULT 'draft',
  `style_id` int unsigned DEFAULT NULL COMMENT 'Optional: default routing for a style',
  `unit_id` int unsigned DEFAULT NULL COMMENT 'Optional: default production unit (master_contractors.id)',
  `total_smv` decimal(10,2) DEFAULT NULL COMMENT 'Sum of step SMV',
  `total_hours_100` decimal(10,2) DEFAULT NULL COMMENT 'Hours required for 100 pcs (total_smv * 100 / 60)',
  `created_by` int unsigned DEFAULT NULL COMMENT 'users.id',
  `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_routing_code_company` (`company_id`,`routing_code`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`),
  KEY `unit_id` (`unit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 2. Production Routing Steps
-- ============================================================
CREATE TABLE IF NOT EXISTS `production_routing_steps` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `routing_id` int unsigned NOT NULL,
  `sequence` int unsigned NOT NULL DEFAULT 10,
  `operation_name` varchar(150) NOT NULL,
  `operation_type` enum('cutting','bundling','printing','embroidery','sewing','finishing','packing','quality','other') DEFAULT 'sewing',
  `work_center_id` int unsigned DEFAULT NULL COMMENT 'master_work_centers.id',
  `line_id` int unsigned DEFAULT NULL COMMENT 'Optional: default line (master_production_lines.id)',
  `machine_type` varchar(100) DEFAULT NULL COMMENT 'Overlock, Flatlock, Single Needle, etc.',
  `operators_required` int unsigned DEFAULT 1,
  `smv` decimal(8,2) DEFAULT 0 COMMENT 'Standard Minute Value',
  `sam` decimal(8,2) DEFAULT NULL COMMENT 'Standard Allowed Minutes (= SMV)',
  `lead_time_hours` decimal(6,2) DEFAULT NULL,
  `rework_pct` decimal(5,2) DEFAULT 0 COMMENT 'Expected % rework',
  `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 `routing_id` (`routing_id`),
  KEY `work_center_id` (`work_center_id`),
  KEY `line_id` (`line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 3. Traceability on production_order_operations
--    Links applied steps back to the routing library master.
--    Run once on existing databases.
-- ============================================================
ALTER TABLE `production_order_operations`
  ADD COLUMN `routing_id` int unsigned DEFAULT NULL COMMENT 'production_routing.id applied' AFTER `sequence`,
  ADD COLUMN `routing_step_id` int unsigned DEFAULT NULL COMMENT 'production_routing_steps.id source step' AFTER `routing_id`,
  ADD KEY `routing_id` (`routing_id`);
