-- ============================================================
-- FASHCOGNITIVE PACKING MANAGEMENT SYSTEM
-- Migration: migration_packing.sql
-- ============================================================

-- 1. PACKING INSTRUCTIONS (per buyer/style)
CREATE TABLE IF NOT EXISTS `packing_instructions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `buyer_id` int unsigned DEFAULT NULL,
  `style_id` int unsigned DEFAULT NULL,
  `instruction_name` varchar(255) NOT NULL,
  `fold_type` varchar(100) DEFAULT NULL,
  `garment_orientation` varchar(100) DEFAULT NULL,
  `polybag_type` varchar(100) DEFAULT NULL,
  `polybag_size` varchar(50) DEFAULT NULL,
  `sticker_position` varchar(100) DEFAULT NULL,
  `barcode_position` varchar(100) DEFAULT NULL,
  `size_sticker_required` tinyint(1) NOT NULL DEFAULT 0,
  `hanger_required` tinyint(1) NOT NULL DEFAULT 0,
  `hanger_type` varchar(100) DEFAULT NULL,
  `carton_type` varchar(100) DEFAULT NULL DEFAULT '5 Ply Export',
  `carton_weight_max_kg` decimal(6,2) DEFAULT 18.00,
  `carton_length_cm` decimal(6,1) DEFAULT NULL,
  `carton_width_cm` decimal(6,1) DEFAULT NULL,
  `carton_height_cm` decimal(6,1) DEFAULT NULL,
  `pieces_per_carton` int unsigned DEFAULT NULL,
  `carton_printing` text DEFAULT NULL,
  `shipping_marks` text DEFAULT NULL,
  `special_instructions` text DEFAULT NULL,
  `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 `idx_company` (`company_id`),
  KEY `idx_buyer` (`buyer_id`),
  KEY `idx_style` (`style_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. PACKING ORDERS (generated from Buyer PO)
CREATE TABLE IF NOT EXISTS `packing_orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_order_code` varchar(50) NOT NULL,
  `buyer_po_id` int unsigned NOT NULL,
  `buyer_id` int unsigned DEFAULT NULL,
  `style_id` int unsigned DEFAULT NULL,
  `factory_id` int unsigned DEFAULT NULL,
  `packing_instruction_id` int unsigned DEFAULT NULL,
  `total_qty` int unsigned NOT NULL DEFAULT 0,
  `total_cartons_planned` int unsigned NOT NULL DEFAULT 0,
  `total_cartons_packed` int unsigned NOT NULL DEFAULT 0,
  `total_pieces_packed` int unsigned NOT NULL DEFAULT 0,
  `pieces_per_carton` int unsigned DEFAULT 50,
  `destination` varchar(255) DEFAULT NULL,
  `shipment_date` date DEFAULT NULL,
  `etd` date DEFAULT NULL,
  `eta` date DEFAULT NULL,
  `shipment_mode` varchar(50) DEFAULT NULL,
  `status` enum('pending','in_progress','packing_complete','palletizing','container_loading','verified','shipped') NOT NULL DEFAULT 'pending',
  `packing_started_at` timestamp NULL DEFAULT NULL,
  `packing_completed_at` timestamp NULL DEFAULT NULL,
  `packing_accuracy` decimal(5,2) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int unsigned 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 `uk_packing_code` (`packing_order_code`),
  KEY `idx_company` (`company_id`),
  KEY `idx_buyer_po` (`buyer_po_id`),
  KEY `idx_buyer` (`buyer_id`),
  KEY `idx_style` (`style_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. PACKING ORDER LINES (color/size breakdown)
CREATE TABLE IF NOT EXISTS `packing_order_lines` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `packing_order_id` int unsigned NOT NULL,
  `color` varchar(100) NOT NULL,
  `size` varchar(100) NOT NULL,
  `quantity` int unsigned NOT NULL DEFAULT 0,
  `quantity_packed` int unsigned NOT NULL DEFAULT 0,
  `quantity_remaining` int unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_packing_order` (`packing_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. PACKING STATIONS (physical stations in factory)
CREATE TABLE IF NOT EXISTS `packing_stations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `station_code` varchar(50) NOT NULL,
  `station_name` varchar(255) NOT NULL,
  `factory_id` int unsigned DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `has_scale` tinyint(1) NOT NULL DEFAULT 0,
  `has_camera` tinyint(1) NOT NULL DEFAULT 0,
  `has_printer` tinyint(1) NOT NULL DEFAULT 1,
  `has_scanner` tinyint(1) NOT NULL DEFAULT 1,
  `status` enum('active','inactive','maintenance') 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_station_code` (`station_code`),
  KEY `idx_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. PACKING SESSIONS (active packing runs)
CREATE TABLE IF NOT EXISTS `packing_sessions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `session_code` varchar(50) NOT NULL,
  `packing_order_id` int unsigned NOT NULL,
  `station_id` int unsigned DEFAULT NULL,
  `operator_name` varchar(255) DEFAULT NULL,
  `operator_id` int unsigned DEFAULT NULL,
  `carton_number` int unsigned NOT NULL DEFAULT 1,
  `carton_code` varchar(50) DEFAULT NULL,
  `pieces_packed` int unsigned NOT NULL DEFAULT 0,
  `target_pieces` int unsigned NOT NULL DEFAULT 0,
  `session_status` enum('active','paused','completed','cancelled') NOT NULL DEFAULT 'active',
  `started_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` timestamp NULL DEFAULT NULL,
  `notes` text 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 `uk_session_code` (`session_code`),
  KEY `idx_packing_order` (`packing_order_id`),
  KEY `idx_station` (`station_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. CARTONS (individual cartons)
CREATE TABLE IF NOT EXISTS `cartons` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `carton_code` varchar(50) NOT NULL,
  `packing_order_id` int unsigned NOT NULL,
  `session_id` int unsigned DEFAULT NULL,
  `pallet_id` int unsigned DEFAULT NULL,
  `container_id` int unsigned DEFAULT NULL,
  `carton_seq` int unsigned NOT NULL DEFAULT 1,
  `total_qty` int unsigned NOT NULL DEFAULT 0,
  `weight_kg` decimal(8,2) DEFAULT NULL,
  `length_cm` decimal(6,1) DEFAULT NULL,
  `width_cm` decimal(6,1) DEFAULT NULL,
  `height_cm` decimal(6,1) DEFAULT NULL,
  `cbm` decimal(8,4) DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size_range` varchar(100) DEFAULT NULL,
  `is_mixed` tinyint(1) NOT NULL DEFAULT 0,
  `is_sealed` tinyint(1) NOT NULL DEFAULT 0,
  `seal_number` varchar(100) DEFAULT NULL,
  `label_printed` tinyint(1) NOT NULL DEFAULT 0,
  `qr_code` varchar(500) DEFAULT NULL,
  `barcode` varchar(255) DEFAULT NULL,
  `status` enum('packing','packed','labeled','palletized','loaded','shipped') NOT NULL DEFAULT 'packing',
  `packed_at` timestamp NULL DEFAULT NULL,
  `sealed_at` timestamp NULL 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 `uk_carton_code` (`carton_code`),
  KEY `idx_packing_order` (`packing_order_id`),
  KEY `idx_pallet` (`pallet_id`),
  KEY `idx_container` (`container_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. CARTON ITEMS (garments inside each carton — fully traceable)
CREATE TABLE IF NOT EXISTS `carton_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `carton_id` int unsigned NOT NULL,
  `packing_order_line_id` int unsigned DEFAULT NULL,
  `color` varchar(100) NOT NULL,
  `size` varchar(100) NOT NULL,
  `quantity` int unsigned NOT NULL DEFAULT 0,
  `barcode` varchar(255) DEFAULT NULL,
  `garment_barcode` varchar(255) DEFAULT NULL,
  `verified` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_carton` (`carton_id`),
  KEY `idx_line` (`packing_order_line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. CARTON IMAGES (photos per carton)
CREATE TABLE IF NOT EXISTS `carton_images` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `carton_id` int unsigned NOT NULL,
  `image_type` enum('packing','label','seal','pallet','container','other') NOT NULL DEFAULT 'packing',
  `file_path` varchar(500) DEFAULT NULL,
  `caption` varchar(255) DEFAULT NULL,
  `uploaded_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_carton` (`carton_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. PALLETS
CREATE TABLE IF NOT EXISTS `pallets` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `pallet_code` varchar(50) NOT NULL,
  `packing_order_id` int unsigned NOT NULL,
  `total_cartons` int unsigned NOT NULL DEFAULT 0,
  `total_qty` int unsigned NOT NULL DEFAULT 0,
  `weight_kg` decimal(8,2) DEFAULT NULL,
  `height_cm` decimal(6,1) DEFAULT NULL,
  `length_cm` decimal(6,1) DEFAULT NULL,
  `width_cm` decimal(6,1) DEFAULT NULL,
  `cbm` decimal(8,4) DEFAULT NULL,
  `is_stacked` tinyint(1) NOT NULL DEFAULT 0,
  `is_wrapped` tinyint(1) NOT NULL DEFAULT 0,
  `ai_load_balance` varchar(50) DEFAULT 'balanced',
  `status` enum('building','complete','loaded') NOT NULL DEFAULT 'building',
  `completed_at` timestamp NULL 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 `uk_pallet_code` (`pallet_code`),
  KEY `idx_packing_order` (`packing_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. PALLET CARTONS (link table)
CREATE TABLE IF NOT EXISTS `pallet_cartons` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `pallet_id` int unsigned NOT NULL,
  `carton_id` int unsigned NOT NULL,
  `position_x` int unsigned DEFAULT 0,
  `position_y` int unsigned DEFAULT 0,
  `position_z` int unsigned DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pallet_carton` (`pallet_id`, `carton_id`),
  KEY `idx_carton` (`carton_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. CONTAINERS
CREATE TABLE IF NOT EXISTS `packing_containers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `container_code` varchar(50) NOT NULL,
  `container_number` varchar(50) DEFAULT NULL,
  `container_type` enum('20ft','40ft','40ft_hq','20ft_open','40ft_open') NOT NULL DEFAULT '40ft',
  `packing_order_id` int unsigned DEFAULT NULL,
  `seal_number` varchar(100) DEFAULT NULL,
  `max_weight_kg` decimal(10,2) DEFAULT 26000.00,
  `max_cbm` decimal(8,4) DEFAULT 67.7000,
  `current_weight_kg` decimal(10,2) DEFAULT 0.00,
  `current_cbm` decimal(8,4) DEFAULT 0.00,
  `total_cartons` int unsigned NOT NULL DEFAULT 0,
  `total_qty` int unsigned NOT NULL DEFAULT 0,
  `loading_pct` decimal(5,2) DEFAULT 0.00,
  `weight_distribution` varchar(50) DEFAULT 'balanced',
  `truck_plate` varchar(50) DEFAULT NULL,
  `driver_name` varchar(255) DEFAULT NULL,
  `loading_date` date DEFAULT NULL,
  `status` enum('empty','loading','full','sealed','shipped') NOT NULL DEFAULT 'empty',
  `loaded_at` timestamp NULL DEFAULT NULL,
  `sealed_at` timestamp NULL DEFAULT NULL,
  `notes` text 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 `uk_container_code` (`container_code`),
  KEY `idx_packing_order` (`packing_order_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. CONTAINER CARTONS (link table)
CREATE TABLE IF NOT EXISTS `container_cartons` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `container_id` int unsigned NOT NULL,
  `carton_id` int unsigned NOT NULL,
  `pallet_id` int unsigned DEFAULT NULL,
  `position_x` int unsigned DEFAULT 0,
  `position_y` int unsigned DEFAULT 0,
  `position_z` int unsigned DEFAULT 0,
  `loaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_container_carton` (`container_id`, `carton_id`),
  KEY `idx_carton` (`carton_id`),
  KEY `idx_pallet` (`pallet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. PACKING QUALITY CHECKS
CREATE TABLE IF NOT EXISTS `packing_quality` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `carton_id` int unsigned DEFAULT NULL,
  `packing_order_id` int unsigned NOT NULL,
  `check_type` enum('wrong_size','wrong_color','wrong_barcode','wrong_polybag','wrong_fold','wrong_sticker','wrong_carton','weight_error','label_mismatch','other') NOT NULL,
  `severity` enum('critical','major','minor') NOT NULL DEFAULT 'minor',
  `description` text DEFAULT NULL,
  `detected_by` enum('operator','scanner','camera','ai','inspector') NOT NULL DEFAULT 'operator',
  `status` enum('open','correcting','resolved','dismissed') NOT NULL DEFAULT 'open',
  `resolved_by` int unsigned DEFAULT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `resolution_notes` text 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 `idx_carton` (`carton_id`),
  KEY `idx_packing_order` (`packing_order_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. SHIPPING MARKS
CREATE TABLE IF NOT EXISTS `shipping_marks` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_order_id` int unsigned NOT NULL,
  `mark_type` enum('shipping','carton_number','handling','destination','origin','barcode','qr_code','custom') NOT NULL DEFAULT 'shipping',
  `mark_text` text NOT NULL,
  `mark_position` varchar(100) DEFAULT NULL,
  `print_count` int unsigned NOT NULL DEFAULT 0,
  `status` enum('active','printed') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_packing_order` (`packing_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15. PACKING DOCUMENTS
CREATE TABLE IF NOT EXISTS `packing_documents` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_order_id` int unsigned NOT NULL,
  `doc_type` enum('packing_list','carton_list','carton_label','pallet_label','container_plan','shipping_marks','completion_certificate','packing_report','barcode_labels','other') NOT NULL,
  `doc_name` varchar(255) NOT NULL,
  `file_path` varchar(500) DEFAULT NULL,
  `generated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_packing_order` (`packing_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 16. PACKING AI ANALYSIS
CREATE TABLE IF NOT EXISTS `packing_ai_analysis` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_order_id` int unsigned NOT NULL,
  `analysis_type` enum('carton_optimization','container_loading','shipment_readiness','cost_optimization','weight_distribution') NOT NULL,
  `input_data` json DEFAULT NULL,
  `result_data` json DEFAULT NULL,
  `recommendation` text DEFAULT NULL,
  `efficiency_score` decimal(5,2) DEFAULT NULL,
  `cost_saving_usd` decimal(12,2) DEFAULT NULL,
  `status` enum('pending','completed','applied','dismissed') NOT NULL DEFAULT 'pending',
  `applied_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_packing_order` (`packing_order_id`),
  KEY `idx_type` (`analysis_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 17. PACKING AUDIT LOG
CREATE TABLE IF NOT EXISTS `packing_audit` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `entity_type` varchar(50) NOT NULL,
  `entity_id` int unsigned NOT NULL,
  `action` varchar(50) NOT NULL,
  `old_values` json DEFAULT NULL,
  `new_values` json DEFAULT NULL,
  `performed_by` int unsigned DEFAULT NULL,
  `performed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_entity` (`entity_type`, `entity_id`),
  KEY `idx_performed_at` (`performed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
