-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Common.UtcDate2Local]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Common.UtcDate2Local]
GO
CREATE FUNCTION [dbo].[fn.Common.UtcDate2Local]
(    
    @localdate datetime
)
RETURNS datetime 
AS
BEGIN 

	declare @resultdate datetime
	set @resultdate = @localdate

	DECLARE @date DATETIME
	SET @date = getdate()

	DECLARE @utcdate DATETIME
	SET @utcdate = GetUTCDate()

	declare @ydt int, @mdt int, @ddt int, @hdt int

	set @ydt = DATEDIFF(year, @utcdate, @date)
	set @mdt = DATEDIFF(month, @utcdate, @date)
	set @ddt = DATEDIFF(day, @utcdate, @date) 
	set @hdt = DATEDIFF(hour, @utcdate, @date)

	set @resultdate = DATEADD(year,  @ydt, @resultdate)
	set @resultdate = DATEADD(month, @mdt, @resultdate)
	set @resultdate = DATEADD(day,   @ddt, @resultdate) 
	set @resultdate = DATEADD(hour,  @hdt, @resultdate)
	
    RETURN @resultdate
END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Common.SplitString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Common.SplitString]
GO
CREATE FUNCTION [dbo].[fn.Common.SplitString]
(    
    @RowData NVARCHAR(MAX),
    @Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE 
(
    ID INT IDENTITY(1,1),
    Data NVARCHAR(MAX)
) 
AS
BEGIN 
    DECLARE @Iterator INT
    SET @Iterator = 1

    DECLARE @FoundIndex INT
    SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)

    WHILE (@FoundIndex>0)
    BEGIN
        INSERT INTO @RtnValue (data)
        SELECT 
            Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))

        SET @RowData = SUBSTRING(@RowData,
                @FoundIndex + DATALENGTH(@Delimeter) / 2,
                LEN(@RowData))

        SET @Iterator = @Iterator + 1
        SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
    END
    
    INSERT INTO @RtnValue (Data)
    SELECT Data = LTRIM(RTRIM(@RowData))

    RETURN
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[view.Notification.JobsData]'))
DROP VIEW [dbo].[view.Notification.JobsData]
GO
CREATE VIEW [dbo].[view.Notification.JobsData]
AS
	SELECT DISTINCT
		type,
		latest_result,
		COUNT(*) OVER(PARTITION BY type, latest_result)  as count
	FROM [dbo].[C.BJobs]
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[view.AllJobsSessions]'))
DROP VIEW [dbo].[view.AllJobsSessions]
GO
CREATE VIEW [dbo].[view.AllJobsSessions]
AS
SELECT  dbo.[C.Backup.Model.JobSessions].id,
	    dbo.[C.Backup.Model.JobSessions].job_id,		
		dbo.[C.Backup.Model.JobSessions].job_name, 
		dbo.[C.Backup.Model.JobSessions].job_type,
		dbo.[C.Backup.Model.JobSessions].creation_time,
		dbo.[C.Backup.Model.JobSessions].end_time, 
		dbo.[C.Backup.Model.JobSessions].state,
		dbo.[C.Backup.Model.JobSessions].result,
		dbo.[C.Backup.Model.JobSessions].control, 
		dbo.[C.Backup.Model.JobSessions].description,
		dbo.[C.Backup.Model.JobSessions].operation,
		dbo.[C.Backup.Model.JobSessions].progress,
		dbo.[C.Backup.Model.JobSessions].db_instance_id,
		CASE
			WHEN dbo.[C.Backup.Model.BackupJobSessions].is_retry IS NULL THEN 0
			ELSE dbo.[C.Backup.Model.BackupJobSessions].is_retry
		END as is_retry

FROM	
		[dbo].[C.Backup.Model.JobSessions]  LEFT JOIN dbo.[C.Backup.Model.BackupJobSessions] 
		ON [dbo].[C.Backup.Model.JobSessions].[id] = dbo.[C.Backup.Model.BackupJobSessions].id


GO
-----------------------------------------------------


-----------------------------------------------------
--
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[view.Backup.BackupJobSessions]'))
DROP VIEW [dbo].[view.Backup.BackupJobSessions]
GO
CREATE VIEW [dbo].[view.Backup.BackupJobSessions]
AS
SELECT  dbo.[C.Backup.Model.BackupJobSessions].id, 
		dbo.[C.Backup.Model.BackupJobSessions].is_retry, 
		dbo.[C.Backup.Model.BackupJobSessions].total_objects, 
		dbo.[C.Backup.Model.BackupJobSessions].processed_objects, 
		dbo.[C.Backup.Model.BackupJobSessions].total_size, 
		dbo.[C.Backup.Model.BackupJobSessions].processed_size,
		dbo.[C.Backup.Model.BackupJobSessions].job_source_type, 
		dbo.[C.Backup.Model.BackupJobSessions].avg_speed, 
		dbo.[C.Backup.Model.BackupJobSessions].is_startfull, 
		dbo.[C.Backup.Model.BackupJobSessions].db_instance_id,
		dbo.[C.Backup.Model.BackupJobSessions].stored_size,
		dbo.[C.Backup.Model.JobSessions].job_id,
		dbo.[C.Backup.Model.JobSessions].job_name, 
		dbo.[C.Backup.Model.JobSessions].job_type,
		dbo.[C.Backup.Model.JobSessions].creation_time,
		dbo.[C.Backup.Model.JobSessions].end_time, 
		dbo.[C.Backup.Model.JobSessions].state,
		dbo.[C.Backup.Model.JobSessions].result,
		dbo.[C.Backup.Model.JobSessions].control, 
		dbo.[C.Backup.Model.JobSessions].description,
		dbo.[C.Backup.Model.JobSessions].operation,
		dbo.[C.Backup.Model.JobSessions].progress
FROM
	dbo.[C.Backup.Model.BackupJobSessions] INNER JOIN
    dbo.[C.Backup.Model.JobSessions]
ON
	dbo.[C.Backup.Model.BackupJobSessions].id = dbo.[C.Backup.Model.JobSessions].id
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Backup.GetSessionLog]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Backup.GetSessionLog]
GO
CREATE FUNCTION [dbo].[fn.Backup.GetSessionLog]
(
	@log_xml xml
)
RETURNS nvarchar( max )
AS
BEGIN
	DECLARE @retVal as nvarchar( max )
	set @retVal = ''
	
	declare @log_idx int
	set @log_idx = 1

	declare @log_str  nvarchar(max)
	declare @log_date nvarchar(max)
	declare @log_status nvarchar(max)
	
	set @log_str = @log_xml.value('(/log_xml/Root/Log[attribute::Id=''1''])[1]', 'nvarchar(max)')
	set @log_date = @log_xml.value('(/log_xml/Root/Log[attribute::Id=''1''])[1]/@Time', 'nvarchar(max)')	
	set @log_status = @log_xml.value('(/log_xml/Root/Log[attribute::Id=''1''])[1]/@Status', 'nvarchar(max)')	

	while @log_str is not null
	begin
		set @retVal = @retVal + @log_date
		
		if @log_status = 'EFailed'
			set @retVal = @retVal + ' ERROR: '
		else
			set @retVal = @retVal + ' Info: '

		set @retVal = @retVal + @log_str		
		set @retVal = @retVal + '\n'
		
		set @log_idx = @log_idx + 1	
		
		set @log_str = @log_xml.value('(/log_xml/Root/Log[@Id=sql:variable("@log_idx")])[1]', 'nvarchar(max)')	
		set @log_date = @log_xml.value('(/log_xml/Root/Log[@Id=sql:variable("@log_idx")])[1]/@Time', 'nvarchar(max)')	
		set @log_status = @log_xml.value('(/log_xml/Root/Log[@Id=sql:variable("@log_idx")])[1]/@Status', 'nvarchar(max)')	

	end

	RETURN @retVal;
END
GO
-----------------------------------------------------


-----------------------------------------------------
--
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[view.Backup.SbSessions]'))
DROP VIEW [dbo].[view.Backup.SbSessions]
GO
CREATE VIEW [dbo].[view.Backup.SbSessions]
AS
SELECT
	sbs.id, 
	sbs.preferred_date, 
	sbs.db_instance_id,
	
	js.job_id, 
	js.job_name, 
	js.job_type, 
	js.creation_time, 
	js.end_time, 
	js.state, 
	js.result, 
	js.control, 
	js.description, 
	[dbo].[fn.Backup.GetSessionLog] (js.log_xml) log, 
	js.operation, 
	js.progress

FROM         
	dbo.[C.Backup.Model.JobSessions] js INNER JOIN
    dbo.[C.Backup.Model.SbSessions] sbs
ON 
	js.id = sbs.id
GO
-----------------------------------------------------



-----------------------------------------------------
--
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Backup.GetVmStats]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Backup.GetVmStats]
GO
CREATE FUNCTION [dbo].[fn.Backup.GetVmStats] ()
RETURNS 
@vms_stat TABLE
( 
	db_instance_id uniqueidentifier,
	vms_count int,
	vms_total_size BigInt
)
AS
BEGIN
	INSERT @vms_stat( [db_instance_id], [vms_count], [vms_total_size])
	(
		select 
		db_instance_id,
		case when total_objects is null then 0 else total_objects end,
		case when total_size is null then 0 else total_size end
		from
		(
			select 
			j.db_instance_id, 
			sum(total_objects) total_objects, 
			sum(total_size) total_size
			from
				[dbo].[C.BJobs] j
			LEFT JOIN 
				(
					SELECT 
						sessions.[db_instance_id],
						sessions.[job_id],
						sessions.[total_objects],
						sessions.[total_size],
						sessions.[creation_time]
					FROM [dbo].[view.Backup.BackupJobSessions] sessions
					where 
					sessions.[creation_time] = 
					(
						SELECT MAX([dbo].[view.Backup.BackupJobSessions].[creation_time]) 
						FROM [dbo].[view.Backup.BackupJobSessions]
						WHERE [dbo].[view.Backup.BackupJobSessions].[job_id] = sessions.[job_id]
					)		
				)q (db_instance_id, job_id, total_objects, total_size, session_time)
			ON
				j.id = q.job_id
			group by j.db_instance_id
		) o
	)
	RETURN 
END
GO
-----------------------------------------------------


-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.GetRoleAccounts]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Security.GetRoleAccounts]
GO
CREATE PROCEDURE [dbo].[usp.Security.GetRoleAccounts]
AS
BEGIN	
	SET NOCOUNT ON;
		
	SELECT 
		[dbo].[Security.RoleAccounts].[id] as role_account_id,
		[dbo].[Security.Accounts].[id] as account_id,
		[dbo].[Security.Accounts].[name] as account_name,
		[dbo].[Security.Accounts].[nt4_name] as account_nt4_name,
		[dbo].[Security.Accounts].[type] as account_type,
		[dbo].[Security.Accounts].[sid] as account_sid,
		[dbo].[Security.Roles].[id] as role_id,
		[dbo].[Security.Roles].[name] as role_name,
		[dbo].[Security.RoleAccounts].[security_scope_enabled] as security_scope_enabled,
		[dbo].[Security.RoleAccounts].[role_group_id] as role_group_id,
		[dbo].[Security.RoleAccounts].[settings]
	FROM
		[dbo].[Security.RoleAccounts], [dbo].[Security.Accounts], [dbo].[Security.Roles]
	WHERE
		[dbo].[Security.RoleAccounts].[account_id] = [dbo].[Security.Accounts].[id] AND
		[dbo].[Security.RoleAccounts].[role_id] = [dbo].[Security.Roles].[id]
END
GO
-----------------------------------------------------
		


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.AddAccountInRole]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Security.AddAccountInRole]
GO
CREATE PROCEDURE [dbo].[usp.Security.AddAccountInRole]
	@account_id uniqueidentifier,
	@name nvarchar(255),
	@nt4_name nvarchar(255),
	@type int,
	@sid nvarchar(100),
	@role_id uniqueidentifier,
	@security_scope_enabled bit,
	@role_group_id uniqueidentifier,
	@settings xml
AS
BEGIN	
	SET NOCOUNT ON;		
	
	IF EXISTS (SELECT * FROM [dbo].[Security.RoleAccounts] WHERE [account_id] = @account_id AND [role_id] = @role_id)
		RAISERROR(N'The role-account association already exists', 9, 1 )
	
	DECLARE @role_account_id uniqueidentifier
	SET @role_account_id = NEWID()
	INSERT INTO [dbo].[Security.RoleAccounts]( [id], [role_id], [account_id], [security_scope_enabled],[role_group_id],[settings] )
	VALUES(@role_account_id , @role_id, @account_id, @security_scope_enabled, @role_group_id, @settings)
	
	SELECT @role_account_id as role_account_id
END
GO	
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.RemoveAccountFromRole]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Security.RemoveAccountFromRole]
GO
CREATE PROCEDURE [dbo].[usp.Security.RemoveAccountFromRole]
	@role_account_id uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;	
	
	DECLARE @account_id uniqueidentifier
	DECLARE @role_account_group_id uniqueidentifier	

	SELECT TOP(1) 
		@account_id = [dbo].[Security.Accounts].[id],
		@role_account_group_id = [dbo].[Security.RoleAccounts].[role_group_id]
	FROM [dbo].[Security.Accounts] 
		INNER JOIN [dbo].[Security.RoleAccounts] ON [dbo].[Security.Accounts].[id] = [dbo].[Security.RoleAccounts].[account_id]
	WHERE [dbo].[Security.RoleAccounts].[id] = @role_account_id

	IF (SELECT COUNT(*) FROM [dbo].[Security.RoleAccounts] WHERE [dbo].[Security.RoleAccounts].[account_id] = @account_id) = 1
	BEGIN
		DELETE FROM [dbo].[Security.Accounts]
		WHERE [dbo].[Security.Accounts].[id] = @account_id

		DELETE FROM [dbo].[Security.HierarchyScopes] 
		WHERE role_account_group_id = @role_account_group_id
	END
	
	DELETE FROM [dbo].[Security.RoleAccounts]
	WHERE [dbo].[Security.RoleAccounts].[id] = @role_account_id
END
GO
-----------------------------------------------------
		


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.UpdateRoleAccount]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Security.UpdateRoleAccount]
GO            
CREATE PROCEDURE [dbo].[usp.Security.UpdateRoleAccount]
	@role_account_id uniqueidentifier,
	@account_id uniqueidentifier,
	@account_name nvarchar(255),
	@account_nt4_name nvarchar(255),
	@account_type int,
	@account_sid nvarchar(100),
	@role_id uniqueidentifier,
	@security_scope_enabled bit,
	@settings xml
AS
BEGIN	
	SET NOCOUNT ON;	
	
	IF NOT EXISTS (SELECT * FROM [dbo].[Security.RoleAccounts] WHERE [id] = @role_account_id)
		RAISERROR(N'The role-account association does not exists', 9, 1 )
	
	UPDATE [dbo].[Security.Accounts]
		SET [dbo].[Security.Accounts].[name] = @account_name,
			[dbo].[Security.Accounts].[sid] = @account_sid,
			[dbo].[Security.Accounts].[nt4_name] = @account_nt4_name,
			[dbo].[Security.Accounts].[type] = @account_type
	WHERE [dbo].[Security.Accounts].[id] = @account_id

	UPDATE [dbo].[Security.RoleAccounts]
		SET [dbo].[Security.RoleAccounts].[role_id] = @role_id,
			[dbo].[Security.RoleAccounts].[security_scope_enabled] = @security_scope_enabled,
			[dbo].[Security.RoleAccounts].[settings] = @settings
	WHERE [dbo].[Security.RoleAccounts].[id] = @role_account_id
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.RemoveRoleAccount]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Security.RemoveRoleAccount]
GO            
CREATE PROCEDURE [dbo].[usp.Security.RemoveRoleAccount]
	@role_account_id uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;	
	
	IF NOT EXISTS (SELECT * FROM [dbo].[Security.RoleAccounts] WHERE [id] = @role_account_id)
		RAISERROR(N'The role-account association does not exists', 9, 1 )

	DELETE FROM [dbo].[Security.RoleAccounts]
	WHERE [dbo].[Security.RoleAccounts].[id] = @role_account_id
END
GO
-----------------------------------------------------	



				
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Repl.PostDataCollection]') AND type in (N'P'))
	DROP PROCEDURE [dbo].[usp.Repl.PostDataCollection]
GO			
CREATE PROCEDURE [dbo].[usp.Repl.PostDataCollection]
	@dbInstanceId uniqueidentifier,
	@vbServerVersionStr nvarchar(50),
	@vbServerVersionMajor int,
	@vbServerVersionMinor int,
	@srvNameInCat nvarchar(256) = null
AS
BEGIN	
	SET NOCOUNT ON;
	
	-- First of all sync old data tables
	if @vbServerVersionMajor < 5 
		RAISERROR('Versions less than 5 is not supported.', 9, 1)
	if @vbServerVersionMajor = 5 
		begin exec [dbo].[usp.Upgrade.56.InternalSync] @dbInstanceId, N'6' end	
	if (@vbServerVersionMajor < 6 OR 
		(@vbServerVersionMajor = 6 AND @vbServerVersionMinor = 0) OR
		(@vbServerVersionMajor = 6 AND @vbServerVersionMinor = 1))
		begin exec [dbo].[usp.Upgrade.v6_to_v61.InternalSync] @dbInstanceId, N'65' end
	if (@vbServerVersionMajor < 7)
		begin exec [dbo].[usp.Upgrade.v65_to_v70.InternalSync] @dbInstanceId, N'C' end

	EXEC [dbo].[usp.GridReport.Config.FixVmsReport]	

	DECLARE @FustyMarkers TABLE ( id uniqueidentifier )
	
	INSERT INTO @FustyMarkers
		SELECT [dbo].[BJobs.Markers].[id] FROM [dbo].[BJobs.Markers]
		INNER JOIN [dbo].[C.BJobs] ON [dbo].[BJobs.Markers].[job_id] = [dbo].[C.BJobs].[id]
	WHERE [dbo].[C.BJobs].[latest_result] = -1 OR [dbo].[C.BJobs].[latest_result] = 5

	DELETE FROM [dbo].[BJobs.Markers]
	WHERE [dbo].[BJobs.Markers].[id] IN ( SELECT id FROM @FustyMarkers )

	INSERT INTO @FustyMarkers(id)
	SELECT [dbo].[BJobs.Markers].[id] FROM [dbo].[BJobs.Markers]
		INNER JOIN [dbo].[C.BJobs] ON [dbo].[BJobs.Markers].[job_id] = [dbo].[C.BJobs].[id]
		LEFT JOIN [dbo].[C.Backup.Model.JobSessions] ON [dbo].[C.BJobs].[id] = [dbo].[C.Backup.Model.JobSessions].[job_id]
	WHERE 
		[dbo].[C.BJobs].[db_instance_id] = @dbInstanceId AND
		([dbo].[BJobs.Markers].[mark_time] < [dbo].[C.Backup.Model.JobSessions].[creation_time] OR
		[dbo].[BJobs.Markers].[mark_time] < [dbo].[C.Backup.Model.JobSessions].[end_time])

	UPDATE [dbo].[C.BJobs]
		SET [C.BJobs].[latest_result] = [dbo].[BJobs.Markers].[latest_result]
	FROM [dbo].[BJobs.Markers]
	WHERE 					
		[dbo].[C.BJobs].[db_instance_id] = @dbInstanceId AND
		[dbo].[C.BJobs].[id] = [dbo].[BJobs.Markers].[job_id] AND
		[dbo].[BJobs.Markers].[id] NOT IN (SELECT id FROM @FustyMarkers) 

	DELETE FROM [dbo].[BJobs.Markers]
	WHERE [dbo].[BJobs.Markers].[id] IN ( SELECT id FROM @FustyMarkers )
	
	UPDATE [dbo].[Repl.Topology.BackupServers]
		SET [dbo].[Repl.Topology.BackupServers].[version] = @vbServerVersionStr,
			[dbo].[Repl.Topology.BackupServers].[major_version] = @vbServerVersionMajor
	WHERE [dbo].[Repl.Topology.BackupServers].[current_db_id] = @dbInstanceId
	
	IF (@srvNameInCat IS NOT NULL)
	BEGIN
		UPDATE [dbo].[Repl.Topology.BackupServers]
		SET [dbo].[Repl.Topology.BackupServers].[name_in_catalog] = @srvNameInCat
		WHERE [dbo].[Repl.Topology.BackupServers].[current_db_id] = @dbInstanceId
	END

	-- vCenter servers
	-- Insert newly added hosts 
	INSERT INTO [dbo].[VCPlugins] (id, hostname, port, username, password, version, status, plugin_version, installed_by, plugin_state)
	SELECT NEWID(), t.name, t.port, t.user_name, '', [dbo].[fn.Util.ParseVcVersion](t.info), 0, NULL, NULL, 0
	FROM (
		SELECT DISTINCT  h.[name], sc.[port], creds.[user_name], h.[info]
                                FROM [dbo].[C.Hosts] h 
                                LEFT OUTER JOIN [dbo].[C.Soap_creds] sc ON h.[id] = sc.[host_id]
								LEFT OUTER JOIN [dbo].[C.Credentials] creds ON sc.[creds] = creds.[id]
                                LEFT OUTER JOIN [dbo].[VCPlugins] vc ON vc.hostname = h.name
                                WHERE h.[type] = 1 
								AND h.[db_instance_id] =@dbInstanceId 
								AND vc.hostname IS NULL
								AND LEN(h.[info]) > 1
	) t
	-- Mark removed hosts with warning status							
	
	DECLARE @vc_status TABLE ( id uniqueidentifier,  hcount int, bscount int, plugin_state int);
							
	INSERT INTO @vc_status(id, hcount, bscount, plugin_state) 
		SELECT pl.id, tbl.hcount, tbl.bscount, pl.plugin_state
		FROM [dbo].[VCPlugins] pl
		JOIN 
		(
			SELECT vc.id, COUNT(DISTINCT ph.id) as hcount, COUNT(bs.current_db_id) as bscount
			FROM [dbo].[VCPlugins] vc
			JOIN [dbo].[C.Hosts] h ON (vc.hostname = h.name) 
			LEFT JOIN [dbo].[C.PhysicalHosts] ph ON (ph.id = h.physical_host_id)
			LEFT JOIN [dbo].[Repl.Topology.BackupServers] bs ON (h.db_instance_id = bs.current_db_id)
			GROUP BY vc.id 
		) as tbl ON (tbl.id = pl.id)
	
	-- Update host state 	
	UPDATE [dbo].[VCPlugins] SET status = 0 WHERE id 
		IN (SELECT id FROM @vc_status WHERE (hcount > 0 OR bscount > 0))
			
	-- Set host warning if plugin status is unknown or installed
	UPDATE [dbo].[VCPlugins] SET status = 1 WHERE id 
		IN (SELECT id FROM @vc_status WHERE (hcount = 0 OR bscount = 0) AND (plugin_state = 0 OR plugin_state = 2))
	-- delete host if plugin status is not installed or unsupported
	DELETE FROM [dbo].[VCPlugins] WHERE id IN 
	(SELECT id FROM @vc_status WHERE (hcount = 0 OR bscount = 0) AND (plugin_state = 1 OR plugin_state = 3))
	
END
GO	
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.BackupSrv.GetBackupServerName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.BackupSrv.GetBackupServerName]
GO
CREATE FUNCTION [dbo].[fn.BackupSrv.GetBackupServerName]
(
	@backup_srv_id uniqueidentifier
)
RETURNS nvarchar( 255 )
AS
BEGIN
	DECLARE @retVal nvarchar( 255 )

	
	SELECT TOP(1) @retVal = [dbo].[Repl.Topology.BackupServers].[display_name]
	FROM [dbo].[Repl.Topology.BackupServers]
	WHERE [dbo].[Repl.Topology.BackupServers].[id] = @backup_srv_id

	IF @retVal IS NULL
		SELECT @retVal = N'Unknown'

	RETURN @retVal

END
GO	
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.BackupSrv.FindCurrentDbId]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.BackupSrv.FindCurrentDbId]
GO	
CREATE FUNCTION [dbo].[fn.BackupSrv.FindCurrentDbId]
(
	@backupsrv_id uniqueidentifier
)
RETURNS uniqueidentifier
AS
BEGIN
	DECLARE @db_id uniqueidentifier

	SELECT TOP(1) @db_id = [dbo].[Repl.Topology.BackupServers].[current_db_id]
	FROM [dbo].[Repl.Topology.BackupServers]
	WHERE [dbo].[Repl.Topology.BackupServers].[id] = @backupsrv_id
	
	RETURN @db_id
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.BackupSrv.GetActualJobsNumber]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.BackupSrv.GetActualJobsNumber]
GO
CREATE FUNCTION [dbo].[fn.BackupSrv.GetActualJobsNumber]
(
	@backupsrv_id uniqueidentifier
)
RETURNS int
AS
BEGIN
	DECLARE @db_instance_id uniqueidentifier
	SELECT @db_instance_id = [dbo].[fn.BackupSrv.FindCurrentDbId]( @backupsrv_id )
	IF @db_instance_id IS NULL
		RETURN 0

	DECLARE @jobs_count int
	SELECT @jobs_count = COUNT([dbo].[C.BJobs].[id])
	FROM [dbo].[C.BJobs]
	WHERE [dbo].[C.BJobs].[db_instance_id] = @db_instance_id

	RETURN @jobs_count
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Update.IncUsn]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Update.IncUsn]	
GO            
CREATE PROCEDURE [dbo].[usp.Update.IncUsn]	
	@newHighestUsn BigInt OUT	
AS
BEGIN	
	SET NOCOUNT ON;
	
	UPDATE [dbo].[Update.Counters]
	SET highestUsn = highestUsn + 1
	WHERE [counter_type] = 0
		
	SELECT TOP(1) @newHighestUsn = [dbo].[Update.Counters].[highestUsn]
	FROM [dbo].[Update.Counters]
	WHERE [counter_type] = 0

	IF @newHighestUsn IS NULL
		BEGIN
			INSERT INTO [dbo].[Update.Counters]( [highestUsn], [counter_type] )
			VALUES( 0, 0 )
			SELECT @newHighestUsn = 0
		END
		
END
GO
-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.GetCurrDbUpdateCounter]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.GetCurrDbUpdateCounter]
GO	

CREATE PROCEDURE [dbo].[usp.Data.GetCurrDbUpdateCounter]	
AS
BEGIN	
	SET NOCOUNT ON;
    
	SELECT TOP(1) [dbo].[Update.Counters].[highestUsn] as 'db_updates_counter'
	FROM [dbo].[Update.Counters]
	WHERE [counter_type] = 0
END	 		
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Update.OnCatalogCfgChanged]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Update.OnCatalogCfgChanged]	
GO            
CREATE PROCEDURE [dbo].[usp.Update.OnCatalogCfgChanged]	
	@newHighestUsn BigInt OUT	
AS
BEGIN	
	SET NOCOUNT ON;
	
	EXEC [dbo].[usp.Update.IncUsn] @newHighestUsn OUT
	
	if  EXISTS	(SELECT * FROM [dbo].[Update.Counters] WHERE [counter_type] = 1 )
		BEGIN
			UPDATE [dbo].[Update.Counters]
			SET [highestUsn] = @newHighestUsn
			WHERE [counter_type] = 1
		END
	ELSE
		BEGIN
			INSERT INTO [dbo].[Update.Counters]( [highestUsn],[counter_type])
			VALUES( @newHighestUsn, 1 )
		END			
END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Update.GetCatalogCfgUpdateCounter]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Update.GetCatalogCfgUpdateCounter]
GO	

CREATE PROCEDURE [dbo].[usp.Update.GetCatalogCfgUpdateCounter]	
AS
BEGIN	
	SET NOCOUNT ON;
    
	SELECT TOP(1) [dbo].[Update.Counters].[highestUsn] as 'db_updates_counter'
	FROM [dbo].[Update.Counters]
	WHERE [counter_type] = 1
END	 		
GO
-----------------------------------------------------


		
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Update.AddTombstone]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Update.AddTombstone]	
GO            		
CREATE PROCEDURE [dbo].[usp.Update.AddTombstone]
	@tombstoneTypeId uniqueidentifier	
AS
BEGIN	
	SET NOCOUNT ON;
	
	DECLARE @tombstoneUsn BigInt

	EXEC [dbo].[usp.Update.IncUsn] @tombstoneUsn OUT

	UPDATE [dbo].[Update.TombstoneTypes]
	SET [highest_tombstone_usn] = @tombstoneUsn
	WHERE [id] = @tombstoneTypeId

END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Repl.Topology.EnumerateReplicatedVbDb]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Repl.Topology.EnumerateReplicatedVbDb]
GO            
CREATE PROCEDURE [usp.Repl.Topology.EnumerateReplicatedVbDb]
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT [Repl.Topology.BackupDbInstances].[id] as db_id,
		   [Repl.Topology.BackupDbInstances].[last_sync_usn],
		   [Repl.Topology.BackupDbInstances].[last_sync_time]
	FROM  [Repl.Topology.BackupDbInstances]
END
GO
-----------------------------------------------------


	    
	    
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Repl.Topology.UpdateBackupServerSyncPoint]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Repl.Topology.UpdateBackupServerSyncPoint]
GO            	    
CREATE PROCEDURE [dbo].[usp.Repl.Topology.UpdateBackupServerSyncPoint]
	@backup_server_id uniqueidentifier,
	@db_id uniqueidentifier,
	@sync_usn BigInt,
	@sync_date datetime
AS
BEGIN				
	SET NOCOUNT ON;
	IF NOT EXISTS ( SELECT * FROM [Repl.Topology.BackupServers] WHERE [Repl.Topology.BackupServers].[id] = @backup_server_id )
		RAISERROR('The specified backup server does not exist in replication database.', 9, 1)

	-- Define sync. point USN
	DECLARE @sync_point_usn bigint
	EXEC [dbo].[usp.Update.IncUsn] @sync_point_usn OUT

	IF NOT EXISTS( SELECT * FROM [Repl.Topology.BackupDbInstances] WHERE [Repl.Topology.BackupDbInstances].[id] = @db_id )
		BEGIN
			INSERT INTO [dbo].[Repl.Topology.BackupDbInstances]( [id],[last_sync_usn], [last_sync_time], [update_usn])
			VALUES( @db_id, @sync_usn, @sync_date, @sync_point_usn )
		END
	ELSE
		BEGIN
			UPDATE [dbo].[Repl.Topology.BackupDbInstances]
			SET [update_usn] = @sync_point_usn,
				[last_sync_usn] = @sync_usn,
				[last_sync_time] = @sync_date
			WHERE [id] = @db_id
		END

	UPDATE [Repl.Topology.BackupServers]
	SET [current_db_id] = @db_id
	WHERE [id] = @backup_server_id
END
GO
-----------------------------------------------------
		
		
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Rendering.EnumContainers]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Rendering.EnumContainers]
GO		
CREATE PROCEDURE [dbo].[usp.Rendering.EnumContainers]	
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT [dbo].[Rendering.Containers].[id],
		   [dbo].[Rendering.Containers].[display_name],
		   [dbo].[Rendering.Containers].[type],
		   [dbo].[Rendering.Containers].[is_history_bar_required]
	FROM	[dbo].[Rendering.Containers]
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Rendering.EnumImgFolders]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Rendering.EnumImgFolders]
GO
CREATE PROCEDURE [dbo].[usp.Rendering.EnumImgFolders]	
AS
BEGIN
	SET NOCOUNT ON;

	SELECT [dbo].[Rendering.ImageFolders].[id] as img_folder_id,
		   [dbo].[Rendering.ImageFolders].[folder_name]
	FROM   [dbo].[Rendering.ImageFolders]
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Rendering.EnumImgFiles]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Rendering.EnumImgFiles]
GO		
CREATE PROCEDURE [dbo].[usp.Rendering.EnumImgFiles]
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT [dbo].[Rendering.ImageFiles].[id] as img_file_id,
		   [dbo].[Rendering.ImageFolders].[folder_name],
		   [dbo].[Rendering.ImageFiles].[file_name]
	FROM
		  [dbo].[Rendering.ImageFolders] INNER JOIN [dbo].[Rendering.ImageFiles]
		  ON [dbo].[Rendering.ImageFolders].[id] = [dbo].[Rendering.ImageFiles].[folder_id]
		   
    
END
GO
-----------------------------------------------------
			
			
			

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Dashboard.GetData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Dashboard.GetData]
GO			   
CREATE PROCEDURE [dbo].[usp.Dashboard.GetData]	
	@period_hours int
AS
BEGIN
	DECLARE @utc_time_current datetime, @utc_time_from datetime
	SET @utc_time_current = GETUTCDATE()
	SET @utc_time_from = DATEADD(hour, -@period_hours, @utc_time_current)
	
	DECLARE @job_objects table
	(
		object_id uniqueidentifier,
		object_type int
	)
	INSERT INTO @job_objects(object_id, object_type)
	SELECT DISTINCT
		[dbo].[C.BObjects].[id],
		[dbo].[C.BObjects].[type]
	FROM 
		[dbo].[Repl.Topology.BackupServers]
		INNER JOIN [dbo].[C.Backup.Model.Backups] ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.Backup.Model.Backups].[db_instance_id]
		INNER JOIN [dbo].[C.Backup.Model.Points] ON [dbo].[C.Backup.Model.Backups].[id] = [dbo].[C.Backup.Model.Points].[backup_id]
		INNER JOIN [dbo].[C.Backup.Model.OIBs] ON [dbo].[C.Backup.Model.Points].[id] = [dbo].[C.Backup.Model.OIBs].[point_id]
		INNER JOIN [dbo].[C.BObjects] ON [dbo].[C.Backup.Model.OIBs].[object_id] = [dbo].[C.BObjects].[id]

	DECLARE @vms_stat TABLE
	(
		db_instance_id uniqueidentifier,
		vms_count int,
		vms_total_size BigInt
	)
	INSERT INTO @vms_stat( [db_instance_id], [vms_count], [vms_total_size])
	SELECT * FROM [dbo].[fn.Backup.GetVmStats]()

	SELECT 
--SUMMARY BOX
	(SELECT COUNT(*) FROM  [dbo].[Repl.Topology.BackupServers]) as BackupServers,
	(SELECT	COUNT(*) FROM [dbo].[Repl.Topology.BackupServers] INNER JOIN [dbo].[C.BJobs] ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.BJobs].[db_instance_id]) as Jobs,
	(SELECT COUNT(*) FROM @job_objects vms WHERE vms.[object_type] = 1) as VMs,
	(SELECT COUNT(*) FROM @job_objects vms WHERE vms.[object_type] = 4) as Templates,
	
--DATA BOX
	(
		SELECT ISNULL(SUM(q.avg_speed), 0)
		FROM 
		(
			SELECT
				AVG([dbo].[C.Backup.Model.BackupTaskSessions].[avg_speed]) as avg_speed
			FROM 
				[dbo].[Repl.Topology.BackupServers]
				INNER JOIN [dbo].[C.BJobs] ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.BJobs].[db_instance_id]
				INNER JOIN [dbo].[view.Backup.BackupJobSessions] ON [dbo].[C.BJobs].[id] = [dbo].[view.Backup.BackupJobSessions].[job_id]
				INNER JOIN [dbo].[C.Backup.Model.BackupTaskSessions] ON [dbo].[view.Backup.BackupJobSessions].[id] = [dbo].[C.Backup.Model.BackupTaskSessions].[session_id]
			WHERE 
				(
					[dbo].[C.Backup.Model.BackupTaskSessions].[status] = 0 OR
					[dbo].[C.Backup.Model.BackupTaskSessions].[status] = 3
				) AND
				[dbo].[C.Backup.Model.BackupTaskSessions].[end_time] > @utc_time_from
			GROUP BY [dbo].[C.Backup.Model.BackupTaskSessions].[db_instance_id]
		) q
	) as ProcessingSpeed,
	(
		SELECT ISNULL(SUM(vm_stat.[vms_total_size]), 0) 
		FROM @vms_stat vm_stat
	) as TotalSourceData,
	(
		SELECT ISNULL(SUM([dbo].[C.Backup.Model.Storages].[full_size]), 0)
		FROM [dbo].[C.Backup.Model.Storages]
		WHERE [dbo].[C.Backup.Model.Storages].[file_path] LIKE '%.vbk'
	) as FullBackupsSize,
	(
		SELECT ISNULL(SUM([dbo].[C.Backup.Model.Storages].[full_size]), 0)
		FROM [dbo].[C.Backup.Model.Storages]
		WHERE [dbo].[C.Backup.Model.Storages].[file_path] NOT LIKE '%.vbk'
	) as ResPointsSize,

--LAST PERIOD BOX						
	(SELECT COUNT([dbo].[C.BJobs].[id]) 
		FROM [dbo].[Repl.Topology.BackupServers]
			INNER JOIN [dbo].[C.BJobs] ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.BJobs].[db_instance_id]
			INNER JOIN [dbo].[view.Backup.BackupJobSessions] ON [dbo].[C.BJobs].[id] = [dbo].[view.Backup.BackupJobSessions].[job_id]
		WHERE [dbo].[view.Backup.BackupJobSessions].[end_time] > @utc_time_from) as TotalJobRuns,
	(SELECT COUNT([dbo].[C.BJobs].[id]) 
		FROM [dbo].[Repl.Topology.BackupServers]
			INNER JOIN [dbo].[C.BJobs] ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.BJobs].[db_instance_id]
			INNER JOIN [dbo].[view.Backup.BackupJobSessions] ON [dbo].[C.BJobs].[id] = [dbo].[view.Backup.BackupJobSessions].[job_id]
		WHERE
			[dbo].[view.Backup.BackupJobSessions].[result] = 0 AND 
			[dbo].[view.Backup.BackupJobSessions].[end_time] > @utc_time_from) as JobSuccesses,
	(SELECT COUNT([dbo].[C.BJobs].[id]) 
		FROM [dbo].[Repl.Topology.BackupServers]
			INNER JOIN [dbo].[C.BJobs] ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.BJobs].[db_instance_id]
			INNER JOIN [dbo].[view.Backup.BackupJobSessions] ON [dbo].[C.BJobs].[id] = [dbo].[view.Backup.BackupJobSessions].[job_id]
		WHERE
			[dbo].[view.Backup.BackupJobSessions].[result] = 1 AND 
			[dbo].[view.Backup.BackupJobSessions].[end_time] > @utc_time_from) as JobWarnings,
	(SELECT COUNT([dbo].[C.BJobs].[id]) 
		FROM [dbo].[Repl.Topology.BackupServers]
			INNER JOIN [dbo].[C.BJobs] ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.BJobs].[db_instance_id]
			INNER JOIN [dbo].[view.Backup.BackupJobSessions] ON [dbo].[C.BJobs].[id] = [dbo].[view.Backup.BackupJobSessions].[job_id]
		WHERE
			[dbo].[view.Backup.BackupJobSessions].[result] = 2 AND 
			[dbo].[view.Backup.BackupJobSessions].[end_time] > @utc_time_from) as JobErrors,
	--STATUS BOX
	(
		SELECT COUNT([dbo].[C.Backup.Model.JobSessions].[id])
		FROM [dbo].[Repl.Topology.BackupServers]
			INNER JOIN [dbo].[C.BJobs] ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.BJobs].[db_instance_id]
			INNER JOIN [dbo].[C.Backup.Model.JobSessions] ON [dbo].[C.BJobs].[id] = [dbo].[C.Backup.Model.JobSessions].[job_id]
			INNER JOIN [dbo].[C.Backup.Model.SbSessions] ON [dbo].[C.Backup.Model.JobSessions].[id] = [dbo].[C.Backup.Model.SbSessions].[id]
		WHERE 
			[dbo].[C.Backup.Model.JobSessions].[end_time] > @utc_time_from AND
			[dbo].[C.Backup.Model.JobSessions].[result] = 2 --ERROR
	) as BackupErrors
END	
GO
-----------------------------------------------------



			
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Dashboard.GetDataAllocation]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Dashboard.GetDataAllocation]
GO			   
CREATE PROCEDURE [dbo].[usp.Dashboard.GetDataAllocation]
AS
BEGIN
	-- Data Allocation
	SELECT 
		[dbo].[Repl.Topology.BackupServers].[id], 
		[dbo].[Repl.Topology.BackupServers].[display_name] as name, 
		q.[vms_total_size] as value					
	FROM [dbo].[Repl.Topology.BackupServers]
	INNER JOIN
	(
		SELECT db_instance_id, vms_total_size FROM [dbo].[fn.Backup.GetVmStats]()
	)q ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = q.[db_instance_id]
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Dashboard.GetActivity]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Dashboard.GetActivity]
GO
CREATE PROCEDURE [dbo].[usp.Dashboard.GetActivity](
	@period_hours int)
AS
BEGIN

	DECLARE @utc_time_current datetime, @utc_time_from datetime, @utc_time_min datetime
	SET @utc_time_current = GETUTCDATE()
	SET @utc_time_from = DATEADD(hour, -@period_hours, @utc_time_current)
	SET @utc_time_min = '09.09.1900 0:00:00'

	SELECT 
		[dbo].[Repl.Topology.BackupServers].[id],
		[dbo].[Repl.Topology.BackupServers].[display_name] as backup_server_name,
		@utc_time_from as time_from,
		@utc_time_current as time_till,
		0 as value
	FROM [dbo].[Repl.Topology.BackupServers]
	UNION ALL
	SELECT
		[dbo].[Repl.Topology.BackupServers].[id],
		[dbo].[Repl.Topology.BackupServers].[display_name] as backup_server_name,
		[dbo].[C.Backup.Model.BackupTaskSessions].[creation_time] as time_from,
		CASE
			WHEN [dbo].[C.Backup.Model.BackupTaskSessions].[end_time] < @utc_time_min THEN 
				(
					SELECT TOP(1) [dbo].[Repl.ServerSessions].[end_date]
					FROM [dbo].[Repl.ServerSessions]
						INNER JOIN [dbo].[Repl.SessionRelations] ON [dbo].[Repl.SessionRelations].[server_session_id] = [dbo].[Repl.ServerSessions].[id]
						INNER JOIN [dbo].[Enterprise.Sessions] ON [dbo].[Repl.SessionRelations].[parent_session_id] = [dbo].[Enterprise.Sessions].[id]
					WHERE 
						[dbo].[Repl.ServerSessions].[backup_server_id] = [dbo].[Repl.Topology.BackupServers].[id] AND
						[dbo].[Repl.ServerSessions].[end_date] IS NOT NULL AND
						[dbo].[Repl.ServerSessions].[status] = 1
					ORDER BY [dbo].[Repl.ServerSessions].[end_date] DESC
				)
			ELSE [dbo].[C.Backup.Model.BackupTaskSessions].[end_time]
		END as time_till,
		ISNULL(AVG([dbo].[C.Backup.Model.BackupTaskSessions].[avg_speed]), 0) as value
	FROM 
		[dbo].[Repl.Topology.BackupServers] INNER JOIN [dbo].[C.Backup.Model.BackupTaskSessions]
		ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.Backup.Model.BackupTaskSessions].[db_instance_id]	
	WHERE 
		[dbo].[C.Backup.Model.BackupTaskSessions].[creation_time] > @utc_time_from OR
		[dbo].[C.Backup.Model.BackupTaskSessions].[end_time] < @utc_time_min OR
		[dbo].[C.Backup.Model.BackupTaskSessions].[end_time] > @utc_time_from
	GROUP BY
		[dbo].[Repl.Topology.BackupServers].[id],
		[dbo].[Repl.Topology.BackupServers].[display_name],
		[dbo].[C.Backup.Model.BackupTaskSessions].[creation_time],
		[dbo].[C.Backup.Model.BackupTaskSessions].[end_time]
	ORDER BY time_from
	
END
GO
-----------------------------------------------------
		
		
		
		
			
			
			
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.AddBackupServer]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.AddBackupServer]
GO			
CREATE PROCEDURE [dbo].[usp.Config.AddBackupServer]
	@serverId uniqueidentifier,
	@displayName nvarchar(100),
	@description nvarchar(2000),
	@ipOrDnsName nvarchar(100),
	@port int,
	@domain nvarchar(100) = null,
	@login nvarchar(100) = null,
	@password nvarchar(1024) = null
AS
BEGIN
	SET NOCOUNT ON;
					
	INSERT INTO [dbo].[Repl.Topology.BackupServers]([id], [display_name], [description], [ip_or_dns_name], [port])
	VALUES (@serverId, @displayName, @description, @ipOrDnsName, @port)
	
	IF (@login <> '' AND @login IS NOT NULL AND @password IS NOT NULL )
	BEGIN
		INSERT INTO [dbo].[Repl.Topology.BackupServerCreds]([id], [backup_server], [domain], [login], [pwd])
		VALUES (NEWID(), @serverId, @domain, @login, @password)
	END
	
	DECLARE @usn bigint
	EXEC [dbo].[usp.Update.IncUsn] @usn OUT
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.UpdateBackupServer]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.UpdateBackupServer]
GO			
CREATE PROCEDURE [dbo].[usp.Config.UpdateBackupServer]
	@serverId uniqueidentifier,
	@displayName nvarchar(100),
	@description nvarchar(2000),
	@ipOrDnsName nvarchar(100),
	@port int,
	@domain nvarchar(100) = null,
	@login nvarchar(100) = null,
	@password nvarchar(1024) = null
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [dbo].[Repl.Topology.BackupServers]
		SET [display_name] = @displayName,
			[description] = @description,
			[ip_or_dns_name] = @ipOrDnsName,
			[port] = @port
		WHERE [id] = @serverId
		
	IF (@login IS NULL) 
		DELETE FROM [dbo].[Repl.Topology.BackupServerCreds] WHERE [backup_server] = @serverId
	ELSE 
	BEGIN 
		IF EXISTS (SELECT * FROM [dbo].[Repl.Topology.BackupServerCreds] WHERE [backup_server] = @serverId)				
			UPDATE [dbo].[Repl.Topology.BackupServerCreds]
				SET [domain] = @domain,
					[login] = @login,
					[pwd] = COALESCE(@password, bsc.pwd)
			FROM
				[dbo].[Repl.Topology.BackupServerCreds] bsc
			WHERE [backup_server] = @serverId
		ELSE
			INSERT INTO [dbo].[Repl.Topology.BackupServerCreds]([id], [backup_server], [domain], [login], [pwd])
			VALUES (NEWID(), @serverId, @domain, @login, @password)
	END

	DECLARE @usn bigint
	EXEC [dbo].[usp.Update.IncUsn] @usn OUT
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.UpdateBackupServerVersion]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.UpdateBackupServerVersion]
GO			
CREATE PROCEDURE [dbo].[usp.Config.UpdateBackupServerVersion]
	@serverId uniqueidentifier,
	@versionStr nvarchar(50),
	@versionMajor int
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [dbo].[Repl.Topology.BackupServers]
		SET [dbo].[Repl.Topology.BackupServers].[version] = @versionStr,
			[dbo].[Repl.Topology.BackupServers].[major_version] = @versionMajor
	WHERE [dbo].[Repl.Topology.BackupServers].[id] = @serverId
	
	DECLARE @usn bigint
	EXEC [dbo].[usp.Update.IncUsn] @usn OUT
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.RemoveBackupServer]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.RemoveBackupServer]
GO			
CREATE PROCEDURE [dbo].[usp.Config.RemoveBackupServer]
	@server_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [dbo].[Repl.Topology.BackupServerCreds] WHERE backup_server = @server_id
	DECLARE @vb_db_id uniqueidentifier;
	SELECT @vb_db_id = [dbo].[fn.BackupSrv.FindCurrentDbId]( @server_id )
	DELETE FROM [dbo].[Repl.Topology.BackupServers] WHERE [dbo].[Repl.Topology.BackupServers].[id] = @server_id;
	IF NOT EXISTS (SELECT id FROM [dbo].[Repl.Topology.BackupServers] WHERE [dbo].[Repl.Topology.BackupServers].[current_db_id] = @vb_db_id)
	BEGIN
		DELETE FROM [dbo].[Repl.Topology.BackupDbInstances] WHERE [dbo].[Repl.Topology.BackupDbInstances].[id] = @vb_db_id
	END
	
	DELETE FROM [dbo].[C.LicensedHosts] WHERE [dbo].[C.LicensedHosts].[db_instance_id] = @vb_db_id
	
	DECLARE @usn bigint
	EXEC [dbo].[usp.Update.IncUsn] @usn OUT
END
GO
-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.AddSearchServer]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.Catalog.AddSearchServer]
GO			
CREATE PROCEDURE [dbo].[usp.Config.Catalog.AddSearchServer]
	@serverId uniqueidentifier,	
	@ipOrDnsName nvarchar(50),
	@port int,
	@description nvarchar(2000),
	@oib_index_limit int,
	@domain nvarchar(100) = null,
	@login nvarchar(100) = null,
	@password nvarchar(1024) = null
AS
BEGIN
	SET NOCOUNT ON;
					
	INSERT INTO [dbo].[Catalog.SearchServers]([id], [ip_or_dns_name], [port], [description], [oibs_index_limit], [indexed_oibs_num])
	VALUES (@serverId, @ipOrDnsName, @port, @description, @oib_index_limit, 0 )
	
	IF (@login <> '' AND @login IS NOT NULL AND @password IS NOT NULL )
	BEGIN
		INSERT INTO [dbo].[Catalog.SearchServerCreds]([search_srv_id], [domain], [login], [pwd])
		VALUES ( @serverId, @domain, @login, @password)
	END
	
	DECLARE @usn bigint
	EXEC [dbo].[usp.Update.OnCatalogCfgChanged] @usn OUT
END
GO
-----------------------------------------------------



-- usp.Config.Catalog.UpdateSearchServer
-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.UpdateSearchServer]') AND type in (N'P'))
	DROP PROCEDURE [dbo].[usp.Config.Catalog.UpdateSearchServer]
GO			
CREATE PROCEDURE [dbo].[usp.Config.Catalog.UpdateSearchServer]
	@serverId uniqueidentifier,	
	@ipOrDnsName nvarchar(50),
	@port int,
	@description nvarchar(2000),
	@oib_index_limit int = 10000,
	@indexed_oibs_num int = 0,
	@used_sources_num int = 0,
	@domain nvarchar(100) = null,
	@login nvarchar(100) = null,
	@password nvarchar(1024) = null
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE [dbo].[Catalog.SearchServers]
		SET [ip_or_dns_name] = @ipOrDnsName,
			[port] = @port,
			[description] = @description,
			[oibs_index_limit] = @oib_index_limit,
			[indexed_oibs_num] = @indexed_oibs_num,
			[used_sources_num] = @used_sources_num			
		WHERE [id] = @serverId
		
	IF (@login IS NULL) 
		DELETE FROM [dbo].[Catalog.SearchServerCreds] WHERE [search_srv_id] = @serverId
	ELSE 
	BEGIN 
		IF EXISTS (SELECT * FROM [dbo].[Catalog.SearchServerCreds] WHERE [search_srv_id] = @serverId)
			UPDATE [dbo].[Catalog.SearchServerCreds]
				SET [domain] = @domain,
					[login] = @login,
					[pwd] = COALESCE(@password, ssc.pwd)
			FROM
				[dbo].[Catalog.SearchServerCreds] ssc
			WHERE [search_srv_id] = @serverId
		ELSE
			INSERT INTO [dbo].[Catalog.SearchServerCreds]([search_srv_id], [domain], [login], [pwd])
			VALUES (@serverId, @domain, @login, @password)
	END
	
	DECLARE @usn bigint
	EXEC [dbo].[usp.Update.OnCatalogCfgChanged] @usn OUT
END
GO
-----------------------------------------------------



-- usp.Config.Catalog.RemoveSearchServer
-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.RemoveSearchServer]') AND type in (N'P'))
	DROP PROCEDURE [dbo].[usp.Config.Catalog.RemoveSearchServer]
GO			
CREATE PROCEDURE [dbo].[usp.Config.Catalog.RemoveSearchServer]
	@serverId uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [dbo].[Catalog.SearchServerCreds] 
	WHERE [dbo].[Catalog.SearchServerCreds].[search_srv_id] = @serverId
	
	DELETE FROM [dbo].[Catalog.SearchServers] 
	WHERE [dbo].[Catalog.SearchServers].[id] = @serverId;
		
	DECLARE @usn bigint
	EXEC [dbo].[usp.Update.OnCatalogCfgChanged] @usn OUT
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.SetSearchServerUsageStat]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.Catalog.SetSearchServerUsageStat]
GO			
CREATE PROCEDURE [dbo].[usp.Config.Catalog.SetSearchServerUsageStat]
	@server_id uniqueidentifier,	
	@used_sources_num int,
    @effective_oibs_num int	
AS
BEGIN
	SET NOCOUNT ON;
					
	UPDATE [dbo].[Catalog.SearchServers]
    SET [indexed_oibs_num] = @effective_oibs_num,
  		  [used_sources_num] = @used_sources_num
	WHERE [id] = @server_id
	
	DECLARE @usn bigint
	EXEC [dbo].[usp.Update.OnCatalogCfgChanged] @usn OUT
END
GO
-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.RemoveSearchServer]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.Catalog.RemoveSearchServer]
GO			
CREATE PROCEDURE [dbo].[usp.Config.Catalog.RemoveSearchServer]
	@server_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [dbo].[Catalog.SearchServerCreds] WHERE search_srv_id = @server_id	
	DELETE FROM [dbo].[Catalog.SearchServerDelRequests] WHERE search_srv_id = @server_id
	DELETE FROM [dbo].[Catalog.SearchServers] WHERE id = @server_id
	
	
	DECLARE @usn bigint
	EXEC [dbo].[usp.Update.OnCatalogCfgChanged] @usn OUT
END
GO
-----------------------------------------------------



--
-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.MarkSearchSrvAsRemoving]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.Catalog.MarkSearchSrvAsRemoving]
GO			
CREATE PROCEDURE [dbo].[usp.Config.Catalog.MarkSearchSrvAsRemoving]
	@server_id uniqueidentifier,
	@cleanup_srv_sources bit
AS
BEGIN
	SET NOCOUNT ON;

	IF NOT EXISTS ( SELECT * FROM [dbo].[Catalog.SearchServerDelRequests] WHERE search_srv_id = @server_id)
		INSERT INTO [dbo].[Catalog.SearchServerDelRequests] ([search_srv_id], [cleanup_srv_sources])
		VALUES( @server_id, @cleanup_srv_sources)	
	
	DECLARE @usn bigint
	EXEC [dbo].[usp.Update.OnCatalogCfgChanged] @usn OUT
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.SetSearchSrvIndexedOibsNum]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.Catalog.SetSearchSrvIndexedOibsNum]
GO			
CREATE PROCEDURE [dbo].[usp.Config.Catalog.SetSearchSrvIndexedOibsNum]
	@server_id uniqueidentifier,
	@indexed_oibs_num int
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [dbo].[CatalogSearchServers]
	SET indexed_oibs_num = @indexed_oibs_num
	WHERE id = @server_id
	
		
	DECLARE @usn bigint
	EXEC [dbo].[usp.Update.OnCatalogCfgChanged] @usn OUT
END
GO

-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.EnumSearchServers]') AND type in (N'P'))
	DROP PROCEDURE [dbo].[usp.Config.Catalog.EnumSearchServers]
GO			
	CREATE PROCEDURE [dbo].[usp.Config.Catalog.EnumSearchServers]	
	AS
	BEGIN
		SET NOCOUNT ON;
		
		SELECT [id] as srv_id, [ip_or_dns_name], [port], [description], [oibs_index_limit], [indexed_oibs_num], [used_sources_num]
		FROM [dbo].[Catalog.SearchServers]
		
	END
GO

-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.EnumSearchSrvCreds]') AND type in (N'P'))
	DROP PROCEDURE [dbo].[usp.Config.Catalog.EnumSearchSrvCreds]
GO			
	CREATE PROCEDURE [dbo].[usp.Config.Catalog.EnumSearchSrvCreds]	
	AS
	BEGIN
		SET NOCOUNT ON;
		
		SELECT [search_srv_id] as srv_id, [domain], [login], [pwd]
		FROM [dbo].[Catalog.SearchServerCreds]
		
	END
GO

-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.EnumSearchSrvDelRequests]') AND type in (N'P'))
	DROP PROCEDURE [dbo].[usp.Config.Catalog.EnumSearchSrvDelRequests]
GO			
	CREATE PROCEDURE [dbo].[usp.Config.Catalog.EnumSearchSrvDelRequests]	
	AS
	BEGIN
		SET NOCOUNT ON;
		
		SELECT [search_srv_id] as srv_id, [cleanup_srv_sources]
		FROM [dbo].[Catalog.SearchServerDelRequests]		
	END
GO

-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.SureScalarSettingsInitialized]') AND type in (N'P'))
	DROP PROCEDURE [dbo].[usp.Config.Catalog.SureScalarSettingsInitialized]
GO			
	CREATE PROCEDURE [dbo].[usp.Config.Catalog.SureScalarSettingsInitialized]			
	AS
	BEGIN
		SET NOCOUNT ON;
		
		if NOT EXISTS (SELECT * FROM [dbo].[Catalog.ScalarSettings] )
		BEGIN
			INSERT INTO [dbo].[Catalog.ScalarSettings]( [id], [bsessions_retention_period], [allow_search_servers])
			VALUES( 0 , 3 , 1)
		END				
	END
GO

-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.SetBSessionsRetentionPeriod]') AND type in (N'P'))
	DROP PROCEDURE [dbo].[usp.Config.Catalog.SetBSessionsRetentionPeriod]
GO			
	CREATE PROCEDURE [dbo].[usp.Config.Catalog.SetBSessionsRetentionPeriod]	
		@retention_in_monthes int,
		@keep_index_for_archived_backups bit
	AS
	BEGIN
		SET NOCOUNT ON;
		
		EXEC [usp.Config.Catalog.SureScalarSettingsInitialized]
		
		UPDATE [dbo].[Catalog.ScalarSettings]
		SET 
			[bsessions_retention_period] = @retention_in_monthes,
			[keep_index4archived_backups] = @keep_index_for_archived_backups
		
		DECLARE @usn bigint
		EXEC [dbo].[usp.Update.OnCatalogCfgChanged] @usn OUT
	END
GO

-------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.GetBSessionsRetentionPeriod]') AND type in (N'P'))
	DROP PROCEDURE [dbo].[usp.Config.Catalog.GetBSessionsRetentionPeriod]
GO			
	CREATE PROCEDURE [dbo].[usp.Config.Catalog.GetBSessionsRetentionPeriod]			
	AS
	BEGIN
		SET NOCOUNT ON;
		
				
		SELECT  bsessions_retention_period, keep_index4archived_backups FROM [dbo].[Catalog.ScalarSettings]
		WHERE id = 0
				
	END
GO

-------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.SetAllowSearchServers]') AND type in (N'P'))
	DROP PROCEDURE [dbo].[usp.Config.Catalog.SetAllowSearchServers]
GO

	CREATE PROCEDURE [dbo].[usp.Config.Catalog.SetAllowSearchServers]	
		@allow_servers bit
	AS
	BEGIN
		SET NOCOUNT ON;

		EXEC [usp.Config.Catalog.SureScalarSettingsInitialized]
		
		UPDATE [dbo].[Catalog.ScalarSettings]
		SET [allow_search_servers] = @allow_servers
		
		DECLARE @usn bigint
		EXEC [dbo].[usp.Update.OnCatalogCfgChanged] @usn OUT
	END
GO

-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Catalog.IsAllowSearchServer]') AND type in (N'P'))
	DROP PROCEDURE [dbo].[usp.Config.Catalog.IsAllowSearchServer]
GO

	CREATE PROCEDURE [dbo].[usp.Config.Catalog.IsAllowSearchServer]
	AS
	BEGIN
		SET NOCOUNT ON;
		SELECT allow_search_servers FROM [dbo].[Catalog.ScalarSettings]
		WHERE id = 0
	END
GO


--

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Scheduler.GetSettings]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.Scheduler.GetSettings]
GO
CREATE PROCEDURE [dbo].[usp.Config.Scheduler.GetSettings]
	@settings_id uniqueidentifier = null			
AS
BEGIN
	SET NOCOUNT ON;

	IF @settings_id IS NULL
		SET @settings_id = 'D87CDAF7-483E-4128-B7C6-2C4A11CFE4EF';
		
	SELECT 
		[dbo].[Config.Scheduler.Options].id as settings_id,
		[dbo].[Config.Scheduler.Options].schedule_type,
		[dbo].[Config.Scheduler.Options].period_sec,
		[dbo].[Config.Scheduler.Options].period_type,
		[dbo].[Config.Scheduler.Options].start_time
	FROM
		[dbo].[Config.Scheduler.Options]
	WHERE
		[dbo].[Config.Scheduler.Options].id = @settings_id
END
GO		
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Scheduler.UpdateSettings]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.Scheduler.UpdateSettings]
GO
CREATE PROCEDURE [dbo].[usp.Config.Scheduler.UpdateSettings]
	@settings_id uniqueidentifier,
	@schedule_type int,
	@period_sec int,
	@period_type int
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE [dbo].[Config.Scheduler.Options] 
	SET 
		schedule_type = @schedule_type,
		period_sec = @period_sec,
		period_type = @period_type,
		start_time = GETUTCDATE()
		
	DECLARE @usn bigint
	EXEC [dbo].[usp.Update.IncUsn] @usn OUT
END
GO				
-----------------------------------------------------
		
		

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Scheduler.GetCatSyncSettings]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.Scheduler.GetCatSyncSettings]
GO
CREATE PROCEDURE [dbo].[usp.Config.Scheduler.GetCatSyncSettings]
AS
BEGIN
	SET NOCOUNT ON;
		
	SELECT TOP(1)
		[dbo].[Config.Scheduler.CatSyncSettings].id as settings_id,
		[dbo].[Config.Scheduler.CatSyncSettings].schedule_type,
		[dbo].[Config.Scheduler.CatSyncSettings].settings
	FROM
		[dbo].[Config.Scheduler.CatSyncSettings]
END
GO		
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Scheduler.UpdateCatSyncSettings]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.Scheduler.UpdateCatSyncSettings]
GO
CREATE PROCEDURE [dbo].[usp.Config.Scheduler.UpdateCatSyncSettings]
	@schedule_type int,
	@settings xml
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE [dbo].[Config.Scheduler.CatSyncSettings] 
	SET 
		schedule_type = @schedule_type,
		settings = @settings
		
	DECLARE @usn bigint
	EXEC [dbo].[usp.Update.IncUsn] @usn OUT
END
GO				
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Config.Scheduler.CreateCatSyncSettings]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Config.Scheduler.CreateCatSyncSettings]
GO
CREATE PROCEDURE [dbo].[usp.Config.Scheduler.CreateCatSyncSettings]
	@settings_id uniqueidentifier,
	@schedule_type int,
	@settings xml = N'<?xml version="1.0"?><settings><condition name="MinTimeAfterBJobComplete" enabled="true" period="15" period_type="0"></condition><condition name="MaxTimeAfterLastCatJobStarted" enabled="true" period="4" period_type="1"></condition></settings>'
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO [dbo].[Config.Scheduler.CatSyncSettings] ([id], [settings], [schedule_type])
	VALUES (@settings_id, @settings, @schedule_type )
END
GO		
-----------------------------------------------------
		
		
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Notification.GetSettings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Notification.GetSettings]
GO			
CREATE PROCEDURE [dbo].[usp.Notification.GetSettings]
AS
BEGIN
	SELECT [smtp_server]
		,[smtp_server_port]
		,[authentication]
		,[domain]
		,[username]
		,[password]
		,[to]
		,[from]
		,[send_enabled]
		,[subject]
		,[body]
		,[notif_period]
		,[web_site_url]
		,[lr_send]
		,[lr_from]
		,[lr_to]
		,[lr_subject]
		,[lr_state_pending]
		,[lr_state_ready]
		,[lr_state_cancelled]
		,[lr_state_approved]
		,[lr_state_failed]
		,[lr_state_stopped]
		,[flr_send]
		,[flr_from]
		,[flr_to]
		,[smtp_server_ssl]
	FROM [dbo].[Notification.MailSettings]
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Notification.UpdateSettings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Notification.UpdateSettings]
GO			
CREATE PROCEDURE [dbo].[usp.Notification.UpdateSettings]
	@smtp_server NVARCHAR(255),
	@smtp_server_port INT,
	@authentication BIT,
	@domain NVARCHAR(100),
	@username NVARCHAR(100),
	@password NVARCHAR(1024),
	@to NVARCHAR(max),
	@from NVARCHAR(max),	   
	@send_enabled BIT,
	@subject NVARCHAR(max),	   
	@body TEXT,
	@notif_period int,
	@web_site_url nvarchar(255),
	
	@lr_send bit,
	@lr_from nvarchar(1000),
	@lr_to nvarchar(1000),
	@lr_subject nvarchar(1000),
	@lr_state_pending bit,
	@lr_state_ready bit,
	@lr_state_cancelled bit,
	@lr_state_approved bit,
	@lr_state_failed bit,
	@lr_state_stopped bit,
	
	@flr_send bit,
	@flr_from nvarchar(1000),
	@flr_to nvarchar(1000),
	
	@smtp_server_ssl bit
AS
BEGIN
	IF NOT EXISTS( SELECT * FROM [dbo].[Notification.MailSettings] )
		INSERT INTO [dbo].[Notification.MailSettings]
				   ([smtp_server], [smtp_server_port],
					[authentication] ,[domain] ,[username] ,[password],
					[to], [from], [send_enabled], [subject], [body],
					[notif_period], [web_site_url],
					[lr_from],[lr_to],[lr_subject],
					[lr_state_pending],[lr_state_ready],[lr_state_cancelled],
					[lr_state_approved],[lr_state_failed],[lr_state_stopped],
					[lr_send], [flr_send], [flr_from], [flr_to], [smtp_server_ssl]
				   )
			 VALUES (
					@smtp_server,
					@smtp_server_port,
					@authentication,
					@domain,
					@username,
					@password,
					@to,
					@from,					   
					@send_enabled,
					@subject,
					@body,
					@notif_period,
					@web_site_url,
					@lr_from,
					@lr_to,
					@lr_subject,
					@lr_state_pending,
					@lr_state_ready,
					@lr_state_cancelled,
					@lr_state_approved,
					@lr_state_failed,
					@lr_state_stopped,
					@lr_send,
					@flr_send,
					@flr_from,
					@flr_to,
					@smtp_server_ssl
				   )
	ELSE
		UPDATE [dbo].[Notification.MailSettings]
		SET [smtp_server] = @smtp_server
			,[smtp_server_port] = @smtp_server_port
			,[authentication] = @authentication
			,[Domain] = @Domain
			,[username] = @username
			,[password] = @password
			,[to] = @to
			,[from] = @from
			,[send_enabled] = @send_enabled
			,[subject] = @subject
			,[body] = @body
			,[notif_period] = @notif_period
			,[web_site_url] = @web_site_url
			,[lr_from] = @lr_from
			,[lr_to] = @lr_to
			,[lr_subject] =	@lr_subject
			,[lr_state_pending] = @lr_state_pending
			,[lr_state_ready] = @lr_state_ready
			,[lr_state_cancelled] = @lr_state_cancelled
			,[lr_state_approved] = @lr_state_approved
			,[lr_state_failed] = @lr_state_failed
			,[lr_state_stopped]	= @lr_state_stopped
			,[lr_send] = @lr_send
			,[flr_send] = @flr_send
			,[flr_from] = @flr_from
			,[flr_to] = @flr_to
			,[smtp_server_ssl] = @smtp_server_ssl
					
END

GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Notification.OpenSession]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Notification.OpenSession]
GO
CREATE PROCEDURE [dbo].[usp.Notification.OpenSession]
	@session_id uniqueidentifier,
	@start_time datetime
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO [dbo].[Notification.Sessions] ([id], [start_time], [end_time], [status])
	VALUES (@session_id, @start_time, null, 0)
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Notification.CloseSession]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Notification.CloseSession]
GO
CREATE PROCEDURE [dbo].[usp.Notification.CloseSession]
	@session_id uniqueidentifier,
	@end_time datetime,
	@status int
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [dbo].[Notification.Sessions]
		SET 
			[end_time] = @end_time,
			[status] = @status
		WHERE [id] = @session_id
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Notification.GetJobNotificationVariablesValues]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Notification.GetJobNotificationVariablesValues]
GO
CREATE PROCEDURE [dbo].[usp.Notification.GetJobNotificationVariablesValues]
AS
BEGIN
	SET NOCOUNT ON;
		
	DECLARE @utc_time_current datetime, @utc_time_from datetime
	SET @utc_time_current = GETUTCDATE()
	SET @utc_time_from = DATEADD(hour, -24, @utc_time_current)

	SELECT
		ISNULL((SELECT SUM(count) FROM [dbo].[view.Notification.JobsData] 
			WHERE [dbo].[view.Notification.JobsData].[latest_result] = 0), 0) as success_backups,
		ISNULL((SELECT SUM(count) FROM [dbo].[view.Notification.JobsData] 
			WHERE [dbo].[view.Notification.JobsData].[latest_result] = 1), 0) as warning_backups,
		ISNULL((SELECT SUM(count) FROM [dbo].[view.Notification.JobsData] 
			WHERE [dbo].[view.Notification.JobsData].[latest_result] = 2), 0) as error_backups,

		(SELECT COUNT([dbo].[C.BJobs].[id]) 
			FROM [dbo].[Repl.Topology.BackupServers]
				INNER JOIN [dbo].[C.BJobs] ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.BJobs].[db_instance_id]
				INNER JOIN [dbo].[view.Backup.BackupJobSessions] ON [dbo].[C.BJobs].[id] = [dbo].[view.Backup.BackupJobSessions].[job_id]
			WHERE						
				[dbo].[view.Backup.BackupJobSessions].[result] = 0 AND 
				[dbo].[view.Backup.BackupJobSessions].[end_time] > @utc_time_from) as success_bjob_runs,

		(SELECT COUNT([dbo].[C.BJobs].[id]) 
			FROM [dbo].[Repl.Topology.BackupServers]
				INNER JOIN [dbo].[C.BJobs] ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.BJobs].[db_instance_id]
				INNER JOIN [dbo].[view.Backup.BackupJobSessions] ON [dbo].[C.BJobs].[id] = [dbo].[view.Backup.BackupJobSessions].[job_id]
			WHERE						
				[dbo].[view.Backup.BackupJobSessions].[result] = 1 AND 
				[dbo].[view.Backup.BackupJobSessions].[end_time] > @utc_time_from) as warning_bjob_runs,

		(SELECT COUNT([dbo].[C.BJobs].[id]) 
			FROM [dbo].[Repl.Topology.BackupServers]
				INNER JOIN [dbo].[C.BJobs] ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.BJobs].[db_instance_id]
				INNER JOIN [dbo].[view.Backup.BackupJobSessions] ON [dbo].[C.BJobs].[id] = [dbo].[view.Backup.BackupJobSessions].[job_id]
			WHERE
				[dbo].[view.Backup.BackupJobSessions].[result] = 2 AND 
				[dbo].[view.Backup.BackupJobSessions].[end_time] > @utc_time_from) as error_bjob_runs
END
GO					
-----------------------------------------------------







	
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Startup.EnumInitialReportPages]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Startup.EnumInitialReportPages]
GO
CREATE PROCEDURE [dbo].[usp.Startup.EnumInitialReportPages]	
	@labrequests_enabled as bit
AS
BEGIN			
	SET NOCOUNT ON;

	SELECT [Startup.InitialReportPages].[id],
	   [Startup.InitialReportPages].[container_id],
	   [Rendering.Containers].[display_name] as container_display_name,
	   [Startup.InitialReportPages].[report_id],
	   [Reports].[unique_name] as report_unique_name,
	   [Reports].[naviname_builder_id]
	FROM
	   ([Startup.InitialReportPages] INNER JOIN [Rendering.Containers] ON [Startup.InitialReportPages].[container_id] = [Rendering.Containers].[id])
	   INNER JOIN [Reports] 
	   ON [Startup.InitialReportPages].[report_id] = [Reports].[id]
	where 
		case [Reports].[unique_name]
			when 'cat_lab_requests' then @labrequests_enabled else 1		
		end = 1	 
	ORDER BY [Startup.InitialReportPages].[page_ordinal_number]
       
END
GO
-----------------------------------------------------



		
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Startup.EnumInitialReportAttrs]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Startup.EnumInitialReportAttrs]
GO	
CREATE PROCEDURE [usp.Startup.EnumInitialReportAttrs]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT [Startup.InitialReportAttrs].[initial_page_id] as report_page_id,
		   [Startup.InitialReportAttrs].[attr_name],
		   [Startup.InitialReportAttrs].[attr_value]
	FROM [Startup.InitialReportAttrs]	 
END
GO
-----------------------------------------------------







-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.C.BJobs.ResolveJobID]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.C.BJobs.ResolveJobID]
GO	
CREATE PROCEDURE [dbo].[usp.Data.C.BJobs.ResolveJobID]
	@job_id as uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT TOP(1) [dbo].[C.BJobs].[name] as job_name,
		[dbo].[C.BJobs].[type] as job_type,
		[dbo].[C.BJobs].[description] as job_description,
		[dbo].[C.BJobs].[schedule],
		[dbo].[C.BJobs].[options],
		[dbo].[C.BJobs].[schedule_enabled],
		[dbo].[C.BJobs].[db_instance_id]
	FROM [dbo].[C.BJobs]
	WHERE [dbo].[C.BJobs].[id] = @job_id

END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.SbTaskSessions.GetSbTaskSessions]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.SbTaskSessions.GetSbTaskSessions]
GO	
CREATE PROCEDURE [dbo].[usp.Data.SbTaskSessions.GetSbTaskSessions]
	@job_id as uniqueidentifier,
	@obj_id as uniqueidentifier
AS
BEGIN	
	select
		sbts.*
	from
		[dbo].[C.Backup.Model.JobSessions] js,
		[dbo].[C.Backup.Model.SbTaskSessions] sbts
	where
		js.id = sbts.drsession_id and
		js.job_id = @job_id  and
		sbts.object_id = @obj_id
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.SureBackup.GetLinkedBJobs]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.SureBackup.GetLinkedBJobs]
GO	
CREATE PROCEDURE [dbo].[usp.Data.SureBackup.GetLinkedBJobs]
	@sbjob_id as uniqueidentifier
AS
BEGIN

	IF EXISTS (
		SELECT 
			[dbo].[C.BJobs].[id]
		FROM [dbo].[C.BJobs]
		WHERE 
			[dbo].[C.BJobs].[id] = @sbjob_id AND
			[C.BJobs].[options].value('(/DRJobOptions/LinkWithJobs)[1]', 'bit') = 1
	)
	BEGIN
		SELECT 
			[dbo].[C.BJobs].[id] as bjob_id,
			[dbo].[C.BJobs].[name] as bjob_name
		FROM [dbo].[Repl.Topology.BackupServers]
			INNER JOIN [dbo].[C.BJobs] ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.BJobs].[db_instance_id]
			INNER JOIN [dbo].[C.LinkedJobs] ON [dbo].[C.BJobs].[id] = [dbo].[C.LinkedJobs].[linked_job_id]
		WHERE 
			[dbo].[C.LinkedJobs].[job_id] = @sbjob_id
	END
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.IsJobRunning]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.IsJobRunning]
GO	
CREATE PROCEDURE [dbo].[usp.Data.IsJobRunning]
	@job_id as uniqueidentifier
AS
BEGIN

	IF EXISTS (
		SELECT [dbo].[C.BJobs].[id]
		FROM [dbo].[Repl.Topology.BackupServers]
			INNER JOIN [dbo].[C.BJobs] ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.BJobs].[db_instance_id]
			INNER JOIN [dbo].[C.Backup.Model.JobSessions] ON [dbo].[C.BJobs].[id] = [dbo].[C.Backup.Model.JobSessions].[job_id]
		WHERE 
			[dbo].[C.BJobs].[id] = @job_id AND
			[dbo].[C.Backup.Model.JobSessions].[state] <> -1
		)
		SELECT 1
	ELSE
		SELECT 0
		
END
GO
-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.OIBs.GetOibById]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.OIBs.GetOibById]
GO	
CREATE PROCEDURE [dbo].[usp.Data.OIBs.GetOibById]
	@id as uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;
	
	select oib.* 
	from
		[dbo].[C.Backup.Model.OIBs] oib
	where
		oib.id = @id	

END
GO
-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.C.BObjects.GetObjectByOibId]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.C.BObjects.GetObjectByOibId]
GO	
CREATE PROCEDURE [dbo].[usp.Data.C.BObjects.GetObjectByOibId]
	@oib_id as uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;
	
	select obj.* 
	from
		[dbo].[C.BObjects] obj,
		[dbo].[C.Backup.Model.OIBs] oib
	where
		oib.object_id = obj.id and
		oib.id = @oib_id	

END
GO
-----------------------------------------------------



		
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.C.BJobs.MarkBJobAsStarting]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.C.BJobs.MarkBJobAsStarting]
GO
CREATE PROCEDURE [dbo].[usp.Data.C.BJobs.MarkBJobAsStarting]
	@job_id uniqueidentifier,
	@db_instance_id uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;

	UPDATE [dbo].[C.BJobs]
	SET [latest_result] = 3
	WHERE [id] = @job_id AND [db_instance_id] = @db_instance_id

	IF EXISTS (SELECT id FROM [dbo].[BJobs.Markers] WHERE [dbo].[BJobs.Markers].[job_id] = @job_id)
		UPDATE [dbo].[BJobs.Markers]
			SET [dbo].[BJobs.Markers].[latest_result] = 3,
				[dbo].[BJobs.Markers].[mark_time] = GETUTCDATE()
		WHERE [dbo].[BJobs.Markers].[job_id] = @job_id
	ELSE
		INSERT INTO [dbo].[BJobs.Markers](id, job_id, latest_result, mark_time)
		VALUES(NEWID(), @job_id, 3, GETUTCDATE())
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.C.BJobs.MarkBJobAsStopping]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.C.BJobs.MarkBJobAsStopping]
GO			
CREATE PROCEDURE [dbo].[usp.Data.C.BJobs.MarkBJobAsStopping]
	@job_id uniqueidentifier,
	@db_instance_id uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;
	
	UPDATE [dbo].[C.BJobs]
	SET [latest_result] = 4
	WHERE ([id] = @job_id AND [db_instance_id] = @db_instance_id) AND ( [latest_result] = 3 OR [latest_result] = 5 )

	IF EXISTS (SELECT id FROM [dbo].[BJobs.Markers] WHERE [dbo].[BJobs.Markers].[job_id] = @job_id)
		UPDATE [dbo].[BJobs.Markers]
			SET [dbo].[BJobs.Markers].[latest_result] = 4,
				[dbo].[BJobs.Markers].[mark_time] = GETUTCDATE()
		WHERE [dbo].[BJobs.Markers].[job_id] = @job_id AND [dbo].[BJobs.Markers].[latest_result]  <> -1
	ELSE
		INSERT INTO [dbo].[BJobs.Markers](id, job_id, latest_result, mark_time)
		VALUES(NEWID(), @job_id, 4, GETUTCDATE())
		
	DELETE FROM [dbo].[BJobs.Markers] WHERE [latest_result] = -1
END
GO
-----------------------------------------------------


		
	
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.Collecting.OpenSession]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.Collecting.OpenSession]
GO			
CREATE PROCEDURE [dbo].[usp.Data.Collecting.OpenSession]
	@session_id uniqueidentifier,
	@request_type int,
	@start_date datetime
AS
BEGIN	
	SET NOCOUNT ON;
					
	DECLARE @to_delete TABLE( session_id uniqueidentifier )
	INSERT INTO @to_delete (session_id )
		SELECT id FROM [dbo].[Enterprise.Sessions] WHERE request_type = 2
	
	INSERT INTO [dbo].[Enterprise.Sessions]([id], [request_type], [start_date], [status], [details], [ordinal_number])
	VALUES( 
		@session_id, 
		@request_type, 
		@start_date, 
		0, 
		N'', 
		CASE
			WHEN @request_type = 2 THEN 0
			ELSE 1
		END)
	IF @request_type = 2 -- JOB ACTIVITY
	BEGIN
		DELETE FROM [dbo].[Enterprise.Sessions]
			WHERE [dbo].[Enterprise.Sessions].[id] IN (SELECT session_id FROM @to_delete)
		DELETE FROM [dbo].[Repl.SessionRelations]
			WHERE [dbo].[Repl.SessionRelations].[parent_session_id] IN (SELECT session_id FROM @to_delete)
	END
END
GO	
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.Collecting.CloseSession]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.Collecting.CloseSession]
GO			
CREATE PROCEDURE [dbo].[usp.Data.Collecting.CloseSession]
	@session_id uniqueidentifier,
	@end_date datetime,
	@status int,
	@details nvarchar(max)
AS
BEGIN	
	SET NOCOUNT ON;

	UPDATE [dbo].[Enterprise.Sessions]
		SET [dbo].[Enterprise.Sessions].[end_date] = @end_date,
			[dbo].[Enterprise.Sessions].[status] = @status,
			[dbo].[Enterprise.Sessions].[details] = @details
		WHERE id = @session_id;
END
GO	
-----------------------------------------------------
		
		
		
		
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.Collecting.OpenServerSession]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.Collecting.OpenServerSession]
GO			
CREATE PROCEDURE [dbo].[usp.Data.Collecting.OpenServerSession]
	@session_id uniqueidentifier,
	@parent_session_id uniqueidentifier,
	@backup_server_id uniqueidentifier,
	@start_date datetime,
	@comment nvarchar(max)
AS
BEGIN
	INSERT INTO [dbo].[Repl.ServerSessions]([id], [backup_server], [backup_server_id], [status], [start_date], [end_date], [comment])
	(
		select @session_id, backup_servers.display_name, backup_servers.id, 0, @start_date, null, @comment from [dbo].[Repl.Topology.BackupServers] backup_servers
		where backup_servers.id = @backup_server_id
	)
	
	INSERT INTO [dbo].[Repl.SessionRelations]([id], [parent_session_id], [server_session_id])
	VALUES( NEWID(), @parent_session_id, @session_id)
END
GO	
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.Collecting.CloseServerSession]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.Collecting.CloseServerSession]
GO
CREATE PROCEDURE [dbo].[usp.Data.Collecting.CloseServerSession]
	@session_id uniqueidentifier,
	@status int,
	@end_date datetime,
	@comment nvarchar(max)
AS
BEGIN	
	UPDATE [dbo].[Repl.ServerSessions]
		SET [dbo].[Repl.ServerSessions].[status] = @status,
			[dbo].[Repl.ServerSessions].[end_date] = @end_date,
			[dbo].[Repl.ServerSessions].[comment] = @comment						
			
		WHERE id = @session_id
END
GO		
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.Collecting.GetServersWithJobActivity]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.Collecting.GetServersWithJobActivity]
GO			
CREATE PROCEDURE [dbo].[usp.Data.Collecting.GetServersWithJobActivity]
	@is_collecting bit = 0 OUT
AS
BEGIN

	select distinct * from
	(
	SELECT [dbo].[Repl.Topology.BackupServers].[id] as backup_server_id 
	FROM 
		[dbo].[Repl.Topology.BackupServers]
		INNER JOIN [dbo].[Repl.Topology.BackupDbInstances]  ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[Repl.Topology.BackupDbInstances].[id]
		INNER JOIN [dbo].[view.Backup.BackupJobSessions] ON [dbo].[Repl.Topology.BackupDbInstances].[id] = [dbo].[view.Backup.BackupJobSessions].[db_instance_id]
		INNER JOIN [dbo].[C.BJobs] ON [dbo].[view.Backup.BackupJobSessions].[job_id] = [dbo].[C.BJobs].[id]
	WHERE 
		[dbo].[view.Backup.BackupJobSessions].[state] IN (3, 4, 5, 6, 7) --'Starting','Stopping','Working','Pausing','Resuming'.
		OR [dbo].[C.BJobs].[latest_result] = 3
	GROUP BY 
		[dbo].[Repl.Topology.BackupServers].[id]
	union	
	select 
		lrs_approved.aux_data.value('(/CLabRequestStateAuxDataApproved/BackupServerId)[1]', 'uniqueidentifier')  backup_server_id
	from 
		[dbo].[Air.LabRequests] lr,
		[dbo].[Air.LabRequestStates] lrs_current,
		[dbo].[Air.LabRequestStates] lrs_approved
	where 
		lr.state_id = lrs_current.id
		and lrs_current.state not in (
		0, --pending
		1, --cancelled
		6, --stopped
		7 --failed	
		) and
		lrs_approved.id = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lr.id, 2) --approved
	) bs(backup_server_id) 


	SET @is_collecting = 
	(
		SELECT Count(*)
		FROM 
		(
			SELECT TOP(3)
				id, [dbo].[Enterprise.Sessions].[end_date]
			FROM [dbo].[Enterprise.Sessions]
			ORDER BY [dbo].[Enterprise.Sessions].[start_date] DESC
		) as q
		WHERE q.end_date IS NULL
	)
	
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.Collecting.RemoveOldSessions]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.Collecting.RemoveOldSessions]
GO			
CREATE PROCEDURE [dbo].[usp.Data.Collecting.RemoveOldSessions]
	@min_date_time datetime,
	@removed_count int OUT
AS
BEGIN
	DECLARE @tmp table (removed_session_id uniqueidentifier)

	DELETE FROM [dbo].[Enterprise.Sessions]
	OUTPUT DELETED.id INTO @tmp
	WHERE 
		[dbo].[Enterprise.Sessions].[end_date] < @min_date_time

	SET @removed_count = (SELECT Count(*) FROM @tmp)
END
GO		
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.Collecting.RemoveVbServerData]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.Collecting.RemoveVbServerData]
GO			
CREATE PROCEDURE [dbo].[usp.Data.Collecting.RemoveVbServerData]
	@old_db_instance_id uniqueidentifier
AS
BEGIN

	DELETE FROM [dbo].[C.Folder_Host]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.ObjectsInJobs]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.Soap_creds]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.BJobs]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.BObjects]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.Backup.Model.Points]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.Ssh_creds]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.LicensedHosts]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.Hosts]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.Folders]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.Backup.Model.Backups]
	WHERE db_instance_id = @old_db_instance_id		
	DELETE FROM [dbo].[C.Backup.Model.Storages]
	WHERE db_instance_id = @old_db_instance_id	
	DELETE FROM [dbo].[C.HostsByJobs]
	WHERE db_instance_id = @old_db_instance_id	
	DELETE FROM [dbo].[C.Backup.Model.OIBs]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.VirtualLabs]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.ObjectsInApplicationGroups]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.DRRoles]
	WHERE db_instance_id = @old_db_instance_id	
	DELETE FROM [dbo].[C.Backup.Model.SbTaskSessions]
	WHERE db_instance_id = @old_db_instance_id	
	DELETE FROM [dbo].[C.Backup.Model.JobSessions]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.Backup.Model.SbSessions]
	WHERE db_instance_id = @old_db_instance_id	
	DELETE FROM [dbo].[C.Backup.Model.BackupJobSessions]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[C.Backup.Model.BackupTaskSessions]
	WHERE db_instance_id = @old_db_instance_id		
	DELETE FROM [dbo].[C.LinkedJobs]
	WHERE db_instance_id = @old_db_instance_id		
	DELETE FROM [dbo].[C.SbVerificationRules]
	WHERE db_instance_id = @old_db_instance_id

	--------------------------------------------
	-- OLD TABLES	
	DELETE FROM [dbo].[Backups]
	WHERE db_instance_id = @old_db_instance_id
	DELETE FROM [dbo].[ObjectsInBackups]
	WHERE db_instance_id = @old_db_instance_id
	--
	--------------------------------------------
	
	
	/*
	UPDATE [dbo].[Repl.Topology.BackupDbInstances]
		SET 
			[dbo].[Repl.Topology.BackupDbInstances].[id] = new_db_instance_id,
			[dbo].[Repl.Topology.BackupDbInstances].[last_sync_usn] = 0,
			[dbo].[Repl.Topology.BackupDbInstances].[update_usn] = 0
	WHERE [dbo].[Repl.Topology.BackupDbInstances].[id] = old_db_instance_id
	*/
	DELETE FROM [dbo].[Repl.Topology.BackupDbInstances]
	WHERE [dbo].[Repl.Topology.BackupDbInstances].[id] = @old_db_instance_id
	
	UPDATE [dbo].[Repl.Topology.BackupServers]
		SET [dbo].[Repl.Topology.BackupServers].[current_db_id] = null
	WHERE [dbo].[Repl.Topology.BackupServers].[current_db_id] = @old_db_instance_id
END
GO		
-----------------------------------------------------
	
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.Collecting.SetNewColumnsDataCollected]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.Collecting.SetNewColumnsDataCollected]
GO			
CREATE PROCEDURE [dbo].[usp.Data.Collecting.SetNewColumnsDataCollected]
	@db_instance_id uniqueidentifier
AS
BEGIN	
	IF NOT EXISTS(SELECT * FROM [dbo].[Repl.Scheme.Collect] c WHERE c.db_instance_id = @db_instance_id)
	BEGIN 
		INSERT INTO [dbo].[Repl.Scheme.Collect](db_instance_id, collect_new_columns) VALUES (@db_instance_id, 'false') 
	END
	ELSE BEGIN 
		UPDATE [dbo].[Repl.Scheme.Collect] SET collect_new_columns = 'false' WHERE db_instance_id = @db_instance_id
	END
END
GO

-----------------------------------------------------
	
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.Collecting.NeedCollectNewColumnsData]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.Collecting.NeedCollectNewColumnsData]
GO			
CREATE PROCEDURE [dbo].[usp.Data.Collecting.NeedCollectNewColumnsData]
	@db_instance_id uniqueidentifier
AS
BEGIN	
		SELECT ISNULL(collect_new_columns, 'true') as needsCollect 
		FROM [dbo].[Repl.Topology.BackupServers] bs 
        LEFT OUTER JOIN [dbo].[Repl.Scheme.Collect] ct ON bs.current_db_id = ct.db_instance_id 
		WHERE bs.current_db_id = @db_instance_id
END
GO

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usp.Data.Collecting.FindVbSrvNameInCatalogByBackupId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.Collecting.FindVbSrvNameInCatalogByBackupId]
GO			
CREATE PROCEDURE [dbo].[usp.Data.Collecting.FindVbSrvNameInCatalogByBackupId]
	@backupId uniqueidentifier
AS
BEGIN

	SELECT [dbo].[Repl.Topology.BackupServers].[name_in_catalog]
	FROM [dbo].[C.Backup.Model.Backups]
	INNER JOIN [dbo].[Repl.Topology.BackupDbInstances] ON [dbo].[C.Backup.Model.Backups].[db_instance_id] = [dbo].[Repl.Topology.BackupDbInstances].[id]
	INNER JOIN [dbo].[Repl.Topology.BackupServers] ON [dbo].[Repl.Topology.BackupDbInstances].[id] = [dbo].[Repl.Topology.BackupServers].[current_db_id]
	WHERE [dbo].[C.Backup.Model.Backups].[id] = @backupId
END
GO
-----------------------------------------------------



		
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Dashboard.GetSettings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Dashboard.GetSettings]
GO			
CREATE PROCEDURE [dbo].[usp.Dashboard.GetSettings]
AS
BEGIN
	SELECT 	[show_backup_window],
			[backup_window_from_min],
			[backup_window_length_min],
			[activity_graph_scale],
			[no_file_restore_downloads],
			[flr_restricted_by_ext], 
			[flr_ext_restrictions]
	FROM [dbo].[DashboardSettings]
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.C.BJobs.GetLatestBJobActivityTime]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.C.BJobs.GetLatestBJobActivityTime]
GO
CREATE PROCEDURE [dbo].[usp.Data.C.BJobs.GetLatestBJobActivityTime]		
AS
BEGIN

SELECT TOP(1) 
	CASE 
		WHEN [dbo].[C.Backup.Model.JobSessions].[state] <> -1  THEN GETUTCDATE() 
		ELSE [dbo].[C.Backup.Model.JobSessions].[end_time]
	END as last_bjob_activity
FROM [dbo].[C.Backup.Model.JobSessions]
WHERE 
	[dbo].[C.Backup.Model.JobSessions].[job_type] = 0
ORDER BY [dbo].[C.Backup.Model.JobSessions].[creation_time] DESC

END
GO
-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.C.BJobs.GetLatestBJobCompleteTime]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.C.BJobs.GetLatestBJobCompleteTime]
GO
CREATE PROCEDURE [dbo].[usp.Data.C.BJobs.GetLatestBJobCompleteTime]		
AS
BEGIN

SELECT TOP(1) 
	[dbo].[C.Backup.Model.JobSessions].[end_time] as last_bjob_completed
FROM [dbo].[C.Backup.Model.JobSessions]
WHERE 
	[dbo].[C.Backup.Model.JobSessions].[job_type] = 0 AND
	[dbo].[C.Backup.Model.JobSessions].[state] = -1
ORDER BY [dbo].[C.Backup.Model.JobSessions].[creation_time] DESC

END
GO
-----------------------------------------------------






-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.EnterpriseSessions.GetLatestCatSyncActivityTime]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.EnterpriseSessions.GetLatestCatSyncActivityTime]
GO
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.GetLatestCatSyncActivityTime]
AS
BEGIN

	SELECT
		CASE
			WHEN q.[status] = 0 THEN 1
			ELSE 0
		END as is_in_progress,
		q.[end_date] as last_cat_sync_activity
	FROM
		(SELECT TOP(1) [dbo].[Enterprise.Sessions].[status], [dbo].[Enterprise.Sessions].[end_date]
		FROM [dbo].[Enterprise.Sessions]
		WHERE [dbo].[Enterprise.Sessions].[job_type] = 1 OR 
			[dbo].[Enterprise.Sessions].[job_type] = 2
		ORDER BY [dbo].[Enterprise.Sessions].[start_date] DESC) as q
		
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.EnterpriseSessions.GetEffectiveSessionResult]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.EnterpriseSessions.GetEffectiveSessionResult]
GO
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.GetEffectiveSessionResult]
	@session_id uniqueidentifier
AS
BEGIN
	SELECT 
		MAX(
			CASE [severity] 
				WHEN 4 THEN 1
				ELSE [severity]
			END
		) as result
	FROM [dbo].[Enterprise.SessionEvents]
	WHERE [session_id] = @session_id
END
GO
-----------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.EnterpriseSessions.GetEffectiveSessionResultExt]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.EnterpriseSessions.GetEffectiveSessionResultExt]
GO
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.GetEffectiveSessionResultExt]
	@session_id uniqueidentifier
AS
BEGIN
	SELECT t1.result, t1.text
	FROM
	(	
		SELECT 
			t.result, 
			se.text, 
			ROW_NUMBER() OVER (ORDER BY event_time ASC) as number
		FROM [dbo].[Enterprise.SessionEvents] se
		JOIN 
		(
			SELECT session_id,
				MAX(
					CASE [severity] 
						WHEN 4 THEN 1
						ELSE [severity]
					END
				) as result
			FROM [dbo].[Enterprise.SessionEvents]	
			WHERE session_id = @session_id
			GROUP BY session_id
		) t
		ON se.session_id = t.session_id AND se.severity = t.result
	) t1
	WHERE t1.number = 1
END
GO

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.EnterpriseSessions.GetRunning]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.EnterpriseSessions.GetRunning]
GO
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.GetRunning]
AS
BEGIN
	SELECT *
	FROM [dbo].[Enterprise.Sessions]
	WHERE [status] = 0 AND [job_type] IN (4)
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Dashboard.UpdateSettings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Dashboard.UpdateSettings]
GO			
CREATE PROCEDURE [dbo].[usp.Dashboard.UpdateSettings]
	@show_backup_window bit,
	@backup_window_from_min int,
	@backup_window_length_min int,
	@activity_graph_scale int,
	@no_file_restore_downloads bit,
	@flrRestrictedByExt bit,
	@flrExtRestrictions nvarchar(1024)
AS
BEGIN
	IF NOT EXISTS( SELECT * FROM [dbo].[DashboardSettings] )
		INSERT INTO [dbo].[DashboardSettings] ([show_backup_window], [backup_window_from_min], [backup_window_length_min], [activity_graph_scale], [no_file_restore_downloads], [flr_restricted_by_ext], [flr_ext_restrictions])
		VALUES (@show_backup_window, @backup_window_from_min, @backup_window_length_min, @activity_graph_scale, @no_file_restore_downloads, @flrRestrictedByExt, @flrExtRestrictions )
	ELSE
		UPDATE [dbo].[DashboardSettings]
		SET [show_backup_window] = @show_backup_window,
			[backup_window_from_min] = @backup_window_from_min,
			[backup_window_length_min] = @backup_window_length_min,
			[activity_graph_scale] = @activity_graph_scale,
			[no_file_restore_downloads] = @no_file_restore_downloads,
			[flr_restricted_by_ext] = @flrRestrictedByExt,
			[flr_ext_restrictions] = @flrExtRestrictions
END
GO
-----------------------------------------------------



 




---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Backup.GetBObjectName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Backup.GetBObjectName]
GO	
CREATE FUNCTION [dbo].[fn.Backup.GetBObjectName]
(
	@object_id uniqueidentifier,
	@db_instance_id uniqueidentifier
)
RETURNS nvarchar( 2000 )
AS
BEGIN
	DECLARE @retVal as nvarchar( 2000 )
	
	SELECT @retVal = [dbo].[C.BObjects].[object_name]
	FROM [dbo].[C.BObjects]
	WHERE [dbo].[C.BObjects].[id] = @object_id AND
		  [dbo].[C.BObjects].[db_instance_id] = @db_instance_id

	IF @retVal is NULL
		SELECT @retVal = N'Unknown'

	
	RETURN @retVal;

END
GO
---------------------------------------------------



---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Backup.GetBackupPath]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Backup.GetBackupPath]
GO	
CREATE FUNCTION [dbo].[fn.Backup.GetBackupPath]
(
	@backup_id uniqueidentifier,
	@db_instance_id uniqueidentifier
)
RETURNS nvarchar( 255 )
AS
BEGIN	
	DECLARE @retVal nvarchar( 255 )

	SELECT TOP(1) @retVal = [dbo].[Backups].[file_name]
	FROM [dbo].[Backups]
	WHERE [dbo].[Backups].[id] = @backup_id AND
		  [dbo].[Backups].[db_instance_id] = @db_instance_id

	IF @retVal IS NULL
		SELECT @retVal = N'Unknown'

	RETURN @retVal

END
GO
---------------------------------------------------


---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Backup.GetJobName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	BEGIN
		DROP FUNCTION [dbo].[fn.Backup.GetJobName]
	END
GO	
	CREATE FUNCTION [dbo].[fn.Backup.GetJobName]
	(
		@backup_id uniqueidentifier,
		@db_instance_id uniqueidentifier
	)
	RETURNS nvarchar( 255 )
	AS
	BEGIN	
		DECLARE @retVal nvarchar( 255 )

		SELECT TOP(1) @retVal = [dbo].[Backups].[job_name]
		FROM [dbo].[Backups]
		WHERE [dbo].[Backups].[id] = @backup_id AND
			  [dbo].[Backups].[db_instance_id] = @db_instance_id

		IF @retVal IS NULL
			SELECT @retVal = N'Unknown'

		RETURN @retVal

	END
GO
---------------------------------------------------


---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.License.GetLicensedHostsAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.License.GetLicensedHostsAll]
GO			
CREATE PROCEDURE [dbo].[usp.License.GetLicensedHostsAll]
AS
BEGIN
	select 
		lh.* 
	from
		[dbo].[C.LicensedHosts] lh,
		(select id, ROW_NUMBER() over (PARTITION BY uuid ORDER BY uuid) rn from [dbo].[C.LicensedHosts]) lhp
	where lh.id = lhp.id and
		  lhp.rn = 1
END
GO
---------------------------------------------------


---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.License.GetBServerUidForLicensedHost]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.License.GetBServerUidForLicensedHost]
GO			
CREATE PROCEDURE [dbo].[usp.License.GetBServerUidForLicensedHost]
	@host_uid nvarchar(255)
AS
BEGIN
	SELECT [dbo].[Repl.Topology.BackupServers].[id] as server_id 
	FROM [dbo].[C.LicensedHosts] 
	INNER JOIN [dbo].[Repl.Topology.BackupServers] ON [dbo].[C.LicensedHosts].[db_instance_id]=[dbo].[Repl.Topology.BackupServers].[current_db_id]
	WHERE [dbo].[C.LicensedHosts].[uuid]=@host_uid
END
GO
---------------------------------------------------


---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Startup.ChangeInitialPageOrders]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Startup.ChangeInitialPageOrders]
GO			
CREATE PROCEDURE [dbo].[usp.Startup.ChangeInitialPageOrders]
AS
BEGIN
	UPDATE [dbo].[Startup.InitialReportPages]
		SET [dbo].[Startup.InitialReportPages].[page_ordinal_number]=
			CASE [dbo].[Startup.InitialReportPages].[page_ordinal_number]
				WHEN 3 THEN 7
				WHEN 4 THEN 9
				WHEN 6 THEN 11
				ELSE [dbo].[Startup.InitialReportPages].[page_ordinal_number]
			END
END		
GO
---------------------------------------------------


---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetLabRequestsForUser]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetLabRequestsForUser]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetLabRequestsForUser]
	@sid nvarchar(100)
AS
BEGIN
	select lr.* from
		[dbo].[Air.LabRequests] lr
	where 
		lr.sid = @sid
END
GO
---------------------------------------------------



---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetLabRequest]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetLabRequest]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetLabRequest]
	@id uniqueidentifier
AS
BEGIN
	select lr.* from
		[dbo].[Air.LabRequests] lr
	where 
		lr.id = @id
END
GO
---------------------------------------------------


---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.AddLabRequest]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.AddLabRequest]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.AddLabRequest]
	@id uniqueidentifier,
	@req_vmname nvarchar(1000),
    @req_vmtype int,
    @req_date datetime,
    @sid nvarchar(50),
    @user nvarchar(1000),
    @description nvarchar(4000),
    @preferred_date datetime,
    @preferred_duration_sec bigint,
    @queue_num int OUTPUT,
    @aux_data xml,
    @created_date datetime
AS
BEGIN

	INSERT INTO [dbo].[Air.LabRequests]
		([id], [req_vmname], [req_vmtype], [req_date], [sid], [user], [description], [preferred_date], [preferred_duration_sec], [aux_data])
	VALUES
		(
		@id,
		@req_vmname,
		@req_vmtype,
		@req_date,
		@sid,
		@user,
		@description,
		@preferred_date,
		@preferred_duration_sec,
		@aux_data
		)
		
	select @queue_num = queue_num from [dbo].[Air.LabRequests] where id = @id
	
	declare @stid uniqueidentifier
	exec [dbo].[usp.LabRequests.SetLabRequestState] @stid OUTPUT, @id, @created_date, 0, N'', '<CLabRequestStateAuxDataPending xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />'
END
GO
---------------------------------------------------





---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.SetLabRequestState]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.SetLabRequestState]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.SetLabRequestState]
	@id uniqueidentifier OUTPUT,
    @request_id uniqueidentifier,
    @date datetime,
    @state int,
    @description nvarchar(4000),
    @aux_data xml
AS
BEGIN

	declare @stid uniqueidentifier
	set @stid = newid()
	
	INSERT INTO [dbo].[Air.LabRequestStates]
	([id], [request_id], [date], [state], [description], [aux_data])
	VALUES
		(		
		@stid,
		@request_id,
		@date,
		@state,
		@description,
		@aux_data
		)
			
	update
		[dbo].[Air.LabRequests]
	set 
		state_id = @stid
	where
		id = @request_id
	
	set @id = @stid
	
END
GO
---------------------------------------------------



---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.UpdateLabRequestState]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.UpdateLabRequestState]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.UpdateLabRequestState]
	@id uniqueidentifier,
    @request_id uniqueidentifier,
    @date datetime,
    @state int,
    @description nvarchar(4000),
    @aux_data xml
AS
BEGIN

	UPDATE
		[dbo].[Air.LabRequestStates]
	SET
		[request_id] = @request_id,
		[date]  = @date,
		[state] = @state,
		[description] = @description,
		[aux_data] = @aux_data
	where
		id = @id

END
GO
---------------------------------------------------



---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetLabRequestStateByRequest]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetLabRequestStateByRequest]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetLabRequestStateByRequest]
    @request_id uniqueidentifier
AS
BEGIN
	select top 1 lrst.* from 
		[dbo].[Air.LabRequestStates] lrst,
		[dbo].[Air.LabRequests] lr
	where 
		lr.state_id = lrst.id and
		lr.id = @request_id
END
GO
---------------------------------------------------



---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetLabRequestStates]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetLabRequestStates]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetLabRequestStates]
    @request_id uniqueidentifier
AS
BEGIN
	select lrst.* from 
		[dbo].[Air.LabRequests] lr,
		[dbo].[Air.LabRequestStates] lrst
	where 
		lr.id = lrst.request_id and
		lr.id = @request_id
	order by num
END
GO
---------------------------------------------------




---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetLabRequestState]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetLabRequestState]
GO
CREATE PROCEDURE [dbo].[usp.LabRequests.GetLabRequestState]
	@id uniqueidentifier
AS
BEGIN	

	SELECT *
	FROM [dbo].[Air.LabRequestStates]
	where
		id = @id
	
END
GO
---------------------------------------------------



---------------------------------------------------
--
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.GetLabRequestLastStateId]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.GetLabRequestLastStateId]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.GetLabRequestLastStateId]
(
	@request_id uniqueidentifier,
	@state int
)
RETURNS uniqueidentifier
AS
BEGIN	
	DECLARE @id uniqueidentifier

	SELECT top 1 @id = id
	FROM [dbo].[Air.LabRequestStates]
	where
		request_id = @request_id and
		state = @state
	order by num desc
	
	RETURN @id

END
GO
---------------------------------------------------


---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetLabRequestStateLog]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetLabRequestStateLog]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetLabRequestStateLog]
    @state_id uniqueidentifier
AS
BEGIN

	declare @log nvarchar(max)
	declare @state int

	select @state = state 
	from 
		[dbo].[Air.LabRequestStates]
	where
		id = @state_id
		
	if @state = 0 --Pending
	begin
		exec @log = [dbo].[fn.LabRequests.LabRequestStateLogPending] @state_id
	end

	if @state = 1 --Cancelled
	begin
		exec @log = [dbo].[fn.LabRequests.LabRequestStateLogCancelled] @state_id
	end

	if @state = 2 --Approved
	begin
		exec @log = [dbo].[fn.LabRequests.LabRequestStateLogApproved] @state_id
	end

	if @state = 3 --Preparing
	begin
		exec @log = [dbo].[fn.LabRequests.LabRequestStateLogPreparing] @state_id
	end

	if @state = 4 --Ready
	begin
		exec @log = [dbo].[fn.LabRequests.LabRequestStateLogReady] @state_id
	end

	if @state = 5 --Stopping
	begin
		exec @log = [dbo].[fn.LabRequests.LabRequestStateLogStopping] @state_id
	end

	if @state = 6 --Stopped
	begin
		exec @log = [dbo].[fn.LabRequests.LabRequestStateLogStopped] @state_id
	end
	    
	if @state = 7 --Failed
	begin
		exec @log = [dbo].[fn.LabRequests.LabRequestStateLogFailed] @state_id
	end
	
	select @state_id state_id, @log log

END
GO
---------------------------------------------------







---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.GetVmRecoveryPoints]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.GetVmRecoveryPoints]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.GetVmRecoveryPoints]
(
	@obj_id uniqueidentifier,
	@job_type int = null-- OR NULL
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT DISTINCT 
		oibs.original_oib_id,
		oibs.creation_time
	FROM 
		[dbo].[C.BObjects] bobj
		INNER JOIN [dbo].[C.Backup.Model.OIBs] oibs ON 	bobj.id = oibs.object_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
		INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON oibs.[db_instance_id] = bservers.[current_db_id]
	WHERE 
	 oibs.is_corrupted = 0 AND
	 bobj.id = @obj_id AND
	 (
	  (bobj.platform = 0 AND backups.job_target_type IN (0, 1)) OR
	  (bobj.platform = 1 AND backups.job_target_type = 0)
	 ) AND
	 (@job_type IS NULL OR backups.job_target_type = @job_type)	
)
GO
---------------------------------------------------


---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.GetVmRecoveryPointsCount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.GetVmRecoveryPointsCount]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.GetVmRecoveryPointsCount]
(
	@obj_id uniqueidentifier,
	@job_type int = null--OR NULL
)
RETURNS int 
AS
BEGIN
	DECLARE @pointsCount as int
	SELECT @pointsCount = count(*) from [dbo].[fn.LabRequests.GetVmRecoveryPoints](@obj_id, @job_type)
	RETURN @pointsCount;
END	
GO
---------------------------------------------------



---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetRecoveryVmData]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetRecoveryVmData]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetRecoveryVmData]
	@vmname nvarchar(1000),
	@job_type int = null--OR NULL
AS
BEGIN

	SELECT DISTINCT
		bobj.id, 
		bobj.object_name, 
		bs.id as backup_server_id, 
		bs.display_name as backup_server_name, 
		[dbo].[fn.LabRequests.GetVmRecoveryPointsCount](bobj.id, backups.job_target_type) as  restore_points_count,
		vmdetails.details_dns,
		vmdetails.details_ip,
		--relevant_bobjects.job_target_type as job_type
		backups.job_target_type as job_type
	FROM
		[dbo].[C.BObjects] bobj
		INNER JOIN [dbo].[Repl.Topology.BackupServers] bs ON bobj.db_instance_id = bs.current_db_id
		LEFT OUTER JOIN	[dbo].[fn.LabRequests.GetVmDetailsAll]() vmdetails	ON vmdetails.bobject_id = bobj.id
		INNER JOIN [dbo].[C.Backup.Model.OIBs] oibs on bobj.id = oibs.object_id
		INNER JOIN [dbo].[C.Backup.Model.Points] points on oibs.point_id = points.id
		INNER JOIN [dbo].[C.Backup.Model.Backups] backups on backups.id = points.backup_id
		WHERE
			(
				(bobj.platform = 0 AND backups.job_target_type IN (0, 1)) OR
				(bobj.platform = 1 AND backups.job_target_type = 0)
			) AND
			(@job_type IS NULL OR backups.job_target_type = @job_type) AND
				(
					bobj.object_name like ('%' + @vmname + '%') or
					oibs.guest_os_info.value('(/guest_os_info/GuestInfo/Property[attribute::Name=''Ip''])[1]', 'nvarchar(max)') like ('%' + @vmname + '%') or
					oibs.guest_os_info.value('(/guest_os_info/GuestInfo/Property[attribute::Name=''DnsName''])[1]', 'nvarchar(max)') like ('%' + @vmname + '%')
				)		
END			
GO
---------------------------------------------------



---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetVmRecoveryPoints]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetVmRecoveryPoints]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetVmRecoveryPoints]
	@obj_id uniqueidentifier,
	@creation_time datetime,
	@preferedJobType int,
	@show_all bit
AS
BEGIN

	DECLARE @jobType int
	
	SELECT TOP(1)
		@jobType =
		CASE
			WHEN @show_all = 1 THEN NULL
			ELSE @preferedJobType
		END
	FROM 
		[dbo].[C.BObjects] objs
		INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON objs.[db_instance_id] = bservers.[current_db_id]
	WHERE objs.id = @obj_id
	
	DECLARE @vmrecpoints as table(original_oib_id uniqueidentifier, creation_time datetime)
	INSERT INTO @vmrecpoints(original_oib_id, creation_time)
	SELECT
		rp.original_oib_id, rp.creation_time
	FROM
		[dbo].[fn.LabRequests.GetVmRecoveryPoints](@obj_id, @jobType) rp	
	ORDER BY rp.creation_time DESC


	DECLARE @result as table(id uniqueidentifier, creation_time datetime)

	IF @show_all = 1
	BEGIN
		INSERT @result(id, creation_time)
		SELECT original_oib_id, creation_time FROM @vmrecpoints
	END
	ELSE
	BEGIN
		
		DECLARE @min_creation_time datetime
		DECLARE @max_creation_time datetime
		SELECT @min_creation_time = MAX(creation_time) FROM @vmrecpoints WHERE creation_time <= @creation_time
		SELECT @max_creation_time = MIN(creation_time) FROM @vmrecpoints WHERE creation_time >= @creation_time

		IF @min_creation_time is null
			SET @min_creation_time = @max_creation_time
		IF @max_creation_time is null
			SET @max_creation_time = @min_creation_time			
			
		-- +1 point
		insert @result(id, creation_time)
		select 
			top 2 original_oib_id, creation_time 
		from
			@vmrecpoints
		where 
			creation_time >= @max_creation_time
		order by creation_time ASC
			
		-- NEAREST points	
		insert @result(id, creation_time)
		select 
			top 1 original_oib_id, creation_time 
		from
			@vmrecpoints
		where 
			@min_creation_time <= creation_time and 
			creation_time <= @max_creation_time

		-- +1 point
		insert @result(id, creation_time)
		select 
			top 2 original_oib_id, creation_time 
		from
			@vmrecpoints
		where 
			creation_time <= @min_creation_time
		order by creation_time desc
				
	end

	select distinct * from @result order by creation_time desc
		
END
GO
---------------------------------------------------



---------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetVmRecoveryDrvJobs]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetVmRecoveryDrvJobs]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetVmRecoveryDrvJobs]
	@orig_oib_id uniqueidentifier,
	@show_all bit
AS
BEGIN

	DECLARE @obj_id uniqueidentifier, @platform int
	SELECT TOP(1) @obj_id = objs.id, @platform = objs.platform
	FROM [dbo].[C.Backup.Model.OIBs] oibs
	INNER JOIN [dbo].[C.BObjects] objs ON oibs.[object_id] = objs.[id]
	WHERE oibs.original_oib_id = @orig_oib_id
		
	IF @obj_id IS NULL 
		RAISERROR (N'Object was not found found.', 9, 1); 
	

	CREATE TABLE #BJobsWithObject(bjob_id uniqueidentifier, name nvarchar(255))
	INSERT INTO #BJobsWithObject 
	SELECT DISTINCT jobs.id, jobs.name 
	FROM [dbo].[C.BJobs] jobs 	
	INNER JOIN [dbo].[C.ObjectsInJobs] oijs ON jobs.[id] = oijs.[job_id]
	WHERE 
		jobs.[type] IN (0,1) AND  --backup, replica
		oijs.[object_id]=@obj_id
	
	CREATE TABLE #AppGroupsWithObject(folder_id uniqueidentifier, app_group nvarchar(255))	
	INSERT INTO #AppGroupsWithObject
	SELECT DISTINCT 
		folders.[id],
		folders.[name]
	FROM
		[dbo].[C.ObjectsInApplicationGroups] oiags
		INNER JOIN [dbo].[C.Folders] folders ON oiags.folder_id = folders.id
	WHERE 
		oiags.[object_id]=@obj_id
								
	SELECT DISTINCT * 
	FROM
	(
		SELECT
			bj.id job_id,
			bj.name job_name,	
				
			last_drs.creation_time started_date,	
			last_drs.preferred_date preferred_date,
			last_drs.state state,
			agwo.app_group,
			vl.name virtual_lab,
			(case when bjwo.bjob_id is null then 0 else 1 end) as in_linked,
			(case when agwo.folder_id is null then 0 else 1 end) as in_appgroup
		FROM
			[dbo].[C.BJobs] bj
			INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON bj.[db_instance_id] = bservers.[current_db_id]			
			LEFT OUTER JOIN [dbo].[C.VirtualLabs] vl on bj.target_host_id = vl.id		
			LEFT OUTER JOIN 
				(
					select drs.id, drs.job_id, jbs.creation_time, drs.preferred_date, drs.state
					from [dbo].[view.Backup.SbSessions] drs,
						 [dbo].[C.Backup.Model.JobSessions] jbs,
						 (
							select job_id, max(creation_time) creation_time from [dbo].[view.Backup.SbSessions]
							group by job_id		
						 ) last_jbs
					where drs.id = jbs.id and jbs.creation_time = last_jbs.creation_time
				) last_drs	on last_drs.job_id = bj.id
			LEFT OUTER JOIN [dbo].[C.LinkedJobs] linked_jobs ON linked_jobs.[job_id] = bj.id
			LEFT OUTER JOIN #BJobsWithObject bjwo ON linked_jobs.[linked_job_id] = bjwo.bjob_id
			LEFT OUTER JOIN [dbo].[C.ObjectsInJobs] oijs ON bj.id = oijs.job_id		
			LEFT OUTER JOIN #AppGroupsWithObject agwo ON oijs.[folder_id] = agwo.[folder_id]
		WHERE
			bj.type = 3 AND 
			bj.platform = @platform
	) q
	WHERE
		CASE @show_all 
			when 1 then 1
			else
			(
				case
					when in_appgroup = 1 then 1 
					when in_linked = 1 then 1
					else 0
				end
			)
		END = 1
			
	
	DROP TABLE #BJobsWithObject
	DROP TABLE #AppGroupsWithObject	

END
GO
---------------------------------------------------



---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetReadyToPrepareLabRequests]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetReadyToPrepareLabRequests]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetReadyToPrepareLabRequests]
AS
BEGIN

	select 
	  lrd.request_id,
	  lrd.request_name,
	  bs.id server_id,
	  bs.display_name server_name,
	  bj.id job_id,
	  bj.name job_name,
	  drs_working.sess_id,
	  lrd.oib_id oib_id
	from
		(
			  select 
					lr.id request_id,
					lr.req_vmname request_name,
					lrs.aux_data.value('(/CLabRequestStateAuxDataApproved/BackupServerId)[1]', 'uniqueidentifier') server_id,
					lrs.aux_data.value('(/CLabRequestStateAuxDataApproved/JobId)[1]', 'uniqueidentifier') job_id,					
					lrs.aux_data.value('(/CLabRequestStateAuxDataApproved/OibId)[1]', 'uniqueidentifier') as oib_id,
					lr.preferred_date preferred_date
			  from
					[dbo].[Air.LabRequests] lr,
					[dbo].[Air.LabRequestStates] lrs
			  where
					lr.state_id = lrs.id and
					lrs.state = 2 --approved
		) lrd
	inner join
		[dbo].[Repl.Topology.BackupServers] bs
	on
		lrd.server_id = bs.id
	inner join
		[dbo].[C.BJobs] bj
	on 
		bj.id = lrd.job_id
	inner join
		(     
			  select j.id, case when drs.sess_count is null then 0 else drs.sess_count end sess_count
			  from 
					[dbo].[C.BJobs] j
			  left outer join
					(
					select job_id, count(*) sess_count from
						  [dbo].[view.Backup.SbSessions] s
					where
						  s.state <> 5 and --working -(i.e. transitional)
						  s.state <> -1    --stopped / 
					group by s.job_id
					) drs
			  on j.id = drs.job_id
		) drs_transitional(job_id, sess_count)
	on
		bj.id = drs_transitional.job_id 
	inner join
		(
	    
			  select j.id job_id, drs.id sess_id, drs.preferred_date preferred_date
			  from 
					[dbo].[C.BJobs] j
			  left outer join
					(
					select * from 
						  [dbo].[view.Backup.SbSessions] s
					where 
						  s.state = 5 --working 
					) drs
			  on j.id = drs.job_id
	                
		) drs_working     
	on          
		bj.id = drs_working.job_id -- consider preferred_date
	left outer join
		[dbo].[C.Backup.Model.SbTaskSessions] drts
	on
		drts.oib_id = lrd.oib_id and
		drts.drsession_id = drs_working.sess_id	--consider sbtask_session
	where 
		drs_transitional.sess_count = 0 --exclude jobs with transitional sessions
		and (drts.id is null or drts.type <> 1 or drts.state = 4) --don't start vm in verification task session (not stopped)

		
END
GO
---------------------------------------------------




---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetReadyToStopLabRequests]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetReadyToStopLabRequests]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetReadyToStopLabRequests]
AS
BEGIN

	select 
		lr.*,
		lrs_preparing.aux_data.value('(/CLabRequestStateAuxDataPreparing/SessId)[1]', 'uniqueidentifier') session_id,
		lrs_approved.aux_data.value('(/CLabRequestStateAuxDataApproved/BackupServerId)[1]', 'uniqueidentifier') server_id
	from
		[dbo].[Air.LabRequests] lr,
		[dbo].[Air.LabRequestStates] lrs_preparing,
		[dbo].[Air.LabRequestStates] lrs_approved,
		[dbo].[Air.LabRequestStates] lrs_ready
	where
	    lr.state_id = lrs_ready.id and lrs_ready.state = 4 and -- ready
		lrs_preparing.id = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lr.id, 3) and --preparing
		lrs_approved.id  = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lr.id, 2) and --approved
		dateadd(second, lrs_ready.aux_data.value('(/CLabRequestStateAuxDataReady/ActDuration)[1]', 'int'), lrs_ready.date) < getutcdate()
		
END
GO
---------------------------------------------------




---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetReadyLabRequests]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetReadyLabRequests]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetReadyLabRequests]
AS
BEGIN

	select 
		lr.id request_id,
		drts.appliance_ip,
		drts.subnet_ip,
		drts.subnet_mask,
		drts.vm_external_ip,

		lrs_approved.aux_data.value('(/CLabRequestStateAuxDataApproved/ActualVmName)[1]', 'nvarchar(max)')	actual_vmname,		
		lrs_approved.aux_data.value('(/CLabRequestStateAuxDataApproved/ActualDuration)[1]', 'int')	        actual_duration
		
	from
		[dbo].[Air.LabRequests] lr,
		[dbo].[Air.LabRequestStates] lrs_preparing,
		[dbo].[Air.LabRequestStates] lrs_approved,
		[dbo].[C.Backup.Model.SbTaskSessions] drts
	where 
		lrs_approved.id  = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lr.id, 2) and --approved
		lr.state_id = lrs_preparing.id and lrs_preparing.state = 3 and --preparing
		drts.id = lrs_preparing.aux_data.value('(/CLabRequestStateAuxDataPreparing/SessId)[1]', 'uniqueidentifier') and
		drts.state = 2 and drts.vm_external_ip is not null and drts.vm_external_ip <> ''
  
END
GO
---------------------------------------------------






---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetReadyToFailLabRequests]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetReadyToFailLabRequests]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetReadyToFailLabRequests]
AS
BEGIN

	select 
		request_id,
		session_id,
		server_id,
		case
			when is_tasksess_or_sess_stopped = 1 then 'SureBackup job terminated unexpectedly.'
			when is_tasksess_started_but_vmextip_notretrieved = 1 then 'Unable to retrieve external IP for VM.'
			else ''
		end reason,
		case
			when is_tasksess_started_but_vmextip_notretrieved = 1 then 1
			else 0
		end need_stop
	from
	(
		select 
			lr.id  request_id,
			drts.id session_id,
			lrs_approved.aux_data.value('(/CLabRequestStateAuxDataApproved/BackupServerId)[1]', 'uniqueidentifier') server_id,
			
			case 
				when (drts.state = 4 or drs.state = -1) then 1 -- sb session stopped
				else 0
			end is_tasksess_or_sess_stopped,
			
			case 
				when (drts.state = 2 and (drts.vm_external_ip is null or drts.vm_external_ip = '')) then 1
				else 0
			end is_tasksess_started_but_vmextip_notretrieved,
			
			case
				when drts.overall_status = 2 then 1 --sb task session failed
				else 0
			end is_tasksess_failed
		from
			[dbo].[Air.LabRequests] lr,
			[dbo].[Air.LabRequestStates] lrs_current,
			[dbo].[Air.LabRequestStates] lrs_preparing,
			[dbo].[Air.LabRequestStates] lrs_approved,		
			[dbo].[C.Backup.Model.SbTaskSessions] drts,
			[dbo].[view.Backup.SbSessions] drs
		where
			lr.state_id = lrs_current.id and
			drs.id = drts.drsession_id and
			(
				lrs_current.state = 3 or --preparing
				lrs_current.state = 4    --ready
			) and
			lrs_approved.id  = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lr.id, 2) and --approved
			lrs_preparing.id = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lr.id, 3) and --preparing
			drts.id = lrs_preparing.aux_data.value('(/CLabRequestStateAuxDataPreparing/SessId)[1]', 'uniqueidentifier')
	) o
	where
		is_tasksess_or_sess_stopped = 1 or
		is_tasksess_started_but_vmextip_notretrieved = 1 or
		is_tasksess_failed = 1


END
GO
---------------------------------------------------




---------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetRecoveryVmDataCurrentId]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetRecoveryVmDataCurrentId]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetRecoveryVmDataCurrentId]
	@request_id uniqueidentifier
AS
BEGIN
	
	select bo.id from 
		[dbo].[C.BObjects] bo,
		[dbo].[C.Backup.Model.OIBs] oibs
	where 
		bo.id = oibs.object_id and
		oibs.id = 
		(
			select				
				lrs_approved.aux_data.value('(/CLabRequestStateAuxDataApproved/OibId)[1]', 'uniqueidentifier')
			from 
				[dbo].[Air.LabRequests] lr,
				[dbo].[Air.LabRequestStates] lrs_approved
			where
				lr.id = @request_id and
			    lrs_approved.id  = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lr.id, 2) --approved		
		)
	    
END
GO








---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetRecoveryPointDataCurrentId]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetRecoveryPointDataCurrentId]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetRecoveryPointDataCurrentId]
	@request_id uniqueidentifier
AS
BEGIN
		
	select
		lrs_approved.aux_data.value('(/CLabRequestStateAuxDataApproved/OibId)[1]', 'uniqueidentifier') as oib_id
	from 
		[dbo].[Air.LabRequests] lr,
		[dbo].[Air.LabRequestStates] lrs_approved
	where
		lr.id = @request_id and
		lrs_approved.id  = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lr.id, 2) --approved
	    
END
GO
---------------------------------------------------



---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetRecoveryJobDataCurrentId]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetRecoveryJobDataCurrentId]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetRecoveryJobDataCurrentId]
	@request_id uniqueidentifier
AS
BEGIN
		
	select
		lrs_approved.aux_data.value('(/CLabRequestStateAuxDataApproved/JobId)[1]', 'uniqueidentifier') job_id
	from 
		[dbo].[Air.LabRequests] lr,
		[dbo].[Air.LabRequestStates] lrs_approved
	where
		lr.id = @request_id and
		lrs_approved.id  = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lr.id, 2) --approved
    
END
GO
---------------------------------------------------



-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.LabRequestState2HumanReadableFormat]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.LabRequestState2HumanReadableFormat]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.LabRequestState2HumanReadableFormat]
(
	@request_state int
)
RETURNS nvarchar( 2000 )
AS
BEGIN
	DECLARE @retVal as nvarchar( 2000 )
	
	select @retVal = case
		when @request_state = 0 then 'Pending'
        when @request_state = 1 then 'Cancelled'
        when @request_state = 2 then 'Approved'
        when @request_state = 3 then 'Preparing'
        when @request_state = 4 then 'Ready'
        when @request_state = 5 then 'Stopping'
        when @request_state = 6 then 'Stopped'
        when @request_state = 7 then 'Failed'

    end
        
	RETURN @retVal;
	
END
GO
-----------------------------------------------------




-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.LabRequestStateLogPending]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogPending]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogPending]
(
	@state_id uniqueidentifier
)
RETURNS nvarchar( max )
AS
BEGIN
	DECLARE @log as nvarchar( max )

	declare @ssdate datetime
	
	declare @user    nvarchar(1000)
	declare @reqvm   nvarchar(1000)
	declare @reqdate datetime

	select @user = lr.[user], @reqvm = lr.[req_vmname], @reqdate = lr.[preferred_date] from [dbo].[Air.LabRequests] lr
	where lr.id = (select lrs.request_id from [dbo].[Air.LabRequestStates] lrs where lrs.id = @state_id) 

	select @ssdate = lrs.date
	from [dbo].[Air.LabRequestStates] lrs
	where lrs.id = @state_id

	set @reqdate = [dbo].[fn.Common.UtcDate2Local] (@reqdate)
	set @ssdate = [dbo].[fn.Common.UtcDate2Local] (@ssdate)
	set @log = N'[' + cast(@ssdate as nvarchar(1000)) + N'] ' +  N'Pending, User: ' + @user + ', VM: ' + @reqvm + ', Required date: ' + cast(@reqdate as nvarchar(1000))
        
	RETURN @log;	
END
GO
-----------------------------------------------------


-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.LabRequestStateLogCancelled]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogCancelled]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogCancelled]
(
	@state_id uniqueidentifier
)
RETURNS nvarchar( max )
AS
BEGIN
	DECLARE @log as nvarchar( max )

	declare @ssdate datetime
	declare @failure_msg nvarchar(max)
	
	select 
		@ssdate = lrs.date,
		@failure_msg = lrs.aux_data.value('(/CLabRequestStateAuxDataCancelled/FailureMessage)[1]', 'nvarchar(max)')
	from
		[dbo].[Air.LabRequestStates] lrs
	where
		lrs.id = @state_id
	
	set @ssdate = [dbo].[fn.Common.UtcDate2Local] (@ssdate)
	set @log = N'[' + cast(@ssdate as nvarchar(1000)) + N'] ' + N'Cancelled'
      
    if @failure_msg is not null and @failure_msg <> ''
	begin 

		declare @fmtlog nvarchar(max)
		set @fmtlog = [dbo].[fn.LabRequests.FormatLabRequestStateFailureMessage] (@failure_msg)

		set @log = @log + nchar(10) + @fmtlog
	end
        
	RETURN @log;	
END
GO
-----------------------------------------------------



-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.FormatLabRequestStateFailureMessage]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.FormatLabRequestStateFailureMessage]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.FormatLabRequestStateFailureMessage]
(
	@failure_msg nvarchar(max)
)
RETURNS nvarchar( max )
AS
BEGIN
	if @failure_msg is null or @failure_msg = ''
		return null
	
	declare @chars nvarchar(max)
	set @chars = char(10);
	 
	declare @sslines as table (id int, data nvarchar(max))
	insert into @sslines(id, data)
	select * from [dbo].[fn.Common.SplitString] (@failure_msg, @chars)

	declare @lineid int
	set @lineid = 1

	declare @linemaxid int
	select @linemaxid = max(id) + 1 from @sslines

	DECLARE @fmtsslog as nvarchar( max )
	set @fmtsslog = '     ERROR:' + char(10)

	while @lineid < @linemaxid
	begin
		declare @line nvarchar(max)
		select @line = data from @sslines where id = @lineid
		if @line is not null
		begin
			set @fmtsslog = @fmtsslog + '     ' + @line + char(10)
			set @lineid = @lineid + 1
		end
	end	
	
	return @fmtsslog;
	
END
GO
-----------------------------------------------------




-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.LabRequestStateLogApproved]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogApproved]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogApproved]
(
	@state_id uniqueidentifier
)
RETURNS nvarchar( max )
AS
BEGIN

	DECLARE @log as nvarchar( max )

	declare @ssdate datetime

	declare @bs_id  uniqueidentifier
	declare @job_id uniqueidentifier
	declare @oib_id uniqueidentifier
	declare @failure_msg nvarchar(max)

	declare @user    nvarchar(1000)
	set @user = ''
	
	select
		@ssdate = lrs.date,
		@bs_id  = lrs.aux_data.value('(/CLabRequestStateAuxDataApproved/BackupServerId)[1]', 'uniqueidentifier'),
		@job_id = lrs.aux_data.value('(/CLabRequestStateAuxDataApproved/JobId)[1]', 'uniqueidentifier'),
		@oib_id = lrs.aux_data.value('(/CLabRequestStateAuxDataApproved/OibId)[1]', 'uniqueidentifier'),
		@failure_msg = lrs.aux_data.value('(/CLabRequestStateAuxDataApproved/FailureMessage)[1]', 'nvarchar(max)'),
		@user        = lrs.aux_data.value('(/CLabRequestStateAuxDataApproved/UserName)[1]', 'nvarchar(1000)')
	from	
		[dbo].[Air.LabRequestStates] lrs
	where
		lrs.id = @state_id 
			
	declare @bs_name nvarchar(255)
	declare @job     nvarchar(1000)
	declare @vlab    nvarchar(1000)
	declare @vm      nvarchar(1000)
	declare @date    datetime

	set @job = ''
	set @vlab = ''

	select
		@bs_name = ip_or_dns_name
	from
		[dbo].[Repl.Topology.BackupServers]
	where 
		id = @bs_id
		
	select 
		@job = name
	from
		[dbo].[C.BJobs]
	where 
		id = @job_id
		
	select
		@vm = object_name
	from
		[dbo].[C.BObjects]
	where 
		id = (select object_id from [dbo].[C.Backup.Model.OIBs] where id = @oib_id)

	select
		@date = creation_time
	from
		[dbo].[C.Backup.Model.OIBs] 
	where
		id = @oib_id

	select
		@vlab = name
	from
		[dbo].[C.VirtualLabs]
	where id = (select target_host_id from [dbo].[C.BJobs] where id = @job_id)

	set @date = [dbo].[fn.Common.UtcDate2Local] (@date)
	set @ssdate = [dbo].[fn.Common.UtcDate2Local] (@ssdate)
	set @log = N'[' + cast(@ssdate as nvarchar(1000)) + N'] ' + 'Approved, By User: ' + @user + ', Backup Server: ' + @bs_name + ', Job: ' + @job + ', Virtual Lab: ' + @vlab + ', VM: ' + @vm + ', Date: ' + cast(@date as nvarchar(1000))




	if @failure_msg is not null and @failure_msg <> ''
	begin 

		declare @fmtlog nvarchar(max)
		set @fmtlog = [dbo].[fn.LabRequests.FormatLabRequestStateFailureMessage] (@failure_msg)

		set @log = @log + nchar(10) + @fmtlog
	end

	RETURN @log;	
END
GO
-----------------------------------------------------



-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.LabRequestStateLogPreparing]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogPreparing]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogPreparing]
(
	@state_id uniqueidentifier
)
RETURNS nvarchar( max )
AS
BEGIN
	DECLARE @log as nvarchar( max )
	declare @ssdate datetime
	declare @failure_msg nvarchar(max)

	declare @create_new bit
	set @create_new = 0

	select
		@ssdate     = lrs.date,
		@create_new = lrs.aux_data.value('(/CLabRequestStateAuxDataPreparing/CreateNew)[1]', 'bit'),
		@failure_msg = lrs.aux_data.value('(/CLabRequestStateAuxDataPreparing/FailureMessage)[1]', 'nvarchar(max)')		
	from
		[dbo].[Air.LabRequestStates] lrs
	where
		lrs.id = @state_id
	
	set @ssdate = [dbo].[fn.Common.UtcDate2Local] (@ssdate)
	set @log = N'[' + cast(@ssdate as nvarchar(1000)) + N'] ' + N'Preparing'


	if @create_new = 1 
		set @log = @log + ', new session started.'
	else
		set @log = @log + ', using existing session.'
    
    
    if @failure_msg is not null and @failure_msg <> ''
	begin 

		declare @fmtlog nvarchar(max)
		set @fmtlog = [dbo].[fn.LabRequests.FormatLabRequestStateFailureMessage] (@failure_msg)

		set @log = @log + nchar(10) + @fmtlog
	end
    
	RETURN @log;	
END
GO
-----------------------------------------------------



-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.LabRequestStateLogReady]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogReady]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogReady]
(
	@state_id uniqueidentifier
)
RETURNS nvarchar( max )
AS
BEGIN

	DECLARE @log as nvarchar( max )


	declare @ssdate datetime
	declare @failure_msg nvarchar(max)
	
	declare @gateway_ip  as nvarchar( 1000 )
	declare @subnet_ip   as nvarchar( 1000 )
	declare @subnet_mask as nvarchar( 1000 )
	declare @vmext_ip    as nvarchar( 1000 )
	
	select 
		@ssdate      = lrs_ready.date,
		@gateway_ip  = lrs_ready.aux_data.value('(/CLabRequestStateAuxDataReady/GatewayIp)[1]', 'nvarchar(1000)'),
		@subnet_ip   = lrs_ready.aux_data.value('(/CLabRequestStateAuxDataReady/SubnetIp)[1]', 'nvarchar(1000)'),
		@subnet_mask = lrs_ready.aux_data.value('(/CLabRequestStateAuxDataReady/SubnetMask)[1]', 'nvarchar(1000)'),
		@vmext_ip    = lrs_ready.aux_data.value('(/CLabRequestStateAuxDataReady/VmExtIp)[1]', 'nvarchar(1000)'),
		@failure_msg = lrs_ready.aux_data.value('(/CLabRequestStateAuxDataReady/FailureMessage)[1]', 'nvarchar(max)')
	from 	
		[dbo].[Air.LabRequestStates] lrs_ready
	where
		lrs_ready.id = @state_id
		
	set @ssdate = [dbo].[fn.Common.UtcDate2Local] (@ssdate)
	set @log = N'[' + cast(@ssdate as nvarchar(1000)) + N'] ' + N'Ready: ' + CHAR(10)

	if @gateway_ip is not null
		set @log = @log + N'     Gateway Ip: '  + @gateway_ip + CHAR(10)
	if @subnet_ip is not null
		set @log = @log + N'     Subnet Ip: '   + @subnet_ip + CHAR(10)
	if @subnet_mask is not null
		set @log = @log + N'     Subnet Mask: ' + @subnet_mask + CHAR(10)
	if @vmext_ip is not null
		set @log = @log + N'     Virtual Machine external Ip: ' + @vmext_ip + CHAR(10)
    
    if @failure_msg is not null and @failure_msg <> ''
	begin 

		declare @fmtlog nvarchar(max)
		set @fmtlog = [dbo].[fn.LabRequests.FormatLabRequestStateFailureMessage] (@failure_msg)

		set @log = @log + nchar(10) + @fmtlog
	end
        
	RETURN @log;	
END
GO
-----------------------------------------------------



-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.LabRequestStateLogStopping]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogStopping]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogStopping]
(
	@state_id uniqueidentifier
)
RETURNS nvarchar( max )
AS
BEGIN
	DECLARE @log as nvarchar( max )

	declare @ssdate datetime
	declare @failure_msg nvarchar(max)

	select
		@ssdate = date,
		@failure_msg = lrs.aux_data.value('(/CLabRequestStateAuxDataStopping/FailureMessage)[1]', 'nvarchar(max)')
	from
		[dbo].[Air.LabRequestStates] lrs
	where
		lrs.id = @state_id


	set @ssdate = [dbo].[fn.Common.UtcDate2Local] (@ssdate)
	set @log = N'[' + cast(@ssdate as nvarchar(1000)) + N'] ' + N'Stopping'
    
    if @failure_msg is not null and @failure_msg <> ''
	begin 

		declare @fmtlog nvarchar(max)
		set @fmtlog = [dbo].[fn.LabRequests.FormatLabRequestStateFailureMessage] (@failure_msg)

		set @log = @log + nchar(10) + @fmtlog
	end
	
	RETURN @log;	
END
GO
-----------------------------------------------------



-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.LabRequestStateLogStopped]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogStopped]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogStopped]
(
	@state_id uniqueidentifier
)
RETURNS nvarchar( max )
AS
BEGIN
	DECLARE @log as nvarchar( max )
	
	declare @ssdate datetime
	declare @failure_msg nvarchar(max)
	
	select
		@ssdate = lrs.date,
		@failure_msg = lrs.aux_data.value('(/CLabRequestStateAuxDataStopped/FailureMessage)[1]', 'nvarchar(max)')
	from
		[dbo].[Air.LabRequestStates] lrs
	where
		lrs.id = @state_id

	set @ssdate = [dbo].[fn.Common.UtcDate2Local] (@ssdate)
	set @log = N'[' + cast(@ssdate as nvarchar(1000)) + N'] ' + N'Stopped'
    
	if @failure_msg is not null and @failure_msg <> ''
	begin 

		declare @fmtlog nvarchar(max)
		set @fmtlog = [dbo].[fn.LabRequests.FormatLabRequestStateFailureMessage] (@failure_msg)

		set @log = @log + nchar(10) + @fmtlog
	end
	
	RETURN @log;	
END
GO
-----------------------------------------------------



-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.LabRequestStateLogFailed]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogFailed]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.LabRequestStateLogFailed]
(
	@state_id uniqueidentifier
)
RETURNS nvarchar( max )
AS
BEGIN
	DECLARE @log as nvarchar( max )
	DECLARE @sslog as nvarchar( max )
	
	declare @ssdate datetime
	declare @failure_msg nvarchar(max)
		
	select top 1 
		@sslog = sbs.log, 
		@ssdate = lrs_failed.date,
		@failure_msg = lrs_failed.aux_data.value('(/CLabRequestStateAuxDataFailed/FailureMessage)[1]', 'nvarchar(max)')
	from 	
		[dbo].[Air.LabRequestStates] lrs_failed,
		[dbo].[Air.LabRequestStates] lrs_preparing,
		[dbo].[view.Backup.SbSessions] sbs,
		[dbo].[C.Backup.Model.SbTaskSessions] sbts
	where
		lrs_failed.id = @state_id and
		lrs_preparing.id = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lrs_failed.request_id, 3) and --preparing
		sbts.id = lrs_preparing.aux_data.value('(/CLabRequestStateAuxDataPreparing/SessId)[1]', 'uniqueidentifier') and
		sbs.id = sbts.drsession_id
		
		
	declare @chars nvarchar(max)
	set @chars = '\n';
	 
	declare @sslines as table (id int, data nvarchar(max))
	insert into @sslines(id, data)
	select * from [dbo].[fn.Common.SplitString] (@sslog, @chars)

	declare @lineid int
	set @lineid = 1

	declare @linemaxid int
	select @linemaxid = max(id) from @sslines

	DECLARE @fmtsslog as nvarchar( max )
	set @fmtsslog = ''

	while @lineid < @linemaxid
	begin
		declare @line nvarchar(max)
		select @line = data from @sslines where id = @lineid
		if @line is not null
		begin
			set @fmtsslog = @fmtsslog + '     ' + @line + char(10)
			set @lineid = @lineid + 1
		end
	end
	
	set @ssdate = [dbo].[fn.Common.UtcDate2Local] (@ssdate)
	set @log = N'[' + cast(@ssdate as nvarchar(1000)) + N'] ' + N'Failed: ' + CHAR(10) + @fmtsslog



	if @failure_msg is not null and @failure_msg <> ''
	begin 

		declare @fmtlog nvarchar(max)
		set @fmtlog = [dbo].[fn.LabRequests.FormatLabRequestStateFailureMessage] (@failure_msg)

		set @log = @log + nchar(10) + @fmtlog
	end
	
        
	RETURN @log;	
END
GO
-----------------------------------------------------


---------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.LabRequests.GetLabRequestDetails]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.LabRequests.GetLabRequestDetails]
GO			
CREATE PROCEDURE [dbo].[usp.LabRequests.GetLabRequestDetails]
    @request_id uniqueidentifier
AS
BEGIN

	declare @details nvarchar(max)
	set @details = N''

	declare @requested_user nvarchar(1000)
	declare @creation_date  datetime
	declare @approved_date  datetime

	select 
		@requested_user = lr.[user],
		@creation_date = lrs_pending.date,
		@approved_date = lrs_approved.date 
	from
		[dbo].[Air.LabRequests] lr
	left outer join
		[dbo].[Air.LabRequestStates] lrs_pending
	on
		lrs_pending.id  = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lr.id, 0) --pending 
	left outer join
		[dbo].[Air.LabRequestStates] lrs_approved
	on
		lrs_approved.id  = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lr.id, 2) --approved 
	where
		lr.id = @request_id 


	set @creation_date = [dbo].[fn.Common.UtcDate2Local] (@creation_date)
	set @approved_date = [dbo].[fn.Common.UtcDate2Local] (@approved_date)

	if @creation_date is not null
		set @details = @details + 'Creation date: ' + cast(@creation_date as nvarchar(1000)) + CHAR(10)

	if @requested_user is not null
		set @details = @details + 'Requested by: ' + @requested_user + CHAR(10)

	if @approved_date is not null
		set @details = @details + 'Approval date: ' + cast(@approved_date as nvarchar(1000)) + CHAR(10)
		
	select @details details

END
GO
---------------------------------------------------



				
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.ReRegHrefBinding]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.ReRegHrefBinding]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Config.ReRegHrefBinding]	
	@trg_report_id uniqueidentifier,
	@href_id uniqueidentifier,
	@href_binding_id uniqueidentifier,
	@dyn_attr1_name nvarchar( 100 ),
	@datasrc_dynattr1_colname nvarchar( 100 ),
	@dyn_attr2_name nvarchar( 100 ) = null,
	@datasrc_dynattr2_colname nvarchar( 100 ) = null,
	@dyn_attr3_name nvarchar( 100 ) = null,
	@datasrc_dynattr3_colname nvarchar( 100 ) = null
AS
BEGIN	
	SET NOCOUNT ON;
	
	DELETE FROM [dbo].[Report.GridReport.Binding.HrefColBindings]
	WHERE [dbo].[Report.GridReport.Binding.HrefColBindings].[id] = @href_binding_id
	
	DELETE FROM [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs]
	WHERE [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs].[href_binding_id] = @href_binding_id
	
	DELETE FROM [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs]
	WHERE [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs].[href_binding_id] = @href_binding_id
	
	-- REGISTER HREF BINDING
	INSERT INTO [dbo].[Report.GridReport.Binding.HrefColBindings]( [id],  [target_report_id], [hyperlink_id] )
	VALUES( @href_binding_id,  @trg_report_id, @href_id )
	
	-- REGISTER DYNAMIC ATTRIBUTES
	INSERT INTO [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs]( [id],[href_binding_id],[attr_name],[datasrc_column], [is_mandatory_attr])
	VALUES( NEWID(), @href_binding_id, @dyn_attr1_name, @datasrc_dynattr1_colname, 1 )
	
	IF @dyn_attr2_name IS NOT NULL
		INSERT INTO [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs]( [id],[href_binding_id],[attr_name],[datasrc_column], [is_mandatory_attr])
		VALUES( NEWID(), @href_binding_id, @dyn_attr2_name, @datasrc_dynattr2_colname, 1 )
		
	IF @dyn_attr3_name IS NOT NULL
		INSERT INTO [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs]( [id],[href_binding_id],[attr_name],[datasrc_column], [is_mandatory_attr])
		VALUES( NEWID(), @href_binding_id, @dyn_attr3_name, @datasrc_dynattr3_colname, 1 )

END
GO
---------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.ReRegDynHrefBinding]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.ReRegDynHrefBinding]
GO													
CREATE PROCEDURE [dbo].[usp.GridReport.Config.ReRegDynHrefBinding]
	@href_binding1_id uniqueidentifier,
	@href_binding2_id uniqueidentifier,
	@resolve_proc nvarchar(255),
	@dyn_href_binding_id uniqueidentifier,
	@dyn_attr1_name nvarchar(100) = null,
	@datasrc_dynattr1_colname nvarchar(100) = null
AS
BEGIN	
	SET NOCOUNT ON;
	
	delete [dbo].[Report.GridReport.Binding.DynHrefColBindings]
	where [id] = @dyn_href_binding_id
	
	delete [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs]
	where [href_binding_id] = @dyn_href_binding_id
	
	INSERT INTO [dbo].[Report.GridReport.Binding.DynHrefColBindings]( [id], [href_binding1_id], [href_binding2_id], [resolve_proc] )
	VALUES( @dyn_href_binding_id, @href_binding1_id, @href_binding2_id, @resolve_proc )
	
	IF @dyn_attr1_name IS NOT NULL
		INSERT INTO [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs]( [id],[href_binding_id],[attr_name],[datasrc_column], [is_mandatory_attr])
		VALUES( NEWID(), @dyn_href_binding_id, @dyn_attr1_name, @datasrc_dynattr1_colname, 1 )
END	
GO	
-----------------------------------------------------
				
				

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.GetVmDetailsAll]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.GetVmDetailsAll]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.GetVmDetailsAll] ()
RETURNS 
@vms_details TABLE
( 
	bobject_id uniqueidentifier,
	details_dns nvarchar(1000),
	details_ip  nvarchar(1000)
)
AS
BEGIN
	INSERT @vms_details( [bobject_id], [details_dns], [details_ip])
	(
		select object_id, details_dns, details_ip from
		(
			select
				id,
				object_id,
				guest_os_info.value('(/guest_os_info/GuestInfo/Property[attribute::Name=''DnsName''])[1]', 'nvarchar(max)') details_dns,
				guest_os_info.value('(/guest_os_info/GuestInfo/Property[attribute::Name=''Ip''])[1]', 'nvarchar(max)') details_ip,
				row_number() over (partition by object_id order by object_id) rn
			from
				[dbo].[C.Backup.Model.OIBs]
			where 
				guest_os_info.value('(/guest_os_info/GuestInfo/Property[attribute::Name=''Ip''])[1]', 'nvarchar(max)') <> ''
		) oibs(id, object_id, details_dns, details_ip, rn) 
		where 
			oibs.rn = 1
	)
	RETURN 
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Common.ConvertXmlLogToTextLog]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Common.ConvertXmlLogToTextLog]
GO													
CREATE PROCEDURE [dbo].[usp.Common.ConvertXmlLogToTextLog]
	@log_xml xml
AS
BEGIN
	CREATE TABLE #tmp(txt NTEXT)
	INSERT INTO #tmp VALUES('')

	DECLARE @pointer binary(16),@txtlen int
	select @pointer = textptr(txt) from #tmp	
	
	declare @log_str  nvarchar(max), @log_date_utc nvarchar(100), @log_date_local nvarchar(100), @log_status nvarchar(50)
	--declare @log_str_length int, @log_date_length int, @log_status_length int
	declare @log_idx int
	declare @dt_log_date datetime, @time_shift int

	set @log_idx = 1
	set @txtlen = 0
	set @time_shift = DATEDIFF(hh, getutcdate(), getdate())
			
	DECLARE @str nvarchar(max)

	WHILE EXISTS(SELECT * FROM @log_xml.nodes('/log_xml/Root/Log') as Y(ID) WHERE Y.ID.value('@Id', 'int') = @log_idx)
	begin

		SELECT 	
			@log_str = (Y.ID.value('Title[1]', 'nvarchar(100)') + CHAR(13) + CHAR(10)),
			@log_date_utc = Y.ID.value('@Time', 'nvarchar(19)'),
			@log_status = Y.ID.value('@Status', 'nvarchar(100)')
		FROM @log_xml.nodes('/log_xml/Root/Log') as Y(ID)
		WHERE Y.ID.value('@Id', 'int') = @log_idx

		IF @log_str IS NULL
			BREAK
			
	
		SET @dt_log_date = CASE WHEN UPPER(@log_date_utc) = '0001-01-01T00:00:00' 
			THEN CONVERT(DATETIME, '1753-01-01')
			ELSE DATEADD(hh, @time_shift, @log_date_utc)
		END

		SET @log_date_local = CAST(@dt_log_date as nvarchar(100))
		SET @str = @log_date_local + CHAR(9)

		updatetext #tmp.txt @pointer @txtlen 0 @str 
		SET @txtlen = @txtlen + LEN(@str)

		IF @log_status = 'EFailed'
		BEGIN
			SET @str = N'ERROR:' + CHAR(9)
			updatetext #tmp.txt @pointer @txtlen 0 @str
			SET @txtlen = @txtlen + LEN(@str)
		END
		ELSE
		BEGIN
			SET @str = N'Info:' + CHAR(9)
			updatetext #tmp.txt @pointer @txtlen 0 @str
			SET @txtlen = @txtlen + LEN(@str)
		END

		updatetext #tmp.txt @pointer @txtlen 0 @log_str
		SET @txtlen = @txtlen + LEN(@log_str)

		set @log_idx = @log_idx + 1

	END

	SELECT * FROM #tmp
END
GO
-----------------------------------------------------


-----------------------------------------------------
-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.AddLogMsg]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.AddLogMsg]
GO
CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.AddLogMsg]
	@session_id uniqueidentifier,
	@event_time datetime,
	@event_severity int,
	@event_text nvarchar(max),
	@ordinal_num int
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [dbo].[FLRSessionEvents] ([session_id]  , [event_time] ,[severity] ,[text] , [ordinal_num] ) 
	VALUES (
		@session_id,
		@event_time,
		@event_severity,
		@event_text,
		@ordinal_num
	)
	
	UPDATE [dbo].[FLRSessions] 
	SET [top_log_num] = @ordinal_num 
	WHERE [session_id] = @session_id 
END
GO
-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.CompleteSession]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.CompleteSession]
GO
CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.CompleteSession]
	@session_id uniqueidentifier,
	@session_result int
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE [dbo].[FLRSessions] 
	SET 
		[status] = @session_result,
		[end_time] = GETUTCDATE(),
		[processed_objects]  = [total_objects],
		[progress] = 100 
	WHERE 
		[session_id] = @session_id
END

GO
-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.CreateRestoreSession]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.CreateRestoreSession]
GO
CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.CreateRestoreSession] 
	@session_id uniqueidentifier,
	@start_time datetime,
	@status int,
    @details nvarchar(max),
    @options nvarchar(max),
    @initiator_name nvarchar(300),
    @initiator_sid nvarchar(100)  
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [dbo].[FLRSessions]( [session_id],[start_time], [status], [details], [processed_objects], [total_objects], [options], [initiator_name], [initiator_sid]) 
	VALUES
	(
		@session_id,
	    @start_time, 
	    @status, 	     		
	    @details,
	    0, 0,
	    @options,
	    @initiator_name, 
	    @initiator_sid   
	)
END
GO
-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.GetAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.GetAll]
GO
CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.GetAll]
	@limit int,
	@offset int
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @total_records int 
	SET @total_records = (
		SELECT COUNT([session_id]) AS total_records
		FROM [dbo].[FLRSessions] 
	)
	SELECT 
		s.session_id,
		s.start_time,
		s.status,
		s.end_time,
		s.processed_objects,
		s.total_objects,
		s.details,
		@total_records as sessions_count,
		s.options.value('(/Request/Type)[1]', 'nvarchar(50)') AS restore_type,
		s.options.value('(/Request/TargetPath)[1]', 'nvarchar(max)') AS target_path,
		s.top_log_num,
		s.initiator_name,
		s.initiator_sid,
		s.progress
	FROM (
		SELECT ROW_NUMBER() OVER(ORDER BY [start_time] DESC)AS row_num, *
		FROM [dbo].[FLRSessions] 
	) AS s
	WHERE s.row_num BETWEEN @offset AND @limit+@offset
	ORDER BY s.start_time DESC
END
GO
-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.GetEffectiveSessionResult]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.GetEffectiveSessionResult]
GO
CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.GetEffectiveSessionResult]
	@session_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	SELECT MAX(
		CASE [severity]
			WHEN 4 THEN 1
			ELSE [severity]
		END	
	) AS result 
	FROM [dbo].[FLRSessionEvents]
	WHERE [session_id] = @session_id
END
GO
-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.GetSessionById]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.GetSessionById]
GO
CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.GetSessionById] 
	@session_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	SELECT 
		s.session_id, 
		s.start_time,
		s.status,
		s.end_time,
		s.processed_objects,
		s.total_objects,
		s.options.value('(/Request/Type)[1]', 'nvarchar(50)') AS restore_type,
		s.options.value('(/Request/TargetPath)[1]', 'nvarchar(max)') AS target_path,
		s.top_log_num,
		s.initiator_name,
		s.initiator_sid,
		s.progress
	FROM [dbo].[FLRSessions] s
	WHERE s.session_id = @session_id 
END
GO
-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.GetSessionLog]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.GetSessionLog]
GO
CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.GetSessionLog]
	@session_id uniqueidentifier,
	@ordinal_num int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT 
		e.event_time, e.severity, e.text, e.ordinal_num 
	FROM	
		[dbo].[FLRSessionEvents] e
	WHERE 
		e.session_id = @session_id
		AND 
		e.ordinal_num > @ordinal_num
	ORDER BY
		e.ordinal_num ASC	 	
END
GO
-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.UpdateSessionObjects]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.UpdateSessionObjects]
GO
CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.UpdateSessionObjects]
	@session_id uniqueidentifier,
	@total_objects int,
	@processed_objects int
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE [dbo].[FLRSessions]
	SET 
		[processed_objects] = @processed_objects,
		[total_objects] = @total_objects
	WHERE 
		[session_id] = @session_id 
END
GO
-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Common.f_insert_as_first]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Common.f_insert_as_first]
GO
CREATE FUNCTION [dbo].[fn.Common.f_insert_as_first] (@x1 XML, @x2 XML)
RETURNS XML
AS
BEGIN
      IF (@x1 is null)
            RETURN null
      IF ((@x1.value('count(/*)','int') = 0) OR (@x2 is null))
            RETURN @x1
      IF (@x2.value('count(/*)','int') = 0)
            RETURN @x1
      IF ((@x1.value('count(/*)','int') > 1) OR (@x2.value('count(/*)','int') > 1))
            RETURN @x1 
 
      DECLARE @x XML
      SET @x = CONVERT(XML, (CONVERT(nvarchar(MAX), @x1) + CONVERT(nvarchar(MAX), @x2)))
      SET @x.modify('insert /*[2] as first into /*[1]')
      SET @x.modify('delete /*[2]')
      RETURN @x
END
GO
-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.AddFilesLogXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.AddFilesLogXml]
GO
CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.AddFilesLogXml]	
			@row_session_id uniqueidentifier,
			@row_time nvarchar(100),
			@row_status nvarchar(10),
			@row_operation nvarchar(300),
            @row_source_item_name nvarchar(300),
            @row_source_item_path nvarchar(300),
            @row_target_item_name nvarchar(300),
            @row_target_item_path nvarchar(300),
            @row_size bigint,
            @row_host nvarchar(300),
            @row_point_oibid uniqueidentifier,
            @row_point_datetime nvarchar(100)
AS
BEGIN
	SET NOCOUNT ON;
	--Select log
	DECLARE @myDoc xml 
	SET @myDoc = (SELECT [files_log]	
	FROM [dbo].[FLRSessions]
	WHERE [session_id] = @row_session_id)
	--Get new log line
	DECLARE @newXmlLogLine xml 
	SET @newXmlLogLine = N'<Log Id="" Usn="0" Status="" Time="">
								<Operation>new</Operation>
								<SourceItemName>new</SourceItemName>
								<SourceItemPath>new</SourceItemPath>
								<TargetItemName>new</TargetItemName>
								<TargetItemPath>new</TargetItemPath>
								<ItemSize>new</ItemSize>
								<Host>new</Host>
								<PointOibId>new</PointOibId>
								<PointDateTime>new</PointDateTime>
							</Log>'  

	DECLARE @new_id bigint 
	SET @new_id = @myDoc.value('(/Root/@TotalId)[1]', 'bigint' ) + 1

	--Update log line
	SET @newXmlLogLine.modify('replace value of (/Log/@Id)[1] with sql:variable("@new_id")')
	SET @newXmlLogLine.modify('replace value of (/Log/@Status)[1] with sql:variable("@row_status")')
	SET @newXmlLogLine.modify('replace value of (/Log/@Time)[1] with sql:variable("@row_time")')
	SET @newXmlLogLine.modify('replace value of (/Log/Operation[1]/text())[1] with sql:variable("@row_operation")')
	SET @newXmlLogLine.modify('replace value of (/Log/SourceItemName[1]/text())[1] with sql:variable("@row_source_item_name")')
	SET @newXmlLogLine.modify('replace value of (/Log/SourceItemPath[1]/text())[1] with sql:variable("@row_source_item_path")')
	SET @newXmlLogLine.modify('replace value of (/Log/TargetItemName[1]/text())[1] with sql:variable("@row_target_item_name")')
	SET @newXmlLogLine.modify('replace value of (/Log/TargetItemPath[1]/text())[1] with sql:variable("@row_target_item_path")')
	SET @newXmlLogLine.modify('replace value of (/Log/ItemSize[1]/text())[1] with sql:variable("@row_size")')
	SET @newXmlLogLine.modify('replace value of (/Log/Host[1]/text())[1] with sql:variable("@row_host")')
	SET @newXmlLogLine.modify('replace value of (/Log/PointOibId[1]/text())[1] with sql:variable("@row_point_oibid")')
	SET @newXmlLogLine.modify('replace value of (/Log/PointDateTime[1]/text())[1] with sql:variable("@row_point_datetime")')

	--Update log	
	SET @myDoc.modify('replace value of (/Root/@TotalId)[1] with sql:variable("@new_id")')
	SET @myDoc = [dbo].[fn.Common.f_insert_as_first](@myDoc, @newXmlLogLine)
		
	UPDATE [dbo].[FLRSessions] 
	SET [files_log] = @myDoc 
	WHERE [session_id] = @row_session_id 
		
	SELECT @new_id as [id]
END  
GO
-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.GetFilesLogXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.GetFilesLogXml]
GO
CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.GetFilesLogXml]	
			@row_session_id uniqueidentifier,
			@row_usn bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @myDoc xml 
	SET @myDoc = '<Root TotalUsn="0"></Root>'
	DECLARE @newXmlLogLine xml 
	DECLARE @topUsn bigint
	SET @newXmlLogLine = (SELECT [files_log].query('/Root/Log[@Id > sql:variable("@row_usn")]')
	FROM [dbo].[FLRSessions] 
	WHERE [session_id] = @row_session_id)
	
	SET  @topUsn = (SELECT [files_log].value('(/Root/@TotalId)[1]', 'nvarchar(max)')
	FROM [dbo].[FLRSessions] 
	WHERE [session_id] = @row_session_id)
	
	
	SET @newXmlLogLine = '<Logs>' + CONVERT(nvarchar(max), @newXmlLogLine) + '</Logs>'
	SET @myDoc.modify('replace value of (/Root/@TotalUsn)[1] with sql:variable("@topUsn")')
	SET @myDoc = [dbo].[fn.Common.f_insert_as_first](@myDoc, @newXmlLogLine)
	
	SELECT @myDoc
END  
GO
-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.RegisterDownload]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.RegisterDownload]
GO

CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.RegisterDownload]
	@id uniqueidentifier,
	@session_id uniqueidentifier,
	@file_path nvarchar(max)
	
AS
BEGIN
	INSERT INTO [dbo].[FLRSessionFiles] ([id], [session_id], [file_path], [creation_time] )
	VALUES (@id, @session_id, @file_path, GETUTCDATE())
END
GO

-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.GetDownload]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.GetDownload]
GO
CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.GetDownload]
	@session_id uniqueidentifier
	
AS
BEGIN
	SELECT [id], [session_id], [file_path], [creation_time]
	FROM [dbo].[FLRSessionFiles] 
	WHERE [session_id] = @session_id
END
GO
-----------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.RemoveOldDownloads]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.RemoveOldDownloads]
GO

CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.RemoveOldDownloads]
	@timestamp datetime
	
AS
BEGIN

	SELECT * FROM (
		SELECT [id], [session_id], [file_path],[creation_time]
		FROM [dbo].[FLRSessionFiles] 
		WHERE [creation_time] <= @timestamp
	) t 
	WHERE NOT t.[file_path] IN (
		SELECT [file_path]	FROM [dbo].[FLRSessionFiles] 
		WHERE [creation_time] > @timestamp
	)
	
	DELETE FROM [dbo].[FLRSessionFiles]
	WHERE [creation_time] <= @timestamp
END
GO
-------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.UpdateProgress]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.UpdateProgress]
GO

CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.UpdateProgress]
	@session_id uniqueidentifier,
	@progress int
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE [dbo].[FLRSessions]
	SET 
		[progress] = @progress
	WHERE 
		[session_id] = @session_id 
END
GO
---------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Backup.GetSessionLogHtml]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Backup.GetSessionLogHtml]
GO

CREATE FUNCTION [dbo].[fn.Backup.GetSessionLogHtml]
(
	@log_xml xml
)
RETURNS nvarchar( max )
AS
BEGIN
	DECLARE @retVal as nvarchar( max )
	set @retVal = '<div class="x-session-details">'
	
	declare @log_idx int
	set @log_idx = 1

	declare @log_str  nvarchar(max)
	declare @log_date datetime
	declare @log_date_str nvarchar(50)
	declare @log_date_xml xml
	declare @log_status nvarchar(max)
	declare @severity nvarchar(20)
	
	set @log_str = @log_xml.value('(/log_xml/Root/Log[attribute::Id=''1''])[1]', 'nvarchar(max)')
	-- we make our datetime look like UTC to keep time part (xs:datetime "normalizes" datetime to UTC)
	set @log_date_str = LEFT(@log_xml.value('(/log_xml/Root/Log[attribute::Id=''1''])[1]/@Time', 'nvarchar(50)'), 27) + 'Z'	
	set @log_status = @log_xml.value('(/log_xml/Root/Log[attribute::Id=''1''])[1]/@Status', 'nvarchar(max)')	

	while @log_str is not null
	begin	
		if @log_status = 'EFailed'
			set @severity = 'x-log-error'
		else if @log_status = 'EWarning'
			set @severity = 'x-log-warning' 
		else if @log_status = 'ESucceeded'
			set @severity = 'x-log-success'
		else 
			set @severity = 'x-log-running'		
		
		SET @log_date_xml = cast('' AS xml) --Gets an empty Xml datatype
		SET @log_date = @log_date_xml.value('xs:dateTime(sql:variable("@log_date_str"))', 'datetime')
		
		set @retVal = @retVal + '<div class="x-log-record-wrap"><div class="x-log-severity ' + @severity + '"></div>' +
		'<div class="x-log-record"><span class="log-record-time">' + CONVERT(varchar, @log_date, 20 ) + '</span>' + @log_str + '</div></div>'	
		set @log_idx = @log_idx + 1	
		
		set @log_str = @log_xml.value('(/log_xml/Root/Log[@Id=sql:variable("@log_idx")])[1]', 'nvarchar(max)')	
		set @log_date_str = LEFT(@log_xml.value('(/log_xml/Root/Log[@Id=sql:variable("@log_idx")])[1]/@Time', 'nvarchar(50)'), 27) + 'Z'		
		set @log_status = @log_xml.value('(/log_xml/Root/Log[@Id=sql:variable("@log_idx")])[1]/@Status', 'nvarchar(max)')	

	end
	
	set @retVal = @retVal + '</ div>'
	
	RETURN @retVal;
END
GO
---------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.GetAllForUser]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.GetAllForUser]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.GetAllForUser]
	@limit int,
	@offset int,
	@initiator_sid nvarchar(100)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @total_records int 
	SET @total_records = (
		SELECT COUNT([session_id]) AS total_records
		FROM [dbo].[FLRSessions] 
		WHERE [dbo].[FLRSessions].[initiator_sid] = @initiator_sid
	)
	SELECT 
		s.session_id,
		s.start_time,
		s.status,
		s.end_time,
		s.processed_objects,
		s.total_objects,
		s.details,
		@total_records as sessions_count,
		s.options.value('(/Request/Type)[1]', 'nvarchar(50)') AS restore_type,
		s.options.value('(/Request/TargetPath)[1]', 'nvarchar(max)') AS target_path,
		s.top_log_num,
		s.initiator_name,
		s.initiator_sid,
		s.progress
	FROM (
		SELECT ROW_NUMBER() OVER(ORDER BY [start_time] DESC)AS row_num, *
		FROM [dbo].[FLRSessions] 
		WHERE [dbo].[FLRSessions].[initiator_sid] = @initiator_sid
	) AS s
	WHERE s.row_num BETWEEN @offset AND @limit+@offset
	ORDER BY s.start_time DESC
END

GO

---------------------------------------------------


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.FileLogXML2Table]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.FileLogXML2Table]
GO

CREATE FUNCTION [dbo].[fn.FileLogXML2Table](@sess_id uniqueidentifier, @xCol xml)
returns @ret_Table table 
(session_id uniqueidentifier, item_name varchar(255), 
	item_path varchar(max), 
	host varchar(255), status varchar(50), 
	rest_time varchar(255),
	item_size int)
with schemabinding
as
begin
      insert into @ret_Table 
      select @sess_id, 
      nref.value('(SourceItemName)[1]', 'varchar(255)'),
      nref.value('(SourceItemPath)[1]', 'varchar(max)'),
      nref.value('(Host)[1]', 'varchar(255)'),
      nref.value('(@Status)[1]', 'varchar(50)'),
      LEFT(nref.value('(@Time)[1]', 'varchar(255)'), 27) + 'Z',
      nref.value('(ItemSize)[1]', 'int')
      from   @xCol.nodes('/Root/Log') R(nref)
      return
end
GO


/* datetime from xml string */

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.xmlDateToDatetime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.xmlDateToDatetime]
GO

CREATE FUNCTION [dbo].[fn.xmlDateToDatetime] 
(
	@dateTimeStr varchar(255)
)
RETURNS datetime
AS
BEGIN
	DECLARE @date datetime;
	select @date = cast('<datetime>' + @dateTimeStr +'</datetime>' as xml).value('xs:dateTime(.[1])', 'datetime')
	return @date;
END
GO


/* create view */

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

CREATE VIEW [dbo].[view.FLRSessions.FilesHistory]
AS
SELECT     s.item_name, s.item_path, s.host, s.status, s.session_id, [dbo].[fn.xmlDateToDatetime](s.rest_time) AS restore_time, s.item_size, initiator_name, initiator_sid
FROM         [dbo].[FLRSessions] CROSS APPLY[dbo].[fn.FileLogXML2Table](session_id, files_log) s

GO

/* create SP */

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.GetFilesHistory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.GetFilesHistory]
GO

CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.GetFilesHistory]
	@limit int,
	@offset int
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @total_records int;
	
	SET @total_records = (
		SELECT COUNT([session_id]) AS total_records
		FROM [dbo].[view.FLRSessions.FilesHistory]
	)
	SELECT 
		@total_records as total_count, s.*
	FROM (
		SELECT ROW_NUMBER() OVER(ORDER BY [restore_time] DESC)AS row_num, *
		FROM [dbo].[view.FLRSessions.FilesHistory]
	) AS s
	WHERE s.row_num BETWEEN @offset AND @limit+@offset
	ORDER BY s.[restore_time] DESC
END

GO

---
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.GetFilesHistoryForUser]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.GetFilesHistoryForUser]
GO

CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.GetFilesHistoryForUser]
	@limit int,
	@offset int,
	@user_sid varchar(255)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @total_records int;
	
	SET @total_records = (
		SELECT COUNT([session_id]) AS total_records
		FROM [dbo].[view.FLRSessions.FilesHistory]
		WHERE initiator_sid = @user_sid
	)
	SELECT 
		s.*
	FROM (
		SELECT ROW_NUMBER() OVER(ORDER BY [restore_time] DESC)AS row_num, *
		FROM [dbo].[view.FLRSessions.FilesHistory]
		WHERE initiator_sid = @user_sid
	) AS s
	WHERE s.row_num BETWEEN @offset AND @limit+@offset
	ORDER BY s.[restore_time] DESC
END
GO
---------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.FLRSessions.GetRequest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.FLRSessions.GetRequest]
GO
CREATE PROCEDURE [dbo].[usp.Data.FLRSessions.GetRequest]
	@session_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	
	SELECT 
		options
	FROM [dbo].[FLRSessions] 
	WHERE [session_id] = @session_id
END
GO


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.TransformCppText]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.TransformCppText]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.TransformCppText] (
		@text nvarchar(max))
	RETURNS nvarchar(max)
	AS
	BEGIN
		SET @text = replace(@text, '\t', CHAR(9))
		SET @text = replace(@text, '\n', CHAR(10))
		SET @text = replace(@text, '\r', CHAR(13))
		RETURN @text
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.IsEmptyString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.IsEmptyString]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.IsEmptyString] (
		@str nvarchar(max))
	RETURNS bit
	AS
	BEGIN
		IF (@str = NULL OR len(@str) = 0)
			RETURN 'TRUE'

		RETURN 'FALSE'
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.IsBlankString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.IsBlankString]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.IsBlankString] (
		@str nvarchar(max))
	RETURNS bit
	AS
	BEGIN
		IF ([dbo].[fn.Upgrade.IsEmptyString](@str) = 'FALSE')
		BEGIN
			IF (patindex([dbo].[fn.Upgrade.TransformCppText]('%[^ \t]%'), @str) > 0)
				RETURN 'FALSE'
		END

		RETURN 'TRUE'
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.FormatString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.FormatString]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.FormatString] (
		@format nvarchar(255),
		@arg0 sql_variant)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @result nvarchar(255)
		SET @format = [dbo].[fn.Upgrade.TransformCppText](@format)
		EXEC xp_sprintf @result OUTPUT, @format, @arg0
		RETURN @result
	END
GO

-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.FormatString2]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.FormatString2]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.FormatString2] (
		@format nvarchar(255),
		@arg0 sql_variant,
		@arg1 sql_variant)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @result nvarchar(255)
		SET @format = [dbo].[fn.Upgrade.TransformCppText](@format)
		EXEC xp_sprintf @result OUTPUT, @format, @arg0, @arg1
		RETURN @result
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.FormatString5]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.FormatString5]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.FormatString5] (
		@format nvarchar(255),
		@arg0 sql_variant,
		@arg1 sql_variant,
		@arg2 sql_variant,
		@arg3 sql_variant,
		@arg4 sql_variant)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @result nvarchar(255)
		SET @format = [dbo].[fn.Upgrade.TransformCppText](@format)
		EXEC xp_sprintf @result OUTPUT, @format, @arg0, @arg1, @arg2, @arg3, @arg4
		RETURN @result
	END
GO

-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.SplitString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.SplitString]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.SplitString] (
		@str nvarchar(max),
		@separator nvarchar(10))
	RETURNS @array table ( element nvarchar(max) )
	AS
	BEGIN
		IF ([dbo].[fn.Upgrade.IsEmptyString](@str) = 'TRUE')
			RETURN

		IF ([dbo].[fn.Upgrade.IsEmptyString](@separator) = 'TRUE')
		BEGIN
			INSERT INTO @array (element) VALUES (@str)
			RETURN
		END

		DECLARE @start bigint
		SET @start = 0

		WHILE ('TRUE' = 'TRUE')
		BEGIN
			DECLARE @element nvarchar(max)

			DECLARE @position bigint
			SET @position = charindex(@separator, @str, @start)

			IF (@position = 0)
				SET @element = substring(@str, @start, len(@str) - @start + 1)
			ELSE
				SET @element = substring(@str, @start, @position - @start)

			INSERT INTO @array (element) VALUES (@element)

			IF (@position = 0)
				BREAK

			SET @start = @position + len(@separator)				
		END

		RETURN
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.EscapeText]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.EscapeText]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.EscapeText] ( @text nvarchar(max) )
	RETURNS nvarchar(max)
	AS
	BEGIN
		SET @text = replace(@text, '<', '&lt;')
		SET @text = replace(@text, '>', '&gt;')
		SET @text = replace(@text, '&', '&amp;')
		SET @text = replace(@text, '"', '&quot;')
		SET @text = replace(@text, '''', '&apos;')
		RETURN @text
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.FormatIso8601DateTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.FormatIso8601DateTime]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.FormatIso8601DateTime] (@value datetime)
	RETURNS nvarchar(255)
	AS
	BEGIN
		SET @value = dateadd(hour, datediff(hh, getutcdate(), getdate()), @value)

		-- ISO 8601 format: international standard - works with any language setting
        -- '2006-09-07T16:42:31.301'
		DECLARE @str_iso8601 nvarchar(100)
		SET @str_iso8601 = convert(varchar, @value, 126)

		IF (patindex('%[.]%', @str_iso8601) = 0)
			SET @str_iso8601 = @str_iso8601 + '.'

		SET @str_iso8601 = @str_iso8601 + replicate('0', 27 - len(@str_iso8601))

		DECLARE @time_zone int
		SET @time_zone = datediff(hh, getutcdate(), getdate())

		DECLARE @str_time_zone nvarchar(100)
		SET @str_time_zone = right(replicate('0', 2) + convert(varchar, abs(@time_zone)), 2)

		IF (sign(@time_zone) = -1)
			SET @str_time_zone = '-' + @str_time_zone
		ELSE
			SET @str_time_zone = '+' + @str_time_zone

		-- '2011-10-31T15:06:52.9434222+04:00'
		RETURN [dbo].[fn.Upgrade.FormatString2]('%s%s:00', @str_iso8601, @str_time_zone)
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.FormatDateTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.FormatDateTime]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.FormatDateTime] (@value datetime)
	RETURNS nvarchar(255)
	AS
	BEGIN
		SET @value = dateadd(hour, datediff(hh, getutcdate(), getdate()), @value)
		RETURN convert(nvarchar, @value, 20)
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.ConvertXmlDateTimeToSql]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.ConvertXmlDateTimeToSql]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.ConvertXmlDateTimeToSql] (@value nvarchar(255))
	RETURNS datetime
	AS
	BEGIN
		DECLARE @empty_xml xml
		SET @empty_xml = cast('' as xml)

		DECLARE @date_time datetime
		SET @date_time = @empty_xml.value('xs:dateTime(sql:variable("@value"))', 'datetime')

		RETURN @date_time
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.FormatUtcInvariantDateTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.FormatUtcInvariantDateTime]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.FormatUtcInvariantDateTime] (@value datetime)
	RETURNS nvarchar(255)
	AS
	BEGIN
		-- UTC format: invariant culture
		DECLARE @utc datetime
		SET @utc = dateadd(hh, datediff(hh, getdate(), getutcdate()), @value)
		RETURN convert(nvarchar, @utc, 101) + ' ' + convert(nvarchar, @utc, 108)
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.FormatDateTimeDiff]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.FormatDateTimeDiff]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.FormatDateTimeDiff] (
		@start_time datetime,
		@end_time datetime)
	RETURNS nvarchar(255)
	AS
	BEGIN
		RETURN convert(varchar,(@end_time - @start_time), 108)
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.RightTrimString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.RightTrimString]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.RightTrimString] (
		@str nvarchar(max),
		@pattern nvarchar(255))
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @internal_pattern nvarchar(max)
		SET @internal_pattern = [dbo].[fn.Upgrade.FormatString]('%%[%s]', @pattern)

		DECLARE @index bigint
		SET @index = -1

		WHILE (1 = 1)
		BEGIN
			DECLARE @position bigint		
			SET @position = patindex(@internal_pattern, @str)
			IF (@position = 0)
				BREAK
	
			SET @index = @position - 1
			SET @internal_pattern = @internal_pattern + '_'
		END

		IF (@index <> -1)
			SET @str = substring(@str, 1, @index)

		RETURN @str
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.GetJobSourceTypeName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.GetJobSourceTypeName]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.GetJobSourceTypeName] (
		@type int)
	RETURNS nvarchar(255)
	AS
	BEGIN
		-- EJobSourceType.Files = 3
		IF (@type = 3)
			RETURN 'folder'

		RETURN 'VM'
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.GetTaskSourceModeName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.GetTaskSourceModeName]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.GetTaskSourceModeName] (
		@source_mode int)
	RETURNS nvarchar(255)
	AS
	BEGIN
		-- Unknown = 0
		-- NetSsh = 1
		-- NetNfc = 2
		-- VcbSan = 3
		-- VcbNbd = 4
		-- VcbNbdSsl = 5
		-- VddkSanNbd = 6
		-- VddkSanNbdSsl = 7
		-- VddkSan = 8
		-- VddkNbd = 9
		-- VddkNbdSsl = 10
		-- VddkHotAdd = 11
		-- VddkHotAddNbd = 12

		IF (@source_mode = 1)   -- NetSsh
			RETURN 'service console agent'

		IF (@source_mode = 2)   -- NetNfc
			RETURN 'agentless'

		IF (@source_mode = 3)   -- VcbSan
			RETURN 'VCB SAN'

		IF (@source_mode = 4)   -- VcbNbd
			RETURN 'VCB NBD'

		IF (@source_mode = 5)   -- VcbNbdSsl
			RETURN 'VCB NBDSSL'

		IF (@source_mode = 6)   -- VddkSanNbd
			RETURN 'SAN/NBD'

		IF (@source_mode = 7)   -- VddkSanNbdSsl
			RETURN 'SAN/NBDSSL'

		IF (@source_mode = 8)   -- VddkSan
			RETURN 'SAN'

		IF (@source_mode = 9)   -- VddkNbd
			RETURN 'NBD'

		IF (@source_mode = 10)  -- VddkNbdSsl
			RETURN 'VCB SAN'

		IF (@source_mode = 11)  -- VddkHotAdd
			RETURN 'HOTADD'

		IF (@source_mode = 12)  -- VddkHotAddNbd
			RETURN 'HOTADD/NBD'

		RETURN ''               -- Unknown
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.GetTaskChangeTrackingName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.GetTaskChangeTrackingName]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.GetTaskChangeTrackingName] (
		@source_mode int,
		@change_tracking bit)
	RETURNS nvarchar(255)
	AS
	BEGIN
		-- VddkSanNbd = 6
		-- VddkSanNbdSsl = 7
		-- VddkSan = 8
		-- VddkNbd = 9
		-- VddkNbdSsl = 10
		-- VddkHotAdd = 11
		-- VddkHotAddNbd = 12

		IF (@source_mode >= 6 AND @source_mode <= 12)
		BEGIN
			DECLARE @str nvarchar(255)

			IF (@change_tracking = 'TRUE')
				SET @str = 'with'
			ELSE
				SET @str = 'without'

			RETURN [dbo].[fn.Upgrade.FormatString]('%s changed block tracking', @str)
		END

		RETURN ''
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.InternalKb]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.InternalKb]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.InternalKb] (
		@size bigint)
	RETURNS float
	AS
	BEGIN
		RETURN @size / 1024.;
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.InternalMb]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.InternalMb]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.InternalMb] (
		@size bigint)
	RETURNS float
	AS
	BEGIN
		RETURN @size / 1048576.;
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.InternalGb]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.InternalGb]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.InternalGb] (
		@size bigint)
	RETURNS float
	AS
	BEGIN
		RETURN @size / 1073741824.;
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.InternalTb]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.InternalTb]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.InternalTb] (
		@size bigint)
	RETURNS float
	AS
	BEGIN
		RETURN @size / 1099511627776.;
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.FormatSize]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.FormatSize]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.FormatSize] (
		@size bigint)
	RETURNS nvarchar(255)
	AS
	BEGIN
		IF ([dbo].[fn.Upgrade.InternalMb](@size) < 1)
			RETURN [dbo].[fn.Upgrade.FormatString]('%s KB', cast([dbo].[fn.Upgrade.InternalKb](@size) as decimal(18, 2)))

		IF ([dbo].[fn.Upgrade.InternalGb](@size) < 1)
			RETURN [dbo].[fn.Upgrade.FormatString]('%s MB', cast([dbo].[fn.Upgrade.InternalMb](@size) as decimal(18, 2)))

		IF ([dbo].[fn.Upgrade.InternalTb](@size) < 1)
			RETURN [dbo].[fn.Upgrade.FormatString]('%s GB', cast([dbo].[fn.Upgrade.InternalGb](@size) as decimal(18, 2)))

		RETURN [dbo].[fn.Upgrade.FormatString]('%s TB', cast([dbo].[fn.Upgrade.InternalTb](@size) as decimal(18, 2)))
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.FormatRate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.FormatRate]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.FormatRate] (
		@speed bigint)
	RETURNS nvarchar(255)
	AS
	BEGIN
		IF ([dbo].[fn.Upgrade.InternalMb](@speed) < 1)
			RETURN [dbo].[fn.Upgrade.FormatString]('%s KB/s', round([dbo].[fn.Upgrade.InternalKb](@speed), 0))

		IF ([dbo].[fn.Upgrade.InternalGb](@speed) < 1)
			RETURN [dbo].[fn.Upgrade.FormatString]('%s MB/s', round([dbo].[fn.Upgrade.InternalMb](@speed), 0))

		RETURN [dbo].[fn.Upgrade.FormatString]('%s GB/s', round([dbo].[fn.Upgrade.InternalGb](@speed), 0))
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.CreateXmlLogRecord]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.CreateXmlLogRecord]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.CreateXmlLogRecord] (
		@id bigint,
		@time datetime,
		@title nvarchar(max),
		@make_pretty bit)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @str_time nvarchar(255)
		DECLARE @str_utc_time nvarchar(255)

		IF (@make_pretty = 'TRUE')
		BEGIN
			SET @title = ltrim(@title)
			SET @title = [dbo].[fn.Upgrade.RightTrimString](@title, '. ')
			SET @title = [dbo].[fn.Upgrade.EscapeText](@title)
		END

		SET @str_time = [dbo].[fn.Upgrade.FormatIso8601DateTime](@time)
		SET @str_utc_time = [dbo].[fn.Upgrade.FormatUtcInvariantDateTime](@time)

		RETURN
			'<Log Cookie="" Usn="0" Status="ESucceeded" Style="ENone" Id="' + cast(@id as nvarchar(255)) + 
			'" Time="' + @str_time + '" UtcTime="' + @str_utc_time + '" StartTime="' + @str_time + 
			'"><Title>' + @title + '</Title><Description/></Log>'
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.CreateBackupJobSessionXmlLog]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.CreateBackupJobSessionXmlLog]
GO

	CREATE FUNCTION [dbo].[fn.Upgrade.CreateBackupJobSessionXmlLog] (
		@start_time datetime,
		@end_time datetime,
		@operation nvarchar(400),
		@description nvarchar(max),
		@total_objects int,
		@total_size bigint,
		@processed_size bigint,
		@average_speed bigint,
		@job_source_type int,
		@successes int,
		@warnings int,
		@failures int)
	RETURNS nvarchar(max)
	AS
	BEGIN
		-- Calculations

		DECLARE @session_info_count int
		SET @session_info_count = @failures + @warnings + @successes

		DECLARE @str_job_source_type nvarchar(255)
		SET @str_job_source_type = [dbo].[fn.Upgrade.GetJobSourceTypeName](@job_source_type)
	
		-- Build Report

		DECLARE @result nvarchar(max)
		SET @result = ''

		SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
			0,
			@start_time,
			[dbo].[fn.Upgrade.FormatString5]('%s of %s %s processed (%s failed, %s warnings)', @session_info_count, @total_objects, @str_job_source_type, @failures, @warnings),
			'FALSE')

		SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
			1,
			@start_time,
			[dbo].[fn.Upgrade.FormatString2]('Total size of %s to backup: %s', @str_job_source_type, [dbo].[fn.Upgrade.FormatSize](@total_size)),
			'FALSE')

		SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
			2,
			@start_time,
			'Processed size: ' + [dbo].[fn.Upgrade.FormatSize](@processed_size),
			'FALSE')

		SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
			3,
			@start_time,
			'Processing rate: ' + [dbo].[fn.Upgrade.FormatRate](@average_speed),
			'FALSE')

		SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
			4,
			@start_time,
			'Start time: ' + [dbo].[fn.Upgrade.FormatDateTime](@start_time), --cast(@start_time as nvarchar(255)),
			'FALSE')

		SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
			5,
			@start_time,
			'End time: ' + [dbo].[fn.Upgrade.FormatDateTime](@end_time), --cast(@end_time as nvarchar(255)),
			'FALSE')

		SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
			6,
			@start_time,
			'Duration: ' + [dbo].[fn.Upgrade.FormatDateTimeDiff](@start_time, @end_time),
			'FALSE')

		DECLARE @index bigint
		SET @index = 6

		DECLARE @sep nvarchar(255)
		SET @sep = '. '

		IF ([dbo].[fn.Upgrade.IsBlankString](@operation) = 'FALSE' OR [dbo].[fn.Upgrade.IsBlankString](@description) = 'FALSE')
		BEGIN
			DECLARE @text nvarchar(max)
			SET @text = @operation + @sep + @description
			SET @text = replace(@text, [dbo].[fn.Upgrade.TransformCppText]('\r'), '')
			SET @text = replace(@text, [dbo].[fn.Upgrade.TransformCppText]('\n'), @sep)

			SELECT
				@index = @index + 1,
				@result = coalesce(@result + '', '') + [dbo].[fn.Upgrade.CreateXmlLogRecord](@index, @start_time, [element], 'TRUE')
			FROM
				[dbo].[fn.Upgrade.SplitString](@text, @sep)
			WHERE
				[dbo].[fn.Upgrade.IsBlankString]([element]) = 'FALSE'
		END

		RETURN '<log_xml><Root TotalUsn="0" TotalId="' + cast(@index as nvarchar(255)) + '">' + @result + '</Root></log_xml>'
	END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.CreateBackupTaskSessionXmlLog]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.CreateBackupTaskSessionXmlLog]
GO
CREATE FUNCTION [dbo].[fn.Upgrade.CreateBackupTaskSessionXmlLog]
(    
		@start_time datetime,
		@end_time datetime,
		@operation nvarchar(400),
		@reason nvarchar(max),
		@total_objects int,
		@total_size bigint,
		@processed_objects int,
		@processed_size bigint,
		@average_speed bigint,
		@source_mode int,
		@change_tracking bit,
		@job_source_type int
)
RETURNS nvarchar(max) 
AS
BEGIN 
	DECLARE @result nvarchar(max)
	SET @result = ''

	SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
		0,
		@start_time,
		[dbo].[fn.Upgrade.FormatString2]('%s of %s files processed', @processed_objects, @total_objects),
		'FALSE')

	SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
		1,
		@start_time,
		[dbo].[fn.Upgrade.FormatString2]('Total %s size: %s', [dbo].[fn.Upgrade.GetJobSourceTypeName](@job_source_type), [dbo].[fn.Upgrade.FormatSize](@total_size)),
		'FALSE')

	SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
		2,
		@start_time,
		'Processed size: ' + [dbo].[fn.Upgrade.FormatSize](@processed_size),
		'FALSE')

	SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
		3,
		@start_time,
		'Processing rate: ' + [dbo].[fn.Upgrade.FormatRate](@average_speed),
		'FALSE')

	SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
		4,
		@start_time,
		[dbo].[fn.Upgrade.FormatString2]('Backup mode: %s %s', [dbo].[fn.Upgrade.GetTaskSourceModeName](@source_mode), [dbo].[fn.Upgrade.GetTaskChangeTrackingName](@source_mode, @change_tracking)),
		'FALSE')

	SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
		5,
		@start_time,
		'Start time: ' + [dbo].[fn.Upgrade.FormatDateTime](@start_time), --cast(@start_time as nvarchar(255)),
		'FALSE')

	SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
		6,
		@start_time,
		'End time: ' + [dbo].[fn.Upgrade.FormatDateTime](@end_time), --cast(@end_time as nvarchar(255)),
		'FALSE')

	SET @result = @result + [dbo].[fn.Upgrade.CreateXmlLogRecord](
		7,
		@start_time,
		'Duration: ' + [dbo].[fn.Upgrade.FormatDateTimeDiff](@start_time, @end_time),
		'FALSE')

	DECLARE @index bigint
	SET @index = 7

	DECLARE @sep nvarchar(255)
	SET @sep = '. '

	IF ([dbo].[fn.Upgrade.IsBlankString](@operation) = 'FALSE' OR [dbo].[fn.Upgrade.IsBlankString](@reason) = 'FALSE')
	BEGIN
		DECLARE @text nvarchar(max)
		SET @text = @operation + @sep + @reason
		SET @text = replace(@text, [dbo].[fn.Upgrade.TransformCppText]('\r'), '')
		SET @text = replace(@text, [dbo].[fn.Upgrade.TransformCppText]('\n'), @sep)

		SELECT
			@index = @index + 1,
			@result = coalesce(@result + '', '') + [dbo].[fn.Upgrade.CreateXmlLogRecord](@index, @start_time, [element], 'TRUE')
		FROM
			[dbo].[fn.Upgrade.SplitString](@text, @sep)
		WHERE
			[dbo].[fn.Upgrade.IsBlankString]([element]) = 'FALSE'
	END

	RETURN '<log_xml><Root TotalUsn="0" TotalId="' + cast(@index as nvarchar(255)) + '">' + @result + '</Root></log_xml>'
END
GO
-----------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Repl.Topology.UpdateBackupServerEMName]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Repl.Topology.UpdateBackupServerEMName]
GO
CREATE PROCEDURE [usp.Repl.Topology.UpdateBackupServerEMName]
	@backup_server_id uniqueidentifier,
	@em_name NVARCHAR(255) = null
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Repl.Topology.BackupServers]
	SET [em_name] = @em_name
	WHERE [id] = @backup_server_id
END

GO
-----------------------------------------------------



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Backup.GetSessionLogData]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Backup.GetSessionLogData]
GO
CREATE FUNCTION [dbo].[fn.Backup.GetSessionLogData]
(
	@log_xml xml
)
RETURNS @log_table table
(
	id int PRIMARY KEY,
	created_time datetime,
	title nvarchar(max),
	status nvarchar(max)
) 
AS
BEGIN
	
	declare @log_idx int
	declare @log_str  nvarchar(max)
	declare @log_date_str nvarchar(50)
	declare @log_date datetime
	declare @log_datexml xml
	declare @log_status nvarchar(max)
	
	
	set @log_idx = 1;
	set @log_str = @log_xml.value('(/log_xml/Root/Log[@Id=sql:variable("@log_idx")])[1]', 'nvarchar(max)')
	
	while @log_str is not null
	begin
		-- we make our datetime look like UTC to keep time part (xs:datetime "normalizes" datetime to UTC)
		set @log_date_str = LEFT(@log_xml.value('(/log_xml/Root/Log[@Id=sql:variable("@log_idx")])[1]/@Time', 'nvarchar(50)'), 27) + 'Z'	
		set @log_status = @log_xml.value('(/log_xml/Root/Log[@Id=sql:variable("@log_idx")])[1]/@Status', 'nvarchar(max)')		
		
		SET @log_datexml = cast('' AS xml) --Gets an empty Xml datatype
		SET @log_date = @log_datexml.value('xs:dateTime(sql:variable("@log_date_str"))', 'datetime')
		
		INSERT INTO @log_table(id, created_time, title, status )
		VALUES
		(
			@log_idx, 
			@log_date,
			@log_str,
			@log_status
		)
		
		set @log_idx = @log_idx + 1	
		set @log_str = @log_xml.value('(/log_xml/Root/Log[@Id=sql:variable("@log_idx")])[1]', 'nvarchar(max)')	
	end
	
	return 
END
GO
---------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.RestoreVmJobSessions.GetAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.RestoreVmJobSessions.GetAll]
GO
CREATE PROCEDURE [dbo].[usp.Data.RestoreVmJobSessions.GetAll]
	@limit int,
	@offset int,
	@order_column nvarchar(255) = 'creation_time',
	@order_asc bit  = 0,
	@initiator_sid nvarchar(255) = NULL,
	@total_records int OUT
AS
BEGIN
	SET NOCOUNT ON;
	
	SET @total_records = (
		SELECT COUNT(s.id)
		FROM 
		[dbo].[C.Backup.Model.JobSessions] s,
		[dbo].[C.Backup.Model.RestoreJobSessions] rs
		WHERE  s.job_type = 4 AND s.id = rs.id -- Restore vm job
	)
	
	SELECT *
	FROM (
SELECT 
		ROW_NUMBER() OVER(
			ORDER BY
				CASE @order_asc 
					WHEN 1 THEN
						CASE @order_column
							WHEN 'creation_time'		THEN s.creation_time 
							WHEN 'end_time'				THEN s.end_time 
							WHEN 'state'				THEN s.state 
							WHEN 'initiator_name'		THEN rs.initiator_name 
							WHEN 'result'				THEN s.result 
							ELSE  s.creation_time 
						END
					ELSE 1 
				END ASC,
				CASE @order_asc 
					WHEN 0 THEN
						CASE @order_column
							WHEN 'creation_time'		THEN s.creation_time 
							WHEN 'end_time'				THEN s.end_time 
							WHEN 'state'				THEN s.state 
							WHEN 'initiator_name'		THEN rs.initiator_name 
							WHEN 'result'				THEN s.result 
							ELSE  s.creation_time 
						END
					ELSE 1 
				END DESC
		) AS row_num, 
		s.[id],
			s.[creation_time],
			s.[end_time],
			s.[job_name],
			s.[job_type],
			s.[job_id],
			s.[result],
			s.[description],
			s.[operation],
			s.[state],
			rs.[options],
			rs.[initiator_sid],
			rs.[initiator_name],	
			rs.[reason],
			rs.[platform],
			rs.[multi_restore_id],
			rs.[restore_type],
			rs.[oib_id],
			rs.[is_internal],
			rs.[oib_display_name],
			rs.[oib_creation_time],
			rs.[sub_type]
	FROM [dbo].[C.Backup.Model.JobSessions] s 
	JOIN [dbo].[C.Backup.Model.RestoreJobSessions] rs ON (s.id = rs.id)
	WHERE rs.initiator_sid = 
		CASE 
			WHEN @initiator_sid IS NULL THEN rs.initiator_sid
			ELSE @initiator_sid
		END	
	) AS result
	WHERE result.row_num BETWEEN @offset AND @limit+@offset
	ORDER BY row_num
END

GO
--------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.JobSession.GetLogForSession]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.JobSession.GetLogForSession]
GO

CREATE PROCEDURE [dbo].[usp.Data.JobSession.GetLogForSession]
	@session_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @xml_log xml
	
	SELECT @xml_log = log_xml FROM [dbo].[C.Backup.Model.JobSessions] WHERE id = @session_id
	SELECT * FROM [dbo].[fn.Backup.GetSessionLogData](@xml_log)
END
GO
---------------------------------------------------
-- Restore scope
--------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.GetUniqueObject]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.GetUniqueObject]
GO

CREATE FUNCTION [dbo].[fn.GetUniqueObject]
(
	@object_id uniqueidentifier
)
RETURNS 
@object TABLE 
(
	object_ref nvarchar(max),
	host_uuid nvarchar(max)
)
AS
BEGIN
	INSERT @object (object_ref, host_uuid)(
		SELECT TOP 1 o.object_id, ph.bios_uuid as host_uuid 
		FROM dbo.[C.BObjects] o
		JOIN dbo.[C.Hosts] h ON (o.host_id = h.id)
		JOIN dbo.[C.PhysicalHosts] ph ON (h.physical_host_id = ph.id)
		WHERE o.id = @object_id
		
	)
	RETURN 
END
GO


---------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.C.BJobs.GetAllBJobsRefs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.C.BJobs.GetAllBJobsRefs]
GO
CREATE PROCEDURE [dbo].[usp.Data.C.BJobs.GetAllBJobsRefs]
AS
BEGIN
	SET NOCOUNT ON;

    select id as job_id, name as job_name from [dbo].[C.BJobs]
END

GO

---------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.C.BJobs.GetAllBJobsDescriptions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.C.BJobs.GetAllBJobsDescriptions]
GO	
CREATE PROCEDURE [dbo].[usp.Data.C.BJobs.GetAllBJobsDescriptions]
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT 
		[dbo].[C.BJobs].[id] as job_id,
		[dbo].[C.BJobs].[name] as job_name,
		[dbo].[C.BJobs].[type] as job_type,
		[dbo].[C.BJobs].[description] as job_description,
		[dbo].[C.BJobs].[schedule],
		[dbo].[C.BJobs].[options],
		[dbo].[C.BJobs].[schedule_enabled],
		[dbo].[C.BJobs].[db_instance_id]
	FROM [dbo].[C.BJobs]

END
GO

---------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.C.BJobs.FindBJobDescriptionByNameProc]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.C.BJobs.FindBJobDescriptionByNameProc]
GO	
CREATE PROCEDURE [dbo].[usp.Data.C.BJobs.FindBJobDescriptionByNameProc]
	@job_name as nvarchar(255)
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT TOP(1) [dbo].[C.BJobs].[id] as job_id,
		[dbo].[C.BJobs].[type] as job_type,
		[dbo].[C.BJobs].[description] as job_description,
		[dbo].[C.BJobs].[schedule],
		[dbo].[C.BJobs].[options],
		[dbo].[C.BJobs].[schedule_enabled],
		[dbo].[C.BJobs].[db_instance_id]
	FROM [dbo].[C.BJobs]
	WHERE [dbo].[C.BJobs].[name] = @job_name

END
GO

---------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.C.ObjectsInJobs.GetBJobObjectsInJob]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Data.C.ObjectsInJobs.GetBJobObjectsInJob]
GO	
CREATE PROCEDURE [dbo].[usp.Data.C.ObjectsInJobs.GetBJobObjectsInJob]
	@job_id as 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].[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].[platform],
		[dbo].[C.BObjects].[uuid]
	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


----------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.Security.RoleAccounts.GetAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.Security.RoleAccounts.GetAll]
GO

CREATE PROCEDURE [dbo].[usp.Data.Security.RoleAccounts.GetAll]
	@limit int,
	@offset int,
	@order_column nvarchar(255) = 'roles_account',
	@order_asc bit  = 0,
	@total_records int OUT
AS
BEGIN
SET NOCOUNT ON;
	
	SET @total_records = (
		SELECT COUNT( DISTINCT ra.role_group_id)
		FROM 
		[dbo].[Security.RoleAccounts] ra
	);
	
	SELECT *
	FROM (
		SELECT 
				ROW_NUMBER() OVER(
					ORDER BY
						CASE @order_asc 
							WHEN 1 THEN
								CASE @order_column
									WHEN 'roles_name'		THEN ro.[name] 
									WHEN 'roles_account'	THEN ac.[name] 
									ELSE  ro.[name]  
								END
							ELSE 1 
						END ASC,
						CASE @order_asc 
							WHEN 0 THEN
								CASE @order_column
									WHEN 'roles_name'		THEN ro.[name]  
									WHEN 'roles_account'	THEN ac.[name] 
									ELSE  ro.[name] 
								END
							ELSE 1
						END DESC
				) AS row_num, 
					ra.[role_group_id] as id,
					ac.[type] as roles_acctype,
					ac.[name] as roles_account,
					ro.[name] as roles_name,
					ro.[number] as roles_number,
					ra.[settings]
				FROM 
					[dbo].[Security.RoleAccounts] ra, [dbo].[Security.Accounts] ac, [dbo].[Security.Roles] ro
				WHERE 
					ra.[account_id] = ac.[id] AND
					ra.[role_id] = ro.[id]
					AND (ro.[number] != 3 AND ro.[number] != 4)
		

	) AS result
	WHERE result.row_num BETWEEN @offset AND @limit+@offset
	ORDER BY row_num
    
END
GO
---------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.RemoveRoleAccountGroup]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Security.RemoveRoleAccountGroup]
GO
CREATE PROCEDURE [dbo].[usp.Security.RemoveRoleAccountGroup]
	@role_group_id uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;	
	
	DECLARE @account_id uniqueidentifier

	SELECT TOP(1) @account_id = [dbo].[Security.Accounts].[id] 
	FROM [dbo].[Security.Accounts] 
	INNER JOIN [dbo].[Security.RoleAccounts] ON [dbo].[Security.Accounts].[id] = [dbo].[Security.RoleAccounts].[account_id]
	WHERE [dbo].[Security.RoleAccounts].[role_group_id] = @role_group_id

	DELETE FROM [dbo].[Security.Accounts]
	WHERE [dbo].[Security.Accounts].[id] = @account_id
		
	DELETE FROM [dbo].[Security.LoggedOnUsers] WHERE id IN (
		SELECT lou.login_id FROM [dbo].[Security.LoggedOnUserAccounts] lou
		JOIN [dbo].[Security.LoggedOnUserAccounts] lou2 ON (lou2.login_id = lou.login_id)
		WHERE lou.account_id = @account_id
		GROUP BY lou.login_id HAVING COUNT(lou2.login_id) = 1
	)
		
	DELETE FROM [dbo].[Security.LoggedOnUserAccounts] WHERE account_id = @account_id;

	-- DELETE scopes
	DELETE FROM [dbo].[Security.HierarchyScopes] 
	WHERE [dbo].[Security.HierarchyScopes].[role_account_group_id] = @role_group_id;

	DELETE FROM [dbo].[Security.RoleAccounts]
	WHERE [dbo].[Security.RoleAccounts].[role_group_id] = @role_group_id
END
GO


--------------------------------------------------------------------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.GetLoggedUserInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Security.GetLoggedUserInfo]
GO
CREATE PROCEDURE [dbo].[usp.Security.GetLoggedUserInfo]
	@sid_binary varbinary(max)
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [dbo].[Security.LoggedOnUsers] WHERE sid_binary = @sid_binary;
END
GO
--------------------------------------------------------------------------------------------------------------------------------------


--------------------------------------------------------------------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.SaveLoggedUserInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Security.SaveLoggedUserInfo]
GO

CREATE PROCEDURE [dbo].[usp.Security.SaveLoggedUserInfo]
	@username varchar(max),
	@sid varchar(max),
	@sid_binary varbinary(max),
	@id uniqueidentifier OUT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @login_id uniqueidentifier;
	
	SELECT @login_id=id FROM [dbo].[Security.LoggedOnUsers] WHERE sid_string = @sid;
	
	
	IF (@login_id IS NULL)
	BEGIN
		SET @login_id = NEWID();
		INSERT INTO [dbo].[Security.LoggedOnUsers](id, sid_string, sid_binary, name)
		VALUES (@login_id, @sid, @sid_binary, @username)
	END
	
	SET @id = @login_id;
END

GO
--------------------------------------------------------------------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.RemoveLoggedUserInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Security.RemoveLoggedUserInfo]
GO

CREATE PROCEDURE [dbo].[usp.Security.RemoveLoggedUserInfo]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	DELETE FROM [dbo].[Security.LoggedOnUsers] WHERE id = @id;
	DELETE FROM [dbo].[Security.LoggedOnUserAccounts] WHERE login_id = @id;
	
END

GO
--------------------------------------------------------------------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.AddLoggedUserAccounts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Security.AddLoggedUserAccounts]
GO

CREATE PROCEDURE [dbo].[usp.Security.AddLoggedUserAccounts]
	@login_id uniqueidentifier,
	@account_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	if NOT EXISTS (SELECT id FROM [dbo].[Security.LoggedOnUserAccounts] WHERE login_id = @login_id AND account_id = @account_id)
	BEGIN	
		INSERT INTO [dbo].[Security.LoggedOnUserAccounts] (id, account_id, login_id) 
		VALUES (NEWID(), @account_id, @login_id)
	END
	
END
GO
---------------------------------------------------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.ClearLoggedUsers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Security.ClearLoggedUsers]
GO

CREATE PROCEDURE [dbo].[usp.Security.ClearLoggedUsers]
AS
BEGIN
	DELETE FROM [dbo].[Security.LoggedOnUserAccounts];
	DELETE FROM [dbo].[Security.LoggedOnUsers]
END

GO
--------------------------------------------------------------------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.IsFoldersContainHost]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.IsFoldersContainHost]
GO

CREATE FUNCTION [dbo].[fn.IsFoldersContainHost]
		(@row_host_id uniqueidentifier)
	RETURNS BIT
	AS
	BEGIN
	DECLARE @hifsCount int
	SELECT 
		@hifsCount = COUNT(*) 
	FROM [dbo].[C.Folder_Host] 
	WHERE [host_id] = @row_host_id
	DECLARE @res BIT
	IF (@hifsCount > 0)
		SET @res = 1
	ELSE
		SET @res = 0
		RETURN @res
	END	


GO
--------------------------------------------------------------------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.IsImportedHost]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.IsImportedHost]
GO

CREATE FUNCTION [dbo].[fn.IsImportedHost]
		(@host_id uniqueidentifier)
	RETURNS BIT
	AS
	BEGIN
		DECLARE @local_host_id uniqueidentifier
		SET @local_host_id = '6745a759-2205-4cd2-b172-8ec8f7e60ef8'
		IF (@host_id <> @local_host_id)
		BEGIN
			DECLARE @count int
			SELECT 
				@count = COUNT(*) 
			FROM 
				[C.Hosts]
			WHERE 
				[id] = @host_id AND 
				[parent_id] = '00000000-0000-0000-0000-000000000000' AND 
				[dbo].[fn.IsFoldersContainHost]([id]) = 'False'
			DECLARE @res BIT
			IF (@count > 0)
				SET @res = 1
			ELSE
				SET @res = 0
		END
		ELSE
			SET @res = 0
		RETURN @res
	END	
GO
--------------------------------------------------------------------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.IsRootVisibleHost]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.IsRootVisibleHost]
GO

	CREATE FUNCTION [dbo].[fn.IsRootVisibleHost]
		(@host_id uniqueidentifier)
	RETURNS BIT
	AS	
	BEGIN
		DECLARE @local_host_id uniqueidentifier
		SET @local_host_id = '6745a759-2205-4cd2-b172-8ec8f7e60ef8'
		DECLARE @unknown_vi_host_id uniqueidentifier
		SET @unknown_vi_host_id = 'D7B57228-436F-4241-A128-04FA5FCFA032'
		DECLARE @vcType int
		SET @vcType = 1
		DECLARE @res BIT
		IF (@host_id = @local_host_id)
			SET @res = 1
		ELSE IF (@host_id = @unknown_vi_host_id)
			SET @res = 0
		ELSE
		BEGIN
			DECLARE @count int
			SELECT 
				@count = COUNT(*) 
			FROM 
				[C.Hosts] 
			WHERE 
				[id] = @host_id AND 
				([parent_id] = '00000000-0000-0000-0000-000000000000' OR [type] = @vcType) AND ([dbo].[fn.IsImportedHost]([id]) = 'False')			
			IF (@count > 0)
				SET @res = 1
			ELSE
				SET @res = 0
		END		
		RETURN @res
	END		


GO
--------------------------------------------------------------------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.GetRootHosts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.GetRootHosts]
GO

CREATE PROCEDURE [dbo].[usp.Data.GetRootHosts]
AS
BEGIN
		SELECT 
			h.* 
		FROM 
			[dbo].[C.Hosts] h 
			LEFT JOIN [dbo].[C.Hosts] ph ON (h.parent_id = ph.id )
			INNER JOIN [dbo].[Repl.Topology.BackupDbInstances] db ON h.[db_instance_id] = db.[id]
		WHERE 
			(h.[type] !=3 AND h.[parent_id] = '00000000-0000-0000-0000-000000000000'  AND [dbo].[fn.IsRootVisibleHost](h.[id]) = 'True')
		OR 
			(h.[type] = 1 AND ph.parent_id = '00000000-0000-0000-0000-000000000000' AND ph.type = 14 AND [dbo].[fn.IsImportedHost](h.[id]) = 'False')
END
GO
---------------------------------------------------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.OIBs.GetByObjectId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.OIBs.GetByObjectId]
GO

CREATE PROCEDURE [dbo].[usp.Data.OIBs.GetByObjectId]
	@object_id as uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;
	
	select 
	* 
	from 
		[dbo].[C.Backup.Model.OIBs] oibs
	where
	 oibs.object_id = @object_id
	 AND oibs.is_corrupted = 0
	 ORDER BY oibs.creation_time DESC

END
GO
---------------------------------------------------------------------------------------------------------------------------------------

-- Backup.Model.GetUncorruptedOibsByOriginalOibId
PRINT N'Creating [dbo].[usp.Data.OIBs.GetOibsByOriginalOibId]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.OIBs.GetUncorruptedOibsByOriginalOibId]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[usp.Data.OIBs.GetUncorruptedOibsByOriginalOibId]
GO

	CREATE PROCEDURE [dbo].[usp.Data.OIBs.GetUncorruptedOibsByOriginalOibId]
		@original_oib_id   uniqueidentifier
	AS
	BEGIN
		SET NOCOUNT ON;	
		
		select *
		from [dbo].[C.Backup.Model.OIBs] oibs
		where oibs.[original_oib_id] = @original_oib_id AND oibs.is_corrupted = 0
		
	END
GO
---------------------------------------------------------------------------------------------------------------------------------------


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.GetAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.GetAll]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.GetAll]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  
	SELECT * FROM [dbo].[Security.HierarchyScopes];
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.GetForLoggedOnUser]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.GetForLoggedOnUser]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.GetForLoggedOnUser]
	@userSid varbinary(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
  
	SELECT * FROM [dbo].[Security.HierarchyScopes] s
	JOIN [dbo].[Security.RoleAccounts] ra ON (ra.role_group_id = s.role_account_group_id)
	JOIN [dbo].[Security.Accounts] ac ON (ac.id = ra.account_id)
	JOIN [dbo].[Security.LoggedOnUserAccounts] lua ON (lua.account_id = ac.id)
	JOIN [dbo].[Security.LoggedOnUsers] lu ON (lu.id = lua.login_id)
	WHERE lu.sid_binary = @userSid;
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.GetForAccount]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.GetForAccount]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.GetForAccount]
	@accountId uniqueidentifier
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
  
	SELECT * FROM [dbo].[Security.HierarchyScopes] s
	JOIN [dbo].[Security.RoleAccount] ra ON (ra.role_group_id = s.role_account_group_id)
	JOIN [dbo].[Security.Accounts] ac ON (ac.id = ra.account_id)
	WHERE ac.id = @accountId;
END
GO



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Backup.MakeObjectHash]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Backup.MakeObjectHash]
GO
CREATE FUNCTION [dbo].[fn.Backup.MakeObjectHash]
(    
    @hierarchy_object_ref nvarchar(434),
	@hierarchy_root_instance_id nvarchar(255)
)
RETURNS varbinary(16) 
AS
BEGIN 

	declare @hash varbinary(16)
	set @hash = HASHBYTES('MD5', CONVERT(varchar(max), @hierarchy_object_ref) + CONVERT(varchar(max), @hierarchy_root_instance_id))	
    RETURN @hash
END
GO


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Common.FromBase64]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Common.FromBase64]
GO
CREATE FUNCTION [dbo].[fn.Common.FromBase64]
(
	@str varchar(max)
)
RETURNS varbinary(max)
AS
BEGIN 

	declare @bin varbinary(max)
	SET @bin = CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@str"))', 'VARBINARY(MAX)');		
    RETURN @bin
END
GO


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Common.ToBase64]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Common.ToBase64]
GO
CREATE FUNCTION [dbo].[fn.Common.ToBase64]
(
	@bin varbinary(max)
)
RETURNS varchar(max) 
AS
BEGIN 

	declare @str varchar(max)
	SET @str = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'VARCHAR(MAX)')
    RETURN @str
END
GO
-----------------------------------------------------


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.SyncRoleAccountScopes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.SyncRoleAccountScopes]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.SyncRoleAccountScopes]
	@objId uniqueidentifier,
	@changesDoc xml
AS
BEGIN
	SET NOCOUNT ON;
  
    DECLARE @changes table
    (
		[id] uniqueidentifier,
        [scope_state] int, 
        [scope_name] nvarchar(255),
        [hierarchy_root_instance_id] nvarchar(255),
        [hierarchy_object_ref] nvarchar(434),
        [hierarchy_view_type] int,
        [hierarchy_object_type] int,
        [platform] int
    )
    
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @changesDoc
    
    INSERT INTO @changes([id],[scope_state],[scope_name],[hierarchy_root_instance_id],[hierarchy_object_ref],[hierarchy_view_type],[hierarchy_object_type], [platform])
    SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (
        [id] uniqueidentifier 'id',
        [scope_state] int 'scope_state', 
        [scope_name] nvarchar(255) 'scope_name',
        [hierarchy_root_instance_id] nvarchar(255) 'hierarchy_root_instance_id',
        [hierarchy_object_ref] nvarchar(434) 'hierarchy_object_ref',
        [hierarchy_view_type] int 'hierarchy_view_type',
        [hierarchy_object_type] int 'hierarchy_object_type',
        [platform] int 'platform'
    )
    EXEC sp_xml_removedocument @idoc
	
	UPDATE [dbo].[Security.HierarchyScopes]
	SET [scope_state] = src.[scope_state],
        [scope_name]  = src.[scope_name],
        [hierarchy_root_instance_id] = src.[hierarchy_root_instance_id],
        [hierarchy_object_ref]  = src.[hierarchy_object_ref],
        [hierarchy_object_keyhash] = [dbo].[fn.Backup.MakeObjectHash](src.[hierarchy_object_ref], src.[hierarchy_root_instance_id]),
        [hierarchy_view_type]  = src.[hierarchy_view_type],
        [hierarchy_object_type] = src.[hierarchy_object_type],
        [platform] = src.[platform]
	FROM [dbo].[Security.HierarchyScopes] tgt INNER JOIN @changes src ON tgt.id = src.id
	WHERE tgt.[role_account_group_id] = @objId

	INSERT INTO [dbo].[Security.HierarchyScopes]
	SELECT 
		src.[id], 
		@objId, 
		src.[scope_state],
        src.[scope_name],
        src.[hierarchy_root_instance_id],
        src.[hierarchy_object_ref],
        [dbo].[fn.Backup.MakeObjectHash](src.[hierarchy_object_ref], src.[hierarchy_root_instance_id]),
        src.[hierarchy_view_type],
        src.[hierarchy_object_type],
        src.[platform]
	FROM @changes src LEFT OUTER JOIN [dbo].[Security.HierarchyScopes] tgt ON src.id = tgt.id
	WHERE tgt.id IS NULL

	DELETE [dbo].[Security.HierarchyScopes]
	FROM [dbo].[Security.HierarchyScopes] tgt LEFT OUTER JOIN @changes src ON tgt.id = src.id
	WHERE tgt.[role_account_group_id] = @objId AND src.id IS NULL

	
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.GetUnprocessed]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.GetUnprocessed]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.GetUnprocessed]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


	SELECT * FROM [dbo].[Security.HierarchyScopes] WHERE [scope_state] = 0;
END

GO
---------------------------------------------------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.GetForRoleAccountGroup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.GetForRoleAccountGroup]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.GetForRoleAccountGroup]
	@role_account_group_id uniqueidentifier
AS
BEGIN

	SET NOCOUNT ON;

	SELECT * FROM [dbo].[Security.HierarchyScopes] s
	WHERE s.[role_account_group_id] = @role_account_group_id

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.AddScope]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.AddScope]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.AddScope]
	@id uniqueidentifier,
	@role_account_group_id uniqueidentifier,
	@scope_name nvarchar(255),
	@hierarchy_object_ref nvarchar(255),
	@hierarchy_root_instance_id nvarchar(255),
	@hierarchy_view_type int,
	@hierarchy_object_type int,
	@hierarchy_object_platform int
AS
BEGIN

	SET NOCOUNT ON;
	
	DECLARE @hierarchy_object_keyhash varbinary(16)
	SET @hierarchy_object_keyhash = [dbo].[fn.Backup.MakeObjectHash](@hierarchy_object_ref, @hierarchy_root_instance_id)

	DECLARE @scope_state int
	SET @scope_state = 
		CASE 
			WHEN (SELECT TOP(1) scope_state FROM [dbo].[Security.HierarchyScopes] WHERE hierarchy_object_keyhash = @hierarchy_object_keyhash AND scope_state = 1) IS NOT NULL THEN 1
			ELSE 0
		END

	INSERT INTO [dbo].[Security.HierarchyScopes] 
		(id, role_account_group_id, scope_state, scope_name, hierarchy_object_ref, hierarchy_root_instance_id, 
		hierarchy_object_keyhash, hierarchy_view_type, hierarchy_object_type, platform)
	VALUES 
		(@id, 
		@role_account_group_id, 
		@scope_state, 
		@scope_name, 
		@hierarchy_object_ref, 
		@hierarchy_root_instance_id, 
		@hierarchy_object_keyhash, 
		@hierarchy_view_type, 
		@hierarchy_object_type,
		@hierarchy_object_platform);

END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.DeleteForRoleGroup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.DeleteForRoleGroup]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.DeleteForRoleGroup]
	@role_group_id uniqueidentifier
AS
BEGIN

	SET NOCOUNT ON;

	DELETE FROM [dbo].[Security.HierarchyScopes]  
	WHERE role_account_group_id = @role_group_id

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.ChangeState]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.ChangeState]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.ChangeState]
	@scopeId uniqueidentifier,
	@state int
AS
BEGIN

	SET NOCOUNT ON;

	UPDATE [dbo].[Security.HierarchyScopes]  
	SET [dbo].[Security.HierarchyScopes].[scope_state] = @state
	WHERE [dbo].[Security.HierarchyScopes].[id] = @scopeId
END
GO




PRINT N'Creating [dbo].[usp.Security.SyncVmsRecursiveInclusion]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.SyncVmsRecursiveInclusion]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[usp.Security.SyncVmsRecursiveInclusion]
GO
CREATE PROCEDURE [dbo].[usp.Security.SyncVmsRecursiveInclusion]
	@objId varbinary(16),
	@changesDoc xml
AS
BEGIN
	SET NOCOUNT ON;
  
    CREATE TABLE #syncVmsRecInclusion (
		[id] as [container_key_hash] + [vm_key_hash] PERSISTED NOT NULL,		
		[container_key_hash] varbinary(16),
        [vm_key_hash] varbinary(16),
		[vm_real_name] nvarchar(256)
        CONSTRAINT [PK_Security.VmsRecursiveInclusion] PRIMARY KEY CLUSTERED 
		(
			[id] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
		) ON [PRIMARY]
    
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @changesDoc
    
    INSERT INTO #syncVmsRecInclusion([container_key_hash],[vm_key_hash],[vm_real_name])
	SELECT @objId, [dbo].[fn.Common.FromBase64](vm_key_hash),vm_real_name
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (        
        [vm_key_hash] nvarchar(max) 'vm_key_hash',
		[vm_real_name] nvarchar(256) 'vm_real_name'
    )
    EXEC sp_xml_removedocument @idoc
    
    --select  * from #syncVmsRecInclusion
	
	UPDATE [dbo].[Security.VmsRecursiveInclusion]
	SET 
        [vm_key_hash]  = src.[vm_key_hash],
		[vm_real_name] = src.[vm_real_name]
    FROM [dbo].[Security.VmsRecursiveInclusion] tgt INNER JOIN #syncVmsRecInclusion src ON tgt.[id] = src.[id]
    WHERE tgt.[container_key_hash] = @objId

	INSERT INTO [dbo].[Security.VmsRecursiveInclusion] ([container_key_hash],[vm_key_hash],[vm_real_name])
	SELECT 
		@objId,
		src.[vm_key_hash],
		src.[vm_real_name]
	FROM #syncVmsRecInclusion src LEFT OUTER JOIN [dbo].[Security.VmsRecursiveInclusion] tgt ON src.[id] = tgt.[id]
	WHERE tgt.id IS NULL

	CREATE TABLE #VmsRecInclusion2delete([id] varbinary(32), [vm_key_hash] varbinary(16), vm_real_name nvarchar(255))
	INSERT INTO #VmsRecInclusion2delete
	SELECT tgt.id, tgt.vm_key_hash, tgt.vm_real_name	
	FROM [dbo].[Security.VmsRecursiveInclusion] tgt LEFT OUTER JOIN #syncVmsRecInclusion src ON tgt.[id] = src.[id]
	WHERE tgt.[container_key_hash] = @objId AND src.[id] IS NULL
	
	SELECT * from #VmsRecInclusion2delete toDelete	
	LEFT OUTER JOIN [dbo].[C.BObjects] objs ON toDelete.[vm_key_hash] = objs.[unique_key_hash]	
	LEFT OUTER JOIN [dbo].[C.Backup.Model.OIBs] oibs ON oibs.[object_id] = objs.id
	WHERE 
	oibs.[id] IS NULL
	--select * from [dbo].[Security.VmsRecursiveInclusion]

	DELETE vmsInclusions 
	FROM [dbo].[Security.VmsRecursiveInclusion] vmsInclusions
	INNER JOIN #VmsRecInclusion2delete toDelete	ON vmsInclusions.[id] = toDelete.[id]
	LEFT OUTER JOIN [dbo].[C.BObjects] objs ON toDelete.[vm_key_hash] = objs.[unique_key_hash]	
	LEFT OUTER JOIN [dbo].[C.Backup.Model.OIBs] oibs ON oibs.[object_id] = objs.id
	WHERE 
	oibs.[id] IS NULL
	
	
	DROP TABLE #VmsRecInclusion2delete
	DROP TABLE #syncVmsRecInclusion

END
GO


-----------------------------------------------------------------------------------
--PRINT N'Creating [dbo].[Security.DeleteFromVmRecursiveInclusion]'
--IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.DeleteVmRecursiveInclusionByContainerKey]') AND type in (N'P', N'PC'))
--	DROP PROCEDURE [dbo].[usp.Security.DeleteVmRecursiveInclusionByContainerKey]
--GO

--CREATE PROCEDURE [dbo].[usp.Security.DeleteVmRecursiveInclusionByContainerKey]
--	@container_key varbinary(16)
--AS
--BEGIN
--	SET NOCOUNT ON;
--	---DELETE FROM   WHERE container_key_hash = @container_key;

--    SET NOCOUNT ON;
--    DECLARE @imported_changes table
--    (
--        db_instance_id uniqueidentifier,
--       [id] uniqueidentifier,
--       [folder_id] uniqueidentifier,
--       [host_id] uniqueidentifier
--    )
    
--    /*              IMPORT XML DOCUMENT       */
--    DECLARE @idoc int
--    EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
    
--    INSERT INTO @imported_changes([id],[folder_id],[host_id])
--    SELECT *
--    FROM OPENXML( @idoc, '/Root/row', 2)
--    WITH
--    (
--       [id] uniqueidentifier 'id',
--       [folder_id] uniqueidentifier 'folder_id',
--       [host_id] uniqueidentifier 'host_id'
--    )
--    EXEC sp_xml_removedocument @idoc
    
--    /*             ADD DBID  FIELD     */
--    UPDATE @imported_changes
--    SET db_instance_id= @dbInstanceId
    
--    /*             TIMEZONE UPDATES     */
--    /*      UPDATE EXISTING ROWS        */
--    UPDATE [C.Folder_Host]
--    SET
--        [C.Folder_Host].[folder_id] = src.[folder_id],
--        [C.Folder_Host].[host_id] = src.[host_id]
--    FROM [C.Folder_Host] INNER JOIN @imported_changes src
--    ON [C.Folder_Host].id = src.id
    
--    /*      INSERT NEW ROWS       */
--    INSERT INTO [C.Folder_Host](db_instance_id,[id],[folder_id],[host_id])
--    SELECT
--        src.db_instance_id,
--        src.[id],
--        src.[folder_id],
--        src.[host_id]
--    FROM @imported_changes src LEFT OUTER JOIN [C.Folder_Host] trg
--    ON src.id = trg.id
--    WHERE trg.id IS NULL

--END
--GO
-------

---------------------------------------------------------------------------------
PRINT N'Creating [dbo].[Security.DeleteVmRecursiveInclusionByContainerKey]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.DeleteVmRecursiveInclusionByContainerKey]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[usp.Security.DeleteVmRecursiveInclusionByContainerKey]
GO

CREATE PROCEDURE [dbo].[usp.Security.DeleteVmRecursiveInclusionByContainerKey]
	@container_key varbinary(16)
AS
BEGIN
	SET NOCOUNT ON;
	DELETE FROM  [dbo].[Security.VmsRecursiveInclusion] WHERE container_key_hash = @container_key;

END
GO
-----


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Backup.GetAccessibleObjects]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Backup.GetAccessibleObjects]
GO

CREATE FUNCTION [dbo].[fn.Backup.GetAccessibleObjects]
(	
	@userSid varbinary(max)
	
)
RETURNS 
 @BObjects TABLE 
(
	[id] uniqueidentifier,
	[type] int,
	[host_id] uniqueidentifier,
	[object_name] nvarchar(2000),
	[object_id] nvarchar(434),
	[viobject_type] nvarchar(50),
	[guest_os] xml,
	[db_instance_id] uniqueidentifier,
	[mb_size] bigint,
	[path] nvarchar(400),
	[platform] int,
	[uuid] nvarchar(256),
	[unique_key_hash] varbinary(max),
	[display_name] nvarchar(256)
)
AS
BEGIN

	DECLARE @scope_ids TABLE (
		scope_id uniqueidentifier
	);

	-- Select scope containers
	INSERT INTO @scope_ids SELECT s.id FROM [dbo].[Security.HierarchyScopes] s 
	JOIN [dbo].[Security.RoleAccounts] ra ON (s.role_account_group_id = ra.role_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)
	WHERE ra.security_scope_enabled = 1 AND lu.sid_binary = @userSid AND s.scope_state <> 2;

	DECLARE @count int;
	SELECT @count = COUNT(sc.scope_id) FROM @scope_ids sc; 
	
	IF (@count = 0)
		INSERT INTO @BObjects SELECT * FROM [dbo].[C.BObjects];
		
	ELSE 
		INSERT INTO @BObjects SELECT bo.* FROM [dbo].[C.BObjects] bo
			JOIN [dbo].[Security.VmsRecursiveInclusion] vr ON (bo.unique_key_hash = vr.vm_key_hash)
			JOIN [dbo].[Security.HierarchyScopes] hs ON (hs.hierarchy_object_keyhash = vr.container_key_hash)
			JOIN @scope_ids si ON (hs.id = si.scope_id)
	
	return

END
GO

---------------------------------------------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.AddAccount]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Security.AddAccount]
GO
CREATE PROCEDURE [dbo].[usp.Security.AddAccount]
	@account_id uniqueidentifier,
	@name nvarchar(255),
	@nt4_name nvarchar(255),
	@type int,
	@sid nvarchar(100)
AS
BEGIN	
	SET NOCOUNT ON;		
	
	IF EXISTS (SELECT * FROM [dbo].[Security.Accounts] WHERE id = @account_id)
		RAISERROR(N'The account with ID already exists', 9, 1 )
	
	IF EXISTS (SELECT * FROM [dbo].[Security.Accounts] WHERE nt4_name = @nt4_name)
		RAISERROR(N'The account with same NT4 name already exists', 9, 1 )

	IF EXISTS (SELECT * FROM [dbo].[Security.Accounts] WHERE sid = @sid)
		RAISERROR(N'The account with same identity reference already exists', 9, 1 )
		
	INSERT INTO [dbo].[Security.Accounts]( [id], [name], [sid], [nt4_name], [type])
		VALUES ( @account_id, @name, @sid, @nt4_name, @type)
	
END
GO	
-----------------------------------------------------


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.Update]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.Update]
	@id uniqueidentifier,
	@scope_name varchar(255),
	@scope_state int,
	@hierarchy_root_instance_id varchar(255),
	@hierarchy_object_ref varchar(255),
	@hierarchy_object_type int,
	@hierarchy_view_type int
AS
BEGIN

	SET NOCOUNT ON;

	UPDATE [dbo].[Security.HierarchyScopes] 
		SET 
		[scope_name] = @scope_name,
		[scope_state] = @scope_state,
		[hierarchy_root_instance_id] = @hierarchy_root_instance_id,
		[hierarchy_object_ref] = @hierarchy_object_ref,
		[hierarchy_object_type] = @hierarchy_object_type,
		[hierarchy_view_type] = @hierarchy_view_type,
		[hierarchy_object_keyhash] = [dbo].[fn.Backup.MakeObjectHash](@hierarchy_object_ref, @hierarchy_root_instance_id)
	WHERE 
		[dbo].[Security.HierarchyScopes].id = @id

END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Enterprise.UserTasks.Create]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Enterprise.UserTasks.Create]
GO
CREATE PROCEDURE [dbo].[usp.Enterprise.UserTasks.Create]
	@type int,
	@name nvarchar(255),
	@life_time_mins int,
	@stage int,
	@result int
AS
BEGIN

	SET NOCOUNT ON;
	
	DECLARE @userTaskId uniqueidentifier
	SET @userTaskId = newid()

	DECLARE @now datetime
	SET @now = GETUTCDATE()	

	INSERT INTO [dbo].[Enterprise.UserTasks] ([id],	[type],	[name], [creation_time], [expiration_time], [stage], [result])
	VALUES(@userTaskId, @type, @name, @now, DATEADD(mi, @life_time_mins, @now), @stage, @result)

	SELECT * FROM [dbo].[Enterprise.UserTasks]
	WHERE [id] = @userTaskId

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Enterprise.UserTasks.Get]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Enterprise.UserTasks.Get]
GO
CREATE PROCEDURE [dbo].[usp.Enterprise.UserTasks.Get]
	@user_task_id uniqueidentifier = '00000000-0000-0000-0000-000000000000'
AS
BEGIN

	SET NOCOUNT ON;

	SELECT * FROM [dbo].[Enterprise.UserTasks]
	WHERE (@user_task_id = '00000000-0000-0000-0000-000000000000' OR @user_task_id = [dbo].[Enterprise.UserTasks].id)

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Enterprise.UserTasks.GetExpired]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Enterprise.UserTasks.GetExpired]
GO
CREATE PROCEDURE [dbo].[usp.Enterprise.UserTasks.GetExpired]
AS
BEGIN

	SET NOCOUNT ON;

	SELECT * FROM [dbo].[Enterprise.UserTasks]
	WHERE [dbo].[Enterprise.UserTasks].[expiration_time] < GETUTCDATE()

END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Enterprise.UserTasks.Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Enterprise.UserTasks.Update]
GO
CREATE PROCEDURE [dbo].[usp.Enterprise.UserTasks.Update]
	@user_task_id uniqueidentifier,
	@stage int,
	@result int,
	@failure_msg nvarchar(max) = NULL,
	@job_session_id uniqueidentifier = NULL
AS
BEGIN

	SET NOCOUNT ON;

	UPDATE [dbo].[Enterprise.UserTasks]		
		SET 
			[stage] = @stage,
			[result] = @result,
			[failure_msg] = @failure_msg,
			[job_session_id] = @job_session_id
	WHERE
		[dbo].[Enterprise.UserTasks].id = @user_task_id

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Enterprise.UserTasks.Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Enterprise.UserTasks.Delete]
GO
CREATE PROCEDURE [dbo].[usp.Enterprise.UserTasks.Delete]
	@user_task_id uniqueidentifier
AS
BEGIN

	SET NOCOUNT ON;

	DELETE FROM [dbo].[Enterprise.UserTasks]
	WHERE [dbo].[Enterprise.UserTasks].id = @user_task_id

END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Enterprise.UserTasks.GetAssociated]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Enterprise.UserTasks.GetAssociated]
GO
CREATE PROCEDURE [dbo].[usp.Enterprise.UserTasks.GetAssociated]
	@job_session_id uniqueidentifier
AS
BEGIN

	SET NOCOUNT ON;

	SELECT * FROM [dbo].[Enterprise.UserTasks]
	WHERE [dbo].[Enterprise.UserTasks].[job_session_id] = @job_session_id	

END
GO



IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Common.GetTodayDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Common.GetTodayDate]
GO
CREATE FUNCTION [dbo].[fn.Common.GetTodayDate]()
RETURNS datetime 
AS
BEGIN 
	
	DECLARE @today datetime
	SET @today = GETDATE()
	SET @today = DATEADD(hour, -DATEPART(hour,@today), @today)
	SET @today = DATEADD(minute, -DATEPART(minute,@today), @today)
	SET @today = DATEADD(second, -DATEPART(second,@today), @today)
	SET @today = DATEADD(millisecond, -DATEPART(millisecond, @today), @today)

	RETURN @today
END
GO




IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Common.GetTodayDateUTC]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Common.GetTodayDateUTC]
GO
CREATE FUNCTION [dbo].[fn.Common.GetTodayDateUTC]()
RETURNS datetime 
AS
BEGIN 
	
	DECLARE @today datetime
	SET @today = [dbo].[fn.Common.GetTodayDate]()
		
	RETURN DATEADD(minute, DATEDIFF(minute, GETDATE(), GETUTCDATE()), @today)
END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Enterprise.Jobs.GetSettings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Enterprise.Jobs.GetSettings]
GO
CREATE PROCEDURE [dbo].[usp.Enterprise.Jobs.GetSettings]
	@job_type int = -1 --all by default
AS
BEGIN

	SET NOCOUNT ON;

	SELECT * FROM [dbo].[Enterprise.JobSettings]
	WHERE @job_type = [dbo].[Enterprise.JobSettings].[job_type] OR @job_type = -1

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Enterprise.Jobs.UpdateLatestStartTime]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Enterprise.Jobs.UpdateLatestStartTime]
GO
CREATE PROCEDURE [dbo].[Enterprise.Jobs.UpdateLatestStartTime]
	@jobSettingsId uniqueidentifier,
	@startTimeByScheduler datetime
AS
BEGIN

	SET NOCOUNT ON;

	UPDATE [dbo].[Enterprise.JobSettings]
	SET [latest_start_time_by_scheduler] = @startTimeByScheduler
	WHERE [dbo].[Enterprise.JobSettings].[id] = @jobSettingsId

END
GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.IsUserRestrictedBySecurityScopes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Security.IsUserRestrictedBySecurityScopes]
GO
CREATE PROCEDURE [dbo].[usp.Security.IsUserRestrictedBySecurityScopes]
	@userSid varbinary(max)
AS
BEGIN

	SET NOCOUNT ON;

	SELECT CAST(COUNT(*) as bit) FROM [dbo].[Security.RoleAccounts] ra 
				JOIN [dbo].[Security.LoggedOnUserAccounts] lua ON (lua.account_id = ra.account_id)
				JOIN [dbo].[Security.LoggedOnUsers] lu ON (lu.id = lua.login_id)
				WHERE ra.security_scope_enabled = 1 AND lu.sid_binary = @userSid

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.FilterAccessibleVms]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.FilterAccessibleVms]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.FilterAccessibleVms]
	@userSid varbinary(max),
	@filteredDoc xml
AS
BEGIN

	SET NOCOUNT ON;
	
	CREATE TABLE #FilteredVms (	
	 [vm_name] nvarchar(max)
	 )

	DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @filteredDoc
    
    INSERT INTO #FilteredVms([vm_name])
	SELECT *
    FROM OPENXML( @idoc, '/Root/row', 2)
    WITH
    (        
        [vm_name] nvarchar(max) 'vm_name'--vm_real_name
    )
    EXEC sp_xml_removedocument @idoc

	SELECT DISTINCT vms.*
	FROM #FilteredVms vms		
		JOIN [dbo].[Security.VmsRecursiveInclusion] vri ON vms.vm_name = vri.vm_real_name
		JOIN [dbo].[Security.HierarchyScopes] s ON vri.container_key_hash = s.hierarchy_object_keyhash
		JOIN [dbo].[Security.RoleAccounts] ra ON (s.role_account_group_id = ra.role_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)
	WHERE 		
		ra.security_scope_enabled = 1 AND lu.sid_binary = @userSid AND s.scope_state <> 2		

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.IsVmAccessible]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.IsVmAccessible]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.IsVmAccessible]
	@userSid varbinary(max),
	@vmRealName nvarchar(256)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT
		CAST(CASE 
			WHEN COUNT(*) >  0 THEN 1
			ELSE 0
		END as bit)as result
			
	FROM 			
		[dbo].[Security.VmsRecursiveInclusion] vri
		JOIN [dbo].[Security.HierarchyScopes] s ON vri.container_key_hash = s.hierarchy_object_keyhash
		JOIN [dbo].[Security.RoleAccounts] ra ON (s.role_account_group_id = ra.role_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)
	WHERE
		ra.security_scope_enabled = 1 AND lu.sid_binary = @userSid AND s.scope_state <> 2 AND vri.vm_real_name = @vmRealName

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.IsVmAccessible2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.IsVmAccessible2]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.IsVmAccessible2]
	@userSid varbinary(max),
	@vmRestorePointId uniqueidentifier
AS
BEGIN
	SELECT
		CAST(CASE 
			WHEN COUNT(*) >  0 THEN 1
			ELSE 0
		END as bit)as result			
	FROM 			
		[dbo].[Security.VmsRecursiveInclusion] vri
		JOIN [dbo].[C.BObjects] bo ON bo.unique_key_hash = vri.vm_key_hash
		JOIN [dbo].[C.Backup.Model.OIBs] oibs ON oibs.object_id = bo.id
		JOIN [dbo].[Security.HierarchyScopes] s ON vri.container_key_hash = s.hierarchy_object_keyhash
		JOIN [dbo].[Security.RoleAccounts] ra ON (s.role_account_group_id = ra.role_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)
	WHERE
		ra.security_scope_enabled = 1 AND lu.sid_binary = @userSid AND s.scope_state <> 2 AND oibs.id = @vmRestorePointId
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.GetAllUserPermissions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Security.GetAllUserPermissions]
GO
CREATE PROCEDURE [dbo].[usp.Security.GetAllUserPermissions]
	@userSid varbinary(max)
AS
BEGIN

	SET NOCOUNT ON;

	SELECT DISTINCT 
		r.[id] as role_id,
        r.[name] as role_name,
        [dbo].[Security.RolePermissions].[permission] as permission,
        r.[number] as role_number
	FROM [dbo].[Security.RolePermissions]
		JOIN [dbo].[Security.Roles] r ON r.[id] = [dbo].[Security.RolePermissions].[role_id]
		JOIN [dbo].[Security.RoleAccounts] ra ON ra.[role_id] = r.[id] 
		JOIN [dbo].[Security.LoggedOnUserAccounts] lua ON (lua.account_id = ra.account_id)		
		JOIN [dbo].[Security.LoggedOnUsers] lu ON (lu.id = lua.login_id)
	WHERE 
		lu.sid_binary = @userSid

END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.GetAllUserRoleAccountSettings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Security.GetAllUserRoleAccountSettings]
GO
CREATE PROCEDURE [dbo].[usp.Security.GetAllUserRoleAccountSettings]
	@userSid varbinary(max)
AS
BEGIN

	SET NOCOUNT ON;

	SELECT 
		r.[id] as role_id,
        r.[name] as role_name,
        r.[number] as role_number,
		ra.[settings] as settings
	FROM
		[dbo].[Security.Roles] r
		JOIN [dbo].[Security.RoleAccounts] ra ON ra.[role_id] = r.[id] 
		JOIN [dbo].[Security.LoggedOnUserAccounts] lua ON (lua.account_id = ra.account_id)		
		JOIN [dbo].[Security.LoggedOnUsers] lu ON (lu.id = lua.login_id)
	WHERE 
		lu.sid_binary = @userSid

END
GO

--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Util.ParseVcVersion]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Util.ParseVcVersion]
GO

CREATE FUNCTION [fn.Util.ParseVcVersion] 
(
	@version_str varchar(255)
)
RETURNS varchar(50)
AS
BEGIN
	DECLARE @retVal nvarchar( 255 )
	
	DECLARE @version_end int;
	DECLARE @version_len int;
	DECLARE @version_start int;
	DECLARE @version varchar(50);
	DECLARE @buildnum_start int;
	DECLARE @buildnum varchar(50);
	DECLARE @buildnum_end int;
	
	
	SET @version_len = LEN(N'VMware vCenter Server') + 1;
	SET @version_end = CHARINDEX(' ', @version_str, @version_len + 1);
	SET @version = LTRIM(RTRIM(SUBSTRING(@version_str, @version_len, @version_end - @version_len )));
	SET @buildnum_start = CHARINDEX(N'build-', @version_str, @version_end);
	SET @buildnum = LTRIM(RTRIM(SUBSTRING(@version_str, @buildnum_start + LEN(N'build-'), LEN(@version_str))));
	
	SET @retVal = @version + '.' + @buildnum;
	RETURN @retVal

END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usp.VCPlugin.GetKnownSupportedVCHostsInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.VCPlugin.GetKnownSupportedVCHostsInfo]
GO
CREATE PROCEDURE [dbo].[usp.VCPlugin.GetKnownSupportedVCHostsInfo]
	@limit int = 5000,
	@offset int = 0,
	@order_column nvarchar(255) = 'hostname',
	@order_asc bit  = 0,
	@total_records int OUT
AS
BEGIN
	SET NOCOUNT ON;

    -- total records
	SET @total_records = (
		SELECT COUNT(*)
		FROM 
		[dbo].[VCPlugins]
	)
	PRINT @total_records;
	
	SELECT *
	FROM (
	SELECT 	
		ROW_NUMBER() OVER(
		ORDER BY
			CASE @order_asc 
				WHEN 1 THEN
					CASE @order_column
						WHEN 'version'	THEN s.version 
						WHEN 'status'	THEN CAST(s.status as varchar) 
						ELSE  s.hostname 
					END
			END ASC,
			CASE @order_asc 
				WHEN 0 THEN
					CASE @order_column
						WHEN 'version'	THEN s.[version] 
						WHEN 'status'	THEN CAST(s.status as varchar)  
						ELSE  s.[hostname]
					END
			END DESC
		) AS row_num, 
			s.[id],
			s.[hostname],
			s.[port],
			s.[username],
			s.[password],
			s.[version],
			s.[status],
			s.[plugin_version],
			s.[installed_by],
			s.[plugin_state]
	
	FROM [dbo].[VCPlugins] s 
	) AS result
	WHERE result.row_num BETWEEN @offset AND @limit+@offset
	ORDER BY row_num
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.VCPlugin.GetVcHostById]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.VCPlugin.GetVcHostById]
GO
CREATE PROCEDURE [dbo].[usp.VCPlugin.GetVcHostById]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM [dbo].[VCPlugins] WHERE id=@id;
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.VCPlugin.SetPluginInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.VCPlugin.SetPluginInfo]
GO
CREATE PROCEDURE [dbo].[usp.VCPlugin.SetPluginInfo]
	@id uniqueidentifier,
	@plugin_version varchar(50),
	@plugin_state int,
	@installed_by varchar(255) = NULL
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE [dbo].[VCPlugins] 
	SET [plugin_version] = @plugin_version, 
	[plugin_state] = @plugin_state,
	[installed_by] = @installed_by
	WHERE id=@id;
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.VCPlugin.SetCredentials]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.VCPlugin.SetCredentials]
GO
CREATE PROCEDURE [dbo].[usp.VCPlugin.SetCredentials]
	@id uniqueidentifier,
	@username varchar(255),
	@password  nvarchar(512),
	@port int

AS
BEGIN
	SET NOCOUNT ON;

	UPDATE [dbo].[VCPlugins] 
	SET [username] = @username, 
	[password] = @password,
	[port] = @port
	WHERE id=@id;
END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.VmRestorePoint.GetForObject]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.VmRestorePoint.GetForObject]
GO

CREATE PROCEDURE [dbo].[usp.Data.VmRestorePoint.GetForObject]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	SELECT oib.id, oib.creation_time, oib.type, bkp.job_name, bkp.job_target_type as job_type
	FROM [dbo].[C.Backup.Model.OIBs] oib
	LEFT JOIN [C.Backup.Model.Points] p ON (oib.point_id = p.id)
	LEFT JOIN [C.Backup.Model.Backups] bkp ON (bkp.id = p.backup_id)
	WHERE oib.object_id = @id
	AND oib.is_corrupted = 0
	ORDER BY oib.creation_time DESC
	 
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.FindJobCredentials]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.FindJobCredentials]
GO


CREATE PROCEDURE [dbo].[usp.FindJobCredentials]
		@row_job_id uniqueidentifier,
		@row_obj_id uniqueidentifier
	AS
	BEGIN
		SET NOCOUNT ON;
		IF(@row_obj_id IS NULL)
		BEGIN
			SELECT * 
			FROM [dbo].[C.JobVssCredentials]
			WHERE [job_id] = @row_job_id AND [oij_id] IS NULL
		END
		ELSE
		BEGIN
			SELECT * 
			FROM [dbo].[C.JobVssCredentials]
			WHERE [job_id] = @row_job_id AND [oij_id] = @row_obj_id
		END
END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Common.GetDatabaseLock]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Common.GetDatabaseLock]
GO


CREATE PROCEDURE [dbo].[usp.Common.GetDatabaseLock]
	@row_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	SELECT [value] FROM [dbo].[Options] WHERE [id] = @row_id
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.List_RolePermissions]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.List_RolePermissions]
GO
CREATE FUNCTION [dbo].[fn.List_RolePermissions] ()
RETURNS 
@Result TABLE 
(
	[role_id] [uniqueidentifier],
	[permission] [int]
)
AS
BEGIN
	--  Permission types
	DECLARE @view int, @configure int, @collect int, @restoreFiles int, @downloadRestoredFile int, @vmRestore int, @lookup int,
	@createCredentials int, @readCredentials int, @updateCredentials int, @deleteCredentials int, @cloneJob int

	SELECT @view = 1, @configure = 2, @collect = 4, @cloneJob = 5, 
		@restoreFiles = 8, @downloadRestoredFile = 16, @vmRestore = 32, 
		@lookup = 20,
		@createCredentials = 21, @readCredentials = 22, @updateCredentials = 23, @deleteCredentials = 24 

	--  Roles
	DECLARE @admin_role_id uniqueidentifier, @viewer_role_id uniqueidentifier, @restore_operator uniqueidentifier, @vm_restore_operator uniqueidentifier
	SELECT 
		@viewer_role_id = 'D19A3D33-CB77-4ffe-94E6-001432483A4E',
		@admin_role_id = '5F37A46B-9CE2-40f4-8A62-B45B079257FC',
		@restore_operator = 'F84A8B62-49B8-4D0C-B25B-92321B52BAB6',
		@vm_restore_operator = 'C11C0C38-BA8B-49C7-BF70-FC2058FFF1E2'


	INSERT INTO @Result 
	--Portal Administrator
	SELECT @admin_role_id, @view UNION ALL
	SELECT @admin_role_id, @configure UNION ALL
	SELECT @admin_role_id, @collect UNION ALL
	SELECT @admin_role_id, @restoreFiles UNION ALL
	SELECT @admin_role_id, @downloadRestoredFile UNION ALL
	SELECT @admin_role_id, @vmRestore UNION ALL
	SELECT @admin_role_id, @lookup UNION ALL
	SELECT @admin_role_id, @createCredentials UNION ALL
	SELECT @admin_role_id, @readCredentials UNION ALL
	SELECT @admin_role_id, @updateCredentials UNION ALL
	SELECT @admin_role_id, @deleteCredentials UNION ALL
	SELECT @admin_role_id, @cloneJob UNION ALL

	--Portal User
	SELECT @viewer_role_id, @view UNION ALL
	--SELECT @viewer_role_id, @lookup UNION ALL
	--SELECT @viewer_role_id, @createCredentials UNION ALL
	--SELECT @viewer_role_id, @readCredentials UNION ALL
	--SELECT @viewer_role_id, @updateCredentials UNION ALL
	--SELECT @viewer_role_id, @deleteCredentials UNION ALL
	--SELECT @viewer_role_id, @cloneJob UNION ALL
	---SELECT @viewer_role_id, @restoreFiles UNION ALL
	-- SELECT @viewer_role_id, @vmRestore UNION ALL

	--Restore Operator
	SELECT @restore_operator, @restoreFiles UNION ALL

	--VM Restore Operator
	SELECT @vm_restore_operator, @vmRestore

	RETURN 
END
GO


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Fill_RolePermissions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Fill_RolePermissions]
GO
CREATE PROCEDURE [dbo].[Fill_RolePermissions]
AS
BEGIN
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Security.RolePermissions]') AND type in (N'U'))
	BEGIN
		DELETE FROM [dbo].[Security.RolePermissions]

		INSERT INTO [dbo].[Security.RolePermissions]
		SELECT newid(), rp.* FROM [dbo].[fn.List_RolePermissions] () rp
	END
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.DeleteById]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.DeleteById]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.DeleteById]
	@scope_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	DELETE FROM [dbo].[Security.HierarchyScopes]  
	WHERE id = @scope_id
END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.SecurityScope.GetRoleAccountGroupId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.SecurityScope.GetRoleAccountGroupId]
GO
CREATE PROCEDURE [dbo].[usp.SecurityScope.GetRoleAccountGroupId]
	@account_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	SELECT role_group_id FROM [dbo].[Security.RoleAccounts] 
	WHERE account_id = @account_id
	GROUP by role_group_id
END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usp.Security.AddAccountInRole2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Security.AddAccountInRole2]
GO

CREATE PROCEDURE [dbo].[usp.Security.AddAccountInRole2]
	@account_id uniqueidentifier,
	@role_id uniqueidentifier,
	@security_scope_enabled bit,
	@role_group_id uniqueidentifier,
	@settings xml
AS
BEGIN	
	SET NOCOUNT ON;		
	IF EXISTS (SELECT * FROM [dbo].[Security.RoleAccounts] WHERE [account_id] = @account_id AND [role_id] = @role_id)
		RAISERROR(N'The role-account association already exists', 9, 1 )
	DECLARE @role_account_id uniqueidentifier
	SET @role_account_id = NEWID()
	INSERT INTO [dbo].[Security.RoleAccounts]( [id], [role_id], [account_id], [security_scope_enabled],[role_group_id],[settings] )
	VALUES(@role_account_id , @role_id, @account_id, @security_scope_enabled, @role_group_id, @settings)
	SELECT @role_account_id as role_account_id
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.RemoveRoleAccount2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Security.RemoveRoleAccount2]
GO

CREATE PROCEDURE [dbo].[usp.Security.RemoveRoleAccount2]
	@account_id uniqueidentifier,
	@role_id uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;	
	DECLARE @role_account_group_id uniqueidentifier;
	DECLARE @role_account_id uniqueidentifier;
	SELECT TOP(1) 
		@role_account_id = [dbo].[Security.RoleAccounts].[id],
		@role_account_group_id = [dbo].[Security.RoleAccounts].[role_group_id]
	FROM [dbo].[Security.Accounts] 
		INNER JOIN [dbo].[Security.RoleAccounts] ON [dbo].[Security.Accounts].[id] = [dbo].[Security.RoleAccounts].[account_id]
	WHERE [dbo].[Security.RoleAccounts].[role_id] = @role_id
		AND [dbo].[Security.RoleAccounts].[account_id] = @account_id;
		
	IF (SELECT COUNT(*) FROM [dbo].[Security.RoleAccounts] WHERE [dbo].[Security.RoleAccounts].[account_id] = @account_id) = 1
	BEGIN
		DELETE FROM [dbo].[Security.Accounts]
		WHERE [dbo].[Security.Accounts].[id] = @account_id
		DELETE FROM [dbo].[Security.HierarchyScopes] 
		WHERE role_account_group_id = @role_account_group_id
	END
	DELETE FROM [dbo].[Security.RoleAccounts]
	WHERE [dbo].[Security.RoleAccounts].[id] = @role_account_id
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.RemoveAccount]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Security.RemoveAccount]
GO

CREATE PROCEDURE [dbo].[usp.Security.RemoveAccount]
	@account_id uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;	
	DECLARE @admin_role_id uniqueidentifier;
	SET @admin_role_id = '5F37A46B-9CE2-40f4-8A62-B45B079257FC';
	
	IF ( SELECT COUNT(*) FROM [dbo].[Security.RoleAccounts] 
		 WHERE [role_id] = @admin_role_id) > 1
	BEGIN	
		-- DELETE scopes
		DECLARE @role_account_group_id uniqueidentifier;
		SELECT @role_account_group_id = [role_group_id]
		FROM [dbo].[Security.RoleAccounts] WHERE [account_id] = @account_id;
		
		DELETE FROM [dbo].[Security.Accounts] WHERE [id] = @account_id;
		DELETE FROM [dbo].[Security.HierarchyScopes] 
		WHERE [role_account_group_id] = @role_account_group_id;
		
		DELETE FROM [dbo].[Security.LoggedOnUsers] WHERE id IN (
			SELECT lou.login_id FROM [dbo].[Security.LoggedOnUserAccounts] lou
			JOIN [dbo].[Security.LoggedOnUserAccounts] lou2 ON (lou2.login_id = lou.login_id)
			WHERE lou.account_id = @account_id
			GROUP BY lou.login_id HAVING COUNT(lou2.login_id) = 1
		)
	
		DELETE FROM [dbo].[Security.LoggedOnUserAccounts]
		WHERE [account_id] = @account_id;	
		
		DELETE FROM [dbo].[Security.RoleAccounts]
		WHERE [role_group_id] = @role_account_group_id;
		
	END
	ELSE
		RAISERROR(N'Cannot delete last account in administrator role', 9, 1 )
END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.GetFirstSuitableOibByActiveJobs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.GetFirstSuitableOibByActiveJobs]
GO
CREATE PROCEDURE [dbo].[usp.Data.GetFirstSuitableOibByActiveJobs]
	@orig_oib_id uniqueidentifier
AS
BEGIN	
	SELECT oibs.* 
	FROM [dbo].[C.BJobs] jobs
	INNER JOIN [dbo].[C.Backup.Model.Backups] backups ON jobs.[id] = backups.[job_id]
	INNER JOIN [dbo].[C.Backup.Model.Storages] stg ON backups.[id] = stg.[backup_id]
	INNER JOIN [dbo].[C.Backup.Model.OIBs] oibs ON stg.[id] = oibs.[storage_id]
	INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON oibs.[db_instance_id] = bservers.[current_db_id]
	WHERE jobs.[type] IN (0,1) AND oibs.[original_oib_id] = @orig_oib_id
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.GetFirstSuitableOibByImportedBackups]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Data.GetFirstSuitableOibByImportedBackups]
GO
CREATE PROCEDURE [dbo].[usp.Data.GetFirstSuitableOibByImportedBackups]
	@orig_oib_id uniqueidentifier
AS
BEGIN	
	SELECT oibs.* 
	FROM 
	[dbo].[C.Backup.Model.Backups] backups
	INNER JOIN [dbo].[C.Backup.Model.Storages] stg ON backups.[id] = stg.[backup_id]
	INNER JOIN [dbo].[C.Backup.Model.OIBs] oibs ON stg.[id] = oibs.[storage_id]
	INNER JOIN [dbo].[Repl.Topology.BackupServers] bservers ON oibs.[db_instance_id] = bservers.[current_db_id]
	WHERE oibs.[original_oib_id] = @orig_oib_id AND
	(backups.[job_id] IS NULL OR backups.[job_id] = '00000000-0000-0000-0000-000000000000')
	
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Security.DisableSecurityScopeForAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Security.DisableSecurityScopeForAll]
GO
CREATE PROCEDURE [dbo].[usp.Security.DisableSecurityScopeForAll]
AS
BEGIN	
	UPDATE [dbo].[Security.RoleAccounts] 
	SET [security_scope_enabled] = 0 
	WHERE [security_scope_enabled] = 1;
END
GO


-- Backup.Model.GetUncorruptedOibsByOibId
PRINT N'Creating [dbo].[usp.Data.VmRestorePoint.GetAllByOibId]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.VmRestorePoint.GetAllByOibId]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[usp.Data.VmRestorePoint.GetAllByOibId]
GO

	CREATE PROCEDURE [dbo].[usp.Data.VmRestorePoint.GetAllByOibId]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @backup_id uniqueidentifier, @object_id uniqueidentifier
	select @backup_id = points.backup_id,
		   @object_id = oibs.object_id  
	from 
		[dbo].[C.Backup.Model.OIBs] oibs,
		[dbo].[C.Backup.Model.Points] points
	WHERE oibs.id = @id
	AND oibs.point_id = points.id

	SELECT oib.id, oib.creation_time, oib.type, bkp.job_name, bkp.job_target_type as job_type
	FROM [dbo].[C.Backup.Model.OIBs] oib
	LEFT JOIN [C.Backup.Model.Points] p ON (oib.point_id = p.id)
	LEFT JOIN [C.Backup.Model.Backups] bkp ON (p.backup_id = bkp.id)
	WHERE oib.object_id = @object_id
	AND p.backup_id = @backup_id
	AND oib.is_corrupted = 0
	ORDER BY oib.creation_time DESC
END

GO

PRINT N'Creating [dbo].[usp.Data.EnumHostsByHostInstanceId]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Data.EnumHostsByHostInstanceId]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[usp.Data.EnumHostsByHostInstanceId]
GO
CREATE PROCEDURE [dbo].[usp.Data.EnumHostsByHostInstanceId]
AS
BEGIN
	SELECT *
	FROM
	(
		SELECT 
			h.*,
			ROW_NUMBER() OVER (PARTITION BY host_instance_id ORDER BY host_instance_id) AS number
		FROM 
			[dbo].[C.Hosts] h
	) t
	WHERE number = 1 AND host_instance_id IS NOT NULL
END

GO