USE [Configuration01_Dat]
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'nType' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [nType] int NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'nSubType' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [nSubType] int NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'tValue' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [tValue] nvarchar(255) NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'tName' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [tName] nvarchar(255) NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'mFilter' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [mFilter] nvarchar(255) NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'bAddNew' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [bAddNew] bit NOT NULL DEFAULT ('0');
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'bSynch' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [bSynch] bit NOT NULL DEFAULT ('0');
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'nMailExt' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [nMailExt] int NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'nProjectID' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [nProjectID] int NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'tUniqueFields' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [tUniqueFields] nvarchar(50) NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'nImportSrc' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [nImportSrc] int NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'dLastRun' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [dLastRun] datetime NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'nInterval' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [nInterval] int NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'ADInstance' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [ADInstance] nvarchar(255) NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'TenantID' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [TenantID] nvarchar(255) NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'ClientID' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [ClientID] nvarchar(255) NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'SecretKey' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [SecretKey] nvarchar(255) NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'nImportType' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [nImportType] int NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'nImportAuthType' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [nImportAuthType] int NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'TotalImported' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [TotalImported] int NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'TotalAdded' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [TotalAdded] int NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'TotalSynchronized' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [TotalSynchronized] int NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Errors' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [Errors] int NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Enabled' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [Enabled] int NULL;
END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Show_Advanced' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [Show_Advanced] int NULL;
END
GO

----------------------
-- DATA
----------------------

IF NOT EXISTS (SELECT nID FROM [tblDts] WHERE nID = 5)
BEGIN
	INSERT [dbo].[tblDts] ([nID], [nRevisionNumber], [tShowInSummary], [nType], [nStateID], 
	[tName], [mDetailedDescription],[bAddNew], [bSynch], [nProjectID], [tUniqueFields], [nImportSrc], 
	[nInterval], [ADInstance], [TenantID], 
	[ClientID], [SecretKey], [nImportType], [nImportAuthType], [Enabled]) 
	VALUES (5, 1, N'-1', 1, 1, 
	N'Import Users from Azure AD', N'Configuration settings for importing users from Azure Active Directory into VIZOR.', 1, 0, 8, N'Id', 36, 
	24, N'https://login.microsoftonline.com/{0}', NULL, 
	NULL, NULL, 1, 46, 1)
END

GO


-- Max per Due Date on HelpDesk
IF NOT EXISTS (SELECT nID FROM [tblDts] WHERE nID = 3)
BEGIN
	INSERT [dbo].[tblDts] ([nID], [nRevisionNumber], [tShowInSummary], [nType], [nSubType],
	[tValue], [tName], [mDetailedDescription], [nStateID], [nProjectID]
	)

	VALUES (3, 1, N'-1', 2, 17,
	'0', 'Maximum Records per Due Date - All record types', 
	'VIZOR will not allow submtting records (e.g. HelpDesk ticket) for a given Due Date if the maximum number of records with the same Due Date has been reached. This limit applies to any type of record in the specified VIZOR project. A value of 0 indicates there is no limit.',
	1, 4	)

END


IF NOT EXISTS (SELECT nID FROM [tblDts] WHERE nID = 2)
BEGIN
	INSERT [dbo].[tblDts] ([nID], [nRevisionNumber], [tShowInSummary]) 
	VALUES (2, 1, N'-1')

END

UPDATE  [dbo].[tblDts]
	SET [nType]= 2,
	[nSubType]=18,
	[tName]='Public URL', 
	[mDetailedDescription]='The effective URL for users to log into VIZOR. This is typically a full DNS name including the Virtual Directory, like: https://try.vizor.cloud/connect/',
	[nStateID]=1,
	[nProjectID]=NULL
	WHERE nID = 2
	
GO

-- Due Date Blackout Dates on HelpDesk
IF NOT EXISTS (SELECT nID FROM [tblDts] WHERE nID = 4)
BEGIN
	INSERT [dbo].[tblDts] ([nID], [nRevisionNumber], [tShowInSummary], [nType], [nSubType],
	[tValue], [tName], [mDetailedDescription], [nStateID], [nProjectID]
	)

	VALUES (4, 1, N'-1', 2, 19,
	'0', 'Enable Blackout Dates for Due Date', 
	'Set the value to 1 to enable. If enabled, Blackout Dates specified in the choice table "BlackoutDates" will be enforced when submitting a record. This only applies to the Due Date field in the specified VIZOR project.',
	1, 4	)

END

-- MS Azure Authentication
IF NOT EXISTS (SELECT nID FROM [tblDts] WHERE nID = 6)
BEGIN
	INSERT [dbo].[tblDts] ([nID], [nRevisionNumber], [tShowInSummary], [nType], [nSubType],
	[tValue], [tName], [mDetailedDescription], [nStateID], [nProjectID]
	)

	VALUES (6, 1, N'-1', 2, 471,
	'0', 'Enable logons using Microsoft / Azure / Office 365 accounts', 
	'Set the value to 1 to enable. Enable this option to allow users to log on with their Microsoft / Azure / Office 365 accounts from the logon page. This change will only be applied after you restart IIS or recycle the VIZOR Application Pools.',
	1, NULL);
	
END
GO

