-- Deploy camper:edit_booking_from_payment to pg
-- requires: roles
-- requires: schema_camper
-- requires: booking
-- requires: booking__payment_fields
-- requires: booking__stay
-- requires: booking_option
-- requires: payment
-- requires: payment__acsi_card
-- requires: payment_option

begin;

set search_path to camper, public;

create or replace function edit_booking_from_payment(booking_slug uuid, payment_slug uuid) returns integer as
$$
declare
	bid integer;
begin
	with p as (
		select company_id
		     , campsite_type_id
		     , daterange(arrival_date, departure_date) as stay
		     , 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
		from payment
		where payment.slug = payment_slug
	)
	update booking
	set company_id = p.company_id
	  , campsite_type_id = p.campsite_type_id
	  , stay = p.stay
	  , subtotal_nights = p.subtotal_nights
	  , number_adults = p.number_adults
	  , subtotal_adults = p.subtotal_adults
	  , number_teenagers = p.number_teenagers
	  , subtotal_teenagers = p.subtotal_teenagers
	  , number_children = p.number_children
	  , subtotal_children = p.subtotal_children
	  , number_dogs = p.number_dogs
	  , subtotal_dogs = p.subtotal_dogs
	  , subtotal_tourist_tax = p.subtotal_tourist_tax
	  , total = p.total
	  , currency_code = p.currency_code
	  , zone_preferences = p.zone_preferences
	  , acsi_card = p.acsi_card
	from p
	where slug = booking_slug
	returning booking_id into bid;

	delete from booking_option
	where booking_id = bid;

	insert into booking_option
	( booking_id
	, campsite_type_option_id
	, units
	, subtotal
	)
	select bid
	     , campsite_type_option_id
	     , units
	     , subtotal
	from payment_option
	join payment using (payment_id)
	where payment.slug = payment_slug
	;

	return bid;
end;
$$
	language plpgsql
;

revoke execute on function edit_booking_from_payment(uuid, uuid) from public;
grant execute on function edit_booking_from_payment(uuid, uuid) to employee;
grant execute on function edit_booking_from_payment(uuid, uuid) to admin;

commit;