-- ============================================================
-- PACKING MODULE SEED DATA
-- ============================================================

-- Packing Instructions (sample buyer-specific)
INSERT INTO `packing_instructions` (`company_id`, `buyer_id`, `style_id`, `instruction_name`, `fold_type`, `garment_orientation`, `polybag_type`, `polybag_size`, `sticker_position`, `barcode_position`, `size_sticker_required`, `hanger_required`, `carton_type`, `carton_weight_max_kg`, `carton_length_cm`, `carton_width_cm`, `carton_height_cm`, `pieces_per_carton`, `carton_printing`, `shipping_marks`, `special_instructions`, `status`) VALUES
(1, 1, NULL, 'H&M Standard Packing', 'Flat Fold', 'Face Up', 'Self Adhesive', '30x40cm', 'Front Center', 'Right Side', 1, 0, '5 Ply Export', 18.00, 60, 40, 35, 50, '2-color flexo print per H&M spec', 'H&M logo, PO#, Style#, Color, Size, Made in Bangladesh', 'Keep polybag seal on right side', 'active'),
(1, 2, NULL, 'Zara Premium Packing', 'Flat Fold', 'Face Down', 'Self Adhesive', '28x38cm', 'Back Center', 'Left Side', 1, 0, '3 Ply Export', 15.00, 55, 38, 32, 40, 'Single color print per Inditex spec', 'ZARA, PO#, Style#, RN#, Made in Bangladesh', 'Tissue paper between garments', 'active'),
(1, 3, NULL, 'Nike Sustainble Packing', 'Rolled', 'Face Up', 'Recycled Poly', '32x42cm', 'Front Right', 'Inside Polybag', 1, 0, 'Corrugated Recycled', 20.00, 65, 45, 40, 48, 'Eco-label required per Nike RSL', 'Nike swoosh, PO#, Style#, Color code, Size, Country of origin', 'Use Nike sustainable poly only', 'active'),
(1, 4, NULL, 'Adidas Packing Spec', 'Flat Fold', 'Face Up', 'Self Adhesive', '30x40cm', 'Front Center', 'Back Side', 1, 0, '5 Ply Export', 18.00, 60, 42, 35, 50, '2-color per Adidas spec', 'Adidas logo, PO#, Style#, Size, Barcode', 'Fold per Adidas visual guide', 'active'),
(1, 5, NULL, 'Uniqlo Standard', 'Flat Fold', 'Face Up', 'Self Adhesive', '25x35cm', 'Center', 'Front', 0, 0, '3 Ply Standard', 12.00, 50, 35, 30, 60, 'Simple black print', 'UNIQLO, PO#, Style#, Size', 'Vacuum pack for down items', 'active');

-- Packing Stations
INSERT INTO `packing_stations` (`company_id`, `station_code`, `station_name`, `factory_id`, `location`, `has_scale`, `has_camera`, `has_printer`, `has_scanner`, `status`) VALUES
(1, 'PS-01', 'Packing Station 1', NULL, 'Packing Floor - Zone A', 1, 1, 1, 1, 'active'),
(1, 'PS-02', 'Packing Station 2', NULL, 'Packing Floor - Zone A', 1, 1, 1, 1, 'active'),
(1, 'PS-03', 'Packing Station 3', NULL, 'Packing Floor - Zone B', 1, 0, 1, 1, 'active'),
(1, 'PS-04', 'Packing Station 4', NULL, 'Packing Floor - Zone B', 1, 1, 1, 1, 'active'),
(1, 'PS-05', 'Packing Station 5', NULL, 'Packing Floor - Zone C', 1, 0, 1, 1, 'active'),
(1, 'PS-06', 'Packing Station 6', NULL, 'Packing Floor - Zone C', 1, 1, 1, 1, 'active'),
(1, 'PS-QC', 'QC Packing Station', NULL, 'QC Area', 1, 1, 1, 1, 'active'),
(1, 'PS-SP', 'Special Packing', NULL, 'Premium Zone', 1, 1, 1, 1, 'active');

-- Packing Orders (auto-generated from existing Buyer POs)
INSERT INTO `packing_orders` (`company_id`, `packing_order_code`, `buyer_po_id`, `buyer_id`, `style_id`, `factory_id`, `total_qty`, `total_cartons_planned`, `total_cartons_packed`, `total_pieces_packed`, `pieces_per_carton`, `destination`, `shipment_date`, `etd`, `eta`, `shipment_mode`, `status`, `packing_started_at`, `packing_accuracy`, `notes`) VALUES
(1, 'PKO-2026-0001', 1, 1, 1, NULL, 12000, 240, 240, 12000, 50, 'Germany', '2026-08-15', '2026-08-20', '2026-09-15', 'sea', 'verified', '2026-07-01 08:00:00', 99.92, 'H&M order - completed and verified'),
(1, 'PKO-2026-0002', 2, 1, 2, NULL, 8000, 200, 180, 7200, 40, 'Sweden', '2026-09-01', '2026-09-05', '2026-10-01', 'sea', 'in_progress', '2026-07-10 08:00:00', NULL, 'H&M second order - packing in progress'),
(1, 'PKO-2026-0003', 3, 2, 3, NULL, 5000, 125, 0, 0, 40, 'Spain', '2026-10-10', '2026-10-15', '2026-11-10', 'sea', 'pending', NULL, NULL, 'Zara order - pending start'),
(1, 'PKO-2026-0004', 6, 3, 6, NULL, 10000, 209, 45, 2160, 48, 'USA', '2026-08-25', '2026-08-30', '2026-09-20', 'sea', 'in_progress', '2026-07-15 08:00:00', NULL, 'Nike eco order - packing started'),
(1, 'PKO-2026-0005', 4, 3, 4, NULL, 15000, 300, 0, 0, 50, 'Netherlands', '2026-09-15', '2026-09-20', '2026-10-15', 'sea', 'pending', NULL, NULL, 'Nike big order - awaiting materials');

-- Packing Order Lines
INSERT INTO `packing_order_lines` (`packing_order_id`, `color`, `size`, `quantity`, `quantity_packed`, `quantity_remaining`) VALUES
(1, 'Black', 'S', 2000, 2000, 0),
(1, 'Black', 'M', 2500, 2500, 0),
(1, 'Black', 'L', 2500, 2500, 0),
(1, 'Black', 'XL', 1500, 1500, 0),
(1, 'White', 'S', 800, 800, 0),
(1, 'White', 'M', 1200, 1200, 0),
(1, 'White', 'L', 1000, 1000, 0),
(1, 'White', 'XL', 500, 500, 0),
(2, 'Navy', 'S', 1200, 1100, 100),
(2, 'Navy', 'M', 1800, 1700, 100),
(2, 'Navy', 'L', 1800, 1600, 200),
(2, 'Navy', 'XL', 1200, 1000, 200),
(2, 'Grey', 'S', 800, 700, 100),
(2, 'Grey', 'M', 1200, 1100, 100),
(3, 'Red', 'XS', 500, 0, 500),
(3, 'Red', 'S', 800, 0, 800),
(3, 'Red', 'M', 1200, 0, 1200),
(3, 'Red', 'L', 1200, 0, 1200),
(3, 'Red', 'XL', 800, 0, 800),
(3, 'Red', 'XXL', 500, 0, 500),
(4, 'Black', 'S', 1600, 400, 1200),
(4, 'Black', 'M', 2400, 800, 1600),
(4, 'Black', 'L', 2400, 600, 1800),
(4, 'Black', 'XL', 1600, 360, 1240),
(5, 'White', 'S', 2000, 0, 2000),
(5, 'White', 'M', 3000, 0, 3000),
(5, 'White', 'L', 3000, 0, 3000),
(5, 'White', 'XL', 2000, 0, 2000),
(5, 'Black', 'S', 1200, 0, 1200),
(5, 'Black', 'M', 1800, 0, 1800),
(5, 'Black', 'L', 1200, 0, 1200),
(5, 'Black', 'XL', 800, 0, 800);

-- Cartons (for completed and in-progress orders)
-- PKO-0001 (H&M - all 240 cartons)
INSERT INTO `cartons` (`company_id`, `carton_code`, `packing_order_id`, `session_id`, `carton_seq`, `total_qty`, `weight_kg`, `length_cm`, `width_cm`, `height_cm`, `cbm`, `color`, `size_range`, `is_mixed`, `is_sealed`, `label_printed`, `status`, `packed_at`, `sealed_at`)
SELECT 1, CONCAT('CTN-2026-0001-', LPAD(n, 3, '0')), 1, NULL, n, 50,
  16.50 + (RAND() * 2), 60, 40, 35, 0.0840,
  CASE WHEN n <= 40 THEN 'Black' WHEN n <= 50 THEN 'White' ELSE 'Black' END,
  CASE WHEN n <= 24 THEN 'S-M' WHEN n <= 44 THEN 'M-L' ELSE 'L-XL' END,
  0, 1, 1, 'shipped',
  '2026-07-05 10:00:00', '2026-07-05 10:15:00'
FROM (
  SELECT a.N + b.N * 10 + 1 AS n
  FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
       (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2) b
) nums WHERE n <= 240;

-- PKO-0002 (H&M in-progress - 180 cartons)
INSERT INTO `cartons` (`company_id`, `carton_code`, `packing_order_id`, `carton_seq`, `total_qty`, `weight_kg`, `length_cm`, `width_cm`, `height_cm`, `cbm`, `color`, `is_mixed`, `is_sealed`, `label_printed`, `status`, `packed_at`)
SELECT 1, CONCAT('CTN-2026-0002-', LPAD(n, 3, '0')), 2, n, 40,
  13.00 + (RAND() * 1.5), 55, 38, 32, 0.0669,
  CASE WHEN n % 3 = 0 THEN 'Grey' ELSE 'Navy' END,
  0, 1, 1, 'packed',
  '2026-07-18 09:00:00'
FROM (
  SELECT a.N + b.N * 10 + 1 AS n
  FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
       (SELECT 0 AS N UNION SELECT 1) b
) nums WHERE n <= 180;

-- PKO-0004 (Nike - 45 cartons packed so far)
INSERT INTO `cartons` (`company_id`, `carton_code`, `packing_order_id`, `carton_seq`, `total_qty`, `weight_kg`, `length_cm`, `width_cm`, `height_cm`, `cbm`, `color`, `is_mixed`, `is_sealed`, `label_printed`, `status`, `packed_at`)
SELECT 1, CONCAT('CTN-2026-0004-', LPAD(n, 3, '0')), 4, n, 48,
  17.00 + (RAND() * 2), 65, 45, 40, 0.1170,
  'Black', 0, 1, 1, 'packed',
  '2026-07-20 08:00:00'
FROM (
  SELECT a.N + b.N * 10 + 1 AS n
  FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
       (SELECT 0 AS N) b
) nums WHERE n <= 45;

-- Carton Items (sample for first few cartons)
INSERT INTO `carton_items` (`carton_id`, `packing_order_line_id`, `color`, `size`, `quantity`, `barcode`) VALUES
(1, 1, 'Black', 'S', 50, 'BLK-S-0001'),
(2, 1, 'Black', 'S', 50, 'BLK-S-0002'),
(3, 1, 'Black', 'S', 50, 'BLK-S-0003'),
(4, 1, 'Black', 'S', 50, 'BLK-S-0004'),
(5, 2, 'Black', 'M', 50, 'BLK-M-0005'),
(6, 2, 'Black', 'M', 50, 'BLK-M-0006'),
(7, 2, 'Black', 'M', 50, 'BLK-M-0007'),
(8, 2, 'Black', 'M', 50, 'BLK-M-0008'),
(9, 2, 'Black', 'M', 50, 'BLK-M-0009'),
(10, 3, 'Black', 'L', 50, 'BLK-L-0010'),
(11, 3, 'Black', 'L', 50, 'BLK-L-0011'),
(12, 3, 'Black', 'L', 50, 'BLK-L-0012'),
(13, 3, 'Black', 'L', 50, 'BLK-L-0013'),
(14, 3, 'Black', 'L', 50, 'BLK-L-0014'),
(15, 4, 'Black', 'XL', 50, 'BLK-XL-0015'),
(16, 4, 'Black', 'XL', 50, 'BLK-XL-0016'),
(17, 4, 'Black', 'XL', 50, 'BLK-XL-0017'),
(18, 5, 'White', 'S', 50, 'WHT-S-0018'),
(19, 5, 'White', 'S', 50, 'WHT-S-0019'),
(20, 6, 'White', 'M', 50, 'WHT-M-0020'),
(21, 6, 'White', 'M', 50, 'WHT-M-0021'),
(22, 7, 'White', 'L', 50, 'WHT-L-0022'),
(23, 7, 'White', 'L', 50, 'WHT-L-0023'),
(24, 8, 'White', 'XL', 50, 'WHT-XL-0024');

-- Pallets (for completed order PKO-0001)
INSERT INTO `pallets` (`company_id`, `pallet_code`, `packing_order_id`, `total_cartons`, `total_qty`, `weight_kg`, `height_cm`, `length_cm`, `width_cm`, `cbm`, `is_stacked`, `is_wrapped`, `ai_load_balance`, `status`, `completed_at`) VALUES
(1, 'PALL-001', 1, 20, 1000, 340.50, 180, 120, 100, 2.1600, 1, 1, 'balanced', 'loaded', '2026-07-06 14:00:00'),
(1, 'PALL-002', 1, 20, 1000, 338.20, 175, 120, 100, 2.1000, 1, 1, 'balanced', 'loaded', '2026-07-06 14:30:00'),
(1, 'PALL-003', 1, 20, 1000, 342.80, 182, 120, 100, 2.1840, 1, 1, 'slightly_front_heavy', 'loaded', '2026-07-06 15:00:00'),
(1, 'PALL-004', 1, 20, 1000, 335.60, 178, 120, 100, 2.1360, 1, 1, 'balanced', 'loaded', '2026-07-06 15:30:00'),
(1, 'PALL-005', 1, 20, 1000, 341.20, 180, 120, 100, 2.1600, 1, 1, 'balanced', 'loaded', '2026-07-06 16:00:00'),
(1, 'PALL-006', 1, 20, 1000, 339.80, 177, 120, 100, 2.1240, 1, 1, 'balanced', 'loaded', '2026-07-06 16:30:00'),
(1, 'PALL-007', 1, 20, 1000, 344.10, 183, 120, 100, 2.1960, 1, 1, 'balanced', 'loaded', '2026-07-06 17:00:00'),
(1, 'PALL-008', 1, 20, 1000, 336.90, 176, 120, 100, 2.1120, 1, 1, 'balanced', 'loaded', '2026-07-06 17:30:00'),
(1, 'PALL-009', 1, 20, 1000, 340.00, 180, 120, 100, 2.1600, 1, 1, 'balanced', 'loaded', '2026-07-07 08:00:00'),
(1, 'PALL-010', 1, 20, 1000, 337.50, 178, 120, 100, 2.1360, 1, 1, 'balanced', 'loaded', '2026-07-07 08:30:00'),
(1, 'PALL-011', 1, 20, 1000, 341.00, 181, 120, 100, 2.1720, 1, 1, 'balanced', 'loaded', '2026-07-07 09:00:00'),
(1, 'PALL-012', 1, 20, 1000, 338.80, 179, 120, 100, 2.1480, 1, 1, 'balanced', 'loaded', '2026-07-07 09:30:00');

-- Containers (for verified order PKO-0001)
INSERT INTO `packing_containers` (`company_id`, `container_code`, `container_number`, `container_type`, `packing_order_id`, `seal_number`, `max_weight_kg`, `max_cbm`, `current_weight_kg`, `current_cbm`, `total_cartons`, `total_qty`, `loading_pct`, `weight_distribution`, `truck_plate`, `driver_name`, `loading_date`, `status`, `loaded_at`, `sealed_at`) VALUES
(1, 'CNT-001', 'MSCU-4567890', '40ft', 1, 'SEAL-2026-001', 26000, 67.70, 18250.00, 43.20, 120, 6000, 63.81, 'balanced', 'DHK-12345', 'Mohammed Rahman', '2026-07-08', 'sealed', '2026-07-08 08:00:00', '2026-07-08 14:00:00'),
(1, 'CNT-002', 'MSCU-4567891', '40ft', 1, 'SEAL-2026-002', 26000, 67.70, 17920.00, 41.50, 120, 6000, 61.30, 'balanced', 'DHK-12346', 'Kamal Ahmed', '2026-07-08', 'sealed', '2026-07-08 14:30:00', '2026-07-08 18:00:00');

-- Container Cartons (link first 120 cartons to containers)
INSERT INTO `container_cartons` (`container_id`, `carton_id`, `pallet_id`, `position_x`, `position_y`, `position_z`)
SELECT 1, c.id, p.id,
  FLOOR((c.carton_seq - 1) / 20) AS px,
  FLOOR(((c.carton_seq - 1) % 20) / 5) AS py,
  (c.carton_seq - 1) % 5 AS pz
FROM cartons c
JOIN pallets p ON p.packing_order_id = c.packing_order_id AND p.id <= 6
WHERE c.packing_order_id = 1 AND c.carton_seq <= 120
ORDER BY c.carton_seq;

INSERT INTO `container_cartons` (`container_id`, `carton_id`, `pallet_id`, `position_x`, `position_y`, `position_z`)
SELECT 2, c.id, p.id,
  FLOOR((c.carton_seq - 121) / 20) AS px,
  FLOOR(((c.carton_seq - 121) % 20) / 5) AS py,
  (c.carton_seq - 121) % 5 AS pz
FROM cartons c
JOIN pallets p ON p.packing_order_id = c.packing_order_id AND p.id >= 7
WHERE c.packing_order_id = 1 AND c.carton_seq > 120 AND c.carton_seq <= 240
ORDER BY c.carton_seq;

-- Packing Quality Issues
INSERT INTO `packing_quality` (`company_id`, `carton_id`, `packing_order_id`, `check_type`, `severity`, `description`, `detected_by`, `status`, `resolved_at`, `resolution_notes`) VALUES
(1, 25, 2, 'wrong_size', 'major', 'Size M packed in Size L carton - CTN-0002-0025', 'scanner', 'resolved', '2026-07-18 14:00:00', 'Garment removed and re-sorted'),
(1, 88, 2, 'weight_error', 'minor', 'Carton weight 14.2kg exceeds mixed tolerance', 'operator', 'resolved', '2026-07-19 10:00:00', 'One piece removed, weight now 12.8kg'),
(1, 156, 2, 'wrong_barcode', 'critical', 'Barcode mismatch - garment barcode belongs to different PO', 'scanner', 'open', NULL, NULL),
(1, NULL, 4, 'wrong_polybag', 'minor', 'Nike sustainable polybag not used - standard poly applied', 'camera', 'open', NULL, NULL),
(1, 200, 4, 'wrong_fold', 'minor', 'Garment folded flat instead of rolled per Nike spec', 'ai', 'correcting', NULL, 'Re-folding in progress');

-- Shipping Marks
INSERT INTO `shipping_marks` (`company_id`, `packing_order_id`, `mark_type`, `mark_text`, `mark_position`, `status`) VALUES
(1, 1, 'shipping', 'H&M Hennes & Mauritz AB\nSödra Hamnvägen 6B\nSE-251 08 Helsingborg\nSweden', 'All sides', 'printed'),
(1, 1, 'carton_number', 'CTN NO: {carton_seq} / {total_cartons}\nSTYLE: {style_no}\nCOLOR: {color}\nSIZE: {size}\nQTY: {qty}\nGW: {weight} KG', 'Front face', 'printed'),
(1, 1, 'handling', 'THIS SIDE UP\nFRAGILE\nKEEP DRY\nDO NOT STACK MORE THAN 3 HIGH', 'All sides', 'printed'),
(1, 1, 'origin', 'MADE IN BANGLADESH', 'Right side', 'printed'),
(1, 2, 'shipping', 'H&M Hennes & Mauritz AB\nSödra Hamnvägen 6B\nSE-251 08 Helsingborg\nSweden', 'All sides', 'active'),
(1, 3, 'shipping', 'ZARA / INDITEX\nAvenida de la Diputación s/n\n15143 Arteixo, Spain', 'All sides', 'active'),
(1, 4, 'shipping', 'NIKE Inc.\nOne Bowerman Drive\nBeaverton, OR 97005, USA', 'All sides', 'active');

-- Packing AI Analysis
INSERT INTO `packing_ai_analysis` (`company_id`, `packing_order_id`, `analysis_type`, `result_data`, `recommendation`, `efficiency_score`, `cost_saving_usd`, `status`) VALUES
(1, 1, 'carton_optimization', '{"suggested_ppc": 50, "optimized_cartons": 240, "mixed_cartons": 0, "wasted_space_pct": 2.1}', 'Standard 50ppc is optimal for this order. No mixed cartons needed.', 97.90, 1250.00, 'applied'),
(1, 1, 'container_loading', '{"utilization_pct": 92.3, "cartons_per_container": 120, "containers_needed": 2, "weight_balanced": true}', 'Two 40ft containers with balanced loading. Weight distribution is optimal.', 92.30, 3200.00, 'applied'),
(1, 2, 'carton_optimization', '{"suggested_ppc": 40, "optimized_cartons": 200, "mixed_cartons": 2, "wasted_space_pct": 3.8}', 'Use 198 standard cartons + 2 mixed cartons for remaining pieces.', 96.20, 480.00, 'completed'),
(1, 4, 'carton_optimization', '{"suggested_ppc": 48, "optimized_cartons": 209, "mixed_cartons": 5, "wasted_space_pct": 4.2}', 'Nike order: Use 48ppc standard. 5 mixed cartons for size/color remainders.', 95.80, 890.00, 'completed'),
(1, 4, 'shipment_readiness', '{"packing_pct": 10.3, "labels_pct": 10.3, "pallet_pct": 0, "container_pct": 0, "docs_pct": 0, "overall": 10.3}', 'Early stage. Focus on completing carton packing before palletizing.', 10.30, NULL, 'completed');

-- Packing Documents
INSERT INTO `packing_documents` (`company_id`, `packing_order_id`, `doc_type`, `doc_name`) VALUES
(1, 1, 'packing_list', 'Packing List - PKO-2026-0001 - H&M'),
(1, 1, 'carton_list', 'Carton List - PKO-2026-0001 - 240 Cartons'),
(1, 1, 'carton_label', 'Carton Labels - PKO-2026-0001'),
(1, 1, 'pallet_label', 'Pallet Labels - PKO-2026-0001 - 12 Pallets'),
(1, 1, 'container_plan', 'Container Loading Plan - PKO-2026-0001'),
(1, 1, 'shipping_marks', 'Shipping Marks - PKO-2026-0001'),
(1, 1, 'completion_certificate', 'Packing Completion Certificate - PKO-2026-0001'),
(1, 1, 'packing_report', 'Packing Report - PKO-2026-0001');
