IF OBJECT_ID(N'KAAS_CP_AcitvateNewUserAccount',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_AcitvateNewUserAccount]
GO

CREATE PROCEDURE 
	[dbo].[KAAS_CP_AcitvateNewUserAccount]
    (@EmailId       VARCHAR(200),
	 @UserName      VARCHAR(256),
	 @PhoneNumber   VARCHAR(50),
	 @PasswordHash  NVARCHAR(512))

AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_AcitvateNewUserAccount]									   *
* Description: To activate new user account with password hash										   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-08    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

-- Update user details to activate account
UPDATE [dbo].[ClientLogin]
	SET
		[PasswordHash]						 = @PasswordHash,
		[Name]								 = @UserName,
		[PhoneNumber]						 = @PhoneNumber,
		[IsEmailConfirmed]					 = 1,
		[IsUserVerifiedEmail]				 = 1,
		[EmailActivationToken]				 = NULL,
		[EmailActivationTokenExpirationTime] = NULL
	WHERE [Email] = @EmailId


END

GO
IF OBJECT_ID(N'KAAS_CP_GetCurrentTenantFirmName',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_GetCurrentTenantFirmName]
GO
CREATE PROCEDURE [dbo].[KAAS_CP_GetCurrentTenantFirmName]
AS
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_GetCurrentTenantFirmName]	      							   *
* Description: To get tenant firm name for client portal											   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-04    Aakif        Created																   *
 *******************************************************************************************************/

BEGIN

	--SET DEFAULT HEADER IF FIRM NAME IS NOT AVAILABLE
	SELECT
		[NAME] AS [FirmName]
	FROM
		[dbo].[control]
END

GO






IF OBJECT_ID(N'KAAS_CP_GetMatterDetailsForClient',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_GetMatterDetailsForClient]
GO

CREATE PROCEDURE
	[dbo].[KAAS_CP_GetMatterDetailsForClient]
	(@LoginId     BIGINT,
	 @CaseCode    VARCHAR(15) = NULL)

AS
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_GetMatterDetailsForClient]									   *
* Description: To get matter details for client portal												   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-09    Aakif        Created																   *
* 2021-06-11    Aakif        Included option to serach with case code. User TimeZone conversion		   *
*							 and label names for User1, User2, User3 and Your Ref					   *
 *******************************************************************************************************/

BEGIN
	SELECT *,
	
		CONVERT(NVARCHAR(15), dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone(
		dbo.KAAS_CP_GetLastOrNextActionDate(MatterCode, 1, 0), @LoginId), 23)	AS [LastActionDate],

		CONVERT(NVARCHAR(15), dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone(
		dbo.KAAS_CP_GetLastOrNextActionDate(MatterCode, 0, 1), @LoginId), 23)	AS [LastMilestoneDate],

		CONVERT(NVARCHAR(15), dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone(
		dbo.KAAS_CP_GetLastOrNextActionDate(MatterCode, 0, 0), @LoginId), 23)	AS [NextActionDate],

		dbo.KAAS_CP_GetLastOrNextActionText(MatterCode, 1, 0)					AS LastActionDescription,
		dbo.KAAS_CP_GetLastOrNextActionText(MatterCode, 0, 1)					AS LastMilestoneDescription,
		dbo.KAAS_CP_GetLastOrNextActionText(MatterCode, 0, 0)					AS NextActionDescription
	FROM
	(
		SELECT 
			CON.[Name]					AS ClientName,
			CON.[Address]				AS ClientAddress,
			MAT.[Code]					AS MatterCode,
			MAT.[Description]			AS MatterDescription,
			SM.[HandlerName]			AS CaseHandler,
			RTRIM(CTL.[USERPROMPT1])	AS User1Label,
			MAT.[User1]					AS User1,
			RTRIM(CTL.[USERPROMPT2])	AS User2Label,
			MAT.[User2]					AS User2,
			RTRIM(CTL.[USERPROMPT3])	AS User3Label,
			MAT.[User3]					AS User3,
			RTRIM(CTL.[YourRef])		AS YourRefLabel,
			MAT.[YourRef]				AS YourRef,
			MAT.[Started]				AS MatterStartDate,
			MAT.[CloseDate]				AS MatterCloseDate,	
			MAT.[Status]				AS MatterStatusCode,
			RTRIM(SC.[DESCRIPTION])		AS MatterStatus,
			MAT.[OriginalDebt]			AS MatterOriginalDebt
		FROM
			 [Contacts] CON
		JOIN [matters] MAT
			ON CON.Code = MAT.ClientCode
		JOIN [SearchMatters] SM
			ON SM.Code = MAT.Code
		JOIN [StatusCodes] SC
			ON SC.CODE = MAT.[Status]
		CROSS JOIN [control] CTL
		WHERE
			MAT.Code IN (
			SELECT MatterCode FROM dbo.KAAS_CP_TFGetMatterCodeForClient(@LoginId))
			AND (ISNULL(@CaseCode, '') = '' OR MAT.Code = @CaseCode)
	 ) FIN

END

GO






IF OBJECT_ID(N'KAAS_CP_GetSettings',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_GetSettings]
GO
CREATE PROCEDURE [dbo].[KAAS_CP_GetSettings]
AS
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_GetSettings]	      										   *
* Description: To get settings for client portal													   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-27    Aakif        Created																   *
* 2021-05-31    Aakif        Included column for two factor authentication							   *
 *******************************************************************************************************/

BEGIN
	SELECT
		[TenantLogo]						   AS [TenantLogo], 
		[TenantLogoType]					   AS [TenantLogoType],
		[UPEKey]							   AS [UPEKey],
		ISNULL([IsTwoFactorEnabled], 0)		   AS [IsTwoFactorEnabled],
		ISNULL([IsLockOut_Attempt], 0)		   AS [IsLockOutAttempt],
		ISNULL([LockoutDurationInMins], 10)    AS [LockoutLimitInMin],
		ISNULL([PasswordResetNotification], 0) AS [PasswordResetNotification]
	FROM
		[dbo].[ClientPortalSetting]
END

GO






IF OBJECT_ID(N'KAAS_CP_GetUserLoginDetails',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_GetUserLoginDetails]
GO
CREATE PROCEDURE 
	 [dbo].[KAAS_CP_GetUserLoginDetails]
	(@EmailId VARCHAR(200))
AS 
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_GetUserLoginDetails]      									   *
* Description: To get login details for specified email												   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-27    Aakif        Created                                                                   *
* 2021-05-31    Aakif        Included column for two factor authentication							   *
 *******************************************************************************************************/

BEGIN
	SELECT
		  [ClientLoginId]							AS	 [ClientLoginId],
		  [Email]									AS	 [Email],
		  [PhoneNumber]								AS	 [PhoneNumber],
		  [Name]									AS	 [Name],
		  [ClientDefaulTimeZone]					AS	 [ClientDefaulTimeZone],
		  [AccessCode]								AS	 [AccessCode],
		  [AccessCodeStatus]						AS	 [AccessCodeStatus],
		  [PasswordHash]							AS	 [PasswordHash],
		  ISNULL([NoOfAttempt],0)					AS   [NoOfAttempt],
		  [LastAccessedDateTime]					AS	 [LastAccessedDateTime],
		  [LockedOutEndDate]						AS	 [LockedOutEndDate],
		  [SecurityStamp]							AS	 [SecurityStamp],
		  ISNULL([IsTwoFactorEnabled],0)			AS   [IsTwoFactorEnabled],
		  [IsActiveLogin]							AS   [IsActiveLogin],
		  [IsFirstlogin]							AS   [IsFirstlogin],
		  [IsDeleted]								AS	 [IsDeleted],
		  [IsAdmin]									AS	 [IsAdmin],
		  [IsEmailConfirmed]						AS	 [IsEmailConfirmed],
		  [IsPhoneNumberConfirmed]					AS	 [IsPhoneNumberConfirmed],
		  [EmailActivationToken]					AS	 [EmailActivationToken],
		  [ConcurrencyStamp]						AS   [ConcurrencyStamp],
		  [PasswordResetOTP]						AS   [PasswordResetOTP],
		  [PasswordResetOTPCreationTime]			AS   [PasswordResetOTPCreationTime],  
		  [PasswordResetOTPExpirationTime]			AS   [PasswordResetOTPExpirationTime],
		  [OTPAccessToken]							AS	 [OTPAccessToken],
		  [OTPAccessTokenCreationTime]				AS	 [OTPAccessTokenCreationTime],
		  [OTPAccessTokenExpirationTime]			AS   [OTPAccessTokenExpirationTime],
		  [PasswordResetOTPAttemptCount]			AS	 [PasswordResetOTPAttemptCount],
		  [PasswordResetLockOutEndDate]				AS	 [PasswordResetLockOutEndDate],
		  [IsPasswordResetOTPVerified]				AS	 [IsPasswordResetOTPVerified],
		  [IsUserVerifiedEmail]						AS	 [IsUserVerifiedEmail],
		  [VerifiedByAdminUserId]					AS	 [VerifiedByAdminUserId],
		  [EmailActivationTokenCreatedTime]			AS   [EmailActivationTokenCreatedTime],
		  [EmailActivationTokenExpirationTime]		AS   [EmailActivationTokenExpirationTime],
		  [IsAdminVerifiedUserLogin]				AS   [IsAdminVerifiedUserLogin],
		  [SecretQuestion]							AS   [SecretQuestion],
		  [Answer]									AS	 [Answer]
	FROM
		  [dbo].[ClientLogin]
	WHERE 
		  [Email] = @EmailId
END

GO
IF OBJECT_ID(N'KAAS_CP_RegisterNewUser',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_RegisterNewUser]
GO

CREATE PROCEDURE 
	[dbo].[KAAS_CP_RegisterNewUser]
    (@EmailId               VARCHAR(200),
	 @IsTwoFactorEnabled    BIT = 0,
	 @EmailActivationToken  NVARCHAR(252) = NULL,
	 @TokenCreationTime     DATETIME = NULL,
	 @TokenExpirationTime   DATETIME = NULL)

AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_RegisterNewUser] 											   *
* Description: To register new user in Client Portal												   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-07    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

-- Update OTP with its expiry time

INSERT INTO
	[dbo].[ClientLogin]
	(
	 [Email],
	 [IsActiveLogin],
	 [IsTwoFactorEnabled],
	 [EmailActivationToken],
	 [EmailActivationTokenCreatedTime],
	 [EmailActivationTokenExpirationTime]
	)
	VALUES
	(
	 @EmailId,
	 0,
	 @IsTwoFactorEnabled,
	 @EmailActivationToken,
	 @TokenCreationTime,
	 @TokenExpirationTime
	)

END

GO
IF OBJECT_ID(N'KAAS_CP_UpdateAccessCodeForUser',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_UpdateAccessCodeForUser]
GO
CREATE PROCEDURE 
	 [dbo].[KAAS_CP_UpdateAccessCodeForUser]
	(@EmailId            VARCHAR(200),
	 @AccessCode		  VARCHAR(50)  = NULL,
	 @AccessCodeStatus   VARCHAR(200) = NULL,
	 @OffSetForOtpExpiry TINYINT      = 10)
AS
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_UpdateAccessCodeForUser] 									   *
* Description: To store Access Code and OTP Expirty time for user									   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-31    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN
	DECLARE @CurrentDateTime DATETIME = GETUTCDATE();

-- Update OTP with its expiry time
	UPDATE [dbo].[ClientLogin]
		SET 
			[AccessCode]				   = @AccessCode,
		    [AccessCodeStatus]			   = @AccessCodeStatus,
		    [OTPAccessTokenCreationTime]   = @CurrentDateTime,
		    [OTPAccessTokenExpirationTime] = DATEADD(MINUTE, @OffSetForOtpExpiry, @CurrentDateTime)
		WHERE 
			[Email] = @EmailId
END

GO
IF OBJECT_ID(N'KAAS_CP_UpdateIncorrectLoginAttempt',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_UpdateIncorrectLoginAttempt]
GO
CREATE PROCEDURE 
	 [dbo].[KAAS_CP_UpdateIncorrectLoginAttempt]
	(@EmailId VARCHAR(200),
	 @Result  INT = 0 OUTPUT)
AS
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_UpdateIncorrectLoginAttempt]								   *
* Description: To update lockout date and number of attempts for user								   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-01    Aakif        Created																   *
 *******************************************************************************************************/

BEGIN
	DECLARE @CurrentLoginAttempt TINYINT;
	DECLARE @MaxLockoutAttemp INT, @LockoutTimeoutLimit INT;

--GET current incorrect login attempt count
	SELECT 
	  @CurrentLoginAttempt   = ISNULL(NoOfAttempt,0) 
	FROM 
	  [dbo].[ClientLogin]

-- GET Max attempt and lockout duration from setting
	SELECT 
		@MaxLockoutAttemp    = ISNULL(IsLockOut_Attempt, 3),
		@LockoutTimeoutLimit = ISNULL(LockoutDurationInMins, 10)
	FROM 
		[dbo].[ClientPortalSetting]

-- Step 1: Update current attempt at login
	UPDATE [dbo].[ClientLogin]
	  SET 
		[NoOfAttempt] = (@CurrentLoginAttempt + 1)
	  WHERE 
		[Email] = @EmailId

-- Step 2: Check if current attempt exceeds limit and update lockedout date
IF(@MaxLockoutAttemp <= (@CurrentLoginAttempt + 1 ))
	BEGIN
	    UPDATE [dbo].[ClientLogin]
		  SET 
			[LockedOutEndDate] = DATEADD(MINUTE, @LockoutTimeoutLimit, GETUTCDATE())
		  WHERE 
			[Email] = @EmailId
			
		SET @Result = @LockoutTimeoutLimit

	END
END

GO

IF OBJECT_ID(N'KAAS_CP_UpdateLastLoginData',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_UpdateLastLoginData]
GO
CREATE PROCEDURE 
	  [dbo].[KAAS_CP_UpdateLastLoginData]
	 (@EmailId     VARCHAR(200),
	  @LoginId     BIGINT,
	  @DeviceInfo  NVARCHAR(20)   = NULL,
	  @BrowserInfo NVARCHAR(20)   = NULL,
	  @IPAddress   NVARCHAR(30)   = NULL)
AS
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_UpdateLastAccessDate] 										   *
* Description: To update last accessed datetime for user											   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-27    Aakif        Created                                                                   *
* 2021-06-31    Aakif        Reset LockedoutEndDate after successful login							   *
 *******************************************************************************************************/

BEGIN
-- Step 1: Update last login access date
	UPDATE [dbo].[ClientLogin]
		SET [LastAccessedDateTime] = GETUTCDATE(),
		    [NoOfAttempt]		   = 0,
		    [LockedOutEndDate]     = NULL,
		    [IsFirstlogin]         = 0,
		    [IsActiveLogin]        = 1
		WHERE 
			[Email] = @EmailId

-- Step 2: Add user login history
	INSERT INTO 
	    [dbo].[ClientPortalLogonInfo]
		([ClientLoginId]
		,[LastAccessedDateTime]
		,[DeviceInfo]
		,[Browser]
		,[IPAddress])
	VALUES
		(@LoginId
		,GETUTCDATE()
		,@DeviceInfo
		,@BrowserInfo
		,@IPAddress)
END

GO






IF OBJECT_ID(N'KAAS_CP_UpdatePasswordHash',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_UpdatePasswordHash]
GO

CREATE PROCEDURE 
	[dbo].[KAAS_CP_UpdatePasswordHash]
    (@LoginId         BIGINT,
	 @NewPasswordHash NVARCHAR(512))

AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_UpdatePasswordHash] 										   *
* Description: To update password hash after the reset is successful								   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-02    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

-- Update password hash after password is reset
UPDATE [dbo].[ClientLogin]
	SET
		[PasswordHash]				  = @NewPasswordHash,
		[PasswordResetLockOutEndDate] = NULL
	WHERE [ClientLoginId] = @LoginId


END

GO
IF OBJECT_ID(N'KAAS_CP_UpdateResetPasswordCode',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_UpdateResetPasswordCode]
GO

CREATE PROCEDURE 
	[dbo].[KAAS_CP_UpdateResetPasswordCode]
    (@LoginId            BIGINT,
	 @ResetPasswordCode  VARCHAR(10),
	 @CodeCreationTime   DATETIME = NULL,
	 @CodeExpirationTime DATETIME = NULL)

AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_UpdateResetPasswordCode] 									   *
* Description: To store Reset password OTP Expirty time for user									   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-02    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

-- Update OTP with its expiry time
UPDATE [dbo].[ClientLogin]
	SET
		[IsPasswordResetOTPVerified]	 = 0,
		[PasswordResetOtp]               = @ResetPasswordCode,
		[PasswordResetOtpcreationTime]   = @CodeCreationTime,
		[PasswordResetOtpexpirationTime] = @CodeExpirationTime
	WHERE [ClientLoginId] = @LoginId

END

GO
IF OBJECT_ID(N'KAAS_CP_ValidateResetPassword',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_ValidateResetPassword]
GO

CREATE PROCEDURE 
	[dbo].[KAAS_CP_ValidateResetPassword]
    (@LoginId		  BIGINT,
	 @IsOtpValid	  BIT,
	 @IsPasswordReset BIT = 0)

AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_ValidateResetPassword] 									   *
* Description: To validate Reset password OTP and lockout user if invalid credentails provided		   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-03    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

DECLARE @CurrentAttempt TINYINT, @LockoutLimit TINYINT, @LockoutDuration INT;

--HANDLE FOR VALID RESET PWD OTP
IF(@IsOtpValid = 1)
BEGIN
	UPDATE [dbo].[ClientLogin]
		SET
			[IsPasswordResetOTPVerified] = 1
		WHERE [ClientLoginId] = @LoginId

END

ELSE
BEGIN
	
	--GET CURRENT ATTEMPT COUNT TO DECIDE WHETHER TO LOCKOUT USER
	SELECT 
		@CurrentAttempt = ISNULL(PasswordResetOTPAttemptCount, 0)
	FROM 
		[dbo].[ClientLogin]

	-- SET LOCKOUT TENANT SETTINGS
	SELECT
		@LockoutLimit	 = ISNULL(IsLockOut_Attempt, 3),
		@LockoutDuration = ISNULL(LockoutDurationInMins, 10)
	FROM
		[dbo].[ClientPortalSetting]

	-- LOCKOUT USER FOR MAX LIMIT REACHED
	IF(@CurrentAttempt >= @LockoutLimit)
	BEGIN
		UPDATE [dbo].[ClientLogin]
			SET
				[PasswordResetLockOutEndDate] = DATEADD(MINUTE, @LockoutDuration, GETUTCDATE())
			WHERE [ClientLoginId] = @LoginId
	END

	--INCREMENT CURRENT ATTEMPT COUNT
	UPDATE [dbo].[ClientLogin]
		SET
			[PasswordResetOTPAttemptCount] = (@CurrentAttempt + 1)
		WHERE [ClientLoginId] = @LoginId
END


--RESET ALL DATA AFTER PASSWORD IS RESET
IF(@IsPasswordReset = 1)
BEGIN
	UPDATE [dbo].[ClientLogin]
		SET
			[PasswordResetOTPAttemptCount]	 = 0,
			[IsPasswordResetOTPVerified]	 = 0,
			[PasswordResetLockOutEndDate]	 = NULL,
			[PasswordResetOTP]				 = NULL,
			[PasswordResetOTPCreationTime]	 = NULL,
			[PasswordResetOTPExpirationTime] = NULL
		WHERE [ClientLoginId] = @LoginId

END

END

GO