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

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

-- BEGIN cs_Users
EXECUTE sp_executesql
N'ALTER TABLE [dbo].[cs_Users] ADD
	[UserName] [nvarchar] (256) NOT NULL CONSTRAINT [DF_cs_Users_UserName] DEFAULT '''',
	[Email] [nvarchar] (256) NOT NULL CONSTRAINT [DF_cs_Users_Email] DEFAULT '''',
	[IsApproved] [bit] NULL, 
	[IsAnonymous] [bit] NOT NULL CONSTRAINT [DF_cs_Users_IsAnonymous] DEFAULT 0,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_cs_Users_CreateDate] DEFAULT (getdate())'

EXECUTE sp_executesql
N'UPDATE	CU
SET
	CU.[UserName] = AU.[UserName],
	CU.[Email] = ISNULL(AM.[Email], ''''),
	CU.[IsApproved] = AM.[IsApproved],
	CU.[IsAnonymous] = AU.[IsAnonymous],
	CU.[CreateDate] = AM.[CreateDate]
FROM
	[dbo].[cs_Users] CU INNER JOIN [dbo].[aspnet_Users] AU ON CU.[MembershipID] = AU.[UserId]
	INNER JOIN [dbo].[aspnet_Membership] AM ON CU.[MembershipID] = AM.[UserId]'

EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Users] DROP CONSTRAINT [DF_cs_Users_UserName]'

EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Users] DROP CONSTRAINT [DF_cs_Users_Email]'

EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Users] DROP CONSTRAINT [DF_cs_Users_IsAnonymous]'

EXECUTE sp_executesql N'ALTER TABLE [dbo].[cs_Users] DROP CONSTRAINT [DF_cs_Users_CreateDate]'

DECLARE	@ConstraintName nvarchar(255)
SELECT	@ConstraintName = CONSTRAINT_NAME
FROM	INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE	TABLE_NAME = 'cs_Users' AND CONSTRAINT_TYPE = 'FOREIGN KEY'

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME = @ConstraintName AND TABLE_NAME = 'cs_Users' AND COLUMN_NAME = 'MembershipID')
BEGIN
	DECLARE	@sql nvarchar(4000)
	SET		@sql = 'ALTER TABLE [dbo].[cs_Users] DROP CONSTRAINT [' + @ConstraintName + ']'
	EXECUTE sp_executesql @sql
END
-- END cs_Users

EXECUTE sp_executesql N'ALTER TABLE [cs_ProductPermissions] DROP [FK_cs_ProductPermissions_RoleID]'

EXECUTE sp_executesql N'ALTER TABLE [cs_SectionPermissions] DROP [FK_cs_SectionPermissions_RoleID]'

EXECUTE sp_executesql N'ALTER TABLE [cs_RoleQuotas] DROP [FK_cs_GalleryRoleQuotas_aspnet_Roles]'

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

