<?php

require $_SERVER['DOCUMENT_ROOT'] . '/vendor/autoload.php';
use GuzzleHttp\Client;
use Stichoza\GoogleTranslate\GoogleTranslate;

// Configuración de las credenciales de Shopify
$shopifyDomain = 'extreme-simracing.myshopify.com';
$accessToken = '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';

// Configuración de MySQL
$dbHost = 'localhost';
$dbUser = 'ndconsulta';
$dbPass = 'Nitro2021';
$dbName = 'db_tiendanitro';

// Conexión a MySQL
$mysqli = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
$mysqli->set_charset("utf8mb4"); // Configurar la conexión a MySQL para usar utf8mb4

if ($mysqli->connect_error) {
    die('Error de conexión a MySQL: ' . $mysqli->connect_error);
}

// Aumentar el límite de tamaño de paquete permitido en MySQL
$mysqli->query("SET GLOBAL max_allowed_packet = 16777216");

// Configuración de la solicitud a la API de Shopify
$client = new Client([
    'base_uri' => "https://$shopifyDomain",
    'headers' => [
        'Content-Type' => 'application/json',
        'X-Shopify-Access-Token' => $accessToken,
    ],
]);

// Inicializar el traductor
$tr = new GoogleTranslate('es');

// Función para limpiar el HTML y traducir solo el contenido de texto en partes
function translateBodyHtml($html, $translator, $maxLength = 1000) {
    if (empty($html)) {
        return $html; // Si el HTML está vacío, no hacemos nada
    }

    // Cargar el contenido HTML en DOMDocument
    $doc = new DOMDocument();
    @$doc->loadHTML(mb_convert_encoding($html, 'HTML-ENTITIES', 'UTF-8'));

    // Extraer el texto y traducirlo por partes
    $text = '';
    $nodes = $doc->getElementsByTagName('body')->item(0)->childNodes;
    $translatedParts = [];

    foreach ($nodes as $node) {
        if ($node->nodeType == XML_TEXT_NODE) {
            $text .= $node->nodeValue . ' ';
        } else {
            $text .= $doc->saveHTML($node) . ' ';
        }

        if (strlen($text) > $maxLength) {
            try {
                $translatedParts[] = $translator->translate($text);
            } catch (Exception $e) {
                return false; // Error en la traducción
            }
            $text = '';
        }
    }

    if (!empty($text)) {
        try {
            $translatedParts[] = $translator->translate($text);
        } catch (Exception $e) {
            return false; // Error en la traducción
        }
    }

    // Combinar todas las partes traducidas
    $translatedText = implode(' ', $translatedParts);

    // Crear un nuevo documento con el texto traducido
    $newDoc = new DOMDocument();
    @$newDoc->loadHTML(mb_convert_encoding($translatedText, 'HTML-ENTITIES', 'UTF-8'));

    // Combinar el texto traducido con el HTML original
    $translatedHtml = '';
    $nodes = $newDoc->getElementsByTagName('body')->item(0)->childNodes;
    foreach ($nodes as $node) {
        $translatedHtml .= $newDoc->saveHTML($node);
    }

    return $translatedHtml;
}

// Preparar las consultas de inserción y actualización para productos
$stmtInsertProduct = $mysqli->prepare("INSERT INTO inv_extreme_simracing_products (id, title, body_html, vendor, product_type, created_at, handle, updated_at, published_at, template_suffix, published_scope, tags, status, admin_graphql_api_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmtUpdateProduct = $mysqli->prepare("UPDATE inv_extreme_simracing_products SET status = ?, updated_at = ?, published_at = ? WHERE id = ?");

// Preparar las consultas de inserción y actualización para variantes
$stmtInsertVariant = $mysqli->prepare("INSERT INTO inv_extreme_simracing_variants (id, product_id, title, price, sku, position, inventory_policy, compare_at_price, fulfillment_service, inventory_management, option1, option2, option3, created_at, updated_at, taxable, barcode, grams, weight, weight_unit, inventory_item_id, inventory_quantity, old_inventory_quantity, requires_shipping, admin_graphql_api_id, image_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmtUpdateVariant = $mysqli->prepare("UPDATE inv_extreme_simracing_variants SET price = ?, inventory_quantity = ? WHERE id = ?");

// Preparar las consultas de inserción y actualización para imágenes
$stmtInsertImage = $mysqli->prepare("INSERT INTO inv_extreme_simracing_images (id, alt, position, product_id, created_at, updated_at, admin_graphql_api_id, width, height, src) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmtUpdateImage = $mysqli->prepare("UPDATE inv_extreme_simracing_images SET alt = ?, updated_at = ?, src = ? WHERE id = ?");

// Función para verificar si el producto existe
function productExists($mysqli, $productId) {
    $stmt = $mysqli->prepare("SELECT COUNT(*) FROM inv_extreme_simracing_products WHERE id = ?");
    $stmt->bind_param("i", $productId);
    $stmt->execute();
    $stmt->bind_result($count);
    $stmt->fetch();
    $stmt->close();
    return $count > 0;
}

// Función para verificar si la variante existe
function variantExists($mysqli, $variantId) {
    $stmt = $mysqli->prepare("SELECT COUNT(*) FROM inv_extreme_simracing_variants WHERE id = ?");
    $stmt->bind_param("i", $variantId);
    $stmt->execute();
    $stmt->bind_result($count);
    $stmt->fetch();
    $stmt->close();
    return $count > 0;
}

// Función para verificar si la imagen existe
function imageExists($mysqli, $imageId) {
    $stmt = $mysqli->prepare("SELECT COUNT(*) FROM inv_extreme_simracing_images WHERE id = ?");
    $stmt->bind_param("i", $imageId);
    $stmt->execute();
    $stmt->bind_result($count);
    $stmt->fetch();
    $stmt->close();
    return $count > 0;
}

// Función para limpiar y ajustar el SKU
function cleanSku($sku, $variantId) {
    if (empty($sku)) {
        return $variantId; // Si el SKU es NULL o vacío, usar el $variantId
    }

    // Reemplazar los caracteres "(" y ")" por guiones, excepto si ")" está al final
    $sku = str_replace(["(", "（"], "-", $sku);
    $sku = preg_replace("/\)$/", "", $sku); // Quitar ")" si está al final
    $sku = preg_replace("/\s+/", "-", $sku); // Reemplazar espacios por guiones

    // Eliminar cualquier "）" al final del SKU
    $sku = preg_replace("/）$/", "", $sku);

    // Asegurarse de que no queden guiones seguidos
    $sku = preg_replace('/-+/', '-', $sku); 

    // Eliminar guiones al principio o al final del SKU
    return trim($sku, '-');
}

// Función para ajustar los acentos en mayúsculas en los títulos
function fixTitleCase($title) {
    $title = mb_convert_case($title, MB_CASE_TITLE, "UTF-8"); // Convertir a título
    $title = preg_replace_callback('/[ÁÉÍÓÚÑÜ]/u', function($matches) {
        return mb_strtolower($matches[0], 'UTF-8');
    }, $title);
    return $title;
}


// Función para procesar productos
function processProducts($mysqli, $products, $stmtInsertProduct, $stmtUpdateProduct, $stmtInsertVariant, $stmtUpdateVariant, $stmtInsertImage, $stmtUpdateImage, $tr) {
    foreach ($products as $product) {
        $productId = $product['id'];
        $productName = fixTitleCase(ucwords(strtolower($tr->translate($product['title']))));
        $bodyHtml = $product['body_html'];
        $bodyHtmlTranslated = translateBodyHtml($bodyHtml, $tr);

        // Si hay error en la traducción, saltar el producto
        if ($bodyHtmlTranslated === false) {
            continue;
        }

        $vendor = $product['vendor'];
        $productType = $product['product_type'];
        $createdAt = $product['created_at'];
        $handle = $product['handle'];
        $updatedAt = $product['updated_at'];
        $publishedAt = $product['published_at'];
        $templateSuffix = $product['template_suffix'];
        $publishedScope = $product['published_scope'];
        $tags = is_array($product['tags']) ? implode(', ', $product['tags']) : $product['tags'];
        $status = $product['status'];
        $adminGraphqlApiId = $product['admin_graphql_api_id'];

        // Verificar si el producto ya existe
        if (productExists($mysqli, $productId)) {
            // Actualizar el producto
            $stmtUpdateProduct->bind_param("ssss", $status, $updatedAt, $publishedAt, $productId);
            $stmtUpdateProduct->execute();
        } else {
            // Insertar el producto
            $stmtInsertProduct->bind_param("isssssssssssss", $productId, $productName, $bodyHtmlTranslated, $vendor, $productType, $createdAt, $handle, $updatedAt, $publishedAt, $templateSuffix, $publishedScope, $tags, $status, $adminGraphqlApiId);
            $stmtInsertProduct->execute();
        }

        // Procesar las variantes del producto
        foreach ($product['variants'] as $variant) {
            $variantId = $variant['id'];
            $variantTitle = $variant['title'];
            $variantPrice = $variant['price'];
            $variantInventoryQuantity = $variant['inventory_quantity'];
            $variantSku = cleanSku($variant['sku'], $variantId);

            // Verificar si la variante ya existe
            if (variantExists($mysqli, $variantId)) {
                // Actualizar la variante
                $stmtUpdateVariant->bind_param("dii", $variantPrice, $variantInventoryQuantity, $variantId);
                $stmtUpdateVariant->execute();
            } else {
                $variantSku = cleanSku($variant['sku'], $variantId);
                $variantPosition = $variant['position'];
                $variantInventoryPolicy = $variant['inventory_policy'];
                $variantCompareAtPrice = $variant['compare_at_price'];
                $variantFulfillmentService = $variant['fulfillment_service'];
                $variantInventoryManagement = $variant['inventory_management'];
                $variantOption1 = $variant['option1'];
                $variantOption2 = isset($variant['option2']) ? $variant['option2'] : NULL;
                $variantOption3 = isset($variant['option3']) ? $variant['option3'] : NULL;
                $variantCreatedAt = $variant['created_at'];
                $variantUpdatedAt = $variant['updated_at'];
                $variantTaxable = $variant['taxable'] ? 1 : 0;
                $variantBarcode = $variant['barcode'];
                $variantGrams = $variant['grams'];
                $variantWeight = $variant['weight'];
                $variantWeightUnit = $variant['weight_unit'];
                $variantInventoryItemId = $variant['inventory_item_id'];
                $variantOldInventoryQuantity = $variant['old_inventory_quantity'];
                $variantRequiresShipping = $variant['requires_shipping'] ? 1 : 0;
                $variantAdminGraphqlApiId = $variant['admin_graphql_api_id'];
                $variantImageId = isset($variant['image_id']) ? $variant['image_id'] : NULL;

                // Insertar la variante
                $stmtInsertVariant->bind_param("iissssssssssssssidsisissss", $variantId, $productId, $variantTitle, $variantPrice, $variantSku, $variantPosition, $variantInventoryPolicy, $variantCompareAtPrice, $variantFulfillmentService, $variantInventoryManagement, $variantOption1, $variantOption2, $variantOption3, $variantCreatedAt, $variantUpdatedAt, $variantTaxable, $variantBarcode, $variantGrams, $variantWeight, $variantWeightUnit, $variantInventoryItemId, $variantInventoryQuantity, $variantOldInventoryQuantity, $variantRequiresShipping, $variantAdminGraphqlApiId, $variantImageId);
                $stmtInsertVariant->execute();
            }
        }

        // Procesar las imágenes del producto
        foreach ($product['images'] as $image) {
            $imageId = $image['id'];
            $imageAlt = $image['alt'];
            $imagePosition = $image['position'];
            $imageProductId = $image['product_id'];
            $imageCreatedAt = $image['created_at'];
            $imageUpdatedAt = $image['updated_at'];
            $imageAdminGraphqlApiId = $image['admin_graphql_api_id'];
            $imageWidth = $image['width'];
            $imageHeight = $image['height'];
            $imageSrc = $image['src'];

            // Verificar si la imagen ya existe
            if (imageExists($mysqli, $imageId)) {
                // Actualizar la imagen
                $stmtUpdateImage->bind_param("sssi", $imageAlt, $imageUpdatedAt, $imageSrc, $imageId);
                $stmtUpdateImage->execute();
            } else {
                // Insertar la imagen
                $stmtInsertImage->bind_param("isiissssss", $imageId, $imageAlt, $imagePosition, $imageProductId, $imageCreatedAt, $imageUpdatedAt, $imageAdminGraphqlApiId, $imageWidth, $imageHeight, $imageSrc);
                $stmtInsertImage->execute();
            }
        }
    }
}

// Función para obtener productos de Shopify con paginación
function getShopifyProducts($client, $mysqli, $stmtInsertProduct, $stmtUpdateProduct, $stmtInsertVariant, $stmtUpdateVariant, $stmtInsertImage, $stmtUpdateImage, $tr) {
    $url = "/admin/api/2023-10/products.json?limit=100";
    do {
        $response = $client->request('GET', $url);
        $data = json_decode($response->getBody(), true);
        processProducts($mysqli, $data['products'], $stmtInsertProduct, $stmtUpdateProduct, $stmtInsertVariant, $stmtUpdateVariant, $stmtInsertImage, $stmtUpdateImage, $tr);

        // Obtener el enlace a la siguiente página de productos
        $linkHeader = $response->getHeader('Link');
        if (!empty($linkHeader)) {
            $matches = [];
            preg_match('/<([^>]+)>; rel="next"/', $linkHeader[0], $matches);
            $url = isset($matches[1]) ? $matches[1] : null;
        } else {
            $url = null;
        }
    } while ($url);
}

// Obtener y procesar todos los productos de Shopify
getShopifyProducts($client, $mysqli, $stmtInsertProduct, $stmtUpdateProduct, $stmtInsertVariant, $stmtUpdateVariant, $stmtInsertImage, $stmtUpdateImage, $tr);

// Cerrar las declaraciones preparadas
$stmtInsertProduct->close();
$stmtUpdateProduct->close();
$stmtInsertVariant->close();
$stmtUpdateVariant->close();
$stmtInsertImage->close();
$stmtUpdateImage->close();

// Ejecutar las consultas adicionales
$mysqli->query("
UPDATE extreme_simracing_products
SET status = 'inactive',
    inventory_quantity = 0,
    price = 0;");

$mysqli->query("
INSERT INTO extreme_simracing_products (
    idProducto, 
    idVariacion, 
    Titulo, 
    Variacion, 
    Descripcion, 
    published_scope, 
    status, 
    sku, 
    grams, 
    weight, 
    inventory_quantity, 
    price, 
    product_type, 
    image_id
)
SELECT 
    inv_extreme_simracing_products.id AS idProducto, 
    inv_extreme_simracing_variants.id AS idVariacion, 
    inv_extreme_simracing_products.title AS Titulo, 
    inv_extreme_simracing_variants.title AS Variacion, 
    inv_extreme_simracing_products.body_html AS Descripcion, 
    inv_extreme_simracing_products.published_scope, 
    inv_extreme_simracing_products.status, 
    inv_extreme_simracing_variants.sku, 
    inv_extreme_simracing_variants.grams, 
    inv_extreme_simracing_variants.weight, 
    inv_extreme_simracing_variants.inventory_quantity, 
    inv_extreme_simracing_variants.price, 
    inv_extreme_simracing_products.product_type, 
    inv_extreme_simracing_variants.image_id
FROM 
    inv_extreme_simracing_products 
LEFT JOIN 
    inv_extreme_simracing_variants 
ON 
    inv_extreme_simracing_products.id = inv_extreme_simracing_variants.product_id
WHERE 
    inv_extreme_simracing_products.published_scope = 'web' 
    AND inv_extreme_simracing_products.status = 'active' 
    AND inv_extreme_simracing_variants.inventory_quantity > 3
ON DUPLICATE KEY UPDATE
    idProducto = VALUES(idProducto),
    sku = VALUES(sku),
    Titulo = VALUES(Titulo),
    Variacion = VALUES(Variacion),
    Descripcion = VALUES(Descripcion),
    published_scope = VALUES(published_scope),
    status = VALUES(status),
    grams = VALUES(grams),
    weight = VALUES(weight),
    inventory_quantity = VALUES(inventory_quantity),
    price = VALUES(price),
    product_type = VALUES(product_type),
    image_id = VALUES(image_id);

");

$mysqli->query("
 UPDATE extreme_simracing_products esp
JOIN (
    SELECT
        extreme_simracing_products.idVariacion,
        (
            extreme_simracing_products.price - (
                extreme_simracing_products.price * tn_proveedores.descuento
            ) + (
                extreme_simracing_products.price * tn_proveedores.ImpuestoMiami
            )
        ) + tn_proveedores.costoAdicionalUSD AS ProductoUSD,
        (
            pesoLbsUSA * tn_proveedores.precioLibraUSD
        ) AS EnvioUSAColUSD,
        (
            (
                extreme_simracing_products.price - (
                    extreme_simracing_products.price * tn_proveedores.descuento
                ) + (
                    extreme_simracing_products.price * tn_proveedores.ImpuestoMiami
                )
            ) + tn_proveedores.costoAdicionalUSD
        ) + (
            extreme_simracing_products.pesoLbsUSA * tn_proveedores.precioLibraUSD
        ) AS CostoTotalUSD,
        (
            (
                (
                    extreme_simracing_products.price - (
                        extreme_simracing_products.price * tn_proveedores.descuento
                    ) + (
                        extreme_simracing_products.price * tn_proveedores.ImpuestoMiami
                    )
                ) + tn_proveedores.costoAdicionalUSD
            ) + (
                extreme_simracing_products.pesoLbsUSA * tn_proveedores.precioLibraUSD
            )
        ) * tn_monedas.TRMFinal AS CostoTotalCop,
        ROUND(((
            (
                (
                    extreme_simracing_products.price - (
                        extreme_simracing_products.price * tn_proveedores.descuento
                    ) + (
                        extreme_simracing_products.price * tn_proveedores.ImpuestoMiami
                    )
                ) + tn_proveedores.costoAdicionalUSD
            ) + (
                extreme_simracing_products.pesoLbsUSA * tn_proveedores.precioLibraUSD
            )
        ) * tn_monedas.TRMFinal) * tn_proveedores.FactorUtilidad,
            -2
        ) AS PrecioVentaCOP
    FROM
        extreme_simracing_products,
        tn_monedas,
        tn_proveedores
    WHERE
        tn_proveedores.id = 2
        AND tn_monedas.id = 1
) AS subquery
ON esp.idVariacion = subquery.idVariacion
SET
    esp.ProductoUSD = subquery.ProductoUSD,
    esp.EnvioUSAColUSD = subquery.EnvioUSAColUSD,
    esp.CostoTotalUSD = subquery.CostoTotalUSD,
    esp.CostoTotalCop = subquery.CostoTotalCop,
    esp.PrecioVentaCOP = subquery.PrecioVentaCOP;");

$mysqli->query("
SET @max_referencia = (
    SELECT MAX(CAST(SUBSTRING(referenciaTN, 4, LENGTH(referenciaTN) - 3) AS UNSIGNED))
    FROM tn_productos
    WHERE referenciaTN LIKE 'TN-%'
);");

$mysqli->query("INSERT INTO tn_productos ( 
    id, 
    idEstado, 
    idTipoEnvio, 
    idTipoProducto, 
    idProveedor, 
    idImpuesto, 
    referenciaTN, 
    referenciaProveedor, 
    nombre, 
    Descripcion, 
    isPendiente 
)
SELECT 
    NULL AS id, 
    3 AS Estado, 
    1 AS idTipoEnvio, 
    2 AS Tipo, 
    2 AS Proveedor, 
    1 AS Impuesto, 
    CONCAT('TN-', LPAD(@max_referencia := @max_referencia + 1, 5, '0')) AS Ref, 
    extreme_simracing_products.idProducto, 
    MAX(extreme_simracing_products.Titulo) AS Titulo, 
    MAX(extreme_simracing_products.Descripcion) AS Descripcion, 
    1 AS Expr1
FROM 
    extreme_simracing_products 
    LEFT JOIN tn_productos tn ON extreme_simracing_products.idProducto = tn.referenciaProveedor
WHERE 
    tn.id IS NULL
GROUP BY 
    extreme_simracing_products.idProducto
ORDER BY 
    extreme_simracing_products.idProducto;");

$mysqli->query("
 INSERT INTO tn_productos_variantes (
    referenciaTN, 
    referenciaProveedor, 
    txtColor, 
    idProducto, 
    idTipoMinInventario, 
    idEstado, 
    inventario, 
    precioListaProveedor, 
    PVP
)
SELECT 
    CONCAT(tn_productos.referenciaTN, '-', LPAD(ROW_NUMBER() OVER (
        PARTITION BY tn_productos.id ORDER BY extreme_simracing_products.idVariacion
    ), 2, '0')) AS referenciaTN_generada,
    extreme_simracing_products.idVariacion, 
    CASE 
        WHEN extreme_simracing_products.Variacion = 'Default Title' THEN 'Unico'
        ELSE extreme_simracing_products.Variacion 
    END AS txtColor,
    tn_productos.id, 
    1 AS MinInv, 
    3 AS Estado, 
    extreme_simracing_products.inventory_quantity, 
    extreme_simracing_products.CostoTotalCop, 
    extreme_simracing_products.PrecioVentaCOP
FROM 
    extreme_simracing_products
INNER JOIN 
    tn_productos 
    ON extreme_simracing_products.idProducto = tn_productos.referenciaProveedor
LEFT JOIN 
    tn_productos_variantes tv 
    ON tv.referenciaProveedor = extreme_simracing_products.idVariacion
WHERE 
    tv.id IS NULL
ORDER BY 
    tn_productos.referenciaTN, 
    extreme_simracing_products.idVariacion;");

$mysqli->query("
UPDATE tn_productos_variantes
JOIN extreme_simracing_products ON tn_productos_variantes.referenciaProveedor = extreme_simracing_products.idVariacion
SET tn_productos_variantes.inventario = extreme_simracing_products.inventory_quantity,
    tn_productos_variantes.precioListaProveedor = extreme_simracing_products.CostoTotalCop,
    tn_productos_variantes.PVP = extreme_simracing_products.PrecioVentaCOP
WHERE tn_productos_variantes.referenciaProveedor = extreme_simracing_products.idVariacion;
");

// Cerrar la conexión a MySQL
$mysqli->close();

echo 'Productos importados exitosamente a MySQL.';
?>

<!DOCTYPE html>
<html lang="es">
<head>
    <meta charset="UTF-8">
    <title>Proceso terminado</title>
</head>
<body>
    <p>Proceso terminado. Mostrando barra de progreso...</p>
    <iframe src="https://sistema.nitrosimracing.com.co/conexiones/wp/inventario_api.php?&id_proveedor=2" style="width:100%; height:500px; border:none;"></iframe>
</body>
</html>