CREATE OR REPLACE PROCEDURE rds.sp_loadanswerfromfrllorecipeprocess(
    exportid uuid,
    xml xml
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_exportid uuid := exportid;
    p_xml      xml  := xml;
    v_type     int ;
BEGIN
    
    SELECT e.datatype
    INTO v_type
    FROM rds.t_export_frllo_recipe e
    WHERE e.exportfrllorecipeid = p_exportid;
    
    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/reg_num/text()', x))[1]::varchar(100)           AS reg_num,
               COALESCE(
                   (XPATH('/element/recipe_id/text()', x))[1]::varchar(100),
                   (XPATH('/element/relise_id/text()', x))[1]::varchar(100))    AS recipe_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('/errors/element', x)) t;
    
    IF ((SELECT COUNT(*) FROM temp_answer) > 0) THEN
        RAISE EXCEPTION 'Структура загружаемого файла не соответствует документации, либо Вы загружаете пустой файл!';
    END IF;
    
    CREATE TEMP TABLE temp_upd
    (
        id                      SERIAL PRIMARY KEY,
        reg_num                 varchar(100),
        status                  int,
        recipe_id               varchar(100),
        error                   text,
        exportfrllorecipe       uuid,
        exportfrllorecipedataid uuid
    );
    
    CREATE TEMP TABLE temp_dt
    (
        id INT
    );
    
    INSERT INTO
        temp_upd (reg_num, status, recipe_id, error, exportfrllorecipe, exportfrllorecipedataid)
    SELECT a.reg_num,
           a.status,
           a.recipe_id,
           a.error,
           fed.exportfrllorecipe,
           fed.exportfrllorecipedataid
    FROM rds.t_export_frllo_recipe_data fed
    CROSS JOIN LATERAL (
        SELECT a.reg_num,
               a.status,
               MAX(a.recipe_id)                                  AS recipe_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.exportfrllorecipe = p_exportId
          AND a.document_id = fed.exportfrllorecipedataid
        GROUP BY a.document_id, a.reg_num, a.status
        ) a;
    
    WHILE EXISTS (SELECT 1 FROM temp_upd)
        LOOP
            
            WITH cte AS
                     (SELECT t.reg_num,
                             t.status,
                             t.recipe_id,
                             t.error,
                             fed.exportfrllorecipe,
                             fed.exportfrllorecipedataid,
                             t.id AS id
                      FROM rds.t_export_frllo_recipe_data fed
                      JOIN temp_upd t ON t.exportfrllorecipe = fed.exportfrllorecipe AND
                                         t.exportfrllorecipedataid = fed.exportfrllorecipedataid
                      LIMIT 10000
                     ),
                 cte2 AS
                     (
                         UPDATE rds.t_export_frllo_recipe_data fed
                             SET frlloid = cte.reg_num,
                                 frllostatus = cte.status,
                                 frllorecipe_id = cte.recipe_id,
                                 frlloerror = cte.error
                             WHERE fed.exportfrllorecipedataid = cte.exportfrllorecipedataid
                                 AND fed.exportfrllorecipe = cte.exportfrllorecipe
                             RETURNING cte.id
                     )
            INSERT
            INTO
                temp_dt (id)
            SELECT id
            FROM cte2;
            
            IF (v_type = 0) THEN
                
                UPDATE r
                SET frlloid = fed.frlloid
                FROM rds.t_recipe r
                JOIN rds.t_export_frllo_recipe_data fed ON fed.objid = r.recipeid
                JOIN temp_upd u ON u.exportfrllorecipe = fed.exportfrllorecipe
                WHERE fed.frlloid IS NOT NULL
                  AND r.frlloid IS NULL
                  AND EXISTS (SELECT 1 FROM temp_dt d WHERE d.id = u.id);
                
                UPDATE r
                SET frllotype = (CASE WHEN r.frllotype = 1 THEN 0 ELSE NULL END)
                FROM rds.t_recipe r
                JOIN rds.t_export_frllo_recipe_data fed ON fed.objid = r.recipeid
                JOIN temp_upd u ON u.exportfrllorecipe = fed.exportfrllorecipe
                WHERE fed.frllostatus = 400
                  AND r.frllotype IS NOT 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;
            
            ELSIF (v_type = 1) THEN
                
                UPDATE re
                SET frlloid = fed.frlloid
                FROM rds.t_recipe_element re
                JOIN rds.t_export_frllo_recipe_data fed ON fed.objid = re.recipeelementid
                JOIN temp_upd u ON u.exportfrllorecipe = fed.exportfrllorecipe
                WHERE fed.frlloid IS NOT NULL
                  AND re.frlloid IS NULL
                  AND EXISTS (SELECT 1 FROM temp_dt d WHERE d.id = u.id);
                
                
                UPDATE re
                SET frllotype = (CASE WHEN re.frllotype = 1 THEN 0 ELSE NULL END)
                FROM rds.t_recipe_element re
                JOIN rds.t_export_frllo_recipe_data fed ON fed.objid = re.recipeelementid
                JOIN temp_upd u ON u.exportfrllorecipe - fed.exportfrllorecipe
                WHERE fed.frllostatus = 400
                  AND re.frllotype IS NOT 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 IF;
        
        END LOOP;
END;
$$