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


CREATE OR REPLACE PROCEDURE rds.sp_loadanswerfromfrllorecipeprocess(
    "ExportId" uuid,
    "Xml" text
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_exportid     uuid := "ExportId";
    p_xml          xml  := "Xml"::XML;
    v_type         int ;
    v_generalerror text :=  '';
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;
    
    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,
            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 rds.t_recipe r
                    SET frlloid = fed.frlloid
                    FROM rds.t_export_frllo_recipe_data fed
                    JOIN temp_upd u ON u.exportfrllorecipe = fed.exportfrllorecipe
                    WHERE fed.objid = r.recipeid
                      AND fed.frlloid IS NOT NULL
                      AND r.frlloid IS NULL
                      AND EXISTS (SELECT 1 FROM temp_dt d WHERE d.id = u.id);
                    
                    UPDATE rds.t_recipe r
                    SET frllotype = (CASE WHEN r.frllotype = 1 THEN 0 ELSE NULL END)
                    FROM rds.t_export_frllo_recipe_data fed
                    JOIN temp_upd u ON u.exportfrllorecipe = fed.exportfrllorecipe
                    WHERE fed.objid = r.recipeid
                      AND 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 rds.t_recipe_element re
                    SET frlloid = fed.frlloid
                    FROM rds.t_export_frllo_recipe_data fed
                    JOIN temp_upd u ON u.exportfrllorecipe = fed.exportfrllorecipe
                    WHERE fed.objid = re.recipeelementid
                      AND fed.frlloid IS NOT NULL
                      AND re.frlloid IS NULL
                      AND EXISTS (SELECT 1 FROM temp_dt d WHERE d.id = u.id);
                    
                    
                    UPDATE rds.t_recipe_element re
                    SET frllotype = (CASE WHEN re.frllotype = 1 THEN 0 ELSE NULL END)
                    FROM rds.t_export_frllo_recipe_data fed
                    JOIN temp_upd u ON u.exportfrllorecipe - fed.exportfrllorecipe
                    WHERE fed.objid = re.recipeelementid
                      AND 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 IF;
END;
$$