--******************************************************************--
--* REST STORED PROCEDURES
--******************************************************************--
----------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.Reports.Summary.GetOverviewFrame]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Rest.Reports.Summary.GetOverviewFrame]
GO
CREATE PROCEDURE [dbo].[Rest.Reports.Summary.GetOverviewFrame]
AS
BEGIN
	SET NOCOUNT ON;
		CREATE TABLE #LatestSessions
	(	[id] uniqueidentifier,
		[job_id] uniqueidentifier,
		[state] int	
	)
	INSERT INTO #LatestSessions
	SELECT q.[id], q.[job_id], q.[state]
	FROM
		(SELECT 
			session.[id], session.[job_id], session.[state],
			ROW_NUMBER() OVER (PARTITION BY session.job_id ORDER BY session.[creation_time] DESC) as rn
		FROM 			
			[dbo].[C.Backup.Model.JobSessions] session
			INNER JOIN [dbo].[C.BJobs] jobs ON session.[job_id] = jobs.[id]
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON jobs.[db_instance_id] = bservers.[current_db_id]
		WHERE jobs.[type] IN (0,1)) as q --backup, replica
	WHERE q.rn = 1

	SELECT
		(SELECT count(id) FROM [dbo].[Repl.Topology.BackupDbInstances]) as backup_servers,
		(SELECT count(repo.id) FROM [dbo].[C.BackupRepositories] repo
			INNER JOIN [dbo].[Repl.Topology.BackupDbInstances] db ON repo.[db_instance_id] = db.[id]) as repositories,
		(SELECT count(proxy.id) FROM [dbo].[C.BackupProxies] proxy
			INNER JOIN [dbo].[Repl.Topology.BackupDbInstances] db ON proxy.[db_instance_id] = db.[id]) as proxies,
		(SELECT count(id) FROM #LatestSessions sessions WHERE sessions.[state] IN (3, 5)) as running_jobs, --starting, running
		(SELECT count(jobs.id) FROM [dbo].[C.BJobs] jobs
			INNER JOIN [dbo].[Repl.Topology.BackupDbInstances] db ON jobs.[db_instance_id] = db.[id]
		 WHERE 
			jobs.[schedule_enabled] = 1 AND 
			ISNULL(jobs.options.value('(/*/RunManually)[1]', 'bit'), 1) = 0 AND 
			jobs.[type] IN (0,1)
		) as scheduled_jobs,
		(SELECT count(task_sessions.id) FROM #LatestSessions latest_sessions
			INNER JOIN [dbo].[C.Backup.Model.BackupTaskSessions] task_sessions ON latest_sessions.[id] = task_sessions.[session_id]
			WHERE task_sessions.[status] = 0) as vms_success, -- success
		(SELECT count(task_sessions.id) FROM #LatestSessions latest_sessions
			INNER JOIN [dbo].[C.Backup.Model.BackupTaskSessions] task_sessions ON latest_sessions.[id] = task_sessions.[session_id]
			WHERE task_sessions.[status] = 3) as vms_warning, -- warning
		(SELECT count(task_sessions.id) FROM #LatestSessions latest_sessions
			INNER JOIN [dbo].[C.Backup.Model.BackupTaskSessions] task_sessions ON latest_sessions.[id] = task_sessions.[session_id]
			WHERE task_sessions.[status] = 2) as vms_error -- error
	DROP TABLE #LatestSessions

END  
GO



-----------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.Reports.LatestOibsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.Reports.LatestOibsView]
GO
	CREATE VIEW [dbo].[Rest.Reports.LatestOibsView]
	AS
		SELECT q.id, q.creation_time, q.backup_id, q.object_id
		FROM
	    (
	    SELECT 
			oibs.id,
			oibs.creation_time,
			oibs.object_id,
			--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].[C.Backup.Model.OIBs] oibs 
			INNER JOIN [dbo].[C.Backup.Model.Points] pnts ON pnts.id = oibs.point_id
			INNER JOIN [dbo].[C.Backup.Model.Backups] backups ON pnts.backup_id = backups.id
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON backups.[db_instance_id] = bservers.[current_db_id]
		 WHERE 
			backups.job_target_type IN (0,1) AND
			oibs.is_corrupted = 0
		) q
		WHERE 
			q.rn = 1

GO


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Rest.Reports.BackupInfoForPeriod]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[fn.Rest.Reports.BackupInfoForPeriod]
GO
	CREATE FUNCTION [dbo].[fn.Rest.Reports.BackupInfoForPeriod]
	(
		@days int
	)
	RETURNS 
		@Result TABLE 
		(
			[id] uniqueidentifier,
			[job_name] nvarchar(256),
			[is_replica] bit,
			[full_storages_size] bigint,
			[incremental_storages_size] bigint
		)
	AS
	BEGIN
	
		DECLARE @utc_time_current datetime, @utc_time_from datetime
		SET @utc_time_current = GETUTCDATE()
		SET @utc_time_from = DATEADD(day, -@days, @utc_time_current)

		INSERT INTO @Result 
		SELECT
			b.[id],
			b.[job_name],
			CONVERT(BIT, CASE WHEN b.[job_target_type] = 1 THEN 1 ELSE 0 END) as is_replica,
			ISNULL(CASE 
				WHEN b.[job_target_type] = 0 THEN sss.[full_storages_size]
				ELSE 0
			END, 0) as full_storages_size,
			ISNULL(CASE 
				WHEN b.[job_target_type] = 0 THEN sss.[incremental_storages_size]
				ELSE 0
			END, 0) as incremental_storages_size
			--,sss.[total_storages_size]
		FROM
			[dbo].[C.Backup.Model.Backups] b			
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON b.[db_instance_id] = bservers.[current_db_id]
			INNER JOIN (
				SELECT
					ss.[backup_id],
					SUM((1 - ss.[is_incremental]) * ss.[ssize]) AS full_storages_size,
					SUM(ss.[is_incremental] * ss.[ssize]) AS incremental_storages_size
					--SUM(ss.[ssize]) AS total_storages_size					
				FROM
					(
						SELECT
							s.[backup_id],
							ISNULL(s.stats.value('(/stats/CBackupStats/BackupSize)[1]', 'bigint'), 0) as ssize,
							CASE WHEN PATINDEX('%.vbk', s.file_path) > 0 THEN 0 ELSE 1 END AS is_incremental							
						FROM
							[dbo].[C.Backup.Model.Storages] s
							INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON s.[db_instance_id] = bservers.[current_db_id]
						WHERE
							s.[creation_time] > @utc_time_from
					) ss
				GROUP BY [backup_id]
			) sss ON sss.[backup_id] = b.[id]
		WHERE
			b.job_target_type IN (0,1)
			
	RETURN
	END
GO



IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.Reports.Summary.GetVmsOverviewFrame]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Rest.Reports.Summary.GetVmsOverviewFrame]
GO
CREATE PROCEDURE [dbo].[Rest.Reports.Summary.GetVmsOverviewFrame]
	@days int
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @utc_time_current datetime, @utc_time_from datetime
	SET @utc_time_current = GETUTCDATE()
	SET @utc_time_from = DATEADD(day, -@days, @utc_time_current)
	CREATE TABLE #VmRestorePoints
	(
		[vm_obj_id] uniqueidentifier,
		[oib_id] uniqueidentifier,
		[oib_creation_time] datetime,
		[storage_id] uniqueidentifier,
		[job_target_type] int,
		[backup_id] uniqueidentifier,
		[oib_type] int,
		[oib_approx_size] bigint,
		is_corrupted bit
	)
	INSERT INTO #VmRestorePoints
	SELECT 
		oibs.[object_id] as vm_obj_id, 
		oibs.[id] as oib_id,
		oibs.[creation_time] as oib_creation_time,
		oibs.[storage_id] as storage_id,
		backups.[job_target_type] as job_target_type,
		backups.[id] as backup_id,
		oibs.[type] as oib_type,
		oibs.[approx_size] as oib_approx_size,
		oibs.[is_corrupted]
	FROM 
		[dbo].[C.Backup.Model.OIBs] oibs
		INNER JOIN [dbo].[C.Backup.Model.Points] points ON oibs.[point_id] = points.[id]
		INNER JOIN [dbo].[C.Backup.Model.Backups] backups ON points.[backup_id] = backups.[id]
		INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON backups.[db_instance_id] = bservers.[current_db_id]
	WHERE 
		--oibs.[is_corrupted] = 0 AND 
		oibs.[creation_time] > @utc_time_from AND
		backups.[job_target_type] IN (0, 1)


	DECLARE @expected_backedup_vms int
	DECLARE @expected_replicated_vms int
	DECLARE @total_last_oibs int
	SELECT @expected_backedup_vms = ISNULL(COUNT(vm_obj_id), 0)
	FROM 
		(SELECT DISTINCT vms.[vm_obj_id] FROM #VmRestorePoints vms WHERE vms.[job_target_type] = 0) as q	
	SELECT @expected_replicated_vms = ISNULL(COUNT(vm_obj_id), 0)
	FROM 
		( SELECT DISTINCT vms.[vm_obj_id] FROM #VmRestorePoints vms WHERE vms.[job_target_type] = 1) as q

	SELECT @total_last_oibs = ISNULL(COUNT(vms.vm_obj_id), 0)
	FROM #VmRestorePoints vms
		INNER JOIN [dbo].[Rest.Reports.LatestOibsView] last_oibs ON vms.[oib_id] = last_oibs.[id]	
	SELECT 
		(
			SELECT ISNULL(COUNT(vm_obj_id), 0)
			FROM 
				(SELECT DISTINCT vms.[vm_obj_id] FROM #VmRestorePoints vms WHERE vms.[is_corrupted] = 0 AND vms.[job_target_type] = 0) as q	
		)as backedup_vms,
		(
			SELECT ISNULL(COUNT(vm_obj_id), 0)
			FROM 
				(SELECT DISTINCT vms.[vm_obj_id] FROM #VmRestorePoints vms WHERE vms.[is_corrupted] = 0 AND vms.[job_target_type] = 1) as q
		) as replicated_vms,
		(
			SELECT ISNULL(COUNT(*), 0)
			FROM
			(SELECT DISTINCT vms.[vm_obj_id] FROM #VmRestorePoints vms WHERE vms.[is_corrupted] = 0) as q
		) as protected_vms,
		(
			SELECT ISNULL(SUM(backups.[full_storages_size]), 0) FROM [dbo].[fn.Rest.Reports.BackupInfoForPeriod](@days) backups
			WHERE backups.[is_replica] = 0
		) as full_backup_points_size,
		(			
			SELECT ISNULL(SUM(backups.[incremental_storages_size]), 0) FROM [dbo].[fn.Rest.Reports.BackupInfoForPeriod](@days) backups
			WHERE backups.[is_replica] = 0
		) as incr_backup_points_size,
		(
			SELECT ISNULL(SUM(stg.[full_size]), 0) FROM #VmRestorePoints vms
			INNER JOIN [dbo].[C.Backup.Model.Storages] stg ON vms.[storage_id] = stg.[id]
			WHERE 
				vms.[is_corrupted] = 0 AND
				job_target_type = 1 AND -- replica
				vms.[oib_type] IN (0,1,4) --full, rollback, snapshot
		) as replica_restore_points_size,
		(
			SELECT ISNULL(COUNT(*), 0) FROM #VmRestorePoints vms WHERE vms.[is_corrupted] = 0
		) as restore_points,
		(
			SELECT ISNULL(SUM(q.[oib_approx_size]), 0)
			FROM 
			(
				SELECT 
					vms.[oib_approx_size],
					ROW_NUMBER() OVER (PARTITION BY vms.vm_obj_id ORDER BY vms.[oib_creation_time] DESC) as rn
				FROM #VmRestorePoints vms
				WHERE vms.[is_corrupted] = 0
			) as q
			WHERE q.rn = 1
		) as source_vms_size,
		(
			SELECT 
				CASE @expected_backedup_vms + @expected_replicated_vms
					WHEN 0 THEN 0
					ELSE @total_last_oibs / (CAST(@expected_backedup_vms + @expected_replicated_vms as real))
				END
		) as success_backup_ratio
	DROP TABLE #VmRestorePoints
END
GO





IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.Reports.Summary.GetJobStatsFrame]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Rest.Reports.Summary.GetJobStatsFrame]
GO
CREATE PROCEDURE [dbo].[Rest.Reports.Summary.GetJobStatsFrame]
	@days int
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @utc_time_current datetime, @utc_time_from datetime
	SET @utc_time_current = GETUTCDATE()
	SET @utc_time_from = DATEADD(day, -@days, @utc_time_current)
	CREATE TABLE #LatestSessions
	(	[id] uniqueidentifier,
		[job_id] uniqueidentifier,
		[state] int	
	)
	INSERT INTO #LatestSessions
	SELECT q.[id], q.[job_id], q.[state]
	FROM
		(SELECT 
			session.[id], session.[job_id], session.[state],
			ROW_NUMBER() OVER (PARTITION BY session.job_id ORDER BY session.[creation_time] DESC) as rn
		FROM 
			[dbo].[C.Backup.Model.JobSessions] session
			INNER JOIN [dbo].[C.BJobs] jobs ON session.[job_id] = jobs.[id]
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON jobs.[db_instance_id] = bservers.[current_db_id]
		WHERE 
			jobs.[type] IN (0,1) AND --backup, replica
			session.[creation_time] > @utc_time_from
		) as q
	WHERE q.rn = 1
	
	DECLARE @max_backup_job_duration_mins int
	DECLARE @max_duration_backup_job_name nvarchar(1024)
	DECLARE @max_replica_job_duration_mins int
	DECLARE @max_duration_replica_job_name nvarchar(1024)
	


	SELECT TOP(1)
		@max_backup_job_duration_mins = q.duration,
		@max_duration_backup_job_name = q.job_name
	FROM
		(SELECT
					MAX(
						DATEDIFF(mi, sessions.[creation_time], 
						CASE 
							WHEN sessions.[end_time] < sessions.[creation_time] THEN @utc_time_current
							ELSE sessions.[end_time]
						END
						)
					) as duration,
					sessions.[job_name] as job_name
		FROM 
			[dbo].[Repl.Topology.BackupDbInstances] db
			INNER JOIN [dbo].[view.Backup.BackupJobSessions] sessions ON db.[id] = sessions.[db_instance_id]
		WHERE 
			sessions.[job_type] = 0 AND --backup
			sessions.[creation_time] > @utc_time_from
		GROUP BY sessions.[job_name]) as q
	ORDER BY q.duration DESC
	

	SELECT TOP(1)
		@max_replica_job_duration_mins = q.duration,
		@max_duration_replica_job_name = q.job_name
	FROM
		(SELECT
					MAX(
						DATEDIFF(mi, sessions.[creation_time], 
						CASE 
							WHEN sessions.[end_time] < sessions.[creation_time] THEN @utc_time_current
							ELSE sessions.[end_time]
						END
						)
					) as duration,
					sessions.[job_name] as job_name
		FROM 
			[dbo].[Repl.Topology.BackupDbInstances] db
			INNER JOIN [dbo].[view.Backup.BackupJobSessions] sessions ON db.[id] = sessions.[db_instance_id]
		WHERE 
			sessions.[job_type] = 1 AND --replica
			sessions.[creation_time] > @utc_time_from
		GROUP BY sessions.[job_name]) as q
	ORDER BY q.duration DESC

	
	SELECT 
		(
			SELECT COUNT(id) FROM #LatestSessions sessions WHERE sessions.[state] IN (3, 5)--starting, running
		) as running_jobs, 
		(
			SELECT COUNT(jobs.id) 
			FROM [dbo].[C.BJobs] jobs 
				INNER JOIN [dbo].[Repl.Topology.BackupDbInstances] db ON jobs.[db_instance_id] = db.[id]
			WHERE 
				jobs.[schedule_enabled] = 1 AND 
				ISNULL(jobs.options.value('(/*/RunManually)[1]', 'bit'), 1) = 0 AND
				jobs.[type] = 0
		) as backup_scheduled_jobs,
		(
			SELECT COUNT(jobs.id) 
			FROM [dbo].[C.BJobs] jobs 
				INNER JOIN [dbo].[Repl.Topology.BackupDbInstances] db ON jobs.[db_instance_id] = db.[id]
			WHERE 
				jobs.[schedule_enabled] = 1 AND 
				ISNULL(jobs.options.value('(/*/RunManually)[1]', 'bit'), 1) = 0 AND
				jobs.[type] = 1
		) as replica_scheduled_jobs,
		(
			SELECT COUNT(jobs.[id]) 
			FROM [dbo].[Repl.Topology.BackupServers]
				INNER JOIN [dbo].[C.BJobs] jobs ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = jobs.[db_instance_id]
				INNER JOIN [dbo].[view.Backup.BackupJobSessions] ON jobs.[id] = [dbo].[view.Backup.BackupJobSessions].[job_id]
			WHERE 
				jobs.[type] IN (0,1) AND
				[dbo].[view.Backup.BackupJobSessions].[end_time] > @utc_time_from
		) as total_job_runs,
		(
			SELECT COUNT(jobs.[id]) 
			FROM [dbo].[Repl.Topology.BackupServers]
				INNER JOIN [dbo].[C.BJobs] jobs ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = jobs.[db_instance_id]
				INNER JOIN [dbo].[view.Backup.BackupJobSessions] ON jobs.[id] = [dbo].[view.Backup.BackupJobSessions].[job_id]
			WHERE
				jobs.[type] IN (0,1) AND
				[dbo].[view.Backup.BackupJobSessions].[result] = 0 AND 
				[dbo].[view.Backup.BackupJobSessions].[end_time] > @utc_time_from
		) as job_successes,
		(
			SELECT COUNT(jobs.[id]) 
			FROM [dbo].[Repl.Topology.BackupServers]
				INNER JOIN [dbo].[C.BJobs] jobs ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = jobs.[db_instance_id]
				INNER JOIN [dbo].[view.Backup.BackupJobSessions] ON jobs.[id] = [dbo].[view.Backup.BackupJobSessions].[job_id]
			WHERE
				jobs.[type] IN (0,1) AND
				[dbo].[view.Backup.BackupJobSessions].[result] = 1 AND 
				[dbo].[view.Backup.BackupJobSessions].[end_time] > @utc_time_from
		) as job_warnings,
		(
			SELECT COUNT(jobs.[id]) 
			FROM [dbo].[Repl.Topology.BackupServers]
				INNER JOIN [dbo].[C.BJobs] jobs ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = jobs.[db_instance_id]
				INNER JOIN [dbo].[view.Backup.BackupJobSessions] ON jobs.[id] = [dbo].[view.Backup.BackupJobSessions].[job_id]
			WHERE
				jobs.[type] IN (0,1) AND
				[dbo].[view.Backup.BackupJobSessions].[result] = 2 AND 
				[dbo].[view.Backup.BackupJobSessions].[end_time] > @utc_time_from
		) as job_errors,
		ISNULL(@max_backup_job_duration_mins, 0) as max_backup_job_duration,
		ISNULL(@max_duration_backup_job_name, N'') as max_duration_backup_job_name,
		ISNULL(@max_replica_job_duration_mins, 0) as max_replica_job_duration,
		ISNULL(@max_duration_replica_job_name, N'') as max_duration_replica_job_name				
END  
GO




IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.Reports.Summary.GetProcessedVmsFrame]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Rest.Reports.Summary.GetProcessedVmsFrame]
GO
CREATE PROCEDURE [dbo].[Rest.Reports.Summary.GetProcessedVmsFrame]
	@days int
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @utc_time_current datetime, @utc_time_from datetime
	SET @utc_time_current = GETUTCDATE()
	SET @utc_time_from = DATEADD(day, -@days, @utc_time_current)
	
	CREATE TABLE #SuccessVmRestorePoints
	(
		[vm_obj_id] uniqueidentifier,
		[oib_id] uniqueidentifier,
		[oib_creation_time] datetime,
		[job_target_type] int,
		[backup_id] uniqueidentifier,
		[oib_type] int,
		[oib_approx_size] bigint,
		[is_corrupted] bit
	)

	INSERT INTO #SuccessVmRestorePoints
	SELECT 
		oibs.[object_id] as vm_obj_id, 
		oibs.[id] as oib_id,
		oibs.[creation_time] as oib_creation_time,
		backups.[job_target_type] as job_target_type,
		backups.[id] as backup_id,
		oibs.[type] as oib_type,
		oibs.[approx_size] as oib_approx_size,
		oibs.[is_corrupted]		
	FROM 
		[dbo].[Repl.Topology.BackupDbInstances] db
		INNER JOIN [dbo].[C.Backup.Model.OIBs] oibs ON db.[id] = oibs.[db_instance_id]		
		INNER JOIN [dbo].[C.Backup.Model.Points] points ON oibs.[point_id] = points.[id]
		INNER JOIN [dbo].[C.Backup.Model.Backups] backups ON points.[backup_id] = backups.[id]
	WHERE 
		oibs.[is_corrupted] = 0 AND 
		oibs.[creation_time] > @utc_time_from AND
		backups.[job_target_type] IN (0, 1)

	SELECT 
		vms.[oib_id] as id, 
		vms.[oib_creation_time] as creation_time, 
		vms.[job_target_type] as job_target_type,
		DATEDIFF(dd, vms.[oib_creation_time], @utc_time_current) as days_in_depth
	FROM #SuccessVmRestorePoints vms	
	ORDER BY vms.[oib_creation_time] DESC
	
END  
GO




IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.Reports.Summary.GetRepositoriesFrame]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Rest.Reports.Summary.GetRepositoriesFrame]
GO
CREATE PROCEDURE [dbo].[Rest.Reports.Summary.GetRepositoriesFrame]	
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		repo.[name],
		repo.[total_space],
		repo.[free_space],
		repo.[total_space] - repo.[free_space] as used_space
	FROM [dbo].[C.BackupRepositories] repo
		INNER JOIN [dbo].[Repl.Topology.BackupDbInstances] db ON repo.[db_instance_id] = db.[id]
	END  
GO



IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.GetJobItems]') AND type in (N'P'))
DROP PROCEDURE [dbo].[Rest.GetJobItems]
GO
CREATE PROCEDURE [dbo].[Rest.GetJobItems]
	@job_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
		SELECT
		    [dbo].[C.ObjectsInJobs].[id] AS [oij_id],
		    [dbo].[C.ObjectsInJobs].[job_id] AS [oij_job_id],
		    [dbo].[C.ObjectsInJobs].[folder_id] AS [oij_folder_id],
		    [dbo].[C.ObjectsInJobs].[vss_options] AS [oij_vss_options],
		    [dbo].[C.ObjectsInJobs].[location] AS [oij_location],
		    [dbo].[C.ObjectsInJobs].[type] AS [oij_type],
            [dbo].[C.BObjects].[platform],
		    [dbo].[C.BObjects].[id],
		    [dbo].[C.BObjects].[type],
		    [dbo].[C.BObjects].[host_id],
		    [dbo].[C.BObjects].[object_name],
		    [dbo].[C.BObjects].[object_id],
		    [dbo].[C.BObjects].[viobject_type],
		    [dbo].[C.BObjects].[guest_os],
		    [dbo].[C.BObjects].[path],		                    
		    [dbo].[C.BObjects].[uuid],
            [dbo].[C.BObjects].[display_name]
	    FROM
		    [dbo].[C.ObjectsInJobs],
		    [dbo].[C.BObjects]
	    WHERE
		    [dbo].[C.ObjectsInJobs].[object_id] = [dbo].[C.BObjects].[id] AND
		    [dbo].[C.ObjectsInJobs].[db_instance_id] = [dbo].[C.BObjects].[db_instance_id] AND
		    [dbo].[C.ObjectsInJobs].[job_id] = @job_id
END
GO


--******************************************************************--
--* REST API VIEWS
--******************************************************************--
----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.RootHostsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.RootHostsView]
GO
	CREATE VIEW [dbo].[Rest.RootHostsView] 
	AS
		SELECT 
			h.*,			
			bservers.[id] as parent_bserver_id,
			bservers.[ip_or_dns_name] as parent_bserver_name
		FROM [dbo].[C.Hosts] h 
		INNER JOIN [dbo].[Repl.Topology.BackupDbInstances] db ON h.[db_instance_id] = db.[id]		
		INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON h.[db_instance_id] = bservers.[current_db_id]
		WHERE 	
			bservers.[major_version] >= 7 AND
			h.[type] in (0, 1, 6, 7, 8, 9, 14)
			AND	h.[parent_id] = '00000000-0000-0000-0000-000000000000' AND 
			[dbo].[fn.IsRootVisibleHost](h.[id]) = 'True'
GO


--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.JobsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.JobsView]
GO
	CREATE VIEW [dbo].[Rest.JobsView]
	AS
		SELECT 
			jobs.[id] as id
			,jobs.[type] as type
			,jobs.[name] as name
			,jobs.[description] as description
			,~ISNULL(jobs.options.value('(/*/RunManually)[1]', 'bit'), 1) as is_schedule_configured
			,jobs.[schedule] as schedule_options
			,jobs.[platform] as platform
			,jobs.[schedule_enabled] as is_schedule_enabled
			,jobs.[next_run_time] as next_run
			
			,jobs.[target_host_id]
			,jobs.[target_dir]
			,jobs.[target_file]
			,jobs.[options]			
			,jobs.[is_deleted]
			,jobs.[latest_result]
			,jobs.[post_command_run_count]
			,jobs.[vss_options]
			,jobs.[vcb_host_id]
			,jobs.[target_type]
			,jobs.[parent_schedule_id]			
			--,[job_source_type]
			--,[included_size]
			--,[excluded_size]			
			--,[source_proxy_host_id]
			--,[target_proxy_host_id]
			--,[repository_id]
			--,[initialsync_repository_id]			
			,bservers.[id] as parent_bserver_id
			,bservers.[ip_or_dns_name] as parent_bserver_name
		FROM 
			[dbo].[C.BJobs] jobs 
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON jobs.[db_instance_id] = bservers.[current_db_id]
		WHERE 
			bservers.[major_version] >= 7 AND
			jobs.[type] IN (0,1) -- backup, replica, copy
GO



--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.GetRepositoryUniqueIdByIdOrEmptyId]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.GetRepositoryUniqueIdByIdOrEmptyId]
GO
CREATE FUNCTION [dbo].[fn.GetRepositoryUniqueIdByIdOrEmptyId]
(
	@db_instance_id uniqueidentifier,
	@repo_id_or_null uniqueidentifier
)
RETURNS uniqueidentifier
AS
BEGIN
	
	declare @repo_unqiue_id uniqueidentifier
	
	SELECT @repo_unqiue_id = repo.unique_id FROM [dbo].[C.BackupRepositories] repo 
	WHERE repo.[db_instance_id] = @db_instance_id AND 
	repo.id = 
		CASE
		 WHEN @repo_id_or_null = '00000000-0000-0000-0000-000000000000' THEN '88788F9E-D8F5-4eb4-BC4F-9B3F5403BCEC'
		 ELSE @repo_id_or_null
		END
	
	RETURN @repo_unqiue_id
END
GO


--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.BackupsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.BackupsView]
GO
	CREATE VIEW [dbo].[Rest.BackupsView] 
	AS
		SELECT
			q.[id],
			q.[name],
			q.[job_id],
			q.[job_name],
			q.[backupserver_id],
			q.[backupinstance_id],
			q.[backupserver_name],
			repo.[unique_id] as repository_id,
			repo.[name] as repository_name			
		FROM 
		(
		SELECT 
			backups.[id] as id,
			backups.[job_name] as name,
			backups.[job_id] as job_id,
			backups.[job_name] as job_name,			
			bservers.[id] as backupserver_id,
			bservers.[current_db_id] as backupinstance_id,
			bservers.[ip_or_dns_name] as backupserver_name,
			[dbo].[fn.GetRepositoryUniqueIdByIdOrEmptyId](backups.[db_instance_id], backups.[repository_id]) repo_unique_id
		FROM 
			[dbo].[C.Backup.Model.Backups] backups			
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON backups.[db_instance_id] = bservers.[current_db_id]
		WHERE 
			backups.[job_target_type] = 0
		) q
		INNER JOIN [dbo].[C.BackupRepositories] repo ON q.repo_unique_id = repo.[unique_id]
		--WHERE bservers.[major_version] >= 7
GO

--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.ReplicasView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.ReplicasView]
GO
	CREATE VIEW [dbo].[Rest.ReplicasView] 
	AS
		SELECT
			q.[id],
			q.[name],
			q.[job_id],
			q.[job_name],
			q.[backupserver_id],
			q.[backupinstance_id],
			q.[backupserver_name],
			repo.[unique_id] as repository_id,
			repo.[name] as repository_name			
		FROM 
		(
		SELECT 
			backups.[id] as id,
			backups.[job_name] as name,
			backups.[job_id] as job_id,
			backups.[job_name] as job_name,			
			bservers.[id] as backupserver_id,
			bservers.[current_db_id] as backupinstance_id,
			bservers.[ip_or_dns_name] as backupserver_name,
			[dbo].[fn.GetRepositoryUniqueIdByIdOrEmptyId](backups.[db_instance_id], backups.[repository_id]) repo_unique_id
		FROM 
			[dbo].[C.Backup.Model.Backups] backups			
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON backups.[db_instance_id] = bservers.[current_db_id]
		WHERE 
			backups.[job_target_type] = 1
		) q
		INNER JOIN [dbo].[C.BackupRepositories] repo ON q.repo_unique_id = repo.[unique_id]
		--WHERE bservers.[major_version] >= 7
GO

--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.RestorePointsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.RestorePointsView]
GO
	CREATE VIEW [dbo].[Rest.RestorePointsView] 
	AS
		SELECT 
			points.[id] as id,
			CAST(points.[creation_time] as nvarchar(256)) as name,
			points.[creation_time] as point_creation_time,
			backups.[id] as backup_id,			
			backups.[job_name] as backup_name,
			bservers.[id] as backupserver_id,
			bservers.[current_db_id] as backupinstance_id,
			bservers.[ip_or_dns_name] as backupserver_name
		FROM 
			[dbo].[C.Backup.Model.Points] points
			INNER JOIN [dbo].[C.Backup.Model.Backups] backups ON points.[backup_id] = backups.[id]
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON backups.[db_instance_id] = bservers.[current_db_id]
		WHERE 
			backups.[job_target_type] = 0
			AND bservers.[major_version] >= 7
GO

--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.VmRestorePointsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.VmRestorePointsView]
GO
	CREATE VIEW [dbo].[Rest.VmRestorePointsView]
	AS
		SELECT 
			oibs.[id],
			oibs.[object_id],
			oibs.[vmname] + '@' + CONVERT(nvarchar(50), oibs.creation_time, 120) as name,
			oibs.[vmname] as vm_real_name,
			oibs.[display_name] as vm_display_name,
			oibs.[creation_time] as creation_time,
			oibs.[type] as type,
			oibs.[alg] as algorithm,
			oibs.[has_index] as has_index,
			oibs.[original_oib_id] as original_oib_id,
			points.[id] as point_id,
			CAST(points.[creation_time] as nvarchar(256)) as point_name,
			bservers.[id] as backupserver_id,
			bservers.[current_db_id] as backupinstance_id,
			bservers.[ip_or_dns_name] as backupserver_name
		FROM 
			[dbo].[C.Backup.Model.OIBs] oibs
			INNER JOIN [dbo].[C.Backup.Model.Points] points ON oibs.[point_id] = points.[id]
			INNER JOIN [dbo].[C.Backup.Model.Backups] backups ON points.[backup_id] = backups.[id]
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON points.[db_instance_id] = bservers.[current_db_id]
		WHERE 
			backups.[job_target_type] = 0 AND
			bservers.[major_version] >= 7 AND 
			oibs.is_corrupted = 0
GO

--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.VmReplicaPointsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.VmReplicaPointsView]
GO
	CREATE VIEW [dbo].[Rest.VmReplicaPointsView]
	AS
		SELECT 
			oibs.[id],
			oibs.[object_id],
			oibs.[vmname] + '@' + CONVERT(nvarchar(50), oibs.creation_time, 120) as name,
			oibs.[vmname] as vm_real_name,
			oibs.[display_name] as vm_display_name,
			oibs.[creation_time] as creation_time,
			oibs.[type] as type,
			oibs.[alg] as algorithm,
			oibs.[has_index] as has_index,
			oibs.[original_oib_id] as original_oib_id,
			backups.[id] as backup_id,			
			backups.[job_name] as backup_name,		
			bservers.[id] as backupserver_id,
			bservers.[current_db_id] as backupinstance_id,
			bservers.[ip_or_dns_name] as backupserver_name
		FROM 
			[dbo].[C.Backup.Model.OIBs] oibs
			INNER JOIN [dbo].[C.Backup.Model.Points] points ON oibs.[point_id] = points.[id]
			INNER JOIN [dbo].[C.Backup.Model.Backups] backups ON points.[backup_id] = backups.[id]
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON points.[db_instance_id] = bservers.[current_db_id]
		WHERE 
			backups.[job_target_type] = 1 AND
			bservers.[major_version] >= 7 AND 
			oibs.is_corrupted = 0
GO

--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.RepositoriesView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.RepositoriesView]
GO
	CREATE VIEW [dbo].[Rest.RepositoriesView]
	AS
		SELECT
			r.unique_id as id,
			r.name as name,
			r.total_space,
			r.free_space,			
			bservers.[id] as parent_bserver_id,
			bservers.[ip_or_dns_name] as parent_bserver_name	
		FROM
			[dbo].[C.BackupRepositories] r 			
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON r.[db_instance_id] = bservers.[current_db_id]
		WHERE bservers.[major_version] >= 7
GO

--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.BackupJobSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.BackupJobSessionsView]
GO
	CREATE VIEW [dbo].[Rest.BackupJobSessionsView]
	AS
		SELECT 
			js.id,
			js.job_name + '@' + CONVERT(nvarchar(50), js.creation_time, 120) AS name,
			js.job_type,
			js.job_id,
			js.job_name,
			js.creation_time,
			js.end_time,
			js.state,
			js.result,
			bjs.is_retry,
			bjs.cur_point_id,
			CAST(pt.[creation_time] as nvarchar(256)) as cur_point_name,
			js.reason as failure_message,
			bs.id AS backupserver_id,
			bs.ip_or_dns_name AS backupserver_name
		FROM 
			[dbo].[C.Backup.Model.BackupJobSessions] bjs
			INNER JOIN [dbo].[C.Backup.Model.JobSessions] js ON bjs.id = js.id
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bs ON bjs.db_instance_id = bs.current_db_id
			LEFT JOIN [dbo].[C.Backup.Model.Points] pt ON pt.id = bjs.cur_point_id
		WHERE 
			bs.[major_version] >= 7 AND
			js.creation_time >= DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()) - 14) AND
			js.job_type = 0
			
GO
--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.ReplicaJobSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.ReplicaJobSessionsView]
GO
	CREATE VIEW [dbo].[Rest.ReplicaJobSessionsView]
	AS
		SELECT 
			js.id,
			js.job_name + '@' + CONVERT(nvarchar(50), js.creation_time, 120) AS name,
			js.job_type,
			js.job_id,
			js.job_name,
			js.creation_time,
			js.end_time,
			js.state,
			js.result,
			bjs.is_retry,
			js.reason as failure_message,
			bs.id AS backupserver_id,
			bs.ip_or_dns_name AS backupserver_name
		FROM 
			[dbo].[C.Backup.Model.BackupJobSessions] bjs
			INNER JOIN [dbo].[C.Backup.Model.JobSessions] js ON bjs.id = js.id
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bs ON bjs.db_instance_id = bs.current_db_id
		WHERE 
			bs.[major_version] >= 7 AND
			js.creation_time >= DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()) - 14) AND
			js.job_type = 1

GO
------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.TaskSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.TaskSessionsView]
GO
	CREATE VIEW [dbo].[Rest.TaskSessionsView]
	AS
		SELECT 
			ts.id,
			ts.object_name + '@' + CONVERT(nvarchar(50), ts.creation_time, 120) AS name,
			ts.creation_time,
			ts.end_time,
			ts.status,
			ts.object_name as vm_display_name,
			CAST(ts.reason as nvarchar(max)) as reason,
			ts.total_size,
			js.id as job_id,
			js.job_name + ', ' + CAST(js.creation_time as nvarchar(50)) AS job_name,
			bs.id AS backupserver_id,
			bs.ip_or_dns_name AS backupserver_name
		FROM 
			[dbo].[C.Backup.Model.BackupTaskSessions] ts
			JOIN [dbo].[C.Backup.Model.JobSessions] js ON ts.session_id = js.id
			JOIN [dbo].[Repl.Topology.BackupServers] bs ON js.db_instance_id = bs.current_db_id 
		WHERE 
			bs.[major_version] >= 7 AND
			ts.creation_time >= DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()) - 14) AND
			js.job_type = 0
GO
------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.ReplicaTaskSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.ReplicaTaskSessionsView]
GO
	CREATE VIEW [dbo].[Rest.ReplicaTaskSessionsView]
	AS
		SELECT 
			ts.id,
			ts.object_name + '@' + CONVERT(nvarchar(50), ts.creation_time, 120) AS name,
			ts.creation_time,
			ts.end_time,
			ts.status,
			ts.object_name as vm_display_name,
			CAST(ts.reason as nvarchar(max)) as reason,
			ts.total_size,
			js.id as job_id,
			js.job_name + ', ' + CAST(js.creation_time as nvarchar(50)) AS job_name,
			bs.id AS backupserver_id,
			bs.ip_or_dns_name AS backupserver_name
		FROM 
			[dbo].[C.Backup.Model.BackupTaskSessions] ts
			JOIN [dbo].[C.Backup.Model.JobSessions] js ON ts.session_id = js.id
			JOIN [dbo].[Repl.Topology.BackupServers] bs ON js.db_instance_id = bs.current_db_id 
		WHERE 
			bs.[major_version] >= 7 AND
			ts.creation_time >= DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()) - 14) AND
			js.job_type = 1
GO
--------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.BackupServersView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.BackupServersView]
GO
	CREATE VIEW [dbo].[Rest.BackupServersView]
	AS
		SELECT 
			bs.id,
			bs.display_name AS name,
			bs.ip_or_dns_name,
			bs.description,
			bs.current_db_id,
			bs.port,
			bs.version,
			name_in_catalog  
		FROM 
			[dbo].[Repl.Topology.BackupServers] bs
			LEFT OUTER JOIN [Repl.Topology.BackupServerCreds] bsc ON  bs.id = bsc.backup_server
		WHERE bs.[major_version] >= 7
GO

--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.UserVmRestorePointsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.UserVmRestorePointsView]
GO
	CREATE VIEW [dbo].[Rest.UserVmRestorePointsView]
	AS
		SELECT 
			vrp.*,
			t.sid as userSid
		FROM [dbo].[Rest.VmRestorePointsView] vrp
		JOIN 
		(
			SELECT DISTINCT lu.sid_string as sid, bo.id 
			FROM [dbo].[C.BObjects] bo 
			JOIN [dbo].[Security.VmsRecursiveInclusion] vri on vri.vm_key_hash = bo.unique_key_hash
			JOIN [dbo].[Security.HierarchyScopes] hs on hs.hierarchy_object_keyhash = vri.container_key_hash
			JOIN [dbo].[Security.RoleAccounts] ra on ra.role_group_id = hs.role_account_group_id
			JOIN [dbo].[Security.LoggedOnUserAccounts] lua ON (lua.account_id = ra.account_id)		
			JOIN [dbo].[Security.LoggedOnUsers] lu ON (lu.id = lua.login_id)
		) as t on t.id = vrp.object_id		
GO

--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.UserVmReplicaPointsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.UserVmReplicaPointsView]
GO
	CREATE VIEW [dbo].[Rest.UserVmReplicaPointsView]
	AS
		SELECT 
			vrp.*,
			t.sid as userSid
		FROM [dbo].[Rest.VmReplicaPointsView] vrp
		JOIN 
		(
			SELECT DISTINCT lu.sid_string as sid, bo.id 
			FROM [dbo].[C.BObjects] bo 
			JOIN [dbo].[Security.VmsRecursiveInclusion] vri on vri.vm_key_hash = bo.unique_key_hash
			JOIN [dbo].[Security.HierarchyScopes] hs on hs.hierarchy_object_keyhash = vri.container_key_hash
			JOIN [dbo].[Security.RoleAccounts] ra on ra.role_group_id = hs.role_account_group_id
			JOIN [dbo].[Security.LoggedOnUserAccounts] lua ON (lua.account_id = ra.account_id)		
			JOIN [dbo].[Security.LoggedOnUsers] lu ON (lu.id = lua.login_id)
		) as t on t.id = vrp.object_id		
GO

--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.ObjectsInJobView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.ObjectsInJobView]
GO
	CREATE VIEW [dbo].[Rest.ObjectsInJobView]
	AS
		SELECT 
			objsInJob.[id] AS id,
		    objsInJob.[vss_options] AS oij_vss_options,
		    objsInJob.[type] AS oij_type,
			objsInJob.[order_no] AS oij_order_no,
		    objs.[type] as obj_type,
		    objs.[object_name] as obj_name,
		    objs.[object_id] as obj_ref,
		    objs.[viobject_type] as obj_type_str,
            objs.[display_name]as obj_display_name,
			objs.[platform]as obj_platform,	
			objs.[host_id] as obj_host_id,
			jobs.[id] as job_id,
			jobs.[name] as job_name,
			bservers.[id] as bserver_id,
			bservers.[ip_or_dns_name] as bserver_name

	    FROM [dbo].[C.ObjectsInJobs] objsInJob 
			INNER JOIN [dbo].[C.BJobs] jobs ON objsInJob.[job_id] = jobs.[id]
			INNER JOIN [dbo].[C.BObjects] objs ON objsInJob.[object_id] = objs.[id]
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON objsInJob.[db_instance_id] = bservers.[current_db_id]		
		WHERE bservers.[major_version] >= 7
GO


--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.AccountRolesView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.AccountRolesView]
GO
	CREATE VIEW [dbo].[Rest.AccountRolesView]
	AS
		SELECT 
			ra.[id] AS id,
			ra.[role_id] AS role_id,
			roles.[name] AS role_name,
			ra.[account_id] AS account_id,
			acc.[name] AS account_name,
			ra.[settings] as settings
	    FROM [dbo].[Security.RoleAccounts] ra
			INNER JOIN [dbo].[Security.Roles] roles ON ra.[role_id] = roles.[id]
			INNER JOIN [dbo].[Security.Accounts] acc ON ra.[account_id] = acc.[id]	
GO


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.AccountScopesView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.AccountScopesView]
GO
CREATE VIEW [dbo].[Rest.AccountScopesView]
	AS
	SELECT DISTINCT 
		s.id, 
		ra.account_id,
		s.role_account_group_id, 
		s.scope_state, 
		s.scope_name, 
		s.hierarchy_root_instance_id, 
		s.hierarchy_object_ref, 
		s.hierarchy_object_type,
		s.platform
		
	FROM [dbo].[Security.HierarchyScopes] s
	JOIN [dbo].[Security.RoleAccounts] ra ON (ra.role_group_id = s.role_account_group_id)
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Rest.AccountsView]'))
DROP VIEW [dbo].[Rest.AccountsView]
GO

CREATE VIEW [dbo].[Rest.AccountsView]
	AS
		SELECT DISTINCT
			acc.*,
			ra.security_scope_enabled as security_scope_enabled 
			FROM [dbo].[Security.RoleAccounts] ra
			INNER JOIN [dbo].[Security.Accounts] acc ON acc.id = ra.account_id
GO


--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.RestoreJobSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.RestoreJobSessionsView]
GO
	CREATE VIEW [dbo].[Rest.RestoreJobSessionsView]
	AS
		SELECT 
			js.id,
			js.job_name + '@' + CONVERT(nvarchar(50), js.creation_time, 120) AS name,
			js.job_type,
			js.creation_time,
			js.end_time,
			js.state,
			rjs.oib_display_name as vm_display_name,
			js.result,
			js.reason as failure_message,
			-- bjs.is_retry,
			bs.id AS backupserver_id,
			bs.ip_or_dns_name AS backupserver_name
		FROM 
			[dbo].[C.Backup.Model.RestoreJobSessions] rjs
			INNER JOIN [dbo].[C.Backup.Model.JobSessions] js ON rjs.id = js.id
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bs ON rjs.db_instance_id = bs.current_db_id
		WHERE 
			bs.[major_version] >= 7 AND
			js.creation_time >= DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()) - 14) AND
			js.job_type IN (4, 5, 6, 10, 11, 13, 25, 29, 41, 42, 48)
GO


--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.UserRestorePointsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.UserRestorePointsView]
GO
	CREATE VIEW [dbo].[Rest.UserRestorePointsView]
	AS
	
		SELECT DISTINCT
			pt.*,
			t.sid as userSid
		FROM [dbo].[Rest.RestorePointsView] pt
		JOIN [dbo].[C.Backup.Model.OIBs] vmpt ON pt.[id] = vmpt.[point_id]
		JOIN 
		(
			SELECT DISTINCT lu.sid_string as sid, bo.id 
			FROM [dbo].[C.BObjects] bo 
			JOIN [dbo].[Security.VmsRecursiveInclusion] vri on vri.vm_key_hash = bo.unique_key_hash
			JOIN [dbo].[Security.HierarchyScopes] hs on hs.hierarchy_object_keyhash = vri.container_key_hash
			JOIN [dbo].[Security.RoleAccounts] ra on ra.role_group_id = hs.role_account_group_id
			JOIN [dbo].[Security.LoggedOnUserAccounts] lua ON (lua.account_id = ra.account_id)		
			JOIN [dbo].[Security.LoggedOnUsers] lu ON (lu.id = lua.login_id)
		) as t on t.id = vmpt.object_id	
GO

--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.UserRestoreJobSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.UserRestoreJobSessionsView]
GO
	CREATE VIEW [dbo].[Rest.UserRestoreJobSessionsView]
	AS
		SELECT 
			js.id,
			js.job_name + '@' + CONVERT(nvarchar(50), js.creation_time, 120) AS name,
			js.job_type,
			js.creation_time,
			js.end_time,
			js.state,
			rjs.oib_display_name as vm_display_name,
			js.result,
			bs.id AS backupserver_id,
			bs.ip_or_dns_name AS backupserver_name,
			t.sid as userSid
		FROM 
			[dbo].[C.Backup.Model.RestoreJobSessions] rjs
			INNER JOIN [dbo].[C.Backup.Model.JobSessions] js ON rjs.id = js.id
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bs ON rjs.db_instance_id = bs.current_db_id
			INNER JOIN [dbo].[C.Backup.Model.OIBs] vmpt ON rjs.oib_id = vmpt.[id]
			INNER JOIN 
			(
				SELECT DISTINCT lu.sid_string as sid, bo.id 
				FROM [dbo].[C.BObjects] bo 
				JOIN [dbo].[Security.VmsRecursiveInclusion] vri on vri.vm_key_hash = bo.unique_key_hash
				JOIN [dbo].[Security.HierarchyScopes] hs on hs.hierarchy_object_keyhash = vri.container_key_hash
				JOIN [dbo].[Security.RoleAccounts] ra on ra.role_group_id = hs.role_account_group_id
				JOIN [dbo].[Security.LoggedOnUserAccounts] lua ON (lua.account_id = ra.account_id)		
				JOIN [dbo].[Security.LoggedOnUsers] lu ON (lu.id = lua.login_id)
			) as t on t.id = vmpt.object_id	
		WHERE 
			bs.[major_version] >= 7 AND
			js.creation_time >= DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()) - 14) AND
			js.job_type IN (4, 5, 6, 10, 11, 13, 25, 29, 41, 42, 48)
GO

------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.UserTaskSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.UserTaskSessionsView]
GO
	CREATE VIEW [dbo].[Rest.UserTaskSessionsView]
	AS
		SELECT 
			ts.id,
			ts.object_name + '@' + CONVERT(nvarchar(50), ts.creation_time, 120) AS name,
			ts.creation_time,
			ts.end_time,
			ts.status,
			ts.object_name as vm_display_name,
			CAST(ts.reason as nvarchar(max)) as reason,
			ts.total_size,
			js.id as job_id,
			js.job_name + ', ' + CAST(js.creation_time as nvarchar(50)) AS job_name,
			bs.id AS backupserver_id,
			bs.ip_or_dns_name AS backupserver_name,
			t.sid as userSid
		FROM 
			[dbo].[C.Backup.Model.BackupTaskSessions] ts
			JOIN [dbo].[C.Backup.Model.JobSessions] js ON ts.session_id = js.id
			JOIN [dbo].[Repl.Topology.BackupServers] bs ON js.db_instance_id = bs.current_db_id 
			INNER JOIN 
			(
				SELECT DISTINCT lu.sid_string as sid, bo.id 
				FROM [dbo].[C.BObjects] bo 
				JOIN [dbo].[Security.VmsRecursiveInclusion] vri on vri.vm_key_hash = bo.unique_key_hash
				JOIN [dbo].[Security.HierarchyScopes] hs on hs.hierarchy_object_keyhash = vri.container_key_hash
				JOIN [dbo].[Security.RoleAccounts] ra on ra.role_group_id = hs.role_account_group_id
				JOIN [dbo].[Security.LoggedOnUserAccounts] lua ON (lua.account_id = ra.account_id)		
				JOIN [dbo].[Security.LoggedOnUsers] lu ON (lu.id = lua.login_id)
			) as t on t.id = ts.object_id
		WHERE 
			bs.[major_version] >= 7 AND
			ts.creation_time >= DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()) - 14) AND
			js.job_type = 0
GO

------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.UserReplicaTaskSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.UserReplicaTaskSessionsView]
GO
	CREATE VIEW [dbo].[Rest.UserReplicaTaskSessionsView]
	AS
		SELECT 
			ts.id,
			ts.object_name + '@' + CONVERT(nvarchar(50), ts.creation_time, 120) AS name,
			ts.creation_time,
			ts.end_time,
			ts.status,
			ts.object_name as vm_display_name,
			CAST(ts.reason as nvarchar(max)) as reason,
			ts.total_size,
			js.id as job_id,
			js.job_name + ', ' + CAST(js.creation_time as nvarchar(50)) AS job_name,
			bs.id AS backupserver_id,
			bs.ip_or_dns_name AS backupserver_name,
			t.sid as userSid
		FROM 
			[dbo].[C.Backup.Model.BackupTaskSessions] ts
			JOIN [dbo].[C.Backup.Model.JobSessions] js ON ts.session_id = js.id
			JOIN [dbo].[Repl.Topology.BackupServers] bs ON js.db_instance_id = bs.current_db_id 
			INNER JOIN 
			(
				SELECT DISTINCT lu.sid_string as sid, bo.id 
				FROM [dbo].[C.BObjects] bo 
				JOIN [dbo].[Security.VmsRecursiveInclusion] vri on vri.vm_key_hash = bo.unique_key_hash
				JOIN [dbo].[Security.HierarchyScopes] hs on hs.hierarchy_object_keyhash = vri.container_key_hash
				JOIN [dbo].[Security.RoleAccounts] ra on ra.role_group_id = hs.role_account_group_id
				JOIN [dbo].[Security.LoggedOnUserAccounts] lua ON (lua.account_id = ra.account_id)		
				JOIN [dbo].[Security.LoggedOnUsers] lu ON (lu.id = lua.login_id)
			) as t on t.id = ts.object_id
		WHERE 
			bs.[major_version] >= 7 AND
			ts.creation_time >= DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()) - 14) AND
			js.job_type = 1
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rest.LastBackupJobSessionsView]') AND type in (N'V'))
	DROP VIEW [dbo].[Rest.LastBackupJobSessionsView]
GO
	CREATE VIEW [dbo].[Rest.LastBackupJobSessionsView]
	AS
		SELECT *
		FROM
		(
			SELECT 
				bjs.*,
				ROW_NUMBER() OVER(PARTITION BY job_id ORDER BY end_time DESC) AS number
			FROM [dbo].[Rest.BackupJobSessionsView] bjs
		) t
		WHERE t.number = 1
GO