Modelagem e normalização: 1NF–3NF + quando desnormalizar

-- ❌ Violação de 1NF: múltiplos valores em uma célula
CREATE TABLE pedidos_ruim (
    id INT,
    cliente TEXT,
    produtos TEXT,          -- "Camiseta, Calça, Tênis" ← não atômico
    precos TEXT             -- "50.00, 120.00, 200.00" ← não atômico
);

-- ❌ Violação de 1NF: grupos repetidos de colunas
CREATE TABLE pedidos_ruim2 (
    id INT,
    cliente TEXT,
    produto1 TEXT, preco1 DECIMAL,
    produto2 TEXT, preco2 DECIMAL,   -- grupo repetido
    produto3 TEXT, preco3 DECIMAL    -- limite arbitrário de 3 itens
);

-- ✅ 1NF: valores atômicos, sem grupos repetidos
CREATE TABLE pedidos (
    id SERIAL PRIMARY KEY,
    cliente_id INT,
    data TIMESTAMP
);
CREATE TABLE itens_pedido (
    id SERIAL PRIMARY KEY,
    pedido_id INT REFERENCES pedidos(id),
    produto TEXT NOT NULL,
    preco DECIMAL(10,2) NOT NULL,
    quantidade INT NOT NULL DEFAULT 1
);
-- Cada célula tem um valor atômico, sem limite de itens por pedido

-- ❌ Violação de 2NF (chave composta, dependência parcial):
CREATE TABLE item_pedido_ruim (
    pedido_id INT,
    produto_id INT,
    quantidade INT,
    -- Dependência parcial: estes dependem só de produto_id, não de (pedido_id, produto_id)
    produto_nome TEXT,
    produto_preco DECIMAL,
    categoria TEXT,
    PRIMARY KEY (pedido_id, produto_id)
);

-- ✅ 2NF: separar o que depende de produto_id
CREATE TABLE produtos (
    id SERIAL PRIMARY KEY,
    nome TEXT NOT NULL,
    preco_base DECIMAL(10,2) NOT NULL,
    categoria_id INT REFERENCES categorias(id)
);
CREATE TABLE itens_pedido (
    pedido_id INT,
    produto_id INT,
    quantidade INT NOT NULL,
    preco_unitario DECIMAL(10,2) NOT NULL,  -- preço no momento do pedido
    PRIMARY KEY (pedido_id, produto_id)
);

-- ❌ Violação de 3NF (dependência transitiva):
CREATE TABLE funcionarios_ruim (
    id SERIAL PRIMARY KEY,
    nome TEXT,
    dept_id INT,
    dept_nome TEXT,         -- depende de dept_id, não de id diretamente
    dept_localizacao TEXT   -- idem
);

-- ✅ 3NF: dependência transitiva resolvida
CREATE TABLE departamentos (
    id SERIAL PRIMARY KEY,
    nome TEXT NOT NULL,
    localizacao TEXT
);
CREATE TABLE funcionarios (
    id SERIAL PRIMARY KEY,
    nome TEXT NOT NULL,
    dept_id INT REFERENCES departamentos(id)
);

-- Coluna calculada persistida (PostgreSQL GENERATED STORED):
CREATE TABLE itens_pedido (
    id SERIAL PRIMARY KEY,
    quantidade INT NOT NULL,
    preco_unitario DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) GENERATED ALWAYS AS (quantidade * preco_unitario) STORED
    -- subtotal é calculado e armazenado automaticamente
);
SELECT SUM(subtotal) FROM itens_pedido WHERE pedido_id = 42;
-- Sem JOIN ou recálculo — diretamente da coluna

-- Endereço desnormalizado no pedido (dado histórico):
CREATE TABLE pedidos (
    id SERIAL PRIMARY KEY,
    cliente_id INT REFERENCES clientes(id),
    -- Snapshot do endereço no momento do pedido:
    endereco_rua TEXT,
    endereco_cidade TEXT,
    endereco_cep TEXT,
    -- Mesmo que o cliente mude o endereço, o pedido histórico mantém o original
    total DECIMAL(10,2)
);

-- Trigger para manter total atualizado:
CREATE OR REPLACE FUNCTION atualizar_total_pedido()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE pedidos
    SET total = (
        SELECT COALESCE(SUM(quantidade * preco_unitario), 0)
        FROM itens_pedido
        WHERE pedido_id = COALESCE(NEW.pedido_id, OLD.pedido_id)
    )
    WHERE id = COALESCE(NEW.pedido_id, OLD.pedido_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;