PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Prune obsolete messages: master-DB SP to enumerate active tracks for a project'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Creating procedure [mig_director].[Project_GetActiveTracks]...'
GO

CREATE OR ALTER procedure [mig_director].[Project_GetActiveTracks]
	@ProjectID uniqueidentifier
as
	set nocount on

	-----------------------------------------------------
	-- Returns one row per in-use track for @ProjectID
	-- (ProjectState=Active, TrackUsage.Publish=1,
	-- IsArchive=0). Used by the Prune Messages job to
	-- iterate the project's tracks and stream currently-
	-- occurring MessageIDs from each MigrationDB into the
	-- ProjectDB keep set.
	-- The MigrationCS column is built via mig_config.ParseCS
	-- the same way the [mig_tracking].[ProjectTrack] view
	-- does it, so the .NET caller can connect directly
	-- without a second round-trip.
	-----------------------------------------------------
	select
		c.[TrackID]
	,	b.[Description] as [TrackName]
	,	mig_config.ParseCS(
			c.[MigrationDB_CSFormat]
		,	isnull(f.[DataSource], d.[DataSource])
		,	c.[MigrationDB_Name]
		) as [MigrationCS]
	,	d.[ServerId]
	,	c.[NumberOnServer]
	from
		[mig_director].[Project] as a
		inner join [mig_director].[TrackUsage] as b
			on b.[ProjectID] = a.[ProjectID]
		inner join [mig_director].[Track] as c
			on b.[TrackUsageID] = c.[ActiveTrackUsage]
		inner join [mig_director].[Server] as d
			on c.[ServerId] = d.[ServerId]
		left outer join [mig_director].[Server] as f
			on c.[MigrationDB_ServerId] = f.[ServerId]
	where
		a.[ProjectID]		= @ProjectID
		and a.[ProjectState]	= 1
		and b.[Publish]			= 1
		and c.[IsArchive]		= 0
	order by
		d.[ServerId]
	,	c.[NumberOnServer]
GO

PRINT N'Update complete.'
GO
