<?php
/**
 * ImportManager - Handles shipment data importation
 * 
 * This class manages the import process for shipment data including:
 * - File parsing (CSV, Excel)
 * - Data validation
 * - Temporary storage
 * - Database integration
 */
class ImportManager {
    private $db;
    private $tempDir;
    private $userId;
    private $fileTypes = ['transit', 'local'];
    private $validatedData = [];
    private $errors = [];
    private $templateFields = [
        'transit' => [
            'Destination' => 'destination',
            'Customer ID' => 'customer',
            'Consignee Name' => 'consignee_name', 
            'Currency' => 'currency',
            'OBL Number' => 'obl_no',
            'ETA Date' => 'eta',
            'Initial ETA' => 'initial_eta',
            'Pre-Alert Received Date' => 'pre_alert_received_date',
            'Discharge Date' => 'discharge_date',
            'Vessel Number' => 'vno',
            'Port' => 'port',
            'Free Detention Days' => 'free_detention',
            'Remarks' => 'remarks',
        ],
        'local' => [
            'Destination' => 'destination',
            'Customer ID' => 'customer',
            'Consignee Name' => 'consignee_name',
            'Currency' => 'currency',
            'OBL Number' => 'obl_no',
            'ETA Date' => 'eta',
            'Remarks' => 'remarks',
            'Transporter' => 'transporter'
        ]
    ];
    
    private $containerFields = [
        'Container Code' => 'container_code',
        'Container Size' => 'container_size',
        'Driver Name' => 'driver_name',
        'Driver Phone' => 'driver_phone_number',
        'Driver License' => 'driver_license',
        'Way Bill' => 'way_bill'
    ];

    private $requiredFields = [
        'transit' => ['destination', 'customer', 'consignee_name', 'obl_no'],
        'local' => ['destination', 'customer', 'consignee_name']
    ];

    /**
     * Constructor
     *
     * @param mysqli $db Database connection
     * @param int $userId Current user ID
     */
    public function __construct($db, $userId) {
        $this->db = $db;
        $this->userId = $userId;
        $this->tempDir = dirname(__DIR__) . '/temp/imports';
        
        // Ensure temp directory exists
        if (!is_dir($this->tempDir)) {
            mkdir($this->tempDir, 0755, true);
        }
    }

    /**
     * Generate import template for download
     *
     * @param string $type Type of shipment (transit or local)
     * @return void Outputs CSV file
     */
    public function generateTemplate($type) {
        if (!in_array($type, $this->fileTypes)) {
            throw new Exception("Invalid shipment type: $type");
        }
        
        $filename = "{$type}_shipment_import_template.csv";
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        
        $output = fopen('php://output', 'w');
        
        // Combine main fields with container fields
        $fields = array_merge(
            $this->templateFields[$type],
            $this->containerFields
        );
        
        // Write headers
        fputcsv($output, array_keys($fields));
        
        // Write sample row with instructions
        $sampleRow = array_fill(0, count($fields), '');
        $sampleRow[0] = 'Example: BU-BU, DR-LUB, etc.';
        $sampleRow[1] = 'Enter valid Customer ID';
        $sampleRow[3] = '$,Tsh.';
        
        if ($type == 'transit') {
            $sampleRow[5] = 'YYYY-MM-DD format';
            $sampleRow[11] = '7, 14, 21, 30, 40, 55';
        } else {
            $sampleRow[4] = 'YYYY-MM-DD format';
        }
        
        // Container section instructions
        $containerStartIdx = count($this->templateFields[$type]);
        $sampleRow[$containerStartIdx] = 'CONTAINER SECTION BEGINS HERE';
        $sampleRow[$containerStartIdx + 1] = '20,40,40HC';
        
        fputcsv($output, $sampleRow);
        
        // Add empty row
        fputcsv($output, array_fill(0, count($fields), ''));
        
        fclose($output);
    }

    /**
     * Process uploaded file
     *
     * @param array $file $_FILES array element
     * @param string $type Shipment type (transit or local)
     * @return string Import ID for reference
     */
    public function processUploadedFile($file, $type) {
        if (!in_array($type, $this->fileTypes)) {
            throw new Exception("Invalid shipment type: $type");
        }
        
        // Validate file
        if (!isset($file['tmp_name']) || empty($file['tmp_name'])) {
            throw new Exception("No file uploaded");
        }
        
        // Check file extension
        $extension = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        if (!in_array($extension, ['csv', 'xlsx', 'xls'])) {
            throw new Exception("Invalid file format. Please upload CSV or Excel files");
        }
        
        // Save uploaded file to temporary directory for debugging
        $debugFilePath = $this->tempDir . '/debug_' . time() . '.' . $extension;
        if (!move_uploaded_file($file['tmp_name'], $debugFilePath)) {
            throw new Exception("Failed to save uploaded file for processing");
        }
        
        // Parse file based on extension
        if ($extension === 'csv') {
            $data = $this->parseCSV($debugFilePath, $type);
        } else {
            $data = $this->parseExcel($debugFilePath, $type);
        }
        
        // Validate data
        $result = $this->validateData($data, $type);
        
        // Save validated data to temp file
        $importId = uniqid('import_');
        $this->saveTemporaryData($importId, $result, $type);
        
        return $importId;
    }
    
    /**
     * Parse CSV file - Completely revised for more robust header handling
     *
     * @param string $filePath Path to CSV file
     * @param string $type Shipment type
     * @return array Parsed data
     */
    private function parseCSV($filePath, $type) {
        $data = [];
        $headerMapping = [];
        
        // Debug info
        error_log("Parsing CSV file: $filePath for type: $type");
        
        // Get expected field maps
        $expectedFields = array_merge(
            $this->templateFields[$type],
            $this->containerFields
        );
        
        // Convert keys to lowercase for case-insensitive matching
        $expectedFieldsLower = array_change_key_case(array_flip($expectedFields), CASE_LOWER);
        
        if (($handle = fopen($filePath, "r")) !== FALSE) {
            // First try to detect the encoding and BOM
            $firstLine = fgets($handle);
            rewind($handle);
            
            // Check and remove BOM if present
            if (substr($firstLine, 0, 3) === "\xEF\xBB\xBF") {
                // File has UTF-8 BOM, need to adjust reading
                error_log("UTF-8 BOM detected in CSV file");
                $firstLine = substr($firstLine, 3);
                // Skip BOM for next reads
                fseek($handle, 3);
            }
            
            // Debug header row
            $headerRow = fgetcsv($handle);
            rewind($handle); // Go back to start for normal processing
            
            if (!$headerRow) {
                error_log("Failed to read CSV header row");
                throw new Exception("Could not read headers from CSV file");
            }
            
            error_log("CSV headers found: " . implode(", ", $headerRow));
            
            // Get headers and clean them
            $headers = fgetcsv($handle);
            if (!$headers) {
                throw new Exception("Could not read headers from CSV file");
            }
            
            // Clean headers and map them to our expected fields
            foreach ($headers as $index => $header) {
                $header = trim($header);
                $headerLower = strtolower($header);
                
                // First try direct match
                if (isset($expectedFields[$header])) {
                    $headerMapping[$index] = $expectedFields[$header];
                    continue;
                }
                
                // Then try case-insensitive match
                if (isset($expectedFieldsLower[$headerLower])) {
                    $headerMapping[$index] = $expectedFieldsLower[$headerLower];
                    continue;
                }
                
                // Try more flexible matching - remove spaces, special chars
                $cleanHeader = preg_replace('/[^a-z0-9]/', '', $headerLower);
                foreach ($expectedFields as $expectedHeader => $fieldName) {
                    $cleanExpected = preg_replace('/[^a-z0-9]/', '', strtolower($expectedHeader));
                    if ($cleanHeader === $cleanExpected) {
                        $headerMapping[$index] = $fieldName;
                        break;
                    }
                }
            }
            
            // Debug the mapping
            error_log("Header mapping created: " . json_encode($headerMapping));
            
            // Check for required headers
            $expectedFieldNames = array_values($this->templateFields[$type]);
            $requiredFieldNames = array_intersect($expectedFieldNames, $this->requiredFields[$type]);
            $mappedFieldNames = array_values($headerMapping);
            
            $missingRequiredFields = array_diff($this->requiredFields[$type], $mappedFieldNames);
            
            if (!empty($missingRequiredFields)) {
                // Output more detailed debug info before throwing exception
                error_log("Missing required fields: " . implode(", ", $missingRequiredFields));
                error_log("Required fields for $type: " . implode(", ", $this->requiredFields[$type]));
                error_log("Mapped fields: " . implode(", ", $mappedFieldNames));
                
                // Check header by header to find the problem
                foreach ($this->requiredFields[$type] as $required) {
                    $found = false;
                    foreach ($mappedFieldNames as $mapped) {
                        if ($mapped === $required) {
                            $found = true;
                            break;
                        }
                    }
                    if (!$found) {
                        error_log("Field not found in mapping: $required");
                    }
                }
                
                // Check headers one by one for the missing fields
                foreach ($missingRequiredFields as $missingField) {
                    foreach ($expectedFields as $expectedHeader => $fieldName) {
                        if ($fieldName === $missingField) {
                            error_log("Missing header in CSV: $expectedHeader (field: $fieldName)");
                            // Check for similar headers
                            foreach ($headers as $csvHeader) {
                                $similarity = similar_text(strtolower($csvHeader), strtolower($expectedHeader), $percent);
                                error_log("Similarity to '$csvHeader': $percent%");
                            }
                        }
                    }
                }
                
                throw new Exception("Missing required headers: " . implode(", ", $missingRequiredFields));
            }
            
            // Parse data rows
            $rowIndex = 0;
            while (($row = fgetcsv($handle)) !== FALSE) {
                $rowIndex++;
                
                // Skip empty rows
                if (empty(array_filter($row))) {
                    continue;
                }
                
                $rowData = [];
                foreach ($headerMapping as $index => $field) {
                    if (isset($row[$index])) {
                        $rowData[$field] = trim($row[$index]);
                    }
                }
                
                // Only add rows with at least some data
                if (!empty($rowData)) {
                    $data[] = $rowData;
                }
            }
            
            fclose($handle);
            
            // Debug rows found
            error_log("Found " . count($data) . " data rows");
        } else {
            error_log("Failed to open CSV file: $filePath");
            throw new Exception("Could not open the CSV file for reading");
        }
        
        return $data;
    }
    
    /**
     * Parse Excel file - Also revised for more robust header handling
     *
     * @param string $filePath Path to Excel file
     * @param string $type Shipment type
     * @return array Parsed data
     */
    private function parseExcel($filePath, $type) {
        // Require PhpSpreadsheet
        require_once dirname(__DIR__) . '/vendor/phpoffice/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php';
        
        try {
            error_log("Parsing Excel file: $filePath for type: $type");
            
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filePath);
            $worksheet = $spreadsheet->getActiveSheet();
            $rows = $worksheet->toArray();
            
            if (empty($rows)) {
                throw new Exception("No data found in Excel file");
            }
            
            // Get headers from first row
            $headers = $rows[0];
            error_log("Excel headers found: " . implode(", ", $headers));
            
            // Get expected field maps
            $expectedFields = array_merge(
                $this->templateFields[$type],
                $this->containerFields
            );
            
            // Convert keys to lowercase for case-insensitive matching
            $expectedFieldsLower = array_change_key_case(array_flip($expectedFields), CASE_LOWER);
            
            // Clean headers and map them to our expected fields
            $headerMapping = [];
            foreach ($headers as $index => $header) {
                $header = trim($header);
                $headerLower = strtolower($header);
                
                // First try direct match
                if (isset($expectedFields[$header])) {
                    $headerMapping[$index] = $expectedFields[$header];
                    continue;
                }
                
                // Then try case-insensitive match
                if (isset($expectedFieldsLower[$headerLower])) {
                    $headerMapping[$index] = $expectedFieldsLower[$headerLower];
                    continue;
                }
                
                // Try more flexible matching - remove spaces, special chars
                $cleanHeader = preg_replace('/[^a-z0-9]/', '', $headerLower);
                foreach ($expectedFields as $expectedHeader => $fieldName) {
                    $cleanExpected = preg_replace('/[^a-z0-9]/', '', strtolower($expectedHeader));
                    if ($cleanHeader === $cleanExpected) {
                        $headerMapping[$index] = $fieldName;
                        break;
                    }
                }
            }
            
            // Debug the mapping
            error_log("Header mapping created: " . json_encode($headerMapping));
            
            // Check for required headers
            $expectedFieldNames = array_values($this->templateFields[$type]);
            $requiredFieldNames = array_intersect($expectedFieldNames, $this->requiredFields[$type]);
            $mappedFieldNames = array_values($headerMapping);
            
            $missingRequiredFields = array_diff($this->requiredFields[$type], $mappedFieldNames);
            
            if (!empty($missingRequiredFields)) {
                // Output more detailed debug info
                error_log("Missing required fields: " . implode(", ", $missingRequiredFields));
                error_log("Required fields for $type: " . implode(", ", $this->requiredFields[$type]));
                error_log("Mapped fields: " . implode(", ", $mappedFieldNames));
                
                throw new Exception("Missing required headers: " . implode(", ", $missingRequiredFields));
            }
            
            // Parse data rows
            $data = [];
            for ($i = 1; $i < count($rows); $i++) {
                $row = $rows[$i];
                
                // Skip empty rows
                if (empty(array_filter($row))) {
                    continue;
                }
                
                $rowData = [];
                foreach ($headerMapping as $index => $field) {
                    if (isset($row[$index])) {
                        $rowData[$field] = trim($row[$index]);
                    }
                }
                
                // Only add rows with at least some data
                if (!empty($rowData)) {
                    $data[] = $rowData;
                }
            }
            
            error_log("Found " . count($data) . " data rows in Excel file");
            return $data;
            
        } catch (Exception $e) {
            error_log("Error parsing Excel file: " . $e->getMessage());
            throw new Exception("Error parsing Excel file: " . $e->getMessage());
        }
    }
    
    /**
     * Validate imported data
     *
     * @param array $data Data to validate
     * @param string $type Shipment type
     * @return array Validated data with errors
     */
    private function validateData($data, $type) {
        $validatedData = [];
        $errors = [];
        
        foreach ($data as $index => $row) {
            $rowErrors = [];
            
            // Check required fields
            foreach ($this->requiredFields[$type] as $field) {
                if (!isset($row[$field]) || trim($row[$field]) === '') {
                    $rowErrors[] = "Missing required field: $field";
                }
            }
            
            // Validate destination format
            if (isset($row['destination']) && !empty($row['destination'])) {
                if (!preg_match('/^[A-Z]{2}-[A-Z]{2,3}$/', $row['destination'])) {
                    $rowErrors[] = "Invalid destination format. Must be like 'DR-LUB' or 'BU-BU'";
                } else {
                    // Generate file number
                    try {
                        $row['file_number'] = $this->generateFileNumber($row['destination'], $type);
                    } catch (Exception $e) {
                        $rowErrors[] = "Error generating file number: " . $e->getMessage();
                    }
                }
            }
            
            // Validate dates
            $dateFields = ['eta', 'initial_eta', 'pre_alert_received_date', 'discharge_date'];
            foreach ($dateFields as $field) {
                if (isset($row[$field]) && !empty($row[$field])) {
                    // Accept various date formats and convert to Y-m-d
                    try {
                        $date = new DateTime($row[$field]);
                        $row[$field] = $date->format('Y-m-d');
                    } catch (Exception $e) {
                        $rowErrors[] = "Invalid date format for $field";
                    }
                }
            }
            
            // Validate customer ID
            if (isset($row['customer']) && !empty($row['customer'])) {
                // Check if it's numeric
                if (!is_numeric($row['customer'])) {
                    $rowErrors[] = "Customer ID must be a number";
                } else {
                    // Check if customer exists
                    $customerQuery = "SELECT customer_id, first_name FROM customers WHERE customer_id = ?";
                    $stmt = $this->db->prepare($customerQuery);
                    $customerId = (int)$row['customer'];
                    $stmt->bind_param('i', $customerId);
                    $stmt->execute();
                    $result = $stmt->get_result();
                    
                    if ($result->num_rows === 0) {
                        $rowErrors[] = "Customer ID $customerId not found";
                    } else {
                        // Store customer name for display
                        $customerData = $result->fetch_assoc();
                        $row['customer_name'] = $customerData['first_name'];
                    }
                }
            }
            
            // Validate currency
            if (isset($row['currency']) && !empty($row['currency']) && !in_array($row['currency'], ['$', 'Tsh.'])) {
                $rowErrors[] = "Invalid currency. Must be $ or Tsh.";
                // Default to $ if invalid
                $row['currency'] = '$';
            }
            
            // Validate container size if present
            if (isset($row['container_size']) && !empty($row['container_size']) && 
                !in_array($row['container_size'], ['20', '40', '40HC'])) {
                $rowErrors[] = "Invalid container size. Must be 20, 40, or 40HC";
            }
            
            // Validate free detention days if present (transit only)
            if ($type == 'transit' && isset($row['free_detention']) && !empty($row['free_detention'])) {
                if (!in_array($row['free_detention'], ['7', '14', '21', '30', '40', '55'])) {
                    $rowErrors[] = "Invalid free detention days. Valid values: 7, 14, 21, 30, 40, 55";
                }
            }
            
            // For transit shipments, set local/transit flag
            $row['local_transit'] = ($type == 'transit') ? 2 : 1;
            
            // Store validation results
            if (!empty($rowErrors)) {
                $errors[$index] = $rowErrors;
            }
            
            $validatedData[$index] = $row;
        }
        
        return [
            'data' => $validatedData,
            'errors' => $errors,
            'type' => $type
        ];
    }
    
    /**
     * Save temporary data for later processing
     *
     * @param string $importId Import ID
     * @param array $data Validated data with errors
     * @param string $type Shipment type
     * @return void
     */
    private function saveTemporaryData($importId, $data, $type) {
        $tempFile = $this->tempDir . '/' . $importId . '.json';
        
        $saveData = [
            'importId' => $importId,
            'type' => $type,
            'userId' => $this->userId,
            'timestamp' => time(),
            'data' => $data['data'],
            'errors' => $data['errors']
        ];
        
        file_put_contents($tempFile, json_encode($saveData));
    }
    
    /**
     * Get temporary data from saved file
     *
     * @param string $importId Import ID
     * @return array Import data
     */
    public function getImportData($importId) {
        $tempFile = $this->tempDir . '/' . $importId . '.json';
        
        if (!file_exists($tempFile)) {
            throw new Exception("Import data not found");
        }
        
        $data = json_decode(file_get_contents($tempFile), true);
        
        // Verify it's a valid import file
        if (!isset($data['importId']) || $data['importId'] !== $importId) {
            throw new Exception("Invalid import data");
        }
        
        return $data;
    }
    
    /**
     * Get import status summary
     *
     * @param string $importId Import ID
     * @return array Status summary
     */
    public function getImportStatus($importId) {
        $data = $this->getImportData($importId);
        
        $totalRows = count($data['data']);
        $rowsWithErrors = count($data['errors']);
        $validRows = $totalRows - $rowsWithErrors;
        
        return [
            'importId' => $importId,
            'type' => $data['type'],
            'timestamp' => $data['timestamp'],
            'totalRows' => $totalRows,
            'validRows' => $validRows,
            'rowsWithErrors' => $rowsWithErrors,
            'isValid' => ($rowsWithErrors === 0 && $totalRows > 0)
        ];
    }
    
    /**
     * Generate file number based on destination and type
     *
     * @param string $destination Destination code (e.g., "DR-LUB")
     * @param string $type Shipment type
     * @return string Generated file number
     */
    private function generateFileNumber($destination, $type) {
        try {
            // Split destination into country and city codes
            list($country, $city) = explode('-', $destination);
            
            // Get current year
            $year = date('y');
            
            // Query to find the last used number for this country-city combination for the current year
            $query = "SELECT file_number FROM files 
                      WHERE file_number LIKE 'RFT/{$country}/{$city}/%/{$year}' 
                      ORDER BY id DESC LIMIT 1";
            
            $result = $this->db->query($query);
            
            if ($result && $result->num_rows > 0) {
                $row = $result->fetch_assoc();
                $lastFileNumber = $row['file_number'];
                
                // Extract the numeric part
                $parts = explode('/', $lastFileNumber);
                $lastNumber = intval($parts[3]);
                $newNumber = $lastNumber + 1;
            } else {
                // No existing records for this combination, start with 1
                $newNumber = 1;
            }
            
            // Format with leading zeros (3 digits)
            $formattedNumber = sprintf("%03d", $newNumber);
            
            // Construct the file number in format RFT/DR/BUK/001/24
            return "RFT/{$country}/{$city}/{$formattedNumber}/{$year}";
        } catch (Exception $e) {
            error_log("Error generating file number: " . $e->getMessage());
            throw new Exception("Failed to generate file number");
        }
    }
    
    /**
     * Process approved import
     *
     * @param string $importId Import ID
     * @return array Result with counts and errors
     */
    public function processApprovedImport($importId) {
        $data = $this->getImportData($importId);
        $shipmentType = $data['type'];
        $importData = $data['data'];
        
        // Initialize counters
        $results = [
            'success' => 0,
            'error' => 0,
            'skipped' => 0,
            'errors' => []
        ];

        // Start transaction
        $this->db->begin_transaction();
        
        try {
            foreach ($importData as $index => $row) {
                // Skip rows with known errors
                if (isset($data['errors'][$index])) {
                    $results['skipped']++;
                    continue;
                }
                
                try {
                    if ($shipmentType === 'transit') {
                        $this->saveTransitShipment($row);
                    } else {
                        $this->saveLocalShipment($row);
                    }
                    
                    $results['success']++;
                } catch (Exception $e) {
                    $results['error']++;
                    $results['errors'][] = "Row " . ($index + 1) . ": " . $e->getMessage();
                    
                    // For critical errors, rollback and abort
                    if (stripos($e->getMessage(), 'duplicate entry') !== false) {
                        throw $e;
                    }
                }
            }
            
            // Commit transaction if no errors
            $this->db->commit();
            
            // Delete temp file if successful
            $tempFile = $this->tempDir . '/' . $importId . '.json';
            if (file_exists($tempFile)) {
                unlink($tempFile);
            }
            
            return $results;
        } catch (Exception $e) {
            // Rollback on critical error
            $this->db->rollback();
            
            $results['error']++;
            $results['errors'][] = "Critical error: " . $e->getMessage();
            
            return $results;
        }
    }
    
    /**
     * Save a transit shipment
     *
     * @param array $row Shipment data
     * @return void
     */
    private function saveTransitShipment($row) {
        // Extract container data if present
        $containerData = null;
        if (isset($row['container_code']) && !empty($row['container_code'])) {
            $containerData = [
                'container_code' => $row['container_code'],
                'container_size' => $row['container_size'] ?? '',
                'driver_name' => $row['driver_name'] ?? '',
                'driver_phone_number' => $row['driver_phone_number'] ?? '',
                'driver_license' => $row['driver_license'] ?? '',
            ];
        }
        
        // File number should already be generated
        if (empty($row['file_number'])) {
            throw new Exception("Missing file number");
        }
        
        // Prepare fields needed for transit shipment
        $fileNumber = $row['file_number'];
        $customer = (int)$row['customer'];
        $consigneeName = $row['consignee_name'];
        $destination = $row['destination'];
        $currency = $row['currency'] ?? '$';
        $oblNo = $row['obl_no'] ?? null;
        $eta = !empty($row['eta']) ? $row['eta'] : null;
        $initialEta = !empty($row['initial_eta']) ? $row['initial_eta'] : null;
        $preAlertReceivedDate = !empty($row['pre_alert_received_date']) ? $row['pre_alert_received_date'] : null;
        $dischargeDate = !empty($row['discharge_date']) ? $row['discharge_date'] : null;
        $vno = $row['vno'] ?? null;
        $port = $row['port'] ?? null;
        $freeDays = $row['free_detention'] ?? null;
        $remarks = $row['remarks'] ?? null;
        $localTransit = 2; // Transit shipment
        
        // Insert main shipment record
        $sql = "INSERT INTO files (
                    file_number, customer, consignee_name, 
                    destination, currency, obl_no, eta, initial_eta, 
                    pre_alert_received_date, discharge_date, vno, port,
                    remarks, `local/transit`, free_detention, created_date
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        
        $stmt = $this->db->prepare($sql);
        if (!$stmt) {
            throw new Exception("Prepare statement failed: " . $this->db->error);
        }
        
        $stmt->bind_param(
            'sississssssssis',
            $fileNumber, $customer, $consigneeName,
            $destination, $currency, $oblNo, $eta, $initialEta,
            $preAlertReceivedDate, $dischargeDate, $vno, $port,
            $remarks, $localTransit, $freeDays
        );
        
        if (!$stmt->execute()) {
            throw new Exception("Error creating shipment: " . $stmt->error);
        }
        
        // Get the inserted file ID
        $fileId = $this->db->insert_id;
        
        // Handle container details if present
        if ($containerData) {
            $containerSql = "INSERT INTO container_details (
                file_number, container_code, container_size,
                driver_name, driver_phone_number, driver_license
            ) VALUES (?, ?, ?, ?, ?, ?)";
            
            $containerStmt = $this->db->prepare($containerSql);
            if (!$containerStmt) {
                throw new Exception("Prepare container statement failed: " . $this->db->error);
            }
            
            $containerStmt->bind_param(
                'ssssss',
                $fileNumber,
                $containerData['container_code'],
                $containerData['container_size'],
                $containerData['driver_name'],
                $containerData['driver_phone_number'],
                $containerData['driver_license']
            );
            
            if (!$containerStmt->execute()) {
                throw new Exception("Error saving container details: " . $containerStmt->error);
            }
        }
        
        // Create notification
        $notifMessage = "New transit file with file number {$fileNumber} has been created via import.";
        $notifSql = "INSERT INTO notifications (file_id, file_number, notif_message, notif_message_id, created_at) 
                     VALUES (?, ?, ?, 1, NOW())";
        
        $notifStmt = $this->db->prepare($notifSql);
        if ($notifStmt) {
            $notifStmt->bind_param('iss', $fileId, $fileNumber, $notifMessage);
            $notifStmt->execute();
        }
        
        // Create charges record (empty placeholder)
        $chargeSql = "INSERT INTO charges (file_number, created_date) VALUES (?, NOW())";
        $chargeStmt = $this->db->prepare($chargeSql);
        if ($chargeStmt) {
            $chargeStmt->bind_param('s', $fileNumber);
            $chargeStmt->execute();
        }
    }
    
    /**
     * Save a local shipment
     *
     * @param array $row Shipment data
     * @return void
     */
    private function saveLocalShipment($row) {
        // Extract container data if present
        $containerData = null;
        if (isset($row['container_code']) && !empty($row['container_code'])) {
            $containerData = [
                'container_code' => $row['container_code'],
                'container_size' => $row['container_size'] ?? '',
                'way_bill' => $row['way_bill'] ?? '',
                'driver_name' => $row['driver_name'] ?? '',
                'driver_phone_number' => $row['driver_phone_number'] ?? '',
                'driver_license' => $row['driver_license'] ?? '',
            ];
        }
        
        // File number should already be generated
        if (empty($row['file_number'])) {
            throw new Exception("Missing file number");
        }
        
        // Prepare fields needed for local shipment
        $fileNumber = $row['file_number'];
        $customer = (int)$row['customer'];
        $consigneeName = $row['consignee_name'];
        $transType = $row['trans_type'] ?? null;
        $transporter = $row['transporter'] ?? null;
        $destination = $row['destination'];
        $currency = $row['currency'] ?? '$';
        $oblNo = $row['obl_no'] ?? null;
        $eta = !empty($row['eta']) ? $row['eta'] : null;
        $remarks = $row['remarks'] ?? null;
        $localTransit = 1; // Local shipment
        
        // Insert main shipment record
        $sql = "INSERT INTO files (
                    file_number, customer, trans_type, transporter, currency,
                    consignee_name, obl_no, eta, destination, remarks, 
                    `local/transit`, created_date
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        
        $stmt = $this->db->prepare($sql);
        if (!$stmt) {
            throw new Exception("Prepare statement failed: " . $this->db->error);
        }
        
        $stmt->bind_param(
            'sississssi',
            $fileNumber, $customer, $transType, $transporter, $currency,
            $consigneeName, $oblNo, $eta, $destination, $remarks,
            $localTransit
        );
        
        if (!$stmt->execute()) {
            throw new Exception("Error creating shipment: " . $stmt->error);
        }
        
        // Get the inserted file ID
        $fileId = $this->db->insert_id;
        
        // Handle container details if present
        if ($containerData) {
            $containerSql = "INSERT INTO container_details (
                file_number, container_code, container_size, way_bill,
                driver_name, driver_phone_number, driver_license
            ) VALUES (?, ?, ?, ?, ?, ?, ?)";
            
            $containerStmt = $this->db->prepare($containerSql);
            if (!$containerStmt) {
                throw new Exception("Prepare container statement failed: " . $this->db->error);
            }
            
            $containerStmt->bind_param(
                'sssssss',
                $fileNumber,
                $containerData['container_code'],
                $containerData['container_size'],
                $containerData['way_bill'],
                $containerData['driver_name'],
                $containerData['driver_phone_number'],
                $containerData['driver_license']
            );
            
            if (!$containerStmt->execute()) {
                throw new Exception("Error saving container details: " . $containerStmt->error);
            }
        }
        
        // Create notification
        $notifMessage = "New local file with file number {$fileNumber} has been created via import.";
        $notifSql = "INSERT INTO notifications (file_id, file_number, notif_message, notif_message_id, created_at) 
                     VALUES (?, ?, ?, 1, NOW())";
        
        $notifStmt = $this->db->prepare($notifSql);
        if ($notifStmt) {
            $notifStmt->bind_param('iss', $fileId, $fileNumber, $notifMessage);
            $notifStmt->execute();
        }
        
        // Create charges record (empty placeholder)
        $chargeSql = "INSERT INTO charges (file_number, created_date) VALUES (?, NOW())";
        $chargeStmt = $this->db->prepare($chargeSql);
        if ($chargeStmt) {
            $chargeStmt->bind_param('s', $fileNumber);
            $chargeStmt->execute();
        }
    }
}
?>