<?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 = 'fc_sistema';

// 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 fc_simagic_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 fc_simagic_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 fc_simagic_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 fc_simagic_variants SET price = ?, inventory_quantity = ? WHERE id = ?");

// Preparar las consultas de inserción y actualización para imágenes
$stmtInsertImage = $mysqli->prepare("INSERT INTO fc_simagic_images (id, alt, position, product_id, created_at, updated_at, admin_graphql_api_id, width, height, src) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmtUpdateImage = $mysqli->prepare("UPDATE fc_simagic_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 fc_simagic_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 fc_simagic_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 fc_simagic_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) {
        // Verificar si el product_type contiene "SIMAGIC"
        if (strpos(strtoupper($product['product_type']), 'SIMAGIC') === false) {
            continue; // Saltar este producto si no contiene "SIMAGIC"
        }

        $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'] === 'Default Title' ? 'Unico' : $variant['title'];
            $variantOption1 = isset($variant['option1']) && $variant['option1'] === 'Default Title' ? 'Unico' : $variant['option1'];
            $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();

// Cerrar la conexión a MySQL
$mysqli->close();

echo 'Productos importados exitosamente a MySQL.';
?>