plpgsql.sql

-- View com informações de reservas, cliente e hospedagem
CREATE VIEW vw_reservas_completas AS
SELECT 
    r.reserva_id,
    c.nome AS cliente,
    h.tipo AS tipo_hospedagem,
    e.rua || ', ' || e.numero || ' - ' || e.cidade AS endereco,
    r.data_inicio,
    r.data_fim,
    r.preco_total
FROM tb_reservas r
JOIN tb_clientes c ON r.cliente_id = c.cliente_id
JOIN tb_hospedagens h ON r.hospedagem_id = h.hospedagem_id
JOIN tb_enderecos e ON h.endereco_id = e.endereco_id;

SELECT * FROM vw_reservas_completas;
SELECT * FROM vw_reservas_completas WHERE preco_total > 1650;


-- Função para calcular dias de estadia
CREATE OR REPLACE FUNCTION calcular_dias_estadia(reserva_id_value INT)
RETURNS INT AS $$
DECLARE dias INT;
BEGIN
    SELECT (r.data_fim - r.data_inicio) INTO dias
    FROM tb_reservas r
    WHERE r.reserva_id = reserva_id_value;
    
    RETURN dias;
END;
$$ LANGUAGE plpgsql;

SELECT calcular_dias_estadia(4);
SELECT calcular_dias_estadia(3);


-- Atualiza automaticamente quantidade de hospedagens com base nos cadastros
CREATE OR REPLACE PROCEDURE atualizar_qtd_hospedagens()
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE tb_proprietarios p
    SET qtd_hospedagens = (
        SELECT COUNT(*)
        FROM tb_hospedagens h
        WHERE h.proprietario_id = p.proprietario_id
    );
END;
$$;

CALL atualizar_qtd_hospedagens();


-- Trigger para log de data de atualização
ALTER TABLE tb_hospedagens
ADD COLUMN atualizado_em TIMESTAMP;

CREATE OR REPLACE FUNCTION trg_atualiza_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.atualizado_em = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_atualiza_timestamp
BEFORE UPDATE ON tb_hospedagens
FOR EACH ROW
EXECUTE FUNCTION trg_atualiza_timestamp();