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

CREATE OR REPLACE PROCEDURE rds.sp_loadanswerfromfrlloprocess(
    "ExportId" uuid,
    "Xml" text
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml          xml  := "Xml"::xml;
    p_exportid     int  := "ExportId";
    v_entityid     int;
    v_palid        int;
    v_rrid         int;
    v_specid       int;
    v_bskid        int;
    v_generalerror text := '';

BEGIN
    
    SELECT e.entities
    INTO v_entityid
    FROM rds.t_export_frllo e
    WHERE e.exportfrlloid = p_exportid;
    
    SELECT e.entityid
    INTO v_palid
    FROM vclib.t_entities e
    WHERE e.entname = 'PALLIATIVE_REGISTER';
    
    SELECT e.entityid
    INTO v_rrid
    FROM vclib.t_entities e
    WHERE e.entname = 'RR_PATIENT';
    
    SELECT e.entityid
    INTO v_specid
    FROM vclib.t_entities e
    WHERE e.entname = 'SPECIAL_BENEFITS_REGISTR';
    
    SELECT e.entityid
    INTO v_bskid
    FROM vclib.t_entities e
    WHERE e.entname = 'BSK_REGISTER';
    
    DROP TABLE IF EXISTS temp_answer;
    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)::integer    AS status,
               (XPATH('/element/reg_num/text()', x))[1]::varchar(100)           AS reg_num,
               (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 ((SELECT COUNT(*) FROM temp_answer) > 0) 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 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 or fed.FrlloStatus is null);
    
    ELSE
        
        UPDATE rds.t_export_frllo_data fed
        SET frlloid     = a.reg_num,
            frllostatus = a.status,
            frlloerror  = a.error
        FROM (SELECT an.document_id,
                     an.reg_num,
                     an.status,
                     STRING_AGG(a2.code || '-' || a2.description, '/') AS error
              FROM temp_answer an
              LEFT JOIN temp_answer a2
                  ON a2.document_id = an.document_id
              WHERE fed.exportfrllo = p_exportid
                AND an.document_id = fed.exportfrllodataid
              GROUP BY an.document_id, an.reg_num, an.status
             ) a
        WHERE fed.exportfrllo = p_exportid
          AND fed.exportfrllodataid = a.document_id;
        
        IF (v_entityid = v_palid) THEN
            UPDATE rds.t_palliative_register pal
            SET frlloid = fed.frlloid
            FROM rds.t_export_frllo_data fed
            WHERE fed.objid = pal.palliativeregisterid
              AND fed.exportfrllo = p_exportid
              AND fed.frlloid IS NOT NULL
              AND pal.frlloid IS NULL;
        END IF;
        
        IF (v_entityid = v_rrid) THEN
            UPDATE rds.t_rr_patient rr
            SET frlloid = fed.frlloid
            FROM rds.t_export_frllo_data fed
            WHERE fed.objid = rr.id
              AND fed.exportfrllo = p_exportid
              AND fed.frlloid IS NOT NULL
              AND rr.frlloid IS NULL;
        END IF;
        
        IF (v_entityid = v_specid) THEN
            UPDATE rds.t_special_benefits_registr spec
            SET frlloid = fed.frlloid
            FROM rds.t_export_frllo_data fed
            WHERE fed.objid = spec.specialbenefitsregistrid
              AND fed.exportfrllo = p_exportid
              AND fed.frlloid IS NOT NULL
              AND spec.frlloid IS NULL;
        END IF;
        
        IF (v_entityid = v_bskid) THEN
            UPDATE rds.t_bsk_register bsk
            SET frlloid = frlloid
            FROM rds.t_export_frllo_data fed
            WHERE fed.objid = bsk.bskregisterid
              AND fed.exportfrllo = p_exportid
              AND fed.frlloid IS NOT NULL
              AND bsk.frlloid IS NULL;
        END IF;
    END IF;
END;
$$