-- Esquema para almacenar datos de sitios PrestaShop
-- CSV: All-Live-PrestaShop-Sites.xlsx - Meta Data.csv

USE trafikhub_web;

-- Tabla principal de sitios PrestaShop
CREATE TABLE IF NOT EXISTS prestashop_sites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    domain VARCHAR(255) NOT NULL COMMENT 'Dominio del sitio',
    location_on_site TEXT COMMENT 'Ubicación/es en el sitio',
    tech_spend_usd DECIMAL(15,2) DEFAULT 0 COMMENT 'Gasto tecnológico en USD',
    sales_revenue_usd DECIMAL(15,2) DEFAULT 0 COMMENT 'Ingresos por ventas en USD',
    social_followers INT DEFAULT 0 COMMENT 'Seguidores en redes sociales',
    employees INT DEFAULT 0 COMMENT 'Número de empleados',
    company VARCHAR(255) COMMENT 'Nombre de la empresa',
    vertical VARCHAR(255) COMMENT 'Vertical/Categoría de negocio',
    tranco_rank INT COMMENT 'Ranking Tranco',
    page_rank INT COMMENT 'Google PageRank',
    majestic_rank INT COMMENT 'Ranking Majestic',
    umbrella_rank INT COMMENT 'Ranking Umbrella',
    telephones TEXT COMMENT 'Teléfonos separados por punto y coma',
    emails TEXT COMMENT 'Emails separados por punto y coma',
    twitter VARCHAR(255) COMMENT 'URL Twitter',
    facebook VARCHAR(255) COMMENT 'URL Facebook',
    linkedin VARCHAR(255) COMMENT 'URL LinkedIn',
    google VARCHAR(255) COMMENT 'URL Google',
    pinterest VARCHAR(255) COMMENT 'URL Pinterest',
    github VARCHAR(255) COMMENT 'URL GitHub',
    instagram VARCHAR(255) COMMENT 'URL Instagram',
    vk VARCHAR(255) COMMENT 'URL VK',
    vimeo VARCHAR(255) COMMENT 'URL Vimeo',
    youtube VARCHAR(255) COMMENT 'URL YouTube',
    tiktok VARCHAR(255) COMMENT 'URL TikTok',
    threads VARCHAR(255) COMMENT 'URL Threads',
    x VARCHAR(255) COMMENT 'URL X (Twitter nuevo)',
    people TEXT COMMENT 'Personas contactadas',
    city VARCHAR(255) COMMENT 'Ciudad',
    state VARCHAR(255) COMMENT 'Estado/Provincia',
    zip VARCHAR(50) COMMENT 'Código postal',
    country VARCHAR(10) COMMENT 'Código país (2 letras)',
    first_detected DATE COMMENT 'Primera detección',
    last_found DATE COMMENT 'Última vez encontrado',
    first_indexed DATE COMMENT 'Primera indexación',
    last_indexed DATE COMMENT 'Última indexación',
    exclusion VARCHAR(255) COMMENT 'Exclusión',
    compliance VARCHAR(255) COMMENT 'Cumplimiento (GDPR, etc.)',
    fecha_importacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_domain (domain),
    INDEX idx_company (company),
    INDEX idx_vertical (vertical),
    INDEX idx_country (country),
    INDEX idx_city (city),
    INDEX idx_sales_revenue (sales_revenue_usd DESC),
    INDEX idx_tranco (tranco_rank),
    INDEX idx_first_detected (first_detected),
    INDEX idx_last_found (last_found)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla para normalizar verticales/categorías
CREATE TABLE IF NOT EXISTS prestashop_verticals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(255) NOT NULL UNIQUE,
    descripcion TEXT,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vista consolidada con estadísticas
CREATE OR REPLACE VIEW v_prestashop_estadisticas AS
SELECT 
    COUNT(*) as total_sitios,
    COUNT(DISTINCT country) as total_paises,
    COUNT(DISTINCT city) as total_ciudades,
    COUNT(DISTINCT vertical) as total_verticles,
    SUM(sales_revenue_usd) as total_ingresos,
    AVG(sales_revenue_usd) as promedio_ingresos,
    SUM(tech_spend_usd) as total_tech_spend,
    MAX(sales_revenue_usd) as max_ingresos,
    SUM(CASE WHEN compliance LIKE '%GDPR%' THEN 1 ELSE 0 END) as sitios_gdpr
FROM prestashop_sites;

-- Vista por país
CREATE OR REPLACE VIEW v_prestashop_por_pais AS
SELECT 
    country,
    COUNT(*) as total_sitios,
    COUNT(DISTINCT city) as ciudades,
    SUM(sales_revenue_usd) as total_ingresos,
    AVG(sales_revenue_usd) as promedio_ingresos,
    SUM(employees) as total_empleados
FROM prestashop_sites
GROUP BY country
ORDER BY total_sitios DESC;

-- Vista por vertical/categoría
CREATE OR REPLACE VIEW v_prestashop_por_vertical AS
SELECT 
    vertical,
    COUNT(*) as total_sitios,
    COUNT(DISTINCT country) as paises,
    SUM(sales_revenue_usd) as total_ingresos,
    AVG(sales_revenue_usd) as promedio_ingresos
FROM prestashop_sites
WHERE vertical IS NOT NULL AND vertical != ''
GROUP BY vertical
ORDER BY total_sitios DESC;

-- Vista por ranking
CREATE OR REPLACE VIEW v_prestashop_top_sites AS
SELECT 
    domain,
    company,
    vertical,
    country,
    city,
    sales_revenue_usd,
    employees,
    tranco_rank,
    page_rank,
    first_detected,
    compliance
FROM prestashop_sites
WHERE sales_revenue_usd > 0
ORDER BY sales_revenue_usd DESC
LIMIT 1000;

