using System.Data;
//using MySql.Data.MySqlClient;

using MySqlConnector;
//using MySqlConnection = MySqlConnector;

using Microsoft.Extensions.Configuration;
using System.Configuration;

/*
 * 2023-10-23
 * Se agregan mejoras en el retorno de datos (if (p.Value != DBNull.Value)) ya que de inicio parecia que mysqlclient no permite 
 * regresar un tipo null, se valida y seria de mejor forma y buena practica regresar un tipo vacio  (DataSet emptyDataSet = new DataSet(); return emptyDataSet;)
 * en lugar de solo un null. (se puede regresar a la logica que se trae solo esto es una opcion de mejora).
 */

namespace servicesDAO
{
    public class DataAccess
    {

        #region atributos
        public DateTime EmptyDate = new DateTime(1900, 1, 1);
        private int timeout = 900;
        #endregion

        #region propiedades
        public MySqlConnection Connection { get; }

        public string ConnectionString { get { return Connection.ConnectionString; } }

        #endregion

        #region constructores
        public DataAccess(string connectionString)
        {
            Connection = new MySqlConnection(connectionString);
        }
        public DataAccess()
        {
            String str = System.Configuration.ConfigurationManager.ConnectionStrings["Main"].ConnectionString;
            Connection = new MySqlConnection(str);

            /*IConfigurationRoot configuration = new ConfigurationBuilder()
            .SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
            .AddJsonFile("appsettings.json")
            .Build();
            String str = configuration.GetConnectionString("Main");   

            Connection = new MySqlConnection(str); */
        }
        #endregion

        #region métodos
        private void GetStatusConnection(MySqlConnection connection)
        {
            using (connection = new MySqlConnection(ConnectionString))
            {
                String message = String.Empty;
                switch (connection.State)
                {
                    case System.Data.ConnectionState.Open:
                        message = "\n >>>>>>>>> Successful connection <<<<<<<<<<";
                        Console.WriteLine(message);
                        break;

                    case System.Data.ConnectionState.Closed:
                        message = "\n >>>>>>>>> Error! The database connection state is Closed <<<<<<<<<<";
                        throw new Exception(message);
                        break;

                    default:
                        message = connection.State.ToString();
                        Console.WriteLine(message);
                        break;
                }
            }
        }
        public void ExecuteMySqlCommand(string sSQL, MySqlParameter[] parameters)
        {
            using (MySqlConnection mySQLConn = new MySqlConnection(ConnectionString))
            using (MySqlCommand mySQLCommd = new MySqlCommand())
            {
                try
                {
                    mySQLConn.Open();
                    mySQLCommd.Connection = mySQLConn;
                    mySQLCommd.CommandText = sSQL;
                    mySQLCommd.CommandType = CommandType.Text;
                    if (parameters != null)
                        foreach (MySqlParameter p in parameters)
                            mySQLCommd.Parameters.Add(p);
                    mySQLCommd.ExecuteNonQuery();
                    mySQLConn.Close();
                }
                catch (MySqlException eMySql)
                {
                    Console.WriteLine("ERROR CATCH \n");
                    Console.WriteLine(eMySql);
                }
            }
        }

        public void ExecuteStoreProcedure(string pStoreProcedure, MySqlParameter[] pParameters, int iTimeOut)
        {
            using (MySqlConnection mySQLConn = new MySqlConnection(ConnectionString))
            using (MySqlCommand mySQLCommd = new MySqlCommand())
            {
                mySQLConn.Open();
                mySQLCommd.Connection = mySQLConn;
                mySQLCommd.CommandText = pStoreProcedure;
                mySQLCommd.CommandType = CommandType.StoredProcedure;
                mySQLCommd.CommandTimeout = iTimeOut;
                if (pParameters != null)
                    foreach (MySqlParameter p in pParameters)
                        mySQLCommd.Parameters.Add(p);
                mySQLCommd.ExecuteNonQuery();
                mySQLConn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="nameStoreProcedure"></param>
        /// <returns></returns>
        public List<String> GetParametersFromSP(string nameStoreProcedure)
        {
            List<String> lstResult = new List<string>();
            DataSet dataset = new DataSet();
            using (MySqlConnection mySQLConn = new MySqlConnection(ConnectionString))
            using (MySqlCommand mySQLCommd = new MySqlCommand())
            {
                mySQLConn.Open();
                mySQLCommd.CommandTimeout = timeout;
                mySQLCommd.Connection = mySQLConn;
                mySQLCommd.CommandText = nameStoreProcedure;
                mySQLCommd.CommandType = CommandType.StoredProcedure;
                MySqlCommandBuilder.DeriveParameters(mySQLCommd);
                foreach (MySqlParameter p in mySQLCommd.Parameters)
                    if (p.Value != DBNull.Value)
                    {
                        lstResult.Add(p.ParameterName);
                    }
                    else
                    {
                        // Maneja el caso en que el valor de la base de datos es nulo
                        lstResult.Add("Valor nulo");
                    }
            }
            return lstResult;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="nameStoreProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet GetDataSetFromStoreProcedure(string nameStoreProcedure, MySqlParameter[] parameters)
        {
            DataSet dataset = new DataSet();
            using (MySqlConnection mySQLConn = new MySqlConnection(ConnectionString))
            using (MySqlCommand mySQLCommd = new MySqlCommand())
            {
                mySQLConn.Open();
                GetStatusConnection(mySQLConn);
                MySqlDataAdapter mySqlAdapter;
                mySQLCommd.Connection = mySQLConn;
                mySQLCommd.CommandText = nameStoreProcedure;
                mySQLCommd.CommandType = CommandType.StoredProcedure;
                if (parameters != null)
                    foreach (MySqlParameter p in parameters)
                        mySQLCommd.Parameters.Add(p);
                mySqlAdapter = new MySqlDataAdapter(mySQLCommd);
                mySqlAdapter.Fill(dataset);
                mySQLConn.Close();
            }
            //return dataset;
            if (dataset.Tables.Count == 0 || dataset.Tables.Cast<DataTable>().All(t => t.Rows.Count == 0))
            {
                // Crea un DataSet vacío
                DataSet emptyDataSet = new DataSet();
                return emptyDataSet;
            }

            return dataset;
        }

        public DataTable GetDataTableFromSQLCommand(string commandText)
        {
            DataSet dataset = new DataSet();
            using (MySqlConnection mySQLConn = new MySqlConnection(ConnectionString))
            using (MySqlCommand mySQLCommd = new MySqlCommand())
            {
                mySQLConn.Open();
                MySqlDataAdapter mySqlAdapter;
                mySQLCommd.Connection = mySQLConn;
                mySQLCommd.CommandText = commandText;
                mySQLCommd.CommandType = CommandType.Text;
                mySqlAdapter = new MySqlDataAdapter(mySQLCommd);
                mySqlAdapter.Fill(dataset);
                mySQLConn.Close();
            }
            //return dataset.Tables[0];
            if (dataset.Tables.Count > 0 && dataset.Tables[0].Rows.Count > 0)
            {
                return dataset.Tables[0];
            }
            else
            {
                DataTable emptyTable = new DataTable(); // Crea una tabla vacía
                return emptyTable;
            }
        }

        public DataTable GetDataTableFromStoreProcedure(string nameStoreProcedure, MySqlParameter[] parameters)
        {
            DataSet dataset = new DataSet();
            using (MySqlConnection mySQLConn = new MySqlConnection(ConnectionString))
            using (MySqlCommand mySQLCommd = new MySqlCommand())
            {
                try {
                    mySQLConn.Open();
                    MySqlDataAdapter mySqlAdapter;
                    mySQLCommd.CommandTimeout = timeout;
                    mySQLCommd.Connection = mySQLConn;
                    mySQLCommd.CommandText = nameStoreProcedure;
                    mySQLCommd.CommandType = CommandType.StoredProcedure;
                    
                    if (parameters != null)
                        foreach (MySqlParameter p in parameters)
                            mySQLCommd.Parameters.AddWithValue(p.ParameterName, p.Value);
                    mySqlAdapter = new MySqlDataAdapter(mySQLCommd);
                    mySqlAdapter.Fill(dataset);               
                    mySQLConn.Close();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error al abrir la conexión: " + ex.Message);
                }
            }
            //return dataset.Tables.Count == 0 ? null : dataset.Tables[0];
            if (dataset.Tables.Count > 0 && dataset.Tables[0].Rows.Count > 0)
            {
                return dataset.Tables[0];
            }
            else
            {
                DataTable emptyTable = new DataTable(); // Crea una tabla vacía
                return emptyTable;
            }
        }

        #endregion

    }
}