-- Deploy camper:flush_payments to pg
-- requires: roles
-- requires: schema_camper
-- requires: payment
-- requires: payment_option
-- requires: payment_redsys_response

begin;

set search_path to camper, public;

create or replace function flush_payments() returns void as
$$
	delete from payment_redsys_response
	where payment_id in (
		select payment_id
		from payment
		where payment_status = 'draft'
		  and updated_at <= current_timestamp - interval '1 day'
	);

	delete from payment_option
	where payment_id in (
		select payment_id
		from payment
		where payment_status = 'draft'
		  and updated_at <= current_timestamp - interval '1 day'
	);

	delete from payment
	where payment_status = 'draft'
	  and updated_at <= current_timestamp - interval '1 day'
	;

	update payment
	set payment_status = 'failed'
	  , updated_at = current_timestamp
	where payment_status = 'pending'
	  and updated_at <= current_timestamp - interval '1 hour'
	;
$$
	language sql
	security definer
	set search_path = camper, pg_temp;
;

revoke execute on function flush_payments() from public;
grant execute on function flush_payments() to guest;
grant execute on function flush_payments() to employee;
grant execute on function flush_payments() to admin;

commit;