/*
database test -> collation utf8mb3_general_ci
table configuration -> collation latin1_swedish_ci
table logtable -> collation utf8mb3_general_ci
procedure logExecutedQuery -> db_collation latin1_swedish_ci
procedure getparamvalue -> db_collation utf8mb3_general_ci


database test_automation -> collation latin1_swedish_ci
procedure loadEngine -> db_collation latin1_swedish_ci
*/



CREATE PROCEDURE test.`logExecutedQuery`(IN logTable VARCHAR(255), IN clientId BIGINT,IN clientName VARCHAR(255),IN procedureName VARCHAR(255), IN blockName VARCHAR(255),IN stat VARCHAR(255), IN messageText TEXT, IN rowsAffected BIGINT)
BEGIN
		SET @insert_log= CONCAT("INSERT INTO ",logTable,"(date_created,procedure_name,block_name,STATUS,message,rows_affected,client_name,client_id)
		VALUES(NOW(), '",procedureName,"', '",blockName,"', '",stat,"', '",messageText,"',",rowsAffected,",'",clientName,"',",clientId," )");
		PREPARE insert_logStatement FROM @insert_log;
		EXECUTE insert_logStatement;
		DEALLOCATE PREPARE insert_logStatement;
	END


CREATE PROCEDURE test.`getparamvalue`(IN clientID VARCHAR(20),IN clientName VARCHAR(255),IN shard VARCHAR(255),IN logTable VARCHAR(255),IN configParam VARCHAR(255),OUT returnGroupCodes VARCHAR(255),INOUT p_status VARCHAR(10), OUT error_block VARCHAR(255))
BEGIN
	IF p_status = 1 THEN
		BEGIN
			DECLARE EXIT HANDLER FOR SQLEXCEPTION
			BEGIN
				GET DIAGNOSTICS CONDITION 1
					@errorSQLState = RETURNED_SQLSTATE, @errorNo = MYSQL_ERRNO, @errorMessage = MESSAGE_TEXT;
				SET @errorMessage = CONCAT('Error [',@errorSQLState,'][',@errorNo,']: ',replace(@errorMessage,"'",''));
				select @errorMessage;
				SET @p_status = -1;
				CALL stage_automation.cm_error_log (@error_block,@errorMessage,@namcheProc, @subJobId, @acronym);
				CALL logExecutedQuery(logTable,clientID,clientName, 'getConfigurationParameterValue',CONCAT('get parameter value for ',configParam,' from configuration table'),'FAILED',@errorMessage,0);
			END;
			SET @checkParameterValue='';
			SET @queryStatement = CONCAT('SELECT parameter_value INTO @checkParameterValue  FROM ',shard,'.configuration WHERE client_id = ',clientID,' AND configuration_parameter = "',configParam,'" LIMIT 1');
			SET @rows_affected =0;
			PREPARE queryStatement FROM @queryStatement;
			EXECUTE queryStatement;
			SET @rows_affected=(SELECT ROW_COUNT());
			DEALLOCATE PREPARE queryStatement;
			
			SET returnGroupCodes = @checkParameterValue;
			CALL logExecutedQuery(logTable,clientID,clientName, 'getConfigurationParameterValue',CONCAT('get parameter value for ',configParam,' from configuration table'),'SUCCESS',CONCAT('parameter ',configParam,' , values : "',@checkParameterValue,'". '),0);
		END;
		END IF;

	END



CREATE PROCEDURE test_automation.`loadEngine`(IN id VARCHAR(10) ,IN _date VARCHAR(20), IN SUBJOBID  VARCHAR(255),IN ACRONYM VARCHAR(30),IN configVersion VARCHAR(10) )
BEGIN
	
	SET net_read_timeout = 99999;
	SET net_write_timeout = 99999;
	SET @@GLOBAL.max_sp_recursion_depth = 255;
	SET @@session.max_sp_recursion_depth = 255;
	
	SET @logTable = 'log_engine';
	
	SET @p_status = 1;
	SET @errorBlock='';
	SET @configTable= CONCAT(configuration);
	SET @layoutVersion='';
	SET @filterValue='';
	
	SET @stageShard = '';
	SET @passedDate = _date;
	SET @errorMessage='';
	SET @subJobId = SUBJOBID;
	SET @acronym = ACRONYM;
	SET @errorNumber = '';
	SET @clientID=0;
	SET @clientName='DEMO';
	SET @configParamForTask = 'UpdateTaskStatus';
	SET @isTrueForAutoTask=0;
	SET @MemberPcp=0;


	SET @errorBlock = 'changestatus';
	BEGIN
			BEGIN
				SET @value = '';
				CALL test.getConfigurationParameterValue(@clientID,@clientName,@shard,@logTable,@configParamForTask,@value,@p_status,@errorBlock);
				CALL test.logExecutedQueryWithQuery(@logTable,@clientID,@clientName,'loadCMEngine','get parameter_value from configuration ','SUCCESS',CONCAT('get parameter_value = ',@value),0,'');
			END;
		END IF;
	END;


call test_automation.loadEngine("111", "2025-07-29", "123123123", "demo", "3.0");