-- ============================================
-- FUNNEL BUILDER PRO - Database Schema
-- ============================================
-- 
-- Este arquivo cria todas as tabelas necessárias
-- para o funcionamento do sistema com backend.
--
-- Execute este script no seu MySQL/MariaDB antes
-- de usar a API PHP.
--
-- ============================================

-- Cria o banco de dados (se não existir)
CREATE DATABASE IF NOT EXISTS funnel_builder 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE funnel_builder;

-- ============================================
-- TABELA: funnels
-- Armazena os funis criados
-- ============================================

CREATE TABLE IF NOT EXISTS `funnels` (
    `id` VARCHAR(100) PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `category` VARCHAR(50) NOT NULL,
    `redirect_url` TEXT NOT NULL,
    `facebook_pixel` VARCHAR(50) DEFAULT NULL,
    `quiz` TEXT NOT NULL,
    `scratch` TEXT NOT NULL,
    `footer` TEXT DEFAULT NULL,
    `status` ENUM('active', 'inactive', 'draft') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_status` (`status`),
    INDEX `idx_category` (`category`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABELA: events
-- Registra todos os eventos dos funis
-- ============================================

CREATE TABLE IF NOT EXISTS `events` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `funnel_id` VARCHAR(100) NOT NULL,
    `event_name` VARCHAR(50) NOT NULL,
    `params` TEXT DEFAULT NULL,
    `user_agent` TEXT DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`funnel_id`) REFERENCES `funnels`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_funnel_id` (`funnel_id`),
    INDEX `idx_event_name` (`event_name`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABELA: funnel_stats
-- Estatísticas agregadas por dia
-- ============================================

CREATE TABLE IF NOT EXISTS `funnel_stats` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `funnel_id` VARCHAR(100) NOT NULL,
    `date` DATE NOT NULL,
    `views` INT UNSIGNED DEFAULT 0,
    `leads` INT UNSIGNED DEFAULT 0,
    `conversion_rate` DECIMAL(5,2) DEFAULT 0.00,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`funnel_id`) REFERENCES `funnels`(`id`) ON DELETE CASCADE,
    
    UNIQUE KEY `unique_funnel_date` (`funnel_id`, `date`),
    INDEX `idx_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABELA: settings
-- Configurações globais do sistema
-- ============================================

CREATE TABLE IF NOT EXISTS `settings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) UNIQUE NOT NULL,
    `value` TEXT DEFAULT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- DADOS INICIAIS
-- ============================================

-- Configurações padrão
INSERT INTO `settings` (`key`, `value`) VALUES
('base_domain', 'https://seudominio.com'),
('gtm_id', ''),
('timezone', 'America/Sao_Paulo')
ON DUPLICATE KEY UPDATE `key`=`key`;

-- ============================================
-- VIEWS PARA ANALYTICS
-- ============================================

-- View: Estatísticas gerais de cada funil
CREATE OR REPLACE VIEW `funnel_analytics` AS
SELECT 
    f.id,
    f.name,
    f.category,
    f.status,
    COUNT(DISTINCT e.id) as total_events,
    COUNT(DISTINCT CASE WHEN e.event_name = 'page_view' THEN e.id END) as total_views,
    COUNT(DISTINCT CASE WHEN e.event_name = 'quiz_answer' THEN e.id END) as total_quiz_answers,
    COUNT(DISTINCT CASE WHEN e.event_name = 'scratch_complete' THEN e.id END) as total_scratch_completes,
    COUNT(DISTINCT CASE WHEN e.event_name = 'conversion' THEN e.id END) as total_conversions,
    CASE 
        WHEN COUNT(DISTINCT CASE WHEN e.event_name = 'page_view' THEN e.id END) > 0 
        THEN ROUND((COUNT(DISTINCT CASE WHEN e.event_name = 'conversion' THEN e.id END) / 
                   COUNT(DISTINCT CASE WHEN e.event_name = 'page_view' THEN e.id END)) * 100, 2)
        ELSE 0 
    END as conversion_rate,
    f.created_at,
    f.updated_at
FROM `funnels` f
LEFT JOIN `events` e ON f.id = e.funnel_id
GROUP BY f.id;

-- View: Eventos de hoje
CREATE OR REPLACE VIEW `today_events` AS
SELECT 
    funnel_id,
    event_name,
    COUNT(*) as event_count,
    DATE(created_at) as event_date
FROM `events`
WHERE DATE(created_at) = CURDATE()
GROUP BY funnel_id, event_name, DATE(created_at);

-- ============================================
-- STORED PROCEDURES
-- ============================================

-- Procedure: Atualizar estatísticas diárias
DELIMITER //

CREATE PROCEDURE IF NOT EXISTS `update_daily_stats`()
BEGIN
    -- Atualiza estatísticas de ontem (se ainda não foi processado)
    INSERT INTO `funnel_stats` (funnel_id, date, views, leads, conversion_rate)
    SELECT 
        funnel_id,
        DATE(created_at) as date,
        COUNT(CASE WHEN event_name = 'page_view' THEN 1 END) as views,
        COUNT(CASE WHEN event_name = 'conversion' THEN 1 END) as leads,
        CASE 
            WHEN COUNT(CASE WHEN event_name = 'page_view' THEN 1 END) > 0
            THEN (COUNT(CASE WHEN event_name = 'conversion' THEN 1 END) / 
                  COUNT(CASE WHEN event_name = 'page_view' THEN 1 END)) * 100
            ELSE 0
        END as conversion_rate
    FROM `events`
    WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
    GROUP BY funnel_id, DATE(created_at)
    ON DUPLICATE KEY UPDATE
        views = VALUES(views),
        leads = VALUES(leads),
        conversion_rate = VALUES(conversion_rate);
END //

DELIMITER ;

-- ============================================
-- TRIGGERS
-- ============================================

-- Trigger: Limpar eventos antigos (mantém últimos 90 dias)
DELIMITER //

CREATE TRIGGER IF NOT EXISTS `cleanup_old_events` 
AFTER INSERT ON `events`
FOR EACH ROW
BEGIN
    -- A cada 1000 inserções, limpa eventos antigos
    IF (SELECT COUNT(*) FROM `events`) % 1000 = 0 THEN
        DELETE FROM `events` 
        WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
    END IF;
END //

DELIMITER ;

-- ============================================
-- ÍNDICES ADICIONAIS PARA PERFORMANCE
-- ============================================

-- Índice composto para queries de analytics
CREATE INDEX IF NOT EXISTS `idx_events_funnel_name_date` 
ON `events` (`funnel_id`, `event_name`, `created_at`);

-- Índice para busca por IP (útil para detectar fraudes)
CREATE INDEX IF NOT EXISTS `idx_events_ip` 
ON `events` (`ip_address`);

-- ============================================
-- USUÁRIOS E PERMISSÕES (OPCIONAL)
-- ============================================

-- Tabela de usuários (para sistema multi-usuário)
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) UNIQUE NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `role` ENUM('admin', 'user') DEFAULT 'user',
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_email` (`email`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Relaciona funis com usuários
ALTER TABLE `funnels` 
ADD COLUMN `user_id` INT UNSIGNED DEFAULT NULL AFTER `id`,
ADD FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL;

-- ============================================
-- DADOS DE EXEMPLO (OPCIONAL)
-- ============================================

-- Funil de exemplo
INSERT INTO `funnels` (
    `id`, 
    `name`, 
    `category`, 
    `redirect_url`, 
    `facebook_pixel`,
    `quiz`,
    `scratch`,
    `footer`,
    `status`
) VALUES (
    'funnel_example_001',
    'Exemplo - Cartão de Crédito',
    'cartao-credito',
    'https://seusite.com/oferta',
    '123456789012345',
    '{"title":"Ganhe seu Cartão de Crédito","subtitle":"Responda 1 pergunta","question":"Qual sua renda mensal?","options":["Até R$ 2.000","R$ 2.000 - R$ 5.000","Acima de R$ 5.000"],"bgColor":"#0a0a14","titleColor":"#ffffff","accentColor":"#a855f7","logo":""}',
    '{"title":"Parabéns!","subtitle":"Você foi pré-aprovado","text":"RASPA AQUÍ","percentage":40,"prizeBadge":"Pré-aprovado","prizeTitle":"Cartão aprovado!","prizeDescription":"Limite de até R$ 15.000","ctaButtonText":"SOLICITAR CARTÃO","bgColor":"#d1d5db","ctaButtonColor":"#00ff88","image":""}',
    '{"text":"© 2026 SuaMarca","disclaimer":"Valores ilustrativos"}',
    'draft'
);

-- ============================================
-- CONSULTAS ÚTEIS
-- ============================================

-- Ver funis com suas estatísticas
-- SELECT * FROM funnel_analytics;

-- Ver eventos de hoje
-- SELECT * FROM today_events;

-- Top 10 funis por conversão
-- SELECT id, name, total_views, total_conversions, conversion_rate 
-- FROM funnel_analytics 
-- ORDER BY conversion_rate DESC 
-- LIMIT 10;

-- Eventos por hora (últimas 24h)
-- SELECT 
--     DATE_FORMAT(created_at, '%Y-%m-%d %H:00') as hour,
--     event_name,
--     COUNT(*) as count
-- FROM events
-- WHERE created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
-- GROUP BY hour, event_name
-- ORDER BY hour DESC;

-- ============================================
-- BACKUP E MANUTENÇÃO
-- ============================================

-- Para fazer backup:
-- mysqldump -u usuario -p funnel_builder > backup_funnel_builder.sql

-- Para restaurar backup:
-- mysql -u usuario -p funnel_builder < backup_funnel_builder.sql

-- Para otimizar tabelas:
-- OPTIMIZE TABLE funnels, events, funnel_stats;

-- ============================================
-- NOTAS FINAIS
-- ============================================

-- 1. Ajuste as configurações de acordo com seu servidor
-- 2. Configure cron job para executar update_daily_stats() diariamente
-- 3. Monitore o tamanho da tabela 'events' e ajuste a retenção conforme necessário
-- 4. Configure backup automático diário
-- 5. Considere adicionar índices adicionais baseado nas suas queries mais frequentes

-- Cron job sugerido (adicione ao crontab):
-- 0 1 * * * mysql -u usuario -psenha funnel_builder -e "CALL update_daily_stats();"

-- ============================================
-- FIM DO SCRIPT
-- ============================================