Digitalización: PacketRoute S.L.

-- Creación del esquema relacional en PostgreSQL para PacketRoute S.L.

CREATE TABLE cliente (
    id_cliente SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    cif_nif VARCHAR(20) UNIQUE NOT NULL,
    direccion VARCHAR(200) NOT NULL,
    telefono VARCHAR(15),
    email VARCHAR(100) UNIQUE,
    activo BOOLEAN DEFAULT TRUE
);

CREATE TABLE empleado (
    id_empleado SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    cargo VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    id_dominio_ad VARCHAR(50) UNIQUE -- Vinculación con Samba AD
);

CREATE TABLE producto (
    id_producto SERIAL PRIMARY KEY,
    codigo_barras VARCHAR(50) UNIQUE NOT NULL,
    descripcion VARCHAR(150) NOT NULL,
    stock_actual INT CHECK (stock_actual >= 0),
    stock_minimo INT DEFAULT 5 CHECK (stock_minimo >= 0)
);

CREATE TABLE pedido (
    id_pedido SERIAL PRIMARY KEY,
    id_cliente INT REFERENCES cliente(id_cliente),
    fecha_pedido TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estado VARCHAR(30) DEFAULT 'Pendiente' CHECK (estado IN ('Pendiente', 'Preparando', 'En Ruta', 'Entregado', 'Devuelto'))
);

CREATE TABLE item_pedido (
    id_item SERIAL PRIMARY KEY,
    id_pedido INT REFERENCES pedido(id_pedido) ON DELETE CASCADE,
    id_producto INT REFERENCES producto(id_producto),
    cantidad INT CHECK (cantidad > 0)
);

CREATE TABLE registro_auditoria (
    id_auditoria SERIAL PRIMARY KEY,
    tabla_afectada VARCHAR(50) NOT NULL,
    operacion VARCHAR(10) NOT NULL,
    usuario_db VARCHAR(50) DEFAULT CURRENT_USER,
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    valores_anteriores JSONB,
    valores_nuevos JSONB
);

-- Trigger para auditoría automática de cambios en la tabla 'cliente'

CREATE OR REPLACE FUNCTION procesar_auditoria_clientes()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO registro_auditoria (tabla_afectada, operacion, valores_anteriores, valores_nuevos)
        VALUES ('cliente', TG_OP, row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb);
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO registro_auditoria (tabla_afectada, operacion, valores_anteriores)
        VALUES ('cliente', TG_OP, row_to_json(OLD)::jsonb);
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO registro_auditoria (tabla_afectada, operacion, valores_nuevos)
        VALUES ('cliente', TG_OP, row_to_json(NEW)::jsonb);
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_auditoria_cliente
AFTER INSERT OR UPDATE OR DELETE ON cliente
FOR EACH ROW EXECUTE FUNCTION procesar_auditoria_clientes();

-- Procedimiento almacenado para gestionar devoluciones de pedidos de forma íntegra

CREATE OR REPLACE PROCEDURE procesar_devolucion_pedido(
    p_id_pedido INT,
    p_motivo_devolucion TEXT
) AS $$
DECLARE
    r_item RECORD;
BEGIN
    -- Validar que el pedido exista y esté Entregado o En Ruta
    IF NOT EXISTS (SELECT 1 FROM pedido WHERE id_pedido = p_id_pedido) THEN
        RAISE EXCEPTION 'El pedido con ID % no existe.', p_id_pedido;
    END IF;

    -- Cambiar estado del pedido a Devuelto
    UPDATE pedido 
    SET estado = 'Devuelto' 
    WHERE id_pedido = p_id_pedido;

    -- Devolver stock de cada ítem del pedido al inventario de productos
    FOR r_item IN 
        SELECT id_producto, cantidad 
        FROM item_pedido 
        WHERE id_pedido = p_id_pedido
    LOOP
        UPDATE producto 
        SET stock_actual = stock_actual + r_item.cantidad 
        WHERE id_producto = r_item.id_producto;
    END LOOP;

    RAISE NOTICE 'Devolución procesada con éxito para el pedido %. Inventario reabastecido.', p_id_pedido;
END;
$$ LANGUAGE plpgsql;

#!/bin/bash
# Script de Backup Nocturno de Base de Datos para PacketRoute S.L.
# Rotación de copias de seguridad de 7 días hacia el almacenamiento NAS

BACKUP_DIR="/mnt/nas/backups/postgresql"
DB_NAME="packetroute_db"
DB_USER="postgres"
DATE=$(date +%Y-%m-%d_%H%M%S)
LOG_FILE="/var/log/db_backup.log"

# Asegurar que el directorio de backups existe
mkdir -p "$BACKUP_DIR"

echo "[$DATE] Iniciando copia de seguridad de $DB_NAME..." >> "$LOG_FILE"

# Generar volcado comprimido de la base de datos
pg_dump -U "$DB_USER" "$DB_NAME" | gzip > "$BACKUP_DIR/db_backup_${DB_NAME}_${DATE}.sql.gz"

if [ $? -eq 0 ]; then
    echo "[$DATE] Copia de seguridad generada con éxito en $BACKUP_DIR" >> "$LOG_FILE"
else
    echo "[$DATE] ERROR al generar la copia de seguridad" >> "$LOG_FILE"
    exit 1
fi

# Eliminar copias de seguridad con más de 7 días (rotación de backup)
find "$BACKUP_DIR" -name "db_backup_${DB_NAME}_*" -mtime +7 -delete

echo "[$DATE] Proceso de backup nocturno y rotación finalizado." >> "$LOG_FILE"

# Playbook de Ansible para mantenimiento de seguridad y actualizaciones de software
# Se ejecuta mensualmente para asegurar el parcheo de todos los servidores

- name: Actualización de seguridad de infraestructura PacketRoute
  hosts: servidores_internos
  become: yes
  tasks:
    - name: Actualizar caché de paquetes APT (Debian/Ubuntu)
      apt:
        update_cache: yes
        cache_valid_time: 3600

    - name: Aplicar actualizaciones de seguridad del sistema operativo
      apt:
        upgrade: safe
        autoremove: yes
        autoclean: yes

    - name: Comprobar si se requiere reiniciar después del parcheo
      stat:
        path: /var/run/reboot-required
      register: reboot_required_file

    - name: Reiniciar el servidor si es necesario
      reboot:
        msg: "Reinicio del sistema requerido por actualizaciones de seguridad"
        connect_timeout: 5
        reboot_timeout: 300
        pre_reboot_delay: 0
      when: reboot_required_file.stat.exists