-- ============================================================
-- Chatbot tables (TrackingPremium)
-- scripts/chatbot/01-chatbot-tables.sql
--
-- Tablas:
--   chatbot_configuration
--   chatbot_usage_monthly
--   chatbot_channel_configuration
--   chatbot_business_hours
--
-- Prerrequisitos (deben existir):
--   maincompany, user, extraplancompany, whatsapp_phone, agency
--
-- No modifica: twilio, whatsapp_phone
-- El plan del chatbot sigue en extraplan / extraplancompany
-- con category = 'CHATBOT'.
-- ============================================================

-- 1) Configuración del chatbot (1 por compañía)
CREATE TABLE IF NOT EXISTS `chatbot_configuration` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `maincompany_id` INT NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 0,
    `default_language` VARCHAR(10) NOT NULL DEFAULT 'es',
    `timezone` VARCHAR(100) NOT NULL,
    `welcome_message` TEXT DEFAULT NULL,
    `fallback_message` TEXT DEFAULT NULL,
    `unavailable_message` TEXT DEFAULT NULL,
    `human_handoff_message` TEXT DEFAULT NULL,
    `unknown_customer_message` TEXT DEFAULT NULL,
    `system_prompt` LONGTEXT DEFAULT NULL,
    `history_message_limit` INT NOT NULL DEFAULT 20,
    `conversation_timeout_minutes` INT NOT NULL DEFAULT 30,
    `handoff_timeout_minutes` INT DEFAULT NULL,
    `allow_unknown_customers` TINYINT(1) NOT NULL DEFAULT 0,
    `auto_identify_customer` TINYINT(1) NOT NULL DEFAULT 1,
    `auto_handoff_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    `created_by_id` INT DEFAULT NULL,
    `updated_by_id` INT DEFAULT NULL,
    UNIQUE INDEX `unique_chatbot_configuration_company` (`maincompany_id`),
    INDEX `IDX_CHATBOT_CONFIGURATION_MAINCOMPANY` (`maincompany_id`),
    INDEX `IDX_CHATBOT_CONFIGURATION_CREATED_BY` (`created_by_id`),
    INDEX `IDX_CHATBOT_CONFIGURATION_UPDATED_BY` (`updated_by_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE `chatbot_configuration`
    ADD CONSTRAINT `FK_CHATBOT_CONFIGURATION_MAINCOMPANY` FOREIGN KEY (`maincompany_id`) REFERENCES `maincompany` (`id`),
    ADD CONSTRAINT `FK_CHATBOT_CONFIGURATION_CREATED_BY` FOREIGN KEY (`created_by_id`) REFERENCES `user` (`id`),
    ADD CONSTRAINT `FK_CHATBOT_CONFIGURATION_UPDATED_BY` FOREIGN KEY (`updated_by_id`) REFERENCES `user` (`id`);

-- 2) Consumo mensual
CREATE TABLE IF NOT EXISTS `chatbot_usage_monthly` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `maincompany_id` INT NOT NULL,
    `extraplancompany_id` INT NOT NULL,
    `year` SMALLINT NOT NULL,
    `month` SMALLINT NOT NULL,
    `conversation_count` INT NOT NULL DEFAULT 0,
    `overage_conversation_count` INT NOT NULL DEFAULT 0,
    `customer_message_count` INT NOT NULL DEFAULT 0,
    `bot_message_count` INT NOT NULL DEFAULT 0,
    `ai_request_count` INT NOT NULL DEFAULT 0,
    `input_tokens` BIGINT NOT NULL DEFAULT 0,
    `output_tokens` BIGINT NOT NULL DEFAULT 0,
    `estimated_ai_cost` NUMERIC(12, 6) NOT NULL DEFAULT 0.000000,
    `estimated_channel_cost` NUMERIC(12, 6) NOT NULL DEFAULT 0.000000,
    `human_handoff_count` INT NOT NULL DEFAULT 0,
    `resolved_by_bot_count` INT NOT NULL DEFAULT 0,
    `last_conversation_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    UNIQUE INDEX `uniq_chatbot_usage_company_plan_period` (`maincompany_id`, `extraplancompany_id`, `year`, `month`),
    INDEX `idx_chatbot_usage_monthly_maincompany` (`maincompany_id`),
    INDEX `idx_chatbot_usage_monthly_extraplancompany` (`extraplancompany_id`),
    INDEX `idx_chatbot_usage_monthly_period` (`year`, `month`),
    INDEX `idx_chatbot_usage_monthly_last_conversation` (`last_conversation_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE `chatbot_usage_monthly`
    ADD CONSTRAINT `FK_CHATBOT_USAGE_MONTHLY_MAINCOMPANY` FOREIGN KEY (`maincompany_id`) REFERENCES `maincompany` (`id`),
    ADD CONSTRAINT `FK_CHATBOT_USAGE_MONTHLY_EXTRAPLANCOMPANY` FOREIGN KEY (`extraplancompany_id`) REFERENCES `extraplancompany` (`id`);

-- 3) Canales WhatsApp (global / por agencia)
CREATE TABLE IF NOT EXISTS `chatbot_channel_configuration` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `chatbot_configuration_id` INT NOT NULL,
    `whatsapp_phone_id` INT NOT NULL,
    `agency_id` INT DEFAULT NULL,
    `display_name` VARCHAR(150) NOT NULL,
    `is_global` TINYINT(1) NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `priority` INT NOT NULL DEFAULT 0,
    `metadata` JSON DEFAULT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    UNIQUE INDEX `uniq_chatbot_channel_whatsapp_phone` (`whatsapp_phone_id`),
    UNIQUE INDEX `uniq_chatbot_channel_config_agency` (`chatbot_configuration_id`, `agency_id`),
    INDEX `idx_chatbot_channel_configuration` (`chatbot_configuration_id`),
    INDEX `idx_chatbot_channel_whatsapp_phone` (`whatsapp_phone_id`),
    INDEX `idx_chatbot_channel_agency` (`agency_id`),
    INDEX `idx_chatbot_channel_is_active` (`is_active`),
    INDEX `idx_chatbot_channel_is_global` (`is_global`),
    INDEX `idx_chatbot_channel_priority` (`priority`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE `chatbot_channel_configuration`
    ADD CONSTRAINT `FK_CHATBOT_CHANNEL_CONFIGURATION` FOREIGN KEY (`chatbot_configuration_id`) REFERENCES `chatbot_configuration` (`id`),
    ADD CONSTRAINT `FK_CHATBOT_CHANNEL_WHATSAPP_PHONE` FOREIGN KEY (`whatsapp_phone_id`) REFERENCES `whatsapp_phone` (`id`),
    ADD CONSTRAINT `FK_CHATBOT_CHANNEL_AGENCY` FOREIGN KEY (`agency_id`) REFERENCES `agency` (`id`);

-- 4) Horarios (varios intervalos por día)
CREATE TABLE IF NOT EXISTS `chatbot_business_hours` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `chatbot_configuration_id` INT NOT NULL,
    `agency_id` INT DEFAULT NULL,
    `day_of_week` SMALLINT NOT NULL,
    `is_open` TINYINT(1) NOT NULL DEFAULT 1,
    `open_time` TIME DEFAULT NULL,
    `close_time` TIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    INDEX `idx_chatbot_business_hours_configuration` (`chatbot_configuration_id`),
    INDEX `idx_chatbot_business_hours_agency` (`agency_id`),
    INDEX `idx_chatbot_business_hours_day` (`day_of_week`),
    INDEX `idx_chatbot_business_hours_is_open` (`is_open`),
    INDEX `idx_chatbot_business_hours_scope_day` (`chatbot_configuration_id`, `agency_id`, `day_of_week`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE `chatbot_business_hours`
    ADD CONSTRAINT `FK_CHATBOT_BUSINESS_HOURS_CONFIGURATION` FOREIGN KEY (`chatbot_configuration_id`) REFERENCES `chatbot_configuration` (`id`),
    ADD CONSTRAINT `FK_CHATBOT_BUSINESS_HOURS_AGENCY` FOREIGN KEY (`agency_id`) REFERENCES `agency` (`id`);