-- Esquema para checker de PrestaShop (ping + detección)
-- Se ejecuta sobre la base de datos trafikhub_web

USE trafikhub_web;

-- Tabla para guardar resultados de checks
CREATE TABLE IF NOT EXISTS prestashop_status_check (
    id INT AUTO_INCREMENT PRIMARY KEY,
    site_id INT NOT NULL COMMENT 'ID del sitio en prestashop_sites',
    domain VARCHAR(255) NOT NULL COMMENT 'Dominio del sitio',
    http_code INT COMMENT 'Código HTTP de respuesta (200, 301, 404, etc.)',
    response_time_ms INT COMMENT 'Tiempo de respuesta en ms',
    is_prestashop TINYINT(1) DEFAULT 0 COMMENT '1=es PrestaShop, 0=no detectado',
    prestashop_version VARCHAR(50) COMMENT 'Versión de PrestaShop detectada',
    prestashop_indicators TEXT COMMENT 'JSON con indicadores encontrados',
    error_message TEXT COMMENT 'Mensaje de error si hubo problema',
    http_headers TEXT COMMENT 'Headers HTTP importantes (JSON)',
    final_url VARCHAR(500) COMMENT 'URL final después de redirects',
    status ENUM('pending', 'checked', 'error', 'timeout') DEFAULT 'pending',
    check_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha del check',
    INDEX idx_site_id (site_id),
    INDEX idx_domain (domain),
    INDEX idx_http_code (http_code),
    INDEX idx_is_prestashop (is_prestashop),
    INDEX idx_check_date (check_date DESC),
    FOREIGN KEY (site_id) REFERENCES prestashop_sites(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vista de sitios con último check
CREATE OR REPLACE VIEW v_prestashop_sites_status AS
SELECT 
    ps.id,
    ps.domain,
    ps.company,
    ps.vertical,
    ps.city,
    ps.country,
    ps.sales_revenue_usd,
    psc.http_code,
    psc.response_time_ms,
    psc.is_prestashop,
    psc.prestashop_version,
    psc.status as check_status,
    psc.check_date,
    psc.error_message
FROM prestashop_sites ps
LEFT JOIN (
    SELECT 
        site_id,
        http_code,
        response_time_ms,
        is_prestashop,
        prestashop_version,
        status,
        check_date,
        error_message,
        ROW_NUMBER() OVER (PARTITION BY site_id ORDER BY check_date DESC) as rn
    FROM prestashop_status_check
) psc ON ps.id = psc.site_id AND psc.rn = 1
ORDER BY ps.id;

-- Vista de estadísticas de checks
CREATE OR REPLACE VIEW v_prestashop_check_stats AS
SELECT 
    COUNT(*) as total_checks,
    COUNT(CASE WHEN http_code = 200 THEN 1 END) as http_200,
    COUNT(CASE WHEN http_code BETWEEN 301 AND 399 THEN 1 END) as http_redirects,
    COUNT(CASE WHEN http_code BETWEEN 400 AND 499 THEN 1 END) as http_4xx,
    COUNT(CASE WHEN http_code BETWEEN 500 AND 599 THEN 1 END) as http_5xx,
    COUNT(CASE WHEN is_prestashop = 1 THEN 1 END) as confirmed_prestashop,
    COUNT(CASE WHEN status = 'timeout' THEN 1 END) as timeouts,
    COUNT(CASE WHEN status = 'error' THEN 1 END) as errors,
    AVG(response_time_ms) as avg_response_time,
    MAX(check_date) as last_check_date
FROM prestashop_status_check;

-- Vista de sitios que necesitan check
CREATE OR REPLACE VIEW v_prestashop_pending_checks AS
SELECT 
    ps.id,
    ps.domain,
    ps.company,
    COALESCE(last_check.check_date, '1900-01-01') as last_check_date,
    CASE 
        WHEN last_check.check_date IS NULL THEN 1
        WHEN last_check.check_date < DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1
        ELSE 0
    END as needs_check
FROM prestashop_sites ps
LEFT JOIN (
    SELECT site_id, MAX(check_date) as check_date
    FROM prestashop_status_check
    GROUP BY site_id
) last_check ON ps.id = last_check.site_id
ORDER BY needs_check DESC, ps.id
LIMIT 1000;

