-- ============================================================
-- FashCognitive Manufacturing Execution Module
-- Production Orders (Discrete) + Process Orders (Batch)
-- ============================================================

-- ============================================================
-- 1. Production Lines (Master)
-- ============================================================
CREATE TABLE IF NOT EXISTS `master_production_lines` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `factory_id` int unsigned DEFAULT NULL,
  `line_name` varchar(100) NOT NULL,
  `line_code` varchar(20) DEFAULT NULL,
  `capacity_per_day` int unsigned DEFAULT NULL COMMENT 'Units per day',
  `section` enum('cutting','sewing','finishing','packing','printing','embroidery','washing','dyeing','knitting','other') DEFAULT 'sewing',
  `status` enum('active','inactive','maintenance') NOT NULL DEFAULT 'active',
  `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 `factory_id` (`factory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 2. Work Centers (Machines / Tables)
-- ============================================================
CREATE TABLE IF NOT EXISTS `master_work_centers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `factory_id` int unsigned DEFAULT NULL,
  `line_id` int unsigned DEFAULT NULL,
  `work_center_name` varchar(150) NOT NULL,
  `work_center_code` varchar(30) DEFAULT NULL,
  `machine_type` varchar(100) DEFAULT NULL COMMENT 'Overlock, Flatlock, Single Needle, Jet Dyeing, etc.',
  `category` enum('sewing','cutting','finishing','packing','printing','embroidery','dyeing','washing','knitting','compacting','other') DEFAULT 'sewing',
  `hourly_rate` decimal(10,2) DEFAULT 0,
  `capacity_per_hour` decimal(8,2) DEFAULT NULL,
  `status` enum('available','in_use','maintenance','idle') NOT NULL DEFAULT 'available',
  `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 `factory_id` (`factory_id`),
  KEY `line_id` (`line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 3. Production Orders (Discrete Manufacturing)
-- ============================================================
CREATE TABLE IF NOT EXISTS `production_orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `prod_order_no` varchar(30) NOT NULL,
  `order_type` enum('production','subcontract','rework','sample','maintenance') NOT NULL DEFAULT 'production',
  `buyer_po_id` int unsigned DEFAULT NULL COMMENT 'master_buyer_pos.id',
  `style_id` int unsigned DEFAULT NULL COMMENT 'master_styles.id',
  `color` varchar(50) DEFAULT NULL,
  `size` varchar(20) DEFAULT NULL,
  `description` text,
  `planned_qty` int unsigned NOT NULL DEFAULT 0,
  `completed_qty` int unsigned NOT NULL DEFAULT 0,
  `rejected_qty` int unsigned NOT NULL DEFAULT 0,
  `scrapped_qty` int unsigned NOT NULL DEFAULT 0,
  `uom` varchar(20) DEFAULT 'PCS',
  `factory_id` int unsigned DEFAULT NULL,
  `line_id` int unsigned DEFAULT NULL,
  `work_center_id` int unsigned DEFAULT NULL,
  `planned_start_date` date DEFAULT NULL,
  `planned_end_date` date DEFAULT NULL,
  `actual_start_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `priority` enum('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `status` enum('draft','planned','released','in_progress','paused','completed','closed','cancelled') NOT NULL DEFAULT 'draft',
  `completion_pct` decimal(5,2) NOT NULL DEFAULT 0,
  `total_smv` decimal(10,2) DEFAULT NULL,
  `total_sam` decimal(10,2) DEFAULT NULL,
  `estimated_cost` decimal(12,2) DEFAULT 0,
  `actual_cost` decimal(12,2) DEFAULT 0,
  `notes` text,
  `created_by` int unsigned DEFAULT NULL,
  `approved_by` int unsigned DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `released_at` datetime DEFAULT NULL,
  `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 `prod_order_no` (`prod_order_no`, `company_id`),
  KEY `company_id` (`company_id`),
  KEY `buyer_po_id` (`buyer_po_id`),
  KEY `style_id` (`style_id`),
  KEY `factory_id` (`factory_id`),
  KEY `line_id` (`line_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 4. Production Order Operations (Routing Steps)
-- ============================================================
CREATE TABLE IF NOT EXISTS `production_order_operations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `prod_order_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,
  `machine_type` varchar(100) DEFAULT NULL,
  `planned_qty` int unsigned NOT NULL DEFAULT 0,
  `completed_qty` int unsigned NOT NULL DEFAULT 0,
  `rejected_qty` int unsigned NOT NULL DEFAULT 0,
  `operators_required` int unsigned DEFAULT 1,
  `smv` decimal(8,2) DEFAULT 0,
  `sam` decimal(8,2) DEFAULT NULL,
  `planned_hours` decimal(6,2) DEFAULT NULL,
  `actual_hours` decimal(6,2) DEFAULT NULL,
  `planned_start_date` date DEFAULT NULL,
  `planned_end_date` date DEFAULT NULL,
  `actual_start_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `status` enum('pending','in_progress','completed','on_hold','skipped') 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 `prod_order_id` (`prod_order_id`),
  KEY `work_center_id` (`work_center_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 5. Production Order Materials (BOM per Order)
-- ============================================================
CREATE TABLE IF NOT EXISTS `production_order_materials` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `prod_order_id` int unsigned NOT NULL,
  `item_id` int unsigned DEFAULT NULL COMMENT 'master_items.id',
  `material_name` varchar(255) NOT NULL,
  `material_type` enum('fabric','trim','accessory','packaging','chemical','other') DEFAULT 'fabric',
  `color` varchar(50) DEFAULT NULL,
  `size` varchar(20) DEFAULT NULL,
  `uom` varchar(20) DEFAULT 'Kg',
  `required_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `issued_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `consumed_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `returned_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `wastage_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `rate` decimal(12,2) DEFAULT 0,
  `total_cost` decimal(12,2) DEFAULT 0,
  `warehouse_id` int unsigned DEFAULT NULL,
  `batch_no` varchar(50) DEFAULT NULL,
  `status` enum('planned','partial_issued','fully_issued','consumed') 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 `prod_order_id` (`prod_order_id`),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 6. Production Order Bundles (QR/Barcode Tracking)
-- ============================================================
CREATE TABLE IF NOT EXISTS `production_order_bundles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `prod_order_id` int unsigned NOT NULL,
  `bundle_no` varchar(50) NOT NULL,
  `qr_code` varchar(255) DEFAULT NULL,
  `barcode` varchar(100) DEFAULT NULL,
  `color` varchar(50) DEFAULT NULL,
  `size` varchar(20) DEFAULT NULL,
  `quantity` int unsigned NOT NULL DEFAULT 0,
  `completed_qty` int unsigned NOT NULL DEFAULT 0,
  `rejected_qty` int unsigned NOT NULL DEFAULT 0,
  `current_operation_id` int unsigned DEFAULT NULL COMMENT 'production_order_operations.id',
  `current_station` varchar(100) DEFAULT NULL,
  `status` enum('created','in_process','at_qc','completed','rejected','packed') NOT NULL DEFAULT 'created',
  `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 `bundle_no` (`bundle_no`, `company_id`),
  KEY `company_id` (`company_id`),
  KEY `prod_order_id` (`prod_order_id`),
  KEY `current_operation_id` (`current_operation_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 7. Production Order QC (Quality Checkpoints)
-- ============================================================
CREATE TABLE IF NOT EXISTS `production_order_qc` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `prod_order_id` int unsigned NOT NULL,
  `operation_id` int unsigned DEFAULT NULL COMMENT 'production_order_operations.id',
  `bundle_id` int unsigned DEFAULT NULL COMMENT 'production_order_bundles.id',
  `inspection_type` enum('inline','midline','endline','final','random') NOT NULL DEFAULT 'inline',
  `inspector_name` varchar(150) DEFAULT NULL,
  `sample_size` int unsigned DEFAULT 0,
  `passed_qty` int unsigned DEFAULT 0,
  `rejected_qty` int unsigned DEFAULT 0,
  `defects` json DEFAULT NULL COMMENT '[{defect, count, severity}]',
  `result` enum('pass','fail','pending') NOT NULL DEFAULT 'pending',
  `remarks` text,
  `inspected_at` datetime DEFAULT NULL,
  `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 `prod_order_id` (`prod_order_id`),
  KEY `operation_id` (`operation_id`),
  KEY `bundle_id` (`bundle_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 8. Production Order WIP (Work In Progress Snapshot)
-- ============================================================
CREATE TABLE IF NOT EXISTS `production_order_wip` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `prod_order_id` int unsigned NOT NULL,
  `operation_id` int unsigned DEFAULT NULL,
  `snapshot_date` date NOT NULL,
  `qty_at_station` int unsigned NOT NULL DEFAULT 0,
  `qty_completed` int unsigned NOT NULL DEFAULT 0,
  `qty_rejected` int unsigned NOT NULL DEFAULT 0,
  `wait_time_hours` decimal(6,2) DEFAULT 0,
  `efficiency_pct` decimal(5,2) DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `prod_order_id` (`prod_order_id`),
  KEY `snapshot_date` (`snapshot_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 9. Production Order Labor
-- ============================================================
CREATE TABLE IF NOT EXISTS `production_order_labor` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `prod_order_id` int unsigned NOT NULL,
  `operation_id` int unsigned DEFAULT NULL,
  `worker_name` varchar(150) DEFAULT NULL,
  `worker_id` varchar(50) DEFAULT NULL,
  `role` enum('operator','helper','supervisor','qc') DEFAULT 'operator',
  `shift` enum('day','night','general') DEFAULT 'day',
  `hours_worked` decimal(6,2) DEFAULT 0,
  `pieces_produced` int unsigned DEFAULT 0,
  `rate_per_hour` decimal(10,2) DEFAULT 0,
  `total_cost` decimal(10,2) DEFAULT 0,
  `date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `prod_order_id` (`prod_order_id`),
  KEY `operation_id` (`operation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 10. Production Order Machine Logs
-- ============================================================
CREATE TABLE IF NOT EXISTS `production_order_machine_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `prod_order_id` int unsigned NOT NULL,
  `operation_id` int unsigned DEFAULT NULL,
  `work_center_id` int unsigned DEFAULT NULL,
  `machine_status` enum('running','idle','breakdown','maintenance') NOT NULL DEFAULT 'running',
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `runtime_minutes` int unsigned DEFAULT 0,
  `downtime_minutes` int unsigned DEFAULT 0,
  `downtime_reason` varchar(255) DEFAULT NULL,
  `operator_name` varchar(150) DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `prod_order_id` (`prod_order_id`),
  KEY `work_center_id` (`work_center_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- PROCESS ORDERS (Batch / Process Manufacturing)
-- ============================================================

-- ============================================================
-- 11. Process Orders
-- ============================================================
CREATE TABLE IF NOT EXISTS `process_orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `process_order_no` varchar(30) NOT NULL,
  `order_type` enum('dyeing','washing','compacting','knitting','finishing','heat_setting','mercerizing','coating','subcontract','rework','other') NOT NULL DEFAULT 'dyeing',
  `buyer_po_id` int unsigned DEFAULT NULL,
  `style_id` int unsigned DEFAULT NULL,
  `material_name` varchar(255) DEFAULT NULL COMMENT 'e.g. Cotton Jersey, Polyester Twill',
  `fabric_type` varchar(100) DEFAULT NULL,
  `batch_no` varchar(50) DEFAULT NULL,
  `planned_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `completed_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `rejected_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `uom` varchar(20) DEFAULT 'Kg',
  `color` varchar(50) DEFAULT NULL,
  `shade` varchar(50) DEFAULT NULL,
  `recipe_id` int unsigned DEFAULT NULL,
  `machine_id` int unsigned DEFAULT NULL COMMENT 'master_work_centers.id',
  `factory_id` int unsigned DEFAULT NULL,
  `planned_start_date` date DEFAULT NULL,
  `planned_end_date` date DEFAULT NULL,
  `actual_start_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `priority` enum('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `status` enum('draft','planned','released','in_progress','paused','completed','closed','cancelled') NOT NULL DEFAULT 'draft',
  `completion_pct` decimal(5,2) NOT NULL DEFAULT 0,
  `estimated_cost` decimal(12,2) DEFAULT 0,
  `actual_cost` decimal(12,2) DEFAULT 0,
  `shrinkage_pct` decimal(5,2) DEFAULT 0,
  `weight_loss_pct` decimal(5,2) DEFAULT 0,
  `ph_level` decimal(4,1) DEFAULT NULL,
  `notes` text,
  `created_by` int unsigned DEFAULT NULL,
  `approved_by` int unsigned DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `released_at` datetime DEFAULT NULL,
  `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 `process_order_no` (`process_order_no`, `company_id`),
  KEY `company_id` (`company_id`),
  KEY `buyer_po_id` (`buyer_po_id`),
  KEY `style_id` (`style_id`),
  KEY `machine_id` (`machine_id`),
  KEY `factory_id` (`factory_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 12. Process Order Recipes (Chemical/Material Formulation)
-- ============================================================
CREATE TABLE IF NOT EXISTS `process_order_recipes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `process_order_id` int unsigned NOT NULL,
  `recipe_name` varchar(255) DEFAULT NULL,
  `material_name` varchar(255) NOT NULL,
  `material_type` enum('chemical','dye','water','salt','softener','agent','auxiliary','other') DEFAULT 'chemical',
  `concentration` decimal(8,2) DEFAULT NULL COMMENT 'g/L or %',
  `required_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `actual_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `uom` varchar(20) DEFAULT 'Kg',
  `rate` decimal(12,2) DEFAULT 0,
  `total_cost` decimal(12,2) DEFAULT 0,
  `sequence` int unsigned NOT NULL DEFAULT 0,
  `addition_time_min` int unsigned DEFAULT NULL COMMENT 'Minutes from start',
  `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 `process_order_id` (`process_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 13. Process Order Operations (Process Steps)
-- ============================================================
CREATE TABLE IF NOT EXISTS `process_order_operations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `process_order_id` int unsigned NOT NULL,
  `sequence` int unsigned NOT NULL DEFAULT 10,
  `operation_name` varchar(150) NOT NULL,
  `machine_id` int unsigned DEFAULT NULL COMMENT 'master_work_centers.id',
  `machine_name` varchar(150) DEFAULT NULL,
  `target_temp_c` decimal(5,1) DEFAULT NULL COMMENT 'Target temperature in Celsius',
  `actual_temp_c` decimal(5,1) DEFAULT NULL,
  `target_duration_min` int unsigned DEFAULT NULL COMMENT 'Duration in minutes',
  `actual_duration_min` int unsigned DEFAULT NULL,
  `target_ph` decimal(4,1) DEFAULT NULL,
  `actual_ph` decimal(4,1) DEFAULT NULL,
  `speed_rpm` decimal(8,2) DEFAULT NULL,
  `liquor_ratio` varchar(20) DEFAULT NULL COMMENT 'e.g. 1:8',
  `planned_start_date` date DEFAULT NULL,
  `planned_end_date` date DEFAULT NULL,
  `actual_start_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `status` enum('pending','in_progress','completed','on_hold','skipped') NOT NULL DEFAULT 'pending',
  `operator_name` varchar(150) 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 `process_order_id` (`process_order_id`),
  KEY `machine_id` (`machine_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 14. Process Order Batches (Batch Tracking)
-- ============================================================
CREATE TABLE IF NOT EXISTS `process_order_batches` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `process_order_id` int unsigned NOT NULL,
  `batch_no` varchar(50) NOT NULL,
  `input_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `output_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `rejected_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `shrinkage_pct` decimal(5,2) DEFAULT 0,
  `status` enum('created','in_process','completed','rejected') NOT NULL DEFAULT 'created',
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime 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`),
  UNIQUE KEY `batch_no` (`batch_no`, `company_id`),
  KEY `company_id` (`company_id`),
  KEY `process_order_id` (`process_order_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 15. Process Order Consumption (Actual Material Usage)
-- ============================================================
CREATE TABLE IF NOT EXISTS `process_order_consumption` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `process_order_id` int unsigned NOT NULL,
  `batch_id` int unsigned DEFAULT NULL,
  `recipe_id` int unsigned DEFAULT NULL,
  `material_name` varchar(255) NOT NULL,
  `planned_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `actual_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `uom` varchar(20) DEFAULT 'Kg',
  `warehouse_id` int unsigned DEFAULT NULL,
  `batch_no` varchar(50) DEFAULT NULL,
  `issued_at` datetime DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `process_order_id` (`process_order_id`),
  KEY `batch_id` (`batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 16. Process Order Output (Yield Tracking)
-- ============================================================
CREATE TABLE IF NOT EXISTS `process_order_output` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `process_order_id` int unsigned NOT NULL,
  `batch_id` int unsigned DEFAULT NULL,
  `output_qty` decimal(10,2) NOT NULL DEFAULT 0,
  `output_uom` varchar(20) DEFAULT 'Kg',
  `quality_grade` enum('a','b','c','rejected') DEFAULT 'a',
  `shrinkage_pct` decimal(5,2) DEFAULT 0,
  `color_fastness` varchar(50) DEFAULT NULL,
  `ph_value` decimal(4,1) DEFAULT NULL,
  `gsm_after` decimal(8,2) DEFAULT NULL,
  `destination_warehouse_id` int unsigned DEFAULT NULL,
  `received_at` datetime DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `process_order_id` (`process_order_id`),
  KEY `batch_id` (`batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 17. Process Order Quality (Quality Parameters)
-- ============================================================
CREATE TABLE IF NOT EXISTS `process_order_quality` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `process_order_id` int unsigned NOT NULL,
  `batch_id` int unsigned DEFAULT NULL,
  `inspection_type` enum('in_process','final','random') NOT NULL DEFAULT 'final',
  `inspector_name` varchar(150) DEFAULT NULL,
  `color_shade` varchar(50) DEFAULT NULL,
  `color_fastness_wash` varchar(20) DEFAULT NULL,
  `color_fastness_rub` varchar(20) DEFAULT NULL,
  `color_fastness_light` varchar(20) DEFAULT NULL,
  `shrinkage_pct` decimal(5,2) DEFAULT NULL,
  `gsm` decimal(8,2) DEFAULT NULL,
  `ph_value` decimal(4,1) DEFAULT NULL,
  `strength_n` decimal(8,2) DEFAULT NULL COMMENT 'Bursting strength in Newtons',
  `hand_feel` varchar(50) DEFAULT NULL,
  `pilling_rating` varchar(20) DEFAULT NULL,
  `result` enum('pass','conditional','fail','pending') NOT NULL DEFAULT 'pending',
  `remarks` text,
  `inspected_at` datetime DEFAULT NULL,
  `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 `process_order_id` (`process_order_id`),
  KEY `batch_id` (`batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 18. Process Order Machine Logs
-- ============================================================
CREATE TABLE IF NOT EXISTS `process_order_machine_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `process_order_id` int unsigned NOT NULL,
  `operation_id` int unsigned DEFAULT NULL,
  `machine_id` int unsigned DEFAULT NULL,
  `machine_status` enum('running','idle','breakdown','maintenance','cleaning') NOT NULL DEFAULT 'running',
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `runtime_minutes` int unsigned DEFAULT 0,
  `downtime_minutes` int unsigned DEFAULT 0,
  `downtime_reason` varchar(255) DEFAULT NULL,
  `temperature_c` decimal(5,1) DEFAULT NULL,
  `pressure_bar` decimal(6,2) DEFAULT NULL,
  `operator_name` varchar(150) DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `process_order_id` (`process_order_id`),
  KEY `machine_id` (`machine_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 19. Manufacturing Orders (Unified View - Virtual/Reference Table)
-- The app uses production_orders + process_orders as backends
-- This table is optional for unified tracking
-- ============================================================
CREATE TABLE IF NOT EXISTS `manufacturing_orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `mfg_order_no` varchar(30) NOT NULL,
  `order_type` enum('production','process','subcontract','rework','sample','maintenance') NOT NULL,
  `production_order_id` int unsigned DEFAULT NULL,
  `process_order_id` int unsigned DEFAULT NULL,
  `buyer_po_id` int unsigned DEFAULT NULL,
  `style_id` int unsigned DEFAULT NULL,
  `status` enum('draft','planned','released','in_progress','paused','completed','closed','cancelled') NOT NULL DEFAULT 'draft',
  `priority` enum('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `planned_start_date` date DEFAULT NULL,
  `planned_end_date` date DEFAULT NULL,
  `completion_pct` decimal(5,2) 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`),
  UNIQUE KEY `mfg_order_no` (`mfg_order_no`, `company_id`),
  KEY `company_id` (`company_id`),
  KEY `production_order_id` (`production_order_id`),
  KEY `process_order_id` (`process_order_id`),
  KEY `buyer_po_id` (`buyer_po_id`),
  KEY `style_id` (`style_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
