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

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 EXISTS (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'cs_RollerBlogPost' and convert(varbinary(12), COLUMN_NAME) = 0x550072006C0049006400)
begin
	print N'Renaming cs_RollerBlogPost.UrlId to UrlID'
	exec sp_rename N'cs_RollerBlogPost.UrlId', N'UrlID_TEMP'
	exec sp_rename N'cs_RollerBlogPost.UrlID_TEMP', N'UrlID'
end


IF EXISTS (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'cs_UrlRedirects' and convert(varbinary(10), COLUMN_NAME) = 0x750072006C0049004400)
begin
	print N'Renaming cs_UrlRedirects.urlID to UrlID'
	exec sp_rename N'cs_UrlRedirects.urlID', N'UrlID_TEMP'
	exec sp_rename N'cs_UrlRedirects.UrlID_TEMP', N'UrlID'
end

IF EXISTS (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'cs_UrlRedirects' and convert(varbinary(6), COLUMN_NAME) = 0x750072006C00)
begin
	print N'Renaming cs_UrlRedirects.url to Url'
	exec sp_rename N'cs_UrlRedirects.url', N'Url_TEMP'
	exec sp_rename N'cs_UrlRedirects.Url_TEMP', N'Url'
end

IF EXISTS (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'cs_UrlRedirects' and convert(varbinary(22), COLUMN_NAME) = 0x69006D007000720065007300730069006F006E007300)
begin
	print N'Renaming cs_UrlRedirects.impressions to Impressions'
	exec sp_rename N'cs_UrlRedirects.impressions', N'Impressions_TEMP'
	exec sp_rename N'cs_UrlRedirects.Impressions_TEMP', N'Impressions'
end

IF EXISTS (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'cs_UrlRedirects' and convert(varbinary(22), COLUMN_NAME) = 0x6400650073006300720069007000740069006F006E00)
begin
	print N'Renaming cs_UrlRedirects.description to Description'
	exec sp_rename N'cs_UrlRedirects.description', N'Description_TEMP'
	exec sp_rename N'cs_UrlRedirects.Description_TEMP', N'Description'
end


IF EXISTS (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'cs_UserReadPost' and convert(varbinary(12), COLUMN_NAME) = 0x550073006500720049006400)
begin
	print N'Renaming cs_UserReadPost.UserId to UserID'
	exec sp_rename N'cs_UserReadPost.UserId', N'UserID_TEMP'
	exec sp_rename N'cs_UserReadPost.UserID_TEMP', N'UserID'
end


IF EXISTS (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'cs_Users_OpenID' and convert(varbinary(12), COLUMN_NAME) = 0x550073006500720049006400)
begin
	print N'Renaming cs_Users_OpenID.UserId to UserID'
	exec sp_rename N'cs_Users_OpenID.UserId', N'UserID_TEMP'
	exec sp_rename N'cs_Users_OpenID.UserID_TEMP', N'UserID'
end


IF EXISTS (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'cs_Thread_Daily_Stats' and convert(varbinary(18), COLUMN_NAME) = 0x530065006300740069006F006E0049006400)
begin
	print N'Renaming cs_Thread_Daily_Stats.SectionId to SectionID'
	exec sp_rename N'cs_Thread_Daily_Stats.SectionId', N'SectionID_TEMP'
	exec sp_rename N'cs_Thread_Daily_Stats.SectionID_TEMP', N'SectionID'
end

IF EXISTS (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'cs_Thread_Daily_Stats' and convert(varbinary(16), COLUMN_NAME) = 0x54006800720065006100640049006400)
begin
	print N'Renaming cs_Thread_Daily_Stats.ThreadId to ThreadID'
	exec sp_rename N'cs_Thread_Daily_Stats.ThreadId', N'ThreadID_TEMP'
	exec sp_rename N'cs_Thread_Daily_Stats.ThreadID_TEMP', N'ThreadID'
end


IF EXISTS (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'cs_AddonConfigurationData' and convert(varbinary(14), COLUMN_NAME) = 0x4100640064006F006E0049006400)
begin
	print N'Renaming cs_AddonConfigurationData.AddonId to AddonID'
	exec sp_rename N'cs_AddonConfigurationData.AddonId', N'AddonID_TEMP'
	exec sp_rename N'cs_AddonConfigurationData.AddonID_TEMP', N'AddonID'
end


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