DROP PROCEDURE IF EXISTS sp_milkdirection;

CREATE OR REPLACE PROCEDURE public.sp_milkdirection(
    "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_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'MILK_DIRECTION';
    v_ownerid         uuid;
    v_registerid      int ;
BEGIN
    DROP TABLE IF EXISTS temp_milk_direction;
    CREATE TEMP TABLE temp_milk_direction AS
    SELECT
        (XPATH('/MILK_DIRECTION/RegisterID/text()', x))[1]::VARCHAR(10)::INT                                AS registerid,
        (XPATH('/MILK_DIRECTION/RegisterID/text()', x))[1]::VARCHAR(10)::INT                                AS realidpatient,
        (XPATH('/MILK_DIRECTION/ID/text()', x))[1]::VARCHAR(36)::uuid                                       AS id,
        (XPATH('/MILK_DIRECTION/LPU/text()', x))[1]::VARCHAR(6)                                             AS lpu,
        TO_TIMESTAMP((XPATH('/MILK_DIRECTION/EnterDate/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                          AS enterdate,
        (XPATH('/MILK_DIRECTION/LastName/text()', x))[1]::VARCHAR(40)                                       AS lastname,
        (XPATH('/MILK_DIRECTION/FirstName/text()', x))[1]::VARCHAR(40)                                      AS firstname,
        (XPATH('/MILK_DIRECTION/FatherName/text()', x))[1]::VARCHAR(40)                                     AS fathername,
        (XPATH('/MILK_DIRECTION/BirthSurname/text()', x))[1]::VARCHAR(40)                                   AS birthsurname,
        TO_TIMESTAMP((XPATH('/MILK_DIRECTION/Birthday/text()', x))[1]::VARCHAR(10), 'DD.MM.YYYY')           AS birthday,
        TO_TIMESTAMP((XPATH('/MILK_DIRECTION/Date/text()', x))[1]::VARCHAR(10), 'DD.MM.YYYY')               AS date,
        (XPATH('/MILK_DIRECTION/Sex/text()', x))[1]::VARCHAR(10)::INT                                       AS sex,
        (XPATH('/MILK_DIRECTION/Kod/text()', x))[1]::VARCHAR(32)                                            AS kod,
        (XPATH('/MILK_DIRECTION/DocSeries/text()', x))[1]::VARCHAR(16)                                      AS docseries,
        (XPATH('/MILK_DIRECTION/DocNumber/text()', x))[1]::VARCHAR(16)                                      AS docnumber,
        (XPATH('/MILK_DIRECTION/DocType/text()', x))[1]::VARCHAR(10)::INT                                   AS doctype,
        (XPATH('/MILK_DIRECTION/SNILS/text()', x))[1]::VARCHAR(14)                                          AS snils,
        (XPATH('/MILK_DIRECTION/ChangeType/text()', x))[1]::VARCHAR(10)::INT                                AS changetype,
        TO_TIMESTAMP((XPATH('/MILK_DIRECTION/DocIssueDate/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                          AS docissuedate,
        (XPATH('/MILK_DIRECTION/DocIssueOrg/text()', x))[1]::VARCHAR(256)                                   AS docissueorg,
        (XPATH('/MILK_DIRECTION/Enp/text()', x))[1]::VARCHAR(16)                                            AS enp,
        (XPATH('/MILK_DIRECTION/RealFiasAddress/text()', x))[1]::VARCHAR(36)                                AS realfiasaddress,
        (XPATH('/MILK_DIRECTION/RealFiasHome/text()', x))[1]::VARCHAR(36)                                   AS realfiashome,
        (XPATH('/MILK_DIRECTION/RealCity/text()', x))[1]::VARCHAR(100)                                      AS realcity,
        (XPATH('/MILK_DIRECTION/RealCityPrefix/text()', x))[1]::VARCHAR(10)                                 AS realcityprefix,
        (XPATH('/MILK_DIRECTION/RealStreet/text()', x))[1]::VARCHAR(100)                                    AS realstreet,
        (XPATH('/MILK_DIRECTION/RealStreetPrefix/text()', x))[1]::VARCHAR(10)                               AS realstreetprefix,
        (XPATH('/MILK_DIRECTION/RealHouse/text()', x))[1]::VARCHAR(100)                                     AS realhouse,
        (XPATH('/MILK_DIRECTION/RealBuild/text()', x))[1]::VARCHAR(100)                                     AS realbuild,
        (XPATH('/MILK_DIRECTION/RealStruc/text()', x))[1]::VARCHAR(100)                                     AS realstruc,
        (XPATH('/MILK_DIRECTION/RealRoom/text()', x))[1]::VARCHAR(100)                                      AS realroom,
        TO_TIMESTAMP((XPATH('/MILK_DIRECTION/RealLiveStartDate/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                          AS reallivestartdate,
        TO_TIMESTAMP((XPATH('/MILK_DIRECTION/RealLiveEndDate/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                          AS realliveenddate,
        (XPATH('/MILK_DIRECTION/LegalFiasAddress/text()', x))[1]::VARCHAR(36)                               AS legalfiasaddress,
        (XPATH('/MILK_DIRECTION/LegalFiasHome/text()', x))[1]::VARCHAR(36)                                  AS legalfiashome,
        (XPATH('/MILK_DIRECTION/LegalCity/text()', x))[1]::VARCHAR(100)                                     AS legalcity,
        (XPATH('/MILK_DIRECTION/LegalCityPrefix/text()', x))[1]::VARCHAR(10)                                AS legalcityprefix,
        (XPATH('/MILK_DIRECTION/LegalStreet/text()', x))[1]::VARCHAR(100)                                   AS legalstreet,
        (XPATH('/MILK_DIRECTION/LegalStreetPrefix/text()', x))[1]::VARCHAR(10)                              AS legalstreetprefix,
        (XPATH('/MILK_DIRECTION/LegalHouse/text()', x))[1]::VARCHAR(100)                                    AS legalhouse,
        (XPATH('/MILK_DIRECTION/LegalBuild/text()', x))[1]::VARCHAR(100)                                    AS legalbuild,
        (XPATH('/MILK_DIRECTION/LegalStruc/text()', x))[1]::VARCHAR(100)                                    AS legalstruc,
        (XPATH('/MILK_DIRECTION/LegalRoom/text()', x))[1]::VARCHAR(100)                                     AS legalroom,
        TO_TIMESTAMP((XPATH('/MILK_DIRECTION/LegalLiveStartDate/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                          AS legallivestartdate,
        TO_TIMESTAMP((XPATH('/MILK_DIRECTION/LegalLiveEndDate/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                          AS legalliveenddate,
        (XPATH('/MILK_DIRECTION/CitizenShip/text()', x))[1]::VARCHAR(3)                                     AS citizenship,
        (XPATH('/MILK_DIRECTION/ExcludeReason/text()', x))[1]::VARCHAR(10)::INT                             AS excludereason
    FROM UNNEST(XPATH('/MILK_DIRECTION', p_xml)) x;

    DROP TABLE IF EXISTS temp_milk_direction_benefits;
    CREATE TEMP TABLE temp_milk_direction_benefits AS
    SELECT
        (XPATH('/MILK_DIRECTION_BENEFIT/DirectionID/text()', x))[1]::VARCHAR(36)::uuid                 AS directionid,
        TO_TIMESTAMP((XPATH('/MILK_DIRECTION_BENEFIT/DiagDate/text()', x))[1]::TEXT, 'DD.MM.YYYY')     AS diagdate,
        (XPATH('/MILK_DIRECTION_BENEFIT/Document/text()', x))[1]::VARCHAR(16)                          AS document,
        TO_TIMESTAMP((XPATH('/MILK_DIRECTION_BENEFIT/DocumentDate/text()', x))[1]::TEXT, 'DD.MM.YYYY') AS documentdate,
        (XPATH('/MILK_DIRECTION_BENEFIT/Mkb/text()', x))[1]::VARCHAR(8)                                AS mkb,
        (XPATH('/MILK_DIRECTION_BENEFIT/BenefitCode/text()', x))[1]::VARCHAR(255)                      AS benefitcode,
        (XPATH('/MILK_DIRECTION_BENEFIT/BenefitViews/text()', x))[1]::VARCHAR(10)::INT                 AS benefitviews,
        (XPATH('/MILK_DIRECTION_BENEFIT/PostCode/text()', x))[1]::VARCHAR(6)                           AS postcode,
        (XPATH('/MILK_DIRECTION_BENEFIT/ChangeType/text()', x))[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 'Не верно сгенерирован идентификатор льготника в регистре!';
            RETURN;
        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
                   (
                       rd.id AS "ID",
                       rd.registerid AS "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;
$$