/*

	select * from [AvastAgentServiceConfiguration]

e:\Managed Workplace\90-NativeAV\OMSolution.root\OMSolution\LPI.Database.OM\SQL_CE\039_InitialData_810_90-NativeAV.sql
*/

insert [AvastAgentServiceConfiguration] ([id],[service_id],[key],[value],[is_url])
SELECT 1, 0,	'check_xmpp_queue_interval_sec',	'86400',0
	where not exists (select * from AvastAgentServiceConfiguration where service_id = 0 and [key] = 'check_xmpp_queue_interval_sec')
go

insert [AvastAgentServiceConfiguration] ([id],[service_id],[key],[value],[is_url])
SELECT 2, 0,	'latest_version',	'3.3.0', 0
	where not exists (select * from AvastAgentServiceConfiguration where service_id = 0 and [key] = 'latest_version')
go


/*
	AvastAgentServiceConfiguration 92-SP1
*/
insert [AvastAgentServiceConfiguration] ([id],[service_id],[key],[value],[is_url])
SELECT 4, 0,	'report_server_url',	'http://localhost:7207/ReportService/event', 1
	where not exists (select * from AvastAgentServiceConfiguration where service_id = 0 and [key] = 'report_server_url')
go

insert [AvastAgentServiceConfiguration] ([id],[service_id],[key],[value],[is_url])
select 5, 1,	'2014_latest_version',	'2014.0.4570',0
	where not exists (select * from AvastAgentServiceConfiguration where service_id = 1 and [key] like '20%_latest_version'  )
go

/*
	AvastAgentServiceConfiguration 92-SP1
*/
insert [AvastAgentServiceConfiguration] ([id],[service_id],[key],[value],[is_url])
select 6, 1,	'2014_update_server_backup',	'http://backup.avg.cz/softw/14cc/update/', 1
	where not exists (select * from AvastAgentServiceConfiguration where service_id = 1 and [key] like '20%_update_server_backup')
go

insert [AvastAgentServiceConfiguration] ([id],[service_id],[key],[value],[is_url])
select 7, 1,	'2014_update_server_primary',	'http://update.avg.com/softw/14cc/update/', 1
	where not exists (select * from AvastAgentServiceConfiguration where service_id = 1 and [key] like '20%_update_server_primary')
go

insert [AvastAgentServiceConfiguration] ([id],[service_id],[key],[value],[is_url])
select 8, 1,	'2014_license_key',	'FxYnm/MnAKlC2CkLxHFr0OMmKmZQyjOZ+f1dT2eNAxoAXPNC0t7BBLJ/y3YwywMB4kIodcJ5gpfHoy+R0TZP1CjVHas8ZfKANyu5S/5lH3/be2yYG30rY5sX/natYy2C80ANkqDFgf3hOVbaBsWgR2z+fMBu1KCqfxQWFyn5ufc=', 0
	where not exists (select * from AvastAgentServiceConfiguration where service_id = 1 and [key] like '20%_license_key')
go

insert [AvastAgentServiceConfiguration] ([id],[service_id],[key],[value],[is_url])
select 9, 1,	'2014_download_avg_x86',	'http://download.avg.com/filedir/inst/avg_cci_x86_all_2014_4765a8097.exe', 1
	where not exists (select * from AvastAgentServiceConfiguration where service_id = 1 and [key] like '20%_download_avg_x86')
go

/*
	9.2-SP1
*/

insert [AvastAgentServiceConfiguration] ([id],[service_id],[key],[value],[is_url])
select 10, 1,	'2014_download_avg_x64',	'http://download.avg.com/filedir/inst/avg_cci_x64_all_2014_4765a8097.exe', 1
	where not exists (select * from AvastAgentServiceConfiguration where service_id = 1 and [key] like '20%_download_avg_x64')
go

/*
	9.2-SP1
*/

insert [AvastAgentServiceConfiguration] ([id],[service_id],[key],[value],[is_url])
select 11, 1,	'app_remover',	'https://installer.cloudcare.avg.com/AppRemover.exe', 1
	where not exists (select * from AvastAgentServiceConfiguration where service_id = 1 and [key] = 'app_remover')
go

/* 
	11.1.0-SP1 - Avast
*/

insert [AvastAgentServiceConfiguration] ([id],[service_id],[key],[value],[is_url])
SELECT 12, 0,	'policy_api_url',	'https://localhost:7206/v2/PolicyService',1
	where not exists (select * from AvastAgentServiceConfiguration where service_id = 0 and [key] = 'policy_api_url')
go

insert [AvastAgentServiceConfiguration] ([id],[service_id],[key],[value],[is_url])
SELECT 14, 1,	'product_update_map',	'http://vers.avast.com/vers/produpd.xml',1
	where not exists (select * from AvastAgentServiceConfiguration where service_id = 1 and [key] = 'product_update_map')
go

/*
	AvastAgentServiceConfiguration 10.2 MR1
*/
update 
	[AvastAgentServiceConfiguration] 
set 
	value = '2016.0.7797'
   ,[key] = '2016_latest_version'
where 
	[key] like '20%_latest_version'
go

update 
	[AvastAgentServiceConfiguration] 
set 
	value = 'http://files-download.avg.com/inst/mp/avg_cci_x86_all_1_0_2_36337_913.exe'
   ,[key] = '2016_download_avg_x86'
where 
	[key] like '20%_download_avg_x86'
go

update 
	[AvastAgentServiceConfiguration] 
set 
	value = 'http://files-download.avg.com/inst/mp/avg_cci_x64_all_1_0_2_36337_912.exe'
   ,[key] = '2016_download_avg_x64'
where 
	[key] like '20%_download_avg_x64'
go

update 
	[AvastAgentServiceConfiguration] 
set 
	value = 'http://update.avg.com/softw/16cc/update',
    [key] = '2016_update_server_primary'
where 
	[key] like '20%_update_server_primary'
go

update 
	[AvastAgentServiceConfiguration] 
set 
	value = 'http://backup.avg.cz/softw/16cc/update',
    [key] = '2016_update_server_backup'
where 
	[key] like '20%_update_server_backup'
go

update 
	[AvastAgentServiceConfiguration] 
set 
	value = 'n2fZ7oSSoOTCUdT0vCM+o5vBSfhSldXF3Ydi0PymCYshFU3hI+ANfp0RycTHEx6n5tVXh53asuZkKuKRepD5qYx57NHqK6PucgOVFwcf+2+KiOquHFwUFZfuDewrdNA2GK+x31PKVRLuWURwlGymi7M78VCgKzZ7v0TS5StrpQg=',
    [key] = '2016_license_key'
where 
	[key] like '20%_license_key'
go

/*
	AvastAgentServiceConfiguration 11.3
*/
UPDATE [AvastAgentServiceConfiguration]
SET [value] = '4.11.0'
WHERE [key] = 'latest_version'
GO
/*
	Alerts
*/

SET IDENTITY_INSERT [AlertType] ON
GO

INSERT [AlertType] ([AlertTypeID], [AlertTypeName], [IsSelfHealable], [AllowMultipleAlertRules], [IsLegacy90], [AlertingEngine], [AlertSubjectType], [AlertSourceType]) 
		select 301, N'AvVirusDefinitionOutOfDate', 0, 0, 0, 1, 2, 1
		 where not exists (select * from [AlertType] where AlertTypeID = 301)
GO
INSERT [AlertType] ([AlertTypeID], [AlertTypeName], [IsSelfHealable], [AllowMultipleAlertRules], [IsLegacy90], [AlertingEngine], [AlertSubjectType], [AlertSourceType]) 
		select 302, N'AvVirusThreatDetected', 0, 0, 0, 1, 2, 1
		 where not exists (select * from [AlertType] where AlertTypeID = 302)
GO
INSERT [AlertType] ([AlertTypeID], [AlertTypeName], [IsSelfHealable], [AllowMultipleAlertRules], [IsLegacy90], [AlertingEngine], [AlertSubjectType], [AlertSourceType]) 
		select 303, N'AvDisabled', 0, 0, 0, 1, 2, 1
		 where not exists (select * from [AlertType] where AlertTypeID = 303)
GO
INSERT [AlertType] ([AlertTypeID], [AlertTypeName], [IsSelfHealable], [AllowMultipleAlertRules], [IsLegacy90], [AlertingEngine], [AlertSubjectType], [AlertSourceType]) 
		select 304, N'AvVirusScanOverdue', 0, 0, 0, 1, 2, 1
		 where not exists (select * from [AlertType] where AlertTypeID = 304)
GO
INSERT [AlertType] ([AlertTypeID], [AlertTypeName], [IsSelfHealable], [AllowMultipleAlertRules], [IsLegacy90], [AlertingEngine], [AlertSubjectType], [AlertSourceType]) 
		select 305, N'AvDeviceRestartRequired', 0, 0, 0, 1, 2, 1
		 where not exists (select * from [AlertType] where AlertTypeID = 305)
GO

SET IDENTITY_INSERT [AlertType] OFF
GO

/*
	Adding Alert Types Attributes

AlertAttributeTypes for VirusDefinitionOutOfDate Alert Type
*/
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 301, 'SuppressionDuration', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 301
		                              and [AttributeName] = 'SuppressionDuration')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 301, 'Unit', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 301
		                              and [AttributeName] = 'Unit')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 301, 'Version', 'string', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 301
		                              and [AttributeName] = 'Version')
GO
/*
	AlertAttributeTypes for VirusThreatDetected Alert Type
*/
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 302, 'SuppressionDuration', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 302
		                              and [AttributeName] = 'SuppressionDuration')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 302, 'Unit', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 302
		                              and [AttributeName] = 'Unit')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 302, 'ThreatType', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 302
		                              and [AttributeName] = 'ThreatType')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 302, 'ThreatName', 'string', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 302
		                              and [AttributeName] = 'ThreatName')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 302, 'Severity', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 302
		                              and [AttributeName] = 'Severity')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 302, 'Source', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 302
		                              and [AttributeName] = 'Source')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 302, 'State', 'Int64', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 302
		                              and [AttributeName] = 'State')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 302, 'ObjectPath', 'string', 1
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 302
		                              and [AttributeName] = 'ObjectPath')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 302, 'ThreatShield', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 302
		                              and [AttributeName] = 'ThreatShield')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 302, 'VirusAction', 'long', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 302
		                              and [AttributeName] = 'VirusAction')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 302, 'VirusType', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 302
		                              and [AttributeName] = 'VirusType')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 302, 'Flags', 'string', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 302
		                              and [AttributeName] = 'Flags')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 302, 'Version', 'string', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 302
		                              and [AttributeName] = 'Version')
GO
/*
	AlertAttributeTypes for AvDisabled Alert Type
*/
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 303, 'DisabledComponent', 'string', 1
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 303
		                              and [AttributeName] = 'DisabledComponent')
GO
/*
	AlertAttributeTypes for VirusScanOverdue Alert Type
*/
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 304, 'SuppressionDuration', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 304
		                              and [AttributeName] = 'SuppressionDuration')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 304, 'Unit', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 304
		                              and [AttributeName] = 'Unit')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 304, 'NumberOfDayWihNoScan', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 304
		                              and [AttributeName] = 'NumberOfDayWihNoScan')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 304, 'NoScanUnit', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 304
		                              and [AttributeName] = 'NoScanUnit')
GO
/*
	AlertAttributeTypes for DeviceRestartRequired Alert Type
*/
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 305, 'SuppressionDuration', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 305
		                              and [AttributeName] = 'SuppressionDuration')
GO
INSERT [AlertAttributeType] ([AlertTypeID], [AttributeName], [DataType], [KeyIndex]) 
	select 305, 'Unit', 'int', NULL
		 where not exists (select * from [AlertAttributeType] where AlertTypeID = 305
		                              and [AttributeName] = 'Unit')
GO

/*
	Adding AV Monitor Types
*/
INSERT MonitorType ([MonitorTypeID], [Name], [MonitorSubjectType], [IsLegacy90]) 
		select 301, N'AvVirusDefinitionOutOfDate', 2, 0
		 where not exists (select * from MonitorType where [MonitorTypeID] = 301)
GO
INSERT MonitorType ([MonitorTypeID], [Name], [MonitorSubjectType], [IsLegacy90]) 
		select 302, N'AvVirusThreatDetected', 2, 0
		 where not exists (select * from MonitorType where [MonitorTypeID] = 302)
GO
INSERT MonitorType ([MonitorTypeID], [Name], [MonitorSubjectType], [IsLegacy90]) 
		select 303, N'AvDisabled', 2, 0
		 where not exists (select * from MonitorType where [MonitorTypeID] = 303)
GO
INSERT MonitorType ([MonitorTypeID], [Name], [MonitorSubjectType], [IsLegacy90]) 
		select 304, N'AvVirusScanOverdue', 2, 0
		 where not exists (select * from MonitorType where [MonitorTypeID] = 304)
GO
INSERT MonitorType ([MonitorTypeID], [Name], [MonitorSubjectType], [IsLegacy90]) 
		select 305, N'AvDeviceRestartRequired', 2, 0
		 where not exists (select * from MonitorType where [MonitorTypeID] = 305)
GO
insert into ManagedElementType ( IsSingleInstance, ME2CategoryID, ManagedElementGuidColumnName, ManagedElementTypeGuid, TableName, TypeName )
    select 0,4,'VirusChestItemGuid','96F3003B-5DEF-4BDB-92AD-74D60443DE51','AV_VirusChest','AV_VirusChest'
    where not exists (select * from  ManagedElementType where  ManagedElementTypeGuid = '96F3003B-5DEF-4BDB-92AD-74D60443DE51' )
go

insert ManagedElementAttributeType 
(AttributeName, DataType, IsArray, IsKey, IsPKMember, IsMonitored, ManagedElementAttributeTypeGuid, ManagedElementTypeGuid, SqlDataType, [DataLength], UnitOfMeasure, IsIdentity)
 select 'Id','System.String',0,1,0,1,'E8DCB585-A162-4606-B547-D4A487B49AC8','96F3003B-5DEF-4BDB-92AD-74D60443DE51','nvarchar',null,null,0
 where not exists (select * from  ManagedElementAttributeType where  ManagedElementAttributeTypeGuid = 'E8DCB585-A162-4606-B547-D4A487B49AC8' )
go  

insert ManagedElementAttributeType 
(AttributeName, DataType, IsArray, IsKey, IsPKMember, IsMonitored, ManagedElementAttributeTypeGuid, ManagedElementTypeGuid, SqlDataType, [DataLength], UnitOfMeasure, IsIdentity)
 select 'FileName','System.String',0,0,0,1,'E4E2ECBD-6B91-4F17-9D0D-0432070BAE5D','96F3003B-5DEF-4BDB-92AD-74D60443DE51','nvarchar',null,null,0
 where not exists (select * from  ManagedElementAttributeType where  ManagedElementAttributeTypeGuid = 'E4E2ECBD-6B91-4F17-9D0D-0432070BAE5D' )  
go
 
insert ManagedElementAttributeType 
(AttributeName, DataType, IsArray, IsKey, IsPKMember, IsMonitored, ManagedElementAttributeTypeGuid, ManagedElementTypeGuid, SqlDataType, [DataLength], UnitOfMeasure, IsIdentity)
 select 'Location','System.String',0,0,0,1,'EB4B45EC-ABDE-426E-B826-2088F8632AD2','96F3003B-5DEF-4BDB-92AD-74D60443DE51','nvarchar',null,null,0
 where not exists (select * from  ManagedElementAttributeType where  ManagedElementAttributeTypeGuid = 'EB4B45EC-ABDE-426E-B826-2088F8632AD2' )
go

insert ManagedElementAttributeType 
(AttributeName, DataType, IsArray, IsKey, IsPKMember, IsMonitored, ManagedElementAttributeTypeGuid, ManagedElementTypeGuid, SqlDataType, [DataLength], UnitOfMeasure, IsIdentity)
 select 'VirusName','System.String',0,0,0,1,'39248283-9C83-4DD2-ADAD-90003B7B8F0B','96F3003B-5DEF-4BDB-92AD-74D60443DE51','nvarchar',null,null,0
 where not exists (select * from  ManagedElementAttributeType where  ManagedElementAttributeTypeGuid = '39248283-9C83-4DD2-ADAD-90003B7B8F0B' )
go
 
insert ManagedElementAttributeType 
(AttributeName, DataType, IsArray, IsKey, IsPKMember, IsMonitored, ManagedElementAttributeTypeGuid, ManagedElementTypeGuid, SqlDataType, [DataLength], UnitOfMeasure, IsIdentity)
 select 'AddedTime','System.String',0,0,0,1,'3394A384-243F-4387-914B-56F5AC5B3999','96F3003B-5DEF-4BDB-92AD-74D60443DE51','nvarchar',null,null,0
 where not exists (select * from  ManagedElementAttributeType where  ManagedElementAttributeTypeGuid = '3394A384-243F-4387-914B-56F5AC5B3999' )
go

insert ManagedElementAttributeType 
(AttributeName, DataType, IsArray, IsKey, IsPKMember, IsMonitored, ManagedElementAttributeTypeGuid, ManagedElementTypeGuid, SqlDataType, [DataLength], UnitOfMeasure, IsIdentity)
 select 'Status','System.String',0,0,0,1,'35AC2703-C48A-4E3B-81EF-5347F7CE2E11','96F3003B-5DEF-4BDB-92AD-74D60443DE51','nvarchar',null,null,0
 where not exists (select * from  ManagedElementAttributeType where  ManagedElementAttributeTypeGuid = '35AC2703-C48A-4E3B-81EF-5347F7CE2E11' )
go

insert ManagedElementAttributeType 
(AttributeName, DataType, IsArray, IsKey, IsPKMember, IsMonitored, ManagedElementAttributeTypeGuid, ManagedElementTypeGuid, SqlDataType, [DataLength], UnitOfMeasure, IsIdentity)
 select 'StatusChangedTime','System.String',0,0,0,1,'7BBC5CB4-22B6-4AA9-9B71-B13FA5C7719D','96F3003B-5DEF-4BDB-92AD-74D60443DE51','nvarchar',null,null,0
 where not exists (select * from  ManagedElementAttributeType where  ManagedElementAttributeTypeGuid = '7BBC5CB4-22B6-4AA9-9B71-B13FA5C7719D' )
go

