-- Deploy camper:check_in_guests to pg
-- requires: roles
-- requires: schema_camper
-- requires: booking
-- requires: booking_guest
-- requires: checked_in_guest
-- requires: extension_pg_libphonenumber

begin;

set search_path to camper, public;

create or replace function check_in_guests(bid integer, guests checked_in_guest[]) returns void as
$$
	insert into booking_guest
	( booking_id
	, id_document_type_id
	, id_document_number
	, id_document_issue_date
	, given_name
	, first_surname
	, second_surname
	, sex_id
	, birthdate
	, country_code
	, phone
	, address
	)
	select bid
	     , id_document_type_id
	     , id_document_number
	     , id_document_issue_date
	     , given_name
	     , first_surname
	     , second_surname
	     , sex_id
	     , birthdate
	     , country_code
	     , case when phone is null or phone = '' then null else parse_packed_phone_number(phone, country_code) end
	     , address
	from unnest(guests) as guest
	on conflict (booking_id, id_document_type_id, id_document_number) do update
	set id_document_type_id = excluded.id_document_type_id
	  , id_document_number = excluded.id_document_number
	  , id_document_issue_date = excluded.id_document_issue_date
	  , given_name = excluded.given_name
	  , first_surname = excluded.first_surname
	  , second_surname = excluded.second_surname
	  , sex_id = excluded.sex_id
	  , birthdate = excluded.birthdate
	  , country_code = excluded.country_code
	  , phone = excluded.phone
	  , address = excluded.address
	  , updated_at = current_timestamp
	;

	delete from booking_guest
	where booking_id = bid
	and updated_at < current_timestamp
	;

	update booking
	set booking_status = 'checked-in'
	where booking_id = bid
	and booking_status in ('created', 'confirmed')
	;
$$
	language sql
;

revoke execute on function check_in_guests(integer, checked_in_guest[]) from public;
grant execute on function check_in_guests(integer, checked_in_guest[]) to employee;
grant execute on function check_in_guests(integer, checked_in_guest[]) to admin;

commit;