-- Compatibilidad API FE para webhook externo del panel y reenvío de estados MH.
-- Ejecutar una sola vez sobre la base usada por la API FE.

SET @db := DATABASE();

SET @sql := IF(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='documentos') AND NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='documentos' AND COLUMN_NAME='webhook_url'), 'ALTER TABLE `documentos` ADD COLUMN `webhook_url` VARCHAR(500) NULL AFTER `respuesta_error_hacienda`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='documentos') AND NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='documentos' AND COLUMN_NAME='webhook_token'), 'ALTER TABLE `documentos` ADD COLUMN `webhook_token` VARCHAR(180) NULL AFTER `webhook_url`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='documentos') AND NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='documentos' AND COLUMN_NAME='mh_intentos_webhook'), 'ALTER TABLE `documentos` 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=@db AND TABLE_NAME='documentos') AND NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='documentos' AND COLUMN_NAME='mh_webhook_ultimo_en'), '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;
SET @sql := IF(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='documentos') AND NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='documentos' AND INDEX_NAME='idx_documentos_webhook_token'), 'ALTER TABLE `documentos` ADD INDEX `idx_documentos_webhook_token` (`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=@db AND TABLE_NAME='mensajes_receptor') AND NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='mensajes_receptor' AND COLUMN_NAME='webhook_url'), 'ALTER TABLE `mensajes_receptor` ADD COLUMN `webhook_url` VARCHAR(500) NULL AFTER `usuario_envio_id`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='mensajes_receptor') AND NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='mensajes_receptor' AND COLUMN_NAME='webhook_token'), 'ALTER TABLE `mensajes_receptor` ADD COLUMN `webhook_token` VARCHAR(180) NULL AFTER `webhook_url`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='mensajes_receptor') AND NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='mensajes_receptor' AND INDEX_NAME='idx_mr_webhook_token'), 'ALTER TABLE `mensajes_receptor` ADD INDEX `idx_mr_webhook_token` (`webhook_token`)', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;