--------------------------------------------------------
--  DDL for Package Body PKG_REPOSITORY_ACTIONS
--------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE PACKAGE BODY "ENVX_OPER"."PKG_REPOSITORY_ACTIONS" AS 
   ----------------------------------------------------
   --------------------- FUNCTIONS --------------------

   ----------------------------------------------
	FUNCTION f_get_branch (
		p_json_parameters IN CLOB,
		po_message        OUT VARCHAR2
	) RETURN CLOB IS

		v_rest_request_id  VARCHAR2(500);
		v_id_repository    NUMBER;
      --	Variables para realizar la llamada http
		v_url              VARCHAR2(2000);
		v_http_method      VARCHAR2(100);
		v_http_headers     http_headers_tab;
		v_body             CLOB;
		v_http_status_code NUMBER;
		v_response         CLOB;
		v_response_code    VARCHAR2(50);
		v_error_message    VARCHAR2(400);
		v_count_reg        NUMBER := 0;
		v_list_size        VARCHAR2(100);
		v_page_number      VARCHAR2(100);
	BEGIN

      --------------------------------------------------------
      -- Se obtienen los valores de solicitud y repositorio --
		v_rest_request_id := JSON_VALUE(p_json_parameters,
           '$.rest_request_id');
		v_id_repository   := JSON_VALUE(p_json_parameters,
           '$.repository_id');
		v_list_size       := JSON_VALUE(p_json_parameters,
           '$.list_size');
		v_page_number     := JSON_VALUE(p_json_parameters,
           '$.page_number');
      -- Se obtienen los valores de solicitud y repositorio --
      --------------------------------------------------------

      -- Extraer URL de una tabla --
		v_url             := 'https://api.dev.envx.io/repo/v1/action/branch?restRequestId='
		         || v_rest_request_id
		         || '&repositoryId='
		         || v_id_repository
		         || '&groupType=default&pageNumber='
		         || v_page_number
		         || '&listSize='
		         || v_list_size;

		v_http_method     := 'GET';

      -------------------------------------
      -- Llamada REST a /action/branches --
		pkg_sicd_commons.p_rest_call_apim(
		                                 v_url,
		                                 v_http_method,
		                                 v_body,
		                                 v_http_status_code,
		                                 v_http_headers,
		                                 v_response,
		                                 v_response_code,
		                                 v_error_message
		);
   
      -- Llamada REST a /action/branches --
      -------------------------------------

		RETURN v_response;
	EXCEPTION
		WHEN OTHERS THEN
			po_message := 'Error: ' || sqlerrm;
	END;
   ----------------------------------------------
   ----------------------------------------------
	FUNCTION f_get_filesgroup (
		p_json_parameters IN CLOB,
		po_message        OUT VARCHAR2
	) RETURN CLOB AS

		v_json_aux         json :=
			JSON(
				p_json_parameters
			);

      -- Variables para la lectura de json de entrada --
		v_rest_request_id  VARCHAR2(100);
		v_request_id       NUMBER;
		v_repository_id    NUMBER;
		v_branch_name      VARCHAR2(50);
		v_type_group       VARCHAR2(100);
		v_list_size        VARCHAR2(100);
		v_page_number      VARCHAR2(100);
      -- Variables para la lectura de json de entrada --

      --	Variables para ejecutar p_rest_call --
		v_url              VARCHAR2(2000);
		v_http_method      VARCHAR2(100);
		v_http_headers     http_headers_tab;
		v_body             CLOB;
		v_http_status_code NUMBER;
		v_response         CLOB;
		v_response_code    VARCHAR2(50);
		v_error_message    VARCHAR2(400);
   --	Variables para ejecutar p_rest_call --
	BEGIN
      ---------------------------------------
      -- Obtencion de variables de trabajo --
		v_request_id      := JSON_VALUE(v_json_aux,
           '$.request_id');
		v_rest_request_id := JSON_VALUE(v_json_aux,
           '$.rest_request_id');
		v_repository_id   := JSON_VALUE(v_json_aux,
           '$.repository_id');
		v_branch_name     := JSON_VALUE(v_json_aux,
           '$.branch_name');
		v_type_group      := JSON_VALUE(v_json_aux,
           '$.type_group');
		v_list_size       := JSON_VALUE(v_json_aux,
           '$.list_size');
		v_page_number     := JSON_VALUE(v_json_aux,
           '$.page_number');
      --   v_files_group_id  := JSON_VALUE(v_json_aux, '$.filesGroupId');
      -- Obtencion de variables de trabajo --
      ---------------------------------------

		IF v_repository_id IS NOT NULL THEN
         -- Extraer URL de una tabla --
			v_url         := 'https://api.dev.envx.io/repo/v1/action/filesGroup?'
			         || 'restRequestId='
			         || v_rest_request_id
			         || '&repositoryId='
			         || v_repository_id
			         || '&branchName='
			         || v_branch_name
			         || '&groupType='
			         || v_type_group
			         || '&listSize='
			         || v_list_size
			         || '&pageNumber='
			         || v_page_number;
         --                       || '&filesGroupId='
         --                       || v_files_group_id;

			v_http_method := 'GET';

         -----------------------------------------------------
         -- Se realiza la llamada REST a /action/filesGroup --
			pkg_sicd_commons.p_rest_call_apim(
			                                 v_url,
			                                 v_http_method,
			                                 v_body,
			                                 v_http_status_code,
			                                 v_http_headers,
			                                 v_response,
			                                 v_response_code,
			                                 v_error_message
			);
         -- Se realiza la llamada REST a /action/filesGroup --
         -----------------------------------------------------

			RETURN v_response;
			po_message    := v_error_message;
		END IF;

	EXCEPTION
		WHEN OTHERS THEN
			po_message := 'Error: ' || sqlerrm;
	END;
   ----------------------------------------------
   ----------------------------------------------
	FUNCTION f_get_file (
		p_json_parameters IN CLOB,
		po_message        OUT VARCHAR2
	) RETURN CLOB AS

		v_json_aux         CLOB;

      -- Variables para la lectura de parametros de entrada --
		v_rest_request_id  VARCHAR2(100);
		v_request_id       NUMBER;
		v_repository_id    NUMBER;
		v_branch_name      VARCHAR2(50);
		v_type_group       VARCHAR2(100);
		v_group_id         VARCHAR2(100);
		v_list_size        VARCHAR2(100);
		v_page_number      VARCHAR2(100);
		v_http_headers     http_headers_tab;
      -- Variables para la lectura de parametros de entrada --

      --	Variables para ejecutar la llamada http --
		v_url              VARCHAR2(2000);
		v_http_method      VARCHAR2(100);
		v_body             CLOB;
		v_http_status_code NUMBER;
		v_response         CLOB;
		v_response_code    VARCHAR2(50);
		v_error_message    VARCHAR2(400);
   --	Variables para ejecutar la llamada http --
	BEGIN
      --------------------------------------------------------
      -- Se obtienen los valores de solicitud y repositorio --
		v_request_id      := JSON_VALUE(p_json_parameters,
           '$.request_id');
		v_rest_request_id := JSON_VALUE(p_json_parameters,
           '$.rest_request_id');
		v_repository_id   := JSON_VALUE(p_json_parameters,
           '$.repository_id');
		v_branch_name     := JSON_VALUE(p_json_parameters,
           '$.branch_name');
		v_type_group      := JSON_VALUE(p_json_parameters,
           '$.type_group');
		v_group_id        := JSON_VALUE(p_json_parameters,
           '$.group_id');
		v_list_size       := JSON_VALUE(p_json_parameters,
           '$.list_size');
		v_page_number     := JSON_VALUE(p_json_parameters,
           '$.page_number');
      -- Se obtienen los valores de solicitud y repositorio --
      --------------------------------------------------------

		IF v_repository_id IS NOT NULL THEN
         -- Extraer URL de una tabla --
			v_url         := 'https://api.dev.envx.io/repo/v1/action/files?'
			         || 'restRequestId='
			         || v_rest_request_id
			         || '&repositoryId='
			         || v_repository_id
			         || '&branchName='
			         || v_branch_name
			         || '&groupType='
			         || v_type_group
			         || '&filesGroupId='
			         || v_group_id
			         || '&listSize='
			         || v_list_size
			         || '&pageNumber='
			         || v_page_number;

			v_http_method := 'GET';
		
         ------------------------------------------------
         -- Se realiza la llamada REST a file --
			pkg_sicd_commons.p_rest_call_apim(
			                                 v_url,
			                                 v_http_method,
			                                 v_body,
			                                 v_http_status_code,
			                                 v_http_headers,
			                                 v_response,
			                                 v_response_code,
			                                 v_error_message
			);
         -- Se realiza la llamada REST a file --
         ------------------------------------------------

			RETURN v_response;
			po_message    := 'Mostrando la pagina ' || v_page_number;
		END IF;

	EXCEPTION
		WHEN OTHERS THEN
			po_message := 'Error: ' || sqlerrm;
	END;
   ----------------------------------------------
   ----------------------------------------------
	FUNCTION f_filesgroup_query (
		pi_filegroup_type   IN VARCHAR2,
		pi_filesgroup_jsonc IN VARCHAR2
	) RETURN CLOB AS
		v_query  CLOB;
		v_select VARCHAR2(4000);
	BEGIN
		SELECT UNIQUE 'SELECT ' || list_representation
		  INTO v_select
		  FROM repository_type_search t
		 WHERE t.alias = pi_filegroup_type;

		v_query := v_select
		           || '
      FROM JSON_TABLE(json(:'
		           || upper(pi_filesgroup_jsonc)
		           || q'~), '$'
        COLUMNS (
          pageNumber NUMBER PATH '$.pageNumber',
          totalPage NUMBER PATH '$.totalPage',
          filesGroup JSON PATH '$.filesGroup[*]',
          NESTED PATH '$.filesGroup[*]' COLUMNS(
            c_date VARCHAR2(100) PATH '$.date',
            author VARCHAR2(100) PATH '$.author',
            email VARCHAR2(100) PATH '$.email',
            groupId VARCHAR2(100) PATH '$.groupId',
            message VARCHAR2(100) PATH '$.message',
            total NUMBER PATH '$.stats.total',
            additions NUMBER PATH '$.stats.additions',
            deletions NUMBER PATH '$.stats.deletions'
          )
        )
      )
      ORDER BY 1;~';
		RETURN v_query;
	END;
   ----------------------------------------------
   ----------------------------------------------
	FUNCTION f_files_query (
		pi_files_jsonc IN VARCHAR2
	) RETURN CLOB AS
		v_query CLOB;
	BEGIN
		v_query := q'~SELECT jt.files as file_name, APEX_UTIL.URL_ENCODE(jt.files) AS file_id
      FROM JSON_TABLE(nvl(:~'
		           || pi_files_jsonc
		           || q'~,'[ ]'), '$.files[*]'
      COLUMNS (files PATH '$.file')
      ) jt
      UNION 
      SELECT file_name as file_name, APEX_UTIL.URL_ENCODE(file_name) AS file_id
      FROM json_table(:P4_SELECTED_OBJ,'$[*]' COLUMNS (file_name PATH '$.object_name' )) q
      ORDER BY 1;~';
		RETURN v_query;
	END;
   ----------------------------------------------
   ----------------------------------------------
	FUNCTION f_branches_query (
		pi_branches_jsonc IN VARCHAR2
	) RETURN CLOB AS
		v_query  CLOB;
		v_select VARCHAR2(4000);
	BEGIN
		v_query := q'~SELECT branch_name, branch_name as id_branch FROM JSON_TABLE(JSON(:~'
		           || pi_branches_jsonc
		           || q'~), '$.branch[*]'
		COLUMNS (
		  branch_name VARCHAR2(4000) PATH '$.name'
		));~';
		RETURN v_query;
	END;
   ----------------------------------------------
   ----------------------------------------------
	PROCEDURE p_manage_object_form (
		pi_selector     IN VARCHAR2,
		pi_groups       IN VARCHAR2,
		p_selected_list IN OUT CLOB,
		p_selected_obj  IN OUT CLOB
	) AS

		v_json_array       json_array_t := json_array_t();
		v_object           VARCHAR2(4000);
		v_object_aux       VARCHAR2(4000);
		v_array            apex_application_global.vc_arr2;
		v_coincidence_flag NUMBER := 0;
		v_log_message      CLOB;
		v_error            VARCHAR2(500);
	BEGIN
		IF pi_selector IS NOT NULL THEN
			FOR i IN 1..regexp_count(
			                        pi_selector,
			                        ':'
			            ) + 1 LOOP
				v_object := regexp_substr(
				                         pi_selector,
				                         '[^:]+',
				                         1,
				                         i
				            );
				BEGIN
					SELECT object_json
					  INTO v_object_aux
					  FROM
						JSON_TABLE (
							JSON(
								p_selected_obj
							), '$[*]'
							COLUMNS
								object_name PATH '$.object_name',
								object_json JSON PATH '$'
						)
					q
					 WHERE q.object_name = utl_url.unescape(v_object);
                
            -- v_object_aux := UTL_URL.UNESCAPE(v_object_aux);

				EXCEPTION
					WHEN no_data_found THEN
						v_object_aux := '{"object_id":"'
						                || pi_groups
						                || '","object_name":"'
						                || utl_url.unescape(v_object)
						                || '","object_order":"'
						                || i
						                || '"}';
					WHEN too_many_rows THEN
						v_error := sqlerrm;
						INSERT INTO internal_test VALUES (
							systimestamp,
							p_selected_obj,
							v_error,
							pi_selector
						);

						COMMIT;
				END;

				v_json_array.append(
				                   JSON(
						                  v_object_aux
					                  )
				);
			END LOOP;

			p_selected_list := pi_selector;
			p_selected_obj  := v_json_array.to_clob();
		END IF;
	END;
   ----------------------------------------------
	PROCEDURE p_save_object_form(
		pi_selected_obj         IN CLOB,
		pi_json_parameter       IN CLOB,
		po_message				OUT VARCHAR2
	) AS
		v_groups               VARCHAR2(400);
		v_selected_list        CLOB;                
		v_selector             CLOB;                -- 'test1.txt:test2.txt:test3.txt:test4.txt'
		v_id_request           NUMBER := 0;
		v_id_pattern           NUMBER := 0;
		v_id_req_hgroup        VARCHAR2(400);
		v_selected_obj         CLOB;
	BEGIN
	
		v_selector         := JSON_VALUE(pi_json_parameter, '$.selector');
		v_groups           := JSON_VALUE(pi_json_parameter, '$.groups');
		v_selected_list    := JSON_VALUE(pi_json_parameter, '$.selected_list');
		v_id_request       := JSON_VALUE(pi_json_parameter, '$.id_request');
		v_id_pattern       := JSON_VALUE(pi_json_parameter, '$.id_pattern');
		v_id_req_hgroup    := JSON_VALUE(pi_json_parameter, '$.id_req_hgroup');
	
		v_selected_obj     :=  pi_selected_obj;
		
		DELETE deployment_req_object
		 WHERE id_req_hgroup = v_id_req_hgroup;
		COMMIT;
	
		IF v_selector IS NOT NULL THEN
	
			INSERT INTO deployment_req_object (
				id_dep_req_object,
				id_request,
				id_pattern,
				id_req_hgroup,
				object_order,
				id_object_repo,
				id_reqdet_instance,
				object_dep_status,
				object_version,
				object_name,
				object_source_path
			)SELECT s_deployment_req_obj_id.NEXTVAL,
					   v_id_request,
					   v_id_pattern,
					   v_id_req_hgroup,
					   ROWNUM as object_order,
					   object_id,
					   NULL,
					   NULL,
					   NULL,
					   UTL_URL.UNESCAPE(REGEXP_SUBSTR(object_name, '[^/]+$', 1, 1)) as object_name,
					   UTL_URL.UNESCAPE(REGEXP_SUBSTR(object_name, '.*\/')) as object_path
				FROM
		JSON_TABLE(
			JSON(
				pi_selected_obj
		),
		           '$[*]'
			COLUMNS (
				object_name VARCHAR2 ( 300 ) PATH '$.object_name',
		           object_id VARCHAR2 ( 300 ) PATH '$.object_id'
		)
		);

	COMMIT;
		END IF;
	EXCEPTION
		WHEN OTHERS THEN
			po_message := 'Error: ' || sqlerrm;
	END;
	----------------------------------------------
END pkg_repository_actions;

/