numerus/deploy/remove_payment.sql

-- Deploy numerus:remove_payment to pg
-- requires: roles
-- requires: schema_numerus
-- requires: expense_payment
-- requires: payment
-- requires: payment_attachment
-- requires: update_expense_payment_status

begin;

set search_path to numerus, public;

create or replace function remove_payment(payment_slug uuid) returns void as
$$
declare
	pid integer;
	eid integer;
begin
	select payment_id into pid from payment where slug = payment_slug;
	if not found then
		return;
	end if;

	delete from expense_payment where payment_id = pid returning expense_id into eid;
	if eid is not null then
		perform update_expense_payment_status(null, eid, 0);
	end if;

	delete from payment_attachment where payment_id = pid;
	delete from payment where payment_id = pid;
end
$$
	language plpgsql
;

revoke execute on function remove_payment(uuid) from public;
grant execute on function remove_payment(uuid) to invoicer;
grant execute on function remove_payment(uuid) to admin;

commit;