-- ============================================================
-- Transferencia en ejecución del chatbot (MySQL, base separada)
-- scripts/chatbot/mysql/02-chatbot-handoff.sql
--
-- Ejecutar contra la base MySQL del chatbot (CHATBOT_DATABASE_URL),
-- no contra la base principal de la aplicación.
--
-- Tabla:
--   chatbot_handoff
--
-- agency_id y agent_user_id son identificadores de la base principal.
-- No hay claves foráneas hacia esa base. La única clave es
-- chatbot_handoff.conversation_id -> chatbot_conversation.id.
-- ============================================================

CREATE TABLE IF NOT EXISTS chatbot_handoff (
    id INT AUTO_INCREMENT NOT NULL,
    conversation_id INT NOT NULL,
    reason VARCHAR(32) NOT NULL,
    status VARCHAR(20) NOT NULL,
    agency_id INT DEFAULT NULL,
    agent_user_id INT DEFAULT NULL,
    requested_at DATETIME NOT NULL,
    assigned_at DATETIME DEFAULT NULL,
    attended_at DATETIME DEFAULT NULL,
    closed_at DATETIME DEFAULT NULL,
    INDEX idx_chatbot_handoff_conversation (conversation_id),
    INDEX idx_chatbot_handoff_status (status),
    INDEX idx_chatbot_handoff_agent (agent_user_id),
    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_handoff'
      AND CONSTRAINT_NAME = 'fk_chatbot_handoff_conversation'
);

SET @fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE chatbot_handoff ADD CONSTRAINT fk_chatbot_handoff_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;