-- CRM Avançado - Score de Leads, Funil, E-mail Marketing, Automações

-- ============================================
-- LEAD SCORING
-- ============================================

CREATE TABLE IF NOT EXISTS lead_scores (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    lead_id BIGINT UNSIGNED NOT NULL,
    
    -- Score
    score_total INT NOT NULL DEFAULT 0,
    score_demografia INT DEFAULT 0,
    score_comportamento INT DEFAULT 0,
    score_engajamento INT DEFAULT 0,
    score_historico INT DEFAULT 0,
    
    -- Classificação
    classificacao ENUM('quente', 'morno', 'frio', 'gelado') DEFAULT 'frio',
    
    -- Metadados
    ultima_atualizacao DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_lead (lead_id),
    INDEX idx_score (score_total),
    INDEX idx_classificacao (classificacao),
    UNIQUE KEY uk_tenant_lead (tenant_id, lead_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Configuração de scoring
CREATE TABLE IF NOT EXISTS lead_scoring_config (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    
    -- Pesos dos fatores
    peso_demografia INT DEFAULT 30,
    peso_comportamento INT DEFAULT 25,
    peso_engajamento INT DEFAULT 25,
    peso_historico INT DEFAULT 20,
    
    -- Limites de classificação
    limite_quente INT DEFAULT 80,
    limite_morno INT DEFAULT 50,
    limite_frio INT DEFAULT 20,
    
    -- Regras de scoring
    regras_json TEXT NULL, -- JSON com regras customizadas
    
    -- Atualização automática
    atualizacao_automatica BOOLEAN DEFAULT TRUE,
    intervalo_atualizacao_horas INT DEFAULT 24,
    
    -- Metadados
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Histórico de scores
CREATE TABLE IF NOT EXISTS lead_score_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    lead_id BIGINT UNSIGNED NOT NULL,
    
    -- Score
    score_total INT NOT NULL,
    classificacao VARCHAR(20) NOT NULL,
    
    -- Motivo da mudança
    motivo VARCHAR(255) NULL,
    
    -- Metadados
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_lead (lead_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- FUNIL DE CONVERSÃO
-- ============================================

CREATE TABLE IF NOT EXISTS funnel_metrics (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    
    -- Período
    data DATE NOT NULL,
    mes INT NOT NULL,
    ano INT NOT NULL,
    
    -- Estágio
    stage_id BIGINT UNSIGNED NOT NULL,
    stage_nome VARCHAR(100) NOT NULL,
    
    -- Métricas
    leads_entrada INT DEFAULT 0,
    leads_saida INT DEFAULT 0,
    leads_conversao INT DEFAULT 0,
    taxa_conversao DECIMAL(5,2) DEFAULT 0,
    tempo_medio_dias DECIMAL(10,2) DEFAULT 0,
    valor_total DECIMAL(18,2) DEFAULT 0,
    
    -- Metadados
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_data (data),
    INDEX idx_mes_ano (mes, ano),
    INDEX idx_stage (stage_id),
    UNIQUE KEY uk_tenant_stage_data (tenant_id, stage_id, data)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Análise de ciclo de vendas
CREATE TABLE IF NOT EXISTS sales_cycle_analysis (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    
    -- Período
    mes INT NOT NULL,
    ano INT NOT NULL,
    
    -- Métricas
    ciclo_medio_dias DECIMAL(10,2) DEFAULT 0,
    taxa_fechamento DECIMAL(5,2) DEFAULT 0,
    ticket_medio DECIMAL(18,2) DEFAULT 0,
    leads_gerados INT DEFAULT 0,
    leads_convertidos INT DEFAULT 0,
    
    -- Metadados
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_mes_ano (mes, ano),
    UNIQUE KEY uk_tenant_mes_ano (tenant_id, mes, ano)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- E-MAIL MARKETING
-- ============================================

CREATE TABLE IF NOT EXISTS email_campaigns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    
    -- Dados da campanha
    nome VARCHAR(255) NOT NULL,
    assunto VARCHAR(255) NOT NULL,
    conteudo_html TEXT NOT NULL,
    conteudo_texto TEXT NULL,
    
    -- Segmentação
    segmento_tipo ENUM('todos', 'leads', 'clientes', 'customizado') DEFAULT 'todos',
    segmento_filtros_json TEXT NULL, -- JSON com filtros customizados
    
    -- Status
    status ENUM('rascunho', 'agendada', 'enviando', 'enviada', 'pausada', 'cancelada') DEFAULT 'rascunho',
    
    -- Agendamento
    data_envio DATETIME NULL,
    
    -- Analytics
    total_destinatarios INT DEFAULT 0,
    total_enviados INT DEFAULT 0,
    total_entregues INT DEFAULT 0,
    total_abertos INT DEFAULT 0,
    total_cliques INT DEFAULT 0,
    total_rejeitados INT DEFAULT 0,
    taxa_abertura DECIMAL(5,2) DEFAULT 0,
    taxa_clique DECIMAL(5,2) DEFAULT 0,
    
    -- A/B Testing
    ab_test_enabled BOOLEAN DEFAULT FALSE,
    ab_variant_a_id BIGINT UNSIGNED NULL,
    ab_variant_b_id BIGINT UNSIGNED NULL,
    ab_winner_id BIGINT UNSIGNED NULL,
    
    -- Metadados
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_status (status),
    INDEX idx_data_envio (data_envio)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Destinatários da campanha
CREATE TABLE IF NOT EXISTS email_campaign_recipients (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    campaign_id BIGINT UNSIGNED NOT NULL,
    
    -- Destinatário
    email VARCHAR(255) NOT NULL,
    nome VARCHAR(255) NULL,
    lead_id BIGINT UNSIGNED NULL,
    cliente_id BIGINT UNSIGNED NULL,
    
    -- Status de envio
    status ENUM('pendente', 'enviado', 'entregue', 'aberto', 'clicado', 'rejeitado', 'erro') DEFAULT 'pendente',
    data_envio DATETIME NULL,
    data_abertura DATETIME NULL,
    data_clique DATETIME NULL,
    ip_abertura VARCHAR(45) NULL,
    user_agent_abertura TEXT NULL,
    
    -- Metadados
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_campaign (campaign_id),
    INDEX idx_email (email),
    INDEX idx_status (status),
    FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Templates de e-mail
CREATE TABLE IF NOT EXISTS email_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    
    -- Dados do template
    nome VARCHAR(255) NOT NULL,
    assunto VARCHAR(255) NOT NULL,
    conteudo_html TEXT NOT NULL,
    conteudo_texto TEXT NULL,
    
    -- Categoria
    categoria VARCHAR(100) NULL, -- boas_vindas, follow_up, abandoned_cart, etc
    
    -- Variáveis disponíveis
    variaveis_json TEXT NULL, -- JSON com lista de variáveis disponíveis
    
    -- Status
    ativo BOOLEAN DEFAULT TRUE,
    
    -- Metadados
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_categoria (categoria),
    INDEX idx_ativo (ativo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- AUTOMAÇÕES DE MARKETING
-- ============================================

CREATE TABLE IF NOT EXISTS marketing_automations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    
    -- Dados da automação
    nome VARCHAR(255) NOT NULL,
    descricao TEXT NULL,
    
    -- Trigger (gatilho)
    trigger_tipo ENUM('lead_criado', 'lead_estagio', 'lead_score', 'cliente_criado', 'pedido_abandonado', 'customizado') NOT NULL,
    trigger_config_json TEXT NULL, -- JSON com configuração do trigger
    
    -- Ações
    acoes_json TEXT NOT NULL, -- JSON com array de ações
    
    -- Status
    status ENUM('ativo', 'inativo', 'pausado') DEFAULT 'inativo',
    
    -- Estatísticas
    total_execucoes INT DEFAULT 0,
    total_sucessos INT DEFAULT 0,
    total_falhas INT DEFAULT 0,
    
    -- Metadados
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_status (status),
    INDEX idx_trigger (trigger_tipo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Log de execuções de automações
CREATE TABLE IF NOT EXISTS automation_executions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    automation_id BIGINT UNSIGNED NOT NULL,
    
    -- Contexto
    trigger_data_json TEXT NULL, -- Dados que dispararam a automação
    lead_id BIGINT UNSIGNED NULL,
    cliente_id BIGINT UNSIGNED NULL,
    
    -- Status
    status ENUM('pendente', 'executando', 'sucesso', 'falha') DEFAULT 'pendente',
    erro TEXT NULL,
    
    -- Metadados
    iniciado_em DATETIME NULL,
    finalizado_em DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_automation (automation_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at),
    FOREIGN KEY (automation_id) REFERENCES marketing_automations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

