/*
Configuration Tables
Add configuration for password policy
*/
--01 - entity type
	DECLARE @ConfigurationEntityTypes TABLE ([Id] [int] NOT NULL,[Title] [nvarchar](max) NOT NULL);

	INSERT into @ConfigurationEntityTypes ([Id], [Title]) VALUES (13, N'PasswordPolicy');

	SET IDENTITY_INSERT [dbo].[ConfigurationEntityTypes] ON;

		MERGE INTO dbo.[ConfigurationEntityTypes] AS TARGET  
			USING @ConfigurationEntityTypes AS SOURCE  
			ON TARGET.Id = SOURCE.Id	
			WHEN MATCHED THEN    
				UPDATE SET Title = SOURCE.Title
			WHEN NOT MATCHED BY TARGET THEN   
				INSERT ([Id], [Title]) VALUES (SOURCE.Id, SOURCE.Title)  
		OUTPUT $action, SOURCE.*;
		
	SET IDENTITY_INSERT [dbo].[ConfigurationEntityTypes] OFF;

	
--02 - parameter types
	DECLARE @ConfigurationParameterTypes TABLE (Id int NOT NULL,Title nvarchar(max) NOT NULL);

	INSERT into @ConfigurationParameterTypes (Id, Title) VALUES (64, N'Maximum')
	INSERT into @ConfigurationParameterTypes (Id, Title) VALUES (65, N'Minimum')
	INSERT into @ConfigurationParameterTypes (Id, Title) VALUES (66, N'Expiration')
	INSERT into @ConfigurationParameterTypes (Id, Title) VALUES (67, N'AboutToExpire')
	INSERT into @ConfigurationParameterTypes (Id, Title) VALUES (68, N'IsHistoryActive')
	INSERT into @ConfigurationParameterTypes (Id, Title) VALUES (69, N'HistoryGenerations')
	INSERT into @ConfigurationParameterTypes (Id, Title) VALUES (70, N'LockTime')
	INSERT into @ConfigurationParameterTypes (Id, Title) VALUES (71, N'MaximumFailedAttempts')

	SET IDENTITY_INSERT dbo.ConfigurationParameterTypes ON

	MERGE INTO dbo.ConfigurationParameterTypes AS TARGET  
		USING @ConfigurationParameterTypes AS SOURCE  
		ON TARGET.Id = SOURCE.Id
		WHEN MATCHED THEN    
			UPDATE SET Title = SOURCE.Title
		WHEN NOT MATCHED BY TARGET THEN   
			INSERT (Id, Title) VALUES (SOURCE.Id, SOURCE.Title)  
	OUTPUT $action, SOURCE.*;

	SET IDENTITY_INSERT dbo.ConfigurationParameterTypes OFF


--03- policy parameters
	DECLARE @ConfigurationEntityParameters TABLE (Id int NOT NULL,EntityId int NOT NULL,ParameterId int not null);

	INSERT into @ConfigurationEntityParameters (Id, EntityId, ParameterId) VALUES (87, 13, 1)	--Name
	INSERT into @ConfigurationEntityParameters (Id, EntityId, ParameterId) VALUES (88, 13, 64)	--Maximum
	INSERT into @ConfigurationEntityParameters (Id, EntityId, ParameterId) VALUES (89, 13, 65)	--Minimum
	INSERT into @ConfigurationEntityParameters (Id, EntityId, ParameterId) VALUES (90, 13, 66)	--Expiration
	INSERT into @ConfigurationEntityParameters (Id, EntityId, ParameterId) VALUES (91, 13, 67)	--AboutToExpire
	INSERT into @ConfigurationEntityParameters (Id, EntityId, ParameterId) VALUES (92, 13, 68)	--IsHistoryActive
	INSERT into @ConfigurationEntityParameters (Id, EntityId, ParameterId) VALUES (93, 13, 69)	--HistoryGenerations
	INSERT into @ConfigurationEntityParameters (Id, EntityId, ParameterId) VALUES (94, 13, 70)	--LockTime
	INSERT into @ConfigurationEntityParameters (Id, EntityId, ParameterId) VALUES (95, 13, 71)	--MaximumFailedAttempts

	MERGE INTO dbo.ConfigurationEntityParameters AS TARGET  
		USING @ConfigurationEntityParameters AS SOURCE  
		ON TARGET.Id = SOURCE.Id
		WHEN MATCHED THEN    
			UPDATE SET entityid = SOURCE.entityid,ParameterId=source.ParameterId 
		WHEN NOT MATCHED BY TARGET THEN   
			INSERT (Id, EntityId,ParameterId) VALUES (SOURCE.Id,Source.EntityId, SOURCE.ParameterId)  
	OUTPUT $action, SOURCE.*;

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--	Add Password policy default
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

--04: ConfigurationGroups
--	Create Instance of the group 
	DECLARE @ConfigurationGroups TABLE (EntityId int NOT NULL,GuidKey uniqueidentifier not null );

	INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (13, 'A8E5DB52-785F-4861-A58D-46D3EB59FF1B') --Password policy default

	MERGE INTO dbo.ConfigurationGroups AS TARGET  
		USING @ConfigurationGroups AS SOURCE  
		ON upper(TARGET.GuidKey) = upper(SOURCE.GuidKey)
		WHEN MATCHED THEN    
				UPDATE SET entityid = SOURCE.entityid
		WHEN NOT MATCHED BY TARGET THEN   
				INSERT (EntityId,GuidKey) VALUES (Source.EntityId,upper(source.GuidKey)) 
	OUTPUT $action, SOURCE.*;

	declare @configurationGroupId int
	select @configurationGroupId = id from ConfigurationGroups where guidkey='A8E5DB52-785F-4861-A58D-46D3EB59FF1B'

--05: ConfigurationGroupParameters
--	Fill the group instance
	DECLARE @ConfigurationGroupParameters TABLE (GroupId int NOT NULL,ParameterId int not null, Value nvarchar(max));

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 1, N'PasswordPolicyDefault'	--name
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 64, N'20'	--Maximum password len
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 65, N'6'	--Minimum password len
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 66, N'365'	--Expiration days
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 67, N'7'	--Password about to expire days
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 68, N'true'	--IsHistoryActive
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 69, N'3'	--HistoryGenerations
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 70, N'5'	--LockTime
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 71, N'3'	--MaximumFailedAttempts

	MERGE INTO dbo.ConfigurationGroupParameters AS TARGET  
			USING @ConfigurationGroupParameters AS SOURCE  
			ON TARGET.GroupId  = SOURCE.GroupId
			AND TARGET.ParameterId =  SOURCE.ParameterId
			WHEN MATCHED THEN    
					UPDATE SET Value=source.Value
			WHEN NOT MATCHED BY TARGET THEN   
					INSERT ( GroupId,ParameterId,Value) VALUES (Source.GroupId, SOURCE.ParameterId,source.Value)  
	OUTPUT $action, SOURCE.*;

--06: ConfigurationEntityTree
--	Set the identifier in the tree for the instance
	DECLARE @ConfigurationEntityTree TABLE (EntityTypeId int not null,GroupId int NOT NULL, MachineName nvarchar(max), ServiceName nvarchar(max), ServiceId nvarchar(max), 
											ModuleIdentifier nvarchar(max),ParentEntityId int null,GuidKey uniqueidentifier not null );


	INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 13, @configurationGroupId, N'MachineId', N'Policies', N'PasswordPolicyDefault',N'PasswordPolicy', NULL, '1E1A8CE8-556A-4EAA-9C64-111E56D5DA16'

	MERGE INTO dbo.ConfigurationEntityTree AS TARGET  
			USING @ConfigurationEntityTree AS SOURCE  
			ON upper(TARGET.GuidKey) = upper(SOURCE.GuidKey)
             
			WHEN MATCHED THEN    
					UPDATE SET  MachineName=source.MachineName, ServiceName=source.ServiceName, ServiceId=source.ServiceId, 
					ModuleIdentifier=source.ModuleIdentifier, ParentEntityId=source.ParentEntityId
			WHEN NOT MATCHED BY TARGET THEN   
					INSERT ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId,GuidKey) 
					VALUES ( source.EntityTypeId, source.GroupId, source.MachineName, source.ServiceName, source.ServiceId, source.ModuleIdentifier, source.ParentEntityId, upper(source.GuidKey))
	OUTPUT $action, SOURCE.*;
