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 = 0;
Set @Patch = 9;

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

/* Manage updates to cs_Friends_xref */
IF EXISTS (SELECT 1 FROM dbo.sysindexes WHERE [id] = OBJECT_ID(N'[cs_Friends_xref]') AND [name] = N'IX_cs_Friends_xref_Friends_xrefId')
	exec sp_executesql N'DROP INDEX [cs_Friends_xref].[IX_cs_Friends_xref_Friends_xrefId]'

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'cs_Friends_xref' AND COLUMN_NAME = N'Friends_xrefId')
	exec sp_executesql N'alter table [cs_Friends_xref] drop column [Friends_xrefId]'

/* this is a temporary identity */
exec sp_executesql N'alter table [cs_Friends_xref] add [Friends_xrefId] int identity(1, 1) not null'

exec sp_executesql N'CREATE INDEX [IX_cs_Friends_xref_Friends_xrefId] ON [cs_Friends_xref]
(
	[Friends_xrefId]
)
ON [PRIMARY]'

set nocount on
exec sp_executesql N'delete from [cs_Friends_xref]
where exists (
	select 1 from [cs_Friends_xref] b
	where
		b.[UserID] = [cs_Friends_xref].[UserID]
		and b.[FriendUserID] = [cs_Friends_xref].[FriendUserID]
	group by
		b.[UserID], b.[FriendUserID]
	having
		min(b.[Friends_xrefId]) < [cs_Friends_xref].[Friends_xrefId]
)'
set nocount off

IF EXISTS (SELECT 1 FROM dbo.sysindexes WHERE [id] = OBJECT_ID(N'[cs_Friends_xref]') AND [name] = N'IX_cs_Friends_xref_Friends_xrefId')
	exec sp_executesql N'DROP INDEX [cs_Friends_xref].[IX_cs_Friends_xref_Friends_xrefId]'

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'cs_Friends_xref' AND COLUMN_NAME = N'Friends_xrefId')
	exec sp_executesql N'alter table [cs_Friends_xref] drop column [Friends_xrefId]'

IF EXISTS (SELECT 1 FROM dbo.sysindexes WHERE [id] = OBJECT_ID(N'[cs_Friends_xref]') AND [name] = N'IXUC_cs_Friends_xref_UserID_FriendUserID')
	exec sp_executesql N'DROP INDEX [cs_Friends_xref].[IXUC_cs_Friends_xref_UserID_FriendUserID]'

exec sp_executesql N'CREATE UNIQUE CLUSTERED INDEX [IXUC_cs_Friends_xref_UserID_FriendUserID] ON [cs_Friends_xref]
(
	[UserID]
	, [FriendUserID]
) WITH FILLFACTOR = 90
ON [PRIMARY]'

IF EXISTS (SELECT 1 FROM dbo.sysindexes WHERE [id] = OBJECT_ID(N'[cs_Friends_xref]') AND [name] = N'IX_cs_Friends_xref_FriendUserID')
	exec sp_executesql N'DROP INDEX [cs_Friends_xref].[IX_cs_Friends_xref_FriendUserID]'

exec sp_executesql N'CREATE INDEX [IX_cs_Friends_xref_FriendUserID] ON [cs_Friends_xref]
(
	[FriendUserID]
) WITH FILLFACTOR = 90
ON [PRIMARY]'

exec sp_executesql N'dbcc dbreindex(''cs_Friends_xref'')'


/* Now manage updates to cs_SearchBarrel */
if exists (select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
	where tc.TABLE_NAME = N'cs_SearchBarrel' and tc.CONSTRAINT_NAME = 'IX_cs_SearchBarrel')
	exec sp_executesql N'ALTER TABLE [cs_SearchBarrel] DROP CONSTRAINT [IX_cs_SearchBarrel]'

exec sp_executesql N'ALTER TABLE [cs_SearchBarrel] ADD CONSTRAINT [IX_cs_SearchBarrel] UNIQUE NONCLUSTERED
(
	[WordHash] ASC,
	[PostID] ASC
) WITH FILLFACTOR = 90
ON [PRIMARY]'

IF EXISTS (SELECT 1 FROM dbo.sysindexes WHERE [id] = OBJECT_ID(N'[cs_SearchBarrel]') AND [name] = N'WordHash_SectionID_CLX_IDX')
	exec sp_executesql N'DROP INDEX [cs_SearchBarrel].[WordHash_SectionID_CLX_IDX]'

exec sp_executesql N'CREATE CLUSTERED INDEX [WordHash_SectionID_CLX_IDX] ON [cs_SearchBarrel] 
(
	[WordHash] ASC,
	[SectionID] ASC
) WITH FILLFACTOR = 90 ON [PRIMARY]'

exec sp_executesql N'dbcc dbreindex(''cs_SearchBarrel'')'



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

