DROP FUNCTION if exists sp_InformingDn;
drop procedure if exists sp_InformingDn;

CREATE procedure sp_InformingDn
(
    "FileID" uuid default null::uuid,
    "Worker" uuid default null::uuid,
    x xml default null::xml
,	inout "Res" boolean default null::boolean
,	inout "Err" text default null::text
)
LANGUAGE plpgsql
AS $procedure$
declare 
    _VERSION varchar(5);
    _FILENAME varchar(50);
    _SMO varchar(5);
    _InfoFile uuid;
begin
    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;
    
    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
    ,    null::uuid as INSURED -- заглушка    
    ,    null::uuid as ActualInsuredPolicyRecordID
    ,    _SMO as SMO -- заглушка
    ,    null::uuid as MkbId
    ,    null::int as DuplicateCount
    from
    (
        select unnest(xpath('/INFORMING_DN/ZL', x)) as z
    ) t1;

   	create temp table TmpDNDS as
	SELECT xmltable.*
	FROM (SELECT cast("x" as xml) as xml_doc_column),
	XMLTABLE ('/INFORMING_DN/ZL/DNDS' PASSING xml_doc_column
	COLUMNS                  
	  enp varchar(16) PATH '../ENP' NOT NULL
	, dn_ds varchar(10) PATH './DS' not NULL
	);
   
    update TmpZL
    set MkbId = (
        select mkb.mkbid
        from ISZL.T_MKB mkb
        where mkb.MCBCode = TmpZL.DS and mkb.EndCode is null
        limit 1
    );

        --step 0
    update TmpZL t1
    set INSURED = 
    (
        select t2.InsuredID from ISZL.T_INSURED t2
        where t2.ENP = t1.ENP
        limit 1
    ),
    ActualInsuredPolicyRecordID = 
    (
        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
        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
        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
        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;

    update TmpZL
    set DuplicateCount = 
        (select count(*)
	    from TmpZL z
	    join iszl.T_DN_INFO di on
		    di.Insured = z.INSURED and
		    di.Smo = z.SMO and
		    di.INFO_TYPE = z.TIP_INFO and
		    di.INFO_DATE = z.DATA_INF
	    where z.INSURED is not null and di.Insured is not null)    
    where INSURED is not null;
    
    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;

    insert into TmpErrors
    select 0, 'Код мкб (' || DS || ') не найден в справочнике МКБ. NPOLIS (' || NPOLIS || ')'
    from TmpZL
    where MkbId is null;

    insert into TmpErrors
    select 2, 'Уже существует информирование с указанными параметрами. NPOLIS(' || NPOLIS || ')'
    from TmpZL
    where DuplicateCount > 0;
    
 if exists(select* from TmpErrors)
then
	create temp table temp_table(Res boolean,Err text);
   	insert into temp_table(Res,Err)
        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;
   	select Res from temp_table into "Res";
	select Err from temp_table into "Err";

	drop table if exists temp_table;
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
                call 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, FileName)
        select gen_random_uuid(), t1.F_INF, t1.DATA_INF, t1.TIP_INFO, t1.N_INF, t1.SMO, t1.INSURED, _FILENAME
        from TmpZL t1
        where t1.INSURED IS NOT NULL AND t1.DuplicateCount <= 0;

 -- добавляем в коллекцию с новой загрузкой диагнозов
  if (select count(*) from TmpDNDS) > 0 then
    insert into iszl.t_dn_info_diagnosis (
      dninfodiagnosisid,
      mkb,
      dninfo
    )
    select
      gen_random_uuid(),
      mkb.mkbid,
      zl.itemid
    from TmpDNDS as dn
    join tmpzl as zl on dn.enp = zl.enp
    join iszl.t_mkb as mkb on mkb.mcbcode = dn.dn_ds
    where zl.insured is not null
    and zl.duplicatecount <= 0;
  end if;
 
-- добавляем в коллекцию по старой загрузке (с учетом того, чтоы не дублировать записи)
  insert into iszl.t_dn_info_diagnosis
				(
					dninfodiagnosisid,
					mkb,
					dninfo
				)
			select
      			gen_random_uuid(),
				zl.mkbid,
				zl.itemid
			from TmpZL as zl
			where zl.insured is not null
			and zl.duplicatecount <= 0
			and not exists (select dninfo from iszl.t_dn_info_diagnosis where dninfo = zl.itemid);
       
drop table TmpZL;
drop table TmpZGLV;
drop table TmpErrors;
drop table TmpDNDS;

end;
$procedure$;
