CREATE OR REPLACE PROCEDURE public.sp_bskdirection(
    "Xml" text DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT 'RDSSync',
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml"::xml;
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    v_result          text;
    v_operationtype   varchar(6);
    v_contentencoding varchar(255);
    v_type            varchar(255);
    v_ownerid         uuid;
    v_registerid      int;
    v_changetype      int;
    c_insert          varchar(6)   := 'Insert';
    c_update          varchar(6)   := 'Update';
    c_delete          varchar(6)   := 'Delete';
BEGIN

    SELECT (XPATH('/BSK_DIRECTION/RegisterID/text()', x))[1]::varchar(10)::int AS registerid,
           (XPATH('/BSK_DIRECTION/ChangeType/text()', x))[1]::varchar(10)::int AS changetype,
           'utf-8',
           'BSK_DIRECTION'
    INTO v_registerid,
        v_changetype,
        v_contentencoding,
        v_type
    FROM UNNEST(XPATH('/BSK_DIRECTION', p_xml)) x;

    DROP TABLE IF EXISTS temp_bsk_direction;
    CREATE TEMP TABLE temp_bsk_direction AS
        SELECT (XPATH('/BSK_DIRECTION/ID/text()', x))[1]::text::uuid                      AS id,
               (XPATH('/BSK_DIRECTION/LPU/text()', x))[1]::varchar(6)                     AS lpu,
               TO_TIMESTAMP((XPATH('/BSK_DIRECTION/EnterDate/text()', x))[1]::text, 'DD.MM.YYYY')          AS enterdate,
               (XPATH('/BSK_DIRECTION/LastName/text()', x))[1]::varchar(40)               AS lastname,
               (XPATH('/BSK_DIRECTION/FirstName/text()', x))[1]::varchar(40)              AS firstname,
               (XPATH('/BSK_DIRECTION/FatherName/text()', x))[1]::varchar(40)             AS fathername,
               TO_TIMESTAMP((XPATH('/BSK_DIRECTION/Birthday/text()', x))[1]::text, 'DD.MM.YYYY')          AS birthday,
               TO_TIMESTAMP((XPATH('/BSK_DIRECTION/Date/text()', x))[1]::text, 'DD.MM.YYYY')              AS date,
               (XPATH('/BSK_DIRECTION/Sex/text()', x))[1]::varchar(10)::int               AS sex,
               (XPATH('/BSK_DIRECTION/Kod/text()', x))[1]::varchar(32)                    AS kod,
               (XPATH('/BSK_DIRECTION/DocSeries/text()', x))[1]::varchar(16)              AS docseries,
               (XPATH('/BSK_DIRECTION/DocNumber/text()', x))[1]::varchar(16)              AS docnumber,
               (XPATH('/BSK_DIRECTION/DocType/text()', x))[1]::varchar(10)::int           AS doctype,
               (XPATH('/BSK_DIRECTION/RegisterID/text()', x))[1]::varchar(10)::int        AS registerid,
               (XPATH('/BSK_DIRECTION/Snils/text()', x))[1]::varchar(14)                  AS snils,
               (XPATH('/BSK_DIRECTION/ChangeType/text()', x))[1]::varchar(10)::int        AS changetype,
               (XPATH('/BSK_DIRECTION/ExcludeReason/text()', x))[1]::varchar(10)::int     AS excludereason,
               TO_TIMESTAMP((XPATH('/BSK_DIRECTION/CreateDateTime/text()', x))[1]::text, 'YYYY-MM-DD HH24:MI:SS')     AS createdatetime,
               (XPATH('/BSK_DIRECTION/BirthSurname/text()', x))[1]::varchar(40)           AS birthsurname,
               TO_TIMESTAMP((XPATH('/BSK_DIRECTION/DocIssueDate/text()', x))[1]::text, 'DD.MM.YYYY')       AS docissuedate,
               (XPATH('/BSK_DIRECTION/Enp/text()', x))[1]::varchar(16)                    AS enp,
               (XPATH('/BSK_DIRECTION/DocIssueOrg/text()', x))[1]::varchar(256)           AS docissueorg,
               (XPATH('/BSK_DIRECTION/RealFiasAddress/text()', x))[1]::varchar(36)        AS realfiasaddress,
               (XPATH('/BSK_DIRECTION/RealFiasHome/text()', x))[1]::varchar(36)           AS realfiashome,
               (XPATH('/BSK_DIRECTION/RealCity/text()', x))[1]::varchar(100)              AS realcity,
               (XPATH('/BSK_DIRECTION/RealCityPrefix/text()', x))[1]::varchar(10)         AS realcityprefix,
               (XPATH('/BSK_DIRECTION/RealStreet/text()', x))[1]::varchar(100)            AS realstreet,
               (XPATH('/BSK_DIRECTION/RealStreetPrefix/text()', x))[1]::varchar(10)       AS realstreetprefix,
               (XPATH('/BSK_DIRECTION/RealHouse/text()', x))[1]::varchar(100)             AS realhouse,
               (XPATH('/BSK_DIRECTION/RealBuild/text()', x))[1]::varchar(100)             AS realbuild,
               (XPATH('/BSK_DIRECTION/RealStruc/text()', x))[1]::varchar(100)             AS realstruc,
               (XPATH('/BSK_DIRECTION/RealRoom/text()', x))[1]::varchar(100)              AS realroom,
               TO_TIMESTAMP((XPATH('/BSK_DIRECTION/RealLiveStartDate/text()', x))[1]::text, 'DD.MM.YYYY')  AS reallivestartdate,
               TO_TIMESTAMP((XPATH('/BSK_DIRECTION/RealLiveEndDate/text()', x))[1]::text, 'DD.MM.YYYY')    AS realliveenddate,
               (XPATH('/BSK_DIRECTION/LegalFiasAddress/text()', x))[1]::varchar(36)       AS legalfiasaddress,
               (XPATH('/BSK_DIRECTION/LegalFiasHome/text()', x))[1]::varchar(36)          AS legalfiashome,
               (XPATH('/BSK_DIRECTION/LegalCity/text()', x))[1]::varchar(100)             AS legalcity,
               (XPATH('/BSK_DIRECTION/LegalCityPrefix/text()', x))[1]::varchar(10)        AS legalcityprefix,
               (XPATH('/BSK_DIRECTION/LegalStreet/text()', x))[1]::varchar(100)           AS legalstreet,
               (XPATH('/BSK_DIRECTION/LegalStreetPrefix/text()', x))[1]::varchar(10)      AS legalstreetprefix,
               (XPATH('/BSK_DIRECTION/LegalHouse/text()', x))[1]::varchar(100)            AS legalhouse,
               (XPATH('/BSK_DIRECTION/LegalBuild/text()', x))[1]::varchar(100)            AS legalbuild,
               (XPATH('/BSK_DIRECTION/LegalStruc/text()', x))[1]::varchar(100)            AS legalstruc,
               (XPATH('/BSK_DIRECTION/LegalRoom/text()', x))[1]::varchar(100)             AS legalroom,
               TO_TIMESTAMP((XPATH('/BSK_DIRECTION/LegalLiveStartDate/text()', x))[1]::text, 'DD.MM.YYYY') AS legallivestartdate,
               TO_TIMESTAMP((XPATH('/BSK_DIRECTION/LegalLiveEndDate/text()', x))[1]::text, 'DD.MM.YYYY')   AS legalliveenddate,
               (XPATH('/BSK_DIRECTION/CitizenShip/text()', x))[1]::varchar(3)             AS citizenship
        FROM UNNEST(XPATH('/BSK_DIRECTION', p_xml)) x;

    DROP TABLE IF EXISTS temp_bsk_benefits;
    CREATE TEMP TABLE temp_bsk_benefits AS
    SELECT
        (XPATH('/BSK_DIRECTION_BENEFIT/DirectionID/text()', x))[1]::VARCHAR(36)::uuid                      AS directionid,
        (XPATH('/BSK_DIRECTION_BENEFIT/DirectionBenID/text()', x))[1]::VARCHAR(36)::uuid                   AS directionbenid,
        TO_TIMESTAMP((XPATH('/BSK_DIRECTION_BENEFIT/DiagDate/text()', x))[1]::TEXT, 'DD.MM.YYYY')          AS diagdate,
        (XPATH('/BSK_DIRECTION_BENEFIT/Document/text()', x))[1]::VARCHAR(16)                               AS document,
        TO_TIMESTAMP((XPATH('/BSK_DIRECTION_BENEFIT/DocumentDate/text()', x))[1]::TEXT,
                     'DD.MM.YYYY')                                                                         AS documentdate,
        (XPATH('/BSK_DIRECTION_BENEFIT/Mkb/text()', x))[1]::VARCHAR(8)                                     AS mkb,
        (XPATH('/BSK_DIRECTION_BENEFIT/BenefitCode/text()', x))[1]::VARCHAR(255)                           AS benefitcode,
        (XPATH('/BSK_DIRECTION_BENEFIT/BenefitViews/text()', x))[1]::VARCHAR(10)::INT                      AS benefitviews,
        (XPATH('/BSK_DIRECTION_BENEFIT/PostCode/text()', x))[1]::VARCHAR(255)                              AS postcode,
        (XPATH('/BSK_DIRECTION_BENEFIT/ChangeType/text()', x))[1]::VARCHAR(10)::INT                        AS changetype,
        (XPATH('/BSK_DIRECTION_BENEFIT/ServiceCode/text()', x))[1]::VARCHAR(20)                            AS servicecode,
        (XPATH('/BSK_DIRECTION_BENEFIT/ServiceName/text()', x))[1]::VARCHAR(255)                           AS servicename,
        TO_TIMESTAMP((XPATH('/BSK_DIRECTION_BENEFIT/DateBegin/text()', x))[1]::TEXT, 'DD.MM.YYYY')         AS datebegin,
        TO_TIMESTAMP((XPATH('/BSK_DIRECTION_BENEFIT/DateEnd/text()', x))[1]::TEXT, 'DD.MM.YYYY')           AS dateend,
        (XPATH('/BSK_DIRECTION_BENEFIT/ExclusionReason/text()', x))[1]::VARCHAR(10)::INT                   AS exclusionreason,
        TO_TIMESTAMP((XPATH('/BSK_DIRECTION_BENEFIT/EstimatedDDate/text()', x))[1]::TEXT,
                     'DD.MM.YYYY')                                                                         AS estimatedddate,
        TO_TIMESTAMP((XPATH('/BSK_DIRECTION_BENEFIT/DRegistrationDate/text()', x))[1]::TEXT,
                     'DD.MM.YYYY')                                                                         AS dregistrationdate,
        (XPATH('/BSK_DIRECTION_BENEFIT/Place/text()', x))[1]::VARCHAR(10)::INT                             AS place,
        (XPATH('/BSK_DIRECTION_BENEFIT/LpuTo/text()', x))[1]::VARCHAR(6)                                   AS lputo,
        TO_TIMESTAMP((XPATH('/BSK_DIRECTION_BENEFIT/CloseDate/text()', x))[1]::TEXT, 'DD.MM.YYYY')         AS closedate,
        (XPATH('/BSK_DIRECTION_BENEFIT/ConcomitantMkb/text()', x))[1]::VARCHAR(255)                        AS concomitantmkb,
        (XPATH('/BSK_DIRECTION_BENEFIT/ContomitantDisease/text()', x))[1]::VARCHAR(255)                    AS contomitantdisease
    FROM UNNEST(XPATH('/BSK_DIRECTION/BSK_DIRECTION_BENEFITS/BSK_DIRECTION_BENEFIT', p_xml)) x;

    DROP TABLE IF EXISTS temp_medicaments;
    CREATE TEMP TABLE temp_medicaments AS
    SELECT
        (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/DirectionBenId/text()', x))[1]::VARCHAR(36)::uuid  AS directionbenid,
        (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/Mnn/text()', x))[1]::VARCHAR(10)::INT              AS mnn,
        TO_TIMESTAMP((XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/DateStart/text()', x))[1]::TEXT,
                     'DD.MM.YYYY')                                                                    AS datestart,
        TO_TIMESTAMP((XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/EndDate/text()', x))[1]::TEXT,
                     'DD.MM.YYYY')                                                                    AS enddate,
        (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/MedicationForms/text()', x))[1]::VARCHAR(36)::uuid AS medicationforms,
        (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/Regiment/text()', x))[1]::VARCHAR(255)             AS regiment,
        (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/Dose/text()', x))[1]::TEXT::DECIMAL(31, 10)        AS dose,
        (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/Prepid/text()', x))[1]::VARCHAR(10)::INT           AS prepid,
        (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/DayliCoeff/text()', x))[1]::TEXT::DECIMAL(31, 10)  AS daylicoeff,
        (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/MeasureUnit/text()', x))[1]::VARCHAR(255)          AS measureunit,
        (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/MeasureUnitType/text()', x))[1]::VARCHAR(10)::INT  AS measureunittype,
        (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/SingleDose/text()', x))[1]::TEXT::DECIMAL(31, 10)  AS singledose,
        (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/Multiplicity/text()', x))[1]::VARCHAR(10)::INT     AS multiplicity,
        (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/DoseView/text()', x))[1]::VARCHAR(255)             AS doseview,
        (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/DoseViewType/text()', x))[1]::VARCHAR(10)::INT     AS doseviewtype
        FROM UNNEST(XPATH(
            '/BSK_DIRECTION/BSK_DIRECTION_BENEFITS/BSK_DIRECTION_BENEFIT/BSK_BENEFITS_DIRECTION_MEDICAMENTS/BSK_BENEFITS_DIRECTION_MEDICAMENT',
            p_xml)) x;

    DROP TABLE IF EXISTS temp_complications;
    CREATE TEMP TABLE temp_complications AS
    SELECT
        (XPATH('/BSK_REGISTER_COMPLICATION_LINK/DirectionBenId/text()', x))[1]::VARCHAR(36)::uuid  AS directionbenid,
        (XPATH('/BSK_REGISTER_COMPLICATION_LINK/ComplicationID/text()', x))[1]::VARCHAR(36)::uuid  AS complicationid,
        (XPATH('/BSK_REGISTER_COMPLICATION_LINK/ComplicationType/text()', x))[1]::VARCHAR(10)::INT AS complicationtype,
        TO_TIMESTAMP((XPATH('/BSK_REGISTER_COMPLICATION_LINK/RegistrationDate/text()', x))[1]::TEXT,
                     'DD.MM.YYYY')                                                                 AS registrationdate,
        (XPATH('/BSK_REGISTER_COMPLICATION_LINK/MKBCode/text()', x))[1]::VARCHAR(255)              AS mkbcode,
        (XPATH('/BSK_REGISTER_COMPLICATION_LINK/Diagnosis/text()', x))[1]::VARCHAR(255)            AS diagnosis,
        (XPATH('/BSK_REGISTER_COMPLICATION_LINK/EhrRusR6JR8PINUS/text()', x))[1]::VARCHAR(10)::INT AS ehrrusr6jr8pinus,
        (XPATH('/BSK_REGISTER_COMPLICATION_LINK/EhrRusR6JR8PINUSVer/text()', x))[1]::VARCHAR(8)    AS ehrrusr6jr8pinusver,
        (XPATH('/BSK_REGISTER_COMPLICATION_LINK/EhrRusIN6SHID9F6/text()', x))[1]::VARCHAR(10)::INT AS ehrrusin6shid9f6,
        (XPATH('/BSK_REGISTER_COMPLICATION_LINK/EhrRusIN6SHID9F6Ver/text()', x))[1]::VARCHAR(8)    AS ehrrusin6shid9f6ver,
        (XPATH('/BSK_REGISTER_COMPLICATION_LINK/EhrRusJEDS208EN8/text()', x))[1]::VARCHAR(10)::INT AS ehrrusjeds208en8,
        (XPATH('/BSK_REGISTER_COMPLICATION_LINK/EhrRusJEDS208EN8Ver/text()', x))[1]::VARCHAR(8)    AS ehrrusjeds208en8ver,
        (XPATH('/BSK_REGISTER_COMPLICATION_LINK/LeftVentricularEjectionFraction/text()',
               x))[1]::VARCHAR(10)::INT                                                            AS leftventricularejectionfraction,
        TO_TIMESTAMP((XPATH('/BSK_REGISTER_COMPLICATION_LINK/FractionRegistrationDate/text()',
                            x))[1]::TEXT,
                     'DD.MM.YYYY')                                                                 AS fractionregistrationdate
    FROM UNNEST(XPATH(
        '/BSK_DIRECTION/BSK_DIRECTION_BENEFITS/BSK_DIRECTION_BENEFIT/BSK_REGISTER_COMPLICATION_LINKS/BSK_REGISTER_COMPLICATION_LINK',
        p_xml)) x;

    IF (v_registerid IS NULL AND v_changetype <> 1) THEN

        UPDATE bskd
        SET registerid = br.pac_id
        FROM temp_bsk_direction bskd
        JOIN rds.t_bsk_register br ON bskd.snils = br.snils
        WHERE bskd.registerid IS NULL
          AND bskd.snils IS NOT NULL
          AND bskd.snils <> ''
          AND bskd.snils <> '              '
          AND ((br.dateend IS NULL OR br.dateend > p_datetime) AND br.excludereason IS NULL)
        RETURNING br.pac_id
            INTO v_registerid;

        -- Поиск по серии и номеру документа
        UPDATE bskd
        SET registerid = br.pac_id
        FROM temp_bsk_direction bskd
        JOIN rds.t_bsk_register br ON bskd.docseries = br.docseries AND bskd.docnumber = br.docnumber
        WHERE bskd.registerid IS NULL
          AND bskd.docseries IS NOT NULL
          AND bskd.docnumber IS NOT NULL
          AND bskd.docseries <> ''
          AND bskd.docnumber <> ''
          AND ((br.dateend IS NULL OR br.dateend > p_datetime) AND br.excludereason IS NULL)
        RETURNING br.pac_id
            INTO v_registerid;

        -- Поиск по ФИО + ДР
        UPDATE bskd
        SET registerid = br.pac_id
        FROM temp_bsk_direction bskd
        JOIN rds.t_bsk_register br ON bskd.lastname = br.lastname AND bskd.firstname = br.firstname AND
                                      (bskd.fathername = br.fathername OR
                                       (bskd.fathername IS NULL AND br.fathername IS NULL)) AND
                                      bskd.birthday = br.birthday
        WHERE bskd.registerid IS NULL
          AND ((br.dateend IS NULL OR br.dateend > p_datetime) AND br.excludereason IS NULL)
        RETURNING br.pac_id
            INTO v_registerid;

    ELSIF (v_registerid IS NULL AND v_changetype = 1) THEN

        CALL public.sp_generateidforentity('BSK_REGISTER', 'PrimaryKey', 1, v_registerid);

        UPDATE temp_bsk_direction SET registerid = v_registerid;

        IF (v_registerid = 0) THEN
            RAISE EXCEPTION 'Не верно сгенерирован идентификатор льготника в регистре!';
            RETURN;
        END IF;
    END IF;

    UPDATE rds.t_bsk_register rp
    SET lastname           = p.lastname,
        firstname          = p.firstname,
        fathername         = p.fathername,
        birthday           = p.birthday,
        kod                = p.kod,
        docseries          = p.docseries,
        docnumber          = p.docnumber,
        sex                = esex.enumid,
        snils              = p.snils,
        doctype            = edoc.enumid,
        lpumain            = p.lpu,
        lasteditdate       = p_datetime,
        docissuedate       = p.docissuedate,
        docissueorg        = p.docissueorg,
        enp                = p.enp,
        realfiasaddress    = p.realfiasaddress,
        realfiashome       = p.realfiashome,
        realcity           = p.realcity,
        realcityprefix     = p.realcityprefix,
        realstreet         = p.realstreet,
        realstreetprefix   = p.realstreetprefix,
        realhouse          = p.realhouse,
        realbuild          = p.realbuild,
        realstruc          = p.realstruc,
        realroom           = p.realroom,
        reallivestartdate  = p.reallivestartdate,
        realliveenddate    = p.realliveenddate,
        legalfiasaddress   = p.legalfiasaddress,
        legalfiashome      = p.legalfiashome,
        legalcity          = p.legalcity,
        legalcityprefix    = p.legalcityprefix,
        legalstreet        = p.legalstreet,
        legalstreetprefix  = p.legalstreetprefix,
        legalhouse         = p.legalhouse,
        legalbuild         = p.legalbuild,
        legalstruc         = p.legalstruc,
        legalroom          = p.legalroom,
        legallivestartdate = p.legallivestartdate,
        legalliveenddate   = p.legalliveenddate,
        citizenship        = cs.ehrrusid,
        ehrrus5o5e88vhc6   = dul.ehrrus5o5e88vhc6
    FROM temp_bsk_direction               p
    LEFT JOIN vclib.t_enums               esex ON esex.enumtypeid = 103 AND esex.code = p.sex
    LEFT JOIN vclib.t_enums               edoc ON edoc.enumtypeid = 105 AND edoc.code = p.doctype
    LEFT JOIN ehr.t_ehr_rus_o00015        cs ON cs.code = p.citizenship
    LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = edoc.enumid
    WHERE p.registerid = rp.pac_id
      AND p.changetype = 2;

    UPDATE rds.t_bsk_register rp
    SET lasteditdate  = p_datetime,
        dateend       = p.enterdate,
        excludereason = p.excludereason
    FROM temp_bsk_direction p
    WHERE p.registerid = rp.pac_id
      AND p.changetype = 3;

    UPDATE rds.t_bsk_register_data brd
    SET closedate       = p_datetime,
        lasteditdate    = p_datetime,
        exclusionreason = CASE
                              WHEN p.excludereason = 0 THEN 2
                              WHEN p.excludereason = 1 THEN 0
                              WHEN p.excludereason = 2 THEN 3
                          END
    FROM rds.t_bsk_register rp
    JOIN temp_bsk_direction p ON p.registerid = rp.pac_id
    WHERE rp.bskregisterid = brd.bskregister
      AND p.changetype = 3
      AND brd.closedate IS NULL;

    INSERT INTO
        rds.t_bsk_register
    (bskregisterid,
     excludereason,
     lasteditdate,
     lastname,
     firstname,
     fathername,
     birthday,
     kod,
     docseries,
     docnumber,
     pac_id,
     sex,
     snils,
     doctype,
     lpumain,
     datebegin,
     dateend,
     docissuedate,
     docissueorg,
     enp,
     realfiasaddress,
     realfiashome,
     realcity,
     realcityprefix,
     realstreet,
     realstreetprefix,
     realhouse,
     realbuild,
     realstruc,
     realroom,
     reallivestartdate,
     realliveenddate,
     legalfiasaddress,
     legalfiashome,
     legalcity,
     legalcityprefix,
     legalstreet,
     legalstreetprefix,
     legalhouse,
     legalbuild,
     legalstruc,
     legalroom,
     legallivestartdate,
     legalliveenddate,
     citizenship,
     ehrrus5o5e88vhc6)
    SELECT gen_random_uuid(),
           excludereason,
           p_datetime,
           lastname,
           firstname,
           fathername,
           birthday,
           kod,
           docseries,
           docnumber,
           registerid,
           esex.enumid,
           snils,
           edoc.enumid,
           lpu,
           p_datetime,
           NULL,
           d.docissuedate,
           d.docissueorg,
           d.enp,
           realfiasaddress,
           realfiashome,
           realcity,
           realcityprefix,
           realstreet,
           realstreetprefix,
           realhouse,
           realbuild,
           realstruc,
           realroom,
           reallivestartdate,
           realliveenddate,
           legalfiasaddress,
           legalfiashome,
           legalcity,
           legalcityprefix,
           legalstreet,
           legalstreetprefix,
           legalhouse,
           legalbuild,
           legalstruc,
           legalroom,
           legallivestartdate,
           legalliveenddate,
           cs.ehrrusid,
           dul.ehrrus5o5e88vhc6
    FROM temp_bsk_direction               d
    LEFT JOIN vclib.t_enums               esex ON esex.enumtypeid = 103 AND esex.code = d.sex
    LEFT JOIN vclib.t_enums               edoc ON edoc.enumtypeid = 105 AND edoc.code = d.doctype
    LEFT JOIN ehr.t_ehr_rus_o00015        cs ON cs.code = d.citizenship
    LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = edoc.enumid
    WHERE d.changetype = 1;

    INSERT INTO
        rds.t_bsk_register_data
    (bskregisterdataid,
     bskregister,
     servicecode,
     servicename,
     diagdate,
     document,
     documentdate,
     mkb,
     benefits,
     begindate,
     enddate,
     exclusionreason,
     estimatedddate,
     dregistrationdate,
     place,
     lpumainto,
     closedate,
     lpumain,
     createdatetime,
     lasteditdate,
     concomitantmkb,
     contomitantdisease)
    SELECT bd.directionbenid
         , r.bskregisterid
         , servicecode
         , servicename
         , diagdate
         , document
         , documentdate
         , mkb
         , b.benefitsid
         , COALESCE(bd.datebegin, p_datetime)
         , COALESCE(
        bd.dateend,
        CASE
            WHEN b.duration IS NOT NULL AND b.periodofact IS NOT NULL AND b.closetype IS NOT NULL THEN
                CASE
                    WHEN b.closetype = 0 AND b.periodofact = 0 THEN brr.birthday + (b.duration || ' days')::INTERVAL
                    WHEN b.closetype = 0 AND b.periodofact = 1 THEN brr.birthday + (b.duration || ' months')::INTERVAL
                    WHEN b.closetype = 0 AND b.periodofact = 2 THEN brr.birthday + (b.duration || ' years')::INTERVAL
                    WHEN b.closetype = 1 AND b.periodofact = 0 THEN COALESCE(bd.datebegin, p_datetime) +
                                                                    (b.duration || ' days')::INTERVAL
                    WHEN b.closetype = 1 AND b.periodofact = 1 THEN COALESCE(bd.datebegin, p_datetime) +
                                                                    (b.duration || ' months')::INTERVAL
                    WHEN b.closetype = 1 AND b.periodofact = 2 THEN COALESCE(bd.datebegin, p_datetime) +
                                                                    (b.duration || ' years')::INTERVAL
                    ELSE NULL
                END
            ELSE NULL
        END
           )
         , bd.exclusionreason
         , bd.estimatedddate
         , bd.dregistrationdate
         , bd.place
         , bd.lputo
         , bd.closedate
         , brr.lpu
         , COALESCE(brr.createdatetime, p_datetime)
         , p_datetime
         , bd.concomitantmkb
         , bd.contomitantdisease
    FROM temp_bsk_benefits       bd
    JOIN      temp_bsk_direction brr ON brr.id = bd.directionid
    JOIN      rds.t_bsk_register r ON r.pac_id = v_registerid
    LEFT JOIN vclib.t_enums      e ON e.enumtypeid = 190 AND e.code = bd.benefitviews
    LEFT JOIN rds.t_benefits     b ON b.benefitcode = bd.benefitcode AND b.benefitviews = e.enumid
    WHERE bd.changetype = 1;

    UPDATE rds.t_bsk_register_data rpb
    SET dregistrationdate = rdb.dregistrationdate,
        lasteditdate      = p_datetime
    FROM temp_bsk_benefits       rdb
    JOIN      rds.t_bsk_register r ON r.pac_id = v_registerid
    LEFT JOIN vclib.t_enums      e ON e.enumtypeid = 190 AND e.code = rdb.benefitviews
    LEFT JOIN rds.t_benefits     b ON b.benefitcode = rdb.benefitcode AND b.benefitviews = e.enumid
    WHERE rpb.bskregister = r.bskregisterid
      AND rpb.benefits = b.benefitsid
      AND rdb.changetype = 2
      AND (rpb.enddate IS NULL OR rpb.enddate > p_datetime)
      AND rpb.closedate IS NULL;

    INSERT INTO
        rds.t_bsk_register_medicaments
    (bskregistermedicamentsid,
     bskregisterdata,
     cmnn,
     datestart,
     enddate,
     medicationforms,
     regiment,
     dose,
     measureunit,
     prepid,
     daylicoeff,
     singledose,
     multiplicity,
     doseview)
    SELECT gen_random_uuid(),
           ben.directionbenid,
           (SELECT cmnnid FROM pharm.t_c_mnn WHERE otherid = meds.mnn LIMIT 1),
           meds.datestart,
           meds.enddate,
           meds.medicationforms,
           meds.regiment,
           meds.dose,
           (SELECT dw.code
            FROM pharm.t_dose_view dw
            WHERE dw.name = meds.measureunit
              AND dw.viewtype = meds.measureunittype
            LIMIT 1
           ),
           meds.prepid,
           meds.daylicoeff,
           meds.singledose,
           meds.multiplicity,
           (SELECT dw.code
            FROM pharm.t_dose_view dw
            WHERE dw.name = meds.doseview
              AND dw.viewtype = meds.doseviewtype
            LIMIT 1
           )
    FROM temp_medicaments  meds
    JOIN temp_bsk_benefits ben ON ben.directionbenid = meds.directionbenid
    WHERE ben.changetype = 1;


    INSERT INTO
        rds.t_bsk_register_complication_link (bskregistercomplicationlinkid,
                                              bskregisterdata,
                                              complicationtype,
                                              registrationdate,
                                              mkbcode,
                                              diagnosis,
                                              type_)
    SELECT compl.complicationid,
           ben.directionbenid,
           compl.complicationtype,
           compl.registrationdate,
           compl.mkbcode,
           compl.diagnosis,
           (SELECT e.entityid FROM vclib.t_entities e WHERE e.entname = 'BSK_REGISTER_CHRONIC_HEART_FAILURE' LIMIT 1)
    FROM temp_complications compl
    JOIN temp_bsk_benefits  ben ON ben.directionbenid = compl.directionbenid
    WHERE ben.changetype = 1;

    INSERT INTO
        rds.t_bsk_register_complication_link
    (bskregistercomplicationlinkid, bskregisterdata, complicationtype, registrationdate, mkbcode, diagnosis, type_)
    SELECT compl.complicationid,
           ben.directionbenid,
           compl.complicationtype,
           compl.registrationdate,
           compl.mkbcode,
           compl.diagnosis,
           (SELECT e.entityid FROM vclib.t_entities e WHERE e.entname = 'BSK_REGISTER_CHRONIC_HEART_FAILURE' LIMIT 1)
    FROM temp_complications     compl
    JOIN      temp_bsk_benefits ben ON ben.directionbenid = compl.directionbenid
    LEFT JOIN LATERAL ( SELECT er.ehrrusid
                        FROM ehr.t_ehr_rus_r6jr8pinus     er
                        LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                        WHERE er.idehr = compl.ehrrusr6jr8pinus
                          AND rrb.currversion = compl.ehrrusr6jr8pinusver
                        LIMIT 1
                  )             ehr1 ON TRUE
    LEFT JOIN LATERAL ( SELECT er.ehrrusid
                        FROM ehr.t_ehr_rus_in6shid9f6     er
                        LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                        WHERE er.idehr = compl.ehrrusin6shid9f6
                          AND rrb.currversion = compl.ehrrusin6shid9f6ver
                        LIMIT 1
                  )             ehr2 ON TRUE
    LEFT JOIN LATERAL ( SELECT er.ehrrusid
                        FROM ehr.t_ehr_rus_jeds208en8     er
                        LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                        WHERE er.idehr = compl.ehrrusjeds208en8
                          AND rrb.currversion = compl.ehrrusjeds208en8ver
                        LIMIT 1
                  )             ehr3 ON TRUE
    WHERE ben.changetype = 1;

    SELECT XMLELEMENT
           (
               NAME "BSK_REGISTER",
               XMLFOREST
               (
                   rd.id AS "ID",
                   rd.registerid AS "RegisterID"
               )
           )::text
    INTO v_result
    FROM temp_bsk_direction rd;

    WITH header_data AS (SELECT XMLELEMENT(
                                    NAME "header",
                                    XMLFOREST(v_operationtype AS "TYPE")
                                ) AS header_xml
                        ),
         patient_data AS (SELECT XMLAGG(
                                     XMLELEMENT(
                                         NAME "PATIENT",
                                         XMLFOREST(
                                             rp.pac_id AS "RegisterID",
                                             rp.lastname AS "LastName",
                                             rp.firstname AS "FirstName",
                                             rp.fathername AS "FatherName",
                                             TO_CHAR(rp.birthday, 'DD.MM.YYYY') AS "Birthday",
                                             rp.kod AS "Kod",
                                             rp.docseries AS "DocSeries",
                                             rp.docnumber AS "DocNumber",
                                             esex.code AS "Sex",
                                             rp.snils AS "Snils",
                                             TO_CHAR(rp.datebegin, 'DD.MM.YYYY') AS "DateBegin",
                                             TO_CHAR(rp.dateend, 'DD.MM.YYYY') AS "DateEnd",
                                             TO_CHAR(rp.lasteditdate, 'DD.MM.YYYY') AS "LastEditDate",
                                             rp.lpumain AS "Lpu",
                                             rp.excludereason AS "ExcludeReason",
                                             edoc.code AS "DocType",
                                             TO_CHAR(rp.docissuedate, 'DD.MM.YYYY') AS "DocIssueDate",
                                             rp.docissueorg AS "DocIssueOrg",
                                             rp.enp AS "Enp",
                                             rp.realfiasaddress AS "RealFiasAddress",
                                             rp.realfiashome AS "RealFiasHome",
                                             rp.realcity AS "RealCity",
                                             rp.realcityprefix AS "RealCityPrefix",
                                             rp.realstreet AS "RealStreet",
                                             rp.realstreetprefix AS "RealStreetPrefix",
                                             rp.realhouse AS "RealHouse",
                                             rp.realbuild AS "RealBuild",
                                             rp.realstruc AS "RealStruc",
                                             rp.realroom AS "RealRoom",
                                             TO_CHAR(rp.reallivestartdate, 'DD.MM.YYYY') AS "RealLiveStartDate",
                                             TO_CHAR(rp.realliveenddate, 'DD.MM.YYYY') AS "RealLiveEndDate",
                                             rp.legalfiasaddress AS "LegalFiasAddress",
                                             rp.legalfiashome AS "LegalFiasHome",
                                             rp.legalcity AS "LegalCity",
                                             rp.legalcityprefix AS "LegalCityPrefix",
                                             rp.legalstreet AS "LegalStreet",
                                             rp.legalstreetprefix AS "LegalStreetPrefix",
                                             rp.legalhouse AS "LegalHouse",
                                             rp.legalbuild AS "LegalBuild",
                                             rp.legalstruc AS "LegalStruc",
                                             rp.legalroom AS "LegalRoom",
                                             TO_CHAR(rp.legallivestartdate, 'DD.MM.YYYY') AS "LegalLiveStartDate",
                                             TO_CHAR(rp.legalliveenddate, 'DD.MM.YYYY') AS "LegalLiveEndDate",
                                             cs.code AS "CitizenShip",
                                             doc.codeehr AS "EhrRus5o5e88vhc6",
                                             rrb.currversion AS "EhrRus5o5e88vhc6Ver"
                                         ),
                                         (SELECT XMLAGG(
                                                     XMLELEMENT(
                                                         NAME "BENEFITS",
                                                         XMLFOREST(
                                                             rp.pac_id AS "RegisterID",
                                                             rpb.bskregisterdataid AS "BenID",
                                                             TO_CHAR(rpb.diagdate, 'DD.MM.YYYY') AS "DiagDate",
                                                             rpb.document AS "Document",
                                                             TO_CHAR(rpb.documentdate, 'DD.MM.YYYY') AS "DocumentDate",
                                                             rpb.mkb AS "Mkb",
                                                             b.benefitcode AS "BenefitCode",
                                                             e.code AS "BenefitViews",
                                                             TO_CHAR(rpb.begindate, 'DD.MM.YYYY') AS "BeginDate",
                                                             TO_CHAR(rpb.enddate, 'DD.MM.YYYY') AS "EndDate",
                                                             rpb.servicecode AS "ServiceCode",
                                                             rpb.servicename AS "ServiceName",
                                                             rpb.exclusionreason AS "ExclusionReason",
                                                             TO_CHAR(rpb.estimatedddate, 'DD.MM.YYYY') AS
                                                             "EstimatedDDate",
                                                             TO_CHAR(rpb.dregistrationdate, 'DD.MM.YYYY') AS
                                                             "DRegistrationDate",
                                                             rpb.place AS "Place",
                                                             rpb.lpumainto AS "LpuTo",
                                                             rpb.lpumain AS "Lpu",
                                                             TO_CHAR(rpb.closedate, 'DD.MM.YYYY') AS "CloseDate"
                                                         ),
                                                         (SELECT XMLAGG(
                                                                     XMLELEMENT(
                                                                         NAME "MEDICAMENTS",
                                                                         XMLFOREST(
                                                                             meds.bskregisterdata AS "BenID",
                                                                             mnn.otherid AS "Mnn",
                                                                             TO_CHAR(meds.datestart, 'DD.MM.YYYY') AS
                                                                             "DateStart",
                                                                             TO_CHAR(meds.enddate, 'DD.MM.YYYY') AS
                                                                             "EndDate",
                                                                             meds.medicationforms AS "MedicationForms",
                                                                             meds.regiment AS "Regiment",
                                                                             meds.dose AS "Dose",
                                                                             meds.prepid AS "Prepid",
                                                                             meds.daylicoeff AS "DayliCoeff",
                                                                             dv.name AS "MeasureUnit",
                                                                             dv.viewtype AS "MeasureUnitType",
                                                                             meds.singledose AS "SingleDose",
                                                                             meds.multiplicity AS "Multiplicity",
                                                                             dvdv.name AS "DoseViewUnit",
                                                                             dvdv.viewtype AS "DoseViewUnitType"
                                                                         )
                                                                     )
                                                                 )
                                                          FROM rds.t_bsk_register_medicaments meds
                                                          JOIN      pharm.t_c_mnn             mnn ON mnn.cmnnid = meds.cmnn
                                                          LEFT JOIN pharm.t_dose_view         dv ON dv.code = meds.measureunit
                                                          LEFT JOIN pharm.t_dose_view         dvdv ON dvdv.code = meds.doseview
                                                          WHERE meds.bskregisterdata = rpb.bskregisterdataid
                                                         )
                                                     )
                                                 )
                                          FROM rds.t_bsk_register_data rpb
                                          JOIN rds.t_benefits          b ON b.benefitsid = rpb.benefits
                                          JOIN vclib.t_enums           e ON e.enumid = b.benefitviews
                                          WHERE rpb.bskregister = rp.bskregisterid
                                         )
                                     )
                                 ) AS patient_xml
                          FROM rds.t_bsk_register rp
                          JOIN      vclib.t_enums esex ON esex.enumid = rp.sex
                          LEFT JOIN vclib.t_enums edoc ON edoc.enumid = rp.doctype
                          LEFT JOIN ehr.t_ehr_rus_o00015     cs  ON cs.ehrrusid = rp.citizenship
                          LEFT JOIN ehr.t_ehr_rus_5o5e88vhc6 doc ON doc.ehrrusid = rp.ehrrus5o5e88vhc6
                          LEFT JOIN ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = doc.ehrrusrefbooks
                          WHERE rp.pac_id = v_registerid
                        ),
         body_data AS (SELECT XMLELEMENT(
                                  NAME "body",
                                  (SELECT patient_xml FROM patient_data)
                              ) AS body_xml
                        )
    SELECT XMLELEMENT(
               NAME "BSK_REGISTER",
               (SELECT header_xml FROM header_data),
               (SELECT body_xml FROM body_data)
           )::text
    INTO p_xml;


    INSERT INTO
        rds.t_rabbit_send_object
    (rabbitsendobjectid,
     queuename,
     content,
     messageid,
     headers,
     contentencoding,
     type,
     userid,
     state,
     createdatetime,
     ownerid)
    SELECT gen_random_uuid(),
           s.servername,
           public.converttexttobinaryzip(v_result, ''),
           NULL,
           NULL,
           v_contentencoding,
           v_type,
           p_user,
           0,
           p_datetime,
           v_ownerid
    FROM rds.t_sync_servers s
    WHERE s.servername = p_queuename
      AND s.servertype = 0
      AND v_result IS NOT NULL;


    INSERT INTO
        rds.t_rabbit_send_object
    (rabbitsendobjectid,
     queuename,
     content,
     messageid,
     headers,
     contentencoding,
     type,
     userid,
     state,
     createdatetime,
     ownerid)
    SELECT gen_random_uuid(),
           s.servername,
           public.converttexttobinaryzip(p_xml::text, ''),
           NULL,
           NULL,
           v_contentencoding,
           v_type,
           p_user,
           0,
           p_datetime,
           v_ownerid
    FROM rds.t_sync_servers s
    WHERE s.servertype = 1;

    UPDATE public.t_entity_ids
    SET id = (SELECT MAX(b.pac_id) + 1 FROM rds.t_bsk_register b)
    WHERE entname IN ('BSK_REGISTER', 'RDS.BSK_REGISTER');
END;
$$;