CREATE OR REPLACE PROCEDURE AKUZ.sp_AKUZ_DloAccessPostDirection(
    "Xml" TEXT DEFAULT NULL::TEXT,
    "IsNew" INTEGER DEFAULT 1,
    "ID" uuid DEFAULT NULL::UUID,
    "QueueName" CHARACTER VARYING DEFAULT NULL::CHARACTER VARYING,
    "dateTime" TIMESTAMP DEFAULT NULL,
    "user" uuid DEFAULT NULL::uuid)
AS
$$
DECLARE
    v_Result          TEXT;
    v_ContentEncoding VARCHAR(255) := 'utf-8';
    v_Type            VARCHAR(255) := 'DLO_ACCESSED_POSTS_DIRECTION';
    v_OperationType   INTEGER;
    v_INSERT          INTEGER      := 0;
    v_DirectionState  INTEGER;
    v_DS_Accept       INTEGER      := 1;
    p_Xml             xml          := "Xml";
    p_IsNew           INTEGER      := "IsNew";
    p_ID              uuid         := "ID";
    p_QueueName       VARCHAR(100) := "QueueName";
    p_dateTime        TIMESTAMP    := "dateTime";
    p_User            uuid         := "user";
BEGIN
    p_dateTime := COALESCE(p_dateTime, CURRENT_TIMESTAMP(0));

    IF (p_Xml IS NULL) THEN
        SELECT
            XMLELEMENT(
                NAME "DLO_ACCESSED_POSTS_DIRECTION",
                XMLELEMENT(NAME "header", XMLFOREST('Update' AS "TYPE")),
                XMLELEMENT
                (
                    NAME "body",
                    XMLELEMENT
                    (
                        NAME "DLO_ACCESSED_POSTS_DIRECTION",
                        XMLFOREST
                        (
                            dfidap.DloAccessedPostsDirectionID AS "DLO_ACCESSED_POSTS_DIRECTION_ID",
                            dfidap.DirectionType AS "DIRECTION_TYPE",
                            dfidap.DirectionState AS "DIRECTION_STATE",
                            lm.LpuCode AS "LPU_MAIN",
                            dfidap.Snils AS "SNILS",
                            dfidap.PostCode AS "POST_CODE",
                            p.Code AS "DOC_CODE",
                            dfidap.LastName AS "LAST_NAME",
                            dfidap.FirstName AS "FIRST_NAME",
                            dfidap.FatherName AS "FATHER_NAME",
                            s.Code AS "SPECIALITY",
                            pt.Code AS "POST_TYPE",
                            dfidap.Category AS "CATEGORY",
                            TO_CHAR(dfidap.BeginAccessDate, 'YYYY-MM-DD') AS "BEGIN_ACCESS_DATE",
                            TO_CHAR(dfidap.EndAccessDate, 'YYYY-MM-DD') AS "END_ACCESS_DATE",
                            TO_CHAR(dfidap.BeginWorkDate, 'YYYY-MM-DD') AS "BEGIN_WORK_DATE",
                            TO_CHAR(dfidap.CertificateDate, 'YYYY-MM-DD') AS "CERTIFICATE_DATE",
                            TO_CHAR(dfidap.ExpulsionDate, 'YYYY-MM-DD') AS "EXPULSION_DATE",
                            e1.IdEhr AS "EhrRus7PJSAM6GJM",
                            rrb1.CurrVersion AS "EhrRus7PJSAM6GJMVer",
                            e2.IdEhr AS "EhrRusLS9R0Q4VR4",
                            rrb2.CurrVersion AS "EhrRusLS9R0Q4VR4Ver"
                        )
                    )
                )
            )::TEXT
        INTO v_Result
        FROM AKUZ.T_DLO_ACCESSED_POSTS_DIRECTION dfidap
        JOIN AKUZ.T_LPU_MAIN lm ON dfidap.LpuMain = lm.LpuId
        JOIN AKUZ.T_SPECIALITY s ON dfidap.Speciality = s.SpecialityId
        JOIN AKUZ.T_POST_TYPE pt ON dfidap.PostType = pt.PostTypeID
        JOIN AKUZ.T_POST p ON p.ResourceID = dfidap.Post
        LEFT JOIN EHR.T_EHR_RUS_7PJSAM6GJM e1 ON dfidap.EhrRus7pjsam6gjm = e1.EhrRusId
        LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = e1.EhrRusRefBooks
        LEFT JOIN EHR.T_EHR_RUS_LS9R0Q4VR4 e2 ON dfidap.EhrRusLs9r0q4vr4 = e2.EhrRusId
        LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = e2.EhrRusRefBooks
        WHERE
            dfidap.DloAccessedPostsDirectionID = p_ID;

        INSERT INTO
            AKUZ.T_RABBIT_SEND_OBJECT (RabbitSendObjectID,
                                       QueueName,
                                       Content,
                                       MessageID,
                                       Headers,
                                       ContentEncoding,
                                       Type,
                                       UserID,
                                       State,
                                       CreateDateTime,
                                       OwnerID)
        VALUES
            (gen_random_uuid(),
             p_QueueName,
             ConvertTextToBinaryZip(v_Result, ''),
             NULL,
             NULL,
             v_ContentEncoding,
             v_Type,
             NULL,
             0,
             p_dateTime,
             p_ID);

    ELSE
        CREATE TEMPORARY TABLE temp_DLO_ACCESSED_POSTS_DIRECTION AS
        SELECT
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/DLO_ACCESSED_POSTS_DIRECTION_ID/text()', x))[1]::TEXT::uuid AS Id,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/DIRECTION_TYPE/text()', x))[1]::TEXT::INTEGER               AS DirectionType,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/DIRECTION_STATE/text()', x))[1]::TEXT::INTEGER              AS DirectionState,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/POST_CODE/text()', x))[1]::TEXT::VARCHAR(32)                AS PostCode,
            TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS_DIRECTION/EXPULSION_DATE/text()', x))[1]::TEXT,
                         'YYYY-MM-DD')                                                                        AS ExpulsionDate,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/REFUSING_REASON/text()', x))[1]::TEXT                       AS RefusingReason
        FROM UNNEST(XPATH('/DLO_ACCESSED_POSTS_DIRECTION/body/DLO_ACCESSED_POSTS_DIRECTION', p_Xml)) AS x;

        UPDATE AKUZ.T_DLO_ACCESSED_POSTS_DIRECTION dap
        SET
            DirectionState = ndap.DirectionState,
            RefusingReason = ndap.RefusingReason
        FROM temp_DLO_ACCESSED_POSTS_DIRECTION ndap
        WHERE ndap.Id = dap.DloAccessedPostsDirectionID;

        SELECT
            DirectionState,
            DirectionType
        INTO
            v_DirectionState,
            v_OperationType
        FROM temp_DLO_ACCESSED_POSTS_DIRECTION
        LIMIT 1;

        IF (v_DirectionState = v_DS_Accept) THEN
            IF (v_OperationType = v_INSERT) THEN
                INSERT INTO
                    AKUZ.T_DLO_ACCESSED_POSTS (DloAccessedPostsID,
                                               LpuMain,
                                               PostCode,
                                               LastName,
                                               FirstName,
                                               FatherName,
                                               Category,
                                               BeginAccessDate,
                                               EndAccessDate,
                                               BeginWorkDate,
                                               CertificateDate,
                                               MedicalSpecialityCode,
                                               ExpulsionDate,
                                               Snils,
                                               PostType,
                                               Speciality,
                                               UpdateInfoDate,
                                               EhrRus7pjsam6gjm,
                                               EhrRusLs9r0q4vr4)
                SELECT
                    gen_random_uuid(),
                    dir.LpuMain,
                    dir.PostCode,
                    dir.LastName,
                    dir.FirstName,
                    dir.FatherName,
                    dir.Category,
                    dir.BeginAccessDate,
                    dir.EndAccessDate,
                    dir.BeginWorkDate,
                    dir.CertificateDate,
                    dir.MedicalSpecialityCode,
                    dir.ExpulsionDate,
                    dir.Snils,
                    dir.PostType,
                    dir.Speciality,
                    p_dateTime,
                    dir.EhrRus7pjsam6gjm,
                    dir.EhrRusLs9r0q4vr4
                FROM temp_DLO_ACCESSED_POSTS_DIRECTION dlo
                JOIN AKUZ.T_DLO_ACCESSED_POSTS_DIRECTION dir ON dlo.Id = dir.DloAccessedPostsDirectionID;

            ELSE
                UPDATE AKUZ.T_DLO_ACCESSED_POSTS post
                SET ExpulsionDate = dir.ExpulsionDate
                FROM temp_DLO_ACCESSED_POSTS_DIRECTION dir
                WHERE
                      dir.PostCode = post.PostCode
                  AND post.ExpulsionDate IS NULL;
            END IF;
        END IF;

        DROP TABLE temp_DLO_ACCESSED_POSTS_DIRECTION;
    END IF;
END;
$$
    LANGUAGE plpgsql;
