Functions.sql

-- Function to calculate revenue for a given period.
CREATE OR REPLACE FUNCTION library.fn_revenue_by_period (p_date_started DATE, p_date_finished DATE)
RETURNS NUMERIC AS $$
DECLARE 
	total_revenue NUMERIC;
BEGIN
	 SELECT SUM(quantity * price) INTO total_revenue
	 FROM library.tb07_ordered_item AS oi
	JOIN library.tb06_order AS o ON o.id_order = oi.id_order
	WHERE o.order_data BETWEEN p_date_started AND p_date_finished;

	IF total_revenue IS NULL THEN
	RETURN 0.0;
	END IF;	
	
	RETURN total_revenue;
END;
$$ LANGUAGE plpgsql;

SELECT library.fn_revenue_by_period('2025-10-01', '2025-11-30');

-- Function to check books by author
CREATE OR REPLACE FUNCTION library.fn_get_books_by_author (p_author_name TEXT)
RETURNS TEXT AS $$
DECLARE book_count INT;
BEGIN 
	SELECT COUNT(*) INTO book_count
	FROM library.tb02_author AS a
	JOIN library.tb04_book AS b on b.id_author = b.id_author
	WHERE a.author_name = p_author_name; 

	IF p_author_name IS NULL THEN
	RETURN 'Error: invalid values';
	END IF;

	RETURN book_count;
END;
$$ LANGUAGE plpgsql;

SELECT library.fn_get_books_by_author ('Machado de Assis');



--Função de calcular o estoque atual real disponível
CREATE OR REPLACE FUNCTION library.fn_calculate_available_stock (p_book_id INT)
RETURNS INT AS $$
DECLARE total_stock INT;
		stock_in_transit INT;
		available_stock INT;	
BEGIN
	--Obtem o estoque atual do livro
	SELECT quantity INTO total_stock
	FROM library.tb04_book AS b
	WHERE b.id_book = p_book_id;

	--Se o livro não existir, gera uma exceção
	IF p_book_id IS NULL THEN
	RAISE EXCEPTION 'Book with ID % not found.', p_book_id;
	END IF;

	--Calcula a quantidade de livros atualmente indisponíveis 1 = Emprestado e 4 = Reservado
	SELECT COALESCE(SUM(oi.quantity),0) INTO stock_in_transit
	FROM library.tb07_ordered_item as oi
	JOIN library.tb06_order as o on o.id_order = oi.id_order
	WHERE oi.id_book = p_book_id AND o.id_order_status IN (1,4);

	available_stock := total_stock - stock_in_transit;

	RETURN available_stock;

END;
$$ LANGUAGE plpgsql;


SELECT library.fn_calculate_available_stock ('1');