CREATE DEFINER=`blisstechindo22`@`%` PROCEDURE `Default_Client_Upsert`(
	IN `_ClientID` CHAR(4),
	IN `_FirstName` VARCHAR(32),
	IN `_MiddleName` VARCHAR(64),
	IN `_LastName` VARCHAR(32),
	IN `_Phone` VARCHAR(16),
	IN `_Email` VARCHAR(64),
	IN `_Password` VARCHAR(150),
	IN `_Address` VARCHAR(64),
	IN `_AdminNote` VARCHAR(255),
	IN `_UUID` VARCHAR(255),
	IN `ShopID` CHAR(4),
	IN `_UserID` CHAR(4))
BEGIN
	# SET HANDLING ON ERROR DUPLICATE ID
	DECLARE CONTINUE HANDLER FOR 1062
	BEGIN
		# SET UPSERT STATUS & UPSERT MESSAGE WHEN ERROR DUPLICATE ID
		SET @UPSERT_STATUS = 0;
		SET @UPSERT_MESSAGE = CONCAT('duplicate ID found');
		INSERT INTO DuplicateIDLog
				VALUES (UUID(),'Client',@ClientID, @TRY, NOW());
	END;

	# DECLARE EXIT HANDLER FOR SQLEXCEPTION
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
		BEGIN
			# SET STATUS & ERROR MESSAGE
			SET @UPSERT_STATUS = 0;
			GET DIAGNOSTICS CONDITION 1
        @UPSERT_MESSAGE = MESSAGE_TEXT;
			CALL TransactionRollBack();
    END;

	# START UPSERT TRANSACTION
	CALL TransactionStart();
			# REDECLARE PARAMETER
			SET @ClientID = _ClientID;
			SET @FirstName = _FirstName;
			SET @MiddleName = _MiddleName;
			SET @LastName = _LastName;
			SET @`Phone` = `_Phone`;
			SET @Email = _Email;
			SET @`Password` = `_Password`;
			SET @Address = _Address;
			SET @AdminNote = _AdminNote;
			SET @UUID = _UUID;
			SET @ShopID = ShopID;
			SET @NEWDATA = NULL;
			SET @UserID = `_UserID`;
			SET @TABLEID = "E2Q6";
			SET @PKEY = "ClientID";
			SET @RESULTORIGINDATA = "";

			# MAKE DECISION THIS IS INSERT OR UPDATE ACTION
			IF(@ClientID IS NULL OR @ClientID = '') THEN
				# START INSERT SECTION
				SET @ACTIONTYPE = 'insert';
				SET @TRY = 0;
						SET @MAX_TRY = GetMaxTry();
						# TRY LOOP UNTIL SUCCESSFULLY INSERTED NEW ROW
						# TO PREVENT DUPLICATE ID CODE
						INSERT_LOOP : WHILE @TRY < @MAX_TRY DO
							SET @ClientID = (SELECT RandomCode());
							SET @UPSERT_STATUS = 1;
							# TRY INSERT NEW ROW
							INSERT IGNORE INTO `Client`
									(ClientID,
										FirstName,
										MiddleName,
										LastName,
										Phone,
										Email,
										`Password`,
										Address,
										UUID,
										AdminNote,
										Created_at)
							VALUES
									(@ClientID,
										@FirstName,
										@MiddleName,
										@LastName,
										@Phone,
										@Email,
										@`Password`,
										@Address,
										@UUID,
										@AdminNote,
										NOW());

							SET @TRY = @TRY + 1;
							# CHECK IF INSERT SUCCESS
							IF @UPSERT_STATUS = 1 THEN
								# SET RESULT MESSAGE WHEN INSERT SUCCESS
								SET @UPSERT_MESSAGE = 'insert success';
								# BREAK TRY LOOP
								LEAVE INSERT_LOOP;
							END IF;
						END WHILE;
						# END INSERT SECTION
				ELSE
					# START UPDATE SECTION
					SET @ACTIONTYPE = 'update';
					# SET ORIGIN DATA BEFORE UPDATE
					CALL GetOriginData(@TABLEID,@PKEY,@ClientID);
					# EXECUTE QUERY
					PREPARE STMT FROM @QRYORIGINDATA;
					EXECUTE STMT;
					DEALLOCATE PREPARE STMT;
					#SET NEWDATA FOR USERACTIVITY LOG
					SET @NEWDATA = CONCAT_WS("#~",
															COALESCE(@ClientID,"NULL"),
															COALESCE(@FirstName,"NULL"),
															COALESCE(@MiddleName,"NULL"),
															COALESCE(@LastName,"NULL"),
															COALESCE(@Phone,"NULL"),
															COALESCE(@Email,"NULL"),
															COALESCE(@`Password`,"NULL"),
															COALESCE(@Address,"NULL"),
															COALESCE(@Lang,"NULL"),
															COALESCE(@UUID,"NULL"),
															COALESCE(@AdminNote,"NULL"),
															COALESCE(@LastLogin,"NULL"),
															NULL,
															NOW(),
															NULL);
					# EXECUTE UPDATE
					UPDATE `Client` C
						SET
							C.FirstName = COALESCE(@FirstName,C.FirstName),
							C.MiddleName = COALESCE(@MiddleName,C.MiddleName),
							C.LastName = COALESCE(@LastName,C.LastName),
							C.Phone = COALESCE(@Phone,C.Phone),
							C.Email = COALESCE(@Email,C.Email),
							C.`Password` = COALESCE(@`Password`,C.`Password`),
							C.Address = COALESCE(@Address,C.Address),
							C.AdminNote = COALESCE(@AdminNote,C.AdminNote),
							C.UUID = COALESCE(@UUID, C.UUID),
							C.Updated_at = NOW()
						WHERE C.ClientID = @ClientID;
					# SET RESULT STATUS & MESSAGE WHEN UPDATE SUCCESS
					SET @UPSERT_STATUS = 1;
					SET @UPSERT_MESSAGE = 'update success';
					# END UPDATE SECTION
			END IF;
			# INSERT USER ACTIVITY
			CALL Default_UserActivity_Upsert(@UserID,@ACTIONTYPE,@TABLEID,@ClientID,@RESULTORIGINDATA,@NEWDATA,@PKEY,@UPSERT_STATUS);
			# COMMIT TRANSACTION IF NO ERROR
			CALL TransactionCommit();
	# SELECT OUTPUT ROW
	SELECT @UPSERT_STATUS AS `STATUS`, @UPSERT_MESSAGE AS `MESSAGE`, @ClientID AS `ROW_ID`;
END