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

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

-- *****************************************
--
-- OTHER PARTS OF THIS PATCH MOVED TO SCHEMA PATCH 3.0.19
--
-- *****************************************

-- for cs_Thread_Daily_Stats
-- drop cs_Thread_Daily_Stats PK constraint
IF EXISTS (select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = N'cs_Thread_Daily_Stats'
	and CONSTRAINT_NAME = N'PK_cs_Thread_Daily_Stats' and CONSTRAINT_TYPE = N'PRIMARY KEY')
	exec sp_executesql N'ALTER TABLE [dbo].[cs_Thread_Daily_Stats] DROP CONSTRAINT [PK_cs_Thread_Daily_Stats]'

-- add cs_Thread_Daily_Stats PK constraint with new fill factor
exec sp_executesql N'
ALTER TABLE [dbo].[cs_Thread_Daily_Stats] ADD CONSTRAINT [PK_cs_Thread_Daily_Stats] PRIMARY KEY CLUSTERED
(
	[SectionID] ASC
	, [ThreadID] ASC
	, [Day] DESC
) 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 
