CREATE OR REPLACE PROCEDURE rds.sp_dnident(
    xml xml
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    
    CREATE TEMP TABLE zglv AS
        SELECT (XPATH('/ZGLV/VERSION/text()', x))[1]::varchar(5)   AS version,
               (XPATH('/ZGLV/DATA/text()', x))[1]::text::timestamp AS data,
               (XPATH('/ZGLV/FILENAME/text()', x))[1]::varchar(24) AS filename,
               (XPATH('/ZGLV/MCOD/text()', x))[1]::text::int       AS mcod,
               CURRENT_TIMESTAMP                                   AS receiveddate,
               CURRENT_TIMESTAMP                                   AS receivedtime
        FROM UNNEST(XPATH('/DN_LIST/ZGLV', xml)) x;
    
    CREATE TEMP TABLE zl AS
        SELECT (XPATH('/ZL/ID_PAC/text()', x))[1]::varchar(36) AS id_pac,
               (XPATH('/ZL/FAM/text()', x))[1]::varchar(40)    AS fam,
               (XPATH('/ZL/IM/text()', x))[1]::varchar(40)     AS im,
               (XPATH('/ZL/OT/text()', x))[1]::varchar(40)     AS ot,
               (XPATH('/ZL/DR/text()', x))[1]::text::timestamp AS dr,
               (XPATH('/ZL/NPOLIS/text()', x))[1]::VARCHAR(20) AS npolis,
               (XPATH('/ZL/ENP/text()', x))[1]::VARCHAR(16)    AS enp,
               (XPATH('/ZL/VPOLIS/text()', x))[1]::text::int   AS vpolis,
               (XPATH('/ZL/SPOLIS/text()', x))[1]::VARCHAR(10) AS spolis,
               (XPATH('/ZL/SMO/text()', x))[1]::text::int      AS smo,
               (XPATH('/ZL/SNILS/text()', x))[1]::varchar(14)  AS snils,
               NULL::VARCHAR(36)                               AS patient,
               e'Верные данные: \n'::text                      AS errorstring
        FROM UNNEST(XPATH('/DN_LIST/ZL', xml)) x;
    
    CREATE TEMP TABLE contacts AS
        SELECT (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]::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 zlid,
               (XPATH('/ZL/ID_PAC/text()', x))[1]::varchar(36)          AS id_pac,
               (XPATH('/DN/DATE_BEGIN/text()', x2))[1]::text::timestamp AS date_begin,
               (XPATH('/DN/DATE_OUT/text()', x2))[1]::text::timestamp   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/SMILS_MR/text()', x2))[1]::varchar(14)       AS snils_mr,
               (XPATH('/DN/SPEC/text()', x2))[1]::text::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('/PRESENCES/DATE_PLAN/text()', x2))[1]::text::timestamp AS date_plan,
               (XPATH('/PRESENCES/PLACE/text()', x2))[1]::text::int           AS place,
               (XPATH('/PRESENCES/MO_P/text()', x2))[1]::text::int            AS mo_p
        FROM UNNEST(XPATH('/DN_LIST/ZL', xml)) x
        CROSS JOIN LATERAL UNNEST(XPATH('ZL/DN/PRESENCES', x)) x2;
    
    --step 1
    UPDATE zl t1
    SET patient = (SELECT MAX(patientid)
                   FROM rds.t_patient t2
                   WHERE (t2.lastname = t1.fam AND t2.firstname = t1.im AND t2.birthday = t1.dr AND
                          t2.polisnumber = t1.npolis)
                  )
    WHERE t1.patient IS NULL;
    
    --step 2
    UPDATE zl t1
    SET patient = (SELECT MAX(patientid)
                   FROM rds.t_patient t2
                   WHERE (t2.lastname = t1.fam AND t2.firstname = t1.im AND t2.birthday = t1.dr)
                  )
    WHERE t1.patient IS NULL;
    
    --step 3
    UPDATE zl t1
    SET patient = (SELECT MAX(patientid)
                   FROM rds.t_patient t2
                   WHERE (t2.lastname = t1.fam AND t2.firstname = t1.im AND t2.polisnumber = t1.npolis)
                  )
    WHERE t1.patient IS NULL;
    
    --step 4
    UPDATE zl t1
    SET patient = (SELECT MAX(patientid)
                   FROM rds.t_patient t2
                   WHERE (t2.lastname = t1.fam AND t2.birthday = t1.dr AND t2.polisnumber = t1.npolis)
                  )
    WHERE t1.patient IS NULL;
    
    UPDATE zl p
    SET errorstring = 'Ошибка идентификации'
    WHERE p.patient IS NULL;
    
    UPDATE p
    SET errorstring = errorstring || 'Пациент умер. Дата смерти: ' || patient.datedeath::date::text || e'\n'
    FROM zl p
    JOIN rds.t_patient patient ON p.patient = patient.patientid
    WHERE patient.datedeath IS NOT NULL;
    
    UPDATE p
    SET errorstring = errorstring || 'Фамилия: ' || patient.lastname || e'/n'
    FROM zl p
    JOIN rds.t_patient patient ON p.patient = patient.patientid
    WHERE p.fam <> patient.lastname;
    
    UPDATE p
    SET errorstring = errorstring || 'Имя: ' || patient.firstname || e'/n'
    FROM zl p
    JOIN rds.t_patient patient ON p.patient = patient.patientid
    WHERE p.im <> patient.firstname;
    
    UPDATE p
    SET errorstring = errorstring || 'Отчество: ' || patient.fathername || e'/n'
    FROM zl p
    JOIN rds.t_patient patient ON p.patient = patient.patientid
    WHERE p.ot <> patient.fathername
      AND p.ot IS NOT NULL;
    
    UPDATE p
    SET errorstring = errorstring || 'Дата рождения: ' || patient.birthday::date::text || e'/n'
    FROM zl p
    JOIN rds.t_patient patient ON p.patient = patient.patientid
    WHERE p.dr <> patient.birthday;
    
    UPDATE p
    SET errorstring = errorstring || 'СНИЛС: ' || patient.snils || e'/n'
    FROM zl p
    JOIN rds.t_patient patient ON p.patient = patient.patientid
    WHERE p.snils <> patient.snils
      AND p.snils IS NOT NULL;
    
    UPDATE p
    SET errorstring = errorstring || 'Тип полиса: ' || patient.policyform::varchar(2) || e'/n'
    FROM zl p
    JOIN rds.t_patient patient ON p.patient = patient.patientid
    WHERE p.vpolis <> patient.policyform
      AND p.vpolis IS NOT NULL;
    
    UPDATE p
    SET errorstring = errorstring || 'Серия полиса: ' || patient.polisserial || e'/n'
    FROM zl p
    JOIN rds.t_patient patient ON p.patient = patient.patientid
    WHERE p.spolis <> patient.polisserial
      AND p.spolis IS NOT NULL;
    
    UPDATE p
    SET errorstring = errorstring || 'Номер полиса: ' || patient.polisnumber || e'/n'
    FROM zl p
    JOIN rds.t_patient patient ON p.patient = patient.patientid
    WHERE p.npolis <> patient.polisnumber;
    
    UPDATE p
    SET errorstring = (SELECT CASE
                                  WHEN dn.snils_mr IS NOT NULL THEN 'СНИЛС врача: ' || patient.snils_d
                                  ELSE 'Не указан СНИЛС врача'
                              END
                      )
    FROM zl p
    JOIN rds.t_patient patient ON p.patient = patient.patientid
    JOIN dn dn ON dn.zlid = p.id_pac
    WHERE dn.snils_mr <> patient.snils_d;
    
    SELECT ((SELECT REPLACE(filename, 'IDN', 'DN') FROM zglv)),
           '<?xml version="1.0" encoding="Windows-1251"?>' ||
           (SELECT XMLELEMENT
                   (
                       NAME "DN_LIST",
                       (SELECT XMLAGG
                               (
                                   XMLELEMENT(
                                       NAME "ZGLV",
                                       XMLFOREST
                                       (
                                           '1.0' AS "VERSION",
                                           TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD') AS "DATA",
                                           REPLACE(filename, 'IDN', 'DN') AS "FILENAME",
                                           mcod AS "MCOD"
                                       )
                                   )
                               )
                        FROM zglv
                       )
                   ),
                   (SELECT XMLAGG
                           (
                               XMLELEMENT
                               (
                                   NAME "ZL",
                                   XMLELEMENT(NAME "ID_PAC", p.id_pac),
                                   XMLELEMENT(NAME "FAM", patient.lastname),
                                   XMLELEMENT(NAME "IM", patient.firstname),
                                   XMLELEMENT(NAME "OT", patient.fathername),
                                   XMLELEMENT(NAME "DR", TO_CHAR(patient.birthday, 'YYYY-MM-DD')),
                                   XMLELEMENT(NAME "ENP", patient.enp),
                                   XMLELEMENT(NAME "VPOLIS", patient.policyform),
                                   XMLELEMENT(NAME "SPOLIS", patient.polisserial),
                                   XMLELEMENT(NAME "NPOLIS", patient.polisnumber),
                                   XMLELEMENT(NAME "SMO", patient.f002smo),
                                   XMLELEMENT(NAME "SNILS", patient.snils),
                                   (SELECT XMLAGG
                                           (
                                               XMLELEMENT
                                               (
                                                   NAME "CONTACTS",
                                                   XMLELEMENT(NAME "CONTACT", contacts.contact),
                                                   XMLELEMENT(NAME "TYPE", contacts.type)
                                               )
                                           )
                                    FROM contacts
                                    WHERE contacts.id_pac = p.id_pac
                                   ),
                                   (SELECT XMLAGG(
                                               XMLELEMENT
                                               (
                                                   NAME "DN",
                                                   XMLELEMENT(NAME "DATE_BEGIN", dn.date_begin),
                                                   XMLELEMENT(NAME "DATE_OUT", dn.date_out),
                                                   XMLELEMENT(NAME "DS", dn.ds),
                                                   XMLELEMENT(NAME "MO", dn.mo),
                                                   XMLELEMENT(NAME "STATUS", dn.status),
                                                   XMLELEMENT(NAME "SNILS_MR", patient.snils_d),
                                                   XMLELEMENT(NAME "SPEC", dn.spec),
                                                   (SELECT XMLAGG(
                                                               XMLELEMENT(
                                                                   NAME "PRESENCES",
                                                                   XMLELEMENT(NAME "DATE_PLAN", presences.date_plan),
                                                                   XMLELEMENT(NAME "PLACE", presences.place),
                                                                   XMLELEMENT(NAME "MO_P", presences.mo_p)
                                                               )
                                                           )
                                                    FROM presences
                                                    WHERE presences.id_pac = p.id_pac
                                                   )
                                               )
                                           )
                                    FROM dn
                                    WHERE dn.zlid = p.id_pac
                                   ),
                                   CASE
                                       WHEN p.errorstring = ('Верные данные: ' || e'\n')
                                           THEN NULL
                                       ELSE p.errorstring
                                   END
                               )
                           )
                    FROM zl p
                    LEFT JOIN rds.t_patient patient ON p.patient = patient.patientid
                   )
           ) ::TEXT;
    
    DROP TABLE presences;
    DROP TABLE contacts;
    DROP TABLE dn;
    DROP TABLE zl;
    DROP TABLE zglv;

END;
$$
