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

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_FavoriteUsers]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN

	EXECUTE sp_executesql N'
	INSERT INTO cs_FriendRequests
		([RequestorUserID], [RequesteeUserID], [Message], [FriendshipState], [CreatedDate], [LastModifiedDate])
	SELECT U.UserID, FU.UserID, ''A friendship request has been initiated.  Please either accept or reject this request.'' as [Message], 2 as [FriendshipState], getdate() as [CreatedDate], getdate() as [LastModifiedDate]
		FROM cs_Users U
		INNER JOIN cs_FavoriteUsers FU ON FU.OwnerID = U.UserID AND FU.UserID IN 
			(SELECT OwnerID FROM cs_FavoriteUsers FUs WHERE FUs.UserID = U.UserID)
	WHERE NOT EXISTS (SELECT * FROM cs_FriendRequests FR WHERE FR.RequestorUserID = U.UserID AND FR.RequesteeUserID = FU.UserID)'

	EXECUTE sp_executesql N'
	DELETE FROM cs_FriendRequests WHERE FriendshipRequestID in
	(SELECT FR.FriendshipRequestID FROM cs_FriendRequests FR
		INNER JOIN cs_FriendRequests FRS ON FR.[RequesteeUserID] = FRS.[RequestorUserID] AND FRS.[RequesteeUserID] = FR.[RequestorUserID] AND FR.[RequesteeUserID] < FRS.[RequesteeUserID]
	 WHERE FR.FriendshipState = 2 AND FR.[RequesteeUserID] <> FR.[RequestorUserID])'

	EXECUTE sp_executesql N'
	INSERT INTO cs_Friends_xref (UserID, FriendUserID)
	SELECT FR.RequestorUserID, FR.RequesteeUserID
		FROM cs_FriendRequests FR
		WHERE FR.FriendshipState = 2 AND NOT EXISTS (Select * FROM cs_Friends_xref F WHERE F.UserID = FR.RequestorUserID AND F.FriendUserID = FR.RequesteeUserID)'

	EXECUTE sp_executesql N'
	INSERT INTO cs_Friends_xref (UserID, FriendUserID)
	SELECT FR.RequesteeUserID, FR.RequestorUserID
		FROM cs_FriendRequests FR
		WHERE FR.FriendshipState = 2 AND NOT EXISTS (Select * FROM cs_Friends_xref F WHERE F.FriendUserID = FR.RequestorUserID AND F.UserID = FR.RequesteeUserID)'

	EXECUTE sp_executesql N'
	INSERT INTO cs_FriendRequests
		([RequestorUserID], [RequesteeUserID], [Message], [FriendshipState], [CreatedDate], [LastModifiedDate])
	SELECT U.UserID, FU.UserID, ''A friendship request has been initiated.  Please either accept or reject this request.'' as [Message], 1 as [FriendshipState], getdate() as [CreatedDate], getdate() as [LastModifiedDate]
		FROM cs_Users U
		INNER JOIN cs_FavoriteUsers FU ON FU.OwnerID = U.UserID AND FU.UserID NOT IN 
			(SELECT OwnerID FROM cs_FavoriteUsers FUs WHERE FUs.UserID = U.UserID)
	WHERE NOT EXISTS (SELECT * FROM cs_FriendRequests FR WHERE FR.RequestorUserID = U.UserID AND FR.RequesteeUserID = FU.UserID)'


	EXECUTE sp_executesql N'
	INSERT INTO mg_EmailQueue (SettingsID, EmailXml) 
	SELECT S.SettingsID, 
		N''<?xml version="1.0" encoding="utf-16"?><MailMessage><Revision>0</Revision><Headers /><DeliveryNotificationOptions>None</DeliveryNotificationOptions><Priority>Normal</Priority><From><MailAddress><Telligent.MailGateway.Common.Components.MailAddressXml><Revision>0</Revision><Name>''
		+ S.[SiteName]
		+ N'' - Automated Email</Name><Address>info@notset.com</Address></Telligent.MailGateway.Common.Components.MailAddressXml></MailAddress></From><To><MailAddress><Revision>0</Revision><Name /><Address>''
		+ U.Email
		+ N''</Address></MailAddress></To><Cc /><Bcc /><Sender><MailAddress><Telligent.MailGateway.Common.Components.MailAddressXml /></MailAddress></Sender><ReplyTo><MailAddress><Telligent.MailGateway.Common.Components.MailAddressXml /></MailAddress></ReplyTo><Subject>New Friend Request(s) on ''
		+ S.[SiteName]
		+ N''</Subject><SubjectEncoding /><Body>You have friendships that are awaiting confirmation at &lt;a href=''
		+ S.[SiteUrl]
		+ N''/&gt;''
		+ S.[SiteName]
		+ N''&lt;/a&gt;&lt;p&gt;Please visit the &lt;a href=''
		+ S.[SiteUrl]
		+ N''/members/''
		+ U.UserName
		+ N''/friends/default.aspx&gt;My Friends&lt;/a&gt; page of your profile to approve these requests.&lt;/p&gt;</Body><BodyEncoding>65001</BodyEncoding><BodyFormat /><IsBodyHtml>True</IsBodyHtml><Attachments><Attachment /></Attachments><AttachmentHandles /><AlternateViews></AlternateViews></MailMessage>''
	FROM (
		SELECT DISTINCT UP.[SettingsID], UX.[Email], UX.UserName
		FROM cs_Users UX
		INNER JOIN cs_UserProfile UP on UX.UserID = UP.UserID AND UP.EnableEmail = 1
		WHERE EXISTS (
			SELECT 1 FROM [cs_FriendRequests] FR
			WHERE UX.UserID = FR.RequesteeUserID AND FR.FriendshipState = 1
		)
	) AS U
	INNER JOIN (
		select SX.SettingsID
			, coalesce( convert( nvarchar(512), substring(SX.SettingsXML, len(''SiteName'')+2+NULLIF(patindex(N''%<SiteName>%</SiteName>%'', SX.SettingsXML), 0),NULLIF(patindex(N''%</SiteName>%'', SX.SettingsXML), 0) - len(''SiteName'') - 2 - NULLIF(patindex(N''%<SiteName>%</SiteName>%'', SX.SettingsXML), 0)) ), N''NO_SITE''	) AS [SiteName]
			, coalesce( convert( nvarchar(512), substring(SX.SettingsXML, len(''SiteUrl'' )+2+NULLIF(patindex(N''%<SiteUrl>%</SiteUrl>%''  , SX.SettingsXML), 0),NULLIF(patindex(N''%</SiteUrl>%'' , SX.SettingsXML), 0) - len(''SiteUrl'' ) - 2 - NULLIF(patindex(N''%<SiteUrl>%</SiteUrl>%''  , SX.SettingsXML), 0)) ), N''NO SITE URL'') AS [SiteUrl]
		FROM cs_SiteSettings SX
		WHERE
			''true'' = convert( nvarchar(4), substring(SX.SettingsXML, len(''EnableEmail'')+2+NULLIF(patindex(N''%<EnableEmail>%</EnableEmail>%'', SX.SettingsXML), 0),NULLIF(patindex(N''%</EnableEmail>%'', SX.SettingsXML), 0) - len(''EnableEmail'') - 2 - NULLIF(patindex(N''%<EnableEmail>%</EnableEmail>%'', SX.SettingsXML), 0)) )
	) S on S.SettingsID = U.SettingsID
	'

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 
