-- ============================================================================
-- MIGRACIÓN MULTI-TENANT (SaaS) - WhatsApp Business API Platform
-- Base de datos: artechso_apitestwsp
-- Fecha: 2026-03
-- Descripción: Convierte la plataforma single-tenant a multi-tenant
--              usando company_id como discriminador en todas las tablas.
--              Migra todos los datos existentes a company_id = 1 (Artech).
-- ============================================================================
-- INSTRUCCIONES:
--   1. Hacer BACKUP completo antes de ejecutar
--   2. IMPORTANTE: Seleccionar la base de datos correcta ANTES de ejecutar:
--      - LOCAL:      USE `apiwhatsapp`;
--      - PRODUCCIÓN: USE `artechso_apitestwsp`;
--   3. Ejecutar en phpMyAdmin (cPanel) o MySQL Workbench
--   4. Este script es 100% IDEMPOTENTE: se puede ejecutar varias veces sin error
--   5. Usa procedimiento temporal para verificar si columnas/índices/FK ya existen
-- ============================================================================

-- ⚠️  DESCOMENTA la línea que corresponda a tu entorno:
-- USE `apiwhatsapp`;              -- LOCAL
USE `artechso_apitestwsp`;      -- PRODUCCIÓN

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

-- ============================================================================
-- 0. PROCEDIMIENTO AUXILIAR IDEMPOTENTE
--    Permite ADD COLUMN, ADD INDEX y ADD CONSTRAINT solo si no existen
-- ============================================================================

DROP PROCEDURE IF EXISTS `_safe_alter`;
DELIMITER $$
CREATE PROCEDURE `_safe_alter`(IN p_sql TEXT)
BEGIN
  DECLARE _failed TINYINT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR 1060 SET _failed = 1;  -- Duplicate column name
  DECLARE CONTINUE HANDLER FOR 1061 SET _failed = 1;  -- Duplicate key name
  DECLARE CONTINUE HANDLER FOR 1826 SET _failed = 1;  -- Duplicate FK name
  DECLARE CONTINUE HANDLER FOR 1068 SET _failed = 1;  -- Multiple primary key
  DECLARE CONTINUE HANDLER FOR 1146 SET _failed = 1;  -- Table doesn't exist
  SET @_stmt = p_sql;
  PREPARE _s FROM @_stmt;
  IF _failed = 0 THEN
    EXECUTE _s;
    DEALLOCATE PREPARE _s;
  END IF;
END$$
DELIMITER ;

-- ============================================================================
-- 1. NUEVAS TABLAS (IF NOT EXISTS = seguro re-ejecutar)
-- ============================================================================

-- 1.1 Tabla de empresas/compañías
CREATE TABLE IF NOT EXISTS `companies` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT 'Nombre de la empresa',
  `slug` varchar(50) NOT NULL COMMENT 'Identificador URL-safe único',
  `wa_token` text DEFAULT NULL COMMENT 'Token permanente de WhatsApp Cloud API',
  `wa_phone_number_id` varchar(32) DEFAULT NULL COMMENT 'Phone Number ID de Meta',
  `wa_waba_id` varchar(32) DEFAULT NULL COMMENT 'WhatsApp Business Account ID',
  `wa_display_phone` varchar(20) DEFAULT NULL COMMENT 'Número en formato display (+593...)',
  `wa_verified_name` varchar(100) DEFAULT NULL COMMENT 'Nombre verificado en WhatsApp',
  `graph_api_version` varchar(10) NOT NULL DEFAULT 'v18.0' COMMENT 'Versión de Graph API a usar',
  `webhook_secret` varchar(128) DEFAULT NULL COMMENT 'App Secret para verificar firmas de webhooks',
  `verify_token` varchar(128) DEFAULT NULL COMMENT 'Token de verificación del webhook',
  `max_messages_per_day` int NOT NULL DEFAULT 1000 COMMENT 'Límite diario de mensajes',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_slug` (`slug`),
  UNIQUE KEY `uniq_phone_number_id` (`wa_phone_number_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 1.2 Tabla de API keys (autenticación por empresa)
CREATE TABLE IF NOT EXISTS `api_keys` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `label` varchar(50) DEFAULT 'default' COMMENT 'Etiqueta descriptiva de la key',
  `api_key` varchar(64) NOT NULL COMMENT 'Clave pública (X-API-Key)',
  `api_secret` varchar(128) NOT NULL COMMENT 'Clave secreta (X-API-Secret)',
  `permissions` json DEFAULT NULL COMMENT '{"send":true,"reports":true,"templates":true,"admin":false}',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_api_key` (`api_key`),
  KEY `idx_company_id` (`company_id`),
  CONSTRAINT `fk_apikeys_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 1.3 Tabla de campañas (envíos masivos)
CREATE TABLE IF NOT EXISTS `campaigns` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `name` varchar(100) NOT NULL,
  `template_id` varchar(50) NOT NULL COMMENT 'template_id de whatsapp_templates',
  `status` enum('draft','processing','paused','completed','failed') NOT NULL DEFAULT 'draft',
  `total_recipients` int NOT NULL DEFAULT 0,
  `sent_count` int NOT NULL DEFAULT 0,
  `failed_count` int NOT NULL DEFAULT 0,
  `delay_seconds` int NOT NULL DEFAULT 2 COMMENT 'Delay entre envíos',
  `csv_original_name` varchar(255) DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_campaigns_company` (`company_id`),
  KEY `idx_campaigns_status` (`status`),
  KEY `idx_campaigns_template` (`template_id`),
  CONSTRAINT `fk_campaigns_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 1.4 Tabla de destinatarios de campaña
--     message_id es BIGINT porque messages.id es BIGINT
CREATE TABLE IF NOT EXISTS `campaign_recipients` (
  `id` int NOT NULL AUTO_INCREMENT,
  `campaign_id` int NOT NULL,
  `phone_number` varchar(20) NOT NULL,
  `variables` json DEFAULT NULL COMMENT 'Variables para esta fila',
  `header_media_url` varchar(512) DEFAULT NULL,
  `status` enum('pending','sent','failed') NOT NULL DEFAULT 'pending',
  `message_id` bigint DEFAULT NULL COMMENT 'FK a messages.id si se envió',
  `error_message` text DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cr_campaign` (`campaign_id`),
  KEY `idx_cr_status` (`status`),
  KEY `idx_cr_message` (`message_id`),
  CONSTRAINT `fk_cr_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cr_message` FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ============================================================================
-- 2. AGREGAR company_id A TABLAS EXISTENTES (idempotente via _safe_alter)
-- ============================================================================

-- 2.1 messages
CALL _safe_alter('ALTER TABLE `messages` ADD COLUMN `company_id` int NOT NULL DEFAULT 1 AFTER `id`');
CALL _safe_alter('ALTER TABLE `messages` ADD COLUMN `campaign_id` int DEFAULT NULL AFTER `company_id`');

-- 2.2 incoming_messages
CALL _safe_alter('ALTER TABLE `incoming_messages` ADD COLUMN `company_id` int NOT NULL DEFAULT 1 AFTER `id`');

-- 2.3 message_status
CALL _safe_alter('ALTER TABLE `message_status` ADD COLUMN `company_id` int NOT NULL DEFAULT 1 AFTER `id`');

-- 2.4 billing_events
CALL _safe_alter('ALTER TABLE `billing_events` ADD COLUMN `company_id` int NOT NULL DEFAULT 1 AFTER `id`');

-- 2.5 webhooks_raw
CALL _safe_alter('ALTER TABLE `webhooks_raw` ADD COLUMN `company_id` int DEFAULT NULL AFTER `id`');

-- 2.6 whatsapp_templates
CALL _safe_alter('ALTER TABLE `whatsapp_templates` ADD COLUMN `company_id` int NOT NULL DEFAULT 1 AFTER `id`');

-- 2.7 template_variable_mapping
CALL _safe_alter('ALTER TABLE `template_variable_mapping` ADD COLUMN `company_id` int NOT NULL DEFAULT 1 AFTER `id`');

-- 2.8 crm_users
CALL _safe_alter('ALTER TABLE `crm_users` ADD COLUMN `company_id` int DEFAULT NULL AFTER `id`');

-- ============================================================================
-- 3. INSERTAR EMPRESA ARTECH COMO company_id = 1
-- ============================================================================

INSERT INTO `companies` (`id`, `name`, `slug`, `wa_token`, `wa_phone_number_id`, `wa_waba_id`, `wa_display_phone`, `wa_verified_name`, `graph_api_version`, `webhook_secret`, `verify_token`, `is_active`)
VALUES (
  1,
  'Artech Solutions',
  'artech',
  'EAATIWf0RUZAkBP3ZANUINnzRyVhDHHENN01AHpGzMimTosEhRR44eaGTNaUwanyRs6KjS0e4PcDeFshZAi4UxyWwo5NePeyDnOVARILgZBzu4qOW3ayltoAQqZAWVE9Md8jQ7OIzZAIHKc6zyXQ7RxMMO4DZCKVGoW4mD6fH5W4GDxocUSFN4TswEi8cP2mC1s02H4RZBjcyb1WCZC6jrO16WDpV48jO4XHxOZCAlQK4k7',
  '817854531405693',
  '799342059122741',
  '+593981802659',
  'Artech Solution 2659',
  'v18.0',
  '37494a6d8633c56910bbcd3dfddc8e11',
  'mi-token-de-verificacion-2025',
  1
) ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- 3.1 Insertar API key para Artech (empresa legacy)
INSERT INTO `api_keys` (`company_id`, `label`, `api_key`, `api_secret`, `is_active`)
VALUES (
  1,
  'legacy-default',
  'ak-artech-2026-xj9k3m8p7n5q2w1z',
  'sk-artech-secure-2026-r4t7y2u9i1o3p6a8',
  1
) ON DUPLICATE KEY UPDATE `label` = VALUES(`label`);

-- ============================================================================
-- 4. MIGRAR DATOS EXISTENTES A company_id = 1 (idempotente via _safe_alter)
-- ============================================================================

CALL _safe_alter('UPDATE `messages` SET `company_id` = 1 WHERE `company_id` = 1');
CALL _safe_alter('UPDATE `incoming_messages` SET `company_id` = 1 WHERE `company_id` = 1');
CALL _safe_alter('UPDATE `message_status` SET `company_id` = 1 WHERE `company_id` = 1');
CALL _safe_alter('UPDATE `billing_events` SET `company_id` = 1 WHERE `company_id` = 1');
CALL _safe_alter('UPDATE `webhooks_raw` SET `company_id` = 1 WHERE `company_id` IS NULL');
CALL _safe_alter('UPDATE `whatsapp_templates` SET `company_id` = 1 WHERE `company_id` = 1');
CALL _safe_alter('UPDATE `template_variable_mapping` SET `company_id` = 1 WHERE `company_id` = 1');
CALL _safe_alter('UPDATE `crm_users` SET `company_id` = 1 WHERE `company_id` IS NULL');

-- ============================================================================
-- 5. ÍNDICES PARA RENDIMIENTO MULTI-TENANT (idempotente via _safe_alter)
-- ============================================================================

-- 5.1 messages
CALL _safe_alter('ALTER TABLE `messages` ADD KEY `idx_messages_company` (`company_id`)');
CALL _safe_alter('ALTER TABLE `messages` ADD KEY `idx_messages_company_created` (`company_id`, `created_at`)');
CALL _safe_alter('ALTER TABLE `messages` ADD KEY `idx_messages_company_to` (`company_id`, `to_e164`)');
CALL _safe_alter('ALTER TABLE `messages` ADD KEY `idx_messages_campaign` (`campaign_id`)');

-- 5.2 incoming_messages
CALL _safe_alter('ALTER TABLE `incoming_messages` ADD KEY `idx_incoming_company` (`company_id`)');
CALL _safe_alter('ALTER TABLE `incoming_messages` ADD KEY `idx_incoming_company_from` (`company_id`, `from_number`)');
CALL _safe_alter('ALTER TABLE `incoming_messages` ADD KEY `idx_incoming_company_received` (`company_id`, `received_at`)');

-- 5.3 message_status
CALL _safe_alter('ALTER TABLE `message_status` ADD KEY `idx_msgstatus_company` (`company_id`)');

-- 5.4 billing_events
CALL _safe_alter('ALTER TABLE `billing_events` ADD KEY `idx_billing_company` (`company_id`)');
CALL _safe_alter('ALTER TABLE `billing_events` ADD KEY `idx_billing_company_ts` (`company_id`, `ts`)');

-- 5.5 webhooks_raw
CALL _safe_alter('ALTER TABLE `webhooks_raw` ADD KEY `idx_webhooks_company` (`company_id`)');

-- 5.6 whatsapp_templates
CALL _safe_alter('ALTER TABLE `whatsapp_templates` ADD KEY `idx_templates_company` (`company_id`)');
CALL _safe_alter('ALTER TABLE `whatsapp_templates` ADD KEY `idx_templates_company_status` (`company_id`, `status`)');

-- Modificar la restricción UNIQUE de template_id para que sea por empresa
-- Solo ejecutar si el índice original existe
DROP PROCEDURE IF EXISTS `_fix_template_unique`;
DELIMITER $$
CREATE PROCEDURE `_fix_template_unique`()
proc_body: BEGIN
  DECLARE idx_exists INT DEFAULT 0;
  DECLARE tbl_exists INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR 1146 BEGIN END;

  SELECT COUNT(*) INTO tbl_exists FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'whatsapp_templates';
  IF tbl_exists = 0 THEN
    LEAVE proc_body;
  END IF;

  SELECT COUNT(*) INTO idx_exists
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'whatsapp_templates' AND INDEX_NAME = 'template_id';
  IF idx_exists > 0 THEN
    -- Primero dropear FK que depende de este índice (template_variable_mapping_ibfk_1)
    SET @_fk_exists = 0;
    SELECT COUNT(*) INTO @_fk_exists
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'template_variable_mapping'
      AND CONSTRAINT_NAME = 'template_variable_mapping_ibfk_1' AND CONSTRAINT_TYPE = 'FOREIGN KEY';
    IF @_fk_exists > 0 THEN
      ALTER TABLE `template_variable_mapping` DROP FOREIGN KEY `template_variable_mapping_ibfk_1`;
    END IF;
    ALTER TABLE `whatsapp_templates` DROP INDEX `template_id`;
  END IF;

  SELECT COUNT(*) INTO idx_exists
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'whatsapp_templates' AND INDEX_NAME = 'uniq_company_template';
  IF idx_exists = 0 THEN
    ALTER TABLE `whatsapp_templates` ADD UNIQUE KEY `uniq_company_template` (`company_id`, `template_id`);
  END IF;
END$$
DELIMITER ;
CALL `_fix_template_unique`();
DROP PROCEDURE IF EXISTS `_fix_template_unique`;

-- 5.7 template_variable_mapping - actualizar unique constraint
DROP PROCEDURE IF EXISTS `_fix_tvm_unique`;
DELIMITER $$
CREATE PROCEDURE `_fix_tvm_unique`()
proc_body: BEGIN
  DECLARE idx_exists INT DEFAULT 0;
  DECLARE tbl_exists INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR 1146 BEGIN END;

  SELECT COUNT(*) INTO tbl_exists FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'template_variable_mapping';
  IF tbl_exists = 0 THEN
    LEAVE proc_body;
  END IF;

  SELECT COUNT(*) INTO idx_exists
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'template_variable_mapping' AND INDEX_NAME = 'unique_mapping';
  IF idx_exists > 0 THEN
    -- Verificar si ya incluye company_id (3 columnas = ya migrado)
    SELECT COUNT(*) INTO idx_exists
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'template_variable_mapping'
      AND INDEX_NAME = 'unique_mapping' AND COLUMN_NAME = 'company_id';
    IF idx_exists = 0 THEN
      ALTER TABLE `template_variable_mapping` DROP INDEX `unique_mapping`;
      ALTER TABLE `template_variable_mapping` ADD UNIQUE KEY `unique_mapping` (`company_id`, `template_id`, `variable_position`);
    END IF;
  ELSE
    ALTER TABLE `template_variable_mapping` ADD UNIQUE KEY `unique_mapping` (`company_id`, `template_id`, `variable_position`);
  END IF;
END$$
DELIMITER ;
CALL `_fix_tvm_unique`();
DROP PROCEDURE IF EXISTS `_fix_tvm_unique`;

-- ============================================================================
-- 6. FOREIGN KEYS PARA company_id (idempotente via _safe_alter)
-- ============================================================================

CALL _safe_alter('ALTER TABLE `messages` ADD CONSTRAINT `fk_messages_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)');
CALL _safe_alter('ALTER TABLE `messages` ADD CONSTRAINT `fk_messages_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE SET NULL');

CALL _safe_alter('ALTER TABLE `incoming_messages` ADD CONSTRAINT `fk_incoming_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)');

CALL _safe_alter('ALTER TABLE `message_status` ADD CONSTRAINT `fk_msgstatus_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)');

CALL _safe_alter('ALTER TABLE `billing_events` ADD CONSTRAINT `fk_billing_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)');

CALL _safe_alter('ALTER TABLE `whatsapp_templates` ADD CONSTRAINT `fk_templates_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)');

CALL _safe_alter('ALTER TABLE `template_variable_mapping` ADD CONSTRAINT `fk_tvmapping_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)');

-- ============================================================================
-- 7. RECREAR VISTAS CON company_id
-- ============================================================================

DROP VIEW IF EXISTS `vw_last_status`;
CREATE VIEW `vw_last_status` AS
  SELECT
    `m`.`company_id` AS `company_id`,
    `m`.`id` AS `message_id`,
    `m`.`to_e164` AS `to_e164`,
    `m`.`template_name` AS `template_name`,
    `m`.`created_at` AS `created_at`,
    SUBSTRING_INDEX(
      GROUP_CONCAT(`ms`.`status` ORDER BY `ms`.`ts` DESC SEPARATOR ','),
      ',', 1
    ) AS `last_status`
  FROM `messages` `m`
  LEFT JOIN `message_status` `ms` ON `m`.`id` = `ms`.`message_id`
  GROUP BY `m`.`company_id`, `m`.`id`, `m`.`to_e164`, `m`.`template_name`, `m`.`created_at`;

DROP VIEW IF EXISTS `vw_status_times`;
CREATE VIEW `vw_status_times` AS
  SELECT
    `m`.`company_id` AS `company_id`,
    `ms`.`message_id` AS `message_id`,
    `m`.`to_e164` AS `to_e164`,
    `m`.`template_name` AS `template_name`,
    MAX(CASE WHEN `ms`.`status` = 'sent' THEN `ms`.`ts` END) AS `sent_at`,
    MAX(CASE WHEN `ms`.`status` = 'delivered' THEN `ms`.`ts` END) AS `delivered_at`,
    MAX(CASE WHEN `ms`.`status` = 'read' THEN `ms`.`ts` END) AS `read_at`,
    MAX(CASE WHEN `ms`.`status` = 'failed' THEN `ms`.`ts` END) AS `failed_at`
  FROM `message_status` `ms`
  JOIN `messages` `m` ON `ms`.`message_id` = `m`.`id`
  GROUP BY `m`.`company_id`, `ms`.`message_id`, `m`.`to_e164`, `m`.`template_name`;

-- ============================================================================
-- 8. QUITAR DEFAULT 1 DE company_id (idempotente via _safe_alter)
-- ============================================================================

CALL _safe_alter('ALTER TABLE `messages` ALTER COLUMN `company_id` DROP DEFAULT');
CALL _safe_alter('ALTER TABLE `incoming_messages` ALTER COLUMN `company_id` DROP DEFAULT');
CALL _safe_alter('ALTER TABLE `message_status` ALTER COLUMN `company_id` DROP DEFAULT');
CALL _safe_alter('ALTER TABLE `billing_events` ALTER COLUMN `company_id` DROP DEFAULT');
CALL _safe_alter('ALTER TABLE `whatsapp_templates` ALTER COLUMN `company_id` DROP DEFAULT');
CALL _safe_alter('ALTER TABLE `template_variable_mapping` ALTER COLUMN `company_id` DROP DEFAULT');

-- ============================================================================
-- 9. LIMPIEZA: eliminar procedimiento auxiliar
-- ============================================================================
DROP PROCEDURE IF EXISTS `_safe_alter`;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================================
-- VERIFICACIÓN POST-MIGRACIÓN
-- Ejecutar estos queries para validar:
-- ============================================================================
-- SELECT COUNT(*) AS empresas FROM companies;
-- SELECT COUNT(*) AS api_keys FROM api_keys;
-- SELECT 'messages' AS tabla, COUNT(*) AS total, COUNT(DISTINCT company_id) AS empresas FROM messages
-- UNION ALL SELECT 'incoming_messages', COUNT(*), COUNT(DISTINCT company_id) FROM incoming_messages
-- UNION ALL SELECT 'whatsapp_templates', COUNT(*), COUNT(DISTINCT company_id) FROM whatsapp_templates
-- UNION ALL SELECT 'billing_events', COUNT(*), COUNT(DISTINCT company_id) FROM billing_events;

-- ============================================================================
-- 10. FIX: wa_phone_number_id, wa_token, wa_waba_id → nullable
-- Permite clonar empresas sin WA config (NULL en vez de PENDING_)
-- MySQL UNIQUE permite múltiples NULLs → resuelve la constraint
-- ============================================================================
ALTER TABLE `companies` MODIFY COLUMN `wa_token` text DEFAULT NULL COMMENT 'Token permanente de WhatsApp Cloud API';
ALTER TABLE `companies` MODIFY COLUMN `wa_phone_number_id` varchar(32) DEFAULT NULL COMMENT 'Phone Number ID de Meta';
ALTER TABLE `companies` MODIFY COLUMN `wa_waba_id` varchar(32) DEFAULT NULL COMMENT 'WhatsApp Business Account ID';

-- Quitar UNIQUE constraint — permite que múltiples empresas compartan el mismo número
ALTER TABLE `companies` DROP INDEX `uniq_phone_number_id`;

-- Limpiar PENDING_ existentes: poner NULL (el super-admin puede asignar el ID real con update_company)
UPDATE `companies` SET `wa_phone_number_id` = NULL WHERE `wa_phone_number_id` LIKE 'PENDING_%';
UPDATE `companies` SET `wa_token` = NULL WHERE `wa_token` = '';
UPDATE `companies` SET `wa_waba_id` = NULL WHERE `wa_waba_id` = '';