<?php
session_start();
ob_start();

require __DIR__ . '/../../vendor/autoload.php';
require_once __DIR__ . '/config/db.php';  // define $conn = new mysqli(...)
use GuzzleHttp\Client;
use GuzzleHttp\Exception\RequestException;

// — constantes de configuración —
const CLIENT_ID       = '3658695067033012';
const CLIENT_SECRET   = 'GZBg63xJNM9Eeb2cOhF9JwsOEv6vTxo7';
const TOKEN_ROW_ID    = 1;  // idMP en tb_marketplace_tokens

// — crea tablas de importación si no existen —

// ventas
$conn->query("
  CREATE TABLE IF NOT EXISTS importml_sales (
    order_id BIGINT PRIMARY KEY,
    raw_data JSON NOT NULL,
    inserted_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
");

// items
$conn->query("
  CREATE TABLE IF NOT EXISTS importml_sales_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    item_data JSON NOT NULL,
    inserted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(order_id) REFERENCES importml_sales(order_id) ON DELETE CASCADE
  )
");

// pagos
$conn->query("
  CREATE TABLE IF NOT EXISTS importml_sales_payments (
    id                          INT AUTO_INCREMENT PRIMARY KEY,
    order_id                    BIGINT NOT NULL,
    payer_id                    BIGINT NOT NULL,
    payment_type                VARCHAR(50),
    taxes_amount                DECIMAL(20,2),
    coupon_amount               DECIMAL(20,2),
    shipping_cost               DECIMAL(20,2),
    status_detail               VARCHAR(100),
    marketplace_fee             DECIMAL(20,2),
    overpaid_amount             DECIMAL(20,2),
    total_paid_amount           DECIMAL(20,2),
    installment_amount          DECIMAL(20,2) NULL,
    transaction_amount          DECIMAL(20,2),
    transaction_amount_refunded DECIMAL(20,2),
    inserted_at                 DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(order_id) REFERENCES importml_sales(order_id) ON DELETE CASCADE
  )
");

// envíos
$conn->query("
  CREATE TABLE IF NOT EXISTS importml_sales_shipping (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    shipping_data JSON NOT NULL,
    inserted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(order_id) REFERENCES importml_sales(order_id) ON DELETE CASCADE
  )
");

// facturación
$conn->query("
  CREATE TABLE IF NOT EXISTS importml_sales_billing (
  order_id BIGINT PRIMARY KEY,
  cust_id BIGINT,
  name VARCHAR(100),
  last_name VARCHAR(100),
  business_name VARCHAR(255),
  identification_type VARCHAR(10),
  identification_number VARCHAR(50),
  birth_date DATE,
  doc_type_number VARCHAR(50),
  cust_type VARCHAR(10), -- CO o BU

  -- Taxes
  taxpayer_type_id VARCHAR(50),
  taxpayer_type_desc VARCHAR(100),
  contributor VARCHAR(100),
  economic_activity VARCHAR(100),
  state_registration VARCHAR(100),

  -- Address
  street_name VARCHAR(255),
  street_number VARCHAR(100),
  city_name VARCHAR(100),
  neighborhood VARCHAR(100),
  zip_code VARCHAR(20),
  comment VARCHAR(255),
  country_id CHAR(2),
  state_code VARCHAR(20),
  state_name VARCHAR(100),

  -- MLA fields
  secondary_doc_type VARCHAR(20),
  secondary_doc_number VARCHAR(50),

  inserted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY(order_id) REFERENCES importml_sales(order_id) ON DELETE CASCADE
);");

// notas
$conn->query("
  CREATE TABLE IF NOT EXISTS importml_sales_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    note_data JSON NOT NULL,
    inserted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(order_id) REFERENCES importml_sales(order_id) ON DELETE CASCADE
  )
");

// usuarios
$conn->query("
  CREATE TABLE IF NOT EXISTS importml_users (
    user_id    BIGINT PRIMARY KEY,
    city       VARCHAR(100) NULL,
    state      VARCHAR(100) NULL,
    nickname   VARCHAR(100) NOT NULL,
    mail       VARCHAR(255) NOT NULL,
    perfil     VARCHAR(255) NOT NULL,
    inserted_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
");

// impuestos de orden
$conn->query("
  CREATE TABLE IF NOT EXISTS importml_sales_taxes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    amount DECIMAL(20,2),
    currency_id VARCHAR(10),
    inserted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(order_id) REFERENCES importml_sales(order_id) ON DELETE CASCADE
  )
");

// — inicializa cliente HTTP —
$http = new Client([
    'base_uri' => 'https://api.mercadolibre.com',
    'timeout'  => 30,
]);

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 = ?
         LIMIT 1
    ");
    $idMP = TOKEN_ROW_ID;
    $stmt->bind_param('i', $idMP);
    $stmt->execute();
    $creds = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    if (!$creds) die("ERROR: Ejecuta authorize.php primero.");
    return $creds;
}

function refreshTokenIfNeeded(Client $http, array &$c, mysqli $conn)
{
    $expiresAt = strtotime($c['updated_at']) + $c['expires_in'];
    if (time() >= $expiresAt - 60) {
        $resp = $http->post('/oauth/token', [
            'form_params' => [
                'grant_type'    => 'refresh_token',
                'client_id'     => CLIENT_ID,
                'client_secret' => CLIENT_SECRET,
                'refresh_token' => $c['refresh_token'],
            ],
            'headers' => ['Accept' => 'application/json']
        ]);
        $data = json_decode((string)$resp->getBody(), true);
        $c['access_token']  = $data['access_token'];
        $c['refresh_token'] = $data['refresh_token'];
        $c['expires_in']    = $data['expires_in'];
        $upd = $conn->prepare("
            UPDATE tb_marketplace_tokens
               SET access_token=?, refresh_token=?, expires_in=?, updated_at=NOW()
             WHERE idMP=?
        ");
        $tokenid = TOKEN_ROW_ID;
        $upd->bind_param(
            'ssii',
            $c['access_token'],
            $c['refresh_token'],
            $c['expires_in'],
            $tokenid
        );
        $upd->execute();
        $upd->close();
    }
}

function saleExistsImport(mysqli $conn, int $orderId): bool
{
    $stmt = $conn->prepare("SELECT 1 FROM importml_sales WHERE order_id=?");
    $stmt->bind_param('i', $orderId);
    $stmt->execute();
    $stmt->store_result();
    $ex = $stmt->num_rows > 0;
    $stmt->close();
    return $ex;
}

function userExistsImport(mysqli $conn, int $userId): bool
{
    $stmt = $conn->prepare("SELECT 1 FROM importml_users WHERE user_id=?");
    $stmt->bind_param('i', $userId);
    $stmt->execute();
    $stmt->store_result();
    $ex = $stmt->num_rows > 0;
    $stmt->close();
    return $ex;
}

function upsertUser(Client $http, array $creds, mysqli $conn, int $userId)
{
    $resp = $http->get("/users/{$userId}", [
        'headers' => ['Authorization' => "Bearer {$creds['access_token']}"]
    ]);
    $u = json_decode((string)$resp->getBody(), true);
    $city   = $u['address']['city']  ?? null;
    $state  = $u['address']['state'] ?? null;
    $nick   = $u['nickname'];
    $mail   = "{$nick}@mercadolibre.com.co";
    $perfil = $u['permalink'];

    if (userExistsImport($conn, $userId)) {
        $upd = $conn->prepare("
            UPDATE importml_users
               SET city=?, state=?, nickname=?, mail=?, perfil=?, inserted_at=NOW()
             WHERE user_id=?
        ");
        $upd->bind_param('sssssi', $city, $state, $nick, $mail, $perfil, $userId);
        $upd->execute();
        $upd->close();
    } else {
        $ins = $conn->prepare("
            INSERT INTO importml_users
              (user_id, city, state, nickname, mail, perfil)
            VALUES (?,?,?,?,?,?)
        ");
        $ins->bind_param('isssss', $userId, $city, $state, $nick, $mail, $perfil);
        $ins->execute();
        $ins->close();
    }
}

$creds = getCredentials($conn);
refreshTokenIfNeeded($http, $creds, $conn);

// paginación
$after  = '2025-01-01T00:00:00.000-00:00';
$limit  = 50;
$offset = 0;
$processedMasters = [];

do {
    $res = $http->get('/orders/search', [
        'headers' => ['Authorization' => "Bearer {$creds['access_token']}"],
        'query' => [
            'seller' => $creds['user_id'],
            'order.date_created.from' => $after,
            'limit' => $limit,
            'offset' => $offset
        ]
    ]);
    $page = json_decode($res->getBody(), true);

    foreach ($page['results'] as $sum) {
        $childId = (int)$sum['id'];
        try {
            $r2 = $http->get("/orders/{$childId}", [
                'headers' => ['Authorization' => "Bearer {$creds['access_token']}"]
            ]);
            $order = json_decode((string)$r2->getBody(), true);
        } catch (RequestException $e) {
            continue;
        }

        // usuarios
        upsertUser($http, $creds, $conn, (int)$order['buyer']['id']);
        upsertUser($http, $creds, $conn, (int)$order['seller']['id']);

        // masterId = pack_id o order_id
        $masterId = !empty($order['pack_id']) ? (int)$order['pack_id'] : $childId;

        // primera vez por pack: upsert header + limpiar hijos + insertar taxes
        if (!in_array($masterId, $processedMasters, true)) {
            $rawOrder = json_encode($order, JSON_UNESCAPED_UNICODE);
            if (saleExistsImport($conn, $masterId)) {
                $upd = $conn->prepare("
                    UPDATE importml_sales
                       SET raw_data=?, inserted_at=NOW()
                     WHERE order_id=?
                ");
                $upd->bind_param('si', $rawOrder, $masterId);
                $upd->execute();
                $upd->close();

                // limpiar hijos
                $conn->query("DELETE FROM importml_sales_items   WHERE order_id={$masterId}");
                $conn->query("DELETE FROM importml_sales_payments WHERE order_id={$masterId}");
                $conn->query("DELETE FROM importml_sales_shipping WHERE order_id={$masterId}");
                $conn->query("DELETE FROM importml_sales_billing  WHERE order_id={$masterId}");
                $conn->query("DELETE FROM importml_sales_notes    WHERE order_id={$masterId}");
                $conn->query("DELETE FROM importml_sales_taxes    WHERE order_id={$masterId}");
            } else {
                $ins = $conn->prepare("
                    INSERT INTO importml_sales(order_id,raw_data)
                    VALUES(?,?)
                ");
                $ins->bind_param('is', $masterId, $rawOrder);
                $ins->execute();
                $ins->close();
            }

            // insertar impuestos de orden
            if (isset($order['taxes']['amount'])) {
                $amt  = $order['taxes']['amount'];
                $cur  = $order['taxes']['currency_id'] ?? '';
                $insT = $conn->prepare("
                    INSERT INTO importml_sales_taxes(order_id,amount,currency_id)
                    VALUES(?,?,?)
                ");
                $insT->bind_param('dds', $masterId, $amt, $cur);
                $insT->execute();
                $insT->close();
            }

            $processedMasters[] = $masterId;
        }

        // items
        if (!empty($order['order_items'])) {
            foreach ($order['order_items'] as $item) {
                $ri = json_encode($item, JSON_UNESCAPED_UNICODE);
                $ii = $conn->prepare("
                    INSERT INTO importml_sales_items(order_id,item_data)
                    VALUES(?,?)
                ");
                $ii->bind_param('is', $masterId, $ri);
                $ii->execute();
                $ii->close();
            }
        }

        // pagos aprobados (todos)
        if (!empty($order['payments'])) {
            foreach ($order['payments'] as $p) {
                if ($p['status'] !== 'approved') continue;
                $stmt = $conn->prepare("
                    INSERT INTO importml_sales_payments(
                      order_id,payer_id,payment_type,taxes_amount,coupon_amount,
                      shipping_cost,status_detail,marketplace_fee,
                      overpaid_amount,total_paid_amount,installment_amount,
                      transaction_amount,transaction_amount_refunded
                    ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)
                ");
                $stmt->bind_param(
                    'iisddddsddddd',
                    $masterId,
                    $p['payer_id'],
                    $p['payment_type'],
                    $p['taxes_amount'],
                    $p['coupon_amount'],
                    $p['shipping_cost'],
                    $p['status_detail'],
                    $p['marketplace_fee'],
                    $p['overpaid_amount'],
                    $p['total_paid_amount'],
                    $p['installment_amount'],
                    $p['transaction_amount'],
                    $p['transaction_amount_refunded']
                );
                $stmt->execute();
                $stmt->close();
            }
        }
        // obtiene detalle de envío
        if (!empty($order['shipping']['id'])) {
            $shipId = (int)$order['shipping']['id'];
            try {
                $rShip = $http->get("/shipments/{$shipId}", [
                    'headers' => [
                        'Authorization'  => "Bearer {$creds['access_token']}",
                        'x-format-new'   => 'true',
                    ]
                ]);
                $shipDetail = json_encode(json_decode((string)$rShip->getBody(), true), JSON_UNESCAPED_UNICODE);
            } catch (RequestException $e) {
                $shipDetail = json_encode($order['shipping'], JSON_UNESCAPED_UNICODE);
            }
            $insShip = $conn->prepare("
                INSERT INTO importml_sales_shipping (order_id, shipping_data)
                VALUES (?, ?)
            ");
            $insShip->bind_param('is', $masterId, $shipDetail);
            $insShip->execute();
            $insShip->close();
        }

        // obtiene billing_info
        try {
            $rBill = $http->get("/orders/{$childId}/billing_info", [
                'headers' => [
                    'Authorization' => "Bearer {$creds['access_token']}",
                    'x-version'    => '2',
                ]
            ]);

            $billing = json_decode((string)$rBill->getBody(), true);

            $buyer = $billing['buyer'] ?? [];
            $info = $buyer['billing_info'] ?? [];
            $addr = $info['address'] ?? [];
            $state = $addr['state'] ?? [];
            $ident = $info['identification'] ?? [];
            $taxes = $info['taxes'] ?? [];
            $insc = $taxes['inscriptions'] ?? [];
            $attributes = $info['attributes'] ?? [];

            $stmt = $conn->prepare("
        INSERT INTO importml_sales_billing (
            order_id, cust_id, name, last_name, business_name,
            identification_type, identification_number,
            birth_date, doc_type_number, cust_type,
            taxpayer_type_id, taxpayer_type_desc, contributor, economic_activity, state_registration,
            street_name, street_number, city_name, neighborhood, zip_code, comment,
            country_id, state_code, state_name,
            secondary_doc_type, secondary_doc_number
        ) VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )
        ON DUPLICATE KEY UPDATE
            name=VALUES(name), last_name=VALUES(last_name), business_name=VALUES(business_name),
            identification_type=VALUES(identification_type), identification_number=VALUES(identification_number),
            birth_date=VALUES(birth_date), doc_type_number=VALUES(doc_type_number), cust_type=VALUES(cust_type),
            taxpayer_type_id=VALUES(taxpayer_type_id), taxpayer_type_desc=VALUES(taxpayer_type_desc),
            contributor=VALUES(contributor), economic_activity=VALUES(economic_activity),
            state_registration=VALUES(state_registration),
            street_name=VALUES(street_name), street_number=VALUES(street_number),
            city_name=VALUES(city_name), neighborhood=VALUES(neighborhood),
            zip_code=VALUES(zip_code), comment=VALUES(comment), country_id=VALUES(country_id),
            state_code=VALUES(state_code), state_name=VALUES(state_name),
            secondary_doc_type=VALUES(secondary_doc_type), secondary_doc_number=VALUES(secondary_doc_number),
            updated_at=NOW()
    ");

            $billing_name         = $info['name'] ?? null;
            $billing_last_name    = $info['last_name'] ?? null;
            $billing_business     = $info['business_name'] ?? null;
            $ident_type           = $ident['type'] ?? null;
            $ident_number         = $ident['number'] ?? null;
            $birth_date           = $attributes['birth_date'] ?? null;
            $doc_type_number      = $attributes['doc_type_number'] ?? null;
            $cust_type            = $attributes['cust_type'] ?? null;
            $taxpayer_type_id     = $info['taxpayer_type']['id'] ?? null;
            $taxpayer_type_desc   = $info['taxpayer_type']['description'] ?? null;
            $contributor          = $insc['contributor'] ?? null;
            $economic_activity    = $insc['economic_activity'] ?? null;
            $state_registration   = $insc['state_registration'] ?? null;
            $street_name          = $addr['street_name'] ?? null;
            $street_number        = $addr['street_number'] ?? null;
            $city_name            = $addr['city_name'] ?? null;
            $neighborhood         = $addr['neighborhood'] ?? null;
            $zip_code             = $addr['zip_code'] ?? null;
            $comment              = $addr['comment'] ?? null;
            $country_id           = $addr['country_id'] ?? null;
            $state_code           = $state['code'] ?? null;
            $state_name           = $state['name'] ?? null;
            $secondary_doc_type   = $info['secondary_doc_type'] ?? null;
            $secondary_doc_number = $info['secondary_doc_number'] ?? null;
            $cust_id              = $buyer['cust_id'] ?? null;

            $stmt->bind_param(
                'iissssssssssssssssssssssss',
                $masterId,
                $cust_id,
                $billing_name,
                $billing_last_name,
                $billing_business,
                $ident_type,
                $ident_number,
                $birth_date,
                $doc_type_number,
                $cust_type,
                $taxpayer_type_id,
                $taxpayer_type_desc,
                $contributor,
                $economic_activity,
                $state_registration,
                $street_name,
                $street_number,
                $city_name,
                $neighborhood,
                $zip_code,
                $comment,
                $country_id,
                $state_code,
                $state_name,
                $secondary_doc_type,
                $secondary_doc_number
            );

            $stmt->execute();
            $stmt->close();
        } catch (RequestException $e) {
            // billing_info no disponible
        }

        // obtiene notas
        try {
            $rNotes = $http->get("/orders/{$childId}/notes", [
                'headers' => ['Authorization' => "Bearer {$creds['access_token']}"]
            ]);
            $notes = json_decode((string)$rNotes->getBody(), true);
            foreach ($notes as $note) {
                $rawNote = json_encode($note, JSON_UNESCAPED_UNICODE);
                $insNote = $conn->prepare("
                    INSERT INTO importml_sales_notes (order_id, note_data)
                    VALUES (?, ?)
                ");
                $insNote->bind_param('is', $masterId, $rawNote);
                $insNote->execute();
                $insNote->close();
            }
        } catch (RequestException $e) {
            // sin notas
        }
    }

    $offset += $limit;
} while ($offset < $page['paging']['total']);

echo "Importación completada.\n";