-- Fix unique constraint introduced for WhatsApp thread keys.
-- Internal chat channels should not collide on NULL/empty external keys.

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'chat_channels'
      AND index_name = 'uq_chat_channels_external_thread'
);
SET @idx_sql := IF(
    @idx_exists = 1,
    'ALTER TABLE chat_channels DROP INDEX uq_chat_channels_external_thread',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'chat_channels'
      AND index_name = 'idx_chat_channels_external_thread'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE chat_channels ADD INDEX idx_chat_channels_external_thread (tenant_id, origin, integration_connection_id, external_thread_key)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
