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('/DLO_ACCESSED_POSTS/body/DLO_ACCESSED_POST/OPERATION_TYPE/text()', x))[1]::varchar(10)::int AS v_operationtype,
               (XPATH('/DLO_ACCESSED_POSTS/body/DLO_ACCESSED_POST/ID/text()', x))[1]::text::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('/DLO_ACCESSED_POSTS/ID/text()', x))[1]::text::uuid                       AS id,
                   (XPATH('/DLO_ACCESSED_POSTS/POST_CODE/text()', x))[1]::varchar(255)              AS postcode,
                   (XPATH('/DLO_ACCESSED_POSTS/DOC_CODE/text()', x))[1]::varchar(255)               AS doccode,
                   (XPATH('/DLO_ACCESSED_POSTS/LAST_NAME/text()', x))[1]::varchar(40)               AS lastname,
                   (XPATH('/DLO_ACCESSED_POSTS/FIRST_NAME/text()', x))[1]::varchar(40)              AS firstname,
                   (XPATH('/DLO_ACCESSED_POSTS/FATHER_NAME/text()', x))[1]::varchar(40)             AS fathername,
                   (XPATH('/DLO_ACCESSED_POSTS/CATEGORY/text()', x))[1]::text::int                  AS category,
                   TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS/BEGIN_ACCESS_DATE/text()', x))[1]::text,
                                'YYYY-MM-DD')                                                       AS beginaccessdate,
                   TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POST/SEND_ACCESS_DATE/text()', x))[1]::text,
                                'YYYY-MM-DD')                                                       AS endaccessdate,
                   TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS/BEGIN_WORK_DATE/text()', x))[1]::text,
                                'YYYY-MM-DD')                                                       AS beginworkdate,
                   TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS/CERTIFICATE_DATE/text()', x))[1]::text,
                                'YYYY-MM-DD')                                                       AS certificatedate,
                   (XPATH('/DLO_ACCESSED_POSTS/MEDICAL_SPECIALITY_CODE/text()', x))[1]::varchar(16) AS medicalspecialitycode,
                   TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS/EXPULSION_DATE/text()', x))[1]::text,
                                'YYYY-MM-DD')                                                       AS expulsiondate,
                   (XPATH('/DLO_ACCESSED_POSTS/SNILS/text()', x))[1]::varchar(14)                   AS snils,
                   (XPATH('/DLO_ACCESSED_POSTS/POST_TYPE/text()', x))[1]::varchar(4)                AS posttype,
                   (XPATH('/DLO_ACCESSED_POSTS/SPECIALITY/text()', x))[1]::varchar(3)               AS speciality,
                   (XPATH('/DLO_ACCESSED_POSTS/LPU_MAIN/text()', x))[1]::varchar(6)                 AS lpumain,
                   (XPATH('/DLO_ACCESSED_POSTS/EhrRus7PJSAM6GJM/text()', x))[1]::varchar(10)::int   AS ehrrus7pjsam6gjm,
                   (XPATH('/DLO_ACCESSED_POSTS/EhrRus7PJSAM6GJMVer/text()', x))[1]::varchar(8)      AS ehrrus7pjsam6gjmver,
                   (XPATH('/DLO_ACCESSED_POSTS/EhrRusLS9R0Q4VR4/text()', x))[1]::varchar(10)::int   AS ehrrusls9r0q4vr4,
                   (XPATH('/DLO_ACCESSED_POSTS/EhrRusLS9R0Q4VR4Ver/text()', x))[1]::varchar(8)      AS ehrrusls9r0q4vr4ver
            FROM UNNEST(XPATH('/DLO_ACCESSED_POSTS/body/DLO_ACCESSED_POSTS', 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 ';
            RETURN;
        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 ';
            RETURN;
        END IF;

        -- Обновление существующих записей
        UPDATE aps.t_dlo_accessed_posts dap
        SET lpumain               = ndap.lpumain,
            postcode              = ndap.doccode,
            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,
                                      lpumain)
        SELECT p.id,
               doccode,
               lastname,
               firstname,
               fathername,
               category,
               beginaccessdate,
               endaccessdate,
               beginworkdate,
               certificatedate,
               medicalspecialitycode,
               expulsiondate,
               snils,
               posttype,
               speciality,
               er1.ehrrusid,
               er2.ehrrusid,
               lpumain
        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;
    END IF;
END;
$$;

