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


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

BEGIN
    
    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/stock_id/text()', x))[1]::varchar(100)          AS stock_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_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,
            stock_id                 varchar(100),
            status                   int,
            error                    text,
            exportfrlloremains       uuid,
            exportfrlloremainsdataid int
        );
        
        CREATE TEMP TABLE temp_dt
        (
            id integer
        );
        
        INSERT INTO
            temp_upd (stock_id, status, error, exportfrlloremains, exportfrlloremainsdataid)
        SELECT a.stock_id,
               a.status,
               a.error,
               fed.exportfrlloremains,
               fed.exportfrlloremainsdataid
        FROM rds.t_export_frllo_remains_data fed
        CROSS JOIN LATERAL (
            SELECT a_inner.stock_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.exportfrlloremains = p_exportid
              AND a_inner.document_id = fed.exportfrlloremainsdataid
            GROUP BY a_inner.document_id, a_inner.stock_id, a_inner.status
            ) a;
        
        WHILE (EXISTS (SELECT 1 FROM temp_upd))
            LOOP
                
                WITH cte AS (SELECT upd.stock_id,
                                    upd.status,
                                    upd.error,
                                    upd.id,
                                    upd.exportfrlloremains,
                                    upd.exportfrlloremainsdataid
                             FROM rds.t_export_frllo_remains_data fed
                             JOIN temp_upd upd ON fed.exportfrlloremains = upd.exportfrlloremains AND
                                                  upd.exportfrlloremainsdataid = fed.exportfrlloremainsdataid
                             LIMIT 1000
                            ),
                     cte2 AS (
                         UPDATE rds.t_export_frllo_remains_data f
                             SET frlloid = cte.stock_id,
                                 frllostatus = cte.status,
                                 frlloerror = cte.error
                             FROM rds.t_export_frllo_remains_data fed
                             WHERE f.exportfrlloremainsdataid = fed.exportfrlloremainsdataid and fed.exportfrlloremainsdataid = cte.exportfrlloremainsdataid AND fed.exportfrlloremains = cte.exportfrlloremains
                             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
$$