CREATE OR REPLACE PROCEDURE APS.sp_APS_DloAccessPost(
    Xml xml DEFAULT NULL,
    IsNew integer DEFAULT 1,
    ID uuid DEFAULT NULL,
    QueueName varchar(100) DEFAULT NULL,
    dateTime timestamp without time zone DEFAULT NULL,
    "user" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_Result text;
    v_ContentEncoding varchar(255);
    v_Type varchar(255);
    v_OperationType integer;
    v_OwnerID uuid;

    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));
    v_ContentEncoding := 'utf-8';
    v_Type := 'DLO_ACCESSED_POSTS';

    IF (p_Xml IS NOT NULL) THEN
        SELECT
            (xpath('./OPERATION_TYPE/text()', x))[1]::int AS v_OperationType,
            (xpath('./ID/text()', x))[1]::uuid AS v_OwnerID,
            p_Xml::text
        INTO
            v_OperationType, v_OwnerID, v_Result
        FROM
            (SELECT p_Xml AS x) AS t;

        CREATE TEMPORARY TABLE temp_DLO_ACCESSED_POSTS AS
        SELECT
            (xpath('./ID/text()', x))[1]::uuid AS ID,
            (xpath('./POST_CODE/text()', x))[1]::varchar(255) AS PostCode,
            (xpath('./LAST_NAME/text()', x))[1]::varchar(40) AS LastName,
            (xpath('./FIRST_NAME/text()', x))[1]::varchar(40) AS FirstName,
            (xpath('./FATHER_NAME/text()', x))[1]::varchar(40) AS FatherName,
            (xpath('./CATEGORY/text()', x))[1]::int AS Category,
            to_timestamp((xpath('./BEGIN_ACCESS_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS BeginAccessDate,
            to_timestamp((xpath('./END_ACCESS_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS EndAccessDate,
            to_timestamp((xpath('./BEGIN_WORK_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS BeginWorkDate,
            to_timestamp((xpath('./CERTIFICATE_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS CertificateDate,
            (xpath('./MEDICAL_SPECIALITY_CODE/text()', x))[1]::varchar(16) AS MedicalSpecialityCode,
            to_timestamp((xpath('./EXPULSION_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS ExpulsionDate,
            (xpath('./SNILS/text()', x))[1]::varchar(14) AS Snils,
            (xpath('./POST_TYPE/text()', x))[1]::varchar(4) AS PostType,
            (xpath('./SPECIALITY/text()', x))[1]::varchar(3) AS Speciality,
            (xpath('./LPU_MAIN/text()', x))[1]::varchar(6) AS LpuMain
        FROM
            unnest(xpath('//DLO_ACCESSED_POST', p_Xml)) as x;

        -- Обновление существующих записей
        UPDATE APS.T_DLO_ACCESSED_POSTS dap
        SET
            LpuMain = ndap.LpuMain,
            PostCode = ndap.PostCode,
            LastName = ndap.LastName,
            FirstName = ndap.FirstName,
            FatherName = ndap.FatherName,
            Category = ndap.Category,
            BeginAccessDate = ndap.BeginAccessDate,
            EndAccessDate = ndap.EndAccessDate,
            BeginWorkDate = ndap.BeginWorkDate,
            CertificateDate = ndap.CertificateDate,
            MedicalSpecialityCode = ndap.MedicalSpecialityCode,
            ExpulsionDate = ndap.ExpulsionDate,
            Snils = ndap.Snils,
            PostType = ndap.PostType,
            Speciality = ndap.Speciality
        FROM temp_DLO_ACCESSED_POSTS ndap
        WHERE ndap.ID = dap.DloAccessedPostsID;

        -- Вставка новых записей из временной таблицы
        INSERT INTO APS.T_DLO_ACCESSED_POSTS (
            DloAccessedPostsID,
            PostCode,
            LastName,
            FirstName,
            FatherName,
            Category,
            BeginAccessDate,
            EndAccessDate,
            BeginWorkDate,
            CertificateDate,
            MedicalSpecialityCode,
            ExpulsionDate,
            Snils,
            PostType,
            Speciality
        )
        SELECT
            P.ID,
            PostCode,
            LastName,
            FirstName,
            FatherName,
            Category,
            BeginAccessDate,
            EndAccessDate,
            BeginWorkDate,
            CertificateDate,
            MedicalSpecialityCode,
            ExpulsionDate,
            Snils,
            PostType,
            Speciality
        FROM temp_DLO_ACCESSED_POSTS P;

        -- Удаление временной таблицы после использования
        DROP TABLE temp_DLO_ACCESSED_POSTS;
    END IF;
END;
$$;

