using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Runtime.CompilerServices;

namespace SQLManager
{
    public class SQLManager
    {
        private readonly SqlConnection _sqlConnection;
        private readonly Action<string> _loggerAction;

        private static readonly Type[] _SQLObjects;
        static SQLManager()
        {
            List<Type> sqlObjects = new List<Type>(); // new();

            // https://stackoverflow.com/a/851287
            Assembly[] assemblies = AppDomain.CurrentDomain.GetAssemblies();

            foreach (var assembly in assemblies)
            {
                // https://stackoverflow.com/a/1315668
                foreach (var @class in assembly.GetTypes())
                {
                    // https://stackoverflow.com/a/11227373
                    if (!@class.UnderlyingSystemType.IsSubclassOf(typeof(SQLObject)))
                        continue;

                    // Found a SQLObject
                    SQLObjectAttribute sQLObjectAttribute = @class.GetCustomAttribute<SQLObjectAttribute>();

                    if (sQLObjectAttribute.IsNameValid())
                    {
                        sqlObjects.Add(@class.UnderlyingSystemType);
                    }
                    else
                        throw new Exception($"Class named \'{@class.Name}\' was found, but the attribute {nameof(SQLObjectAttribute)} has an invalid Name.");
                }
            }
            _SQLObjects = sqlObjects.ToArray();
        }

        /// <summary>
        /// Connects the user to the database with the given ID and the given password
        /// </summary>
        /// <param name="id">ID of the account</param>
        /// <param name="password">Password of the account</param>
        public SQLManager(string dataSourceName, string databaseName, string id, string password, Action<string> loggerAction)
        {
            _sqlConnection = new SqlConnection($"Data source ={dataSourceName}; Initial Catalog ={databaseName}; User Id= {id};password ={password}");

            _loggerAction = loggerAction;


            TryOpenConnection(caller: nameof(SQLManager));

            OnLog($"SQL Manager Object created on '{dataSourceName}' ({databaseName}) by '{id}'", nameof(SQLManager));
        }

        enum CommandType
        {
            Scalar,
            NonQuery,
            Reader
        }

        /// <summary>
        /// Executes the given command with the correct method
        /// </summary>
        /// <typeparam name="T">Type of the expected value</typeparam>
        /// <param name="sqlCmd">Command to execute</param>
        /// <param name="type">Type of the command</param>
        /// <returns>Value of the given command</returns>
        private T ExecuteSQLCommand<T>(string sqlCmd, CommandType type, [CallerMemberName] string caller = "")
        {
            if (_sqlConnection != null)
                return ExecCommand<T>(new SqlCommand(sqlCmd, _sqlConnection), type, caller);

            OnLog("SQL Connection is not defined.");
            return default;
        }

        private T ExecCommand<T>(SqlCommand cmd, CommandType type, [CallerMemberName] string caller = "")
        {
            OnLog($"Request to execute the command '{cmd.CommandText}' ({type}).", caller);

            try
            {
                TryOpenConnection(true);

                object value = default;

                switch (type)
                {
                    case CommandType.Scalar:
                        value = cmd.ExecuteScalar(); // First column of first row  (min, max, count, avg)
                        break;
                    case CommandType.NonQuery:
                        value = cmd.ExecuteNonQuery(); // DML Request (Update, insert, delete)
                        break;
                    case CommandType.Reader:
                        value = cmd.ExecuteReader(); // Table
                        break;
                    default:
                        throw new NotImplementedException();
                }

                if (type != CommandType.Reader) // Needs to stay opened
                    TryCloseConnection(true);

                return (T)value;
            }
            catch (Exception e)
            {
                OnLog(e.Message);
            }
            return default;
        }

        // TODO: Make a single method that takes the command, checks if it's valid, then execute the correct commad type
        private T ExecuteScalarCommand<T>(string sqlCmd, [CallerMemberName] string caller = "")
            => ExecuteSQLCommand<T>(sqlCmd, CommandType.Scalar, caller);

        private SqlDataReader ExecuteReaderCommand(string sqlCmd, [CallerMemberName] string caller = "")
            => ExecuteSQLCommand<SqlDataReader>(sqlCmd, CommandType.Reader, caller);

        private int ExecuteNonQueryCommand(string sqlCmd, [CallerMemberName] string caller = "")
            => ExecuteSQLCommand<int>(sqlCmd, CommandType.NonQuery, caller);

        public object[] ExecuteStoredProcedure(string procName, Dictionary<string, (object, ParameterDirection)> parameters)
        {
            object[] outputsValue = null;

            try
            {
                SqlCommand cmdInsert = new SqlCommand(procName, _sqlConnection)
                {
                    CommandType = System.Data.CommandType.StoredProcedure
                };

                var parms = parameters.ToArray();

                var outputs = new List<SqlParameter>();
                bool hasReturnValue = false;

                foreach (var item in parms)
                {
                    var (value, dir) = item.Value;

                    var sqlParameter = new SqlParameter(item.Key.StartsWith("@") ? item.Key : $"@{item.Key}", value)
                    {
                        Direction = dir
                    };

                    if (sqlParameter.Direction == ParameterDirection.ReturnValue)
                        hasReturnValue = true;

                    if (sqlParameter.Direction == ParameterDirection.Output || sqlParameter.Direction == ParameterDirection.ReturnValue)
                        outputs.Add(sqlParameter);
                    cmdInsert.Parameters.Add(sqlParameter);
                }

                TryOpenConnection();

                OnLog($"Executing the stored procedure '{procName}'.");

                if (hasReturnValue)
                    cmdInsert.ExecuteScalar();
                else
                    cmdInsert.ExecuteNonQuery();

                TryCloseConnection();

                outputsValue = new object[outputs.Count];

                for (int i = 0; i < outputs.Count; ++i)
                    outputsValue[i] = outputs[i].Value;
            }
            catch (Exception e)
            {
                OnLog(e.Message);
            }
            TryCloseConnection();
            return outputsValue;
        }

        public SqlDataReader ExecuteFunction(string funcName)
        {
            return ExecuteReaderCommand($"SELECT * FROM {funcName}()");
        }

        /// <returns>Value of the given column</returns>
        public object GetValue(SqlDataReader reader, string[] headers, string propertyName, Type propertyType)
        {
            try
            {
                int index = Array.IndexOf(headers, propertyName);

                if (index == -1)
                {
                    OnLog($"Column {propertyName} not found.");
                    return default;
                }

                return reader.GetValue(index);
            }
            catch (Exception e)
            {
                OnLog(e.Message);
            }
            return default;
        }

        /// <summary>
        /// Gives the name of the SQL Object linked to the given class
        /// </summary>
        /// <returns>Name of the SQL Object</returns>
        public string GetTableName<T>()
        {
            try
            {
                int index = Array.IndexOf(_SQLObjects, typeof(T));

                if (index != -1)
                    return _SQLObjects[index].GetCustomAttribute<SQLObjectAttribute>().GetName();
            }
            catch (Exception e)
            {
                OnLog(e.Message);
                return null; // ERROR
            }
            OnLog($"Type {typeof(T)} is not supported.");
            return null; // INVALID TYPE
        }

        /// <summary>
        /// Loads all the object of the given class
        /// </summary>
        /// <remarks>If the given class has</remarks>
        public void LoadItems<T>(out List<T> loadedItems, bool sortItems = true) where T : SQLObject
        {
            string nomTable = GetTableName<T>();
            loadedItems = new List<T>();

            if (string.IsNullOrEmpty(nomTable))
            {
                OnLog($"No SQL Object has the type {typeof(T)}.");
                return;
            }

            SqlDataReader sqlRead = this.ExecuteReaderCommand($"SELECT * FROM {nomTable}");

            if (sqlRead == null)
            {
                OnLog($"No table or view named \'{nomTable}\' exists.");
                return;
            }

            LoadItems(sqlRead, out loadedItems);

            if (sortItems && Array.IndexOf(typeof(T).GetInterfaces(), typeof(IComparable)) != -1)
            {
                loadedItems.Sort();
                OnLog("Items were sorted.");
            }
        }

        public void LoadItems<T>(SqlDataReader sqlRead, out List<T> loadedItems) where T : SQLObject
        {
            loadedItems = new List<T>(); // new();

            if (sqlRead == null)
            {
                OnLog("Given SqlDataReader is not defined.");
                return;
            }

            bool headersInitialized = false;
            string[] headers = null;

            // Get fields with the attribute SQLField
            var fields = typeof(Form1.Restaurant).GetFields().ToList();
            var names = new List<string>();
            for (int i = fields.Count - 1; i >= 0; --i)
            {
                var attribute = fields[i].GetCustomAttribute<SQLFieldAttribute>();

                if (attribute == null)
                    fields.RemoveAt(i);
                else
                    names.Add(attribute.GetName());
            }

            try
            {
                while (sqlRead.Read())
                {
                    if (!headersInitialized)
                    {
                        headers = new string[sqlRead.FieldCount];
                        for (int i = 0; i < headers.Length; i++)
                        {
                            headers[i] = sqlRead.GetName(i);
                        }
                        headersInitialized = true;
                    }

                    T instance = (T)Activator.CreateInstance(typeof(T));

                    for (int i = 0; i != fields.Count; ++i)
                        fields[i].SetValue(instance, GetValue(sqlRead, headers, names[fields.Count - i - 1], fields[i].FieldType));

                    loadedItems.Add(instance);
                }
            }
            catch (Exception e)
            {
                OnLog(e.Message);
            }

            //sqlRead.Close();
            //sqlRead.Dispose();

            TryCloseConnection();
        }

        public int GetItemsCount<T>([CallerMemberName] string caller = "")
        {
            string nomTable = GetTableName<T>();
            OnLog($"Request to get the items count of '{nomTable}'.", caller);

            if (string.IsNullOrEmpty(nomTable))
            {
                OnLog($"No SQL Object has the type {typeof(T)}.");
                return -1;
            }
            return this.ExecuteScalarCommand<int>($"SELECT COUNT(*) FROM {nomTable}");
        }

        /// <summary>
        /// Updates the entry that has the same ID
        /// </summary>
        public void UpdateItem<T>(T item) where T : SQLObject
        {
            try
            {
                // https://stackoverflow.com/a/4144817
                PropertyInfo[] properties = typeof(T).GetProperties();

                PropertyInfo firstProperty = properties[0];
                // TODO: Make an abstract SQLObject and search for the ID property

                KeyValuePair<string, object> IDCLASSE = new KeyValuePair<string, object>(firstProperty.Name, firstProperty.GetValue(item));

                string tableName = GetTableName<T>();

                SqlDataReader reader = this.ExecuteReaderCommand($"SELECT * FROM {tableName} WHERE {IDCLASSE.Key} = {IDCLASSE.Value}");
                reader.Read();

                foreach (PropertyInfo prop in properties)
                {
                    object newValue = prop.GetValue(item);
                    object oldValue = reader[prop.Name];

                    // Optimisation (Limitation des packages envoyés)
                    if (!oldValue.Equals(newValue))
                    {
                        this.ExecuteNonQueryCommand($"UPDATE {tableName} SET {prop.Name} = {newValue} WHERE {IDCLASSE.Key} = {IDCLASSE.Value}");
                        OnLog($"Updated \'{prop.Name}\' from {oldValue} to {newValue}");
                    }
                }
            }
            catch (Exception e)
            {
                OnLog(e);
            }
        }

        public void OnLog(object value, [CallerMemberName] string caller = "")
        {
            string message = "";

            if (!string.IsNullOrEmpty(caller))
                message += $"[{caller}] ";

            _loggerAction?.Invoke(message + value.ToString());
        }

        /// <summary>
        /// Try to open the SQL connection
        /// </summary>
        /// <param name="silentCheck">The error message will show if the connection is already opened</param>
        private void TryOpenConnection(bool silentCheck = false, [CallerMemberName] string caller = "")
        {
            if (_sqlConnection.State != System.Data.ConnectionState.Closed && _sqlConnection.State != System.Data.ConnectionState.Broken)
            {
                if (!silentCheck)
                    OnLog($"The connection is already opened ({_sqlConnection.State}).", caller);
                return;
            }

            try
            {
                _sqlConnection.Open();
                OnLog("Successfully opened the SQL Connection.", caller);
            }
            catch (Exception e)
            {
                OnLog(e.Message, caller);
            }
        }

        public void CloseConnection([CallerMemberName] string caller = "") => TryCloseConnection(false, caller);

        /// <summary>
        /// Try to close the SQL connection
        /// </summary>
        /// <param name="silentCheck">The error message will show if the connection is already closed</param>
        private void TryCloseConnection(bool silentCheck = false, [CallerMemberName] string caller = "")
        {
            if (_sqlConnection.State == System.Data.ConnectionState.Broken ||
                _sqlConnection.State == System.Data.ConnectionState.Closed)
            {
                if (!silentCheck)
                    OnLog($"The connection is already closed ({_sqlConnection.State}).", caller);
                return;
            }

            try
            {
                _sqlConnection.Close();
                OnLog("Successfully closed the SQL Connection.", caller);
            }
            catch (Exception e)
            {
                OnLog(e.Message, caller);
            }
        }

        ~SQLManager()
        {
            TryCloseConnection(caller: "SQLManager");
        }

        /// <summary>
        /// Determines what class can be converted into a SQL object
        /// </summary>
        [AttributeUsage(AttributeTargets.Class, AllowMultiple = false, Inherited = true)]
        public class SQLObjectAttribute : Attribute
        {
            string Name { get; set; }

            public SQLObjectAttribute(string name)
            {
                Name = name;
            }

            public string GetName() => Name;

            public bool IsNameValid() => !string.IsNullOrEmpty(Name);
        }

        [AttributeUsage(AttributeTargets.Field, AllowMultiple = false)]
        public class SQLFieldAttribute : Attribute
        {
            string Name { get; set; }

            public SQLFieldAttribute(string name)
            {
                Name = name;
            }

            public string GetName() => Name;
        }

        [SQLObject(null)]
        public abstract class SQLObject
        {
            /// <summary>
            /// Field that has an unique value for every object
            /// </summary>
            public abstract object ID_OBJECT { get; }
        }
    }
}