aonestar.public

Parameters

Name	Type	Mode
p_data	jsonb	IN
photo	bytea	IN (DEFAULT NULL)
image	bytea	IN (DEFAULT NULL)
image_type	text	IN (DEFAULT NULL)
photo_type	text	IN (DEFAULT NULL)

Definition

declare
    v_msg_text text;
    v_sqlstate text;
    v_detail text;
    v_hint text;
    v_context text;

    v_doc_type int2;
    v_tag text;
    v_create_guest_profile boolean;
    v_passport_no citext;
    v_surname text; 
    v_given_name text; 
    v_sex text;
    v_country_code text;
    v_nation_code text;
    v_date_of_birth date;
    v_date_of_expire date;  
    v_personal_number text;

    v_count_guest int;
    v_country_id int4;
    v_guest_record record;
    v_guest_document_id int4;
    v_new_guest_id int4;
    v_guest_document_record record;
    v_nationality_id int4;
    v_new_guest_document_id int4;
    v_result json;
   
    photo_data bytea;
    image_data bytea;
    file_ext text;
    content_type text;
    s3_file_path text;
    prop_code text;
    v_image_url text;
    v_photo_url text;
   
    v_photo_path text;
    v_image_path text;
    v_guest_id int4;

    v_retry_count integer;
    v_max_retries constant integer := 3;
    v_delay_seconds constant integer := 0;
begin
    -- ข้อมูลทั่วไป
    v_doc_type := (p_data ->> 'doc_type')::int2;
    v_tag := coalesce(p_data ->> 'tag', '');
    v_create_guest_profile := (p_data ->> 'create_guest_profile')::boolean;
    
    v_passport_no := coalesce(p_data -> 'data' ->> 'passport_no', '');
    v_surname := coalesce(p_data -> 'data' ->> 'surname','');
    v_given_name := coalesce(p_data -> 'data' ->> 'given_name', '');
    v_sex := coalesce(p_data -> 'data' ->> 'sex','');
    v_country_code := coalesce(p_data -> 'data' ->> 'country_code','');
    v_nation_code := coalesce(p_data -> 'data' ->> 'nation_code','');
  
   
    v_date_of_birth := case
    		when p_data -> 'data' ->> 'date_of_birth' is null then null
    		else to_date(p_data -> 'data' ->> 'date_of_birth', 'YYYY-MM-DD')
	end;
      
    
    v_date_of_expire := case
    		when p_data -> 'data' ->> 'date_of_expire' is null then null
    		else to_date(p_data -> 'data' ->> 'date_of_expire', 'YYYY-MM-DD')
	end;
   
   
     v_personal_number := coalesce(p_data -> 'data' ->> 'personal_number', '');

    if v_tag = '' then 
        -- Modified by Pisanu 2025-07-11: Use system date instead of Now()
         v_tag := to_char(fn_system_date(), 'YYYY-MM-DD');
        
    end if;

    -- Get country and nationality
    select id into v_country_id
    from country c
    where c.code = v_country_code
    limit 1;

    select n.id into v_nationality_id
    from nationality n 
    where n.iso_code = v_nation_code
    limit 1;

    if v_nation_code = 'D' then 
        select n.id into v_nationality_id
        from nationality n 
        where n.iso_code = 'DEU'
        limit 1;
    end if;

    -- Get property code for S3 path
    prop_code := fn_prop_code();

    -- Upload Photo with retry mechanism
    photo_data := photo;
    if photo_data is not null then
        file_ext := case when photo_type like '%image/png%' then '.png' else '.jpg' end;
        content_type := case when photo_type like '%image/png%' then 'image/png' else 'image/jpeg' end;
        v_photo_path := prop_code || '/guest_document/' || gen_random_uuid()::text || 
		    '_' ||  v_passport_no || '_photo' || file_ext;
		   
	   -- if prop_code is not null and v_passport_no is not null then
         v_photo_url := doc.s3_upload_file(
           file_path => coalesce(v_photo_path, ''),
           file_data => photo_data,
           content_type => coalesce(content_type, '')
         );
	   -- end if;
        
--        v_retry_count := 0;
--        <<photo_upload_retry>>
--        while v_retry_count < v_max_retries loop
--            begin
--                -- Upload photo
--                v_photo_url := doc.s3_upload_file(
--                    file_path => v_photo_path,
--                    file_data => photo_data,
--                    content_type => content_type
--                );
--
--                if v_photo_url is not null and v_photo_url != '' then
--                    -- Wait for file to be available
--                    perform pg_sleep(v_delay_seconds);
--                    exit photo_upload_retry;
--                end if;
--
--                v_retry_count := v_retry_count + 1;
--                if v_retry_count < v_max_retries then
--                    perform pg_sleep(v_delay_seconds);
--                end if;
--            exception when others then
--                v_retry_count := v_retry_count + 1;
--                if v_retry_count >= v_max_retries then
--                    raise exception 'Failed to upload photo after % attempts', v_max_retries;
--                end if;
--                perform pg_sleep(v_delay_seconds);
--            end;
--        end loop;
	        
    end if;

    -- Upload Image with retry mechanism
    image_data := image;
    if image_data is not null then
        file_ext := case when image_type like '%image/png%' then '.png' else '.jpg' end;
        content_type := case when image_type like '%image/png%' then 'image/png' else 'image/jpeg' end;
        v_image_path := prop_code || '/guest_document/' || gen_random_uuid()::text || 
		    '_' || v_passport_no || '_image' || file_ext;
        v_image_url := doc.s3_upload_file(
           file_path => coalesce(v_image_path, ''),
           file_data => photo_data,
           content_type => coalesce(content_type, '')
        );
       
--        v_retry_count := 0;
--        <<image_upload_retry>>
--        while v_retry_count < v_max_retries loop
--            begin
--                -- Upload image
--                v_image_url := doc.s3_upload_file(
--                    file_path => v_image_path,
--                    file_data => image_data,
--                    content_type => content_type
--                );
--
--                if v_image_url is not null and v_image_url != '' then
--                    -- Wait for file to be available
--                    perform pg_sleep(v_delay_seconds);
--                    exit image_upload_retry;
--                end if;
--
--                v_retry_count := v_retry_count + 1;
--                if v_retry_count < v_max_retries then
--                    perform pg_sleep(v_delay_seconds);
--                end if;
--            exception when others then
--                v_retry_count := v_retry_count + 1;
--                if v_retry_count >= v_max_retries then
--                    raise exception 'Failed to upload image after % attempts', v_max_retries;
--                end if;
--                perform pg_sleep(v_delay_seconds);
--            end;
--        end loop;
       
       
    end if;

    -- Handle guest profile
    if v_create_guest_profile then
        select * into v_guest_record
        from guest g 
        where g.doc_number = v_passport_no
        and g.country_id = v_country_id 
        limit 1;

        if not found then
            -- Create new guest
            select * from sp_save_guest(
                i_guest_id => null,
                i_last_name => v_surname,
                i_first_name => v_given_name,
                i_sex => v_sex,
                i_birthdate => v_date_of_birth,
                i_nation_id => v_nationality_id,
                i_country_id => v_country_id,
                i_doc_number => v_passport_no,
                i_doc_type => v_doc_type,
                i_doc_expire => v_date_of_expire,
                i_doc_country => v_country_id
            ) into v_new_guest_id;
        else
            -- Update existing guest
            select * from sp_save_guest(
                i_guest_id => v_guest_record.id,
                i_last_name => v_surname,
                i_first_name => v_given_name,
                i_sex => v_sex,
                i_birthdate => v_date_of_birth,
                i_nation_id => v_nationality_id,
                i_country_id => v_country_id,
                i_doc_number => v_passport_no,
                i_doc_type => v_doc_type,
                i_doc_expire => v_date_of_expire,
                i_doc_country => v_country_id
            ) into v_new_guest_id;
        end if;
    end if;

    -- Handle guest document
    select id into v_guest_document_id 
    from guest_document gd 
    where gd.doc_number = v_passport_no
    and gd.country_id = v_country_id
    and gd.expire_date = v_date_of_expire
    and is_assigned = false
    limit 1;

    if found then
        -- Update existing document
        update guest_document
        set doc_type = v_doc_type,
            doc_number = v_passport_no,
            expire_date = v_date_of_expire,
            country_id = v_country_id,
            doc_data = p_data -> 'data',
            photo_url = v_photo_url,
            image_url = v_image_url,
            photo_path = v_photo_path,
            image_path = v_image_path,
            nation_id = v_nationality_id,
            tag = v_tag
        where id = v_guest_document_id;
    else
        -- Insert new document
        insert into guest_document(
            doc_type,
            doc_number,
            expire_date,
            country_id,
            doc_data,
            photo_url,
            image_url,
            photo_path,
            image_path,
            tag,
            nation_id
        ) values (
            v_doc_type,
            v_passport_no,
            v_date_of_expire,
            v_country_id,
            p_data -> 'data',
            v_photo_url,
            v_image_url,
            v_photo_path,
            v_image_path,
            v_tag,
            v_nationality_id
        )
        returning id into v_new_guest_document_id;
    end if;

    -- Prepare the result
    v_guest_id := coalesce(v_new_guest_document_id, v_guest_document_id);

    select json_build_object(
        'surname', v_surname,
        'given_name', v_given_name,
        'gender', v_sex,
        'date_of_birth', v_date_of_birth,
        'country', v_country_code,
        'nationality', v_nation_code,
        'passport_name', v_passport_no,
        'expire_date', v_date_of_expire,
        'photo_url', v_photo_url,
        'image_url', v_image_url,
        'guest_id' , v_new_guest_id
    ) into v_result;

    return fn_result_success(v_result);

exception
    when others then
        get stacked diagnostics
            v_msg_text = message_text,
            v_sqlstate = returned_sqlstate,
            v_detail = pg_exception_detail,
            v_hint = pg_exception_hint,
            v_context = pg_exception_context;

        return fn_handle_error(
            v_sqlstate,
            v_msg_text,
            v_detail,
            v_hint,
            v_context,
            'sp_save_guest_document_passport_json',
            p_data
        );
end;