---

CREATE OR REPLACE PROCEDURE ws.sp_request_root(IN p_s_request text, OUT p_s_responce text, OUT p_s_resp_status text, OUT p_s_resp_msg text)
LANGUAGE plpgsql
AS $procedure$
    
declare
    sExecProc         text;
    nMethodId         bigint;
    nMethodLogId     bigint;
    nMethodLogLookupId bigint;
    sRequestHash     ws.method_log.req_hash%type;

    sDbLinkDb    text;

    sStakedText01 text;
    sStakedText02 text;
    sStakedText03 text;
begin

    p_s_responce := null;    
    p_s_resp_status := '200';
    p_s_resp_msg := 'OK';    

    -----------------------------------------
    -- base chk
    -----------------------------------------
    begin
        sExecProc := lower( trim( p_s_request::json ->> 'RequestParams_Method' ) );
    exception when OTHERS then
        p_s_resp_status := '400';
        p_s_resp_msg := 'Передан невалидный JSON';            
    end;

    if ( sExecProc is null and p_s_resp_status = '200' ) then
        p_s_resp_status := '400';
        p_s_resp_msg := 'Не указан RequestParams_Method';
    end if;
    
    if ( to_regproc(sExecProc) is null and p_s_resp_status = '200' ) then
        p_s_resp_status := '405';
        p_s_resp_msg := concat('Не найден целевой объект', ' "' || sExecProc || '"' );        
    end if;

    if ( p_s_resp_status <> '200' ) then
        return;
    end if;

    -----------------------------------------
    -- get/add methods.id from/to methods list
    -----------------------------------------
    begin
        select m.id, nullif(m.dblink_db, '')
         into STRICT nMethodId, sDbLinkDb
         from ws.methods m
         where m.code = sExecProc;
    exception when NO_DATA_FOUND then
        nMethodId := nextval('ws.seq_common'::regclass);

        insert into ws.methods(id, code)
        values(nMethodId, sExecProc);
    end;

    if not exists (    select 1
                     from integration.list_db_for_dblink l
                     where l."describe" = sDbLinkDb ) then
        raise notice 'Не найден указанный на методе dblink = %', sDbLinkDb;
        sDbLinkDb := null;
    end if;

    -----------------------------------------
    -- main routine
    -----------------------------------------
    nMethodLogId := nextval('ws.seq_common'::regclass);

    begin
        sRequestHash := md5( p_s_request :: jsonb :: text );
    exception when OTHERS then
        sRequestHash := md5( p_s_request );
    end;

    insert into ws.method_log(id, method_id, req_hash)
    values(nMethodLogId, nMethodId, sRequestHash);

    -- get cached responce
    select ml.id, p.part_value
     into nMethodLogLookupId, p_s_responce
     from ws.method_log ml
        join ws.methods m on m.id = ml.method_id
        join ws.method_log_parts p on (p.method_log_id = ml.id and p.part_type = 'resp')
     where ml.method_id = nMethodId
     and ml.req_hash = sRequestHash
     and ml.resp_status = '200'
     and coalesce(ml.cache_type, 0) = 0
     and ml.id < nMethodLogId    
     and ml.start_dt >= clock_timestamp() - m.resp_lookup_sec * interval '1 second'    
     order by ml.start_dt desc
     limit 1;     
    
    --
    if ( nMethodLogLookupId is null ) then    
        insert into ws.method_log_parts(method_log_id, part_type, part_value)
        values(nMethodLogId, 'req', p_s_request);    
        
        -- main call
        begin
            if exists (select 1
                         from information_schema.routines r
                        where r.routine_name = lower(split_part(sExecProc, '.', 2))
                         and r.routine_schema = lower(split_part(sExecProc, '.', 1))
                         and r.routine_type = 'PROCEDURE'
                     ) then
                if sDbLinkDb is not null then                
                    sExecProc := format('call %s(%L, null)', sExecProc, p_s_request);
                    sExecProc := format('select * from dblink(integration.dblink_string_connect(%L), %L) AS (output_val text)', sDbLinkDb, sExecProc);
                    
                    -- raise notice '%', sExecProc;
                    execute sExecProc INTO STRICT p_s_responce;
                else        
                    execute 'CALL ' || sExecProc || ' ($1, $2)' INTO p_s_responce USING p_s_request, p_s_responce;
                 end if;
            else
                sExecProc := format('select t.* from %s(%L) t', sExecProc, p_s_request);
                if sDbLinkDb is not null then
                    sExecProc := format('select tt.* from integration.get_result_from_dyn_dblink(%L, %L) tt', sDbLinkDb, sExecProc);
                end if;
                
                -- raise notice '%', sExecProc;
                execute sExecProc INTO STRICT p_s_responce;
            end if;

            -- close log
            insert into ws.method_log_parts(method_log_id, part_type, part_value)
            values(nMethodLogId, 'resp', p_s_responce);
        EXCEPTION WHEN OTHERS
        THEN            
            GET STACKED DIAGNOSTICS
                sStakedText01 = MESSAGE_TEXT,
                sStakedText02 = PG_EXCEPTION_DETAIL,
                sStakedText03 = PG_EXCEPTION_CONTEXT;

            p_s_resp_msg := left(concat( sStakedText01,
                                         E'\r\n' || sStakedText02,
                                         E'\r\n' || sStakedText03 ), 512);
            p_s_resp_status := '500';
        END;

        update ws.method_log l
         set end_dt = clock_timestamp(),
             resp_status = p_s_resp_status,
             resp_msg = p_s_resp_msg
         where l.id = nMethodLogId;
        
    else
        update ws.method_log l
         set end_dt = clock_timestamp(),
             resp_status = p_s_resp_status,
             resp_msg = p_s_resp_msg,
             cache_type = 1,
             cache_method_log_id = nMethodLogLookupId
         where l.id = nMethodLogId;
    end if;

end;
$procedure$