-- ============================================================
-- Conversaciones y mensajes del chatbot (MySQL, base separada)
-- scripts/chatbot/mysql/01-chatbot-conversations.sql
--
-- Ejecutar contra la base MySQL del chatbot (CHATBOT_DATABASE_URL),
-- no contra la base principal de la aplicación.
--
-- Tablas:
--   chatbot_conversation
--   chatbot_message
--
-- La transferencia en ejecución está en
-- scripts/chatbot/mysql/02-chatbot-handoff.sql.
--
-- maincompany_id, chatbot_configuration_id, chatbot_channel_configuration_id,
-- agency_id, customer_id y agent_user_id son identificadores de la base
-- principal. No hay claves foráneas hacia esa base. La única clave es
-- chatbot_message.conversation_id -> chatbot_conversation.id.
-- ============================================================

CREATE TABLE IF NOT EXISTS chatbot_conversation (
    id INT AUTO_INCREMENT NOT NULL,
    maincompany_id INT NOT NULL,
    chatbot_configuration_id INT NOT NULL,
    chatbot_channel_configuration_id INT NOT NULL,
    agency_id INT DEFAULT NULL,
    customer_id INT DEFAULT NULL,
    customer_wa_id VARCHAR(20) NOT NULL,
    status VARCHAR(20) NOT NULL,
    mode VARCHAR(20) NOT NULL,
    started_at DATETIME NOT NULL,
    last_activity_at DATETIME NOT NULL,
    closed_at DATETIME DEFAULT NULL,
    INDEX idx_chatbot_conversation_company (maincompany_id),
    INDEX idx_chatbot_conversation_configuration (chatbot_configuration_id),
    INDEX idx_chatbot_conversation_channel (chatbot_channel_configuration_id),
    INDEX idx_chatbot_conversation_agency (agency_id),
    INDEX idx_chatbot_conversation_customer (customer_id),
    INDEX idx_chatbot_conversation_open_lookup (chatbot_channel_configuration_id, customer_wa_id, status),
    INDEX idx_chatbot_conversation_last_activity (maincompany_id, last_activity_at),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS chatbot_message (
    id INT AUTO_INCREMENT NOT NULL,
    conversation_id INT NOT NULL,
    sender VARCHAR(20) NOT NULL,
    body LONGTEXT NOT NULL,
    external_message_id VARCHAR(128) DEFAULT NULL,
    agent_user_id INT DEFAULT NULL,
    created_at DATETIME NOT NULL,
    UNIQUE INDEX uniq_chatbot_message_external_id (external_message_id),
    INDEX idx_chatbot_message_conversation (conversation_id),
    INDEX idx_chatbot_message_sender (sender),
    INDEX idx_chatbot_message_created_at (created_at),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

SET @fk_exists := (
    SELECT COUNT(*)
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND TABLE_NAME = 'chatbot_message'
      AND CONSTRAINT_NAME = 'fk_chatbot_message_conversation'
);

SET @fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE chatbot_message ADD CONSTRAINT fk_chatbot_message_conversation FOREIGN KEY (conversation_id) REFERENCES chatbot_conversation (id) ON DELETE CASCADE',
    'SELECT 1'
);

PREPARE fk_stmt FROM @fk_sql;
EXECUTE fk_stmt;
DEALLOCATE PREPARE fk_stmt;