DROP PROCEDURE IF EXISTS DBO.sp_updatebenefitsfromrds;

CREATE OR REPLACE PROCEDURE APS.sp_updatebenefitsfromrds
(
	"Xml" xml DEFAULT NULL::xml
,	"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())', x))[1]::varchar(255)    as BenefitsID,
            (xpath('(BENEFIT/BenefitCode[1]/text())', x))[1]::varchar(255)   as BenefitCode,
            (xpath('(BENEFIT/BenefitName[1]/text())', x))[1]::varchar(255)   as BenefitName,
            (xpath('(BENEFIT/Actual[1]/text())', x))[1]::text::boolean       as Actual,
            (xpath('(BENEFIT/Private[1]/text())', x))[1]::text::boolean      as Private,
            (xpath('(BENEFIT/PercentPay[1]/text())', x))[1]::text::int       as PercentPay,
            (xpath('(BENEFIT/BenefitViews[1]/text())', x))[1]::text::int     as BenefitViews,
            (xpath('(BENEFIT/BenefitType[1]/text())', x))[1]::text::int      as BenefitType,
            (xpath('(BENEFIT/CloseType[1]/text())', x))[1]::text::int        as CloseType,
            (xpath('(BENEFIT/PeriodOfAct[1]/text())', x))[1]::text::int      as PeriodOfAct,
            (xpath('(BENEFIT/Duration[1]/text())', 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")) as x
                ) BENEFIT
                left join VCLib.T_ENUMS bView on bView.EnumTypeID = 190
                        and
                    bView.Code = (xpath('BENEFIT/BenefitViews[1]/text()', x))[1]::text::int
                left join VCLib.T_ENUMS bType on bType.EnumTypeID = 242
                        and
                    bType.Code = (xpath('(BENEFIT/BenefitType[1]/text()', 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.IdEHR = (xpath('BENEFIT/EHRRUS1U0GD15H2K[1]/text()', x))[1]::text::int 
                        and 
                        RRB.CURRVERSION = (xpath('BENEFIT/EHRRUS1U0GD15H2KVer[1]/text()', 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()', x))[1]::varchar(255) 
                        and 
                        RRB.CURRVERSION = (xpath('BENEFIT/EHRRUSSDO8U3N5ROVer[1]/text()', 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 ehr1.IdEHR = (xpath('BENEFIT/EhrRusVzn438[1]/text()', x))[1]::text::int
                        and
                        RRB.CURRVERSION = (xpath('BENEFIT/EhrRusVzn438Ver[1]/text()', x))[1]::varchar(255) 
                    limit 1
		        ) eh3 on true;


    -- Переключаем в неактуальные те льготы, которых нету в XML
    update aps.t_benefits b
    set b.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
            b.BenefitName = nb.BenefitName,
            b.Actual = nb.Actual,
            b.PercentPay = coalesce(nb.PercentPay,100),
            --b.Private = isnull(nb.Private, b.Private),
            b.BenefitType = nb.BenefitTypeEnum,
            b.EhrRus1u0gd15h2k = nb.EhrRus1u0gd15h2k,
            b.EhrRusSdo8u3n5ro = nb.EhrRusSdo8u3n5ro,
            b.EhrRusVzn438 = nb.EhrRusVzn438,
            b.CloseType = nb.CloseType,
            b.PeriodOfAct = nb.PeriodOfAct,
            b.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
            NEWID(),
            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(),
        'Не удалось подобрать вид льготы!',
        CAST("Xml" AS NVARCHAR(MAX)),
        NULL,
        'BENEFITS',
        0,
        0,
        NULL
    FROM Tmp_benefits nb
    WHERE nb.BenefitViewsEnum IS NULL limit 1;

    DROP TABLE if exists Tmp_benefits;
End;
$$;