-- =====================================================
-- Tabla: cotizaciones_publicas
-- Almacena las solicitudes del cotizador web público
-- =====================================================

CREATE TABLE IF NOT EXISTS `cotizaciones_publicas` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `fecha_solicitud` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Datos del envío
    `tipo_transporte` VARCHAR(20) NOT NULL COMMENT 'aereo, maritimo, terrestre',
    `origen` VARCHAR(100) NOT NULL,
    `destino` VARCHAR(100) NOT NULL,
    `categoria` VARCHAR(50) NOT NULL,
    `descripcion` TEXT NULL,
    `peso_kg` DECIMAL(10,2) NOT NULL DEFAULT 0,
    
    -- Estimación mostrada al cliente
    `estimacion_min` DECIMAL(10,2) NOT NULL DEFAULT 0,
    `estimacion_max` DECIMAL(10,2) NOT NULL DEFAULT 0,
    
    -- Datos de contacto
    `nombre` VARCHAR(100) NOT NULL,
    `empresa` VARCHAR(100) NULL,
    `email` VARCHAR(100) NOT NULL,
    `telefono` VARCHAR(50) NOT NULL,
    `preferencia_contacto` VARCHAR(20) DEFAULT 'email' COMMENT 'email, whatsapp, llamada',
    `idioma` VARCHAR(5) DEFAULT 'es',
    
    -- Seguimiento
    `estado` VARCHAR(20) NOT NULL DEFAULT 'nueva' COMMENT 'nueva, contactada, cotizada, cerrada, descartada',
    `asignado_a` VARCHAR(100) NULL,
    `fecha_contacto` DATETIME NULL,
    `fecha_cotizacion` DATETIME NULL,
    `notas_internas` TEXT NULL,
    `cotizacion_id` INT(11) NULL COMMENT 'FK a quotation si se convierte en cotización formal',
    
    -- Metadata
    `ip_cliente` VARCHAR(45) NULL,
    `user_agent` VARCHAR(255) NULL,
    `fuente` VARCHAR(50) DEFAULT 'web' COMMENT 'web, widget, landing',
    
    PRIMARY KEY (`id`),
    INDEX `idx_estado` (`estado`),
    INDEX `idx_fecha` (`fecha_solicitud`),
    INDEX `idx_email` (`email`),
    INDEX `idx_asignado` (`asignado_a`)
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Vista para el dashboard de solicitudes pendientes
-- =====================================================

CREATE OR REPLACE VIEW `v_cotizaciones_pendientes` AS
SELECT 
    id,
    fecha_solicitud,
    tipo_transporte,
    CONCAT(origen, ' → ', destino) AS ruta,
    categoria,
    peso_kg,
    CONCAT('$', FORMAT(estimacion_min, 0), ' - $', FORMAT(estimacion_max, 0)) AS estimacion,
    nombre,
    empresa,
    email,
    telefono,
    preferencia_contacto,
    estado,
    asignado_a,
    TIMESTAMPDIFF(HOUR, fecha_solicitud, NOW()) AS horas_sin_respuesta
FROM cotizaciones_publicas
WHERE estado IN ('nueva', 'contactada')
ORDER BY fecha_solicitud DESC;

-- =====================================================
-- Estadísticas por día
-- =====================================================

CREATE OR REPLACE VIEW `v_estadisticas_cotizaciones` AS
SELECT 
    DATE(fecha_solicitud) AS fecha,
    COUNT(*) AS total_solicitudes,
    SUM(CASE WHEN tipo_transporte = 'aereo' THEN 1 ELSE 0 END) AS aereo,
    SUM(CASE WHEN tipo_transporte = 'maritimo' THEN 1 ELSE 0 END) AS maritimo,
    SUM(CASE WHEN tipo_transporte = 'terrestre' THEN 1 ELSE 0 END) AS terrestre,
    SUM(CASE WHEN estado = 'cotizada' THEN 1 ELSE 0 END) AS convertidas,
    AVG(peso_kg) AS peso_promedio
FROM cotizaciones_publicas
GROUP BY DATE(fecha_solicitud)
ORDER BY fecha DESC;
