DROP PROCEDURE IF EXISTS APS.sp_APS_DloAccessPost;

CREATE OR REPLACE PROCEDURE APS.sp_APS_DloAccessPost(
    "Xml" text 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"::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,
            (xpath('./EhrRus7PJSAM6GJM/text()', x))[1]::varchar(10)::int AS EhrRus7pjsam6gjm,
            (xpath('./EhrRus7PJSAM6GJMVer/text()', x))[1]::varchar(8) AS EhrRus7pjsam6gjmVer,
            (xpath('./EhrRusLS9R0Q4VR4/text()', x))[1]::varchar(10)::int AS EhrRusLs9r0q4vr4,
            (xpath('./EhrRusLS9R0Q4VR4Ver/text()', x))[1]::varchar(8) AS EhrRusLs9r0q4vr4Ver
        FROM
            unnest(xpath('//DLO_ACCESSED_POST', p_Xml)) as x;

        IF (EXISTS (
            SELECT 1 
            FROM temp_DLO_ACCESSED_POSTS dir 
            LEFT JOIN LATERAL (
                SELECT ehr.IdEhr, rrb.CurrVersion
                FROM EHR.T_EHR_RUS_7PJSAM6GJM ehr
                JOIN EHR.T_EHR_RUS_REF_BOOKS rrb on rrb.EhrRusRefBooksID = ehr.EhrRusRefBooks
                WHERE ehr.IdEhr = dir.EhrRus7pjsam6gjm and rrb.CurrVersion = dir.EhrRus7pjsam6gjmVer
                LIMIT 1
            ) e ON TRUE
            WHERE dir.EhrRus7pjsam6gjm is not null and dir.EhrRus7pjsam6gjmVer is not null
            and e.IdEhr is null and e.CurrVersion is null))
        THEN
            RAISE EXCEPTION 'Установлены некорректные значения EhrRus7pjsam6gjm и EhrRus7pjsam6gjmVer '; 
        END IF;

        IF (EXISTS (
            SELECT 1 
            FROM temp_DLO_ACCESSED_POSTS dir 
            LEFT JOIN LATERAL (
                SELECT ehr.IdEhr, rrb.CurrVersion
                FROM EHR.T_EHR_RUS_LS9R0Q4VR4 ehr
                JOIN EHR.T_EHR_RUS_REF_BOOKS rrb on rrb.EhrRusRefBooksID = ehr.EhrRusRefBooks
                WHERE ehr.IdEhr = dir.EhrRusLs9r0q4vr4 and rrb.CurrVersion = dir.EhrRusLs9r0q4vr4Ver
                LIMIT 1
            ) e ON TRUE
            WHERE dir.EhrRusLs9r0q4vr4 is not null and dir.EhrRusLs9r0q4vr4Ver is not null
            and e.IdEhr is null and e.CurrVersion is null))
        THEN
            RAISE EXCEPTION 'Установлены некорректные значения EhrRusLS9R0Q4VR4 и EhrRusLS9R0Q4VR4Ver ';
        END IF;

        -- Обновление существующих записей
        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,
            EhrRus7pjsam6gjm = er1.EhrRusID,
            EhrRusLs9r0q4vr4 = er2.EhrRusID
        FROM temp_DLO_ACCESSED_POSTS ndap
        LEFT JOIN LATERAL (
                SELECT er.EhrRusID
                FROM EHR.T_EHR_RUS_7PJSAM6GJM er
                JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                WHERE er.IdEHR = ndap.EhrRus7pjsam6gjm AND RRB.CURRVERSION = ndap.EhrRus7pjsam6gjmVer
                LIMIT 1) er1 ON TRUE
       LEFT JOIN LATERAL (
                SELECT er.EhrRusID
                FROM EHR.T_EHR_RUS_LS9R0Q4VR4 er
                JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                WHERE er.IdEHR = ndap.EhrRusLs9r0q4vr4 AND RRB.CURRVERSION = ndap.EhrRusLs9r0q4vr4Ver
                LIMIT 1) er2 ON TRUE
        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,
            EhrRus7pjsam6gjm,
            EhrRusLs9r0q4vr4
        )
        SELECT
            P.ID,
            PostCode,
            LastName,
            FirstName,
            FatherName,
            Category,
            BeginAccessDate,
            EndAccessDate,
            BeginWorkDate,
            CertificateDate,
            MedicalSpecialityCode,
            ExpulsionDate,
            Snils,
            PostType,
            Speciality,
            er1.EhrRusId,
            er2.EhrRusId
        FROM temp_DLO_ACCESSED_POSTS P
        LEFT JOIN LATERAL (
            SELECT er.EhrRusID
            FROM EHR.T_EHR_RUS_7PJSAM6GJM er
            JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
            WHERE er.IdEHR = P.EhrRus7pjsam6gjm AND RRB.CURRVERSION = P.EhrRus7pjsam6gjmVer
            LIMIT 1) er1 ON TRUE
        LEFT JOIN LATERAL (
            SELECT er.EhrRusID
            FROM EHR.T_EHR_RUS_LS9R0Q4VR4 er
            JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
            WHERE er.IdEHR = P.EhrRusLs9r0q4vr4 AND RRB.CURRVERSION = P.EhrRusLs9r0q4vr4Ver
            LIMIT 1) er2 ON TRUE;

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

