DROP FUNCTION IF EXISTS public.sp_loadproflist(x xml, "FileID" uuid, "Worker" uuid);
DROP FUNCTION IF EXISTS public.sp_loadproflist(x xml, "FileID" uuid, "Worker" uuid, "PackageName" text, "FileName" text);
CREATE OR REPLACE FUNCTION public.sp_loadproflist(
    x xml DEFAULT NULL::xml,
    "FileID" uuid DEFAULT NULL::uuid,
    "Worker" uuid DEFAULT NULL::uuid,
    "PackageName" text default null::uuid,
    "FileName" text default null::uuid)
RETURNS TABLE(issinglexmlflk boolean, content text, filename text, targetdirectory text, codepage text, errordescrxpath text)
LANGUAGE plpgsql
AS $function$
declare 
    _FILENAME varchar(50);
    _SMO varchar(5);
    _MCOD varchar(6);
    _DATA timestamp;
    _ExistFile uuid;
    _CrDate timestamp;
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('//MCOD[1]/text()', z))[1]::varchar(6)                               as MCOD
	,	(xpath('//SMO[1]/text()', z))[1]::varchar(5)                               	as SMO
    ,    date_trunc('day', now()::timestamp)                                            as ReceivedDate
    ,    now()::timestamp                                                            as ReceivedTime
    from
    (
        select unnest(xpath('/PROF_LIST/ZGLV', x)) as z
    ) t1;
    
    select 
        into _FILENAME, _SMO, _MCOD, _DATA, _CrDate
        t.FileName, t.Smo, t.Mcod, t.Data, current_timestamp
    from TmpZGLV t
    limit 1;
    
    Drop Table If Exists TmpPERS;
    Create Temp Table TmpPERS
    As
    select
        gen_random_uuid()    as ItemID
	,	(xpath('//ID_PAC[1]/text()', z))[1]::varchar(36)::uuid     					as ID_PAC
	,	(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
	,	(xpath('//W[1]/text()', z))[1]::text::int                             		as W
	,	to_timestamp((xpath('//DR[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')	as DR
	,	(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
	,	(xpath('//SNILS[1]/text()', z))[1]::varchar(14)                           	as SNILS
	,	(xpath('//PROF[1]/MONTH[1]/text()', z))[1]::text::int                       as MONTH
	,	(xpath('//PROF[1]/YEAR[1]/text()', z))[1]::text::int                        as YEAR
	,	(xpath('//PROF[1]/TYPE[1]/text()', z))[1]::text::int                        as TYPE
	,	(xpath('//PROF[1]/IDDT[1]/text()', z))[1]::varchar(3)                       as IDDT
	,	_DATA																		as DATA
    ,    _SMO                                                                        as SMO
    ,    _MCOD                                                                        as CODE_MO
	,	(xpath('//V016[1]/text()', z))[1]::varchar(36)::uuid           				as V016 -- заглушка
	,	(xpath('//InsuredPolicyRecordID[1]/text()', z))[1]::varchar(36)::uuid     	as InsuredPolicyRecordID -- заглушка	
	,	(xpath('//DispDirectID[1]/text()', z))[1]::varchar(36)::uuid   			  	as DispDirectID -- заглушка	
	,	(xpath('//DuplicateCount[1]/text()', z))[1]::text::int 				    	as DuplicateCount -- заглушка
    from
    (
        select unnest(xpath('/PROF_LIST/ZL', x)) as z
    ) t1;
    
    Drop Table If Exists TmpCONTACTS;
    Create Temp Table TmpCONTACTS
    As
    select
		(xpath('//ID_PAC[1]/text()', z))[1]::varchar(36)::uuid		as ID_PAC
	,	(xpath('//ENP[1]/text()', z))[1]::varchar(16)          		as ENP
	,	(xpath('//CONTACT[1]/text()', con))[1]::varchar(255)		as CONTACT
	,	(xpath('//TYPE[1]/text()', con))[1]::text::varchar(2)		as TYPE
    from
    (
        select z, unnest(xpath('/ZL/CONTACTS', z)) as con
        from
        (
            select unnest(xpath('/PERS_LIST/ZL', x)) as z
        ) t1
    ) t2;
    
    
    update TmpPERS p
    set V016 = v016.V016ID
    from iszl.T_V016_DISPT v016 
    where v016.IDDT = p.IDDT
    and v016.DATEEND is null;

    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors
    As
    select 1::int as Code, 'Значение поля IDDT не содержится в справочнике V016 ID_PAC(' || t1.ID_PAC::varchar(36) || ') NPOLIS(' || t1.NPOLIS || ')'::varchar(250) as Comment
    from TmpPERS t1
    where t1.V016 is null;

    --step 0.
    update TmpPERS t1
    set InsuredPolicyRecordID = 
    (
        select t2.InsuredPolicyRecordID 
        from ISZL.T_INSURED_POLICY_RECORD t2
        join ISZL.T_POLICY p on p.PolicyID = t2.Policy
        where t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Patronymic = t1.OT and t2.Birthday = t1.DR 
        and p.Number = t1.NPOLIS
        limit 1
    )
    where t1.InsuredPolicyRecordID is null 
    and not exists(Select* from TmpErrors);
    --step 1. без отчества
    update TmpPERS t1
    set InsuredPolicyRecordID = 
    (
        select t2.InsuredPolicyRecordID 
        from ISZL.T_INSURED_POLICY_RECORD t2
        join ISZL.T_POLICY p on p.PolicyID = t2.Policy
        where t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR and p.Number = t1.NPOLIS
        limit 1
    )    
    where t1.InsuredPolicyRecordID is null
    and not exists(select* from TmpErrors);
    --step 2. без отчества и ДР
    update TmpPERS t1
    set InsuredPolicyRecordID = 
    (
        select t2.InsuredPolicyRecordID 
        from ISZL.T_INSURED_POLICY_RECORD t2
        join ISZL.T_POLICY p on p.PolicyID = t2.Policy
          where t2.LastName = t1.FAM and t2.FirstName = t1.IM and p.Number = t1.NPOLIS
        limit 1
     )
    where t1.InsuredPolicyRecordID is null
    and not exists(select* from TmpErrors);
    --step 3. без отчества и Фамилии
    update TmpPERS t1
    set InsuredPolicyRecordID = 
    (
          select t2.InsuredPolicyRecordID 
          from ISZL.T_INSURED_POLICY_RECORD t2
        join ISZL.T_POLICY p on p.PolicyID = t2.Policy
        where t2.FirstName = t1.IM and t2.Birthday = t1.DR and p.Number = t1.NPOLIS
        limit 1
    )
    where t1.InsuredPolicyRecordID is null
    and not exists(select* from TmpErrors);
    
    insert into TmpErrors(CODE, COMMENT)
    select 1, 'Не найден полис из учета полисов застрахованного ID_PAC(' || t1.ID_PAC::varchar(36) || ') NPOLIS(' || t1.NPOLIS || ')'
    from TmpPERS t1
    where t1.InsuredPolicyRecordID is null;

    insert into TmpErrors(CODE, COMMENT)
    select 3, 'Повторное направление на проф. мероприятия в отчетном году. ID_PAC(' || t1.ID_PAC::varchar(36) || ') NPOLIS(' || t1.NPOLIS || ')'
    from TmpPERS t1
    join iszl.T_INSURED_POLICY_RECORD ipr on ipr.InsuredPolicyRecordID = t1.InsuredPolicyRecordID
    where (t1.IDDT = 'ОПВ' or t1.IDDT = 'ДВ4') and t1.type = 0 and exists (
                    select t2.DispDirectID  
                    from ISZL.T_DISP_DIRECT t2
                        join iszl.T_V016_DISPT v016 on v016.V016ID = t2.V016Dispt
                    where (t2.YEAR = t1.YEAR and t2.Insured = ipr.Insured and (v016.IDDT = 'ОПВ' or v016.IDDT = 'ДВ4'))   
                        and t2.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 1)
                );
    
    if not exists(select* from TmpErrors)
    then
        if "FileID" is not null
        then
            --------------------
            --Сheck File Exist--
            --------------------
            _ExistFile := (select FileID from ISZL.T_DISP_DIRECT_FILE where FILENAME = _FILENAME limit 1);

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

            insert into ISZL.T_DISP_DIRECT_FILE(FileID, VERSION, DATA, FILENAME, receiveddate, recievedtime
            , YEAR, MONTH, CODE_MO, Worker, ID_MO)
            select t.FileId, t.VERSION, t.DATA, t.FILENAME, now()::timestamp, now()::timestamp, null, null
            , t.MCOD, "Worker", null
            from TmpZGLV t;
        
        end if;

        update iszl.T_DISP_DIRECT t1
        set DispDirectID = (
                select t2.DispDirectID  
                from ISZL.T_DISP_DIRECT t2
                where (t2.YEAR = t1.YEAR and t2.V016Dispt = t1.V016 and t2.Insured = ipr.Insured) 
                and t2.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 1)
                limit 1  
            )
        from TmpPERS p
        join iszl.T_INSURED_POLICY_RECORD ipr on ipr.InsuredPolicyRecordID = t1.InsuredPolicyRecordID
        where p.type <> 0 and t1.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 1);

        ------------------
        --Добавляем только новых в iszl.T_DISP_DIRECT из #PERS  p.TYPE = 0
         INSERT INTO iszl.T_DISP_DIRECT(DispDirectID, DispDirectFile, ID_PAC, FAM, IM, OT, Sex, DR, ENP, VPOLIS, SPOLIS
        , NPOLIS, SNILS_P, YEAR, V016Dispt, MONTH_Q, Smo, Mo, Insured, InsuredPolicyRecord, FileName, CreationDate)
         select p.ItemID, "FileID", p.ID_PAC, p.FAM, p.IM, p.OT, p.W, p.DR, p.ENP, p.VPOLIS, p.SPOLIS
        , p.NPOLIS, p.SNILS, p.YEAR, p.V016, p.MONTH, orgsmo.OrganizationID, orgmo.OrganizationID
        , ipr.Insured, p.InsuredPolicyRecordID, _FILENAME, _CrDate
         from TmpPERS p
         inner join
            iszl.T_INSURED_POLICY_RECORD ipr 
            on ipr.InsuredPolicyRecordID = p.InsuredPolicyRecordID
         left join
            ISZL.T_ORGANIZATION orgsmo 
            on orgsmo.Code = p.SMO
         left join
            ISZL.T_ORGANIZATION orgmo
            on orgmo.Code = p.CODE_MO
        where p.TYPE = 0;


        --обновляем данные где type = 1
        update iszl.T_DISP_DIRECT dd
        set DispDirectFile = "FileID"
        , FAM = p.FAM
        , IM = p.IM
        , OT = p.OT
        , Sex = p.W
        , DR = p.DR
        , ENP = p.ENP
        , VPOLIS = p.VPOLIS
        , SPOLIS = p.SPOLIS
        , NPOLIS = p.NPOLIS
        , SNILS_P = p.SNILS
        , Smo = orgsmo.OrganizationID
        , Mo = orgmo.OrganizationID
        , V016Dispt = p.V016
        , MONTH_Q = p.MONTH
        from TmpPERS p
        left join ISZL.T_ORGANIZATION orgsmo on orgsmo.Code = p.SMO
        left join ISZL.T_ORGANIZATION orgmo on orgmo.Code = p.CODE_MO
        where dd.ENP = p.ENP and dd.YEAR = p.YEAR and dd.V016Dispt = p.V016 and p.TYPE = 1
        and not exists(select* from iszl.T_DISP_DIRECT_FACT fact where fact.DispDirect = dd.DispDirectID)
        and dd.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 1);
     
        -- Сбор идентификаторов на удаление
        drop table if exists forDelete;
        create temp table forDelete as
        select distinct dd.DispDirectID as DispDirectID 
        from TmpPERS p
            join ISZL.T_INSURED_POLICY_RECORD ipr on ipr.InsuredPolicyRecordID = p.InsuredPolicyRecordID
            join ISZL.T_DISP_DIRECT dd on dd.Insured = ipr.Insured
            join ISZL.T_ORGANIZATION orgmo on orgmo.OrganizationID = dd.Mo
                    and
                dd.YEAR = p.YEAR
                    and
                dd.MONTH_Q = p.MONTH
                    and
                dd.V016Dispt = p.V016
                    and
                orgmo.Code = p.CODE_MO
        where p.InsuredPolicyRecordID is not null
                and
            p.TYPE = 2
                and
            dd.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 1);

        -- Отвязываем факт
        update ISZL.T_DISP_DIRECT_FACT ddf
        set DispDirect = null
        where ddf.DispDirect in 
            (
                select fd.DispDirectID
                from forDelete fd
            );

        -- Удаляем информирование
        delete from ISZL.T_DISP_DIRECT_INFO ddi
        where ddi.DispDirect in 
            (
                select fd.DispDirectID
                from forDelete fd
            );

        -- Удаляем опросы
        delete from ISZL.T_DISP_DIRECT_INTERVIEW ddi
        where ddi.DispDirect in 
            (
                select fd.DispDirectID
                from forDelete fd
            );

        -- Удаляем историю статусов
        delete from ISZL.T_DISP_DIRECT_STATUS dds
        where dds.DispDirect in 
            (
                select fd.DispDirectID
                from forDelete fd
            );

        -- Удаляем контакты этой записи ДН
        delete from ISZL.T_PATIENT_CONTACT pc
        where pc.DispDirect in 
            (
                select fd.DispDirectID
                from forDelete fd
            );

        -- Сами записи ДН
        delete from ISZL.T_DISP_DIRECT dd
        where dd.DispDirectID in
            (
                select fd.DispDirectID
                from forDelete fd
            );
            

        --------------------
        INSERT INTO iszl.T_INSURED_CONTACT(InsuredContactID, Contact, ContactType, Insured)
        select gen_random_uuid(), con.CONTACT
        , (select ct.ContactTypeID from iszl.T_CONTACT_TYPE ct where ct.Name = con.TYPE limit 1)
        , ipr.Insured
        from TmpCONTACTS con
        inner join
            TmpPERS p
            on con.ID_PAC = p.ID_PAC
        inner join
            iszl.T_INSURED_POLICY_RECORD ipr
            on ipr.InsuredPolicyRecordID = p.InsuredPolicyRecordID
        where  p.TYPE = 0 
        and not exists(select* from iszl.T_INSURED_CONTACT c where c.Contact = con.CONTACT and c.Insured = ipr.Insured);

    end if;

    return query
    select (case 
               when (select COUNT(*) from TmpErrors) = 0 then false
               else true
            end)::boolean as issinglexmlflk,
    '<?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 as err,
    ('FLK_' + "FileName" + '.xml')::text as filename,
    'FLK'::text as targetdirectory,
    null::text as codepage,
    '//COMMENT'::text as errordescrxpath;

end;
$function$;
