//*****************************************************************************
// This code file is part of the Universal Provider Framework for SharePoint.
// This file was written by Adam Buenz [WSS MVP] of ARB Security Solutions, LLC
//                  http://www.sharepointsecurity.com
//
// This file and its parts is free for re-distribution, for use in both free 
// and commercial applications, however this header must remain intact for legal 
// use. The Universal Provider Framework is distributed in the hope that it will
// be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
//*****************************************************************************

//**************************************
// Current Version: 1.0.0.0 (Beta)
//**************************************

// namespace references
using System;
using System.Configuration.Provider;
using System.Data;
using System.Data.Common;
using System.Runtime.InteropServices;
using System.Web.Security;

namespace Universal.SharePointProvider.Framework
{
    internal class SharePointUsersProvider
    {
        /// <summary>
        /// Declare constructor for the SharePointUsersProvider class
        /// </summary>
        /// <param name="owner"></param>
        /// <param name="provider"></param>
        /// <param name="applicationName"></param>
        public SharePointUsersProvider(ProviderBase owner, DbProviderFactory provider, string applicationName)
        {
            this.owner = owner;
            this.applicationName = applicationName;
            this.provider = provider;
        }

        /// <summary>
        /// Declare variables
        /// </summary>
        private readonly string applicationName;
        private readonly ProviderBase owner;
        private readonly DbProviderFactory provider;

        /// <summary>
        /// Declare properties 
        /// </summary>
        private string ApplicationName
        {
            get
            {
                return this.applicationName;
            }
        }

        private string Name
        {
            get
            {
                return this.owner.Name;
            }
        }

        /// <summary>
        /// Declare enumeration for the provider type
        /// </summary>
        public enum ProviderType
        {
            All = 7,
            Membership = 1,
            Profile = 4,
            Role = 2
        }

        /// <summary>
        /// General parameter information
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        protected virtual void ApplyParameterInfo(DbParameter parameter, DbType dbType, object value)
        {
            // Set the DBtype parameter
            parameter.DbType = dbType;
            // Set the value of the parameter
            parameter.Value = value;
        }

        /// <summary>
        /// Database command and connection 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        private DbCommand CreateCommand(string commandText, DbConnection connection)
        {
            DbCommand userProviderDbCommand = this.provider.CreateCommand();
            userProviderDbCommand.Connection = connection;
            userProviderDbCommand.CommandText = commandText;
            return userProviderDbCommand;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private DbParameter CreateParameter(string name, DbType dbType, object value)
        {
            DbParameter userProviderParameterCommand = this.provider.CreateParameter();
            userProviderParameterCommand.ParameterName = name;
            this.ApplyParameterInfo(userProviderParameterCommand, dbType, value);
            return userProviderParameterCommand;
        }

        /// <summary>
        /// General parameters of the factory implemenation
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private DbParameter CreateParameter(string name, DbType dbType, int size, object value)
        {
            DbParameter userProviderParameterCommand = this.provider.CreateParameter();
            userProviderParameterCommand.ParameterName = name;
            userProviderParameterCommand.Size = size;
            this.ApplyParameterInfo(userProviderParameterCommand, dbType, value);
            return userProviderParameterCommand;
        }

        /// <summary>
        /// Create the user in the membership data store
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="isAnonymous"></param>
        /// <param name="username"></param>
        /// <param name="userId"></param>
        /// <param name="userMustNotExist"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public object CreateUser(DbConnection conn, bool isAnonymous, string username, object userId, bool userMustNotExist, out MembershipCreateStatus status)
        {
            IDbCommand createUserDbCommand = null;
            bool flagValid;

            // Determine whether the creation status resulted in success
            status = MembershipCreateStatus.Success;
            object objOutput = null;

            // Get various information related to the user
            objOutput = this.GetMembershipByUserName(conn, "username", DbType.String, username.ToLower(), out flagValid, false);
            if (objOutput != null)
            {
                if (userMustNotExist || ((userId != null) && (((Guid) objOutput) != ((Guid) userId))))
                {
                    // Declare whether the creation status results in a duplicate username
                    status = MembershipCreateStatus.DuplicateUserName;
                    return objOutput;
                }
                if (!flagValid && !isAnonymous)
                {
                    createUserDbCommand = this.CreateCommand("UPDATE aspnet_users SET lastactivitydate = :lastactivitydate, isanonymous = FALSE WHERE userid = :userid ", conn);
                    createUserDbCommand.Parameters.Add(this.CreateParameter("lastactivitydate", DbType.DateTime, DateTime.Now));
                    createUserDbCommand.Parameters.Add(this.CreateParameter("userid", DbType.Guid, userId));
                    createUserDbCommand.ExecuteNonQuery();
                }
                return objOutput;
            }
            if (string.IsNullOrEmpty(username))
            {
                // determine whether the creation status resulted in an invalid username
                status = MembershipCreateStatus.InvalidUserName;
                return objOutput;
            }

            // Get the current Date/Time for the insert of user into the data store
            DateTime currentTime = DateTime.Now;
            createUserDbCommand = this.CreateCommand("INSERT INTO aspnet_users ( lastactivitydate, applicationname, username, userid, isanonymous) VALUES ( :lastactivitydate, :applicationname, :username, :userid, :isanonymous)", conn);
            createUserDbCommand.Parameters.Add(this.CreateParameter("lastactivitydate", DbType.DateTime, currentTime));
            
            // Throw an error message if there are insatisfactions with the userID
            if ((userId != null) && !(userId is Guid))
            {
                throw new ArgumentException("userid");
            }

            // determine whether the GUID is null
            if (userId == null)
            {
                // Create a new GUID if the GUID is null
                userId = Guid.NewGuid();
            }

            // Add various parameters to the database
            createUserDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName));
            createUserDbCommand.Parameters.Add(this.CreateParameter("userid", DbType.Guid, userId));
            createUserDbCommand.Parameters.Add(this.CreateParameter("username", DbType.String, 0xff, username));
            createUserDbCommand.Parameters.Add(this.CreateParameter("isanonymous", DbType.Boolean, isAnonymous));
            createUserDbCommand.ExecuteNonQuery();
            return userId;
        }

        /// <summary>
        /// Delete the user from the database
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="username"></param>
        /// <param name="providerType"></param>
        /// <returns></returns>
        public bool DeleteUser(DbConnection conn, string username, ProviderType providerType)
        {
            bool flagValidate;
            string textQuery;
            int primaryCounter = 0;
            int secondaryCounter = 0;

            //dbObject can be customized to query data type
            object dbObject = null;
            dbObject = this.GetMembershipByUserName(conn, "username", DbType.String, username.ToLower(), out flagValidate, false);
            
            // determine if the username is null, if so the user can't be deleted
            if (dbObject == null)
            {
                return false;
            }

            //DB Command line
            IDbCommand deleteUserDbCommand = null;

            // Determine if the provider type is that of a pluggable role provider
            if ((providerType & ProviderType.Role) > ((ProviderType) 0))
            {
                deleteUserDbCommand = this.CreateCommand("DELETE FROM aspnet_usersinroles WHERE userid = :userid", conn);
                deleteUserDbCommand.Parameters.Add(this.CreateParameter("userid", DbType.Guid, dbObject));
                try
                {
                    //execute nonquery for "delete and insert", if the return is 0 so for error handling
                    if (deleteUserDbCommand.ExecuteNonQuery() > 0)
                    {
                        primaryCounter++;
                    }
                    secondaryCounter++;
                }
                catch (Exception exception)
                {
                    throw exception;
                }
            }
            else if (this.GetRecordCount(conn, "aspnet_usersinroles", dbObject) == 0)
            {
                secondaryCounter++;
            }

            // determine if the provider type is that of a profile
            if ((providerType & ProviderType.Profile) > ((ProviderType) 0))
            {

                // Remove the user based on userID from arbitrary profiles
                textQuery = "DELETE FROM aspnet_profiles WHERE userid = :userid";
                
                // determine whether the command is empty, and if so populate with the textQuery
                if (deleteUserDbCommand == null)
                {
                    deleteUserDbCommand = this.CreateCommand(textQuery, conn);
                }
                else
                {
                    deleteUserDbCommand.CommandText = textQuery;
                    deleteUserDbCommand.Parameters.Clear();
                }
                deleteUserDbCommand.Parameters.Add(this.CreateParameter("userid", DbType.Guid, dbObject));
                try
                {
                    if (deleteUserDbCommand.ExecuteNonQuery() > 0)
                    {
                        primaryCounter++;
                    }
                    secondaryCounter++;
                }
                catch
                {
                }
            }
            else if (this.GetRecordCount(conn, "aspnet_profiles", dbObject) == 0)
            {
                secondaryCounter++;
            }

            // Determine if the provider type is that of membership
            if ((providerType & ProviderType.Membership) > ((ProviderType) 0))
            {
                // Remove the user from the various points of membership based on UserID
                textQuery = "DELETE FROM aspnet_membership WHERE userid = :userid";
                if (deleteUserDbCommand == null)
                {
                    deleteUserDbCommand = this.CreateCommand(textQuery, conn);
                }
                else
                {
                    deleteUserDbCommand.CommandText = textQuery;
                    deleteUserDbCommand.Parameters.Clear();
                }
                deleteUserDbCommand.Parameters.Add(this.CreateParameter("userid", DbType.Guid, dbObject));
                try
                {
                    if (deleteUserDbCommand.ExecuteNonQuery() > 0)
                    {
                        primaryCounter++;
                    }
                    secondaryCounter++;
                }
                catch (Exception exception)
                {
                    throw exception;
                }
            }
            else if (this.GetRecordCount(conn, "aspnet_membership", dbObject) == 0)
            {
                secondaryCounter++;
            }
            if (((providerType & ProviderType.All) == ProviderType.All) || (secondaryCounter == 3))
            {
                textQuery = "DELETE FROM aspnet_users WHERE userid = :userid";
                if (deleteUserDbCommand == null)
                {
                    deleteUserDbCommand = this.CreateCommand(textQuery, conn);
                }
                else
                {
                    deleteUserDbCommand.CommandText = textQuery;
                    deleteUserDbCommand.Parameters.Clear();
                }
                deleteUserDbCommand.Parameters.Add(this.CreateParameter("userid", DbType.Guid, dbObject));
                if (deleteUserDbCommand.ExecuteNonQuery() > 0)
                {
                    primaryCounter++;
                }
            }
            return (primaryCounter > 0);
        }

        /// <summary>
        /// Get membership and various information related to the user
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        /// <param name="authenticated"></param>
        /// <param name="caseSensitive"></param>
        /// <returns></returns>
        public object GetMembershipByUserName(DbConnection conn, string parameterName, DbType dbType, object value, out bool authenticated, bool caseSensitive)
        {
            authenticated = false;
            string textQuery = "SELECT userid, isanonymous FROM aspnet_users WHERE LOWER(applicationname) = :applicationname AND ";
            if (caseSensitive)
            {
                textQuery = textQuery + parameterName + " = :" + parameterName;
            }
            else
            {
                textQuery = textQuery + "LOWER(" + parameterName + ") = :" + parameterName;
            }

            // Database command
            IDbCommand selectUserDbCommand = this.CreateCommand(textQuery, conn);
            selectUserDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
            selectUserDbCommand.Parameters.Add(this.CreateParameter(parameterName, dbType, value));
            object dbObj = null;
            
            // Once the database connection is opened call the the reader to harvest data
            IDataReader selectUserDataReader = null;
            try
            {
                selectUserDataReader = selectUserDbCommand.ExecuteReader();
                if (selectUserDataReader.Read())
                {
                    dbObj = (Guid) this.ReadField(selectUserDataReader, 0, DbType.Guid);
                    authenticated = !selectUserDataReader.GetBoolean(1);
                }
            }
            finally
            {
                if (selectUserDataReader != null)
                {
                    selectUserDataReader.Close();
                }
            }

            // The value that is returned
            return dbObj;
        }

        /// <summary>
        /// Get user records for the database using the userID
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        private int GetRecordCount(DbConnection conn, string tableName, object userId)
        {
            IDbCommand getRecCountDbCommand = this.CreateCommand("SELECT COUNT(*) FROM " + tableName + " WHERE userid = :userid ", conn);
            getRecCountDbCommand.Parameters.Add(this.CreateParameter("userid", DbType.Guid, userId));
            IDataReader getRecCountDbreader = null;
            try
            {
                getRecCountDbreader = getRecCountDbCommand.ExecuteReader();
                if (getRecCountDbreader.Read())
                {
                    return getRecCountDbreader.GetInt32(0);
                }
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                if (getRecCountDbreader != null)
                {
                    getRecCountDbreader.Close();
                }
            }
            return 0;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="i"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        protected virtual object ReadField(IDataReader reader, int i, DbType dbType)
        {
            return reader.GetValue(i);
        }

        /// <summary>
        /// Update user information in the database
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="user"></param>
        /// <returns></returns>
        public Guid UpdateUser(DbConnection conn, MembershipUser user)
        {
            object objReturn = null;
            bool flagValidate;
            if (((objReturn = this.GetMembershipByUserName(conn, "username", DbType.String, user.UserName.ToLower(), out flagValidate, false)) == null) || ((user.ProviderUserKey != null) && (((Guid) objReturn) != ((Guid) user.ProviderUserKey))))
            {
                throw new ProviderException("User does not exist.");
            }
            IDbCommand updateUserDbCommand = this.CreateCommand("UPDATE aspnet_users SET lastactivitydate = :lastactivitydate WHERE LOWER(applicationname) = :applicationname AND LOWER(username) = :username", conn);
            updateUserDbCommand.Parameters.Add(this.CreateParameter("lastactivitydate", DbType.DateTime, DateTime.Now));
            updateUserDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
            updateUserDbCommand.Parameters.Add(this.CreateParameter("username", DbType.String, 0xff, user.UserName.ToLower()));
            updateUserDbCommand.ExecuteNonQuery();
            return (Guid) objReturn;
        }

        /// <summary>
        /// Modify various user parameters in the pluggable membership datastore
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="userName"></param>
        /// <param name="parameters"></param>
        public void UpdateUserParameter(string connectionString, string userName, params object[] parameters)
        {
            DbConnection updateUserParamConnection = this.provider.CreateConnection();
            updateUserParamConnection.ConnectionString = connectionString;
            string textQuery = "UPDATE aspnet_users SET ";
            for (int i = 0; i < parameters.Length; i += 3)
            {
                textQuery = textQuery + ((string) parameters[i]) + " = :" + ((string) parameters[i]);
                if ((i + 3) < parameters.Length)
                {
                    textQuery = textQuery + ", ";
                }
                else
                {
                    textQuery = textQuery + " ";
                }
            }
            textQuery = textQuery + "WHERE LOWER(applicationname) = :applicationname AND LOWER(username) = :username";
            IDbCommand caseUserParamDbCommand = this.CreateCommand(textQuery, updateUserParamConnection);
            for (int ii = 0; ii < parameters.Length; ii += 3)
            {
                caseUserParamDbCommand.Parameters.Add(this.CreateParameter((string) parameters[ii], (DbType) parameters[ii + 1], parameters[ii + 2]));
            }
            caseUserParamDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
            caseUserParamDbCommand.Parameters.Add(this.CreateParameter("username", DbType.String, 0xff, userName.ToLower()));
            try
            {
                updateUserParamConnection.Open();
                caseUserParamDbCommand.ExecuteNonQuery();
            }
            finally
            {
                updateUserParamConnection.Close();
            }
        }
     }
}