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"?>' ||
           XMLELEMENT(
                   NAME "DN_LIST",
                   XMLFOREST(
                           XMLELEMENT(
                                   NAME "ZGLV",
                                   (SELECT XMLFOREST(
                                                   XMLELEMENT(NAME "VERSION", '1.0'),
                                                   XMLELEMENT(NAME "DATA", TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD')),
                                                   XMLELEMENT(NAME "FILENAME",
                                                              (SELECT REPLACE(filename, 'IDN', 'DN') FROM zglv)),
                                                   XMLELEMENT(NAME "MCOD", mcod))
                                    FROM zglv
                                   )
                           ),
                           (SELECT XMLAGG
                                   (
                                           XMLELEMENT
                                           (
                                                   NAME "ZL",
                                                   XMLFOREST
                                                   (
                                                           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;
$$
