SQL De 0 a Producción

# Cambiar al usuario postgres del sistema y abrir consola psql
sudo -u postgres psql

-- 1. Crear un usuario (rol) con contraseña
CREATE USER dev_user WITH PASSWORD 'mi_password_segura';

-- 2. Crear una base de datos asignada a ese usuario
CREATE DATABASE mi_primera_db OWNER dev_user;

-- 3. Salir
\q

# 4. Ahora puedes conectarte directamente desde la terminal:
psql -U dev_user -d mi_primera_db -h localhost

-- Tipos comunes: SERIAL (autoincremental), VARCHAR(n), TEXT, BOOLEAN, TIMESTAMP
-- NUMERIC(10,2) es ideal para dinero (nunca uses FLOAT para dinero)

CREATE TABLE productos (
    id          SERIAL PRIMARY KEY,        -- ID único, se genera solo
    nombre      VARCHAR(200) NOT NULL,     -- Obligatorio
    descripcion TEXT,                       -- Opcional, sin límite
    precio      NUMERIC(10,2) NOT NULL,    -- Decimal exacto
    stock       INTEGER DEFAULT 0,          -- Si no se indica, vale 0
    activo      BOOLEAN DEFAULT true,
    created_at  TIMESTAMP DEFAULT NOW()
);

-- INSERT (Crear)
INSERT INTO productos (nombre, precio, stock) VALUES
    ('Teclado mecánico', 89.99, 50),
    ('Ratón gaming', 45.50, 120);

-- SELECT (Leer)
SELECT nombre, precio FROM productos WHERE precio > 50 ORDER BY precio DESC LIMIT 3;

-- UPDATE (Modificar) - NUNCA olvides el WHERE
UPDATE productos SET precio = precio * 1.10 WHERE nombre = 'Teclado mecánico';

-- DELETE (Eliminar) - NUNCA olvides el WHERE
DELETE FROM productos WHERE activo = false;

-- Filtros avanzados
SELECT * FROM productos WHERE precio BETWEEN 40 AND 100;
SELECT * FROM productos WHERE nombre IN ('Ratón gaming', 'Webcam HD');
SELECT * FROM productos WHERE nombre ILIKE '%teclado%'; -- ILIKE ignora mayúsculas

-- Lógica condicional (CASE)
SELECT nombre, stock,
    CASE 
        WHEN stock = 0 THEN 'Agotado'
        WHEN stock < 10 THEN 'Bajo stock'
        ELSE 'Suficiente'
    END AS estado
FROM productos;

-- Agrupación (GROUP BY)
SELECT categoria, COUNT(*) AS total, AVG(precio) AS precio_medio
FROM productos
GROUP BY categoria
HAVING AVG(precio) > 50; -- HAVING filtra grupos, WHERE filtra filas individuales

-- Tabla de pedidos con FK hacia clientes
-- CHECK: El guardián de la lógica. Evita precios o stocks imposibles.
ALTER TABLE productos ADD CONSTRAINT chk_positivo CHECK (precio > 0 AND stock >= 0);

-- Combinación única (un mismo pedido no puede tener el mismo producto en 2 líneas)
ALTER TABLE pedido_items ADD CONSTRAINT uq_item UNIQUE (pedido_id, producto_id);

-- Cascada: Si se borra un usuario, borrar sus pedidos
-- ON DELETE CASCADE: Limpieza automática. Si se borra un usuario, borra sus pedidos.
-- (¡Peligro! Úsalo con cuidado, a veces es mejor prohibir el borrado con RESTRICT)
ALTER TABLE pedidos ADD CONSTRAINT fk_usuario FOREIGN KEY (user_id) 
REFERENCES usuarios(id) ON DELETE CASCADE;

-- Tabla de pedidos con FK hacia clientes
-- Tabla de pedidos con FK (Foreign Key) hacia clientes
CREATE TABLE pedidos (
    id          SERIAL PRIMARY KEY,
    cliente_id  INTEGER NOT NULL REFERENCES clientes(id),
    total       NUMERIC(10,2) NOT NULL
);

-- INNER JOIN: Solo clientes con pedidos (intersección)
-- INNER JOIN: Intersección estricta. Muestra SOLO los clientes que SÍ tienen pedidos.
SELECT c.nombre, p.total
FROM pedidos p
INNER JOIN clientes c ON p.cliente_id = c.id;

-- LEFT JOIN: Todos los clientes, tengan o no pedidos
-- LEFT JOIN: Incluyente. Muestra TODOS los clientes (la tabla de la izquierda),
-- e incluye sus pedidos. Si no tienen pedidos, muestra NULL en esas columnas.
SELECT c.nombre, COUNT(p.id) AS total_pedidos
FROM clientes c
LEFT JOIN pedidos p ON c.id = p.cliente_id
GROUP BY c.nombre;

-- 1. Título + Autor (INNER JOIN)
SELECT l.titulo, a.nombre AS autor
FROM libros l
INNER JOIN autores a ON l.autor_id = a.id;

-- 2. Autores sin libros (LEFT JOIN)
SELECT a.nombre
FROM autores a
LEFT JOIN libros l ON a.id = l.autor_id
WHERE l.id IS NULL;

-- 3. Total de libros por autor
SELECT a.nombre, COUNT(l.id) AS total_libros
FROM autores a
LEFT JOIN libros l ON a.id = l.autor_id
GROUP BY a.nombre
ORDER BY total_libros DESC;

-- Productos con precio superior a la media
SELECT nombre, precio FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos);

-- CTEs (Common Table Expressions) con WITH
-- Hacen el código infinitamente más legible que subconsultas anidadas
WITH resumen_clientes AS (
    SELECT c.id, c.nombre, SUM(p.total) AS gasto_total
    FROM clientes c LEFT JOIN pedidos p ON c.id = p.cliente_id
    GROUP BY c.id, c.nombre
)
SELECT nombre, gasto_total FROM resumen_clientes WHERE gasto_total > 100;

-- Vistas (Views) - Consultas preguardadas en la DB
CREATE VIEW v_clientes_vip AS
SELECT * FROM resumen_clientes WHERE gasto_total > 1000;

BEGIN;  -- Inicia la transacción
    
    -- Restamos dinero a Ana
    UPDATE cuentas SET saldo = saldo - 200 WHERE nombre = 'Ana';
    
    -- Si algo explota aquí, el COMMIT final no se ejecutará
    
    -- Sumamos dinero a Luis
    UPDATE cuentas SET saldo = saldo + 200 WHERE nombre = 'Luis';

COMMIT; -- O usar ROLLBACK si capturamos un error

CREATE TABLE cuentas (
    id      SERIAL PRIMARY KEY,
    titular VARCHAR(100) NOT NULL,
    saldo   NUMERIC(10,2) NOT NULL CHECK (saldo >= 0)  -- Red de seguridad
);

INSERT INTO cuentas (titular, saldo) VALUES ('Ana', 1000.00), ('Luis', 500.00);

-- Transferencia exitosa (Ana -> Luis : 200€)
BEGIN;
    UPDATE cuentas SET saldo = saldo - 200 WHERE id = 1;
    UPDATE cuentas SET saldo = saldo + 200 WHERE id = 2;
COMMIT;

-- Transferencia fallida (Ana -> Luis : 2000€)
BEGIN;
    UPDATE cuentas SET saldo = saldo - 2000 WHERE id = 1;
    -- Falla por el CHECK (saldo >= 0). PostgreSQL aborta automáticamente.
ROLLBACK;  -- Los saldos quedan intactos

-- B-Tree index (por defecto) para búsquedas rápidas
CREATE INDEX idx_clientes_email ON clientes(email);

-- Índice compuesto (útil cuando filtras por 2 columnas a la vez)
CREATE INDEX idx_pedidos_cliente_estado ON pedidos(cliente_id, estado);

-- Ver el plan de ejecución de la DB para saber si usa el índice
EXPLAIN ANALYZE SELECT * FROM clientes WHERE email = 'ana@email.com';

-- Crear usuario para la app web con permisos limitados
CREATE USER app_web WITH PASSWORD 'seguro_123';
GRANT SELECT, INSERT, UPDATE ON productos, pedidos TO app_web;

-- Revocar permisos peligrosos
REVOKE DELETE ON pedidos FROM app_web;

-- Suma acumulada de gastos del cliente en cada pedido, manteniendo las filas de pedidos intactas
SELECT 
    cliente_id, 
    total, 
    SUM(total) OVER (PARTITION BY cliente_id ORDER BY fecha) AS acumulado
FROM pedidos;

-- Ranking del más caro al más barato por categoría
SELECT nombre, categoria, precio,
    RANK() OVER (PARTITION BY categoria ORDER BY precio DESC) AS rank
FROM productos;

-- 1. Función que inserta en el log
CREATE OR REPLACE FUNCTION fn_auditar_productos() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.precio <> OLD.precio THEN
        INSERT INTO log_precios (prod_id, precio_viejo, precio_nuevo)
        VALUES (OLD.id, OLD.precio, NEW.precio);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. El Trigger que llama a la función en cada UPDATE
CREATE TRIGGER trg_auditar_precios
AFTER UPDATE ON productos FOR EACH ROW EXECUTE FUNCTION fn_auditar_productos();

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE usuarios (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY, -- UUID oculta a atacantes cuántos usuarios tienes
    email VARCHAR(150) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE productos (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    precio NUMERIC(10,2) NOT NULL CHECK (precio > 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE pedidos (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    usuario_id UUID REFERENCES usuarios(id),
    estado VARCHAR(20) DEFAULT 'pendiente'
);

CREATE TABLE pedido_items (
    pedido_id UUID REFERENCES pedidos(id) ON DELETE CASCADE,
    producto_id UUID REFERENCES productos(id),
    cantidad INTEGER NOT NULL CHECK (cantidad > 0),
    precio_fijado NUMERIC(10,2) NOT NULL, -- Precio al momento de comprar
    PRIMARY KEY (pedido_id, producto_id)
);

-- 5. Lógica de Negocio (Procedimiento Almacenado)
-- Procesa un pedido validando stock y calculando totales de manera atómica

CREATE OR REPLACE PROCEDURE sp_registrar_compra(
    p_usuario_id UUID,
    p_producto_id UUID,
    p_cantidad INTEGER
)
LANGUAGE plpgsql AS $$
DECLARE
    v_precio NUMERIC(10,2);
    v_pedido_id UUID;
BEGIN
    -- 1. FASE DE VALIDACIÓN: Obtener precio y validar stock
    SELECT precio INTO v_precio FROM productos_pro 
    WHERE id = p_producto_id AND stock >= p_cantidad;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Producto no encontrado o stock insuficiente';
    END IF;

    -- 2. FASE DE ACCIÓN: Descontar stock del inventario
    UPDATE productos_pro SET stock = stock - p_cantidad WHERE id = p_producto_id;

    -- 3. FASE DE REGISTRO: Crear cabecera del pedido
    INSERT INTO pedidos_pro (usuario_id, total, estado)
    VALUES (p_usuario_id, v_precio * p_cantidad, 'pagado')
    RETURNING id INTO v_pedido_id;

    -- 4. FASE DE DETALLE: Insertar los items del pedido
    INSERT INTO pedido_items (pedido_id, producto_id, cantidad, precio_unit)
    VALUES (v_pedido_id, p_producto_id, p_cantidad, v_precio);

    -- 5. FINALIZACIÓN: El COMMIT es implícito si no hubo errores
    RAISE NOTICE 'Venta registrada con éxito. Pedido ID: %', v_pedido_id;
END;
$$;