MOBILE_API/DB/SPs/mobile_proc_OTPRequest.sql

ALTER PROC mobile_proc_OTPRequest
(
	@FLAG VARCHAR(20)
	,@OTP_CODE VARCHAR(6)
	,@USER_ID VARCHAR(150)
	,@REQUEST_FOR VARCHAR(10) = NULL
	,@RECEIVER_ID BIGINT = NULL
)
AS;
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN
	DECLARE @ROW_ID BIGINT = NULL, @IS_EXPIRED BIT = 0, @IS_USED BIT = 0, @MOBILE VARCHAR(30), @EXPIRE_MINUTES TINYINT = 10, @CURRENT_OTP VARCHAR(10)
			, @REQUEST_MINUTES INT
	IF @FLAG = 'REQUEST'
	BEGIN
		IF @USER_ID IS NULL OR (NOT EXISTS(SELECT * FROM CUSTOMERMASTER(NOLOCK) WHERE USERNAME = @USER_ID)
									AND NOT EXISTS(SELECT * FROM CUSTOMERMASTERTEMP(NOLOCK) WHERE USERNAME = @USER_ID))
		BEGIN
			SELECT 1 Code, 'Invalid user!' Msg, NULL Id
			RETURN
		END


		IF @REQUEST_FOR = 'REGISTER'
		BEGIN
			IF EXISTS(SELECT * FROM TBL_MOBILE_OTP_REQUEST(NOLOCK) WHERE [USER_ID] = @USER_ID AND IS_SUCCESS = 1 AND REQUEST_FOR = 'REGISTER')
			BEGIN
				SELECT 2 Code, 'User already verified!' Msg, NULL Id
				RETURN
			END

			SELECT @CURRENT_OTP = OTP_CODE,  @REQUEST_MINUTES = DATEDIFF(MINUTE, REQUESTED_DATE, GETDATE())
			FROM TBL_MOBILE_OTP_REQUEST
			WHERE [USER_ID] = @USER_ID 
			AND REQUEST_FOR = 'REGISTER'
			AND IS_EXPIRED = 0
		END
		ELSE
		BEGIN
			IF @RECEIVER_ID IS NULL
			BEGIN
				SELECT 3 Code, 'Receiver ID can not be null for Receiver OTP Request!' Msg, NULL Id
				RETURN
			END

			IF EXISTS(SELECT * FROM TBL_MOBILE_OTP_REQUEST(NOLOCK) WHERE [USER_ID] = @USER_ID AND IS_SUCCESS = 1 AND REQUEST_FOR = 'RECEIVER'
							AND RECEIVER_ID = @RECEIVER_ID)
			BEGIN
				SELECT 4 Code, 'Receiver already verified!' Msg, NULL Id
				RETURN
			END

			SELECT @CURRENT_OTP = OTP_CODE,  @REQUEST_MINUTES = DATEDIFF(MINUTE, REQUESTED_DATE, GETDATE())
			FROM TBL_MOBILE_OTP_REQUEST
			WHERE [USER_ID] = @USER_ID 
			AND RECEIVER_ID = @RECEIVER_ID 
			AND REQUEST_FOR = 'RECEIVER'
			AND IS_EXPIRED = 0
		END

		IF @CURRENT_OTP IS NOT NULL AND @REQUEST_MINUTES <= @EXPIRE_MINUTES
		BEGIN
			SELECT 103 Code, 'Success, Old OTP used' Msg, @OTP_CODE Id
			RETURN
		END
		

		IF @CURRENT_OTP IS NOT NULL AND @REQUEST_MINUTES > @EXPIRE_MINUTES
		BEGIN
			UPDATE TBL_MOBILE_OTP_REQUEST SET IS_EXPIRED = 1
			WHERE [USER_ID] = @USER_ID 
			AND REQUEST_FOR = @REQUEST_FOR 
			AND ISNULL(RECEIVER_ID, 1) = CASE WHEN @REQUEST_FOR = 'RECEIVER' THEN @RECEIVER_ID ELSE 1 END
			AND OTP_CODE = @CURRENT_OTP
		END


		INSERT INTO TBL_MOBILE_OTP_REQUEST([USER_ID], OTP_CODE, REQUESTED_DATE, IS_EXPIRED, IS_SUCCESS, REQUEST_FOR, RECEIVER_ID)
		SELECT @USER_ID, @OTP_CODE, GETDATE(), 0, 0, @REQUEST_FOR, @RECEIVER_ID

		SELECT 0 Code, 'Success' Msg, @EXPIRE_MINUTES Id
	END
	ELSE IF @FLAG = 'SUBMIT'
	BEGIN
		IF @USER_ID IS NULL OR (NOT EXISTS(SELECT * FROM CUSTOMERMASTER(NOLOCK) WHERE USERNAME = @USER_ID)
									AND NOT EXISTS(SELECT * FROM CUSTOMERMASTERTEMP(NOLOCK) WHERE USERNAME = @USER_ID))
		BEGIN
			SELECT 1 Code, 'Invalid user!' Msg, NULL Id
			RETURN
		END

		IF @REQUEST_FOR = 'RECEIVER'
		BEGIN
			SELECT @ROW_ID = ROW_ID,
					@IS_EXPIRED = IS_EXPIRED, 
					@IS_USED = IS_SUCCESS
			FROM TBL_MOBILE_OTP_REQUEST(NOLOCK) 
			WHERE [USER_ID] = @USER_ID
			AND OTP_CODE = @OTP_CODE
			AND REQUEST_FOR = @REQUEST_FOR
			AND RECEIVER_ID = @RECEIVER_ID
		END
		ELSE
		BEGIN
			SELECT @ROW_ID = ROW_ID,
					@IS_EXPIRED = IS_EXPIRED, 
					@IS_USED = IS_SUCCESS
			FROM TBL_MOBILE_OTP_REQUEST(NOLOCK) 
			WHERE [USER_ID] = @USER_ID
			AND OTP_CODE = @OTP_CODE
			AND REQUEST_FOR = @REQUEST_FOR
		END


		IF @ROW_ID IS NULL
		BEGIN
			SELECT 2 Code, 'Invalid OTP!' Msg, NULL Id
			RETURN
		END
		IF @IS_EXPIRED = 1
		BEGIN
			SELECT 3 Code, 'OTP is already expired!' Msg, NULL Id
			RETURN
		END
		IF @IS_USED = 1
		BEGIN
			SELECT 4 Code, 'OTP is already used!' Msg, NULL Id
			RETURN
		END
		IF EXISTS (SELECT TOP 1 1 FROM TBL_MOBILE_OTP_REQUEST(NOLOCK) WHERE ROW_ID = @ROW_ID AND DATEDIFF(MINUTE, REQUESTED_DATE, GETDATE()) > @EXPIRE_MINUTES)
		BEGIN
			UPDATE TBL_MOBILE_OTP_REQUEST SET IS_EXPIRED = 1 WHERE ROW_ID = @ROW_ID
			SELECT 3  Code, 'OTP is already expired!' Msg, NULL Id
			RETURN
		END

		UPDATE TBL_MOBILE_OTP_REQUEST SET VERIFIED_DATE = GETDATE(), IS_SUCCESS = 1
		WHERE ROW_ID = @ROW_ID

		IF @REQUEST_FOR = 'RECEIVER'
			UPDATE RECEIVERINFORMATION SET isOTPVerified = 1 WHERE RECEIVERID = @RECEIVER_ID
		ELSE
		BEGIN
			IF EXISTS (SELECT * FROM CUSTOMERMASTER(NOLOCK) WHERE username = @USER_ID)
				UPDATE CUSTOMERMASTER SET isEmailVerified = 1 WHERE username = @USER_ID
			ELSE
				UPDATE CUSTOMERMASTERTEMP SET isEmailVerified = 1 WHERE username = @USER_ID
		END

		SELECT 0 Code, 'Success' Msg, @ROW_ID Id, @EXPIRE_MINUTES Extra
	END
END