PRINT N'Creating [dbo].[WmiServer.BackupProxiesView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.BackupProxiesView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.BackupProxiesView]
GO
	CREATE VIEW  [dbo].[WmiServer.BackupProxiesView]
	AS
		SELECT 
			p.*,
			c.[is_up_to_date] as component_is_up_to_date,
			c.[version] as component_version,
			(
				SELECT 
					COUNT(*) 
				FROM 
					[dbo].[Backup.Model.ResourceUsages] u 
				WHERE 
					p.[id] = u.[resource_id] AND 
					u.[resource_type] = 2								-- u.[resource_type] = ResourceTypes.Proxy
			) as curr_usage,
			CASE 
                WHEN p.usn > h.usn AND p.usn > c.usn THEN p.usn
                WHEN h.usn > p.usn AND h.usn > c.usn THEN h.usn
                ELSE c.usn
            END as real_usn,
            ISNULL(p.options.value('(/ViProxyOptions/IsAutoDetectDisks)[1]', 'bit'), 0) as auto_detect_disks,
			CAST((
				CASE WHEN p.options.value('(/ViProxyOptions/IsAutoDetectDisks)[1]', 'bit') = 1 THEN NULL
				ELSE
				(
					SELECT phd.datastore_name as name
					FROM 
						[dbo].[PhysHost_Disk] phd
					WHERE 
						phd.[physhost_id] = c.[physical_host_id] AND
						phd.[is_manual_added] = 1
					FOR XML RAW ('Datastore'), ROOT ('Datastores')
				)
				END
				) as XML) as connected_datastores
		FROM
			[dbo].[BackupProxies] p,
			[dbo].[Hosts] h,
			[dbo].[HostComponents] c
		WHERE
			c.[physical_host_id] = h.[physical_host_id] AND
			c.[type] = 0 AND											-- c.[type] = EComponentType.Transport
			h.[id] = p.[host_id]
	
GO

--------------------------------------------------------------------------------
PRINT N'Creating [dbo].[WmiServer.BackupRepositoriesView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.BackupRepositoriesView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.BackupRepositoriesView]
GO
	CREATE VIEW [dbo].[WmiServer.BackupRepositoriesView]
	AS	
		SELECT 
			r.id as repo_id,
			r.name as repo_name,
			r.total_space,
			r.free_space,
			r.type as repo_type,
			r.options as repo_options,
			r.path as path,
			h.*,
			c.[is_up_to_date] as component_is_up_to_date,
			c.[version] as component_version,
			(
				SELECT 
					COUNT(*) 
				FROM 
					[dbo].[Backup.Model.ResourceUsages] u 
				WHERE 
					r.[id] = u.[resource_id] AND 
					u.[resource_type] = 1								-- u.[resource_type] = ResourceTypes.Repository
			) as curr_usage,
			ISNULL(c.[type], -1) as comp_type,
			CASE 
                WHEN r.usn > ISNULL(h.usn, 0) AND r.usn > ISNULL(c.usn, 0) THEN r.usn
                WHEN r.usn < ISNULL(h.usn, 0)  AND ISNULL(h.usn, 0) > ISNULL(c.usn, 0) THEN h.usn
                ELSE c.usn
            END as max_usn
		FROM
			[dbo].[BackupRepositories] r 
			LEFT JOIN [dbo].[Hosts] h ON r.[host_id] = h.[id]
			LEFT JOIN [dbo].[HostComponents] c ON h.[physical_host_id] = c.[physical_host_id]
		WHERE
			ISNULL(c.[type], -1) IN (-1, 0) 											-- c.[type] = EComponentType.Transport			
GO



--------------------------------------------------------------------------------
-- [GetJobsWmiInfo]
PRINT N'Creating [dbo].[WmiServer.JobsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.JobsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.JobsView]
GO
	CREATE VIEW [dbo].[WmiServer.JobsView]
	AS
		SELECT
			jobs.[id],
			jobs.[name],
			jobs.[type],
			jobs.[latest_result],
			CASE jobs.[type]
				WHEN 51 THEN ISNULL(jobs.[options].value('(/JobOptionsRoot/GenerationPolicy/SimpleRetentionRestorePoints)[1]', 'int'), 0)
				ELSE ISNULL(jobs.[options].value('(/JobOptionsRoot/RetainCycles)[1]', 'int'), 0)
			END as retain_cycles,
			schedule_enabled, 
			schedule AS schedule_options, 
			ISNULL(options.value('(/*/RunManually)[1]', 'bit'), 1) AS run_manually,
			options,
			jobs.[usn],
			jobs.[parent_schedule_id],
			jobs.[platform]
		FROM
			[dbo].[BJobs] jobs	
		WHERE
			jobs.[type] IN (0,1,2,
			8,--QuickMigration
			24,--FileTapeBackup
			28,--VmTapeBackup
			51--BackupSync
			)
GO

--------------------------------------------------------------------------------
-- [WmiServer.ObjectsInJobsView]
PRINT N'Creating [dbo].[WmiServer.ObjectsInJobsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.ObjectsInJobsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.ObjectsInJobsView]
GO
	CREATE VIEW [dbo].[WmiServer.ObjectsInJobsView]
	AS
		SELECT
			j.[id] as id,
			o.[object_name] as obj_name,
			o.[object_id] as obj_reference,
			CASE 
				WHEN o.[platform] = 0 AND o.[viobject_type] = N'VirtualMachine' THEN 1 --VM
				WHEN o.[platform] = 1 THEN o.[type] 
				WHEN o.[platform] = 4 AND o.[type] = 4 THEN 1--VM
				ELSE 0
			END as obj_type,
			o.[platform] as obj_platform,
			j.[type] as oij_type,
			CASE 
                WHEN j.usn > o.usn THEN j.usn
                ELSE o.usn
            END as usn
		FROM 
			[dbo].[ObjectsInJobs] j,
			[dbo].[BObjects] o
		WHERE 
			j.[object_id] = o.[id] AND
			j.[type] IN (0,1) --include vm or exclude vm only
GO

--------------------------------------------------------------------------------
-- [WmiServer.HostsView]
PRINT N'Creating [dbo].[WmiServer.HostsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.HostsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.HostsView]
GO
	CREATE VIEW [dbo].[WmiServer.HostsView]
	AS
		SELECT
			id,
			type,
			name,
			info,
			usn,
			dns_name,
			host_unique_id
		FROM 
			[dbo].[fn.VisibleHosts]()
GO

-----------------
-- 
PRINT N'Creating [dbo].[WmiServer.BackupToVmInBackupAssociationView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.BackupToVmInBackupAssociationView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.BackupToVmInBackupAssociationView]
GO

	CREATE VIEW  [dbo].[WmiServer.BackupToVmInBackupAssociationView]
	AS
			
		SELECT left_id, right_id
		FROM 
		(
			SELECT 
				pnts.backup_id as left_id,
				oibs.id as right_id,
				ROW_NUMBER() OVER (PARTITION BY pnts.backup_id,oibs.object_id ORDER BY pnts.num DESC, oibs.[creation_time] DESC) rn 
			FROM  
				[dbo].[Backup.Model.Points] pnts,
				[dbo].[Backup.Model.OIBs] oibs
			WHERE				
				pnts.id = oibs.point_id and
				oibs.is_corrupted = 0
		) q
		WHERE q.rn = 1
GO



-----------------
PRINT N'Creating [dbo].[WmiServer.VmInBackupToVmAssociationView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.VmInBackupToVmAssociationView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.VmInBackupToVmAssociationView]
GO

	CREATE VIEW [dbo].[WmiServer.VmInBackupToVmAssociationView]
	AS
		SELECT left_id, right_id
		FROM 
		(
			SELECT 
				oibs.id as left_id,
				vms.id as right_id,
				ROW_NUMBER() OVER (PARTITION BY pnts.backup_id,oibs.object_id ORDER BY pnts.num DESC, oibs.[creation_time] DESC) rn 
			FROM  
				[dbo].[Backup.Model.Points] pnts,
				[dbo].[Backup.Model.OIBs] oibs,
				[dbo].[BObjects] vms
			WHERE				
				pnts.id = oibs.point_id and
				oibs.is_corrupted = 0 and
				oibs.object_id = vms.id
		) q
		WHERE q.rn = 1 
GO



-----------------
PRINT N'Creating [dbo].[WmiServer.LatestOibsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.LatestOibsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.LatestOibsView]
GO

	CREATE VIEW [dbo].[WmiServer.LatestOibsView]
	AS
		SELECT q.id, q.creation_time, q.backup_id, q.object_id, q.usn
		FROM
	    (
	    SELECT 
			oibs.id,
			oibs.creation_time,
			oibs.object_id,
			oibs.usn,
			--oibs.vmname,
			pnts.backup_id,
			ROW_NUMBER() OVER (PARTITION BY pnts.backup_id, oibs.object_id ORDER BY pnts.num DESC, oibs.[creation_time] DESC) rn
		 FROM 
			[dbo].[Backup.Model.OIBs] oibs 
			JOIN [dbo].[Backup.Model.Points] pnts ON pnts.id = oibs.point_id
		 WHERE 
			oibs.is_corrupted = 0
		) q
		WHERE 
			q.rn = 1

GO


-----------------
PRINT N'Creating [dbo].[WmiServer.VmInBackupsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.VmInBackupsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.VmInBackupsView]
GO
	CREATE VIEW [dbo].[WmiServer.VmInBackupsView]
	AS
			
		--CREATE TABLE #PartitionedOibs (vm_in_backup_id uniqueidentifier, last_backup_date datetime, backup_id uniqueidentifier, obj_id uniqueidentifier, vm_in_backup_usn bigint )
		--INSERT INTO #PartitionedOibs
		--SELECT * FROM #PartitionedOibs
		
		SELECT 			
			loibs.vm_in_backup_id,
			loibs.vm_in_backup_usn,
			loibs.last_backup_date,
			qq.first_backup_date, 
			qq.restore_points
		FROM
		(
			SELECT
				 q.backup_id,
				 q.object_id,
				 MIN(q.creation_time) as first_backup_date,
				 MAX(q.rn) as restore_points
			FROM		
			(
					SELECT 
						oibs.id,
						oibs.object_id,
						--oibs.vmname,
						pnts.backup_id,
						pnts.link_id,
						oibs.creation_time,
						ROW_NUMBER() OVER (PARTITION BY pnts.backup_id, oibs.object_id ORDER BY pnts.num DESC, oibs.[creation_time] DESC) rn
					 FROM 
						[dbo].[Backup.Model.OIBs] oibs 
						JOIN [dbo].[Backup.Model.Points] pnts ON pnts.id = oibs.point_id
					 WHERE 
						oibs.is_corrupted = 0
			)q
			GROUP BY q.backup_id, q.object_id
		) qq
		INNER JOIN 
		(SELECT 
			id as vm_in_backup_id
			,creation_time as last_backup_date
			,backup_id as backup_id
			,object_id as obj_id
			,usn as vm_in_backup_usn
		 FROM [dbo].[WmiServer.LatestOibsView] 
		--WHERE (id = '00000000-0000-0000-0000-000000000000' OR id = q.id)
		) loibs ON loibs.backup_id = qq.backup_id AND loibs.obj_id = qq.object_id
GO


---------------------------------
PRINT N'Creating [dbo].[WmiServer.JobSessionsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.JobSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.JobSessionsView]
GO
	CREATE VIEW [dbo].[WmiServer.JobSessionsView]
    AS
          SELECT 
                  js.id,
                  js.job_id,
				  js.job_type,
                  js.state,
                  js.result,
				  js.reason,
                  creation_time,
                  end_time,
                  processed_size,
				  js.orig_session_id,
                  js.log_xml.value('(/Root/Log)[1]', 'nvarchar(max)') as last_log_entry,
				  bjs.will_be_retried,
				  js.job_name,
                  CASE 
                        WHEN js.usn > bjs.usn THEN js.usn
                        ELSE bjs.usn
                  END as usn,
				  bjs.source_storage as src_stg_load,
				  bjs.source_proxy as src_proxy_load,
				  bjs.source_network as src_network_load,
				  bjs.target_network as tgt_network_load,
				  bjs.target_storage as tgt_stg_load,
				  bjs.[backup_total_size] as total_backed_up_size,
				  bjs.[avg_speed] as processing_rate,
				  bjs.[job_algorithm] as job_alg,
				  bjs.[session_algorithm] as session_alg
          FROM [dbo].[Backup.Model.BackupJobSessions] bjs
          INNER JOIN [dbo].[Backup.Model.JobSessions] js ON bjs.id = js.id
		  WHERE js.job_type IN (
			  0, 
			  1, 
			  2, 
			  3, 
			  8, 
			  24,
			  28,
			  50,
			  51)
GO


--------------------------------------------------------------------------------
-- [WmiServer.VPowerServersView]
PRINT N'Creating [dbo].[WmiServer.VPowerServersView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.VPowerServersView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.VPowerServersView]
GO
	CREATE VIEW [dbo].[WmiServer.VPowerServersView]
	AS
		SELECT 
			c.[id] as component_id,
			h.[name] as host_name,
			h.[type] as host_type,
			c.[is_up_to_date] as component_is_up_to_date,
			c.[version] as component_version,
			CASE 
                WHEN h.usn > c.usn THEN h.usn
                ELSE c.usn
            END as real_usn
		FROM
			[dbo].[Hosts] h,
			[dbo].[HostComponents] c
		WHERE
			c.[physical_host_id] = h.[physical_host_id] AND
			c.[type] = 1 											-- c.[type] = EComponentType.Nfs			
GO



-- [WmiServer.VmsView]
PRINT N'Creating [dbo].[WmiServer.VmsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.VmsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.VmsView]
GO
	CREATE VIEW [dbo].[WmiServer.VmsView]
	AS
		SELECT 			
			o.*,
			hosts.name as hierarchy_root_name
		FROM
			[dbo].[BObjects] o
			INNER JOIN [dbo].[fn.VisibleHosts]() hosts ON o.host_id = hosts.id
		WHERE
			(o.[platform] = 0 AND o.[viobject_type] = N'VirtualMachine') OR
			(o.[platform] = 1 AND o.[type] = 1) OR
			(o.[platform] = 4 AND o.[type] = 4) 
GO

--------------------------------------------------------------------------------
-- [WmiServer.BackupTaskSessionsView]
PRINT N'Creating [dbo].[WmiServer.BackupTaskSessionsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.BackupTaskSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.BackupTaskSessionsView]
GO
	CREATE VIEW [dbo].[WmiServer.BackupTaskSessionsView]
	AS
		SELECT 
			bts.[id],
			bts.[status],
			bts.[object_name],
			bts.[work_details],
			bts.[creation_time],
			bts.[end_time],
			bts.[stored_size],
			bts.[reason],
			bts.[total_size] as total_vm_size,
			bts.[read_size] as read_size,
			bts.[processed_used_size] as processed_used_size,
			bts.[avg_speed] as processing_rate,
			bts.[source_storage] as src_stg_load,
			bts.[source_proxy] as src_proxy_load,
			bts.[source_network] as src_network_load,
			bts.[target_network] as tgt_network_load,
			bts.[target_storage] as tgt_stg_load,
			bts.[usn],
			bts.[total_objects] as total_objects,
			bts.[processed_objects] as processed_objects
		FROM
			[dbo].[Backup.Model.BackupTaskSessions] bts
GO



--------------------------------------------------------------------------------
-- [WmiServer.BackupsView]
PRINT N'Creating [dbo].[WmiServer.BackupsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.BackupsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.BackupsView]
GO
	CREATE VIEW [dbo].[WmiServer.BackupsView]
	AS
			
		WITH ReplicaBackupPointsCTE
		(
		 backup_id, --uniqueidentifier,
		 stg_id, --uniqueidentifier,
		 size, --bigint,
		 is_incremental, --bit,
		 creation_time, --datetime,
		 usn --bigint
		)
		AS
		(
			SELECT DISTINCT
				b.[id] as backup_id,
				q_stgs_oibs.[stg_id],
				q_stgs_oibs.[size],
				q_stgs_oibs.[is_incremental],
				q_stgs_oibs.[creation_time],
				[dbo].[MaxValue2](b.usn, q_stgs_oibs.usn) as usn
			FROM
				[dbo].[Backup.Model.Backups] b
				LEFT JOIN
				(
					-- points:
					SELECT
						s.id as stg_id,
						s.backup_id as backup_id,
						q_oibs.id as oib_id,
						ISNULL(s.stats.value('(/CBackupStats/BackupSize)[1]', 'bigint'), 0) as size,
						q_oibs.[creation_time],
						CASE WHEN PATINDEX('%.vbk', s.file_path) > 0 THEN 0 ELSE 1 END AS is_incremental,		
						[dbo].[MaxValue2](s.usn, q_oibs.usn) as usn
					FROM
					[dbo].[Backup.Model.Storages] s 
					INNER JOIN 	
					(
						SELECT	
							oibs.[id],
							oibs.[storage_id],
							points.[creation_time],			
							[dbo].[MaxValue2](points.[usn], oibs.[usn]) as usn
						FROM 
						[dbo].[Backup.Model.Points] points
						INNER JOIN [dbo].[Backup.Model.OIBs] oibs ON points.[id] = oibs.[point_id]
						WHERE oibs.is_corrupted = 0 AND oibs.parent_id IS NULL
						GROUP BY oibs.[id], oibs.[storage_id], points.[creation_time], points.[usn], oibs.[usn]
					) q_oibs
					ON s.[id] = q_oibs.[storage_id]
				) q_stgs_oibs ON b.id = q_stgs_oibs.backup_id
		 )
		SELECT
			backups.id,
			backups.job_name,
			CONVERT(BIT, CASE WHEN backups.[job_target_type] = 1 THEN 1 ELSE 0 END) as is_replica,
			(SELECT MAX(points.usn) FROM ReplicaBackupPointsCTE points WHERE points.backup_id = backups.id) as usn,
			(SELECT ISNULL(SUM(points.size), 0) FROM ReplicaBackupPointsCTE points WHERE points.backup_id = backups.id AND is_incremental = 0) as full_storages_size,
			(SELECT ISNULL(SUM(points.size), 0) FROM ReplicaBackupPointsCTE points WHERE points.backup_id = backups.id AND is_incremental = 1) as incremental_storages_size,
			(SELECT ISNULL(COUNT(stg_id), 0) FROM ReplicaBackupPointsCTE points WHERE points.backup_id = backups.id AND is_incremental = 0) as full_points_count,
			(SELECT ISNULL(COUNT(stg_id), 0) FROM ReplicaBackupPointsCTE points WHERE points.backup_id = backups.id AND is_incremental = 1) as incr_points_count,
			backups.platform
		FROM
			[dbo].[Backup.Model.Backups] backups
		--order by job_name

GO


--------------------------------------------------------------------------------
-- [WmiServer.SbJobVmSessionsView]
PRINT N'Creating [dbo].[WmiServer.SbJobVmSessionsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.SbJobVmSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.SbJobVmSessionsView]
GO
	CREATE VIEW [dbo].[WmiServer.SbJobVmSessionsView]
	AS
		SELECT 
			s.[id],
			p.[creation_time] as verification_date,
			s.[start_time],
			s.[ping_status],
			s.[heartbeat_status],
			s.[test_script_status],
			s.[overall_status],
			s.[state],
			s.[finish_time],
			o.[options],
			CASE 
				WHEN s.usn > ISNULL(o.usn, 0) THEN s.usn
				ELSE ISNULL(o.usn, 0)
			END as usn
		FROM
			[dbo].[Backup.Model.SbTaskSessions] s
			JOIN [dbo].[Backup.Model.OIBs] oibs ON s.[oib_id] = oibs.[id]
			JOIN [dbo].[Backup.Model.Points] p ON oibs.[point_id] = p.[id]
			LEFT JOIN [dbo].[ObjectsInApplicationGroups] o ON s.[object_id] = o.[object_id]
GO


--------------------------------------------------------------------------------
-- [WmiServer.SbJobsView]
PRINT N'Creating [dbo].[WmiServer.SbJobsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.SbJobsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.SbJobsView]
GO
	CREATE VIEW [dbo].[WmiServer.SbJobsView]
	AS
		SELECT 
			[id],
			[name],
			schedule_enabled, 
			schedule AS schedule_options,
			ISNULL(options.value('(/*/RunManually)[1]', 'bit'), 1) AS run_manually,
			[usn],
			[parent_schedule_id],
			[platform]
		FROM
			[dbo].[BJobs]
		WHERE 
			[type] = 3
GO


--------------------------------------------------------------------------------
PRINT N'Creating [dbo].[WmiServer.RestoreSessionsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.RestoreSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.RestoreSessionsView]
GO
	CREATE VIEW [dbo].[WmiServer.RestoreSessionsView]
	AS
		SELECT 
			js.[id] AS id,
			js.[job_type] AS type,
			js.[creation_time] AS start_time,
			js.[end_time] AS end_time,
			rjs.[oib_creation_time] AS restore_point_date,
			rjs.[initiator_name],
			rjs.[oib_display_name] as vm_display_name,
			[dbo].[MaxValue](js.[usn], rjs.[usn]) AS usn
		FROM
			[dbo].[Backup.Model.JobSessions] js
			INNER JOIN [dbo].[Backup.Model.RestoreJobSessions] rjs ON js.[id] = rjs.[id]
GO


--------------------------------------------------------------------------------
PRINT N'Creating [dbo].[WmiServer.RestoreItemsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.RestoreItemsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.RestoreItemsView]
GO
	CREATE VIEW [dbo].[WmiServer.RestoreItemsView]
	AS
		SELECT 
		   ra.[id]
		  --,ra.[restore_session_id]
		  ,ra.[restore_state] as state
		  ,ra.[item_type]
		  ,ra.[item_name]
		  ,ra.[item_size]
		  ,ra.[dest_type]
		  ,ra.[dest_host]
		  ,ra.[destination] as destination
		  ,ra.[start_time]
		  ,ra.[finish_time]
		  ,ra.[message]
		  ,ra.[source]
		  ,ra.[usn]
		FROM
			[dbo].[ItemRestoreAudits] ra
GO



PRINT N'Creating [dbo].[WmiServer.ServiceComponentsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.ServiceComponentsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.ServiceComponentsView]
GO
	CREATE VIEW [dbo].[WmiServer.ServiceComponentsView]
	AS
		SELECT 
			c.[id] AS service_id,
			c.[type] AS component_type,
			h.*
		FROM
			[dbo].[HostComponents] c,
			[dbo].[Hosts] h
		WHERE
			h.[physical_host_id] = c.[physical_host_id] 
GO



PRINT N'Creating [dbo].[WmiServer.SbAppGroupsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.SbAppGroupsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.SbAppGroupsView]
GO
	CREATE VIEW [dbo].[WmiServer.SbAppGroupsView]
	AS
		SELECT 
			f.[id],
			f.[name] as name,
			f.[usn] as usn
		FROM		
			[dbo].[Folders] f
		WHERE
			f.[parent_id] = '32E53DC4-62EA-47b4-8E02-88509707D587'--app groups root
GO

---------------------------------
PRINT N'Creating [dbo].[WmiServer.SbJobSessionsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.SbJobSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.SbJobSessionsView]
GO
	CREATE VIEW [dbo].[WmiServer.SbJobSessionsView]
    AS
          SELECT 
                  js.id,
                  js.state,
                  js.result,
                  creation_time,
                  end_time,
				  js.job_name,
				  js.reason,
                  CASE 
                        WHEN js.usn > bjs.usn THEN js.usn
                        ELSE bjs.usn
                  END as usn
          FROM [dbo].[Backup.Model.SbSessions] bjs
          INNER JOIN [dbo].[Backup.Model.JobSessions] js ON bjs.id = js.id
GO


PRINT N'Creating [dbo].[WmiServer.CifsSharesView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.CifsSharesView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.CifsSharesView]
GO
	CREATE VIEW [dbo].[WmiServer.CifsSharesView]
	AS
		SELECT 
			repo.[id] as id,
			repo.[id] as repo_id,
			repo.[path] as path
		FROM
			[dbo].[BackupRepositories] repo
		WHERE
			repo.[type] = 2 -- CIFS
GO



PRINT N'Creating [dbo].[WmiServer.LinuxRepositoriesView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.LinuxRepositoriesView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.LinuxRepositoriesView]
GO
	CREATE VIEW [dbo].[WmiServer.LinuxRepositoriesView]
	AS
		SELECT 
			repo.[id] as id,
			repo.[id] as repo_id,
			repo.[path] as path
		FROM
			[dbo].[BackupRepositories] repo
		WHERE
			repo.[type] = 1 -- Linux
GO


PRINT N'Creating [dbo].[WmiServer.RestorePointsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.RestorePointsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.RestorePointsView]
GO
	CREATE VIEW [dbo].[WmiServer.RestorePointsView]
	AS
		SELECT 
			points.[id] as id,
			points.[file_path] as file_path,
			ISNULL(points.stats.query('CBackupStats/BackupSize').value('.', 'bigint'), 0) as backup_size,
			points.[creation_time] as restore_point_date,
			ISNULL(points.[stats].query('CBackupStats/DedupRatio').value('.', 'int'), 100) as dedup_ratio,
			ISNULL(points.[stats].query('CBackupStats/CompressRatio').value('.', 'int'), 100) as compress_ratio,
			points.[usn] as usn,
			(SELECT COUNT(*) FROM [dbo].[Backup.Model.OIBs] oib WHERE oib.[storage_id]=points.[id] AND oib.[is_corrupted]=0) as oibs_count
		FROM
			[dbo].[Backup.Model.Storages] points
GO



PRINT N'Creating [dbo].[WmiServer.VmRestorePointsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.VmRestorePointsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.VmRestorePointsView]
GO
	CREATE VIEW [dbo].[WmiServer.VmRestorePointsView]
	AS
		SELECT 
			vmRestorePoints.[id] as id,
			vmRestorePoints.[creation_time] as vm_restore_point_date,			
			vmRestorePoints.[vmname] as vm_name,
			vmRestorePoints.[display_name] as display_vm_name,
			vmRestorePoints.[storage_id] as storage_id,
			vmRestorePoints.[usn] as usn
		FROM
			[dbo].[Backup.Model.OIBs] vmRestorePoints
GO

PRINT N'Creating [dbo].[WmiServer.WanAcceleratorsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.WanAcceleratorsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.WanAcceleratorsView]	
GO
	CREATE VIEW [dbo].[WmiServer.WanAcceleratorsView]
	AS
		SELECT
			wa.[id],
			wa.[name],
			wa.[description],
			wa.[host_id],
			hc.[type],
			hc.[is_up_to_date] as is_up_to_date,
			hc.[version],
			hc.options.value('(/WanAcceleratorClientOptions/MaxCacheSize)[1]', 'int') as maxCacheSize,
			hc.options.value('(/WanAcceleratorClientOptions/SizeUnit)[1]', 'nvarchar(10)') as sizeUnit,
			hc.options.value('(/WanAcceleratorClientOptions/TrafficPort)[1]', 'int') as trafficPort,
			hc.options.value('(/WanAcceleratorClientOptions/CachePath)[1]', 'nvarchar(max)') as cachePath,
			hc.options.value('(/WanAcceleratorClientOptions/DownloadStreamCount)[1]', 'int') as connectionsCount
		FROM [dbo].[WanAccelerators] wa
		JOIN [dbo].[Hosts] h ON wa.[host_id] = h.[id]
		JOIN [dbo].[HostComponents] hc ON hc.[physical_host_id] = h.[physical_host_id]
		WHERE hc.[type] = 5
GO

PRINT N'Creating [dbo].[WmiServer.JobProcessesView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.JobProcessesView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.JobProcessesView]	
GO
	CREATE VIEW [dbo].[WmiServer.JobProcessesView]
	AS
		SELECT 
			js.[id],
			js.[pid],
			(SELECT TOP 1 Name FROM [dbo].[Hosts] WHERE id = '6745a759-2205-4cd2-b172-8ec8f7e60ef8') as host_name,
			0 as process_type --manager process
		FROM [dbo].[Backup.Model.JobSessions] js
		WHERE EXISTS(SELECT id FROM [dbo].[Backup.Model.BackupJobSessions] bjs WHERE bjs.[id] = js.[id])
		AND js.state = 5
		UNION
		SELECT 
			pa.[session_id], 
			pa.[pid], 
			pa.[host_name], 
			1 as process_type --agent process
		FROM [dbo].[ProxyAgents] pa
GO


PRINT N'Creating [dbo].[WmiServer.TapeJobsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.TapeJobsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.TapeJobsView]	
GO
	CREATE VIEW [dbo].[WmiServer.TapeJobsView]
	AS
		SELECT
			tjobs.[id],
			tjobs.[usn],
			tjobs.[full_mediapool_id],
			tjobs.[incremental_mediapool_id]
		FROM
			[dbo].[Tape.jobs] tjobs		

GO



PRINT N'Creating [dbo].[WmiServer.BackupCopyJobsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.BackupCopyJobsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.BackupCopyJobsView]	
GO
	CREATE VIEW [dbo].[WmiServer.BackupCopyJobsView]
	AS
		SELECT
			jobs.[id],
			jobs.[usn],
			jobs.[options]
		FROM
			[dbo].[BJobs] jobs
		WHERE
			jobs.[type] = 51

GO




PRINT N'Creating [dbo].[WmiServer.MediaPoolsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.MediaPoolsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.MediaPoolsView]	
GO
	CREATE VIEW [dbo].[WmiServer.MediaPoolsView]
	AS
		SELECT
			mps.[id],
			mps.[name],
			mps.[usn]
		FROM
			[dbo].[Tape.media_pools] mps

GO



PRINT N'Creating [dbo].[WmiServer.TapesView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.TapesView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.TapesView]	
GO
	CREATE VIEW [dbo].[WmiServer.TapesView]
	AS
		SELECT
			tapes.[id],
			tapes.[name],
			tapes.[barcode],
			tapes.[media_family_id],
			tapes.[media_pool_id],
			tapes.[media_id],
			tapes.[usn]
		FROM
			[dbo].[Tape.tape_mediums] tapes

GO



PRINT N'Creating [dbo].[WmiServer.TombstonesView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.TombstonesView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.TombstonesView]	
GO
	CREATE VIEW [dbo].[WmiServer.TombstonesView]
	AS
		SELECT
			ts.[uid] as id,
			ts.[table_name],
			ts.[date],
			ts.[usn]
		FROM
			[dbo].[TombStones] ts

GO


PRINT N'Creating [dbo].[WmiServer.TapeStorageToTapeAssociationsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.TapeStorageToTapeAssociationsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.TapeStorageToTapeAssociationsView]	
GO
	CREATE VIEW [dbo].[WmiServer.TapeStorageToTapeAssociationsView]
	AS
		SELECT
			tm.id, 
			ts.tape_storage_id as storage_id
		FROM dbo.[Tape.files] tf
		JOIN dbo.[Tape.file_parts] fp on fp.[file_id] = tf.id
		JOIN dbo.[Tape.tape_mediums] tm on fp.media_sequence_number = tm.media_sequence_number
		JOIN dbo.[Tape.storages] ts on ts.file_id = tf.id
		WHERE ts.media_family_id = tm.media_family_id

GO


PRINT N'Creating [dbo].[WmiServer.TapeStoragesView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.TapeStoragesView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.TapeStoragesView]
GO
	CREATE VIEW [dbo].[WmiServer.TapeStoragesView]
	AS
		SELECT 
			bs.id, 
			bs.creation_time, 
			bs.file_path, 
			bs.backup_id, 
			bs.usn,
			ISNULL(bs.stats.value('(/CBackupStats/BackupSize)[1]', 'bigint'), 0) as size
		FROM dbo.[Backup.Model.Storages] bs
		JOIN dbo.[Tape.storages] ts on bs.id = ts.tape_storage_id

GO


PRINT N'Creating [dbo].[WmiServer.TapeBackupsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.TapeBackupsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.TapeBackupsView]
GO
	CREATE VIEW [dbo].[WmiServer.TapeBackupsView]
	AS
		SELECT 
			id, 
			job_name, 
			job_target_type, 
			usn
		FROM [dbo].[Backup.Model.Backups]
		WHERE job_target_type = 28 --VmTapeBackup
		--OR job_target_type = 24 --FileTapeBackup

GO

PRINT N'Creating [dbo].[WmiServer.TapeOibsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.TapeOibsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.TapeOibsView]
GO
	CREATE VIEW [dbo].[WmiServer.TapeOibsView]
	AS
		SELECT 
			vmRestorePoints.[id] as id,	
			vmRestorePoints.[vmname] as vm_name,
			vmRestorePoints.[display_name] as display_vm_name,
			vmRestorePoints.[storage_id] as storage_id,
			dbo.MaxValue(vmRestorePoints.[usn], bs.usn) as usn
		FROM [dbo].[Backup.Model.OIBs] vmRestorePoints
		JOIN dbo.[Backup.Model.Storages] bs on vmRestorePoints.[storage_id] = bs.[id]
		JOIN dbo.[Tape.storages] ts on bs.id = ts.tape_storage_id
		WHERE vmRestorePoints.[is_corrupted] = 0

GO

PRINT N'Creating [dbo].[WmiServer.JobSessionEventsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.JobSessionEventsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.JobSessionEventsView]
GO
	CREATE VIEW [dbo].[WmiServer.JobSessionEventsView]
	AS
		SELECT *
		FROM [dbo].[Events.JobSessionEvents]

GO

PRINT N'Creating [dbo].[WmiServer.TaskSessionEventsView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.TaskSessionEventsView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.TaskSessionEventsView]
GO
	CREATE VIEW [dbo].[WmiServer.TaskSessionEventsView]
	AS
		SELECT *
		FROM [dbo].[Events.TaskSessionEvents]

GO

PRINT N'Creating [dbo].[WmiServer.VmInVMwareReplicaView]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WmiServer.VmInVMwareReplicaView]') AND type in (N'V'))
	DROP VIEW [dbo].[WmiServer.VmInVMwareReplicaView]
GO
	CREATE VIEW [dbo].[WmiServer.VmInVMwareReplicaView]
	AS
		SELECT 
			oibs.id,
			oibs.object_id,
			b.id as backup_id,
			oibs.aux_data.value('(/COibAuxData/SnapReplicaAuxData/ReplVmName)[1]', 'nvarchar(255)') as repl_vm_name,
			oibs.aux_data.value('(/COibAuxData/SnapReplicaAuxData/ReplVmRef)[1]', 'nvarchar(255)') as repl_vm_ref,
			oibs.aux_data.value('(/COibAuxData/SnapReplicaAuxData/ReplSummary/Summary/ReplLocation)[1]', 'nvarchar(255)') as repl_location,
			dbo.MaxValue4(lov.usn, oibs.usn, p.usn, b.usn) as usn
		FROM [dbo].[WmiServer.LatestOibsView] lov
		JOIN [dbo].[Backup.Model.OIBs] oibs ON oibs.id = lov.id
		JOIN [dbo].[Backup.Model.Points] p ON oibs.point_id = p.id
		JOIN [dbo].[Backup.Model.Backups] b ON p.backup_id = b.id
		WHERE oibs.is_corrupted = 0 AND b.[job_target_type] = 1 AND b.[platform] = 0

GO
