-- WhatsApp Cloud operational bridge
-- Safe after 040_market_acceleration_foundation.sql

ALTER TABLE chat_channels
    ADD COLUMN IF NOT EXISTS origin ENUM('internal', 'whatsapp') NOT NULL DEFAULT 'internal' AFTER channel_type,
    ADD COLUMN IF NOT EXISTS integration_connection_id BIGINT UNSIGNED NULL AFTER origin,
    ADD COLUMN IF NOT EXISTS contact_id BIGINT UNSIGNED NULL AFTER integration_connection_id,
    ADD COLUMN IF NOT EXISTS external_thread_key VARCHAR(120) NULL AFTER contact_id,
    ADD COLUMN IF NOT EXISTS external_address VARCHAR(40) NULL AFTER external_thread_key,
    ADD COLUMN IF NOT EXISTS metadata_json JSON NULL AFTER external_address;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'chat_channels'
      AND index_name = 'idx_chat_channels_origin'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE chat_channels ADD INDEX idx_chat_channels_origin (tenant_id, origin, integration_connection_id, created_at)',
    '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_address'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE chat_channels ADD INDEX idx_chat_channels_external_address (tenant_id, origin, external_address)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @fk_exists := (
    SELECT COUNT(*) FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'chat_channels'
      AND constraint_name = 'fk_chat_channels_integration_connection'
);
SET @fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE chat_channels ADD CONSTRAINT fk_chat_channels_integration_connection FOREIGN KEY (integration_connection_id) REFERENCES integration_connections(id) ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE stmt FROM @fk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @fk_exists := (
    SELECT COUNT(*) FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'chat_channels'
      AND constraint_name = 'fk_chat_channels_contact'
);
SET @fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE chat_channels ADD CONSTRAINT fk_chat_channels_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE stmt FROM @fk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

ALTER TABLE chat_messages
    ADD COLUMN IF NOT EXISTS direction ENUM('internal', 'inbound', 'outbound') NOT NULL DEFAULT 'internal' AFTER message_type,
    ADD COLUMN IF NOT EXISTS provider VARCHAR(40) NULL AFTER direction,
    ADD COLUMN IF NOT EXISTS external_message_id VARCHAR(160) NULL AFTER provider,
    ADD COLUMN IF NOT EXISTS delivery_status VARCHAR(40) NULL AFTER external_message_id,
    ADD COLUMN IF NOT EXISTS external_sender_name VARCHAR(120) NULL AFTER delivery_status,
    ADD COLUMN IF NOT EXISTS metadata_json JSON NULL AFTER mentions_json;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'chat_messages'
      AND index_name = 'uq_chat_messages_external_provider_id'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE chat_messages ADD UNIQUE KEY uq_chat_messages_external_provider_id (tenant_id, provider, external_message_id)',
    '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_messages'
      AND index_name = 'idx_chat_messages_direction'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE chat_messages ADD INDEX idx_chat_messages_direction (tenant_id, channel_id, direction, created_at)',
    '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_messages'
      AND index_name = 'idx_chat_messages_delivery_status'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE chat_messages ADD INDEX idx_chat_messages_delivery_status (tenant_id, provider, delivery_status, created_at)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
