-- Deploy camper:marshal_payment to pg
-- requires: roles
-- requires: schema_camper
-- requires: payment
-- requires: payment_customer
-- requires: payment_option
-- requires: payment__acsi_card
-- requires: payment_customer__-acsi_card

begin;

set search_path to camper, public;

create or replace function marshal_payment(pid integer) returns jsonb as
$$
	select to_jsonb(ctx)
	from (
		select company_id
		     , campsite_type_id
		     , arrival_date
		     , departure_date
		     , subtotal_nights
		     , number_adults
		     , subtotal_adults
		     , number_teenagers
		     , subtotal_teenagers
		     , number_children
		     , subtotal_children
		     , number_dogs
		     , subtotal_dogs
		     , subtotal_tourist_tax
		     , total
		     , currency_code
		     , zone_preferences
		     , acsi_card
		     , full_name
		     , address
		     , postal_code
		     , city
		     , country_code
		     , email
		     , phone
		     , lang_tag
		     , (
			select array_agg(to_jsonb(o))
			from (
				select campsite_type_option_id
				     , units
				     , subtotal
				from payment_option
				where payment_option.payment_id = payment.payment_id
			) o
		     ) as options
		from payment
		join payment_customer using (payment_id)
		where payment_id = pid
	) as ctx;
$$
	language sql
;

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

commit;