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,
                                          error)
    SELECT gen_random_uuid(),
           'Не удалось подобрать вид льготы!',
           "dateTime",
           "Xml",
           NULL,
           'BENEFITS',
           0,
           'false',
           NULL,
           'Не удалось подобрать вид льготы!'
    FROM tmp_benefits nb
    WHERE nb.benefitviewsenum IS NULL
    LIMIT 1;

END;
$$;