using System.Web.Security;
using System.Configuration.Provider;
using System.Collections.Specialized;
using System;
using System.Data;
using System.Configuration;
using System.Diagnostics;
using System.Web;
using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using System.Web.Configuration;
using Npgsql;
using NpgsqlTypes;

/*

This provider works with the following schema for the table of user data.

CREATE TABLE users
(
  applicationname text NOT NULL,
  username text NOT NULL,
  passwd text NOT NULL,
  email text NOT NULL,
  passwordquestion text,
  passwordanswer text,
  isapproved bit(1),
  islockedout bit(1),
  creationdate date,
  lastlogindate date,
  lastpasswordchangeddate date,
  lastlockedoutdate date,
  failedpasswordattemptcount integer,
  failedpasswordattemptwindowstart date,
  failedpasswordanswerattemptcount integer,
  failedpasswordanswerattemptwindowstart date,
  comments text,
  lastactivitydate date,
  CONSTRAINT users_primarykey PRIMARY KEY (applicationname, username)
) 
WITHOUT OIDS;
*/

/// <summary>
/// NpgsqlProviderUserKey pour alimenter le ProviderUserKey de MemberShipUser avec NpgsqlMemberShipProvider
/// </summary>
[Serializable()]
public class NpgsqlProviderUserKey : Object
{
    private string pApplicationName;
    private string pUserName;

    public string ApplicationName
    {
        get { return pApplicationName; }
        set { pApplicationName = value; }
    }

    public string UserName
    {
        get { return pUserName; }
        set { pUserName = value; }
    }
}

/// <summary>
/// Description résumée de NpgsqlMembershipProvider
/// </summary>
public class NpgsqlMembershipProvider : MembershipProvider
{

    private int newPasswordLength = 12;
    private string tableName = "users";
    private string connectionString;

    private string pApplicationName;
    private bool pEnablePasswordReset;
    private bool pEnablePasswordRetrieval;
    private bool pRequiresQuestionAndAnswer;
    private bool pRequiresUniqueEmail;
    private int pMaxInvalidPasswordAttempts;
    private int pPasswordAttemptWindow;
    private MembershipPasswordFormat pPasswordFormat;
    private int pMinRequiredNonAlphanumericCharacters;
    private int pMinRequiredPasswordLength;
    private string pPasswordStrengthRegularExpression;
    private MachineKeySection machineKey;

    //
    // Utils methods
    //

    private string EncodePassword(string password)
    {
        string encodedPassword = password;

        switch (PasswordFormat)
        {
            case MembershipPasswordFormat.Clear:
                break;
            case MembershipPasswordFormat.Encrypted:
                encodedPassword =
                  Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
                break;
            case MembershipPasswordFormat.Hashed:
                HMACSHA1 hash = new HMACSHA1();
                hash.Key = HexToByte(machineKey.ValidationKey);
                encodedPassword =
                  Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
                break;
            default:
                throw new ProviderException("Unsupported password format.");
        }

        return encodedPassword;
    }
    private string UnEncodePassword(string encodedPassword)
    {
        string password = encodedPassword;

        switch (PasswordFormat)
        {
            case MembershipPasswordFormat.Clear:
                break;
            case MembershipPasswordFormat.Encrypted:
                password =
                  Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
                break;
            case MembershipPasswordFormat.Hashed:
                throw new ProviderException("Cannot unencode a hashed password.");
            default:
                throw new ProviderException("Unsupported password format.");
        }

        return password;
    }
    private bool CheckPassword(string password, string dbpassword)
    {
        string pass1 = password;
        string pass2 = dbpassword;

        switch (PasswordFormat)
        {
            case MembershipPasswordFormat.Encrypted:
                pass2 = UnEncodePassword(dbpassword);
                break;
            case MembershipPasswordFormat.Hashed:
                pass1 = EncodePassword(password);
                break;
            default:
                break;
        }

        if (pass1 == pass2)
        {
            return true;
        }

        return false;
    }
    private MembershipUser GetUserFromReader(NpgsqlDataReader reader)
    {
        NpgsqlProviderUserKey providerUserKey = new NpgsqlProviderUserKey();

        providerUserKey.ApplicationName = pApplicationName;
        providerUserKey.UserName = reader.GetString(0);

        string username = reader.GetString(0);
        string email = reader.GetString(1);

        string passwordQuestion = "";
        if (reader.GetValue(2) != DBNull.Value)
            passwordQuestion = reader.GetString(2);

        string comment = "";
        if (reader.GetValue(3) != DBNull.Value)
            comment = reader.GetString(3);

        bool isApproved = reader.GetBoolean(4);
        bool isLockedOut = reader.GetBoolean(5);
        DateTime creationDate = reader.GetDateTime(6);

        DateTime lastLoginDate = new DateTime();
        if (reader.GetValue(7) != DBNull.Value)
            lastLoginDate = reader.GetDateTime(7);

        DateTime lastActivityDate = reader.GetDateTime(8);
        DateTime lastPasswordChangedDate = reader.GetDateTime(9);

        DateTime lastLockedOutDate = new DateTime();
        if (reader.GetValue(10) != DBNull.Value)
            lastLockedOutDate = reader.GetDateTime(10);

        MembershipUser u = new MembershipUser(this.Name,
                                              username,
                                              providerUserKey,
                                              email,
                                              passwordQuestion,
                                              comment,
                                              isApproved,
                                              isLockedOut,
                                              creationDate,
                                              lastLoginDate,
                                              lastActivityDate,
                                              lastPasswordChangedDate,
                                              lastLockedOutDate);

        return u;
    }
    private string GetConfigValue(string configValue, string defaultValue)
    {
        if (String.IsNullOrEmpty(configValue))
            return defaultValue;

        return configValue;
    }
    private byte[] HexToByte(string hexString)
    {
        byte[] returnBytes = new byte[hexString.Length / 2];
        for (int i = 0; i < returnBytes.Length; i++)
            returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
        return returnBytes;
    }
    private void UpdateFailureCount(string username, string failureType)
    {
        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT failedpasswordattemptcount, " +
                                              "  failedpasswordattemptwindowstart, " +
                                              "  failedpasswordanswerattemptcount, " +
                                              "  failedpasswordanswerattemptwindowstart " +
                                              "  FROM " + tableName + " " +
                                              "  WHERE username = :username AND applicationname = :applicationname", conn);

        cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

        NpgsqlDataReader reader = null;
        DateTime windowStart = new DateTime();
        int failureCount = 0;

        try
        {
            conn.Open();

            reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

            if (reader.HasRows)
            {
                reader.Read();

                if (failureType == "password")
                {
                    failureCount = reader.GetInt32(0);
                    windowStart = reader.GetDateTime(1);
                }

                if (failureType == "passwordAnswer")
                {
                    failureCount = reader.GetInt32(2);
                    windowStart = reader.GetDateTime(3);
                }
            }

            reader.Close();

            DateTime windowEnd = windowStart.AddMinutes(PasswordAttemptWindow);

            if (failureCount == 0 || DateTime.Now > windowEnd)
            {
                // First password failure or outside of PasswordAttemptWindow. 
                // Start a new password failure count from 1 and a new window starting now.

                if (failureType == "password")
                    cmd.CommandText = "UPDATE " + tableName + " " +
                                      "  SET failedpasswordattemptcount = :failedpasswordattemptcount, " +
                                      "      failedpasswordattemptwindowstart = :failedpasswordattemptwindowstart " +
                                      "  WHERE username = :username AND applicationname = :applicationname";

                if (failureType == "passwordAnswer")
                    cmd.CommandText = "UPDATE " + tableName + " " +
                                      "  SET failedpasswordanswerattemptcount = :failedpasswordanswerattemptcount, " +
                                      "      failedpasswordanswerattemptwindowstart = :failedpasswordanswerattemptwindowstart " +
                                      "  WHERE username = :username AND applicationname = :applicationname";

                cmd.Parameters.Clear();

                cmd.Parameters.Add("failedpasswordattemptcount", NpgsqlDbType.Integer).Value = 1;
                cmd.Parameters.Add("failedpasswordattemptwindowstart", NpgsqlDbType.Date).Value = DateTime.Now;
                cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
                cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

                if (cmd.ExecuteNonQuery() < 0)
                    throw new ProviderException("Unable to update failure count and window start.");
            }
            else
            {
                if (failureCount++ >= MaxInvalidPasswordAttempts)
                {
                    // Password attempts have exceeded the failure threshold. Lock out
                    // the user.

                    cmd.CommandText = "UPDATE " + tableName + " " +
                                      "  SET islockedout = :islockedout, lastlockedoutdate = :lastlockedoutdate " +
                                      "  WHERE username = :username AND applicationname = :applicationname";

                    cmd.Parameters.Clear();

                    cmd.Parameters.Add("islockedout", NpgsqlDbType.Bit).Value = true;
                    cmd.Parameters.Add("lastlockedoutdate", NpgsqlDbType.Date).Value = DateTime.Now;
                    cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
                    cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

                    if (cmd.ExecuteNonQuery() < 0)
                        throw new ProviderException("Unable to lock out user.");
                }
                else
                {
                    // Password attempts have not exceeded the failure threshold. Update
                    // the failure counts. Leave the window the same.

                    if (failureType == "password")
                        cmd.CommandText = "UPDATE " + tableName + " " +
                                          "  SET failedpasswordattemptcount = :failedpasswordattemptcount" +
                                          "  WHERE username = :username AND applicationname = :applicationname";

                    if (failureType == "passwordAnswer")
                        cmd.CommandText = "UPDATE " + tableName + " " +
                                          "  SET failedpasswordanswerattemptcount = :failedpasswordanswerattemptcount" +
                                          "  WHERE username = :username AND applicationname = :applicationname";

                    cmd.Parameters.Clear();

                    cmd.Parameters.Add("failedpasswordattemptcount", NpgsqlDbType.Integer).Value = failureCount;
                    cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
                    cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

                    if (cmd.ExecuteNonQuery() < 0)
                        throw new ProviderException("Unable to update failure count.");
                }
            }
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            if (reader != null) { reader.Close(); }
            conn.Close();
        }
    }

    //
    //  Override methods
    //

    public override void Initialize(string name, NameValueCollection config)
    {
        //
        // Initialize values from web.config.
        //

        if (config == null)
            throw new ArgumentNullException("config");

        if (name == null || name.Length == 0)
            name = "NpgsqlMembershipProvider";

        if (String.IsNullOrEmpty(config["description"]))
        {
            config.Remove("description");
            config.Add("description", "Npgsql PostgreSQL Membership provider");
        }

        // Initialize the abstract base class.
        base.Initialize(name, config);

        pApplicationName = GetConfigValue(config["applicationName"],
                                        System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
        pMaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"));
        pPasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
        pMinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"], "1"));
        pMinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"));
        pPasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], ""));
        pEnablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
        pEnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"));
        pRequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"));
        pRequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));

        string temp_format = config["passwordFormat"];
        if (temp_format == null)
        {
            temp_format = "Hashed";
        }

        switch (temp_format)
        {
            case "Hashed":
                pPasswordFormat = MembershipPasswordFormat.Hashed;
                break;
            case "Encrypted":
                pPasswordFormat = MembershipPasswordFormat.Encrypted;
                break;
            case "Clear":
                pPasswordFormat = MembershipPasswordFormat.Clear;
                break;
            default:
                throw new ProviderException("Password format not supported.");
        }

        //
        // Initialize NpgsqlConnection.
        //

        ConnectionStringSettings ConnectionStringSettings =
          ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

        if (ConnectionStringSettings == null || ConnectionStringSettings.ConnectionString.Trim() == "")
        {
            throw new ProviderException("Connection string cannot be blank.");
        }

        connectionString = ConnectionStringSettings.ConnectionString;


        // Get encryption and decryption key information from the configuration.
        Configuration cfg =
          WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
        machineKey = (MachineKeySection)cfg.GetSection("system.web/machineKey");
/*
        // Create table if not exists
        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand count = new NpgsqlCommand(
            "select count(*) " +
            "from pg_tables " +
            "where schemaname = 'public' and tablename= :tablename"
            );
        count.Parameters.Add("tablename",NpgsqlDbType.Text).Value = tableName;

        try
        {
            conn.Open();
            if( (long)count.ExecuteScalar() == 0 )
            {
                NpgsqlCommand cmd = new NpgsqlCommand(
                    "CREATE TABLE " + tableName + " " +
                    "( " +
                        "applicationname text NOT NULL, " +
                        "username text NOT NULL, " +
                        "passwd text NOT NULL, " +
                        "email text NOT NULL, " +
                        "passwordquestion text, " +
                        "passwordanswer text, " +
                        "isapproved bit(1), " +
                        "islockedout bit(1), " +
                        "creationdate date, " +
                        "lastlogindate date, " +
                        "lastpasswordchangeddate date, " +
                        "lastlockedoutdate date, " +
                        "failedpasswordattemptcount integer, " +
                        "failedpasswordattemptwindowstart date, " +
                        "failedpasswordanswerattemptcount integer, " +
                        "failedpasswordanswerattemptwindowstart date, " +
                        "comments text, " +
                        "lastactivitydate date, " +
                        "CONSTRAINT users_providerkey PRIMARY KEY (applicationname, username) " +
                    ") ", conn);
                cmd.ExecuteNonQuery();
            }
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }
*/
    }

    public override string ApplicationName
    {
        get { return pApplicationName; }
        set { pApplicationName = value; }
    }

    public override bool ChangePassword(string username, string oldPassword, string newPassword)
    {
        if (!ValidateUser(username, oldPassword))
            return false;

        ValidatePasswordEventArgs args =
          new ValidatePasswordEventArgs(username, newPassword, true);

        OnValidatingPassword(args);

        if (args.Cancel)
            if (args.FailureInformation != null)
                throw args.FailureInformation;
            else
                throw new MembershipPasswordException("Change password canceled due to new password validation failure.");


        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("UPDATE " + tableName +
                " SET passwd = :passwd, lastpasswordchangeddate = :lastpasswordchangeddate " +
                " WHERE username = :username AND applicationname = :applicationname", conn);

        cmd.Parameters.Add("passwd", NpgsqlDbType.Text).Value = EncodePassword(newPassword);
        cmd.Parameters.Add("lastpasswordchangeddate", NpgsqlDbType.Date).Value = DateTime.Now;
        cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;


        int rowsAffected = 0;

        try
        {
            conn.Open();

            rowsAffected = cmd.ExecuteNonQuery();
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }

        if (rowsAffected > 0)
        {
            return true;
        }

        return false;
    }

    public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
    {
        if (!ValidateUser(username, password))
            return false;

        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("UPDATE " + tableName +
                " SET passwordquestion = :passwordquestion, passwordanswer = :passwordanswer" +
                " WHERE username = :username AND applicationname = :applicationname", conn);

        cmd.Parameters.Add("passwordquestion", NpgsqlDbType.Text).Value = newPasswordQuestion;
        cmd.Parameters.Add("passwordanswer", NpgsqlDbType.Text).Value = EncodePassword(newPasswordAnswer);
        cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;


        int rowsAffected = 0;

        try
        {
            conn.Open();

            rowsAffected = cmd.ExecuteNonQuery();
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }

        if (rowsAffected > 0)
        {
            return true;
        }

        return false;
    }

    public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status)
    {
        ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);

        OnValidatingPassword(args);

        if (args.Cancel)
        {
            status = MembershipCreateStatus.InvalidPassword;
            return null;
        }

        if (RequiresUniqueEmail && GetUserNameByEmail(email) != "")
        {
            status = MembershipCreateStatus.DuplicateEmail;
            return null;
        }

        MembershipUser u = GetUser(username, false);

        if (u == null)
        {
            DateTime createDate = DateTime.Now;

            if (providerUserKey == null)
            {
                providerUserKey = new NpgsqlProviderUserKey();
                ((NpgsqlProviderUserKey)providerUserKey).ApplicationName = pApplicationName;
                ((NpgsqlProviderUserKey)providerUserKey).UserName = username;
            }
            else
            {
                if (!(providerUserKey is NpgsqlProviderUserKey))
                {
                    status = MembershipCreateStatus.InvalidProviderUserKey;
                    return null;
                }
            }

            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            NpgsqlCommand cmd = new NpgsqlCommand("INSERT INTO " + tableName +
                  " (username, passwd, email, passwordquestion, " +
                  " passwordanswer, isapproved," +
                  " comments, creationdate, lastpasswordchangeddate, lastactivitydate," +
                  " applicationname, islockedout, lastlockedoutdate," +
                  " failedpasswordattemptcount, failedpasswordattemptwindowstart, " +
                  " failedpasswordanswerattemptcount, failedpasswordanswerattemptwindowstart)" +
                  " Values(:username, :passwd, :email, :passwordquestion, " +
                  " :passwordanswer, :isapproved, " +
                  " :comment, :creationdate, :lastpasswordchangeddate, :lastactivitydate, " +
                  " :applicationname, :islockedout, :lastlockedoutdate, " +
                  " :failedpasswordattemptcount, :failedpasswordanswerattemptwindowstart, " +
                  " :failedpasswordanswerattemptcount, :failedpasswordanswerattemptwindowstart)", conn);

            cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
            cmd.Parameters.Add("passwd", NpgsqlDbType.Text).Value = EncodePassword(password);
            cmd.Parameters.Add("email", NpgsqlDbType.Text).Value = email;
            cmd.Parameters.Add("passwordquestion", NpgsqlDbType.Text).Value = passwordQuestion;
            cmd.Parameters.Add("passwordanswer", NpgsqlDbType.Text).Value = EncodePassword(passwordAnswer);
            cmd.Parameters.Add("isapproved", NpgsqlDbType.Bit).Value = isApproved;
            cmd.Parameters.Add("comment", NpgsqlDbType.Text).Value = "";
            cmd.Parameters.Add("creationdate", NpgsqlDbType.Date).Value = createDate;
            cmd.Parameters.Add("lastpasswordchangeddate", NpgsqlDbType.Date).Value = createDate;
            cmd.Parameters.Add("lastactivitydate", NpgsqlDbType.Date).Value = createDate;
            cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;
            cmd.Parameters.Add("islockedout", NpgsqlDbType.Bit).Value = false;
            cmd.Parameters.Add("lastlockedoutdate", NpgsqlDbType.Date).Value = createDate;
            cmd.Parameters.Add("failedpasswordattemptcount", NpgsqlDbType.Integer).Value = 0;
            cmd.Parameters.Add("failedpasswordattemptwindowstart", NpgsqlDbType.Date).Value = createDate;
            cmd.Parameters.Add("failedpasswordanswerattemptcount", NpgsqlDbType.Integer).Value = 0;
            cmd.Parameters.Add("failedpasswordanswerattemptwindowstart", NpgsqlDbType.Date).Value = createDate;

            try
            {
                conn.Open();

                int recAdded = cmd.ExecuteNonQuery();

                if (recAdded > 0)
                {
                    status = MembershipCreateStatus.Success;
                }
                else
                {
                    status = MembershipCreateStatus.UserRejected;
                }
            }
            catch (NpgsqlException e)
            {
                status = MembershipCreateStatus.ProviderError;
                throw e;
            }
            finally
            {
                conn.Close();
            }

            return GetUser(username, false);
        }
        else
        {
            status = MembershipCreateStatus.DuplicateUserName;
        }

        return null;
    }

    public override bool DeleteUser(string username, bool deleteAllRelatedData)
    {
        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("DELETE FROM " + tableName +
                " WHERE username = :username AND applicationname = :applicationname", conn);

        cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

        int rowsAffected = 0;

        try
        {
            conn.Open();

            rowsAffected = cmd.ExecuteNonQuery();

            if (deleteAllRelatedData)
            {
                // Process commands to delete all data for the user in the database.
            }
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }

        if (rowsAffected > 0)
            return true;

        return false;
    }

    public override bool EnablePasswordReset
    {
        get { return pEnablePasswordReset; }
    }

    public override bool EnablePasswordRetrieval
    {
        get { return pEnablePasswordRetrieval; }
    }

    public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
    {
        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT Count(*) FROM " + tableName + " " +
                                          "WHERE email LIKE :email AND applicationname = :applicationname", conn);
        cmd.Parameters.Add("email", NpgsqlDbType.Text).Value = emailToMatch;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = ApplicationName;

        MembershipUserCollection users = new MembershipUserCollection();

        NpgsqlDataReader reader = null;
        totalRecords = 0;

        try
        {
            conn.Open();
            totalRecords = (int)(long)cmd.ExecuteScalar();

            if (totalRecords <= 0) { return users; }

            cmd.CommandText = "SELECT username, email, passwordquestion," +
                     " comments, isapproved, islockedout, creationdate, lastlogindate," +
                     " lastactivitydate, lastpasswordchangeddate, lastlockedoutdate " +
                     " FROM " + tableName + " " +
                     " WHERE email LIKE :email AND applicationname = :applicationname " +
                     " ORDER BY username Asc";

            reader = cmd.ExecuteReader();

            int counter = 0;
            int startIndex = pageSize * pageIndex;
            int endIndex = startIndex + pageSize - 1;

            while (reader.Read())
            {
                if (counter >= startIndex)
                {
                    MembershipUser u = GetUserFromReader(reader);
                    users.Add(u);
                }

                if (counter >= endIndex) { cmd.Cancel(); }

                counter++;
            }
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            if (reader != null) { reader.Close(); }

            conn.Close();
        }

        return users;
    }

    public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
    {
        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT Count(*) FROM " + tableName + " " +
                  "WHERE username LIKE :username AND applicationname = :applicationname", conn);

        cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = usernameToMatch;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

        MembershipUserCollection users = new MembershipUserCollection();

        NpgsqlDataReader reader = null;

        try
        {
            conn.Open();
            totalRecords = (int)(long)cmd.ExecuteScalar();

            if (totalRecords <= 0) { return users; }

            cmd.CommandText = "SELECT username, email, passwordquestion," +
              " comments, isapproved, islockedout, creationdate, lastlogindate," +
              " lastactivitydate, lastpasswordchangeddate, lastlockedoutdate " +
              " FROM " + tableName + " " +
              " WHERE username LIKE :username AND applicationname = :applicationname " +
              " ORDER BY username Asc";

            reader = cmd.ExecuteReader();

            int counter = 0;
            int startIndex = pageSize * pageIndex;
            int endIndex = startIndex + pageSize - 1;

            while (reader.Read())
            {
                if (counter >= startIndex)
                {
                    MembershipUser u = GetUserFromReader(reader);
                    users.Add(u);
                }

                if (counter >= endIndex) { cmd.Cancel(); }

                counter++;
            }
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            if (reader != null) { reader.Close(); }

            conn.Close();
        }

        return users;
    }

    public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
    {
        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT Count(*) FROM " + tableName + " " +
                                          "WHERE applicationname = :applicationname", conn);

        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = ApplicationName;

        MembershipUserCollection users = new MembershipUserCollection();

        NpgsqlDataReader reader = null;
        totalRecords = 0;

        try
        {
            conn.Open();
            totalRecords = (int)(long)cmd.ExecuteScalar();

            if (totalRecords <= 0) { return users; }

            cmd.CommandText = "SELECT username, email, passwordquestion," +
                     " comments, isapproved, islockedout, creationdate, lastlogindate," +
                     " lastactivitydate, lastpasswordchangeddate, lastlockedoutdate " +
                     " FROM " + tableName + " " +
                     " WHERE applicationname = :applicationname " +
                     " ORDER BY username Asc";

            reader = cmd.ExecuteReader();

            int counter = 0;
            int startIndex = pageSize * pageIndex;
            int endIndex = startIndex + pageSize - 1;

            while (reader.Read())
            {
                if (counter >= startIndex)
                {
                    MembershipUser u = GetUserFromReader(reader);
                    users.Add(u);
                }

                if (counter >= endIndex) { cmd.Cancel(); }

                counter++;
            }
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            if (reader != null) { reader.Close(); }
            conn.Close();
        }

        return users;
    }

    public override int GetNumberOfUsersOnline()
    {
        TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
        DateTime compareTime = DateTime.Now.Subtract(onlineSpan);

        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT Count(*) FROM " + tableName +
                " WHERE lastactivitydate > :lastactivitydate AND applicationname = :applicationname", conn);

        cmd.Parameters.Add("lastactivitydate", NpgsqlDbType.Date).Value = compareTime;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

        int numOnline = 0;

        try
        {
            conn.Open();

            numOnline = (int)(long)cmd.ExecuteScalar();
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }

        return numOnline;
    }

    public override string GetPassword(string username, string answer)
    {
        if (!EnablePasswordRetrieval)
        {
            throw new ProviderException("Password Retrieval Not Enabled.");
        }

        if (PasswordFormat == MembershipPasswordFormat.Hashed)
        {
            throw new ProviderException("Cannot retrieve Hashed passwords.");
        }

        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT passwd, passwordanswer, islockedout FROM " + tableName +
              " WHERE username = :username AND applicationname = :applicationname", conn);

        cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

        string password = "";
        string passwordAnswer = "";
        NpgsqlDataReader reader = null;

        try
        {
            conn.Open();

            reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

            if (reader.HasRows)
            {
                reader.Read();

                if (reader.GetBoolean(2))
                    throw new MembershipPasswordException("The supplied user is locked out.");

                password = reader.GetString(0);
                passwordAnswer = reader.GetString(1);
            }
            else
            {
                throw new MembershipPasswordException("The supplied user name is not found.");
            }
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            if (reader != null) { reader.Close(); }
            conn.Close();
        }


        if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
        {
            UpdateFailureCount(username, "passwordAnswer");

            throw new MembershipPasswordException("Incorrect password answer.");
        }


        if (PasswordFormat == MembershipPasswordFormat.Encrypted)
        {
            password = UnEncodePassword(password);
        }

        return password;
    }

    public override MembershipUser GetUser(string username, bool userIsOnline)
    {
        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT username, email, passwordquestion," +
             " comments, isapproved, islockedout, creationdate, lastlogindate," +
             " lastactivitydate, lastpasswordchangeddate, lastlockedoutdate" +
             " FROM " + tableName + " WHERE username = :username AND applicationname = :applicationname", conn);

        cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

        MembershipUser u = null;
        NpgsqlDataReader reader = null;

        try
        {
            conn.Open();

            reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                reader.Read();
                u = GetUserFromReader(reader);

                if (userIsOnline)
                {
                    NpgsqlCommand updateCmd = new NpgsqlCommand("UPDATE " + tableName + " " +
                              "SET lastactivitydate = :lastactivitydate " +
                              "WHERE username = :username AND applicationname = :applicationname", conn);

                    updateCmd.Parameters.Add("lastactivitydate", NpgsqlDbType.Date).Value = DateTime.Now;
                    updateCmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
                    updateCmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

                    updateCmd.ExecuteNonQuery();
                }
            }

        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            if (reader != null) { reader.Close(); }

            conn.Close();
        }

        return u;
    }

    public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
    {
        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT username, email, passwordquestion," +
              " comments, isapproved, islockedout, creationdate, lastlogindate," +
              " lastactivitydate, lastpasswordchangeddate, lastlockedoutdate" +
              " FROM " + tableName + " WHERE applicationname = :applicationname and username = :username", conn);

        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = ((NpgsqlProviderUserKey)providerUserKey).ApplicationName;
        cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = ((NpgsqlProviderUserKey)providerUserKey).UserName;

        MembershipUser u = null;
        NpgsqlDataReader reader = null;

        try
        {
            conn.Open();

            reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                reader.Read();
                u = GetUserFromReader(reader);

                if (userIsOnline)
                {
                    NpgsqlCommand updateCmd = new NpgsqlCommand("UPDATE " + tableName + " " +
                              "SET lastactivitydate = :lastactivitydate " +
                              "WHERE applicationname = :applicationname and username= :username", conn);

                    updateCmd.Parameters.Add("lastactivitydate", NpgsqlDbType.Date).Value = DateTime.Now;
                    updateCmd.Parameters.Add("applicationame", NpgsqlDbType.Text).Value = ((NpgsqlProviderUserKey)providerUserKey).ApplicationName;
                    updateCmd.Parameters.Add("username", NpgsqlDbType.Text).Value = ((NpgsqlProviderUserKey)providerUserKey).UserName;

                    updateCmd.ExecuteNonQuery();
                }
            }

        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            if (reader != null) { reader.Close(); }

            conn.Close();
        }

        return u;
    }

    public override string GetUserNameByEmail(string email)
    {
        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT username" +
              " FROM " + tableName + " WHERE email = :email AND applicationname = :applicationname", conn);

        cmd.Parameters.Add("email", NpgsqlDbType.Text).Value = email;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

        string username = "";

        try
        {
            conn.Open();

            username = (string)cmd.ExecuteScalar();
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }

        if (username == null)
            username = "";

        return username;
    }

    public override int MaxInvalidPasswordAttempts
    {
        get { return pMaxInvalidPasswordAttempts; }
    }

    public override int MinRequiredNonAlphanumericCharacters
    {
        get { return pMinRequiredNonAlphanumericCharacters; }
    }

    public override int MinRequiredPasswordLength
    {
        get { return pMinRequiredPasswordLength; }
    }

    public override int PasswordAttemptWindow
    {
        get { return pPasswordAttemptWindow; }
    }

    public override MembershipPasswordFormat PasswordFormat
    {
        get { return pPasswordFormat; }
    }

    public override string PasswordStrengthRegularExpression
    {
        get { return pPasswordStrengthRegularExpression; }
    }

    public override bool RequiresQuestionAndAnswer
    {
        get { return pRequiresQuestionAndAnswer; }
    }

    public override bool RequiresUniqueEmail
    {
        get { return pRequiresUniqueEmail; }
    }

    public override string ResetPassword(string username, string answer)
    {
        if (!EnablePasswordReset)
        {
            throw new NotSupportedException("Password reset is not enabled.");
        }

        if (answer == null && RequiresQuestionAndAnswer)
        {
            UpdateFailureCount(username, "passwordAnswer");

            throw new ProviderException("Password answer required for password reset.");
        }

        string newPassword =
          System.Web.Security.Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters);


        ValidatePasswordEventArgs args =
          new ValidatePasswordEventArgs(username, newPassword, true);

        OnValidatingPassword(args);

        if (args.Cancel)
            if (args.FailureInformation != null)
                throw args.FailureInformation;
            else
                throw new MembershipPasswordException("Reset password canceled due to password validation failure.");


        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT passwordanswer, islockedout FROM " + tableName +
              " WHERE username = :username AND applicationname = :applicationname", conn);

        cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

        int rowsAffected = 0;
        string passwordAnswer = "";
        NpgsqlDataReader reader = null;

        try
        {
            conn.Open();

            reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

            if (reader.HasRows)
            {
                reader.Read();

                if (reader.GetBoolean(1))
                    throw new MembershipPasswordException("The supplied user is locked out.");

                passwordAnswer = reader.GetString(0);
            }
            else
            {
                throw new MembershipPasswordException("The supplied user name is not found.");
            }

            if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
            {
                UpdateFailureCount(username, "passwordAnswer");

                throw new MembershipPasswordException("Incorrect password answer.");
            }

            NpgsqlCommand updateCmd = new NpgsqlCommand("UPDATE " + tableName +
                " SET passwd = :passwd, lastpasswordchangeddate = :lastpasswordchangeddate" +
                " WHERE username = :username AND applicationname = :applicationname AND islockedout = :islockedout", conn);

            updateCmd.Parameters.Add("passwd", NpgsqlDbType.Text).Value = EncodePassword(newPassword);
            updateCmd.Parameters.Add("lastpasswordchangeddate", NpgsqlDbType.Date).Value = DateTime.Now;
            updateCmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
            updateCmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;
            updateCmd.Parameters.Add("islockedout", NpgsqlDbType.Bit).Value = false;

            rowsAffected = updateCmd.ExecuteNonQuery();
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            if (reader != null) { reader.Close(); }
            conn.Close();
        }

        if (rowsAffected > 0)
        {
            return newPassword;
        }
        else
        {
            throw new MembershipPasswordException("User not found, or user is locked out. Password not Reset.");
        }
    }

    public override bool UnlockUser(string userName)
    {
        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("UPDATE " + tableName + " " +
                                          " SET islockedout = :islockedout, lastlockedoutdate = :lastlockedoutdate " +
                                          " WHERE username = :username AND applicationname = :applicationname", conn);

        cmd.Parameters.Add("islockedout", NpgsqlDbType.Bit).Value = false;
        cmd.Parameters.Add("lastlockedoutdate", NpgsqlDbType.Date).Value = DateTime.Now;
        cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = userName;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

        int rowsAffected = 0;

        try
        {
            conn.Open();

            rowsAffected = cmd.ExecuteNonQuery();
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }

        if (rowsAffected > 0)
            return true;

        return false;
    }

    public override void UpdateUser(MembershipUser user)
    {
        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("UPDATE " + tableName +
                " SET email = :email, comments = :comment," +
                " isapproved = :isapproved" +
                " WHERE username = :username AND applicationname = :applicationname", conn);

        cmd.Parameters.Add("email", NpgsqlDbType.Text).Value = user.Email;
        cmd.Parameters.Add("comment", NpgsqlDbType.Text).Value = user.Comment;
        cmd.Parameters.Add("isapproved", NpgsqlDbType.Bit).Value = user.IsApproved;
        cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = user.UserName;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;


        try
        {
            conn.Open();

            cmd.ExecuteNonQuery();
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }
    }

    public override bool ValidateUser(string username, string password)
    {
        bool isValid = false;

        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT passwd, isapproved FROM " + tableName +
                " WHERE username = :username AND applicationname = :applicationname AND islockedout = :islockedout", conn);

        cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;
        cmd.Parameters.Add("islockedout", NpgsqlDbType.Bit).Value = false;

        NpgsqlDataReader reader = null;
        bool isApproved = false;
        string pwd = "";

        try
        {
            conn.Open();

            reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

            if (reader.HasRows)
            {
                reader.Read();
                pwd = reader.GetString(0);
                isApproved = reader.GetBoolean(1);
            }
            else
            {
                return false;
            }

            reader.Close();

            if (CheckPassword(password, pwd))
            {
                if (isApproved)
                {
                    isValid = true;

                    NpgsqlCommand updateCmd = new NpgsqlCommand("UPDATE " + tableName + " SET lastlogindate = :lastlogindate" +
                                                            " WHERE username = :username AND applicationname = :applicationname", conn);

                    updateCmd.Parameters.Add("lastlogindate", NpgsqlDbType.Date).Value = DateTime.Now;
                    updateCmd.Parameters.Add("username", NpgsqlDbType.Text).Value = username;
                    updateCmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

                    updateCmd.ExecuteNonQuery();
                }
            }
            else
            {
                conn.Close();

                UpdateFailureCount(username, "password");
            }
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            if (reader != null) { reader.Close(); }
            conn.Close();
        }

        return isValid;
    }
}