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)
        {
            ILog logger = log4net.LogManager.GetLogger((System.Reflection.MethodBase.GetCurrentMethod().DeclaringType));
            logger.Debug(" inside GetSQLData for command text = " + commandText);
            DataSet ds= null;
            try
            {
                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;
                ds = new DataSet();
                da.Fill(ds);
            }
            catch (SqlException ex)
            {
                logger.Fatal("Exception in GetSQLData for command text =" + commandText + ", Exception= " + ex.Message);
            }
            return ds;
        }

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

            string sp = "GetAllModuleStatusWithSlug";

            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 loginId, string password)
        {
            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", loginId);
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.String;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@sPassword", password);
            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? date;
                            if (dr[dc] == DBNull.Value)
                                date = null;
                            else
                                date = (DateTime)dr[dc];

                            objUser.DeactivationDate = date;
                        }
                        if (dc.ColumnName == "ModifierId")
                        {
                            int tempVal;
                            objUser.ModifierId = Int32.TryParse(dr[dc].ToString(), out tempVal) ? tempVal : (int?)null;
                        }
                        if (dc.ColumnName == "ModifiedDate")
                        {
                            DateTime? date;
                            if (dr[dc] == DBNull.Value)
                                date = null;
                            else
                                date = (DateTime)dr[dc];

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

                    }
                }
            }

            else
            {
                objUser = null;
            }

            if (objUser != null)
            {

                int licenseId = objModel.GetUserLicenseIdByUserId(objUser.Id);
                if (licenseId != 0)
                {
                    objUser.License = objModel.GetLicenseDetailsByLicenseId(licenseId);
                    objUser.LicenseSubscriptions = objModel.GetLicenseSubscriptionDetailsByLicenseId(licenseId);
                }
                else
                {
                    objUser.License = null;
                }

                if (objUser.UserType == User.SUPER_ADMIN || objUser.UserType == User.GENERAL_ADMIN)
                {
                    objUser.Modules = objModel.GetUserModules();
                }
                else
                {
                    if (objUser.License != null)
                    {
                        if (objUser.LicenseSubscriptions != null)
                        {
                            DateTime? subscriptionValidThrough = objUser.LicenseSubscriptions.SubscriptionValidThrough;
                            if (subscriptionValidThrough != null && subscriptionValidThrough.Value.Date >= DateTime.Now.Date)
                            {
                                ArrayList allModulesList = objModel.GetUserModules();
                                ArrayList licensedModulesList = objModel.GetModuleStatusByLicenseId(licenseId);

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

                                if (!objUser.License.IsTermAccepted)
                                { 
                                    ArrayList termsList = DBModel.GetTermsOfServiceText();
                                    foreach(Hashtable item in termsList)
                                    {
                                            objUser.TermsOfServiceTitle = item["title"].ToString();
                                            objUser.TermsOfServiceText = item["content"].ToString();
                                    }
                                }
                            }
                            else
                            {
                                objUser.IsSubscriptionExpired = true;
                                objUser.SubscriptionExpirationDateString = objUser.LicenseSubscriptions.SubscriptionValidThrough.Value.Date.ToString("MM/dd/yyyy").ToString();
                            }
                        }
                    }
                }
            }

            return objUser;
        }

        protected int GetUserLicenseIdByUserId(int userId)
        {
            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", userId);
            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 licenseId)
        {
            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", licenseId);
            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 module in allModules)
            {
                foreach (Hashtable userModule in modulesByLicense)
                {
                    if ((userModule["Title"].ToString().Trim() == module["name"].ToString().Trim()) && (Convert.ToBoolean(userModule["Status"]) == true))
                    {
                        moduleHash = new Hashtable();
                        moduleHash.Add("name", userModule["Title"]);
                        moduleHash.Add("slug", module["slug"]);

                        userModules.Add(moduleHash);
                    }
                }
            }

            return userModules;
        }

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

            switch (userTypeId)
            {
                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 emailId)
        {
            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", emailId);
                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")
                        {
                            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? date;
                            if (dr[dc] == DBNull.Value)
                                date = null;
                            else
                                date = (DateTime)dr[dc];

                            objUser.DeactivationDate = date;
                        }
                        if (dc.ColumnName == "ModifierId")
                        {
                            int tempVal;
                            objUser.ModifierId = Int32.TryParse(dr[dc].ToString(), out tempVal) ? tempVal : (int?)null;
                        }
                        if (dc.ColumnName == "ModifiedDate")
                        {
                            DateTime? date;
                            if (dr[dc] == DBNull.Value)
                                date = null;
                            else
                                date = (DateTime)dr[dc];

                            objUser.ModifiedDate = date;
                        }
                        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)
        {
            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", userInfo["loginId"].ToString());
            cmd.Parameters.AddWithValue("@sEmailId", userInfo["emailId"].ToString());
            cmd.Parameters.AddWithValue("@sNewPassword", userInfo["newPassword"].ToString());
            result = cmd.ExecuteNonQuery();
            conn.Close();
            return result;
        }

        protected LicenseSubscriptionDetails GetLicenseSubscriptionDetailsByLicenseId(int licenseId)
        {
            LicenseSubscriptionDetails lsd = new LicenseSubscriptionDetails();
            try
            {
                conn = new SqlConnection(dbConnectionString);
                cmd = new SqlCommand();
                SqlDataAdapter adapter;
                SqlParameter param;
                DataSet ds = new DataSet();

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

                param = new SqlParameter("@iLicenseId", licenseId);
                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)
                {
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dc.ColumnName == "Id")
                            lsd.Id = Convert.ToInt32(dr[dc]);
                        if (dc.ColumnName == "LicenseId")
                            lsd.LicenseId = Convert.ToInt32(dr[dc]);
                        if (dc.ColumnName == "SubscriptionPlanId")
                        {
                            int tempVal;
                            lsd.SubscriptionPlanId = Int32.TryParse(dr[dc].ToString(), out tempVal) ? tempVal : (int?)null;
                        }
                        if (dc.ColumnName == "SubscriptionValidFrom")
                        {
                            DateTime? date;
                            if (dr[dc] == DBNull.Value)
                                date = null;
                            else
                                date = (DateTime)dr[dc];

                            lsd.SubscriptionValidFrom = date;
                        }
                        if (dc.ColumnName == "SubscriptionValidThrough")
                        {
                            DateTime? date;
                            if (dr[dc] == DBNull.Value)
                                date = null;
                            else
                                date = (DateTime)dr[dc];

                            lsd.SubscriptionValidThrough = date;
                        }
                        if (dc.ColumnName == "RenewelDate")
                        {
                            DateTime? date;
                            if (dr[dc] == DBNull.Value)
                                date = null;
                            else
                                date = (DateTime)dr[dc];

                            lsd.RenewalDate = date;
                        }
                        if (dc.ColumnName == "PaymentMode")
                            lsd.PaymentMode = dr[dc].ToString();
                        if (dc.ColumnName == "TotalAmount")
                            lsd.TotalAmount = Convert.ToDouble(dr[dc]);
                        if (dc.ColumnName == "AmountPaid")
                            lsd.AmountPaid = Convert.ToDouble(dr[dc]);
                        if (dc.ColumnName == "AmountPending")
                            lsd.AmountPending = Convert.ToDouble(dr[dc]);
                        if (dc.ColumnName == "NoofImages")
                            lsd.NoOfImages = Convert.ToInt32(dr[dc]);
                    }
                }
            }
            catch (Exception ex)
            {

            }

            return lsd;
        }

        protected License GetLicenseDetailsByLicenseId(int licenseId)
        {
            License lic = new License();
            try
            {
                conn = new SqlConnection(dbConnectionString);
                cmd = new SqlCommand();
                SqlDataAdapter adapter;
                SqlParameter param;
                DataSet ds = new DataSet();

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

                param = new SqlParameter("@Id", licenseId);
                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)
                {
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dc.ColumnName == "Id")
                            lic.Id = Convert.ToInt32(dr[dc]);
                        if (dc.ColumnName == "AccountNumber")
                            lic.AccountNumber = dr[dc].ToString();
                        if (dc.ColumnName == "LicenseeFirstName")
                            lic.LicenseeFirstName = dr[dc].ToString();
                        if (dc.ColumnName == "LicenseeLastName")
                            lic.LicenseeLastName = dr[dc].ToString();
                        if (dc.ColumnName == "LicenseTypeId")
                            lic.LicenseTypeId = Convert.ToInt32(dr[dc]);
                        if (dc.ColumnName == "InstitutionName")
                            lic.InstitutionName = dr[dc].ToString();
                        if (dc.ColumnName == "Address1")
                            lic.Address1 = dr[dc].ToString();
                        if (dc.ColumnName == "Address2")
                            lic.Address2 = dr[dc].ToString();
                        if (dc.ColumnName == "CountryId")
                            lic.CountryId = Convert.ToInt32(dr[dc]);
                        if (dc.ColumnName == "StateId")
                            lic.StateId = Convert.ToInt32(dr[dc]);
                        if (dc.ColumnName == "City")
                            lic.City = dr[dc].ToString();
                        if (dc.ColumnName == "Zip")
                            lic.Zip = dr[dc].ToString();
                        if (dc.ColumnName == "Phone")
                            lic.Phone = dr[dc].ToString();
                        if (dc.ColumnName == "EmailId")
                            lic.EmailId = dr[dc].ToString();
                        if (dc.ColumnName == "TotalLogins")
                            lic.TotalLogins = Convert.ToInt32(dr[dc]);
                        if (dc.ColumnName == "AccountTypeId")
                            lic.AccountTypeId = Convert.ToInt32(dr[dc]);
                        if (dc.ColumnName == "IsActive")
                            lic.IsActive = Convert.ToBoolean(dr[dc]);
                        if (dc.ColumnName == "IsDistrictSiteLicense")
                            lic.IsDistrictSiteLicense = Convert.ToBoolean(dr[dc]);
                        if (dc.ColumnName == "CreationDate")
                            lic.CreationDate = Convert.ToDateTime(dr[dc]);
                        if (dc.ColumnName == "ModifiedDate")
                        {
                            DateTime? date;
                            if (dr[dc] == DBNull.Value)
                                date = null;
                            else
                                date = (DateTime)dr[dc];

                            lic.ModifiedDate = date;
                        }
                        if (dc.ColumnName == "NoOfRenewals")
                            lic.NoOfRenewals = Convert.ToInt32(dr[dc]);
                        if (dc.ColumnName == "IsTermsAccepted")
                            lic.IsTermAccepted = Convert.ToBoolean(dr[dc]);
                        if (dc.ColumnName == "ProductId")
                            lic.ProductId = dr[dc].ToString();
                    }
                }
            }
            catch (Exception ex)
            {

            }

            return lic;
        }

        public static int UpdateLicenseTermStatus(string accountNumber)
        {
            ILog logger = log4net.LogManager.GetLogger((System.Reflection.MethodBase.GetCurrentMethod().DeclaringType));
            logger.Debug(" inside UpdateTermAcceptedStatus for AccountNumber = " + accountNumber);
            int result = 0;
            try
            {
                conn = new SqlConnection(dbConnectionString);
                cmd = new SqlCommand();
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = "UpdateLicenseTermAcceptedStatus";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@sAccountNumber", accountNumber);
                result = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                conn.Close();
                logger.Fatal("Exception in UpdateLicenseTermStatus for AccountNumber =" + accountNumber + ", Exception= " + ex.Message);
            }
            return result;
        }

        protected static ArrayList GetTermsOfServiceText()
        {
            ArrayList arrTermsOfService = new ArrayList();
            Hashtable contentHash = null;
            string str = string.Empty;
            string spName = "GetTermsOfServiceText";
            DataSet ds = DBModel.GetSQLData(spName, true);
            DataTable dt = ds.Tables[0];

            foreach (DataRow dr in dt.Rows)
            {
                contentHash = new Hashtable();
                contentHash.Add(AIAConstants.KEY_TITLE, dr["Title"]);
                contentHash.Add(AIAConstants.KEY_CONTENT, dr["Content"]);
                arrTermsOfService.Add(contentHash);
            }
            return arrTermsOfService;
        }
    }
}