numerus/deploy/update_expense_payment_status.sql

-- Deploy numerus:update_expense_payment_status to pg
-- requires: roles
-- requires: schema_numerus
-- requires: expense
-- requires: payment
-- requires: expense_payment
-- requires: expense_tax_amount
-- requires: available_expense_status
-- requires: available_payment_status

begin;

set search_path to numerus, public;

create or replace function update_expense_payment_status(pid integer, eid integer, amount_cents integer) returns void as
$$
	update payment
	set payment_status = case when expense.amount + coalesce(tax.amount, 0) > amount_cents or exists (select 1 from expense_payment as ep where ep.expense_id = expense.expense_id and payment_id <> pid) then 'partial' else 'complete' end
	from expense
	left join ( select expense_id, sum(amount) as amount from expense_tax_amount group by expense_id) as tax using (expense_id)
	where expense.expense_id = eid
	  and payment_id = pid
	;

	update expense
	set expense_status = case
		when paid_amount >= expense.amount + tax_amount then 'paid'
		when paid_amount = 0 then 'pending'
		else 'partial' end
	from (
		select coalesce (sum(payment.amount), 0) as paid_amount
		from expense_payment
		join payment using (payment_id)
		where expense_payment.expense_id = eid
	) as payment,
	(
		select coalesce (sum(amount), 0) as tax_amount
		from expense_tax_amount
		where expense_id = eid
	) as tax
	where expense.expense_id = eid
	;
$$
	language sql
;

revoke execute on function update_expense_payment_status(integer, integer, integer) from public;
grant execute on function update_expense_payment_status(integer, integer, integer) to invoicer;
grant execute on function update_expense_payment_status(integer, integer, integer) to admin;

commit;