database.sql

-- ============================================================
-- ESCÁNER DE RED - Base de Datos MySQL
-- Estructura optimizada con validaciones y reglas de negocio
-- ============================================================

-- Crear base de datos
CREATE DATABASE IF NOT EXISTS escaner_red CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE escaner_red;

-- ============================================================
-- TABLA: Fabricante
-- Almacena información de fabricantes de dispositivos (basado en OUI)
-- ============================================================
CREATE TABLE IF NOT EXISTS fabricante (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codigo_oui VARCHAR(8) NOT NULL UNIQUE COMMENT 'Primeros 3 bytes de MAC (ej: 00-1A-2B)',
    nombre VARCHAR(255) NOT NULL COMMENT 'Nombre del fabricante',
    descripcion TEXT COMMENT 'Descripción adicional del fabricante',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_oui (codigo_oui),
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: Protocolo
-- Catálogo de protocolos de red disponibles
-- ============================================================
CREATE TABLE IF NOT EXISTS protocolo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL UNIQUE COMMENT 'Nombre del protocolo (TCP, UDP, ICMP, etc.)',
    descripcion VARCHAR(255) COMMENT 'Descripción del protocolo',
    puerto_por_defecto INT COMMENT 'Puerto por defecto si aplica',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: Equipo
-- Información completa de dispositivos detectados en la red
-- ============================================================
CREATE TABLE IF NOT EXISTS equipo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hostname VARCHAR(255) COMMENT 'Nombre del equipo en la red',
    direccion_ip VARCHAR(45) NOT NULL COMMENT 'Dirección IPv4 o IPv6',
    mac_address VARCHAR(17) COMMENT 'Dirección MAC (formato: XX-XX-XX-XX-XX-XX)',
    fabricante_id INT COMMENT 'Referencia al fabricante del dispositivo',
    sistema_operativo VARCHAR(100) COMMENT 'Sistema operativo detectado',
    version_so VARCHAR(100) COMMENT 'Versión del sistema operativo',
    tipo_dispositivo VARCHAR(50) COMMENT 'Tipo: PC, Router, Servidor, IoT, etc.',
    estado ENUM('activo', 'inactivo', 'desconocido') DEFAULT 'activo',
    primera_deteccion TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de primera aparición',
    ultima_deteccion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Última vez visto',
    notas TEXT COMMENT 'Notas adicionales del administrador',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Índices para búsquedas rápidas
    INDEX idx_ip (direccion_ip),
    INDEX idx_mac (mac_address),
    INDEX idx_hostname (hostname),
    INDEX idx_fabricante (fabricante_id),
    INDEX idx_estado (estado),
    INDEX idx_ultima_deteccion (ultima_deteccion),
    
    -- Relación con fabricante
    FOREIGN KEY (fabricante_id) REFERENCES fabricante(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: Protocolo_Usado
-- Registro histórico de uso de puertos/protocolos por equipo
-- Incluye regla de 10 minutos para evitar duplicados
-- ============================================================
CREATE TABLE IF NOT EXISTS protocolo_usado (
    id INT AUTO_INCREMENT PRIMARY KEY,
    equipo_id INT NOT NULL COMMENT 'Equipo que usa el puerto',
    protocolo_id INT NOT NULL COMMENT 'Protocolo utilizado',
    numero_puerto INT NOT NULL COMMENT 'Número de puerto',
    nombre_servicio VARCHAR(100) COMMENT 'Nombre del servicio (HTTP, SSH, etc.)',
    estado ENUM('abierto', 'cerrado', 'filtrado') DEFAULT 'abierto',
    fecha_deteccion TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha y hora de detección',
    tiempo_respuesta_ms INT COMMENT 'Tiempo de respuesta en milisegundos',
    banner TEXT COMMENT 'Banner del servicio si está disponible',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Índices para búsquedas y validación de 10 minutos
    INDEX idx_equipo (equipo_id),
    INDEX idx_protocolo (protocolo_id),
    INDEX idx_puerto (numero_puerto),
    INDEX idx_fecha (fecha_deteccion),
    INDEX idx_equipo_puerto_fecha (equipo_id, numero_puerto, fecha_deteccion),
    
    -- Relaciones
    FOREIGN KEY (equipo_id) REFERENCES equipo(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (protocolo_id) REFERENCES protocolo(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- DATOS INICIALES: Protocolos comunes
-- ============================================================
INSERT INTO protocolo (nombre, descripcion, puerto_por_defecto) VALUES
('TCP', 'Transmission Control Protocol - Protocolo de control de transmisión', NULL),
('UDP', 'User Datagram Protocol - Protocolo de datagramas de usuario', NULL),
('ICMP', 'Internet Control Message Protocol - Protocolo de mensajes de control', NULL),
('HTTP', 'Hypertext Transfer Protocol', 80),
('HTTPS', 'HTTP Secure', 443),
('FTP', 'File Transfer Protocol', 21),
('SSH', 'Secure Shell', 22),
('TELNET', 'Telnet Protocol', 23),
('SMTP', 'Simple Mail Transfer Protocol', 25),
('DNS', 'Domain Name System', 53),
('POP3', 'Post Office Protocol v3', 110),
('IMAP', 'Internet Message Access Protocol', 143),
('SMB', 'Server Message Block', 445),
('RDP', 'Remote Desktop Protocol', 3389),
('MYSQL', 'MySQL Database', 3306),
('POSTGRESQL', 'PostgreSQL Database', 5432)
ON DUPLICATE KEY UPDATE descripcion = VALUES(descripcion);

-- ============================================================
-- DATOS INICIALES: Fabricantes comunes (OUI)
-- ============================================================
INSERT INTO fabricante (codigo_oui, nombre, descripcion) VALUES
('00-00-0C', 'Cisco Systems', 'Equipos de red Cisco'),
('00-1A-2B', 'Ayecom Technology', 'Dispositivos de red'),
('00-50-56', 'VMware', 'Máquinas virtuales VMware'),
('08-00-27', 'Oracle VirtualBox', 'Máquinas virtuales VirtualBox'),
('00-0C-29', 'VMware', 'Máquinas virtuales VMware'),
('00-15-5D', 'Microsoft Hyper-V', 'Máquinas virtuales Hyper-V'),
('00-1B-21', 'Intel Corporate', 'Dispositivos Intel'),
('00-1E-68', 'Quanta Computer', 'Equipos de cómputo'),
('00-23-24', 'G-PRO Computer', 'Equipos de cómputo'),
('00-24-D7', 'Intel Corporate', 'Dispositivos Intel'),
('00-26-B9', 'Dell', 'Equipos Dell'),
('00-50-B6', 'Good Way', 'Dispositivos de red'),
('3C-D9-2B', 'Hewlett Packard', 'Equipos HP'),
('AC-DE-48', 'Private (Apple)', 'Dispositivos Apple'),
('DC-A6-32', 'Raspberry Pi Foundation', 'Raspberry Pi'),
('B8-27-EB', 'Raspberry Pi Foundation', 'Raspberry Pi'),
('00-1A-11', 'Google', 'Dispositivos Google'),
('F4-F5-D8', 'Google', 'Dispositivos Google'),
('44-07-0B', 'Google', 'Dispositivos Google'),
('E8-6F-38', 'Amazon Technologies', 'Dispositivos Amazon'),
('A4-08-EA', 'Murata Manufacturing', 'Módulos WiFi'),
('CC-50-E3', 'Amazon Technologies', 'Dispositivos Amazon/Echo'),
('00-14-22', 'Dell', 'Equipos Dell'),
('00-21-9B', 'Dell', 'Equipos Dell'),
('00-1D-09', 'Dell', 'Equipos Dell')
ON DUPLICATE KEY UPDATE nombre = VALUES(nombre);

-- ============================================================
-- VISTAS ÚTILES
-- ============================================================

-- Vista: Resumen de equipos con información completa
CREATE OR REPLACE VIEW vista_equipos_completa AS
SELECT 
    e.id,
    e.hostname,
    e.direccion_ip,
    e.mac_address,
    f.nombre AS fabricante,
    e.sistema_operativo,
    e.version_so,
    e.tipo_dispositivo,
    e.estado,
    e.primera_deteccion,
    e.ultima_deteccion,
    (SELECT COUNT(*) FROM protocolo_usado pu WHERE pu.equipo_id = e.id) AS total_puertos_detectados
FROM equipo e
LEFT JOIN fabricante f ON e.fabricante_id = f.id
ORDER BY e.ultima_deteccion DESC;

-- Vista: Actividad reciente de puertos (últimas 24 horas)
CREATE OR REPLACE VIEW vista_actividad_puertos AS
SELECT 
    e.hostname,
    e.direccion_ip,
    e.mac_address,
    pu.numero_puerto,
    pu.nombre_servicio,
    p.nombre AS protocolo,
    pu.estado,
    pu.fecha_deteccion,
    pu.tiempo_respuesta_ms
FROM protocolo_usado pu
INNER JOIN equipo e ON pu.equipo_id = e.id
INNER JOIN protocolo p ON pu.protocolo_id = p.id
WHERE pu.fecha_deteccion >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY pu.fecha_deteccion DESC;

-- ============================================================
-- FUNCIONES Y PROCEDIMIENTOS
-- ============================================================

-- Función: Verificar si un puerto fue usado en los últimos 10 minutos
DELIMITER //
CREATE FUNCTION puerto_usado_recientemente(
    p_equipo_id INT, 
    p_puerto INT
) RETURNS BOOLEAN
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE existe INT DEFAULT 0;
    
    SELECT COUNT(*) INTO existe
    FROM protocolo_usado
    WHERE equipo_id = p_equipo_id 
      AND numero_puerto = p_puerto
      AND fecha_deteccion >= DATE_SUB(NOW(), INTERVAL 10 MINUTE);
    
    RETURN existe > 0;
END //
DELIMITER ;

-- Procedimiento: Registrar uso de puerto (con validación de 10 minutos)
DELIMITER //
CREATE PROCEDURE registrar_puerto_usado(
    IN p_equipo_id INT,
    IN p_protocolo_id INT,
    IN p_puerto INT,
    IN p_servicio VARCHAR(100),
    IN p_estado VARCHAR(20),
    IN p_tiempo_respuesta INT,
    IN p_banner TEXT,
    OUT p_resultado VARCHAR(50),
    OUT p_mensaje VARCHAR(255)
)
BEGIN
    -- Verificar si el puerto ya fue registrado en los últimos 10 minutos
    IF puerto_usado_recientemente(p_equipo_id, p_puerto) THEN
        SET p_resultado = 'OMITIDO';
        SET p_mensaje = CONCAT('Puerto ', p_puerto, ' ya fue registrado en los últimos 10 minutos');
    ELSE
        INSERT INTO protocolo_usado (
            equipo_id, 
            protocolo_id, 
            numero_puerto, 
            nombre_servicio, 
            estado, 
            tiempo_respuesta_ms, 
            banner
        ) VALUES (
            p_equipo_id, 
            p_protocolo_id, 
            p_puerto, 
            p_servicio, 
            p_estado, 
            p_tiempo_respuesta, 
            p_banner
        );
        
        SET p_resultado = 'REGISTRADO';
        SET p_mensaje = CONCAT('Puerto ', p_puerto, ' registrado correctamente');
    END IF;
END //
DELIMITER ;

-- Procedimiento: Validar MAC e IP no duplicadas para diferentes hostnames
DELIMITER //
CREATE PROCEDURE validar_equipo(
    IN p_hostname VARCHAR(255),
    IN p_direccion_ip VARCHAR(45),
    IN p_mac_address VARCHAR(17),
    OUT p_valido BOOLEAN,
    OUT p_mensaje VARCHAR(500)
)
BEGIN
    DECLARE conflicto_ip_hostname VARCHAR(255) DEFAULT NULL;
    DECLARE conflicto_mac_hostname VARCHAR(255) DEFAULT NULL;
    DECLARE conflicto_ip VARCHAR(45) DEFAULT NULL;
    DECLARE conflicto_mac VARCHAR(17) DEFAULT NULL;
    
    SET p_valido = TRUE;
    SET p_mensaje = 'OK';
    
    -- Verificar si la IP está asignada a otro hostname
    SELECT hostname, direccion_ip INTO conflicto_ip_hostname, conflicto_ip
    FROM equipo
    WHERE direccion_ip = p_direccion_ip 
      AND hostname IS NOT NULL 
      AND hostname != '' 
      AND hostname != p_hostname
    LIMIT 1;
    
    IF conflicto_ip_hostname IS NOT NULL THEN
        SET p_valido = FALSE;
        SET p_mensaje = CONCAT('⚠ CONFLICTO: La IP ', p_direccion_ip, 
                               ' ya está asignada al hostname "', conflicto_ip_hostname, '"');
    END IF;
    
    -- Verificar si la MAC está asignada a otro hostname
    IF p_mac_address IS NOT NULL AND p_mac_address != '' THEN
        SELECT hostname, mac_address INTO conflicto_mac_hostname, conflicto_mac
        FROM equipo
        WHERE mac_address = p_mac_address 
          AND hostname IS NOT NULL 
          AND hostname != '' 
          AND hostname != p_hostname
        LIMIT 1;
        
        IF conflicto_mac_hostname IS NOT NULL THEN
            SET p_valido = FALSE;
            IF p_mensaje = 'OK' THEN
                SET p_mensaje = CONCAT('⚠ CONFLICTO: La MAC ', p_mac_address, 
                                       ' ya está asignada al hostname "', conflicto_mac_hostname, '"');
            ELSE
                SET p_mensaje = CONCAT(p_mensaje, ' | La MAC ', p_mac_address, 
                                       ' ya está asignada al hostname "', conflicto_mac_hostname, '"');
            END IF;
        END IF;
    END IF;
END //
DELIMITER ;

-- Procedimiento: Guardar o actualizar equipo con validación
DELIMITER //
CREATE PROCEDURE guardar_equipo(
    IN p_hostname VARCHAR(255),
    IN p_direccion_ip VARCHAR(45),
    IN p_mac_address VARCHAR(17),
    IN p_sistema_operativo VARCHAR(100),
    IN p_version_so VARCHAR(100),
    IN p_tipo_dispositivo VARCHAR(50),
    OUT p_equipo_id INT,
    OUT p_resultado VARCHAR(50),
    OUT p_mensaje VARCHAR(500)
)
BEGIN
    DECLARE v_fabricante_id INT DEFAULT NULL;
    DECLARE v_oui VARCHAR(8);
    DECLARE v_valido BOOLEAN;
    DECLARE v_msg_validacion VARCHAR(500);
    DECLARE v_equipo_existente INT DEFAULT NULL;
    
    -- Validar que no haya conflictos de IP/MAC con otros hostnames
    CALL validar_equipo(p_hostname, p_direccion_ip, p_mac_address, v_valido, v_msg_validacion);
    
    IF NOT v_valido THEN
        SET p_equipo_id = NULL;
        SET p_resultado = 'ERROR';
        SET p_mensaje = v_msg_validacion;
    ELSE
        -- Obtener fabricante basado en OUI de la MAC
        IF p_mac_address IS NOT NULL AND LENGTH(p_mac_address) >= 8 THEN
            SET v_oui = UPPER(LEFT(p_mac_address, 8));
            SELECT id INTO v_fabricante_id FROM fabricante WHERE codigo_oui = v_oui LIMIT 1;
        END IF;
        
        -- Verificar si el equipo ya existe (por IP)
        SELECT id INTO v_equipo_existente FROM equipo WHERE direccion_ip = p_direccion_ip LIMIT 1;
        
        IF v_equipo_existente IS NOT NULL THEN
            -- Actualizar equipo existente
            UPDATE equipo SET
                hostname = COALESCE(p_hostname, hostname),
                mac_address = COALESCE(p_mac_address, mac_address),
                fabricante_id = COALESCE(v_fabricante_id, fabricante_id),
                sistema_operativo = COALESCE(p_sistema_operativo, sistema_operativo),
                version_so = COALESCE(p_version_so, version_so),
                tipo_dispositivo = COALESCE(p_tipo_dispositivo, tipo_dispositivo),
                estado = 'activo',
                ultima_deteccion = CURRENT_TIMESTAMP
            WHERE id = v_equipo_existente;
            
            SET p_equipo_id = v_equipo_existente;
            SET p_resultado = 'ACTUALIZADO';
            SET p_mensaje = CONCAT('Equipo actualizado: ', p_direccion_ip);
        ELSE
            -- Insertar nuevo equipo
            INSERT INTO equipo (
                hostname, 
                direccion_ip, 
                mac_address, 
                fabricante_id, 
                sistema_operativo, 
                version_so, 
                tipo_dispositivo,
                estado
            ) VALUES (
                p_hostname, 
                p_direccion_ip, 
                p_mac_address, 
                v_fabricante_id, 
                p_sistema_operativo, 
                p_version_so, 
                p_tipo_dispositivo,
                'activo'
            );
            
            SET p_equipo_id = LAST_INSERT_ID();
            SET p_resultado = 'CREADO';
            SET p_mensaje = CONCAT('Nuevo equipo registrado: ', p_direccion_ip);
        END IF;
    END IF;
END //
DELIMITER ;

-- Procedimiento: Limpiar registros antiguos
DELIMITER //
CREATE PROCEDURE limpiar_registros_antiguos(IN dias_antiguedad INT)
BEGIN
    DECLARE equipos_eliminados INT DEFAULT 0;
    DECLARE puertos_eliminados INT DEFAULT 0;
    
    -- Eliminar registros de puertos antiguos
    DELETE FROM protocolo_usado 
    WHERE fecha_deteccion < DATE_SUB(NOW(), INTERVAL dias_antiguedad DAY);
    SET puertos_eliminados = ROW_COUNT();
    
    -- Marcar equipos inactivos
    UPDATE equipo 
    SET estado = 'inactivo' 
    WHERE ultima_deteccion < DATE_SUB(NOW(), INTERVAL dias_antiguedad DAY)
      AND estado = 'activo';
    SET equipos_eliminados = ROW_COUNT();
    
    SELECT 
        equipos_eliminados AS equipos_marcados_inactivos,
        puertos_eliminados AS registros_puertos_eliminados;
END //
DELIMITER ;

-- ============================================================
-- TRIGGERS
-- ============================================================

-- Trigger: Actualizar estado del equipo cuando se detecta actividad
DELIMITER //
CREATE TRIGGER tr_actualizar_estado_equipo
AFTER INSERT ON protocolo_usado
FOR EACH ROW
BEGIN
    UPDATE equipo 
    SET estado = 'activo', 
        ultima_deteccion = CURRENT_TIMESTAMP 
    WHERE id = NEW.equipo_id;
END //
DELIMITER ;

-- ============================================================
-- CONSULTAS DE EJEMPLO
-- ============================================================

-- Ejemplo 1: Ver todos los equipos con su fabricante
-- SELECT * FROM vista_equipos_completa;

-- Ejemplo 2: Ver actividad de puertos en las últimas 24 horas
-- SELECT * FROM vista_actividad_puertos;

-- Ejemplo 3: Buscar conflictos de IP/MAC
-- CALL validar_equipo('MI-PC', '192.168.1.100', '00-1A-2B-3C-4D-5E', @valido, @mensaje);
-- SELECT @valido, @mensaje;

-- Ejemplo 4: Registrar un puerto con validación de 10 minutos
-- CALL registrar_puerto_usado(1, 1, 80, 'HTTP', 'abierto', 50, NULL, @resultado, @mensaje);
-- SELECT @resultado, @mensaje;

-- Ejemplo 5: Guardar un equipo con todas las validaciones
-- CALL guardar_equipo('DESKTOP-001', '192.168.1.50', '00-1A-2B-3C-4D-5E', 'Windows 11', '23H2', 'PC', @id, @resultado, @mensaje);
-- SELECT @id, @resultado, @mensaje;