CREATE OR REPLACE PROCEDURE rds.sp_dninsert(
    fileid uuid,
    xml xml
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_fileid uuid = fileid;

BEGIN

    CREATE TEMP TABLE zglv AS
    SELECT (XPATH('/ZGLV/VERSION/text()', x))[1]::varchar(5),
           (XPATH('/ZGLV/DATA/text()', x))[1]::text::timestamp,
           (XPATH('/ZGLV/FILENAME/text()', x))[1]::VARCHAR(24),
           (XPATH('/ZGLV/MCOD/text()', x))[1]::TEXT::INT
    FROM UNNEST(XPATH('/DN_LIST/ZGLV', xml)) x;

    CREATE TEMP TABLE zl AS
    SELECT gen_random_uuid()                                   AS itemid,
           (XPATH('/DN_LIST/FileID/text()', x))[1]::text::uuid AS zglv,
           (XPATH('/ZL/ID_PAC/text()', x2))[1]::varchar(36)    AS id_pac,
           (XPATH('/ZL/FAM/text()', x2))[1]::varchar(40)       AS fam,
           (XPATH('/ZL/IM/text()', x2))[1]::varchar(40)        AS im,
           (XPATH('/ZL/OT/text()', x2))[1]::varchar(40)        AS ot,
           (XPATH('/ZL/DR/text()', x2))[1]::text::timestamp    AS dr,
           (XPATH('/ZL/ENP/text()', x2))[1]::VARCHAR(16)       AS enp,
           (XPATH('/ZL/VPOLIS/text()', x2))[1]::int            AS vpolis,
           (XPATH('/ZL/SPOLIS/text()', x2))[1]::VARCHAR(10)    AS spolis,
           (XPATH('/ZL/NPOLIS/text()', x2))[1]::VARCHAR(20)    AS npolis,
           (XPATH('/ZL/SMO/text()', x2))[1]::VARCHAR(5)        AS smo,
           (XPATH('/ZL/SNILS/text()', x2))[1]::VARCHAR(5)      AS snils
    FROM UNNEST(XPATH('/DN_LIST', xml)) x
    CROSS JOIN LATERAL UNNEST(XPATH('/DN_LIST/ZL', x)) x2;

    CREATE TEMP TABLE contacts AS
    SELECT gen_random_uuid()                                        AS contactid,
           (XPATH('/ZL/ID_PAC/text()', x))[1]::varchar(36)          AS id_pac,
           (XPATH('/CONTACTS/CONTACT/text()', x2))[1]::varchar(250) AS contact,
           (XPATH('/CONTACTS/TYPE/text()', x2))[1]::text::int       AS type
    FROM UNNEST(XPATH('/DN_LIST/ZL', xml)) x
    CROSS JOIN LATERAL UNNEST(XPATH('/ZL/CONTACTS', x)) x2;

    CREATE TEMP TABLE dn AS
    SELECT gen_random_uuid()                                   AS dnid,
           (XPATH('/ZL/ID_PAC/text()', x))[1]::varchar(36)     AS zlid,
           (XPATH('/DN/DN_ID/text()', x2))[1]::varchar(36)     AS disp_init_id,
           (XPATH('/DN/DATE_BEGIN/text()', x2))[1]::text::date AS date_begin,
           (XPATH('/DN/DATE_OUT/text()', x2))[1]::text::date   AS date_out,
           (XPATH('/DN/DS/text()', x2))[1]::varchar(10)        AS ds,
           (XPATH('/DN/MO/text()', x2))[1]::text::int          AS mo,
           (XPATH('/DN/STATUS/text()', x2))[1]::text::int      AS status,
           (XPATH('/DN/SNILS_MR/text()', x2))[1]::varchar(14)  AS snils_mr,
           (XPATH('/DN/SPEC/text()', x2))[1]::int              AS spec
    FROM UNNEST(XPATH('/DN_LIST/ZL', xml)) x
    CROSS JOIN LATERAL UNNEST(XPATH('/ZL/DN', x)) x2;

    CREATE TEMP TABLE presences AS
    SELECT gen_random_uuid()                                         AS presenceid,
           (XPATH('/ZL/ID_PAC/text()', x))[1]::varchar(36)           AS id_pac,
           (XPATH('/DN/DN_ID/text()', x2))[1]::varchar(36)           AS disp_init_id,
           (XPATH('/PRESENCES/DATE_PLAN/text()', x3))[1]::text::date AS date_plan,
           (XPATH('/PRESENCES/PLACE/text()', x3))[1]::text::int      AS place,
           (XPATH('/PRESENCES/MO_P/text()', x3))[1]::text::int       AS mo_p
    FROM UNNEST(XPATH('/DN_LIST/ZL', xml)) x
    CROSS JOIN UNNEST(XPATH('/ZL/DN', x)) x2
    CROSS JOIN UNNEST(XPATH('/DN/PRESENCES', x2)) x3;

    UPDATE item
    SET id_pac = zl.id_pac
    FROM rds.t_patient item
    JOIN zl zl ON item.enp = zl.enp;

    CREATE TEMPORARY TABLE temp_t_dn_item AS
    SELECT zl.id_pac,
           zl.fam,
           zl.im,
           zl.ot,
           zl.dr,
           zl.enp,
           zl.vpolis,
           zl.spolis,
           zl.npolis,
           zl.smo,
           zl.snils,
           NULL::uuid AS id,
           NULL::uuid AS patient
    FROM zl zl;

    UPDATE tempitem
    SET id      = gen_random_uuid(),
        patient = patient.patientid
    FROM temp_t_dn_item tempitem
    JOIN rds.t_patient patient ON patient.id_pac = tempitem.id_pac;

    CREATE TEMPORARY TABLE tempnotdistinctid AS
    SELECT dn.zlid,
           dn.date_begin,
           dn.date_out,
           dn.disp_init_id,
           dn.status,
           dn.ds,
           dn.snils_mr,
           dn.spec,
           dnitem.patient,
           dnitem.id_pac,
           dn.disp_init_id
    FROM dn dn
    JOIN temp_t_dn_item dnitem ON dn.zlid = dnitem.id_pac
    WHERE dnitem.patient IS NOT NULL;

    UPDATE dnitem
    SET date_out = dn.date_out
    FROM rds.t_dn_item dnitem
    JOIN tempnotdistinctid dn ON dn.disp_init_id = dnitem.dn_id
    WHERE dn.date_out IS NOT NULL
      AND dnitem.date_out IS NULL;

    INSERT INTO
        rds.t_dn_item(dnitemid, date_begin, date_out, dn_id, ds_disp, snils_vr, patient, id_pac, dnfile)
    SELECT *
    FROM tempnotdistinctid tempnotdist
    JOIN rds.t_mkb mkb ON mkb.mcbcode = tempnotdist.ds AND mkb.endcode IS NULL
    WHERE NOT EXISTS(SELECT 1
                     FROM rds.t_dn_item dnitem
                     JOIN rds.t_mkb mkb1 ON dnitem.ds_disp = mkb.mkbid AND mkb1.mcbcode = mkb.mcbcode
                     WHERE dnitem.id_pac = tempnotdist.id_pac
                       AND dnitem.date_begin = tempnotdist.date_begin
                    );

    INSERT INTO
        rds.t_dn_item_plan(dnitemplanid, date_plan, place, mo_p, dnitem)
    SELECT *
    FROM presences pres
    JOIN tempnotdistinctid dn ON dn.disp_init_id = pres.disp_init_id
    LEFT JOIN rds.t_lpu_main lpumain ON lpumain.mcod = pres.mo_p
    WHERE NOT EXISTS(SELECT 1
                     FROM rds.t_dn_item item
                     JOIN rds.t_dn_item_plan itemplan ON itemplan.dnitem = item.dnitemid
                     WHERE item.id_pac = dn.id_pac
                       AND itemplan.date_plan = pres.date_plan
                       AND itemplan.place = pres.place
                    );


--     INSERT INTO
--         rds.t_patient_contact (patientcontactid, contact, patientcontacttype
-- --                                        patient
--     )
--     select
--         gen_random_uuid(),
--         cont.contact,
--         (select code from rds.t_patient_contact_type where code = cont.type)
-- --         tempdnlistitem.patient
--     FROM contacts cont
--     JOIN temp_t_dn_item tempdnlistitem on tempdnlistitem.id_pac = cont.id_pac
--     WHERE NOT EXISTS(SELECT 1
--                      FROM RDS.T_RDS_PATIENT pat
--                      JOIN RDS.t_patient_contact patcont on pat.insuredid = patcont.patient and patcont.contact = cont.contact
--                      join rds.t_patient_contact_type conttype on conttype.code = patcont.patientcontacttype
--                      where pat.id_pac = tempdnlistitem.id_pac);
-- --

    drop table zglv;
    drop table zl;
    drop table contacts;
    drop table dn;
    drop table presences;
    drop table temp_t_dn_item;
    drop table tempnotdistinctid;
END;
$$
