/****** Object:  Schema [mig_tracking]    Script Date: 21.06.2023 14:08:54 ******/
CREATE SCHEMA [mig_tracking]
GO
/****** Object:  Schema [mig_utility]    Script Date: 21.06.2023 14:08:54 ******/
CREATE SCHEMA [mig_utility]
GO
/****** Object:  UserDefinedFunction [mig_tracking].[ufCountCondition]    Script Date: 21.06.2023 14:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [mig_tracking].[ufCountCondition]
(
    @countValue bigint
)
RETURNS varchar(10)
AS
BEGIN

    RETURN
        CASE
            WHEN @countValue IS NULL THEN '(null)'
            WHEN @countValue = 0 THEN '0'
            WHEN @countValue > 0 THEN '>0'
            WHEN @countValue < 0 THEN '<0'
        END

END


GO
/****** Object:  UserDefinedFunction [mig_tracking].[ufGetAutomaticState]    Script Date: 21.06.2023 14:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [mig_tracking].[ufGetAutomaticState] 
(
    @actualCount bigint,
    @baselineCount bigint
)
RETURNS int
AS
BEGIN

    DECLARE @result int;

    DECLARE @condActual varchar(10);
    DECLARE @condBaseline varchar(10);

    -- Get conditions
    SET @condActual   = [mig_tracking].[ufCountCondition](@actualCount);
    SET @condBaseline = [mig_tracking].[ufCountCondition](@baselineCount);

    -- Get state
    SELECT TOP 1 @result = [AutomaticStateCode]
    FROM [mig_tracking].[AutomaticStateRule]
    WHERE ISNULL([ActualCountCondition], @condActual) = @condActual
      AND ISNULL([BaselineCountCondition], @condBaseline) = @condBaseline
    ORDER BY [SortOrder];

	-- Return the result of the function
	RETURN @result;

END


GO
/****** Object:  UserDefinedFunction [mig_utility].[ParseDelimitedString]    Script Date: 21.06.2023 14:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [mig_utility].[ParseDelimitedString] (
	@delimitedString varchar(max)
,   @delimiter varchar(50) = ','
) returns @strings table(
	[Value] varchar(8000)
)
as
begin
	declare @index int = -1 
	declare @text varchar(max) = @delimitedString

	while (len(@text) > 0) 
	begin  
		set @index = charindex(@delimiter, @text)  

		if (@index = 0) and (len(@text) > 0)  
		begin   
			insert into @strings values (@text)
			break  
		end  

		if (@index > 1)  
		begin   
			insert into @strings values (left(@text, @index - 1))   
			set @text = right(@text, (len(@text) - @index))  
		end  
		else 
		begin
			set @text = right(@text, (len(@text) - @index)) 
		end
	end

	return
end
GO
/****** Object:  UserDefinedFunction [mig_utility].[ParseGuids]    Script Date: 21.06.2023 14:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [mig_utility].[ParseGuids] 
(
	@delimitedString nvarchar(max)
, @delimiter nvarchar(50) = ','
)
returns @ids table
(
	Id uniqueidentifier
)
as
begin
  insert into @ids
  select convert(uniqueidentifier, [Value]) 
  from [mig_utility].[ParseDelimitedString](@delimitedString, @delimiter)

  return
end
GO
/****** Object:  UserDefinedFunction [mig_utility].[ParseInfoList]    Script Date: 21.06.2023 14:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [mig_utility].[ParseInfoList](
	@list xml
)
returns @values table([Value] varchar(max))
as
begin
	if @list is not null
	begin
		insert @values
		select  
		 [Values].[ref].value('.[1]', 'varchar(max)') as Value
		 from @list.nodes('/*/*/*') [Values](ref)
	end
	
	return
end
GO
/****** Object:  UserDefinedFunction [mig_utility].[ParseIntegers]    Script Date: 21.06.2023 14:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [mig_utility].[ParseIntegers] 
(
	@delimitedString nvarchar(max)
, @delimiter nvarchar(50) = ','
)
returns @integers table
(
	[Value] int
)
as
begin
  insert into @integers
  select convert(int, [Value]) 
  from [mig_utility].[ParseDelimitedString](@delimitedString, @delimiter)

  return
end
GO

/****** Object:  Table [mig_tracking].[MessageState]    Script Date: 21.06.2023 14:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[MessageState](
	[MessageStateCode] [int] NOT NULL,
	[Label] [varchar](50) NOT NULL,
 CONSTRAINT [PK_MessageState] PRIMARY KEY CLUSTERED 
(
	[MessageStateCode] 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_tracking].[MessageStateMatrix]    Script Date: 21.06.2023 14:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[MessageStateMatrix](
	[UserStateCode] [int] NOT NULL,
	[AutomaticStateCode] [int] NOT NULL,
	[MessageStateCode] [int] NOT NULL,
 CONSTRAINT [PK_MessageStateMatrix] PRIMARY KEY CLUSTERED 
(
	[UserStateCode] ASC,
	[AutomaticStateCode] 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:  View [mig_tracking].[vwMessageState]    Script Date: 21.06.2023 14:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [mig_tracking].[vwMessageState]
AS
select	
	A.[UserStateCode]
, A.[AutomaticStateCode]
, B.[Label]
from
	[mig_tracking].[MessageStateMatrix] as A 
	inner join [mig_tracking].[MessageState] as B on A.[MessageStateCode] = B.[MessageStateCode]



GO
/****** Object:  Table [mig_tracking].[AutomaticState]    Script Date: 21.06.2023 14:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[AutomaticState](
	[AutomaticStateCode] [int] NOT NULL,
	[Label] [varchar](50) NOT NULL,
 CONSTRAINT [PK_AutomaticState] PRIMARY KEY CLUSTERED 
(
	[AutomaticStateCode] 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_tracking].[AutomaticStateRule]    Script Date: 21.06.2023 14:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[AutomaticStateRule](
	[RuleId] [int] IDENTITY(1,1) NOT NULL,
	[SortOrder] [int] NOT NULL,
	[ActualCountCondition] [varchar](10) NULL,
	[BaselineCountCondition] [varchar](50) NULL,
	[AutomaticStateCode] [int] NOT NULL,
	[Comment] [varchar](128) NULL,
 CONSTRAINT [PK_AutomaticStateRule] PRIMARY KEY CLUSTERED 
(
	[RuleId] 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_tracking].[UserState]    Script Date: 21.06.2023 14:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[UserState](
	[UserStateCode] [int] NOT NULL,
	[Label] [varchar](50) NOT NULL,
	[IsTeamUserState] [bit] NOT NULL,
	[IsExternalUserState] [bit] NOT NULL,
	[IsCompleted] [bit] NOT NULL,
	[SortOrder] [int] NOT NULL,
 CONSTRAINT [PK_UserState] PRIMARY KEY CLUSTERED 
(
	[UserStateCode] 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_tracking].[UserStateUpdate]    Script Date: 21.06.2023 14:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[UserStateUpdate](
	[UserStateCode] [int] NOT NULL,
	[AutomaticStateCode] [int] NOT NULL,
	[NewUserStateCode] [int] NOT NULL,
 CONSTRAINT [PK_UserStateUpdate] PRIMARY KEY CLUSTERED 
(
	[UserStateCode] ASC,
	[AutomaticStateCode] 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
INSERT [mig_tracking].[AutomaticState] ([AutomaticStateCode], [Label]) VALUES (1, N'New')
GO
INSERT [mig_tracking].[AutomaticState] ([AutomaticStateCode], [Label]) VALUES (2, N'Reoccurs')
GO
INSERT [mig_tracking].[AutomaticState] ([AutomaticStateCode], [Label]) VALUES (3, N'Reopened')
GO
INSERT [mig_tracking].[AutomaticState] ([AutomaticStateCode], [Label]) VALUES (4, N'Gone')
GO
SET IDENTITY_INSERT [mig_tracking].[AutomaticStateRule] ON 
GO
INSERT [mig_tracking].[AutomaticStateRule] ([RuleId], [SortOrder], [ActualCountCondition], [BaselineCountCondition], [AutomaticStateCode], [Comment]) VALUES (1, 100, N'>0', N'(null)', 1, N'New')
GO
INSERT [mig_tracking].[AutomaticStateRule] ([RuleId], [SortOrder], [ActualCountCondition], [BaselineCountCondition], [AutomaticStateCode], [Comment]) VALUES (2, 100, N'>0', N'>0', 2, N'Reoccurs')
GO
INSERT [mig_tracking].[AutomaticStateRule] ([RuleId], [SortOrder], [ActualCountCondition], [BaselineCountCondition], [AutomaticStateCode], [Comment]) VALUES (3, 100, N'>0', N'0', 3, N'Reopened')
GO
INSERT [mig_tracking].[AutomaticStateRule] ([RuleId], [SortOrder], [ActualCountCondition], [BaselineCountCondition], [AutomaticStateCode], [Comment]) VALUES (4, 100, N'0', NULL, 4, N'Gone')
GO
INSERT [mig_tracking].[AutomaticStateRule] ([RuleId], [SortOrder], [ActualCountCondition], [BaselineCountCondition], [AutomaticStateCode], [Comment]) VALUES (5, 100, N'(null)', NULL, 4, N'Gone')
GO
SET IDENTITY_INSERT [mig_tracking].[AutomaticStateRule] OFF
GO
INSERT [mig_tracking].[MessageState] ([MessageStateCode], [Label]) VALUES (1, N'New')
GO
INSERT [mig_tracking].[MessageState] ([MessageStateCode], [Label]) VALUES (2, N'New?')
GO
INSERT [mig_tracking].[MessageState] ([MessageStateCode], [Label]) VALUES (3, N'Waiting')
GO
INSERT [mig_tracking].[MessageState] ([MessageStateCode], [Label]) VALUES (4, N'In Progress')
GO
INSERT [mig_tracking].[MessageState] ([MessageStateCode], [Label]) VALUES (5, N'Accepted')
GO
INSERT [mig_tracking].[MessageState] ([MessageStateCode], [Label]) VALUES (6, N'Resolved')
GO
INSERT [mig_tracking].[MessageState] ([MessageStateCode], [Label]) VALUES (7, N'Regression')
GO
INSERT [mig_tracking].[MessageState] ([MessageStateCode], [Label]) VALUES (8, N'Done')
GO
INSERT [mig_tracking].[MessageState] ([MessageStateCode], [Label]) VALUES (9, N'Done?')
GO
INSERT [mig_tracking].[MessageState] ([MessageStateCode], [Label]) VALUES (10, N'Waiting?')
GO
INSERT [mig_tracking].[MessageState] ([MessageStateCode], [Label]) VALUES (11, N'Rejected')
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (0, 1, 1)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (0, 2, 1)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (0, 3, 1)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (0, 4, 8)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (1, 1, 7)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (1, 2, 7)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (1, 3, 7)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (1, 4, 9)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (2, 1, 6)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (2, 2, 6)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (2, 3, 6)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (2, 4, 8)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (3, 1, 3)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (3, 2, 3)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (3, 3, 3)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (3, 4, 8)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (4, 1, 4)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (4, 2, 4)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (4, 3, 4)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (4, 4, 9)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (5, 1, 5)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (5, 2, 5)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (5, 3, 5)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (5, 4, 5)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (6, 1, 11)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (6, 2, 11)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (6, 3, 11)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (6, 4, 8)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (7, 1, 10)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (7, 2, 10)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (7, 3, 10)
GO
INSERT [mig_tracking].[MessageStateMatrix] ([UserStateCode], [AutomaticStateCode], [MessageStateCode]) VALUES (7, 4, 9)
GO
INSERT [mig_tracking].[UserState] ([UserStateCode], [Label], [IsTeamUserState], [IsExternalUserState], [IsCompleted], [SortOrder]) VALUES (0, N'None', 1, 1, 0, 0)
GO
INSERT [mig_tracking].[UserState] ([UserStateCode], [Label], [IsTeamUserState], [IsExternalUserState], [IsCompleted], [SortOrder]) VALUES (1, N'Regression', 1, 1, 0, 0)
GO
INSERT [mig_tracking].[UserState] ([UserStateCode], [Label], [IsTeamUserState], [IsExternalUserState], [IsCompleted], [SortOrder]) VALUES (2, N'Resolved', 1, 1, 1, 0)
GO
INSERT [mig_tracking].[UserState] ([UserStateCode], [Label], [IsTeamUserState], [IsExternalUserState], [IsCompleted], [SortOrder]) VALUES (3, N'Waiting', 0, 0, 0, 0)
GO
INSERT [mig_tracking].[UserState] ([UserStateCode], [Label], [IsTeamUserState], [IsExternalUserState], [IsCompleted], [SortOrder]) VALUES (4, N'In Progress', 1, 1, 0, 0)
GO
INSERT [mig_tracking].[UserState] ([UserStateCode], [Label], [IsTeamUserState], [IsExternalUserState], [IsCompleted], [SortOrder]) VALUES (5, N'Accepted', 1, 1, 1, 0)
GO
INSERT [mig_tracking].[UserState] ([UserStateCode], [Label], [IsTeamUserState], [IsExternalUserState], [IsCompleted], [SortOrder]) VALUES (6, N'Rejected', 0, 1, 0, 0)
GO
INSERT [mig_tracking].[UserState] ([UserStateCode], [Label], [IsTeamUserState], [IsExternalUserState], [IsCompleted], [SortOrder]) VALUES (7, N'Waiting?', 0, 0, 0, 0)
GO
INSERT [mig_tracking].[UserStateUpdate] ([UserStateCode], [AutomaticStateCode], [NewUserStateCode]) VALUES (1, 4, 2)
GO
INSERT [mig_tracking].[UserStateUpdate] ([UserStateCode], [AutomaticStateCode], [NewUserStateCode]) VALUES (2, 1, 1)
GO
INSERT [mig_tracking].[UserStateUpdate] ([UserStateCode], [AutomaticStateCode], [NewUserStateCode]) VALUES (2, 2, 1)
GO
INSERT [mig_tracking].[UserStateUpdate] ([UserStateCode], [AutomaticStateCode], [NewUserStateCode]) VALUES (2, 3, 1)
GO
ALTER TABLE [mig_tracking].[AutomaticStateRule] ADD  CONSTRAINT [DF_AutomaticStateRule_SortOrder]  DEFAULT ((100)) FOR [SortOrder]
GO
ALTER TABLE [mig_tracking].[UserState] ADD  CONSTRAINT [DF_UserState_IsTeamUser]  DEFAULT ((0)) FOR [IsTeamUserState]
GO
ALTER TABLE [mig_tracking].[UserState] ADD  CONSTRAINT [DF_UserState_IsExternalUser]  DEFAULT ((0)) FOR [IsExternalUserState]
GO
ALTER TABLE [mig_tracking].[UserState] ADD  CONSTRAINT [DF_UserState_IsCompleted]  DEFAULT ((0)) FOR [IsCompleted]
GO
ALTER TABLE [mig_tracking].[UserState] ADD  CONSTRAINT [DF_UserState_SortOrder]  DEFAULT ((0)) FOR [SortOrder]
GO
ALTER TABLE [mig_tracking].[MessageStateMatrix]  WITH CHECK ADD  CONSTRAINT [FK_MessageStateMatrix_AutomaticState] FOREIGN KEY([AutomaticStateCode])
REFERENCES [mig_tracking].[AutomaticState] ([AutomaticStateCode])
GO
ALTER TABLE [mig_tracking].[MessageStateMatrix] CHECK CONSTRAINT [FK_MessageStateMatrix_AutomaticState]
GO
ALTER TABLE [mig_tracking].[MessageStateMatrix]  WITH CHECK ADD  CONSTRAINT [FK_MessageStateMatrix_MessageState] FOREIGN KEY([MessageStateCode])
REFERENCES [mig_tracking].[MessageState] ([MessageStateCode])
GO
ALTER TABLE [mig_tracking].[MessageStateMatrix] CHECK CONSTRAINT [FK_MessageStateMatrix_MessageState]
GO
ALTER TABLE [mig_tracking].[MessageStateMatrix]  WITH CHECK ADD  CONSTRAINT [FK_MessageStateMatrix_UserState] FOREIGN KEY([UserStateCode])
REFERENCES [mig_tracking].[UserState] ([UserStateCode])
GO
ALTER TABLE [mig_tracking].[MessageStateMatrix] CHECK CONSTRAINT [FK_MessageStateMatrix_UserState]
GO