using System.IO;
using System.Data;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using Microsoft.Extensions.Configuration;
using System;
using System.Reflection;

namespace Ado.Net
{
    public class MySqlHelper
    {
        public string ConnectionString { get; }

        public MySqlHelper()
        {
            var build = new ConfigurationBuilder();
            build.SetBasePath(Directory.GetCurrentDirectory());
            build.AddJsonFile("appsettings.json", true, true);

            ConnectionString = build.Build()["ConnectionStrings:MySql"];
        }

        #region ExecuteNonQuery

        public int ExecuteNonQuery(string commandText)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand command = new MySqlCommand(commandText, connection))
                {
                    connection.Open();

                    return command.ExecuteNonQuery();
                }
            }
        }

        public int ExecuteNonQuery(string commandText, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand command = new MySqlCommand(commandText, connection))
                {
                    command.Parameters.AddRange(parameters);

                    connection.Open();

                    return command.ExecuteNonQuery();
                }
            }
        }

        #endregion ExecuteNonQuery

        #region ExecuteReader

        public MySqlDataReader ExecuteReader(string commandText)
        {
            MySqlConnection connection = new MySqlConnection(ConnectionString);
            MySqlCommand command = new MySqlCommand(commandText, connection);

            connection.Open();

            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public MySqlDataReader ExecuteReader(string commandText, params MySqlParameter[] parameters)
        {
            MySqlConnection connection = new MySqlConnection(ConnectionString);
            MySqlCommand command = new MySqlCommand(commandText, connection);

            command.Parameters.AddRange(parameters);

            connection.Open();

            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        #endregion ExecuteReader

        #region ExecuteDataSet

        public DataSet ExecuteDataSet(string commandText)
        {
            using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(commandText, ConnectionString))
            {
                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                return dataSet;
            }
        }

        public DataSet ExecuteDataSet(string commandText, params MySqlParameter[] parameters)
        {
            using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(commandText, ConnectionString))
            {
                DataSet dataSet = new DataSet();
                dataAdapter.SelectCommand.Parameters.AddRange(parameters);
                dataAdapter.Fill(dataSet);
                return dataSet;
            }
        }

        #endregion ExecuteDataSet

        #region ExecuteDataTable

        public DataTable ExecuteDataTable(string commandText)
        {
            using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(commandText, ConnectionString))
            {
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                return dataTable;
            }
        }

        public DataTable ExecuteDataTable(string commandText, params MySqlParameter[] parameters)
        {
            using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(commandText, ConnectionString))
            {
                DataTable dataTable = new DataTable();
                dataAdapter.SelectCommand.Parameters.AddRange(parameters);
                dataAdapter.Fill(dataTable);
                return dataTable;
            }
        }

        #endregion ExecuteDataTable

        #region ExecuteScalar

        public object ExecuteScalar(string commandText)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand command = new MySqlCommand(commandText, connection))
                {
                    connection.Open();

                    return command.ExecuteScalar();
                }
            }
        }

        public object ExecuteScalar(string commandText, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand command = new MySqlCommand(commandText, connection))
                {
                    command.Parameters.AddRange(parameters);

                    connection.Open();

                    return command.ExecuteScalar();
                }
            }
        }

        #endregion ExecuteScalar

        #region ExecuteTransaction

        public int ExecuteTransaction(List<string> list)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand command = new MySqlCommand())
                {
                    connection.Open();

                    MySqlTransaction transaction = connection.BeginTransaction();

                    command.Connection = connection;
                    command.Transaction = transaction;

                    try
                    {
                        int result = 0;

                        foreach (var item in list)
                        {
                            command.CommandText = item;

                            result = command.ExecuteNonQuery();
                        }

                        transaction.Commit();

                        return result;
                    }
                    catch (System.Exception)
                    {
                        transaction.Rollback();

                        return 0;
                    }
                }
            }
        }

        public int ExecuteTransaction(List<KeyValuePair<string, MySqlParameter[]>> list)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand command = new MySqlCommand())
                {
                    connection.Open();

                    MySqlTransaction transaction = connection.BeginTransaction();

                    command.Connection = connection;
                    command.Transaction = transaction;

                    try
                    {
                        int result = 0;

                        foreach (var item in list)
                        {
                            command.CommandText = item.Key;
                            command.Parameters.Clear();
                            command.Parameters.AddRange(item.Value);

                            result = command.ExecuteNonQuery();
                        }

                        transaction.Commit();

                        return result;
                    }
                    catch (System.Exception)
                    {
                        transaction.Rollback();

                        return 0;
                    }
                }
            }
        }

        #endregion ExecuteTransaction

        #region ExecuteMySqlScript

        public int ExecuteMySqlScript(string path)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand command = new MySqlCommand())
                {
                    using (StreamReader streamReader = new StreamReader(path, System.Text.Encoding.UTF8))
                    {
                        command.Connection = connection;
                        command.CommandText = streamReader.ReadToEnd();
                        connection.Open();

                        return command.ExecuteNonQuery();
                    }
                }
            }
        }

        #endregion ExecuteMySqlScript

        #region GetEntities

        public List<T> GetEntities<T>(string commandText)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand command = new MySqlCommand(commandText, connection))
                {
                    connection.Open();

                    using (MySqlDataReader dataReader = command.ExecuteReader())
                    {
                        List<T> list = new List<T>();

                        while (dataReader.Read())
                        {
                            List<string> field = new List<string>(dataReader.FieldCount);

                            for (int i = 0; i < dataReader.FieldCount; i++)
                            {
                                field.Add(dataReader.GetName(i).ToLower());
                            }

                            T model = Activator.CreateInstance<T>();

                            foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
                            {
                                if (field.Contains(property.Name.ToLower()))
                                {
                                    property.SetValue(model, Convert.ChangeType(dataReader[property.Name], property.PropertyType), null);
                                }
                            }

                            list.Add(model);
                        }

                        return list;
                    }
                }
            }
        }

        public List<T> GetEntities<T>(string commandText, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand command = new MySqlCommand(commandText, connection))
                {
                    connection.Open();

                    command.Parameters.AddRange(parameters);

                    using (MySqlDataReader dataReader = command.ExecuteReader())
                    {
                        List<T> list = new List<T>();

                        while (dataReader.Read())
                        {
                            List<string> field = new List<string>(dataReader.FieldCount);

                            for (int i = 0; i < dataReader.FieldCount; i++)
                            {
                                field.Add(dataReader.GetName(i).ToLower());
                            }

                            T model = Activator.CreateInstance<T>();

                            foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
                            {
                                if (field.Contains(property.Name.ToLower()))
                                {
                                    property.SetValue(model, Convert.ChangeType(dataReader[property.Name], property.PropertyType), null);
                                }
                            }

                            list.Add(model);
                        }

                        return list;
                    }
                }
            }
        }

        #endregion GetEntities

        #region GetEntity

        public T GetEntity<T>(string commandText)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand command = new MySqlCommand(commandText, connection))
                {
                    connection.Open();

                    using (MySqlDataReader dataReader = command.ExecuteReader())
                    {
                        T model = Activator.CreateInstance<T>();

                        if (dataReader.Read())
                        {
                            List<string> field = new List<string>(dataReader.FieldCount);

                            for (int i = 0; i < dataReader.FieldCount; i++)
                            {
                                field.Add(dataReader.GetName(i).ToLower());
                            }

                            foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
                            {
                                if (field.Contains(property.Name.ToLower()))
                                {
                                    property.SetValue(model, Convert.ChangeType(dataReader[property.Name], property.PropertyType), null);
                                }
                            }
                        }

                        return model;
                    }
                }
            }
        }

        public T GetEntity<T>(string commandText, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand command = new MySqlCommand(commandText, connection))
                {
                    connection.Open();

                    command.Parameters.AddRange(parameters);

                    using (MySqlDataReader dataReader = command.ExecuteReader())
                    {
                        T model = Activator.CreateInstance<T>();

                        if (dataReader.Read())
                        {
                            List<string> field = new List<string>(dataReader.FieldCount);

                            for (int i = 0; i < dataReader.FieldCount; i++)
                            {
                                field.Add(dataReader.GetName(i).ToLower());
                            }

                            foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
                            {
                                if (field.Contains(property.Name.ToLower()))
                                {
                                    property.SetValue(model, Convert.ChangeType(dataReader[property.Name], property.PropertyType), null);
                                }
                            }
                        }

                        return model;
                    }
                }
            }
        }

        #endregion GetEntity
    }
}