<?php
// import_v2.php
require __DIR__ . '/../../vendor/autoload.php';
require_once 'config/db.php';

use GuzzleHttp\Client;
use GuzzleHttp\Exception\ClientException;

/**
 * 0) Prepara tablas de staging: las recrea desde cero.
 */
function prepareImportTables(mysqli $conn)
{
    // Productos
    $conn->query("DROP TABLE IF EXISTS import_products");
    if ($conn->error) {
        die("Error al borrar import_products: {$conn->error}\n");
    }
    $conn->query("
        CREATE TABLE import_products (
            id VARCHAR(50) PRIMARY KEY,
            title VARCHAR(255),
            description TEXT,
            category_id VARCHAR(50),
            price DECIMAL(12,2),
            available_quantity INT DEFAULT 0,
            sold_quantity      INT DEFAULT 0,
            sku VARCHAR(100),
            status VARCHAR(50),
            have_variants TINYINT(1),
            listing_type_id VARCHAR(50),
            listing_fee_amount DECIMAL(12,2),
            sale_fee_amount DECIMAL(12,2),
            currency_id VARCHAR(3),
            financing_add_on_fee DECIMAL(12,2),
            fixed_fee DECIMAL(12,2),
            gross_amount DECIMAL(12,2),
            meli_percentage_fee DECIMAL(12,2),
            percentage_fee DECIMAL(12,2)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ");
    if ($conn->error) {
        die("Error al crear import_products: {$conn->error}\n");
    }

    // Variantes
    $conn->query("DROP TABLE IF EXISTS import_products_variation");
    if ($conn->error) {
        die("Error al borrar import_products_variation: {$conn->error}\n");
    }
    $conn->query("
        CREATE TABLE import_products_variation (
            product_id   VARCHAR(50),
            variant_id   VARCHAR(50),
            sku          VARCHAR(100),
            status       VARCHAR(50),
            stock        INT,
            available_quantity INT DEFAULT 0,
            sold_quantity      INT DEFAULT 0,
            price        DECIMAL(12,2),
            color        VARCHAR(100),
            PRIMARY KEY (product_id, variant_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ");
    if ($conn->error) {
        die("Error al crear import_products_variation: {$conn->error}\n");
    }
}

/**
 * 1) Recuperar credenciales recientes de ML.
 */
function getCredentials(mysqli $conn): array
{
    $stmt = $conn->prepare("
        SELECT user_id, access_token, refresh_token, expires_in, updated_at
          FROM tb_marketplace_tokens
         WHERE idMP = 1
         ORDER BY updated_at DESC
         LIMIT 1
    ");
    $stmt->execute();
    $stmt->bind_result($user_id, $access_token, $refresh_token, $expires_in, $updated_at);
    $stmt->fetch();
    $stmt->close();
    return compact('user_id', 'access_token', 'refresh_token', 'expires_in', 'updated_at');
}

/**
 * 2) Refrescar token si está a punto de expirar.
 */
function refreshTokenIfNeeded(Client $http, array &$creds, mysqli $conn)
{
    $expireTs = strtotime($creds['updated_at']) + $creds['expires_in'];
    if (time() < $expireTs - 60) {
        return;
    }
    $resp = $http->post('/oauth/token', [
        'form_params' => [
            'grant_type'    => 'refresh_token',
            'client_id'     => '3658695067033012',
            'client_secret' => 'GZBg63xJNM9Eeb2cOhF9JwsOEv6vTxo7',
            'refresh_token' => $creds['refresh_token'],
        ],
        'headers' => ['Accept' => 'application/json']
    ]);
    $data = json_decode($resp->getBody(), true);

    // Actualizar credenciales en BD
    $creds['access_token']  = $data['access_token'];
    $creds['refresh_token'] = $data['refresh_token'];
    $creds['expires_in']    = $data['expires_in'];

    // Sólo 3 placeholders: access_token, refresh_token, expires_in
    $stmt = $conn->prepare("
        UPDATE tb_marketplace_tokens
           SET access_token  = ?,
               refresh_token = ?,
               expires_in    = ?,
               updated_at    = NOW()
         WHERE idMP = 1
    ");
    $stmt->bind_param(
        'ssi',
        $creds['access_token'],
        $creds['refresh_token'],
        $creds['expires_in']
    );
    $stmt->execute();
    $stmt->close();
}

/**
 * 3) Realiza el scan/scroll, vuelca en staging, llama al SP y espera 5s.
 */

// ——————————————————————————————————————————————————————
//  Biblioteca para tarifas: caché + back-off exponencial
// ——————————————————————————————————————————————————————
function getListingPrices(Client $http, string $token, float $price, string $category_id): array
{
    static $cache = [];

    // clave única por precio+categoría
    $key = $price . '|' . $category_id;
    if (isset($cache[$key])) {
        return $cache[$key];
    }

    $tries = 0;
    do {
        try {
            // throttle mínimo entre llamadas
            if ($tries > 0) {
                // Exponencial: 1s, 2s, 4s…
                sleep(pow(2, $tries - 1));
            } else {
                // opcional: un micro-sleep para espaciar
                usleep(100000); // 0.1s
            }

            $resp = $http->get('/sites/MCO/listing_prices', [
                'query'   => ['price' => $price, 'category_id' => $category_id],
                'headers' => [
                    'Accept'        => 'application/json',
                    'Authorization' => "Bearer {$token}",
                ],
            ]);
            $data = json_decode($resp->getBody(), true);

            // ML a veces devuelve directamente un objeto en lugar de array
            if (isset($data['listing_type_id'])) {
                $data = [$data];
            }

            return $cache[$key] = $data;
        } catch (ClientException $e) {
            // si no es rate-limit o excedimos reintentos, relanzar
            if ($e->getResponse()->getStatusCode() !== 429 || ++$tries >= 5) {
                throw $e;
            }
            // si fue 429, loopa para reintentar con back-off
        }
    } while (true);
}

function importAllItems(mysqli $conn)
{
    // 3.0) Crear/limpiar staging
    prepareImportTables($conn);

    // 3.1) Cargar credenciales y cliente HTTP
    $creds = getCredentials($conn);
    if (empty($creds['access_token'])) {
        die("No hay token: ejecuta authorize.php primero.\n");
    }
    $http = new Client([
        'base_uri' => 'https://api.mercadolibre.com',
        'timeout'  => 30
    ]);
    // en importAllItems(), reemplaza:
    // session_start();
    // por:
    if (session_status() !== PHP_SESSION_ACTIVE) {
        session_start();
    }

    refreshTokenIfNeeded($http, $creds, $conn);

    $token = $creds['access_token'];
    $user  = $creds['user_id'];
    $limit = 50;

    // 3.2) Primer scan
    $resp = $http->get("/users/{$user}/items/search", [
        'headers' => [
            'Authorization' => "Bearer {$token}",
            'Accept'        => 'application/json',
        ],
        'query' => [
            'search_type' => 'scan',
            'limit'       => $limit,
        ]
    ]);
    $data     = json_decode($resp->getBody(), true);
    $total    = intval($data['paging']['total'] ?? 0);
    $results  = $data['results'] ?? [];
    $scrollId = $data['scroll_id'] ?? null;

    if ($total < 1) {
        echo "No hay productos para importar.\n";
        return;
    }
    echo "Total a importar: {$total}\n";

    // 3.3) Procesar en bucle scroll+batch
    while (!empty($results)) {
        $chunks = array_chunk($results, 20);
        foreach ($chunks as $c) {
            $batchResp = $http->get('/items', [
                'headers' => [
                    'Authorization' => "Bearer {$token}",
                    'Accept'        => 'application/json',
                ],
                'query' => [
                    'ids' => implode(',', $c),
                ]
            ]);
            $batch = json_decode($batchResp->getBody(), true);
            foreach ($batch as $entry) {
                saveOrUpdateProduct($conn, $entry['body'], $http, $token);
            }
        }
        echo "  Procesados " . count($results) . " ítems (scroll)\n";

        if (!$scrollId) {
            break;
        }
        $resp    = $http->get("/users/{$user}/items/search", [
            'headers' => [
                'Authorization' => "Bearer {$token}",
                'Accept'        => 'application/json',
            ],
            'query' => [
                'search_type' => 'scan',
                'scroll_id'   => $scrollId,
                'limit'       => $limit,
            ]
        ]);
        $data     = json_decode($resp->getBody(), true);
        $results  = $data['results'] ?? [];
        $scrollId = $data['scroll_id'] ?? null;
    }

    echo "Importación completada: {$total} ítems.\n";
}

/**
 * Extrae el SKU principal de un ítem.
 */
function extractSkuBase(array $item): string
{
    if (!empty($item['attributes'])) {
        foreach ($item['attributes'] as $attr) {
            if (($attr['id'] ?? '') === 'SELLER_SKU') {
                return $attr['value_name'] ?? '';
            }
        }
    }
    if (!empty($item['seller_custom_field'])) {
        return $item['seller_custom_field'];
    }
    if (!empty($item['variations'][0]['seller_custom_field'])) {
        return $item['variations'][0]['seller_custom_field'];
    }
    return '';
}

/**
 * Extrae el SKU de una variación.
 */
function extractVariantSku(array $variation): string
{
    if (!empty($variation['attributes'])) {
        foreach ($variation['attributes'] as $attr) {
            if (($attr['id'] ?? '') === 'SELLER_SKU') {
                return $attr['value_name'] ?? '';
            }
        }
    }
    return $variation['seller_custom_field'] ?? '';
}

/**
 * 4) Inserta o actualiza producto en import_products.
 */
function saveOrUpdateProduct(mysqli $conn, array $p, Client $http, string $token)
{
    $id             = $p['id'];
    $title       = $p['title']       ?? '';
    $category_id = $p['category_id'] ?? '';
    $price          = $p['price'] ?? 0.0;
    $available_qty = (int)($p['available_quantity'] ?? 0);
    $sold_qty      = (int)($p['sold_quantity']      ?? 0);
    $status         = $p['status'] ?? '';
    $have_variants  = !empty($p['variations']) ? 1 : 0;

    // Descripción
    try {
        $descResp    = $http->get("/items/{$id}/description", [
            'headers' => [
                'Authorization' => "Bearer {$token}",
                'Accept'        => 'application/json',
            ]
        ]);
        $descData    = json_decode($descResp->getBody(), true);
        $description = $descData['plain_text'] ?? '';
    } catch (\Exception $e) {
        $description = '';
    }

    // SKU
    $sku = extractSkuBase($p);
    /*
    // Listing prices
    $listingResp = $http->get('/sites/MCO/listing_prices', [
        'query'   => ['price' => $price, 'category_id' => $category_id],
        'headers' => ['Accept' => 'application/json']
    ]);
    */
    // — Comisiones por categoría (listing & sale fees)
    if ($category_id !== '') {
        try {
            $lpData = getListingPrices($http, $token, (float)$price, $category_id);
            $first  = $lpData[0] ?? [];
        } catch (\Exception $e) {
            $first = [];
            echo "[WARN] No se pudieron recuperar las comisiones: {$e->getMessage()}\n";
        }
    } else {
        $first = [];
    }


    //$first  = $lpData[0] ?? [];

    // Mapeo de cargos
    $listing_type_id     = $first['listing_type_id']       ?? null;
    $listing_fee_amount  = (float)($first['listing_fee_amount'] ?? 0);
    $sale_fee_amount     = (float)($first['sale_fee_amount']    ?? 0);
    $currency_id         = $first['currency_id']           ?? null;
    $details             = $first['sale_fee_details']      ?? [];
    $financing_add_fee   = (float)($details['financing_add_on_fee'] ?? 0);
    $fixed_fee           = (float)($details['fixed_fee']            ?? 0);
    $gross_amount        = (float)($details['gross_amount']         ?? 0);
    $meli_percentage_fee = (float)($details['meli_percentage_fee']  ?? 0);
    $percentage_fee      = (float)($details['percentage_fee']       ?? 0);

    // INSERT en staging
    $stmt = $conn->prepare("
        INSERT INTO import_products (
            id, title, description, category_id, price, available_quantity, sold_quantity, 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?, ?, ?, ?, ?, ?)
    ");
    $stmt->bind_param(
        "ssssdiiissddssddddd",
        $id,
        $title,
        $description,
        $category_id,
        $price,
        $available_qty,
        $sold_qty,
        $sku,
        $status,
        $have_variants,
        $listing_type_id,
        $listing_fee_amount,
        $sale_fee_amount,
        $currency_id,
        $financing_add_fee,
        $fixed_fee,
        $gross_amount,
        $meli_percentage_fee,
        $percentage_fee
    );
    $stmt->execute();
    if ($stmt->error) {
        echo "[ERROR import_products] {$stmt->error}\n";
    }
    $stmt->close();

    // Guardar sus variaciones en staging
    if (!empty($p['variations'])) {
        foreach ($p['variations'] as $variation) {
            saveVariant($conn, $id, $variation);
        }
    }
}

/**
 * 5) Inserta o actualiza variación en import_products_variation.
 */
function saveVariant(mysqli $conn, string $product_id, array $variation)
{
    $skuVar     = extractVariantSku($variation);
    $variant_id = $variation['id'];
    $status     = $variation['status']             ?? '';
    $stock      = (int)($variation['available_quantity'] ?? 0);
    $available_qty = (int)($variation['available_quantity'] ?? 0);
    $sold_qty      = (int)($variation['sold_quantity']      ?? 0);
    $price      = (float)($variation['price']          ?? 0.0);
    $color      = '';
    foreach ($variation['attribute_combinations'] ?? [] as $a) {
        if (($a['id'] ?? '') === 'COLOR') {
            $color = $a['value_name'] ?? '';
            break;
        }
    }

    $stmt = $conn->prepare("
        INSERT INTO import_products_variation (
            product_id, variant_id, sku, status, stock, price, color,available_quantity,sold_quantity
        ) VALUES (?, ?, ?,?,?, ?, ?, ?, ?)
    ");
    $stmt->bind_param(
        "sssiidsii",
        $product_id,
        $variant_id,
        $skuVar,
        $status,
        $stock,
        $price,
        $color,
        $available_qty,
        $sold_qty,
    );
    $stmt->execute();
    if ($stmt->error) {
        echo "[ERROR import_products_variation] {$stmt->error}\n";
    }
    $stmt->close();
}


// ▶️ Importación + SP + pausa + iframe

// 1) Intentar la importación hasta 3 veces
$maxRetries = 3;
for ($i = 1; $i <= $maxRetries; $i++) {
    try {
        importAllItems($conn);
        echo "✅ importAllItems completado en el intento {$i}.\n";
        break;
    } catch (\Exception $e) {
        echo "❌ Error importAllItems (intento {$i}): " . $e->getMessage() . "\n";
        if ($i === $maxRetries) {
            die("‼️ importAllItems falló tras {$maxRetries} intentos.\n");
        }
        echo "    Reintentando en 5 segundos…\n";
        sleep(5);
    }
}

// 2) Ejecutar el procedimiento de consolidación
echo "🔄 Ejecutando SP actualizarMarketplace()\n";
if (! $conn->query("CALL actualizarMarketplace()")) {
    die("‼️ Error al ejecutar actualizarMarketplace(): {$conn->error}\n");
}
echo "✅ actualizarMarketplace() completado.\n";

// 3) Esperar 20 segundos
echo "⏳ Esperando 20 segundos antes de lanzar el iframe…\n";
sleep(20);

// 4) Cerrar conexión DB
echo "🔒 Cerrando conexión DB\n";
$conn->close();

// 5) Renderizar página con iframe
?>
<!DOCTYPE html>
<html lang="es">

<head>
    <meta charset="UTF-8">
    <title>Sincronización ML</title>
    <style>
        body,
        html {
            margin: 0;
            padding: 0;
            height: 100%;
        }

        iframe {
            border: none;
            width: 100%;
            height: 100%;
        }
    </style>
</head>

<body>
    <iframe src="https://sistema.compranet.com.co/marketplace/mercadolibre/sincronizacionml.php"
        title="Sincronización MercadoLibre">
        Tu navegador no soporta iframes.
    </iframe>
</body>

</html>