/*
select * from ASPNetusers
select * from ASPNetUserRoles
select * from ASPNetRoles
select * from AffiliateAdmin

select u.Id, u.FullName, u.Email, u.AffiliateId, r.Name as RoleName, a.[Key]
from ASPNetUsers u
	LEFT JOIN ASPNetUserRoles ur on ur.UserId = u.Id
	LEFT JOIN ASPNetRoles r on r.Id = ur.RoleId
	LEFT JOIN AffiliateAdmin aa on u.Id = aa.ApplicationUserId
	LEFT JOIN Affiliate a on aa.AffiliateId = a.Id
order by u.fullname

update aspnetuserroles set userid = lower(userid), roleid = lower(roleid)
update affiliateadmin set applicationuserid = lower(applicationuserid)

*/

DECLARE @UserEmail varchar(max), @AffiliateKey varchar(max), @RoleName varchar(max), @AffiliateId int, @RoleId uniqueidentifier, @UserId uniqueidentifier

SET @UserEmail = 'smcpeters@brb.org'
SET @AffiliateKey = 'wfgw'
--SET @RoleName = 'Administrator'
SET @RoleName = 'Affiliate Administrator'

SELECT @UserId = Id from ASPNetUsers where Email = @UserEmail
SELECT @RoleId = Id from ASPNetRoles where Name = @RoleName
SELECT @AffiliateId = Id from Affiliate where [Key] = @AffiliateKey

--SELECT LOWER(@UserId), LOWER(@RoleId), @AffiliateId
--RETURN


IF (@UserId is null OR @RoleId is null OR @AffiliateId is null)
BEGIN
	PRINT 'Exiting: missing or invalid info'
	RETURN
END

IF(@RoleName = 'Affiliate Administrator')
BEGIN

	IF NOT EXISTS(	SELECT 1
					FROM AffiliateAdmin
					WHERE AffiliateId = @AffiliateId AND ApplicationUserId = @UserId)
	BEGIN
		PRINT 'Adding AffiliateAdmin record'
		INSERT INTO AffiliateAdmin VALUES(LOWER(@UserId), @AffiliateId, GETUTCDATE())
	END
	ELSE BEGIN
		PRINT 'AffiliateAdmin record exists'
	END
END

IF NOT EXISTS(	SELECT 1
				FROM ASPNetUserRoles
				WHERE UserId = @UserId AND RoleId = @RoleId)
BEGIN
	PRINT 'Adding UserRole'
	INSERT INTO ASPNetUserRoles VALUES(LOWER(@UserId), LOWER(@RoleId))
END
ELSE
BEGIN
	PRINT 'UserRole already exists'
END