DROP PROCEDURE IF EXISTS rds.sp_loadanswerfromfrllorequestprocess(UUID, TEXT);
DROP PROCEDURE IF EXISTS rds.sp_loadanswerfromfrllorequestprocess(UUID, XML);
CREATE OR REPLACE PROCEDURE rds.sp_loadanswerfromfrllorequestprocess(
    "ExportId" uuid,
    "Xml" TEXT)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_exportid     uuid := "ExportId";
    p_xml          xml  := "Xml"::xml;
    v_generalerror TEXT = '';

BEGIN

    CREATE TEMP TABLE temp_answer
        ON COMMIT DROP AS
    SELECT
        (XPATH('/element/document_id/text()', x))[1]::VARCHAR(100)::uuid AS document_id,
        (XPATH('/element/status/text()', x))[1]::VARCHAR(10)::INT        AS status,
        (XPATH('/element/reg_num/text()', x))[1]::VARCHAR(100)           AS reg_num,
        (XPATH('/element/pers_needs_id/text()', x))[1]::VARCHAR(100)     AS pers_needs_id,
        (XPATH('/element/code/text()', t))[1]::TEXT                      AS code,
        (XPATH('/element/description/text()', t))[1]::TEXT               AS description
    FROM UNNEST(XPATH('root/result/element', p_xml)) x
    CROSS JOIN LATERAL UNNEST(XPATH('element/errors/element', x)) t;

    IF ((
            SELECT COUNT(*) FROM temp_answer a WHERE a.document_id IS NULL AND a.description IS NOT NULL
        ) > 0) THEN

        SELECT
            STRING_AGG(a.description, '||')
        INTO v_generalerror
        FROM temp_answer a
        WHERE
              a.document_id IS NULL
          AND a.description IS NOT NULL;


        UPDATE rds.t_export_frllo_data fed
        SET
            frllostatus = 400,
            frlloerror  = v_generalerror
        WHERE
              fed.exportfrllo = p_exportid
          AND fed.frllostatus <> 1;

    ELSE
        CREATE TEMP TABLE temp_upd
        (
            id                           SERIAL PRIMARY KEY,
            reg_num                      VARCHAR(100),
            status                       INTEGER,
            pers_needs_id                VARCHAR(100),
            error                        TEXT,
            exportfrllodrugrequest       uuid,
            exportfrllodrugrequestitemid uuid
        ) ON COMMIT DROP;

        CREATE TEMP TABLE temp_dt
        (
            id INT
        ) ON COMMIT DROP ;

        INSERT INTO
            temp_upd (reg_num, status, pers_needs_id, error, exportfrllodrugrequest, exportfrllodrugrequestitemid)
        SELECT
            a.reg_num,
            a.status,
            a.pers_needs_id,
            a.error,
            fed.exportfrllodrugrequest,
            fed.exportfrllodrugrequestitemid
        FROM rds.t_export_frllo_drug_request_item fed
        CROSS JOIN LATERAL (
            SELECT
                a.reg_num,
                a.status,
                MAX(a.pers_needs_id)                                    AS pers_needs_id,
                STRING_AGG(a2.code || '-' || a2.description || '/', '') AS error
            FROM temp_answer a
            LEFT JOIN temp_answer a2 ON a2.document_id = a.document_id
            WHERE
                  fed.exportfrllodrugrequest = p_exportid
              AND a.document_id = fed.exportfrllodrugrequestitemid
            GROUP BY a.document_id, a.reg_num, a.status
            ) a;

        WHILE EXISTS
        (
            SELECT
                1
            FROM temp_upd
        )
            LOOP

                WITH
                    cte AS (
                        UPDATE fed
                            SET
                                frlloid = u.reg_num,
                                frllostatus = u.status,
                                frllopersneedsid = u.pers_needs_id,
                                frlloerror = u.error
                            FROM rds.t_export_frllo_drug_request_item fed
                                JOIN temp_upd u ON u.exportfrllodrugrequest = fed.exportfrllodrugrequest AND
                                                   u.exportfrllodrugrequestitemid = fed.exportfrllodrugrequestitemid
                            RETURNING u.id
                    )
                INSERT
                INTO
                    temp_dt(id)
                SELECT
                    id
                FROM cte;


                UPDATE pdd
                SET frlloid = fed.frlloid
                FROM rds.t_patient_drug_demand pdd
                JOIN rds.t_export_frllo_drug_request_item fed ON fed.objid = pdd.patientdrugdemandid
                JOIN temp_upd u ON u.exportfrllodrugrequest = fed.exportfrllodrugrequest
                WHERE
                      fed.frlloid IS NOT NULL
                  AND pdd.frlloid IS NULL
                  AND EXISTS
                      (
                          SELECT
                              1
                          FROM temp_dt d
                          WHERE d.id = u.id
                      );

                DELETE
                FROM temp_upd u
                WHERE
                    EXISTS
                    (
                        SELECT
                            1
                        FROM temp_dt d
                        WHERE d.id = u.id
                    );

                DELETE FROM temp_dt;

            END LOOP;
    END IF;
END;
$$