OrmLiteAuthRepositoryAsync.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using ServiceStack.Data;
using ServiceStack.Text;
using ServiceStack.OrmLite;

namespace ServiceStack.Auth
{
    public partial class OrmLiteAuthRepository<TUserAuth, TUserAuthDetails> : OrmLiteAuthRepositoryBase<TUserAuth, TUserAuthDetails>
        where TUserAuth : class, IUserAuth
        where TUserAuthDetails : class, IUserAuthDetails
    {
        protected async Task<IDbConnection> OpenDbConnectionAsync()
        {
            return this.NamedConnection != null
                ? await dbFactory.OpenDbConnectionAsync(NamedConnection).ConfigAwait()
                : await dbFactory.OpenDbConnectionAsync().ConfigAwait();
        }

        public override async Task ExecAsync(Func<IDbConnection,Task> fn)
        {
            using var db = await OpenDbConnectionAsync().ConfigAwait();
            await fn(db).ConfigAwait();
        }

        public override async Task<T> ExecAsync<T>(Func<IDbConnection, Task<T>> fn)
        {
            using var db = await OpenDbConnectionAsync().ConfigAwait();
            return await fn(db).ConfigAwait();
        }
    }

    public partial class OrmLiteAuthRepositoryMultitenancy<TUserAuth, TUserAuthDetails> 
        : OrmLiteAuthRepositoryBase<TUserAuth, TUserAuthDetails>, IAsyncDisposable
        where TUserAuth : class, IUserAuth
        where TUserAuthDetails : class, IUserAuthDetails
    {
        public override async Task ExecAsync(Func<IDbConnection,Task> fn)
        {
            if (db == null)
                throw new NotSupportedException("This operation can only be called within context of a Request");

            await fn(db).ConfigAwait();
        }

        public override async Task<T> ExecAsync<T>(Func<IDbConnection, Task<T>> fn)
        {
            if (db == null)
                throw new NotSupportedException("This operation can only be called within context of a Request");

            return await fn(db).ConfigAwait();
        }

        public async Task EachDb(Func<IDbConnection,Task> fn)
        {
            if (dbFactory == null)
                throw new NotSupportedException("This operation can only be called on Startup");

            var ormLiteDbFactory = (OrmLiteConnectionFactory)dbFactory;

            foreach (var connStr in connectionStrings)
            {
                //Required by In Memory Sqlite
                var dbConn = connStr == ormLiteDbFactory.ConnectionString
                    ? await dbFactory.OpenDbConnectionAsync().ConfigAwait()
                    : await dbFactory.OpenDbConnectionStringAsync(connStr).ConfigAwait();

                using (dbConn)
                {
                    await fn(dbConn).ConfigAwait();
                }
            }
        }

        public async ValueTask DisposeAsync()
        {
            if (db is IAsyncDisposable asyncDisposable)
                await asyncDisposable.DisposeAsync().ConfigureAwait(false);
            else
                Dispose();
        }
    }

    public abstract partial class OrmLiteAuthRepositoryBase<TUserAuth, TUserAuthDetails> 
        : IUserAuthRepositoryAsync, IRequiresSchema, IClearableAsync, IManageRolesAsync, IManageApiKeysAsync, IQueryUserAuthAsync
        where TUserAuth : class, IUserAuth
        where TUserAuthDetails : class, IUserAuthDetails
    {
        public abstract Task ExecAsync(Func<IDbConnection, Task> fn);

        public abstract Task<T> ExecAsync<T>(Func<IDbConnection, Task<T>> fn);

        public virtual async Task<IUserAuth> CreateUserAuthAsync(IUserAuth newUser, string password, CancellationToken token=default)
        {
            newUser.ValidateNewUser(password);

            return await ExecAsync(async db =>
            {
                await AssertNoExistingUserAsync(db, newUser, token: token).ConfigAwait();

                newUser.PopulatePasswordHashes(password);
                newUser.CreatedDate = DateTime.UtcNow;
                newUser.ModifiedDate = newUser.CreatedDate;

                await db.SaveAsync((TUserAuth)newUser, token: token).ConfigAwait();

                newUser = await db.SingleByIdAsync<TUserAuth>(newUser.Id, token).ConfigAwait();
                return newUser;
            }).ConfigAwait();
        }

        protected virtual async Task AssertNoExistingUserAsync(IDbConnection db, IUserAuth newUser, IUserAuth exceptForExistingUser = null, CancellationToken token=default)
        {
            if (newUser.UserName != null)
            {
                var existingUser = await GetUserAuthByUserNameAsync(db, newUser.UserName, token).ConfigAwait();
                if (existingUser != null
                    && (exceptForExistingUser == null || existingUser.Id != exceptForExistingUser.Id))
                    throw new ArgumentException(ErrorMessages.UserAlreadyExistsFmt.LocalizeFmt(newUser.UserName.SafeInput()));
            }
            if (newUser.Email != null)
            {
                var existingUser = await GetUserAuthByUserNameAsync(db, newUser.Email, token).ConfigAwait();
                if (existingUser != null
                    && (exceptForExistingUser == null || existingUser.Id != exceptForExistingUser.Id))
                    throw new ArgumentException(ErrorMessages.EmailAlreadyExistsFmt.LocalizeFmt(newUser.Email.SafeInput()));
            }
        }

        public virtual async Task<IUserAuth> UpdateUserAuthAsync(IUserAuth existingUser, IUserAuth newUser, string password, CancellationToken token=default)
        {
            newUser.ValidateNewUser(password);

            return await ExecAsync(async db =>
            {
                await AssertNoExistingUserAsync(db, newUser, existingUser, token).ConfigAwait();

                newUser.Id = existingUser.Id;
                newUser.PopulatePasswordHashes(password, existingUser);
                newUser.CreatedDate = existingUser.CreatedDate;
                newUser.ModifiedDate = DateTime.UtcNow;

                await db.SaveAsync((TUserAuth)newUser, token: token).ConfigAwait();

                return newUser;
            }).ConfigAwait();
        }

        public virtual async Task<IUserAuth> UpdateUserAuthAsync(IUserAuth existingUser, IUserAuth newUser, CancellationToken token=default)
        {
            newUser.ValidateNewUser();

            return await ExecAsync(async db =>
            {
                await AssertNoExistingUserAsync(db, newUser, existingUser, token).ConfigAwait();

                newUser.Id = existingUser.Id;
                newUser.PasswordHash = existingUser.PasswordHash;
                newUser.Salt = existingUser.Salt;
                newUser.DigestHa1Hash = existingUser.DigestHa1Hash;
                newUser.CreatedDate = existingUser.CreatedDate;
                newUser.ModifiedDate = DateTime.UtcNow;

                await db.SaveAsync((TUserAuth)newUser, token: token).ConfigAwait();

                return newUser;
            }).ConfigAwait();
        }

        public virtual async Task<IUserAuth> GetUserAuthByUserNameAsync(string userNameOrEmail, CancellationToken token=default)
        {
            if (userNameOrEmail == null)
                return null;

            return await ExecAsync(async db =>
            {
                if (!hasInitSchema)
                {
                    hasInitSchema = await db.TableExistsAsync<TUserAuth>(token).ConfigAwait(); 

                    if (!hasInitSchema)
                        throw new Exception("OrmLiteAuthRepository Db tables have not been initialized. Try calling 'InitSchema()' in your AppHost Configure method.");
                }
                return await GetUserAuthByUserNameAsync(db, userNameOrEmail, token).ConfigAwait();
            }).ConfigAwait();
        }

        private async Task<TUserAuth> GetUserAuthByUserNameAsync(IDbConnection db, string userNameOrEmail, CancellationToken token=default)
        {
            var isEmail = userNameOrEmail.Contains("@");
            var lowerUserName = userNameOrEmail.ToLower();
            
            TUserAuth userAuth = null;

            // Usernames/Emails are saved in Lower Case so we can do an exact search using lowerUserName
            if (HostContext.GetPlugin<AuthFeature>()?.SaveUserNamesInLowerCase == true)
            {
                return isEmail
                    ? (await db.SelectAsync<TUserAuth>(q => q.Email == lowerUserName, token).ConfigAwait()).FirstOrDefault()
                    : (await db.SelectAsync<TUserAuth>(q => q.UserName == lowerUserName, token).ConfigAwait()).FirstOrDefault();
            }
            
            // Try an exact search using index first
            userAuth = isEmail
                ? (await db.SelectAsync<TUserAuth>(q => q.Email == userNameOrEmail, token).ConfigAwait()).FirstOrDefault()
                : (await db.SelectAsync<TUserAuth>(q => q.UserName == userNameOrEmail, token).ConfigAwait()).FirstOrDefault();

            if (userAuth != null)
                return userAuth;

            // Fallback to a non-index search if no exact match is found
            if (ForceCaseInsensitiveUserNameSearch)
            {
                userAuth = isEmail
                    ? (await db.SelectAsync<TUserAuth>(q => q.Email.ToLower() == lowerUserName, token).ConfigAwait()).FirstOrDefault()
                    : (await db.SelectAsync<TUserAuth>(q => q.UserName.ToLower() == lowerUserName, token).ConfigAwait()).FirstOrDefault();
            }
            
            return userAuth;
        }

        public async Task<List<IUserAuth>> GetUserAuthsAsync(string orderBy = null, int? skip = null, int? take = null, CancellationToken token=default)
        {
            return await ExecAsync(async db => {
                var q = db.From<TUserAuth>();
                SetOrderBy(q, orderBy);
                if (skip != null || take != null)
                    q.Limit(skip, take);
                return (await db.SelectAsync(q, token).ConfigAwait()).ConvertAll(x => (IUserAuth)x);
            }).ConfigAwait();
        }

        public async Task<List<IUserAuth>> SearchUserAuthsAsync(string query, string orderBy = null, int? skip = null, int? take = null, CancellationToken token=default)
        {
            return await ExecAsync(async db => {
                var q = db.From<TUserAuth>();
                if (!string.IsNullOrEmpty(query))
                {
                    q.Where(x => x.UserName.Contains(query) ||
                                 x.PrimaryEmail.Contains(query) ||
                                 x.Email.Contains(query) ||
                                 x.DisplayName.Contains(query) ||
                                 x.Company.Contains(query));
                }
                SetOrderBy(q, orderBy);
                if (skip != null || take != null)
                    q.Limit(skip, take);
                return (await db.SelectAsync(q, token).ConfigAwait()).ConvertAll(x => (IUserAuth)x);
            }).ConfigAwait();
        }
        
        public virtual async Task<IUserAuth> TryAuthenticateAsync(string userName, string password, CancellationToken token=default)
        {
            var userAuth = await GetUserAuthByUserNameAsync(userName, token).ConfigAwait();
            if (userAuth == null)
                return null;

            if (userAuth.VerifyPassword(password, out var needsRehash))
            {
                await this.RecordSuccessfulLoginAsync(userAuth, needsRehash, password, token).ConfigAwait();
                return userAuth;
            }

            await this.RecordInvalidLoginAttemptAsync(userAuth, token).ConfigAwait();

            return null;
        }

        public virtual async Task<IUserAuth> TryAuthenticateAsync(Dictionary<string, string> digestHeaders, string privateKey, int nonceTimeOut, string sequence, CancellationToken token=default)
        {
            var userAuth = await GetUserAuthByUserNameAsync(digestHeaders["username"], token).ConfigAwait();
            if (userAuth == null)
                return null;

            if (userAuth.VerifyDigestAuth(digestHeaders, privateKey, nonceTimeOut, sequence))
            {
                await this.RecordSuccessfulLoginAsync(userAuth, token).ConfigAwait();
                return userAuth;
            }

            await this.RecordInvalidLoginAttemptAsync(userAuth, token).ConfigAwait();
            return null;
        }

        public virtual async Task DeleteUserAuthAsync(string userAuthId, CancellationToken token=default)
        {
            await ExecAsync(async db => {
                
                using var trans = db.OpenTransaction();
                var userId = int.Parse(userAuthId);

                await db.DeleteAsync<TUserAuth>(x => x.Id == userId, token: token).ConfigAwait();
                await db.DeleteAsync<TUserAuthDetails>(x => x.UserAuthId == userId, token: token).ConfigAwait();
                await db.DeleteAsync<UserAuthRole>(x => x.UserAuthId == userId, token: token).ConfigAwait();

                trans.Commit();
            }).ConfigAwait();
        }

        public virtual async Task LoadUserAuthAsync(IAuthSession session, IAuthTokens tokens, CancellationToken token=default)
        {
            if (session == null)
                throw new ArgumentNullException(nameof(session));

            var userAuth = await GetUserAuthAsync(session, tokens, token).ConfigAwait();
            await LoadUserAuthAsync(session, userAuth, token).ConfigAwait();
        }

        private async Task LoadUserAuthAsync(IAuthSession session, IUserAuth userAuth, CancellationToken token=default)
        {
            await session.PopulateSessionAsync(userAuth, this, token).ConfigAwait();
        }

        public virtual async Task<IUserAuth> GetUserAuthAsync(string userAuthId, CancellationToken token=default)
        {
            if (string.IsNullOrEmpty(userAuthId))
                throw new ArgumentNullException(nameof(userAuthId));
            
            return await ExecAsync(async db => 
                await db.SingleByIdAsync<TUserAuth>(int.Parse(userAuthId), token).ConfigAwait()).ConfigAwait();
        }

        public virtual async Task SaveUserAuthAsync(IAuthSession authSession, CancellationToken token=default)
        {
            if (authSession == null)
                throw new ArgumentNullException(nameof(authSession));

            await ExecAsync(async db =>
            {
                var userAuth = !authSession.UserAuthId.IsNullOrEmpty()
                    ? await db.SingleByIdAsync<TUserAuth>(int.Parse(authSession.UserAuthId), token).ConfigAwait()
                    : authSession.ConvertTo<TUserAuth>();

                if (userAuth.Id == default && !authSession.UserAuthId.IsNullOrEmpty())
                    userAuth.Id = int.Parse(authSession.UserAuthId);

                userAuth.ModifiedDate = DateTime.UtcNow;
                if (userAuth.CreatedDate == default)
                    userAuth.CreatedDate = userAuth.ModifiedDate;

                await db.SaveAsync(userAuth, token: token).ConfigAwait();
            }).ConfigAwait();
        }

        public virtual async Task SaveUserAuthAsync(IUserAuth userAuth, CancellationToken token=default)
        {
            if (userAuth == null)
                throw new ArgumentNullException(nameof(userAuth));

            userAuth.ModifiedDate = DateTime.UtcNow;
            if (userAuth.CreatedDate == default(DateTime))
                userAuth.CreatedDate = userAuth.ModifiedDate;

            await ExecAsync(async db =>
            {
                await db.SaveAsync((TUserAuth) userAuth, token: token).ConfigAwait();
            }).ConfigAwait();
        }

        public virtual async Task<List<IUserAuthDetails>> GetUserAuthDetailsAsync(string userAuthId, CancellationToken token=default)
        {
            var id = int.Parse(userAuthId);
            return await ExecAsync(async db =>
            {
                return (await db.SelectAsync<TUserAuthDetails>(q => q.UserAuthId == id, token).ConfigAwait())
                    .OrderBy(x => x.ModifiedDate).Cast<IUserAuthDetails>().ToList();
            }).ConfigAwait();
        }

        public virtual async Task<IUserAuth> GetUserAuthAsync(IAuthSession authSession, IAuthTokens tokens, CancellationToken token=default)
        {
            if (!authSession.UserAuthId.IsNullOrEmpty())
            {
                var userAuth = await GetUserAuthAsync(authSession.UserAuthId, token).ConfigAwait();
                if (userAuth != null)
                    return userAuth;
            }
            if (!authSession.UserAuthName.IsNullOrEmpty())
            {
                var userAuth = await GetUserAuthByUserNameAsync(authSession.UserAuthName, token).ConfigAwait();
                if (userAuth != null)
                    return userAuth;
            }

            if (tokens == null || tokens.Provider.IsNullOrEmpty() || tokens.UserId.IsNullOrEmpty())
                return null;

            return await ExecAsync(async db =>
            {
                var oAuthProvider = (await db.SelectAsync<TUserAuthDetails>(q =>
                    q.Provider == tokens.Provider && q.UserId == tokens.UserId, token).ConfigAwait()).FirstOrDefault();

                if (oAuthProvider != null)
                {
                    var userAuth = await db.SingleByIdAsync<TUserAuth>(oAuthProvider.UserAuthId, token).ConfigAwait();
                    return userAuth;
                }
                return null;
            });
        }

        public virtual async Task<IUserAuthDetails> CreateOrMergeAuthSessionAsync(IAuthSession authSession, IAuthTokens tokens, CancellationToken token=default)
        {
            TUserAuth userAuth = (TUserAuth)await GetUserAuthAsync(authSession, tokens, token).ConfigAwait()
                ?? typeof(TUserAuth).CreateInstance<TUserAuth>();

            return await ExecAsync(async db =>
            {
                var authDetails = (await db.SelectAsync<TUserAuthDetails>(
                    q => q.Provider == tokens.Provider && q.UserId == tokens.UserId, token).ConfigAwait()).FirstOrDefault();

                if (authDetails == null)
                {
                    authDetails = typeof(TUserAuthDetails).CreateInstance<TUserAuthDetails>();
                    authDetails.Provider = tokens.Provider;
                    authDetails.UserId = tokens.UserId;
                }

                authDetails.PopulateMissing(tokens, overwriteReserved: true);
                userAuth.PopulateMissingExtended(authDetails);

                userAuth.ModifiedDate = DateTime.UtcNow;
                if (userAuth.CreatedDate == default)
                    userAuth.CreatedDate = userAuth.ModifiedDate;

                await db.SaveAsync(userAuth, token: token).ConfigAwait();

                authDetails.UserAuthId = userAuth.Id;

                authDetails.ModifiedDate = userAuth.ModifiedDate;
                if (authDetails.CreatedDate == default)
                    authDetails.CreatedDate = userAuth.ModifiedDate;

                await db.SaveAsync(authDetails, token: token).ConfigAwait();

                return authDetails;
            }).ConfigAwait();
        }

        public virtual async Task ClearAsync(CancellationToken token=default)
        {
            await ExecAsync(async db =>
            {
                await db.DeleteAllAsync<TUserAuth>(token).ConfigAwait();
                await db.DeleteAllAsync<TUserAuthDetails>(token).ConfigAwait();
                if (UseDistinctRoleTables)
                    await db.DeleteAllAsync<UserAuthRole>(token).ConfigAwait();
            }).ConfigAwait();
        }

        public virtual async Task<ICollection<string>> GetRolesAsync(string userAuthId, CancellationToken token=default)
        {
            AssertUserAuthId(userAuthId);
            if (!UseDistinctRoleTables)
            {
                var userAuth = await GetUserAuthAsync(userAuthId, token).ConfigAwait();
                return userAuth?.Roles ?? TypeConstants.EmptyStringList;
            }
            else
            {
                return await ExecAsync(async db =>
                {
                    return (await db.SelectAsync<UserAuthRole>(q => q.UserAuthId == int.Parse(userAuthId) && q.Role != null, token).ConfigAwait())
                        .ConvertAll(x => x.Role);
                }).ConfigAwait();
            }
        }

        public virtual async Task<ICollection<string>> GetPermissionsAsync(string userAuthId, CancellationToken token=default)
        {
            AssertUserAuthId(userAuthId);
            if (!UseDistinctRoleTables)
            {
                var userAuth = await GetUserAuthAsync(userAuthId, token).ConfigAwait();
                return userAuth?.Permissions ?? TypeConstants.EmptyStringList;
            }
            else
            {
                return await ExecAsync(async db =>
                {
                    return (await db.SelectAsync<UserAuthRole>(q => q.UserAuthId == int.Parse(userAuthId) && q.Permission != null, token).ConfigAwait())
                        .ConvertAll(x => x.Permission);
                }).ConfigAwait();
            }
        }

        public virtual async Task<Tuple<ICollection<string>, ICollection<string>>> GetRolesAndPermissionsAsync(string userAuthId, CancellationToken token=default)
        {
            ICollection<string> roles;
            ICollection<string> permissions;
            
            AssertUserAuthId(userAuthId);
            if (!UseDistinctRoleTables)
            {
                var userAuth = await GetUserAuthAsync(userAuthId, token).ConfigAwait();
                if (userAuth != null)
                {
                    roles = userAuth.Roles;
                    permissions = userAuth.Permissions;
                }
                else
                {
                    roles = permissions = TypeConstants.EmptyStringArray;
                }
            }
            else
            {
                roles = new List<string>();
                permissions = new List<string>();
                
                var rolesAndPerms = await ExecAsync(async db =>
                {
                    return await db.KeyValuePairsAsync<string,string>(db.From<UserAuthRole>()
                        .Where(x => x.UserAuthId == int.Parse(userAuthId))
                        .Select(x => new { x.Role, x.Permission }), token).ConfigAwait(); 
                }).ConfigAwait();

                foreach (var kvp in rolesAndPerms)
                {
                    if (kvp.Key != null)
                        roles.Add(kvp.Key);
                    if (kvp.Value != null)
                        permissions.Add(kvp.Value);
                }                
            }
            return Tuple.Create(roles, permissions);
        }

        public virtual async Task<bool> HasRoleAsync(string userAuthId, string role, CancellationToken token=default)
        {
            if (role == null)
                throw new ArgumentNullException(nameof(role));

            if (userAuthId == null)
                return false;

            if (!UseDistinctRoleTables)
            {
                var userAuth = await GetUserAuthAsync(userAuthId, token).ConfigAwait();
                return userAuth.Roles != null && userAuth.Roles.Contains(role);
            }
            else
            {
                return await ExecAsync(async db =>
                {
                    return await db.CountAsync<UserAuthRole>(q =>
                        q.UserAuthId == int.Parse(userAuthId) && q.Role == role, token).ConfigAwait() > 0;
                }).ConfigAwait();
            }
        }

        public virtual async Task<bool> HasPermissionAsync(string userAuthId, string permission, CancellationToken token=default)
        {
            if (permission == null)
                throw new ArgumentNullException(nameof(permission));

            if (userAuthId == null)
                return false;

            if (!UseDistinctRoleTables)
            {
                var userAuth = await GetUserAuthAsync(userAuthId, token).ConfigAwait();
                return userAuth.Permissions != null && userAuth.Permissions.Contains(permission);
            }
            else
            {
                return await ExecAsync(async db =>
                {
                    return await db.CountAsync<UserAuthRole>(q =>
                        q.UserAuthId == int.Parse(userAuthId) && q.Permission == permission, token).ConfigAwait() > 0;
                }).ConfigAwait();
            }
        }

        public virtual async Task AssignRolesAsync(string userAuthId, ICollection<string> roles = null, ICollection<string> permissions = null, CancellationToken token=default)
        {
            var userAuth = await GetUserAuthAsync(userAuthId, token).ConfigAwait();
            if (!UseDistinctRoleTables)
            {
                if (!roles.IsEmpty())
                {
                    foreach (var missingRole in roles.Where(x => userAuth.Roles == null || !userAuth.Roles.Contains(x)))
                    {
                        if (userAuth.Roles == null)
                            userAuth.Roles = new List<string>();

                        userAuth.Roles.Add(missingRole);
                    }
                }

                if (!permissions.IsEmpty())
                {
                    foreach (var missingPermission in permissions.Where(x => userAuth.Permissions == null || !userAuth.Permissions.Contains(x)))
                    {
                        if (userAuth.Permissions == null)
                            userAuth.Permissions = new List<string>();

                        userAuth.Permissions.Add(missingPermission);
                    }
                }

                await SaveUserAuthAsync(userAuth, token).ConfigAwait();
            }
            else
            {
                await ExecAsync(async db =>
                {
                    var now = DateTime.UtcNow;
                    var userRoles = await db.SelectAsync<UserAuthRole>(q => q.UserAuthId == userAuth.Id, token).ConfigAwait();

                    if (!roles.IsEmpty())
                    {
                        var roleSet = userRoles.Where(x => x.Role != null).Select(x => x.Role).ToSet();
                        foreach (var role in roles)
                        {
                            if (!roleSet.Contains(role))
                            {
                                await db.InsertAsync(new UserAuthRole
                                {
                                    UserAuthId = userAuth.Id,
                                    Role = role,
                                    CreatedDate = now,
                                    ModifiedDate = now,
                                }, token: token).ConfigAwait();
                            }
                        }
                    }

                    if (!permissions.IsEmpty())
                    {
                        var permissionSet = userRoles.Where(x => x.Permission != null).Select(x => x.Permission).ToSet();
                        foreach (var permission in permissions)
                        {
                            if (!permissionSet.Contains(permission))
                            {
                                await db.InsertAsync(new UserAuthRole
                                {
                                    UserAuthId = userAuth.Id,
                                    Permission = permission,
                                    CreatedDate = now,
                                    ModifiedDate = now,
                                }, token: token).ConfigAwait();
                            }
                        }
                    }
                }).ConfigAwait();
            }
        }

        public virtual async Task UnAssignRolesAsync(string userAuthId, ICollection<string> roles = null, ICollection<string> permissions = null, CancellationToken token=default)
        {
            var userAuth = await GetUserAuthAsync(userAuthId, token).ConfigAwait();
            if (!UseDistinctRoleTables)
            {
                roles.Each(x => userAuth.Roles.Remove(x));
                permissions.Each(x => userAuth.Permissions.Remove(x));

                if (roles != null || permissions != null)
                {
                    await SaveUserAuthAsync(userAuth, token).ConfigAwait();
                }
            }
            else
            {
                await ExecAsync(async db =>
                {
                    if (!roles.IsEmpty())
                    {
                        await db.DeleteAsync<UserAuthRole>(q => q.UserAuthId == userAuth.Id && roles.Contains(q.Role), token: token).ConfigAwait();
                    }
                    if (!permissions.IsEmpty())
                    {
                        await db.DeleteAsync<UserAuthRole>(q => q.UserAuthId == userAuth.Id && permissions.Contains(q.Permission), token: token).ConfigAwait();
                    }
                }).ConfigAwait();
            }
        }

        public async Task<bool> ApiKeyExistsAsync(string apiKey, CancellationToken token=default)
        {
            return await ExecAsync(async db =>
            {
                return await db.ExistsAsync<ApiKey>(x => x.Id == apiKey, token).ConfigAwait();
            }).ConfigAwait();
        }

        public async Task<ApiKey> GetApiKeyAsync(string apiKey, CancellationToken token=default)
        {
            return await ExecAsync(async db => 
                await db.SingleByIdAsync<ApiKey>(apiKey, token).ConfigAwait()).ConfigAwait();
        }

        public async Task<List<ApiKey>> GetUserApiKeysAsync(string userId, CancellationToken token=default)
        {
            return await ExecAsync(async db =>
            {
                var q = db.From<ApiKey>()
                    .Where(x => x.UserAuthId == userId)
                    .And(x => x.CancelledDate == null)
                    .And(x => x.ExpiryDate == null || x.ExpiryDate >= DateTime.UtcNow)
                    .OrderByDescending(x => x.CreatedDate);

                return await db.SelectAsync(q, token).ConfigAwait();
            }).ConfigAwait();
        }

        public async Task StoreAllAsync(IEnumerable<ApiKey> apiKeys, CancellationToken token=default)
        {
            await ExecAsync(async db =>
            {
                await db.SaveAllAsync(apiKeys, token).ConfigAwait();
            }).ConfigAwait();
        }
    }
}