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 DEFAULT 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`),
  KEY `idx_webhook_hacienda_clave_estado` (`clave`, `estado`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @db_name := DATABASE();
SET @col_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'webhook_hacienda_logs'
    AND COLUMN_NAME = 'payload_hash'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `webhook_hacienda_logs` ADD COLUMN `payload_hash` CHAR(64) NULL AFTER `estado`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'webhook_hacienda_logs'
    AND INDEX_NAME = 'uq_webhook_hacienda_clave_payload'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `webhook_hacienda_logs` ADD UNIQUE KEY `uq_webhook_hacienda_clave_payload` (`clave`, `payload_hash`)',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'documentos'
    AND COLUMN_NAME = 'mh_intentos_webhook'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `documentos` ADD COLUMN `mh_intentos_webhook` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `respuesta_error_hacienda`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'documentos'
    AND COLUMN_NAME = 'mh_webhook_ultimo_en'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `documentos` ADD COLUMN `mh_webhook_ultimo_en` DATETIME NULL AFTER `mh_intentos_webhook`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;