<?php
namespace App\Services;
use App\Services\ContainerIsoMapping;
use Illuminate\Support\Facades\Log;

class BaplieParser
{
    private $filePath;
    private $voyageData = [];
    private $containersData = [];
    private $locationsData = [];
    private $totalWeight = 0;
    private $containerCount = 0;
    private $containersWithWeight = 0;
    private $excelData = null;
    private $isoMap = [];
    private $oldToNewMap = [];

     public function __construct($filePath = null)
    {
        $this->filePath = $filePath;
        
        // Load ISO mapping
        $this->isoMap = ContainerIsoMapping::getIsoMap();
        $this->buildOldToNewMap();
        
        Log::info("BaplieParser initialized", [
            'file_provided' => !empty($filePath),
            'iso_mappings' => count($this->isoMap)
        ]);
        
        // Only parse BAPLIE if file provided
        if ($filePath && file_exists($filePath)) {
            $this->parseFile();
            $this->debugIsoMapping();
        }
    }
    
private function normalizeStatusToCode($status): string
{
    if (empty($status)) return 'U';
    
    $status = strtoupper(trim($status));
    
    // Full variations → '5'
    if (in_array($status, ['F', 'FULL', 'FCL', 'LADEN', 'LOADED', '5', 'L'])) {
        return '5';
    }
    
    // Empty variations → '4'
    if (in_array($status, ['E', 'EMPTY', 'MTY', 'MT', '4'])) {
        return '4';
    }
    
    // Log status yang tidak dikenali
    if ($status !== 'U') {
        Log::warning("Unknown status value", ['status' => $status]);
    }
    
    return 'U';
}

/**
 * Convert status code to human-readable text
 */
private function statusCodeToText($statusCode): string
{
    $code = $this->normalizeStatusToCode($statusCode); // Normalize dulu untuk safety
    
    return match($code) {
        '5' => 'Full',
        '4' => 'Empty',
        default => 'Unknown'
    };
}
    
    // Method to get containers by weight type
    public function getContainersByWeightType($weightType)
    {
        return array_filter($this->containersData, function($container) use ($weightType) {
            foreach ($container['weight_details'] as $weightDetail) {
                if (strpos($weightDetail['type_description'], $weightType) !== false) {
                    return true;
                }
            }
            return false;
        });
    }

    private function buildOldToNewMap()
    {
        foreach ($this->isoMap as $newCode => $details) {
            if (!empty($details['old_code'])) {
                $this->oldToNewMap[$details['old_code']] = $newCode;
            }
        }
    }
    
   private function mapContainerStatus($statusCode)
{
    
    return $this->statusCodeToText($statusCode);
}

  public function countContainerStatuses(array $containers)
    {
        $countFull = 0;
        $countEmpty = 0;
        $countUnknown = 0;
        foreach ($containers as $container) {
            $status = $this->mapContainerStatus($container['status_code'] ?? '');
            if ($status === 'Full') {
                $countFull++;
            } elseif ($status === 'Empty') {
                $countEmpty++;
            } else {
                $countUnknown++;
            }
        }
        return [
            'Full' => $countFull,
            'Empty' => $countEmpty,
            'Unknown' => $countUnknown,
        ];
    }
    
private function parseFile()
    {
        if (!file_exists($this->filePath)) {
            throw new \Exception("File not found: " . $this->filePath);
        }
        
        $content = file_get_contents($this->filePath);
        
        // Split berdasarkan pattern yang lebih robust
        $segments = explode("'", $content);
        
        $currentContainer = null;
        $segmentCount = 0;
        $firstLocCount = 0; // Track first LOC+147+ per container
        $nadCount = 0; // Track NAD+CA+ (container end marker)
        $inContainerBlock = false; // Flag untuk menandai kita sedang dalam blok container
        
        error_log("=== STARTING BAPLIE PARSING (LOC-NAD Block Mode) ===");
        error_log("Total segments to process: " . count($segments));
        error_log("File size: " . strlen($content) . " bytes");
        
        foreach ($segments as $segment) {
            $segment = trim($segment);
            $segmentCount++;
            
            if (empty($segment)) continue;
            
            // Log progress setiap 500 segment
            if ($segmentCount % 500 == 0) {
                error_log("Processing segment $segmentCount, containers saved: {$this->containerCount}");
            }
            
            // Parse header segments first
            $this->parseHeaderSegments($segment);
            
            // LOC+147+ = MULAI atau LANJUT container block
            if (strpos($segment, 'LOC+147+') === 0) {
                
                // Jika belum ada container aktif, buat baru (first LOC+147+)
                if ($currentContainer === null) {
                    $firstLocCount++;
                    $inContainerBlock = true;
                    
                    // Parse posisi bay-row-tier dari LOC+147
                    $parts = explode('+', $segment);
                    $positionData = $parts[2] ?? '';
                    $position = explode(':', $positionData)[0] ?? '';
                    
                    $bay = '';
                    $row = '';
                    $tier = '';
                    
                    if (strlen($position) >= 7) {
                        $bay = substr($position, 0, 3);
                        $row = substr($position, 3, 2);
                        $tier = substr($position, 5, 2);
                    } elseif (!empty($position)) {
                        $bay = $position;
                    }
                    
                    // Buat container baru
                    $currentContainer = [
                        'container_no' => '', // Akan diisi dari EQD
                        'iso_type' => '',     // Akan diisi dari EQD
                        'status' => 'U',
                        'status_code' => 'U',
                        'status_mapped' => 'Unknown',
                        'weight_kg' => 0,
                        'weight_details' => [],
                        'primary_weight_type' => null,
                        'bay' => $bay,
                        'row' => $row,
                        'tier' => $tier,
                        'stowage_location' => $position,
                        'stowage_positions' => [$position], // Track multiple positions
                        'pol_locode' => '',
                        'pod_locode' => '',
                        'delivery_locode' => '',
                        'bl_no' => '',
                        'imdg_class' => '',
                        'handling_instruction' => '',
                        'operator_code' => '',
                        'size' => 'Unknown',
                        'type_group' => 'Unknown',
                        'jenis' => 'unknown',
                        'keterangan' => "Container block #$firstLocCount",
                        'locations' => [],
                        'segment_debug' => $segment,
                        'processing_note' => ''
                    ];
                    
                    error_log("LOC+147 #$firstLocCount: Started container block at position $position");
                } 
                // Jika sudah ada container aktif, ini adalah stowage position tambahan
                else {
                    $parts = explode('+', $segment);
                    $positionData = $parts[2] ?? '';
                    $position = explode(':', $positionData)[0] ?? '';
                    
                    // Update ke posisi terakhir (yang biasanya lebih valid)
                    if (strlen($position) >= 7) {
                        $currentContainer['bay'] = substr($position, 0, 3);
                        $currentContainer['row'] = substr($position, 3, 2);
                        $currentContainer['tier'] = substr($position, 5, 2);
                        $currentContainer['stowage_location'] = $position;
                    } elseif (!empty($position)) {
                        $currentContainer['bay'] = $position;
                        $currentContainer['stowage_location'] = $position;
                    }
                    
                    // Tambahkan ke list positions
                    if (!empty($position)) {
                        $currentContainer['stowage_positions'][] = $position;
                        error_log("  Additional LOC+147: $position (total positions: " . count($currentContainer['stowage_positions']) . ")");
                    }
                }
                continue;
            }
            
            // ✅ NAD+CA+ = AKHIR container block, SAVE container
            if (strpos($segment, 'NAD+CA+') === 0) {
                $nadCount++;
                
                if ($currentContainer !== null && $inContainerBlock) {
                    // Parse operator dari NAD segment
                  // Parse operator dari NAD segment
                    $parts = explode('+', $segment);
                    if (isset($parts[2])) {
                        $operatorInfo = explode(':', $parts[2]);
                        $operator = $operatorInfo[0] ?? '';

                        $currentContainer['operator_code'] = $operator;

                        // ✅ SIMPAN NAD GLOBAL UNTUK DIPAKAI SEBAGAI DEFAULT NANTI
                        if (!session()->has('default_nad') && !empty($operator)) {
                            session()->put('default_nad', $operator);
                            error_log("DEFAULT NAD SET FROM BAPLIE → $operator");
                        }
                    }

                    
                    // Validasi sebelum save
                    if (empty($currentContainer['container_no'])) {
                        $currentContainer['container_no'] = 'UNKNOWN_NAD_' . $nadCount;
                        $currentContainer['processing_note'] .= ' | No EQD found - missing container number';
                    }
                    
                    // Log summary
                    $posCount = count($currentContainer['stowage_positions']);
                    error_log("NAD #$nadCount: Saving container {$currentContainer['container_no']} with $posCount stowage position(s)");
                    
                    $this->addContainer($currentContainer);
                    $currentContainer = null;
                    $inContainerBlock = false;
                } else {
                    error_log("WARNING: Found NAD+CA+ without active container (NAD #$nadCount)");
                }
                continue;
            }
            
            // Parse segments yang berhubungan dengan container aktif
            if ($currentContainer !== null && $inContainerBlock) {
                $this->parseContainerSegments($segment, $currentContainer);
            }
            
            // Deteksi akhir file
            if (strpos($segment, 'UNT+') === 0 || strpos($segment, 'UNZ+') === 0) {
                if ($currentContainer !== null) {
                    error_log("End of file reached - saving last container (missing NAD)");
                    if (empty($currentContainer['container_no'])) {
                        $currentContainer['container_no'] = 'UNKNOWN_EOF_' . ($this->containerCount + 1);
                    }
                    $this->addContainer($currentContainer);
                    $currentContainer = null;
                }
                break;
            }
        }
        
        // Final save jika masih ada container yang belum tersimpan
        if ($currentContainer !== null) {
            error_log("FINAL: Forcing save of last container (no NAD or UNT found)");
            if (empty($currentContainer['container_no'])) {
                $currentContainer['container_no'] = 'UNKNOWN_FINAL_' . ($this->containerCount + 1);
            }
            $this->addContainer($currentContainer);
        }
        
        $this->calculateSummaryStats();
        
        // Validasi akhir
        error_log("=== PARSING COMPLETED ===");
        error_log("Container blocks started (first LOC+147): $firstLocCount");
        error_log("Container blocks ended (NAD+CA): $nadCount");
        error_log("Containers saved: {$this->containerCount}");
        
        if ($firstLocCount != $this->containerCount) {
            error_log("WARNING: First LOC count ($firstLocCount) != saved containers ({$this->containerCount}). Difference: " . ($firstLocCount - $this->containerCount));
        }
        
        if ($nadCount != $this->containerCount) {
            error_log("WARNING: NAD count ($nadCount) != saved containers ({$this->containerCount}). Difference: " . ($nadCount - $this->containerCount));
        }
    }
    private function parseHeaderSegments($segment)
    {
        // Parse UNB segment (Interchange Header)
        if (strpos($segment, 'UNB+') === 0) {
            $parts = explode('+', $segment);
            $this->voyageData['sender'] = $parts[2] ?? '';
            $this->voyageData['recipient'] = $parts[3] ?? '';
            
            if (isset($parts[4])) {
                $dateTime = explode(':', $parts[4]);
                $this->voyageData['transmission_date'] = $dateTime[0] ?? '';
                $this->voyageData['transmission_time'] = $dateTime[1] ?? '';
                
                // ✅ BARU: Simpan raw interchange datetime untuk EDI export
                $this->voyageData['interchange_datetime'] = $parts[4]; // Format: YYMMDD:HHMM
            }
            
            $this->voyageData['interchange_control_ref'] = $parts[5] ?? '';
            
            // ✅ LOGGING untuk debug
            Log::info('UNB segment parsed', [
                'sender' => $this->voyageData['sender'],
                'recipient' => $this->voyageData['recipient'],
                'interchange_datetime' => $this->voyageData['interchange_datetime'] ?? 'N/A',
                'interchange_control_ref' => $this->voyageData['interchange_control_ref']
            ]);
        }
        
        // Parse UNH segment (Message Header)
        if (strpos($segment, 'UNH+') === 0) {
            $parts = explode('+', $segment);
            $this->voyageData['message_reference_number'] = $parts[1] ?? '';
            if (isset($parts[2])) {
                $messageType = explode(':', $parts[2]);
                $this->voyageData['message_type'] = $messageType[0] ?? '';
                $this->voyageData['message_version'] = $messageType[1] ?? '';
                $this->voyageData['message_release'] = $messageType[2] ?? '';
                $this->voyageData['controlling_agency'] = $messageType[3] ?? '';
                $this->voyageData['association_code'] = $messageType[4] ?? '';
            }
        }
        
        // Parse BGM segment (Beginning of Message)
        if (strpos($segment, 'BGM+') === 0) {
            $parts = explode('+', $segment);
            $this->voyageData['document_name'] = $parts[2] ?? '';
            $this->voyageData['message_function'] = $parts[3] ?? '';
        }
        
        // Parse DTM segment (Date/Time)
        if (strpos($segment, 'DTM+') === 0) {
            $parts = explode('+', $segment);
            $dtmData = explode(':', $parts[1]);
            $dtmQualifier = $dtmData[0] ?? '';
            $dtmValue = $dtmData[1] ?? '';
            $dtmFormat = $dtmData[2] ?? '';

            
            if ($dtmQualifier === '137') {
                $this->voyageData['message_date'] = $this->formatDate($dtmValue, $dtmFormat);
                $this->voyageData['document_datetime'] = $dtmValue; 
                $this->voyageData['document_datetime_format'] = $dtmFormat;
            } elseif ($dtmQualifier === '178') {
                // Departure date/time
                $this->voyageData['departure_date'] = $this->formatDate($dtmValue, $dtmFormat);
                $this->voyageData['departure_datetime'] = $dtmValue; 
                $this->voyageData['departure_datetime_format'] = $dtmFormat; 
            } elseif ($dtmQualifier === '136') {
                // Arrival date/time
                $this->voyageData['arrival_date'] = $this->formatDate($dtmValue, $dtmFormat);
                $this->voyageData['arrival_datetime'] = $dtmValue; 
                $this->voyageData['arrival_datetime_format'] = $dtmFormat;
            } elseif ($dtmQualifier === '132') {
                $this->voyageData['actual_arrival_date'] = $this->formatDate($dtmValue, $dtmFormat);
            }
            
           
            Log::info('DTM segment parsed', [
                'qualifier' => $dtmQualifier,
                'value' => $dtmValue,
                'format' => $dtmFormat
            ]);
        }
        
        // Parse TDT segment (Transport Details)
        if (strpos($segment, 'TDT+20+') === 0) {
            $parts = explode('+', $segment);
            $this->voyageData['voyage_no'] = $parts[2] ?? '';

            // ✅ Parse carrier code (parts[5])
            if (isset($parts[5])) {
                $carrierInfo = explode(':', $parts[5]);
                $this->voyageData['carrier_code'] = $carrierInfo[0] ?? '';

                // Debug logging
                \Log::info('🚢 TDT Segment Parsed', [
                    'segment' => $segment,
                    'parts_count' => count($parts),
                    'parts[5]' => $parts[5] ?? 'NOT SET',
                    'carrier_code_parsed' => $this->voyageData['carrier_code']
                ]);
            } else {
                \Log::warning('⚠️ TDT Segment missing parts[5]', [
                    'segment' => $segment,
                    'parts_count' => count($parts)
                ]);
            }

            // ✅ Parse call sign and vessel name (parts[8])
            if (isset($parts[8])) {
                $callSignInfo = explode(':', $parts[8]);
                $this->voyageData['call_sign'] = $callSignInfo[0] ?? '';
                $this->voyageData['vessel_name'] = $callSignInfo[3] ?? '';
            }
        }
    }
    
    private function parseLocationSegment($segment, &$currentContainer)
    {
        if (strpos($segment, 'LOC+') === 0) {
            $parts = explode('+', $segment);
            if (count($parts) >= 3) {
                $locationQualifier = $parts[1];
                $locationInfo = explode(':', $parts[2]);
                $locationCode = $locationInfo[0] ?? '';
                $locationName = $locationInfo[1] ?? '';

                $locationType = $this->mapLocationQualifier($locationQualifier);

                if (!empty($locationCode)) {
                    if ($locationQualifier === '5' || $locationQualifier === '9' || $locationQualifier === '12') {
                        $this->voyageData['port_of_departure'] = $locationCode;
                        $currentContainer['pol_locode'] = $locationCode;
                    } 
                    elseif ($locationQualifier === '6') {
                        $this->voyageData['port_of_discharge'] = $locationCode;
                        $currentContainer['pod_locode'] = $locationCode;
                    }
                    elseif ($locationQualifier === '11') {
                        $this->voyageData['port_of_discharge'] = $locationCode;
                        $currentContainer['pod_locode'] = $locationCode;
                    } 
                    elseif ($locationQualifier === '83') {
                        $this->voyageData['place_of_delivery'] = $locationCode;
                        $currentContainer['delivery_locode'] = $locationCode;
                    }

                    // Tambahkan ke list locations
                    $locationKey = $locationQualifier . '_' . $locationCode;
                    if (!isset($this->locationsData[$locationKey])) {
                        $this->locationsData[$locationKey] = [
                            'qualifier' => $locationQualifier,
                            'type'      => $locationType,
                            'code'      => $locationCode,
                            'name'      => $locationName,
                            'containers_count' => 0
                        ];
                    }

                    $currentContainer['locations'][] = [
                        'qualifier'   => $locationQualifier,
                        'description' => $locationType,
                        'locode'      => $locationCode
                    ];
                }
            }
        }
    }

 public function parseExcelFile($filePath)
{
    try {
        Log::info('BaplieParser: Starting Excel parsing', ['file' => $filePath]);
        
        if (!class_exists('PhpOffice\PhpSpreadsheet\IOFactory')) {
            throw new \Exception('PhpSpreadsheet not installed');
        }
        
        // Detect file type
        $fileContent = file_get_contents($filePath, false, null, 0, 1000);
        
        if (strpos($fileContent, '<html') !== false || strpos($fileContent, '<table') !== false) {
            return $this->parseHtmlTableAsExcel($filePath);
        }
        
        // Try normal Excel parsing
        try {
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($filePath);
            $reader->setReadDataOnly(true);
            $reader->setReadEmptyCells(false);
            
            $spreadsheet = $reader->load($filePath);
            $worksheet = $spreadsheet->getActiveSheet();
            
        } catch (\Exception $e) {
            Log::warning('PhpSpreadsheet failed, trying CSV', ['error' => $e->getMessage()]);
            return $this->parseAsCSV($filePath);
        }
        
        // Get all data
        $rawData = $worksheet->toArray(null, true, true, false);
        
        if (empty($rawData) || count($rawData) < 2) {
            throw new \Exception('Excel file is empty');
        }
        
        // Find header row
        $headerRowIndex = 0; 
        $headers = $rawData[0];

        // Ambil data mulai dari baris ke-2
        $dataRows = array_slice($rawData, 1);

        
        // Map headers
        $headerMap = $this->createExcelHeaderMap($headers);
        
        // BARU: Logging yang lebih detail
        Log::info('Excel headers detected', [
            'header_row' => $headerRowIndex,
            'total_headers' => count($headers),
            'mapped_fields' => array_keys($headerMap),
            'first_data_row' => $headerRowIndex + 2
        ]);
        
        $containers = [];
        $rowCount = 0;
        $skippedRows = []; // BARU: Track skipped rows
        
        foreach ($dataRows as $rowIndex => $row) {
            $actualRowNumber = $headerRowIndex + $rowIndex + 2;
            $rowCount++;
            
            // Skip empty rows
            if (empty(array_filter($row, fn($cell) => !empty(trim($cell)) && $cell !== '--'))) {
                $skippedRows[] = "Row $actualRowNumber: Empty row";
                continue;
            }
            
            // Skip summary rows
            if ($this->isSummaryRow($row)) {
                $skippedRows[] = "Row $actualRowNumber: Summary row - " . ($row[0] ?? 'N/A');
                continue;
            }
            
            try {
                $container = $this->mapExcelRowToContainer($row, $headerMap, $headers);
                
                if ($container !== null && !empty($container['container_no']) && 
                    $this->isValidContainerNumber($container['container_no'])) {
                    $containers[] = $container;
                } else {
                    $skippedRows[] = "Row $actualRowNumber: Invalid container - " . ($row[$headerMap['container_no'] ?? 0] ?? 'N/A');
                }
                
            } catch (\Exception $e) {
                Log::warning('Error processing Excel row', [
                    'row' => $actualRowNumber,
                    'error' => $e->getMessage()
                ]);
                $skippedRows[] = "Row $actualRowNumber: Error - " . $e->getMessage();
            }
        }
        
        // BARU: Logging hasil akhir dengan detail
        Log::info('Excel parsing completed', [
            'rows_processed' => $rowCount,
            'valid_containers' => count($containers),
            'skipped_rows' => count($skippedRows),
            'sample_skipped' => array_slice($skippedRows, 0, 5)
        ]);
        
        return [
            'containers' => $containers,
            'voyage' => [],
            // BARU: Parsing info untuk debugging
            'parsing_info' => [
                'total_rows' => $rowCount,
                'valid_containers' => count($containers),
                'skipped_rows' => $skippedRows
            ]
        ];
        
    } catch (\Exception $e) {
        Log::error('Excel parsing failed', [
            'error' => $e->getMessage(),
            'file' => $filePath,
            'trace' => $e->getTraceAsString() // BARU: Error trace
        ]);
        throw new \Exception('Failed to parse Excel: ' . $e->getMessage());
    }
    }
    protected function parseLocation($value)
    {
        if (!$value) return [null, null, null];

        $value = strtoupper(trim($value));
        $clean = preg_replace('/[^A-Z0-9]/', '', $value);

        if (preg_match('/^\d{6}$/', $clean)) {
            $clean = str_pad($clean, 7, '0', STR_PAD_LEFT);
        }

        // Format 1: 7 digit angka → 3 bay, 2 row, 2 tier
        if (preg_match('/^(\d{3})(\d{2})(\d{2})$/', $clean, $m)) {
            return [$m[1], $m[2], $m[3]];
        }

        // Format 2: 5 digit → selalu bay 001
        if (preg_match('/^(\d{5})$/', $clean, $m)) {
            $digits = $m[1];
            $row = substr($digits, 1, 2);  // posisi 2 digit tengah
            $tier = substr($digits, 3, 2); // posisi 2 digit akhir
            return ['001', $row, $tier];
        }

        // Format 3: A1022 → Zone + Bay + Row
        if (preg_match('/^([A-Z])(\d{2})(\d{2})$/', $clean, $m)) {
            $zone = $m[1];
            $bay = $zone . $m[2];
            $row = $m[3];
            $tier = null;
            return [$bay, $row, $tier];
        }

        // Format 4: fallback (misal 6 digit aneh lainnya)
        if (preg_match('/^(\d{6})$/', $clean, $m)) {
            $bay = substr($m[1], 0, 3);
            $row = substr($m[1], 3, 2);
            $tier = substr($m[1], 5, 1);
            return [$bay, $row, $tier];
        }

        // Tidak cocok
        return [null, null, null];
    }

 private function findHeaderRow($rawData)
    {
        foreach ($rawData as $index => $row) {
            $rowText = strtolower(implode(' ', array_filter($row)));
            
            $keywords = ['container', 'status', 'iso', 'weight', 'pol', 'pod'];
            $matchCount = 0;
            
            foreach ($keywords as $keyword) {
                if (strpos($rowText, $keyword) !== false) {
                    $matchCount++;
                }
            }
            
            if ($matchCount >= 3) {
                Log::info("Header row found at index $index with $matchCount matches");
                return $index;
            }
        }
        
        Log::warning('No header detected, using first row');
        return 0;
    }

private function isSummaryRow($row)
{
    $firstCell = strtolower(trim($row[0] ?? ''));
    
    // Check if first cell is just a number (likely row number, not summary)
    if (is_numeric($firstCell) && strlen($firstCell) <= 3) {
        return false; // Ini bukan summary, ini row number
    }
    
    // Known summary keywords
    $summaryKeywords = [
        'pod', 'pol', 'total', 'subtotal', 'grand total',
        'dangerous cargo', 'over dimension', 'weight', 'sum', 'count'
    ];
    
    foreach ($summaryKeywords as $keyword) {
        if (strpos($firstCell, $keyword) !== false) {
            return true;
        }
    }
    
    // Check "X | Y" pattern (seperti "38 | 6.6" atau "1 | 31")
    if (preg_match('/^\d+\s*\|\s*[\d.]+/', $firstCell)) {
        return true;
    }
    
    // Check if mostly empty or contains only "--"
    $nonEmptyCount = count(array_filter($row, fn($cell) => 
        !empty(trim($cell)) && trim($cell) !== '--' && trim($cell) !== '-'
    ));
    
    return $nonEmptyCount < 3;
}

 private function isValidContainerNumber($containerNo)
    {
        $containerNo = strtoupper(trim($containerNo));
        
        // ISO 6346: 4 letters + 7 digits
        if (preg_match('/^[A-Z]{4}[0-9]{7}$/', $containerNo)) {
            return true;
        }
        
        // Relaxed: 3-4 letters + 6-7 digits
        if (preg_match('/^[A-Z]{3,4}[0-9]{6,7}$/', $containerNo)) {
            Log::info("Non-standard container format accepted: $containerNo");
            return true;
        }
        
        // Reject obvious non-containers
        $rejectKeywords = ['total', 'pod', 'dry', 'full', 'empty', 'weight'];
        foreach ($rejectKeywords as $keyword) {
            if (stripos($containerNo, $keyword) !== false) {
                return false;
            }
        }
        
        if (strlen($containerNo) < 8) {
            return false;
        }
        
        Log::warning("Unusual container format: $containerNo");
        return true;
    }
 
/**
 * PERBAIKAN: Enhanced header mapping dengan konsistensi field names
 */
private function createExcelHeaderMap($headers)
{

    $headerMap = [];

    foreach ($headers as $index => $header) {
        
        $header = (string) $header;
        $header = preg_replace('/[\x00-\x1F\x7F\xA0\xAD]/u', '', $header); // control chars & non-breaking
        $header = preg_replace('/^\xEF\xBB\xBF/', '', $header); // remove UTF-8 BOM
        $header = preg_replace('/\x{FEFF}/u', '', $header); // remove zero-width no-break space

      
        $normalized = strtolower(trim(preg_replace('/[^a-z0-9]+/i', '', $header ?? '')));


        Log::info('Header normalization', [
            'original' => $header,
            'normalized' => $normalized
        ]);

        $mappings = [
            'no' => 'row_number',
            'containerno' => 'container_no',
            'container' => 'container_no',
            'status' => 'status',
            'iso' => 'iso_type',
            'size' => 'size',
            'type' => 'type',
            'gross' => 'weight_kg',
            'weight' => 'weight_kg',
            'grossweight' => 'weight_kg',
            'seal' => 'seal_no',
            'booking' => 'booking_no',
            'lop' => 'pol_locode',
            'pol' => 'pol_locode',
            'discport' => 'pod_locode',
            'disc' => 'pod_locode',
            'pod' => 'pod_locode',
            'fdiscport' => 'delivery_locode',
            'fdisc' => 'delivery_locode',
            'location' => 'location',
            'location' => 'location',
            'slot' => 'location',
            'stowage' => 'location',
            'stowagelocation' => 'location',
            'locationbay' => 'location',
            'locationrow' => 'location',
            'locationtier' => 'location',
            'temp' => 'temp',
            'temperature' => 'temp',
            'arrts' => 'arr_ts',
            'arrby' => 'arr_by',
            'stop' => 'stop',
            'pebno' => 'peb_no',
            'itemclass' => 'item_class',
            'bay' => 'bay',
            'row' => 'row',
            'tier' => 'tier',
            'bayposition' => 'bay',
            'rowposition' => 'row',
            'tierposition' => 'tier',
            'positionbay' => 'bay',
            'positionrow' => 'row',
            'positiontier' => 'tier',
        ];

        if (isset($mappings[$normalized])) {
            $headerMap[$mappings[$normalized]] = $index;
        }
    }

    Log::info('Excel header mapping', ['mapped_fields' => array_keys($headerMap)]);
    Log::info('Excel Headers Found:', $headers);
    

    return $headerMap;
}

    
 private function mapExcelRowToContainer($row, $headerMap, $headers)
{
    $getValue = function($field) use ($row, $headerMap) {
        if (isset($headerMap[$field])) {
            $value = $row[$headerMap[$field]] ?? null;
            // Perbaikan: Handle "--" sebagai null
            return !empty(trim($value)) && trim($value) !== '--' ? trim($value) : null;
        }
        return null;
    };
    
    $containerNo = $getValue('container_no');
    
    // Reject invalid container numbers - DIPERBAIKI
    if (empty($containerNo) || 
        strpos($containerNo, '|') !== false || 
        strpos(strtoupper($containerNo), 'TOTAL') !== false ||
        strpos(strtolower($containerNo), 'pod') !== false ||
        preg_match('/^\d+\'$/', $containerNo)) {
        return null;
    }
    
    // ✅ Normalize status SEKALI
    $rawStatus = $getValue('status');
    $statusCode = $this->normalizeStatusToCode($rawStatus);
    
    // Parse weight dengan handling untuk nilai 0 atau kosong - DIPERBAIKI
    $rawWeight = $getValue('weight_kg');
    $weightKg = 0;
    if (!empty($rawWeight) && $rawWeight !== '0') {
        $weightKg = $this->parseWeightFromString($rawWeight);
    }
    $bay = $getValue('bay') ?? $getValue('bay_position') ?? $getValue('position_bay') ?? null;
    $rowPos = $getValue('row') ?? $getValue('row_no') ?? $getValue('position_row') ?? null;
    $tier = $getValue('tier') ?? $getValue('tier_no') ?? $getValue('position_tier') ?? null;

    $bay = $bay ? ltrim(trim($bay), '0') : '';
    $rowPos = $rowPos ? ltrim(trim($rowPos), '0') : '';
    $tier = $tier ? ltrim(trim($tier), '0') : '';
    
    $container = [
        'container_no' => strtoupper($containerNo), // Auto uppercase
        'status' => $statusCode,
        'status_code' => $statusCode,
        'status_mapped' => $this->statusCodeToText($statusCode),
        'iso_type' => $getValue('iso_type'),
        'size' => $this->extractSizeFromString($getValue('size') ?? $getValue('iso_type')),
        'type' => $getValue('type') ?: 'DRY',
        'weight_kg' => $weightKg,
        'pol_locode' => $getValue('pol_locode'),
        'pod_locode' => $getValue('pod_locode'),
        'delivery_locode' => $getValue('delivery_locode'),
        'bay' => $bay,
        'row' => $rowPos,
        'tier' => $tier,
        'seal_no' => $getValue('seal_no'),
        'booking_no' => $getValue('booking_no'),
        'bl_no' => $getValue('booking_no'),
        'operator_code' => '',
        'imdg_class' => '',
        'handling_instruction' => '',
        'source' => 'excel',
        'updated_at' => now(),
        // BARU: Additional data untuk field tambahan
        'additional_data' => [
            'arr_ts' => $getValue('arr_ts'),
            'arr_by' => $getValue('arr_by'),
            'stop' => $getValue('stop'),
            'peb_no' => $getValue('peb_no'),
            'item_class' => $getValue('item_class'),
        ]
    ];
    
    // Temperature handling
    $container['set_temperature'] = $this->parseTemperatureFromString($getValue('temp'));
    $container['temperature_unit'] = 'CEL';
    $container['is_reefer'] = $this->isReeferFromData($container);

    if (isset($headerMap['location'])) {
        $rawLocation = $row[$headerMap['location']] ?? null;
        list($bay, $rowPos, $tier) = $this->parseLocation($rawLocation);

        $container['bay'] = $bay;
        $container['row'] = $rowPos;
        $container['tier'] = $tier;
    }
    // ISO enrichment
    if (!empty($container['iso_type'])) {
        $this->processIsoType($container);
    } else {
        $container['jenis'] = $this->deriveJenisFromType($container['type']);
        $container['type_group'] = 'G';
        $container['enhanced_by_iso_mapping'] = false;
    }
    
    // Add weight details if exists - DIPERBAIKI
    if ($container['weight_kg'] > 0) {
        $container['weight_details'] = [[
            'type_code' => 'WT',
            'type_description' => 'Gross Weight',
            'value_kg' => $container['weight_kg']
        ]];
        $container['primary_weight_type'] = 'Gross Weight';
    } else {
        $container['weight_details'] = [];
        $container['primary_weight_type'] = null;
    }
    
    // BARU: Logging untuk debugging
    Log::info('Excel container mapped', [
        'container_no' => $container['container_no'],
        'status' => $container['status_mapped'],
        'weight_kg' => $container['weight_kg'],
        'iso_type' => $container['iso_type']
    ]);
    
    return $container;
}

     private function extractSizeFromString($str)
    {
        if (empty($str)) return null;
        
        if (preg_match('/(\d{2})[A-Z]/', $str, $m)) return $m[1] . "'";
        if (preg_match('/^(\d{2})/', $str, $m)) return $m[1] . "'";
        
        return $str;
    }
    
/**
 * PERBAIKAN: Enhanced size extraction
 */
private function extractSize($sizeStr)
{
    if (empty($sizeStr)) return 'Unknown';
    
    $sizeStr = trim($sizeStr);
    
    // Direct size patterns
    if (preg_match('/20[\'"]?/', $sizeStr)) return "20'";
    if (preg_match('/40[\'"]?/', $sizeStr)) return "40'";
    if (preg_match('/45[\'"]?/', $sizeStr)) return "45'";
    
    // ISO code patterns
    if (preg_match('/^2[2G]/', $sizeStr)) return "20'";
    if (preg_match('/^4[2G5]/', $sizeStr)) return "40'";
    if (preg_match('/^L[5G]/', $sizeStr)) return "45'";
    
    return 'Unknown';
}

private function deriveJenisFromType($type)
{
    if (empty($type)) return 'unknown';
    
    $type = strtoupper($type);
    
    if (strpos($type, 'DRY') !== false || strpos($type, 'GP') !== false) {
        return 'dry';
    } elseif (strpos($type, 'REEFER') !== false || strpos($type, 'RF') !== false) {
        return 'reefer';
    } elseif (strpos($type, 'FLAT') !== false || strpos($type, 'FL') !== false) {
        return 'flat';
    } elseif (strpos($type, 'TANK') !== false || strpos($type, 'TK') !== false) {
        return 'tank';
    } elseif (strpos($type, 'OPEN') !== false || strpos($type, 'OT') !== false) {
        return 'open_top';
    }
    
    return 'dry'; // default
}

/**
 * PERBAIKAN: Derive type group from ISO
 */
private function deriveTypeGroup($iso)
{
    if (empty($iso) || strlen($iso) < 3) return 'G';
    
    $typeChar = substr($iso, 2, 1);
    
    if (in_array($typeChar, ['0', '1', '2', '3'])) {
        return 'G'; // General Purpose
    } elseif (in_array($typeChar, ['4', '5', '6'])) {
        return 'R'; // Reefer
    } elseif (in_array($typeChar, ['7', '8', '9'])) {
        return 'P'; // Platform/Flat
    }
    
    return 'G'; // default
}
 private function parseWeightFromString($str)
{
    if (empty($str)) return 0;
    
    $str = trim($str);
    
    // Handle "0" as zero
    if ($str === '0' || $str === '0.0') return 0;
    
    $cleaned = preg_replace('/[^\d.,]/', '', $str);
    if (empty($cleaned)) return 0;
    
    // Handle different decimal formats
    if (substr_count($cleaned, '.') == 1 && substr_count($cleaned, ',') == 1) {
        $cleaned = str_replace(',', '', $cleaned);
    } elseif (substr_count($cleaned, ',') == 1 && strpos($cleaned, '.') === false) {
        $cleaned = str_replace(',', '.', $cleaned);
    } elseif (substr_count($cleaned, ',') > 1) {
        $cleaned = str_replace(',', '', $cleaned);
    }
    
    $weight = floatval($cleaned);
    
    // Auto-convert tons to kg (only if value seems reasonable)
    if ($weight > 0 && $weight < 100) {
        $weight *= 1000;
    }
    
    return $weight;
}

/**
 * Enhanced weight parsing
 */
private function parseWeight($weightStr)
{
    if (empty($weightStr)) return 0;
    
    // Remove all non-numeric characters except decimal point
    $cleaned = preg_replace('/[^\d.]/', '', trim($weightStr));
    
    if (empty($cleaned)) return 0;
    
    $weight = floatval($cleaned);
    
    // Smart conversion: if weight looks like it's in tons, convert to kg
    if ($weight > 0 && $weight < 500) {  // Likely tons if less than 500
        $weight *= 1000;
    }
    
    return round($weight, 2);
}
    private function parseTemperatureFromString($str)
    {
        if (empty($str)) return null;
        
        $cleaned = preg_replace('/[^\d.-]/', '', trim($str));
        
        return is_numeric($cleaned) ? floatval($cleaned) : null;
    }
    
    private function isReeferFromData($container)
    {
        $isoType = strtoupper($container['iso_type'] ?? '');
        if (strlen($isoType) >= 3) {
            $typeChar = substr($isoType, 2, 1);
            if (in_array($typeChar, ['4', '5', '6'])) {
                return true;
            }
        }
        
        if (!empty($container['set_temperature'])) {
            return true;
        }
        
        $type = strtoupper($container['type'] ?? '');
        if (strpos($type, 'REEFER') !== false || strpos($type, 'RF') !== false) {
            return true;
        }
        
        return false;
    }
    
    private function deriveTypeGroupFromISO($iso)
    {
        if (empty($iso) || strlen($iso) < 3) return 'G';
        
        $typeChar = substr($iso, 2, 1);
        
        if (in_array($typeChar, ['0', '1', '2', '3'])) return 'G';
        if (in_array($typeChar, ['4', '5', '6'])) return 'R';
        if (in_array($typeChar, ['7', '8', '9'])) return 'P';
        
        return 'G';
    }
    private function parseHtmlTableAsExcel($filePath)
    {
        try {
            $html = file_get_contents($filePath);
            
            libxml_use_internal_errors(true);
            $doc = new \DOMDocument();
            $doc->loadHTML($html);
            libxml_clear_errors();
            
            $tables = $doc->getElementsByTagName('table');
            if ($tables->length === 0) {
                throw new \Exception('No tables found in HTML');
            }
            
            $table = $tables->item(0);
            $rows = $table->getElementsByTagName('tr');
            
            $data = [];
            foreach ($rows as $row) {
                $cells = $row->getElementsByTagName('td');
                if ($cells->length === 0) {
                    $cells = $row->getElementsByTagName('th');
                }
                
                $rowData = [];
                foreach ($cells as $cell) {
                    $rowData[] = trim($cell->textContent);
                }
                
                if (!empty($rowData)) {
                    $data[] = $rowData;
                }
            }
            
            if (count($data) < 2) {
                throw new \Exception('HTML table has insufficient data');
            }
            
            $headers = array_shift($data);
            $headerMap = $this->createExcelHeaderMap($headers);
            
            $containers = [];
            foreach ($data as $row) {
                if ($this->isSummaryRow($row)) continue;
                
                try {
                    $container = $this->mapExcelRowToContainer($row, $headerMap, $headers);
                    if (!empty($container['container_no'])) {
                        $containers[] = $container;
                    }
                } catch (\Exception $e) {
                    Log::warning('HTML table row error', ['error' => $e->getMessage()]);
                }
            }
            
            Log::info('HTML table parsed', ['containers' => count($containers)]);
            
            return ['containers' => $containers, 'voyage' => []];
            
        } catch (\Exception $e) {
            Log::error('HTML parsing failed', ['error' => $e->getMessage()]);
            throw $e;
        }
    }
   private function parseAsCSV($filePath)
    {
        try {
            $data = [];
            $handle = fopen($filePath, 'r');
            
            while (($row = fgetcsv($handle)) !== FALSE) {
                $data[] = $row;
            }
            fclose($handle);
            
            if (count($data) < 2) {
                throw new \Exception('CSV has insufficient data');
            }
            
            $headers = array_shift($data);
            $headerMap = $this->createExcelHeaderMap($headers);
            
            $containers = [];
            foreach ($data as $row) {
                if ($this->isSummaryRow($row)) continue;
                
                try {
                    $container = $this->mapExcelRowToContainer($row, $headerMap, $headers);
                    if (!empty($container['container_no'])) {
                        $containers[] = $container;
                    }
                } catch (\Exception $e) {
                    Log::warning('CSV row error', ['error' => $e->getMessage()]);
                }
            }
            
            Log::info('CSV parsed', ['containers' => count($containers)]);
            
            return ['containers' => $containers, 'voyage' => []];
            
        } catch (\Exception $e) {
            Log::error('CSV parsing failed', ['error' => $e->getMessage()]);
            throw $e;
        }
    }
public static function fromExcelData($excelData)
{
    $parser = new self(null);
    $parser->processExcelOnlyData($excelData);
    return $parser;
}

/**
 * Process Excel data when no BAPLIE file is provided
 */
private function processExcelOnlyData($excelData)
{
    // Initialize voyage data with basic structure
    $this->voyageData = [
        'vessel_name' => 'Excel Import',
        'voyage_no' => 'EXCEL_' . date('Ymd'),
        'total_containers' => 0,
        'total_weight_kg' => 0,
        'total_weight_tons' => 0,
        'container_size_breakdown' => [],
        'container_type_breakdown' => [],
        'container_jenis_breakdown' => [],
        'container_status_breakdown' => []
    ];
    
    if (isset($excelData['containers']) && is_array($excelData['containers'])) {
        foreach ($excelData['containers'] as $excelContainer) {
            $container = $this->processExcelContainer($excelContainer);
            $this->addContainer($container);
        }
    }
    
    $this->calculateSummaryStats();
}


    private function processExcelContainer($excelContainer)
    {
        $rawStatus = $excelContainer['status'] ?? '';

        $container = [
            'container_no' => $excelContainer['container_no'] ?? '',
            'iso_type' => $excelContainer['iso_type'] ?? '',
            'status' => $rawStatus,
            'status_code' => $this->mapExcelStatus($rawStatus),
            'status_mapped' => $this->mapExcelStatusToStandard($rawStatus),
            'weight_kg' => is_numeric($excelContainer['weight_kg'] ?? 0) ? floatval($excelContainer['weight_kg']) : 0,
            'weight_details' => [],
            'primary_weight_type' => 'Gross Weight',
            'bay' => $excelContainer['bay'] ?? '',
            'row' => $excelContainer['row'] ?? '',
            'tier' => $excelContainer['tier'] ?? '',
            'pol_locode' => $excelContainer['pol_locode'] ?? '',
            'pod_locode' => $excelContainer['pod_locode'] ?? '',
            'delivery_locode' => $excelContainer['delivery_locode'] ?? '',
            'bl_no' => $excelContainer['booking_no'] ?? '',
            'imdg_class' => '',
            'handling_instruction' => '',
            'operator_code' => '',
            'size' => $excelContainer['size'] ?? 'Unknown',
            'type_group' => 'G',
            'jenis' => 'dry',
            'keterangan' => 'Excel Import',
            'locations' => [],
            'source' => 'excel',
            'additional_data' => $excelContainer['additional_data'] ?? []
        ];

        // ✅ FIX: auto-split posisi kalau kolom bay/row/tier kosong tapi kolom position ada
        if (
            (empty($container['bay']) || empty($container['row']) || empty($container['tier'])) &&
            !empty($excelContainer['position'])
        ) {
            $pos = str_pad(preg_replace('/\D/', '', $excelContainer['position']), 6, '0', STR_PAD_LEFT);
            $container['bay'] = substr($pos, 0, 3);
            $container['row'] = substr($pos, 3, 2);
            $container['tier'] = substr($pos, 5, 2);
        }

        if ($container['weight_kg'] > 0) {
            $container['weight_details'][] = [
                'type_code' => 'VGM',
                'type_description' => 'Verified Gross Mass',
                'value_kg' => $container['weight_kg']
            ];
        }

        return $container;
    }


    
    private function mapExcelStatus($excelStatus)
    {
        return $this->normalizeStatusToCode($excelStatus);
    }

    private function mapExcelStatusToStandard($excelStatus)
    {
        return $this->statusCodeToText($excelStatus);
    }
public function mergeWithExcelData($excelData)
{
    $updatedCount = 0;
    $addedCount = 0;
    $conflictCount = 0;
    $mergeLog = [];
    
    if (!isset($excelData['containers']) || !is_array($excelData['containers'])) {
        return [
            'updated_count' => 0, 
            'added_count' => 0, 
            'conflict_count' => 0,
            'merge_log' => ['No Excel containers to merge']
        ];
    }
    
    foreach ($excelData['containers'] as $excelContainer) {
        $containerNumber = $excelContainer['container_no'] ?? '';
        if (empty($containerNumber)) {
            $mergeLog[] = "Skipped container with empty number";
            continue;
        }
        
        // Find existing container
        $existingIndex = $this->findExistingContainer($containerNumber);
        
        if ($existingIndex !== null) {
            // Update existing container
            $conflicts = $this->updateContainerWithExcelData($existingIndex, $excelContainer);
            if (!empty($conflicts)) {
                $conflictCount++;
                $mergeLog[] = "Container $containerNumber updated with conflicts: " . implode(', ', $conflicts);
            } else {
                $mergeLog[] = "Container $containerNumber updated successfully";
            }
            $updatedCount++;
        } else {
            // Add new container from Excel
            $newContainer = $this->processExcelContainer($excelContainer);
            if ($this->validateContainer($newContainer)) {
                $this->addContainer($newContainer);
                $addedCount++;
                $mergeLog[] = "New container $containerNumber added from Excel";
            } else {
                $mergeLog[] = "Container $containerNumber from Excel failed validation";
            }
        }
    }
    
    // Recalculate stats after merge
    $this->calculateSummaryStats();
    
    return [
        'updated_count' => $updatedCount,
        'added_count' => $addedCount,
        'conflict_count' => $conflictCount,
        'merge_log' => $mergeLog
    ];
}



private function updateContainerWithExcelData($index, $excelContainer)
{
    $container = &$this->containersData[$index];
    $conflicts = [];
    
    // ✅ Field mapping yang konsisten
    $fieldMapping = [
        'weight_kg' => ['priority' => 'excel', 'baplie_field' => 'weight_kg'],
        'status' => ['priority' => 'excel', 'baplie_field' => 'status'],
        'pod_locode' => ['priority' => 'excel', 'baplie_field' => 'pod_locode'],
        'pol_locode' => ['priority' => 'excel', 'baplie_field' => 'pol_locode'],
        'booking_no' => ['priority' => 'excel', 'baplie_field' => 'bl_no'], // ✅ booking → bl_no
        'seal_no' => ['priority' => 'excel', 'baplie_field' => 'seal_no'],
        'bay' => ['priority' => 'merge_if_empty', 'baplie_field' => 'bay'],
        'row' => ['priority' => 'merge_if_empty', 'baplie_field' => 'row'],
        'tier' => ['priority' => 'merge_if_empty', 'baplie_field' => 'tier'],
        'size' => ['priority' => 'baplie', 'baplie_field' => 'size'],
        'iso_type' => ['priority' => 'baplie', 'baplie_field' => 'iso_type'],
    ];
    
    foreach ($fieldMapping as $excelField => $config) {
        $excelValue = $excelContainer[$excelField] ?? null;
        $baplieField = $config['baplie_field'];
        $baplieValue = $container[$baplieField] ?? null;
        $priority = $config['priority'];
        
        if (empty($excelValue)) continue;
        
        switch ($priority) {
            case 'excel':
                if (!empty($baplieValue) && $baplieValue != $excelValue) {
                    $conflicts[] = "$excelField: '$baplieValue' -> '$excelValue'";
                }
                
                // Special handling for weight
                if ($excelField === 'weight_kg' && is_numeric($excelValue)) {
                    $oldWeight = floatval($container['weight_kg'] ?? 0);
                    $newWeight = floatval($excelValue);
                    
                    // Update total weight tracker
                    if ($oldWeight > 0) {
                        $this->totalWeight -= $oldWeight;
                        $this->containersWithWeight--;
                    }
                    if ($newWeight > 0) {
                        $this->totalWeight += $newWeight;
                        $this->containersWithWeight++;
                    }
                    
                    $container['weight_kg'] = $newWeight;
                    $container['weight_details'] = array_merge(
                        $container['weight_details'] ?? [],
                        [[
                            'type_code' => 'WT_EXCEL',
                            'type_description' => 'Gross Weight (Excel Override)',
                            'value_kg' => $newWeight
                        ]]
                    );
                    $container['primary_weight_type'] = 'Gross Weight (Excel Override)';
                }
                // Special handling for status
                elseif ($excelField === 'status') {
                    $statusCode = $this->normalizeStatusToCode($excelValue);
                    $container['status'] = $statusCode;
                    $container['status_code'] = $statusCode;
                    $container['status_mapped'] = $this->statusCodeToText($statusCode);
                } 
                else {
                    $container[$baplieField] = $excelValue;
                }
                break;
                
            case 'baplie':
                if (empty($baplieValue)) {
                    $container[$baplieField] = $excelValue;
                } elseif ($baplieValue != $excelValue) {
                    $conflicts[] = "$excelField: kept BAPLIE '$baplieValue' over Excel '$excelValue'";
                }
                break;
                
            case 'merge_if_empty':
                if (empty($baplieValue) || $baplieValue === '0' || $baplieValue === '') {
                    $container[$baplieField] = $excelValue;
                }
                break;
        }
    }
    
    // Mark as updated
    $container['updated_from_excel'] = true;
    $container['excel_update_timestamp'] = date('Y-m-d H:i:s');
    $container['keterangan'] = ($container['keterangan'] ?? '') . ' [Updated from Excel]';
    
    // Merge additional data
    if (isset($excelContainer['additional_data']) && is_array($excelContainer['additional_data'])) {
        $container['additional_data'] = array_merge(
            $container['additional_data'] ?? [],
            $excelContainer['additional_data']
        );
    }
    
    return $conflicts;
}



private function findExistingContainer($containerNumber)
{
    $containerNumber = trim(strtoupper($containerNumber));
    
    // Exact match first
    foreach ($this->containersData as $index => $container) {
        $existingNumber = trim(strtoupper($container['container_no'] ?? ''));
        if ($existingNumber === $containerNumber) {
            return $index;
        }
    }
    
    // Fuzzy match for minor typos
    foreach ($this->containersData as $index => $container) {
        $existingNumber = trim(strtoupper($container['container_no'] ?? ''));
        if (!empty($existingNumber) && levenshtein($existingNumber, $containerNumber) <= 2) {
            Log::warning("Fuzzy match found", ['existing' => $existingNumber, 'input' => $containerNumber]);
            return $index;
        }
    }
    
    return null;
}
/**
 * PERBAIKAN: Map field names dengan konsistensi yang lebih baik
 */
private function mapFieldName($excelField)
{
    $mapping = [
        'weight_kg' => 'weight_kg',
        'status' => 'status',
        'size' => 'size',
        'iso_type' => 'iso_type', 
        'pod_locode' => 'pod_locode',
        'pol_locode' => 'pol_locode',
        'delivery_locode' => 'delivery_locode',
        'bay' => 'bay',
        'row' => 'row',
        'tier' => 'tier',
        'booking_no' => 'bl_no',  // Excel booking -> BAPLIE bl_no
        'seal_no' => 'seal_no',
        'peb_no' => 'peb_no',
        'item_class' => 'item_class'
    ];
    
    return $mapping[$excelField] ?? $excelField;
}

/**
 * Update container field dengan handling khusus per type
 */
private function updateContainerField(&$container, $fieldName, $value, $originalField)
{
    if ($originalField === 'weight_kg' && is_numeric($value)) {
        $container[$fieldName] = floatval($value);
    } elseif ($originalField === 'status') {
        $container['status'] = $value; // Original value
        $container['status_code'] = $this->mapExcelStatus($value); // Kode numerik
        $container['status_mapped'] = $this->mapExcelStatusToStandard($value); // Text
    } else {
        $container[$fieldName] = $value;
    }
}

private function validateContainer($container): bool
{
    // Required fields
    if (empty($container['container_no'])) {
        return false;
    }
    
    // Container number format validation
    if (!preg_match('/^[A-Z]{3,4}[0-9U]{6,7}$/', $container['container_no'])) {
        Log::warning("Unusual container number format", ['container_no' => $container['container_no']]);
        // Don't reject, just log
    }
    
    // Weight validation
    $weight = floatval($container['weight_kg'] ?? 0);
    if ($weight < 0 || $weight > 50000) {
        Log::warning("Suspicious weight", [
            'container_no' => $container['container_no'],
            'weight_kg' => $weight
        ]);
        // Don't reject, just log
    }
    
    return true;
}

    private function mapLocationQualifier($code)
    {
        $map = [
            '5'   => 'Place of Loading',
            '6'   => 'Port of Call (Discharge)',
            '9'   => 'Port of Loading (Container)',
            '11'  => 'Port of Discharge',
            '12'  => 'Alternative Port of Loading',
            '60'  => 'Port of Departure',
            '61'  => 'Port of Arrival',
            '83'  => 'Place of Delivery',
            '88'  => 'Place of Receipt',
            '147' => 'Stowage Location',
        ];
        return $map[$code] ?? 'Unknown Location';
    }
    
private function parseContainerSegments($segment, &$currentContainer)
{
    // TAMBAHAN: Parse EQD+CN+ untuk mendapatkan data container
    if (preg_match('/^EQD\+CN\+([^+]*)\+?/', $segment, $matches)) {
        $parts = explode('+', $segment);
        $containerNo = trim($parts[2] ?? '');
        $isoType = trim($parts[3] ?? '');
        $statusCode = trim($parts[6] ?? '');
        
        if (!empty($containerNo)) {
            $currentContainer['container_no'] = $containerNo;
            $currentContainer['iso_type'] = $isoType;
            
            // Map status code
            if (!empty($statusCode) && in_array($statusCode, ['5','4'])) {
                $currentContainer['status'] = $statusCode;
                $currentContainer['status_code'] = $statusCode;
                $currentContainer['status_mapped'] = $this->mapContainerStatus($statusCode);
            } else {
                $currentContainer['status'] = 'U';
                $currentContainer['status_code'] = 'U';
                $currentContainer['status_mapped'] = 'Unknown';
            }
            
            error_log("EQD parsed for container: $containerNo (ISO: $isoType, Status: $statusCode)");
        }
    }
    
    // Location position - HAPUS yang LOC+147 karena sudah ditangani di parseFile()
    // Hanya tangani LOC segment lainnya (bukan 147)
    elseif (strpos($segment, 'LOC+') === 0 && strpos($segment, 'LOC+147+') !== 0) {
        $this->parseLocationSegment($segment, $currentContainer);
    }
    
    // MEA segments with KGM
    elseif (strpos($segment, 'MEA') === 0 && strpos($segment, 'KGM:') !== false) {
        $parts = explode('+', $segment);
        $typeCode = $parts[1] ?? 'UNK';

        $parsedWeight = 0;
        if (preg_match('/KGM:(\d+)/', $segment, $matches)) {
            $parsedWeight = (float) $matches[1];
        }

        if ($parsedWeight > 0) {
            $currentContainer['weight_kg'] = $parsedWeight;
            $currentContainer['weight_details'][] = [
                'type_code' => $typeCode,
                'type_description' => $this->mapWeightType($typeCode),
                'value_kg' => $parsedWeight
            ];
            $currentContainer['primary_weight_type'] = $this->mapWeightType($typeCode);
        }
    }
    // B/L Reference
    elseif (strpos($segment, 'RFF+BM:') === 0) {
        $parts = explode(':', $segment);
        $currentContainer['bl_no'] = str_replace("'", "", $parts[1] ?? '');
    }
    // NAD segment (Operator)
    // NAD segment (Operator)
    elseif (strpos($segment, 'NAD+CA+') === 0) {
        $parts = explode('+', $segment);

        if (isset($parts[2])) {
            $operatorInfo = explode(':', $parts[2]);
            $operator = $operatorInfo[0] ?? 'Unknown';

            // Simpan ke container sekarang
            $currentContainer['operator_code'] = $operator;

            // ✅ SET DEFAULT NAD GLOBAL JIKA BELUM ADA
            if (!session()->has('default_nad') && !empty($operator) && $operator !== 'Unknown') {
                session()->put('default_nad', $operator);
                logger("✅ DEFAULT NAD SET FROM BAPLIE: $operator");
            }
        }
    }

    // FTX Handling instructions
    elseif (strpos($segment, 'FTX+HAN+++') === 0) {
        $parts = explode('+++', $segment);
        $currentContainer['handling_instruction'] = $parts[1] ?? '';
    }
    // FTX Additional Information
    elseif (strpos($segment, 'FTX+AAY++') === 0) {
        $parts = explode('+', $segment);
        if (isset($parts[3])) {
            $currentContainer['additional_info'] = $parts[3];
        }
    }
    // Dangerous Goods
    elseif (strpos($segment, 'DGS+IMD+') === 0) {
        $parts = explode('+', $segment);
        $currentContainer['imdg_class'] = $parts[2] ?? '';
    }
    // Temperature segments untuk reefer containers
    elseif (strpos($segment, 'TMP+') === 0) {
        $this->parseTemperatureSegment($segment, $currentContainer);
    }
    // Range segments
    elseif (strpos($segment, 'RNG+') === 0) {
        $this->parseRangeSegment($segment, $currentContainer);
    }
}



private function addContainer($container)
{
    // ✅ Normalize status sebelum log
    $statusCode = $this->normalizeStatusToCode($container['status_code'] ?? $container['status'] ?? 'U');
    $statusText = $this->statusCodeToText($statusCode);
    
    Log::info("Adding container", [
        'container_no' => $container['container_no'],
        'status_code' => $statusCode,
        'status_text' => $statusText,
        'weight_kg' => $container['weight_kg'] ?? 0
    ]);
    
    // ✅ Ensure status fields are consistent
    $container['status'] = $statusCode;
    $container['status_code'] = $statusCode;
    $container['status_mapped'] = $statusText;
    
    // Process ISO Type using unified method
    $this->processIsoType($container);
    
    // ✅ Ensure reefer fields exist
    if (!isset($container['is_reefer'])) {
        $container['is_reefer'] = false;
    }
    if (!isset($container['set_temperature'])) {
        $container['set_temperature'] = null;
    }
    if (!isset($container['temperature_unit'])) {
        $container['temperature_unit'] = 'CEL';
    }
    if (!isset($container['temperature_settings'])) {
        $container['temperature_settings'] = [];
    }
    if (!isset($container['temperature_range'])) {
        $container['temperature_range'] = null;
    }
    
    // Auto-detect reefer from ISO type if not already set
    if (!$container['is_reefer'] && !empty($container['iso_type'])) {
        $isoType = strtoupper($container['iso_type']);
        if (strlen($isoType) >= 3) {
            $typeChar = substr($isoType, 2, 1);
            if (in_array($typeChar, ['4', '5', '6'])) {
                $container['is_reefer'] = true;
                $container['jenis'] = 'reefer';
            }
        }
    }
    
    // Add to containers array
    $this->containersData[] = $container;
    
    // Update location counters
    $this->updateLocationCounters($container);
    
    // Update totals
    $this->containerCount++;
    
    // ✅ Only count weight if > 0
    $weight = floatval($container['weight_kg'] ?? 0);
    if ($weight > 0) {
        $this->totalWeight += $weight;
        $this->containersWithWeight++;
    }
}
    
 private function processIsoType(&$container)
{
    $isoType = $container['iso_type'] ?? '';
    
    if (empty($isoType)) {
        $this->setDefaultIsoValues($container);
        return;
    }
    if (isset($this->isoMap[$isoType])) {
        $this->applyIsoMapping($container, $this->isoMap[$isoType], $isoType);
        return;
    }
    if (isset($this->oldToNewMap[$isoType])) {
        $newCode = $this->oldToNewMap[$isoType];
        $container['iso_type'] = $newCode;
        $this->applyIsoMapping($container, $this->isoMap[$newCode], $newCode);
        Log::info("ISO old code mapped", ['old' => $isoType, 'new' => $newCode]);
        return;
    }
    foreach ($this->isoMap as $code => $details) {
        if (levenshtein($isoType, $code) <= 1) {
            $this->applyIsoMapping($container, $details, $code);
            Log::warning("ISO fuzzy match", ['input' => $isoType, 'matched' => $code]);
            return;
        }
    }
    $this->parseBasicIsoType($container, $isoType);
}
    
   private function applyIsoMapping(&$container, $details, $isoType)
    {
        $container['size'] = $this->normalizeSize($details['size'] ?? 'Unknown');
        $container['type_group'] = $details['type_group'] ?? 'G';
        $container['jenis'] = $this->normalizeJenis($details['jenis'] ?? 'dry');
        $container['keterangan'] = ($container['keterangan'] ?? '') . ' [ISO: ' . $isoType . ']';
        $container['enhanced_by_iso_mapping'] = true;
        
        // Auto-detect reefer
        if ($container['jenis'] === 'reefer') {
            $container['is_reefer'] = true;
        }
    }

    private function normalizeSize($size)
    {
        if (strpos($size, "20'") !== false) return "20'";
        if (strpos($size, "40'") !== false) return "40'";
        if (strpos($size, "45'") !== false) return "45'";
        return $size;
    }

    private function normalizeJenis($jenis): string
    {
        $jenis = strtolower($jenis);
        
        if (str_contains($jenis, 'general') || str_contains($jenis, 'dry')) return 'dry';
        if (str_contains($jenis, 'reefer') || str_contains($jenis, 'refrigerat')) return 'reefer';
        if (str_contains($jenis, 'flat') || str_contains($jenis, 'rack')) return 'flat';
        if (str_contains($jenis, 'tank')) return 'tank';
        if (str_contains($jenis, 'open')) return 'open_top';
        
        return 'dry';
    }

        private function setDefaultIsoValues(&$container)
    {
        $container['size'] = 'Unknown';
        $container['type_group'] = 'G';
        $container['jenis'] = 'unknown';
        $container['enhanced_by_iso_mapping'] = false;
    }
    
    private function parseBasicIsoType(&$container, $isoType)
    {
        // Determine size from first char
        $firstChar = substr($isoType, 0, 1);
        $container['size'] = match($firstChar) {
            '2' => "20'",
            '4' => "40'",
            'L' => "45'",
            default => 'Unknown'
        };
        
        // Determine jenis and type_group from third char
        if (strlen($isoType) >= 3) {
            $typeChar = substr($isoType, 2, 1);
            
            if (in_array($typeChar, ['0', '1', '2', '3'])) {
                $container['type_group'] = 'G';
                $container['jenis'] = 'dry';
            } elseif (in_array($typeChar, ['4', '5', '6'])) {
                $container['type_group'] = 'R';
                $container['jenis'] = 'reefer';
                $container['is_reefer'] = true;
            } elseif (in_array($typeChar, ['7', '8', '9'])) {
                $container['type_group'] = 'P';
                $container['jenis'] = 'flat';
            } else {
                $container['type_group'] = 'G';
                $container['jenis'] = 'dry';
            }
        } else {
            $container['type_group'] = 'G';
            $container['jenis'] = $this->deriveJenisFromStatus($container);
        }
        
        $container['keterangan'] = "Fallback parsing: $isoType";
        $container['enhanced_by_iso_mapping'] = false;
    }
    private function deriveJenisFromStatus(&$container): string
    {
        $status = $this->normalizeStatusToCode($container['status'] ?? '');
        return ($status === '4') ? 'empty' : 'dry';
    }
    
    public function debugIsoMapping()
    {
        
        $uniqueIsoTypes = [];
        foreach ($this->containersData as $container) {
            $iso = $container['iso_type'] ?? '';
            if (!empty($iso) && !isset($uniqueIsoTypes[$iso])) {
                $uniqueIsoTypes[$iso] = 0;
            }
            if (!empty($iso)) {
                $uniqueIsoTypes[$iso]++;
            }
        }
        
        error_log("Unique ISO types in data:");
        foreach ($uniqueIsoTypes as $iso => $count) {
            $mapped = isset($this->isoMap[$iso]) ? 'MAPPED' : 'NOT MAPPED';
            error_log("  '$iso': $count containers - $mapped");
        }
        
        return [
            'total_mappings' => count($this->isoMap),
            'unique_iso_types' => $uniqueIsoTypes
        ];
    }
    
    private function updateLocationCounters($container)
    {
        $locations = [
            'pol_locode' => '9',
            'pod_locode' => '11',
            'delivery_locode' => '83'
        ];
        
        foreach ($locations as $field => $qualifier) {
            $locationCode = $container[$field];
            if (!empty($locationCode)) {
                $locationKey = $qualifier . '_' . $locationCode;
                if (isset($this->locationsData[$locationKey])) {
                    $this->locationsData[$locationKey]['containers_count']++;
                }
            }
        }
    }

    private function mapWeightType($code)
    {
        $map = [
            'WT'  => 'Gross Weight',
            'VGM' => 'Verified Gross Mass',
            'AAE' => 'Net Weight',
            'T'   => 'Tare Weight'
        ];
        return $map[$code] ?? 'Unknown Weight Type';
    }
    




private function calculateSummaryStats()
{
    $this->voyageData['total_containers'] = $this->containerCount;
    $this->voyageData['total_weight_kg'] = $this->totalWeight;
    $this->voyageData['total_weight_tons'] = round($this->totalWeight / 1000, 2);
    $this->voyageData['containers_with_weight'] = $this->containersWithWeight;
    $this->voyageData['average_weight_kg'] = $this->containersWithWeight > 0 
        ? round($this->totalWeight / $this->containersWithWeight, 2) 
        : 0;
    
    $sizeBreakdown = [];
    $typeBreakdown = [];
    $jenisBreakdown = [];
    
    // ✅ PERBAIKAN: Status breakdown dengan STRICT keys
    $statusBreakdown = [
        '5' => ['count' => 0, 'weight_kg' => 0],
        '4' => ['count' => 0, 'weight_kg' => 0],
        'U' => ['count' => 0, 'weight_kg' => 0]
    ];
    
    // ✅ CRITICAL: Normalize dan validate semua status
    $invalidStatusContainers = [];
        
    foreach ($this->containersData as $index => $container) {
        // ✅ Normalize STRICTLY
        $rawStatus = $container['status_code'] ?? $container['status'] ?? 'U';
        $statusCode = $this->normalizeStatusToCode($rawStatus);
        
        // ✅ CRITICAL: Update container jika status tidak normalized
        if ($rawStatus !== $statusCode) {
            $this->containersData[$index]['status'] = $statusCode;
            $this->containersData[$index]['status_code'] = $statusCode;
            $this->containersData[$index]['status_mapped'] = $this->statusCodeToText($statusCode);
            
            Log::info('Status normalized in parser', [
                'container_no' => $container['container_no'] ?? 'Unknown',
                'old_status' => $rawStatus,
                'new_status' => $statusCode
            ]);
        }
        
        // ✅ ONLY count if valid status
        if (!isset($statusBreakdown[$statusCode])) {
            // Ini seharusnya tidak pernah terjadi setelah normalisasi
            Log::error('Invalid status code after normalization!', [
                'container_no' => $container['container_no'] ?? 'Unknown',
                'status_code' => $statusCode,
                'raw_status' => $rawStatus
            ]);
            
            // Force ke Unknown
            $statusCode = 'U';
            $this->containersData[$index]['status'] = 'U';
            $this->containersData[$index]['status_code'] = 'U';
            $this->containersData[$index]['status_mapped'] = 'Unknown';
            
            $invalidStatusContainers[] = $container['container_no'] ?? 'Unknown_' . $index;
        }
        
        $statusBreakdown[$statusCode]['count']++;
        
        // ✅ Weight - hanya hitung jika > 0
        $weight = floatval($container['weight_kg'] ?? 0);
        if ($weight > 0) {
            $statusBreakdown[$statusCode]['weight_kg'] += $weight;
        }

        // Size breakdown
        $size = $container['size'] ?? 'Unknown';
        if (!isset($sizeBreakdown[$size])) {
            $sizeBreakdown[$size] = ['count' => 0, 'weight_kg' => 0];
        }
        $sizeBreakdown[$size]['count']++;
        $sizeBreakdown[$size]['weight_kg'] += $weight;

        // Type breakdown
        $type = $container['type_group'] ?? 'Unknown';
        if (!isset($typeBreakdown[$type])) {
            $typeBreakdown[$type] = ['count' => 0, 'weight_kg' => 0];
        }
        $typeBreakdown[$type]['count']++;
        $typeBreakdown[$type]['weight_kg'] += $weight;

        // Jenis breakdown
        $jenis = $container['jenis'] ?? 'Unknown';
        if (!isset($jenisBreakdown[$jenis])) {
            $jenisBreakdown[$jenis] = ['count' => 0, 'weight_kg' => 0];
        }
        $jenisBreakdown[$jenis]['count']++;
        $jenisBreakdown[$jenis]['weight_kg'] += $weight;
    }
    
    // ✅ Verification LOG
    $totalCounted = $statusBreakdown['5']['count'] + $statusBreakdown['4']['count'] + $statusBreakdown['U']['count'];
    if ($totalCounted !== $this->containerCount) {
        Log::error('Status breakdown count mismatch!', [
            'expected' => $this->containerCount,
            'counted' => $totalCounted,
            'breakdown' => $statusBreakdown,
            'invalid_containers' => $invalidStatusContainers
        ]);
    } else {
        Log::info('Status breakdown verified OK', [
            'total' => $this->containerCount,
            'full' => $statusBreakdown['5']['count'],
            'empty' => $statusBreakdown['4']['count'],
            'unknown' => $statusBreakdown['U']['count']
        ]);
    }

    $this->voyageData['container_size_breakdown'] = $sizeBreakdown;
    $this->voyageData['container_type_breakdown'] = $typeBreakdown;
    $this->voyageData['container_jenis_breakdown'] = $jenisBreakdown;
    $this->voyageData['container_status_breakdown'] = $statusBreakdown;

    // Reefer summary
    $reefers = $this->getReeferContainers();
    $this->voyageData['reefer_summary'] = [
        'total_reefers' => count($reefers),
        'reefers_with_temp_settings' => count(array_filter($reefers, function($c) {
            return !empty($c['set_temperature']);
        })),
        'reefer_statistics' => $this->getReeferStatistics()
    ];
}

        private function formatDate($dateStr, $formatCode)
    {
        if (empty($dateStr)) {
            return '';
        }

        if ($formatCode === '101' && strlen($dateStr) >= 6) {
            $year = '20' . substr($dateStr, 0, 2);
            $month = substr($dateStr, 2, 2);
            $day = substr($dateStr, 4, 2);
            return "$year-$month-$day";
        } 
        
        elseif ($formatCode === '201' && strlen($dateStr) >= 10) {
            $year = '20' . substr($dateStr, 0, 2);
            $month = substr($dateStr, 2, 2);
            $day = substr($dateStr, 4, 2);
            $hour = substr($dateStr, 6, 2);
            $minute = substr($dateStr, 8, 2);
            return "$year-$month-$day $hour:$minute";
        }
        
        elseif ($formatCode === '203') {
            if (strlen($dateStr) === 12) {
                // CCYYMMDDHHMM (202505121745)
                $year = substr($dateStr, 0, 4);
                $month = substr($dateStr, 4, 2);
                $day = substr($dateStr, 6, 2);
                $hour = substr($dateStr, 8, 2);
                $minute = substr($dateStr, 10, 2);
                return "$year-$month-$day $hour:$minute";
            } elseif (strlen($dateStr) === 10) {
                $year = '20' . substr($dateStr, 0, 2);
                $month = substr($dateStr, 2, 2);
                $day = substr($dateStr, 4, 2);
                $hour = substr($dateStr, 6, 2);
                $minute = substr($dateStr, 8, 2);
                return "$year-$month-$day $hour:$minute";
            }
        }

        return $dateStr;
    }
    
    // Public getter methods
    public function getVoyageData()
    {
        return $this->voyageData;
    }
    
    public function getContainersData()
    {
        return $this->containersData;
    }
    
    public function getLocationsData()
    {
        return $this->locationsData;
    }
    
    public function getTotalWeight()
    {
        return $this->totalWeight;
    }
    
    public function getTotalWeightInTons()
    {
        return round($this->totalWeight / 1000, 2);
    }
    
    public function getContainerCount()
    {
        return $this->containerCount;
    }
    
    public function getSummaryReport()
    {
        return [
            'voyage_info' => [
                'voyage_no' => $this->voyageData['voyage_no'] ?? '',
                'vessel_name' => $this->voyageData['vessel_name'] ?? '',
                'departure_port' => $this->voyageData['port_of_departure'] ?? '',
                'discharge_port' => $this->voyageData['port_of_discharge'] ?? '',
                'departure_date' => $this->voyageData['departure_date'] ?? '',
                'arrival_date' => $this->voyageData['arrival_date'] ?? ''   
            ],
            'container_summary' => [
                'total_containers' => $this->containerCount,
                'containers_with_weight' => $this->containersWithWeight,
                'total_weight_kg' => $this->totalWeight,
                'total_weight_tons' => round($this->totalWeight / 1000, 2),
                'size_breakdown' => $this->voyageData['container_size_breakdown'] ?? [],
                'type_breakdown' => $this->voyageData['container_type_breakdown'] ?? [],
                'jenis_breakdown' => $this->voyageData['container_jenis_breakdown'] ?? [],
                'status_breakdown' => $this->voyageData['container_status_breakdown'] ?? []
            ],
            'locations_summary' => $this->locationsData
        ];
    }
    
    // Method to get containers by specific criteria
    public function getContainersByPort($portCode, $portType = 'pol')
    {
        $field = $portType . '_locode';
        return array_filter($this->containersData, function($container) use ($field, $portCode) {
            return $container[$field] === $portCode;
        });
    }
    
    public function getContainersBySize($size)
    {
        return array_filter($this->containersData, function($container) use ($size) {
            return $container['size'] === $size;
        });
    }
    
    public function getContainersByType($type)
    {
        return array_filter($this->containersData, function($container) use ($type) {
            return $container['type_group'] === $type;
        });
    }
    
    public function getContainersByJenis($jenis)
    {
        return array_filter($this->containersData, function($container) use ($jenis) {
            return $container['jenis'] === $jenis;
        });
    }
    
    public function getContainersByStatus($status)
    {
        return array_filter($this->containersData, function($container) use ($status) {
            return $container['status_mapped'] === $status;
        });
    }
    
    // Method to get weight statistics
    public function getWeightStatistics()
    {
        $stats = [
            'total_weight_kg' => $this->totalWeight,
            'total_weight_tons' => round($this->totalWeight / 1000, 2),
            'average_weight_kg' => $this->containerCount > 0 ? round($this->totalWeight / $this->containerCount, 2) : 0,
            'weight_types_used' => [],
            'containers_by_weight_type' => []
        ];
        
        foreach ($this->containersData as $container) {
            $primaryType = $container['primary_weight_type'] ?? 'Unknown';
            
            if (!isset($stats['containers_by_weight_type'][$primaryType])) {
                $stats['containers_by_weight_type'][$primaryType] = [
                    'count' => 0,
                    'total_weight_kg' => 0
                ];
            }
            
            $stats['containers_by_weight_type'][$primaryType]['count']++;
            $stats['containers_by_weight_type'][$primaryType]['total_weight_kg'] += $container['weight_kg'];
            
            // Collect all weight types used
            foreach ($container['weight_details'] as $weightDetail) {
                $weightType = $weightDetail['type_description'];
                if (!in_array($weightType, $stats['weight_types_used'])) {
                    $stats['weight_types_used'][] = $weightType;
                }
            }
        }
        
        return $stats;
    }
    
    // Method to get containers with multiple weight measurements
    public function getContainersWithMultipleWeights()
    {
        return array_filter($this->containersData, function($container) {
            return count($container['weight_details']) > 1;
        });
    }
    
    // PERBAIKAN: Method untuk mendapatkan container bermasalah
    public function getProblematicContainers()
    {
        $problematic = [];
        
        foreach ($this->containersData as $container) {
            $issues = [];
            
            // Check untuk container dengan nomor pendek
            if (strlen($container['container_no']) < 10) {
                $issues[] = 'Short container number';
            }
            
            // Check untuk container dengan status unknown
            if ($container['status_mapped'] === 'Unknown') {
                $issues[] = 'Unknown status: ' . ($container['status_code'] ?? 'empty');
            }
            
            // Check untuk container dengan ISO type kosong atau aneh
            if (empty($container['iso_type']) || strlen($container['iso_type']) < 3) {
                $issues[] = 'Invalid/missing ISO type';
            }
            
            // Check untuk container tanpa weight
            if ($container['weight_kg'] <= 0) {
                $issues[] = 'No weight data';
            }
            
            if (!empty($issues)) {
                $problematic[] = [
                    'container_no' => $container['container_no'],
                    'iso_type' => $container['iso_type'],
                    'status_code' => $container['status_code'] ?? '',
                    'status_mapped' => $container['status_mapped'],
                    'weight_kg' => $container['weight_kg'],
                    'issues' => $issues,
                    'segment_debug' => $container['segment_debug'] ?? ''
                ];
            }
        }
        
        return $problematic;
    }
    
    // Method untuk get breakdown dengan lebih detail
    public function getDetailedStatusBreakdown()
    {
        $breakdown = [
            'Full' => ['count' => 0, 'weight_kg' => 0, 'containers' => []],
            'Empty' => ['count' => 0, 'weight_kg' => 0, 'containers' => []],
            'Unknown' => ['count' => 0, 'weight_kg' => 0, 'containers' => []]
        ];
        
        foreach ($this->containersData as $container) {
            $status = $container['status_mapped'];
            
            if (isset($breakdown[$status])) {
                $breakdown[$status]['count']++;
                $breakdown[$status]['weight_kg'] += $container['weight_kg'];
                
                // Simpan sample container untuk debugging
                if (count($breakdown[$status]['containers']) < 5) {
                    $breakdown[$status]['containers'][] = [
                        'container_no' => $container['container_no'],
                        'status_code' => $container['status_code'] ?? '',
                        'weight_kg' => $container['weight_kg']
                    ];
                }
            }
        }
        
        return $breakdown;
    }
    
    private function getUniqueIsoTypes()
    {
        $types = [];
        foreach ($this->containersData as $container) {
            $iso = $container['iso_type'] ?? 'empty';
            $types[$iso] = ($types[$iso] ?? 0) + 1;
        }
        return $types;
    }
    
    private function parseTemperatureSegment($segment, &$currentContainer)
    {
        // Contoh format: TMP+2+-20.0:CEL' atau TMP+2+CEL:12.0'
        $parts = explode('+', $segment);
        
        if (count($parts) >= 3) {
            $tempType = $parts[1]; 
            $tempDataString = $parts[2]; // Contoh: '-20.0:CEL'
            
            $tempData = explode(':', $tempDataString);
            
            $unit = 'CEL'; // Satuan default jika tidak ditemukan
            $value = '';
            
            // Cek jika ada pemisah titik dua (:)
            if (count($tempData) == 2) {
                // Logika cerdas: Tentukan mana angka (nilai) dan mana string (unit)
                
                // Kasus 1: Nilai di depan, Unit di belakang (misal: -20.0:CEL)
                if (is_numeric($tempData[0])) {
                    $value = $tempData[0];
                    $unit = $tempData[1] ?? 'CEL';
                } 
                // Kasus 2: Unit di depan, Nilai di belakang (misal: CEL:12.0)
                elseif (is_numeric($tempData[1])) {
                    $value = $tempData[1];
                    $unit = $tempData[0] ?? 'CEL';
                }
            } else {
                // Kasus tanpa pemisah (misal: TMP+2+12.0)
                $value = $tempDataString;
            }
            
            if (is_numeric($value)) {
                if (!isset($currentContainer['temperature_settings'])) {
                    $currentContainer['temperature_settings'] = [];
                }
                
                $tempSetting = [
                    'type' => $this->mapTemperatureType($tempType),
                    'value' => floatval($value),
                    'unit' => strtoupper($unit), // Pastikan unit selalu UPPERCASE
                    'raw_segment' => $segment
                ];
                
                $currentContainer['temperature_settings'][] = $tempSetting;
                
                // Set primary temperature (Biasanya type '2' adalah Set Temperature)
                if ($tempType === '2') {
                    $currentContainer['set_temperature'] = floatval($value);
                    $currentContainer['temperature_unit'] = strtoupper($unit);
                }
                
                // Mark sebagai reefer container (Penting!)
                $currentContainer['is_reefer'] = true;
                $currentContainer['jenis'] = 'reefer';
                
                error_log("Temperature parsed for {$currentContainer['container_no']}: {$value}°{$unit} ({$this->mapTemperatureType($tempType)})");
            }
        }
    }

private function parseRangeSegment($segment, &$currentContainer)
{
    // RNG+5+CEL:-18:22' format (min:max temperature range)
    $parts = explode('+', $segment);
    if (count($parts) >= 3) {
        $rangeType = $parts[1]; // 5 = temperature range
        $rangeData = explode(':', $parts[2]);
        $unit = $rangeData[0] ?? 'CEL';
        $minTemp = $rangeData[1] ?? '';
        $maxTemp = $rangeData[2] ?? '';
        
        if (is_numeric($minTemp) && is_numeric($maxTemp)) {
            $currentContainer['temperature_range'] = [
                'min' => floatval($minTemp),
                'max' => floatval($maxTemp),
                'unit' => $unit,
                'raw_segment' => $segment
            ];
            
            $currentContainer['is_reefer'] = true;
            $currentContainer['jenis'] = 'reefer';
            
            error_log("Temperature range parsed for {$currentContainer['container_no']}: {$minTemp}°{$unit} to {$maxTemp}°{$unit}");
        }
    }
}

private function mapTemperatureType($typeCode)
{
    $map = [
        '2' => 'Set Temperature',
        '3' => 'Supply Air Temperature', 
        '4' => 'Return Air Temperature',
        '5' => 'Ambient Temperature'
    ];
    
    return $map[$typeCode] ?? 'Unknown Temperature Type';
}



// Method baru untuk mendapatkan reefer containers
public function getReeferContainers()
{
    return array_filter($this->containersData, function($container) {
        return $container['is_reefer'] ?? false;
    });
}
private function convertToCelcius($value, $unit)
{
    if (strtoupper($unit) === 'FAH') {
        // Rumus: C = (F - 32) * 5/9
        return ($value - 32) * (5 / 9);
    }
    return $value;
}

private function groupTemperature($temp)
{
    if ($temp >= 15) return 'Chilled (15°C+)';
    if ($temp >= 2) return 'Fresh (2-14°C)';
    if ($temp >= -18) return 'Frozen (-18 to 1°C)';
    return 'Deep Frozen (<-18°C)';
}
public function getReeferStatistics()
{
    $reefers = $this->getReeferContainers();
    $stats = [
        'total_reefers' => count($reefers),
        'with_temperature_set' => 0,
        'with_temperature_range' => 0,
        'temperature_distribution' => [],
        'average_set_temperature' => null,
        'temperature_ranges_used' => []
    ];
    
    $temperaturesCelcius = []; // Array untuk menyimpan semua suhu SETELAH konversi
    
    foreach ($reefers as $container) {
        if (!empty($container['set_temperature'])) {
            $stats['with_temperature_set']++;
            
            $temp = $container['set_temperature'];
            // Ambil unit suhu, default ke CEL jika tidak ada
            $unit = $container['temperature_unit'] ?? 'CEL'; 
            
            // *** KOREKSI KRITIS: Konversi suhu ke Celsius ***
            $tempCelcius = $this->convertToCelcius($temp, $unit);

            $temperaturesCelcius[] = $tempCelcius;
            
            // Group temperatures menggunakan nilai Celsius
            $tempGroup = $this->groupTemperature($tempCelcius);
            
            if (!isset($stats['temperature_distribution'][$tempGroup])) {
                $stats['temperature_distribution'][$tempGroup] = 0;
            }
            $stats['temperature_distribution'][$tempGroup]++;
        }
        
        // Perincian Rentang Suhu (Tidak perlu konversi unit, cukup catat)
        if (!empty($container['temperature_range'])) {
            $stats['with_temperature_range']++;
            $range = $container['temperature_range'];
            
            // Pastikan min/max adalah float sebelum digunakan di rangeKey
            $rangeMin = floatval($range['min']); 
            $rangeMax = floatval($range['max']); 

            // Tambahkan unit ke kunci untuk membedakan C dan F (jika ada)
            $rangeKey = $rangeMin . ' to ' . $rangeMax . '°' . $range['unit'];
            
            if (!isset($stats['temperature_ranges_used'][$rangeKey])) {
                $stats['temperature_ranges_used'][$rangeKey] = 0;
            }
            $stats['temperature_ranges_used'][$rangeKey]++;
        }
    }
    
    // Hitung rata-rata menggunakan array yang sudah dikonversi ke Celsius
    if (!empty($temperaturesCelcius)) {
        $stats['average_set_temperature'] = round(array_sum($temperaturesCelcius) / count($temperaturesCelcius), 1);
        // CATATAN: Karena ini rata-rata, kita asumsikan ini adalah suhu dalam °C.
    }
    
    return $stats;
}
}