DROP PROCEDURE IF EXISTS sp_rr_direction;

CREATE OR REPLACE PROCEDURE public.sp_rr_direction(
    "Xml" text DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT 'RDSSync',
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL,
    "Verification" boolean 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";
    p_verification    boolean      := "Verification";
    v_result          text ;
    v_operationtype   varchar(6);
    v_contentencoding varchar(255);
    v_type            varchar(255) := 'RR_DIRECTION';
    v_ownerid         uuid ;
    v_rrpatient       int;
    v_vzn             boolean;
    v_error           TEXT;

BEGIN

    SELECT (XPATH('/RR_DIRECTION/TYPE/text()', x))[1]::varchar(6)            AS type,
           (XPATH('/header/ID/text()', t))[1]::varchar(36)                   AS ownerid,
           (XPATH('/RR_DIRECTION/RrPatient/text()', x))[1]::varchar(10)::int AS rrpatient
    INTO v_operationtype, v_ownerid, v_rrpatient
    FROM UNNEST(XPATH('/RR_DIRECTION', p_xml))                     x
    LEFT JOIN LATERAL UNNEST(XPATH('/RR_DIRECTION/header', p_xml)) t ON TRUE;

    DROP TABLE IF EXISTS temp_rr_direction;
    CREATE TEMP TABLE temp_rr_direction AS
    SELECT
        (XPATH('/RR_DIRECTION/ID/text()', x))[1]::VARCHAR(36)::uuid                                AS id,
        (XPATH('/RR_DIRECTION/LPU/text()', x))[1]::VARCHAR(6)                                      AS lpu,
        TO_TIMESTAMP((XPATH('/RR_DIRECTION/EnterDate/text()', x))[1]::TEXT, 'DD.MM.YYYY')          AS enterdate,
        (XPATH('/RR_DIRECTION/LastName/text()', x))[1]::VARCHAR(40)                                AS lastname,
        (XPATH('/RR_DIRECTION/FirstName/text()', x))[1]::VARCHAR(40)                               AS firstname,
        (XPATH('/RR_DIRECTION/FatherName/text()', x))[1]::VARCHAR(40)                              AS fathername,
        TO_TIMESTAMP((XPATH('/RR_DIRECTION/Birthday/text()', x))[1]::TEXT, 'DD.MM.YYYY')           AS birthday,
        (XPATH('/RR_DIRECTION/Sex/text()', x))[1]::VARCHAR(255)                                    AS sex,
        (XPATH('/RR_DIRECTION/DocSeries/text()', x))[1]::VARCHAR(16)                               AS docseries,
        (XPATH('/RR_DIRECTION/DocNumber/text()', x))[1]::VARCHAR(16)                               AS docnumber,
        (XPATH('/RR_DIRECTION/RrPatient/text()', x))[1]::VARCHAR(10)::INT                          AS rrpatient,
        (XPATH('/RR_DIRECTION/SNILS/text()', x))[1]::VARCHAR(14)                                   AS snils,
        (XPATH('/RR_DIRECTION/ExcludeReason/text()', x))[1]::VARCHAR(10)::INT                      AS excludereason,
        (XPATH('/RR_DIRECTION/RrPatient/text()', x))[1]::VARCHAR(10)::INT                          AS realrrpatient,
        (XPATH('/RR_DIRECTION/KodAdr/text()', x))[1]::VARCHAR(20)                                  AS kodadr,
        (XPATH('/RR_DIRECTION/DocType/text()', x))[1]::VARCHAR(10)::INT                            AS doctype,
        (XPATH('/RR_DIRECTION/BirthSurname/text()', x))[1]::VARCHAR(40)                            AS birthsurname,
        (XPATH('/RR_DIRECTION/LpuFirst/text()', x))[1]::VARCHAR(6)                                 AS lpufirst,
        (XPATH('/RR_DIRECTION/ChangeType/text()', x))[1]::VARCHAR(10)::INT                         AS changetype,
        (XPATH('/RR_DIRECTION/SOST_R/text()', x))[1]::VARCHAR(10)::INT                             AS sost_r,
        (XPATH('/RR_DIRECTION/Kod/text()', x))[1]::VARCHAR(32)                                     AS kod,
        TO_TIMESTAMP((XPATH('/RR_DIRECTION/DocIssueDate/text()', x))[1]::TEXT, 'DD.MM.YYYY')       AS docissuedate,
        (XPATH('/RR_DIRECTION/Enp/text()', x))[1]::VARCHAR(16)                                     AS enp,
        (XPATH('/RR_DIRECTION/DocIssueOrg/text()', x))[1]::VARCHAR(256)                            AS docissueorg,
        (XPATH('/RR_DIRECTION/RealFiasAddress/text()', x))[1]::VARCHAR(36)                         AS realfiasaddress,
        (XPATH('/RR_DIRECTION/RealFiasHome/text()', x))[1]::VARCHAR(36)                            AS realfiashome,
        (XPATH('/RR_DIRECTION/RealCity/text()', x))[1]::VARCHAR(100)                               AS realcity,
        (XPATH('/RR_DIRECTION/RealCityPrefix/text()', x))[1]::VARCHAR(10)                          AS realcityprefix,
        (XPATH('/RR_DIRECTION/RealStreet/text()', x))[1]::VARCHAR(100)                             AS realstreet,
        (XPATH('/RR_DIRECTION/RealStreetPrefix/text()', x))[1]::VARCHAR(10)                        AS realstreetprefix,
        (XPATH('/RR_DIRECTION/RealHouse/text()', x))[1]::VARCHAR(100)                              AS realhouse,
        (XPATH('/RR_DIRECTION/RealBuild/text()', x))[1]::VARCHAR(100)                              AS realbuild,
        (XPATH('/RR_DIRECTION/RealStruc/text()', x))[1]::VARCHAR(100)                              AS realstruc,
        (XPATH('/RR_DIRECTION/RealRoom/text()', x))[1]::VARCHAR(100)                               AS realroom,
        TO_TIMESTAMP((XPATH('/RR_DIRECTION/RealLiveStartDate/text()', x))[1]::TEXT, 'DD.MM.YYYY')  AS reallivestartdate,
        TO_TIMESTAMP((XPATH('/RR_DIRECTION/RealLiveEndDate/text()', x))[1]::TEXT, 'DD.MM.YYYY')    AS realliveenddate,
        (XPATH('/RR_DIRECTION/LegalFiasAddress/text()', x))[1]::VARCHAR(36)                        AS legalfiasaddress,
        (XPATH('/RR_DIRECTION/LegalFiasHome/text()', x))[1]::VARCHAR(36)                           AS legalfiashome,
        (XPATH('/RR_DIRECTION/LegalCity/text()', x))[1]::VARCHAR(100)                              AS legalcity,
        (XPATH('/RR_DIRECTION/LegalCityPrefix/text()', x))[1]::VARCHAR(10)                         AS legalcityprefix,
        (XPATH('/RR_DIRECTION/LegalStreet/text()', x))[1]::VARCHAR(100)                            AS legalstreet,
        (XPATH('/RR_DIRECTION/LegalStreetPrefix/text()', x))[1]::VARCHAR(10)                       AS legalstreetprefix,
        (XPATH('/RR_DIRECTION/LegalHouse/text()', x))[1]::VARCHAR(100)                             AS legalhouse,
        (XPATH('/RR_DIRECTION/LegalBuild/text()', x))[1]::VARCHAR(100)                             AS legalbuild,
        (XPATH('/RR_DIRECTION/LegalStruc/text()', x))[1]::VARCHAR(100)                             AS legalstruc,
        (XPATH('/RR_DIRECTION/LegalRoom/text()', x))[1]::VARCHAR(100)                              AS legalroom,
        TO_TIMESTAMP((XPATH('/RR_DIRECTION/LegalLiveStartDate/text()', x))[1]::TEXT,
                     'DD.MM.YYYY')                                                                 AS legallivestartdate,
        TO_TIMESTAMP((XPATH('/RR_DIRECTION/LegalLiveEndDate/text()', x))[1]::TEXT, 'DD.MM.YYYY')   AS legalliveenddate,
        (XPATH('/RR_DIRECTION/CitizenShip/text()', x))[1]::VARCHAR(3)                              AS citizenship
    FROM UNNEST(XPATH('/RR_DIRECTION', p_xml)) x;

    DROP TABLE IF EXISTS temp_rr_direction_benefit;
    CREATE TEMP TABLE temp_rr_direction_benefit AS
    SELECT
        (XPATH('/RR_DIRECTION_BENEFIT/DirectionID/text()', x))[1]::VARCHAR(36)::uuid                 AS rrdirection,
        TO_TIMESTAMP((XPATH('/RR_DIRECTION_BENEFIT/DiagDate/text()', x))[1]::TEXT, 'DD.MM.YYYY')     AS diagdate,
        (XPATH('/RR_DIRECTION_BENEFIT/Document/text()', x))[1]::VARCHAR(16)                          AS document,
        TO_TIMESTAMP((XPATH('/RR_DIRECTION_BENEFIT/DocumentDate/text()', x))[1]::TEXT, 'DD.MM.YYYY') AS documentdate,
        (XPATH('/RR_DIRECTION_BENEFIT/Dop/text()', x))[1]::VARCHAR(1)                                AS dop,
        (XPATH('/RR_DIRECTION_BENEFIT/Mkb/text()', x))[1]::VARCHAR(8)                                AS mkb,
        (XPATH('/RR_DIRECTION_BENEFIT/BenefitCode/text()', x))[1]::VARCHAR(255)                      AS benefitcode,
        (XPATH('/RR_DIRECTION_BENEFIT/BenefitViews/text()', x))[1]::VARCHAR(10)::INT                 AS benefitviews,
        (XPATH('/RR_DIRECTION_BENEFIT/PostCode/text()', x))[1]::VARCHAR(10)                          AS post,
        (XPATH('/RR_DIRECTION_BENEFIT/DivisionManager/text()', x))[1]::VARCHAR(10)                   AS divisionmanager,
        (XPATH('/RR_DIRECTION_BENEFIT/ChangeType/text()', x))[1]::VARCHAR(10)::INT                   AS changetype,
        0                                                                                            AS federalandnos
    FROM UNNEST(XPATH('/RR_DIRECTION/RR_DIRECTION_BENEFIT', p_xml)) x;

    UPDATE temp_rr_direction rd
    SET realrrpatient = rp.rrpatientid
    FROM rds.t_rr_patient rp
    WHERE rp.snils = rd.snils
      AND rd.realrrpatient IS NULL
      AND rd.snils IS NOT NULL
      AND rp.snils <> ''
      AND rp.snils <> '              '
      AND (rp.dateend IS NULL OR rp.dateend >= CURRENT_TIMESTAMP);
    
    UPDATE temp_rr_direction rd
    SET realrrpatient = rp.rrpatientid
    FROM rds.t_rr_patient rp
    WHERE rp.docseries = rd.docseries
      AND rp.docnumber = rd.docnumber
      AND rd.realrrpatient IS NULL
      AND rd.docseries IS NOT NULL
      AND rd.docnumber IS NOT NULL
      AND rd.docseries <> ''
      AND rd.docseries <> ''
      AND (rp.dateend IS NULL OR rp.dateend >= CURRENT_TIMESTAMP)
      AND rp.birthday = rd.birthday;
    
    UPDATE temp_rr_direction rd
    SET realrrpatient = rp.rrpatientid
    FROM rds.t_rr_patient rp
    WHERE rp.lastname = rd.lastname
      AND rp.firstname = rd.firstname
      AND (rp.fathername = rd.fathername OR (rp.fathername IS NULL AND rd.fathername IS NULL))
      AND rp.birthday = rd.birthday
      AND rd.realrrpatient IS NULL
      AND rp.birthday = rd.birthday;
	  
	  select realrrpatient
	  INTO v_rrpatient
	  from temp_rr_direction rd limit 1;

      
	--проверка льгот
	if (exists(select 1 from temp_rr_direction where rrpatient is not null and realrrpatient is not null and rrpatient <> realrrpatient))
	then
        RAISE EXCEPTION 'Id пациента в регистре в направлении не равен совпадает в Id пациента в регистре льготников!';
        return;
	end if;

    IF EXISTS (SELECT 1
               FROM temp_rr_direction_benefit rdb
               JOIN vclib.t_enums             e ON e.enumtypeid = 190 AND e.code = rdb.benefitviews
               JOIN rds.t_benefits            b ON b.benefitcode = rdb.benefitcode AND b.benefitviews = e.enumid
               JOIN temp_rr_direction         d ON d.id = rdb.rrdirection
               WHERE rdb.changetype = 1
                 AND EXISTS (SELECT 1
                             FROM rds.t_rr_patient_benefit db
                             JOIN rds.t_rr_patient         rrp ON rrp.id = db.rrpatient
                             WHERE rrp.rrpatientid = d.rrpatient
                               AND db.benefits = b.benefitsid
                               AND (db.enddate IS NULL OR db.enddate > CURRENT_DATE)
                               AND (b.private = 0 OR (b.private = 1 AND db.lpu_inf = CASE
                                                                                         WHEN LENGTH(d.lpu) > 5
                                                                                             THEN SUBSTRING(d.lpu FROM 3 FOR 4)
                                                                                         ELSE d.lpu
                                                                                     END))
                            )
              ) THEN
        RAISE EXCEPTION 'Льгота уже добавлена!';
        return;
    END IF;

    IF (p_verification = TRUE) THEN

        IF (EXISTS(SELECT *
                   FROM rds.t_nosology          n
                   JOIN rds.t_nosology_benefits nb ON nb.nosology = n.nosologyid
                   JOIN rds.t_benefits          b ON b.benefitsid = nb.benefits
                   JOIN temp_rr_direction       d ON d.snils = n.snils
                   WHERE b.benefitcode = '3'
                     AND d.snils IS NOT NULL
                     AND d.snils <> ''
                     AND d.snils <> '              '
                     AND n.snils IS NOT NULL
                     AND n.snils <> ''
                     AND n.snils <> '              '
                     AND n.birthday = d.birthday
                     AND n.lastname = d.lastname
                     AND n.firstname = d.firstname
                     AND ((d.fathername IS NULL AND n.fathername IS NULL) OR (d.fathername = n.fathername))
                  )) THEN

            v_vzn := TRUE;

        END IF;

        SELECT STRING_AGG('СНИЛС обязательное поле для пациентов старше 2х месяцев!', ' ')
        INTO v_error
        FROM temp_rr_direction_benefit rdb
        JOIN temp_rr_direction         rd ON rd.id = rdb.rrdirection
        JOIN vclib.t_enums             e ON e.enumtypeid = 190 AND e.code = rdb.benefitviews
        JOIN rds.t_benefits            b ON b.benefitcode = rdb.benefitcode AND b.benefitviews = e.enumid
        WHERE (rd.snils IS NULL OR rd.snils = '')
          AND rd.birthday + INTERVAL '2 months' <= CURRENT_DATE
          AND rd.changetype IN (1, 2)
          AND b.benefitcode <> '555';

        UPDATE temp_rr_direction_benefit rdb
        SET federalandnos = (CASE WHEN v_vzn = TRUE AND rdb.benefitcode = '26' THEN TRUE ELSE FALSE END)
        FROM temp_rr_direction rd
        WHERE rd.id = rdb.rrdirection
          AND rdb.changetype = 1
          AND EXISTS
            (SELECT 1
             FROM rds.t_fr_patient         fr
             CROSS JOIN LATERAL (SELECT *
                                 FROM rds.t_fr_record rec
                                 WHERE rec.snils = fr.snils
                                 ORDER BY rec.version DESC
                                 LIMIT 1 ) t
             WHERE fr.birthday = rd.birthday
               AND (fr.de_edv > CURRENT_TIMESTAMP OR fr.de_edv IS NULL OR fr.de_edv = '')
               AND t.deleted = FALSE
               AND (
                 (rd.snils IS NOT NULL AND fr.snils = rd.snils)
                     OR (rd.docseries IS NOT NULL AND rd.docnumber IS NOT NULL AND fr.docseries = rd.docseries AND
                         fr.docnumber = rd.docnumber)
                     OR (fr.lastname = rd.lastname AND fr.firstname = rd.firstname AND
                         (fr.fathername = fr.fathername OR (fr.fathername IS NULL AND rd.firstname IS NULL)) AND
                         ((rd.snils IS NULL AND fr.snils IS NULL) OR fr.snils = rd.snils))
                 )
            );

        SELECT CASE
                   WHEN (v_vzn = FALSE AND rdb.benefitcode = '26') THEN v_error
                   ELSE STRING_AGG(COALESCE(v_error, ' '), ' Пациент присутствует в федеральном регистре!')
               END
        INTO v_error
        FROM temp_rr_direction_benefit rdb
        JOIN temp_rr_direction         rd ON rd.id = rdb.rrdirection
        WHERE rdb.changetype = 1
          AND EXISTS
            (SELECT 1
             FROM rds.t_fr_patient         fr
             CROSS JOIN LATERAL (SELECT *
                                 FROM rds.t_fr_record rec
                                 WHERE rec.snils = fr.snils
                                 ORDER BY rec.version DESC
                                 LIMIT 1 ) t
             WHERE fr.birthday = rd.birthday
               AND (fr.de_edv > CURRENT_TIMESTAMP OR fr.de_edv IS NULL OR fr.de_edv = '')
               AND t.deleted = FALSE
               AND (
                 (rd.snils IS NOT NULL AND fr.snils = rd.snils)
                     OR (rd.docseries IS NOT NULL AND rd.docnumber IS NOT NULL AND fr.docseries = rd.docseries AND
                         fr.docnumber = rd.docnumber)
                     OR (fr.lastname = rd.lastname AND fr.firstname = rd.firstname AND
                         (fr.fathername = fr.fathername OR (fr.fathername IS NULL AND rd.firstname IS NULL)) AND
                         ((rd.snils IS NULL AND fr.snils IS NULL) OR fr.snils = rd.snils))
                 )
            );

        SELECT STRING_AGG(COALESCE(v_error, ' '), ' Не указан ID пациента(PAC_ID)!')
        INTO v_error
        FROM temp_rr_direction rd
        WHERE (rd.rrpatient IS NULL OR rd.rrpatient = 0)
          AND rd.changetype <> 1;

        SELECT STRING_AGG(COALESCE(v_error, ' '), ' PAC_ID должен быть пустой!')
        INTO v_error
        FROM temp_rr_direction rd
        WHERE (rd.rrpatient IS NOT NULL AND rd.rrpatient <> 0)
          AND rd.changetype = 1;

        SELECT STRING_AGG(COALESCE(v_error, ' '), COALESCE(CASE
                                                               WHEN ((rd.realrrpatient IS NOT NULL AND rd.rrpatient <> rd.realrrpatient))
                                                                   THEN ' Пациент не найден! ID пациента(PAC_ID): ' ||
                                                                        COALESCE(rd.rrpatient::varchar(20), '')
                                                           END, ''))
        INTO v_error
        FROM temp_rr_direction rd
        WHERE rd.realrrpatient IS NULL
          AND rd.changetype <> 1;

        SELECT STRING_AGG(COALESCE(v_error, ' '), ' Пациент не найден в базе!')
        INTO v_error
        FROM temp_rr_direction rd
        WHERE rd.realrrpatient IS NULL
          AND rd.changetype <> 1;


        SELECT STRING_AGG(COALESCE(v_error, ' '), ' Льгота от указаной МО не найдена !')
        INTO v_error
        FROM temp_rr_direction_benefit rdb
        JOIN temp_rr_direction         d ON d.id = rdb.rrdirection
        JOIN vclib.t_enums             e ON e.enumtypeid = 190 AND e.code = rdb.benefitviews
        JOIN rds.t_benefits            b ON b.benefitcode = rdb.benefitcode AND b.benefitviews = e.enumid
        WHERE rdb.changetype <> 1
          AND NOT EXISTS (SELECT 1
                          FROM rds.t_rr_patient_benefit pb
                          JOIN rds.t_rr_patient         rrp ON rrp.id = pb.rrpatient
                          WHERE b.benefitsid = pb.benefits
                            AND rrp.rrpatientid = d.rrpatient
                            AND pb.lpu_inf = CASE WHEN LENGTH(d.lpu) > 5 THEN SUBSTR(d.lpu, 3, 4) ELSE d.lpu END
                         );


        SELECT STRING_AGG(COALESCE(v_error, ' '), ' Льгота 95 не должна включаться в Региональный Регистр !')
        INTO v_error
        FROM temp_rr_direction_benefit rdb
        WHERE rdb.benefitcode = '95';

        SELECT STRING_AGG(COALESCE(v_error, ' '),
                          'Для человека старше 3х лет льгота 57(ребенок первых 3х лет жизни) не действует !')
        INTO v_error
        FROM temp_rr_direction_benefit rdb
        JOIN vclib.t_enums             e ON e.enumtypeid = 190 AND e.code = rdb.benefitviews
        JOIN rds.t_benefits            b ON b.benefitcode = rdb.benefitcode AND b.benefitviews = e.enumid
        JOIN temp_rr_direction         d ON d.id = rdb.rrdirection
        WHERE (b.benefitcode = '57' AND b.benefitcode = '057')
          AND e.code = 2
          AND (d.birthday + INTERVAL '3 years') < CURRENT_TIMESTAMP
          AND d.changetype IN (1, 2);


        IF (EXISTS
            (SELECT 1
             FROM rds.t_sickness_group      s
             JOIN rds.t_benefits            b ON b.benefitsid = s.benefits
             JOIN vclib.t_enums             e ON e.enumid = b.benefitviews
             JOIN temp_rr_direction_benefit rdb ON rdb.benefitcode = b.benefitcode AND e.code = rdb.benefitviews and rdb.ChangeType = 1
             LIMIT 1
            )) THEN

            SELECT STRING_AGG(COALESCE(v_error, ' '), ' Не правильный код МКБ для указанной льготы!')
            INTO v_error
            FROM temp_rr_direction_benefit rdb
            JOIN vclib.t_enums             e ON e.enumtypeid = 190 AND e.code = rdb.benefitviews
            JOIN rds.t_benefits            b ON b.benefitcode = rdb.benefitcode AND b.benefitviews = e.enumid
            WHERE e.code = 3
              AND NOT EXISTS (SELECT 1
                              FROM rds.t_sickness_group s
                              WHERE s.benefits = b.benefitsid
                                AND s.mkbk >= rdb.mkb
                                AND s.mkbn <= rdb.mkb
                             );

        END IF;


        IF (v_error IS NOT NULL)
        THEN
            RAISE EXCEPTION '%', v_error;
        return;
        END IF;

    END IF;

    IF (v_rrpatient IS NULL) THEN

        IF (EXISTS (SELECT 1 FROM temp_rr_direction WHERE changetype = 3)) THEN

            RAISE EXCEPTION 'Пациент не найден в регистре!';
        return;
        ELSE

            CALL public.sp_generateidforentity('RR_PATIENT', 'PrimaryKey', 1, v_rrpatient);

            UPDATE temp_rr_direction
            SET rrpatient = v_rrpatient;

            IF (v_rrpatient = 0) THEN

                RAISE EXCEPTION 'Не верно сгенерирован идентификатор льготника в регистре!';
        return;

            END IF;
        END IF;

    ELSE

        UPDATE temp_rr_direction
        SET rrpatient = v_rrpatient;

    END IF;

    UPDATE rds.t_rr_patient rp
    SET lastname           = p.lastname,
        firstname          = p.firstname,
        fathername         = p.fathername,
        birthday           = p.birthday,
        sex                = esex.enumid,
        lasteditdate       = p_datetime,
        lasteditlpu        = p.lpu,
        snils              = p.snils,
        docseries          = p.docseries,
        docnumber          = p.docnumber,
        lpu_inf            = CASE WHEN LENGTH(p.lpu) > 5 THEN SUBSTR(p.lpu, 3, 4) ELSE p.lpu END,
        kodadr             = p.kodadr,
        birthsurname       = p.birthsurname,
        lpumainfirst       = p.lpufirst,
        kod                = p.kod,
        tipd               = p.doctype,
        sost_r             = p.sost_r,
        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_rr_direction                p
    JOIN      vclib.t_enums               esex ON esex.enumtypeid = 103 AND esex.code = p.sex
    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 rp.rrpatientid = p.realrrpatient
      AND p.changetype IN (1, 2);

    INSERT INTO
        rds.t_rr_patient
    (rrpatientid,
     fathername,
     firstname,
     lastname,
     birthday,
     sex,
     snils,
     datebegin,
     dateend,
     lasteditdate,
     lasteditlpu,
     excludereason,
     docseries,
     docnumber,
     lpu_inf,
     kodadr,
     tipd,
     birthsurname,
     lpumainfirst,
     kod,
     sost_r,
     id,
     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 pd.rrpatient,
           pd.fathername,
           pd.firstname,
           pd.lastname,
           pd.birthday,
           esex.enumid,
           pd.snils,
           pd.enterdate,
           NULL,
           CURRENT_TIMESTAMP,
           pd.lpu,
           NULL,
           pd.docseries,
           pd.docnumber,
           CASE WHEN LENGTH(pd.lpu) > 5 THEN SUBSTRING(pd.lpu FROM 3 FOR 4) ELSE pd.lpu END,
           pd.kodadr,
           pd.doctype,
           pd.birthsurname,
           pd.lpufirst,
           pd.kod,
           pd.sost_r,
           gen_random_uuid(),
           pd.docissuedate,
           pd.docissueorg,
           pd.enp,
           pd.realfiasaddress,
           pd.realfiashome,
           pd.realcity,
           pd.realcityprefix,
           pd.realstreet,
           pd.realstreetprefix,
           pd.realhouse,
           pd.realbuild,
           pd.realstruc,
           pd.realroom,
           pd.reallivestartdate,
           pd.realliveenddate,
           pd.legalfiasaddress,
           pd.legalfiashome,
           pd.legalcity,
           pd.legalcityprefix,
           pd.legalstreet,
           pd.legalstreetprefix,
           pd.legalhouse,
           pd.legalbuild,
           pd.legalstruc,
           pd.legalroom,
           pd.legallivestartdate,
           pd.legalliveenddate,
           cs.ehrrusid,
           dul.ehrrus5o5e88vhc6
    FROM temp_rr_direction                pd
    LEFT JOIN vclib.t_enums               esex ON esex.enumtypeid = 103 AND esex.code = pd.sex
    LEFT JOIN vclib.t_enums               edoc ON edoc.enumtypeid = 105 AND edoc.code = pd.doctype
    LEFT JOIN ehr.t_ehr_rus_o00015        cs ON cs.code = pd.citizenship
    LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = edoc.enumid
    WHERE pd.changetype = 1
      AND NOT EXISTS (SELECT 1
                      FROM rds.t_rr_patient rp
                      WHERE rp.rrpatientid = pd.realrrpatient
                     );

    INSERT INTO
        rds.t_rr_direction
    (lastname,
     firstname,
     fathername,
     birthday,
     sex,
     rrdirectionid,
     rrpatient,
     excludereason,
     enterdate,
     date,
     lpu,
     changetype,
     snils,
     docseries,
     docnumber,
     lpu_inf,
     kodadr,
     tipd,
     birthsurname,
     lpumainfirst,
     kod,
     sost_r,
     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)
    SELECT pd.lastname,
           pd.firstname,
           pd.fathername,
           pd.birthday,
           esex.enumid,
           pd.id,
           rrp.id,
           pd.excludereason,
           pd.enterdate,
           pd.enterdate,
           pd.lpu,
           pd.changetype,
           pd.snils,
           pd.docseries,
           pd.docnumber,
           CASE WHEN LENGTH(pd.lpu) > 5 THEN SUBSTRING(pd.lpu FROM 3 FOR 4) ELSE pd.lpu END,
           pd.kodadr,
           pd.doctype,
           pd.birthsurname,
           pd.lpufirst,
           pd.kod,
           pd.sost_r,
           pd.docissuedate,
           pd.docissueorg,
           pd.enp,
           pd.realfiasaddress,
           pd.realfiashome,
           pd.realcity,
           pd.realcityprefix,
           pd.realstreet,
           pd.realstreetprefix,
           pd.realhouse,
           pd.realbuild,
           pd.realstruc,
           pd.realroom,
           pd.reallivestartdate,
           pd.realliveenddate,
           pd.legalfiasaddress,
           pd.legalfiashome,
           pd.legalcity,
           pd.legalcityprefix,
           pd.legalstreet,
           pd.legalstreetprefix,
           pd.legalhouse,
           pd.legalbuild,
           pd.legalstruc,
           pd.legalroom,
           pd.legallivestartdate,
           pd.legalliveenddate,
           cs.ehrrusid
    FROM temp_rr_direction         pd
    LEFT JOIN vclib.t_enums        esex ON esex.enumtypeid = 103 AND esex.code = pd.sex
    LEFT JOIN rds.t_rr_patient     rrp ON rrp.rrpatientid = pd.rrpatient
    LEFT JOIN ehr.t_ehr_rus_o00015 cs ON cs.code = pd.citizenship;

    INSERT INTO
        rds.t_rr_direction_benefit
    (rrdirectionbenefitid,
     rrdirection,
     diagdate,
     document,
     documentdate,
     dop,
     mkb,
     benefits,
     changetype,
     post,
     divisionmanager)
    SELECT gen_random_uuid(),
           rdb.rrdirection,
           rdb.diagdate,
           rdb.document,
           rdb.documentdate,
           rdb.dop,
           rdb.mkb,
           b.benefitsid,
           rdb.changetype,
           rdb.post,
           rdb.divisionmanager
    FROM temp_rr_direction_benefit rdb
    LEFT JOIN temp_rr_direction    d ON d.id = rdb.rrdirection
    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;

    UPDATE rds.t_rr_patient_benefit AS rpb
    SET enddate      = CASE
                           WHEN (p_verification = 1 AND (d.birthday + INTERVAL '2 months') > NOW() AND
                                 (rrp.snils IS NULL OR rrp.snils = '')) THEN
                               (d.birthday + INTERVAL '2 months') - INTERVAL '1 day'
                           WHEN (p_verification = 1 AND b.benefitcode = '57' AND e.code = 2 AND
                                 rrp.snils IS NOT NULL AND rrp.snils <> '') THEN
                               (d.birthday + INTERVAL '3 years') - INTERVAL '1 day'
                           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 d.birthday + (b.duration || ' days')::INTERVAL
                                   WHEN b.closetype = 0 AND b.periodofact = 1
                                       THEN d.birthday + (b.duration || ' months')::INTERVAL
                                   WHEN b.closetype = 0 AND b.periodofact = 2
                                       THEN d.birthday + (b.duration || ' years')::INTERVAL
                                   WHEN b.closetype = 1 AND b.periodofact = 0
                                       THEN d.enterdate + (b.duration || ' days')::INTERVAL
                                   WHEN b.closetype = 1 AND b.periodofact = 1
                                       THEN d.enterdate + (b.duration || ' months')::INTERVAL
                                   WHEN b.closetype = 1 AND b.periodofact = 2
                                       THEN d.enterdate + (b.duration || ' years')::INTERVAL
                                   ELSE NULL
                               END
                           ELSE NULL
                       END,
        lasteditdate = p_datetime,
        lasteditlpu  = d.lpu
    FROM temp_rr_direction_benefit AS rdb
    JOIN temp_rr_direction         AS d ON d.id = rdb.rrdirection
    JOIN vclib.t_enums             AS e ON e.enumtypeid = 190 AND e.code = rdb.benefitviews
    JOIN rds.t_benefits            AS b ON b.benefitcode = rdb.benefitcode AND b.benefitviews = e.enumid
    JOIN rds.t_rr_patient          AS rrp ON rrp.rrpatientid = d.rrpatient
    WHERE rdb.changetype = 1
      AND (rpb.enddate IS NULL OR rpb.enddate > d.enterdate)
      AND (b.private = 0 OR (b.private = 1 AND rpb.lpu_inf = CASE
                                                                 WHEN LENGTH(d.lpu) > 5
                                                                     THEN SUBSTRING(d.lpu FROM 3 FOR 4)
                                                                 ELSE d.lpu
                                                             END));

    INSERT INTO
        rds.t_rr_patient_benefit
    (rrpatientbenefitid,
     diagdate,
     document,
     documentdate,
     dop,
     mkb,
     benefits,
     rrpatient,
     begindate,
     enddate,
     lasteditdate,
     lasteditlpu,
     lpu_inf,
     post,
     divisionmanager)
    SELECT gen_random_uuid(),
           rdb.diagdate,
           rdb.document,
           rdb.documentdate,
           rdb.dop,
           rdb.mkb,
           b.benefitsid,
           rrp.id,
           d.enterdate,
           CASE
               WHEN (p_verification = 1 AND rdb.federalandnos = 1) THEN d.enterdate + INTERVAL '7 days'
               WHEN (p_verification = 1 AND (d.birthday + INTERVAL '2 months') > NOW() AND
                     (rrp.snils IS NULL OR rrp.snils = '')) THEN
                   (d.birthday + INTERVAL '2 months') - INTERVAL '1 day'
               WHEN (p_verification = 1 AND b.benefitcode = '57' AND e.code = 2 AND rrp.snils IS NOT NULL AND
                     rrp.snils <> '') THEN
                   (d.birthday + INTERVAL '3 years') - INTERVAL '1 day'
               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 d.birthday + (b.duration || ' days')::INTERVAL
                       WHEN b.closetype = 0 AND b.periodofact = 1 THEN d.birthday + (b.duration || ' months')::INTERVAL
                       WHEN b.closetype = 0 AND b.periodofact = 2 THEN d.birthday + (b.duration || ' years')::INTERVAL
                       WHEN b.closetype = 1 AND b.periodofact = 0 THEN d.enterdate + (b.duration || ' days')::INTERVAL
                       WHEN b.closetype = 1 AND b.periodofact = 1 THEN d.enterdate + (b.duration || ' months')::INTERVAL
                       WHEN b.closetype = 1 AND b.periodofact = 2 THEN d.enterdate + (b.duration || ' years')::INTERVAL
                       ELSE NULL
                   END
               ELSE NULL
           END,
           p_datetime,
           d.lpu,
           CASE WHEN LENGTH(d.lpu) > 5 THEN SUBSTRING(d.lpu FROM 3 FOR 4) ELSE d.lpu END,
           rdb.post,
           rdb.divisionmanager
    FROM temp_rr_direction_benefit AS rdb
    LEFT JOIN temp_rr_direction    AS d ON d.id = rdb.rrdirection
    LEFT JOIN rds.t_rr_patient     AS rrp ON rrp.rrpatientid = d.rrpatient
    LEFT JOIN vclib.t_enums        AS e ON e.enumtypeid = 190 AND e.code = rdb.benefitviews
    LEFT JOIN rds.t_benefits       AS b ON b.benefitcode = rdb.benefitcode AND b.benefitviews = e.enumid
    WHERE rdb.changetype = 1
      AND NOT EXISTS (SELECT 1
                      FROM rds.t_rr_patient_benefit AS rpb
                      WHERE rpb.rrpatient = rrp.id
                        AND rpb.benefits = b.benefitsid
                        AND (b.private = 0 OR (b.private = 1 AND rpb.lpu_inf = CASE
                                                                                   WHEN LENGTH(d.lpu) > 5
                                                                                       THEN SUBSTRING(d.lpu FROM 3 FOR 4)
                                                                                   ELSE d.lpu
                                                                               END))
                        AND (rpb.enddate IS NULL OR rpb.enddate >= NOW())
                     );

    UPDATE rds.t_rr_patient_benefit rpb
    SET enddate      = d.enterdate,
        lasteditdate = p_datetime,
        lasteditlpu  = d.lpu
    FROM temp_rr_direction_benefit AS rdb
    JOIN temp_rr_direction         AS d ON d.id = rdb.rrdirection
    JOIN vclib.t_enums             AS e ON e.enumtypeid = 190 AND e.code = rdb.benefitviews
    JOIN rds.t_benefits            AS b ON b.benefitcode = rdb.benefitcode AND b.benefitviews = e.enumid
    JOIN rds.t_rr_patient          AS rrp ON rrp.rrpatientid = d.rrpatient
    WHERE rdb.changetype = 3
      AND (rpb.enddate IS NULL OR rpb.enddate > d.enterdate)
      AND (b.private = 0 OR (b.private = 1 AND rpb.lpu_inf = CASE
                                                                 WHEN LENGTH(d.lpu) > 5
                                                                     THEN SUBSTRING(d.lpu FROM 3 FOR 4)
                                                                 ELSE d.lpu
                                                             END));

    UPDATE rds.t_rr_patient_benefit rpb
    SET enddate      = rrd.enterdate,
        lasteditdate = p_datetime,
        lasteditlpu  = rrd.lpu
    FROM temp_rr_direction AS rrd
    JOIN rds.t_rr_patient  AS rrp ON rrp.rrpatientid = rrd.realrrpatient
    WHERE rrd.changetype = 3
      AND (rpb.enddate IS NULL OR rpb.enddate > rrd.enterdate)
      AND EXISTS(SELECT *
                 FROM rds.t_benefits b
                 WHERE b.benefitsid = rpb.benefits
                   AND (b.private = 0 OR (b.private = 1 AND rpb.lpu_inf = CASE
                                                                              WHEN LENGTH(rrd.lpu) > 5
                                                                                  THEN SUBSTRING(rrd.lpu FROM 3 FOR 4)
                                                                              ELSE rrd.lpu
                                                                          END))
                );

    UPDATE rds.t_rr_patient AS rrr
    SET dateend       = t.enddate,
        lasteditdate  = CURRENT_TIMESTAMP,
        excludereason = CASE WHEN t.enddate IS NOT NULL THEN COALESCE(rr.excludereason, 0) ELSE NULL END
    FROM rds.t_rr_patient     rr
    JOIN temp_rr_direction AS d ON d.rrpatient = rr.rrpatientid
    JOIN LATERAL (
             SELECT rrb.enddate
             FROM rds.t_rr_patient_benefit AS rrb
             WHERE rrb.rrpatient = rr.id
             ORDER BY CASE WHEN rrb.enddate IS NULL THEN '9999-01-01'::DATE ELSE rrb.enddate END DESC
             LIMIT 1
             )             AS t ON TRUE
    WHERE rr.rrpatientid = rrr.rrpatientid;

    SELECT XMLELEMENT(
               NAME "RR_DIRECTION",
               XMLFOREST(
                   v_operationtype AS "TYPE",
                   rd.id AS "ID",
                   rd.rrpatient AS "RrPatient"
               )
           )::text
    INTO v_result
    FROM temp_rr_direction rd;


    SELECT XMLELEMENT(
               NAME "RR_DIRECTION",
               XMLELEMENT(
                   NAME "body",
                   (SELECT XMLAGG(
                               XMLELEMENT(
                                   NAME "RR_DIRECTION",
                                   XMLFOREST(
                                       rp.rrpatientid AS "RrPatientID",
                                       rp.snils AS "Snils",
                                       rp.docseries AS "DocSeries",
                                       rp.docnumber AS "DocNumber",
                                       rp.lastname AS "LastName",
                                       rp.firstname AS "FirstName",
                                       rp.fathername AS "FatherName",
                                       TO_CHAR(rp.birthday, 'DD.MM.YYYY') AS "Birthday",
                                       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",
                                       esex.code AS "Sex",
                                       rp.lasteditlpu AS "Lpu",
                                       rp.excludereason AS "ExcludeReason",
                                       rp.tipd AS "DocType",
                                       rp.kod AS "Kod",
                                       rp.kodadr AS "KodAdr",
                                       rp.birthsurname AS "BirthSurname",
                                       rp.lpumainfirst AS "LpuFirst",
                                       rp.sost_r AS "SOST_R",
                                       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.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 "RR_DIRECTION_BENEFIT",
                                                   XMLFOREST(
                                                       rp.rrpatientid AS "RrPatient",
                                                       TO_CHAR(rpb.diagdate, 'DD.MM.YYYY') AS "DiagDate",
                                                       rpb.document AS "Document",
                                                       TO_CHAR(rpb.documentdate, 'DD.MM.YYYY') AS "DocumentDate",
                                                       rpb.dop AS "Dop",
                                                       rpb.mkb AS "Mkb",
                                                       TO_CHAR(rpb.begindate, 'DD.MM.YYYY') AS "BeginDate",
                                                       TO_CHAR(rpb.enddate, 'DD.MM.YYYY') AS "EndDate",
                                                       b.benefitcode AS "BenefitCode",
                                                       b.benefitviews AS "BenefitViews",
                                                       rpb.lasteditlpu AS "Lpu",
                                                       TO_CHAR(rpb.lasteditdate, 'DD.MM.YYYY') AS "LastEditDate",
                                                       rpb.post AS "Post",
                                                       rpb.divisionmanager AS "DivisionManager"
                                                   )
                                               )
                                           )
                                    FROM rds.t_rr_patient_benefit rpb
                                    JOIN rds.t_benefits           b ON b.benefitsid = rpb.benefits
                                    JOIN vclib.t_enums            e ON e.enumid = b.benefitviews
                                    WHERE rpb.rrpatient = rp.id
                                   )
                               )
                           )
                    FROM rds.t_rr_patient              rp
                    JOIN      vclib.t_enums            esex ON esex.enumid = rp.sex
                    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.rrpatientid = v_rrpatient
                   )
               )
           )
    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(rrpatientid) + 1 FROM rds.t_rr_patient)
    WHERE entname IN ('RR_PATIENT', 'RDS.T_RR_PATIENT');

END;
$$