<?php
date_default_timezone_set('America/Bogota');

$conn = new mysqli("localhost", "ndconsulta", "Nitro2021", "db_inventarios_compranet");
if ($conn->connect_error) {
    die("Conexión fallida: " . $conn->connect_error);
}

mysqli_query($conn, 'TRUNCATE TABLE boton_aux_categorias;');
mysqli_query($conn, 'TRUNCATE TABLE boton_aux_combinaciones;');
mysqli_query($conn, 'TRUNCATE TABLE boton_aux_id_productos;');
mysqli_query($conn, 'TRUNCATE TABLE boton_aux_option_values;');
mysqli_query($conn, 'TRUNCATE TABLE boton_aux_imagenes_principal;');
mysqli_query($conn, 'TRUNCATE TABLE boton_aux_product_features;');
mysqli_query($conn, 'TRUNCATE TABLE boton_aux_stock_available;');

$apiUrl = "https://esferos.com/site/api/products?output_format=JSON&ws_key=6QW5T3HTWPMREMYZQ8TW8FH3JY45YGGZ";
$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

// Obtener la lista de productos
curl_setopt($ch, CURLOPT_URL, $apiUrl);
$response = curl_exec($ch);
if (curl_errno($ch)) {
    echo 'Error en cURL: ' . curl_error($ch);
}
$productList = json_decode($response, true);

if (isset($productList['products'])) {
    foreach ($productList['products'] as $product) {
        $productId = $product['id'];

        // Insertar el ID del producto en la tabla boton_aux_id_productos
        $query = "INSERT INTO boton_aux_id_productos (product_id) VALUES (?)";
        $stmt = $conn->prepare($query);
if ($stmt === false) {
    die("Error al preparar la consulta: " . $conn->error);
}

        $stmt->bind_param("i", $productId);
        $stmt->execute();
    }
}

curl_close($ch);
$conn->close();

date_default_timezone_set('America/Bogota');

$conn = new mysqli("localhost", "root", "Proyecto2016", "db_inventarios_compranet");
if ($conn->connect_error) {
    die("Conexión fallida: " . $conn->connect_error);
}

$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

// Obtener los ID de productos de la tabla boton_aux_id_productos
$productIds = [];
$query = "SELECT product_id FROM boton_aux_id_productos";
$result = $conn->query($query);
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $productIds[] = $row['product_id'];
    }
}

// Recorrer cada ID de producto y obtener sus detalles
foreach ($productIds as $productId) {
    $productDetailsUrl = "https://esferos.com/site/api/products/$productId?output_format=JSON&ws_key=6QW5T3HTWPMREMYZQ8TW8FH3JY45YGGZ";
    curl_setopt($ch, CURLOPT_URL, $productDetailsUrl);
    $response = curl_exec($ch);
    if (curl_errno($ch)) {
        echo 'Error en cURL: ' . curl_error($ch);
    }
    $productDetails = json_decode($response, true);

    if (isset($productDetails['product'])) {
        // Extraer la información necesaria
        $reference = $productDetails['product']['reference'];
        $price = $productDetails['product']['price'];
        $availableForOrder = $productDetails['product']['available_for_order'];
        $visibility = $productDetails['product']['visibility'];
        $metaTitle = $productDetails['product']['meta_title'];
        $name = $productDetails['product']['name'];
        $description = $conn->real_escape_string($productDetails['product']['description']);
        $descriptionShort = $conn->real_escape_string($productDetails['product']['description_short']);
        $id_default_image = $productDetails['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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        $stmt->bind_param("isssssssss", $productId, $reference, $price, $availableForOrder, $visibility, $metaTitle, $name, $description, $descriptionShort, $id_default_image);
        $stmt->execute();

        // Procesar las asociaciones
        if (isset($productDetails['product']['associations'])) {
            $associations = $productDetails['product']['associations'];

            // Categorías
            foreach ($associations['categories'] as $category) {
                $stmt = $conn->prepare("INSERT INTO boton_aux_categorias (product_id, categoria_id) VALUES (?, ?)");
                $stmt->bind_param("ii", $productId, $category['id']);
                $stmt->execute();
            }

            // Imágenes
            foreach ($associations['images'] as $image) {
                $stmt = $conn->prepare("INSERT INTO boton_aux_imagenes_principal (product_id, imagen_id) VALUES (?, ?)");
                $stmt->bind_param("ii", $productId, $image['id']);
                $stmt->execute();
            }

            // Combinaciones
            foreach ($associations['combinations'] as $combination) {
                $stmt = $conn->prepare("INSERT INTO boton_aux_combinaciones (product_id, combination_id) VALUES (?, ?)");
                $stmt->bind_param("ii", $productId, $combination['id']);
                $stmt->execute();
            }

            // Opciones de producto
            foreach ($associations['product_option_values'] as $option) {
                $stmt = $conn->prepare("INSERT INTO boton_aux_option_values (product_id, option_id) VALUES (?, ?)");
                $stmt->bind_param("ii", $productId, $option['id']);
                $stmt->execute();
            }

            // Características del producto
            foreach ($associations['product_features'] as $feature) {
                $stmt = $conn->prepare("INSERT INTO boton_aux_product_features (product_id, feature_id, feature_value_id) VALUES (?, ?, ?)");
                $stmt->bind_param("iii", $productId, $feature['id'], $feature['id_feature_value']);
                $stmt->execute();
            }

            // Disponibilidad de stock
            foreach ($associations['stock_availables'] as $stock) {
                $stmt = $conn->prepare("INSERT INTO boton_aux_stock_available (product_id,stock_id, id_product_attribute) VALUES (?, ?, ?)");
                $stmt->bind_param("iii", $productId, $stock['id'], $stock['id_product_attribute']);
                $stmt->execute();
            }
        }
    }
}

curl_close($ch);

mysqli_query($conn, 'TRUNCATE TABLE boton_lista_descuentos;');

$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

// Obtener la lista de precios específicos
$specificPricesUrl = "https://esferos.com/site/api/specific_prices/?output_format=JSON&ws_key=6QW5T3HTWPMREMYZQ8TW8FH3JY45YGGZ";
curl_setopt($ch, CURLOPT_URL, $specificPricesUrl);
$response = curl_exec($ch);
if (curl_errno($ch)) {
    echo 'Error en cURL: ' . curl_error($ch);
}
$specificPricesList = json_decode($response, true);

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=6QW5T3HTWPMREMYZQ8TW8FH3JY45YGGZ";

        curl_setopt($ch, CURLOPT_URL, $specificPriceUrl);
        $response = curl_exec($ch);
        if (curl_errno($ch)) {
            echo 'Error en cURL: ' . curl_error($ch);
        }

        $specificPriceDetails = json_decode($response, true);

        if (isset($specificPriceDetails['specific_price'])) {
            $idProduct = $specificPriceDetails['specific_price']['id_product'];
            $idProductAttribute = $specificPriceDetails['specific_price']['id_product_attribute'];
            $price = $specificPriceDetails['specific_price']['price'];
            $fromQuantity = $specificPriceDetails['specific_price']['from_quantity'];
            $reduction = $specificPriceDetails['specific_price']['reduction'];
            $reductionType = $specificPriceDetails['specific_price']['reduction_type'];
            $fromDate = $specificPriceDetails['specific_price']['from'];
            $toDate = $specificPriceDetails['specific_price']['to'];

            // Insertar los datos en la tabla boton_lista_precios
            $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();
        }
    }
}

curl_close($ch);

// Insertar categorías únicas en boton_lista_categorias
$query = "INSERT IGNORE INTO boton_lista_categorias (id_categoria)
          SELECT categoria_id FROM boton_aux_categorias
          GROUP BY categoria_id
          ORDER BY categoria_id";
mysqli_query($conn, $query);

// Insertar características únicas en boton_lista_nombre_caracteristicas
$query = "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";
mysqli_query($conn, $query);

// Insertar valores de características únicos en boton_lista_caracteristicas
$query = "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";
mysqli_query($conn, $query);

// Insertar opciones únicas en boton_lista_opciones
$query = "INSERT IGNORE INTO boton_lista_opciones (id_opcion)
          SELECT option_id FROM boton_aux_option_values
          GROUP BY option_id
          ORDER BY option_id";
mysqli_query($conn, $query);


// Insertar combinacines
$query = "INSERT IGNORE INTO boton_lista_combinaciones ( product_id, combination_id )
SELECT boton_aux_combinaciones.product_id, boton_aux_combinaciones.combination_id
FROM boton_aux_combinaciones
ORDER BY boton_aux_combinaciones.product_id;";
mysqli_query($conn, $query);


// Insertar stock unico
$query = "INSERT IGNORE INTO boton_lista_stock ( product_id, id_product_attribute, stock_id )
            SELECT boton_aux_stock_available.product_id, boton_aux_stock_available.id_product_attribute, boton_aux_stock_available.stock_id
            FROM boton_aux_stock_available
            ORDER BY boton_aux_stock_available.product_id, boton_aux_stock_available.id_product_attribute;";
mysqli_query($conn, $query);

$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

// Obtener los ID de categorías de la tabla boton_lista_categorias donde nombre_categoria es nulo
$query = "SELECT id_categoria FROM boton_lista_categorias WHERE nombre_categoria IS NULL";
$result = $conn->query($query);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        $idCategoria = $row['id_categoria'];
        $categoryUrl = "https://esferos.com/site/api/categories/$idCategoria?output_format=JSON&ws_key=6QW5T3HTWPMREMYZQ8TW8FH3JY45YGGZ";

        curl_setopt($ch, CURLOPT_URL, $categoryUrl);
        $response = curl_exec($ch);
        if (curl_errno($ch)) {
            echo 'Error en cURL: ' . curl_error($ch);
        }

        $categoryDetails = json_decode($response, true);

        if (isset($categoryDetails['category'])) {
            $categoryName = $conn->real_escape_string($categoryDetails['category']['name']);

            // Actualizar el nombre de la categoría en la tabla boton_lista_categorias
            $updateQuery = "UPDATE boton_lista_categorias SET nombre_categoria = ? WHERE id_categoria = ?";
            $stmt = $conn->prepare($updateQuery);
            $stmt->bind_param("si", $categoryName, $idCategoria);
            $stmt->execute();
        }
    }
}

curl_close($ch);

$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

// Obtener los ID de características de la tabla boton_lista_caracteristicas donde nombre_caracteristica es nulo
$query = "SELECT id_caracteristica FROM boton_lista_nombre_caracteristicas WHERE nombre_caracteristica IS NULL";
$result = $conn->query($query);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        $idValorCaracteristica = $row['id_caracteristica'];
        $featureUrl = "https://esferos.com/site/api/product_features/$idValorCaracteristica?output_format=JSON&ws_key=6QW5T3HTWPMREMYZQ8TW8FH3JY45YGGZ";

        curl_setopt($ch, CURLOPT_URL, $featureUrl);
        $response = curl_exec($ch);
        if (curl_errno($ch)) {
            echo 'Error en cURL: ' . curl_error($ch);
        }

        $featureDetails = json_decode($response, true);

        if (isset($featureDetails['product_feature'])) {
            $featureName = $conn->real_escape_string($featureDetails['product_feature']['name']);

            // Actualizar el nombre de la característica en la tabla boton_lista_caracteristicas
            $updateQuery = "UPDATE boton_lista_nombre_caracteristicas SET nombre_caracteristica = ? WHERE id_caracteristica = ?";
            $stmt = $conn->prepare($updateQuery);
            $stmt->bind_param("si", $featureName, $idValorCaracteristica);
            $stmt->execute();
        }
    }
}

curl_close($ch);

$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

// Obtener los ID de características de la tabla boton_lista_caracteristicas donde nombre_caracteristica es nulo
$query = "SELECT id_valor_caracteristica FROM boton_lista_caracteristicas WHERE nombre_caracteristica IS NULL";
$result = $conn->query($query);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        $idValorCaracteristica = $row['id_valor_caracteristica'];
        $featureUrl = "https://esferos.com/site/api/product_feature_values/$idValorCaracteristica?output_format=JSON&ws_key=6QW5T3HTWPMREMYZQ8TW8FH3JY45YGGZ";

        curl_setopt($ch, CURLOPT_URL, $featureUrl);
        $response = curl_exec($ch);
        if (curl_errno($ch)) {
            echo 'Error en cURL: ' . curl_error($ch);
        }

        $featureDetails = json_decode($response, true);

        if (isset($featureDetails['product_feature_value'])) {
            $featureName = $conn->real_escape_string($featureDetails['product_feature_value']['value']);

            // Actualizar el nombre de la característica en la tabla boton_lista_caracteristicas
            $updateQuery = "UPDATE boton_lista_caracteristicas SET nombre_caracteristica = ? WHERE id_valor_caracteristica = ?";
            $stmt = $conn->prepare($updateQuery);
            $stmt->bind_param("si", $featureName, $idValorCaracteristica);
            $stmt->execute();
        }
    }
}

curl_close($ch);

$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

// Obtener los ID de características de la tabla boton_lista_caracteristicas donde nombre_caracteristica es nulo
$query = "SELECT id_opcion FROM boton_lista_opciones WHERE valor IS NULL";
$result = $conn->query($query);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        $idValorCaracteristica = $row['id_opcion'];
        $featureUrl = "https://esferos.com/site/api/product_option_values/$idValorCaracteristica?output_format=JSON&ws_key=6QW5T3HTWPMREMYZQ8TW8FH3JY45YGGZ";

        curl_setopt($ch, CURLOPT_URL, $featureUrl);
        $response = curl_exec($ch);
        if (curl_errno($ch)) {
            echo 'Error en cURL: ' . curl_error($ch);
        }

        $featureDetails = json_decode($response, true);

        if (isset($featureDetails['product_option_value'])) {
            $featureName = $conn->real_escape_string($featureDetails['product_option_value']['name']);

            // Actualizar el nombre de la característica en la tabla boton_lista_caracteristicas
            $updateQuery = "UPDATE boton_lista_opciones SET valor = ? WHERE id_opcion = ?";
            $stmt = $conn->prepare($updateQuery);
            $stmt->bind_param("si", $featureName, $idValorCaracteristica);
            $stmt->execute();
        }
    }
}

curl_close($ch);

$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

$query = "SELECT stock_id FROM boton_lista_stock ORDER BY product_id";
$result = $conn->query($query);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        $id_stockextractor = $row['stock_id'];
        $linkstock = "https://esferos.com/site/api/stock_availables/$id_stockextractor?output_format=JSON&ws_key=6QW5T3HTWPMREMYZQ8TW8FH3JY45YGGZ";

        curl_setopt($ch, CURLOPT_URL, $linkstock);
        $response = curl_exec($ch);
        if (curl_errno($ch)) {
            echo 'Error en cURL: ' . curl_error($ch);
        }

        $stockdetails = json_decode($response, true);

        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
        }

        // Actualizar el stock en la tabla boton_lista_stock
        $updateQuery = "UPDATE boton_lista_stock SET stock = ? WHERE stock_id = ?";
        $stmt = $conn->prepare($updateQuery);
        $stmt->bind_param("si", $valorstock, $stockdetails);
        $stmt->execute();
    }
}

curl_close($ch);

$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

// Obtener los IDs de combinaciones de la tabla boton_lista_combinaciones donde la referencia es nula
$query = "SELECT combination_id FROM boton_lista_combinaciones WHERE referencia IS NULL";
$result = $conn->query($query);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        $combinationId = $row['combination_id'];
        $combinationUrl = "https://esferos.com/site/api/combinations/$combinationId?output_format=JSON&ws_key=6QW5T3HTWPMREMYZQ8TW8FH3JY45YGGZ";

        curl_setopt($ch, CURLOPT_URL, $combinationUrl);
        $response = curl_exec($ch);
        if (curl_errno($ch)) {
            echo 'Error en cURL: ' . curl_error($ch);
        }

        $combinationDetails = json_decode($response, true);

        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;

            // Actualizar la referencia y el option_value en la tabla boton_lista_combinaciones
            $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();
            }
        }
    }
}

curl_close($ch);

// 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 INTO sistemageneral.invprebotonpromo (codigo, raiz, variacion, proveedor, stock, precio, preciounitario, descuento, idraiz, idatributo)
SELECT CONCAT(bpp.product_id, '-', bpp.reference) AS codigo,
       bppcv.product_id AS raiz,
       blo.valor AS variacion,
       'BotonPromo' AS proveedor,
       bls.stock,
       bpp.price AS precio,
       bpp.price AS preciounitario,
       CONCAT(bppcv.product_id, '_01') AS idraiz,
       CONCAT(bpp.product_id, '-', bpp.reference, '_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,
       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;";

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);

?>