-- START MW-6184
INSERT INTO [AvastAgent]
SELECT [DeviceID]
      ,[DeviceGuid]
      ,[Version]
      ,CASE WHEN [State] = 101 THEN 4 ELSE [State] END AS [State]
	  ,CASE WHEN [Status] = 0 THEN  1
	        WHEN [Status] = 1 THEN  0
	        WHEN [Status] = 2 THEN  1
	        WHEN [Status] = 3 THEN  2010
			END AS ReasonCode
      ,[LastContact]
      ,[IsPolicyAssignedInAfb]
	  ,0 as [RequiresSeeding]
FROM [AV_CloudCareAgent] A
WHERE NOT EXISTS (SELECT * FROM [AvastAgent] B WHERE B.DeviceID = A.DeviceID)
GO

DELETE FROM [AvastAgentPolicy]
GO


-- MW no longer maintains the AV policy, but CC agent still needs a policy for some hardcoded settings. There will be always only one AvastAgentPolicy record with ID=1.
INSERT INTO [AvastAgentPolicy] ([PolicyID] ,[Configuration])
VALUES (1 , '<policy_settings><appearances type="array"><appearance><av_setting_id>1</av_setting_id><browser_performance_advice_enabled>true</browser_performance_advice_enabled><content_filter_notifications>false</content_filter_notifications><id>1</id><license_upgrade_notification_enabled>false</license_upgrade_notification_enabled><play_sounds_on_full_screen>true</play_sounds_on_full_screen><sound_events_enabled>true</sound_events_enabled><tray_notification>5551</tray_notification><user_notification>15</user_notification><user_tray_icon_enabled>true</user_tray_icon_enabled><row_version>1396633579</row_version><cc_tray_icon_enabled>false</cc_tray_icon_enabled><cc_program_menu_enabled>false</cc_program_menu_enabled><shredder_visible>false</shredder_visible><gamemode_on_fullscreen>true</gamemode_on_fullscreen><animate_transitions>true</animate_transitions><ignore_warning_components nil="true" /></appearance></appearances></policy_settings>')
GO

INSERT INTO [AvastAgentDevice]
SELECT [device_id]
      ,[account_id]
      ,1 AS [policy_id]
      ,[device_type_id]
      ,[operating_system_id]
      ,[device_status_id]
      ,[name]
      ,[description]
      ,[comment]
      ,[ccl_id]
      ,[token]
      ,[time_zone_id]
      ,[manufacturer]
      ,[domain]
      ,[wan_ip]
      ,[device_group_id]
      ,[is_master]
      ,[allow_remote_connection]
      ,[device_detection_enabled]
      ,[install_invite_id]
FROM [AVCC_devices]
GO

-- Reconstruct all missing AvastAgentDevice records. The 'auth' action below will force the agent to re-send this data and it will be populated correctly.
INSERT INTO [AvastAgentDevice]
SELECT DeviceID
	  ,1 AS [account_id]
	  ,1 AS [policy_id] 
	  ,1 AS [device_type_id]
	  ,24 AS [operating_system_id]
	  ,6 AS [device_status_id]
	  ,'unknown' AS [name]
      ,NULL AS [description]
      ,NULL AS [comment]
      ,NULL AS [ccl_id]
      ,NULL AS [token]
      ,NULL AS [time_zone_id]
      ,NULL AS [manufacturer]
      ,NULL AS [domain]
      ,NULL AS [wan_ip]
      ,NULL AS [device_group_id]
      ,NULL AS [is_master]
      ,NULL AS [allow_remote_connection]
      ,NULL AS [device_detection_enabled]
      ,NULL AS [install_invite_id]
FROM [AV_CloudCareAgent] A
WHERE NOT EXISTS (SELECT * FROM AvastAgentDevice B WHERE B.device_id = A.DeviceID)  -- To make it re-runable.
GO

-- Migrate AVCC_nics to the new table
INSERT INTO [AvastAgentNic]
SELECT [id]
      ,[device_id]
      ,[mac]
      ,[ipv4]
      ,[ipv6]
FROM [AVCC_nics] A
WHERE NOT EXISTS (SELECT * FROM AvastAgentNic B WHERE B.device_id = A.device_id AND B.id = A.id)  -- To make it re-runable.
GO

-- Migrate AVCC_device_services to the new table
INSERT INTO [AvastAgentService]
SELECT B.DeviceID AS DeviceID
      ,A.service_id AS ServiceID
	  ,A.upgrade_action_id AS UpgradeActionID
	  ,CASE WHEN A.[enabled] IS NULL THEN 1
	        ELSE A.[enabled]  
	        END AS [Enabled]
	  ,A.remove_competitive_products AS RemoveCompetitiveProducts
	   ,CASE WHEN D.[State] is NULL THEN  0
	         WHEN D.[State] = 101 THEN  4
	         ELSE A.device_service_status_id
	         END AS [StatusID]
	   ,CASE WHEN D.[State] IS NULL THEN  0
	         WHEN D.[State] = 101 THEN 101
	         ELSE A.device_service_status_id
	         END AS StateForServiceCenter
	  ,CASE WHEN D.[Status] = 0 THEN  1
	        WHEN D.[Status] = 1 THEN  0
	        WHEN D.[Status] = 2 THEN  1
	        WHEN D.[Status] = 3 THEN  2010
			END AS ReasonCode
	  ,A.install_beta AS InstallBeta
	  ,E.AvmSettingID AS InstallationToken
	  ,NULL AS ManagementUrl
	  ,A.DateCreated DateCreated
FROM AVCC_device_services A
INNER JOIN Device B ON B.DeviceGuid = A.DeviceGuid
INNER JOIN AVCC_devices C ON C.device_id = B.DeviceID
INNER JOIN AV_CloudCareAgent D ON D.DeviceGuid = A.DeviceGuid
INNER JOIN AV_Policy E ON E.PolicyID = C.policy_id
WHERE NOT EXISTS (SELECT * FROM [AvastAgentService] F WHERE F.DeviceID = B.DeviceID AND F.ServiceID = A.service_id) -- To make it re-runable
GO

-- Reconstruct all missing 'AvastAgentService' records. The agent upgrade process will populate the 'InstallationToken' and 'ManagementUrl' correctly.
INSERT INTO [AvastAgentService]
SELECT [DeviceID]
      ,1 AS [ServiceID]
      ,1 AS [UpgradeActionID]
      ,1 AS [Enabled]
      ,1 AS [RemoveCompetitiveProducts]
      ,4 AS [StatusID]
      ,4 AS [StateForServiceCenter]
      ,0 AS [ReasonCode]
      ,0 AS [InstallBeta]
      ,NULL AS [InstallationToken]
      ,NULL AS [ManagementUrl]
      ,GETDATE() AS [DateCreated]
FROM [AV_CloudCareAgent] A
WHERE NOT EXISTS (SELECT * FROM [AvastAgentService] B WHERE B.DeviceID = A.DeviceID AND B.ServiceID = 1)  -- To make it re-runable.
GO

-- Insert an 'auth' action to force CC agent to re-send AVCC_device data. This is only needed for devices where the 'AvastAgentDevice' record was reconstructed 
INSERT INTO [AvastAgentAction]
SELECT DeviceID AS [ActionID] 
       ,A.DeviceID AS [DeviceID]
	   ,1 AS [ServiceID]
	   ,1 AS [Status]
	   ,'auth' AS [Command]
	   ,'{"AuthTable":"9223372036854775807","PolicyId":0}' AS [CmdParameters]
	   ,GETDATE() AS [CmdCreated]
	   ,NULL AS [CmdStarted]
	   ,NULL AS [CmdCompleted]
	   ,NULL AS [ResultCode]
	   ,NULL AS [ResultMessage]
	   ,0 AS [Retries]
	   ,NULL AS [ParentActionID]
FROM [AV_CloudCareAgent] A
WHERE A.DeviceID NOT IN (SELECT device_id FROM [AVCC_devices]) 
AND NOT EXISTS (SELECT * FROM [AvastAgentAction] B WHERE B.ActionID = A.DeviceID) -- To make it re-runable
GO

/* This action is needed because SC needs to be notified with the correct state */
INSERT INTO [AvastAgentAction]
SELECT 1000000 + DeviceID AS [ActionID] 
       ,A.DeviceID AS [DeviceID]
	   ,1 AS [ServiceID]
	   ,1 AS [Status]
	   ,'UnmanageDevice' AS [Command]
	   ,'' AS [CmdParameters]
	   ,GETDATE() AS [CmdCreated]
	   ,NULL AS [CmdStarted]
	   ,NULL AS [CmdCompleted]
	   ,NULL AS [ResultCode]
	   ,NULL AS [ResultMessage]
	   ,0 AS [Retries]
	   ,NULL AS [ParentActionID]
FROM [AV_CloudCareAgent] A
WHERE [State] = 101
AND NOT EXISTS (SELECT * FROM [AvastAgentAction] B WHERE B.ActionID = 1000000 + A.DeviceID) -- To make it re-runable
GO

-- Migrate AVCC_alert_event_types to the new table
INSERT INTO [AvastAgentAlertEventType]
SELECT * FROM [AVCC_alert_event_types]
GO

-- Migrate AVCC_device_subservice_versions to the new table
INSERT INTO [AvastAgentSubserviceVersion] (
		[DeviceGuid]
      ,[DateCreated]
      ,[DateModified]
      ,[SubserviceItemGuid]
      ,[subservice_id]
      ,[major]
      ,[minor]
      ,[micro]
      ,[build]
      ,[incremental]
      ,[version_date]
      ,[out_of_date]
)
SELECT [DeviceGuid]
      ,[DateCreated]
      ,[DateModified]
      ,[SubserviceItemGuid]
      ,[subservice_id]
      ,[major]
      ,[minor]
      ,[micro]
      ,[build]
      ,[incremental]
      ,[version_date]
      ,[out_of_date]
FROM [AVCC_device_subservice_versions]
GO

-- Migrate AVCC_service_configurations to the new table
INSERT INTO [AvastAgentServiceConfiguration] (
	 [id]
	,[service_id]
	,[key]
	,[value]
	,[is_url]
)
SELECT [id]
      ,[service_id]
      ,[key]
      ,[value]
      ,[is_url]
FROM [AVCC_service_configurations]
GO
DELETE FROM [AvastAgentServiceConfiguration] WHERE [key] = 'policy_server_url' AND [service_id] = 0
GO

-- Deleting all AV related records for devices not managed by the OM
DELETE FROM AvastAgentSubserviceVersion 
WHERE DeviceGuid IN (SELECT DeviceGuid FROM Device D WHERE D.IsManaged = 0)
GO

DELETE FROM AvastAgentService 
WHERE DeviceID IN (SELECT DeviceID FROM Device D WHERE D.IsManaged = 0)
GO

DELETE FROM AvastAgentNic 
WHERE device_id IN (SELECT DeviceID FROM Device D WHERE D.IsManaged = 0)
GO

DELETE FROM AvastAgentDevice 
WHERE device_id IN (SELECT DeviceID FROM Device D WHERE D.IsManaged = 0)
GO

DELETE FROM AvastAgentAlertEventType 
WHERE DeviceGuid IN (SELECT DeviceGuid FROM Device D WHERE D.IsManaged = 0)
GO

DELETE FROM AV_VirusChest 
WHERE DeviceGuid IN (SELECT DeviceGuid FROM Device D WHERE D.IsManaged = 0)
GO

DELETE FROM AvastAgentAction 
WHERE DeviceID IN (SELECT DeviceID FROM Device D WHERE D.IsManaged = 0)
GO

DELETE FROM AvastAgent 
WHERE DeviceID IN (SELECT DeviceID FROM Device D WHERE D.IsManaged = 0)
GO

DELETE FROM [MwusDevice] WHERE DeviceGuid NOT IN (SELECT DeviceGuid FROM [Device])
GO

DROP TABLE [AV_Action];
GO
DROP TABLE [AV_CloudCareAgent];
GO
DROP TABLE [AV_Policy];
GO
DROP TABLE [AV_VirusVault];
GO
DROP TABLE [AVCC_alert_event_types];
GO
DROP TABLE [AVCC_device_services];
GO
DROP TABLE [AVCC_device_subservice_versions];
GO
DROP TABLE [AVCC_devices];
GO
DROP TABLE [AVCC_nics];
GO
DROP TABLE [AVCC_service_configurations];
GO

