-- =====================================================
-- Script de creación de tablas para Free Resources - PRODUCCIÓN
-- Sistema integrado con Magento
-- Versión limpia sin consultas de verificación que pueden fallar
-- =====================================================

-- Eliminar tablas existentes en orden correcto
DROP TABLE IF EXISTS free_resources_downloads;
DROP TABLE IF EXISTS free_resources;
DROP TABLE IF EXISTS free_resources_registrations;
DROP TABLE IF EXISTS free_resources_categories;

-- 1. Tabla de categorías
CREATE TABLE free_resources_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(50) DEFAULT NULL,
    color VARCHAR(7) DEFAULT NULL,
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_category_name (name),
    INDEX idx_category_active (is_active),
    INDEX idx_category_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Categorías de recursos gratuitos';

-- 2. Tabla principal de recursos
CREATE TABLE free_resources (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL COMMENT 'Título visible para el cliente',
    name VARCHAR(255) NOT NULL COMMENT 'Nombre interno del recurso',
    description TEXT COMMENT 'Descripción del recurso',
    category VARCHAR(50) NOT NULL COMMENT 'Categoría del recurso',
    
    -- Información del archivo
    filename VARCHAR(255) NOT NULL COMMENT 'Nombre del archivo en el servidor',
    original_filename VARCHAR(255) NOT NULL COMMENT 'Nombre original del archivo',
    file_url VARCHAR(512) NOT NULL COMMENT 'URL o ruta del archivo',
    file_size BIGINT UNSIGNED DEFAULT 0 COMMENT 'Tamaño del archivo en bytes',
    mime_type VARCHAR(100) DEFAULT NULL COMMENT 'Tipo MIME del archivo',
    
    -- Información de la imagen
    image_url VARCHAR(512) DEFAULT NULL COMMENT 'URL de la imagen de preview',
    image_filename VARCHAR(255) DEFAULT NULL COMMENT 'Nombre del archivo de imagen',
    
    -- Estadísticas y estado
    download_count INT UNSIGNED DEFAULT 0 COMMENT 'Número de descargas',
    views INT UNSIGNED DEFAULT 0 COMMENT 'Número de vistas',
    is_active BOOLEAN DEFAULT TRUE COMMENT 'Si el recurso está activo',
    is_featured BOOLEAN DEFAULT FALSE COMMENT 'Si el recurso es destacado',
    
    -- Metadatos
    tags JSON DEFAULT NULL COMMENT 'Tags en formato JSON',
    metadata JSON DEFAULT NULL COMMENT 'Metadatos adicionales',
    
    -- Auditoría
    uploaded_by VARCHAR(255) DEFAULT NULL COMMENT 'Usuario que subió el recurso',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Índices
    INDEX idx_category (category),
    INDEX idx_active (is_active),
    INDEX idx_featured (is_featured),
    INDEX idx_download_count (download_count),
    INDEX idx_created (created_at),
    INDEX idx_filename (filename),
    
    -- Clave foránea
    CONSTRAINT fk_resource_category 
        FOREIGN KEY (category) REFERENCES free_resources_categories(name) 
        ON UPDATE CASCADE ON DELETE RESTRICT
        
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Recursos gratuitos disponibles';

-- 3. Tabla de registros de usuarios
CREATE TABLE free_resources_registrations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(200) NOT NULL,
    email VARCHAR(255) NOT NULL,
    company VARCHAR(255) DEFAULT NULL,
    website VARCHAR(255) DEFAULT NULL COMMENT 'Sitio web del usuario',
    phone VARCHAR(50) DEFAULT NULL,
    
    -- Información adicional
    ip_address VARCHAR(45) DEFAULT NULL COMMENT 'IP del usuario',
    user_agent TEXT DEFAULT NULL COMMENT 'User agent del navegador',
    referrer VARCHAR(512) DEFAULT NULL COMMENT 'Página de referencia',
    utm_source VARCHAR(100) DEFAULT NULL COMMENT 'Fuente UTM',
    utm_medium VARCHAR(100) DEFAULT NULL COMMENT 'Medio UTM',
    utm_campaign VARCHAR(100) DEFAULT NULL COMMENT 'Campaña UTM',
    
    -- Token de acceso
    access_token VARCHAR(255) DEFAULT NULL COMMENT 'Token para acceder a recursos',
    token_expires_at TIMESTAMP NULL DEFAULT NULL COMMENT 'Expiración del token',
    
    -- Preferencias (campo newsletter_subscribed usado en el sistema actual)
    newsletter_subscribed BOOLEAN DEFAULT FALSE COMMENT 'Suscrito a newsletter',
    marketing_consent BOOLEAN DEFAULT FALSE COMMENT 'Consentimiento para marketing',
    
    -- Auditoría
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de registro',
    last_access TIMESTAMP NULL DEFAULT NULL COMMENT 'Último acceso',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Índices
    UNIQUE KEY uk_email (email),
    INDEX idx_registration_date (registration_date),
    INDEX idx_website (website),
    INDEX idx_company (company),
    INDEX idx_newsletter (newsletter_subscribed),
    INDEX idx_access_token (access_token),
    INDEX idx_last_access (last_access)
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Registros de usuarios para recursos gratuitos';

-- 4. Tabla de descargas (tracking)
CREATE TABLE free_resources_downloads (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    resource_id BIGINT UNSIGNED NOT NULL,
    registration_id BIGINT UNSIGNED NOT NULL,
    
    -- Información de la descarga
    download_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    referrer VARCHAR(512) DEFAULT NULL,
    
    -- Metadatos de descarga
    download_method VARCHAR(20) DEFAULT 'direct',
    file_size_at_download BIGINT UNSIGNED DEFAULT NULL,
    download_duration_ms INT UNSIGNED DEFAULT NULL,
    
    -- Auditoría
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Índices
    INDEX idx_resource_id (resource_id),
    INDEX idx_registration_id (registration_id),
    INDEX idx_download_date (download_date),
    INDEX idx_download_method (download_method),
    INDEX idx_ip_address (ip_address),
    
    -- Claves foráneas
    CONSTRAINT fk_download_resource 
        FOREIGN KEY (resource_id) REFERENCES free_resources(id) 
        ON DELETE CASCADE,
    CONSTRAINT fk_download_registration 
        FOREIGN KEY (registration_id) REFERENCES free_resources_registrations(id) 
        ON DELETE CASCADE
        
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tracking de descargas de recursos';

-- =====================================================
-- Insertar categorías predefinidas
-- =====================================================

INSERT INTO free_resources_categories (name, display_name, description, icon, color, sort_order) VALUES
('templates', 'Design Templates', 'Professional design templates for various marketing materials', 'fa-palette', '#3B82F6', 1),
('guides', 'Business Guides', 'Step-by-step guides and tutorials for business growth', 'fa-book', '#10B981', 2),
('tools', 'Useful Tools', 'Business tools, calculators and utilities', 'fa-tools', '#8B5CF6', 3),
('checklists', 'Checklists', 'Marketing and business checklists to ensure success', 'fa-check-square', '#F59E0B', 4),
('catalogs', 'Product Catalogs', 'Product catalogs and brochures', 'fa-catalog', '#EF4444', 5),
('samples', 'Sample Kits', 'Physical product samples and sample kits', 'fa-box', '#EC4899', 6);

-- =====================================================
-- Datos de ejemplo para verificar funcionamiento
-- =====================================================

INSERT INTO free_resources (title, name, description, category, filename, original_filename, file_url, file_size, uploaded_by) VALUES
('Business Card Template', 'business-card-template', 'Professional business card design template', 'templates', 'business-card-template.pdf', 'Business Card Template.pdf', 'uploads/templates/business-card-template.pdf', 1024000, 'system'),
('Marketing Checklist', 'marketing-checklist', 'Complete marketing strategy checklist', 'checklists', 'marketing-checklist.pdf', 'Marketing Checklist.pdf', 'uploads/checklists/marketing-checklist.pdf', 512000, 'system'),
('Logo Design Guide', 'logo-design-guide', 'Step-by-step guide for creating effective logos', 'guides', 'logo-design-guide.pdf', 'Logo Design Guide.pdf', 'uploads/guides/logo-design-guide.pdf', 2048000, 'system');

-- =====================================================
-- Crear índices adicionales para performance
-- =====================================================

CREATE INDEX idx_resources_active_category_downloads ON free_resources (is_active, category, download_count DESC);
CREATE INDEX idx_registrations_date_website ON free_resources_registrations (registration_date, website);
CREATE INDEX idx_downloads_date_resource ON free_resources_downloads (download_date, resource_id);

-- =====================================================
-- SCRIPT COMPLETADO EXITOSAMENTE - v2.1.0
-- =====================================================

-- Este script ha creado:
-- ✅ 4 tablas principales del sistema
-- ✅ 6 categorías predefinidas  
-- ✅ 3 recursos de ejemplo
-- ✅ Índices optimizados para performance
-- ✅ Relaciones y restricciones de integridad
-- ✅ Compatible con sistema modular v2.1.0

-- El sistema está listo para usar.
-- Base de datos compatible con:
-- - AdminAuth module
-- - ResourceManager module  
-- - RegistrationManager module
-- - ConfigManager module
-- - Dashboard stats (get-dashboard-stats.php)
-- - Multi-domain social networks configuration

-- Campos críticos validados:
-- ✅ free_resources.is_active (usado en get-dashboard-stats.php)
-- ✅ free_resources.download_count (usado en estadísticas)
-- ✅ free_resource_registrations.full_name (usado en RegistrationManager)
-- ✅ free_resource_registrations.created_at (usado para "Esta Semana")