/****** Script Date: 5/24/2021 7:16:49 PM ******/

-- To hold user login details and its default time zone information.
IF OBJECT_ID(N'[dbo].[ClientLogin]',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[ClientLogin](
		[ClientLoginId]						[BIGINT] IDENTITY(1,1) NOT NULL,
		[Email]								[varchar](200)         NULL,
		[PhoneNumber]						[varchar](50)          NULL,
		[Name]								[VARCHAR](256)         NULL,
		[ClientDefaulTimeZone]				[VARCHAR] (50)         NULL,
		[AccessCode]						[varchar](100)         NULL,
		[AccessCodeStatus]					[varchar](250)		   NULL,
		[PasswordHash]						[nvarchar](512)		   NULL,
		[NoOfAttempt]						[tinyint]			   NULL,
		[LastAccessedDateTime]				[datetime]             NULL,
		[LockedOutEndDate]					[datetimeoffset](7)    NULL,
		[SecurityStamp]						[nvarchar](512)		   NULL,
		[IsActiveLogin]						[bit]				   NOT NULL,
		[IsFirstlogin]						[bit]				   NOT NULL,
		[IsAdmin]							[bit]				   NOT NULL,
		[IsDeleted]							[bit]				   NOT NULL,
		[IsEmailConfirmed]					[bit]				   NOT NULL,
		[IsPhoneNumberConfirmed]			[bit]                  NOT NULL,
		[EmailActivationToken]				[nvarchar](512)        NULL,
		[ConcurrencyStamp]					[nvarchar](512)        NULL,
		[PasswordResetOTP]					[varchar](10)          NULL,
		[PasswordResetOTPCreationTime]		[datetime]             NULL,
		[PasswordResetOTPExpirationTime]	[datetime]             NULL,
		[OTPAccessToken]					[varchar](50)          NULL,
		[OTPAccessTokenCreationTime]		[datetime]             NULL,
		[OTPAccessTokenExpirationTime]		[datetime]             NULL,
		[PasswordResetOTPAttemptCount]		[int]				   NULL,
		[PasswordResetLockOutEndDate]		[datetime]			   NULL,
		[IsPasswordResetOTPVerified]		[bit]				   NULL,
		[IsUserVerifiedEmail]				[bit]				   NULL,
		[VerifiedByAdminUserId]				[bigint]			   NULL,
		[EmailActivationTokenCreatedTime]	[datetime]			   NULL,
		[EmailActivationTokenExpirationTime] [datetime]			   NULL,
		[IsAdminVerifiedUserLogin]			[bit]				   NULL,
		[SecretQuestion]					[nvarchar](2000)	   NULL,
		[Answer]							[nvarchar](500)		   NULL
		CONSTRAINT [PK_ClientLogin] PRIMARY KEY ([ClientLoginId])
)
END
GO

IF OBJECT_ID(N'DF_ClientLogin_AccessCode',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLogin] 
	ADD CONSTRAINT 
		[DF_ClientLogin_AccessCode]  
	DEFAULT 
		((0)) 
	FOR 
		[AccessCode]
END
GO

IF OBJECT_ID(N'DF_ClientLogin_AccessCodeStatus',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLogin] 
	ADD CONSTRAINT 
		[DF_ClientLogin_AccessCodeStatus]  
	DEFAULT 
		((0)) 
	FOR 
		[AccessCodeStatus]
END
GO

IF OBJECT_ID(N'DF_ClientLogin_SecurityStamp',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLogin] 
	ADD CONSTRAINT 
		[DF_ClientLogin_SecurityStamp]  
	DEFAULT 
		(newid()) 
	FOR 
		[SecurityStamp]
END
GO

IF OBJECT_ID(N'DF_ClientLogin_IsActiveLogin',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLogin] 
	ADD CONSTRAINT 
		[DF_ClientLogin_IsActiveLogin]  
	DEFAULT 
		((1)) 
	FOR 
		[IsActiveLogin]
END
GO

IF OBJECT_ID(N'DF_ClientLogin_IsFirstlogin',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLogin] 
	ADD CONSTRAINT 
		[DF_ClientLogin_IsFirstlogin]  
	DEFAULT 
		((1)) 
	FOR 
		[IsFirstlogin]
END
GO

IF OBJECT_ID(N'DF_ClientLogin_IsEmailConfirmed',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLogin] 
	ADD CONSTRAINT 
		[DF_ClientLogin_IsEmailConfirmed]  
	DEFAULT 
		((0)) 
	FOR 
		[IsEmailConfirmed]
END
GO

IF OBJECT_ID(N'DF_ClientLogin_IsPhoneNumberConfirmed',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLogin] 
	ADD CONSTRAINT 
		[DF_ClientLogin_IsPhoneNumberConfirmed]  
	DEFAULT 
		((0)) 
	FOR 
		[IsPhoneNumberConfirmed]
END
GO

IF OBJECT_ID(N'DF_ClientLogin_PasswordResetOTPAttemptCount',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLogin] 
	ADD CONSTRAINT 
		[DF_ClientLogin_PasswordResetOTPAttemptCount]  
	DEFAULT 
		((0)) 
	FOR 
		[PasswordResetOTPAttemptCount]
END
GO

IF OBJECT_ID(N'DF_ClientLogin_IsAdmin',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLogin] 
	ADD CONSTRAINT 
		[DF_ClientLogin_IsAdmin]  
	DEFAULT 
		((0)) 
	FOR 
		[IsAdmin]
END
GO

IF OBJECT_ID(N'DF_ClientLogin_IsDeleted',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLogin] 
	ADD CONSTRAINT 
		[DF_ClientLogin_IsDeleted]  
	DEFAULT 
		((0)) 
	FOR 
		[IsDeleted]
END
GO

--Table to Map existing client in KH with new Client Portal. 
IF OBJECT_ID(N'[dbo].[ClientLoginAccess]',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[ClientLoginAccess](		
		[ClientLoginAcessId]   [BIGINT] IDENTITY(1,1)   NOT NULL,
		[ClientLoginId]		   [BIGINT]					NOT NULL,
		[ContactCode]		   [VARCHAR](10)			NOT NULL,
		[MatterCode]		   [VARCHAR](11)			NULL,
		[PublishedHandlerCode] [VARCHAR](10)			NULL,
		[PublishedDateTime]	   [DATETIME]				NULL,
		[HasAccessToAllMatter] [CHAR](1)				NULL,
		CONSTRAINT [PK_ClientLoginAccess] PRIMARY KEY (ClientLoginAcessId)
	)
END
GO

IF OBJECT_ID(N'FK_ClientLoginAccess_ClientLoginId_ClientLogin_ClientLoginId',N'F') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLoginAccess] 
	WITH NOCHECK ADD CONSTRAINT 
		[FK_ClientLoginAccess_ClientLoginId_ClientLogin_ClientLoginId]  
	FOREIGN KEY 
		([ClientLoginId])
	REFERENCES 
		[dbo].[ClientLogin] ([ClientLoginId])
END
GO

IF OBJECT_ID(N'FK_ClientLoginAccess_ClientLoginId_ClientLogin_ClientLoginId',N'F') IS NOT NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLoginAccess] 
	CHECK CONSTRAINT
		[FK_ClientLoginAccess_ClientLoginId_ClientLogin_ClientLoginId]
END
GO

IF OBJECT_ID(N'FK_ClientLoginAccess_ContactCode_Contacts_Code',N'F') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLoginAccess] 
	WITH NOCHECK ADD CONSTRAINT 
		[FK_ClientLoginAccess_ContactCode_Contacts_Code]  
	FOREIGN KEY 
		([ContactCode])
	REFERENCES 
		[dbo].[Contacts] ([Code])
END
GO

IF OBJECT_ID(N'FK_ClientLoginAccess_ContactCode_Contacts_Code',N'F') IS NOT NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLoginAccess] 
	CHECK CONSTRAINT
		[FK_ClientLoginAccess_ContactCode_Contacts_Code]
END
GO

IF OBJECT_ID(N'FK_ClientLoginAccess_MatterCode_matters_Code',N'F') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLoginAccess] 
	WITH NOCHECK ADD CONSTRAINT 
		[FK_ClientLoginAccess_MatterCode_matters_Code]  
	FOREIGN KEY 
		([MatterCode])
	REFERENCES 
		[dbo].[matters] ([Code])
END
GO

IF OBJECT_ID(N'FK_ClientLoginAccess_MatterCode_matters_Code',N'F') IS NOT NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLoginAccess] 
	CHECK CONSTRAINT
		[FK_ClientLoginAccess_MatterCode_matters_Code]
END
GO

--Maintain login history
IF OBJECT_ID(N'[dbo].[ClientPortalLogonInfo]',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[ClientPortalLogonInfo](
		[LogonInfoId]		   [BIGINT] IDENTITY(1,1) NOT NULL,
		[ClientLoginId]		   [BIGINT]				  NOT NULL,
		[LastAccessedDateTime] [DATETIME]			  NOT NULL,
		[DeviceInfo]		   [NVARCHAR](20)		  NULL,
		[Browser]			   [NVARCHAR](20)		  NULL,
		[IPAddress]			   [NVARCHAR](30)		  NULL,
		CONSTRAINT [PK_ClientPortalLogonInfo] PRIMARY KEY ([LogonInfoId])
	)
END
GO

IF OBJECT_ID(N'FK_ClientPortalLogonInfo_ClientLoginId_ClientLogin_ClientLoginId',N'F') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalLogonInfo] 
	WITH NOCHECK ADD CONSTRAINT 
		[FK_ClientPortalLogonInfo_ClientLoginId_ClientLogin_ClientLoginId]  
	FOREIGN KEY 
		([ClientLoginId])
	REFERENCES 
		[dbo].[ClientLogin] ([ClientLoginId])
END
GO

IF OBJECT_ID(N'FK_ClientPortalLogonInfo_ClientLoginId_ClientLogin_ClientLoginId',N'F') IS NOT NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalLogonInfo] 
	CHECK CONSTRAINT
		[FK_ClientPortalLogonInfo_ClientLoginId_ClientLogin_ClientLoginId]
END
GO

-- Table to maintain settings to store logo (Image), MimeType and UPE Key
IF OBJECT_ID(N'[dbo].[ClientPortalSetting]',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[ClientPortalSetting](
		[SettingId]		 [INT] IDENTITY(1,1) NOT NULL,
		[TenantLogo]	 [IMAGE]			 NULL,
		[TenantLogoType] [VARCHAR](10)       NULL,
		[UPEKey]		 [VARCHAR](200)      NULL
		CONSTRAINT [PK_ClientPortalSetting] PRIMARY KEY ([SettingId])
	)
END
GO

-- For Two Factor authentication; Included in both Tenant Setting level and User level
IF COL_LENGTH('[dbo].[ClientLogin]', 'IsTwoFactorEnabled') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLogin]
	ADD  
		[IsTwoFactorEnabled] BIT  
END
GO

IF OBJECT_ID(N'DF_ClientLogin_IsTwoFactorEnabled',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientLogin] 
	ADD CONSTRAINT 
		[DF_ClientLogin_IsTwoFactorEnabled]  
	DEFAULT 
		((0))
	FOR 
		[IsTwoFactorEnabled]
END
GO

IF COL_LENGTH('[dbo].[ClientPortalSetting]', 'IsTwoFactorEnabled') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalSetting]
	ADD  
		[IsTwoFactorEnabled] BIT  
END
GO

IF OBJECT_ID(N'DF_ClientPortalSetting_IsTwoFactorEnabled',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalSetting] 
	ADD CONSTRAINT 
		[DF_ClientPortalSetting_IsTwoFactorEnabled]  
	DEFAULT 
		((0))
	FOR 
		[IsTwoFactorEnabled]
END
GO

-- To handle User Lockout feature
IF COL_LENGTH('[dbo].[ClientPortalSetting]', 'IsLockOut_Attempt') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalSetting]
	ADD  
		[IsLockOut_Attempt] INT  
END
GO

IF COL_LENGTH('[dbo].[ClientPortalSetting]', 'LockoutDurationInMins') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalSetting]
	ADD  
		[LockoutDurationInMins] INT  
END
GO

IF COL_LENGTH('[dbo].[ClientPortalSetting]', 'PasswordResetNotification') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalSetting]
	ADD  
		[PasswordResetNotification] TINYINT  
END
GO


-- To store phone number update logs of users
IF OBJECT_ID(N'[dbo].[ClientPortalPhoneNoUpdateLog]',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[ClientPortalPhoneNoUpdateLog](		
		[UpdateLogId]		[BIGINT] IDENTITY(1,1)  NOT NULL,
		[ClientLoginId]		[BIGINT]				NOT NULL,
		[UpdatedByHandler]	[VARCHAR](10)			NOT NULL,
		[UpdateDateTime]	[DATETIME]				NULL,
		CONSTRAINT [PK_ClientPortalPhoneNoUpdateLog] PRIMARY KEY (UpdateLogId)
	)
END
GO

IF OBJECT_ID(N'FK_ClientPortalPhoneNoUpdateLog_ClientLoginId_ClientLogin_ClientLoginId',N'F') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalPhoneNoUpdateLog] 
	WITH NOCHECK ADD CONSTRAINT 
		[FK_ClientPortalPhoneNoUpdateLog_ClientLoginId_ClientLogin_ClientLoginId]  
	FOREIGN KEY 
		([ClientLoginId])
	REFERENCES 
		[dbo].[ClientLogin] ([ClientLoginId])
END
GO

IF OBJECT_ID(N'FK_ClientPortalPhoneNoUpdateLog_ClientLoginId_ClientLogin_ClientLoginId',N'F') IS NOT NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalPhoneNoUpdateLog] 
	CHECK CONSTRAINT
		[FK_ClientPortalPhoneNoUpdateLog_ClientLoginId_ClientLogin_ClientLoginId]
END
GO

IF OBJECT_ID(N'FK_ClientPortalPhoneNoUpdateLog_UpdatedByHandler_Handlers_CODE',N'F') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalPhoneNoUpdateLog] 
	WITH NOCHECK ADD CONSTRAINT 
		[FK_ClientPortalPhoneNoUpdateLog_UpdatedByHandler_Handlers_CODE]  
	FOREIGN KEY 
		([UpdatedByHandler])
	REFERENCES 
		[dbo].[Handlers] ([CODE])
END
GO

IF OBJECT_ID(N'FK_ClientPortalPhoneNoUpdateLog_UpdatedByHandler_Handlers_CODE',N'F') IS NOT NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalPhoneNoUpdateLog] 
	CHECK CONSTRAINT
		[FK_ClientPortalPhoneNoUpdateLog_UpdatedByHandler_Handlers_CODE]
END
GO

-- TO MAINTAIN USER SETTING FOR CLIENT PORTAL
IF OBJECT_ID(N'[dbo].[ClientPortalUserSetting]',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[ClientPortalUserSetting](		
		[SettingId]			[INT] IDENTITY(1,1)  NOT NULL,
		[ClientLoginId]		[BIGINT]			 NOT NULL,
		[Key]				[VARCHAR](100)		 NOT NULL,
		[Value]				[NVARCHAR](MAX)		 NOT NULL,
		CONSTRAINT [PK_ClientPortalUserSetting] PRIMARY KEY ([SettingId])
	)
END
GO

IF OBJECT_ID(N'FK_ClientPortalUserSetting_ClientLoginId_ClientLogin_ClientLoginId',N'F') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalUserSetting] 
	WITH NOCHECK ADD CONSTRAINT 
		[FK_ClientPortalUserSetting_ClientLoginId_ClientLogin_ClientLoginId]  
	FOREIGN KEY 
		([ClientLoginId])
	REFERENCES 
		[dbo].[ClientLogin] ([ClientLoginId])
END
GO

IF OBJECT_ID(N'FK_ClientPortalUserSetting_ClientLoginId_ClientLogin_ClientLoginId',N'F') IS NOT NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalUserSetting] 
	CHECK CONSTRAINT
		[FK_ClientPortalUserSetting_ClientLoginId_ClientLogin_ClientLoginId]
END
GO

IF OBJECT_ID(N'[dbo].[ClientPortalRecentMatter]',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[ClientPortalRecentMatter](		
		[RecentMatterId]	[INT] IDENTITY(1,1)  NOT NULL,
		[ClientLoginId]		[BIGINT]			 NOT NULL,
		[MatterCode]		[VARCHAR](15)		 NOT NULL,
		[LastAccessDate]	[DATETIME]			 NOT NULL,
		CONSTRAINT [PK_ClientPortalRecentMatter] PRIMARY KEY ([RecentMatterId])
	)
END
GO

IF OBJECT_ID(N'FK_ClientPortalRecentMatter_ClientLoginId_ClientLogin_ClientLoginId',N'F') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalRecentMatter] 
	WITH NOCHECK ADD CONSTRAINT 
		[FK_ClientPortalRecentMatter_ClientLoginId_ClientLogin_ClientLoginId]  
	FOREIGN KEY 
		([ClientLoginId])
	REFERENCES 
		[dbo].[ClientLogin] ([ClientLoginId])
END
GO

IF OBJECT_ID(N'FK_ClientPortalRecentMatter_ClientLoginId_ClientLogin_ClientLoginId',N'F') IS NOT NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalRecentMatter] 
	CHECK CONSTRAINT
		[FK_ClientPortalRecentMatter_ClientLoginId_ClientLogin_ClientLoginId]
END
GO

IF OBJECT_ID(N'DF_ClientPortalRecentMatter_LastAccessDate',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ClientPortalRecentMatter] 
	ADD CONSTRAINT 
		[DF_ClientPortalRecentMatter_LastAccessDate]  
	DEFAULT 
		((GETUTCDATE())) 
	FOR 
		[LastAccessDate]
END
GO