SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE @Major int, @Minor int, @Patch int, @Installed DateTime, @Prereqs int

Set @Major = 3;
Set @Minor = 1;
Set @Patch = 28;

Select @Prereqs = isnull(Count(InstallDate),0)  from cs_SchemaVersion where Major=@Major and Minor=@Minor and Patch<@Patch

Select @Installed = InstallDate  from cs_SchemaVersion where Major=@Major and Minor=@Minor and Patch=@Patch

If(@Installed is null AND @Prereqs = @Patch)
	BEGIN
--## Schema Patch ##

-- Create cs_Messaging_Messages table
EXECUTE sp_executesql N'
CREATE TABLE [dbo].[cs_Messaging_Messages](
	[MessageId] [uniqueidentifier] NOT NULL,
	[AuthorId] [int] NOT NULL,
	[Subject] [nvarchar](100) NOT NULL,
	[Body] [nvarchar](1000) NOT NULL,
	[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_cs_Messaging_Messages_DateCreated]  DEFAULT (getdate()),
 CONSTRAINT [PK_cs_Messaging_Messages] PRIMARY KEY CLUSTERED 
(
	[MessageId] ASC
)) ON [PRIMARY]'

-- Create foreign keys for cs_Messaging_Messages table
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_Messages]  WITH CHECK ADD  CONSTRAINT [FK_cs_Messaging_Messages_cs_Users] FOREIGN KEY([AuthorId]) REFERENCES [dbo].[cs_Users] ([UserID])'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_Messages] CHECK CONSTRAINT [FK_cs_Messaging_Messages_cs_Users]'

-- Create indexes for cs_Messaging_Messages table
EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_Messaging_Messages_AuthorId] ON [dbo].[cs_Messaging_Messages] ([AuthorId]) WITH FILLFACTOR = 90 ON [PRIMARY]'

-- Create cs_Messaging_Conversations table
EXECUTE sp_executesql N'
CREATE TABLE [dbo].[cs_Messaging_Conversations](
	[ConversationId] [uniqueidentifier] NOT NULL,
	[DateCreated] [datetime] NOT NULL,
	[DateUpdated] [datetime] NOT NULL,
	[ParticipantCount] [int] NOT NULL,
	[MessageCount] [int] NOT NULL,
 CONSTRAINT [PK_cs_Messaging_Conversations] PRIMARY KEY CLUSTERED 
(
	[ConversationId] ASC
)) ON [PRIMARY]'

-- Create cs_Messaging_ConversationMessages table
EXECUTE sp_executesql N'
CREATE TABLE [dbo].[cs_Messaging_ConversationMessages](
	[ConversationId] [uniqueidentifier] NOT NULL,
	[MessageId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]'

-- Create foreign keys for cs_Messaging_ConversationMessages table
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ConversationMessages]  WITH CHECK ADD  CONSTRAINT [FK_cs_Messaging_ConversationMessages_cs_Messaging_Conversations] FOREIGN KEY([ConversationId]) REFERENCES [dbo].[cs_Messaging_Conversations] ([ConversationId])'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ConversationMessages] CHECK CONSTRAINT [FK_cs_Messaging_ConversationMessages_cs_Messaging_Conversations]'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ConversationMessages]  WITH CHECK ADD  CONSTRAINT [FK_cs_Messaging_ConversationMessages_cs_Messaging_Messages] FOREIGN KEY([MessageId]) REFERENCES [dbo].[cs_Messaging_Messages] ([MessageId])'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ConversationMessages] CHECK CONSTRAINT [FK_cs_Messaging_ConversationMessages_cs_Messaging_Messages]'

-- Create indexes for cs_Messaging_ConversationMessages table
EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_Messaging_ConversationMessages_ConversationId] ON [dbo].[cs_Messaging_ConversationMessages] ([ConversationId]) WITH FILLFACTOR = 90 ON [PRIMARY]'
EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_Messaging_ConversationMessages_MessageId] ON [dbo].[cs_Messaging_ConversationMessages] ([MessageId]) WITH FILLFACTOR = 90 ON [PRIMARY]'

-- Create cs_Messaging_ConversationParticipants table
EXECUTE sp_executesql N'
CREATE TABLE [dbo].[cs_Messaging_ConversationParticipants](
	[ConversationId] [uniqueidentifier] NOT NULL,
	[ParticipantId] [int] NOT NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_cs_Messaging_ConversationParticipants_Active]  DEFAULT ((1))
) ON [PRIMARY]'

-- Create foreign keys for cs_Messaging_ConversationParticipants table
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ConversationParticipants]  WITH CHECK ADD  CONSTRAINT [FK_cs_Messaging_ConversationParticipants_cs_Messaging_Conversations] FOREIGN KEY([ConversationId]) REFERENCES [dbo].[cs_Messaging_Conversations] ([ConversationId])'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ConversationParticipants] CHECK CONSTRAINT [FK_cs_Messaging_ConversationParticipants_cs_Messaging_Conversations]'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ConversationParticipants]  WITH CHECK ADD  CONSTRAINT [FK_cs_Messaging_ConversationParticipants_cs_Users] FOREIGN KEY([ParticipantId]) REFERENCES [dbo].[cs_Users] ([UserID])'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ConversationParticipants] CHECK CONSTRAINT [FK_cs_Messaging_ConversationParticipants_cs_Users]'

-- Create indexes for cs_Messaging_ConversationParticipants table
EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_Messaging_ConversationParticipants_ConversationId] ON [dbo].[cs_Messaging_ConversationParticipants] ([ConversationId]) WITH FILLFACTOR = 90 ON [PRIMARY]'
EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_Messaging_ConversationParticipants_ParticipantId] ON [dbo].[cs_Messaging_ConversationParticipants] ([ParticipantId]) WITH FILLFACTOR = 90 ON [PRIMARY]'

-- Create cs_Messaging_ProfileMessages table
EXECUTE sp_executesql N'
CREATE TABLE [dbo].[cs_Messaging_ProfileMessages](
	[UserId] [int] NOT NULL,
	[MessageId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]'

-- Create foreign keys for cs_Messaging_ProfileMessages table
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ProfileMessages]  WITH CHECK ADD  CONSTRAINT [FK_cs_Messaging_ProfileMessages_cs_Messaging_Messages] FOREIGN KEY([MessageId]) REFERENCES [dbo].[cs_Messaging_Messages] ([MessageId])'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ProfileMessages] CHECK CONSTRAINT [FK_cs_Messaging_ProfileMessages_cs_Messaging_Messages]'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ProfileMessages]  WITH CHECK ADD  CONSTRAINT [FK_cs_Messaging_ProfileMessages_cs_Users] FOREIGN KEY([UserId]) REFERENCES [dbo].[cs_Users] ([UserID])'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ProfileMessages] CHECK CONSTRAINT [FK_cs_Messaging_ProfileMessages_cs_Users]'

-- Create indexes for cs_Messaging_ProfileMessages table
EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_Messaging_ProfileMessages_MessageId] ON [dbo].[cs_Messaging_ProfileMessages] ([MessageId]) WITH FILLFACTOR = 90 ON [PRIMARY]'
EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_Messaging_ProfileMessages_UserId] ON [dbo].[cs_Messaging_ProfileMessages] ([UserId]) WITH FILLFACTOR = 90 ON [PRIMARY]'

-- Create cs_Messaging_GroupMessages table
EXECUTE sp_executesql N'
CREATE TABLE [dbo].[cs_Messaging_GroupMessages](
	[GroupId] [int] NOT NULL,
	[MessageId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]'

-- Create foreign keys for cs_Messaging_GroupMessages table
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_GroupMessages]  WITH CHECK ADD  CONSTRAINT [FK_cs_Messaging_GroupMessages_cs_Messaging_Messages] FOREIGN KEY([MessageId]) REFERENCES [dbo].[cs_Messaging_Messages] ([MessageId])'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_GroupMessages] CHECK CONSTRAINT [FK_cs_Messaging_GroupMessages_cs_Messaging_Messages]'

-- Create indexes for cs_Messaging_GroupMessages table
EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_Messaging_GroupMessages_GroupId] ON [dbo].[cs_Messaging_GroupMessages] ([GroupId]) WITH FILLFACTOR = 90 ON [PRIMARY]'
EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_Messaging_GroupMessages_MessageId] ON [dbo].[cs_Messaging_GroupMessages] ([MessageId]) WITH FILLFACTOR = 90 ON [PRIMARY]'

-- Create cs_Messaging_ActivityMessages table
EXECUTE sp_executesql N'
CREATE TABLE [dbo].[cs_Messaging_ActivityMessages](
	[MessageId] [uniqueidentifier] NOT NULL,
	[UserId] [int] NOT NULL,
	[GroupId] [int] NULL,
    [Type] [nvarchar](50) NOT NULL
) ON [PRIMARY]'

-- Create foreign keys for cs_Messaging_ActivityMessages table
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ActivityMessages]  WITH CHECK ADD  CONSTRAINT [FK_cs_Messaging_ActivityMessages_cs_Messaging_Messages] FOREIGN KEY([MessageId]) REFERENCES [dbo].[cs_Messaging_Messages] ([MessageId])'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ActivityMessages] CHECK CONSTRAINT [FK_cs_Messaging_ActivityMessages_cs_Messaging_Messages]'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ActivityMessages]  WITH CHECK ADD  CONSTRAINT [FK_cs_Messaging_ActivityMessages_cs_Users] FOREIGN KEY([UserId]) REFERENCES [dbo].[cs_Users] ([UserID])'
EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Messaging_ActivityMessages] CHECK CONSTRAINT [FK_cs_Messaging_ActivityMessages_cs_Users]'

-- Create indexes for cs_Messaging_ActivityMessages table
EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_Messaging_ActivityMessages_MessageId] ON [dbo].[cs_Messaging_ActivityMessages] ([MessageId]) WITH FILLFACTOR = 90 ON [PRIMARY]'
EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_Messaging_ActivityMessages_UserId] ON [dbo].[cs_Messaging_ActivityMessages] ([UserId]) WITH FILLFACTOR = 90 ON [PRIMARY]'
EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_Messaging_ActivityMessages_GroupId] ON [dbo].[cs_Messaging_ActivityMessages] ([GroupId]) WITH FILLFACTOR = 90 ON [PRIMARY]'
EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_Messaging_ActivityMessages_Type] ON [dbo].[cs_Messaging_ActivityMessages] ([Type]) WITH FILLFACTOR = 90 ON [PRIMARY]'

--## END Schema Patch ##
Insert into cs_SchemaVersion(Major, Minor, Patch, InstallDate) values (@Major, @Minor, @Patch, GetDate())

Print 'Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ' was applied successfully '

	END
ELSE IF(@Installed is not null)
	BEGIN
Print 'Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ' was already applied on ' + Convert(varchar(50), @Installed)  
	END 
ELSE
	BEGIN
Print 'The patch could not be applied because your current schema is missing previous updates (Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ')' 
	END 
