DROP PROCEDURE IF EXISTS aps.sp_aps_ftload_dloaccesspost;

CREATE OR REPLACE PROCEDURE aps.sp_aps_ftload_dloaccesspost(
    "Xml" text DEFAULT NULL,
    "IsNew" integer DEFAULT 1,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT NULL,
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_result          text;
    v_contentencoding varchar(255);
    v_type            varchar(255);
    v_ftload          varchar(6)   := 'FTLoad';
    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_FTLOAD';
    
    
    -- Создание временной таблицы для хранения данных из XML
    DROP TABLE IF EXISTS temp_dlo_accessed_posts;
    CREATE TEMPORARY TABLE temp_dlo_accessed_posts AS
        SELECT (XPATH('/DLO_ACCESSED_POST/ID/text()', x))[1]::uuid                             AS id,
               (XPATH('/DLO_ACCESSED_POST/POST_CODE/text()', x))[1]::varchar(255)              AS postcode,
               (XPATH('/DLO_ACCESSED_POST/DOC_CODE/text()', x))[1]::varchar(255)               AS doccode,
               (XPATH('/DLO_ACCESSED_POST/LAST_NAME/text()', x))[1]::varchar(40)               AS lastname,
               (XPATH('/DLO_ACCESSED_POST/FIRST_NAME/text()', x))[1]::varchar(40)              AS firstname,
               (XPATH('/DLO_ACCESSED_POST/FATHER_NAME/text()', x))[1]::varchar(40)             AS fathername,
               (XPATH('/DLO_ACCESSED_POST/CATEGORY/text()', x))[1]::int                        AS category,
               TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POST/BEGIN_ACCESS_DATE/text()', x))[1]::text,
                            'YYYY-MM-DD')                                                      AS beginaccessdate,
               TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POST/END_ACCESS_DATE/text()', x))[1]::text,
                            'YYYY-MM-DD')                                                      AS endaccessdate,
               TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POST/BEGIN_WORK_DATE/text()', x))[1]::text,
                            'YYYY-MM-DD')                                                      AS beginworkdate,
               TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POST/CERTIFICATE_DATE/text()', x))[1]::text,
                            'YYYY-MM-DD')                                                      AS certificatedate,
               (XPATH('/DLO_ACCESSED_POST/MEDICAL_SPECIALITY_CODE/text()', x))[1]::varchar(16) AS medicalspecialitycode,
               TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POST/EXPULSION_DATE/text()', x))[1]::text,
                            'YYYY-MM-DD')                                                      AS expulsiondate,
               (XPATH('/DLO_ACCESSED_POST/SNILS/text()', x))[1]::varchar(14)                   AS snils,
               (XPATH('/DLO_ACCESSED_POST/POST_TYPE/text()', x))[1]::varchar(4)                AS posttype,
               (XPATH('/DLO_ACCESSED_POST/SPECIALITY/text()', x))[1]::varchar(3)               AS speciality,
               (XPATH('/DLO_ACCESSED_POST/LPU_MAIN/text()', x))[1]::varchar(6)                 AS lpumain,
               (XPATH('/DLO_ACCESSED_POST/EhrRus7PJSAM6GJM/text()', x))[1]::varchar(10)::int   AS ehrrus7pjsam6gjm,
               (XPATH('/DLO_ACCESSED_POST/EhrRus7PJSAM6GJMVer/text()', x))[1]::varchar(8)      AS ehrrus7pjsam6gjmver,
               (XPATH('/DLO_ACCESSED_POST/EhrRusLS9R0Q4VR4/text()', x))[1]::varchar(10)::int   AS ehrrusls9r0q4vr4,
               (XPATH('/DLO_ACCESSED_POST/EhrRusLS9R0Q4VR4Ver/text()', x))[1]::varchar(8)      AS ehrrusls9r0q4vr4ver
        FROM UNNEST(XPATH('DLO_ACCESSED_POSTS/body/DLO_ACCESSED_POSTS/DLO_ACCESSED_POST', p_xml)) AS x;
    
    ANALYZE temp_dlo_accessed_posts;
    
    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;
    
    -- Удаление существующих записей
    DELETE FROM aps.t_dlo_accessed_posts;
    
    -- Вставка новых записей из временной таблицы
    INSERT INTO
        aps.t_dlo_accessed_posts (dloaccessedpostsid,
                                  postcode,
                                  lastname,
                                  firstname,
                                  fathername,
                                  category,
                                  beginaccessdate,
                                  endaccessdate,
                                  beginworkdate,
                                  certificatedate,
                                  medicalspecialitycode,
                                  expulsiondate,
                                  snils,
                                  posttype,
                                  speciality,
                                  lpumain,
                                  ehrrus7pjsam6gjm,
                                  ehrrusls9r0q4vr4)
    SELECT p.id,
           doccode,
           lastname,
           firstname,
           fathername,
           category,
           beginaccessdate,
           endaccessdate,
           beginworkdate,
           certificatedate,
           medicalspecialitycode,
           expulsiondate,
           snils,
           posttype,
           speciality,
           lpumain,
           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;

END;
$$;
