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 = 31;

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

IF NOT EXISTS (
		select 1 from INFORMATION_SCHEMA.TABLES t
		where t.TABLE_SCHEMA = N'dbo' AND t.TABLE_NAME = N'cs_SpamPosts'
	)
	exec sp_executesql N'CREATE TABLE [dbo].[cs_SpamPosts]
(
	SpamPostID bigint identity(1, 1) not null,
	SourceProcedure nvarchar(128) not null,
	SectionID int not null,
	ParentID int not null,
	AllowDuplicatePosts bit,
	DuplicateIntervalInMinutes int,
	Subject nvarchar(256),
	UserID int not null,
	PostAuthor nvarchar(64),
	Body ntext,
	FormattedBody ntext,
	EmoticonID int,
	IsLocked bit,
	IsSticky bit,
	IsApproved bit,
	StickyDate datetime,
	PostType int,
	PostMedia int,
	PostDate datetime not null, /* ensuring that this is never null */
	UserHostAddress nvarchar(32),
	PropertyNames ntext,
	PropertyValues ntext,
	SettingsID int not null,
	IsTracked bit,
	--PostID int not null, /* leaving this one out on purpose */
	ThreadID int, /* this could possibly be null if a thread starter is ever marked as definite spam */
	PostConfiguration int,
	UpdateSectionStatistics bit,
	UserTime datetime not null, /* if for some reason this value comes in a NULL, I will use PostDate and then finally GETDATE() */
	ApplicationPostType int,
	PostName nvarchar(256),
	ThreadStatus int,
	Points int,
	PostDisplayName nvarchar(64),
	AutoApprove bit,

	/* from cs_weblog_Post_Create */
	TitleUrl nvarchar(256),
	PostConfig int,
	BlogPostType tinyint,
	Categories nvarchar(4000),
	SpamScore int,
	PostStatus int
)'

IF NOT EXISTS (
		select 1 from INFORMATION_SCHEMA.TABLES t
		where t.TABLE_SCHEMA = N'dbo' AND t.TABLE_NAME = N'cs_SpamPosts'
	)
	exec sp_executesql N'ALTER TABLE [dbo].[cs_SpamPosts] WITH NOCHECK ADD 
	CONSTRAINT [PK_cs_SpamPosts] PRIMARY KEY CLUSTERED
	(
		[SpamPostID]
	) ON [PRIMARY]'

IF indexproperty( object_id(N'[dbo].[cs_SpamPosts]'), N'IX_cs_SpamPosts_SettingsID_SectionID', N'IndexID') IS NULL
	exec sp_executesql N'CREATE INDEX [IX_cs_SpamPosts_SettingsID_SectionID] ON [dbo].[cs_SpamPosts]
	(
		[SettingsID]
		, [SectionID]
		, [ThreadID]
	) 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 
