using Google.Protobuf.WellKnownTypes;
using MySql.Data.MySqlClient;
using MySqlX.XDevAPI.Relational;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Serialization;
using Org.BouncyCastle.Asn1.X509;
using servicesDAO;
using System.Data;
using System.Xml.Linq;

namespace server
{
    public class ServicesDAO
    {
        protected DataAccess data = new DataAccess();

        #region funciones
        /// <summary>
        /// Método que convierte una tabla DataTable en una cadena  de texto formato JSON
        /// </summary>
        /// <param name="dataTable">Tabla de datos a convertir</param>
        /// <returns>Cadena de texto en formato JSON</returns>
        public string DataTableToJson(DataTable dataTable)
        {
            string JSONString = string.Empty;
            JSONString = Newtonsoft.Json.JsonConvert.SerializeObject(dataTable);
            return JSONString;
        }

        public DataTable JsonToDataTable(string json)
        {
            DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));
            return dt;
        }

        private List<String> GetParametersfromSP(string nameSP)
        {
            List<String> listParameters = new List<String>();
            listParameters = GetParametersfromSP(nameSP);
            return listParameters;
        }

        /// <summary>
        /// Método dinámico que consulta en BD en base a parámetros tabla y cláusula condicional
        /// </summary>
        /// <param name="tab_name"> nombre de la tabla (DESPUES DEL FROM) o se puede complementar con JOINS completos</param>
        /// <param name="wclause"> sentencia condicional después de la palabra reservada WHERE</param>
        /// <returns>retorna JSON con resultados de la consulta</returns>
        public String getDataTablefromClause(string tab_name, string wclause, string wsent)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[3];
                _param[0] = new MySqlParameter("@tab_name", tab_name);
                _param[1] = new MySqlParameter("@wcol_name", wclause);
                _param[2] = new MySqlParameter("@w_sent", wsent);


                DataTable dtData = data.GetDataTableFromStoreProcedure("cloud_services.spskDy_tablefromclause", _param);
                if (dtData.Rows.Count > 0)
                {
                    string jsonString = DataTableToJson(dtData);
                    return jsonString;
                }
                else { return ""; }
            }
            catch (Exception e)
            { return e.Message; }
        }
        public String getTemporalContractedServiceID()
        {
            try
            {
                
                DataTable dtData = data.GetDataTableFromStoreProcedure("cloud_services.tmp_contracted_services_id", null);
                if (dtData.Rows.Count > 0)
                {
                    string jsonString = DataTableToJson(dtData);
                    return jsonString;
                }
                else { return ""; }
            }
            catch (Exception e)
            { return e.Message; }
        }
        #endregion

        #region métodos de data

        #region data
        /// <summary>
        /// Método que obtiene las categorias de ofertas disponibles
        /// </summary>
        /// <param name="idProductType">IdCategoria, puede ir nulo</param>
        /// <returns>retorna una cadena de texto formato json</returns>
        public DataTable? getProductType(Int32 idProductType = 0)
        {
            try
            {
                MySqlParameter[] parameters = {
                    new MySqlParameter("@idProductType", idProductType > 0 ? idProductType : null) };
                DataTable dtCategories = data.GetDataTableFromStoreProcedure("cloud_services.spslGetProductType", parameters);
                if (dtCategories.Rows.Count > 0)
                    return dtCategories;
                else return null;
            }
            catch (Exception e)
            { return null; }
        }

        /// <summary>
        ///Método que obtiene las categorias de ofertas disponibles
        /// </summary>
        /// <param name="idProductType">IdCategoria, puede ir nulo</param>
        /// <returns>Retorna el resultado en tipo DataTable</returns>
        public DataTable getProductTypeAsDataTable(Int32 idProductType = 0)
        {
            try
            {
                MySqlParameter[] parameters = {
                    new MySqlParameter("@idProductType", idProductType>0 ? idProductType: null) };
                DataTable dtCategories = data.GetDataTableFromStoreProcedure("cloud_services.spslGetProductType", parameters);
                if (dtCategories.Rows.Count > 0)
                {
                    return dtCategories;
                }
                else { return null; }
            }
            catch (Exception e)
            { return null; }
        }

        /// <summary>
        /// Método que obtiene el listado de servicios según categoría seleccionada
        /// </summary>
        /// <param name="idProductType">IdCategoria</param>
        /// <returns>Retorna el listado de servicios en una cadena de texto formato JSON</returns>
        public String getServicesCatalog(Int32 idProductType = 0)
        {
            try
            {
                MySqlParameter[] parameters = {
                    new MySqlParameter("@product_type_id", idProductType > 0 ? idProductType : null) };
                DataTable dataServicesCatalog = data.GetDataTableFromStoreProcedure("cloud_services.spslGetServicesByTypeId", parameters);
                if (dataServicesCatalog.Rows.Count > 0)
                {
                    string jsonString = DataTableToJson(dataServicesCatalog);
                    return jsonString;
                }
                else { return ""; }
            }
            catch (Exception e)
            { return e.Message; }
        }

        public DataTable? getPlansService(Int32 idService = 0)
        {
            try
            {
                MySqlParameter[] parameters = {
                    new MySqlParameter("@idService", idService>0 ? idService : null)
                };
                DataTable dataPlansService = data.GetDataTableFromStoreProcedure("cloud_services.spslGetPlansServicesById", parameters);
                if (dataPlansService.Rows.Count > 0)
                    return dataPlansService;
                else { return null; }
            }
            catch (Exception e)
            { return null; }

        }

        public string getServicesAllContracted()
        {
            try
            {

                DataTable dataPlansService = data.GetDataTableFromStoreProcedure("cloud_services.spslGetAllServicesContracted", null);
                if (dataPlansService.Rows.Count > 0)
                {
                    string jsonString = DataTableToJson(dataPlansService);
                    return jsonString;
                }
                else { return ""; }
            }
            catch (Exception e)
            { return e.Message; }

        }

        public string getIpServiceById(Int32 idFirewall = 0)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlParameter[] parameters = {
                    new MySql.Data.MySqlClient.MySqlParameter("@idFirewall", idFirewall>0 ? idFirewall: null)
                };

                DataTable dataPlansService = data.GetDataTableFromStoreProcedure("cloud_services.spslGetIpServiceById", parameters);
                if (dataPlansService.Rows.Count > 0)
                {
                    string jsonString = DataTableToJson(dataPlansService);
                    return jsonString;
                }
                else { return ""; }
            }
            catch (Exception e)
            { return e.Message; }

        }

        public string setContractedServices(Int32 user_id = 0,
            Int32 backup_scheduled_id = 0, Int32 plan_id = 0,
            Int32 firewall_id = 0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[4];
                _param[0] = new MySqlParameter("@user_id", user_id );
                _param[1] = new MySqlParameter("@backup_scheduled_id", backup_scheduled_id > 0 ? backup_scheduled_id : null);
                _param[2] = new MySqlParameter("@plan_id", plan_id);
                _param[3] = new MySqlParameter("@firewall_id", firewall_id > 0 ? firewall_id : null);


                DataTable dataService = data.GetDataTableFromStoreProcedure("cloud_services.spinIntoContractedServices", _param);
                if (dataService.Rows.Count > 0)
                {
                    int value = 0;
                    if ((Int32.TryParse(dataService.Rows[0][0].ToString(), out value)) && value > 0)
                    {
                        string jsonString = DataTableToJson(dataService);
                        return jsonString;
                    }
                    else
                        throw new Exception("Couldn't get service information ID");

                }
                else { throw new Exception("Couldn't save service information"); }
            }
            catch (Exception e)
            { return e.Message; }

        }
        public string setFirewall(string ips = "", bool is_segment =false, Int32 firewall_id = 0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[3];
                _param[0] = new MySqlParameter("@ips", ips);
                _param[1] = new MySqlParameter("@is_segment", is_segment);
                _param[2] = new MySqlParameter("@firewall_id", firewall_id > 0 ? firewall_id : null);


                DataTable dataPlansService = data.GetDataTableFromStoreProcedure("cloud_services.spinInsertIntoFirewall", _param);
                if (dataPlansService.Rows.Count > 0)
                {
                    string jsonString = DataTableToJson(dataPlansService);
                    return jsonString;
                }
                else { return ""; }
            }
            catch (Exception e)
            { return e.Message; }
        }

        public void setRequestQueue (int contracted_services_id, string resource_specs, DateTime request_datetime,
            string identificator, string name_app)
        {//spinInsertIntoRequestsQueue

        }

        public DataTable? getUserfromUserName(string userName = null, Int32 public_id = 0, Int32 user_id = 0,
            Int32 org_id = 0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[4];
                _param[0] = new MySqlParameter("@user_name", userName);
                _param[1] = new MySqlParameter("@public_id", public_id > 0 ? public_id : null);
                _param[2] = new MySqlParameter("@user_id", user_id > 0 ? user_id : null);
                _param[3] = new MySqlParameter("@org_id", org_id > 0 ? org_id : null);
                DataTable dataService = data.GetDataTableFromStoreProcedure("cloud_services.spskGetUserVCD", _param);
                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                    throw new Exception("Couldn't get user information");

            }
            catch (Exception e)
            { return null; }
        }

        public string setUserVcd(Int32 user_id, string username, string email, Int32 orgid)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[4];
                _param[0] = new MySqlParameter("@userID", user_id);
                _param[1] = new MySqlParameter("@username", username);
                _param[2] = new MySqlParameter("@email", email);
                _param[3] = new MySqlParameter("@org_id", orgid);
                DataTable dataService = data.GetDataTableFromStoreProcedure("cloud_services.spinSetUserVCD", _param);
                if (dataService.Rows.Count > 0)
                {
                    int value = 0;
                    if ((Int32.TryParse(dataService.Rows[0][0].ToString(), out value)) && value > 0)
                    {
                        string jsonString = DataTableToJson(dataService);
                        return jsonString;
                    }
                    else
                        throw new Exception("Couldn't find organization information");
                }
                else
                    throw new Exception("Couldn't get user information");

            }
            catch (Exception e)
            { return e.Message; }
        }

        public DataTable? getOrgfromName(string name=null, Int32 OrgId = 0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[2];
                _param[0] = new MySqlParameter("@org_name", String.IsNullOrEmpty(name)==true ? null :  name);
                _param[1] = new MySqlParameter("@org_id", OrgId > 0 ? OrgId : null);
                DataTable dataService = data.GetDataTableFromStoreProcedure("cloud_services.spskGetOrgVCD", _param);
                if (dataService.Rows.Count > 0)                
                    return dataService;                  
                else
                    throw new Exception("Couldn't get organization information");
            }
            catch (Exception e)
            { return null; }
        }

        public string setOrgVcd(Int32 orgID, string orgname, string host, string apiversion )
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[4];
                _param[0] = new MySqlParameter("@orgID", orgID);
                _param[1] = new MySqlParameter("@org_name", orgname);
                _param[2] = new MySqlParameter("@vhost", host);
                _param[3] = new MySqlParameter("@api_version", apiversion);
                DataTable dataService = data.GetDataTableFromStoreProcedure("cloud_services.spinSetOrgVCD", _param);
                if (dataService.Rows.Count > 0)
                {
                    int value = 0;
                    if ((Int32.TryParse(dataService.Rows[0][0].ToString(), out value)) && value > 0)
                    {
                        string jsonString = DataTableToJson(dataService);
                        return jsonString;
                    }
                    else
                        throw new Exception("Couldn't save organization information");
                }
                else
                    throw new Exception("Couldn't save organization information");

            }
            catch (Exception e)
            { return e.Message; }
        }
        #endregion

        #region axway
        /// <summary>
        /// Método para almacenar en tabla Licenses
        /// </summary>
        /// <param name="specs">especificaciones en formato json tipo string</param>
        /// <param name="contracted_services_id">id del servicio contratado tipo entero</param>
        /// <param name="name">nombre de la organización tipo string</param>
        /// <returns>retorna idlicenses en formato json</returns>
        public string saveLicenses(string specs, int contracted_services_id, string name)
        {
            try
            {

                MySqlParameter[] _param = new MySqlParameter[3];
                _param[0] = new MySqlParameter("@nameValue", name);
                _param[1] = new MySqlParameter("@specs", specs);
                _param[2] = new MySqlParameter("@contracted_services_id", contracted_services_id);

                DataTable dataService = data.GetDataTableFromStoreProcedure("cloud_services.spinNewLicense", _param);
                int value = 0;
                if (dataService.Rows.Count > 0)
                {
                    if ((Int32.TryParse(dataService.Rows[0][0].ToString(), out value)) && value > 0)
                    {
                        string jsonString = DataTableToJson(dataService);
                        return jsonString;
                    }
                    else
                        throw new Exception("Couldn't get license information");
                }
                else
                    throw new Exception("Couldn't save license information");

            }
            catch (Exception e)
            { return e.Message; }
        }

        private string getParametersReport(string user_id)
        {
            String result = String.Empty;
            try
            {
                MySqlParameter[] _param = new MySqlParameter[1];
                _param[0] = new MySqlParameter("@userid", user_id);

                DataTable dataPlansService = data.GetDataTableFromStoreProcedure("cloud_services.getInformationReport", _param);
                if (dataPlansService.Rows.Count > 0)
                {
                     result = DataTableToJson(dataPlansService);
                    
                }
                else
                    throw new Exception("Couldn't find data history information");

            }

            catch (Exception e)
            { result = e.Message; }

            return result;
        }

        #endregion

        #endregion
    }

}