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

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 not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_FriendRequests]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	exec sp_executesql N'CREATE TABLE [dbo].[cs_FriendRequests](
	[FriendshipRequestID] [int] IDENTITY(1,1) NOT NULL,
	[RequestorUserID] [int] NOT NULL,
	[RequesteeUserID] [int] NOT NULL,
	[Message] [varchar](2000) NULL,
	[FriendshipState] [tinyint] NOT NULL,
	[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_cs_FriendRequests_CreatedDate]  DEFAULT (getdate()),
	[LastModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_cs_FriendRequests_LastModifiedDate]  DEFAULT (getdate()),
PRIMARY KEY CLUSTERED 
(
	[FriendshipRequestID] ASC
)WITH FILLFACTOR = 90
)'
	
end

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Friends_xref]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	exec sp_executesql N'CREATE TABLE [dbo].[cs_Friends_xref](
	[UserID] [int] NOT NULL,
	[FriendUserID] [int] NOT NULL)'
exec sp_executesql N'ALTER TABLE [dbo].[cs_Friends_xref]  
	WITH CHECK ADD  CONSTRAINT [FK_cs_Friends_xref_cs_Users] 
	FOREIGN KEY([UserID])
	REFERENCES [dbo].[cs_Users] ([UserID])'
exec sp_executesql N'ALTER TABLE [dbo].[cs_Friends_xref] 
	CHECK CONSTRAINT [FK_cs_Friends_xref_cs_Users]'

	
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 
