-- Módulo Bandeja WhatsApp CRM - INTERLINK.sys
-- Ejecutar una sola vez sobre la base del CRM.
-- No elimina ni modifica mensajes existentes.

CREATE TABLE IF NOT EXISTS `whatsapp_crm_conversations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `phone` varchar(30) NOT NULL,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `status` enum('open','pending','resolved','blocked') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `assigned_user_id` int(10) unsigned DEFAULT NULL,
  `last_read_at` datetime DEFAULT NULL,
  `last_out_at` datetime DEFAULT NULL,
  `last_in_at` datetime DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_wa_conv_phone` (`phone`),
  KEY `idx_wa_conv_customer` (`customer_id`),
  KEY `idx_wa_conv_status` (`status`),
  KEY `idx_wa_conv_updated` (`updated_at`),
  CONSTRAINT `fk_wa_conv_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `whatsapp_crm_action_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `phone` varchar(30) NOT NULL,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `action` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` mediumtext COLLATE utf8mb4_unicode_ci,
  `result` enum('ok','error') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'ok',
  `error_message` text COLLATE utf8mb4_unicode_ci,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_wa_log_phone` (`phone`,`created_at`),
  KEY `idx_wa_log_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Configuración editable WhatsApp Cloud API desde el CRM.
-- Compatible con la tabla whatsapp_settings existente del CRM.
ALTER TABLE `whatsapp_settings` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `whatsapp_settings` ADD COLUMN IF NOT EXISTS `business_account_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `phone_number_id`;
ALTER TABLE `whatsapp_settings` ADD COLUMN IF NOT EXISTS `verify_token` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `token_enc`;
ALTER TABLE `whatsapp_settings` ADD COLUMN IF NOT EXISTS `last_token_test_at` datetime DEFAULT NULL AFTER `updated_at`;
ALTER TABLE `whatsapp_settings` ADD COLUMN IF NOT EXISTS `last_token_test_result` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `last_token_test_at`;

INSERT INTO `whatsapp_settings`
(`id`, `enabled`, `provider`, `api_version`, `phone_number_id`, `token_enc`, `default_country_code`, `created_at`, `updated_at`)
VALUES
(1, 1, 'meta', 'v22.0', NULL, NULL, '54', NOW(), NOW())
ON DUPLICATE KEY UPDATE `updated_at` = `updated_at`;
