-- ═══════════════════════════════════════════════════════════════
-- EmailPRO v3.0 - Estrutura do Banco de Dados MySQL
-- ═══════════════════════════════════════════════════════════════
-- 
-- Banco: atnz7966_disparo
-- User: atnz7966_disparo
-- Charset: utf8mb4 (suporta emojis)
--
-- IMPORTANTE: O sistema cria essas tabelas AUTOMATICAMENTE
-- no primeiro acesso. Este arquivo é apenas para referência
-- ou criação manual se necessário.
--
-- ═══════════════════════════════════════════════════════════════

-- Usar o banco de dados
USE atnz7966_disparo;

-- ═══════════════════════════════════════════════════════════════
-- Tabela 1: smtp_senders (Remetentes SMTP)x]
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `smtp_senders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL COMMENT 'Nome do remetente',
  `email` VARCHAR(255) NOT NULL COMMENT 'E-mail do remetente',
  `host` VARCHAR(255) NOT NULL COMMENT 'Servidor SMTP',
  `username` VARCHAR(255) NOT NULL COMMENT 'Username SMTP',
  `password` VARCHAR(255) NOT NULL COMMENT 'Senha SMTP',
  `port` INT DEFAULT 587 COMMENT 'Porta SMTP (587=TLS, 465=SSL)',
  `secure` VARCHAR(10) DEFAULT 'tls' COMMENT 'Tipo de segurança (tls/ssl)',
  `is_active` TINYINT DEFAULT 1 COMMENT 'Ativo (1) ou Inativo (0)',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_active` (`is_active`),
  INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Configurações de remetentes SMTP';

-- ═══════════════════════════════════════════════════════════════
-- Tabela 2: email_templates (Templates de E-mail)
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `email_templates` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL COMMENT 'Nome do template',
  `subject` VARCHAR(500) NOT NULL COMMENT 'Assunto do e-mail',
  `body` LONGTEXT NOT NULL COMMENT 'Corpo HTML do e-mail',
  `created_by_ai` TINYINT DEFAULT 0 COMMENT 'Criado pela IA (1) ou manual (0)',
  `prompt_used` TEXT COMMENT 'Prompt usado na IA (se aplicável)',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_created_by_ai` (`created_by_ai`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Templates de e-mails salvos';

-- ═══════════════════════════════════════════════════════════════
-- Tabela 3: campaigns (Campanhas de Envio)
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `campaigns` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL COMMENT 'Nome da campanha',
  `sender_id` INT COMMENT 'ID do remetente usado',
  `sender_index` VARCHAR(50) NULL COMMENT 'Index do remetente (fallback)',
  `template_id` INT COMMENT 'ID do template usado (opcional)',
  `subject` VARCHAR(500) NOT NULL COMMENT 'Assunto do e-mail',
  `body` LONGTEXT NOT NULL COMMENT 'Corpo HTML do e-mail',
  `total_emails` INT DEFAULT 0 COMMENT 'Total de e-mails na lista',
  `total_recipients` INT DEFAULT 0 COMMENT 'Total de destinatários',
  `sent_count` INT DEFAULT 0 COMMENT 'Quantidade enviada com sucesso',
  `failed_count` INT DEFAULT 0 COMMENT 'Quantidade que falhou',
  `status` VARCHAR(50) DEFAULT 'pending' COMMENT 'pending/processing/completed/failed/scheduled/sending/cancelled',
  `scheduled_at` DATETIME NULL COMMENT 'Data/hora agendada para envio',
  `recipients_data` LONGTEXT NULL COMMENT 'JSON com lista de destinatários',
  `processed_at` DATETIME NULL COMMENT 'Quando foi processada',
  `started_at` TIMESTAMP NULL COMMENT 'Data/hora de início do envio',
  `completed_at` TIMESTAMP NULL COMMENT 'Data/hora de conclusão',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`sender_id`) REFERENCES `smtp_senders`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`template_id`) REFERENCES `email_templates`(`id`) ON DELETE SET NULL,
  INDEX `idx_status` (`status`),
  INDEX `idx_scheduled` (`scheduled_at`),
  INDEX `idx_created` (`created_at`),
  INDEX `idx_sender` (`sender_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Histórico de campanhas de e-mail';

-- ═══════════════════════════════════════════════════════════════
-- Tabela 4: email_logs (Logs Detalhados de Envio)
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `email_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `campaign_id` INT COMMENT 'ID da campanha',
  `recipient_email` VARCHAR(255) NOT NULL COMMENT 'E-mail do destinatário',
  `status` VARCHAR(50) NOT NULL COMMENT 'Status (success/failed)',
  `error_message` TEXT COMMENT 'Mensagem de erro (se falhou)',
  `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Data/hora do envio',
  FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE CASCADE,
  INDEX `idx_campaign` (`campaign_id`),
  INDEX `idx_recipient` (`recipient_email`),
  INDEX `idx_status` (`status`),
  INDEX `idx_sent_at` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Logs detalhados de cada e-mail enviado';

-- ═══════════════════════════════════════════════════════════════
-- Tabela 5: settings (Configurações do Sistema)
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `settings` (
  `setting_key` VARCHAR(100) PRIMARY KEY COMMENT 'Chave da configuração',
  `setting_value` TEXT NOT NULL COMMENT 'Valor da configuração',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Configurações gerais do sistema';

-- ═══════════════════════════════════════════════════════════════
-- Tabela 6: knowledge_base (Base de Conhecimento para IA) 🧠 NOVO!
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `knowledge_base` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `category` VARCHAR(100) NOT NULL COMMENT 'Categoria (empresa/produtos/tom_voz/etc)',
  `title` VARCHAR(255) NOT NULL COMMENT 'Título do conhecimento',
  `content` LONGTEXT NOT NULL COMMENT 'Conteúdo completo do conhecimento',
  `tags` VARCHAR(500) COMMENT 'Tags separadas por vírgula',
  `is_active` TINYINT DEFAULT 1 COMMENT 'Ativo (1) ou Inativo (0)',
  `usage_count` INT DEFAULT 0 COMMENT 'Quantas vezes foi usado pela IA',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_category` (`category`),
  INDEX `idx_active` (`is_active`),
  INDEX `idx_usage` (`usage_count`),
  FULLTEXT KEY `ft_search` (`title`, `content`, `tags`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Base de Conhecimento usada pela IA para gerar e-mails personalizados';

-- ═══════════════════════════════════════════════════════════════
-- Dados Iniciais (Opcional)
-- ═══════════════════════════════════════════════════════════════

-- Inserir configuração padrão do OpenAI (se ainda não existir)
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) 
VALUES ('openai_api_key', 'SUA_CHAVE_OPENAI_AQUI');

INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) 
VALUES ('openai_model', 'gpt-4o-mini');

INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) 
VALUES ('system_name', 'EmailPRO v3.0');

-- ═══════════════════════════════════════════════════════════════
-- Exemplos de Conhecimentos (Opcional - apenas para demonstração)
-- ═══════════════════════════════════════════════════════════════

-- Descomente as linhas abaixo se quiser inserir exemplos:

/*
INSERT INTO `knowledge_base` (`category`, `title`, `content`, `tags`, `is_active`) VALUES
('empresa', 'Sobre nossa empresa', 'Somos uma empresa focada em soluções de e-mail marketing...', 'empresa,sobre,história', 1),
('tom_voz', 'Como nos comunicamos', 'Nossa comunicação é amigável, próxima e profissional...', 'tom,voz,comunicação', 1),
('produtos', 'Sistema de E-mail Marketing', 'Nosso sistema permite envio em massa com facilidade...', 'produto,email,marketing', 1);
*/

-- ═══════════════════════════════════════════════════════════════
-- Verificação das Tabelas Criadas
-- ═══════════════════════════════════════════════════════════════

-- Execute este comando para verificar se as tabelas foram criadas:
-- SHOW TABLES;

-- Para ver a estrutura de uma tabela específica:
-- DESCRIBE knowledge_base;

-- Para ver dados de exemplo:
-- SELECT * FROM knowledge_base LIMIT 5;

-- ═══════════════════════════════════════════════════════════════
-- Informações Adicionais
-- ═══════════════════════════════════════════════════════════════

/*
CHARSET: utf8mb4_unicode_ci
- Suporta emojis (📧 💡 🎉)
- Compatível com todos os idiomas
- Case-insensitive (padrão)

ENGINE: InnoDB
- Suporta transações
- Suporta chaves estrangeiras
- Melhor performance para escritas

FULLTEXT INDEX:
- Apenas na tabela knowledge_base
- Permite busca rápida por palavras-chave
- Use MATCH AGAINST para buscar

FOREIGN KEYS:
- ON DELETE CASCADE: Deleta registros relacionados
- ON DELETE SET NULL: Define NULL se pai for deletado

ÍNDICES:
- idx_* : Índices normais para acelerar buscas
- ft_* : Índices FULLTEXT para busca de texto
*/

-- ═══════════════════════════════════════════════════════════════
-- FIM DO SCRIPT
-- ═══════════════════════════════════════════════════════════════

-- ✅ Tabelas prontas para uso!
-- ✅ Sistema vai criar automaticamente no primeiro acesso
-- ✅ Este arquivo é apenas para referência ou criação manual