-- ============================================================
-- ÁQUILA AI CORE - Database Migrations
-- Módulo: AI Core
-- Data: 2026-07-10
-- Descrição: Criação de todas as tabelas necessárias para o
--            módulo ÁQUILA AI CORE
-- ============================================================

-- ============================================================
-- 1. Tabela: ai_conversation_contexts
-- Descrição: Armazena o contexto persistente de cada cliente
--            para o Conversation Engine
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_conversation_contexts` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    `client_id` BIGINT UNSIGNED NOT NULL,
    `phone` VARCHAR(20) NOT NULL,
    `name` VARCHAR(255) DEFAULT NULL,
    `plan_name` VARCHAR(255) DEFAULT NULL,
    `plan_expires_at` DATETIME DEFAULT NULL,
    `application` VARCHAR(100) DEFAULT NULL,
    `platform` VARCHAR(100) DEFAULT NULL,
    `device` VARCHAR(255) DEFAULT NULL,
    `last_intent` VARCHAR(100) DEFAULT NULL,
    `last_attended_at` DATETIME DEFAULT NULL,
    `last_message` TEXT DEFAULT NULL,
    `last_message_at` DATETIME DEFAULT NULL,
    `metadata` JSON DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_conversation_contexts_client` (`client_id`),
    INDEX `idx_conversation_contexts_phone` (`phone`),
    CONSTRAINT `fk_conversation_contexts_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 2. Tabela: ai_intents
-- Descrição: Catálogo de intenções reconhecidas pelo Intent Engine
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_intents` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `keywords` JSON NOT NULL COMMENT 'Array de palavras-chave associadas à intenção',
    `priority` INT UNSIGNED NOT NULL DEFAULT 0,
    `is_active` BOOLEAN NOT NULL DEFAULT TRUE,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_intents_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. Tabela: ai_intent_logs
-- Descrição: Histórico de intenções detectadas por conversa
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_intent_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    `session_id` BIGINT UNSIGNED NOT NULL,
    `intent_id` BIGINT UNSIGNED NOT NULL,
    `confidence` DECIMAL(5,4) NOT NULL DEFAULT 0.0000,
    `raw_message` TEXT DEFAULT NULL,
    `detected_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_intent_logs_session` (`session_id`),
    INDEX `idx_intent_logs_intent` (`intent_id`),
    CONSTRAINT `fk_intent_logs_intent` FOREIGN KEY (`intent_id`) REFERENCES `ai_intents`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. Tabela: ai_whatsapp_sessions
-- Descrição: Persistência das conversas do WhatsApp
--            (Conversation Memory)
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_whatsapp_sessions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    `client_id` BIGINT UNSIGNED DEFAULT NULL,
    `phone` VARCHAR(20) NOT NULL,
    `context` JSON DEFAULT NULL COMMENT 'Contexto atual da conversa em JSON',
    `intent` VARCHAR(100) DEFAULT NULL,
    `status` ENUM('open', 'waiting', 'resolved', 'escalated', 'closed') NOT NULL DEFAULT 'open',
    `operator_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'ID do operador humano (se escalado)',
    `summary` TEXT DEFAULT NULL,
    `started_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_activity_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `closed_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_whatsapp_sessions_phone` (`phone`),
    INDEX `idx_whatsapp_sessions_status` (`status`),
    INDEX `idx_whatsapp_sessions_client` (`client_id`),
    CONSTRAINT `fk_whatsapp_sessions_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. Tabela: ai_whatsapp_messages
-- Descrição: Histórico de mensagens individuais dentro de
--            cada sessão WhatsApp
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_whatsapp_messages` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    `session_id` BIGINT UNSIGNED NOT NULL,
    `direction` ENUM('inbound', 'outbound') NOT NULL,
    `sender` VARCHAR(50) NOT NULL COMMENT 'client, bot, operator',
    `content` TEXT NOT NULL,
    `content_type` ENUM('text', 'image', 'audio', 'video', 'document', 'location') NOT NULL DEFAULT 'text',
    `metadata` JSON DEFAULT NULL,
    `sent_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `delivered_at` DATETIME DEFAULT NULL,
    `read_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_whatsapp_messages_session` (`session_id`),
    CONSTRAINT `fk_whatsapp_messages_session` FOREIGN KEY (`session_id`) REFERENCES `ai_whatsapp_sessions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. Tabela: ai_automation_rules
-- Descrição: Regras automáticas do Automation Engine
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_automation_rules` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `trigger_event` VARCHAR(100) NOT NULL COMMENT 'Evento que dispara a regra (ex: client_expired, payment_received)',
    `conditions` JSON DEFAULT NULL COMMENT 'Condições adicionais em JSON',
    `actions` JSON NOT NULL COMMENT 'Ações a serem executadas em JSON',
    `priority` INT UNSIGNED NOT NULL DEFAULT 0,
    `is_active` BOOLEAN NOT NULL DEFAULT TRUE,
    `cooldown_minutes` INT UNSIGNED DEFAULT NULL COMMENT 'Tempo mínimo entre execuções para o mesmo alvo',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_automation_rules_trigger` (`trigger_event`),
    INDEX `idx_automation_rules_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. Tabela: ai_automation_executions
-- Descrição: Log de execuções das regras de automação
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_automation_executions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    `rule_id` BIGINT UNSIGNED NOT NULL,
    `target_type` VARCHAR(100) NOT NULL COMMENT 'Tipo do alvo (client, reseller, session)',
    `target_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID do alvo',
    `status` ENUM('pending', 'running', 'completed', 'failed') NOT NULL DEFAULT 'pending',
    `result` JSON DEFAULT NULL COMMENT 'Resultado da execução',
    `error_message` TEXT DEFAULT NULL,
    `executed_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_automation_executions_rule` (`rule_id`),
    INDEX `idx_automation_executions_target` (`target_type`, `target_id`),
    CONSTRAINT `fk_automation_executions_rule` FOREIGN KEY (`rule_id`) REFERENCES `ai_automation_rules`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. Tabela: ai_knowledge_base
-- Descrição: Base de conhecimento do Knowledge Engine
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_knowledge_base` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    `category` VARCHAR(100) NOT NULL COMMENT 'Categoria (instalacao, configuracao, senha, dns, atualizacao, plataforma)',
    `title` VARCHAR(255) NOT NULL,
    `question` TEXT NOT NULL COMMENT 'Pergunta ou variações da pergunta',
    `answer` TEXT NOT NULL COMMENT 'Resposta formatada',
    `keywords` JSON NOT NULL COMMENT 'Palavras-chave para busca',
    `platform` VARCHAR(100) DEFAULT NULL COMMENT 'Plataforma específica (roku, samsung, lg, android_tv, null=todas)',
    `priority` INT UNSIGNED NOT NULL DEFAULT 0,
    `is_active` BOOLEAN NOT NULL DEFAULT TRUE,
    `views_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `helpful_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_knowledge_base_category` (`category`),
    INDEX `idx_knowledge_base_platform` (`platform`),
    FULLTEXT INDEX `ft_knowledge_base_search` (`title`, `question`, `answer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 9. Tabela: ai_devices
-- Descrição: Dispositivos dos clientes (Device Manager)
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_devices` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    `client_id` BIGINT UNSIGNED NOT NULL,
    `platform` VARCHAR(100) NOT NULL COMMENT 'android, roku, lg, samsung, android_tv, fire_tv',
    `manufacturer` VARCHAR(255) DEFAULT NULL,
    `model` VARCHAR(255) DEFAULT NULL,
    `app_version` VARCHAR(50) DEFAULT NULL,
    `dns_used` VARCHAR(255) DEFAULT NULL,
    `last_access_at` DATETIME DEFAULT NULL,
    `last_ip` VARCHAR(45) DEFAULT NULL,
    `country` VARCHAR(100) DEFAULT NULL,
    `mac_address` VARCHAR(17) DEFAULT NULL,
    `metadata` JSON DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_devices_client` (`client_id`),
    INDEX `idx_devices_platform` (`platform`),
    INDEX `idx_devices_mac` (`mac_address`),
    CONSTRAINT `fk_devices_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 10. Tabela: ai_tickets
-- Descrição: Tickets de suporte (Human Handoff)
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_tickets` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    `session_id` BIGINT UNSIGNED DEFAULT NULL,
    `client_id` BIGINT UNSIGNED DEFAULT NULL,
    `operator_id` BIGINT UNSIGNED DEFAULT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `status` ENUM('open', 'in_progress', 'waiting_client', 'resolved', 'closed') NOT NULL DEFAULT 'open',
    `priority` ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    `category` VARCHAR(100) DEFAULT NULL,
    `resolution` TEXT DEFAULT NULL,
    `opened_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `resolved_at` DATETIME DEFAULT NULL,
    `closed_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_tickets_session` (`session_id`),
    INDEX `idx_tickets_client` (`client_id`),
    INDEX `idx_tickets_status` (`status`),
    INDEX `idx_tickets_operator` (`operator_id`),
    CONSTRAINT `fk_tickets_session` FOREIGN KEY (`session_id`) REFERENCES `ai_whatsapp_sessions`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_tickets_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 11. Tabela: ai_dashboard_metrics
-- Descrição: Métricas agregadas para o Dashboard AI
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_dashboard_metrics` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    `metric_date` DATE NOT NULL,
    `conversations_total` INT UNSIGNED NOT NULL DEFAULT 0,
    `conversations_resolved` INT UNSIGNED NOT NULL DEFAULT 0,
    `conversations_escalated` INT UNSIGNED NOT NULL DEFAULT 0,
    `auto_renewals` INT UNSIGNED NOT NULL DEFAULT 0,
    `auto_sales` INT UNSIGNED NOT NULL DEFAULT 0,
    `pix_generated` INT UNSIGNED NOT NULL DEFAULT 0,
    `pix_paid` INT UNSIGNED NOT NULL DEFAULT 0,
    `pix_amount_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `tests_created` INT UNSIGNED NOT NULL DEFAULT 0,
    `conversions` INT UNSIGNED NOT NULL DEFAULT 0,
    `tickets_opened` INT UNSIGNED NOT NULL DEFAULT 0,
    `tickets_resolved` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_dashboard_metrics_date` (`metric_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 12. Tabela: ai_whatsapp_providers
-- Descrição: Configuração dos provedores WhatsApp disponíveis
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_whatsapp_providers` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    `name` VARCHAR(100) NOT NULL COMMENT 'Nome do provedor (evolution_api, z_api, baileys, meta_cloud_api)',
    `display_name` VARCHAR(255) NOT NULL,
    `config` JSON NOT NULL COMMENT 'Configurações do provedor (url, token, etc)',
    `is_active` BOOLEAN NOT NULL DEFAULT FALSE,
    `is_default` BOOLEAN NOT NULL DEFAULT FALSE,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_whatsapp_providers_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Inserção de dados iniciais (Seed)
-- ============================================================

-- Intenções padrão
INSERT INTO `ai_intents` (`slug`, `name`, `description`, `keywords`, `priority`) VALUES
('buy', 'Comprar', 'Cliente deseja adquirir um plano ou serviço', '["comprar","quero","adquirir","contratar","assinar","plano","preço","valor","quanto custa"]', 10),
('renew', 'Renovar', 'Cliente deseja renovar seu plano', '["renovar","renovação","venceu","expirou","vencer","pagar","continuar"]', 9),
('test', 'Teste', 'Cliente deseja um teste gratuito', '["teste","testar","experimentar","trial","grátis","gratuito","demonstração"]', 8),
('support', 'Suporte', 'Cliente precisa de suporte técnico', '["suporte","ajuda","problema","erro","não funciona","travando","parou","bug"]', 7),
('payment', 'Pagamento', 'Cliente tem dúvida ou quer realizar pagamento', '["pagamento","pagar","pix","boleto","transferência","comprovante","paguei"]', 6),
('config', 'Configuração', 'Cliente precisa de ajuda com configuração', '["configurar","configuração","instalar","instalação","setup","como faz"]', 5),
('app', 'Aplicativo', 'Cliente tem dúvida sobre o aplicativo', '["aplicativo","app","baixar","download","atualizar","versão"]', 4),
('dns', 'DNS', 'Cliente precisa de ajuda com DNS', '["dns","servidor","url","endereço","portal","link"]', 3),
('error', 'Erro', 'Cliente reporta um erro específico', '["erro","error","falha","crash","tela preta","não abre","não carrega"]', 2),
('mac', 'MAC', 'Cliente precisa de ajuda com MAC address', '["mac","mac address","endereço mac","dispositivo","ativar"]', 1),
('reseller', 'Revendedor', 'Assunto relacionado a revendedores', '["revendedor","revenda","crédito","saldo","comissão","parceiro"]', 1);

-- Provedores WhatsApp padrão (desabilitados por padrão)
INSERT INTO `ai_whatsapp_providers` (`name`, `display_name`, `config`, `is_active`, `is_default`) VALUES
('evolution_api', 'Evolution API', '{"base_url":"","api_key":"","instance":""}', FALSE, FALSE),
('z_api', 'Z-API', '{"base_url":"","token":"","instance_id":""}', FALSE, FALSE),
('baileys', 'Baileys', '{"base_url":"","session_name":""}', FALSE, FALSE),
('meta_cloud_api', 'Meta Cloud API', '{"base_url":"https://graph.facebook.com/v18.0","access_token":"","phone_number_id":"","verify_token":""}', FALSE, FALSE);

-- Regras de automação padrão
INSERT INTO `ai_automation_rules` (`name`, `description`, `trigger_event`, `conditions`, `actions`, `priority`, `is_active`, `cooldown_minutes`) VALUES
('Lembrete de Vencimento', 'Envia mensagem quando o plano do cliente vencer', 'client_expired', NULL, '{"type":"send_message","template":"renewal_reminder","channel":"whatsapp"}', 10, TRUE, 1440),
('Renovação Automática após Pagamento', 'Renova automaticamente o plano quando pagamento é confirmado', 'payment_received', NULL, '{"type":"renew_line","auto_renew":true}', 9, TRUE, NULL),
('Retorno após Teste', 'Agenda retorno para clientes que criaram teste', 'test_created', '{"delay_hours":24}', '{"type":"send_message","template":"test_followup","channel":"whatsapp"}', 8, TRUE, 1440),
('Escalar para Operador', 'Abre ticket quando suporte não resolve', 'support_unresolved', '{"max_attempts":3}', '{"type":"open_ticket","priority":"high"}', 7, TRUE, NULL);