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

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DF_BriefConfigDetail_BriefGuid]'))
BEGIN
ALTER TABLE [dbo].[BriefConfigDetail] ADD  CONSTRAINT [DF_BriefConfigDetail_BriefGuid]  DEFAULT (newid()) FOR [BriefGuid]
END
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,"DocumentDateFormat":"dd MMM yyyy"}')
END
GO


-- New DB Structure Changes


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 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

GO

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

GO

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

GO

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
GO

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

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'Release' )
BEGIN
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Release](
	[ReleaseID]	    INT IDENTITY(1,1) NOT NULL, CONSTRAINT PK_Release_ReleaseID PRIMARY KEY CLUSTERED (ReleaseID),
	[Version]		VARCHAR(100),
	[VersionLabel]  VARCHAR(100),
	[ReleaseDate]	DATETIME,
	[ExecutedBy]	VARCHAR(200),
	[UpdatedStatus] TINYINT CONSTRAINT [DF_Release_UpdatedStatus]  DEFAULT (0)
)
END
GO

IF EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'ControlSetting' and [NAME] = 'KeyValue')
BEGIN
ALTER TABLE [dbo].[ControlSetting] ALTER COLUMN [KeyValue] NVARCHAR(MAX)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefDocumentVersion' )
BEGIN
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[BriefDocumentVersion]
(
	[BriefDocVersionUniqId] BIGINT IDENTITY(1,1), CONSTRAINT PK_BriefDocumentVersion_BriefDocVersionUniqId PRIMARY KEY CLUSTERED (BriefDocVersionUniqId),
	[BriefConfigDetailId] BigINT NOT NULL CONSTRAINT FK_BriefDocumentVersion_BriefConfigDetailId FOREIGN KEY (BriefConfigDetailId)REFERENCES [dbo].[BriefConfigDetail] (BriefConfigDetailId),
	[BriefCode] [varchar](20) NOT NULL,
	[ActionId]	INT NOT NULL,
	[TrackReference] INT NOT NULL,
	[DocumentVersion] INT NOT NULL,
	[CreatedDate] DATETIME,
	[CreatedUser] INT
)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'BriefConfigDetail' and [NAME] = 'BriefIsDeleted')
BEGIN
ALTER TABLE [dbo].[BriefConfigDetail] ADD [BriefIsDeleted] TINYINT NOT NULL
CONSTRAINT DF_BriefConfigDetail_BriefIsDeleted DEFAULT 0
WITH VALUES
END
GO

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

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

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

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefActivityLog' )
BEGIN
CREATE TABLE [dbo].[BriefActivityLog] (
	[BriefActyLogUniqId]   BIGINT IDENTITY(1,1) NOT NULL, CONSTRAINT PK_BriefActivityLog_BriefActyLogUniqId PRIMARY KEY CLUSTERED (BriefActyLogUniqId),
	[BriefConfigDetailId]  BIGINT NOT NULL CONSTRAINT FK_BriefActivityLog_BriefConfigDetailId FOREIGN KEY (BriefConfigDetailId)REFERENCES [dbo].[BriefConfigDetail] (BriefConfigDetailId),
	[BriefActyMessages]    VARCHAR(MAX),
	[BriefActySource]      VARCHAR(200),
	[BriefActyStatus]      VARCHAR(20),
	[BriefActyCreatedDate] DATETIME,
	[BriefActyCreatedUser] BIGINT
)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefErrorFileLog' )
BEGIN
CREATE TABLE [dbo].[BriefErrorFileLog](
[BriefErrorFileUniqId]       BIGINT IDENTITY(1,1) NOT NULL, CONSTRAINT PK_BriefErrorFileLog_BriefErrorFileUniqId PRIMARY KEY CLUSTERED (BriefErrorFileUniqId),
[BriefConfigDetailId]        BIGINT NOT NULL CONSTRAINT FK_BriefErrorFileLog_BriefConfigDetailId FOREIGN KEY (BriefConfigDetailId)REFERENCES [dbo].[BriefConfigDetail] (BriefConfigDetailId),
[BriefErrorFileName]         VARCHAR(200),
[BriefErrorFileSource]       VARCHAR(30),
[BriefErrorFileCreatedOn]    DATETIME,
[BriefErrorFileCreatedBy]    BIGINT,
)
END
GO

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

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefTemplate' )
BEGIN
CREATE TABLE [dbo].[BriefTemplate] (
[BriefTemplateUniqId]   INT IDENTITY(1,1) NOT NULL, CONSTRAINT PK_BriefTemplate_BriefTemplateUniqId PRIMARY KEY CLUSTERED (BriefTemplateUniqId),
[BriefConfigDetailId]   BIGINT NOT NULL CONSTRAINT FK_BriefTemplate_BriefConfigDetailId FOREIGN KEY (BriefConfigDetailId)REFERENCES [dbo].[BriefConfigDetail] (BriefConfigDetailId),	
[BriefTemplateName]     VARCHAR(200) NOT NULL,
[BriefTemplateSection]  NVARCHAR(MAX),
[CreatedBy]				BIGINT,
[CreatedOn]				DATETIME
)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'ApplicationSetting' )
BEGIN
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[ApplicationSetting]
(
	[ApplUniqId] INT IDENTITY(1,1), CONSTRAINT PK_ApplicationSetting_ApplUniqId PRIMARY KEY CLUSTERED ([ApplUniqId]),
	[KeyName]  VARCHAR(200),
	[KeyValue] NVARCHAR(MAX)
)
END
GO


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

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

IF NOT EXISTS(SELECT TOP 1 1 FROM [ApplicationSetting] WHERE [KeyName] = 'IncomingEmail') 
BEGIN
INSERT [dbo].[ApplicationSetting] ([KeyName], [KeyValue]) VALUES (N'IncomingEmail', N'Email from [SenderAddress] to [RecipientAddress] on - [Date]')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [ApplicationSetting] WHERE [KeyName] = 'OutgoingEmail') 
BEGIN
INSERT [dbo].[ApplicationSetting] ([KeyName], [KeyValue]) VALUES (N'OutgoingEmail', N'Email to [SenderAddress] by [RecipientAddress] on - [Date]')
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'BriefConfigDetail' AND COLUMN_NAME = 'BriefStatus')
BEGIN
    ALTER TABLE [dbo].[BriefConfigDetail] DROP COLUMN [BriefStatus]
END
GO


IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'BriefConfigDetail' AND COLUMN_NAME = 'BriefExcludeImages')
BEGIN  
	ALTER TABLE [dbo].[BriefConfigDetail] ADD [BriefExcludeImages] BIT NOT NULL
	CONSTRAINT DF_BriefConfigDetail_BriefExcludeImages DEFAULT 1
	WITH VALUES
END
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'BriefConfigDetail' AND COLUMN_NAME = 'BriefTotalSize')
BEGIN  
	ALTER TABLE [dbo].[BriefConfigDetail] ADD [BriefTotalSize] BIGINT NULL
END
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ApplicationSetting' AND COLUMN_NAME = 'KeyValue')
BEGIN
ALTER TABLE [dbo].[ApplicationSetting] ALTER COLUMN [KeyValue] NVARCHAR(MAX)
END
GO

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


IF NOT EXISTS(SELECT TOP 1 1 FROM [ApplicationSetting] WHERE [KeyName] = 'AlertBriefProcessByEmailTemplate') 
BEGIN
INSERT [dbo].[ApplicationSetting] ([KeyName], [KeyValue]) VALUES (N'AlertBriefProcessByEmailTemplate',
 N'<!DOCTYPE html>
<html lang="en" xmlns="http://www.w3.org/1999/xhtml" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office">
   <head>
      <meta charset="utf-8">
      <meta name="viewport" content="width=device-width">
      <meta http-equiv="X-UA-Compatible" content="IE=edge">
      <meta name="x-apple-disable-message-reformatting">
      <style>/ What it does: Remove spaces around the email design added by some email clients. / / Beware: It can remove the padding / margin and add a background color to the compose a reply window. / html, body{margin: 0 auto !important; padding: 0 !important; height: 100% !important; width: 100% !important;}h1, h2, h3{margin-top: 22px; margin-bottom: 22px;}/ What it does: Stops email clients resizing small text. /{-ms-text-size-adjust: 100%; -webkit-text-size-adjust: 100%;}/ What it does: Centers email on Android 4.4 / div[style*="margin: 16px 0"]{margin: 0 !important;}/ What it does: Stops Outlook from adding extra spacing to tables. / table, td{mso-table-lspace: 0pt !important; mso-table-rspace: 0pt !important;}/ What it does: Fixes webkit padding issue. Fix for Yahoo mail table alignment bug. Applies table-layout to the first 2 tables then removes for anything nested deeper. / table{border-spacing: 0 !important; border-collapse: collapse !important; table-layout: fixed !important; margin: 0 auto !important;}table table table{table-layout: auto;}/ What it does: Uses a better rendering method when resizing images in IE. / img{-ms-interpolation-mode: bicubic;}/ What it does: Prevents Windows 10 Mail from underlining links despite inline CSS. Styles for underlined links should be inline. / a{text-decoration: none;}/ What it does: A work-around for email clients meddling in triggered links. / [x-apple-data-detectors], / iOS / .unstyle-auto-detected-links , .aBn{border-bottom: 0 !important; cursor: default !important; color: inherit !important; text-decoration: none !important; font-size: inherit !important; font-family: inherit !important; font-weight: inherit !important; line-height: inherit !important;}/ What it does: Prevents Gmail from displaying a download button on large, non-linked images. / .a6S{display: none !important; opacity: 0.01 !important;}/ If the above doesn"t work, add a .g-img class to any image in question. / img.g-img + div{display: none !important;}/ What it does: Removes right gutter in Gmail iOS app: https://github.com/TedGoas/Cerberus/issues/89 / / Create one of these media queries for each additional viewport size you"d like to fix / / iPhone 4, 4S, 5, 5S, 5C, and 5SE / @media only screen and (min-device-width: 320px) and (max-device-width: 374px){.email-container{min-width: 320px !important;}}/ iPhone 6, 6S, 7, 8, and X / @media only screen and (min-device-width: 375px) and (max-device-width: 413px){.email-container{min-width: 375px !important;}}/ iPhone 6+, 7+, and 8+ / @media only screen and (min-device-width: 414px){.email-container{min-width: 414px !important;}}</style>
      <style>/ What it does: Hover styles for buttons / .button-td, .button-a{transition: all 100ms ease-in;}.button-td-primary:hover, .button-a-primary:hover{background: #555555 !important; border-color: #555555 !important;}/ Media Queries / @media screen and (max-width: 600px){/ What it does: Adjust typography on small screens to improve readability */ .email-container p{font-size: 17px !important;}}</style>
   </head>
   <body width="100%" style="margin: 0; padding: 0 !important; mso-line-height-rule: exactly;">
        <div class="container" style="margin: 0 auto; padding: 0 10px;">
      <center style="width: 100%;">
         <table style="    font-family: Helvetica,Arial,sans-serif;
    width: 100%;
    border-bottom-color: #681351;
    border-bottom-width: 5px;
    border-bottom-style: solid;
    margin: 0;
    padding: 0;
    background-color: #f3f1f1;
    font-size: 14px;" bgcolor="#f3f1f1">
            <tbody>
               <tr style="font-family: Helvetica,Arial,sans-serif; margin: 0; padding: 0;">
                  <td align="">
                     <div style="width: 100%; margin: 0 auto; padding: 0 10px" class="email-container">
                        <h1>Keyhouse Brief Builder</h1>
                     </div>
                  </td>
               </tr>
            </tbody>
         </table>
         <table style="font-family: Helvetica,Arial,sans-serif; width: 100%; margin: 0; padding: 0;" bgcolor="">
            <tbody>
               <tr style="font-family: Helvetica,Arial,sans-serif; margin: 0; padding: 0;">
                  <td style="" bgcolor="#FFFFFF" align="">
                     <div style="max-width: 600px; margin: 0 auto; padding: 0 10px" class="email-container">
                        <p style="text-align: left;">Hi,</p>
                          
        <h2 style="font-size: 16px; margin: 0px 0;">
            List of brief currently in process
            <small style="font-size: 0.5em;"></small>
        </h2>
        <table style="border-collapse: collapse; width: 100%; border: 1px solid #000;">
            <tr style="background-color: #95A5A6; font-size: 14px; letter-spacing: 0.03em;">
                <th style="border: 1px solid #000; padding: 10px;">Less than 100 MB</th>
                <th style="border: 1px solid #000; padding: 10px;">Between 100 MB and 250 MB</th>
                <th style="border: 1px solid #000; padding: 10px;">Between 250 MB and 500 MB</th>
                <th style="border: 1px solid #000; padding: 10px;">Between 500 MB and 750 MB</th>
                <th style="border: 1px solid #000; padding: 10px;">Between 750 MB and 1GB</th>
                <th style="border: 1px solid #000; padding: 10px;">Greater than 1 GB</th>
            </tr>
            <tr style="background-color: #ffffff; box-shadow: 0px 0px 9px 0px rgba(0,0,0,0.1);">
                <td style="border: 1px solid #000; padding: 10px;">#Less100Mb</td>
                <td style="border: 1px solid #000; padding: 10px;">#Between100Mbto250Mb</td>
                <td style="border: 1px solid #000; padding: 10px;">#Between250Mbto500Mb</td>
                <td style="border: 1px solid #000; padding: 10px;">#Between500Mbto750Mb</td>
                <td style="border: 1px solid #000; padding: 10px;">#Between750Mbto1Gb</td>
                <td style="border: 1px solid #000; padding: 10px;">#Greater1gb</td>
            </tr>
        </table>
    
                        <p><br>Thanks, <br>The Keyhouse Team</p>
                     </div>
                  </td>
               </tr>
            </tbody>
         </table>
      </center>
      </div>
      <p></p>
      <p></p>
      <hr style="width: 100%;">
      <p> </p>
      <div style="font-size: 10px; padding: 0 10px"><br></div>
      <p></p>
   </body>
</html>')
END
GO



----SCROL PAGINATION TYPE TABLE -- START
IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'MatterAccessListType' )
BEGIN
CREATE TYPE MatterAccessListType AS TABLE
(
    MatterCode VARCHAR(15)
);
END
GO

--END

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

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

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'BriefLocalFilesActionMap' )
BEGIN
CREATE TABLE [dbo].[BriefLocalFilesActionMap](
BriefConfigDetailId BIGINT CONSTRAINT PK_BriefLocalFilesActionMap_BriefConfigDetailId PRIMARY KEY CLUSTERED (BriefConfigDetailId),
ActionId INT NOT NULL
)
END
GO