using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using log4net;
using Helpers.Log;
using System.Reflection;

/// <summary>
/// Summary description for DAL
/// </summary>
public static class DAL
{
    private static string connectionString = @"";
    private static string key { get; set; }
    private static ILog logger = Logger.GetLogger(MethodBase.GetCurrentMethod().DeclaringType.Name);

    static DAL()
    {
        connectionString = ConfigurationManager.ConnectionStrings["SYSTEM"].ConnectionString; 
    }

    public static bool saveKey(string keyValue, int userID)
    {
        Boolean ret = false;
        try
        {
            if (keyValue != null)
            {
                key = keyValue;
                SqlConnection sqlCon = new SqlConnection();
                sqlCon.ConnectionString = connectionString;
                sqlCon.Open();
                SqlCommand comm = new SqlCommand("SYSTEM.SetUserKey", sqlCon);
                comm.CommandType = CommandType.StoredProcedure;

                SqlParameter sqlParam = new SqlParameter("@Key", SqlDbType.VarChar);
                sqlParam.Value = keyValue;
                comm.Parameters.Add(sqlParam);

                SqlParameter sqlParam1 = new SqlParameter("@UserID", SqlDbType.Int);
                sqlParam1.Value = userID;
                comm.Parameters.Add(sqlParam1);

                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(ds);
                da.Dispose();
                sqlCon.Close();
                ret = true;
            }
        }
        catch (Exception ex)
        {
            ret = false;
        }
        return ret;
    }

    public static Dictionary<string, string> GetDefaultConfigurationValuesForSetup(string accountNumber)
    {
        Dictionary<string, string> a = new Dictionary<string, string>();

        try
        {
            SqlConnection sqlCon = new SqlConnection();
            sqlCon.ConnectionString = connectionString;
            sqlCon.Open();
            SqlCommand comm = new SqlCommand("SYSTEM.GetDefaultConfigurationValuesForSetup", sqlCon);
            comm.CommandType = CommandType.StoredProcedure;

            SqlParameter sqlParam = new SqlParameter("@AccountNumber", SqlDbType.VarChar);
            sqlParam.Value = accountNumber;
            comm.Parameters.Add(sqlParam);

            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);

            if (ds != null)
            {
                for (int column = 0; column < ds.Tables[0].Columns.Count; column++)
                {
                    for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
                        a.Add(ds.Tables[0].Columns[column].ToString(), ds.Tables[0].Rows[0].ItemArray[column].ToString());
                }
            }

            da.Dispose();
            sqlCon.Close();

        }
        catch (Exception ex)
        {
            throw new Exception("Fail", ex);
        }

        return a;
    }

    public static bool recoverPassword(string recoveryPassword, string email)
    {
        Boolean ret = false;
        try
        {
            if (recoveryPassword != null && email != null)
            {
                SqlConnection sqlCon = new SqlConnection();
                sqlCon.ConnectionString = connectionString;
                sqlCon.Open();
                SqlCommand comm = new SqlCommand("SYSTEM.SetRecoveryPassword", sqlCon);
                comm.CommandType = CommandType.StoredProcedure;

                SqlParameter sqlParam = new SqlParameter("@RecoveryPassword", SqlDbType.VarChar);
                sqlParam.Value = recoveryPassword;
                comm.Parameters.Add(sqlParam);

                SqlParameter sqlParam1 = new SqlParameter("@Email", SqlDbType.VarChar);
                sqlParam1.Value = email;
                comm.Parameters.Add(sqlParam1);

                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(ds);
                da.Dispose();
                sqlCon.Close();
                ret = true;
            }
        }
        catch (Exception ex)
        {
            ret = false;
        }
        return ret;
    }

    public static string confirmKey(string keyValue)
    {
        Dictionary<string, string> a = new Dictionary<string, string>();
        string status = "INVALID"; 

        try
        {
            if (keyValue != null)
            {
                key = keyValue;
                SqlConnection sqlCon = new SqlConnection();
                sqlCon.ConnectionString = connectionString;
                sqlCon.Open();
                SqlCommand comm = new SqlCommand("SYSTEM.ValidateUser", sqlCon);
                comm.CommandType = CommandType.StoredProcedure;

                SqlParameter sqlParam = new SqlParameter("@Key", SqlDbType.VarChar);
                sqlParam.Value = keyValue;
                comm.Parameters.Add(sqlParam);

                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(ds);

                if (ds != null)
                {
                    for (int column = 0; column < ds.Tables[0].Columns.Count; column++)
                    {
                        for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
                            a.Add(ds.Tables[0].Columns[column].ToString(), ds.Tables[0].Rows[0].ItemArray[column].ToString());
                    }
                }

                da.Dispose();
                sqlCon.Close();

                status = a["Status"]; 
            }
        }
        catch (Exception ex)
        {
            return "INVALID"; 
        }
        return status;
    }

    public static int? getUserId(string username = null, string email = null)
    {
        Dictionary<string, string> data = new Dictionary<string, string>();
        int? userid = null;

        try
        {
            if (username != null || email != null)
            {
                SqlConnection sqlCon = new SqlConnection();
                sqlCon.ConnectionString = connectionString;
                sqlCon.Open();
                SqlCommand comm = new SqlCommand("SYSTEM.GetUserID", sqlCon);
                comm.CommandType = CommandType.StoredProcedure;

                SqlParameter sqlParam = new SqlParameter("@Username", SqlDbType.VarChar);
                sqlParam.Value = username;
                comm.Parameters.Add(sqlParam);

                sqlParam = new SqlParameter("@Email", SqlDbType.VarChar);
                sqlParam.Value = email;
                comm.Parameters.Add(sqlParam);

                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(ds);

                if (ds != null)
                {
                    for (int column = 0; column < ds.Tables[0].Columns.Count; column++)
                    {
                        for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
                            data.Add(ds.Tables[0].Columns[column].ToString(), ds.Tables[0].Rows[0].ItemArray[column].ToString());
                    }
                }

                da.Dispose();
                sqlCon.Close();

                userid = (String.IsNullOrEmpty(data["UserID"])) ? null : (int?) int.Parse(data["UserID"]);
            }
        }
        catch (Exception ex)
        {
            return null;
        }
        return userid;
    }
    public static Dictionary<string, string> getUserInfo(int userId)
    {
        Dictionary<string, string> data = new Dictionary<string, string>();
        try
        {
            SqlConnection sqlCon = new SqlConnection();
            sqlCon.ConnectionString = connectionString;
            sqlCon.Open();
            SqlCommand comm = new SqlCommand("SYSTEM.GetUserInfo", sqlCon);
            comm.CommandType = CommandType.StoredProcedure;

            SqlParameter sqlParam = new SqlParameter("@UserID", SqlDbType.Int);
            sqlParam.Value = userId;
            comm.Parameters.Add(sqlParam);

            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);

            if (ds != null)
            {
                for (int column = 0; column < ds.Tables[0].Columns.Count; column++)
                {
                    for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
                        data.Add(ds.Tables[0].Columns[column].ToString(), ds.Tables[0].Rows[0].ItemArray[column].ToString());
                }
            }

            da.Dispose();
            sqlCon.Close();        
        }
        catch (Exception ex)
        {
            return null;
        }
        return data;
    }

    public static string setResetPasswordToken(int userid, string token)
    {
        Dictionary<string, string> a = new Dictionary<string, string>();
        string status = "FAILED";

        try
        {
            if (userid != null && token != null)
            {
                SqlConnection sqlCon = new SqlConnection();
                sqlCon.ConnectionString = connectionString;
                sqlCon.Open();
                SqlCommand comm = new SqlCommand("SYSTEM.SetResetPasswordToken", sqlCon);
                comm.CommandType = CommandType.StoredProcedure;

                SqlParameter sqlParam = new SqlParameter("@UserID", SqlDbType.Int);
                sqlParam.Value = userid;
                comm.Parameters.Add(sqlParam);

                sqlParam = new SqlParameter("@ResetPasswordToken", SqlDbType.VarChar);
                sqlParam.Value = token;
                comm.Parameters.Add(sqlParam);

                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(ds);

                if (ds != null)
                {
                    for (int column = 0; column < ds.Tables[0].Columns.Count; column++)
                    {
                        for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
                            a.Add(ds.Tables[0].Columns[column].ToString(), ds.Tables[0].Rows[0].ItemArray[column].ToString());
                    }
                }

                da.Dispose();
                sqlCon.Close();

                status = a["Status"];
            }
        }
        catch (Exception ex)
        {
            return "FAILED";
        }
        return status;
    }

    public static string getPasswordHash(string username)
    {
        Dictionary<string, string> a = new Dictionary<string, string>();
        string passwordHash = string.Empty;

        try
        {
            SqlConnection sqlCon = new SqlConnection();
            sqlCon.ConnectionString = connectionString;
            sqlCon.Open();
            SqlCommand comm = new SqlCommand("SYSTEM.GetPasswordHash", sqlCon);
            comm.CommandType = CommandType.StoredProcedure;

            SqlParameter sqlParam = new SqlParameter("@Username", SqlDbType.VarChar);
            sqlParam.Value = username; 
            comm.Parameters.Add(sqlParam);

            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);

            if (ds != null)
            {
                for (int column = 0; column < ds.Tables[0].Columns.Count; column++)
                {
                    for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
                        a.Add(ds.Tables[0].Columns[column].ToString(), ds.Tables[0].Rows[0].ItemArray[column].ToString());
                }
            }

            da.Dispose();
            sqlCon.Close();

            passwordHash = a["Hash"];
        }
        catch (Exception ex)
        {
            return string.Empty; 
        }
        return passwordHash;
    }

    public static string hashPassword(string input)
    {
        // Use input string to calculate MD5 hash
        using (System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create())
        {
            byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(input);
            byte[] hashBytes = md5.ComputeHash(inputBytes);

            // Convert the byte array to hexadecimal string
            string sb = string.Empty; 
            for (int i = 0; i < hashBytes.Length; i++)
            {
                sb = sb + (hashBytes[i].ToString("X2"));
            }
            return sb.ToString();
        }
    }

    public static List<List<Dictionary<string, string>>> GetMenuData(string username)
    {
        List<List<Dictionary<string, string>>> menuData = new List<List<Dictionary<string, string>>>();
        try
        {
            SqlConnection sqlCon = new SqlConnection();
            sqlCon.ConnectionString = connectionString;
            //sqlCon.Open();
            SqlCommand comm = new SqlCommand("FrontEnd.GetMenuData", sqlCon);
            comm.CommandType = CommandType.StoredProcedure;

            SqlParameter sqlParam = new SqlParameter("@CurrentUser", SqlDbType.VarChar);
            sqlParam.Value = username;
            comm.Parameters.Add(sqlParam);
            DataSet ds = Security.DALSecurity.CallSecuredProcedure(sqlCon, comm); 

            //DataSet ds = new DataSet();
            //SqlDataAdapter da = new SqlDataAdapter(comm);
            //da.Fill(ds);

            if (ds != null)
            {
                for (int table = 0; table < ds.Tables.Count; table++)
                {
                    List<Dictionary<string, string>> rows = new List<Dictionary<string, string>>(); 
                    for (int row = 0; row < ds.Tables[table].Rows.Count; row++)
                    {
                        Dictionary<string, string> values = new Dictionary<string, string>();
                        for (int column = 0; column < ds.Tables[table].Columns.Count; column++)
                        {
                            values.Add(ds.Tables[table].Columns[column].ToString(), ds.Tables[table].Rows[row].ItemArray[column].ToString());
                        }
                        rows.Add(values); 
                    }
                    menuData.Add(rows); 
                }
            }

            //da.Dispose();
            //sqlCon.Close();

        }
        catch (Exception ex)
        {
            logger.Debug(ex.Message);
            throw new Exception("Fail", ex);
        }

        return menuData;
    }

    public static List<List<Dictionary<string, string>>> GetUserSecurityModulesActions(string username)
    {
        List<List<Dictionary<string, string>>> data = new List<List<Dictionary<string, string>>>();
        try
        {
            SqlConnection sqlCon = new SqlConnection();
            sqlCon.ConnectionString = connectionString;
            SqlCommand comm = new SqlCommand("FrontEnd.GetUserSecurityModulesActions", sqlCon);
            comm.CommandType = CommandType.StoredProcedure;

            SqlParameter sqlParam = new SqlParameter("@CurrentUser", SqlDbType.VarChar);
            sqlParam.Value = username;
            comm.Parameters.Add(sqlParam);

            DataSet ds = Security.DALSecurity.CallSecuredProcedure(sqlCon, comm);

            if (ds != null)
            {
                for (int table = 0; table < ds.Tables.Count; table++)
                {
                    List<Dictionary<string, string>> rows = new List<Dictionary<string, string>>();
                    for (int row = 0; row < ds.Tables[table].Rows.Count; row++)
                    {
                        Dictionary<string, string> values = new Dictionary<string, string>();
                        for (int column = 0; column < ds.Tables[table].Columns.Count; column++)
                        {
                            values.Add(ds.Tables[table].Columns[column].ToString(), ds.Tables[table].Rows[row].ItemArray[column].ToString());
                        }
                        rows.Add(values);
                    }
                    data.Add(rows);
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception("Fail", ex);
        }

        return data;
    }

    public static bool UserLoggedIn(string username)
    {
        bool success = false; 
        try
        {
            if (username != null)
            {
                SqlConnection sqlCon = new SqlConnection();
                sqlCon.ConnectionString = connectionString;
                sqlCon.Open();
                SqlCommand comm = new SqlCommand("SYSTEM.UserLoggedIn", sqlCon);
                comm.CommandType = CommandType.StoredProcedure;

                SqlParameter sqlParam = new SqlParameter("@Username", SqlDbType.VarChar);
                sqlParam.Value = username;
                comm.Parameters.Add(sqlParam);

                comm.ExecuteScalar(); 

                //DataSet ds = new DataSet();
                //SqlDataAdapter da = new SqlDataAdapter(comm);
                //da.Fill(ds);

                //if (ds != null)
                //{
                //    for (int column = 0; column < ds.Tables[0].Columns.Count; column++)
                //    {
                //        for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
                //            a.Add(ds.Tables[0].Columns[column].ToString(), ds.Tables[0].Rows[0].ItemArray[column].ToString());
                //    }
                //}

                //da.Dispose();
                sqlCon.Close();

                success = true; 
            }
        }
        catch (Exception ex)
        {
            success = false; 
        }

        return success;
    }
}