-- WhatsApp CRM v8 - Fix esquema whatsapp_settings.
-- Compatible con MariaDB 10.1: no usa ALTER TABLE ADD COLUMN IF NOT EXISTS.
-- Soluciona: Unknown column 'business_account_id' in 'INSERT INTO'.

CREATE TABLE IF NOT EXISTS `whatsapp_settings` (
  `id` tinyint(3) unsigned NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `provider` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'meta',
  `api_version` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'v22.0',
  `phone_number_id` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `business_account_id` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `token_enc` mediumtext COLLATE utf8mb4_unicode_ci,
  `verify_token` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `default_country_code` varchar(8) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '54',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `last_token_test_at` datetime DEFAULT NULL,
  `last_token_test_result` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `whatsapp_settings` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SET @schema := DATABASE();

SET @sql := (SELECT IF(COUNT(*)=0, 'ALTER TABLE `whatsapp_settings` ADD COLUMN `enabled` tinyint(1) NOT NULL DEFAULT 1', 'SELECT 1') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@schema AND TABLE_NAME='whatsapp_settings' AND COLUMN_NAME='enabled');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*)=0, 'ALTER TABLE `whatsapp_settings` ADD COLUMN `provider` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''meta''', 'SELECT 1') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@schema AND TABLE_NAME='whatsapp_settings' AND COLUMN_NAME='provider');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*)=0, 'ALTER TABLE `whatsapp_settings` ADD COLUMN `api_version` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''v22.0''', 'SELECT 1') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@schema AND TABLE_NAME='whatsapp_settings' AND COLUMN_NAME='api_version');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*)=0, 'ALTER TABLE `whatsapp_settings` ADD COLUMN `phone_number_id` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL', 'SELECT 1') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@schema AND TABLE_NAME='whatsapp_settings' AND COLUMN_NAME='phone_number_id');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*)=0, 'ALTER TABLE `whatsapp_settings` ADD COLUMN `business_account_id` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL', 'SELECT 1') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@schema AND TABLE_NAME='whatsapp_settings' AND COLUMN_NAME='business_account_id');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*)=0, 'ALTER TABLE `whatsapp_settings` ADD COLUMN `token_enc` mediumtext COLLATE utf8mb4_unicode_ci', 'SELECT 1') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@schema AND TABLE_NAME='whatsapp_settings' AND COLUMN_NAME='token_enc');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*)=0, 'ALTER TABLE `whatsapp_settings` ADD COLUMN `verify_token` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL', 'SELECT 1') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@schema AND TABLE_NAME='whatsapp_settings' AND COLUMN_NAME='verify_token');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*)=0, 'ALTER TABLE `whatsapp_settings` ADD COLUMN `default_country_code` varchar(8) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''54''', 'SELECT 1') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@schema AND TABLE_NAME='whatsapp_settings' AND COLUMN_NAME='default_country_code');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*)=0, 'ALTER TABLE `whatsapp_settings` ADD COLUMN `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@schema AND TABLE_NAME='whatsapp_settings' AND COLUMN_NAME='created_at');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*)=0, 'ALTER TABLE `whatsapp_settings` ADD COLUMN `updated_at` datetime DEFAULT NULL', 'SELECT 1') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@schema AND TABLE_NAME='whatsapp_settings' AND COLUMN_NAME='updated_at');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*)=0, 'ALTER TABLE `whatsapp_settings` ADD COLUMN `last_token_test_at` datetime DEFAULT NULL', 'SELECT 1') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@schema AND TABLE_NAME='whatsapp_settings' AND COLUMN_NAME='last_token_test_at');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*)=0, 'ALTER TABLE `whatsapp_settings` ADD COLUMN `last_token_test_result` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL', 'SELECT 1') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@schema AND TABLE_NAME='whatsapp_settings' AND COLUMN_NAME='last_token_test_result');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

INSERT INTO `whatsapp_settings`
  (`id`, `enabled`, `provider`, `api_version`, `default_country_code`, `created_at`, `updated_at`)
VALUES
  (1, 1, 'meta', 'v22.0', '54', NOW(), NOW())
ON DUPLICATE KEY UPDATE
  `enabled` = IFNULL(`enabled`, 1),
  `provider` = IFNULL(NULLIF(`provider`, ''), 'meta'),
  `api_version` = IF(`api_version` IS NULL OR `api_version` = '' OR `api_version` IN ('v19.0','v20.0','v21.0'), 'v22.0', `api_version`),
  `default_country_code` = IFNULL(NULLIF(`default_country_code`, ''), '54'),
  `updated_at` = NOW();
