aonestar.public

Parameters

Name	Type	Mode
p_data	jsonb	IN
p_photo	bytea	IN (DEFAULT NULL)
p_image	bytea	IN (DEFAULT NULL)
p_image_type	text	IN (DEFAULT NULL)
p_photo_type	text	IN (DEFAULT NULL)
p_photo_url	text	IN (DEFAULT NULL)
p_image_url	text	IN (DEFAULT NULL)

Definition

DECLARE
    v_doc_type            smallint;
    v_tag                 text;
    v_create_guest_profile boolean;
    v_doc_number          citext;
    v_normalized_doc_number text;
    v_country_id          integer;
    v_nationality_id      integer;
    v_date_of_birth       date;
    v_date_of_expire      date;
    v_guest_id            integer;
    v_guest_document_id   integer;
    v_doc_id              integer;
    v_existing_guest_id   integer;
    v_guest_js            jsonb;

v_surname             text;
    v_given_name          text;
    v_sex                 text;
    v_country_code        text;
    v_nation_code         text;

v_title               text;
    v_first_name          text;
    v_last_name           text;
    v_lang_config         text := fn_intf_param('IDCARD', 'lang', 'en');

v_file_ext            text;
    v_content_type        text;
    v_photo_url           text := NULLIF(p_photo_url, '');
    v_image_url           text := NULLIF(p_image_url, '');
    v_photo_path          text;
    v_image_path          text;
    v_prop_code           text := fn_s3_prop_prefix();
BEGIN
    v_doc_type := NULLIF(p_data->>'doc_type', '')::smallint;
    v_create_guest_profile := COALESCE((p_data->>'create_guest_profile')::boolean, false);
    v_tag := COALESCE(NULLIF(p_data->>'tag', ''), to_char(fn_system_date(), 'YYYY-MM-DD'));

IF v_doc_type IS NULL OR v_doc_type NOT IN (1, 2) THEN
        PERFORM sp_raise_error('99', 'doc_type must be 1 (passport) or 2 (ID card)');
    END IF;

IF jsonb_typeof(p_data->'data') IS DISTINCT FROM 'object' THEN
        PERFORM sp_raise_error('99', 'data is required');
    END IF;

IF v_doc_type = 1 THEN
        v_doc_number := NULLIF(p_data->'data'->>'passport_no', '')::citext;
    ELSE
        v_doc_number := NULLIF(p_data->'data'->>'card_id', '')::citext;
    END IF;

IF v_doc_number IS NULL THEN
        PERFORM sp_raise_error('99', CASE v_doc_type
            WHEN 1 THEN 'passport_no is required'
            ELSE 'card_id is required'
        END);
    END IF;

v_normalized_doc_number := fn_normalize_guest_document_number(v_doc_number::text);
    IF v_normalized_doc_number IS NULL THEN
        PERFORM sp_raise_error('99', 'document number must contain letters or numbers');
    END IF;

-- Binary input is the legacy scanner flow. URL input is the RPC flow.
    IF p_photo IS NOT NULL THEN
        v_file_ext := CASE WHEN p_photo_type LIKE '%image/png%' THEN '.png' ELSE '.jpg' END;
        v_content_type := CASE WHEN p_photo_type LIKE '%image/png%' THEN 'image/png' ELSE 'image/jpeg' END;
        v_photo_path := v_prop_code || '/guest_document/' || gen_random_uuid()::text
            || '_' || v_doc_number || '_photo' || v_file_ext;
        v_photo_url := doc.s3_upload_file(
            file_path => v_photo_path,
            file_data => p_photo,
            content_type => v_content_type
        );
    ELSIF v_photo_url IS NOT NULL THEN
        v_photo_path := fn_guest_document_path_from_url(v_photo_url);
    END IF;

IF p_image IS NOT NULL THEN
        v_file_ext := CASE WHEN p_image_type LIKE '%image/png%' THEN '.png' ELSE '.jpg' END;
        v_content_type := CASE WHEN p_image_type LIKE '%image/png%' THEN 'image/png' ELSE 'image/jpeg' END;
        v_image_path := v_prop_code || '/guest_document/' || gen_random_uuid()::text
            || '_' || v_doc_number || '_image' || v_file_ext;
        v_image_url := doc.s3_upload_file(
            file_path => v_image_path,
            file_data => p_image,
            content_type => v_content_type
        );
    ELSIF v_image_url IS NOT NULL THEN
        v_image_path := fn_guest_document_path_from_url(v_image_url);
    END IF;

IF v_doc_type = 1 THEN
        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 := NULLIF(p_data->'data'->>'date_of_birth', '')::date;
        v_date_of_expire := NULLIF(p_data->'data'->>'date_of_expire', '')::date;

SELECT c.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 = CASE WHEN v_nation_code = 'D' THEN 'DEU' ELSE v_nation_code END
        LIMIT 1;

ELSE
        v_country_id := (SELECT c.id FROM country c WHERE c.code = 'THA' LIMIT 1);
        v_nationality_id := (SELECT n.id FROM nationality n WHERE n.iso_code = 'THA' LIMIT 1);
        v_sex := COALESCE(p_data->'data'->>'sex', '');
        v_sex := CASE v_sex WHEN '1' THEN 'M' WHEN '2' THEN 'F' ELSE NULL END;
        v_date_of_birth := NULLIF(p_data->'data'->>'date_of_birth', '')::date;
        v_date_of_expire := NULLIF(p_data->'data'->>'date_of_expire', '')::date;
        v_title := IIF(v_lang_config = 'th', p_data->'data'->>'title_th', p_data->'data'->>'title_en');
        v_first_name := IIF(v_lang_config = 'th', p_data->'data'->>'name_th', p_data->'data'->>'name_en');
        v_last_name := IIF(v_lang_config = 'th', p_data->'data'->>'lastname_th', p_data->'data'->>'lastname_en');

END IF;

IF v_country_id IS NOT NULL THEN
        PERFORM pg_advisory_xact_lock(hashtextextended(
            v_doc_type::text || ':'
            || v_normalized_doc_number || ':'
            || v_country_id::text,
            0
        ));
    END IF;

IF v_create_guest_profile THEN
        v_guest_id := sp_save_guest_document(p_data->'data', p_photo);
    END IF;

SELECT gd.id INTO v_guest_document_id
    FROM guest_document gd
    WHERE gd.doc_type = v_doc_type
      AND fn_normalize_guest_document_number(gd.doc_number)
          = v_normalized_doc_number
      AND gd.country_id IS NOT DISTINCT FROM v_country_id
    ORDER BY gd.last_scan_time DESC, gd.id DESC
    LIMIT 1;

IF v_guest_document_id IS NOT NULL THEN
        UPDATE guest_document gd
        SET doc_type = v_doc_type,
            doc_number = v_doc_number,
            expire_date = v_date_of_expire,
            country_id = v_country_id,
            doc_data = p_data->'data',
            photo_url = COALESCE(v_photo_url, gd.photo_url),
            image_url = COALESCE(v_image_url, gd.image_url),
            photo_path = COALESCE(v_photo_path, gd.photo_path),
            image_path = COALESCE(v_image_path, gd.image_path),
            nation_id = CASE WHEN v_doc_type = 2 THEN 1 ELSE v_nationality_id END,
            tag = v_tag,
            guest_id = CASE WHEN v_create_guest_profile THEN v_guest_id ELSE gd.guest_id END,
            pending_assignment = NOT (v_create_guest_profile AND v_guest_id IS NOT NULL),
            last_scan_time = current_timestamp
        WHERE gd.id = v_guest_document_id;
        v_doc_id := v_guest_document_id;
    ELSE
        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,
            guest_id, pending_assignment, last_scan_time
        ) VALUES (
            v_doc_type, v_doc_number, v_date_of_expire, v_country_id, p_data->'data',
            v_photo_url, v_image_url, v_photo_path, v_image_path, v_tag,
            CASE WHEN v_doc_type = 2 THEN 1 ELSE v_nationality_id END,
            CASE WHEN v_create_guest_profile THEN v_guest_id ELSE NULL END,
            NOT (v_create_guest_profile AND v_guest_id IS NOT NULL),
            current_timestamp
        )
        RETURNING id INTO v_doc_id;
    END IF;

IF v_create_guest_profile AND v_guest_id IS NOT NULL AND v_doc_id IS NOT NULL THEN
        UPDATE guest_document
        SET guest_id = v_guest_id,
            pending_assignment = false
        WHERE id = v_doc_id
          AND (guest_id IS DISTINCT FROM v_guest_id OR pending_assignment);

UPDATE guest
        SET doc_id = v_doc_id
        WHERE id = v_guest_id
          AND doc_id IS DISTINCT FROM v_doc_id;
    END IF;

-- Always hand back a guest object so the caller has one form-fill shape.
    -- guest.id is the discriminator: not null = an existing profile, null = the
    -- parsed document only. Top-level guest_id keeps its old meaning and stays
    -- null unless a profile was actually created and linked.
    IF v_create_guest_profile AND v_guest_id IS NOT NULL THEN
        v_guest_js := sp_get_guest_js(v_guest_id)::jsonb;
    ELSE
        SELECT gd.guest_id INTO v_existing_guest_id
        FROM guest_document gd
        WHERE gd.id = v_doc_id;

v_existing_guest_id := COALESCE(
            v_existing_guest_id,
            fn_find_guest_by_document(v_doc_type, v_doc_number::text, v_country_id)
        );

IF v_existing_guest_id IS NOT NULL THEN
            v_guest_js := sp_get_guest_js(v_existing_guest_id)::jsonb;
        ELSE
            v_guest_js := fn_guest_document_guest_js(
                i_doc_data => p_data->'data',
                i_doc_type => v_doc_type,
                i_doc_id => v_doc_id,
                i_photo_path => v_photo_path,
                i_photo_url => v_photo_url,
                i_image_path => v_image_path,
                i_image_url => v_image_url
            );
        END IF;
    END IF;

IF v_doc_type = 1 THEN
        RETURN jsonb_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_doc_number,
            'expire_date', v_date_of_expire,
            'photo_url', v_photo_url,
            'image_url', v_image_url,
            'doc_id', v_doc_id,
            'guest_id', v_guest_id,
            'guest', v_guest_js
        );
    END IF;

RETURN (
        SELECT jsonb_build_object(
            'doc_type', gd.doc_type,
            'title', v_title,
            'first_name', v_first_name,
            'last_name', v_last_name,
            'gender', v_sex,
            'address', fn_format_address(p_data->'data'->'address'),
            'card_number', v_doc_number,
            'date_of_birth', v_date_of_birth,
            'date_of_issue', (gd.doc_data->>'date_of_issue')::date,
            'date_of_expire', v_date_of_expire,
            'photo_url', v_photo_url,
            'image_url', v_image_url,
            'doc_id', v_doc_id,
            'guest_id', v_guest_id,
            'guest', v_guest_js
        )
        FROM guest_document gd
        WHERE gd.id = v_doc_id
    );
END;

sp_save_guest_document_data

Parameters

Definition