SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

Set @Major = 4;
Set @Minor = 1;
Set @Patch = 0;

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 ##


EXECUTE sp_executesql N'
CREATE TABLE [dbo].[cs_Wiki_Wikis]
(
	[WikiId] [int] IDENTITY (1, 1) NOT NULL,
	[HubId] [int] NULL,
	[Name] [nvarchar] (256) NOT NULL CONSTRAINT [DF_cs_Wiki_Wikis_Name] DEFAULT (''''),
	[Description] [nvarchar] (1000) NOT NULL CONSTRAINT [DF_cs_Wiki_Wikis_Description] DEFAULT (''''),
	[ApplicationKey] [nvarchar] (256) NOT NULL CONSTRAINT [DF_cs_Wiki_Wikis_ApplicationKey] DEFAULT (''''),
	[IsEnabled] [bit] NOT NULL CONSTRAINT [DF_cs_Wiki_Wikis_IsEnabled] DEFAULT (1),
	CONSTRAINT [PK_cs_Wiki_Wikis] PRIMARY KEY CLUSTERED ([WikiId]) ON [PRIMARY]
)
ON [PRIMARY]'


EXECUTE sp_executesql N'
CREATE TABLE [dbo].[cs_Wiki_Pages]
(
	[PageId] [int] IDENTITY (1, 1) NOT NULL,
	[WikiId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
	[ParentPageId] [int] NULL,
	[Title] [nvarchar] (256) NOT NULL CONSTRAINT [DF_cs_Wiki_Pages_Title] DEFAULT (''''),
	[Body] [ntext] NOT NULL CONSTRAINT [DF_cs_Wiki_Pages_Body] DEFAULT (''''),
	[FormattedBody] [ntext] NOT NULL CONSTRAINT [DF_cs_Wiki_Pages_FormattedBody] DEFAULT (''''),
	[IsPublished] [bit] NOT NULL CONSTRAINT [DF_cs_Wiki_Pages_IsPublished] DEFAULT (1),
	[RatingCount] [int] NOT NULL CONSTRAINT [DF_cs_Wiki_Pages_RatingCount] DEFAULT (0),
	[RatingSum] [int] NOT NULL CONSTRAINT [DF_cs_Wiki_Pages_RatingSum] DEFAULT (0),
	[LastModifiedUtcDate] [datetime] NOT NULL CONSTRAINT [DF_cs_Wiki_Pages_LastModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_cs_Wiki_Pages] PRIMARY KEY CLUSTERED ([PageId]) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'

EXECUTE sp_executesql N'
ALTER TABLE [dbo].[cs_Wiki_Pages] ADD
	CONSTRAINT [FK_cs_Wiki_Pages_cs_Users] FOREIGN KEY ([UserId]) REFERENCES [dbo].[cs_Users] ([UserID]),
	CONSTRAINT [FK_cs_Wiki_Pages_cs_Wiki_Wikis] FOREIGN KEY ([WikiId]) REFERENCES [dbo].[cs_Wiki_Wikis] ([WikiId]) ON DELETE CASCADE,
	CONSTRAINT [FK_cs_Wiki_Pages_cs_Wiki_Pages] FOREIGN KEY ([ParentPageId]) REFERENCES [dbo].[cs_Wiki_Pages] ([PageId])'


EXECUTE sp_executesql N'
CREATE TABLE [dbo].[cs_Wiki_PageRatings]
(
	[PageId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
	[Rating] [int] NOT NULL CONSTRAINT [DF_cs_Wiki_PageRatings_Rating] DEFAULT (3),
	CONSTRAINT [PK_cs_Wiki_PageRatings] PRIMARY KEY CLUSTERED ([PageId], [UserId]) WITH FILLFACTOR = 90 ON [PRIMARY]
)
ON [PRIMARY]'

EXECUTE sp_executesql N'
ALTER TABLE [dbo].[cs_Wiki_PageRatings] ADD
	CONSTRAINT [FK_cs_Wiki_PageRatings_cs_Users] FOREIGN KEY ([UserId]) REFERENCES [dbo].[cs_Users] ([UserID]),
	CONSTRAINT [FK_cs_Wiki_PageRatings_cs_Wiki_Pages] FOREIGN KEY ([PageId]) REFERENCES [dbo].[cs_Wiki_Pages] ([PageId]) ON DELETE CASCADE'


EXECUTE sp_executesql N'
CREATE TABLE [dbo].[cs_Wiki_PageComments]
(
	[PageCommentId] [int] IDENTITY (1, 1) NOT NULL,
	[PageId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
	[Body] [ntext] NOT NULL CONSTRAINT [DF_cs_Wiki_PageComments_Body] DEFAULT (''''),
	[FormattedBody] [ntext] NOT NULL CONSTRAINT [DF_cs_Wiki_PageComments_FormattedBody] DEFAULT (''''),
	[RatingCount] [int] NOT NULL CONSTRAINT [DF_cs_Wiki_PageComments_RatingCount] DEFAULT (0),
	[RatingSum] [int] NOT NULL CONSTRAINT [DF_cs_Wiki_PageComments_RatingSum] DEFAULT (0),
	[PublishedUtcDate] [datetime] NOT NULL CONSTRAINT [DF_cs_Wiki_PageComments_PublishedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_cs_Wiki_PageComments] PRIMARY KEY CLUSTERED ([PageCommentId]) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'

EXECUTE sp_executesql N'
ALTER TABLE [dbo].[cs_Wiki_PageComments] ADD
	CONSTRAINT [FK_cs_Wiki_PageComments_cs_Users] FOREIGN KEY ([UserId]) REFERENCES [dbo].[cs_Users] ([UserID]),
	CONSTRAINT [FK_cs_Wiki_PageComments_cs_Wiki_Pages] FOREIGN KEY ([PageId]) REFERENCES [dbo].[cs_Wiki_Pages] ([PageId]) ON DELETE CASCADE'


EXECUTE sp_executesql N'
CREATE TABLE [dbo].[cs_Wiki_PageCommentRatings]
(
	[PageCommentId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
	[Rating] [int] NOT NULL CONSTRAINT [DF_cs_Wiki_PageCommentRatings_Rating] DEFAULT (3),
	CONSTRAINT [PK_cs_Wiki_PageCommentRatings] PRIMARY KEY CLUSTERED ([PageCommentId], [UserId]) WITH FILLFACTOR = 90 ON [PRIMARY]
)
ON [PRIMARY]'

EXECUTE sp_executesql N'
ALTER TABLE [dbo].[cs_Wiki_PageCommentRatings] ADD
	CONSTRAINT [FK_cs_Wiki_PageCommentRatings_cs_Users] FOREIGN KEY ([UserId]) REFERENCES [dbo].[cs_Users] ([UserID]),
	CONSTRAINT [FK_cs_Wiki_PageCommentRatings_cs_Wiki_PageComments] FOREIGN KEY ([PageCommentId]) REFERENCES [dbo].[cs_Wiki_PageComments] ([PageCommentId]) ON DELETE CASCADE'


EXECUTE sp_executesql N'
CREATE TABLE [dbo].[cs_Wiki_PageRevisions]
(
	[PageRevisionId] [int] IDENTITY (1, 1) NOT NULL,
	[PageId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
	[Title] [nvarchar] (256) NOT NULL CONSTRAINT [DF_cs_Wiki_PageRevisions_Title] DEFAULT (''''),
	[Body] [ntext] NOT NULL CONSTRAINT [DF_cs_Wiki_PageRevisions_Body] DEFAULT (''''),
	[FormattedBody] [ntext] NOT NULL CONSTRAINT [DF_cs_Wiki_PageRevisions_FormattedBody] DEFAULT (''''),
	[LastModifiedUtcDate] [datetime] NOT NULL CONSTRAINT [DF_cs_Wiki_PageRevisions_LastModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_cs_Wiki_PageRevisions] PRIMARY KEY CLUSTERED ([PageRevisionId]) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'

EXECUTE sp_executesql N'
ALTER TABLE [dbo].[cs_Wiki_PageRevisions] ADD
	CONSTRAINT [FK_cs_Wiki_PageRevisions_cs_Users] FOREIGN KEY ([UserId]) REFERENCES [dbo].[cs_Users] ([UserID]),
	CONSTRAINT [FK_cs_Wiki_PageRevisions_cs_Wiki_Pages] FOREIGN KEY ([PageId]) REFERENCES [dbo].[cs_Wiki_Pages] ([PageId]) ON DELETE CASCADE'


--## 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 
