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 Npgsql;
using NpgsqlTypes;

/*

This provider works with the following schema for the tables of role data.

CREATE TABLE roles
(
  rolename text NOT NULL,
  applicationname text NOT NULL,
  CONSTRAINT roles_primarykey PRIMARY KEY (rolename, applicationname)
) 
WITHOUT OIDS;

CREATE TABLE usersinroles
(
  username text NOT NULL,
  rolename text NOT NULL,
  applicationname text NOT NULL,
  CONSTRAINT usersinrole_primarykey PRIMARY KEY (username, rolename, applicationname)
) 
WITHOUT OIDS;

*/

/// <summary>
/// Description résumée de Class1
/// </summary>
public class NpgsqlRoleProvider : RoleProvider
{
    //
    // Global connection string, generic exception message.
    //

    private string rolesTable = "roles";
    private string usersInRolesTable = "usersinroles";

    private string pApplicationName;

    private ConnectionStringSettings pConnectionStringSettings;
    private string connectionString;

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

    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 = "NpgsqlRoleProvider";

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

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


        if (config["applicationName"] == null || config["applicationName"].Trim() == "")
        {
            pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
        }
        else
        {
            pApplicationName = config["applicationName"];
        }

        //
        // Initialize OdbcConnection.
        //

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

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

        connectionString = pConnectionStringSettings.ConnectionString;
    }

    public override void AddUsersToRoles(string[] usernames, string[] roleNames)
    {
        foreach (string rolename in roleNames)
        {
            if (!RoleExists(rolename))
            {
                throw new ProviderException("Role name not found.");
            }
        }

        foreach (string username in usernames)
        {
            if (username.IndexOf(',') > 0)
            {
                throw new ArgumentException("User names cannot contain commas.");
            }

            foreach (string rolename in roleNames)
            {
                if (IsUserInRole(username, rolename))
                {
                    throw new ProviderException("User is already in role.");
                }
            }
        }

        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("INSERT INTO " + usersInRolesTable +
                " (username, rolename, applicationname) " +
                " Values(:username, :rolename, :applicationname)", conn);

        NpgsqlParameter userParm = cmd.Parameters.Add("username", NpgsqlDbType.Text);
        NpgsqlParameter roleParm = cmd.Parameters.Add("rolename", NpgsqlDbType.Text);
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = ApplicationName;

        NpgsqlTransaction tran = null;

        try
        {
            conn.Open();
            tran = conn.BeginTransaction();
            cmd.Transaction = tran;

            foreach (string username in usernames)
            {
                foreach (string rolename in roleNames)
                {
                    userParm.Value = username;
                    roleParm.Value = rolename;
                    cmd.ExecuteNonQuery();
                }
            }

            tran.Commit();
        }
        catch (NpgsqlException e)
        {
            try
            {
                tran.Rollback();
            }
            catch { }

            throw e;
        }
        finally
        {
            conn.Close();
        }
    }

    public override void CreateRole(string roleName)
    {
        if (roleName.IndexOf(',') > 0)
        {
            throw new ArgumentException("Role names cannot contain commas.");
        }

        if (RoleExists(roleName))
        {
            throw new ProviderException("Role name already exists.");
        }

        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("INSERT INTO " + rolesTable +
                " (rolename, applicationname) " +
                " Values(:rolename, :applicationname)", conn);

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

        try
        {
            conn.Open();

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

    public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
    {
        if (!RoleExists(roleName))
        {
            throw new ProviderException("Role does not exist.");
        }

        if (throwOnPopulatedRole && GetUsersInRole(roleName).Length > 0)
        {
            throw new ProviderException("Cannot delete a populated role.");
        }

        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("DELETE FROM " + rolesTable +
                " WHERE rolename = :rolename AND applicationname = :applicationname", conn);

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


        NpgsqlCommand cmd2 = new NpgsqlCommand("DELETE FROM " + usersInRolesTable +
                " WHERE rolename = :rolename AND applicationname = :applicationname", conn);

        cmd2.Parameters.Add("rolename", NpgsqlDbType.Text).Value = roleName;
        cmd2.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = ApplicationName;

        NpgsqlTransaction tran = null;

        try
        {
            conn.Open();
            tran = conn.BeginTransaction();
            cmd.Transaction = tran;
            cmd2.Transaction = tran;

            cmd2.ExecuteNonQuery();
            cmd.ExecuteNonQuery();

            tran.Commit();
        }
        catch (NpgsqlException e)
        {
            try
            {
                tran.Rollback();
            }
            catch { }


            throw e;
        }
        finally
        {
            conn.Close();
        }

        return true;
    }

    public override string[] GetAllRoles()
    {
        string tmpRoleNames = "";

        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT Rolename FROM " + rolesTable +
                  " WHERE applicationname = :applicationname", conn);

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

        NpgsqlDataReader reader = null;

        try
        {
            conn.Open();

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                tmpRoleNames += reader.GetString(0) + ",";
            }
        }
        catch (NpgsqlException e)
        {
           throw e;
        }
        finally
        {
            if (reader != null) { reader.Close(); }
            conn.Close();
        }

        if (tmpRoleNames.Length > 0)
        {
            // Remove trailing comma.
            tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
            return tmpRoleNames.Split(',');
        }

        return new string[0];
    }

    public override string[] FindUsersInRole(string roleName, string usernameToMatch)
    {
        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT username FROM " + usersInRolesTable + " " +
                  "WHERE username LIKE :username AND rolename = :rolename AND applicationname = :applicationname", conn);
        cmd.Parameters.Add("username", NpgsqlDbType.Text).Value = usernameToMatch;
        cmd.Parameters.Add("rolename", NpgsqlDbType.Text).Value = roleName;
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = pApplicationName;

        string tmpUserNames = "";
        NpgsqlDataReader reader = null;

        try
        {
            conn.Open();

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                tmpUserNames += reader.GetString(0) + ",";
            }
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            if (reader != null) { reader.Close(); }

            conn.Close();
        }

        if (tmpUserNames.Length > 0)
        {
            // Remove trailing comma.
            tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
            return tmpUserNames.Split(',');
        }

        return new string[0];
    }

    public override string[] GetRolesForUser(string username)
    {
        string tmpRoleNames = "";

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

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

        NpgsqlDataReader reader = null;

        try
        {
            conn.Open();

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                tmpRoleNames += reader.GetString(0) + ",";
            }
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            if (reader != null) { reader.Close(); }
            conn.Close();
        }

        if (tmpRoleNames.Length > 0)
        {
            // Remove trailing comma.
            tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
            return tmpRoleNames.Split(',');
        }

        return new string[0];
    }

    public override string[] GetUsersInRole(string roleName)
    {
        string tmpUserNames = "";

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

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

        NpgsqlDataReader reader = null;

        try
        {
            conn.Open();

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                tmpUserNames += reader.GetString(0) + ",";
            }
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            if (reader != null) { reader.Close(); }
            conn.Close();
        }

        if (tmpUserNames.Length > 0)
        {
            // Remove trailing comma.
            tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
            return tmpUserNames.Split(',');
        }

        return new string[0];
    }

    public override bool IsUserInRole(string username, string roleName)
    {
        bool userIsInRole = false;

        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT COUNT(*) FROM " + usersInRolesTable +
                " WHERE username = :username AND rolename = :rolename AND applicationname = :applicationname", conn);

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

        try
        {
            conn.Open();

            int numRecs = (int)(long)cmd.ExecuteScalar();

            if (numRecs > 0)
            {
                userIsInRole = true;
            }
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }

        return userIsInRole;
    }

    public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
    {
        foreach (string rolename in roleNames)
        {
            if (!RoleExists(rolename))
            {
                throw new ProviderException("Role name not found.");
            }
        }

        foreach (string username in usernames)
        {
            foreach (string rolename in roleNames)
            {
                if (!IsUserInRole(username, rolename))
                {
                    throw new ProviderException("User is not in role.");
                }
            }
        }


        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("DELETE FROM " + usersInRolesTable +
                " WHERE username = :username AND rolename = :rolename AND applicationname = :applicationname", conn);

        NpgsqlParameter userParm = cmd.Parameters.Add("username", NpgsqlDbType.Text);
        NpgsqlParameter roleParm = cmd.Parameters.Add("rolename", NpgsqlDbType.Text);
        cmd.Parameters.Add("applicationname", NpgsqlDbType.Text).Value = ApplicationName;

        NpgsqlTransaction tran = null;

        try
        {
            conn.Open();
            tran = conn.BeginTransaction();
            cmd.Transaction = tran;

            foreach (string username in usernames)
            {
                foreach (string rolename in roleNames)
                {
                    userParm.Value = username;
                    roleParm.Value = rolename;
                    cmd.ExecuteNonQuery();
                }
            }

            tran.Commit();
        }
        catch (NpgsqlException e)
        {
            try
            {
                tran.Rollback();
            }
            catch { }


            throw e;
        }
        finally
        {
            conn.Close();
        }
    }

    public override bool RoleExists(string roleName)
    {
        bool exists = false;

        NpgsqlConnection conn = new NpgsqlConnection(connectionString);
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT COUNT(*) FROM " + rolesTable +
                  " WHERE rolename = :rolename AND applicationname = :applicationname", conn);

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

        try
        {
            conn.Open();

            int numRecs = (int)(long)cmd.ExecuteScalar();

            if (numRecs > 0)
            {
                exists = true;
            }
        }
        catch (NpgsqlException e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }

        return exists;
    }
}