-- ============================================================
-- Chatbot handoff configuration (TrackingPremium)
-- scripts/chatbot/03-chatbot-handoff-configuration.sql
--
-- Tabla: chatbot_handoff_configuration
-- Configuración de reglas de transferencia a agente humano
-- (global por compañía + override opcional por agencia, mismo
-- patrón que chatbot_business_hours). Solo configuración: no
-- ejecuta el handoff ni registra transferencias reales.
--
-- Prerrequisitos (deben existir):
--   chatbot_configuration, agency
--
-- Equivalente a: migrations/Version20260811100000.php
--
-- Nota errno 150 / 3780:
--   agency_id y target_agency_id deben coincidir con agency.id.
--   Este script usa INT con signo, igual que chatbot_business_hours.
--   Si agency.id es INT UNSIGNED, cambia esas dos columnas a INT UNSIGNED.
--   Si un intento anterior falló a mitad, el DROP de abajo limpia la tabla.
-- ============================================================

-- Si un intento anterior dejó la tabla sin FKs:
DROP TABLE IF EXISTS `chatbot_handoff_configuration`;

CREATE TABLE `chatbot_handoff_configuration` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `chatbot_configuration_id` INT NOT NULL,
    `agency_id` INT DEFAULT NULL,
    `on_customer_request` TINYINT(1) NOT NULL DEFAULT 1,
    `on_bot_unresolved` TINYINT(1) NOT NULL DEFAULT 1,
    `on_negative_sentiment` TINYINT(1) NOT NULL DEFAULT 0,
    `on_outside_business_hours` TINYINT(1) NOT NULL DEFAULT 0,
    `unresolved_attempts_threshold` SMALLINT NOT NULL DEFAULT 2,
    `negative_sentiment_threshold` NUMERIC(3, 2) NOT NULL DEFAULT 0.60,
    `customer_request_keywords` JSON DEFAULT NULL,
    `outside_hours_behavior` VARCHAR(20) NOT NULL DEFAULT 'MESSAGE_ONLY',
    `no_agents_behavior` VARCHAR(20) NOT NULL DEFAULT 'QUEUE',
    `outside_hours_message` LONGTEXT DEFAULT NULL,
    `no_agents_message` LONGTEXT DEFAULT NULL,
    `assignment_scope` VARCHAR(20) NOT NULL DEFAULT 'CUSTOMER_AGENCY',
    `target_agency_id` INT DEFAULT NULL,
    `distribution_strategy` VARCHAR(20) NOT NULL DEFAULT 'MANUAL',
    `max_queue_size` INT DEFAULT NULL,
    `notification_channels` JSON DEFAULT NULL,
    `notification_emails` JSON DEFAULT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    UNIQUE INDEX `uniq_chatbot_handoff_configuration_agency` (`chatbot_configuration_id`, `agency_id`),
    INDEX `idx_chatbot_handoff_configuration` (`chatbot_configuration_id`),
    INDEX `idx_chatbot_handoff_agency` (`agency_id`),
    INDEX `idx_chatbot_handoff_target_agency` (`target_agency_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

-- FKs por separado para localizar cuál falla si vuelve errno 150
ALTER TABLE `chatbot_handoff_configuration`
    ADD CONSTRAINT `FK_CHATBOT_HANDOFF_CONFIGURATION`
    FOREIGN KEY (`chatbot_configuration_id`) REFERENCES `chatbot_configuration` (`id`);

ALTER TABLE `chatbot_handoff_configuration`
    ADD CONSTRAINT `FK_CHATBOT_HANDOFF_AGENCY`
    FOREIGN KEY (`agency_id`) REFERENCES `agency` (`id`);

ALTER TABLE `chatbot_handoff_configuration`
    ADD CONSTRAINT `FK_CHATBOT_HANDOFF_TARGET_AGENCY`
    FOREIGN KEY (`target_agency_id`) REFERENCES `agency` (`id`);

-- Diagnóstico (si alguna FK falla):
-- SHOW CREATE TABLE `agency`;
-- SHOW CREATE TABLE `chatbot_configuration`;
-- SHOW CREATE TABLE `chatbot_business_hours`;
--
-- Si agency.id es INT UNSIGNED, cambia agency_id y target_agency_id
-- a INT UNSIGNED y reejecuta desde el DROP.
--
-- Rollback:
-- DROP TABLE `chatbot_handoff_configuration`;