DROP FUNCTION if exists sp_InformingDn(uuid, uuid, xml);
CREATE OR REPLACE FUNCTION sp_InformingDn
(
    "FileID" uuid default null::uuid,
    "Worker" uuid default null::uuid,
    x xml default null::xml
)
RETURNS TABLE (Res boolean, Err text)
LANGUAGE plpgsql
AS $function$
declare 
    _VERSION varchar(5);
    _FILENAME varchar(50);
    _SMO varchar(5);
    _InfoFile uuid;
begin
    Drop Table If Exists TmpZGLV;
    Create Temp Table TmpZGLV
    As
    select
        "FileID" as FileId
    ,    (xpath('//VERSION[1]/text()', z))[1]::varchar(5)                            as VERSION
    ,    to_timestamp((xpath('//DATA[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')    as DATA
    ,    (xpath('//FILENAME[1]/text()', z))[1]::varchar(50)                          as FILENAME
    ,    (xpath('//SMO[1]/text()', z))[1]::text::varchar(5)                                   as SMO
    ,    date_trunc('day', now()::timestamp)                                            as ReceivedDate
    ,    now()::timestamp                                                            as ReceivedTime
    from
    (
        select unnest(xpath('/INFORMING_DN/ZGLV', x)) as z
    ) t1;
    
    select 
        into _VERSION, _FILENAME, _SMO
        t.Version, t.FileName, t.Smo
    from TmpZGLV t
    limit 1;
    
    Drop Table If Exists TmpZL;
    Create Temp Table TmpZL
    As
    select
        gen_random_uuid()    as ItemID
    ,    (xpath('//FAM[1]/text()', z))[1]::varchar(40)                                   as FAM
    ,    (xpath('//IM[1]/text()', z))[1]::varchar(40)                                    as IM
    ,    (xpath('//OT[1]/text()', z))[1]::varchar(40)                                    as OT
    ,    to_timestamp((xpath('//DR[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')        as DR
    ,    (xpath('//DS[1]/text()', z))[1]::varchar(10)                                    as DS
    ,    (xpath('//ENP[1]/text()', z))[1]::varchar(16)                                   as ENP
    ,    (xpath('//VPOLIS[1]/text()', z))[1]::text::int                                     as VPOLIS
    ,    (xpath('//SPOLIS[1]/text()', z))[1]::varchar(10)                                as SPOLIS
    ,    (xpath('//NPOLIS[1]/text()', z))[1]::varchar(20)                                as NPOLIS
    ,    to_timestamp((xpath('//DATA_INF[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')    as DATA_INF
    ,    (xpath('//F_INF[1]/text()', z))[1]::text::int                                     as F_INF
    ,    (xpath('//N_INF[1]/text()', z))[1]::varchar(50)                                    as N_INF    
    ,    (xpath('//TIP_INFO[1]/text()', z))[1]::text::int                                 as TIP_INFO
    ,    (xpath('//INSURED/text()', z))[1]::varchar(36)::uuid         as INSURED -- заглушка    
    
    ,    _SMO                                                                            as SMO -- заглушка
    from
    (
        select unnest(xpath('/INFORMING_DN/ZL', x)) as z
    ) t1;
    
        --step 0
    update TmpZL t1
    set INSURED = 
    (
        select t2.InsuredID from ISZL.T_INSURED t2
        where t2.ENP = t1.ENP
        limit 1
    )
    where t1.INSURED is null;

    --step 1
    update TmpZL t1
    set INSURED = 
    (
        select t2.InsuredID 
        from ISZL.T_INSURED t2
        left join lateral
        (
            select ipr.InsuredPolicyRecordID, ipr.Policy
            from ISZL.T_INSURED_POLICY_RECORD ipr 
            where t2.InsuredID = ipr.Insured 
            order by ipr.DateBegin desc
            limit 1
        ) record on true
          inner join 
            ISZL.T_POLICY iszlpolicy 
            on iszlpolicy.PolicyID = record.Policy
          where t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR 
        and iszlpolicy.Number = t1.NPOLIS
        limit 1
    )
    where t1.INSURED is null;

    --step 2
    update TmpZL t1
    set INSURED =
    (
        select t2.InsuredID from ISZL.T_INSURED t2
        where t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR
        limit 1
    )
    where t1.INSURED is null;

    --step 3
    update TmpZL t1
    set INSURED =
    (
        select t2.InsuredID
        from ISZL.T_INSURED t2
        left join lateral
        (
            select ipr.InsuredPolicyRecordID, ipr.Policy
            from ISZL.T_INSURED_POLICY_RECORD ipr 
            where t2.InsuredID = ipr.Insured 
            order by ipr.DateBegin desc
            limit 1
        ) record on true
        inner join 
            ISZL.T_POLICY iszlpolicy 
            on iszlpolicy.PolicyID = record.Policy
        where t2.LastName = t1.FAM and t2.FirstName = t1.IM and iszlpolicy.Number = t1.NPOLIS
        limit 1
    )
    where t1.INSURED is null;

    --step 4
    update TmpZL t1
    set INSURED =
    (
        select t2.InsuredID 
        from ISZL.T_INSURED t2
        left join lateral
        (
            select ipr.InsuredPolicyRecordID, ipr.Policy
            from ISZL.T_INSURED_POLICY_RECORD ipr 
            where t2.InsuredID = ipr.Insured 
            order by ipr.DateBegin desc
            limit 1
        ) record on true
        inner join 
            ISZL.T_POLICY iszlpolicy 
            on iszlpolicy.PolicyID = record.Policy
          where t2.LastName = t1.FAM and t2.Birthday = t1.DR and iszlpolicy.Number = t1.NPOLIS
        limit 1
    )
    where t1.INSURED is null;
    
    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors
    As
    select
        1::int as code
    ,    ('Застрахованный не найден ни по ФИО и ДР, ни по номеру и серии полиса. NPOLIS(' 
         || p.NPOLIS || ')')::varchar(255) as comment
    from TmpZL p
    where p.INSURED is null
    union all
    select
        2
    ,    'Пациент умер. Дата смерти: ' || to_char(t2.DateDeath, 'YYYY-MM-DD') || '. NPOLIS(' || p.NPOLIS || ')'
    from TmpZL p
    inner join ISZL.T_INSURED t2 
        on t2.InsuredID = p.INSURED
    where t2.DateDeath is not null;
    
 if exists(select* from TmpErrors)
then
 return query
        select '0'::boolean
        , '<?xml version="1.0" encoding="utf-8"?>
        ' || 
        (
            xmlelement
            (
                name "ROOT"
            ,    (
                    select
                    xmlagg
                    (
                        xmlelement
                        (
                            name "ERR"
                        ,    xmlforest
                            (
                                te.CODE AS "CODE"
                            ,    te.COMMENT AS "COMMENT"
                            )
                        )
                    )
                    FROM TmpErrors te
                )
            )
         )::text;
END IF;

IF (SELECT "FileID" FROM TmpZGLV) is not null
  then
--------------------
    --Check File Exist--
    --------------------
_InfoFile := (select dn.FileID from ISZL.T_DN_INFO_FILE dn where dn.FILENAME = _FILENAME limit 1);

            if _InfoFile is not null
            then
                perform sp_DispDnInfoDel(_InfoFile);
            end if;

            insert into VCLib.T_FILE(FileID, Name, CreateDateTime, FileNameForm, SaveFileLocation, UseOle, type_)
            select t.FileId, t.FILENAME, now()::timestamp, 1, 1, '0'
            , (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DN_INFO_FILE' and e.schema = 'ISZL' limit 1)
            from TmpZGLV t;

            insert into ISZL.T_DN_INFO_FILE(FileID, VERSION, CODE_MO, ID, ReceivedDate, ReceivedTime, DATA, FILENAME)
            select t.FileId, t.VERSION, null, null, t.ReceivedDate, t.ReceivedTime, t.DATA, t.FILENAME
            from TmpZGLV t;
        else
            DELETE FROM ISZL.T_DN_INFO WHERE FileName = _FILENAME;
        end if;

        insert into ISZL.T_DN_INFO(DnInfoID, DnInfoForm, INFO_DATE, INFO_TYPE, Addr, SMO, Insured, Mkb, FileName)
        select gen_random_uuid(), t1.F_INF, t1.DATA_INF, t1.TIP_INFO, t1.N_INF, t1.SMO, t1.INSURED, mkb.MkbID, _FILENAME
        from TmpZL t1
        inner join 
            ISZL.T_MKB mkb 
            on mkb.MCBCode = t1.DS 
            and mkb.EndCode is null
            where t1.INSURED IS NOT NULL AND t1.MkbId IS NOT NULL AND t1.DuplicateCount <= 0;
    --return query
    --select null::boolean, null::text;
end;
$function$;
