-- =============================================================================
-- XPERT SAP B1 - Migracion 029
-- Integracion con SAP Business One Service Layer: configuracion de conexion
-- (singleton, una sola CompanyDB para todo el sistema), cola/log de
-- sincronizacion, y columnas de vinculo SAP en los maestros locales.
-- =============================================================================

-- ============================================================================
-- 1. CONFIGURACION DE CONEXION SAP B1 (singleton - una sola fila, id=1)
-- ============================================================================
CREATE TABLE IF NOT EXISTS `sap_config` (
    `id`                 TINYINT UNSIGNED PRIMARY KEY DEFAULT 1,
    `service_layer_url`  VARCHAR(255) NOT NULL COMMENT 'Ej: https://sap-host:50000/b1s/v1',
    `company_db`         VARCHAR(100) NOT NULL,
    `username`           VARCHAR(100) NOT NULL,
    `password_enc`       VARCHAR(255) NOT NULL COMMENT 'Cifrado con Core\\Crypto (AES-256-CBC), nunca texto plano',
    `verify_ssl`         TINYINT(1)   NOT NULL DEFAULT 1,
    `timeout_seconds`    SMALLINT UNSIGNED NOT NULL DEFAULT 30,
    `is_active`          TINYINT(1)   NOT NULL DEFAULT 1,
    `updated_at`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by`         INT UNSIGNED NULL,
    CONSTRAINT `chk_sap_config_singleton` CHECK (`id` = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Una sola conexion SAP B1 para todo el sistema (no multi-tenant SAP)';

-- ============================================================================
-- 2. COLA / LOG DE SINCRONIZACION (patron portado de WMS, ya probado en produccion)
-- ============================================================================
CREATE TABLE IF NOT EXISTS `sap_sync_log` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `sync_type`        ENUM('MASTER_ITEMS','MASTER_WAREHOUSES','MASTER_BP') NOT NULL,
    `direction`         ENUM('SAP_TO_WMS','WMS_TO_SAP') NOT NULL DEFAULT 'SAP_TO_WMS',
    `status`            ENUM('PENDING','SUCCESS','ERROR','RETRY','WARN') NOT NULL DEFAULT 'PENDING',
    `retry_count`       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `max_retries`       SMALLINT UNSIGNED NOT NULL DEFAULT 3,
    `next_retry_at`     DATETIME NULL,
    `records_processed` INT UNSIGNED NOT NULL DEFAULT 0,
    `records_failed`    INT UNSIGNED NOT NULL DEFAULT 0,
    `request_payload`   LONGTEXT NULL,
    `response_payload`  LONGTEXT NULL,
    `error_message`     TEXT NULL,
    `triggered_by`      INT UNSIGNED NULL COMMENT 'Usuario que disparo la sincronizacion manual',
    `created_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_syncLog_type_status` (`sync_type`, `status`),
    KEY `idx_syncLog_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Historial y cola de reintentos de cada corrida de sincronizacion con SAP B1';

-- ============================================================================
-- 3. VINCULOS SAP EN MAESTROS LOCALES (denormalizado, sin tabla de mapeo aparte)
-- ============================================================================
ALTER TABLE `items`
    ADD COLUMN `sap_item_code` VARCHAR(50) NULL AFTER `sku`,
    ADD UNIQUE KEY `uq_items_company_sap_code` (`company_id`, `sap_item_code`);

ALTER TABLE `warehouses`
    ADD COLUMN `sap_warehouse_code` VARCHAR(20) NULL AFTER `code`,
    ADD UNIQUE KEY `uq_warehouses_sap_code` (`sap_warehouse_code`);

ALTER TABLE `customers`
    ADD COLUMN `sap_card_code` VARCHAR(20) NULL AFTER `code`,
    ADD UNIQUE KEY `uq_customers_company_sap_code` (`company_id`, `sap_card_code`);

ALTER TABLE `suppliers`
    ADD COLUMN `sap_card_code` VARCHAR(20) NULL AFTER `code`,
    ADD UNIQUE KEY `uq_suppliers_company_sap_code` (`company_id`, `sap_card_code`);
