<?php
require_once 'config/db.php';

/**
 * Obtiene el user_id y access_token más reciente de la tabla tb_tokens
 */
function getAccessTokenAndUserId($conn)
{
    $stmt = $conn->prepare("SELECT user_id, access_token FROM tb_tokens ORDER BY updated_at DESC LIMIT 1");
    $stmt->execute();
    $stmt->bind_result($user_id, $access_token);
    $stmt->fetch();
    $stmt->close();
    return ['user_id' => $user_id, 'access_token' => $access_token];
}

/**
 * Llama a la API de ML para obtener todos los ítems de un usuario (en lotes de 50),
 * luego procesa cada uno en saveProduct().
 */
function saveProducts($conn, $user_id, $access_token)
{
    $offset = 0;
    $limit = 50;

    do {
        $url = "https://api.mercadolibre.com/users/$user_id/items/search?offset=$offset&limit=$limit&access_token=$access_token";

        // file_get_contents con @ para suprimir warning si hay fallo de conexión
        $response = @file_get_contents($url);
        if ($response === FALSE) {
            $error = error_get_last();
            die('Error fetching products: ' . $error['message']);
        }

        $response_data = json_decode($response, true);
        if (isset($response_data['error'])) {
            die('API Error: ' . $response_data['message']);
        }

        // Si no hay más resultados, salimos
        if (!isset($response_data['results']) || empty($response_data['results'])) {
            break;
        }

        // Iterar cada ID de producto devuelto
        foreach ($response_data['results'] as $product_id) {
            $product_data = getProductDetails($product_id, $access_token);
            saveProduct($conn, $product_data);
        }

        $offset += $limit;
    } while ($offset < $response_data['paging']['total']);
}

/**
 * Llama al endpoint /items/<product_id> para obtener el detalle completo del producto
 */
function getProductDetails($product_id, $access_token)
{
    $url = "https://api.mercadolibre.com/items/$product_id?access_token=$access_token";
    $response = @file_get_contents($url);
    if ($response === FALSE) {
        $error = error_get_last();
        die("Error fetching product details for $product_id: " . $error['message']);
    }

    return json_decode($response, true);
}

/**
 * Guarda/actualiza un producto en tb_products. Además:
 * - Llama a /listing_prices para obtener comisiones y costos de publicación.
 * - Inserta el SKU (si existe).
 * - Maneja el "status" de manera segura para evitar warnings.
 * - Guarda las variaciones en tb_product_variants.
 */
function saveProduct($conn, $product)
{
    // 1. Datos básicos del producto
    $id          = $product['id'];
    $title       = $product['title'];
    // plain_text no siempre existe, lo tomamos como '' si no está
    $description = isset($product['plain_text']) ? $product['plain_text'] : '';
    $category_id = $product['category_id'];
    // El precio (float para asegurarnos)
    $price       = isset($product['price']) ? (float)$product['price'] : 0.0;
    // Si no existe seller_custom_field, dejamos SKU vacío
    $sku = '';

    // 1. Si viene en seller_custom_field o seller_sku
    if (!empty($product['seller_custom_field'])) {
        $sku = $product['seller_custom_field'];
    } elseif (!empty($product['seller_sku'])) {
        $sku = $product['seller_sku'];
    }

    // 2. Si aún no hay SKU, intentamos con la primera variación
    if (empty($sku) && isset($product['variations'][0]['seller_custom_field'])) {
        $variantSku = $product['variations'][0]['seller_custom_field'];
        // Extraer base (ej: de CPN-02046-01 → CPN-02046)
        if (preg_match('/^([A-Z]+-\d{5})/', $variantSku, $match)) {
            $sku = $match[1];
        } else {
            $sku = $variantSku; // fallback si no coincide
        }
    }


    // status puede no estar definido en algunos casos
    $status      = isset($product['status']) ? $product['status'] : '';

    // Validamos si tiene variaciones
    $have_variants = (isset($product['variations']) && count($product['variations']) > 0) ? 1 : 0;

    // 2. Llamar a /listing_prices para obtener comisiones/costo de publicación
    //    NOTA: "MCO" es el site de Colombia. Si tu site es otro, cámbialo (MLA, MLB, etc.).
    $listing_type_id      = null;
    $listing_fee_amount   = 0.0;
    $sale_fee_amount      = 0.0;
    $currency_id          = null;
    // Bloque sale_fee_details
    $financing_add_on_fee = 0.0;
    $fixed_fee            = 0.0;
    $gross_amount         = 0.0;
    $meli_percentage_fee  = 0.0;
    $percentage_fee       = 0.0;

    if ($price > 0 && !empty($category_id)) {
        $urlListing = "https://api.mercadolibre.com/sites/MCO/listing_prices?price=$price&category_id=$category_id";
        $listingResponse = @file_get_contents($urlListing);
        if ($listingResponse !== FALSE) {
            $dataListing = json_decode($listingResponse, true);

            // A veces la respuesta es un array, a veces un solo objeto con listing_type_id
            // Verificamos para homogeneizar.
            if (isset($dataListing['listing_type_id'])) {
                // Convertimos en array
                $dataListing = [$dataListing];
            }

            if (is_array($dataListing) && count($dataListing) > 0) {
                // Tomamos el primer elemento (o busca el que te interese)
                $first = $dataListing[0];

                $listing_type_id    = $first['listing_type_id']       ?? null;
                $listing_fee_amount = (float)($first['listing_fee_amount'] ?? 0.0);
                $sale_fee_amount    = (float)($first['sale_fee_amount']    ?? 0.0);
                $currency_id        = $first['currency_id']           ?? null;

                // Extraer "sale_fee_details" si existe
                if (isset($first['sale_fee_details']) && is_array($first['sale_fee_details'])) {
                    $details = $first['sale_fee_details'];
                    $financing_add_on_fee = (float)($details['financing_add_on_fee']  ?? 0.0);
                    $fixed_fee            = (float)($details['fixed_fee']             ?? 0.0);
                    $gross_amount         = (float)($details['gross_amount']          ?? 0.0);
                    $meli_percentage_fee  = (float)($details['meli_percentage_fee']   ?? 0.0);
                    $percentage_fee       = (float)($details['percentage_fee']        ?? 0.0);
                }
            }
        }
    }

    // 3. Insertar/actualizar en tb_products (incluyendo las columnas nuevas)
    //    Asegúrate de que la PK de tb_products sea 'id' o algún UNIQUE KEY para ON DUPLICATE KEY
    $sql = "
        INSERT INTO tb_products (
            id, title, description, category_id, price, sku, status, have_variants,
            listing_type_id, listing_fee_amount, sale_fee_amount, currency_id,
            financing_add_on_fee, fixed_fee, gross_amount, meli_percentage_fee, percentage_fee
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?,
                ?, ?, ?, ?,
                ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
            title=VALUES(title),
            description=VALUES(description),
            category_id=VALUES(category_id),
            price=VALUES(price),
            status=VALUES(status),
            have_variants=VALUES(have_variants),
            listing_type_id=VALUES(listing_type_id),
            listing_fee_amount=VALUES(listing_fee_amount),
            sale_fee_amount=VALUES(sale_fee_amount),
            currency_id=VALUES(currency_id),
            financing_add_on_fee=VALUES(financing_add_on_fee),
            fixed_fee=VALUES(fixed_fee),
            gross_amount=VALUES(gross_amount),
            meli_percentage_fee=VALUES(meli_percentage_fee),
            percentage_fee=VALUES(percentage_fee)
    ";

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        echo "<p style='color:red;'>[ERROR] Prepare failed: " . $conn->error . "</p>";
        return;
    }

    $stmt->bind_param(
        "ssssssssssddsdddd",
        $id,
        $title,
        $description,
        $category_id,
        $price,
        $sku,
        $status,
        $have_variants,

        $listing_type_id,
        $listing_fee_amount,
        $sale_fee_amount,
        $currency_id,

        $financing_add_on_fee,
        $fixed_fee,
        $gross_amount,
        $meli_percentage_fee,
        $percentage_fee
    );

    $stmt->execute();
    if ($stmt->error) {
        echo "<p style='color:red;'>[ERROR] Al guardar producto {$id}: {$stmt->error}</p>";
    }
    $stmt->close();

    // 4. Guardar las variaciones (si existen)
    if ($have_variants) {
        foreach ($product['variations'] as $variation) {
            saveVariant($conn, $id, $variation);
        }
    }
}

/**
 * Guarda/actualiza una variación en tb_product_variants
 */
function saveVariant($conn, $product_id, $variation)
{
    // Prepara la query
    $sql = "INSERT INTO tb_product_variants (product_id, variant_id, sku, status, stock, price, color)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE status=VALUES(status), stock=VALUES(stock),
            price=VALUES(price), color=VALUES(color)";

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        echo "<p style='color:red;'>[ERROR] Prepare variant failed: " . $conn->error . "</p>";
        return;
    }

    $variant_id = $variation['id'];
    $sku        = isset($variation['seller_custom_field']) ? $variation['seller_custom_field'] : '';
    // Evitar warning si 'status' no existe en la variación
    $status     = isset($variation['status']) ? $variation['status'] : '';
    $stock      = isset($variation['available_quantity']) ? (int)$variation['available_quantity'] : 0;
    $price      = isset($variation['price']) ? (float)$variation['price'] : 0.0;

    // Extraer el color de attribute_combinations
    $color = '';
    if (isset($variation['attribute_combinations'])) {
        foreach ($variation['attribute_combinations'] as $attribute) {
            if ($attribute['id'] === 'COLOR' && isset($attribute['value_name'])) {
                $color = $attribute['value_name'];
                break;
            }
        }
    }

    $stmt->bind_param("ssssids", $product_id, $variant_id, $sku, $status, $stock, $price, $color);
    $stmt->execute();
    if ($stmt->error) {
        echo "<p style='color:red;'>[ERROR] Al guardar variante {$variant_id} de producto {$product_id}: {$stmt->error}</p>";
    }
    $stmt->close();
}

// -----------------------------------------------------------------------------------
// EJECUTAMOS EL PROCESO
// -----------------------------------------------------------------------------------
$credentials = getAccessTokenAndUserId($conn);
$user_id = $credentials['user_id'];
$access_token = $credentials['access_token'];

saveProducts($conn, $user_id, $access_token);

echo "Products and variants saved successfully (including listing prices & sale_fee_details).";
$conn->close();