CREATE OR REPLACE PROCEDURE public.sp_milkdirection(
    xml xml,
    queuename varchar(100) DEFAULT 'RDSSync',
    datetime timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := xml;
    p_queuename       varchar(100) := queuename;
    p_datetime        timestamp    := COALESCE(datetime, CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    v_result          text;
    v_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'MILK_DIRECTION';
    v_ownerid         uuid;
    v_registerid      int ;
BEGIN
    
    CREATE TEMP TABLE temp_milk_direction AS
        SELECT (XPATH('/MILK_DIRECTION/RegisterID/text()', p_xml))[1]::varchar(10)::int               AS registerid,
               (XPATH('/MILK_DIRECTION/RegisterID/text()', p_xml))[1]::varchar(10)::int               AS realidpatient,
               (XPATH('/MILK_DIRECTION/ID/text()', p_xml))[1]::varchar(36)::uuid                      AS id,
               (XPATH('/MILK_DIRECTION/LPU/text()', p_xml))[1]::varchar(6)                            AS lpu,
               (XPATH('/MILK_DIRECTION/EnterDate/text()', p_xml))[1]::varchar(10)::timestamp          AS enterdate,
               (XPATH('/MILK_DIRECTION/LastName/text()', p_xml))[1]::varchar(40)                      AS lastname,
               (XPATH('/MILK_DIRECTION/FirstName/text()', p_xml))[1]::varchar(40)                     AS firstname,
               (XPATH('/MILK_DIRECTION/FatherName/text()', p_xml))[1]::varchar(40)                    AS fathername,
               (XPATH('/MILK_DIRECTION/BirthSurname/text()', p_xml))[1]::varchar(40)                  AS birthsurname,
               (XPATH('/MILK_DIRECTION/Birthday/text()', p_xml))[1]::varchar(10)::timestamp           AS birthday,
               (XPATH('/MILK_DIRECTION/Date/text()', p_xml))[1]::varchar(10)::timestamp               AS date,
               (XPATH('/MILK_DIRECTION/Sex/text()', p_xml))[1]::varchar(10)::int                      AS sex,
               (XPATH('/MILK_DIRECTION/Kod/text()', p_xml))[1]::varchar(32)                           AS kod,
               (XPATH('/MILK_DIRECTION/DocSeries/text()', p_xml))[1]::varchar(16)                     AS docseries,
               (XPATH('/MILK_DIRECTION/DocNumber/text()', p_xml))[1]::varchar(16)                     AS docnumber,
               (XPATH('/MILK_DIRECTION/DocType/text()', p_xml))[1]::varchar(10)::int                  AS doctype,
               (XPATH('/MILK_DIRECTION/SNILS/text()', p_xml))[1]::varchar(14)                         AS snils,
               (XPATH('/MILK_DIRECTION/ChangeType/text()', p_xml))[1]::varchar(10)::int               AS changetype,
               (XPATH('/MILK_DIRECTION/DocIssueDate/text()', p_xml))[1]::varchar(10)::timestamp       AS docissuedate,
               (XPATH('/MILK_DIRECTION/DocIssueOrg/text()', p_xml))[1]::varchar(256)                  AS docissueorg,
               (XPATH('/MILK_DIRECTION/Enp/text()', p_xml))[1]::varchar(16)                           AS enp,
               (XPATH('/MILK_DIRECTION/RealFiasAddress/text()', p_xml))[1]::varchar(36)               AS realfiasaddress,
               (XPATH('/MILK_DIRECTION/RealFiasHome/text()', p_xml))[1]::varchar(36)                  AS realfiashome,
               (XPATH('/MILK_DIRECTION/RealCity/text()', p_xml))[1]::varchar(100)                     AS realcity,
               (XPATH('/MILK_DIRECTION/RealCityPrefix/text()', p_xml))[1]::varchar(10)                AS realcityprefix,
               (XPATH('/MILK_DIRECTION/RealStreet/text()', p_xml))[1]::varchar(100)                   AS realstreet,
               (XPATH('/MILK_DIRECTION/RealStreetPrefix/text()', p_xml))[1]::varchar(10)              AS realstreetprefix,
               (XPATH('/MILK_DIRECTION/RealHouse/text()', p_xml))[1]::varchar(100)                    AS realhouse,
               (XPATH('/MILK_DIRECTION/RealBuild/text()', p_xml))[1]::varchar(100)                    AS realbuild,
               (XPATH('/MILK_DIRECTION/RealStruc/text()', p_xml))[1]::varchar(100)                    AS realstruc,
               (XPATH('/MILK_DIRECTION/RealRoom/text()', p_xml))[1]::varchar(100)                     AS realroom,
               (XPATH('/MILK_DIRECTION/RealLiveStartDate/text()', p_xml))[1]::varchar(10)::timestamp  AS reallivestartdate,
               (XPATH('/MILK_DIRECTION/RealLiveEndDate/text()', p_xml))[1]::varchar(10)::timestamp    AS realliveenddate,
               (XPATH('/MILK_DIRECTION/LegalFiasAddress/text()', p_xml))[1]::varchar(36)              AS legalfiasaddress,
               (XPATH('/MILK_DIRECTION/LegalFiasHome/text()', p_xml))[1]::varchar(36)                 AS legalfiashome,
               (XPATH('/MILK_DIRECTION/LegalCity/text()', p_xml))[1]::varchar(100)                    AS legalcity,
               (XPATH('/MILK_DIRECTION/LegalCityPrefix/text()', p_xml))[1]::varchar(10)               AS legalcityprefix,
               (XPATH('/MILK_DIRECTION/LegalStreet/text()', p_xml))[1]::varchar(100)                  AS legalstreet,
               (XPATH('/MILK_DIRECTION/LegalStreetPrefix/text()', p_xml))[1]::varchar(10)             AS legalstreetprefix,
               (XPATH('/MILK_DIRECTION/LegalHouse/text()', p_xml))[1]::varchar(100)                   AS legalhouse,
               (XPATH('/MILK_DIRECTION/LegalBuild/text()', p_xml))[1]::varchar(100)                   AS legalbuild,
               (XPATH('/MILK_DIRECTION/LegalStruc/text()', p_xml))[1]::varchar(100)                   AS legalstruc,
               (XPATH('/MILK_DIRECTION/LegalRoom/text()', p_xml))[1]::varchar(100)                    AS legalroom,
               (XPATH('/MILK_DIRECTION/LegalLiveStartDate/text()', p_xml))[1]::varchar(10)::timestamp AS legallivestartdate,
               (XPATH('/MILK_DIRECTION/LegalLiveEndDate/text()', p_xml))[1]::varchar(10)::timestamp   AS legalliveenddate,
               (XPATH('/MILK_DIRECTION/CitizenShip/text()', p_xml))[1]::varchar(3)                    AS citizenship,
               (XPATH('/MILK_DIRECTION/ExcludeReason/text()', p_xml))[1]::varchar(10)::int            AS excludereason
        FROM UNNEST(XPATH('/MILK_DIRECTION', p_xml)) x;
    
    CREATE TEMP TABLE temp_milk_direction_benefits AS
        SELECT (XPATH('/MILK_DIRECTION_BENEFIT/DirectionID/text()', p_xml))[1]::varchar(36)::uuid AS directionid,
               (XPATH('/MILK_DIRECTION_BENEFIT/DiagDate/text()', p_xml))[1]::text::timestamp      AS diagdate,
               (XPATH('/MILK_DIRECTION_BENEFIT/Document/text()', p_xml))[1]::varchar(16)          AS document,
               (XPATH('/MILK_DIRECTION_BENEFIT/DocumentDate/text()', p_xml))[1]::text::timestamp  AS documentdate,
               (XPATH('/MILK_DIRECTION_BENEFIT/Mkb/text()', p_xml))[1]::varchar(8)                AS mkb,
               (XPATH('/MILK_DIRECTION_BENEFIT/BenefitCode/text()', p_xml))[1]::varchar(255)      AS benefitcode,
               (XPATH('/MILK_DIRECTION_BENEFIT/BenefitViews/text()', p_xml))[1]::varchar(10)::int AS benefitviews,
               (XPATH('/MILK_DIRECTION_BENEFIT/PostCode/text()', p_xml))[1]::varchar(6)           AS postcode,
               (XPATH('/MILK_DIRECTION_BENEFIT/ChangeType/text()', p_xml))[1]::varchar(10)::int   AS changetype
        FROM UNNEST(XPATH('/MILK_DIRECTION/MILK_DIRECTION_BENEFIT', p_xml)) x;
    
    UPDATE temp_milk_direction md
    SET realidpatient = mr.childcode
    FROM rds.t_milk_register mr
    WHERE mr.snils = md.snils
      AND mr.excludereason IS NULL
      AND md.realidpatient IS NULL
      AND mr.snils IS NOT NULL
      AND mr.snils <> ''
      AND mr.snils <> '              '
      AND md.changetype = 1;
    
    UPDATE temp_milk_direction md
    SET realidpatient = mr.childcode
    FROM rds.t_milk_register mr
    WHERE mr.lastname = md.lastname
      AND mr.firstname = md.firstname
      AND (mr.fathername = md.fathername OR (mr.fathername IS NULL AND md.fathername IS NULL))
      AND mr.birthday = md.birthday
      AND mr.excludereason IS NULL
      AND md.realidpatient IS NULL
      AND md.changetype = 1;
    
    IF (EXISTS (SELECT 1 FROM temp_milk_direction WHERE changetype = 1 AND registerid IS NULL AND realidpatient IS NULL
               ))
    THEN
        CALL public.sp_generateidforentity('MILK_REGISTER', 'PrimaryKey', 1, v_registerid);
        
        IF (v_registerid = 0) THEN
            RAISE EXCEPTION 'Не верно сгенерирован идентификатор льготника в регистре!';
        END IF;
        
        UPDATE temp_milk_direction rd
        SET registerid = v_registerid;
    
    ELSIF (EXISTS (SELECT 1
                   FROM temp_milk_direction
                   WHERE changetype = 1
                     AND v_registerid IS NULL
                     AND realidpatient IS NOT NULL
                  )) THEN
        
        UPDATE temp_milk_direction rd
        SET registerid = realidpatient,
            changetype = 2
        FROM temp_milk_direction;
    
    END IF;
    
    UPDATE rds.t_milk_register rp
    SET lastname     = p.lastname,
        firstname    = p.firstname,
        fathername   = p.fathername,
        birthday     = p.birthday,
        sex          = esex.enumid,
        docseries    = p.docseries,
        docnumber    = p.docnumber,
        doctype      = edoc.enumid,
        city         = COALESCE(p.realcity, p.legalcity),
        block        = COALESCE(p.realstruc, p.legalstruc),
        flat         = COALESCE(p.realroom, p.legalroom),
        snils        = p.snils,
        house        = COALESCE(p.realhouse, p.legalhouse),
        street       = COALESCE(p.realstreetprefix, p.legalstreet),
        lasteditdate = p_datetime,
        lasteditlpu  = p.lpu
    FROM temp_milk_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 p.registerid = rp.childcode
      AND p.changetype = 2
      AND rp.excludereason IS NULL;
    
    UPDATE rds.t_milk_register rp
    SET excludereason = p.excludereason,
        lasteditlpu   = p.lpu,
        lasteditdate  = p_datetime
    FROM temp_milk_direction p
    WHERE p.registerid = rp.childcode
      AND changetype = 3
      AND rp.excludereason IS NULL
      AND NOT EXISTS (SELECT 1
                      FROM rds.t_milk_register_data mrd
                      WHERE mrd.milkregister = rp.milkregisterid
                        AND mrd.benefits = '188B840E-35CF-4A09-92CC-A21831646E88'
                     );
    
    UPDATE rds.t_milk_register_data mrd
    SET enddate      = p_datetime,
        lasteditdate = p_datetime,
        lasteditlpu  = p.lpu
    FROM rds.t_milk_register mr
    JOIN temp_milk_direction p ON p.registerid = mr.childcode
    WHERE mr.milkregisterid = mrd.milkregister
      AND mrd.benefits <> '188B840E-35CF-4A09-92CC-A21831646E88'
      AND p.changetype = 3
      AND (mrd.enddate IS NULL OR mrd.enddate > p_datetime);
    
    INSERT INTO
        rds.t_milk_register (milkregisterid,
                             childcode,
                             firstname,
                             lastname,
                             fathername,
                             birthday,
                             sex,
                             docseries,
                             docnumber,
                             doctype,
                             city,
                             block,
                             flat,
                             snils,
                             house,
                             street,
                             lasteditdate,
                             lasteditlpu)
    SELECT gen_random_uuid(),
           d.registerid,
           firstname,
           lastname,
           fathername,
           birthday,
           esex.enumid,
           docseries,
           docnumber,
           edoc.enumid,
           COALESCE(d.realcity, d.legalcity),
           COALESCE(d.realstruc, d.legalstruc),
           COALESCE(d.realroom, d.legalroom),
           snils,
           COALESCE(d.realhouse, d.legalhouse),
           COALESCE(d.realstruc, d.legalstreet),
           p_datetime,
           d.lpu
    FROM temp_milk_direction d
    JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.code = d.sex
    JOIN vclib.t_enums edoc ON edoc.enumtypeid = 105 AND edoc.code = d.doctype
    WHERE d.changetype = 1;
    
    
    INSERT INTO
        rds.t_milk_register_data (milkregisterdataid,
                                  milkregister,
                                  diagdate,
                                  mkb,
                                  benefits,
                                  begindate,
                                  lasteditdate,
                                  enddate,
                                  lasteditlpu)
    SELECT gen_random_uuid(), -- Генерация уникального идентификатора
           r.milkregisterid,
           bd.diagdate,
           bd.mkb,
           b.benefitsid,
           p_datetime,        -- Параметр даты и времени
           p_datetime,        -- Параметр даты и времени
           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 d.birthday + INTERVAL '1 day' * b.duration
                       WHEN b.closetype = 0 AND b.periodofact = 1 THEN d.birthday + INTERVAL '1 month' * b.duration
                       WHEN b.closetype = 0 AND b.periodofact = 2 THEN d.birthday + INTERVAL '1 year' * b.duration
                       WHEN b.closetype = 1 AND b.periodofact = 0 THEN d.enterdate + INTERVAL '1 day' * b.duration
                       WHEN b.closetype = 1 AND b.periodofact = 1 THEN d.enterdate + INTERVAL '1 month' * b.duration
                       WHEN b.closetype = 1 AND b.periodofact = 2 THEN d.enterdate + INTERVAL '1 year' * b.duration
                       ELSE NULL
                   END
               ELSE NULL
           END AS enddate,
           d.lpu
    FROM temp_milk_direction_benefits bd
    JOIN temp_milk_direction d ON d.id = bd.directionid
    JOIN rds.t_milk_register r ON r.childcode = d.registerid
    LEFT JOIN vclib.t_enums e ON e.enumtypeid = 190 AND e.code = bd.benefitviews
    JOIN rds.t_benefits b ON b.benefitcode = bd.benefitcode AND b.benefitviews = e.enumid
    WHERE bd.changetype = 1
      AND NOT EXISTS (SELECT 1
                      FROM rds.t_milk_register_data rpb
                      WHERE rpb.milkregister = r.milkregisterid
                        AND rpb.benefits = b.benefitsid
                        AND (rpb.enddate IS NULL OR rpb.enddate >= CURRENT_DATE)
                     );
    
    UPDATE rds.t_milk_register_data rpb
    SET enddate      = p_datetime,
        lasteditdate = p_datetime,
        lasteditlpu  = d.lpu
    FROM temp_milk_direction_benefits rdb
    JOIN temp_milk_direction d ON d.id = rdb.directionid
    JOIN rds.t_milk_register r ON r.childcode = d.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.milkregister = r.milkregisterid
      AND rpb.benefits = b.benefitsid
      AND rdb.changetype = 3
      AND (rpb.enddate IS NULL OR rpb.enddate > p_datetime);
    
    SELECT XMLAGG
           (
               XMLELEMENT
               (
                   NAME "MILK_REGISTER",
                   XMLFOREST
                   (
                       XMLELEMENT(NAME "ID", rd.id),
                       XMLELEMENT(NAME "RegisterID", rd.registerid)
                   )
               )
           )::text
    into v_result
    FROM temp_milk_direction rd;
    
    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;
    
    UPDATE public.t_entity_ids e
    SET id = (SELECT MAX(childcode) + 1 FROM rds.t_milk_register)
    WHERE e.entname IN ('MILK_REGISTER');
END;
$$