<?php

// Path to the input CSV files
$inputFile = 'BDGreenValley.csv';
$inputFilePreturi = 'bt-gv-updates-prices.csv';
$inputFileTitluri = 'TitluriEAN.csv';
$inputFileDescriptions = 'GV-descriptions2.csv';
$inputFileKitWeights = 'kit_weights.csv';
$outputMagentoFile = 'magento_import_bd.csv';

// Initialize arrays to store product data
$skuData = [];
$simpleProductsSkus = [];
$bundleProductSkus = [];
$mappedDataBySkuSimple = [];
$mappedDataBySkuBundle = [];
$pricesbySkuFz = [];
$specialPricesBySkuFz = [];
$titlesbySkuFz = [];
$descriptionsBySkuFz = []; // Changed to store by sku_fz
$kitMaxWeightsBySkuFz = [];

// Read descriptions from GVdescriptions2.csv
if (($handle = fopen($inputFileDescriptions, 'r')) !== FALSE) {
    $header = fgetcsv($handle, 1000, ",");

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        // Split the first column by comma or semicolon to handle multiple SKUs
        $skus = preg_split('/[,;]+/', $data[0]);
        $skus = array_map('trim', $skus); // Trim whitespace from SKUs

        // Format the description into HTML
        $formattedDescription = formatDescriptionToHtml($data[1]);

        foreach ($skus as $sku) {
            if (!empty($sku)) {
                $descriptionsBySkuFz[$sku] = $formattedDescription;
            }
        }
    }
    fclose($handle);
}

// Read prices
if (($handle = fopen($inputFilePreturi, 'r')) !== FALSE) {
    $header = fgetcsv($handle, 1000, ",");

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $pricesbySkuFz[$data[0]] = (float)$data[2];
        $specialPricesBySkuFz[$data[0]] = (float)$data[1];
    }
    fclose($handle);
}


// Read max weights for kits
if (($handle = fopen($inputFileKitWeights, 'r')) !== FALSE) {
    $header = fgetcsv($handle, 1000, ",");

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $kitMaxWeightsBySkuFz[$data[0]] = (int)$data[1];
    }
    fclose($handle);
}


// Read titles
if (($handle = fopen($inputFileTitluri, 'r')) !== FALSE) {
    $header = fgetcsv($handle, 1000, ",");

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $titlesbySkuFz[$data[0]] = ['name' => $data[1], 'emkp_ean' => $data[2]];
    }
    fclose($handle);
}

function generateBundleValues($skus): string
{
    $bundleValues = "";
    $uniqueSkus = array_unique($skus);
    $qtys = array_count_values($skus);
    $optionName = implode("_", $skus);

    foreach ($uniqueSkus as $sku) {
        $escapedSku = addslashes($sku);
        $qty = $qtys[$sku];
        $bundleValues .= "name={$optionName},type=multi,required=1,sku={$escapedSku},price=0.0000,default=1,default_qty={$qty}.0000,price_type=fixed,can_change_qty=0|";
    }

    return trim($bundleValues, '|');
}

function formatDescriptionToHtml($description) {
    // Split description into paragraphs (assuming double newline separates paragraphs)
    $paragraphs = preg_split('/\s*\n/', trim($description));

    $formattedDescription = '';

    foreach ($paragraphs as $paragraph) {
        // Trim whitespace
        $paragraph = trim($paragraph);

        // Skip empty paragraphs
        if (empty($paragraph)) {
            continue;
        }

        // Check if the paragraph is a list (starts with - or *)
        if (preg_match('/^[-\*1-9]/', $paragraph)) {
            // Split into list items
            $items = preg_split('/\n[-\*]/', $paragraph);
            $formattedDescription .= '<ul>';
            foreach ($items as $item) {
                if (!empty(trim($item))) {
                    $formattedDescription .= '<li><em>' . trim($item) . '</em></li>';
                }
            }
            $formattedDescription .= '</ol>';
        } else {
            // Regular paragraph
            if (str_contains($paragraph,':')!== false) {
                $formattedDescription .= '<p><strong>' . trim($paragraph) . '</strong></p>';
            } else {
                $formattedDescription .= '<p style="text-align: justify;">' . $paragraph . '</p>';
            }
        }
    }

    return $formattedDescription;
}

function addAdditionalDescriptionInfo(array $product, string $description)
{
    //Caracteristici tehnice:
    //-	Tip plafon (K) - auto_roof_type_bare
    //-	Material (T) - auto_material_bare
    //-	Antifurt (V) - auto_anti_theft_bare
    //-	Greutate suportata (kg) (O) - auto_supported_weight_bare
    //-	Lungime bara (cm) (Q) - auto_length_bare
    //-	Latime bare (cm) (P) - auto_width_bare
    //-	Inaltime (cm) (R) - auto_height_bare
    //-	Garantie (ani) (W) - auto_warranty_bare
    //-	Certificare TUV (Y) - auto_tuv_certification_bare
    //-	City Crush test (Z) - auto_city_crush_test_bare
    //-	Observatii (AA)
    //        'Marca' => 'auto_brand',
    //        'Model' => 'auto_model',
    //        'Caroserie' => 'auto_body_bare',
    //        'Fabricatie' => 'auto_from_to_bare',
    //        'Tip plafon' => 'auto_roof_type_bare',

    $description .= '<p><strong>Caracteristici tehnice:</strong></p><ul style="list-style: circle; padding-left: 3em;">';
    if (isset($product['auto_brand'])) {
        $description .= "<li>Marca: {$product['auto_brand']}</li>";
    }
    if (isset($product['auto_model'])) {
        $description .= "<li>Model: {$product['auto_model']}</li>";
    }
    if (isset($product['auto_body_bare'])) {
        $description .= "<li>Caroserie: {$product['auto_body_bare']}</li>";
    }
    if (isset($product['auto_from_to_bare'])) {
        $description .= "<li>Fabricatie: {$product['auto_from_to_bare']}</li>";
    }
    if (isset($product['auto_roof_type_bare'])) {
        $description .= "<li>Tip plafon: {$product['auto_roof_type_bare']}</li>";
    }
    if (isset($product['auto_material_bare'])) {
        $description .= "<li>Material: " . strtolower($product['auto_material_bare']) . ".</li> ";
    }
    if (isset($product['auto_anti_theft_bare'])) {
        $description .= "<li>Sistem antifurt: " . $product['auto_anti_theft_bare'] . " </li>";
    }
    if (isset($product['auto_supported_weight_bare'])) {
        $description .= "<li>Greutate suportata (kg): " . $product['auto_supported_weight_bare'] . "</li>";
    }
    if (isset($product['auto_length_bare'])) {
        $description .= "<li>Lungime (cm): " . $product['auto_length_bare'] . "</li>";
    }
    if (isset($product['auto_width_bare'])) {
        $description .= "<li>Latime (cm): " . $product['auto_width_bare'] . "</li>";
    }
    if (isset($product['auto_height_bare'])) {
        $description .= "<li>Inaltime (cm): " . $product['auto_height_bare'] . "</li>";
    }

    if (isset($product['auto_warranty_bare'])) {
        $description .= "<li>Garantie: " . $product['auto_warranty_bare'] . " ani.</li> ";
    }
    if (isset($product['auto_tuv_certification_bare']) && $product['auto_tuv_certification_bare'] == 1) {
        $description .= "<li>Certificare TUV.</li> ";
    }
    if (isset($product['auto_city_crush_test_bare']) && $product['auto_city_crush_test_bare'] == 1) {
        $description .= "<li>City Crush test.</li> ";
    }
    if (isset($product['auto_observation_bare']) && !empty($product['auto_observation_bare'])) {
        $description .= "<li>Observatii: {$product['auto_observation_bare']}</li>";
    }
    $description .= "</ul>";

    $product['description'] = $description;
    return $description;
}

function generateDefaultDescription($product) {
    $description = "<p>Green Valley, brand al producătorului francez Aurilis Group, oferă soluții inovatoare și performante pentru transportul bagajelor, echipamentelor sportive și altor materiale. Aceste bare transversale sunt special concepute pentru vehiculele cu bare longitudinale, asigurând o potrivire perfectă și o montare rapidă și ușoară.</p>";
    $description .= "<p><b>";
    if (isset($product['auto_product_type_bare']) && isset($product['auto_type_bare']) && isset($product['auto_brand'])) {
        $description .= "Bare transversale " . $product['auto_product_type_bare'] . " din " . $product['auto_type_bare'] . " pentru " . $product['auto_brand'];
    }
    if (isset($product['auto_model'])) {
        $description .= " " . $product['auto_model'] . " ";
    }
    if (isset($product['auto_body_bare'])) {
        $description .= $product['auto_body_bare'] . " ";
    }
    if (isset($product['auto_from_to_bare'])) {
        $description .= "(" . $product['auto_from_to_bare'] . ") ";
    }
    if (isset($product['auto_roof_type_bare'])) {
        $description .= "cu prindere pe " . strtolower($product['auto_roof_type_bare']);
    }
    $description .= "</b></p>";
    $description .= "<ul>";
    if (isset($product['auto_supported_weight_bare'])) {
        $description .= "<li>Greutate maxima suportata: " . $product['auto_supported_weight_bare'] . " kg. </li>";
    }
    if (isset($product['auto_width_bare']) && isset($product['auto_length_bare'])) {
        $description .= "<li>Dimensiuni: " . $product['auto_width_bare'] . " x " . $product['auto_length_bare'] . " cm. </li>";
    }
    if (isset($product['auto_color_bare'])) {
        $description .= "<li>Culoare: " . strtolower($product['auto_color_bare']) . ".</li> ";
    }
    if (isset($product['auto_material_bare'])) {
        $description .= "<li>Material: " . strtolower($product['auto_material_bare']) . ".</li> ";
    }
    if (isset($product['auto_metal_roof_bare'])) {
        $description .= "<li>Compatibil cu plafon metalic: ". ($product['auto_metal_roof_bare'] === 'Da'? 'Da' : 'Nu');
    }
    if (isset($product['auto_glass_roof_bare'])) {
        $description .= "<li>Compatibil cu plafon de sticla: ". ($product['auto_glass_roof_bare'] === 'Da'? 'Da' : 'Nu');
    }
    if (isset($product['auto_anti_theft_bare']) && $product['auto_anti_theft_bare'] == 1) {
        $description .= "<li>Sistem antifurt inclus. </li>";
    }
    if (isset($product['auto_warranty_bare'])) {
        $description .= "<li>Garantie: " . $product['auto_warranty_bare'] . " ani.</li> ";
    }
    if (isset($product['auto_tuv_certification_bare']) && $product['auto_tuv_certification_bare'] == 1) {
        $description .= "<li>Certificare TUV.</li> ";
    }
    if (isset($product['auto_city_crush_test_bare']) && $product['auto_city_crush_test_bare'] == 1) {
        $description .= "<li>City Crush test.</li> ";
    }
    $description .= "</ul>";

    return $description;
}

if (($handle = fopen($inputFile, 'r')) !== FALSE) {
    $header = fgetcsv($handle, 1000, ",");
    $mappedColumns = [
        'Marca' => 'auto_brand',
        'Model' => 'auto_model',
        'Caroserie' => 'auto_body_bare',
        'Fabricatie' => 'auto_from_to_bare',
        'Tip plafon' => 'auto_roof_type_bare',
        'Titlu' => 'name',
        'Producator' => 'manufacturer',
        "Compatibil cu plafon metalic" => 'auto_metal_roof_bare',
        "Compatibil cu plafon sticla" => 'auto_glass_roof_bare',
        "Tip produs (nu se afiseaza in AGD - pt marketplace)" => 'auto_product_type_bare',
        "Tip (nu se afiseaza)" => 'auto_type_bare',
        "Tip bare (nu se afiseaza)" => 'auto_bar_type_bare',
        "Tip montare (nu se afiseaza - pt marketplace)" => 'auto_mount_type_bare',
        "Greutate suportata (kg)" => 'auto_supported_weight_bare',
        "Latime bare (cm)" => 'auto_width_bare',
        "Lungime bara (cm)" => 'auto_length_bare',
        "Inaltime (cm)" => 'auto_height_bare',
        "Culoare" => 'auto_color_bare',
        "Material" => 'auto_material_bare',
        "Greutate produs" => 'weight',
        "Antifurt" => 'auto_anti_theft_bare',
        "Garantie (ani)" => 'auto_warranty_bare',
        "T-slot" => 'auto_t_slot_bare',
        "Certificare TUV" => 'auto_tuv_certification_bare',
        "City Crush test" => 'auto_city_crush_test_bare',
        'Observatii' => 'auto_observation_bare'
    ];

    $bundleItemColumns = ['Item 1', 'Item 2', 'Item 3', 'Item 4'];

    // First pass: Collect all unique SKUs and their attributes
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $row = array_combine($header, $data);
        $uniqueSimpleSku = $row['Fake SKU'];
        $isBundle = false;
        $bundleItems = [];
        $row['Articulo 1'] = str_replace('.', '', $row['Articulo 1']);
        $row['Articulo 1'] = str_replace(',', '', $row['Articulo 1']);

        // Collect bundle items' SKUs for associated_skus
        foreach ($bundleItemColumns as $col) {
            if (!empty($row[$col]) || $row[$col] !== '') {
                $isBundle = true;
                $row[$col] = str_replace('.', '', $row[$col]);
                $row[$col] = str_replace(',', '', $row[$col]);
                $bundleItems[] = $row[$col];
            }
        }

        if ($isBundle) {
            $simpleProductsSkus[$row['Articulo 1']] = $row['Articulo 1'];
            $currentSku = $row['Articulo 1'];
        } else {
            $simpleProductsSkus[$row['Fake SKU']] = $row['Fake SKU'];
            $currentSku = $row['Fake SKU'];
        }

        $attributes = [];
        $attributes['sku_fz'] = $row['Articulo 1'];
        $attributes['sku'] = $currentSku;


        foreach ($mappedColumns as $key => $value) {
            $attributes[$value] = $row[$key];
        }
        $mappedDataBySkuSimple[$currentSku][] = $attributes;

        if ($isBundle) {
            foreach ($bundleItemColumns as $itemSku) {
                if (strlen($row[$itemSku]) > 0) {
                    $simpleProductsSkus[$row[$itemSku]] = $row[$itemSku];
                    $attributes['sku_fz'] = $row[$itemSku];
                    $attributes['sku'] = $row[$itemSku];
                    $mappedDataBySkuSimple[$row[$itemSku]][] = $attributes;
                }
            }

            $bundleSku = $row['Fake SKU'];
            $bundleSkuIntern = $row['Articulo 1'].'_'.implode('_', $bundleItems);
            //$attributes['name'] = substr($row['Name'], 0, 255);

            $attributes['associated_skus'] = $row['Articulo 1'].','.implode(',', $bundleItems); // Use comma instead of pipe
            $bundleValues = '';
            $bundleSkus = array_merge([$row['Articulo 1']], $bundleItems);
            $bundleValues .= generateBundleValues($bundleSkus);
            $attributes['bundle_values'] = $bundleValues;

            $bundleProductSkus[$bundleSku] = $bundleSku;
            $attributes['sku_intern'] = $bundleSkuIntern;
            $attributes['visibility'] = 'Catalog, Search'; // Set visibility for bundle product
            $attributes['sku_fz'] = $row['Fake SKU'];
            $attributes['sku'] = $row['Fake SKU'];
            $mappedDataBySkuBundle[$bundleSku][] = $attributes;
        }

    }

    $simpleProductsSkusCompacted = [];
    foreach ($mappedDataBySkuSimple as $sku => $productData) {
        $keys = array_keys(current($productData));
        foreach ($keys as $key) {
            $data = array_unique(array_column($productData, $key));
            $simpleProductsSkusCompacted[$sku][$key] = implode(',', $data);
        }
        $simpleProductsSkusCompacted[$sku]['product_type'] = 'simple';
        $simpleProductsSkusCompacted[$sku]['sku_intern'] = '';
        $simpleProductsSkusCompacted[$sku]['associated_skus'] = '';
        $simpleProductsSkusCompacted[$sku]['bundle_values'] = '';

        // Set qty to 0 for bundle products
        $simpleProductsSkusCompacted[$sku]['qty'] = isset($mappedDataBySkuBundle[$sku]) ? '0' : '1';
    }

    $bundleProductsSkusCompacted = [];
    foreach ($mappedDataBySkuBundle as $sku => $productData) {
        $row = current($productData);
        $keys = array_keys(current($productData));
        foreach ($keys as $key) {
            $data = array_unique(array_column($productData, $key));
            $bundleProductsSkusCompacted[$sku][$key] = implode(',', $data);
        }
        $bundleProductsSkusCompacted[$sku]['product_type'] = 'bundle';
    }

    //$allProducts = array_push($simpleProductsSkusCompacted, $bundleProductsSkusCompacted);

    $allProducts = [];

    // Second pass: Add Magento-specific columns and output CSV
    $outputHandle = fopen($outputMagentoFile, 'w');

    // Write header row for Magento import file
    $headerRow = [
        'sku',
        'sku_fz',
        'price',
        'special_price',
        'sku_intern',
        'attribute_set_code',
        'product_type',
        'visibility',
        'categories',
        'name',
        'description',
        'short_description',
        'weight',
        'product_online',
        'tax_class_name',
        'visibility',
        'price',
        'special_price',
        'bundle_price_type',
        'bundle_sku_type',
        'bundle_price_view',
        'bundle_weight_type',
        'bundle_values',
        'associated_skus',
        'bundle_shipment_type',
        'qty',
        'is_in_stock',
        'store_view_code',
        'product_websites',
        'auto_brand',
        'auto_model',
        'auto_body_bare',
        'auto_from_to_bare',
        'auto_roof_type_bare',

        'auto_metal_roof_bare',
        'auto_glass_roof_bare',

        'auto_product_type_bare',
        'auto_type_bare',
        'auto_bar_type_bare',
        'auto_mount_type_bare',

        'auto_supported_weight_bare',
        'auto_width_bare',
        'auto_length_bare',
        'auto_height_bare',
        'auto_color_bare',
        'auto_material_bare',
        'auto_anti_theft_bare',
        'auto_warranty_bare',
        'auto_t_slot_bare',
        'auto_tuv_certification_bare',
        'auto_city_crush_test_bare',
        'termen_livrare',
        'manage_stock',
        'use_config_manage_stock',
        'emkp_ean',
        'manufacturer'
    ];
    fputcsv($outputHandle, $headerRow);

    // Write each unique product row
    foreach ($simpleProductsSkusCompacted as $sku => $product) {
        // Set additional Magento-specific fields
        $product['attribute_set_code'] = $product['product_type'] == 'bundle' ? 'Bare Transversale Bundle' : 'Bare Transversale Simple';
        $product['categories'] = 'Bare Transversale Green Valley';
        $product['description'] = $product['name'] . ' description';
        $product['short_description'] = $product['name'] . ' short description';
        $product['weight'] = $product['product_type'] == 'bundle' ? '' : '1';
        $product['product_online'] = '1';
        $product['tax_class_name'] = 'TVA';
        $product['visibility'] = 'Catalog, Search';
        if (isset($pricesbySkuFz[$product['sku_fz']])) {
            $product['price'] = $pricesbySkuFz[$product['sku_fz']];
        } else {
            $product['price'] = "999.99";
        }
        if (isset($specialPricesBySkuFz[$product['sku']])) {
            $product['special_price'] = $specialPricesBySkuFz[$product['sku']];
        }
        if (isset($kitMaxWeightsBySkuFz[$product['sku_fz']])) {
            $product['auto_supported_weight_bare'] = $kitMaxWeightsBySkuFz[$product['sku_fz']];
        }
        $product['bundle_price_type'] = $product['product_type'] == 'bundle' ? 'Dynamic' : '';
        $product['bundle_sku_type'] = $product['product_type'] == 'bundle' ? 'Dynamic' : '';
        $product['bundle_price_view'] = $product['product_type'] == 'bundle' ? 'Price Range' : '';
        $product['bundle_weight_type'] = $product['product_type'] == 'bundle' ? 'Dynamic' : '';
        $product['bundle_shipment_type'] = $product['product_type'] == 'bundle' ? 'Together' : '';
        $product['qty'] = 0;// isset($simpleProductsSkusCompacted[$sku]['qty']) ? $simpleProductsSkusCompacted[$sku]['qty'] : '1';
        $product['is_in_stock'] = '1';
        $product['product_websites'] = 'base';
        $product['store_view_code'] = 'default';
        $product['manage_stock'] = "1";
        $product['use_config_manage_stock'] = "1";
        $product['emkp_ean'] = '';

        if (isset($titlesbySkuFz[$product['sku_fz']]['emkp_ean'])) {
            $product['emkp_ean'] = $titlesbySkuFz[$product['sku_fz']]['emkp_ean'];
        }

        $simpleProductsSkusCompacted[$sku] = $product;

        $allProducts[$sku] = $product;
    }

    foreach ($bundleProductsSkusCompacted as $sku => $product) {
        // Set additional Magento-specific fields
        $product['attribute_set_code'] = $product['product_type'] == 'bundle' ? 'Bare Transversale Bundle' : 'Bare Transversale Simple';
        $product['categories'] = 'Bare Transversale Green Valley';
        $product['description'] = $product['name'] . ' description';
        $product['short_description'] = $product['name'] . ' short description';
        $product['weight'] = $product['product_type'] == 'bundle' ? '' : '1';
        $product['product_online'] = '1';
        $product['tax_class_name'] = 'TVA';
        $product['visibility'] = 'Catalog, Search';
        $product['price'] = '0.00';
        $product['bundle_price_type'] = $product['product_type'] == 'bundle' ? 'Dynamic' : '';
        $product['bundle_sku_type'] = $product['product_type'] == 'bundle' ? 'Dynamic' : '';
        $product['bundle_price_view'] = $product['product_type'] == 'bundle' ? 'Price Range' : '';
        $product['bundle_weight_type'] = $product['product_type'] == 'bundle' ? 'Dynamic' : '';
        $product['bundle_shipment_type'] = $product['product_type'] == 'bundle' ? 'Together' : '';
        $product['qty'] = $simpleProductsSkusCompacted[$sku]['qty'] ?? '1';
        $product['is_in_stock'] = '1';
        $product['product_websites'] = 'base';
        $product['store_view_code'] = 'default';
        $product['weight'] = 1;
        $product['manage_stock'] = "0";
        $product['use_config_manage_stock'] = "0";
        $product['emkp_ean'] = '';

        if ($product['product_type'] === 'bundle') {
            $simpleSkus = explode('_', $product['sku_intern']);
            $price = 0.00;
            $maxSupportedWeight = 1000;
            foreach ($simpleSkus as $simpleSku) {
                if (isset($pricesbySkuFz[$simpleSku])) {
                    $price += $pricesbySkuFz[$simpleSku];
                }
                if (isset($kitMaxWeightsBySkuFz[$simpleSku])) {
                    $maxSupportedWeight = min((int)$kitMaxWeightsBySkuFz[$simpleSku], (int)$product['auto_supported_weight_bare'], $maxSupportedWeight);
                }
            }
            $product['price'] = $price;
            if ($maxSupportedWeight < 1000) {
                $product['auto_supported_weight_bare'] = $maxSupportedWeight;
            }
        }

        $bundleProductsSkusCompacted[$sku] = $product;

        $allProducts[$sku] = $product;

        if ($product['product_type'] === 'bundle') {
            $simpleSkus = explode('_', $product['sku_intern']);

            $oldDescription = $product['description'];
            $newDescription = '';

            foreach ($simpleSkus as $simpleSku) {
                if (isset($descriptionsBySkuFz[$simpleSku])) {
                    $newDescription .= $descriptionsBySkuFz[$simpleSku];
                }
            }

            if (!empty($newDescription)) {
                $allProducts[$sku]['description'] = $newDescription;
            }

            foreach ($simpleSkus as $simpleSku) {
                $simpleProductsSkusCompacted[$simpleSku]['visibility'] = 'Not Visible Individually';
                $resetAttributes = [
                    'auto_brand',
                    'auto_model',
                    'auto_body_bare',
                    'auto_from_to_bare',
                    'auto_roof_type_bare',
                    'auto_metal_roof_bare',
                    'auto_glass_roof_bare',
                    'auto_product_type_bare',
                    'auto_type_bare',
                    'auto_bar_type_bare',
                    'auto_mount_type_bare',
                    'auto_supported_weight_bare',
                    'auto_width_bare',
                    'auto_length_bare',
                    'auto_height_bare',
                    'auto_color_bare',
                    'auto_material_bare',
                    'auto_anti_theft_bare',
                    'auto_warranty_bare',
                    'auto_t_slot_bare',
                    'auto_tuv_certification_bare',
                    'auto_city_crush_test_bare',
                ];
                foreach ($resetAttributes as $resetAttribute) {
                    $simpleProductsSkusCompacted[$simpleSku][$resetAttribute] = '';
                }

                $allProducts[$simpleSku] = $simpleProductsSkusCompacted[$simpleSku];
            }
        }
    }

    foreach ($allProducts as $sku => $product) {
        // Handle descriptions
        if ($product['visibility'] !== 'Catalog, Search') {
            // For non-visible products
            $product['name'] = substr($product['name'], 0, 510);
            $product['short_description'] = substr($product['short_description'], 0, 510);
            $product['description'] = substr($product['description'], 0, 510);

            if (isset($titlesbySkuFz[$sku]['name'])) {
                $product['name'] = $titlesbySkuFz[$sku]['name'];
                $product['short_description'] = $titlesbySkuFz[$sku]['name'];
                $product['description'] = $titlesbySkuFz[$sku]['name'];
            }

            // Check if custom description exists for sku_fz
            if (isset($product['sku_fz']) && isset($descriptionsBySkuFz[$product['sku_fz']])) {
                $product['description'] = $descriptionsBySkuFz[$product['sku_fz']];

            }

        } else {
            // For visible products
            if (isset($titlesbySkuFz[$sku]['name'])) {
                $product['name'] = $titlesbySkuFz[$sku]['name'];
                $product['short_description'] = $titlesbySkuFz[$sku]['name'];
            }

            if($product['product_type'] === 'simple') {
                if (isset($product['sku_fz']) && isset($descriptionsBySkuFz[$product['sku_fz']])) {
                    $product['description'] = addAdditionalDescriptionInfo($allProducts[$sku], $descriptionsBySkuFz[$product['sku_fz']]);
                    $allProducts[$sku]['description'] = $product['description'];
                }
            } elseif ($product['product_type'] === 'bundle') {
                // If this is a bundle product, also check its components
                $simpleSkus = explode('_', $product['sku_intern']);
                $componentDescriptions = [];
                foreach ($simpleSkus as $simpleSku) {
                    if (isset($descriptionsBySkuFz[$simpleSku])) {
                        $componentDescriptions[] = $descriptionsBySkuFz[$simpleSku];
                    }
                }
                if (!empty($componentDescriptions)) {
                    $product['description'] = implode("\n\n", array_unique($componentDescriptions));
                    $product['description'] = addAdditionalDescriptionInfo($allProducts[$sku], $product['description']);
                    $allProducts[$sku]['description'] = $product['description'];
                }

            }

            // Generate short description
            $shortDescription = "";
            if (isset($product['auto_product_type_bare']) && isset($product['auto_type_bare'])) {
                $shortDescription .= "Bare transversale " . $product['auto_product_type_bare'] . " din " . $product['auto_type_bare'] . " ";
            }
            if (isset($product['auto_supported_weight_bare'])) {
                $shortDescription .= "cu o greutate maxima suportata de " . $product['auto_supported_weight_bare'] . ". ";
            }
            if (isset($product['auto_tuv_certification_bare']) && $product['auto_tuv_certification_bare'] == 1) {
                $shortDescription .= "Certificare TUV. ";
            }
            if (isset($product['auto_anti_theft_bare']) && $product['auto_anti_theft_bare'] == 1) {
                $shortDescription .= "Sistem antifurt inclus. ";
            }
            $product['short_description'] = $shortDescription;
        }

        // Ensure all HTML special characters are properly encoded
        if (!empty($product['description'])) {
            // Only encode text content, not HTML tags
            $product['description'] = preg_replace_callback(
                '/(?<=>)[^<]+(?=<)|^[^<]+(?=<)|(?<=>)[^<]+$/',
                function($match) {
                    return htmlspecialchars($match[0], ENT_QUOTES | ENT_HTML5, 'UTF-8');
                },
                $product['description']
            );
        }
    }

    foreach ($allProducts as $sku => $product) {

        if ($product['product_type'] !== 'bundle' &&
            ($product['product_type'] !== 'simple' || !isset($kitMaxWeightsBySku[$sku]))
        ) {
            continue;
        }

        $pattern = '/(?<=Sarcina maxima de incarcare este de )\d+(?=\s*kg)/';

        preg_match($pattern, $product['description'], $matches);
        $number = $matches[0] ?? 'abc';

        if (is_numeric($number) && $product['auto_supported_weight_bare'] < (int)$number) {
            $product['description'] = preg_replace($pattern, $product['auto_supported_weight_bare'], $product['description']);
        }

        // Set default values
        $product['associated_skus'] = $product['associated_skus'] ?? '';
        $product['bundle_values'] = $product['bundle_values'] ?? '';
        $product['termen_livrare'] = "1-2 zile";

        $manufacturer = explode(',' ,$product['manufacturer']);
        if (count($manufacturer) > 1) {
            $product['manufacturer'] = 'Green Valley';
        }

        // Format and output row
        $formattedRow = [];
        foreach ($headerRow as $colName) {
            $formattedRow[$colName] = $product[$colName];
        }
        fputcsv($outputHandle, $formattedRow);
    }

    fclose($outputHandle);
    echo "Export completed: Magento-compatible CSV file saved as $outputMagentoFile\n";
} else {
    echo "Unable to open the file.";
}