DROP FUNCTION if exists sp_LoadProfInfo(uuid, uuid, xml);
CREATE OR REPLACE FUNCTION sp_LoadProfInfo
(
    x xml default null::xml,
    "FileID" uuid default null::uuid,
    "Worker" uuid default null::uuid
)
RETURNS TABLE (Res boolean, Err text)
LANGUAGE plpgsql
AS $function$
declare 
    _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_PM/ZGLV', x)) as z
    ) t1;
    
    select 
        into _FILENAME, _SMO
        t.FileName, t.Smo
    from TmpZGLV t
    limit 1;
    
    Drop Table If Exists TmpZAP;
    Create Temp Table TmpZAP
    As
    select
        gen_random_uuid()    as ItemID
    ,    _SMO                                                                            as SMO -- заглушка
    ,    (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('//IDDT[1]/text()', z))[1]::varchar(3)                                    as IDDT
    ,    (xpath('//YEAR[1]/text()', z))[1]::text::int                                     as YEAR
    ,    (xpath('//ENP[1]/text()', z))[1]::varchar(16)                                   as ENP
    ,    (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(250)                                as N_INF    
    ,    (xpath('//TIP_INFO[1]/text()', z))[1]::text::int                                 as TIP_INFO    
    ,    (xpath('//V016[1]/text()', z))[1]::varchar(36)::uuid                               as V016 -- заглушка
    ,    (xpath('//INSURED[1]/text()', z))[1]::varchar(36)::uuid                         as INSURED -- заглушка    
    ,    (xpath('//DispDirect[1]/text()', z))[1]::varchar(36)::uuid                         as DispDirect -- заглушка    
    ,    (xpath('//DuplicateCount[1]/text()', z))[1]::text::int                            as DuplicateCount -- заглушка
    from
    (
        select unnest(xpath('/INFORMING_PM/ZL', x)) as z
    ) t1;

    update TmpZAP t1
    set V016 = v016.V016ID
    from iszl.T_V016_DISPT v016 
    where v016.IDDT = t1.IDDT
    and v016.DATEEND is null;

    --step 0
    update TmpZAP 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 TmpZAP 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 
            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 TmpZAP 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 TmpZAP 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
            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 TmpZAP 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 
            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;

    --step 0
    update TmpZAP t1
    set DispDirect = 
    (
          select t2.DispDirectID from iszl.T_DISP_DIRECT t2
        left join iszl.T_DISP_DIRECT_FACT ddf on ddf.DispDirect = t2.DispDirectID
          where t2.Insured = t1.INSURED and t2.V016Dispt = t1.V016 and t2.YEAR = t1.YEAR
        and ddf.DispDirectFactID is null
        and t2.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 1)
        limit 1
    )
    where t1.INSURED is not null and t1.V016 is not null;


    -- Error #4
    -- Нужно исправить, это набросок
    -- update TmpZAP t1
    -- set DuplicateCount = count(*)
    -- from iszl.t_disp_direct_info ddi
    -- where ddi.Insured = t1.INSURED
    -- and ddi.V016Dispt = t1.V016
    -- and ddi.TIP_INFO = t1.TIP_INFO
    -- and ddi.Smo = t1.SMO
    -- and date_trunc('day', ddi."date") = date_trunc('day', t1."date");

    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors
    As
    select 1::int as Code, 'Значение поля IDDT не содержится в справочнике V016. NPOLIS(' || t1.NPOLIS || ')'::varchar(250) as Comment
    from TmpZAP t1
    where t1.V016 is null
    union all
    select 2::int as Code, 'Застрахованный не найден в БД. NPOLIS(' || t1.NPOLIS || ')'::varchar(250) as Comment
    from TmpZAP t1
    where t1.INSURED is null
    union all
    select 3::int as Code, 'Запись ПМ не найдена в БД. NPOLIS(' || t1.NPOLIS || ')'::varchar(250) as Comment
    from TmpZAP t1
    where t1.DispDirect is null;
    --union all
    --select 4::int as Code, 'Уже существует информирование с указанными параметрами'::varchar(250) as Comment
    --from TmpZAP t1
    --where t1.DuplicateCount > 0;

    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;
    else
        if "FileID" is not null
        then
            --------------------
            --Сheck File Exist--
            --------------------
            _InfoFile := 
            (
                select f.FileID from ISZL.T_DISP_DIRECT_INFO_FILE fi
                inner join
                    VCLib.T_FILE f 
                    on f.FileID = fi.FileID
                where f.NAME = _FILENAME 
                limit 1
            );

            if _InfoFile is not null
            then
                perform sp_DispInfoDel(_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 = 'DISP_DIRECT_INFO_FILE' and e.schema = 'ISZL' limit 1)
            from TmpZGLV t;

            insert into ISZL.T_DISP_DIRECT_INFO_FILE(FileID, VERSION, ReceivedDate, ReceivedTime, Worker)
            select t.FileId, t.VERSION, now()::timestamp, now()::timestamp, "Worker"
            from TmpZGLV t;
        else
            DELETE FROM ISZL.T_DISP_DIRECT_INFO WHERE FileName = _FILENAME;
        end if;

        insert into ISZL.T_DISP_DIRECT_INFO(DispDirectInfoID, Date, DispDirect, DispDirectInfoType, DispDirectInfoFile
        , Addr, Result, ENP, V016Dispt, TIP_INFO, Insured, Mo, Smo, CreationDate, FileName)
         select zap.ItemID, zap.DATA_INF, zap.DispDirect, zap.F_INF, "FileID", zap.N_INF, null, zap.ENP, zap.V016
        , zap.TIP_INFO, zap.INSURED, null, orgsmo.OrganizationID, now()::timestamp, _FILENAME
         from TmpZAP zap
        left join
            ISZL.T_ORGANIZATION orgsmo 
            on orgsmo.Code = zap.SMO;
    end if;
end;
$function$;
