--------------------------------------------------------
--  DDL for Procedure P_MANAGE_REQ_OBJ
--------------------------------------------------------
set define off;

  CREATE OR REPLACE EDITIONABLE PROCEDURE "ENVX_OPER"."P_MANAGE_REQ_OBJ" (
   -- '[{"request_id": 159,"branch_name": "AMDES","type_group": "commit","group_id": "a2s34d5f67c6x5a4sdf567f8dsa76c5vbn67r65s","file_name": "config/api-casa-v1/file1.txt"},{"request_id": 159,"branch_name": "AMDES","type_group": "commit","group_id": "a2s34d5f67c6x5a4sdf567f8dsa76c5vbn67r65s","file_name": "config/api-casa-v1/archivossssssssssssssssss.txt"}]'
   p_json_parameters IN OUT CLOB,
   p_object_order    IN OUT NUMBER,
   pi_json_objects   IN     CLOB,
   pi_name_object    IN     VARCHAR2 DEFAULT NULL,
   pi_select_action  IN     VARCHAR2,
   po_message        OUT    CLOB
) AS

   v_request_id   VARCHAR2(100);
   v_branches     VARCHAR2(100);
   v_type         VARCHAR2(100);
   v_group_id     VARCHAR2(100);
   v_object_name  VARCHAR2(100);
   v_object_order VARCHAR2(100);
   v_coincidence  NUMBER        := 0;
   v_json_array   json_array_t  := json_array_t();
   json_aux       JSON;
   json_aux_del   CLOB;
   obj            json_object_t := json_object_t();
BEGIN
   v_object_order := p_object_order + 1;
   v_request_id   := JSON_VALUE(pi_json_objects, '$.request_id');
   v_branches     := JSON_VALUE(pi_json_objects, '$.branch_name');
   v_type         := JSON_VALUE(pi_json_objects, '$.type_group');
   v_group_id     := JSON_VALUE(pi_json_objects, '$.group_id');
   v_object_name  := JSON_VALUE(pi_json_objects, '$.file_name');
   
   CASE
      WHEN pi_select_action = 'add' THEN
      

         json_aux := json(pi_json_objects);
         v_coincidence := 0;--instr(p_json_parameters, v_object_name);
         
         select count(*)
           into v_coincidence
           from json_table(json(nvl(p_json_parameters,'[]')), '$[*]' columns (obj VARCHAR2(1000) PATH '$.file_name')) j
           where j.obj = JSON_VALUE(json_aux,'$.file_name');
         
         IF v_coincidence = 0 OR v_coincidence IS NULL THEN
         
         v_json_array := json_array_t.parse(nvl(p_json_parameters,'[]'));
         v_json_array.append(json_aux);
          p_json_parameters := v_json_array.to_clob;
            
            
            po_message        := 'El objeto "'
                                 || v_object_name
                                 || '" se ha agregado con exito';
            p_object_order    := v_object_order;
            
         ELSE
            po_message := 'El objeto "'
                          || v_object_name
                          || '" ya existe';
         END IF;

----------------------------------------------------------------------------------------------------------------------------------
      WHEN pi_select_action = 'delete' THEN
      
         json_aux_del      := p_json_parameters;
         p_json_parameters := NULL;
         FOR obj IN (
            SELECT c_object,
                   file_name
              FROM JSON_TABLE (
                      JSON(json_aux_del), '$[*]'
                      COLUMNS
                         c_object           JSON            PATH '$',
                                  file_name VARCHAR2 (1000) PATH '$.file_name'
                   )
         )
         LOOP
            IF obj.file_name != pi_name_object THEN
               v_json_array.append(obj.c_object);
               p_json_parameters := v_json_array.to_string;
            ELSE
               po_message     := 'El objeto "'
                                 || pi_name_object
                                 || '" ha sido eliminado';
               p_object_order := p_object_order - 1;
            END IF;
         END LOOP;

      ELSE
         NULL;
   END CASE;

EXCEPTION
   WHEN OTHERS THEN
      po_message := 'Error: ' || sqlerrm;
END;

/