PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Refactor 2.6'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Add subscription columns to TrackUsage table'
GO
IF NOT EXISTS (
    SELECT 1
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE t.name = 'TrackUsage'
      AND s.name = 'mig_director'
      AND c.name = 'SubscribeNewestSrcEngine'
)
BEGIN
alter table [mig_director].[TrackUsage] alter column [Description] nvarchar(50) not null;
alter table [mig_director].[TrackUsage] add [SubscribeNewestSrcEngine] bit not null constraint DF_TrackUsage_SubscribeNewestSrcEngine default (1);
alter table [mig_director].[TrackUsage] add [SubscribeNewestTgtEngine] bit not null constraint DF_TrackUsage_SubscribeNewestTgtEngine default (1);

alter table [mig_director].[TrackUsage] drop constraint DF_TrackUsage_SubscribeNewestSrcEngine;
alter table [mig_director].[TrackUsage] drop constraint DF_TrackUsage_SubscribeNewestTgtEngine;
END
GO
PRINT N'-- Create procedure [mig_director].[TrackUsage_SetEngineSubscription]..'
GO
create or alter PROCEDURE [mig_director].[TrackUsage_SetEngineSubscription] 
	@trackID uniqueidentifier 
,	@engineCode tinyint -- 0 = Source, 1 = Target
,	@subscribe bit
as
	update [mig_director].[TrackUsage]
	set
		[SubscribeNewestSrcEngine] = case @engineCode
			when 0 then @subscribe
			else [SubscribeNewestSrcEngine]
		end
	,	[SubscribeNewestTgtEngine] = case @engineCode
			when 0 then [SubscribeNewestTgtEngine]
			else @subscribe
		end
	from 
		[mig_director].[TrackUsage] as A
		inner join [mig_director].[Track] as B 
		on 
			A.[TrackUsageID] = B.[ActiveTrackUsage]
		where
			B.[TrackID] = @trackID
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Restrucure TrackProcess table'
GO
IF NOT EXISTS (
    SELECT 1
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE t.name = 'TrackProcess'
      AND s.name = 'mig_director'
      AND c.name = 'DispatcherProcessId'
)
BEGIN
	BEGIN TRANSACTION;

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

	SET XACT_ABORT ON;

	ALTER TABLE [mig_director].[TrackProcess] DROP CONSTRAINT [DF_TrackProcess_ProcessId];
	ALTER TABLE [mig_director].[TrackProcess] DROP CONSTRAINT [DF_TrackProcess_ProcessState];

	CREATE TABLE [mig_director].[tmp_ms_xx_TrackProcess] (
		[TrackID]             UNIQUEIDENTIFIER NOT NULL,
		[ProcessId]           INT              CONSTRAINT [DF_TrackProcess_ProcessId] DEFAULT ((-1)) NOT NULL,
		[DispatcherProcessId] INT              CONSTRAINT [DF_TrackProcess_DispatcherProcessId] DEFAULT ((-1)) NOT NULL,
		[State]               INT              CONSTRAINT [DF_TrackProcess_ProcessState] DEFAULT ((0)) NOT NULL,
		[StartRequested]      VARCHAR (50)     NULL,
		[StopRequested]       VARCHAR (50)     NULL,
		[Error]               VARCHAR (MAX)    NULL,
		[ProcessCheckIn]      DATETIME2 (7)    NULL,
		CONSTRAINT [tmp_ms_xx_constraint_PK_TrackProcess1] PRIMARY KEY CLUSTERED ([TrackID] ASC)
	);

	IF EXISTS (SELECT TOP 1 1 
			   FROM   [mig_director].[TrackProcess])
		BEGIN
			INSERT INTO [mig_director].[tmp_ms_xx_TrackProcess] ([TrackID], [ProcessId], [State], [StartRequested], [StopRequested], [Error], [ProcessCheckIn])
			SELECT   [TrackID],
					 [ProcessId],
					 [State],
					 [StartRequested],
					 [StopRequested],
					 [Error],
					 [ProcessCheckIn]
			FROM     [mig_director].[TrackProcess]
			ORDER BY [TrackID] ASC;
		END

	DROP TABLE [mig_director].[TrackProcess];

	EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_TrackProcess]', N'TrackProcess';

	EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_constraint_PK_TrackProcess1]', N'PK_TrackProcess', N'OBJECT';

	COMMIT TRANSACTION;

	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
END
GO
PRINT N'-- Create procedure [mig_director].[TrackProcessSetDispatcherProcessId]..'
GO
create or alter PROCEDURE [mig_director].[TrackProcessSetDispatcherProcessId] 
  @trackID uniqueidentifier,
  @pId int
as
	update [mig_director].[TrackProcess] 
	set
		[DispatcherProcessId] = @pId
	where 
		[TrackID] = @trackID
GO
PRINT N'-- Alter procedure [mig_director].[MasterPulse]...'
GO
ALTER PROCEDURE [mig_director].[MasterPulse] 
AS
	declare @projects table(
		[ProjectID] uniqueidentifier
	,	[ProjectMode] tinyint
	)
	
	insert into @projects
	select 
		[ProjectID]
	,	[ProjectMode]
	from 
		[mig_director].[Project]
	where 
		[ProjectState] = 1 -- Active only
	
	select * from @projects
	
	declare @tracks table(
		[TrackID] uniqueidentifier
	,	[ServerId] int
	,	[NumberOnServer] int
	,	[ProcessState] int
	,	[ProcessId] int
	,	[DispatcherProcessId] int
	,	[ProcessError] nvarchar(max)
	,	[ProjectID] uniqueidentifier
	,	[Description] nvarchar(50)
	)
	
	insert into @tracks
	select
		A.[TrackID]
	,	A.[ServerId]
	,	A.[NumberOnServer]
	,	isnull(C.[State], 0) as [ProcessState]
	,	isnull(C.[ProcessId], 0) as [ProcessId]
	,	isnull(C.[DispatcherProcessId], 0) as [DispatcherProcessId]	
	,	isnull(C.[Error], '') as [ProcessError]
	,	B.[ProjectId]
	,	B.[Description]
	from
		[mig_director].[Track] as A
		inner join [mig_director].[TrackUsage] as B 
		on 
			A.[ActiveTrackUsage] = B.[TrackUsageID]
		left outer join [mig_director].[TrackProcess] as C 
		on 
			A.[TrackId] = C.[TrackID]
	where 
	      B.[ProjectID] in (select ProjectID from @projects)
	  and B.[Publish] = 1
	  and A.[IsArchive] = 0
	order by 
		A.[ServerId]
	,	A.[NumberOnServer]
	,	B.[Description]
	
	select * from @tracks
	
	select
		[ServerId]
	,	[MachineName]
	,	[Description]
	,	0 as [ProcessState]
	,	convert(bigint, datediff(ss, isnull([ServiceCheckInTime], '2000-01-01'), getdate())) as [SecondsSinceCheckIn]
	from 
		[mig_director].[Server]
	
	select 
		[ParameterName]
	,	[ParameterValue]
	from 
		[mig_config].[MasterParameter]
	where
		[ParameterName] <> 'CSFormat'
GO
PRINT N'-- Restrucure Engine tables'
GO
IF NOT EXISTS (
    SELECT 1
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE t.name = 'EngineRevision' AND s.name = 'mig_director'
)
BEGIN
    CREATE TABLE [mig_director].[EngineRevision] (
		[RevisionID]		UNIQUEIDENTIFIER NOT NULL,
		[EngineID]			UNIQUEIDENTIFIER NOT NULL,
		[ZipFile]			VARBINARY (max) NOT NULL,
		[Label]				NVARCHAR (MAX) NULL,
		[LoadLibrary]		VARCHAR (128) NOT NULL,
		[DeployedAt]		DATETIME2 NOT NULL, 
		[DeployedBy]		NVARCHAR(256) NOT NULL, 
        CONSTRAINT [PK_EngineRevision] PRIMARY KEY CLUSTERED ([EngineID], [RevisionID] ASC)
    );
END;
GO
PRINT N'-- Create table [mig_director].[EngineUsage]'
GO
IF NOT EXISTS (
    SELECT 1
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE t.name = 'EngineUsage' AND s.name = 'mig_director'
)
begin
CREATE TABLE [mig_director].[EngineUsage] (
	[TrackID]			UNIQUEIDENTIFIER NOT NULL,
	[EngineCode]		TINYINT NOT NULL,
	[RevisionID]		UNIQUEIDENTIFIER NOT NULL,
	CONSTRAINT [PK_EngineUsage] PRIMARY KEY CLUSTERED ([TrackID], [EngineCode] ASC)
);
end
if exists (select 1 from sys.columns as c where c.name = 'ZipFile' and object_name(c.object_id) = 'Engine')
begin
	exec sp_executeSql N'
	begin transaction 

	insert [mig_director].[EngineRevision] ([RevisionID], [EngineID], [ZipFile], [LoadLibrary], [DeployedAt], [DeployedBy])
	select 
		newid()
	,	A.[EngineID]
	,	A.[ZipFile] 
	,	A.[LoadLibrary]
	,	A.[DeployedAt],
		A.[DeployedBy]
	from 
		[mig_director].[Engine] as A
		left outer join [mig_director].[EngineRevision] as B
		on 
			A.[EngineID] = B.[EngineID]
	where
		A.[ZipFile] is not null
	and	B.[EngineID] is null

	alter table [mig_director].[Engine] drop column [ZipFile]
	alter table [mig_director].[Engine] drop column [LoadLibrary]
	alter table [mig_director].[Engine] drop column [DeployedAt]
	alter table [mig_director].[Engine] drop column [DeployedBy]
	alter table [mig_director].[Engine] alter column [EngineName] varchar(128) not null;

	insert [mig_director].[EngineUsage]
	select 
		A.[TrackID]
	,	V.[EngineCode]
	,	V.[RevisionID]
	from
		[mig_director].[Track] as A
		inner join [mig_director].[TrackUsage] as B
		on
			A.[ActiveTrackUsage] = B.[TrackUsageID]

			inner join [mig_director].[Project] as C
			on
				B.[ProjectID] = C.[ProjectID]

			inner join [mig_director].[EngineRevision] as src
			on
				C.[SourceEngineID] = src.[EngineID]

			left outer join [mig_director].[EngineRevision] as tgt
			on
				C.[TargetEngineID] = tgt.[EngineID]
	cross apply (values
		(0, src.[RevisionID])
	,	(1, tgt.[RevisionID])
	) as V([EngineCode], [RevisionID])
	where
		V.[RevisionID] is not null

	commit'
end
go
if exists (
	select 1
	from sys.indexes i
	join sys.objects o on i.object_id = o.object_id
	join sys.schemas s on o.schema_id = s.schema_id
	where i.name = 'IX_Engine_NameAndType'
		and o.name = 'Engine'
		and s.name = 'mig_director'
)
begin
	drop index [IX_Engine_NameAndType] on [mig_director].[Engine]
end;
GO
PRINT N'Alter [mig_director].[Runtime_GetVersion] '
GO
CREATE or ALTER PROCEDURE [mig_director].[Runtime_GetVersion]
	@version varchar(50) output
as
	select
		@version = [Version]
	from 
		[mig_director].[Runtime]
	where 
		[IsActive] = 1
GO
PRINT N'Alter [mig_director].[Engine_RevisionUsageList] '
GO
CREATE or ALTER PROCEDURE [mig_director].[Engine_RevisionUsageList] 
	@revisionID uniqueidentifier
as
	select
		A.[RevisionID]
	,	D.[ProjectID]
	,	E.[MachineName]
	,	B.[NumberOnServer]
	,	C.[Description]
	from
		[mig_director].[EngineUsage] as A

		inner join [mig_director].[Track] as B
		on
			A.[TrackID] = B.[TrackID]

		inner join [mig_director].[TrackUsage] as C
		on
			B.[ActiveTrackUsage] = C.[TrackUsageID]

		inner join [mig_director].[Project] as D
		on
			C.[ProjectID] = D.[ProjectID]

		inner join [mig_director].[Server] as E
		on
			B.[ServerId] = e.[ServerId]
	where 
		A.[RevisionID] = @revisionID
GO
PRINT N'Alter [mig_director].[Engine_List] '
GO
alter PROCEDURE [mig_director].[Engine_List] 
as
	-- The project count is done in the application layer
	-- in order to filter out projects that are not known
	-- in the Portal DB

	declare @guidEmpty uniqueidentifier = '00000000-0000-0000-0000-000000000000';

	with projects as (
		select
			P.[ProjectID]
		,   V.[EngineCode]
		,   V.[EngineID]
		from 
			[mig_director].[Project] as P
		cross apply (values
			(0, P.[SourceEngineID])
		,   (1, P.[TargetEngineID])
		) V([EngineCode], [EngineID])
		where
			V.[EngineID] is not null
		and V.[EngineID] <> @guidEmpty
	)
	select
		A.[EngineID]
	,	A.[EngineName]
	,	A.[EngineCode]
	,	isnull(B.[ProjectID], @guidEmpty) as [ProjectID]  
	from
		[mig_director].[Engine] as A
		left outer join projects as B
		on 
			A.[EngineID] = B.[EngineID]
		and	A.[EngineCode] = B.EngineCode
	order by
		A.[EngineName]
	,	A.[EngineCode]	
GO
PRINT N'Alter [mig_director].[Engine_DeploymentList] '
GO
create or alter PROCEDURE [mig_director].[Engine_DeploymentList] 
	@engineID uniqueidentifier
as
	select
		A.[EngineID]
	,	A.[RevisionID]
	,	A.[Label]
	,	A.[LoadLibrary]
	,	A.[DeployedAt]
	,	A.[DeployedBy]
	,	count(E.[ProjectID]) as [UsageCount]
	from
		[mig_director].[EngineRevision] as A

		left outer join [mig_director].[EngineUsage] as B
		on		
			A.[RevisionID] = B.[RevisionID]

		left outer join [mig_director].[Track] as C
		on
			B.[TrackID] = C.[TrackID]

		left outer join [mig_director].[TrackUsage] as D
		on
			C.[ActiveTrackUsage] = D.[TrackUsageID]

		left outer join [mig_director].[Project] as E
		on
			D.[ProjectID] = E.[ProjectID]

		left outer join [mig_director].[Server] as F
		on
			C.[ServerId] = F.[ServerId]
	where 
		A.[EngineID] = @engineID
	group by
		A.[EngineID]
	,	A.[RevisionID]
	,	A.[Label]
	,	A.[LoadLibrary]
	,	A.[DeployedAt]
	,	A.[DeployedBy]
	order by
		A.[DeployedAt] desc
GO
PRINT N'Create [mig_director].[Engine_SetRevisionLabel] '
GO
CREATE or ALTER PROCEDURE [mig_director].[Engine_SetRevisionLabel]
	@revisionID uniqueidentifier
,	@label nvarchar(max) = null
as
	update [mig_director].[EngineRevision]
	set 
		[Label] = @label
	where
		[RevisionID] = @revisionID
GO
PRINT N'Create [mig_director].[Engine_RevisionUsageList] '
GO
create or alter PROCEDURE [mig_director].[Engine_RevisionUsageList] 
	@revisionID uniqueidentifier
as
	select
		A.[RevisionID]
	,	D.[ProjectID]
	,	E.[MachineName]
	,	B.[NumberOnServer]
	,	C.[Description]
	from
		[mig_director].[EngineUsage] as A

		inner join [mig_director].[Track] as B
		on
			A.[TrackID] = B.[TrackID]

		inner join [mig_director].[TrackUsage] as C
		on
			B.[ActiveTrackUsage] = C.[TrackUsageID]

		inner join [mig_director].[Project] as D
		on
			C.[ProjectID] = D.[ProjectID]

		inner join [mig_director].[Server] as E
		on
			B.[ServerId] = e.[ServerId]
	where 
		A.[RevisionID] = @revisionID
GO
PRINT N'Create [mig_director].[Track_GetEngineRevisions] '
GO
create or alter PROCEDURE [mig_director].[Track_GetEngineRevisions]
	@trackID uniqueidentifier
,	@engineCode tinyint = 0
,	@engineID uniqueidentifier output
,	@engineName varchar(128) output
,	@subscribed bit output
as
	select 
		@engineID = D.[EngineID]
	,	@engineName = D.[EngineName]
	,	@subscribed = case @engineCode
			when 0 then B.[SubscribeNewestSrcEngine]
			when 1 then B.[SubscribeNewestTgtEngine]
		end
	from
		[mig_director].[Track] as A
		inner join [mig_director].[TrackUsage] as B
		on
			A.[ActiveTrackUsage] = B.[TrackUsageID]
		inner join [mig_director].[Project] as C
		on
			B.[ProjectID] = C.[ProjectID]
		inner join [mig_director].[Engine] as D
		on
			D.[EngineID] = case @engineCode
				when 0 then C.[SourceEngineID]
				else C.[TargetEngineID]
			end
	where
		A.[TrackID] = @trackID

	select
		A.[RevisionID]
	,	A.[Label]
	,	A.[LoadLibrary]
	,	A.[DeployedAt]
	,	A.[DeployedBy]
	,	cast(case
			when B.[RevisionID] is null then 0
			else 1
		end as bit) as [Active]
	from
		[mig_director].[EngineRevision] as A
		left outer join [mig_director].[EngineUsage] as B
		on
			A.[RevisionID] = B.[RevisionID]
		and B.[TrackID] = @trackID
	where
		A.[EngineID] = @engineID
	order by 
		A.[DeployedAt] desc
GO
PRINT N'Create [mig_director].[Track_SetEngineRevision] '
GO
CREATE OR ALTER procedure [mig_director].[Track_SetEngineRevision]
	@trackID uniqueidentifier
,	@engineCode tinyint
,	@revisionID uniqueidentifier = null
as
	if @revisionID is null
	begin
		-- If no revisionID is provided, set the track to use the newest revision for 
		-- the engine
		declare @engineID uniqueidentifier = (
			select 
				D.[EngineID]
			from
				[mig_director].[Track] as A
				inner join [mig_director].[TrackUsage] as B
				on
					A.[ActiveTrackUsage] = B.[TrackUsageID]
				inner join [mig_director].[Project] as C
				on
					B.[ProjectID] = C.[ProjectID]
				inner join [mig_director].[Engine] as D
				on
					D.[EngineID] = case @engineCode
						when 0 then C.[SourceEngineID]
						else C.[TargetEngineID]
					end
			where
				A.[TrackID] = @trackID
		);
		
		set @revisionID = (
			select top(1)
				A.[RevisionID]
			from
				[mig_director].[EngineRevision] as A
			where
				A.[EngineID] = @engineID
			order by 
				A.[DeployedAt] desc
		);
	end

	merge [mig_director].[EngineUsage] as tgt
    using (
        values (@trackID, @engineCode, @revisionID)
    ) as src([trackid], [enginecode], [revisionid])
	on 
		tgt.[TrackID] = src.[trackID]
	and	tgt.[EngineCode] = src.[engineCode]
	when matched then
		update 
		set
			tgt.[RevisionID] = src.[revisionId]
	when not matched then
		insert (
			[TrackID]
		,	[EngineCode]
		,	[RevisionID]
		)
		values (
			src.[trackID]
		,	src.[engineCode]
		,	src.[revisionId]
		);
GO
PRINT N'Alter PROCEDURE [mig_director].[TrackUsage_Modify_CreateOrUpdate]  '
GO
alter PROCEDURE [mig_director].[TrackUsage_Modify_CreateOrUpdate] 
	@trackID uniqueidentifier 
,	@trackUsageID uniqueidentifier = null
,	@projectID uniqueidentifier = null
,	@usage nvarchar(50) = null
,	@publish bit = null
,	@expireDateOnWeb datetime = null
as
	begin transaction

	if @trackUsageID is null
	begin
		declare @trackUsage table([TrackUsageID] uniqueidentifier)

		if @projectID is null
		begin
			-- Set Unused
			update [mig_director].[Track] 
			set 
				[ActiveTrackUsage] = null
			output deleted.[ActiveTrackUsage] into @trackUsage
			where
				[TrackID] = @trackID
		end
		else
		begin
			  -- Was Unused - New TrackUsage
			insert [mig_director].[TrackUsage] (
				[ProjectID]
			,	[Description]
			,	[Publish]
			,	[ExpireDateOnWeb]
			,	[SubscribeNewestSrcEngine]
			,	[SubscribeNewestTgtEngine]
			) 
			output inserted.[TrackUsageID] into @trackUsage
			values (
				@projectID
			,	@usage
			,	@publish
			,	@expireDateOnWeb
			,	1, 1 -- default subscribe to newest engines for new track usage
			)
		
			-- Get the new TrackUsageID 
			declare @newUsageID uniqueidentifier = (select top(1) [TrackUsageID] from @trackUsage)
		
			-- and clear the temp table, so there is not 2 rows in there for the following delete below
			delete @trackUsage
		
			update [mig_director].[Track]
			set
				[ActiveTrackUsage] = @newUsageID
			,	[ArchiveToTrack] = null
			output deleted.[ActiveTrackUsage] into @trackUsage
			where
				[TrackID] = @trackID
		end
		
		-- Clear any previous TrackUsage
		delete [mig_director].[TrackUsage]
		from
			[mig_director].[TrackUsage] as A
			inner join @trackUsage as B 
			on 
				A.[TrackUsageId] = B.[TrackUsageID]
	end
	else
	begin
		-- Update existing TrackUsage (change of ProjectID is only allowed if the track is not linked as an Archive to another track)
		update [mig_director].[TrackUsage]
		set
			[ProjectID] = @projectID
		,	[Description] = @usage
		,	[Publish] = @publish
		,	[ExpireDateOnWeb] = @expireDateOnWeb
			-- Preserve engine subscriptions if the ProjectID is not changed
		,	[SubscribeNewestSrcEngine] = case [ProjectID] when @projectID then [SubscribeNewestSrcEngine] else 1 end
		,	[SubscribeNewestTgtEngine] = case [ProjectID] when @projectID then [SubscribeNewestTgtEngine] else 1 end
		where
			[TrackUsageID] = @trackUsageID
		and (
			[ProjectID] = @projectID
			or
			not exists (
				select 1
				from [mig_director].[Track] 
				where
				[ArchiveToTrack] = @trackID
			)
		)
	end
	
	if @projectID is not null -- The track is used (not set unused above)
	begin
		----------------------------------------------------------------------------------------------------------------------
		-- A new engine can be created and deployed *before* being used. This call ensures that, when the engine is then used,
		-- the latest deployed revision is automatically set active for the track using the engine
		----------------------------------------------------------------------------------------------------------------------
		exec [mig_director].[_Engine_EnsureActiveRevisions]
			@projectID = @projectID
		,	@trackID = @trackID	
	end
	
	commit
GO
PRINT N'Create PROCEDURE [mig_director].[Project_Modify_CreateOrUpdate]'
GO
ALTER PROCEDURE [mig_director].[Project_Modify_CreateOrUpdate] 
	@projectID uniqueidentifier 
,	@projectMode int
,	@projectState int
,	@projectDB_ServerId int
,	@projectDB_Name varchar(50)
,	@projectDB_CSFormat varchar(max) = null
,	@sourceEngineID uniqueidentifier = null
,	@targetEngineID uniqueidentifier = null
as
	-- capture old/new engine ids to detect changes from the UPDATE
	declare @upd table
	(
		[OldSrc] uniqueidentifier
	,	[NewSrc] uniqueidentifier
	,	[OldTgt] uniqueidentifier
	,	[NewTgt] uniqueidentifier
	);

	update [mig_director].[Project]
	set
		[ProjectMode] = @projectMode
	,	[ProjectState] = @projectState
	,	[ProjectDB_ServerId] = @projectDB_ServerId
	,	[ProjectDB_Name] = @projectDB_Name
	,	[ProjectDB_CSFormat] = @projectDB_CSFormat
	,	[SourceEngineID] = isnull(@sourceEngineID, [SourceEngineID])
	,	[TargetEngineID] = isnull(@targetEngineID, [TargetEngineID])
	output
		deleted.[SourceEngineID]
	,	inserted.[SourceEngineID]
	,	deleted.[TargetEngineID]
	,	inserted.[TargetEngineID]
	into @upd 
	where
		[ProjectID] = @projectID  
	
	if 0 = @@rowCount
	begin
		insert [mig_director].[Project] (
			[ProjectID] 
		,	[ProjectMode] 
		,	[ProjectState] 
		,	[ProjectDB_ServerId] 
		,	[ProjectDB_Name] 
		,	[ProjectDB_CSFormat] 
		,	[SourceEngineID]
		,	[TargetEngineID]
		) values (
			@projectID
		,	@projectMode
		,	@projectState
		,	@projectDB_ServerId
		,	@projectDB_Name
		,	@projectDB_CSFormat
		,	@sourceEngineID
		,	@targetEngineID
		)
	
		select cast(1 as bit) -- Created
	end
	else
	begin
		-- only call the ensure proc if an engine id actually changed
		declare @guidEmpty uniqueidentifier = '00000000-0000-0000-0000-000000000000'

		if exists (
			select 1
			from 
				@upd as A
			where	
				isnull(A.[OldSrc], @guidEmpty) <> isnull(A.[NewSrc], @guidEmpty)
			or	isnull(A.[OldTgt], @guidEmpty) <> isnull(A.[NewTgt], @guidEmpty)
		)
		begin
			exec [mig_director].[_Engine_EnsureActiveRevisions]
				@projectID = @projectID
			,	@trackID = null; -- act on all tracks in use by the project
		end

		select cast(0 as bit) -- Updated
	end
GO
PRINT N'Create PROCEDURE [mig_director].[Project_Modify_CreateOrUpdate]'
GO
CREATE OR ALTER PROCEDURE [mig_director].[_Engine_EnsureActiveRevisions]
	@projectID uniqueidentifier
,	@trackID uniqueidentifier = null
as
	set nocount on;

	-- tracks to act on (either: all active tracks for the project, or the single @trackID)
	declare @tracks table([TrackID] uniqueidentifier primary key)

	if @trackID is null
	begin
		insert @tracks
		select
			A.[TrackID]
		from
			[mig_director].[Track] as A
			inner join [mig_director].[TrackUsage] as B 
			on 
				B.[TrackUsageID] = A.[ActiveTrackUsage]
		where
			B.[ProjectID] = @projectID
	end
	else
	begin
		insert @tracks values(@trackID)
	end

	-- The latest engine revision usages for the project
	declare @usages table (
		[EngineID] uniqueidentifier not null
	,	[EngineCode] tinyint not null
	,	[RevisionID] uniqueidentifier not null
	,	primary key ([EngineCode])
	);

	;with 
		engines as (
			select 
				B.[EngineID]
			,	B.[EngineCode]
			from
				[mig_director].[Project] as A
				-- Use CROSS APPLY with VALUES to unpivot the [SourceEngineID]/[TargetEngineID] pair into two rows.
				cross apply (
					values 
						(A.[SourceEngineID], 0)
					,	(A.[TargetEngineID], 1)
					) as B([EngineID], [EngineCode])
			where 
				A.[ProjectID] = @projectID
			and	B.[EngineID] is not null
			)
	,	revisions as (
			select
				A.[EngineID]
			,	A.[RevisionID]
				-- rank each engine's revisions by deployed time. Latest deployed revision gets rn = 1
			,	row_number() over (partition by A.[EngineID] order by A.[DeployedAt] desc) as [rn]
			from 
				[mig_director].[EngineRevision] as A
			)
	,	latestRevisions as (
			-- take the latest revision per engine
			select
				A.[EngineID]
			,	A.[RevisionID]
			from 
				revisions as A
			where 
				A.[rn] = 1
			)
	
	insert @usages
	select
		B.[EngineID]
	,	B.[EngineCode]
	,	C.[RevisionID]
	from 
		engines as B
		inner join latestRevisions as C
		on 
			C.[EngineID] = B.[EngineID]

	-- If EngineUsage exists for the Track and EngineCode: Update mismatches to latest revision of the correct engine
	update [mig_director].[EngineUsage]
	set 
		[RevisionID] = C.[RevisionID]
	from 
		[mig_director].[EngineUsage] as A
		inner join @tracks as B
		on 
			B.[TrackID] = A.[TrackID]
		inner join @usages as C
		on 
			C.[EngineCode] = A.[EngineCode]
		inner join [mig_director].[EngineRevision] as D
		on 
			D.[RevisionID] = A.[RevisionID]
	where 
		D.[EngineID] <> C.[EngineID];

	-- insert the missing usage rows with the latest RevisionID to ensure that the Track has an active revision 
	-- for each engine it uses. The CROSS JOIN is intentional and creates the desired cartesian product of
	-- tracks and engine usages
	insert [mig_director].[EngineUsage]
	select
		A.[TrackID]
	,	B.[EngineCode]
	,	B.[RevisionID]
	from 
		@tracks as A
		cross join @usages as B -- for each selected track, pair with each required engine side (source/target) → desired rows
		left join [mig_director].[EngineUsage] as C
		on	
			C.[TrackID] = A.[TrackID]
		and	C.[EngineCode] = B.[EngineCode]
	where 
		C.[TrackID] is null;
GO
PRINT N'Create PROCEDURE [mig_director].[Engine_GetProjects]  '
GO
create or alter PROCEDURE [mig_director].[Engine_GetProjects]
	@engineID uniqueidentifier
,	@engineCode tinyint
as
	select 
		[ProjectID]
	from
		[mig_director].[Project]
	where
		(@engineCode = 0 and @engineID = [SourceEngineID])
	or	(@engineCode = 1 and @engineID = [TargetEngineID])
GO
PRINT N'Create PROCEDURE [mig_director].[[Engine_GetRevisionUsages]  '
GO
create or alter procedure [mig_director].[Engine_GetRevisionUsages] 
	@revisionID uniqueidentifier 
as 
	select 
		B.[TrackID]
	,	D.[MachineName] as [Server]
	,	B.[NumberOnServer] as [Number]
	,	C.[Description] as [Usage]
	from
		[mig_director].[EngineUsage] as A

		inner join [mig_director].[Track] as B
		on
			A.[TrackID] = B.[TrackID]

		inner join [mig_director].[TrackUsage] as C
		on 
			B.[ActiveTrackUsage] = C.[TrackUsageID]
		and C.[Publish] = 1

		inner join [mig_director].[Server] as D
		on
			D.[ServerId] = B.[ServerId]

	where 
		A.[RevisionID] = @revisionID
GO
PRINT N'Drop [mig_director].[Engine_GetZipFile] '
GO
DROP PROCEDURE IF EXISTS [mig_director].[Engine_GetZipFile] 
GO
PRINT N'Drop [mig_director].[Engine_SetActiveDeployment] '
GO
DROP PROCEDURE IF EXISTS [mig_director].[Engine_SetActiveDeployment] 
GO
PRINT N'Alter [mig_director].[Engine_UpdateFiles] '
GO
create or alter procedure [mig_director].[Engine_UpdateFiles]
	@engineID uniqueidentifier
,	@engineCode tinyint
,	@zipFile varbinary(max)
,	@label nvarchar(max) = null
,	@loadLibrary varchar(128)
,	@user nvarchar(256)
,	@deployTo nvarchar(max)
,	@debug bit = 0
,	@revisionID uniqueidentifier output
as
	begin transaction

	set @revisionID = newid()

	-- upsert into EngineRevision
	merge [mig_director].[EngineRevision] as tgt
	using (
		select 
			@engineID as [EngineID]
		,	@revisionID as [RevisionID]
	) as src
	on 
		tgt.[EngineID] = src.[EngineID] 
	and	tgt.[RevisionID] = src.[RevisionID]
	when matched then
		update 
		set 
			[ZipFile] = @zipFile
		,	[Label] = @label
		,	[LoadLibrary] = @loadLibrary
		,	[DeployedBy] = @user
		,	[DeployedAt] = sysdatetime()
	when not matched then
		insert (
			[RevisionID]
		,	[EngineID]
		,	[ZipFile]
		,	[Label]
		,	[LoadLibrary]
		,	[DeployedAt]
		,	[DeployedBy]
		) values (
			@revisionID
		,	@engineID
		,	@zipFile
		,	@label
		,	@loadLibrary
		,	sysdatetime()
		,	@user
		);

	-- upsert into Engine
	merge [mig_director].[Engine] as tgt
	using (
		select 
			@engineID as [EngineID]
	) as src
	on 
		tgt.[EngineID] = src.[EngineID]
	when matched then
		update 
		set 
			[EngineCode] = @engineCode
	when not matched then
		insert (
			[EngineID]
		,	[EngineCode]
		,	[EngineName]
		) values (
			@engineID
		,	@engineCode
		,	case 
				when lower(rtrim(@loadLibrary)) like '%.%.dll' then left(@loadLibrary, charindex('.', @loadLibrary) - 1)
				when lower(rtrim(@loadLibrary)) like '%.dll' then left(@loadLibrary, len(@loadLibrary) - 4)
				else @loadLibrary
			end
		);

	-- Deployment
	declare @deployments table([TrackID] uniqueidentifier)

	-- Deployments from input parameter
	if @deployTo is not null and len(rtrim(ltrim(@deployTo))) > 0
	begin
		insert into @deployments
		select distinct 
			cast([value] as uniqueidentifier) 
		from 
			string_split(@deployTo, ';')
	end

	-- Deployments from track subscriptions
	insert into @deployments
	select B.[TrackID]
	from
		[mig_director].[TrackUsage] as A
		inner join [mig_director].[Track] as B 
		on 
			A.[TrackUsageID] = B.[ActiveTrackUsage]
		inner join [mig_director].[Project] as C
		on 
			A.[ProjectID] = C.[ProjectID]
		and (
				(@engineCode = 0 and C.[SourceEngineID] = @engineID)
			or	(@engineCode = 1 and C.[TargetEngineID] = @engineID)
		)
	where
		1 = case @engineCode 
				when 0 then A.[SubscribeNewestSrcEngine]
				else A.[SubscribeNewestTgtEngine]
			end
	and	not exists (
			select 1 
			from @deployments as D 
			where D.[TrackID] = B.[TrackID]
		)

	-- Do the deploy by upserting EngineUsage
	merge [mig_director].[EngineUsage] as tgt
	using (
		select 
			[TrackID]
		,	@engineCode as [engineCode]
		,	@revisionID as [revisionId]
		from @deployments
	) as src
	on 
		tgt.[TrackID] = src.[TrackID]
	and	tgt.[EngineCode] = src.[engineCode]
	when matched then
		update 
		set
			tgt.[RevisionID] = src.[revisionId]
	when not matched then
		insert (
			[TrackID]
		,	[EngineCode]
		,	[RevisionID]
		)
		values (
			src.[TrackID]
		,	src.[engineCode]
		,	src.[revisionId]
		);

	commit
GO
PRINT N'Alter [mig_director].[Engine_Delete] '
GO
alter PROCEDURE [mig_director].[Engine_Delete] 
	@engineID uniqueidentifier
as
	begin transaction

	delete [mig_director].[Engine]
	where
		[EngineID] = @engineID

	declare @deletedRevisions table([RevisionID] uniqueidentifier)

	delete [mig_director].[EngineRevision]
	output deleted.[RevisionID] into @deletedRevisions
	where
		[EngineID] = @engineID

	delete [mig_director].[EngineUsage]
	where
		[RevisionID] in (select [RevisionID] from @deletedRevisions)

	commit
GO
PRINT N'Alter [mig_director].[Engine_Modify_CreateOrUpdate] '
GO
alter PROCEDURE [mig_director].[Engine_Modify_CreateOrUpdate] 
	@engineID uniqueidentifier 
,	@engineName varchar(50)
,	@engineCode tinyint
as
	merge into [mig_director].[Engine] as tgt
	using (select 
		@engineID as [EngineID]
	,	@engineName as [EngineName]
	,	@engineCode as [EngineCode]
	) as src
	on (tgt.[EngineID] = src.[EngineID])
	when matched then
		update set
			tgt.[EngineName] = src.EngineName
		,	tgt.[EngineCode] = src.EngineCode
	when not matched then
		insert (
			[EngineID]
		,	[EngineName]
		,	[EngineCode]
		) values (
			src.[EngineID]
		,	src.[EngineName]
		,	src.[EngineCode]
		);
GO
PRINT N'Drop [mig_director].[Project_GetSourceEngineLibrary] '
GO
drop PROCEDURE if exists [mig_director].[Project_GetSourceEngineLibrary] 
GO
PRINT N'Drop [mig_director].[Project_GetTargetEngineLibrary] '
GO
drop PROCEDURE if exists [mig_director].[Project_GetTargetEngineLibrary] 
GO
PRINT N'Alter [mig_director].[Deploy_ListInstances] '
GO
alter procedure [mig_director].[Deploy_ListInstances] 
	@engineID uniqueidentifier 
as 
	select
		A.[EngineCode]
	,	A.[EngineName]
	from
		[mig_director].[Engine] as A
	where 
		A.[EngineID] = @engineID

	select
		[Name]
	,	[Type]
	,	[Value]
	from
		[mig_config].[AdditionalSetting]

	select 
		D.[TrackID]
	,	E.[MachineName] as [Server]
	,	D.[NumberOnServer] as [Number]
	,	C.[Description] as [Usage]
	,	B.[ProjectID] as [ProjectID]
	,	isnull(F.[State], 0) as [TrackState] 
	,	cast(case A.[EngineCode]
			when 0 then C.[SubscribeNewestSrcEngine]
			when 1 then C.[SubscribeNewestTgtEngine]
		end as bit) as [Subscribed]
	from
		[mig_director].[Engine] as A

		inner join [mig_director].[Project] as B
		on
			B.[ProjectState] = 1
		and ((A.[EngineCode] = 0 and A.[EngineID] = B.[SourceEngineID]) or (A.[EngineCode] = 1 and A.[EngineID] = B.[TargetEngineID]))

		inner join [mig_director].[TrackUsage] as C
		on 
			C.[ProjectID] = B.[ProjectID]
		and C.[Publish] = 1

		inner join [mig_director].[Track] as D
		on
			D.[ActiveTrackUsage] = C.[TrackUsageID]
		and	D.[IsArchive] = 0

		inner join [mig_director].[Server] as E
		on
			E.[ServerId] = D.[ServerId]

		left outer join [mig_director].[TrackProcess] as F
		on
			F.[TrackID] = D.[TrackID]

	where A.[EngineID] = @engineID
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Restrucure Server table'
if not exists (
	select 1 
	from 
		sys.columns as c 
		inner join sys.tables as t 
		on 
			c.object_id = t.object_id
	where 
		c.name = 'DeploymentCachePath' 
	and	t.schema_id = schema_id('mig_director')
	and	t.name = 'Server'
	)
begin
exec sp_ExecuteSql N'
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [mig_director].[tmp_ms_xx_Server] (
    [ServerId]            INT            IDENTITY (1, 1) NOT NULL,
    [MachineName]         NVARCHAR (250)  NOT NULL,
    [QualifiedName]       NVARCHAR (250)  NULL,
    [IP]                  NVARCHAR (250)  NOT NULL,
    [InstanceName]        NVARCHAR (250)  NULL,
    [Port]                INT            NULL,
    [DataSource]          AS             ([mig_config].[BuildDataSource]([QualifiedName], [MachineName], [InstanceName], [port])),
    [Description]         NVARCHAR (250)  NOT NULL,
    [DefaultTrackPath]    NVARCHAR (MAX) NULL,
    [DeploymentCachePath] NVARCHAR (MAX) NULL,
    [ServiceCheckInTime]  DATETIME2 (7)  NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_Server1] PRIMARY KEY CLUSTERED ([ServerId] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [mig_director].[Server])
    BEGIN
        SET IDENTITY_INSERT [mig_director].[tmp_ms_xx_Server] ON;
        INSERT INTO [mig_director].[tmp_ms_xx_Server] ([ServerId], [MachineName], [QualifiedName], [IP], [InstanceName], [Port], [Description], [DefaultTrackPath], [ServiceCheckInTime])
        SELECT   [ServerId],
                 [MachineName],
                 [QualifiedName],
                 [IP],
                 [InstanceName],
                 [Port],
                 [Description],
                 [DefaultTrackPath],
                 [ServiceCheckInTime]
        FROM     [mig_director].[Server]
        ORDER BY [ServerId] ASC;
        SET IDENTITY_INSERT [mig_director].[tmp_ms_xx_Server] OFF;
    END

DROP TABLE [mig_director].[Server];

EXECUTE sp_rename N''[mig_director].[tmp_ms_xx_Server]'', N''Server'';

EXECUTE sp_rename N''[mig_director].[tmp_ms_xx_constraint_PK_Server1]'', N''PK_Server'', N''OBJECT'';

CREATE UNIQUE NONCLUSTERED INDEX [IX_Server] ON [mig_director].[Server]([MachineName] ASC);

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
'
end
GO
PRINT N'Alter [mig_director].[Server_Create] '
GO
alter PROCEDURE [mig_director].[Server_Create] 
	@machineName nvarchar(250)
,	@description nvarchar(250)
,	@qualifiedName nvarchar(250) = null
,	@instanceName nvarchar(250) = null
,	@port int = null
,	@defaultTrackPath varchar(max) = null
,	@deploymentCachePath nvarchar(max) = null
as
	declare @id table([ServerId] int)

	insert [mig_director].[Server] (
		[MachineName]
	,	[IP]
	,	[Description]
	,	[QualifiedName]
	,	[InstanceName]
	,	[Port]
	,	[DefaultTrackPath]
	,	[DeploymentCachePath]
	) 
	output inserted.[ServerId] into @id
	values (
		@machineName
	,	''
	,	@description
	,	@qualifiedName
	,	@instanceName
	,	@port
	,	@defaultTrackPath
	,	@deploymentCachePath
	)

  select top(1) [ServerId] from @id
go
PRINT N'Alter [mig_director].[Server_List] '
GO
alter PROCEDURE [mig_director].[Server_List] 
as
	select
		[ServerId]
	,	[MachineName]
	,	[IP]
	,	[QualifiedName]
	,	[InstanceName]
	,	[Port]
	,	[Description]
	,	[DataSource]
	,	[DefaultTrackPath]
	from
		[mig_director].[Server]
	order by
		[Description]
go
PRINT N'Alter [mig_director].[Server_Modify] '
GO
alter PROCEDURE [mig_director].[Server_Modify] 
	@serverId int
,	@machineName nvarchar(250)
,	@description nvarchar(250) 
,	@qualifiedName nvarchar(250) = null
,	@instanceName nvarchar(250) = null
,	@port int = null
,	@defaultTrackPath nvarchar(max) = null
,	@deploymentCachePath nvarchar(max) = null
as
	update [mig_director].[Server]
	set
		[MachineName] = @machineName
	,	[QualifiedName] = @qualifiedName
	,	[InstanceName] = @instanceName
	,	[Port] = @port
	,	[Description] = @description
	,	[DefaultTrackPath] = @defaultTrackPath
	,	[DeploymentCachePath] = @deploymentCachePath
	where
		[ServerId] = @serverId
GO
PRINT N'Alter [mig_director].[ServiceCheckIn] '
GO
alter PROCEDURE [mig_director].[ServiceCheckIn] 
  @machineName nvarchar(250)
as
	update [mig_director].[Server ]
	set 
		[ServiceCheckInTime] = sysdatetime()
	where
		[MachineName] = @machineName
GO
PRINT N'Alter [mig_director].[Server_GetDeploymentCache] '
GO
create or alter PROCEDURE [mig_director].[Server_GetDeploymentCache] 
	@machineName nvarchar(250)
as
	select
		[DefaultTrackPath]
	,	[DeploymentCachePath]
	from
		[mig_director].[Server]
	where
		[MachineName] = @machineName
go
PRINT N'Alter [mig_director].[Server_GetDataSource] '
GO
alter PROCEDURE [mig_director].[Server_GetDataSource] 
	@machineName varchar(250) = null
,	@qualifiedName varchar(250) = null
,	@instanceName varchar(250) = null
,	@port int = null
as
	select [mig_config].[BuildDataSource](@qualifiedName, @machineName, @instanceName, @port)
go
PRINT N'Alter [mig_director].[TrackProcessCheckIn] '
GO
ALTER PROCEDURE [mig_director].[TrackProcessCheckIn]
	@trackID uniqueidentifier,
	@debugging bit = 0
as
	declare @stopRequested nvarchar(50)

	select 
		@stopRequested = [StopRequested]
	from 
		[mig_director].[TrackProcess] 
	where 
		[TrackID] = @trackID

	if @stopRequested is null
	begin
		update [mig_director].[TrackProcess]
		set 
			[State] = case @debugging
				when 0 then 1
				when 1 then 2
			end
		,	[Error] = null
		,	[ProcessCheckIn] = sysdatetime()
		where [TrackID] = @trackID
	end

	select @stopRequested
GO
PRINT N'-- Create procedure [mig_director].[Engine_GetRevision]'
GO
CREATE OR ALTER PROCEDURE [mig_director].[Engine_GetRevision]
	@trackID uniqueidentifier
,	@engineCode tinyint -- 0: SourceEngine, 1: TargetEngine 
as
	select 
		B.[RevisionID]
	,	B.[Label]
	,	B.[LoadLibrary]
	,	B.[DeployedBy]
	,	B.[DeployedAt]
	from
		[mig_director].[EngineUsage] as A
		inner join [mig_director].[EngineRevision] as B
		on
			A.[RevisionID] = B.[RevisionID]
	where
		A.[EngineCode] = @engineCode
	and	A.[TrackID] = @trackID
GO
PRINT N'-- Create procedure [mig_director].[Engine_Get]'
GO
CREATE or ALTER PROCEDURE [mig_director].[Engine_Get]
	@revisionID uniqueidentifier
as
	select 
		[ZipFile]
	from
		[mig_director].[EngineRevision]
	where
		[RevisionID] = @revisionID
go
PRINT N'-- Alter procedure [mig_director].[TrackProcessSetProcessId]'
GO
alter PROCEDURE [mig_director].[TrackProcessSetProcessId] 
  @trackID uniqueidentifier,
  @pId int
as
	update [mig_director].[TrackProcess] 
	set
		[ProcessId] = @pId
	where 
		[TrackID] = @trackID
GO
PRINT N'Update complete.'
GO