-- Ajuste webhook Hacienda y trazabilidad de estado FE
-- Ejecutar una sola vez sobre la base usada por la API/panel.

CREATE TABLE IF NOT EXISTS `webhook_hacienda_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `clave` VARCHAR(50) NOT NULL,
  `estado` VARCHAR(30) NOT NULL,
  `payload_hash` CHAR(64) NULL,
  `payload` LONGTEXT NULL,
  `headers` LONGTEXT NULL,
  `ip` VARCHAR(45) NULL,
  `procesado` TINYINT(1) NOT NULL DEFAULT 0,
  `error` TEXT NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_webhook_hacienda_clave_payload` (`clave`, `payload_hash`),
  KEY `idx_webhook_hacienda_clave` (`clave`),
  KEY `idx_webhook_hacienda_estado` (`estado`),
  KEY `idx_webhook_hacienda_procesado` (`procesado`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @sql := IF(
  EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'documentos_electronicos'
  ) AND NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'documentos_electronicos' AND COLUMN_NAME = 'mh_intentos_webhook'
  ),
  'ALTER TABLE `documentos_electronicos` ADD COLUMN `mh_intentos_webhook` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `webhook_token`',
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'documentos_electronicos'
  ) AND NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'documentos_electronicos' AND COLUMN_NAME = 'mh_webhook_ultimo_en'
  ),
  'ALTER TABLE `documentos_electronicos` ADD COLUMN `mh_webhook_ultimo_en` DATETIME NULL AFTER `mh_intentos_webhook`',
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;