IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefConfigDetail' )
BEGIN
CREATE TABLE [dbo].[BriefConfigDetail](
	[KeyId] [int] IDENTITY(1,1) NOT NULL,
	[BriefCode]  AS ('BR'+right('000000'+CONVERT([varchar](6),[KeyId]),(6))) PERSISTED,
	[BriefName] [varchar](200) NULL,
	[BriefDesc] [varchar](500) NULL,
	[BriefConfig] [nvarchar](max) NULL,
	[BriefGuid] [uniqueidentifier] NULL,
	[BriefIsActive] [tinyint] NULL,
	[CreatedDate] [datetime] NULL,
	[CreatedUser] [varchar](20) NULL,
 CONSTRAINT [PK_BriefConfigDetail_KeyId] PRIMARY KEY CLUSTERED 
(
	[KeyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
ALTER TABLE [dbo].[BriefConfigDetail] ADD  CONSTRAINT [DF_BriefConfigDetail_BriefGuid]  DEFAULT (newid()) FOR [BriefGuid]
GO


GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefSection' )
BEGIN
CREATE TABLE [dbo].[BriefSection](
	[KeyID] [int] IDENTITY(1,1) NOT NULL,
	[BriefCode] [varchar](8) NULL,
	[Sequence] [int] NULL,
	[CoverPage] [bit] NULL,
	[SectionDetail] [nvarchar](max) NULL,
 CONSTRAINT [PK_BriefSections_KeyId] PRIMARY KEY CLUSTERED 
(
	[KeyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'ControlSetting' )
BEGIN
CREATE TABLE [dbo].[ControlSetting](
	[KeyId] [int] IDENTITY(1,1) NOT NULL,
	[KeyName] [varchar](250) NULL,
	[KeyValue] [varchar](500) NULL,
 CONSTRAINT [Pk_ControlSetting_KeyId] PRIMARY KEY CLUSTERED 
(
	[KeyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'UserDetails' )
BEGIN
CREATE TABLE [dbo].[UserDetails](
	[KeyId] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](200) NULL,
	[UserEmail] [varchar](250) NULL,
	[PasswordHashKey] [nvarchar](max) NULL,
	[LastLoginDate] [datetime] NULL,
	[IsKeyhouseUser] [tinyint] NULL,
PRIMARY KEY CLUSTERED 
(
	[KeyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'BriefConfigDetail' and [NAME] = 'BriefExistingSection')
BEGIN
 ALTER TABLE [dbo].[BriefConfigDetail] 
		 ADD [BriefExistingSection]	[VARCHAR](8) NULL
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'BriefConfigDetail' and [NAME] = 'LastModifiedDate')
BEGIN
 ALTER TABLE [dbo].[BriefConfigDetail] 
         ADD [LastModifiedDate] DATETIME
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'UserDetails' and [NAME] = 'UserCode')
BEGIN
 ALTER TABLE [dbo].[UserDetails] 
         ADD [UserCode] VARCHAR(8)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefGenerationStatus' )
BEGIN
CREATE TABLE [dbo].[BriefGenerationStatus](
	[KeyId] [int] IDENTITY(1,1) NOT NULL,
	[BriefCode] [varchar](100) NULL,
	[Status] [varchar](100) NULL,
	[StartDateTime] [datetime] NULL,
	[EndDateTime] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[KeyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'BriefConfigDetail' and [NAME] = 'BriefStatus')
BEGIN
 ALTER TABLE [dbo].[BriefConfigDetail] 
         ADD [BriefStatus] VARCHAR(100) NULL
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'BriefConfigDetail' and [NAME] = 'BriefTOCConfig')
BEGIN
 ALTER TABLE [dbo].[BriefConfigDetail] 
         ADD [BriefTOCConfig] [nvarchar](max) NULL
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [ControlSetting] WHERE [KeyName] = 'CaseSearchMaxResult') 
BEGIN
INSERT [dbo].[ControlSetting] ([KeyName], [KeyValue]) VALUES (N'CaseSearchMaxResult', N'100')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [ControlSetting] WHERE [KeyName] = 'TOCConfig') 
BEGIN
INSERT [dbo].[ControlSetting] ([KeyName], [KeyValue]) VALUES (N'TOCConfig', N'{"pageNumbering":"Section - Filename - Page","footerFontStyle":"Calibri","tocFontStyle":"Calibri","tocFontSize":7,"footerFontSize":7,"isBoldFooter":true,"isBoldTOC":true,"isPDFBookmarks":true,"isPDFHyperlinks":true,"isLeftAllignToc":true,"isDateColumnIncluded":true,"isIncludeTotal":true,"isIncludeFooter":true}')
END
GO


-- New DB Structure Changes


IF EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'UserDetail' )
Begin
Drop Table [dbo].[UserDetail];
End

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'UserDetail' )
BEGIN
	CREATE TABLE [dbo].[UserDetail]
	(
	[UserDetailId]	BIGINT IDENTITY(1,1) NOT NULL, CONSTRAINT PK_UserDetail_UserDetailId PRIMARY KEY CLUSTERED (UserDetailId),
    [HandlerId] [bigint] NULL,
    [TenantId] [int] NULL,
	[LastAccessedDate] [datetime] NULL,
	[CreatedDate] [datetime] NULL,
	)
END
GO


IF EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefConfigDetail' )
Begin
Drop Table [dbo].[BriefConfigDetail];
End

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefConfigDetail' )
BEGIN
	

/****** Object:  Table [dbo].[BriefConfigDetail]    Script Date: 12/14/2022 1:11:49 PM ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[BriefConfigDetail](
	[BriefConfigDetailId] BIGINT IDENTITY(1,1) NOT NULL, CONSTRAINT PK_BriefConfigDetail_BriefConfigDetailId PRIMARY KEY CLUSTERED (BriefConfigDetailId),
	[BriefCode]  AS ('BR'+right('000000'+CONVERT([varchar](6),[BriefConfigDetailId]),(6))) PERSISTED,
	[BriefName] [varchar](200) Not  NULL,
	[BriefDesc] [varchar](500) Not  NULL,
	[BriefConfig] [nvarchar](max) NULL,
	[BriefGuid] [uniqueidentifier] Not  NULL,
	[BriefIsActive] [tinyint] Not  NULL,
	[CreatedDate] [datetime] Not  NULL,
	[CreatedUserDetailId] BigINT NOT NULL CONSTRAINT FK_BriefConfigDetail_CreatedUserDetailId FOREIGN KEY (CreatedUserDetailId)REFERENCES [dbo].[UserDetail] (UserDetailId),
	[BriefExistingSection] [varchar](8) NULL,
	[ModifiedDate] [datetime] NULL,
	[ModifiedUserDetailId] BigINT NOT NULL CONSTRAINT FK_BriefConfigDetail_ModifiedUserDetailId FOREIGN KEY (ModifiedUserDetailId)REFERENCES [dbo].[UserDetail] (UserDetailId),
	[BriefTOCConfig] [nvarchar](max) NULL
	)

ALTER TABLE [dbo].[BriefConfigDetail] ADD  CONSTRAINT [DF_BriefConfigDetail_BriefGuid]  DEFAULT (newid()) FOR [BriefGuid]

End




IF EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefGenerationStatus' )
Begin
Drop Table [dbo].[BriefGenerationStatus];
End

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefGenerationStatus' )
BEGIN
	
/****** Object:  Table [dbo].[BriefGenerationStatus]    Script Date: 12/14/2022 1:29:45 PM ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[BriefGenerationStatus](
	[BriefGenerationStatusId] BIGINT IDENTITY(1,1) NOT NULL, CONSTRAINT PK_BriefGenerationStatus_BriefGenerationStatusId PRIMARY KEY CLUSTERED (BriefGenerationStatusId),
	[BriefCode] [varchar](100) Not NULL,
	[Status] [varchar](100) Not NULL,
	[StartDateTime] [datetime] Not NULL,
	[EndDateTime] [datetime] NULL,
	[BriefConfigDetailId] BigINT NOT NULL CONSTRAINT FK_BriefGenerationStatus_BriefConfigDetailId FOREIGN KEY (BriefConfigDetailId)REFERENCES [dbo].[BriefConfigDetail] (BriefConfigDetailId),
	)
End




IF EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefSection' )
Begin
Drop Table [dbo].[BriefSection];
End

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefSection' )
BEGIN


/****** Object:  Table [dbo].[BriefSection]    Script Date: 12/14/2022 1:34:28 PM ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[BriefSection](
	[BriefSectionId] BIGINT IDENTITY(1,1) NOT NULL, CONSTRAINT PK_BriefSection_BriefSectionId PRIMARY KEY CLUSTERED (BriefSectionId),
	[BriefCode] [varchar](8) Not NULL,
	[SectionDetail] [nvarchar](max) NULL,
	[BriefConfigDetailId] BigINT NOT NULL CONSTRAINT FK_BriefSection_BriefConfigDetailId FOREIGN KEY (BriefConfigDetailId) REFERENCES [dbo].[BriefConfigDetail] (BriefConfigDetailId),
	[CreatedDate] [datetime] Not  NULL,
	[CreatedUserDetailId] BigINT NOT NULL CONSTRAINT FK_BriefSection_CreatedUserDetailId FOREIGN KEY (CreatedUserDetailId) REFERENCES [dbo].[UserDetail] (UserDetailId),
	[ModifiedDate] [datetime] NULL,
	[ModifiedUserDetailId] BigINT NOT NULL CONSTRAINT FK_BriefSection_ModifiedUserDetailId FOREIGN KEY (ModifiedUserDetailId)REFERENCES [dbo].[UserDetail] (UserDetailId),
	)

End




IF EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'ControlSetting' )
Begin
Drop Table [dbo].[ControlSetting];
End

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'ControlSetting' )
BEGIN
/****** Object:  Table [dbo].[ControlSetting]    Script Date: 12/14/2022 1:38:17 PM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[ControlSetting](
	[ControlSettingId] BIGINT IDENTITY(1,1) NOT NULL, CONSTRAINT PK_ControlSetting_ControlSettingId PRIMARY KEY CLUSTERED (ControlSettingId),
	[KeyName] [varchar](250) Not NULL,
	[KeyValue] [varchar](500) Not NULL,
	[UserDetailId] BigINT NOT NULL CONSTRAINT FK_ControlSetting_UserDetailId FOREIGN KEY (UserDetailId)REFERENCES [dbo].[UserDetail] (UserDetailId)
	)
End



IF EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'BriefConfigDetail' and [NAME] = 'ModifiedUserDetailId')
BEGIN
ALTER TABLE [dbo].[BriefConfigDetail] ALTER COLUMN [ModifiedUserDetailId] BIGINT NULL
END
GO

IF EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'BriefSection' and [NAME] = 'ModifiedUserDetailId')
BEGIN
ALTER TABLE [dbo].[BriefSection] ALTER COLUMN [ModifiedUserDetailId] BIGINT NULL
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'BriefConfigDetail' and [NAME] = 'MatterCode')
BEGIN
ALTER TABLE [dbo].[BriefConfigDetail] ADD [MatterCode] VARCHAR(12)
END
GO