SELECT e JOIN na prática: INNER, LEFT, self-join

-- Schema de exemplo (e-commerce simples)
CREATE TABLE clientes (
    id SERIAL PRIMARY KEY,
    nome TEXT NOT NULL,
    email TEXT UNIQUE
);

CREATE TABLE pedidos (
    id SERIAL PRIMARY KEY,
    cliente_id INT REFERENCES clientes(id),
    total DECIMAL(10,2),
    status TEXT,
    criado_em TIMESTAMP DEFAULT NOW()
);

CREATE TABLE itens_pedido (
    id SERIAL PRIMARY KEY,
    pedido_id INT REFERENCES pedidos(id),
    produto TEXT,
    quantidade INT,
    preco DECIMAL(10,2)
);

-- INNER JOIN: pedidos com seus clientes
SELECT p.id, c.nome, p.total, p.status
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id    -- INNER é default
WHERE p.status = 'processando';

-- LEFT JOIN: todos os clientes, com ou sem pedido
SELECT c.nome, COUNT(p.id) AS total_pedidos
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
GROUP BY c.id, c.nome
ORDER BY total_pedidos DESC;

-- Clientes SEM nenhum pedido (anti-join):
SELECT c.nome
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
WHERE p.id IS NULL;   -- NULL = sem correspondência

-- JOIN múltiplo: clientes → pedidos → itens
SELECT c.nome, p.id AS pedido, ip.produto, ip.quantidade
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
JOIN itens_pedido ip ON ip.pedido_id = p.id
WHERE c.id = 42;

-- Tabela de funcionários com gerente (auto-referência)
CREATE TABLE funcionarios (
    id SERIAL PRIMARY KEY,
    nome TEXT,
    gerente_id INT REFERENCES funcionarios(id),   -- pode ser NULL (CEO)
    salario DECIMAL(10,2)
);

-- Self-join: cada funcionário com seu gerente direto
SELECT
    f.nome AS funcionario,
    g.nome AS gerente,
    f.salario
FROM funcionarios f
LEFT JOIN funcionarios g ON f.gerente_id = g.id   -- LEFT para incluir CEO
ORDER BY g.nome NULLS FIRST;

-- Hierarquia completa com WITH RECURSIVE
WITH RECURSIVE hierarquia AS (
    -- Base: CEO (sem gerente)
    SELECT id, nome, gerente_id, 0 AS nivel, nome::TEXT AS caminho
    FROM funcionarios
    WHERE gerente_id IS NULL

    UNION ALL

    -- Recursivo: subordinados diretos
    SELECT f.id, f.nome, f.gerente_id, h.nivel + 1,
           h.caminho || ' > ' || f.nome
    FROM funcionarios f
    JOIN hierarquia h ON f.gerente_id = h.id
)
SELECT
    REPEAT('  ', nivel) || nome AS hierarquia,
    nivel,
    caminho
FROM hierarquia
ORDER BY caminho;

-- Self-join para comparação: funcionários com salário acima da média do departamento
SELECT f.nome, f.salario, media.media_dept
FROM funcionarios f
JOIN (
    SELECT gerente_id, AVG(salario) AS media_dept
    FROM funcionarios
    GROUP BY gerente_id
) media ON f.gerente_id = media.gerente_id
WHERE f.salario > media.media_dept;

-- CTE básico: WITH nomeia um resultado intermediário
WITH pedidos_recentes AS (
    SELECT *
    FROM pedidos
    WHERE criado_em >= NOW() - INTERVAL '30 days'
),
clientes_ativos AS (
    SELECT cliente_id, COUNT(*) AS num_pedidos, SUM(total) AS total_gasto
    FROM pedidos_recentes
    GROUP BY cliente_id
)
SELECT c.nome, ca.num_pedidos, ca.total_gasto
FROM clientes_ativos ca
JOIN clientes c ON ca.cliente_id = c.id
WHERE ca.total_gasto > 500
ORDER BY ca.total_gasto DESC;

-- CTE vs Subquery — equivalentes em performance (PostgreSQL 12+):
-- Subquery (menos legível):
SELECT c.nome, sub.total_gasto
FROM clientes c
JOIN (
    SELECT cliente_id, SUM(total) AS total_gasto
    FROM pedidos
    WHERE criado_em >= NOW() - INTERVAL '30 days'
    GROUP BY cliente_id
    HAVING SUM(total) > 500
) sub ON c.id = sub.cliente_id;

-- CTE recursivo: categorias com subcategorias (N níveis)
WITH RECURSIVE categorias_arvore AS (
    SELECT id, nome, parent_id, 0 AS profundidade
    FROM categorias WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.nome, c.parent_id, ct.profundidade + 1
    FROM categorias c
    JOIN categorias_arvore ct ON c.parent_id = ct.id
    WHERE ct.profundidade < 10   -- proteção contra ciclos
)
SELECT REPEAT('── ', profundidade) || nome AS arvore
FROM categorias_arvore
ORDER BY nome;