DROP PROCEDURE IF EXISTS DBO.sp_updatebenefitsfromrds;

DROP PROCEDURE IF EXISTS APS.sp_updatebenefitsfromrds;

CREATE OR REPLACE PROCEDURE APS.sp_updatebenefitsfromrds
(
	"Xml" text DEFAULT NULL
,	"QueueName" character varying DEFAULT 'RDSSync'::character varying
,	"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
)
    language plpgsql
as
$$
Begin
       "dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

        Drop Table If Exists Tmp_benefits;
		Create Temp Table Tmp_benefits
        as
        -- Получаем данные из XML по таблице Benefits
        SELECT
             (xpath('BENEFIT/BenefitsID[1]/text()', t.x))[1]::varchar(255) as BenefitsID
            ,(xpath('BENEFIT/BenefitCode[1]/text()', t.x))[1]::varchar(255) as BenefitCode
            ,(xpath('BENEFIT/BenefitName[1]/text()', t.x))[1]::varchar(255) as BenefitName
            ,(xpath('BENEFIT/Actual[1]/text()', t.x))[1]::text::boolean as Actual
            ,(xpath('BENEFIT/Private[1]/text()', t.x))[1]::text::boolean as Private
            ,(xpath('BENEFIT/PercentPay[1]/text()', t.x))[1]::text::int as PercentPay
            ,(xpath('BENEFIT/BenefitViews[1]/text()', t.x))[1]::text::int as BenefitViews
            ,(xpath('BENEFIT/BenefitType[1]/text()', t.x))[1]::text::int as BenefitType
            ,(xpath('BENEFIT/CloseType[1]/text()', t.x))[1]::text::int as CloseType
            ,(xpath('BENEFIT/PeriodOfAct[1]/text()', t.x))[1]::text::int as PeriodOfAct
            ,(xpath('BENEFIT/Duration[1]/text()', t.x))[1]::text::int as Duration
            ,bView.EnumID as BenefitViewsEnum
            ,bType.EnumID as BenefitTypeEnum
            ,eh1.EhrRusID as EhrRus1u0gd15h2k
            ,eh2.EhrRusID as EhrRusSdo8u3n5ro
            ,eh3.EhrRusID as EhrRusVzn438
            from
			(
				select unnest(xpath('/BENEFITS/BENEFIT', "Xml"::xml)) as x
			) t
            left join VCLib.T_ENUMS bView on bView.EnumTypeID = 190 and bView.Code = (xpath('BENEFIT/BenefitViews[1]/text()', t.x))[1]::text::int
            left join VCLib.T_ENUMS bType on bType.EnumTypeID = 242 and bType.Code = (xpath('BENEFIT/BenefitType[1]/text()', t.x))[1]::text::int
            left join lateral (
		    	select n1.EhrRusID
		    	from EHR.T_EHR_RUS_1U0GD15H2K n1
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = n1.EhrRusRefBooks
				where n1.CodeEHR = (xpath('BENEFIT/EHRRUS1U0GD15H2K[1]/text()', t.x))[1]::varchar(255) and RRB.CURRVERSION = (xpath('BENEFIT/EHRRUS1U0GD15H2KVer[1]/text()', t.x))[1]::varchar(255) 
                limit 1
		    ) eh1 on true
		    left join lateral (
		    	select n1.EhrRusID
		    	from EHR.T_EHR_RUS_SDO8U3N5RO n1
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = n1.EhrRusRefBooks
				where n1.CodeEHR = (xpath('BENEFIT/EHRRUSSDO8U3N5RO[1]/text()', t.x))[1]::varchar(255) and RRB.CURRVERSION = (xpath('BENEFIT/EHRRUSSDO8U3N5ROVer[1]/text()', t.x))[1]::varchar(255) 
                limit 1
		    ) eh2 on true
		    left join lateral (
		    	select ehr1.EhrRusID
		    	from EHR.T_EHR_RUS_VZN438 ehr1
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr1.EhrRusRefBooks
				where RRB.CURRVERSION = (xpath('BENEFIT/EhrRusVzn438Ver[1]/text()', t.x))[1]::varchar(255) and ehr1.IdEHR = (xpath('BENEFIT/EhrRusVzn438[1]/text()', t.x))[1]::text::int 
                limit 1
		    ) eh3 on true;

    -- Переключаем в неактуальные те льготы, которых нету в XML
    update aps.t_benefits b
    set Actual = '0'
    where not exists
    (
        select *
        from Tmp_benefits nb
        where nb.BenefitCode = b.BenefitCode
                and
            nb.BenefitViewsEnum = b.BenefitViews
    );

    -- Обновляем имеющиеся льготы
    UPDATE aps.t_benefits b
        SET
            BenefitName = nb.BenefitName,
            Actual = nb.Actual,
            PercentPay = coalesce(nb.PercentPay,100),
            --Private = coalesce(nb.Private, Private),
            BenefitType = nb.BenefitTypeEnum,
            EhrRus1u0gd15h2k = nb.EhrRus1u0gd15h2k,
            EhrRusSdo8u3n5ro = nb.EhrRusSdo8u3n5ro,
            EhrRusVzn438 = nb.EhrRusVzn438,
            CloseType = nb.CloseType,
            PeriodOfAct = nb.PeriodOfAct,
            Duration = nb.Duration
        FROM Tmp_benefits nb 
        where nb.BenefitCode = b.BenefitCode  AND  nb.BenefitViewsEnum = b.BenefitViews;

    -- Загружаем новые льготы
    INSERT INTO APS.T_BENEFITS (BenefitsID, BenefitViews, BenefitCode, BenefitName, Actual, PercentPay, RecipeValidationLexem, BenefitType, EhrRus1u0gd15h2k, EhrRusSdo8u3n5ro, CloseType, PeriodOfAct, Duration, EhrRusVzn438)
        SELECT
            gen_random_uuid(),
            nb.BenefitViewsEnum,
            nb.BenefitCode,
            nb.BenefitName,
            nb.Actual,
            coalesce(nb.PercentPay,100),
            NULL,
            nb.BenefitTypeEnum,
            nb.EhrRus1u0gd15h2k,
            nb.EhrRusSdo8u3n5ro,
            nb.CloseType,
            nb.PeriodOfAct,
            nb.Duration,
			nb.EhrRusVzn438
        FROM Tmp_benefits nb
            where not exists
            (
				SELECT * 
                FROM aps.t_benefits b
				WHERE 
					nb.BenefitCode = b.BenefitCode
					AND 
                    nb.BenefitViewsEnum = b.BenefitViews
            );

    -- Логируем записи с ошибками
    INSERT INTO APS.T_RDS_SYNCHRONISATION_ERRORS (RdsSynchronisationErrorsID, ErrorDescription, DateTime, Contents, Users, PackageType, ErrorType, ProcessedError, LockResource)
    SELECT 
        gen_random_uuid(),
        'Не удалось подобрать вид льготы!',
        "dateTime",
        "Xml",
        NULL,
        'BENEFITS',
        0,
        'false',
        NULL
    FROM Tmp_benefits nb
    WHERE nb.BenefitViewsEnum IS NULL limit 1;

    DROP TABLE if exists Tmp_benefits;
End;
$$;