<?php

/**
 * Este script:
 *  1. Descarga el JSON desde la URL dada.
 *  2. Decodifica el JSON.
 *  3. Inserta o actualiza (ON DUPLICATE KEY) la información en las tablas correspondientes:
 *     - botonshop_productos
 *     - botonshop_variantes
 *     - botonshop_opciones
 *     - botonshop_imagenes
 */

// CONFIGURACIÓN DE CONEXIÓN A LA BASE DE DATOS
$servername = "localhost";
$username   = "ndconsulta";
$password   = "Nitro2021";
$dbname     = "db_inventarios_compranet";

// Conexión con mysqli
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
    die("Error de conexión a la base de datos: " . mysqli_connect_error());
}

// URL del JSON
$jsonUrl = 'https://www.catalogoespacial.com/json_esferos/archivo_combinado_2.json';

// OBTENER CONTENIDO DEL JSON
$jsonData = file_get_contents($jsonUrl);
if (!$jsonData) {
    die("No se pudo obtener el JSON desde la URL proporcionada.");
}

// DECODIFICAR JSON
$data = json_decode($jsonData, true);

// VERIFICAR SI EXISTE LA CLAVE "products"
if (!isset($data['products']) || !is_array($data['products'])) {
    die("El formato del JSON no es el esperado o la clave 'products' no existe.");
}

/**
 * Función auxiliar para hacer INSERT ON DUPLICATE KEY UPDATE
 * en forma de statement preparado (Prepared Statement).
 * Recibe el $conn, la query, y un arreglo con los parámetros
 * (tipos + valores).
 */
function executeUpsert($conn, $query, $bindTypes, ...$params)
{
    $stmt = mysqli_prepare($conn, $query);
    if (!$stmt) {
        die("Falló la preparación del statement: " . mysqli_error($conn));
    }

    // Hacemos bind de parámetros
    mysqli_stmt_bind_param($stmt, $bindTypes, ...$params);

    // Ejecutamos
    if (!mysqli_stmt_execute($stmt)) {
        die("Falló la ejecución del statement: " . mysqli_error($conn));
    }

    mysqli_stmt_close($stmt);
}

/* =====================================================
   1) QUERY para botonshop_productos
   ===================================================== */
$sqlProductos = "
    INSERT INTO botonshop_productos (id, title, body_html, updated_at, status)
    VALUES (?, ?, ?, ?, ?)
    ON DUPLICATE KEY UPDATE
      title = VALUES(title),
      body_html = VALUES(body_html),
      updated_at = VALUES(updated_at),
      status = VALUES(status)
";

/* =====================================================
   2) QUERY para botonshop_variantes
   ===================================================== */
$sqlVariantes = "
    INSERT INTO botonshop_variantes (id, product_id, title, price, taxable, sku, inventory_quantity, admin_graphql_api_id)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    ON DUPLICATE KEY UPDATE
      product_id = VALUES(product_id),
      title = VALUES(title),
      price = VALUES(price),
      taxable = VALUES(taxable),
      sku = VALUES(sku),
      inventory_quantity = VALUES(inventory_quantity),
      admin_graphql_api_id = VALUES(admin_graphql_api_id)
";

/* =====================================================
   3) QUERY para botonshop_opciones
     (columnas: id, product_id, name, option_values)
   ===================================================== */
$sqlOpciones = "
    INSERT INTO botonshop_opciones (id, product_id, name, option_values)
    VALUES (?, ?, ?, ?)
    ON DUPLICATE KEY UPDATE
      product_id = VALUES(product_id),
      name = VALUES(name),
      option_values = VALUES(option_values)
";

/* =====================================================
   4) QUERY para botonshop_imagenes
   ===================================================== */
$sqlImagenes = "
    INSERT INTO botonshop_imagenes (id, product_id, position, src)
    VALUES (?, ?, ?, ?)
    ON DUPLICATE KEY UPDATE
      product_id = VALUES(product_id),
      position = VALUES(position),
      src = VALUES(src)
";

/* =====================================================
   RECORREMOS CADA PRODUCTO DEL JSON
   ===================================================== */
foreach ($data['products'] as $product) {
    // Insertar/Actualizar en botonshop_productos
    $p_id         = $product['id'];
    $p_title      = $product['title'];
    $p_body_html  = $product['body_html'];
    $p_updated_at = isset($product['updated_at']) ? $product['updated_at'] : null;
    $p_status     = isset($product['status'])     ? $product['status']     : null;

    executeUpsert(
        $conn,
        $sqlProductos,
        "issss",
        $p_id,
        $p_title,
        $p_body_html,
        $p_updated_at,
        $p_status
    );

    // ============================
    // VARIANTES
    // ============================
    if (isset($product['variants']) && is_array($product['variants'])) {
        foreach ($product['variants'] as $variant) {
            $v_id         = $variant['id'];
            $v_product_id = $variant['product_id'];
            $v_title      = $variant['title'];
            $v_price      = isset($variant['price']) ? $variant['price'] : "0.00";
            $v_taxable    = (isset($variant['taxable']) && $variant['taxable']) ? 1 : 0;
            $v_sku        = isset($variant['sku']) ? $variant['sku'] : "";
            $v_inv_qty    = isset($variant['inventory_quantity']) ? $variant['inventory_quantity'] : 0;
            $v_graphql    = isset($variant['admin_graphql_api_id']) ? $variant['admin_graphql_api_id'] : "";

            executeUpsert(
                $conn,
                $sqlVariantes,
                "iissisis",
                $v_id,
                $v_product_id,
                $v_title,
                $v_price,
                $v_taxable,
                $v_sku,
                $v_inv_qty,
                $v_graphql
            );
        }
    }

    // ============================
    // OPCIONES
    // ============================
    if (isset($product['options']) && is_array($product['options'])) {
        foreach ($product['options'] as $option) {
            $o_id         = $option['id'];
            $o_product_id = $option['product_id'];
            $o_name       = $option['name'];
            // Convertimos el array de "values" en un string (JSON) para guardarlo en option_values
            $o_values_str = isset($option['values'])
                ? json_encode($option['values'], JSON_UNESCAPED_UNICODE)
                : '[]';

            executeUpsert(
                $conn,
                $sqlOpciones,
                "iiss",
                $o_id,
                $o_product_id,
                $o_name,
                $o_values_str
            );
        }
    }

    // ============================
    // IMÁGENES
    // ============================
    if (isset($product['images']) && is_array($product['images'])) {
        foreach ($product['images'] as $image) {
            $img_id         = $image['id'];
            $img_product_id = $image['product_id'];
            $img_position   = isset($image['position']) ? $image['position'] : 0;
            $img_src        = isset($image['src'])      ? $image['src']      : "";

            executeUpsert(
                $conn,
                $sqlImagenes,
                "iiis",
                $img_id,
                $img_product_id,
                $img_position,
                $img_src
            );
        }
    }

    // ============================
    // IMAGEN PRINCIPAL (product['image'])
    // ============================
    if (isset($product['image']) && is_array($product['image'])) {
        $main_img_id         = $product['image']['id'];
        $main_img_product_id = $product['image']['product_id'];
        $main_img_position   = isset($product['image']['position']) ? $product['image']['position'] : 0;
        $main_img_src        = isset($product['image']['src'])      ? $product['image']['src']      : "";

        executeUpsert(
            $conn,
            $sqlImagenes,
            "iiis",
            $main_img_id,
            $main_img_product_id,
            $main_img_position,
            $main_img_src
        );
    }
}

mysqli_close($conn);
echo "Importación y actualización completadas exitosamente.";