-- ============================================================================
-- FREE RESOURCES - MIGRACIÓN COMPLETA 2025
-- Versión: 2.1.3
-- Fecha: 2025-06-26
-- 
-- Este script incluye TODOS los cambios realizados:
-- - Sistema de notificaciones de descarga
-- - Email logs completo
-- - Tokens universales
-- - Correcciones de BD
-- ============================================================================

-- Limpiar tablas existentes si las hay (CUIDADO: esto borra datos)

-- ============================================================================
-- 1. TABLA PRINCIPAL DE RECURSOS
-- ============================================================================
CREATE TABLE free_resources (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    name VARCHAR(255) NULL, -- Compatibilidad
    description TEXT NOT NULL,
    category VARCHAR(100) NOT NULL DEFAULT 'general',
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NULL,
    file_url VARCHAR(500) NOT NULL,
    file_size BIGINT DEFAULT 0,
    mime_type VARCHAR(100) NULL,
    image_url VARCHAR(500) NULL,
    image_filename VARCHAR(255) NULL,
    downloads INT DEFAULT 0, -- ESTANDARIZADO: siempre "downloads"
    views INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    is_featured TINYINT(1) DEFAULT 0,
    tags TEXT NULL,
    metadata TEXT NULL, -- JSON con información adicional
    uploaded_by INT NULL, -- ID del admin que subió el recurso
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_category (category),
    INDEX idx_active (is_active),
    INDEX idx_featured (is_featured),
    INDEX idx_downloads (downloads),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 2. TABLA DE REGISTROS DE USUARIOS
-- ============================================================================
CREATE TABLE free_resources_registrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED,
    email VARCHAR(255) NOT NULL UNIQUE,
    company VARCHAR(255) NULL,
    website VARCHAR(255) NULL,
    phone VARCHAR(50) NULL,
    newsletter TINYINT(1) DEFAULT 0,
    utm_source VARCHAR(100) NULL,
    utm_medium VARCHAR(100) NULL,
    utm_campaign VARCHAR(100) NULL,
    utm_term VARCHAR(100) NULL,
    utm_content VARCHAR(100) NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    domain VARCHAR(255) NULL, -- Dominio desde donde se registró
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_email (email),
    INDEX idx_company (company),
    INDEX idx_domain (domain),
    INDEX idx_created (created_at),
    INDEX idx_utm_source (utm_source),
    INDEX idx_utm_campaign (utm_campaign)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 3. TABLA DE DESCARGAS (TRACKING)
-- ============================================================================
CREATE TABLE free_resources_downloads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    resource_id INT NOT NULL,
    registration_id INT NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    download_method VARCHAR(50) DEFAULT 'direct',
    utm_source VARCHAR(100) NULL,
    utm_medium VARCHAR(100) NULL,
    utm_campaign VARCHAR(100) NULL,
    domain VARCHAR(255) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (resource_id) REFERENCES free_resources(id) ON DELETE CASCADE,
    FOREIGN KEY (registration_id) REFERENCES free_resources_registrations(id) ON DELETE CASCADE,
    
    INDEX idx_resource (resource_id),
    INDEX idx_registration (registration_id),
    INDEX idx_created (created_at),
    INDEX idx_domain (domain),
    INDEX idx_utm_campaign (utm_campaign)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 5. TABLA DE LOGS DE EMAILS (NUEVA!)
-- ============================================================================
CREATE TABLE email_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email_type VARCHAR(50) NOT NULL, -- 'download_notification', 'welcome', etc.
    to_email VARCHAR(255) NOT NULL,
    subject VARCHAR(500) NOT NULL,
    status VARCHAR(20) NOT NULL, -- 'sent', 'failed', 'pending'
    details TEXT NULL, -- JSON con detalles del email
    domain VARCHAR(255) NULL,
    user_agent TEXT NULL,
    ip_address VARCHAR(45) NULL,
    sent_at DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_email_type (email_type),
    INDEX idx_status (status),
    INDEX idx_sent_at (sent_at),
    INDEX idx_domain (domain),
    INDEX idx_to_email (to_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 6. DATOS DE EJEMPLO PARA TESTING
-- ============================================================================

-- Recursos de ejemplo
INSERT INTO free_resources (title, description, category, filename, file_url, file_size, downloads, is_active, is_featured) VALUES
('Guía Completa de Diseño para Folders', 'Aprende las mejores prácticas para diseñar folders impactantes que conviertan prospectos en clientes.', 'guides', 'guia-diseno-folders-2025.pdf', '/free-resources/uploads/guia-diseno-folders-2025.pdf', 2457600, 847, 1, 1),
('Plantillas de Business Cards Premium', 'Colección de 25 plantillas profesionales editables para tarjetas de presentación modernas.', 'templates', 'plantillas-business-cards.zip', '/free-resources/uploads/plantillas-business-cards.zip', 15728640, 532, 1, 1),
('Calculadora de Costos de Impresión', 'Herramienta Excel para calcular costos exactos de impresión y optimizar tu presupuesto.', 'tools', 'calculadora-costos-impresion.xlsx', '/free-resources/uploads/calculadora-costos-impresion.xlsx', 1048576, 398, 1, 0),
('Checklist de Marketing Directo', 'Lista completa de verificación para campañas de marketing directo exitosas con 47 puntos clave.', 'checklists', 'checklist-marketing-directo.pdf', '/free-resources/uploads/checklist-marketing-directo.pdf', 524288, 721, 1, 1),
('Catálogo de Acabados Premium 2025', 'Todo sobre laminados, barnices UV, hot stamping y otros acabados especiales disponibles.', 'catalogs', 'catalogo-acabados-premium-2025.pdf', '/free-resources/uploads/catalogo-acabados-premium-2025.pdf', 8388608, 156, 1, 0);

-- ============================================================================
-- 7. TRIGGERS PARA AUTOMATIZACIÓN
-- ============================================================================

-- Trigger para actualizar contador de descargas
DELIMITER $$
CREATE TRIGGER update_download_count 
    AFTER INSERT ON free_resources_downloads
    FOR EACH ROW
BEGIN
    UPDATE free_resources 
    SET downloads = downloads + 1, 
        updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.resource_id;
END$$
    END IF;
END$$

DELIMITER ;

-- ============================================================================
-- 8. VISTAS ÚTILES PARA REPORTING
-- ============================================================================

-- Vista de recursos populares
CREATE VIEW popular_resources AS
SELECT 
    r.id,
    r.title,
    r.category,
    r.downloads,
    r.created_at,
    COUNT(d.id) as download_events,
    COUNT(DISTINCT d.registration_id) as unique_downloaders
FROM free_resources r
LEFT JOIN free_resources_downloads d ON r.id = d.resource_id
WHERE r.is_active = 1
GROUP BY r.id, r.title, r.category, r.downloads, r.created_at
ORDER BY r.downloads DESC;

-- Vista de estadísticas de email
CREATE VIEW email_stats AS
SELECT 
    email_type,
    status,
    COUNT(*) as count,
    DATE(sent_at) as date,
    domain
FROM email_logs
GROUP BY email_type, status, DATE(sent_at), domain
ORDER BY date DESC, email_type;

-- Vista de actividad reciente
CREATE VIEW recent_activity AS
SELECT 
    'download' as activity_type,
    CONCAT('Usuario descargó: ', r.title) as description,
    d.created_at,
    d.domain,
    reg.email as user_email
FROM free_resources_downloads d
JOIN free_resources r ON d.resource_id = r.id
JOIN free_resources_registrations reg ON d.registration_id = reg.id
UNION ALL
SELECT 
    'email' as activity_type,
    CONCAT('Email enviado: ', subject) as description,
    sent_at as created_at,
    domain,
    to_email as user_email
FROM email_logs
ORDER BY created_at DESC
LIMIT 50;

-- ============================================================================
-- 9. CONFIGURACIÓN FINAL
-- ============================================================================

-- Asegurar configuración de charset
ALTER DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Comentarios finales en metadatos
INSERT INTO email_logs (email_type, to_email, subject, status, details, domain, sent_at) VALUES
('system', 'admin@system.local', 'Free Resources Database Migration Completed', 'sent', 
 '{"version": "2.1.3", "date": "2025-06-26", "tables_created": 5, "views_created": 3, "triggers_created": 2}',
 'system', NOW());

-- ============================================================================
-- FIN DE LA MIGRACIÓN
-- ============================================================================

-- Registro de finalización exitosa
SELECT 
    'MIGRACIÓN COMPLETADA EXITOSAMENTE' as Status,
    '2.1.3' as Version,
    NOW() as Timestamp,
    DATABASE() as Database_Name;

/*
NOTAS IMPORTANTES PARA IMPLEMENTACIÓN:

1. **Antes de ejecutar:**
   - Hacer backup de la BD existente
   - Verificar que no hay datos importantes en las tablas que se van a borrar
   - Confirmar que el usuario de BD tiene permisos para crear tablas, triggers y vistas

2. **Después de ejecutar:**
   - Verificar que todas las tablas se crearon correctamente
   - Probar que los triggers funcionan insertando datos de prueba
   - Verificar que las vistas devuelven datos correctos

3. **Para producción:**
   - Remover o comentar la sección de "DATOS DE EJEMPLO" si no se desea
   - Ajustar los dominios en los tokens universales según el sitio
   - Configurar correctamente los paths de file_url según la estructura del servidor

4. **Compatibilidad:**
   - Compatible con MySQL 5.7+ y MariaDB 10.2+
   - Utiliza características modernas como columnas generadas y JSON
   - Optimizado para rendimiento con índices apropiados

5. **Sistema de emails:**
   - La tabla email_logs está lista para el sistema de notificaciones
   - Los triggers actualizarán automáticamente contadores
   - Las vistas proporcionan reporting instantáneo
*/