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

    p_fileid = fileid;
    CREATE TEMP TABLE temp_zglv AS
    SELECT fileid,
           (XPATH('/ZGLV/VERSION/text()', x))[1]::VARCHAR(5)   AS version,
           (XPATH('/ZGLV/DATA/text()', x))[1]::TEXT::TIMESTAMP AS data,
           (XPATH('/ZGLV/YEAR/text()', x))[1]::TEXT::INT       AS year,
           (XPATH('/ZGLV/MONTH/text()', x))[1]::TEXT::INT      AS month,
           (XPATH('/ZGLV/FILENAME/text()', x))[1]::VARCHAR(26) AS filename,
           (XPATH('/ZGLV/IDDT/text()', x))[1]::VARCHAR(3)      AS iddt,
           CURRENT_TIMESTAMP                                   AS receiveddate,
           CURRENT_TIMESTAMP                                   AS receivedtime
    FROM UNNEST(XPATH('/FACT_LIST/ZGLV', xml)) AS x;

    CREATE TEMP TABLE temp_fact AS
    SELECT gen_random_uuid()                                     AS itemid,
           (XPATH('/FACT/FAM/text()', x))[1]::VARCHAR(40)        AS fam,
           (XPATH('/FACT/IM/text()', x))[1]::VARCHAR(40)         AS im,
           (XPATH('/FACT/OT/text()', x))[1]::VARCHAR(40)         AS ot,
           (XPATH('/FACT/DR/text()', x))[1]::TEXT::TIMESTAMP     AS dr,
           (XPATH('/FACT/ENP/text()', x))[1]::VARCHAR(16)        AS enp,
           (XPATH('/FACT/CODE_MO/text()', x))[1]::VARCHAR(6)     AS code_mo,
           (XPATH('/FACT/NSCHET/text()', x))[1]::VARCHAR(6)      AS nschet,
           (XPATH('/FACT/DATE_B/text()', x))[1]::TEXT::TIMESTAMP AS date_b,
           (XPATH('/FACT/DATE_E/text()', x))[1]::TEXT::TIMESTAMP AS date_e,
           (XPATH('/FACT/ISHOD/text()', x))[1]::TEXT::INT        AS ishod,
           (XPATH('/FACT/RESULT/text()', x))[1]::TEXT::INT       AS result,
           (XPATH('/FACT/PR_D_N/text()', x))[1]::TEXT::INT       AS pr_d_n,
           zglv.year                                             AS year,
           zglv.month                                            AS month,
           zglv.iddt                                             AS iddt,
           NULL::varchar(36)                                     AS dd_id

    FROM UNNEST(XPATH('/FACT_LIST/FACT', xml)) AS x
    LEFT JOIN temp_zglv zglv ON TRUE;

    CREATE TEMP TABLE temp_destination AS
    SELECT gen_random_uuid()                                         AS itemid,
           (XPATH('/FACT/ENP/text()', x))[1]::VARCHAR(16)            AS enp,
           (XPATH('/FACT/NSCHET/text()', x))[1]::VARCHAR(15)         AS nschet,
           (XPATH('/FACT/NSCHET/text()', x))[1]::VARCHAR(15)         AS nschet,
           (XPATH('/DESTINATION/VID_CODE/text()', x2))[1]::TEXT::INT AS vid_code
    FROM UNNEST(XPATH('/FACT_LIST/FACT', xml)) AS x
    CROSS JOIN LATERAL UNNEST(XPATH('/FACT/DESTINATION', x)) AS x2;


    UPDATE temp_fact t1
    SET dd_id = (SELECT dispdirectid
                 FROM rds.t_disp_direct t2
                 LEFT JOIN rds.t_disp_direct_fact t3 ON t3.dispdirect = t2.dispdirectid
                 LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = t1.iddt AND v016.dateend IS NULL
                 WHERE (t2.enp = t1.enp AND t2.year = t1.year AND t2.v016dispt = v016.v016id AND
                        t3.dispdirectfactid IS NULL)
                 LIMIT 1);

    UPDATE temp_fact t1
    SET dd_id =(SELECT dispdirectid
                FROM rds.t_disp_direct t2
                LEFT JOIN rds.t_disp_direct_fact t3 ON t3.dispdirect = t2.dispdirectid
                LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = t1.iddt AND v016.dateend IS NULL
                WHERE t2.fam = t1.fam
                  AND t2.im = t1.im
                  AND t2.ot = t2.ot
                  AND t2.dr = t1.dr
                  AND t2.year = t1.year
                  AND t2.v016dispt = v016.v016id
                  AND t3.dispdirectfactid IS NULL
                LIMIT 1)
    WHERE t1.dd_id IS NULL;

    UPDATE temp_fact t1
    SET dd_id = (SELECT dispdirectid
                 FROM rds.t_disp_direct t2
                 LEFT JOIN rds.t_disp_direct_fact t3 ON t3.dispdirect = t2.dispdirectid
                 LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = t1.iddt AND v016.dateend IS NULL
                 WHERE t2.fam = t1.fam
                   AND t2.im = t1.im
                   AND t2.dr = t1.dr
                   AND t2.year = t1.year
                   AND t2.v016dispt = v016.v016id
                   AND t3.dispdirectfactid IS NULL
                 LIMIT 1)
    WHERE dd_id IS NULL;

    UPDATE temp_fact t1
    SET dd_id = (SELECT dispdirectid
                 FROM rds.t_disp_direct t2
                 LEFT JOIN rds.t_disp_direct_fact t3 ON t3.dispdirect = t2.dispdirectid
                 LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = t1.iddt AND v016.dateend IS NULL
                 WHERE t2.fam = t1.fam
                   AND t2.ot = t1.ot
                   AND t2.dr = t1.dr
                   AND t2.year = t1.year
                   AND t2.v016dispt = v016.v016id
                   AND t3.dispdirectfactid IS NULL
                 LIMIT 1)
    WHERE dd_id IS NULL;

    UPDATE temp_fact t1
    SET dd_id = (SELECT dispdirectid
                 FROM rds.t_disp_direct t2
                 LEFT JOIN rds.t_disp_direct_fact t3 ON t3.dispdirect = t2.dispdirectid
                 LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = t1.iddt AND v016.dateend IS NULL
                 WHERE t2.im = t1.im
                   AND t2.ot = t1.ot
                   AND t2.dr = t1.dr
                   AND t2.year = t1.year
                   AND t2.v016dispt = v016.v016id
                   AND t3.dispdirectfactid IS NULL
                 LIMIT 1)
    WHERE dd_id IS NULL;

    UPDATE rds.t_disp_direct_fact_file dispfactfile
    SET version      = zglv.version,
        filename     = zglv.filename,
        year         = zglv.year,
        month        = zglv.month,
        v016dispt    = v016.v016id,
        receiveddate = zglv.receiveddate,
        receivedtime = zglv.receivedtime,
        worker       = worker
    FROM temp_zglv zglv
    LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = zglv.iddt AND v016.dateend IS NULL
    WHERE dispfactfile.fileid = zglv.fileid
      AND dispfactfile.fileid = p_fileid;

    INSERT INTO
        rds.t_disp_direct_fact
    (dispdirectfactid,
     datebegin,
     dateend,
     fam,
     im,
     ot,
     rslt_d,
     dispdirect,
     dr,
     enp,
     code_mo,
     ishod,
     nschet,
     year,
     month,
     v016dispt,
     dispdirectfactfile,
     pr_d_n)
    SELECT f.itemid,
           f.date_b,
           f.date_e,
           f.fam,
           f.im,
           f.ot,
           f.result,
           f.dd_id,
           f.dr,
           f.enp,
           f.code_mo,
           f.ishod,
           f.nschet,
           f.year,
           f.month,
           v016.v016id,
           p_fileid,
           f.pr_d_n
    FROM temp_fact f
    LEFT JOIN rds.t_v016_dispt v016
            ON v016.iddt = f.iddt AND v016.dateend IS NULL;


    INSERT INTO
        rds.t_disp_direct_fact_destination
    (dispdirectfactdestinationid, destinationtype, dispdirectfact)
    SELECT gen_random_uuid(),
           d.vid_code,
           f.itemid
    FROM temp_destination d
    JOIN temp_fact f ON f.enp = d.enp AND f.nschet = d.nschet;

    UPDATE rds.t_disp_direct_info ddi
    SET result = 'Диспансеризация пройдена с ' || TO_CHAR(f.date_b, 'YYYY-MM-DD') ||
                 ' по ' || TO_CHAR(COALESCE(f.date_e, CURRENT_DATE), 'YYYY-MM-DD')
    FROM rds.t_disp_direct dd
    JOIN temp_fact f ON f.dd_id = dd.dispdirectid
    WHERE dd.dispdirectid = ddi.dispdirect;

    INSERT INTO
        rds.t_disp_direct
    (dispdirectid,
     dispdirectfactfile,
     fam,
     im,
     ot,
     w,
     dr,
     enp,
     vpolis,
     spolis,
     npolis,
     doctype,
     docser,
     docnum,
     snils_p,
     comentp,
     mr,
     year,
     v016dispt,
     month_q,
     code_smo,
     code_mo,
     snils_d,
     podr,
     uch)
    SELECT gen_random_uuid(),
           p_fileid,
           dd.fam,
           dd.im,
           dd.ot,
           dd.w,
           dd.dr,
           dd.enp,
           dd.vpolis,
           dd.spolis,
           dd.npolis,
           dd.doctype,
           dd.docser,
           dd.docnum,
           dd.snils_p,
           dd.comentp,
           dd.mr,
           CASE
               WHEN EXTRACT(MONTH FROM t1.date_e) = 12 THEN dd.year + 1
               ELSE dd.year
               END AS year,
           v016.v016id,
           CASE
               WHEN EXTRACT(MONTH FROM t1.date_e) = 12 THEN 1
               ELSE EXTRACT(MONTH FROM t1.date_e)
               END AS month_q,
           dd.code_smo,
           dd.code_mo,
           dd.snils_d,
           dd.podr,
           dd.uch
    FROM temp_fact t1
    JOIN rds.t_disp_direct dd ON dd.dispdirectid = t1.dd_id
    LEFT JOIN rds.t_v016_dispt v016 ON v016.iddt = 'ДВ2'
        AND v016.dateend IS NULL
    WHERE t1.result IN (6, 11, 12, 13, 14, 15);

    DROP TABLE temp_fact;
    DROP TABLE temp_destination;
    DROP TABLE temp_zglv;

END;
$$