/****** Object:  Schema [mig_rep]    Script Date: 2020-09-30 20:51:47 ******/
CREATE SCHEMA [mig_rep]
GO
/****** Object:  UserDefinedFunction [mig_rep].[Project_GetCopyModifications]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [mig_rep].[Project_GetCopyModifications] (
	@originalProjectID uniqueidentifier null
,	@copyProjectID uniqueidentifier null
)
returns @modifications table(
	[ProjectID] uniqueidentifier not null
,	[ItemID] uniqueidentifier not null
,	[ClassName] nvarchar(255) not null
,	[Namespace] nvarchar(255) not null
,	[Name] nvarchar(255) not null
,	[CopyCommitId] bigint not null
,	[CopyVersionId] bigint not null
,	[CopyCrc32] char(8) not null
,	[CopyCreatedBy] nvarchar(255) not null
,	[CopyDate] datetime not null
,	[OriginalCommitId] bigint 
,	[OriginalVersionId] bigint 
,	[OriginalCrc32] char(8)
,	[ForwardFitBy] nvarchar(255)
,	[ForwardFitDate] datetime
,	[ForwardFitComment] nvarchar(max)
)
as
begin
	declare @orgProjectID uniqueidentifier = @originalProjectID

	if @orgProjectID is null
	begin
		set @orgProjectID = (select top(1) [CopyOfProjectID] from [mig_rep].[Project] where [ProjectID] = @copyProjectID)
	end;

	with
		-- All the modifications in the copy done after the copy was made
		modifications as (
			select
				A.[ProjectID]
			,	C.[ItemID]
			,	B.[Namespace]
			,	B.[ClassName]
			,	C.[VersionId]
			,	C.[CommitId]
			,	C.[Name]
			,	C.[StringCrc32]
			,	C.[CreatedBy]
			,	C.[CreationDate]
			,	C.[ForwardFitBy]
			,	C.[ForwardFitDate]
			,	C.[ForwardFitComment]
			from 
				[mig_rep].[Project] as A with (NoLock)
				inner join [mig_rep].[Item] as B with (NoLock)
				on	
					A.[ProjectID] = B.[ProjectID]
				inner join [mig_rep].[ItemVersion] as C with (NoLock)
				on	
					B.[ProjectID] = C.[ProjectID]
				and	B.[ItemID] = C.[ItemID]
			where
				(
					(@originalProjectID is not null and A.[CopyOfProjectID] = @originalProjectID)
				or	(@copyProjectID is not null and A.[ProjectID] = @copyProjectID)
				)
			and A.[CreationDate] < C.[CreationDate]
			and C.[VersionId] = (	
				select max([VersionId])
				from [mig_rep].[ItemVersion]
				where
					[ProjectID] = A.[ProjectID]
				and [ItemID] = C.[ItemID]
			)
		)
		-- An original version newer that the copy version and with identical StringCrc32
		-- means that the modification is handled, even if this version is not the 
		-- latest version of the original. This is the case if the original has been 
		-- modified AFTER it was handled
	,	handledOriginals as (
			select
				A.[ItemID]
			,	A.[CommitId]
			,	A.[VersionId]
			,	A.[StringCrc32] 
			from
				[mig_rep].[ItemVersion] AS A with (NoLock)
				inner join modifications AS B with (NoLock)
				on
					A.[ProjectID] = @orgProjectID
				and	A.[ItemID] = B.[ItemID]
				and A.[CreationDate] >= b.[CreationDate]
				and A.[StringCrc32] = B.[StringCrc32]
		)
		-- Latest version of all items in original
	,	allOriginals as (
			select
				A.[ItemID]
			,	A.[CommitId]
			,	A.[VersionId]
			,	A.[StringCrc32] 
			from
				[mig_rep].[ItemVersion] AS A with (NoLock)
			where
				A.[ProjectID] = @orgProjectID
			and	A.[VersionId] = (	
					select max([VersionId])
					from [mig_rep].[ItemVersion] with (NoLock)
					where
						[ProjectID] = A.[ProjectID]
					and [ItemID] = A.[ItemID]
				)
		)
		-- The originals paired with the modifications (or null for original in case the modification is an added item in the copy)
	,	relevantOriginals as (
			select
				A.[ProjectID]
			,	A.[ItemID]
			,	B.[CommitId]
			,	B.[VersionId]
			,	B.[StringCrc32] 
			from
				modifications as A
				left outer join allOriginals as B
				on
					A.[ItemID] = B.[ItemID]
		)

	insert @modifications
	select
		A.[ProjectID]
	,	A.[ItemID]
	,	A.[ClassName]
	,	A.[Namespace]
	,	A.[Name]
	,	A.[CommitId] 
	,	A.[VersionId] 
	,	A.[StringCrc32] 
	,	A.[CreatedBy]
	,	A.[CreationDate]
	,	B.[CommitId] 
	,	B.[VersionId] 
	,	B.[StringCrc32] 
	,	A.[ForwardFitBy]
	,	A.[ForwardFitDate]
	,	A.[ForwardFitComment]
	from
		modifications as A
		inner join relevantOriginals as B
		on	
			A.[ProjectID] = B.[ProjectID]
		and	A.[ItemID] = B.[ItemID]
		left outer join handledOriginals as C with (NoLock)
		on
			A.[ItemID] = C.[ItemID]
	where
		C.[ItemId] is null
	and	(
			B.[StringCrc32] is null -- Added in copy
		or (A.[StringCrc32] <> B.[StringCrc32] and B.[StringCrc32] <> '00000000') -- Modified in copy and not deleted in original
		)	
	and not (B.[StringCrc32] is null and A.[StringCrc32] = '00000000')	-- Exclude items in copy that were added and then deleted

	return
end
GO
/****** Object:  Table [mig_rep].[Commit]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_rep].[Commit](
	[CommitId] [bigint] IDENTITY(1,1) NOT NULL,
	[ProjectID] [uniqueidentifier] NOT NULL,
	[Message] [nvarchar](max) NOT NULL,
	[Label] [nvarchar](max) NULL,
	[LinkedCommitId] [bigint] NULL,
	[IsRollBack] [bit] NULL,
	[CreatedBy] [nvarchar](255) NOT NULL,
	[CreationDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Commit] PRIMARY KEY CLUSTERED 
(
	[CommitId] ASC,
	[ProjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [mig_rep].[Item]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_rep].[Item](
	[ProjectID] [uniqueidentifier] NOT NULL,
	[ItemID] [uniqueidentifier] NOT NULL,
	[ClassName] [nvarchar](255) NOT NULL,
	[Namespace] [nvarchar](255) NOT NULL,
	[CreatedBy] [nvarchar](255) NOT NULL,
	[CreationDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED 
(
	[ProjectID] ASC,
	[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [mig_rep].[ItemVersion]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_rep].[ItemVersion](
	[VersionId] [bigint] IDENTITY(1,1) NOT NULL,
	[CommitId] [bigint] NOT NULL,
	[ProjectID] [uniqueidentifier] NOT NULL,
	[ItemID] [uniqueidentifier] NOT NULL,
	[Crc32] [char](8) NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[ParentItemID] [uniqueidentifier] NULL,
	[ItemContent]  AS (CONVERT([nvarchar](max),Decompress([_ItemContentCompressed]))),
	[_ItemContentCompressed] [varbinary](max) NULL,
	[StringCrc32] [char](8) NOT NULL,
	[StringContent]  AS (CONVERT([nvarchar](max),Decompress([_StringContentCompressed]))),
	[_StringContentCompressed] [varbinary](max) NULL,
	[Deleted]  AS (CONVERT([bit],case when [_ItemContentCompressed] IS NULL then (1) else (0) end)),
	[CreatedBy] [nvarchar](255) NOT NULL,
	[CreationDate] [datetime] NOT NULL,
	[ForwardFitBy] [nvarchar](255) NULL,
	[ForwardFitDate] [datetime] NULL,
	[ForwardFitComment] [nvarchar](max) NULL,
 CONSTRAINT [PK_ItemVersion] PRIMARY KEY CLUSTERED 
(
	[VersionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [mig_rep].[Lock]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_rep].[Lock](
	[LockId] [bigint] IDENTITY(1,1) NOT NULL,
	[ProjectID] [uniqueidentifier] NOT NULL,
	[ItemID] [uniqueidentifier] NOT NULL,
	[WorkingFolder] [nvarchar](255) NULL,
	[IsPendingDelete] [bit] NOT NULL,
	[CreatedBy] [nvarchar](255) NOT NULL,
	[CreationDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Lock] PRIMARY KEY CLUSTERED 
(
	[LockId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [mig_rep].[Policy]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_rep].[Policy](
	[PolicyName] [varchar](255) NOT NULL,
	[PolicyValue] [varchar](max) NOT NULL,
	[ModifiedBy] [varchar](255) NOT NULL,
	[ModificationDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Policy] PRIMARY KEY CLUSTERED 
(
	[PolicyName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [mig_rep].[Project]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_rep].[Project](
	[ProjectID] [uniqueidentifier] NOT NULL,
	[ProjectType] [nvarchar](50) NOT NULL,
	[Title] [nvarchar](255) NULL,
	[ItemContent] [nvarchar](max) NULL,
	[CreatedBy] [nvarchar](255) NOT NULL,
	[DeletedBy] [nvarchar](255) NULL,
	[CreationDate] [datetime] NOT NULL,
	[DeletionDate] [datetime] NULL,
	[CopyOfProjectID] [uniqueidentifier] NULL,
	[FromCommitId] [bigint] NULL,
 CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
(
	[ProjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [mig_rep].[Publish]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_rep].[Publish](
	[PublishId] [bigint] IDENTITY(1,1) NOT NULL,
	[ProjectID] [uniqueidentifier] NOT NULL,
	[Title] [nvarchar](max) NULL,
	[Publisher] [nvarchar](255) NULL,
	[ClassName] [nvarchar](255) NULL,
	[Content] [varbinary](max) NULL,
	[Comment] [nvarchar](max) NULL,
	[CreatedBy] [nvarchar](255) NOT NULL,
	[CreationDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Publish] PRIMARY KEY CLUSTERED 
(
	[PublishId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Index [IX_ItemVersion_Commit]    Script Date: 2020-09-30 20:51:47 ******/
CREATE NONCLUSTERED INDEX [IX_ItemVersion_Commit] ON [mig_rep].[ItemVersion]
(
	[ProjectID] ASC,
	[CommitId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object:  Index [IX_ItemVersion_GetVersion]    Script Date: 2020-09-30 20:51:47 ******/
CREATE NONCLUSTERED INDEX [IX_ItemVersion_GetVersion] ON [mig_rep].[ItemVersion]
(
	[ProjectID] ASC,
	[ItemID] ASC,
	[VersionId] ASC
)
INCLUDE ( 	[Crc32],
	[Deleted]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object:  Index [IX_ItemVersion_Hierarchy]    Script Date: 2020-09-30 20:51:47 ******/
CREATE NONCLUSTERED INDEX [IX_ItemVersion_Hierarchy] ON [mig_rep].[ItemVersion]
(
	[ParentItemID] ASC,
	[ItemID] ASC,
	[ProjectID] ASC,
	[VersionId] ASC,
	[Deleted] ASC,
	[Name] ASC,
	[CommitId] ASC,
	[Crc32] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_ItemVersion_Project]    Script Date: 2020-09-30 20:51:47 ******/
CREATE NONCLUSTERED INDEX [IX_ItemVersion_Project] ON [mig_rep].[ItemVersion]
(
	[ProjectID] ASC,
	[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_Lock_Project]    Script Date: 2020-09-30 20:51:47 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Lock_Project] ON [mig_rep].[Lock]
(
	[ProjectID] ASC,
	[ItemID] ASC
)
INCLUDE ( 	[WorkingFolder],
	[CreatedBy],
	[IsPendingDelete]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [mig_rep].[Commit] ADD  CONSTRAINT [DF_Commit_CreatedBy]  DEFAULT (suser_sname()) FOR [CreatedBy]
GO
ALTER TABLE [mig_rep].[Commit] ADD  CONSTRAINT [DF_Commit_CreationDate]  DEFAULT (getdate()) FOR [CreationDate]
GO
ALTER TABLE [mig_rep].[Item] ADD  CONSTRAINT [DF_Item_CreatedBy]  DEFAULT (suser_sname()) FOR [CreatedBy]
GO
ALTER TABLE [mig_rep].[Item] ADD  CONSTRAINT [DF_Item_CreationDate]  DEFAULT (getdate()) FOR [CreationDate]
GO
ALTER TABLE [mig_rep].[ItemVersion] ADD  CONSTRAINT [DF_ItemVersion_CreatedBy]  DEFAULT (suser_sname()) FOR [CreatedBy]
GO
ALTER TABLE [mig_rep].[ItemVersion] ADD  CONSTRAINT [DF_ItemVersion_CreationDate]  DEFAULT (getdate()) FOR [CreationDate]
GO
ALTER TABLE [mig_rep].[Lock] ADD  CONSTRAINT [DF_Lock_CreatedBy]  DEFAULT (suser_sname()) FOR [CreatedBy]
GO
ALTER TABLE [mig_rep].[Lock] ADD  CONSTRAINT [DF_Lock_CreationDate]  DEFAULT (getdate()) FOR [CreationDate]
GO
ALTER TABLE [mig_rep].[Policy] ADD  CONSTRAINT [DF_Policy_ModifiedBy]  DEFAULT (suser_sname()) FOR [ModifiedBy]
GO
ALTER TABLE [mig_rep].[Policy] ADD  CONSTRAINT [DF_Policy_ModificationDate]  DEFAULT (getdate()) FOR [ModificationDate]
GO
ALTER TABLE [mig_rep].[Project] ADD  CONSTRAINT [DF_Project_CreatedBy]  DEFAULT (suser_sname()) FOR [CreatedBy]
GO
ALTER TABLE [mig_rep].[Project] ADD  CONSTRAINT [DF_Project_CreationDate]  DEFAULT (getdate()) FOR [CreationDate]
GO
ALTER TABLE [mig_rep].[Publish] ADD  CONSTRAINT [DF_Publish_CreatedBy]  DEFAULT (suser_sname()) FOR [CreatedBy]
GO
ALTER TABLE [mig_rep].[Publish] ADD  CONSTRAINT [DF_Publish_CreationDate]  DEFAULT (getdate()) FOR [CreationDate]
GO
/****** Object:  StoredProcedure [mig_rep].[Commit_CreateCommit]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Commit_CreateCommit]
	@projectID uniqueidentifier
,	@message nvarchar(max)
,	@linkedCommitId bigint = null
,	@isRollback bit = null
as
	declare @tmp table([id] bigint)

	insert [mig_rep].[Commit] (
		[ProjectID]
	,	[Message]
	,	[LinkedCommitId]
	,	[IsRollBack]
	,	[CreatedBy]
	) 
	output inserted.[CommitId] into @tmp
	values (
		@projectID
	,	@message
	,	@linkedCommitId
	,	@isRollback
	,	suser_name()
	)

	select top(1) [id] from @tmp

GO
/****** Object:  StoredProcedure [mig_rep].[Commit_DiscardCommit]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Commit_DiscardCommit]
	@commitId bigint
as
	delete [mig_rep].[Commit] 
	where
		[CommitId] = @commitId

GO
/****** Object:  StoredProcedure [mig_rep].[Commit_GetCommit]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Commit_GetCommit]
	@commitId bigint
as
	-- Commit Info ---------------------------------------------------------------------------------------------------------

	declare @commit table(
		[CreatedBy] nvarchar(255)
	,	[CreationDate] datetime
	,	[Message] nvarchar(max)
	,	[LinkedCommitId] bigint null
	,	[IsRollback] bit null
	)

	insert @commit
	select 
		[CreatedBy]
	,	[CreationDate]
	,	[Message]
	,	[LInkedCommitId]
	,	[IsRollback]
	from
		[mig_rep].[Commit] with (NoLock)
	where
		[CommitId] = @commitId;

	-- Return 1. result set
	select * from @commit

	--- Included versions --------------------------------------------------------------------------------------------------
	declare @date datetime = (select top(1) [CreationDate] from @commit)

	declare @versions table(
		[ProjectID] uniqueidentifier
	,	[ItemID] uniqueidentifier
	,	[Namespace] nvarchar(255)
	,	[ClassName] nvarchar(255)
	,	[VersionId] bigint
	,	[Name] nvarchar(255)
	,	[ParentItemID] uniqueidentifier
	,	[Deleted] bit
	,	[Added] bit
	);

	with sub as (
		select 
			A.[ProjectID]
		,	A.[ItemID]
		,	A.[Namespace]
		,	A.[ClassName]
		,	B.[VersionId]
		,	B.[Name]
		,	B.[ParentItemID]
		,	B.[Deleted]
		,	(
				select min(C.[VersionId]) 
				from [mig_rep].[ItemVersion] as C with (NoLock)
				where 
					C.[ProjectID] = A.[ProjectID]
				and	C.[ItemID] = A.[ItemID]
			) as [MinVersionId]
		from
			[mig_rep].[Item] as A with (NoLock)
			inner join [mig_rep].[ItemVersion] as B with (NoLock)
			on 
				A.[ProjectID] = B.[ProjectID]
			and	A.[ItemID] = B.[ItemID]
		where
			B.[CommitId] = @commitId
	)
	insert @versions
	select 
		[ProjectID]
	,	[ItemID]
	,	[Namespace]
	,	[ClassName]
	,	[VersionId]
	,	[Name]
	,	[ParentItemID]
	,	[Deleted]
	,	case [MinVersionId]
			when [VersionId] then 1
			else 0
		end as [Added]
	from sub

	-- Return 2. result set
	select
		[ItemID]
	,	[Namespace]
	,	[ClassName]
	,	[VersionId]
	,	[Name]
	,	[ParentItemID]
	,	[Deleted]
	,	[Added]
	from @versions;

	-- Recursively get ancestors of orphans (where parent item is not included in the commit) ------------------------------
	declare @orphans table(
		[ProjectID] uniqueidentifier
	,	[ItemID] uniqueidentifier
	,	[ParentItemID] uniqueidentifier
	)

	insert @orphans
	select
		A.[ProjectID]
	,	A.[ItemID]
	,	A.[ParentItemID]
	from 
		@versions as A
		left outer join @versions as B
		on
			A.[ParentItemID] = B.[ItemID]
	where
		B.[ItemID] is null
	and	A.[ParentItemID] <> '00000000-0000-0000-0000-000000000000';

	with 
		versions as (
			select
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[VersionId]
			,	A.[ParentItemID]
			,	B.[Namespace]
			,	B.[ClassName]
			,	A.[Name]
			from
				[mig_rep].[ItemVersion] as A with (NoLock)
				inner join [mig_rep].[Item] as B with (NoLock)
				on
					A.[ProjectID] = B.[ProjectID]
				and	A.[ItemID] = B.[ItemID]
			where
				A.[VersionId] = (
					select max([VersionId])
					from [mig_rep].[ItemVersion]
					where
						[ProjectID] = A.[ProjectID]
					and [ItemID] = A.[ItemID]
					and [CreationDate] <= @date
				)
	)
	,	recurse as (
			select
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Namespace]
			,	A.[ClassName]
			,	A.[Name]
			,	B.[ItemId] as [OrphanItemID]
			,	1 as [Level]
			from 
				versions as A
				inner join @orphans as B
				on
					A.[ProjectID] = B.[ProjectID]
				and A.[ItemID] = B.[ParentItemID]

			union all

			select
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Namespace]
			,	A.[ClassName]
			,	A.[Name]
			,	B.[OrphanItemID]
			,	B.[Level] + 1
			from
			  versions as A
			  inner join recurse as B
			  on
				  A.[ProjectID] = B.[ProjectID]
			  and B.[ParentItemID] = A.[ItemID]
	)
	-- Return 3. result set
	select
		[ItemID]
	,	[ParentItemID]
	,	[OrphanItemID]
	,	[Namespace]
	,	[ClassName]
	,	[Name]
	,	[Level]
	from recurse
	where [Level] > 0

GO
/****** Object:  StoredProcedure [mig_rep].[Commit_RevertCommit]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Commit_RevertCommit]
	@commitId bigint
,	@message nvarchar(max)
as
	declare @commitItems table(
		[VersionId] bigint not null
	,	[ProjectID] uniqueidentifier not null
	,	[ItemID] uniqueidentifier not null
	,	[ParentItemID] uniqueidentifier not null
	,	[Name] nvarchar(255) not null
	,	[PreviousVersionId] bigint null
	)

	insert @commitItems
	select
		A.[VersionId]
	,	A.[ProjectID]
	,	A.[ItemID]
	,	A.[ParentItemID]
	,	A.[Name]
	,	(
			select 
				max(B.[VersionId])
			from
				[mig_rep].[ItemVersion] as B
			where
				B.[ProjectID] = A.[ProjectID]
			and	B.[ItemID] = A.[ItemID]
			and	B.[VersionId] < A.[VersionId] -- NB: Less than
		) as [PreviousVersionId]
	from
		[mig_rep].[ItemVersion] as A
	where
		A.[CommitId] = @commitId
	
	declare @locksToRelease table([LockId] bigint)

	begin transaction;
	
	-- Lock items, unique index [IX_Lock_Project] will catch concurrency conflicts as duplicate key
	begin try
		insert [mig_rep].[Lock] (
				[ProjectID], 
				[ItemID], 
				[WorkingFolder],
				[IsPendingDelete],
				[CreatedBy]
			) 
		output inserted.[LockId] into @locksToRelease -- Save the LockId's to be released at the end
		select 
			[ProjectID]
		,	[ItemID]
		,	'[Reverting commit ' + cast(@commitId as varchar(max)) + ']'
		,	0
		,	suser_name()
		from
			@commitItems
	end try
	begin catch
		if 2601 = error_number() -- Duplicate key
		begin
			rollback
			select cast(0 as bit) -- Success = false
			return -- Terminate here
		end
		else
		begin
			-- Other error
			;throw
		end
	end catch

	-- Create new Commit
	declare @projectID uniqueidentifier = (select top(1) [ProjectID] from @commitItems)

	declare @tmp table([Id] bigint)

	insert @tmp
	exec [mig_rep].[Commit_CreateCommit] 
		@projectID = @projectID
	,	@message = @message
	,	@linkedCommitId = @commitId
	,	@isRollback = 0

	declare @newCommitId bigint = (select top(1) [Id] from @tmp);

	-- Insert new versions
	with 
		previous as (
			select 
				A.[VersionId]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Name]
			,	A.[ParentItemID] as [PreviousParentItemID]
			,	A.[Name] as [PreviousName]
			,	B.[Crc32] as[PreviousCrc32]
			,	B.[_ItemContentCompressed] as [PreviousItemContent]
			,	B.[StringCrc32] as[PreviousStringCrc32]
			,	B.[_StringContentCompressed] as [PreviousStringContent]
			from
				@commitItems as A
				left outer join [mig_rep].[ItemVersion] as B
				on 
					A.[PreviousVersionId] = B.[VersionId]
		)

	insert [mig_rep].[ItemVersion] (
		[CommitId]					
	,	[ProjectID]					
	,	[ItemID]					
	,	[Crc32]						
	,	[Name]						
	,	[ParentItemID]				
	,	[_ItemContentCompressed] 
	,	[StringCrc32]
	,	[_StringContentCompressed]
	,	[CreatedBy]		
	)
	select
		@newCommitId
	,	@projectID
	,	[ItemID]
	,	isnull([PreviousCrc32], '00000000')
	,	isnull([PreviousName], [Name])
	,	isnull([PreviousParentItemID], [ParentItemID])
	,	[PreviousItemContent]
	,	isnull([PreviousStringCrc32], '00000000')
	,	[PreviousStringContent]
	,	suser_name()
	from
		previous

	-- Release Locks
	delete [mig_rep].[Lock]
	from
		[mig_rep].[Lock] as A
		inner join @locksToRelease as B
		on
			A.[LockId] = B.[LockId]

	commit

	select cast(1 as bit) -- Success = true

GO
/****** Object:  StoredProcedure [mig_rep].[Commit_RollbackToCommit]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Commit_RollbackToCommit]
	@commitId bigint
,	@message nvarchar(max)
,	@user nvarchar(50)
as
	declare @projectID uniqueidentifier = (select top(1) [ProjectID] from [mig_rep].[Commit] where [CommitId] = @commitId)

	declare @currentItems table(
		[VersionId] bigint not null
	,	[ProjectID] uniqueidentifier not null
	,	[ItemID] uniqueidentifier not null
	,	[ParentItemID] uniqueidentifier not null
	,	[Name] nvarchar(255) not null
	,	[Crc32] char(8) not null
	)

	insert @currentItems
	select
		A.[VersionId]
	,	A.[ProjectID]
	,	A.[ItemID]
	,	A.[ParentItemID]
	,	A.[Name]
	,	A.[Crc32]
	from
		[mig_rep].[ItemVersion] as A 
	where
		A.[ProjectID] = @projectID
	and	A.[CommitId] > @commitId -- NB: Greater than = Only versions that are newer than the commit
	and A.[VersionId] = (
			select 
				max(B.[VersionId])
			from
				[mig_rep].[ItemVersion] as B
			where
				B.[ProjectID] = A.[ProjectID]
			and	B.[ItemID] = A.[ItemID]
	)
	
	declare @locksToRelease table([LockId] bigint)

	begin transaction;
	
	-- Lock items, unique index [IX_Lock_Project] will catch concurrency conflicts as duplicate key
	begin try
		insert [mig_rep].[Lock] (
				[ProjectID], 
				[ItemID], 
				[WorkingFolder],
				[IsPendingDelete],
				[CreatedBy]
			) 
		output inserted.[LockId] into @locksToRelease -- Save the LockId's to be released at the end
		select 
			[ProjectID]
		,	[ItemID]
		,	'[Rolling back to commit ' + cast(@commitId as varchar(max)) + ']'
		,	0
		,	@user
		from
			@currentItems
	end try
	begin catch
		if 2601 = error_number() -- Duplicate key
		begin
			rollback
			select cast(0 as bit) -- Success = false
			return -- Terminate here
		end
		else
		begin
			-- Other error
			;throw
		end
	end catch

	-- Create new Commit
	declare @tmp table([Id] bigint)

	insert @tmp
	exec [mig_rep].[Commit_CreateCommit] 
		@projectID = @projectID
	,	@message = @message
	,	@linkedCommitId = @commitId
	,	@isRollback = 1

	declare @newCommitId bigint = (select top(1) [Id] from @tmp);

	-- Insert new versions
	with 
		previous as (
			select
				A.[VersionId]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Name]
			,	A.[Crc32]
			,	A.[_ItemContentCompressed]
			,	A.[StringCrc32] as[PreviousStringCrc32]
			,	A.[_StringContentCompressed] as [PreviousStringContent]
			from
				[mig_rep].[ItemVersion] as A
			where
				A.[ProjectID] = @projectID
			and	A.[VersionId] = (
					select 
						max(B.[VersionId])
					from
						[mig_rep].[ItemVersion] as B
					where
						B.[ProjectID] = A.[ProjectID]
					and	B.[ItemID] = A.[ItemID]
					and B.[CommitId] <= @commitId -- NB: Less than or equal = Only versions that were current at the time of the commit 
			)
		)

	insert [mig_rep].[ItemVersion] (
		[CommitId]					
	,	[ProjectID]					
	,	[ItemID]					
	,	[Crc32]						
	,	[Name]						
	,	[ParentItemID]				
	,	[_ItemContentCompressed] 
	,	[StringCrc32]
	,	[_StringContentCompressed]
	,	[CreatedBy]		
	)
	select
		@newCommitId
	,	@projectID
	,	A.[ItemID]
	,	isnull(B.[Crc32], '00000000')
	,	isnull(B.[Name], A.[Name])
	,	isnull(B.[ParentItemID], A.[ParentItemID])
	,	B.[_ItemContentCompressed]
	,	isnull(B.[PreviousStringCrc32], '00000000')
	,	B.[PreviousStringContent]
	,	@user
	from
		@currentItems as A
		left outer join previous as B
		on	
			A.[ItemID] = B.[ItemID]
	where
		B.[VersionId] is null or A.[Crc32] <> B.[Crc32]

	-- Release Locks
	delete [mig_rep].[Lock]
	from
		[mig_rep].[Lock] as A
		inner join @locksToRelease as B
		on
			A.[LockId] = B.[LockId]

	commit

	select cast(1 as bit) -- Success = true
GO
/****** Object:  StoredProcedure [mig_rep].[Commit_SetLabel]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Commit_SetLabel]
	@commitId bigint,
	@label nvarchar(max) = null
as
	update [mig_rep].[Commit]
	set
		[Label] = @label
	where
		[CommitId] = @commitId

GO
/****** Object:  StoredProcedure [mig_rep].[Commit_UpdateMessage]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Commit_UpdateMessage]
	@commitId bigint,
	@message nvarchar(max)
as
	update [mig_rep].[Commit]
	set	
		[Message] = @message
	where
		[CommitId] = @commitId

GO
/****** Object:  StoredProcedure [mig_rep].[GetSchemaLevel]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[GetSchemaLevel]
as
	select 1;

GO
/****** Object:  StoredProcedure [mig_rep].[Item_CheckIn]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Item_CheckIn]
	@projectID uniqueidentifier
,	@commitId bigint
,	@itemID uniqueidentifier
,	@name nvarchar(255)
,	@className nvarchar(255)
,	@namespace nvarchar(255)
,	@parentItemID uniqueidentifier
,	@crc32 char(8) = null
,	@itemContent nvarchar(max) = null
,	@stringCrc32 char(8) = null
,	@stringContent nvarchar(max) = null
as
	set nocount off 
		
	declare @currentCrc32 char(8)
	declare @deletedLock table([IsPendingDelete] bit)
	declare @lockWasPendingDelete bit = 0
	declare @changed bit = 0

	-- Remove lock and recover the IsPendingDelete of the deleted lock
	delete [mig_rep].[Lock]
	output deleted.[IsPendingDelete] into @deletedLock
	where 
		[ProjectID] = @projectID and [ItemID] = @itemID

	select top(1)
		@lockWasPendingDelete = [IsPendingDelete]
	from
		@deletedLock

	-- Update Item
	update [Item]
	set
		[ClassName] = @className,
		[Namespace] = @namespace
	where
		[ProjectID] = @projectID and [ItemID] = @itemID

	if 0 = @@rowCount
	begin
		-- First time, insert Item
		insert [mig_rep].[Item] (
			[ProjectID],
			[ItemID],
			[ClassName],
			[Namespace],
			[CreatedBy]
		) values (
			@projectID,
			@itemID,
			@className,
			@namespace,
			suser_name()
		)
	end

	-- Get the Crc32 from existing version to verify if item has changed
	select 
		@currentCrc32 = [Crc32]
	from 
		[mig_rep].[ItemVersion] 
	where 
		[ProjectID] = @projectID and [ItemID] = @itemID
	and [VersionId] = (
		select
			max([VersionId])
		from 
			[mig_rep].[ItemVersion] 
		where 
			[ProjectID] = @projectID and [ItemID] = @itemID
	)

	if isnull(@currentCrc32, '') <> @crc32 or (1 = @lockWasPendingDelete and isnull(@currentCrc32, '') <> '00000000')
	begin
		-- Insert new version
		insert [mig_rep].[ItemVersion] (
			[CommitId]
		,	[ProjectID]
		,	[ItemID]
		,	[Crc32]
		,	[Name]
		,	[ParentItemID]
		,	[CreatedBy]
		,	[_ItemContentCompressed]
		,	[StringCrc32]
		,	[_StringContentCompressed]
		) values (
			@commitId
		,	@projectID
		,	@itemID
		,	case @lockWasPendingDelete
				when 0 then @crc32
				else '00000000'
			end
		,	@name
		,	@parentItemID
		,	suser_name()
		,	case @lockWasPendingDelete
				when 0 then compress(@itemContent)
				else null
			end
		,	case @lockWasPendingDelete
				when 0 then @stringCrc32
				else '00000000'
			end 
		,	case @lockWasPendingDelete
				when 0 then compress(@stringContent)
				else null
			end
		)

		set @changed = 1
	end
	
	if 1 = @changed
	begin
		-- Checkin children in pending delete
		declare csr cursor local for 
		select
			A.[ItemID]
		,	A.[Name]
		,	B.[ClassName]
		,	B.[Namespace]
		from	
			[mig_rep].[ItemVersion] as A
			inner join [mig_rep].[Item] as B
				on
					B.[ProjectID] = A.[ProjectID] 
				and	B.[ItemID] = A.[ItemID]
			inner join [mig_rep].[Lock] as C 
				on
					C.[ProjectID] = A.[ProjectID] 
				and	C.[ItemID] = A.[ItemID]
		where
			a.[VersionId] = (
				select max([VersionId])
				from [mig_rep].[ItemVersion] 
				where
					[ProjectID] = A.[ProjectID] 
				and	[ItemID] = A.[ItemID]
			) 
		and	A.[ParentItemID] = @itemID
		and C.[IsPendingDelete] = 1

		declare @childItemID uniqueidentifier
		declare @childName varchar(max)
		declare @childClassName varchar(max)
		declare @childNamespace varchar(max)

		open csr

		fetch next from csr into @childItemID, @childName, @childClassName, @childNamespace

		while 0 = @@fetch_status
		begin
			exec [mig_rep].[Item_CheckIn]
				@projectID = @projectID
			,	@commitId = @commitId
			,	@itemID = @childItemID
			,	@name = @childName
			,	@className = @childClassName
			,	@namespace = @childNamespace
			,	@parentItemID = @itemID

			fetch next from csr into @childItemID, @childName, @childClassName, @childNamespace
		end

		close csr
		deallocate csr
	end

	select @changed

GO
/****** Object:  StoredProcedure [mig_rep].[Item_CheckOut]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Item_CheckOut]
	@projectID uniqueidentifier,
	@itemID uniqueidentifier,
	@workingFolder nvarchar(255),
	@isPendingDelete bit
as
	set nocount off 
	
	-- if pending delete, update any possible previous checkout done for edit 
	if 1 = @isPendingDelete
	begin
		update [mig_rep].[Lock]
		set
			[IsPendingDelete] = 1
		where
			[ProjectID] = @projectID
		and	[ItemID] = @itemID
	end

	if 0 = @@rowCount
	begin
		-- Add lock
		insert [mig_rep].[Lock] (
			[ProjectID], 
			[ItemID], 
			[WorkingFolder],
			[IsPendingDelete],
			[CreatedBy]
		) values (
			@projectID, 
			@itemID, 
			@workingFolder,
			@isPendingDelete,
			suser_name()
		)
	end

GO
/****** Object:  StoredProcedure [mig_rep].[Item_GetDeletionRoot]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Item_GetDeletionRoot]
	@projectID uniqueidentifier
,	@itemID uniqueidentifier 
as
	with
		items as (
			select 
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Name]
			,	A.[CommitId]
			,	A.[Deleted]
			,	A.[Crc32]
			,	A.[VersionId]
			from
				[mig_rep].[ItemVersion] as A with (NoLock)
			where
				A.[ProjectID] = @projectID
			and A.[VersionId] = (
				select max(B.[VersionId])
				from
					[mig_rep].[ItemVersion] as B with (NoLock)
				where
					A.[ProjectID] = B.[ProjectID]
				and A.[ItemID] = B.[ItemID]
			)
		)
	,	hierarchy as (
			select
				[ProjectID]
			,	[ItemID]
			,	[ParentItemID]
			,	[Name]
			,	[CommitId]
			,	[VersionId]
			,	[Crc32]
			,	[Deleted] as [ParentDeleted]
			,	[Deleted]
			,	0 as [Level]
			from
				items
			where
				[ItemID] = @itemID

			union all
			
			select
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Name]
			,	A.[CommitId]
			,	A.[VersionId]
			,	A.[Crc32]
			,	A.[Deleted] 
			,	B.[Deleted]
			,	B.[Level] + 1
			from
				items as A
				inner join hierarchy as B
				on
					A.[ItemID] = B.[ParentItemID]
				and A.[CommitId] = B.[CommitId]
			where
				B.[ParentDeleted] = 1
		)
	,	deletionRoot as (
			select top(1)
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Name]
			,	A.[CommitId]
			,	A.[VersionId]
			,	A.[Crc32]
			,	A.[ParentDeleted]
			from 
				hierarchy as A
			order by 
				A.[Level] desc
		)
	,	pth as (
			select
				[ItemID]
			,	[ItemID] as [ID]
			,	[ParentItemID]
			,	0 as [Level]
			,	cast([Name] as nvarchar(max)) as [Path] 
			from
				deletionRoot
			union all
			select
				A.[ItemID]
			,	B.[ItemID]
			,	A.[ParentItemID]
			,	B.[Level] + 1
			,	A.[Name] + '/' + B.[Path]
			from
				items as A
				inner join pth as B
				on 
					A.[ItemID] = B.[ParentItemID]
		)

	-- Result set aligned to ProjectItemInfo class
	select top(1)
		A.[ProjectID]
	,	A.[ItemID]
	,	A.[ParentItemID]
	,	A.[Name]
	,	C.[ClassName]
	,	C.[Namespace]
	,	A.[VersionId]
	,	A.[CommitId]
	,	A.[Crc32]
	,	A.[ParentDeleted]
	,	cast(0 as bit) as [First]
	,	D.[CreationDate]
	,	D.[CreatedBy]
	,	D.[Message]
	,	B.[Path]
	from 
		deletionRoot as A
		inner join pth as B
		on 
			A.[ItemID] = B.[ItemID]
		inner join [mig_rep].[Item] as C
		on
			C.[ProjectID] = A.[ProjectID]
		and	C.[ItemID] = A.[ItemID]
		inner join [mig_rep].[Commit] as D
		on 
			A.[CommitId] = D.[CommitId]
	order by 
		B.[Level] desc
GO
/****** Object:  StoredProcedure [mig_rep].[Item_GetHistory]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Item_GetHistory]
	@projectID uniqueidentifier,
	@itemID uniqueidentifier
as
	declare @versions table(
		[VersionId] bigint
	,	[Name] nvarchar(255)
	,	[Deleted] bit
	,	[Crc32] char(8)
	,	[CommitId] bigint
	,	[Generation] int
	,	[CreatedBy] nvarchar(255)
	,	[CreationDate] datetime
	,	[Message] nvarchar(max)
	,	[LinkedCommitId] bigint null
	,	[IsRollback] bit null
	)

	insert @versions
	select	
		A.[VersionId]
	,	A.[Name]
	,	A.[Deleted]
	,	A.[Crc32]
	,	B.[CommitId]
	,	row_number() over (order by A.[CommitId])
	,	B.[CreatedBy]
	,	B.[CreationDate]
	,	B.[Message]
	,	B.[LinkedCommitId]
	,	B.[IsRollBack]
	from 
		[mig_rep].[ItemVersion] as A with (NoLock)
		inner join [mig_rep].[Commit] as B with (NoLock)
		on
			A.[CommitId] = B.[CommitId]
		and A.[ProjectID] = B.[ProjectID]
	where	
		A.[ProjectID] = @projectID
	and A.[ItemID] = @itemID

	select * 
	from 
		@versions 
	order by 
		[VersionId] desc;

	with 
		versions as ( 
			select
				[VersionId]
			,	[CommitId]
			,	[Generation]
			from @versions
		)
	,	annotatedVersions as (
			select
				A.[VersionId]
			,	A.[CommitId]
			,	isNull(B.[CommitId], 0x7FFFFFFFFFFFFFFF) as [NextCommitId] -- BigInt max value
			from 
				versions as A
				left outer join versions as B
				on 
					A.[Generation] = B.[Generation] - 1
		)
	,	labels as (
			select
				[CommitId]
			,	[Label]
			from
				[mig_rep].[Commit]
			where
				[Label] is not null
		)

	select	
		A.[VersionId]
	,	B.[CommitId]
	,	B.[Label]
	from 
		annotatedVersions as A
		inner join labels as B
		on	
			A.[CommitId] <= B.[CommitId]
		and A.[NextCommitId] > B.[CommitId]

GO
/****** Object:  StoredProcedure [mig_rep].[Item_GetState]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Item_GetState]
	@projectID uniqueidentifier,
	@itemID uniqueidentifier,
	@createdBy nvarchar(255) = null output,
	@workingFolder nvarchar(255) = null output,
	@isDeleted bit = 1 output,
	@checkedOutToMe bit = 1 output,
	@isPendingDelete bit = 0 output,
	@crc32 char(8) = null output
as
	set nocount off
	
	select distinct
		@isDeleted = A.[Deleted],
		@crc32 = A.[Crc32],
		@checkedOutToMe = 
			case B.[CreatedBy]
				when suser_name() then 1
				else 0
			end,
		@createdBy = isnull(B.[CreatedBy], ''),
		@workingFolder = isnull(B.[WorkingFolder], ''),
		@isPendingDelete = isnull(B.[IsPendingDelete], 0)
	from
		[mig_rep].[ItemVersion] as A with (NoLock)
		left outer join [mig_rep].[Lock] as B with (NoLock)
			on 
				B.[ProjectID] = A.[ProjectID] 
			and B.[ItemID] = A.[ItemID]
	where
		A.[ProjectID] = @projectID and A.[ItemID] = @itemID
	and A.[VersionId] = (
		select max([VersionId])
		from
			[mig_rep].[ItemVersion] with (NoLock)
		where
			[ProjectID] = A.[ProjectID]
		and	[ItemID] = A.[ItemID]
	)

GO
/****** Object:  StoredProcedure [mig_rep].[Item_GetVersionLatest]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Item_GetVersionLatest]
	@projectID uniqueidentifier,
	@itemID uniqueidentifier,
	@crc32 char(8),
	@maxCommitId bigint = null
as
	select
		A.[ItemContent]
	from
		[mig_rep].[ItemVersion] as A with (NoLock)
	where
		A.[ProjectID] = @projectID and A.[ItemID] = @itemID
	and	A.[VersionId] = (
		select 
			max(B.[VersionId]) 
		from 
			[mig_rep].[ItemVersion] as B with (NoLock)
		where
			B.[ProjectID] = A.[ProjectID] 
		and B.[ItemID] = A.[ItemID]
		and (@maxCommitId is null or [CommitId] <= @maxCommitId)
	)
	and	A.[Deleted] = 0
	and A.[Crc32] <> @crc32
GO
/****** Object:  StoredProcedure [mig_rep].[Item_GetVersionLatestString]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Item_GetVersionLatestString]
	@projectID uniqueidentifier,
	@itemID uniqueidentifier
as
	select
		A.[StringContent]
	from
		[mig_rep].[ItemVersion] as A with (NoLock)
	where
		A.[ProjectID] = @projectID 
	and A.[ItemID] = @itemID
	and	A.[VersionId] = (
		select 
			max(B.[VersionId]) 
		from 
			[mig_rep].[ItemVersion] as B with (NoLock)
		where
			B.[ProjectID] = A.[ProjectID] 
		and B.[ItemID] = A.[ItemID]
	)
	and	A.[Deleted] = 0

GO
/****** Object:  StoredProcedure [mig_rep].[Item_GetVersionSpecific]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Item_GetVersionSpecific]
	@versionId bigint
as
	select
		[ItemContent]
	from
		[mig_rep].[ItemVersion] with (NoLock)
	where
		[VersionId] = @versionId

GO
/****** Object:  StoredProcedure [mig_rep].[Item_GetVersionSpecificString]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Item_GetVersionSpecificString]
	@versionId bigint
as
	select
		[StringContent]
	from
		[mig_rep].[ItemVersion] with (NoLock)
	where
		[VersionId] = @versionId

GO
/****** Object:  StoredProcedure [mig_rep].[Item_RestoreVersion]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Item_RestoreVersion] 
	@versionId bigint
,	@message nvarchar(max)
as
	declare @commitItems table(
		[VersionId] bigint not null
	,	[ProjectID] uniqueidentifier not null
	,	[ItemID] uniqueidentifier not null
	);

	with
		items as (
			select
				A.[VersionId]
			,	A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Deleted]
			from
				[mig_rep].[ItemVersion] as A
				inner join [mig_rep].[ItemVersion] as B
				on
					A.[CommitId] = B.[CommitId]
				and	B.[VersionId] = @versionId
		)
	,	itemHierarchy as (
			select
				[VersionId]
			,	[ProjectID]
			,	[ItemID]
			,	[ParentItemID]
			,	[Deleted]
			from
				items
			where
				[VersionId] = @versionId
			union all
			select
				A.[VersionId]
			,	A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Deleted]	
			from
				items as A
				inner join itemHierarchy as B
				on
					A.[ParentItemID] = B.ItemID
		)

	insert @commitItems
	select 
		A.[VersionId]
	,	A.[ProjectID] 
	,	A.[ItemID] 
	from
		itemHierarchy as A
		inner join [mig_rep].[ItemVersion] as B -- Join back to Latest Version to check that it is deleted
		on
			A.[ProjectID] = B.[ProjectID]
		and A.[ItemID] = B.[ItemID]
	where
		(
			A.[Deleted] <> B.[Deleted] -- Descendant: Newest version is deleted but the commited version is not, or newest version is not deleted but committed version is deleted
		or	A.[VersionId] = @versionId -- The root item itself
		) 
	and	B.[VersionId] = (
			select max(B1.[VersionId])
			from
				[mig_rep].[ItemVersion] as B1
			where
				B.[ProjectID] = B1.[ProjectID]
			and	B.[ItemID] = B1.[ItemID]
		)

	declare @locksToRelease table([LockId] bigint)

	begin transaction;
	
	-- Lock items, unique index [IX_Lock_Project] will catch concurrency conflicts as duplicate key
	begin try
		insert [mig_rep].[Lock] (
				[ProjectID], 
				[ItemID], 
				[WorkingFolder],
				[IsPendingDelete],
				[CreatedBy]
			) 
		output inserted.[LockId] into @locksToRelease -- Save the LockId's to be released at the end
		select 
			[ProjectID]
		,	[ItemID]
		,	'[Restoring version ' + cast(@versionId as varchar(max)) + ']'
		,	0
		,	suser_name()
		from
			@commitItems
	end try
	begin catch
		if 2601 = error_number() -- Duplicate key
		begin
			rollback
			select cast(0 as bit) -- Success = false
			return -- Terminate here
		end
		else
		begin
			-- Other error
			;throw
		end
	end catch

	-- Create new Commit
	declare @projectID uniqueidentifier = (select top(1) [ProjectID] from @commitItems)

	declare @tmp table([Id] bigint)

	insert @tmp
	exec [mig_rep].[Commit_CreateCommit] 
		@projectID = @projectID
	,	@message = @message
	,	@linkedCommitId = null
	,	@isRollback = 0

	declare @newCommitId bigint = (select top(1) [Id] from @tmp);

	-- Insert new versions
	with 
		previous as (
			select 
				B.[VersionId]
			,	B.[ItemID]
			,	B.[ParentItemID]
			,	B.[Name] 
			,	B.[Crc32] 
			,	B.[_ItemContentCompressed] 
			,	B.[StringCrc32] 
			,	B.[_StringContentCompressed]
			from
				@commitItems as A
				left outer join [mig_rep].[ItemVersion] as B
				on 
					A.[VersionId] = B.[VersionId]
		)

	insert [mig_rep].[ItemVersion] (
		[CommitId]					
	,	[ProjectID]					
	,	[ItemID]					
	,	[Crc32]						
	,	[Name]						
	,	[ParentItemID]				
	,	[_ItemContentCompressed] 
	,	[StringCrc32]
	,	[_StringContentCompressed]
	,	[CreatedBy]		
	)
	select
		@newCommitId
	,	@projectID
	,	[ItemID]
	,	[Crc32]
	,	[Name]
	,	[ParentItemID]
	,	[_ItemContentCompressed]
	,	[StringCrc32]
	,	[_StringContentCompressed]
	,	suser_name()
	from
		previous

	-- Release Locks
	delete [mig_rep].[Lock]
	from
		[mig_rep].[Lock] as A
		inner join @locksToRelease as B
		on
			A.[LockId] = B.[LockId]

	commit

	select cast(1 as bit) -- Success = true

GO
/****** Object:  StoredProcedure [mig_rep].[Item_SetCrc32]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Item_SetCrc32]
	@projectID uniqueidentifier
,	@itemID uniqueidentifier
,	@crc32 char(8)
,	@versionId bigint = null
as
	update [mig_rep].[ItemVersion]
	set
		[Crc32] = @crc32
	where
		(@versionId is null and [VersionId] = (select max([VersionId]) from [mig_rep].[ItemVersion] where [ProjectID] = @projectID and [ItemID] = @itemID))
	or	(@versionId is not null and [VersionId] = @versionId)

GO
/****** Object:  StoredProcedure [mig_rep].[Item_SetModificationHandled]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [mig_rep].[Item_SetModificationHandled]
	@versionId bigint
,	@comment nvarchar(max) = null
as

	if @comment is null
	begin
		update [mig_rep].[ItemVersion]
		set
			[ForwardFitBy] = null
		,	[ForwardFitDate] = null
		,	[ForwardFitComment] = null
		where
			[VersionId] = @versionId
	end
	else
	begin
		update [mig_rep].[ItemVersion]
		set
			[ForwardFitBy] = suser_name()
		,	[ForwardFitDate] = getdate()
		,	[ForwardFitComment] = @comment
		where
			[VersionId] = @versionId
	end

GO
/****** Object:  StoredProcedure [mig_rep].[Item_UndoCheckOut]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Item_UndoCheckOut]
	@projectID uniqueidentifier,
	@itemID uniqueidentifier
as
	with
		items as (
			select 
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			from
				[mig_rep].[ItemVersion] as A
			where
				A.[ProjectID] = @projectID
			and A.[Deleted] = 0
			and A.[VersionId] = (
				select max(B.[VersionId])
				from
					[mig_rep].[ItemVersion] as B
				where
					A.[ProjectID] = B.[ProjectID]
				and A.[ItemID] = B.[ItemID]
			)
		)
	,	hierarchy as (
			select
				[ProjectID]
			,	[ItemID]
			,	[ParentItemID]
			from
				items
			where
				[ItemID] = @itemID

			union all
			
			select
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			from
				items as A
				inner join hierarchy as B
				on
					A.[ParentItemID] = B.[ItemID]
		)

	delete [mig_rep].[Lock]
	from
		[mig_rep].[Lock] as A
		inner join hierarchy as B 
		on 
			A.[ProjectID] = B.[ProjectID] 
		and A.[ItemID] = B.[ItemID]
	where
		A.[IsPendingDelete] = 1
	or	A.[ItemID] = @itemID

GO
/****** Object:  StoredProcedure [mig_rep].[Policy_Get]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Policy_Get]
	@policyName varchar(255)
as
	select 
		[PolicyValue]
	from
		[mig_rep].[Policy]
	where
		[PolicyName] = @policyName

GO
/****** Object:  StoredProcedure [mig_rep].[Policy_Set]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Policy_Set]
	@policyName varchar(255),
	@policyValue varchar(max)
as
	update [mig_rep].[Policy]
	set
		[PolicyValue] = @policyValue
	where
		[PolicyName] = @policyName

	if 0 = @@rowCount
	begin
		insert [mig_rep].[Policy] (
			[PolicyName],
			[PolicyValue]
		) values (
			@policyName,
			@policyValue
		)
	end

GO
/****** Object:  StoredProcedure [mig_rep].[Project_AddProject]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_AddProject]
	@projectID UNIQUEIDENTIFIER,
	@projectType NVARCHAR(50),
	@title NVARCHAR(255),
	@itemContent NVARCHAR(MAX)
as
	set nocount off

	declare @newProject bit = 0

	-- Try to restore existing project
	update [mig_rep].[Project]
	set 
		[ProjectType] = @projectType,
		[Title] = @title,
		[ItemContent] = @itemContent,
		[DeletedBy] = null,
		[DeletionDate] = null
	where 
		[ProjectID] = @projectID and [DeletionDate] is not null

	if 0 = @@rowCount
	begin
		-- Deleted project doesn't exist
		set @newProject = 1

		insert into [mig_rep].[Project] (
			[ProjectID], 
			[ProjectType], 
			[Title], 
			[ItemContent]
		) values (
			@projectID, 
			@projectType, 
			@title, 
			@itemContent
		)
	end

	select @newProject

GO
/****** Object:  StoredProcedure [mig_rep].[Project_CopyProject]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [mig_rep].[Project_CopyProject]
	@fromProjectID uniqueidentifier
,	@fromCommitId bigint
,	@copyTitle nvarchar(255)
,	@newProjectID uniqueidentifier = null output
,	@definitionID uniqueidentifier = null output
,	@definitionXml nvarchar(max) = null output
as
	set @newProjectID = newid()

	-- Copy Project
	declare @date datetime = getdate()
	declare @x xml = (select convert(xml, [ItemContent]) from [mig_rep].[Project] where ProjectID = @fromProjectID)
	declare @idChar varchar(50) = lower(convert(varchar(50), @newProjectID))

	set @x.modify('
		replace value of (*/@itemId)[1]
		with sql:variable("@idChar")
	')

	set @x.modify('
		replace value of (*/displayName/text())[1]
		with  sql:variable("@copyTitle")
	')

	set @x.modify('
		replace value of (*/definitionRef/name/text())[1]
		with  sql:variable("@copyTitle")
	')

	set @x.modify('
		replace value of (*/definitionRef/displayName/text())[1]
		with  sql:variable("@copyTitle")
	')

	insert [mig_rep].[Project] (
		[ProjectID]
	,	[ProjectType]
	,	[Title]
	,	[ItemContent]
	,	[CreatedBy]
	,	[CreationDate]
	,	[CopyOfProjectID]
	,	[FromCommitId]
	) 
	select
		@newProjectID
	,	[ProjectType]
	,	@copyTitle
	,	convert(nvarchar(max), @x)
	,	suser_name()
	,	@date
	,	@fromProjectID
	,	@fromCommitId
	from [mig_rep].[Project]
	where
		[ProjectID] = @fromProjectID

	-- Create a Commit for the Project Copy
	declare @tmp table([id] bigint)

	insert @tmp
	exec [mig_rep].[Commit_CreateCommit] @newProjectID, 'Project Copy Created'

	declare @commitId int = (select top(1) [id] from @tmp)
	
	-- Copy last Versions
	insert [mig_rep].[ItemVersion] (
		[CommitId]
	,	[ProjectID]
	,	[ItemID]
	,	[Crc32]
	,	[Name]
	,	[ParentItemID]
	,	[_ItemContentCompressed]
	,	[StringCrc32]
	,	[_StringContentCompressed]
	,	[CreatedBy]
	,	[CreationDate]
	)
	select
		@commitId
	,	@newProjectID
	,	A.[ItemID]
	,	A.[Crc32]
	,	A.[Name]
	,	A.[ParentItemID]
	,	A.[_ItemContentCompressed]
	,	[StringCrc32]
	,	[_StringContentCompressed]
	,	A.[CreatedBy]
	,	A.[CreationDate]
	from 
		[mig_rep].[ItemVersion] as A
	where
		A.[ProjectID] = @fromProjectID
	and	A.[Deleted] = 0
	and	A.[VersionId] = (
		select max([VersionId])
		from [mig_rep].[ItemVersion]
		where 
			[ProjectID] = @fromProjectID
		and [ItemID] = A.[ItemID]
		and [CommitId] <= @fromCommitId
	)

	-- Copy Items
	insert [mig_rep].[Item] (
		[ProjectID]
	,	[ItemID]
	,	[ClassName]
	,	[Namespace]
	,	[CreatedBy]
	,	[CreationDate]
	)
	select
		@newProjectID
	,	A.[ItemID]
	,	A.[ClassName]
	,	A.[Namespace]
	,	A.[CreatedBy]
	,	A.[CreationDate]
	from 
		[mig_rep].[Item] as A
		inner join [mig_rep].[ItemVersion] as B -- Joining back to the newly copied versions to avoid coying deleted items
		on 
			B.[ProjectID] = @newProjectID
		and	A.[ItemID] = B.[ItemID]
	where
		A.[ProjectID] = @fromProjectID
		
	-- Modify main item
	declare @mainItemID uniqueidentifier = @x.value('(*/definitionRef/itemId)[1]', 'uniqueidentifier')

	select 
		@x = convert(xml, [ItemContent])
	from
		[mig_rep].[ItemVersion]
	where
		[ProjectID] = @newProjectID 
	and [ItemID] = @mainItemID

	set @x.modify('
		replace value of (*/header/name/text())[1]
		with  sql:variable("@copyTitle")
	')

	update [mig_rep].[ItemVersion]
	set
		[_ItemContentCompressed] = compress(convert(nvarchar(max), @x))
	where
		[ProjectID] = @newProjectID 
	and [ItemID] = @mainItemID

	set @definitionID = @mainItemID
	set @definitionXml = cast(@x as nvarchar(max))

GO
/****** Object:  StoredProcedure [mig_rep].[Project_DeleteLock]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_DeleteLock]
	@lockId bigint = null
,	@projectID uniqueidentifier = null
,	@itemID uniqueidentifier = null
as

	delete [mig_rep].[Lock]
	where
		(@lockId is not null and [LockId] = @lockId)
	or	(@itemID is not null and [ProjectID] = @projectID and [ItemID] = @itemID)

GO
/****** Object:  StoredProcedure [mig_rep].[Project_ClearLocks] ******/
create procedure [mig_rep].[Project_ClearLocks]
	@projectID uniqueidentifier
as
	declare @user nvarchar(256) = suser_name()

	delete [mig_rep].[Lock]
	where
		[ProjectID] = @projectID
	and [CreatedBy] = @user
go
/****** Object:  StoredProcedure [mig_rep].[Project_GetCommitHistory]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_GetCommitHistory]
	@projectID uniqueidentifier
as
	set nocount off 
	
	select	
		A.[CommitId]
	,	A.[CreatedBy]
	,	A.[CreationDate]
	,	A.[Message]
	,	isnull(A.[Label], '') as [Label]
	,	count(*) as [Size]
	,	A.[LinkedCommitId]
	,	A.[IsRollback]
	from 
		[mig_rep].[Commit] as A with (NoLock)
		inner join [mig_rep].[ItemVersion] as B with (NoLock)
		on
			A.[CommitId] = B.[CommitId]
		and A.[ProjectID] = B.[ProjectID]
	where	
		A.[ProjectID] = @projectID
	group by
		A.[CommitId]
	,	A.[CreatedBy]
	,	A.[CreationDate]
	,	A.[Message]
	,	A.[LinkedCommitId]
	,	A.[IsRollBack]
	,	A.[Label]
	order by
		A.[CommitId] desc

GO
/****** Object:  StoredProcedure [mig_rep].[Project_GetItemList]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_GetItemList]
	@projectID uniqueidentifier
,	@deleted bit,
	@maxCommitId bigint = null
as
	with
		[count] as (
			select
				[ItemID]
			,	count(*) as [Count]
			from 
				[mig_rep].[ItemVersion] with (NoLock)
			group by
				[ItemID]
		)
	,	items as (
			select	
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Name]
			,	A.[VersionId]
			,	A.[CommitId]
			,	A.[Deleted]
			,	A.[Crc32]
			,	cast(case B.[Count]
					when 1 then 1
					else 0 
				end as bit) as [First]
			from 
				[mig_rep].[ItemVersion] as A with (NoLock)
				inner join [count] as B
				on
					A.[ItemID] = B.[ItemID]
			where	
				A.[ProjectID] = @projectID
			and A.[VersionId] = (
				select max([VersionId])
				from
					[mig_rep].[ItemVersion] with (NoLock)
				where
					[ProjectID] = A.[ProjectID]
				and	[ItemID] = A.[ItemID]
				and (@maxCommitId is null or [CommitId] <= @maxCommitId)
			)
		)
	,	hierarchy as (
			select
				[ProjectID]
			,	[ItemID]
			,	[ParentItemID]
			,	[Name]
			,	[VersionId]
			,	[CommitId]
			,	[Deleted]
			,	[First]
			,	[Crc32]
			,	cast([Name] as nvarchar(max)) as [Path]
			from
				items
			where
				[ParentItemID] = '00000000-0000-0000-0000-000000000000'

			union all
			
			select
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Name]
			,	A.[VersionId]
			,	A.[CommitId]
			,	A.[Deleted] 
			,	A.[First]
			,	A.[Crc32]
			,	B.[Path] + '/' + A.[Name]
			from
				items as A
				inner join hierarchy as B
				on
					A.[ProjectID] = B.[ProjectID]
				and	A.[ParentItemID] = B.[ItemID]
		)

	select
		A.[ProjectID]
	,	A.[ItemID]
	,	A.[ParentItemID]
	,	A.[Name]
	,	B.[ClassName]
	,	B.[Namespace]
	,	A.[VersionId]
	,	A.[CommitId]
	,	A.[Crc32]
	,	A.[Deleted]
	,	A.[First]
	,	C.[CreationDate]
	,	C.[CreatedBy]
	,	C.[Message]
	,	A.[Path]
	from
		hierarchy as A
		inner join [mig_rep].[Item] as B with (NoLock)
		on
			A.[ProjectID] = B.[ProjectID]
		and	A.[ItemID] = B.[ItemID]
		inner join [mig_rep].[Commit] as C with (NoLock)
		on
			A.[CommitId] = C.[CommitId]
	where
		A.[Deleted] = @deleted
	order by
		A.[VersionId] desc
GO
/****** Object:  StoredProcedure [mig_rep].[Project_GetItemListHierarchy]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_GetItemListHierarchy] 
	@projectID uniqueidentifier
,	@itemID uniqueidentifier = null
,	@className nvarchar(255) = null
,	@namespace nvarchar(255) = null
as
	declare @rootItems table(
		[ProjectID] uniqueidentifier not null
	,	[ItemID] uniqueidentifier not null
	)

	insert @rootItems
	select
		[ProjectID]
	,	[ItemID]
	from
		[mig_rep].[Item] with (NoLock)
	where
		[ProjectID] = @projectID
	and	(
			-- Get hierarchy for 1 item
			(@itemID is not null and [ItemID] = @itemID)
			-- Get Hierarchy for catalog
		or	(@itemID is null and [ClassName] = @className and [Namespace] = @namespace)
	);

	with 
		versions as (
			select	
				A.[ProjectID] 
			,	A.[ItemID] 
			,	A.[Name]
			,	A.[ParentItemID]
			,	A.[VersionId]
			,	A.[CommitId]
			,	A.[Crc32]
			from 
				[mig_rep].[ItemVersion] as A with (NoLock)
			where	
				A.[ProjectID] = @projectID
			and A.[Deleted] = 0
			and A.[VersionId] = (
				select max([VersionId])
				from
					[mig_rep].[ItemVersion] with (NoLock)
				where
					[ProjectID] = A.[ProjectID]
				and	[ItemID] = A.[ItemID]
			)
		)
	,	hierarchy as (
			-- Anchor
			select	
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Name]
			,	A.[VersionId]
			,	A.[CommitId]
			,	A.[Crc32]
			,	0 as [Level]
			,	cast([Name] as nvarchar(max)) as [Path]
			from 
				versions as A
				inner join @rootItems as B
				on
					A.[ProjectID] = B.[ProjectID]
				and	A.[ItemID] = B.[ItemID]

			union all

			-- Recursive member
			select	
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Name]
			,	A.[VersionId]
			,	A.[CommitId]
			,	A.[Crc32]
			,	B.[Level] + 1
			,	B.[Path] + '/' + A.[Name]
			from 
				versions as A
				inner join hierarchy as B
				on	
					A.[ProjectID] = B.[ProjectID] 
				and	A.[ParentItemID] = B.[ItemID]
		)

	select 
		A.[ProjectID]
	,	A.[ItemID]
	,	A.[ParentItemID]
	,	A.[Name]
	,	B.[ClassName]
	,	B.[Namespace]
	,	A.[VersionId]
	,	A.[CommitId]
	,	A.[Crc32]
	,	cast(0 as bit) as [Deleted] 
	,	cast(0 as bit) as [First] 
	,	C.[CreationDate]
	,	C.[CreatedBy]
	,	C.[Message]
	,	A.[Path]
	from 
		hierarchy as A
		inner join [mig_rep].[Item] as B with (NoLock)
		on
			A.[ProjectID] = B.[ProjectID]
		and	A.[ItemID] = B.[ItemID]
		inner join [mig_rep].[Commit] as C with (NoLock)
		on
			A.[CommitId] = C.[CommitId]
	order by 
		A.[Level] desc
	option (maxrecursion 30);
GO
/****** Object:  StoredProcedure [mig_rep].[Project_GetItemStates]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_GetItemStates]
	@projectID uniqueidentifier
as
	select
		A.[ItemID],
		A.[Deleted],
		A.[Crc32],
		isnull(B.[CreatedBy], '') as [CheckedOutBy],
		isnull(B.[WorkingFolder], '') as [WorkingFolder],
		isnull(B.[IsPendingDelete], 0) as [IsPendingDelete],
		cast(case B.[CreatedBy]
			when suser_name() then 1
			else 0
		end as bit) as [CheckedOutToMe]
	from
		[mig_rep].[ItemVersion] as A with (NoLock)
		left outer join [mig_rep].[Lock] as B with (NoLock)
			on 
				B.[ProjectID] = A.[ProjectID] 
			and B.[ItemID] = A.[ItemID]
	where
		A.[ProjectID] = @projectID 
	and A.[VersionId] = (
		select max([VersionId])
		from
			[mig_rep].[ItemVersion] with (NoLock)
		where
			[ProjectID] = A.[ProjectID]
		and	[ItemID] = A.[ItemID]
	)

GO
/****** Object:  StoredProcedure [mig_rep].[Project_GetLastCommitId]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_GetLastCommitId]
	@projectID uniqueidentifier
as
	select	
		isnull(max([CommitId]), 0)
	from 
		[mig_rep].[Commit] as A with (NoLock)
	where	
		[ProjectID] = @projectID

GO
/****** Object:  StoredProcedure [mig_rep].[Project_GetList]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_GetList]
	@projectType nvarchar(50) = null
as
	set nocount off 
	
	select
		A.[ProjectID]
	,	A.[Title]
	,	A.[ProjectType]
	,	A.[CreatedBy]
	,	A.[CreationDate]
	,	A.[DeletedBy]
	,	A.[DeletionDate]
	,	(
			select
				max([CreationDate])
			from
				[mig_rep].[ItemVersion] as B with (NoLock)
			where
				B.[ProjectID] = A.[ProjectID]
		) as [ModificationDate]
	,	B.[Title] as [CopyOfProject]
	from
		[mig_rep].[Project] as A with (NoLock)
		left outer join [mig_rep].[Project] as B with (NoLock)
		on
			A.CopyOfProjectID = B.[ProjectID]
	where
		@projectType is null or A.[ProjectType] = @projectType

GO
/****** Object:  StoredProcedure [mig_rep].[Project_GetModificationList]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [mig_rep].[Project_GetModificationList]
	@projectID uniqueidentifier
as
	select * 
	from [mig_rep].[Project_GetCopyModifications](null, @projectID)
	order by
		[CopyVersionId] desc

GO
/****** Object:  StoredProcedure [mig_rep].[Project_GetPublishedContent]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_rep].[Project_GetPublishedContent]
	@publishId bigint
as
	set nocount off

	select	
		[Content]
	from 
		[mig_rep].[Publish]
	where 
		[PublishId] = @publishId

GO
/****** Object:  StoredProcedure [mig_rep].[Project_GetPublishedList]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_GetPublishedList]
	@projectID uniqueidentifier = null
as
	set nocount off 
	
	select	
		[PublishId]
	,	[ProjectID]
	,	[Title]
	,	[Publisher]
	,	[ClassName]
	,	[Comment]
	,	[CreationDate]
	from 
		[mig_rep].[Publish]
	where	
		[ProjectID] = @projectID
	order by 
		[PublishId] desc

GO
/****** Object:  StoredProcedure [mig_rep].[Project_GetXmlContent]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_GetXmlContent]
	@projectID uniqueidentifier
as
	select
		[ItemContent]
	from 
		[mig_rep].[Project]	
	where 
		[ProjectID] = @projectID
	and	[DeletionDate] is null

GO
/****** Object:  StoredProcedure [mig_rep].[Project_ListCheckouts]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_ListCheckouts]
	@projectID uniqueidentifier
as
	with 
		latest as (
			select
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	B.[Namespace]
			,	B.[ClassName]
			,	A.[Name]
			from
				[mig_rep].[ItemVersion] as A with (NoLock)
				inner join [mig_rep].[Item] as B with (NoLock)
				on
					A.[ProjectID] = B.[ProjectID]
				and	A.[ItemID] = B.[ItemID]
			where
				A.[ProjectID] = @projectID
			and	A.[VersionId] = (
					select max([VersionId])
					from [mig_rep].[ItemVersion] with (NoLock)
					where
						[ProjectID] = A.[ProjectID]
					and [ItemID] = A.[ItemID]
				)
		)
	,	recurse as (
			select
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Namespace]
			,	A.[ClassName]
			,	A.[Name]
			,	cast(A.[Name] as nvarchar(max)) as [Path]
			from 
				latest as A
			where
				A.[ParentItemID] = '00000000-0000-0000-0000-000000000000'

			union all

			select
				A.[ProjectID]
			,	A.[ItemID]
			,	A.[ParentItemID]
			,	A.[Namespace]
			,	A.[ClassName]
			,	A.[Name]
			,	cast(B.[Path] + '/' + A.[Name] as nvarchar(max))
			from
			  latest as A
			  inner join recurse as B
			  on
				  A.[ProjectID] = B.[ProjectID]
			  and A.[ParentItemID] = B.[ItemID]
		)
	select
		B.[LockId]
	,	A.[ItemID]
	,	B.[CreatedBy]
	,	B.[CreationDate]
	,	B.[WorkingFolder]
	,	A.[Namespace]
	,	A.[ClassName]
	,	B.[IsPendingDelete]
	,	A.[Name]
	,	A.[Path]
	from 
		recurse as A
		inner join [mig_rep].[Lock] as B with (NoLock)
		on 
			A.[ItemID] = B.[ItemID]
	where
		B.[ProjectID] = @projectID
	order by 
		B.[LockId] desc

GO
/****** Object:  StoredProcedure [mig_rep].[Project_ListCopies]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_ListCopies]
	@originalProjectID uniqueidentifier
as
	with 
		modifications as (
			select
				[ProjectID]
			,	case [CopyCrc32]
					when '00000000' then 1
					else 0
				end as [Deleted]
			,	case 
					when [OriginalCrc32] is null then 1
					else 0
				end as [Added]
			,	case 
					when [CopyCrc32] <> '00000000' and [OriginalCrc32] is not null then 1
					else 0
				end as [Modified]
			,	case 
					when [ForwardFitBy] is null then 0
					else 1
				end as [Handled]
			from 
				[mig_rep].[Project_GetCopyModifications](@originalProjectID, null)
		)
	,	counts as (
			select
				[ProjectID]
			,	sum([Deleted]) as [Deleted]
			,	sum([Added]) as [Added]
			,	sum([Modified]) as [Modified]
			,	sum([Handled]) as [Handled]
			from
				modifications
			group by
				[ProjectID]
	)
	select
		A.[ProjectID]
	,	A.[Title]
	,	A.[CreatedBy]
	,	A.[CreationDate]
	,	A.[DeletedBy]
	,	A.[DeletionDate]
	,	A.[FromCommitId]
	,	isnull(B.[Deleted], 0) as [Deleted]
	,	isnull(B.[Added], 0) as [Added]
	,	isnull(B.[Modified], 0) as [Modified]
	,	isnull(B.[Handled], 0) as [Handled]
	from 
		[mig_rep].[Project] as A with (NoLock)
		left outer join counts as B
			on A.[ProjectID] = B.[ProjectID]
	where
		A.[CopyOfProjectID] = @originalProjectID
	order by
		A.[CreationDate]
	,	A.[Title]

GO
/****** Object:  StoredProcedure [mig_rep].[Project_ProjectExists]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_ProjectExists]
	@projectID uniqueidentifier
as
	set nocount off
	
	declare @exists bit = 0

	select 
		@exists = 1
	from
		[mig_rep].[Project] with (NoLock)
	where
		[ProjectID] = @projectID
	and	[DeletionDate] is null

	select @exists

GO
/****** Object:  StoredProcedure [mig_rep].[Project_PublishContent]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_PublishContent]
	@projectID uniqueidentifier,
	@title nvarchar(max),
	@classname nvarchar(255),
	@content varbinary(max),
	@comment nvarchar(max) = null
as
	set nocount off

	insert into [mig_rep].[Publish] (
		[ProjectID], 
		[Title], 
		[Publisher], 
		[ClassName], 
		[Content], 
		[Comment]
	) values (
		@projectID, 
		@title, 
		suser_name(), 
		@classname, 
		@content, 
		@comment
	)

GO
/****** Object:  StoredProcedure [mig_rep].[Project_PurgeProject]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_rep].[Project_PurgeProject]
	@projectID uniqueidentifier
as
	begin transaction

	delete from [mig_rep].[Lock] where ProjectID = @projectID
	delete from [mig_rep].[Commit] where ProjectID = @projectID
	delete from [mig_rep].[ItemVersion] where ProjectID = @projectID
	delete from [mig_rep].[Item] where ProjectID = @projectID
	delete from [mig_rep].[Publish] where ProjectID = @projectID
	delete from [mig_rep].[Project] where ProjectID = @projectID

	commit transaction

GO
/****** Object:  StoredProcedure [mig_rep].[Project_SetTitle]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [mig_rep].[Project_SetTitle]
	@projectID uniqueidentifier
,	@title nvarchar(255)
,	@definitionID uniqueidentifier = null output
,	@definitionXml nvarchar(max) = null output
as
	declare @x xml = (select convert(xml, [ItemContent]) from [mig_rep].[Project] where ProjectID = @projectID)

	set @x.modify('
		replace value of (*/displayName/text())[1]
		with sql:variable("@title")
	')

	set @x.modify('
		replace value of (*/definitionRef/name/text())[1]
		with  sql:variable("@title")
	')

	set @x.modify('
		replace value of (*/definitionRef/displayName/text())[1]
		with  sql:variable("@title")
	')

	update [mig_rep].[Project]
	set 
		[Title] = @title,
		[ItemContent] = convert(nvarchar(max), @x)
	where
		[ProjectID] = @projectID

	-- Modify main item
	declare @mainItemID uniqueidentifier = @x.value('(*/definitionRef/itemId)[1]', 'uniqueidentifier')
	declare @versionId bigint

	select 
		@versionId = [VersionId]
	,	@x = convert(xml, [ItemContent])
	from
		[mig_rep].[ItemVersion]
	where
		[ProjectID] = @projectID 
	and [ItemID] = @mainItemID
	and [VersionId] = (
		select max([VersionId])
		from [mig_rep].[ItemVersion]
		where
			[ProjectID] = @projectID 
		and [ItemID] = @mainItemID
	)

	set @x.modify('
		replace value of (*/*/name/text())[1]
		with sql:variable("@title")
	')

	update [mig_rep].[ItemVersion]
	set
		[_ItemContentCompressed] = compress(convert(nvarchar(max), @x))
	where
		[ProjectID] = @projectID 
	and [VersionId] = @versionId

	set @definitionID = @mainItemID
	set @definitionXml = cast(@x as nvarchar(max))

GO
/****** Object:  StoredProcedure [mig_rep].[Project_ToggleDeleteMark]    Script Date: 2020-09-30 20:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [mig_rep].[Project_ToggleDeleteMark]
	@projectID uniqueidentifier,
	@delete bit
as
	update [mig_rep].[Project]
	set
		[DeletionDate] = 
			case @delete 
				when 1 then getdate()
				else null
			end,
		[DeletedBy] =  
			case @delete 
				when 1 then suser_name()
				else null
			end
	where ProjectID = @projectID

GO