DROP PROCEDURE IF EXISTS akuz.sp_akuz_ftload_dloaccesspost;
CREATE OR REPLACE PROCEDURE akuz.sp_akuz_ftload_dloaccesspost(
    xml text DEFAULT NULL,
    isnew integer DEFAULT 1,
    id uuid DEFAULT NULL,
    queuename character varying DEFAULT NULL,
    datetime timestamp DEFAULT NULL,
    "user" uuid DEFAULT NULL
)
AS
$$
DECLARE
    v_contentencoding varchar(255);
    v_type            varchar(255);
    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';
    
    IF (p_xml IS NOT NULL) THEN
        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]::text::uuid                  AS id,
                   (XPATH('/DLO_ACCESSED_POST/POST_CODE/text()', x))[1]::text                 AS postcode,
                   (XPATH('/DLO_ACCESSED_POST/LAST_NAME/text()', x))[1]::text                 AS lastname,
                   (XPATH('/DLO_ACCESSED_POST/FIRST_NAME/text()', x))[1]::text                AS firstname,
                   (XPATH('/DLO_ACCESSED_POST/FATHER_NAME/text()', x))[1]::text               AS fathername,
                   (XPATH('/DLO_ACCESSED_POST/CATEGORY/text()', x))[1]::text::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]::text   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]::text                     AS snils,
                   (XPATH('/DLO_ACCESSED_POST/POST_TYPE/text()', x))[1]::text                 AS posttype,
                   (XPATH('/DLO_ACCESSED_POST/SPECIALITY/text()', x))[1]::text                AS speciality,
                   (XPATH('/DLO_ACCESSED_POST/LPU_MAIN/text()', x))[1]::text                  AS lpumain,
                   (XPATH('/DLO_ACCESSED_POST/EhrRus7PJSAM6GJM/text()', x))[1]::text::int     AS ehrrus7pjsam6gjm,
                   (XPATH('/DLO_ACCESSED_POST/EhrRus7PJSAM6GJMVer/text()', x))[1]::varchar(8) AS ehrrus7pjsam6gjmver,
                   (XPATH('/DLO_ACCESSED_POST/EhrRusLS9R0Q4VR4/text()', x))[1]::text::int     AS ehrrusls9r0q4vr4,
                   (XPATH('/DLO_ACCESSED_POST/EhrRusLS9R0Q4VR4Ver/text()', x))[1]::varchar(4) AS ehrrusls9r0q4vr4ver
            FROM UNNEST(XPATH('DLO_ACCESSED_POSTS/body/DLO_ACCESSED_POSTS/DLO_ACCESSED_POST', p_xml)) AS x;
        
        ANALYZE temp_dlo_accessed_posts;
        
        DELETE FROM akuz.t_dlo_accessed_posts;
        
        INSERT INTO
            akuz.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,
               postcode,
               lastname,
               firstname,
               fathername,
               category,
               beginaccessdate,
               endaccessdate,
               beginworkdate,
               certificatedate,
               medicalspecialitycode,
               expulsiondate,
               snils,
               (SELECT pt.posttypeid FROM akuz.t_post_type pt WHERE pt.code = p.posttype LIMIT 1),
               (SELECT s.specialityid FROM akuz.t_speciality s WHERE s.code = p.speciality LIMIT 1),
               (SELECT lm.lpuid FROM akuz.t_lpu_main lm WHERE lm.lpucode = p.lpumain LIMIT 1),
               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 IF;
END;
$$
    LANGUAGE plpgsql;
