-- Deploy camper:unmarshal_booking to pg
-- requires: roles
-- requires: schema_camper
-- requires: booking
-- requires: booking_option
-- requires: extension_pg_libphonenumber

begin;

set search_path to camper, public;

grant select, insert on table booking to guest;
grant select, insert on table booking_option to guest;

drop policy if exists delete_from_company on booking;
drop policy if exists update_company on booking;
drop policy if exists insert_to_company on booking;
drop policy if exists select_from_company on booking;

alter table booking disable row level security;

create or replace function unmarshal_booking(data jsonb) returns integer as
$$
declare
	bid integer;
begin
	insert into booking
	( company_id
	, campsite_type_id
	, 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
	, holder_name
	, address
	, postal_code
	, city
	, country_code
	, email
	, phone
	, lang_tag
	)
	values((data->>'company_id')::integer
	     , (data->>'campsite_type_id')::integer
	     , daterange((data->>'arrival_date')::date, (data->>'departure_date')::date)
	     , (data->>'subtotal_nights')::integer
	     , (data->>'number_adults')::integer
	     , (data->>'subtotal_adults')::integer
	     , (data->>'number_teenagers')::integer
	     , (data->>'subtotal_teenagers')::integer
	     , (data->>'number_children')::integer
	     , (data->>'subtotal_children')::integer
	     , (data->>'number_dogs')::integer
	     , (data->>'subtotal_dogs')::integer
	     , (data->>'subtotal_tourist_tax')::integer
	     , (data->>'total')::integer
	     , data->>'currency_code'
	     , data->>'zone_preferences'
	     , (data->>'acsi_card')::boolean
	     , data->>'full_name'
	     , data->>'address'
	     , data->>'postal_code'
	     , data->>'city'
	     , data->>'country_code'
	     , data->>'email'
	     , parse_packed_phone_number(data->>'phone', data->>'country_code')
	     , data->>'lang_tag'
	)
	returning booking_id into bid;

	if jsonb_typeof(data->'options') = 'array' then
		insert into booking_option (booking_id, campsite_type_option_id, units, subtotal)
		select bid, campsite_type_option_id, units, subtotal
		from jsonb_to_recordset(data->'options') as x(campsite_type_option_id integer, units integer, subtotal integer)
		;
	end if;

	return bid;
end;
$$
	language plpgsql
;

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

commit;