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

CREATE OR REPLACE PROCEDURE rds.sp_loadanswerfromfrllorequestprocess(
    "ExportId" uuid,
    "Xml" text
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_exportid     uuid := "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)::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,
               (XPATH('/element/pers_needs_id/text()', x))[1]::varchar(100)     AS pers_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
        CROSS JOIN LATERAL UNNEST(XPATH('/errors/element', x)) t;
    
    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                       integer,
            pers_needs_id                varchar(100),
            error                        text,
            exportfrllodrugrequest       uuid,
            exportfrllodrugrequestitemid uuid
        );
        
        CREATE TEMP TABLE temp_dt
        (
            id int
        );
        
        INSERT INTO
            temp_upd (reg_num, status, pers_needs_id, error, exportfrllodrugrequest, exportfrllodrugrequestitemid)
        SELECT a.reg_num,
               a.status,
               a.pers_needs_id,
               a.error,
               fed.exportfrllodrugrequest,
               fed.exportfrllodrugrequestitemid
        FROM rds.t_export_frllo_drug_request_item fed
        CROSS JOIN LATERAL (
            SELECT a.reg_num,
                   a.status,
                   MAX(a.pers_needs_id)                                    AS pers_needs_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.exportfrllodrugrequest = p_exportid
              AND a.document_id = fed.exportfrllodrugrequestitemid
            GROUP BY a.document_id, a.reg_num, a.status
            ) a;
        
        WHILE EXISTS (SELECT 1 FROM temp_upd)
            LOOP
                
                WITH cte AS (
                    UPDATE fed
                        SET
                            frlloid = u.reg_num,
                            frllostatus = u.status,
                            frllopersneedsid = u.pers_needs_id,
                            frlloerror = u.error
                        FROM rds.t_export_frllo_drug_request_item fed
                            JOIN temp_upd u ON u.exportfrllodrugrequest = fed.exportfrllodrugrequest AND
                                               u.exportfrllodrugrequestitemid = fed.exportfrllodrugrequestitemid
                        RETURNING u.id
                            )
                INSERT
                INTO
                    temp_dt(id)
                SELECT id
                FROM cte;
                
                
                UPDATE pdd
                SET frlloid = fed.frlloid
                FROM rds.t_patient_drug_demand pdd
                JOIN rds.t_export_frllo_drug_request_item fed ON fed.objid = pdd.patientdrugdemandid
                JOIN temp_upd u ON u.exportfrllodrugrequest = fed.exportfrllodrugrequest
                WHERE fed.frlloid IS NOT NULL
                  AND pdd.frlloid IS 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 LOOP;
    END IF;
END;
$$