<?php
date_default_timezone_set('America/Bogota');

// Desactivar la visualización de errores
ini_set('display_errors', 0);
error_reporting(0);

// Función para establecer la conexión a la base de datos
function conectarBD($servidor, $usuario, $contrasena, $nombreBD)
{
    $conn = new mysqli($servidor, $usuario, $contrasena, $nombreBD);
    if ($conn->connect_error) {
        die("Conexión fallida: " . $conn->connect_error);
    }
    return $conn;
}

// Función para ejecutar una consulta SQL y devolver el resultado
function ejecutarConsulta($conn, $sql)
{
    $result = $conn->query($sql);
    if (!$result) {
        echo "Error en la consulta: " . $conn->error;
    }
    return $result;
}

// Función para realizar una petición cURL y devolver la respuesta
function realizarPeticionCurl($url)
{
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $response = curl_exec($ch);
    if (curl_errno($ch)) {
        echo 'Error en cURL: ' . curl_error($ch);
    }
    curl_close($ch);
    return json_decode($response, true);
}

// Conexión a la base de datos
$conn = conectarBD("localhost", "ndconsulta", "Nitro2021", "db_inventarios_compranet");

// Truncar tablas auxiliares
$tablasAux = [
    'boton_aux_categorias', 'boton_aux_combinaciones', 'boton_aux_id_productos',
    'boton_aux_option_values', 'boton_aux_imagenes_principal', 'boton_aux_product_features',
    'boton_aux_stock_available','boton_promo_productos'
];
foreach ($tablasAux as $tabla) {
    ejecutarConsulta($conn, "TRUNCATE TABLE $tabla;");
}

// Obtener la lista de productos en lotes
$batchSize = 100;
$start = 0;
$apiKey = "6QW5T3HTWPMREMYZQ8TW8FH3JY45YGGZ";

do {
    $apiUrl = "https://esferos.com/site/api/products?output_format=JSON&ws_key=$apiKey&limit=$batchSize&start=$start";
    $productList = realizarPeticionCurl($apiUrl);

    if (isset($productList['products'])) {
        foreach ($productList['products'] as $product) {
            $productId = $product['id'];

            // Insertar el ID del producto en la tabla boton_aux_id_productos
            $stmt = $conn->prepare("INSERT INTO boton_aux_id_productos (product_id) VALUES (?)");
            $stmt->bind_param("i", $productId);
            $stmt->execute();
        }
        $start += $batchSize;
    }
} while (isset($productList['products']) && count($productList['products']) > 0);

// Obtener los ID de productos de la tabla boton_aux_id_productos
$productIds = [];
$result = ejecutarConsulta($conn, "SELECT product_id FROM boton_aux_id_productos");
while ($row = $result->fetch_assoc()) {
    $productIds[] = $row['product_id'];
}

// Recorrer cada ID de producto y obtener sus detalles en lotes
$batchSize = 50; // Tamaño del lote para la obtención de detalles de productos
foreach (array_chunk($productIds, $batchSize) as $productBatch) {
    foreach ($productBatch as $productId) {
        $productDetailsUrl = "https://esferos.com/site/api/products/$productId?output_format=JSON&ws_key=$apiKey";
        $productDetails = realizarPeticionCurl($productDetailsUrl);

        if (isset($productDetails['product'])) {
            // Extraer la información necesaria
            $product = $productDetails['product'];
            $reference = $product['reference'];
            $price = $product['price'];
            $availableForOrder = $product['available_for_order'];
            $visibility = $product['visibility'];
            $metaTitle = $product['meta_title'];
            $name = $product['name'];
            $description = $conn->real_escape_string($product['description']);
            $descriptionShort = $conn->real_escape_string($product['description_short']);
            $id_default_image = $product['id_default_image'];

            // Insertar los datos en la tabla boton_promo_productos
            $stmt = $conn->prepare("INSERT INTO boton_promo_productos (product_id, reference, price, available_for_order, visibility, meta_title, name, description, description_short, id_default_image) VALUES (?, ?, ?, ?, ?, ?, ?, ?, CleanHTML(?), ?)");
            $stmt->bind_param("isssssssss", $productId, $reference, $price, $availableForOrder, $visibility, $metaTitle, $name, $description, $descriptionShort, $id_default_image);
            $stmt->execute();

            // Procesar las asociaciones
            if (isset($product['associations'])) {
                procesarAsociaciones($conn, $productId, $product['associations']);
            }
        }
    }
}

// Función para procesar las asociaciones de un producto
function procesarAsociaciones($conn, $productId, $associations)
{
    // Categorías
    if (isset($associations['categories'])) {
        $stmt = $conn->prepare("INSERT INTO boton_aux_categorias (product_id, categoria_id) VALUES (?, ?)");
        foreach ($associations['categories'] as $category) {
            $stmt->bind_param("ii", $productId, $category['id']);
            $stmt->execute();
        }
    }

    // Imágenes
    if (isset($associations['images'])) {
        $stmt = $conn->prepare("INSERT INTO boton_aux_imagenes_principal (product_id, imagen_id) VALUES (?, ?)");
        foreach ($associations['images'] as $image) {
            $stmt->bind_param("ii", $productId, $image['id']);
            $stmt->execute();
        }
    }

    // Combinaciones
    if (isset($associations['combinations'])) {
        $stmt = $conn->prepare("INSERT INTO boton_aux_combinaciones (product_id, combination_id) VALUES (?, ?)");
        foreach ($associations['combinations'] as $combination) {
            $stmt->bind_param("ii", $productId, $combination['id']);
            $stmt->execute();
        }
    }

    // Opciones de producto
    if (isset($associations['product_option_values'])) {
        $stmt = $conn->prepare("INSERT INTO boton_aux_option_values (product_id, option_id) VALUES (?, ?)");
        foreach ($associations['product_option_values'] as $option) {
            $stmt->bind_param("ii", $productId, $option['id']);
            $stmt->execute();
        }
    }

    // Características del producto
    if (isset($associations['product_features'])) {
        $stmt = $conn->prepare("INSERT INTO boton_aux_product_features (product_id, feature_id, feature_value_id) VALUES (?, ?, ?)");
        foreach ($associations['product_features'] as $feature) {
            $stmt->bind_param("iii", $productId, $feature['id'], $feature['id_feature_value']);
            $stmt->execute();
        }
    }

    // Disponibilidad de stock
    if (isset($associations['stock_availables'])) {
        $stmt = $conn->prepare("INSERT INTO boton_aux_stock_available (product_id, stock_id, id_product_attribute) VALUES (?, ?, ?)");
        foreach ($associations['stock_availables'] as $stock) {
            $stmt->bind_param("iii", $productId, $stock['id'], $stock['id_product_attribute']);
            $stmt->execute();
        }
    }
}

// Obtener la lista de precios específicos en lotes
$specificPricesUrl = "https://esferos.com/site/api/specific_prices/?output_format=JSON&ws_key=$apiKey";
$specificPricesList = realizarPeticionCurl($specificPricesUrl);

if (isset($specificPricesList['specific_prices'])) {
    foreach ($specificPricesList['specific_prices'] as $specificPrice) {
        $idSpecificPrice = $specificPrice['id'];
        $specificPriceUrl = "https://esferos.com/site/api/specific_prices/$idSpecificPrice?output_format=JSON&ws_key=$apiKey";
        $specificPriceDetails = realizarPeticionCurl($specificPriceUrl);

        if (isset($specificPriceDetails['specific_price'])) {
            $specificPrice = $specificPriceDetails['specific_price'];
            $idProduct = $specificPrice['id_product'];
            $idProductAttribute = $specificPrice['id_product_attribute'];
            $price = $specificPrice['price'];
            $fromQuantity = $specificPrice['from_quantity'];
            $reduction = $specificPrice['reduction'];
            $reductionType = $specificPrice['reduction_type'];
            $fromDate = $specificPrice['from'];
            $toDate = $specificPrice['to'];

            // Insertar los datos en la tabla boton_lista_descuentos
            $stmt = $conn->prepare("INSERT INTO boton_lista_descuentos (id_specific_price, id_product, id_product_attribute, price, from_quantity, reduction, reduction_type, from_date, to_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
            $stmt->bind_param("iiissssss", $idSpecificPrice, $idProduct, $idProductAttribute, $price, $fromQuantity, $reduction, $reductionType, $fromDate, $toDate);
            $stmt->execute();
        }
    }
}

// Insertar categorías únicas en boton_lista_categorias
ejecutarConsulta($conn, "INSERT IGNORE INTO boton_lista_categorias (id_categoria)
                         SELECT categoria_id FROM boton_aux_categorias GROUP BY categoria_id ORDER BY categoria_id");

// Insertar características únicas en boton_lista_nombre_caracteristicas
ejecutarConsulta($conn, "INSERT IGNORE INTO boton_lista_nombre_caracteristicas (id_caracteristica)
                         SELECT feature_id FROM boton_aux_product_features GROUP BY feature_id ORDER BY feature_id");

// Insertar valores de características únicos en boton_lista_caracteristicas
ejecutarConsulta($conn, "INSERT IGNORE INTO boton_lista_caracteristicas (id_valor_caracteristica)
                         SELECT feature_value_id FROM boton_aux_product_features GROUP BY feature_value_id ORDER BY feature_value_id");

// Insertar opciones únicas en boton_lista_opciones
ejecutarConsulta($conn, "INSERT IGNORE INTO boton_lista_opciones (id_opcion)
                         SELECT option_id FROM boton_aux_option_values GROUP BY option_id ORDER BY option_id");

// Insertar combinaciones únicas en boton_lista_combinaciones
ejecutarConsulta($conn, "INSERT IGNORE INTO boton_lista_combinaciones (product_id, combination_id)
                         SELECT product_id, combination_id FROM boton_aux_combinaciones ORDER BY product_id");

// Insertar stock único en boton_lista_stock
ejecutarConsulta($conn, "INSERT IGNORE INTO boton_lista_stock (product_id, id_product_attribute, stock_id)
                         SELECT product_id, id_product_attribute, stock_id FROM boton_aux_stock_available ORDER BY product_id, id_product_attribute");

// Actualizar el nombre de la categoría en la tabla boton_lista_categorias
$categories = ejecutarConsulta($conn, "SELECT id_categoria FROM boton_lista_categorias WHERE nombre_categoria IS NULL");
while ($row = $categories->fetch_assoc()) {
    $idCategoria = $row['id_categoria'];
    $categoryUrl = "https://esferos.com/site/api/categories/$idCategoria?output_format=JSON&ws_key=$apiKey";
    $categoryDetails = realizarPeticionCurl($categoryUrl);
    if (isset($categoryDetails['category'])) {
        $categoryName = $conn->real_escape_string($categoryDetails['category']['name']);
        $updateQuery = "UPDATE boton_lista_categorias SET nombre_categoria = ? WHERE id_categoria = ?";
        $stmt = $conn->prepare($updateQuery);
        $stmt->bind_param("si", $categoryName, $idCategoria);
        $stmt->execute();
    }
}

// Actualizar el nombre de la característica en la tabla boton_lista_nombre_caracteristicas
$features = ejecutarConsulta($conn, "SELECT id_caracteristica FROM boton_lista_nombre_caracteristicas WHERE nombre_caracteristica IS NULL");
while ($row = $features->fetch_assoc()) {
    $idValorCaracteristica = $row['id_caracteristica'];
    $featureUrl = "https://esferos.com/site/api/product_features/$idValorCaracteristica?output_format=JSON&ws_key=$apiKey";
    $featureDetails = realizarPeticionCurl($featureUrl);
    if (isset($featureDetails['product_feature'])) {
        $featureName = $conn->real_escape_string($featureDetails['product_feature']['name']);
        $updateQuery = "UPDATE boton_lista_nombre_caracteristicas SET nombre_caracteristica = ? WHERE id_caracteristica = ?";
        $stmt = $conn->prepare($updateQuery);
        $stmt->bind_param("si", $featureName, $idValorCaracteristica);
        $stmt->execute();
    }
}

// Actualizar el nombre de la característica en la tabla boton_lista_caracteristicas
$values = ejecutarConsulta($conn, "SELECT id_valor_caracteristica FROM boton_lista_caracteristicas WHERE nombre_caracteristica IS NULL");
while ($row = $values->fetch_assoc()) {
    $idValorCaracteristica = $row['id_valor_caracteristica'];
    $featureUrl = "https://esferos.com/site/api/product_feature_values/$idValorCaracteristica?output_format=JSON&ws_key=$apiKey";
    $featureDetails = realizarPeticionCurl($featureUrl);
    if (isset($featureDetails['product_feature_value'])) {
        $featureName = $conn->real_escape_string($featureDetails['product_feature_value']['value']);
        $updateQuery = "UPDATE boton_lista_caracteristicas SET nombre_caracteristica = ? WHERE id_valor_caracteristica = ?";
        $stmt = $conn->prepare($updateQuery);
        $stmt->bind_param("si", $featureName, $idValorCaracteristica);
        $stmt->execute();
    }
}

// Actualizar el valor de la opción en la tabla boton_lista_opciones
$options = ejecutarConsulta($conn, "SELECT id_opcion FROM boton_lista_opciones WHERE valor IS NULL");
while ($row = $options->fetch_assoc()) {
    $idValorCaracteristica = $row['id_opcion'];
    $featureUrl = "https://esferos.com/site/api/product_option_values/$idValorCaracteristica?output_format=JSON&ws_key=$apiKey";
    $featureDetails = realizarPeticionCurl($featureUrl);
    if (isset($featureDetails['product_option_value'])) {
        $featureName = $conn->real_escape_string($featureDetails['product_option_value']['name']);
        $updateQuery = "UPDATE boton_lista_opciones SET valor = ? WHERE id_opcion = ?";
        $stmt = $conn->prepare($updateQuery);
        $stmt->bind_param("si", $featureName, $idValorCaracteristica);
        $stmt->execute();
    }
}

// Actualizar el stock en la tabla boton_lista_stock
$stocks = ejecutarConsulta($conn, "SELECT stock_id FROM boton_lista_stock ORDER BY product_id");
while ($row = $stocks->fetch_assoc()) {
    $id_stockextractor = $row['stock_id'];
    $linkstock = "https://esferos.com/site/api/stock_availables/$id_stockextractor?output_format=JSON&ws_key=$apiKey";
    $stockdetails = realizarPeticionCurl($linkstock);
    if (isset($stockdetails['stock_available'])) {
        $valorstock = $conn->real_escape_string($stockdetails['stock_available']['quantity']);
    } else {
        $valorstock = 0; // Si no hay stock disponible, establecer stock a 0
    }
    $updateQuery = "UPDATE boton_lista_stock SET stock = ? WHERE stock_id = ?";
    $stmt = $conn->prepare($updateQuery);
    $stmt->bind_param("si", $valorstock, $id_stockextractor);
    $stmt->execute();
}

// Actualizar la referencia y el option_value en la tabla boton_lista_combinaciones
$combinations = ejecutarConsulta($conn, "SELECT combination_id FROM boton_lista_combinaciones WHERE referencia IS NULL");
while ($row = $combinations->fetch_assoc()) {
    $combinationId = $row['combination_id'];
    $combinationUrl = "https://esferos.com/site/api/combinations/$combinationId?output_format=JSON&ws_key=$apiKey";
    $combinationDetails = realizarPeticionCurl($combinationUrl);
    if (isset($combinationDetails['combination'])) {
        $reference = $conn->real_escape_string($combinationDetails['combination']['reference']);
        $optionValue = isset($combinationDetails['combination']['associations']['product_option_values'][0]['id']) ? $combinationDetails['combination']['associations']['product_option_values'][0]['id'] : null;
        $updateQuery = "UPDATE boton_lista_combinaciones SET referencia = ?, option_value = ? WHERE combination_id = ?";
        $stmt = $conn->prepare($updateQuery);
        $stmt->bind_param("sii", $reference, $optionValue, $combinationId);
        $stmt->execute();
        // Insertar imágenes de la combinación en la tabla boton_lista_imagenes_combinacion
        foreach ($combinationDetails['combination']['associations']['images'] as $image) {
            $stmt = $conn->prepare("INSERT INTO boton_lista_imagenes_combinacion (combination_id, imagen_id) VALUES (?, ?)");
            $stmt->bind_param("ii", $combinationId, $image['id']);
            $stmt->execute();
        }
    }
}

// Truncar la tabla invpremarpico en la base de datos sistemageneral
mysqli_query($conn, 'TRUNCATE TABLE sistemageneral.invprebotonpromo;');

// Realizar el INSERT a la tabla invpremarpico
$insert_query = "INSERT IGNORE INTO sistemageneral.invprebotonpromo (codigo, raiz, variacion, proveedor, stock, precio, preciounitario, descuento, idraiz, idatributo)
SELECT CONCAT(bpp.product_id, '-', blc.referencia) AS codigo,
       bppcv.product_id AS raiz,
       blo.valor AS variacion,
       'BotonPromo' AS proveedor,
       bls.stock,
       bpp.price AS precio,
       bpp.price AS preciounitario,
       0 AS descuento,
       CONCAT(bppcv.product_id, '_01') AS idraiz,
       CONCAT(bpp.product_id, '-', blc.referencia, '_01') AS idatributo
FROM boton_lista_stock bls
INNER JOIN boton_promo_productos_con_variaciones bppcv ON bls.stock_id = bppcv.stock_id
INNER JOIN boton_promo_productos bpp ON bppcv.product_id = bpp.product_id
INNER JOIN boton_lista_combinaciones blc ON bls.id_product_attribute = blc.combination_id
INNER JOIN boton_lista_opciones blo ON blc.option_value = blo.id_opcion
UNION
SELECT CONCAT(bpp.product_id, '-', bpp.reference) AS codigo,
       bppsv.product_id AS raiz,
       'N/R' AS variacion,
       'BotonPromo' AS proveedor,
       bls.stock,
       bpp.price AS precio,
       bpp.price AS preciounitario,
       0 AS descuento,
       CONCAT(bppsv.product_id, '_01') AS idraiz,
       CONCAT(bpp.product_id, '-', bpp.reference, '_01') AS idatributo
FROM boton_lista_stock bls
RIGHT JOIN boton_promo_productos_sin_variaciones bppsv ON bls.stock_id = bppsv.stock_id
INNER JOIN boton_promo_productos bpp ON bppsv.product_id = bpp.product_id
ORDER BY codigo;";

// Llamar al procedimiento almacenado actualizar_pvp
$call_query = "CALL actualizar_boton_promo;";
mysqli_query($conn, $call_query);

// Verificar si hubo errores en la llamada al procedimiento almacenado
if (mysqli_error($conn)) {
    echo "Error al llamar al procedimiento almacenado: " . mysqli_error($conn) . "<br>";
} else {
    echo "Procedimiento almacenado ejecutado correctamente<br>";
}

mysqli_query($conn, $insert_query);

// Verificar si hubo errores
if (mysqli_error($conn)) {
    echo "Error al insertar datos: " . mysqli_error($conn);
} else {
    echo "Datos insertados correctamente en sistemageneral.invprebotonpromo";
}

// Cerrar la conexión a la base de datos
mysqli_close($conn);
$HoraFin = date('h:i:s') . "\n";
echo $HoraFin;
echo "Proceso terminado.";
?>

<!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://inventarios.nitrosimracing.com.co/conexionwp/inventario_api.php?&id_proveedor=4" style="width:100%; height:500px; border:none;"></iframe>
</body>
</html>