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

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 * from dbo.sysobjects where id = object_id(N'[dbo].[cs_User_ApiKeys]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
	if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_ApiKeys]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
	EXECUTE sp_Rename N'cs_User_ApiKeys', 'cs_ApiKeys'
	EXECUTE sp_Rename N'PK_cs_User_ApiKeys', 'PK_cs_ApiKeys'

	IF  EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[cs_ApiKeys]') AND name = N'IX_cs_User_ApiKeys_Value')
	BEGIN
		EXECUTE sp_executesql N'DROP INDEX [IX_cs_User_ApiKeys_Value] ON [dbo].[cs_ApiKeys]'
		IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[cs_ApiKeys]') AND name = N'IX_cs_ApiKeys_Value')
			EXECUTE sp_executesql N'CREATE UNIQUE NONCLUSTERED INDEX [IX_cs_ApiKeys_Value] ON [dbo].[cs_ApiKeys] ([Value])'
	END
	IF  EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[cs_ApiKeys]') AND name = N'IX_cs_User_ApiKeys_UserID')
	BEGIN
		EXECUTE sp_executesql N'DROP INDEX [IX_cs_User_ApiKeys_UserID] ON [dbo].[cs_ApiKeys]'
		IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[cs_ApiKeys]') AND name = N'IX_cs_ApiKeys_UserID')
			EXECUTE sp_executesql N'CREATE NONCLUSTERED INDEX [IX_cs_ApiKeys_UserID] ON [dbo].[cs_ApiKeys] ([UserID]) WITH FILLFACTOR = 90 ON [PRIMARY]'
	END
END
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 
