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"::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;
    END IF;
END;
$$
    LANGUAGE plpgsql;
