DROP FUNCTION if exists sp_LoadProfList(xml);
DROP FUNCTION if exists sp_LoadProfList(xml, uuid, uuid);
CREATE OR REPLACE FUNCTION sp_DnIdent_V2
(
    x xml default null::xml
)
RETURNS TABLE (FILENAME text, IDENTIFICATION_RESULT_DN text, MCOD text)
LANGUAGE plpgsql
AS $function$
declare 
    _FILENAME varchar(50);
    _MCOD varchar(6);
    _DATA timestamp;
begin
    Drop Table If Exists TmpZGLV;
    Create Temp Table TmpZGLV
    As
    select
        (xpath('//VERSION/text()', z))[1]::varchar(5)                            as VERSION
    ,    to_timestamp((xpath('//DATA/text()', z))[1]::varchar(10), 'YYYY-MM-DD')    as DATA
    ,    (xpath('//FILENAME/text()', z))[1]::varchar(50)                          as FILENAME
    ,    (xpath('//MCOD/text()', z))[1]::varchar(6)                               as MCOD
    from
    (
        select unnest(xpath('/IDENTIFICATION_DN/ZGLV', x)) as z
    ) t1;

    
    select 
        into _FILENAME, _MCOD, _DATA
        t.FileName, t.Mcod, t.Data
    from TmpZGLV t
    limit 1;
    
    Drop Table If Exists TmpPERS;
    Create Temp Table TmpPERS
    As
    select
        gen_random_uuid()    as ItemID
    ,    (xpath('//ID_PAC/text()', z))[1]::varchar(36)                         as ID_PAC
    ,    (xpath('//FAM/text()', z))[1]::varchar(40)                               as FAM
    ,    (xpath('//IM/text()', z))[1]::varchar(40)                                as IM
    ,    (xpath('//OT/text()', z))[1]::varchar(40)                                as OT
    ,    (xpath('//W/text()', z))[1]::text::int                                     as W
    ,    to_timestamp((xpath('//DR/text()', z))[1]::varchar(10), 'YYYY-MM-DD')    as DR
    ,    (xpath('//ENP/text()', z))[1]::varchar(16)                               as ENP
    ,    (xpath('//DOCTYPE/text()', z))[1]::text::varchar(2)                         as DOCTYPE
    ,    (xpath('//DOCSER/text()', z))[1]::varchar(10)                            as DOCSER
    ,    (xpath('//DOCNUM/text()', z))[1]::varchar(20)                            as DOCNUM
    ,    (xpath('//VPOLIS/text()', z))[1]::text::int                                as VPOLIS
    ,    (xpath('//SPOLIS/text()', z))[1]::varchar(10)                            as SPOLIS
    ,    (xpath('//NPOLIS/text()', z))[1]::varchar(20)                            as NPOLIS
    ,    (xpath('//SMO/text()', z))[1]::text::varchar(5)                            as SMO
    
    ,    (xpath('//SNILS/text()', z))[1]::varchar(14)                               as SNILS
    ,    (xpath('//INSURED/text()', z))[1]::varchar(36)::uuid         as INSURED -- заглушка    
    ,    (xpath('//MoAttachment/text()', z))[1]::varchar(36)::varchar(6)    as MoAttachment -- заглушка        
    ,    ('Верные данные: '  || chr(10))::text                                as ErrorString
    from
    (
        select unnest(xpath('/IDENTIFICATION_DN/ZL', x)) as z
    ) t1;

        --step 0
    update TmpPERS t1
    set INSURED = 
    (
        select t2.InsuredID from ISZL.T_INSURED t2
        where t2.ID_PAC = t1.ID_PAC or t2.ENP = t1.ENP
        limit 1
    )
    where t1.INSURED is null;

    --step 1
    update TmpPERS 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 
            and (ipr.DateStop is null or ipr.DateStop >= _DATA)
            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 TmpPERS 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 TmpPERS 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 
            and (ipr.DateStop is null or ipr.DateStop >= _DATA)
            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 TmpPERS 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 
            and  (ipr.DateStop is null or ipr.DateStop >= _DATA)
            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;
    


    --mo attachment
    update TmpPERS zl
    set MoAttachment = (
        select  org.Code
        from ISZL.T_MO_ATTACHMENT moattach
        join ISZL.T_ORGANIZATION org on org.OrganizationID = moattach.Mo
        where moattach.AttachDate <= now()
        and moattach.Status = 1
        and moattach.Insured = zl.INSURED
        and not exists 
        (select * from ISZL.T_MO_ATTACHMENT t1
        where t1.AttachDate < now()
        and t1.AttachDate >= moattach.AttachDate
        and t1.Status = 2
        )
        limit 1
    );

    Update TmpPERS
    set ErrorString = 'Ошибка идентификации'
    where INSURED is null;

    Update TmpPERS p
    set ErrorString = ErrorString || 'Пациент умер. Дата смерти: ' || to_char(pat.DateDeath, 'YYYY-MM-DD')  || chr(10)
    from ISZL.T_INSURED pat 
    where p.INSURED = pat.InsuredID
    and pat.DateDeath is not null;

    Update TmpPERS p
    set ErrorString = ErrorString || 'Фамилия: ' || pat.LastName  || chr(10)
    from ISZL.T_INSURED pat 
    where p.INSURED = pat.InsuredID
    and p.FAM <> pat.LastName;

    Update TmpPERS p
    set ErrorString = ErrorString || 'Имя: ' || pat.FirstName  || chr(10)
    from ISZL.T_INSURED pat 
    where p.INSURED = pat.InsuredID
    and p.IM <> pat.FirstName;

    Update TmpPERS p
    set ErrorString = ErrorString || 'Отчество: ' || pat.Patronymic  || chr(10)
    from ISZL.T_INSURED pat 
    where p.INSURED = pat.InsuredID
    and p.OT <> pat.Patronymic and p.OT is not null;

    Update TmpPERS p
    set ErrorString = ErrorString || 'Дата рождения: ' || to_char(pat.Birthday, 'YYYY-MM-DD')  || chr(10)
    from ISZL.T_INSURED pat 
    where p.INSURED = pat.InsuredID
    and p.DR <> pat.Birthday;

    Update TmpPERS p
    set ErrorString = ErrorString || 'СНИЛС: ' || pat.SNILS  || chr(10)
    from ISZL.T_INSURED pat 
    where p.INSURED = pat.InsuredID
    and p.SNILS <> pat.SNILS and p.SNILS is not null;

    with upd
    as
    (
        select iszlpolicy.OmsType, pat.InsuredID
        from ISZL.T_INSURED pat
        left join lateral
        (
            select ipr.InsuredPolicyRecordID, ipr.Policy
            from ISZL.T_INSURED_POLICY_RECORD ipr 
            where pat.InsuredID = ipr.Insured 
            and  (ipr.DateStop is null or ipr.DateStop >= _DATA)
            limit 1
        ) record on true
        inner join
            ISZL.T_POLICY iszlpolicy 
            on iszlpolicy.PolicyID = record.Policy
    )
    Update TmpPERS p
    set ErrorString = ErrorString || 'Тип полиса: ' || upd.OmsType::varchar(2)  || chr(10)
    from upd
    where p.INSURED = upd.InsuredID
    and p.VPOLIS <> upd.OmsType 
    and p.VPOLIS is not null;

    with upd
    as
    (
        select iszlpolicy.Series, pat.InsuredID
        from ISZL.T_INSURED pat
        left join lateral
        (
            select ipr.InsuredPolicyRecordID, ipr.Policy
            from ISZL.T_INSURED_POLICY_RECORD ipr 
            where pat.InsuredID = ipr.Insured 
            and  (ipr.DateStop is null or ipr.DateStop >= _DATA)
            limit 1
        ) record on true
        inner join
            ISZL.T_POLICY iszlpolicy 
            on iszlpolicy.PolicyID = record.Policy
    )
    Update TmpPERS p
    set ErrorString = ErrorString || 'Серия полиса: ' || upd.Series  || chr(10)
    from upd
    where p.INSURED = upd.InsuredID
    and p.SPOLIS <> upd.Series 
    and p.SPOLIS is not null;
    
    with upd
    as
    (
        select iszlpolicy.Number, pat.InsuredID
        from ISZL.T_INSURED pat
        left join lateral
        (
            select ipr.InsuredPolicyRecordID, ipr.Policy
            from ISZL.T_INSURED_POLICY_RECORD ipr 
            where pat.InsuredID = ipr.Insured 
            and  (ipr.DateStop is null or ipr.DateStop >= _DATA)
            limit 1
        ) record on true
        inner join
            ISZL.T_POLICY iszlpolicy 
            on iszlpolicy.PolicyID = record.Policy
    )
    Update TmpPERS p
    set ErrorString = ErrorString || 'Номер полиса: ' || upd.Number  || chr(10)
    from upd
    where p.INSURED = upd.InsuredID
    and p.NPOLIS <> upd.Number;
    
    return query
    select
        REPLACE(_FILENAME, 'IRD', 'RD')::text
    ,    '<?xml version="1.0" encoding="utf-8"?>
' ||    (
            select
            xmlelement
            (
                name "IDENTIFICATION_RESULT_DN"
            ,    (
                    select
                    xmlagg
                    (
                        xmlelement
                        (
                            name "ZGLV"
                        ,    xmlforest
                            (
                                '1.0'                                        as "VERSION"
                            ,    to_char(now()::timestamp, 'YYYY-MM-DD')    as "DATA"
                            ,    REPLACE(t.FILENAME, 'IRD', 'RD')            as "FILENAME"
                            ,    t.MCOD                                        as "MCOD"                            
                            )
                        )
                    )
                    from TmpZGLV t
                )
            ,    (
                    select
                    xmlagg
                    (
                        xmlelement
                        (
                            name "ZL"
                        ,    xmlforest
                            (
                                p.ID_PAC  as "ID_PAC"
                            ,    pat.ENP as "ENP"
                            ,    mo.Code as "MO"
                            ,    smo.Code as "SMO"
                            ,    case when p.ErrorString = ('Верные данные: '  || chr(10)) 
                                    then null 
                                    else  p.ErrorString 
                                end as "ERROR"
                            )
                        )
                    )
                    from TmpPERS p
                    left join 
                        ISZL.T_INSURED pat 
                        on p.INSURED = pat.InsuredID
                    left join lateral
                    (
                        select ipr.InsuredPolicyRecordID, ipr.Policy, ipr.Smo
                        from ISZL.T_INSURED_POLICY_RECORD ipr 
                        where pat.InsuredID = ipr.Insured 
                        and  (ipr.DateStop is null or ipr.DateStop >= _DATA)
                        order by ipr.DateBegin desc
                        limit 1
                    ) record on true
                    left join 
                        ISZL.T_POLICY iszlpolicy
                        on iszlpolicy.PolicyID = record.Policy
                    left join 
                        ISZL.T_ORGANIZATION smo
                        on smo.OrganizationID = record.Smo
                    left join lateral
                    (
                        select att.MoAttachmentID, att.Mo
                        from ISZL.T_MO_ATTACHMENT att 
                        where att.Insured = pat.InsuredID
                        and att.Status = 1 
                        and not exists
                        (
                            select * from ISZL.T_MO_ATTACHMENT att1 
                            where att1.Insured = pat.InsuredID and att.Status = 2 
                            and att1.AttachDate::date >= att.AttachDate::date
                        )
                        limit 1
                    ) attachment on true
                    left join 
                        ISZL.T_ORGANIZATION mo 
                        on mo.OrganizationID = attachment.Mo
                )
            )
        )::text
    ,    (select t.MCOD FROM TmpZGLV t limit 1)::text;
end;
$function$;
