<?php

require_once '../app/Models/Customer.php';
require_once '../app/Models/Order.php';
require_once '../app/Models/CakeOrderDetails.php';
require_once '../app/Models/CakeFlavor.php';
require_once '../app/Models/CakeSize.php';
require_once '../app/Models/CakeType.php';
require_once '../app/Models/PickupLocation.php';

class MigrationController
{
    private $db;
    private $customerModel;
    private $orderModel;
    private $cakeDetailsModel;
    private $flavorModel;
    private $sizeModel;
    private $typeModel;
    private $locationModel;
    
    private $flavorMap = [];
    private $sizeMap = [];
    private $typeMap = [];
    private $locationMap = [];
    
    private $stats = [
        'total' => 0,
        'success' => 0,
        'skipped' => 0,
        'errors' => 0,
        'error_details' => []
    ];

    public function __construct()
    {
        $this->checkAuth();
        $this->checkAdmin();
        
        $this->db = $GLOBALS['pdo'];
        $this->customerModel = new Customer();
        $this->orderModel = new Order();
        $this->cakeDetailsModel = new CakeOrderDetails();
        $this->flavorModel = new CakeFlavor();
        $this->sizeModel = new CakeSize();
        $this->typeModel = new CakeType();
        $this->locationModel = new PickupLocation();
        
        $this->buildMappings();
    }

    private function checkAuth()
    {
        if (!isset($_SESSION['user_id'])) {
            redirect('login');
            exit;
        }
    }

    private function checkAdmin()
    {
        if (($_SESSION['user_role'] ?? '') !== 'admin') {
            $_SESSION['error'] = 'Admin only.';
            redirect('dashboard');
            exit;
        }
    }

    private function render($view, $data = [])
    {
        extract($data);
        $content = '';
        ob_start();
        include "../app/Views/{$view}.php";
        $content = ob_get_clean();
        include '../app/Views/layout.php';
    }

    /**
     * Build mapping tables for flavors, sizes, types, and locations
     */
    private function buildMappings()
    {
        // Map flavors
        $flavors = $this->flavorModel->all();
        foreach ($flavors as $flavor) {
            $this->flavorMap[$flavor['name']] = $flavor['id'];
            if (!empty($flavor['name_en'])) {
                $this->flavorMap[$flavor['name_en']] = $flavor['id'];
            }
        }

        // Map sizes
        $sizes = $this->sizeModel->all();
        foreach ($sizes as $size) {
            $this->sizeMap[$size['name']] = $size['id'];
            if (!empty($size['name_en'])) {
                $this->sizeMap[$size['name_en']] = $size['id'];
            }
        }

        // Map types
        $types = $this->typeModel->all();
        foreach ($types as $type) {
            $this->typeMap[$type['name']] = $type['id'];
            if (!empty($type['name_en'])) {
                $this->typeMap[$type['name_en']] = $type['id'];
            }
        }

        // Map locations
        $locations = $this->locationModel->all();
        foreach ($locations as $location) {
            $this->locationMap[$location['name']] = $location['id'];
        }
    }

    /**
     * Display migration page
     */
    public function index()
    {
        $this->render('migration/index', [
            'title' => 'Import Danych - Wybór'
        ]);
    }

    /**
     * Show cake orders import form
     */
    public function cakeOrdersForm()
    {
        $this->render('migration/cake_orders', [
            'title' => 'Import Zamówień Tortów'
        ]);
    }

    /**
     * Show regular orders import form
     */
    public function regularOrdersForm()
    {
        // Get list of CSV files from migration_data directory
        $migrationDataPath = dirname(__DIR__, 2) . '/migration_data';
        $csvFiles = [];
        
        if (is_dir($migrationDataPath)) {
            $files = scandir($migrationDataPath);
            foreach ($files as $file) {
                if (pathinfo($file, PATHINFO_EXTENSION) === 'csv') {
                    $csvFiles[] = $migrationDataPath . '/' . $file;
                }
            }
        }
        
        $this->render('migration/orders', [
            'title' => 'Import Zamówień',
            'csvFiles' => $csvFiles
        ]);
    }

    /**
     * Import cake orders from CSV
     */
    public function importCakeOrders()
    {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header('Location: /samanta_crm/migration/cake-orders');
            exit;
        }

        // Check if file was uploaded
        if (!isset($_FILES['csv_file']) || $_FILES['csv_file']['error'] !== UPLOAD_ERR_OK) {
            $_SESSION['error'] = 'Nie przesłano pliku CSV lub wystąpił błąd.';
            header('Location: /samanta_crm/migration/cake-orders');
            exit;
        }

        $csvFile = $_FILES['csv_file']['tmp_name'];
        
        // Validate file extension
        $fileInfo = pathinfo($_FILES['csv_file']['name']);
        if (strtolower($fileInfo['extension']) !== 'csv') {
            $_SESSION['error'] = 'Plik musi mieć rozszerzenie .csv';
            header('Location: /samanta_crm/migration/cake-orders');
            exit;
        }

        // Get batch settings
        $batchSize = (int)($_POST['batch_size'] ?? 50);
        $offset = (int)($_POST['offset'] ?? 0);
        
        // Store file temporarily for batch processing
        $tempFile = sys_get_temp_dir() . '/migration_' . session_id() . '.csv';
        
        if ($offset === 0) {
            // First batch - copy uploaded file
            move_uploaded_file($csvFile, $tempFile);
            // Reset stats for new import
            $_SESSION['migration_stats'] = $this->stats;
        } else {
            // Continue from previous batch
            if (isset($_SESSION['migration_stats'])) {
                $this->stats = $_SESSION['migration_stats'];
            }
        }

        try {
            $hasMore = $this->processCsvBatch($tempFile, $offset, $batchSize);
            
            // Store stats for next batch
            $_SESSION['migration_stats'] = $this->stats;
            
            if ($hasMore) {
                // More records to process - return progress page
                $this->renderProgress($offset + $batchSize, $batchSize, '/samanta_crm/migration/import-cake-orders');
            } else {
                // Completed - show final results
                $_SESSION['success'] = sprintf(
                    'Import zakończony. Przetworzono: %d, Sukces: %d, Pominięto: %d, Błędy: %d',
                    $this->stats['total'],
                    $this->stats['success'],
                    $this->stats['skipped'],
                    $this->stats['errors']
                );
                
                // Store detailed errors in session for display
                if (!empty($this->stats['error_details'])) {
                    $_SESSION['migration_errors'] = $this->stats['error_details'];
                }
                
                // Cleanup
                if (file_exists($tempFile)) {
                    unlink($tempFile);
                }
                unset($_SESSION['migration_stats']);
                
                header('Location: /samanta_crm/migration/cake-orders');
                exit;
            }
            
        } catch (\Exception $e) {
            $_SESSION['error'] = 'Błąd podczas importu: ' . $e->getMessage();
            if (file_exists($tempFile)) {
                unlink($tempFile);
            }
            unset($_SESSION['migration_stats']);
            header('Location: /samanta_crm/migration/cake-orders');
            exit;
        }
    }

    /**
     * Process CSV file in batches
     */
    private function processCsvBatch($filePath, $offset, $batchSize)
    {
        $handle = fopen($filePath, 'r');
        if (!$handle) {
            throw new \Exception('Nie można otworzyć pliku CSV');
        }

        // Read header row
        $headers = fgetcsv($handle, 0, ',');
        if (!$headers) {
            fclose($handle);
            throw new \Exception('Plik CSV jest pusty');
        }

        // Map headers to indices
        $headerMap = array_flip($headers);

        // Skip to offset
        $lineNumber = 1;
        while ($lineNumber <= $offset && fgetcsv($handle, 0, ',') !== false) {
            $lineNumber++;
        }

        $processed = 0;
        $hasMore = false;
        
        while (($row = fgetcsv($handle, 0, ',')) !== false && $processed < $batchSize) {
            $lineNumber++;
            $this->stats['total']++;
            $processed++;
            
            try {
                $data = $this->mapCsvRow($row, $headerMap);
                
                // Skip if already imported (check by old entry ID if available)
                $oldEntryId = $data['old_entry_id'] ?? null;
                if ($oldEntryId && $this->orderExists($oldEntryId)) {
                    $this->stats['skipped']++;
                    continue;
                }
                
                $this->importOrder($data);
                $this->stats['success']++;
                
            } catch (\Exception $e) {
                $this->stats['errors']++;
                $this->stats['error_details'][] = [
                    'line' => $lineNumber,
                    'error' => $e->getMessage(),
                    'data' => isset($data) ? $data['customer']['name'] ?? 'Unknown' : 'Parse error'
                ];
            }
        }

        // Check if there are more records
        $hasMore = fgetcsv($handle, 0, ',') !== false;
        
        fclose($handle);
        
        return $hasMore;
    }

    /**
     * Render progress page for batch processing
     */
    private function renderProgress($nextOffset, $batchSize, $importUrl = '/samanta_crm/migration/import-cake-orders')
    {
        $progress = [
            'total' => $this->stats['total'],
            'success' => $this->stats['success'],
            'skipped' => $this->stats['skipped'],
            'errors' => $this->stats['errors'],
            'next_offset' => $nextOffset,
            'batch_size' => $batchSize,
            'import_url' => $importUrl
        ];
        
        $this->render('migration/progress', [
            'title' => 'Importowanie...',
            'progress' => $progress
        ]);
    }

    /**
     * Process CSV file line by line
     */
    private function processCsvFile($filePath)
    {
        $handle = fopen($filePath, 'r');
        if (!$handle) {
            throw new \Exception('Nie można otworzyć pliku CSV');
        }

        // Read header row
        $headers = fgetcsv($handle, 0, ',');
        if (!$headers) {
            fclose($handle);
            throw new \Exception('Plik CSV jest pusty');
        }

        // Map headers to indices
        $headerMap = array_flip($headers);

        $lineNumber = 1;
        
        while (($row = fgetcsv($handle, 0, ',')) !== false) {
            $lineNumber++;
            $this->stats['total']++;
            
            try {
                $data = $this->mapCsvRow($row, $headerMap);
                
                // Skip if already imported (check by old entry ID if available)
                $oldEntryId = $data['old_entry_id'] ?? null;
                if ($oldEntryId && $this->orderExists($oldEntryId)) {
                    $this->stats['skipped']++;
                    continue;
                }
                
                $this->importOrder($data);
                $this->stats['success']++;
                
            } catch (\Exception $e) {
                $this->stats['errors']++;
                $this->stats['error_details'][] = [
                    'line' => $lineNumber,
                    'error' => $e->getMessage(),
                    'data' => isset($data) ? $data['customer']['name'] ?? 'Unknown' : 'Parse error'
                ];
            }
        }

        fclose($handle);
    }

    /**
     * Map CSV row to array structure
     */
    private function mapCsvRow($row, $headerMap)
    {
        $get = function($fieldName) use ($row, $headerMap) {
            $index = $headerMap[$fieldName] ?? null;
            return $index !== null ? trim($row[$index]) : '';
        };

        // Parse pickup date and time
        $pickupDate = $this->parseDate($get('Data odbioru'));
        $pickupTime = $this->parseTime($get('Godzina odbioru'));
        
        // Parse size
        $sizeRaw = $get('Wielkość');
        $sizeId = $this->sizeMap[$sizeRaw] ?? null;
        $sizeCustom = null;
        
        // If size is "Inny" (Other), use custom description
        if ($sizeRaw === 'Inny' || !$sizeId) {
            $sizeCustom = $get('Dodatkowe informacje na temat rozmaru');
        }

        // Parse flavor
        $flavorRaw = $get('Smak');
        $flavorId = $this->flavorMap[$flavorRaw] ?? null;
        $flavorCustom = null;
        
        // If flavor is "Inny" (Other), use custom description
        if ($flavorRaw === 'Inny' || !$flavorId) {
            $flavorCustom = $get('Opisz "inny" smak tortu') ?: $flavorRaw;
        }

        // Parse cake types (checkbox values)
        $typeIds = [];
        $typeCustom = null;
        
        if ($get('Standardowy') === 'Standardowy') {
            $typeIds[] = $this->typeMap['Standardowy'] ?? null;
        }
        if ($get('Okazjonalny') === 'Okazjonalny') {
            $typeIds[] = $this->typeMap['Okazjonalny'] ?? null;
            $typeCustom = $get('Wybierz okazjonalny');
        }
        if ($get('Artystyczny') === 'Artystyczny') {
            $typeIds[] = $this->typeMap['Artystyczny'] ?? null;
        }
        if ($get('Weselny') === 'Weselny') {
            $typeIds[] = $this->typeMap['Weselny'] ?? null;
        }
        if ($get('Inny') === 'Inny') {
            $typeCustom = $get('Opisz "inny" rodzaj tort');
        }
        
        $typeIds = array_filter($typeIds);

        // Parse location
        $locationName = $get('Odbiór');
        $locationId = $this->locationMap[$locationName] ?? null;
        
        // Parse delivery address if applicable
        $deliveryAddress = null;
        if ($locationName === 'Dostawa') {
            $deliveryAddress = sprintf(
                "%s %s, %s %s, %s",
                $get('Ulica'),
                $get('Numer'),
                $get('Kod pocztowy'),
                $get('Miasto'),
                $get('Kraj')
            );
        }

        // Parse payment
        $paymentMethod = $get('Płatność') === 'zaliczka' ? 'deposit' : 'on_pickup';
        $depositAmount = $paymentMethod === 'deposit' ? $this->parseDecimal($get('Wartość zaliczki')) : 0;

        // Parse decorations
        $hasInscription = $get('Napis') === 'Tak';
        $inscriptionText = $hasInscription ? $get('Napis treść') : '';

        $hasCandles = $get('Świeczka') === 'Tak';
        $candlesCount = $hasCandles ? (int)$get('Ilość świeczek') : 0;

        $hasNumberCandle = $get('Świeczka cyfry') === 'Tak';
        $numberCandleValue = $hasNumberCandle ? (int)$get('Cyfra') : null;

        $hasSparkler = $get('Raca') === 'Tak';

        $hasWaferPhoto = $get('Opłatek z własnym zdjęciem grafiką') === 'Tak';
        $waferFile = $hasWaferPhoto ? $this->extractFilename($get('Plik / zdjęcie na opłatek')) : '';

        $hasCard = $get('Kartka bilecik') === 'Tak';
        $cardText = $hasCard ? $get('Tekst na bileciku') : '';
        $cardPattern = $hasCard ? $get('Wzór bileciku') : '';

        $hasFigurine = $get('Figurka') === 'Tak';
        $figurineDescription = $hasFigurine ? $get('Inna figurka?') : '';

        $referencePhoto = $this->extractFilename($get('Plik / zdjęcie wzorzec'));
        $notes = $get('Uwagi');

        return [
            'old_entry_id' => $get('ID pozycji'),
            'customer' => [
                'prefix' => $get('Dane zamawiającego (Prefiks)'),
                'first_name' => $get('Dane zamawiającego (Imię)'),
                'last_name' => $get('Dane zamawiającego (Nazwisko)'),
                'phone' => $this->normalizePhone($get('Telefon')),
                'email' => $get('Email'),
                'name' => trim($get('Dane zamawiającego (Imię)') . ' ' . $get('Dane zamawiającego (Nazwisko)'))
            ],
            'order' => [
                'pickup_date' => $pickupDate,
                'pickup_time' => $pickupTime,
                'pickup_location_id' => $locationId,
                'delivery_address' => $deliveryAddress,
                'payment_method' => $paymentMethod,
                'deposit_amount' => $depositAmount,
                'notes' => $get('Zamawiający') . ' - ' . $notes,
                'created_at' => $this->parseDateTime($get('Data pozycji')),
                'user_agent' => $get('Agent użytkownika'),
                'ip_address' => $get('Adres IP użytkownika')
            ],
            'cake' => [
                'flavor_id' => $flavorId,
                'flavor_custom' => $flavorCustom,
                'size_id' => $sizeId,
                'size_custom' => $sizeCustom,
                'cake_type_ids' => implode(',', $typeIds),
                'cake_type_custom' => $typeCustom,
                'has_inscription' => $hasInscription,
                'inscription_text' => $inscriptionText,
                'has_candles' => $hasCandles,
                'candles_count' => $candlesCount,
                'has_number_candle' => $hasNumberCandle,
                'number_candle_value' => $numberCandleValue,
                'has_sparkler' => $hasSparkler,
                'has_wafer_photo' => $hasWaferPhoto,
                'wafer_file' => $waferFile,
                'has_card' => $hasCard,
                'card_text' => $cardText,
                'card_pattern' => $cardPattern,
                'has_figurine' => $hasFigurine,
                'figurine_description' => $figurineDescription,
                'reference_photo' => $referencePhoto,
                'notes' => $notes
            ]
        ];
    }

    /**
     * Import a single order
     */
    private function importOrder($data)
    {
        // Find or create customer
        $customerId = $this->findOrCreateCustomer($data['customer']);
        
        // Create order
        $orderId = $this->createOrder($customerId, $data['order'], $data['old_entry_id']);
        
        // Create cake order details
        $this->createCakeDetails($orderId, $data['cake']);
    }

    /**
     * Find or create customer by phone
     */
    private function findOrCreateCustomer($customerData)
    {
        if (empty($customerData['phone'])) {
            throw new \Exception('Brak numeru telefonu klienta');
        }

        // Try to find existing customer by phone
        $existing = $this->customerModel->findByPhone($customerData['phone']);
        
        if ($existing) {
            return $existing['id'];
        }

        // Create new customer
        $customerId = $this->customerModel->create([
            'name' => $customerData['name'] ?: 'Klient Importowany',
            'phone' => $customerData['phone'],
            'email' => $customerData['email'] ?: null,
            'address' => null,
            'notes' => 'Zaimportowany z Gravity Forms'
        ]);

        return $customerId;
    }

    /**
     * Create order
     */
    private function createOrder($customerId, $orderData, $oldEntryId)
    {
        $orderId = $this->orderModel->create([
            'customer_id' => $customerId,
            'order_type' => 'cake',
            'status' => 'pending',
            'is_cake_order' => 1,
            'pickup_date' => $orderData['pickup_date'],
            'pickup_time' => $orderData['pickup_time'],
            'pickup_location_id' => $orderData['pickup_location_id'],
            'delivery_address' => $orderData['delivery_address'],
            'payment_method' => $orderData['payment_method'],
            'deposit_amount' => $orderData['deposit_amount'],
            'notes' => $orderData['notes'],
            'created_at' => $orderData['created_at']
        ]);

        // Store old entry ID in notes or separate field for tracking
        $this->db->exec(
            "UPDATE orders SET notes = CONCAT(notes, '\n[Gravity Forms ID: {$oldEntryId}]') WHERE id = {$orderId}"
        );

        return $orderId;
    }

    /**
     * Create cake order details
     */
    private function createCakeDetails($orderId, $cakeData)
    {
        $this->cakeDetailsModel->create($orderId, $cakeData);
    }

    /**
     * Check if order already imported
     */
    private function orderExists($oldEntryId)
    {
        $result = $this->db->query(
            "SELECT COUNT(*) as count FROM orders WHERE notes LIKE '%[Gravity Forms ID: {$oldEntryId}]%'"
        )->fetch();
        
        return $result['count'] > 0;
    }

    // Helper functions

    private function parseDate($dateStr)
    {
        if (empty($dateStr)) return null;
        
        // Try format: 2025-11-29
        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateStr)) {
            return $dateStr;
        }
        
        // Try format: 29/11/2025
        if (preg_match('/^(\d{2})\/(\d{2})\/(\d{4})$/', $dateStr, $matches)) {
            return $matches[3] . '-' . $matches[2] . '-' . $matches[1];
        }
        
        return null;
    }

    private function parseTime($timeStr)
    {
        if (empty($timeStr)) return null;
        
        // Format: 09:00 or 9:00
        if (preg_match('/^(\d{1,2}):(\d{2})$/', $timeStr, $matches)) {
            return sprintf('%02d:%02d:00', $matches[1], $matches[2]);
        }
        
        return null;
    }

    private function parseDateTime($datetimeStr)
    {
        if (empty($datetimeStr)) return null;
        
        // Format: 2025-11-28 18:24:49
        if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $datetimeStr)) {
            return $datetimeStr;
        }
        
        return date('Y-m-d H:i:s');
    }

    private function parseDecimal($str)
    {
        if (empty($str)) return 0;
        
        // Remove any non-numeric characters except dot and comma
        $str = preg_replace('/[^\d,.]/', '', $str);
        
        // Replace comma with dot
        $str = str_replace(',', '.', $str);
        
        return (float)$str;
    }

    private function normalizePhone($phone)
    {
        // Remove all non-numeric characters except +
        $phone = preg_replace('/[^\d+]/', '', $phone);
        
        // Ensure it starts with +48 for Polish numbers
        if (!empty($phone) && !str_starts_with($phone, '+')) {
            $phone = '+48' . ltrim($phone, '0');
        }
        
        return $phone;
    }

    private function extractFilename($url)
    {
        if (empty($url)) return '';
        
        // Extract filename from URL
        $parts = parse_url($url);
        if (isset($parts['path'])) {
            return basename($parts['path']);
        }
        
        return '';
    }

    /**
     * Import regular orders from CSV
     */
    public function importOrders()
    {
        try {
            error_log("importOrders called - POST: " . print_r($_POST, true));
            
            $fileSource = $_POST['file_source'] ?? 'upload';
            $orderMapping = json_decode($_POST['order_mapping'] ?? '{}', true);
            $itemMapping = json_decode($_POST['item_mapping'] ?? '{}', true);
            
            error_log("Order mapping: " . print_r($orderMapping, true));
            error_log("Item mapping: " . print_r($itemMapping, true));
            
            // Get file
            $filePath = null;
            if ($fileSource === 'upload') {
                if (empty($_FILES['orders_file']) || $_FILES['orders_file']['error'] !== UPLOAD_ERR_OK) {
                    throw new Exception("Brak pliku lub wystąpił błąd");
                }
                $filePath = $_FILES['orders_file']['tmp_name'];
            } else {
                $filePath = $_POST['orders_path'] ?? '';
                if (empty($filePath) || !file_exists($filePath)) {
                    throw new Exception("Nie znaleziono pliku");
                }
            }
            
            // Open CSV
            $handle = fopen($filePath, 'r');
            if (!$handle) {
                throw new Exception("Nie można otworzyć pliku CSV");
            }
            
            // Remove BOM
            $bom = fread($handle, 3);
            if ($bom !== "\xEF\xBB\xBF") {
                rewind($handle);
            }
            
            // Read headers
            $headers = fgetcsv($handle);
            if (!$headers) {
                fclose($handle);
                throw new Exception("Brak nagłówków w pliku");
            }
            
            $headers = array_map('trim', $headers);
            
            // Prepare stats
            $stats = [
                'orders_imported' => 0,
                'orders_skipped' => 0,
                'items_imported' => 0,
                'items_skipped' => 0,
                'errors' => []
            ];
            
            // Get database connection
            $db = $this->db;
            
            // Find Parent Entry ID column
            $parentIdIndex = array_search('Parent Entry ID', $headers);
            
            // Process orders and items
            $orderCache = []; // Map external_id => internal id
            $lineNum = 1;
            
            while (($row = fgetcsv($handle)) !== false) {
                $lineNum++;
                
                try {
                    // Check if empty row
                    if (empty(array_filter($row))) {
                        continue;
                    }
                    
                    $parentId = trim($row[$parentIdIndex] ?? '');
                    
                    if (empty($parentId)) {
                        // This is an ORDER row
                        $orderData = [];
                        
                        foreach ($orderMapping as $dbField => $csvColumn) {
                            $colIndex = array_search($csvColumn, $headers);
                            if ($colIndex !== false) {
                                $orderData[$dbField] = trim($row[$colIndex] ?? '');
                            }
                        }
                        
                        // Validate required fields
                        if (empty($orderData['order_id_external'])) {
                            $stats['orders_skipped']++;
                            continue;
                        }
                        
                        // Check if already exists
                        $stmt = $db->prepare("SELECT id FROM orders WHERE external_id = ?");
                        $stmt->execute([$orderData['order_id_external']]);
                        $existing = $stmt->fetch(PDO::FETCH_ASSOC);
                        
                        if ($existing) {
                            $orderCache[$orderData['order_id_external']] = $existing['id'];
                            $stats['orders_skipped']++;
                            continue;
                        }
                        
                        // Insert order
                        $sql = "INSERT INTO orders (
                            customer, 
                            order_type, 
                            delivery_date, 
                            message, 
                            customer_name, 
                            external_id,
                            status,
                            created_at
                        ) VALUES (?, ?, ?, ?, ?, ?, 'pending', NOW())";
                        
                        $stmt = $db->prepare($sql);
                        $stmt->execute([
                            $orderData['customer'] ?? '',
                            $orderData['order_type'] ?? '',
                            $orderData['delivery_date'] ?? null,
                            $orderData['message'] ?? '',
                            $orderData['customer_name'] ?? '',
                            $orderData['order_id_external']
                        ]);
                        
                        $orderId = $db->lastInsertId();
                        $orderCache[$orderData['order_id_external']] = $orderId;
                        $stats['orders_imported']++;
                        
                    } else {
                        // This is an ORDER ITEM row
                        $itemData = [];
                        
                        foreach ($itemMapping as $dbField => $csvColumn) {
                            $colIndex = array_search($csvColumn, $headers);
                            if ($colIndex !== false) {
                                $itemData[$dbField] = trim($row[$colIndex] ?? '');
                            }
                        }
                        
                        // Find the order
                        if (!isset($orderCache[$parentId])) {
                            // Try to find in database
                            $stmt = $db->prepare("SELECT id FROM orders WHERE external_id = ?");
                            $stmt->execute([$parentId]);
                            $order = $stmt->fetch(PDO::FETCH_ASSOC);
                            
                            if ($order) {
                                $orderCache[$parentId] = $order['id'];
                            } else {
                                $stats['items_skipped']++;
                                $stats['errors'][] = "Linia $lineNum: Nie znaleziono zamówienia dla pozycji (Parent ID: $parentId)";
                                continue;
                            }
                        }
                        
                        $orderId = $orderCache[$parentId];
                        
                        // Find product by external_id
                        $productId = null;
                        if (!empty($itemData['product_id_external'])) {
                            $stmt = $db->prepare("SELECT id FROM products WHERE code = ?");
                            $stmt->execute([$itemData['product_id_external']]);
                            $product = $stmt->fetch(PDO::FETCH_ASSOC);
                            $productId = $product['id'] ?? null;
                        }
                        
                        // Insert order item
                        $sql = "INSERT INTO order_items (
                            order_id,
                            product_id,
                            product_name,
                            quantity,
                            price,
                            created_at
                        ) VALUES (?, ?, ?, ?, ?, NOW())";
                        
                        $stmt = $db->prepare($sql);
                        $stmt->execute([
                            $orderId,
                            $productId,
                            $itemData['product_name'] ?? '',
                            $itemData['quantity'] ?? 1,
                            $itemData['price'] ?? null
                        ]);
                        
                        $stats['items_imported']++;
                    }
                    
                } catch (Exception $e) {
                    $errorMsg = "Linia $lineNum: " . $e->getMessage();
                    $stats['errors'][] = $errorMsg;
                    if ($lineNum <= 10) {
                        error_log($errorMsg);
                    }
                }
            }
            
            fclose($handle);
            
            header('Content-Type: application/json');
            echo json_encode([
                'success' => true,
                'message' => 'Import zakończony pomyślnie!',
                'stats' => $stats
            ]);
            
        } catch (Exception $e) {
            error_log("Import orders error: " . $e->getMessage());
            header('Content-Type: application/json');
            echo json_encode([
                'success' => false,
                'message' => $e->getMessage()
            ]);
        }
    }

    /**
     * Preview orders data before import
     */
    public function previewOrders()
    {
        try {
            $fileSource = $_POST['file_source'] ?? 'upload';
            $filePath = null;
            
            // Get file based on source
            if ($fileSource === 'upload') {
                if (empty($_FILES['orders_file']) || $_FILES['orders_file']['error'] !== UPLOAD_ERR_OK) {
                    throw new Exception("Brak pliku do podglądu lub wystąpił błąd");
                }
                $filePath = $_FILES['orders_file']['tmp_name'];
            } else {
                $filePath = $_POST['orders_path'] ?? '';
                if (empty($filePath) || !file_exists($filePath)) {
                    throw new Exception("Nie znaleziono wybranego pliku");
                }
            }
            
            // Open and read CSV
            $handle = fopen($filePath, 'r');
            if (!$handle) {
                throw new Exception("Nie można otworzyć pliku CSV");
            }
            
            // Remove BOM if present
            $bom = fread($handle, 3);
            if ($bom !== "\xEF\xBB\xBF") {
                rewind($handle);
            }
            
            // Read headers
            $headers = fgetcsv($handle);
            if (!$headers) {
                fclose($handle);
                throw new Exception("Brak nagłówków w pliku");
            }
            
            // Clean headers
            $headers = array_map('trim', $headers);
            
            // Read sample rows and categorize them
            $orderRows = [];
            $itemRows = [];
            $lineNum = 0;
            
            while (($row = fgetcsv($handle)) !== false && $lineNum < 20) {
                $lineNum++;
                
                // Check if row has data
                if (empty(array_filter($row))) {
                    continue;
                }
                
                // Find Parent Entry ID column index
                $parentIdIndex = array_search('Parent Entry ID', $headers);
                
                if ($parentIdIndex !== false) {
                    $parentId = trim($row[$parentIdIndex] ?? '');
                    
                    if (empty($parentId)) {
                        // This is an order row (parent)
                        $orderRows[] = $row;
                    } else {
                        // This is an order item row (child)
                        $itemRows[] = $row;
                    }
                }
            }
            
            fclose($handle);
            
            // Render preview with manual mapping
            $this->renderOrdersPreviewWithMapping($headers, $orderRows, $itemRows);
            
        } catch (Exception $e) {
            echo '<div class="alert alert-danger">
                    <i class="fas fa-exclamation-triangle"></i>
                    Błąd: ' . htmlspecialchars($e->getMessage()) . '
                  </div>';
        }
    }
    
    /**
     * Render preview with manual column mapping for orders
     */
    private function renderOrdersPreviewWithMapping($headers, $orderRows, $itemRows)
    {
        // Database fields for orders
        $orderFields = [
            'customer' => 'Klient / Zamawiający',
            'order_type' => 'Tryb zamówienia',
            'delivery_date' => 'Data realizacji',
            'message' => 'Wiadomość',
            'customer_name' => 'Imię',
            'order_id_external' => 'ID pozycji (zamówienia)'
        ];
        
        // Database fields for order items
        $itemFields = [
            'quantity' => 'Ilość',
            'product_name' => 'Nazwa produktu',
            'product_category' => 'Kategoria produktu',
            'product_id_external' => 'Id produktu',
            'price' => 'Cena',
            'item_id_external' => 'ID pozycji (pozycji)',
            'parent_order_id' => 'Parent Entry ID'
        ];
        
        echo '<div class="section-header">
                <h5 class="mb-0"><i class="fas fa-table"></i> Mapowanie Kolumn</h5>
              </div>';
        
        // Orders mapping table
        echo '<h6 class="mt-3"><i class="fas fa-shopping-cart"></i> Pola Zamówienia</h6>';
        echo '<div class="table-responsive mb-4">
                <table class="table table-sm table-bordered mapping-table">
                    <thead>
                        <tr>
                            <th>Pole w Bazie Danych</th>
                            <th>Kolumna w CSV</th>
                        </tr>
                    </thead>
                    <tbody>';
        
        foreach ($orderFields as $dbField => $label) {
            $autoSelected = '';
            foreach ($headers as $csvCol) {
                if (stripos($csvCol, $label) !== false || 
                    stripos($csvCol, $dbField) !== false ||
                    ($dbField === 'customer' && stripos($csvCol, 'Klient') !== false) ||
                    ($dbField === 'order_type' && stripos($csvCol, 'Tryb') !== false) ||
                    ($dbField === 'delivery_date' && stripos($csvCol, 'Data realizacji') !== false) ||
                    ($dbField === 'message' && stripos($csvCol, 'Wiadomość') !== false) ||
                    ($dbField === 'customer_name' && stripos($csvCol, 'Imię') !== false) ||
                    ($dbField === 'order_id_external' && stripos($csvCol, 'ID pozycji') !== false && stripos($csvCol, 'pozycji') === false)) {
                    $autoSelected = $csvCol;
                    break;
                }
            }
            
            echo '<tr>
                    <td><strong>' . htmlspecialchars($label) . '</strong><br><small class="text-muted">' . $dbField . '</small></td>
                    <td>
                        <select class="form-select form-select-sm order-field-mapping" data-db-field="' . $dbField . '">
                            <option value="">-- Wybierz kolumnę --</option>';
            foreach ($headers as $csvCol) {
                $selected = ($csvCol === $autoSelected) ? 'selected' : '';
                echo '<option value="' . htmlspecialchars($csvCol) . '" ' . $selected . '>' . htmlspecialchars($csvCol) . '</option>';
            }
            echo '      </select>
                    </td>
                  </tr>';
        }
        
        echo '  </tbody>
                </table>
              </div>';
        
        // Order Items mapping table
        echo '<h6 class="mt-4"><i class="fas fa-box"></i> Pola Pozycji Zamówienia</h6>';
        echo '<div class="table-responsive mb-4">
                <table class="table table-sm table-bordered mapping-table">
                    <thead>
                        <tr>
                            <th>Pole w Bazie Danych</th>
                            <th>Kolumna w CSV</th>
                        </tr>
                    </thead>
                    <tbody>';
        
        foreach ($itemFields as $dbField => $label) {
            $autoSelected = '';
            foreach ($headers as $csvCol) {
                if (stripos($csvCol, $label) !== false || 
                    stripos($csvCol, $dbField) !== false ||
                    ($dbField === 'quantity' && stripos($csvCol, 'Ilość') !== false) ||
                    ($dbField === 'product_name' && stripos($csvCol, 'Nazwa') !== false) ||
                    ($dbField === 'product_category' && stripos($csvCol, 'Kategoria') !== false) ||
                    ($dbField === 'product_id_external' && $csvCol === 'Id') ||
                    ($dbField === 'price' && stripos($csvCol, 'Cena') !== false) ||
                    ($dbField === 'parent_order_id' && stripos($csvCol, 'Parent Entry ID') !== false)) {
                    $autoSelected = $csvCol;
                    break;
                }
            }
            
            echo '<tr>
                    <td><strong>' . htmlspecialchars($label) . '</strong><br><small class="text-muted">' . $dbField . '</small></td>
                    <td>
                        <select class="form-select form-select-sm item-field-mapping" data-db-field="' . $dbField . '">
                            <option value="">-- Wybierz kolumnę --</option>';
            foreach ($headers as $csvCol) {
                $selected = ($csvCol === $autoSelected) ? 'selected' : '';
                echo '<option value="' . htmlspecialchars($csvCol) . '" ' . $selected . '>' . htmlspecialchars($csvCol) . '</option>';
            }
            echo '      </select>
                    </td>
                  </tr>';
        }
        
        echo '  </tbody>
                </table>
              </div>';
        
        // Sample data preview
        echo '<div class="section-header mt-4">
                <h5 class="mb-0"><i class="fas fa-eye"></i> Podgląd Danych</h5>
              </div>';
        
        if (!empty($orderRows)) {
            echo '<h6 class="mt-3"><i class="fas fa-shopping-cart text-primary"></i> Przykładowe Zamówienia</h6>';
            echo '<div class="table-responsive mb-4">
                    <table class="table table-sm table-striped table-bordered">
                        <thead class="table-light">
                            <tr>';
            foreach ($headers as $header) {
                echo '<th>' . htmlspecialchars($header) . '</th>';
            }
            echo '      </tr>
                        </thead>
                        <tbody>';
            foreach (array_slice($orderRows, 0, 3) as $row) {
                echo '<tr>';
                foreach ($row as $cell) {
                    echo '<td>' . htmlspecialchars($cell) . '</td>';
                }
                echo '</tr>';
            }
            echo '  </tbody>
                    </table>
                  </div>';
        }
        
        if (!empty($itemRows)) {
            echo '<h6 class="mt-3"><i class="fas fa-box text-success"></i> Przykładowe Pozycje</h6>';
            echo '<div class="table-responsive mb-4">
                    <table class="table table-sm table-striped table-bordered">
                        <thead class="table-light">
                            <tr>';
            foreach ($headers as $header) {
                echo '<th>' . htmlspecialchars($header) . '</th>';
            }
            echo '      </tr>
                        </thead>
                        <tbody>';
            foreach (array_slice($itemRows, 0, 5) as $row) {
                echo '<tr>';
                foreach ($row as $cell) {
                    echo '<td>' . htmlspecialchars($cell) . '</td>';
                }
                echo '</tr>';
            }
            echo '  </tbody>
                    </table>
                  </div>';
        }
    }
    
    /**
     * Read CSV preview (first N rows)
     */
    private function readCsvPreview($file, $limit = 5)
    {
        $handle = fopen($file, 'r');
        if (!$handle) {
            throw new \Exception("Nie można otworzyć pliku");
        }
        
        // Read header
        $header = fgetcsv($handle);
        if (!$header) {
            fclose($handle);
            throw new \Exception("Pusty plik CSV");
        }
        
        // Clean headers
        $cleanHeaders = array_map(function($h) {
            return trim($h, "\xEF\xBB\xBF \t\n\r\0\x0B");
        }, $header);
        
        // Read sample rows
        $rows = [];
        $count = 0;
        while (($row = fgetcsv($handle)) !== false && $count < $limit) {
            if (count($row) >= count($header)) {
                $rows[] = array_combine($cleanHeaders, $row);
                $count++;
            }
        }
        
        fclose($handle);
        
        return [
            'headers' => $cleanHeaders,
            'rows' => $rows
        ];
    }
    
    /**
     * Analyze orders columns mapping
     */
    private function analyzeOrdersColumns($headers)
    {
        $mapping = [];
        
        foreach ($headers as $header) {
            // ID pozycji is the linking field
            if (stripos($header, 'ID pozycji') !== false) {
                $mapping['entry_id'] = $header;
            }
            if (stripos($header, 'order') !== false && stripos($header, 'id') !== false) {
                $mapping['order_id'] = $header;
            }
            if (stripos($header, 'customer') !== false || stripos($header, 'name') !== false) {
                $mapping['customer_name'] = $header;
            }
            if (stripos($header, 'phone') !== false || stripos($header, 'tel') !== false) {
                $mapping['customer_phone'] = $header;
            }
            if (stripos($header, 'email') !== false) {
                $mapping['customer_email'] = $header;
            }
            if (stripos($header, 'address') !== false || stripos($header, 'adres') !== false) {
                $mapping['customer_address'] = $header;
            }
            if (stripos($header, 'order_date') !== false || stripos($header, 'data zamówienia') !== false) {
                $mapping['order_date'] = $header;
            }
            if (stripos($header, 'delivery') !== false || stripos($header, 'dostaw') !== false) {
                $mapping['delivery_date'] = $header;
            }
        }
        
        return $mapping;
    }
    
    /**
     * Analyze items columns mapping
     */
    private function analyzeItemsColumns($headers)
    {
        $mapping = [];
        
        foreach ($headers as $header) {
            if (stripos($header, 'Parent Entry ID') !== false) {
                $mapping['parent_id'] = $header;
            }
            if (stripos($header, 'Nazwa') !== false) {
                $mapping['product_name'] = $header;
            }
            if (stripos($header, 'Ilo') !== false) {
                $mapping['quantity'] = $header;
            }
            if ($header === 'Id') {
                $mapping['product_id'] = $header;
            }
            if (stripos($header, 'Cena') !== false) {
                $mapping['price'] = $header;
            }
        }
        
        return $mapping;
    }
    
    /**
     * Find sample order-items connection
     */
    private function findSampleConnection($ordersRows, $itemsRows)
    {
        if (empty($ordersRows) || empty($itemsRows)) {
            return null;
        }
        
        // Try to find first order that has items
        // Parent Entry ID in items links to 'ID pozycji' in orders
        foreach ($ordersRows as $order) {
            $entryId = null;
            
            // Find ID pozycji in this order
            foreach ($order as $key => $value) {
                if (stripos($key, 'ID pozycji') !== false) {
                    $entryId = trim($value);
                    break;
                }
            }
            
            if ($entryId) {
                $matchingItems = [];
                
                // Find items with matching Parent Entry ID
                foreach ($itemsRows as $item) {
                    foreach ($item as $key => $value) {
                        if (stripos($key, 'Parent Entry ID') !== false && trim($value) == $entryId) {
                            $matchingItems[] = $item;
                            break;
                        }
                    }
                }
                
                if (!empty($matchingItems)) {
                    return [
                        'order' => $order,
                        'entry_id' => $entryId,
                        'items' => $matchingItems
                    ];
                }
            }
        }
        
        return null;
    }
    
    /**
     * Render preview HTML
     */
    private function renderPreview($ordersPreview, $itemsPreview, $ordersMapping, $itemsMapping, $sampleConnection)
    {
        ?>
        <!-- Column Mapping Section -->
        <div class="row mb-4">
            <div class="col-md-6">
                <h5 class="text-primary">
                    <i class="fas fa-table me-2"></i>Mapowanie kolumn - Zamówienia
                </h5>
                <div class="table-responsive">
                    <table class="table table-sm table-bordered">
                        <thead class="table-light">
                            <tr>
                                <th>Pole systemu</th>
                                <th>Kolumna w CSV</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($ordersMapping as $field => $column): ?>
                            <tr>
                                <td><code><?= htmlspecialchars($field) ?></code></td>
                                <td><strong><?= htmlspecialchars($column) ?></strong></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
                <div class="alert alert-info alert-sm">
                    <small><i class="fas fa-file-csv me-1"></i>Znaleziono <?= count($ordersPreview['rows']) ?> przykładowych zamówień</small>
                </div>
            </div>
            
            <div class="col-md-6">
                <h5 class="text-success">
                    <i class="fas fa-table me-2"></i>Mapowanie kolumn - Produkty
                </h5>
                <div class="table-responsive">
                    <table class="table table-sm table-bordered">
                        <thead class="table-light">
                            <tr>
                                <th>Pole systemu</th>
                                <th>Kolumna w CSV</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($itemsMapping as $field => $column): ?>
                            <tr>
                                <td><code><?= htmlspecialchars($field) ?></code></td>
                                <td><strong><?= htmlspecialchars($column) ?></strong></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
                <div class="alert alert-info alert-sm">
                    <small><i class="fas fa-boxes me-1"></i>Znaleziono <?= count($itemsPreview['rows']) ?> przykładowych produktów</small>
                </div>
            </div>
        </div>
        
        <!-- Sample Connection -->
        <?php if ($sampleConnection): ?>
        <div class="alert alert-success">
            <h5><i class="fas fa-link me-2"></i>Przykład połączenia Zamówienie ↔ Produkty</h5>
            <p class="mb-3">
                <strong>Schemat połączenia:</strong> 
                <code>ID pozycji</code> (zamówienie) = <code>Parent Entry ID</code> (produkty)
            </p>
            
            <div class="card mb-3">
                <div class="card-header bg-primary text-white">
                    <strong>Zamówienie - ID pozycji: <?= htmlspecialchars($sampleConnection['entry_id']) ?></strong>
                </div>
                <div class="card-body">
                    <div class="row">
                        <?php foreach ($sampleConnection['order'] as $key => $value): ?>
                            <?php if (!empty($value) && strlen($value) < 100): ?>
                            <div class="col-md-6 mb-2">
                                <small class="text-muted"><?= htmlspecialchars($key) ?>:</small><br>
                                <strong><?= htmlspecialchars($value) ?></strong>
                            </div>
                            <?php endif; ?>
                        <?php endforeach; ?>
                    </div>
                </div>
            </div>
            
            <div class="card">
                <div class="card-header bg-success text-white">
                    <strong>Produkty w tym zamówieniu (<?= count($sampleConnection['items']) ?>)</strong>
                    <br><small>Parent Entry ID = <?= htmlspecialchars($sampleConnection['entry_id']) ?></small>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-sm">
                            <thead>
                                <tr>
                                    <th>Parent Entry ID</th>
                                    <th>Nazwa produktu</th>
                                    <th>Ilość</th>
                                    <th>ID produktu</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($sampleConnection['items'] as $item): ?>
                                <tr>
                                    <td><span class="badge bg-info"><?= htmlspecialchars($item['Parent Entry ID'] ?? '-') ?></span></td>
                                    <td><?= htmlspecialchars($item['Nazwa'] ?? '-') ?></td>
                                    <td><?= htmlspecialchars($item[$itemsMapping['quantity']] ?? '-') ?></td>
                                    <td><?= htmlspecialchars($item['Id'] ?? '-') ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
        <?php else: ?>
        <div class="alert alert-warning">
            <i class="fas fa-exclamation-triangle me-2"></i>
            Nie znaleziono przykładowego połączenia między zamówieniami a produktami.
            <br><strong>Sprawdź schemat:</strong> <code>Parent Entry ID</code> (plik produktów) = <code>ID pozycji</code> (plik zamówień)
        </div>
        <?php endif; ?>
        
        <!-- Sample Data Tables -->
        <div class="row mt-4">
            <div class="col-12">
                <h5><i class="fas fa-eye me-2"></i>Podgląd pierwszych wierszy</h5>
                
                <ul class="nav nav-tabs" role="tablist">
                    <li class="nav-item">
                        <a class="nav-link active" data-bs-toggle="tab" href="#ordersTab">
                            Zamówienia (<?= count($ordersPreview['rows']) ?> wierszy)
                        </a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" data-bs-toggle="tab" href="#itemsTab">
                            Produkty (<?= count($itemsPreview['rows']) ?> wierszy)
                        </a>
                    </li>
                </ul>
                
                <div class="tab-content border border-top-0 p-3">
                    <div id="ordersTab" class="tab-pane fade show active">
                        <div class="table-responsive">
                            <table class="table table-sm table-striped">
                                <thead class="table-dark">
                                    <tr>
                                        <?php foreach ($ordersPreview['headers'] as $header): ?>
                                            <th><?= htmlspecialchars($header) ?></th>
                                        <?php endforeach; ?>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($ordersPreview['rows'] as $row): ?>
                                    <tr>
                                        <?php foreach ($row as $value): ?>
                                            <td><?= htmlspecialchars(strlen($value) > 50 ? substr($value, 0, 50) . '...' : $value) ?></td>
                                        <?php endforeach; ?>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                    
                    <div id="itemsTab" class="tab-pane fade">
                        <div class="table-responsive">
                            <table class="table table-sm table-striped">
                                <thead class="table-dark">
                                    <tr>
                                        <?php foreach ($itemsPreview['headers'] as $header): ?>
                                            <th><?= htmlspecialchars($header) ?></th>
                                        <?php endforeach; ?>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($itemsPreview['rows'] as $row): ?>
                                    <tr>
                                        <?php foreach ($row as $value): ?>
                                            <td><?= htmlspecialchars(strlen($value) > 50 ? substr($value, 0, 50) . '...' : $value) ?></td>
                                        <?php endforeach; ?>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <?php
    }

    /**
     * Download migration report
     */
    public function downloadReport()
    {
        if (!isset($_SESSION['migration_errors'])) {
            $_SESSION['error'] = 'Brak raportu do pobrania';
            header('Location: /samanta_crm/migration');
            exit;
        }

        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="migration_errors_' . date('Y-m-d_His') . '.csv"');
        
        $output = fopen('php://output', 'w');
        
        // Add BOM for UTF-8
        fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
        
        // Header
        fputcsv($output, ['Linia', 'Błąd', 'Dane']);
        
        // Errors
        foreach ($_SESSION['migration_errors'] as $error) {
            fputcsv($output, [
                $error['line'],
                $error['error'],
                $error['data']
            ]);
        }
        
        fclose($output);
        exit;
    }

    /**
     * Show products migration page
     */
    public function showProductsMigration()
    {
        $migrationPath = '../migration_data';
        $csvFiles = [];
        
        if (is_dir($migrationPath)) {
            $files = scandir($migrationPath);
            foreach ($files as $file) {
                if (pathinfo($file, PATHINFO_EXTENSION) === 'csv') {
                    $csvFiles[] = $migrationPath . '/' . $file;
                }
            }
        }
        
        $this->render('migration/products', ['csvFiles' => $csvFiles]);
    }

    /**
     * Preview products data and show column mapping
     */
    public function previewProducts()
    {
        $fileSource = $_POST['file_source'] ?? 'upload';
        
        // Get file path
        if ($fileSource === 'upload') {
            if (!isset($_FILES['products_base_file']) || $_FILES['products_base_file']['error'] !== UPLOAD_ERR_OK) {
                echo '<div class="alert alert-danger">Błąd przesyłania pliku bazy produktów</div>';
                return;
            }
            $baseFile = $_FILES['products_base_file']['tmp_name'];
        } else {
            $basePath = $_POST['products_base_path'] ?? '';
            
            if (empty($basePath) || strpos(realpath($basePath), realpath('../migration_data')) !== 0) {
                echo '<div class="alert alert-danger">Nieprawidłowa ścieżka pliku bazy</div>';
                return;
            }
            
            $baseFile = $basePath;
        }
        
        // Read CSV preview
        $basePreview = $this->readCsvPreview($baseFile, 20);
        
        // Analyze column mappings
        $baseMapping = $this->analyzeProductsBaseColumns($basePreview['headers']);
        
        // Render preview
        $this->renderProductsPreviewSimple($basePreview, $baseMapping);
    }

    /**
     * Analyze columns for products base file
     */
    private function analyzeProductsBaseColumns($headers)
    {
        $mapping = [];
        
        foreach ($headers as $header) {
            $lower = mb_strtolower(trim($header));
            
            if ($lower === 'nazwa' || $lower === 'name') {
                $mapping['name'] = $header;
            } elseif ($lower === 'opis' || $lower === 'description') {
                $mapping['description'] = $header;
            } elseif ($lower === 'kategoria' || $lower === 'category') {
                $mapping['category'] = $header;
            } elseif ($lower === 'cena' || $lower === 'price') {
                $mapping['price'] = $header;
            } elseif ($lower === 'za ceną' || $lower === 'price_unit') {
                $mapping['price_unit'] = $header;
            } elseif ($lower === 'id pozycji') {
                // ID pozycji should be used as the product code
                $mapping['code'] = $header;
            } elseif ($lower === 'uwagi' || $lower === 'notes') {
                $mapping['notes'] = $header;
            } elseif ($lower === 'produkcja' || $lower === 'production_site') {
                $mapping['production_site'] = $header;
            } elseif ($lower === 'faktor' || $lower === 'factor') {
                $mapping['factor'] = $header;
            } elseif (stripos($lower, 'jed') !== false && stripos($lower, 'prod') !== false) {
                $mapping['production_unit'] = $header;
            } elseif ($lower === 'status') {
                $mapping['status'] = $header;
            }
            // Note: We removed "Kod" mapping - only "ID pozycji" should be used as code
        }
        
        return $mapping;
    }

    /**
     * Analyze columns for products orders file
     */
    private function analyzeProductsOrdersColumns($headers)
    {
        $mapping = [];
        
        foreach ($headers as $header) {
            $lower = mb_strtolower(trim($header));
            
            if ($lower === 'nazwa' || $lower === 'name') {
                $mapping['name'] = $header;
            } elseif (stripos($lower, 'ilo') === 0 || $lower === 'quantity') {
                $mapping['quantity'] = $header;
            } elseif ($lower === 'kategoria' || $lower === 'category') {
                $mapping['category'] = $header;
            } elseif ($lower === 'cena' || $lower === 'price') {
                $mapping['price'] = $header;
            } elseif ($lower === 'opis' || $lower === 'description') {
                $mapping['description'] = $header;
            } elseif ($lower === 'id') {
                // Id in orders file is the product ID (matches ID pozycji in base)
                $mapping['product_id'] = $header;
            } elseif ($lower === 'id pozycji') {
                // ID pozycji in orders file (column 8)
                $mapping['entry_id'] = $header;
            } elseif ($lower === 'parent entry id') {
                $mapping['parent_entry_id'] = $header;
            }
        }
        
        return $mapping;
    }

    /**
     * Find a sample product that exists in both files
     */
    private function findSampleProductMatch($basePreview, $ordersPreview, $baseMapping, $ordersMapping)
    {
        // Try matching by product_id first
        if (isset($baseMapping['product_id']) && isset($ordersMapping['product_id'])) {
            $baseIdCol = $baseMapping['product_id'];  // ID pozycji
            $ordersIdCol = $ordersMapping['product_id'];  // Id
            
            // Create ID index from base products
            $baseIds = [];
            foreach ($basePreview['rows'] as $row) {
                $id = trim($row[$baseIdCol] ?? '');
                if (!empty($id)) {
                    $baseIds[$id] = $row;
                }
            }
            
            // Find matching product in orders
            foreach ($ordersPreview['rows'] as $row) {
                $id = trim($row[$ordersIdCol] ?? '');
                if (!empty($id) && isset($baseIds[$id])) {
                    return [
                        'base' => $baseIds[$id],
                        'orders' => $row,
                        'product_id' => $id,
                        'match_type' => 'id'
                    ];
                }
            }
        }
        
        // Fallback: try matching by name
        if (isset($baseMapping['name']) && isset($ordersMapping['name'])) {
            $baseNameCol = $baseMapping['name'];
            $ordersNameCol = $ordersMapping['name'];
            
            // Create name index from base products
            $baseNames = [];
            foreach ($basePreview['rows'] as $row) {
                $name = trim($row[$baseNameCol] ?? '');
                if (!empty($name)) {
                    $baseNames[mb_strtolower($name)] = $row;
                }
            }
            
            // Find matching product in orders by name
            foreach ($ordersPreview['rows'] as $row) {
                $name = trim($row[$ordersNameCol] ?? '');
                $nameLower = mb_strtolower($name);
                if (!empty($name) && isset($baseNames[$nameLower])) {
                    return [
                        'base' => $baseNames[$nameLower],
                        'orders' => $row,
                        'product_id' => $name,
                        'match_type' => 'name'
                    ];
                }
            }
        }
        
        return null;
    }

    /**
     * Render products preview HTML
     */
    private function renderProductsPreview($basePreview, $ordersPreview, $baseMapping, $ordersMapping, $sampleMatch)
    {
        ?>
        <!-- Column Mapping Configuration -->
        <div class="card mb-4 border-primary">
            <div class="card-header bg-primary text-white">
                <h5 class="mb-0"><i class="fas fa-sliders-h me-2"></i>Konfiguracja Mapowania Kolumn</h5>
            </div>
            <div class="card-body">
                <div class="alert alert-info">
                    <i class="fas fa-info-circle me-2"></i>
                    Wybierz kolumny które odpowiadają sobie w obu plikach. System automatycznie wykrył najprawdopodobniejsze dopasowania.
                </div>
                
                <div class="row">
                    <!-- Base Products Mapping -->
                    <div class="col-md-6">
                        <h6 class="text-primary mb-3">
                            <i class="fas fa-database me-2"></i>Baza Produktów
                        </h6>
                        
                        <div class="mb-3">
                            <label class="form-label fw-bold">
                                <i class="fas fa-key text-danger"></i> ID Produktu (klucz łączący)
                            </label>
                            <select class="form-select" id="base_id_column">
                                <option value="">-- Wybierz kolumnę --</option>
                                <?php foreach ($basePreview['headers'] as $header): ?>
                                    <option value="<?= htmlspecialchars($header) ?>" 
                                        <?= isset($baseMapping['product_id']) && $baseMapping['product_id'] === $header ? 'selected' : '' ?>>
                                        <?= htmlspecialchars($header) ?>
                                    </option>
                                <?php endforeach; ?>
                            </select>
                            <small class="text-muted">Kolumna zawierająca unikalny ID produktu (np. "ID pozycji")</small>
                        </div>
                        
                        <div class="mb-3">
                            <label class="form-label fw-bold">Nazwa Produktu</label>
                            <select class="form-select" id="base_name_column">
                                <option value="">-- Wybierz kolumnę --</option>
                                <?php foreach ($basePreview['headers'] as $header): ?>
                                    <option value="<?= htmlspecialchars($header) ?>"
                                        <?= isset($baseMapping['name']) && $baseMapping['name'] === $header ? 'selected' : '' ?>>
                                        <?= htmlspecialchars($header) ?>
                                    </option>
                                <?php endforeach; ?>
                            </select>
                        </div>
                        
                        <div class="mb-3">
                            <label class="form-label fw-bold">Kategoria</label>
                            <select class="form-select" id="base_category_column">
                                <option value="">-- Wybierz kolumnę --</option>
                                <?php foreach ($basePreview['headers'] as $header): ?>
                                    <option value="<?= htmlspecialchars($header) ?>"
                                        <?= isset($baseMapping['category']) && $baseMapping['category'] === $header ? 'selected' : '' ?>>
                                        <?= htmlspecialchars($header) ?>
                                    </option>
                                <?php endforeach; ?>
                            </select>
                        </div>
                        
                        <div class="mb-3">
                            <label class="form-label fw-bold">Kod Produktu</label>
                            <select class="form-select" id="base_code_column">
                                <option value="">-- Opcjonalne --</option>
                                <?php foreach ($basePreview['headers'] as $header): ?>
                                    <option value="<?= htmlspecialchars($header) ?>"
                                        <?= isset($baseMapping['code']) && $baseMapping['code'] === $header ? 'selected' : '' ?>>
                                        <?= htmlspecialchars($header) ?>
                                    </option>
                                <?php endforeach; ?>
                            </select>
                        </div>
                    </div>
                    
                    <!-- Orders Products Mapping -->
                    <?php if ($ordersPreview): ?>
                    <div class="col-md-6">
                        <h6 class="text-success mb-3">
                            <i class="fas fa-shopping-cart me-2"></i>Produkty z Zamówień
                        </h6>
                        
                        <div class="mb-3">
                            <label class="form-label fw-bold">
                                <i class="fas fa-key text-danger"></i> ID Produktu (klucz łączący)
                            </label>
                            <select class="form-select" id="orders_id_column">
                                <option value="">-- Wybierz kolumnę --</option>
                                <?php foreach ($ordersPreview['headers'] as $header): ?>
                                    <option value="<?= htmlspecialchars($header) ?>"
                                        <?= isset($ordersMapping['product_id']) && $ordersMapping['product_id'] === $header ? 'selected' : '' ?>>
                                        <?= htmlspecialchars($header) ?>
                                    </option>
                                <?php endforeach; ?>
                            </select>
                            <small class="text-muted">Musi odpowiadać ID z bazy produktów (np. "Id")</small>
                        </div>
                        
                        <div class="mb-3">
                            <label class="form-label fw-bold">Nazwa Produktu</label>
                            <select class="form-select" id="orders_name_column">
                                <option value="">-- Wybierz kolumnę --</option>
                                <?php foreach ($ordersPreview['headers'] as $header): ?>
                                    <option value="<?= htmlspecialchars($header) ?>"
                                        <?= isset($ordersMapping['name']) && $ordersMapping['name'] === $header ? 'selected' : '' ?>>
                                        <?= htmlspecialchars($header) ?>
                                    </option>
                                <?php endforeach; ?>
                            </select>
                        </div>
                        
                        <div class="mb-3">
                            <label class="form-label fw-bold">Ilość</label>
                            <select class="form-select" id="orders_quantity_column">
                                <option value="">-- Opcjonalne --</option>
                                <?php foreach ($ordersPreview['headers'] as $header): ?>
                                    <option value="<?= htmlspecialchars($header) ?>"
                                        <?= isset($ordersMapping['quantity']) && $ordersMapping['quantity'] === $header ? 'selected' : '' ?>>
                                        <?= htmlspecialchars($header) ?>
                                    </option>
                                <?php endforeach; ?>
                            </select>
                        </div>
                        
                        <div class="mb-3">
                            <label class="form-label fw-bold">Parent Entry ID</label>
                            <select class="form-select" id="orders_parent_column">
                                <option value="">-- Opcjonalne --</option>
                                <?php foreach ($ordersPreview['headers'] as $header): ?>
                                    <option value="<?= htmlspecialchars($header) ?>"
                                        <?= isset($ordersMapping['parent_entry_id']) && $ordersMapping['parent_entry_id'] === $header ? 'selected' : '' ?>>
                                        <?= htmlspecialchars($header) ?>
                                    </option>
                                <?php endforeach; ?>
                            </select>
                        </div>
                    </div>
                    <?php endif; ?>
                </div>
                
                <div class="text-center mt-3">
                    <button type="button" class="btn btn-primary" id="btnTestMapping">
                        <i class="fas fa-sync-alt me-2"></i>Test Połączenia
                    </button>
                </div>
                
                <!-- Test Results -->
                <div id="testResults" class="mt-3" style="display: none;"></div>
            </div>
        </div>
        
        <!-- Auto-detected Mapping Summary -->
        <div class="row mb-4">
            <div class="col-md-6">
                <h5 class="text-primary">
                    <i class="fas fa-table me-2"></i>Wykryte Mapowanie - Baza
                </h5>
                <div class="table-responsive">
                    <table class="table table-sm table-bordered">
                        <thead class="table-light">
                            <tr>
                                <th>Pole systemu</th>
                                <th>Kolumna w CSV</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($baseMapping as $field => $column): ?>
                            <tr>
                                <td><code><?= htmlspecialchars($field) ?></code></td>
                                <td><strong><?= htmlspecialchars($column) ?></strong></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
            
            <?php if ($ordersPreview): ?>
            <div class="col-md-6">
                <h5 class="text-success">
                    <i class="fas fa-table me-2"></i>Wykryte Mapowanie - Zamówienia
                </h5>
                <div class="table-responsive">
                    <table class="table table-sm table-bordered">
                        <thead class="table-light">
                            <tr>
                                <th>Pole systemu</th>
                                <th>Kolumna w CSV</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($ordersMapping as $field => $column): ?>
                            <tr>
                                <td><code><?= htmlspecialchars($field) ?></code></td>
                                <td><strong><?= htmlspecialchars($column) ?></strong></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
            <?php endif; ?>
        </div>
        
        <!-- Sample Match -->
        <?php if ($sampleMatch): ?>
        <div class="alert alert-success mb-4">
            <h5><i class="fas fa-check-circle me-2"></i>Przykład dopasowania produktu</h5>
            <p class="mb-3">
                <strong>Schemat połączenia:</strong> 
                <?php if ($sampleMatch['match_type'] === 'id'): ?>
                    <code>ID pozycji</code> (baza) = <code>Id</code> (zamówienia)
                    <br><strong>Produkt ID:</strong> <code><?= htmlspecialchars($sampleMatch['product_id']) ?></code>
                <?php else: ?>
                    <code>Nazwa</code> (baza) = <code>Nazwa</code> (zamówienia)
                    <br><strong>Produkt:</strong> <code><?= htmlspecialchars($sampleMatch['product_id']) ?></code>
                    <br><small class="text-warning"><i class="fas fa-info-circle"></i> Dopasowanie przez nazwę (brak wspólnych ID)</small>
                <?php endif; ?>
            </p>
            
            <div class="row">
                <div class="col-md-6">
                    <div class="card">
                        <div class="card-header bg-primary text-white">
                            <strong>Z bazy produktów</strong>
                        </div>
                        <div class="card-body">
                            <?php foreach ($sampleMatch['base'] as $key => $value): ?>
                                <?php if (!empty($value) && strlen($value) < 100): ?>
                                <div class="mb-2">
                                    <small class="text-muted"><?= htmlspecialchars($key) ?>:</small><br>
                                    <strong><?= htmlspecialchars($value) ?></strong>
                                </div>
                                <?php endif; ?>
                            <?php endforeach; ?>
                        </div>
                    </div>
                </div>
                <div class="col-md-6">
                    <div class="card">
                        <div class="card-header bg-success text-white">
                            <strong>Z zamówień</strong>
                        </div>
                        <div class="card-body">
                            <?php foreach ($sampleMatch['orders'] as $key => $value): ?>
                                <?php if (!empty($value) && strlen($value) < 100): ?>
                                <div class="mb-2">
                                    <small class="text-muted"><?= htmlspecialchars($key) ?>:</small><br>
                                    <strong><?= htmlspecialchars($value) ?></strong>
                                </div>
                                <?php endif; ?>
                            <?php endforeach; ?>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <?php else: ?>
        <div class="alert alert-warning mb-4">
            <i class="fas fa-exclamation-triangle me-2"></i>
            Nie znaleziono przykładowego dopasowania między plikami.
            <?php if ($ordersPreview): ?>
            <br><strong>Sprawdź schemat:</strong> <code>ID pozycji</code> (baza produktów) = <code>Id</code> (produkty zamówień)
            <?php endif; ?>
        </div>
        <?php endif; ?>
        
        <!-- Sample Data Tables -->
        <div class="row mt-4">
            <div class="col-12">
                <h5><i class="fas fa-eye me-2"></i>Podgląd pierwszych wierszy</h5>
                
                <ul class="nav nav-tabs" role="tablist">
                    <li class="nav-item">
                        <a class="nav-link active" data-bs-toggle="tab" href="#baseTab">
                            Baza Produktów (<?= count($basePreview['rows']) ?> wierszy)
                        </a>
                    </li>
                    <?php if ($ordersPreview): ?>
                    <li class="nav-item">
                        <a class="nav-link" data-bs-toggle="tab" href="#ordersProductsTab">
                            Produkty z Zamówień (<?= count($ordersPreview['rows']) ?> wierszy)
                        </a>
                    </li>
                    <?php endif; ?>
                </ul>
                
                <div class="tab-content border border-top-0 p-3">
                    <div id="baseTab" class="tab-pane fade show active">
                        <div class="table-responsive">
                            <table class="table table-sm table-striped">
                                <thead class="table-dark">
                                    <tr>
                                        <?php foreach ($basePreview['headers'] as $header): ?>
                                            <th><?= htmlspecialchars($header) ?></th>
                                        <?php endforeach; ?>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($basePreview['rows'] as $row): ?>
                                    <tr>
                                        <?php foreach ($row as $value): ?>
                                            <td><?= htmlspecialchars(strlen($value) > 50 ? substr($value, 0, 50) . '...' : $value) ?></td>
                                        <?php endforeach; ?>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                    
                    <?php if ($ordersPreview): ?>
                    <div id="ordersProductsTab" class="tab-pane fade">
                        <div class="table-responsive">
                            <table class="table table-sm table-striped">
                                <thead class="table-dark">
                                    <tr>
                                        <?php foreach ($ordersPreview['headers'] as $header): ?>
                                            <th><?= htmlspecialchars($header) ?></th>
                                        <?php endforeach; ?>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($ordersPreview['rows'] as $row): ?>
                                    <tr>
                                        <?php foreach ($row as $value): ?>
                                            <td><?= htmlspecialchars(strlen($value) > 50 ? substr($value, 0, 50) . '...' : $value) ?></td>
                                        <?php endforeach; ?>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                    <?php endif; ?>
                </div>
            </div>
        </div>
        
        <div class="alert alert-success mt-4">
            <i class="fas fa-info-circle me-2"></i>
            <strong>Gotowe do importu!</strong><br>
            Kliknij "Potwierdź Import" aby rozpocząć proces importowania produktów do bazy danych.
        </div>
        
        <script>
        document.getElementById('btnTestMapping').addEventListener('click', function() {
            const baseId = document.getElementById('base_id_column').value;
            const ordersId = document.getElementById('orders_id_column')?.value;
            const baseName = document.getElementById('base_name_column').value;
            const ordersName = document.getElementById('orders_name_column')?.value;
            
            if (!baseId || !baseName) {
                alert('Wybierz przynajmniej kolumnę ID i Nazwa dla bazy produktów');
                return;
            }
            
            const testResults = document.getElementById('testResults');
            testResults.style.display = 'block';
            testResults.innerHTML = '<div class="alert alert-info"><i class="fas fa-spinner fa-spin me-2"></i>Testowanie połączenia...</div>';
            
            // Test connection with sample data
            const baseData = <?= json_encode($basePreview['rows']) ?>;
            const ordersData = <?= json_encode($ordersPreview ? $ordersPreview['rows'] : []) ?>;
            const baseHeaders = <?= json_encode($basePreview['headers']) ?>;
            const ordersHeaders = <?= json_encode($ordersPreview ? $ordersPreview['headers'] : []) ?>;
            
            const baseIdIdx = baseHeaders.indexOf(baseId);
            const baseNameIdx = baseHeaders.indexOf(baseName);
            
            let matchFound = false;
            let matchHtml = '';
            
            if (ordersData.length > 0 && ordersId) {
                const ordersIdIdx = ordersHeaders.indexOf(ordersId);
                const ordersNameIdx = ordersHeaders.indexOf(ordersName);
                
                // Build ID map
                const baseIdMap = {};
                baseData.forEach(row => {
                    const id = row[baseIdIdx];
                    if (id) baseIdMap[id.trim()] = row;
                });
                
                // Try to find match
                for (let i = 0; i < ordersData.length; i++) {
                    const orderId = ordersData[i][ordersIdIdx];
                    if (orderId && baseIdMap[orderId.trim()]) {
                        matchFound = true;
                        const baseRow = baseIdMap[orderId.trim()];
                        matchHtml = `
                            <div class="alert alert-success">
                                <h6><i class="fas fa-check-circle me-2"></i>Znaleziono dopasowanie!</h6>
                                <div class="row mt-3">
                                    <div class="col-md-6">
                                        <div class="card border-primary">
                                            <div class="card-header bg-primary text-white">Baza Produktów</div>
                                            <div class="card-body">
                                                <strong>ID:</strong> ${orderId.trim()}<br>
                                                <strong>Nazwa:</strong> ${baseRow[baseNameIdx] || 'N/A'}
                                            </div>
                                        </div>
                                    </div>
                                    <div class="col-md-6">
                                        <div class="card border-success">
                                            <div class="card-header bg-success text-white">Produkty z Zamówień</div>
                                            <div class="card-body">
                                                <strong>ID:</strong> ${orderId.trim()}<br>
                                                <strong>Nazwa:</strong> ${ordersData[i][ordersNameIdx] || 'N/A'}
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        `;
                        break;
                    }
                }
            }
            
            if (!matchFound && ordersData.length > 0) {
                // Try name matching
                const baseNameMap = {};
                baseData.forEach(row => {
                    const name = row[baseNameIdx];
                    if (name) baseNameMap[name.trim().toLowerCase()] = row;
                });
                
                const ordersNameIdx = ordersHeaders.indexOf(ordersName);
                for (let i = 0; i < ordersData.length; i++) {
                    const orderName = ordersData[i][ordersNameIdx];
                    if (orderName && baseNameMap[orderName.trim().toLowerCase()]) {
                        matchFound = true;
                        const baseRow = baseNameMap[orderName.trim().toLowerCase()];
                        matchHtml = `
                            <div class="alert alert-warning">
                                <h6><i class="fas fa-exclamation-triangle me-2"></i>Dopasowanie przez nazwę</h6>
                                <p class="small mb-2">Nie znaleziono dopasowania przez ID, ale znaleziono produkt o tej samej nazwie.</p>
                                <div class="row mt-3">
                                    <div class="col-md-6">
                                        <div class="card border-primary">
                                            <div class="card-header bg-primary text-white">Baza Produktów</div>
                                            <div class="card-body">
                                                <strong>Nazwa:</strong> ${baseRow[baseNameIdx] || 'N/A'}<br>
                                                <strong>ID:</strong> ${baseRow[baseIdIdx] || 'brak'}
                                            </div>
                                        </div>
                                    </div>
                                    <div class="col-md-6">
                                        <div class="card border-success">
                                            <div class="card-header bg-success text-white">Produkty z Zamówień</div>
                                            <div class="card-body">
                                                <strong>Nazwa:</strong> ${ordersData[i][ordersNameIdx] || 'N/A'}<br>
                                                <strong>ID:</strong> ${ordersData[i][ordersHeaders.indexOf(ordersId)] || 'brak'}
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        `;
                        break;
                    }
                }
            }
            
            if (matchFound) {
                testResults.innerHTML = matchHtml;
            } else {
                testResults.innerHTML = `
                    <div class="alert alert-danger">
                        <i class="fas fa-times-circle me-2"></i>
                        <strong>Nie znaleziono dopasowania</strong><br>
                        Sprawdź czy wybrane kolumny ID zawierają wspólne wartości w pierwszych 10 wierszach obu plików.
                        ${ordersData.length === 0 ? '<br><small>Plik zamówień nie został wybrany.</small>' : ''}
                    </div>
                `;
            }
        });
        </script>
        <?php
    }

    /**
     * Render simplified products preview HTML (base file only)
     */
    private function renderProductsPreviewSimple($basePreview, $baseMapping)
    {
        ?>
        <!-- Column Mapping Summary -->
        <div class="card mb-4 border-primary">
            <div class="card-header bg-primary text-white">
                <h5 class="mb-0"><i class="fas fa-table me-2"></i>Mapowanie Kolumn</h5>
            </div>
            <div class="card-body">
                <div class="row">
                    <div class="col-md-6">
                        <h6 class="text-primary mb-3">Wykryte Mapowanie</h6>
                        <div class="table-responsive">
                            <table class="table table-sm table-bordered">
                                <thead class="table-light">
                                    <tr>
                                        <th>Pole w Bazie Danych</th>
                                        <th>Kolumna w CSV</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($baseMapping as $field => $column): ?>
                                    <tr>
                                        <td>
                                            <code><?= htmlspecialchars($field) ?></code>
                                            <?php if ($field === 'product_id'): ?>
                                                <i class="fas fa-key text-danger ms-1" title="Klucz główny"></i>
                                            <?php endif; ?>
                                        </td>
                                        <td><strong><?= htmlspecialchars($column) ?></strong></td>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                    
                    <div class="col-md-6">
                        <h6 class="text-info mb-3">Informacje</h6>
                        <div class="alert alert-info">
                            <ul class="mb-0 small">
                                <li><strong>Znalezionych produktów:</strong> <?= count($basePreview['rows']) ?> (podgląd)</li>
                                <li><strong>Wykrytych kolumn:</strong> <?= count($baseMapping) ?></li>
                                <li><strong>Kategorie:</strong> Zostaną utworzone automatycznie</li>
                                <li><strong>Duplikaty:</strong> Sprawdzanie po kodzie produktu</li>
                            </ul>
                        </div>
                        
                        <div class="alert alert-warning">
                            <strong><i class="fas fa-exclamation-triangle me-2"></i>Uwaga:</strong>
                            <ul class="mb-0 small">
                                <li>Jeśli <code>Kod</code> jest pusty → użyje <code>ID pozycji</code></li>
                                <li>Status <code>notactive</code> → produkty nieaktywne</li>
                                <li>Produkty z duplikatami kodu zostaną pominięte</li>
                            </ul>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        
        <!-- Sample Data Preview -->
        <div class="card mb-4">
            <div class="card-header bg-light">
                <h5 class="mb-0"><i class="fas fa-eye me-2"></i>Podgląd Pierwszych Produktów</h5>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table table-sm table-striped table-hover">
                        <thead class="table-dark">
                            <tr>
                                <?php 
                                $displayFields = ['name', 'category', 'code', 'price', 'price_unit', 'status', 'product_id'];
                                foreach ($displayFields as $field): 
                                    if (isset($baseMapping[$field])): 
                                ?>
                                    <th><?= htmlspecialchars($baseMapping[$field]) ?></th>
                                <?php 
                                    endif;
                                endforeach; 
                                ?>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach (array_slice($basePreview['rows'], 0, 10) as $row): ?>
                            <tr>
                                <?php foreach ($displayFields as $field): ?>
                                    <?php if (isset($baseMapping[$field])): ?>
                                        <td><?= htmlspecialchars(substr($row[$baseMapping[$field]] ?? '', 0, 50)) ?></td>
                                    <?php endif; ?>
                                <?php endforeach; ?>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
                <p class="text-muted small mb-0 mt-2">
                    <i class="fas fa-info-circle me-1"></i>
                    Pokazano pierwszych 10 produktów z pliku. Import przetworzy wszystkie wiersze.
                </p>
            </div>
        </div>
        
        <div class="alert alert-success">
            <i class="fas fa-check-circle me-2"></i>
            <strong>Gotowe do importu!</strong><br>
            Kliknij "Potwierdź Import" aby rozpocząć proces importowania produktów do bazy danych.
            Kategorie które nie istnieją zostaną utworzone automatycznie.
        </div>
        <?php
    }

    /**
     * Import products from CSV files
     */
    public function importProducts()
    {
        header('Content-Type: application/json');
        
        try {
            error_log("Import products called - POST data: " . print_r($_POST, true));
            error_log("Import products called - FILES: " . print_r($_FILES, true));
            
            $fileSource = $_POST['file_source'] ?? 'upload';
            
            // Get file path
            if ($fileSource === 'upload') {
                if (!isset($_FILES['products_base_file'])) {
                    throw new Exception('Brak pliku w $_FILES');
                }
                if ($_FILES['products_base_file']['error'] !== UPLOAD_ERR_OK) {
                    throw new Exception('Błąd przesyłania pliku: ' . $_FILES['products_base_file']['error']);
                }
                $baseFile = $_FILES['products_base_file']['tmp_name'];
                error_log("Using uploaded file: " . $baseFile);
            } else {
                $basePath = $_POST['products_base_path'] ?? '';
                if (empty($basePath)) {
                    throw new Exception('Brak ścieżki pliku');
                }
                if (strpos(realpath($basePath), realpath('../migration_data')) !== 0) {
                    throw new Exception('Nieprawidłowa ścieżka pliku: ' . $basePath);
                }
                $baseFile = $basePath;
                error_log("Using server file: " . $baseFile);
            }
            
            // Read all data
            $handle = fopen($baseFile, 'r');
            if (!$handle) {
                throw new Exception('Nie można otworzyć pliku');
            }
            
            // Remove BOM if present
            $bom = fread($handle, 3);
            if ($bom !== "\xEF\xBB\xBF") {
                rewind($handle);
            }
            
            // Read headers
            $headers = fgetcsv($handle);
            if (!$headers) {
                throw new Exception('Brak nagłówków w pliku');
            }
            
            // Analyze mapping
            $mapping = $this->analyzeProductsBaseColumns($headers);
            error_log("importProducts - Mapping result: " . print_r($mapping, true));
            error_log("importProducts - Headers: " . print_r($headers, true));
            
            if (!isset($mapping['name'])) {
                fclose($handle);
                header('Content-Type: application/json');
                echo json_encode([
                    'success' => false,
                    'message' => 'Nie znaleziono kolumny "Nazwa" w pliku CSV. Sprawdź plik i spróbuj ponownie.'
                ]);
                return;
            }
            
            // Prepare stats
            $stats = [
                'imported' => 0,
                'skipped' => 0,
                'categories_created' => 0,
                'errors' => []
            ];
            
            // Category cache
            $categoryCache = [];
            $stmt = $this->db->query("SELECT id, name FROM categories");
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $categoryCache[mb_strtolower($row['name'])] = $row['id'];
            }
            
            // Prepare insert statement
            $insertStmt = $this->db->prepare("
                INSERT INTO products (code, name, description, category_id, price, price_unit, notes, 
                    production_site, factor, production_unit, status, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
                ON DUPLICATE KEY UPDATE name = VALUES(name), description = VALUES(description),
                    category_id = VALUES(category_id), price = VALUES(price), updated_at = NOW()
            ");
            
            // Process rows
            $lineNum = 1;
            while (($row = fgetcsv($handle)) !== false) {
                $lineNum++;
                
                try {
                    // Debug: log first few rows
                    if ($lineNum <= 5) {
                        error_log("Line $lineNum data: " . print_r($row, true));
                        error_log("Mapping: " . print_r($mapping, true));
                    }
                    
                    // Get values
                    if (!isset($mapping['name'])) {
                        $stats['errors'][] = "Linia $lineNum: Brak mapowania dla kolumny 'name'";
                        $stats['skipped']++;
                        continue;
                    }
                    
                    $nameIndex = array_search($mapping['name'], $headers);
                    $name = trim($row[$nameIndex] ?? '');
                    
                    if (empty($name)) {
                        if ($lineNum <= 5) {
                            error_log("Line $lineNum: Empty name at index $nameIndex");
                        }
                        $stats['skipped']++;
                        continue;
                    }
                    
                    // Get code
                    $code = '';
                    if (isset($mapping['code'])) {
                        $codeIndex = array_search($mapping['code'], $headers);
                        $code = trim($row[$codeIndex] ?? '');
                    }
                    if (empty($code) && isset($mapping['product_id'])) {
                        $idIndex = array_search($mapping['product_id'], $headers);
                        $code = trim($row[$idIndex] ?? '');
                    }
                    if (empty($code)) {
                        $code = 'PROD_' . $lineNum;
                    }
                    
                    // Category
                    $categoryId = null;
                    if (isset($mapping['category'])) {
                        $catIndex = array_search($mapping['category'], $headers);
                        $categoryName = trim($row[$catIndex] ?? '');
                        if (!empty($categoryName)) {
                            $categoryKey = mb_strtolower($categoryName);
                            if (!isset($categoryCache[$categoryKey])) {
                                // Create category
                                $catStmt = $this->db->prepare("INSERT INTO categories (name) VALUES (?) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)");
                                $catStmt->execute([$categoryName]);
                                $categoryId = $this->db->lastInsertId();
                                $categoryCache[$categoryKey] = $categoryId;
                                $stats['categories_created']++;
                            } else {
                                $categoryId = $categoryCache[$categoryKey];
                            }
                        }
                    }
                    
                    // Get other fields using array_search for each mapped field
                    $description = '';
                    if (isset($mapping['description'])) {
                        $descIndex = array_search($mapping['description'], $headers);
                        $description = trim($row[$descIndex] ?? '');
                    }
                    
                    $price = null;
                    if (isset($mapping['price'])) {
                        $priceIndex = array_search($mapping['price'], $headers);
                        $priceVal = trim($row[$priceIndex] ?? '');
                        $price = !empty($priceVal) && is_numeric($priceVal) ? $priceVal : null;
                    }
                    
                    $priceUnit = null;
                    if (isset($mapping['price_unit'])) {
                        $unitIndex = array_search($mapping['price_unit'], $headers);
                        $priceUnit = trim($row[$unitIndex] ?? '');
                    }
                    
                    $notes = '';
                    if (isset($mapping['notes'])) {
                        $notesIndex = array_search($mapping['notes'], $headers);
                        $notes = trim($row[$notesIndex] ?? '');
                    }
                    
                    $productionSite = null;
                    if (isset($mapping['production_site'])) {
                        $siteIndex = array_search($mapping['production_site'], $headers);
                        $productionSite = trim($row[$siteIndex] ?? '');
                    }
                    
                    $factor = null;
                    if (isset($mapping['factor'])) {
                        $factorIndex = array_search($mapping['factor'], $headers);
                        $factorVal = trim($row[$factorIndex] ?? '');
                        $factor = !empty($factorVal) && is_numeric($factorVal) ? $factorVal : null;
                    }
                    
                    $productionUnit = null;
                    if (isset($mapping['production_unit'])) {
                        $unitIndex = array_search($mapping['production_unit'], $headers);
                        $productionUnit = trim($row[$unitIndex] ?? '');
                    }
                    
                    $status = 'active';
                    if (isset($mapping['status'])) {
                        $statusIndex = array_search($mapping['status'], $headers);
                        $status = trim($row[$statusIndex] ?? 'active');
                    }
                    
                    // Insert
                    $insertStmt->execute([
                        $code,
                        $name,
                        $description,
                        $categoryId,
                        $price,
                        $priceUnit,
                        $notes,
                        $productionSite,
                        $factor,
                        $productionUnit,
                        $status
                    ]);
                    
                    $stats['imported']++;
                    
                    if ($lineNum <= 5) {
                        error_log("Line $lineNum: Successfully imported '$name' with code '$code'");
                    }
                    
                } catch (Exception $e) {
                    $errorMsg = "Linia $lineNum: " . $e->getMessage();
                    $stats['errors'][] = $errorMsg;
                    $stats['skipped']++;
                    
                    if ($lineNum <= 5) {
                        error_log($errorMsg);
                    }
                }
            }
            
            fclose($handle);
            
            $message = "Zaimportowano {$stats['imported']} produktów";
            if ($stats['categories_created'] > 0) {
                $message .= ", utworzono {$stats['categories_created']} kategorii";
            }
            if ($stats['skipped'] > 0) {
                $message .= ", pominięto {$stats['skipped']} wierszy";
            }
            
            echo json_encode([
                'success' => true,
                'message' => $message,
                'stats' => $stats
            ]);
            
        } catch (Exception $e) {
            echo json_encode([
                'success' => false,
                'message' => $e->getMessage()
            ]);
        }
    }
}
