DROP PROCEDURE IF EXISTS  rds.sp_loadanswerfromcertificatesprocess(UUID, TEXT);
DROP PROCEDURE IF EXISTS  rds.sp_loadanswerfromcertificatesprocess(UUID, XML);

CREATE OR REPLACE PROCEDURE rds.sp_loadanswerfromcertificatesprocess(
    "ExportId" uuid,
    "Xml" text
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_exportid     uuid := "ExportId";
    p_xml          xml  := "Xml"::xml;
    v_generalerror text := '';

BEGIN
    
    DROP TABLE IF EXISTS temp_answer;
    CREATE TEMP TABLE temp_answer 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/recipe_id/text()', x))[1]::varchar(100)         AS recipe_id,
               (XPATH('/element/recipe_release_id/text()', x))[1]::varchar(100) AS recipe_release_id,
               (XPATH('/element/digit_cert_id/text()', x))[1]::varchar(100)     AS digit_cert_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
        LEFT JOIN LATERAL UNNEST(XPATH('/errors/element', x)) t ON TRUE;
    
    IF ((SELECT COUNT(*) FROM temp_answer a WHERE a.document_id IS NULL AND a.description IS NOT NULL) > 0) THEN
        
        SELECT v_generalerror = v_generalerror || '||' || a.description
        FROM temp_answer a
        WHERE a.document_id IS NULL
          AND a.description IS NOT NULL
        GROUP BY a.description;
        
        UPDATE rds.t_export_frllo_ec_data fed
        SET frllostatus = 400,
            frlloerror  = v_generalerror
        WHERE fed.exportfrlloelectroniccertificate = p_exportid
          AND (fed.FrlloStatus <> 1 or fed.FrlloStatus is null);
    
    ELSE
        DROP TABLE IF EXISTS temp_upd;
        CREATE TEMP TABLE temp_upd
        (
            id                               serial PRIMARY KEY,
            recipe_id                        varchar(100),
            recipe_release_id                varchar(100),
            digit_cert_id                    varchar(100),
            status                           int,
            error                            text,
            electroniccertificateapplication uuid,
            exportfrlloecdataid              uuid,
            exportfrlloelectroniccertificate uuid
        );
        
        DROP TABLE IF EXISTS temp_dt;
        CREATE TEMP TABLE temp_dt
        (
            id integer
        );
        
        INSERT INTO
            temp_upd (recipe_id,
                      status,
                      error,
                      recipe_release_id,
                      digit_cert_id,
                      electroniccertificateapplication,
                      exportfrlloecdataid,
                      exportfrlloelectroniccertificate)
        SELECT a.recipe_id,
               a.status,
               a.error,
               a.recipe_release_id,
               a.digit_cert_id,
               st.electroniccertificateapplication,
               fed.exportfrlloecdataid,
               fed.exportfrlloelectroniccertificate
        FROM rds.t_export_frllo_ec_data fed
        JOIN rds.t_electronic_certificate_status st ON st.electroniccertificatestatusid = fed.objid
        CROSS JOIN LATERAL (
            SELECT a_inner.digit_cert_id,
                   MAX(a_inner.recipe_id),
                   MAX(a_inner.recipe_release_id),
                   a_inner.status,
                   STRING_AGG(a2.code || '-' || a2.description || '/', '') AS error,
                   a_inner.document_id
            FROM temp_answer a_inner
            LEFT JOIN temp_answer a2 ON a2.document_id = a_inner.document_id
            WHERE fed.exportfrlloelectroniccertificate = p_exportid
              AND a_inner.document_id = fed.objid
            GROUP BY a_inner.document_id, a_inner.digit_cert_id, a_inner.status
            ) a
        WHERE fed.exportfrlloelectroniccertificate = p_exportid;
        
        WHILE (EXISTS (SELECT 1 FROM temp_upd))
            LOOP
                
                WITH cte AS (SELECT upd.recipe_id,
                                    upd.status,
                                    upd.error,
                                    upd.id,
                                    upd.recipe_release_id,
                                    upd.digit_cert_id,
                                    upd.electroniccertificateapplication,
                                    upd.exportfrlloecdataid,
                                    upd.exportfrlloelectroniccertificate
                             FROM rds.t_export_frllo_ec_data fed
                             JOIN temp_upd upd ON fed.exportfrlloecdataid = upd.exportfrlloecdataid
                             LIMIT 1000
                            ),
                     cte2 AS (
                         UPDATE rds.t_electronic_certificate_application ap
                             SET frlloid = cte.digit_cert_id
                             FROM rds.t_electronic_certificate_application apppp
                             WHERE apppp.electroniccertificateapplicationid = ap.electroniccertificateapplicationid AND
                                   apppp.electroniccertificateapplicationid = cte.electroniccertificateapplication
                             RETURNING cte.id
                            )
                INSERT
                INTO
                    temp_dt (id)
                SELECT id
                FROM cte2;
                
                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
$$
