<?php
/**
 * ShipmentImporter Class
 * 
 * Handles import functionality for shipment data from Excel or CSV files
 */
class ShipmentImporter {
    private $db;
    private $importType;
    private $columns = [];
    private $errors = [];
    private $warnings = [];
    private $successCount = 0;
    private $failedCount = 0;
    private $userId;
    
    /**
     * Constructor
     * 
     * @param mysqli $db Database connection
     * @param string $importType Type of import (transit or local)
     * @param int $userId Current user ID
     */
    public function __construct($db, $importType, $userId) {
        $this->db = $db;
        $this->importType = $importType;
        $this->userId = $userId;
        $this->loadColumnConfig();
    }
    
    /**
     * Load column configuration from database
     */
    private function loadColumnConfig() {
        $stmt = $this->db->prepare("
            SELECT * FROM import_config 
            WHERE import_type = ? 
            ORDER BY ordinal_position
        ");
        
        $stmt->bind_param("s", $this->importType);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $this->columns = [];
        while ($row = $result->fetch_assoc()) {
            $this->columns[$row['field_name']] = $row;
        }
    }
    
    /**
     * Process the import file
     * 
     * @param string $filePath Path to the import file
     * @param bool $validateOnly Whether to only validate without saving
     * @return array Results of the import process
     */
    public function processFile($filePath, $validateOnly = false) {
        // Check file exists
        if (!file_exists($filePath)) {
            $this->errors[] = "File does not exist: " . $filePath;
            return $this->getResults();
        }
        
        // Detect file type and load the file
        $fileType = pathinfo($filePath, PATHINFO_EXTENSION);
        
        try {
            if ($fileType == 'csv') {
                $data = $this->loadCSV($filePath);
            } elseif ($fileType == 'xlsx' || $fileType == 'xls') {
                $data = $this->loadExcel($filePath);
            } else {
                $this->errors[] = "Unsupported file type: " . $fileType;
                return $this->getResults();
            }
            
            // Validate and process the data
            $this->validateData($data);
            
            // If not just validating, save the data
            if (!$validateOnly && count($this->errors) == 0) {
                $this->saveData($data);
            }
            
        } catch (Exception $e) {
            $this->errors[] = "Error processing file: " . $e->getMessage();
        }
        
        return $this->getResults();
    }
    
    /**
     * Load data from CSV file
     * 
     * @param string $filePath Path to CSV file
     * @return array Data from CSV
     */
    private function loadCSV($filePath) {
        $rows = [];
        $headers = [];
        
        if (($handle = fopen($filePath, "r")) !== FALSE) {
            // Read headers from first row
            if (($data = fgetcsv($handle, 10000, ",")) !== FALSE) {
                $headers = $this->normalizeHeaders($data);
            }
            
            // Read data rows
            $rowIndex = 1; // Start at row 2 (after header)
            while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) {
                $rowIndex++;
                
                // Skip empty rows
                if (count(array_filter($data)) == 0) {
                    continue;
                }
                
                $row = ['_row_index' => $rowIndex];
                foreach ($headers as $colIndex => $header) {
                    $fieldName = $this->getFieldNameFromHeader($header);
                    if ($fieldName && isset($data[$colIndex])) {
                        $row[$fieldName] = $data[$colIndex];
                    }
                }
                
                $rows[] = $row;
            }
            fclose($handle);
        }
        
        return $rows;
    }
    
    /**
     * Load data from Excel file
     * 
     * @param string $filePath Path to Excel file
     * @return array Data from Excel
     */
    private function loadExcel($filePath) {
        require_once __DIR__ . '/vendor/phpoffice/vendor/autoload.php';
        
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($filePath);
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($filePath);
        
        $rows = [];
        $worksheet = $spreadsheet->getActiveSheet();
        
        // Get headers from first row
        $headerRow = $worksheet->getRowIterator(1, 1)->current();
        $cellIterator = $headerRow->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false);
        
        $headers = [];
        $colIndex = 0;
        foreach ($cellIterator as $cell) {
            $headers[$colIndex] = $this->normalizeHeader($cell->getValue());
            $colIndex++;
        }
        
        // Read data rows
        $rowIndex = 1;
        foreach ($worksheet->getRowIterator(2) as $row) {
            $rowIndex++;
            
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);
            
            $rowData = ['_row_index' => $rowIndex];
            $colIndex = 0;
            $hasData = false;
            
            foreach ($cellIterator as $cell) {
                if (isset($headers[$colIndex])) {
                    $fieldName = $this->getFieldNameFromHeader($headers[$colIndex]);
                    if ($fieldName) {
                        $value = $cell->getValue();
                        
                        // Convert date cells to Y-m-d format
                        if ($fieldName && strpos($fieldName, '_date') !== false && $value !== null) {
                            if (\PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cell)) {
                                $value = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value)->format('Y-m-d');
                            }
                        }
                        
                        $rowData[$fieldName] = $value;
                        if ($value !== null && $value !== '') {
                            $hasData = true;
                        }
                    }
                }
                $colIndex++;
            }
            
            // Skip empty rows
            if ($hasData) {
                $rows[] = $rowData;
            }
        }
        
        return $rows;
    }
    
    /**
     * Normalize headers by removing asterisks and trimming
     * 
     * @param array $headers Raw headers
     * @return array Normalized headers
     */
    private function normalizeHeaders($headers) {
        $normalized = [];
        foreach ($headers as $header) {
            $normalized[] = $this->normalizeHeader($header);
        }
        return $normalized;
    }
    
    /**
     * Normalize a single header
     * 
     * @param string $header Raw header
     * @return string Normalized header
     */
    private function normalizeHeader($header) {
        // Remove asterisks and trim
        return trim(str_replace('*', '', $header));
    }
    
    /**
     * Get field name from display name
     * 
     * @param string $header Display name
     * @return string|null Field name if found, null otherwise
     */
    private function getFieldNameFromHeader($header) {
        foreach ($this->columns as $fieldName => $column) {
            if (strtolower($column['display_name']) === strtolower($header)) {
                return $fieldName;
            }
        }
        return null;
    }
    
    /**
     * Validate the imported data
     * 
     * @param array $data Data to validate
     */
    private function validateData(&$data) {
        if (empty($data)) {
            $this->errors[] = "No data found in the import file or headers are incorrect.";
            return;
        }
        
        // Validate each row
        foreach ($data as $index => &$row) {
            $rowIndex = $row['_row_index'] ?? ($index + 2); // Add 2 to account for header row and 0-indexing
            $this->validateRow($row, $rowIndex);
            
            // Generate file number if needed
            if (empty($row['file_number']) && $this->importType == 'transit' && isset($row['destination'])) {
                try {
                    $row['file_number'] = $this->generateFileNumber($row['destination']);
                } catch (Exception $e) {
                    $this->errors[] = "Row $rowIndex: " . $e->getMessage();
                }
            }
        }
    }
    
    /**
     * Validate a single row of data
     * 
     * @param array $row Row data
     * @param int $rowIndex Row index for error reporting
     */
    private function validateRow($row, $rowIndex) {
        // Check required fields
        foreach ($this->columns as $fieldName => $column) {
            if ($column['is_required'] && (!isset($row[$fieldName]) || $row[$fieldName] === '')) {
                $this->errors[] = "Row $rowIndex: Missing required field: " . $column['display_name'];
            }
        }
        
        // Validate fields based on their types and rules
        foreach ($row as $fieldName => $value) {
            if ($fieldName === '_row_index' || empty($value)) {
                continue;
            }
            
            if (!isset($this->columns[$fieldName])) {
                continue; // Skip fields not in our config
            }
            
            $column = $this->columns[$fieldName];
            $fieldType = $column['field_type'];
            
            switch ($fieldType) {
                case 'date':
                    if (!$this->validateDate($value)) {
                        $this->errors[] = "Row $rowIndex: Invalid date format for " . $column['display_name'] . ". Use YYYY-MM-DD format.";
                    }
                    break;
                    
                case 'select':
                    if (!empty($column['validation_rules'])) {
                        $rules = json_decode($column['validation_rules'], true);
                        
                        if (isset($rules['values']) && !in_array($value, $rules['values'])) {
                            $this->errors[] = "Row $rowIndex: Invalid value for " . $column['display_name'] . ". Allowed values: " . implode(', ', $rules['values']);
                        } elseif (isset($rules['source'])) {
                            $this->validateForeignKey($fieldName, $value, $rules, $rowIndex);
                        }
                    }
                    break;
                    
                case 'number':
                    if (!is_numeric($value)) {
                        $this->errors[] = "Row $rowIndex: Value for " . $column['display_name'] . " must be a number.";
                    } elseif (!empty($column['validation_rules'])) {
                        $rules = json_decode($column['validation_rules'], true);
                        
                        if (isset($rules['min']) && $value < $rules['min']) {
                            $this->errors[] = "Row $rowIndex: Value for " . $column['display_name'] . " must be at least " . $rules['min'];
                        }
                        
                        if (isset($rules['max']) && $value > $rules['max']) {
                            $this->errors[] = "Row $rowIndex: Value for " . $column['display_name'] . " must be at most " . $rules['max'];
                        }
                    }
                    break;
                    
                case 'text':
                    if (!empty($column['validation_rules'])) {
                        $rules = json_decode($column['validation_rules'], true);
                        
                        if (isset($rules['min_length']) && strlen($value) < $rules['min_length']) {
                            $this->errors[] = "Row $rowIndex: " . $column['display_name'] . " must be at least " . $rules['min_length'] . " characters.";
                        }
                        
                        if (isset($rules['max_length']) && strlen($value) > $rules['max_length']) {
                            $this->errors[] = "Row $rowIndex: " . $column['display_name'] . " must be at most " . $rules['max_length'] . " characters.";
                        }
                    }
                    break;
            }
        }
    }
    
    /**
     * Validate a date string
     * 
     * @param string $date Date string
     * @return bool Whether the date is valid
     */
    private function validateDate($date) {
        if (!is_string($date)) {
            return false;
        }
        
        $d = \DateTime::createFromFormat('Y-m-d', $date);
        return $d && $d->format('Y-m-d') === $date;
    }
    
    /**
     * Validate a foreign key value
     * 
     * @param string $fieldName Field name
     * @param mixed $value Field value
     * @param array $rules Validation rules
     * @param int $rowIndex Row index for error reporting
     */
    private function validateForeignKey($fieldName, $value, $rules, $rowIndex) {
        if (empty($value)) {
            return;
        }
        
        $table = $rules['source'] ?? '';
        $key = $rules['key'] ?? 'id';
        
        if (empty($table)) {
            return;
        }
        
        // Check if value exists in reference table
        $sql = "SELECT COUNT(*) as count FROM $table WHERE $key = ?";
        $stmt = $this->db->prepare($sql);
        $stmt->bind_param('s', $value);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        
        if ($result['count'] == 0) {
            $this->errors[] = "Row $rowIndex: Invalid value for " . $this->columns[$fieldName]['display_name'] . 
                              ". The value $value does not exist in $table table.";
        }
    }
    
    /**
     * Generate a file number based on destination
     * 
     * @param string $destination Destination code
     * @return string Generated file number
     */
    private function generateFileNumber($destination) {
        $parts = explode('-', $destination);
        if (count($parts) != 2) {
            throw new Exception("Invalid destination format. Must be in format 'XX-YY'");
        }
        
        $country = $parts[0];
        $city = $parts[1];
        
        // Get current year (2 digits)
        $year = date('y');
        
        // Find the last file number for this destination and year
        $pattern = "RFT/{$country}/{$city}/%/{$year}";
        $sql = "SELECT file_number FROM files 
                WHERE file_number LIKE ? 
                ORDER BY id DESC LIMIT 1";
        
        $stmt = $this->db->prepare($sql);
        $likePattern = str_replace('%', '%', $pattern); // Keep the wildcard
        $stmt->bind_param('s', $likePattern);
        $stmt->execute();
        $result = $stmt->get_result();
        
        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
        return "RFT/{$country}/{$city}/{$formattedNumber}/{$year}";
    }
    
    /**
     * Save the imported data
     * 
     * @param array $data Data to save
     */
    private function saveData($data) {
        // Start transaction
        $this->db->begin_transaction();
        
        try {
            foreach ($data as $row) {
                $rowIndex = $row['_row_index'] ?? 0;
                
                // Skip if no file number was generated
                if (empty($row['file_number'])) {
                    $this->warnings[] = "Row $rowIndex: Skipped - No file number was generated.";
                    $this->failedCount++;
                    continue;
                }
                
                // Check if file number already exists
                $sql = "SELECT COUNT(*) as count FROM files WHERE file_number = ?";
                $stmt = $this->db->prepare($sql);
                $stmt->bind_param('s', $row['file_number']);
                $stmt->execute();
                $result = $stmt->get_result()->fetch_assoc();
                
                if ($result['count'] > 0) {
                    $this->warnings[] = "Row $rowIndex: Skipped - File number {$row['file_number']} already exists.";
                    $this->failedCount++;
                    continue;
                }
                
                if ($this->importType === 'transit') {
                    $this->saveTransitShipment($row, $rowIndex);
                } else {
                    $this->saveLocalShipment($row, $rowIndex);
                }
            }
            
            // Commit transaction
            $this->db->commit();
            
        } catch (Exception $e) {
            // Rollback on error
            $this->db->rollback();
            $this->errors[] = "Database error: " . $e->getMessage();
        }
    }
    
    /**
     * Save a transit shipment
     * 
     * @param array $row Row data
     * @param int $rowIndex Row index for error reporting
     */
    private function saveTransitShipment($row, $rowIndex) {
        try {
            // Prepare the SQL - only include fields that are in the config
            $fields = [];
            $placeholders = [];
            $types = "";
            $values = [];
            
            // Always set local/transit = 2 for transit shipments
            $fields[] = "`local/transit`";
            $placeholders[] = "?";
            $types .= "i";
            $values[] = 2;
            
            // Add other fields
            foreach ($this->columns as $fieldName => $column) {
                if (isset($row[$fieldName]) && $row[$fieldName] !== '') {
                    $fields[] = "`$fieldName`";
                    $placeholders[] = "?";
                    
                    // Determine parameter type
                    switch ($column['field_type']) {
                        case 'number':
                            $types .= "i";
                            $values[] = intval($row[$fieldName]);
                            break;
                        case 'date':
                            $types .= "s";
                            $values[] = $row[$fieldName];
                            break;
                        default:
                            $types .= "s";
                            $values[] = $row[$fieldName];
                            break;
                    }
                }
            }
            
            // Add created_date
            $fields[] = "created_date";
            $placeholders[] = "NOW()";
            
            // Build and execute SQL
            $sql = "INSERT INTO files (" . implode(", ", $fields) . ") VALUES (" . implode(", ", $placeholders) . ")";
            $stmt = $this->db->prepare($sql);
            
            if (!$stmt) {
                throw new Exception("Error preparing statement: " . $this->db->error);
            }
            
            if (!empty($types) && !empty($values)) {
                $bindParams = array($types);
                foreach ($values as $key => $value) {
                    $bindParams[] = &$values[$key];
                }
                call_user_func_array(array($stmt, 'bind_param'), $bindParams);
            }
            
            if (!$stmt->execute()) {
                throw new Exception("Error executing statement: " . $stmt->error);
            }
            
            // Save container if provided
            if (!empty($row['container_code'])) {
                $this->saveContainer($row, $rowIndex);
            }
            
            // Create notification
            $this->createNotification($row['file_number']);
            
            // Create charges placeholder
            $this->createChargesRecord($row['file_number']);
            
            $this->successCount++;
            
        } catch (Exception $e) {
            $this->warnings[] = "Row $rowIndex: Error saving - " . $e->getMessage();
            $this->failedCount++;
        }
    }
    
    /**
     * Save a local shipment
     * 
     * @param array $row Row data
     * @param int $rowIndex Row index for error reporting
     */
    private function saveLocalShipment($row, $rowIndex) {
        try {
            // Prepare the SQL - only include fields that are in the config
            $fields = [];
            $placeholders = [];
            $types = "";
            $values = [];
            
            // Always set local/transit = 1 for local shipments
            $fields[] = "`local/transit`";
            $placeholders[] = "?";
            $types .= "i";
            $values[] = 1;
            
            // Add other fields
            foreach ($this->columns as $fieldName => $column) {
                if (isset($row[$fieldName]) && $row[$fieldName] !== '') {
                    $fields[] = "`$fieldName`";
                    $placeholders[] = "?";
                    
                    // Determine parameter type
                    switch ($column['field_type']) {
                        case 'number':
                            $types .= "i";
                            $values[] = intval($row[$fieldName]);
                            break;
                        case 'date':
                            $types .= "s";
                            $values[] = $row[$fieldName];
                            break;
                        default:
                            $types .= "s";
                            $values[] = $row[$fieldName];
                            break;
                    }
                }
            }
            
            // Add created_date
            $fields[] = "created_date";
            $placeholders[] = "NOW()";
            
            // Build and execute SQL
            $sql = "INSERT INTO files (" . implode(", ", $fields) . ") VALUES (" . implode(", ", $placeholders) . ")";
            $stmt = $this->db->prepare($sql);
            
            if (!$stmt) {
                throw new Exception("Error preparing statement: " . $this->db->error);
            }
            
            if (!empty($types) && !empty($values)) {
                $bindParams = array($types);
                foreach ($values as $key => $value) {
                    $bindParams[] = &$values[$key];
                }
                call_user_func_array(array($stmt, 'bind_param'), $bindParams);
            }
            
            if (!$stmt->execute()) {
                throw new Exception("Error executing statement: " . $stmt->error);
            }
            
            // Save container if provided
            if (!empty($row['container_code'])) {
                $this->saveContainer($row, $rowIndex);
            }
            
            // Create notification
            $this->createNotification($row['file_number'], true);
            
            // Create charges placeholder
            $this->createChargesRecord($row['file_number']);
            
            $this->successCount++;
            
        } catch (Exception $e) {
            $this->warnings[] = "Row $rowIndex: Error saving - " . $e->getMessage();
            $this->failedCount++;
        }
    }
    
    /**
     * Save container data
     * 
     * @param array $row Row data
     * @param int $rowIndex Row index for error reporting
     */
    private function saveContainer($row, $rowIndex) {
        // Get transporter name if ID is provided
        $transporter_name = '';
        if (!empty($row['transporter'])) {
            $sql = "SELECT name FROM transporter_info WHERE id = ?";
            $stmt = $this->db->prepare($sql);
            $stmt->bind_param('i', $row['transporter']);
            $stmt->execute();
            $result = $stmt->get_result();
            if ($result && $result->num_rows > 0) {
                $transporterRow = $result->fetch_assoc();
                $transporter_name = $transporterRow['name'];
            }
        }
        
        // Define container fields to insert
        $fields = [
            'file_number', 'container_code', 'container_size'
        ];
        
        $values = [
            $row['file_number'], $row['container_code'], $row['container_size'] ?? ''
        ];
        
        $types = 'sss';
        
        // Add optional fields if they exist
        $optionalFields = [
            'transporter_name' => $transporter_name,
            'driver_name' => $row['driver_name'] ?? null,
            'driver_phone_number' => $row['driver_phone_number'] ?? null,
            'driver_license' => $row['driver_license'] ?? null,
            'way_bill' => $row['way_bill'] ?? null,
            'load_date' => $row['load_date'] ?? null,
            'offload_date' => $row['offload_date'] ?? null
        ];
        
        foreach ($optionalFields as $field => $value) {
            if ($value !== null) {
                $fields[] = $field;
                $values[] = $value;
                $types .= 's';
            }
        }
        
        // Build and execute SQL
        $sql = "INSERT INTO container_details (" . implode(", ", $fields) . ") VALUES (" . implode(", ", array_fill(0, count($fields), '?')) . ")";
        $stmt = $this->db->prepare($sql);
        
        if (!$stmt) {
            throw new Exception("Error preparing container statement: " . $this->db->error);
        }
        
        $bindParams = array($types);
        foreach ($values as $key => $value) {
            $bindParams[] = &$values[$key];
        }
        call_user_func_array(array($stmt, 'bind_param'), $bindParams);
        
        if (!$stmt->execute()) {
            throw new Exception("Error saving container details: " . $stmt->error);
        }
    }
    
    /**
     * Create a notification for a new file
     * 
     * @param string $fileNumber File number
     * @param bool $isLocal Whether this is a local shipment
     */
    private function createNotification($fileNumber, $isLocal = false) {
        // Get the file ID
        $sql = "SELECT id FROM files WHERE file_number = ?";
        $stmt = $this->db->prepare($sql);
        $stmt->bind_param('s', $fileNumber);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($result && $result->num_rows > 0) {
            $row = $result->fetch_assoc();
            $fileId = $row['id'];
            
            // Create notification
            $message = "New " . ($isLocal ? "local" : "transit") . " file with file number {$fileNumber} has been created via import.";
            $sql = "INSERT INTO notifications (file_id, file_number, notif_message, notif_message_id, created_at) 
                    VALUES (?, ?, ?, 1, NOW())";
            
            $stmt = $this->db->prepare($sql);
            $stmt->bind_param('iss', $fileId, $fileNumber, $message);
            $stmt->execute();
        }
    }
    
    /**
     * Create a charges record for a file
     * 
     * @param string $fileNumber File number
     */
    private function createChargesRecord($fileNumber) {
        $sql = "INSERT INTO charges (file_number, created_date) VALUES (?, NOW())";
        $stmt = $this->db->prepare($sql);
        $stmt->bind_param('s', $fileNumber);
        $stmt->execute();
    }
    
    /**
     * Get the results of the import process
     * 
     * @return array Results
     */
    public function getResults() {
        return [
            'success' => count($this->errors) === 0,
            'errors' => $this->errors,
            'warnings' => $this->warnings,
            'successCount' => $this->successCount,
            'failedCount' => $this->failedCount
        ];
    }
}
?>