using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using AIAHTML5.API.Properties;
using AIAHTML5.API.Constants;
using log4net;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using System.Collections;

namespace AIAHTML5.API.Models
{
    public class DBModel
    {
        static string dbConnectionString = System.Configuration.ConfigurationManager.AppSettings["AIADatabaseV5Context"];

        static SqlConnection conn;
        static SqlCommand cmd;


        public DBModel() { }

        public static void OpenConnection()
        {
            SqlConnection conn = new SqlConnection(dbConnectionString);

            if (conn.State == ConnectionState.Closed)
                conn.Open();
        }

        protected static DataSet GetSQLData(string commandText, bool isSp)
        {
            conn = new SqlConnection(dbConnectionString);
            if (isSp)
            {
                cmd = new SqlCommand(commandText, conn);
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd = new SqlCommand(commandText, conn);
            }
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        protected ArrayList GetUserModules()
        {
            ArrayList arrUserModules = new ArrayList();
            Hashtable userModuleHash = null;
            userModuleHash = new Hashtable();

            string sp = "GetAllAvailableModules";

            DataSet ds = DBModel.GetSQLData(sp, true);
            DataTable dt = ds.Tables[0];

            foreach (DataRow drActType in dt.Rows)
            {
                userModuleHash = new Hashtable();
                userModuleHash.Add(AIAConstants.KEY_NAME, drActType["Name"]);
                userModuleHash.Add(AIAConstants.KEY_SLUG, drActType["Slug"]);
                arrUserModules.Add(userModuleHash);
            }
            return arrUserModules;
        }

        public static dynamic GetUserDetailsByLoginIdAndPassword(string sLoginId, string sPassword)
        {
            User objUser = new User();
            DBModel objModel = new DBModel();

            conn = new SqlConnection(dbConnectionString);
            cmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            SqlParameter param;
            DataSet ds = new DataSet();

            cmd.Connection = conn;
            cmd.CommandText = "GetUserDetailsByLoginIdAndPassword";
            cmd.CommandType = CommandType.StoredProcedure;

            param = new SqlParameter("@sLoginId", sLoginId);
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.String;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@sPassword", sPassword);
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.String;
            cmd.Parameters.Add(param);

            da.SelectCommand = cmd;
            DataTable dt = new DataTable();
            da.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dc.ColumnName == "Id")
                            objUser.Id = Convert.ToInt32(dr[dc]);
                        if (dc.ColumnName == "FirstName")
                            objUser.FirstName = dr[dc].ToString();
                        if (dc.ColumnName == "LastName")
                            objUser.LastName = dr[dc].ToString();
                        if (dc.ColumnName == "EmailId")
                            objUser.EmailId = dr[dc].ToString();
                        if (dc.ColumnName == "LoginId")
                            objUser.LoginId = dr[dc].ToString();
                        if (dc.ColumnName == "Password")
                            objUser.Password = dr[dc].ToString();
                        if (dc.ColumnName == "SecurityQuestionId")
                        {
                            int tempVal;
                            objUser.SecurityQuestionId = Int32.TryParse(dr[dc].ToString(), out tempVal) ? tempVal : (int?)null;
                        }
                        if (dc.ColumnName == "SecurityAnswer")
                            objUser.SecurityAnswer = dr[dc].ToString();
                        if (dc.ColumnName == "CreatorId")
                        {
                            int tempVal;
                            objUser.CreatorId = Int32.TryParse(dr[dc].ToString(), out tempVal) ? tempVal : (int?)null;
                        }
                        if (dc.ColumnName == "CreationDate")
                            objUser.CreationDate = Convert.ToDateTime(dr[dc]);
                        if (dc.ColumnName == "DeactivationDate")
                        {
                            DateTime ddDate;
                            objUser.DeactivationDate = DateTime.TryParse(dr[dc].ToString(), out ddDate) ? ddDate : (DateTime?)null;
                        }
                        if (dc.ColumnName == "ModifierId")
                        {
                            int tempVal;
                            objUser.ModifierId = Int32.TryParse(dr[dc].ToString(), out tempVal) ? tempVal : (int?)null;
                        }
                        if (dc.ColumnName == "ModifiedDate")
                        {
                            DateTime mdDate;
                            objUser.ModifiedDate = DateTime.TryParse(dr[dc].ToString(), out mdDate) ? mdDate : (DateTime?)null;
                        }
                        if (dc.ColumnName == "UserTypeId")
                            objUser.UserType = objModel.GetUserTypeStringById(Convert.ToInt32(dr[dc]));
                        if (dc.ColumnName == "IsActive")
                            objUser.IsActive = Convert.ToBoolean(dr[dc]);

                    }
                }
            }

            if (objUser.IsActive)
            {

                if (objUser != null)
                {
                    if (objUser.UserType == User.SUPER_ADMIN)
                    {
                        objUser.Modules = objModel.GetUserModules();
                    }
                    else
                    {
                        int licenseId = objModel.GetUserLicenseIdByUserId(objUser.Id);

                        if (licenseId != 0)
                        {
                            ArrayList allModulesList = objModel.GetUserModules();
                            ArrayList licensedModulesList = objModel.GetModuleStatusByLicenseId(licenseId);

                            ArrayList userModuleList = objModel.GetUserModulesList(allModulesList, licensedModulesList);

                            objUser.Modules = userModuleList;
                        }

                        else
                        {
                            objUser.Modules = null;
                            objUser = null;
                        }

                    }
                }
            }
            else
            {
                objUser = null;
            }

            return objUser;
        }

        protected int GetUserLicenseIdByUserId(int iUserId)
        {
            int _licenseId = 0;
            conn = new SqlConnection(dbConnectionString);
            cmd = new SqlCommand();
            SqlDataAdapter adapter;
            SqlParameter param;
            DataSet ds = new DataSet();

            cmd.Connection = conn;
            cmd.CommandText = "GetLicenseIdByUserId";
            cmd.CommandType = CommandType.StoredProcedure;

            param = new SqlParameter("@iUserId", iUserId);
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.Int32;
            cmd.Parameters.Add(param);

            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
                _licenseId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            return _licenseId;
        }

        protected ArrayList GetModuleStatusByLicenseId(int iLicenseId)
        {
            ArrayList userModulelist = new ArrayList();
            Hashtable modulesHash;
            DataSet ds = new DataSet();

            conn = new SqlConnection(dbConnectionString);
            cmd = new SqlCommand();
            SqlDataAdapter adapter;
            SqlParameter param;

            cmd.Connection = conn;
            cmd.CommandText = "GetModuleStatusByLicenseId";
            cmd.CommandType = CommandType.StoredProcedure;

            param = new SqlParameter("@iLicenseId", iLicenseId);
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.Int32;
            cmd.Parameters.Add(param);

            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);
            DataTable dt = ds.Tables[0];

            foreach (DataRow dr in dt.Rows)
            {
                modulesHash = new Hashtable();
                modulesHash.Add("Id", dr["Id"]);
                modulesHash.Add("Title", dr["Title"]);
                modulesHash.Add("Status", dr["Status"]);
                userModulelist.Add(modulesHash);
            }

            return userModulelist;
        }

        protected ArrayList GetUserModulesList(ArrayList allModules, ArrayList modulesByLicense)
        {
            ArrayList userModules = new ArrayList();
            Hashtable moduleHash;
            foreach (Hashtable all in allModules)
            {
                string slg = all["slug"].ToString();
                foreach (Hashtable user in modulesByLicense)
                {
                    bool y = Convert.ToBoolean(user["Status"]);
                    if ((user["Title"].ToString().Trim() == all["name"].ToString().Trim()) && (Convert.ToBoolean(user["Status"]) == true))
                    {
                        moduleHash = new Hashtable();
                        moduleHash.Add("name", user["Title"]);
                        moduleHash.Add("slug", all["slug"]);

                        userModules.Add(moduleHash);
                    }
                }
            }

            return userModules;
        }

        protected string GetUserTypeStringById(int iUserTypeId)
        {
            string userType = string.Empty;

            switch (iUserTypeId)
            {
                case 1:
                    userType = User.SUPER_ADMIN;
                    break;
                case 2:
                    userType = User.GENERAL_ADMIN;
                    break;
                case 3:
                    userType = User.DISTRICT_ADMIN;
                    break;
                case 4:
                    userType = User.CLIENT_ADMIN;
                    break;
                case 5:
                    userType = User.SINGLE_USER;
                    break;
                case 6:
                    userType = User.CONCURRENT_USER;
                    break;
                case 7:
                    userType = User.RESELLER;
                    break;
                case 8:
                    userType = User.TEST_ACCOUNT;
                    break;
                case 9:
                    userType = User.SITE_USER;
                    break;
            }
            return userType;
        }

        public static User GetUserDetailsByEmailId(string sEmailId)
        {
            User objUser = new User();
            DBModel objModel = new DBModel();

            try
            {
                conn = new SqlConnection(dbConnectionString);
                cmd = new SqlCommand();
                SqlDataAdapter adapter;
                SqlParameter param;
                DataSet ds = new DataSet();

                cmd.Connection = conn;
                cmd.CommandText = "GetUserInfoByEmailId";
                cmd.CommandType = CommandType.StoredProcedure;

                param = new SqlParameter("@sEmailId", sEmailId);
                param.Direction = ParameterDirection.Input;
                param.DbType = DbType.String;
                cmd.Parameters.Add(param);

                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
                DataTable dt = ds.Tables[0];

                foreach (DataRow dr in dt.Rows)
                {
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dc.ColumnName == "Id")
                            objUser.Id = Convert.ToInt32(dr[dc]);
                        if (dc.ColumnName == "FirstName")
                            objUser.FirstName = dr[dc].ToString();
                        if (dc.ColumnName == "LastName")
                            objUser.LastName = dr[dc].ToString();
                        if (dc.ColumnName == "EmailId")
                            objUser.EmailId = dr[dc].ToString();
                        if (dc.ColumnName == "LoginId")
                            objUser.LoginId = dr[dc].ToString();
                        if (dc.ColumnName == "Password")
                            objUser.Password = dr[dc].ToString();
                        if (dc.ColumnName == "SecurityQuestionId")
                        {
                            int tempVal;
                            objUser.SecurityQuestionId = Int32.TryParse(dr[dc].ToString(), out tempVal) ? tempVal : (int?)null;
                        }
                        if (dc.ColumnName == "SecurityAnswer")
                            objUser.SecurityAnswer = dr[dc].ToString();
                        if (dc.ColumnName == "CreatorId")
                            objUser.CreatorId = Convert.ToInt32(dr[dc]);
                        if (dc.ColumnName == "CreationDate")
                            objUser.CreationDate = Convert.ToDateTime(dr[dc]);
                        if (dc.ColumnName == "DeactivationDate")
                        {
                            DateTime? date;
                            if (dr[dc] == DBNull.Value)
                                date = null;
                            else
                                date = (DateTime)dr[dc];

                            objUser.DeactivationDate = date;
                        }
                        if (dc.ColumnName == "ModifierId")
                            objUser.ModifierId = Convert.ToInt32(dr[dc]);
                        if (dc.ColumnName == "ModifiedDate")
                            objUser.ModifiedDate = Convert.ToDateTime(dr[dc]);
                        if (dc.ColumnName == "UserTypeId")
                            objUser.UserType = objModel.GetUserTypeStringById(Convert.ToInt32(dr[dc]));
                        if (dc.ColumnName == "IsActive")
                            objUser.IsActive = Convert.ToBoolean(dr[dc]);

                    }
                }
            }
            catch (Exception ex)
            { }

            return objUser;
        }

        public static int UpdateUserPassword(dynamic userInfo, string sLoginId, string sEmailId)
        {
            int result = 0;
            conn = new SqlConnection(dbConnectionString);
            cmd = new SqlCommand();
            cmd.Connection = conn;
            conn.Open();
            cmd.CommandText = "UpdateUserPassword";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@sLoginId", sLoginId);
            cmd.Parameters.AddWithValue("@sEmailId", sEmailId);
            cmd.Parameters.AddWithValue("@sNewPassword", userInfo["newPassword"].ToString());
            result = cmd.ExecuteNonQuery();
            conn.Close();
            return result;
        }

    }
}