DROP PROCEDURE IF EXISTS  rds.sp_LoadAnswerFromFrlloNeedsProcess;

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

BEGIN
    
    CREATE TEMP TABLE temp_answer AS
        SELECT (XPATH('/element/document_id/text()', x))[1]::varchar(100)           AS document_id,
               (XPATH('/element/status/text()', x))[1]::varchar(10)::int            AS status,
               (XPATH('/element/prognosis_needs_id/text()', x))[1]::varchar(100)     AS prognosis_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
        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_NEEDS_ELEMENT fed
        SET frllostatus = 400,
            frlloerror  = v_generalerror
        WHERE fed.ExportFrlloNeeds = p_exportid
          and fed.FrlloStatus <> 1;
    
    ELSE
        CREATE TEMP TABLE temp_upd
        (
            id                       serial PRIMARY KEY,
            prognosis_needs_id       varchar(100),
            status                   int,
            error                    text,
            ExportFrlloNeeds         int,
            FrlloDocumentId         varchar(100)
        );
        
        CREATE TEMP TABLE temp_dt
        (
            id integer
        );
        
        INSERT INTO
            temp_upd (prognosis_needs_id, status, error, ExportFrlloNeeds, FrlloDocumentId)
        SELECT a.prognosis_needs_id,
               a.status,
               a.error,
               fed.ExportFrlloNeeds,
               fed.FrlloDocumentId
        FROM rds.T_EXPORT_FRLLO_NEEDS_ELEMENT fed
        CROSS JOIN LATERAL (
            SELECT a_inner.prognosis_needs_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.ExportFrlloNeeds = p_exportid
              AND a_inner.document_id = fed.FrlloDocumentId
            GROUP BY a_inner.document_id, a_inner.prognosis_needs_id, a_inner.status
            ) a;
        
        WHILE (EXISTS (SELECT 1 FROM temp_upd))
            LOOP
                
                WITH cte AS (SELECT upd.prognosis_needs_id,
                                    upd.status,
                                    upd.error,
                                    upd.id,
                                    upd.ExportFrlloNeeds,
                                    upd.FrlloDocumentId
                             FROM rds.T_EXPORT_FRLLO_NEEDS_ELEMENT fed
                             JOIN temp_upd upd ON fed.ExportFrlloNeeds = upd.ExportFrlloNeeds AND upd.FrlloDocumentId = fed.FrlloDocumentId
                             LIMIT 1000
                            ),
                     cte2 AS (
                         UPDATE rds.T_EXPORT_FRLLO_NEEDS_ELEMENT f
                             SET --frlloid = cte.prognosis_needs_id,
                                 frllostatus = cte.status,
                                 frlloerror = cte.error
                             FROM rds.T_EXPORT_FRLLO_NEEDS_ELEMENT fed
                             WHERE f.FrlloDocumentId = fed.FrlloDocumentId and fed.FrlloDocumentId = cte.FrlloDocumentId AND fed.ExportFrlloNeeds = cte.ExportFrlloNeeds
                             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
$$