DROP PROCEDURE IF EXISTS rds.sp_loadanswerfromfrllorecipeprocess(UUID, TEXT);
CREATE OR REPLACE PROCEDURE rds.sp_loadanswerfromfrllorecipeprocess(
    "ExportId" uuid,
    "Xml" TEXT,
    "WorkWithError" boolean = false)
LANGUAGE plpgsql
AS
$$
DECLARE
    p_exportid      uuid := "ExportId";
    p_xml           xml  := "Xml"::XML;
    p_workwitherror boolean  := "WorkWithError";
    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;

    DROP TABLE IF EXISTS temp_answer;

    CREATE TEMP TABLE temp_answer ON COMMIT DROP 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
            LEFT JOIN LATERAL UNNEST(XPATH('element/errors/element', x)) t ON TRUE;

    IF NOT EXISTS
    (
        SELECT 1
        FROM temp_answer
    ) THEN
        RAISE EXCEPTION 'Структура загружаемого файла не соответствует документации, либо Вы загружаете пустой файл!';
        RETURN;
    END IF;

    IF ((
            SELECT COUNT(*) FROM temp_answer a WHERE a.document_id IS NULL AND a.description IS NOT NULL
        ) > 0) THEN

        SELECT
            STRING_AGG(a.description, '||')
        INTO v_generalerror
        FROM temp_answer a
        WHERE
              a.document_id IS NULL
          AND a.description IS NOT NULL;

        UPDATE rds.T_EXPORT_FRLLO_RECIPE_DATA fed
        SET
            frllostatus = 400,
            frlloerror  = v_generalerror
        WHERE
              fed.ExportFrlloRecipe = 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,
            reg_num                 VARCHAR(100),
            status                  INT,
            recipe_id               VARCHAR(100),
            error                   TEXT,
            exportfrllorecipe       uuid,
            exportfrllorecipedataid uuid
        );

        DROP TABLE IF EXISTS temp_dt;
        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.recipe_id,
                                    frllostatus = cte.status,
                                    regnum = cte.reg_num,
                                    frlloerror = cte.error
                                from cte
                                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 p_workwitherror = false THEN (CASE WHEN r.FrlloType = 0 THEN null ELSE 0 END) 
                                            else r.FrlloType + 1
                                            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 p_workwitherror = false THEN (CASE WHEN re.FrlloType = 0 THEN null ELSE 0 END) 
                                            else re.FrlloType + 1
                                            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;
$$