CREATE OR REPLACE PROCEDURE rds.sp_dispinfoload(
    fileid uuid,
    xml xml,
    worker uuid
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_fileid uuid := fileid;
    p_worker uuid := worker;
BEGIN

    CREATE TEMPORARY TABLE zglv AS
    SELECT p_fileid                                            AS fileid,
           (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(26) AS filename,
           (XPATH('/ZGLV/SMO/text()', x))[1]::text::integer    AS smo,
           CURRENT_TIMESTAMP                                   AS receiveddate,
           CURRENT_TIMESTAMP                                   AS receivedtime
    FROM UNNEST(XPATH('/INFO_LIST/ZGLV', xml)) x;

    CREATE TEMPORARY TABLE zap AS
    SELECT gen_random_uuid()                                    AS itemid,
           z.smo                                                AS smo,
           (XPATH('/INFO/CODE_MO/text()', x))[1]::text::int     AS mo,
           (XPATH('/INFO/YEAR/text()', x))[1]::text::int        AS year,
           (XPATH('/INFO/FAM/text()', x))[1]::varchar(40)       AS fam,
           (XPATH('/INFO/IM/text()', x))[1]::varchar(40)        AS im,
           (XPATH('/INFO/OT/text()', x))[1]::varchar(40)        AS ot,
           (XPATH('/INFO/DR/text()', x))[1]::text::timestamp    AS dr,
           (XPATH('/INFO/ENP/text()', x))[1]::varchar(16)       AS enp,
           (XPATH('/INFO/S_POL/text()', x))[1]::varchar(10)     AS s_pol,
           (XPATH('/INFO/N_POL/text()', x))[1]::varchar(20)     AS n_pol,
           (XPATH('/INFO/IDDT/text()', x))[1]::varchar(3)       AS iddt,
           (XPATH('/INFO/F_INF/text()', x))[1]::text::int       AS f_inf,
           (XPATH('/INFO/D_INF/text()', x))[1]::text::timestamp AS d_inf,
           (XPATH('/INFO/N_INF/text()', x))[1]::varchar(50)     AS n_inf,
           (XPATH('/INFO/TIP_INFO/text()', x))[1]::text::int    AS tip_info,
           (XPATH('/INFO/RESULT/text()', x))[1]::varchar(50)    AS result
    FROM UNNEST(XPATH('/INFO_LIST/INFO', xml)) x
    LEFT JOIN zglv z ON TRUE;

    UPDATE rds.t_disp_direct_info_file AS dispfileinfo
    SET version      = zglv.version,
        receiveddate = zglv.receiveddate,
        receivedtime = zglv.receivedtime,
        worker       = p_worker
    FROM zglv
    WHERE zglv.fileid = dispfileinfo.fileid
      AND dispfileinfo.fileid = p_fileid;

    INSERT INTO
        rds.t_disp_direct_info (dispdirectinfoid,
                                date,
                                dispdirect,
                                dispdirectinfotype,
                                dispdirectinfofile,
                                addr,
                                code_mo,
                                code_smo,
                                v016dispt,
                                result,
                                enp,
                                tip_info)
    SELECT zap.itemid,
           zap.d_inf,
           NULL,
           zap.f_inf,
           p_fileid,
           zap.n_inf,
           zap.mo,
           zap.smo,
           v016.v016id,
           zap.result,
           zap.enp,
           zap.tip_info
    FROM zap
    LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = zap.iddt AND v016.dateend IS NULL;

    --step 0
    UPDATE rds.t_disp_direct_info AS t1
    SET dispdirect = (SELECT t2.dispdirectid
                      FROM rds.t_disp_direct AS t2
                      LEFT JOIN rds.t_disp_direct_fact AS ddf ON ddf.dispdirect = t2.dispdirectid
                      LEFT JOIN rds.t_v016_dispt AS v016 ON v016.iddt = zap.iddt AND v016.dateend IS NULL
                      WHERE t2.enp = zap.enp
                        AND t2.v016dispt = v016.v016id
                        AND t2.year = zap.year
                        AND ddf.dispdirectfactid IS NULL
                      LIMIT 1)
    FROM zap
    WHERE zap.itemid = t1.dispdirectinfoid;

    --step 1
    UPDATE t1
    SET t1.dispdirect = (SELECT dispdirectid
                         FROM rds.t_disp_direct t2
                         LEFT JOIN rds.t_disp_direct_fact ddf ON ddf.dispdirect = t2.dispdirectid
                         LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = zap.iddt AND v016.dateend IS NULL
                         WHERE (t2.fam = zap.fam AND t2.im = zap.im AND t2.ot = zap.ot AND t2.dr = zap.dr
                             AND t2.v016dispt = v016.v016id AND t2.year = zap.year
                             AND ddf.dispdirectfactid IS NULL)
                         LIMIT 1)
    FROM rds.t_disp_direct_info t1
    JOIN zap ON zap.itemid = t1.dispdirectinfoid
    WHERE t1.dispdirect IS NULL;

    --step 2
    UPDATE t1
    SET t1.dispdirect = (SELECT dispdirectid
                         FROM rds.t_disp_direct t2
                         LEFT JOIN rds.t_disp_direct_fact ddf ON ddf.dispdirect = t2.dispdirectid
                         LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = zap.iddt AND v016.dateend IS NULL
                         WHERE (t2.fam = zap.fam AND t2.im = zap.im AND t2.ot = zap.ot
                             AND COALESCE(t2.docser, '') || t2.docnum = COALESCE(zap.s_pol, '') || zap.n_pol
                             AND t2.v016dispt = v016.v016id AND t2.year = zap.year
                             AND ddf.dispdirectfactid IS NULL)
                         LIMIT 1)
    FROM rds.t_disp_direct_info t1
    JOIN zap ON zap.itemid = t1.dispdirectinfoid
    WHERE t1.dispdirect IS NULL;

    --step 3
    UPDATE t1
    SET t1.dispdirect = (SELECT dispdirectid
                         FROM rds.t_disp_direct t2
                         LEFT JOIN rds.t_disp_direct_fact ddf ON ddf.dispdirect = t2.dispdirectid
                         LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = zap.iddt AND v016.dateend IS NULL
                         WHERE (t2.fam = zap.fam AND t2.im = zap.im AND t2.dr = zap.dr
                             AND COALESCE(t2.docser, '') || t2.docnum = COALESCE(zap.s_pol, '') || zap.n_pol
                             AND t2.v016dispt = v016.v016id AND t2.year = zap.year
                             AND ddf.dispdirectfactid IS NULL)
                         LIMIT 1)
    FROM rds.t_disp_direct_info t1
    JOIN zap ON zap.itemid = t1.dispdirectinfoid
    WHERE t1.dispdirect IS NULL;

    --step 4
    UPDATE t1
    SET t1.dispdirect = (SELECT dispdirectid
                         FROM rds.t_disp_direct t2
                         LEFT JOIN rds.t_disp_direct_fact ddf ON ddf.dispdirect = t2.dispdirectid
                         LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = zap.iddt AND v016.dateend IS NULL
                         WHERE (t2.fam = zap.fam AND t2.ot = zap.ot AND t2.dr = zap.dr
                             AND COALESCE(t2.docser, '') || t2.docnum = COALESCE(zap.s_pol, '') || zap.n_pol
                             AND t2.v016dispt = v016.v016id AND t2.year = zap.year
                             AND ddf.dispdirectfactid IS NULL)
                         LIMIT 1)
    FROM rds.t_disp_direct_info t1
    JOIN zap ON zap.itemid = t1.dispdirectinfoid
    WHERE t1.dispdirect IS NULL;

    --step 5
    UPDATE t1
    SET t1.dispdirect = (SELECT dispdirectid
                         FROM rds.t_disp_direct t2
                         LEFT JOIN rds.t_disp_direct_fact ddf ON ddf.dispdirect = t2.dispdirectid
                         LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = zap.iddt AND v016.dateend IS NULL
                         WHERE (t2.im = zap.im AND t2.ot = zap.ot AND t2.dr = zap.dr
                             AND COALESCE(t2.docser, '') || t2.docnum = COALESCE(zap.s_pol, '') || zap.n_pol
                             AND t2.v016dispt = v016.v016id AND t2.year = zap.year
                             AND ddf.dispdirectfactid IS NULL)
                         LIMIT 1)
    FROM rds.t_disp_direct_info t1
    JOIN zap ON zap.itemid = t1.dispdirectinfoid
    WHERE t1.dispdirect IS NULL;

    --step 6
    UPDATE t1
    SET t1.dispdirect = (SELECT dispdirectid
                         FROM rds.t_disp_direct t2
                         LEFT JOIN rds.t_disp_direct_fact ddf ON ddf.dispdirect = t2.dispdirectid
                         LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = zap.iddt AND v016.dateend IS NULL
                         WHERE (COALESCE(t2.docser, '') || t2.docnum = COALESCE(zap.s_pol, '') || zap.n_pol
                             AND t2.v016dispt = v016.v016id AND t2.year = zap.year
                             AND ddf.dispdirectfactid IS NULL)
                         LIMIT 1)
    FROM rds.t_disp_direct_info t1
    JOIN zap ON zap.itemid = t1.dispdirectinfoid
    WHERE t1.dispdirect IS NULL;

    DROP TABLE zap;
    DROP TABLE zglv;
END;
$$;