---

CREATE OR REPLACE FUNCTION integration.get_client_id_api(curjson text)
RETURNS text
LANGUAGE plpgsql
AS $function$
declare
    v_buffer text;
    v_param jsonb;
begin
    if utils.check_json(curjson) then
        v_param = curjson::jsonb;
        if v_param->>'snils' is not null and v_param->>'insurance_number' is null then
            v_param := v_param || jsonb_build_object('insurance_number', v_param->>'snils');
        end if;
        if v_param->>'birth_date' ~ '\d{4}-\d{2}-\d{2}' then
            v_param := v_param || jsonb_build_object('birth_date', regexp_replace(v_param->>'birth_date', '(\d{4})-(\d{2})-(\d{2})', '\3.\2.\1'));
        end if;
        if v_param->>'certificate_code' = '10' then
            v_param := v_param || jsonb_build_object('certificate_code', '21');
        end if;
        
    else
        return json_build_object('message', 'Не верный формат JSON', 'status', 'Ошибка')::text;
    end if;
    
    v_buffer = (select jsonb_build_object( 'id', i.id, 'death_date', di.death_date)::text
        from ourpension.individual i
        left join lateral (select death_date from ourpension.application_death_info di
                                                where di.individual_id = i.id
                                                    and accepted is not null
                                                    and canceled is null
                                                    and rejected is null
                                                order by accepted desc
                                                limit 1
                                            ) di on true
        where i.id = ourpension.check_id_individual_json(v_param::json)
            and translate(upper(first_name), 'ЁЙ', 'ЕИ') = translate(upper(v_param::json ->>'first_name'), 'ЁЙ', 'ЕИ')
            and case when coalesce(v_param::json ->>'last_name','') <> '' then translate(upper(last_name), 'ЁЙ', 'ЕИ') = translate(upper(v_param::json ->>'last_name'), 'ЁЙ', 'ЕИ') else true end
            and case when coalesce(v_param::json ->>'middle_name' ,'') <> '' then coalesce(translate(upper(middle_name), 'ЁЙ', 'ЕИ'), '') = coalesce(translate(upper(v_param::json ->>'middle_name'), 'ЁЙ', 'ЕИ'), '') else true end
            and case when coalesce(v_param::json ->>'certificate_number' ,'') <> '' then certificate_number = v_param::json ->>'certificate_number' else true end
            and case when coalesce(v_param::json ->>'certificate_series' ,'') <> '' then certificate_series = v_param::json ->>'certificate_series' else true end
--            and birth_date = to_date(v_param::json ->>'birth_date','dd.mm.yyyy')
--            and case when v_param::json ->>'insurance_number' is not null then insurance_number = v_param::json ->>'insurance_number' else true end
    );
    raise notice '%',v_buffer;
return (
    case when v_buffer is not null then
        case
            when integration.f_check_change_snils((v_buffer::json->>'id')::bigint, v_param::json) then v_buffer
            else json_build_object('message', 'Мы не можем вас однозначно идентифицировать. Пожалуйста, обратитесь лично в офис', 'status', 'Ошибка')::text
        end
    else json_build_object('message', 'ФЛ не найдено', 'status', 'Ошибка')::text
    end
);
end;
$function$