using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Reflection;

namespace Deltares.Soilbase
{
    public class Database
    {
        public Database(string name)
        {
            if (string.IsNullOrWhiteSpace(name))
            {
                throw new ArgumentException("name");
            }

            if (!File.Exists(name))
            {
                throw new FileNotFoundException("File not found", name);
            }

            Name = name;
        }

        /// <summary>
        /// Gets or sets the name.
        /// </summary>
        /// <value>
        /// The name.
        /// </value>
        public string Name { get; private set; }

        /// <summary>
        /// Creates a new database using the initial database which is stored as an embedded resource in this assembly.
        /// </summary>
        /// <param name="fileName">File name and path to write the database to.</param>
        /// <returns>An initialized database object</returns>
        public static Database Create(string fileName)
        {
            if (string.IsNullOrWhiteSpace(fileName))
            {
                throw new ArgumentNullException("fileName");
            }

            if (File.Exists(fileName))
            {
                throw new ArgumentException("File already exists");
            }

            Type type = typeof(Database);
            CreateFile(type.Assembly, type.Namespace + ".blank.mdb", fileName);

            return new Database(fileName);
        }

        /// <summary>
        /// Gets the update command.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnNames">The column names.</param>
        /// <returns></returns>
        public Action<IEnumerable<KeyValuePair<string, object>>> GetUpdateCommand(string tableName, params string[] columnNames)
        {
            ThrowIfTableNameIsEmpty(tableName);

            if (columnNames.Length <= 1)
            {
                throw new ArgumentException("No attributes specified. There should be at least two attributes. One column and one id field and their values");
            }

            var commandText = CreateUpdateCommandText(tableName, columnNames);

            return attributes =>
                {
                    ThrowIfAtrributesDontMatch(columnNames, attributes);

                    ExecuteNonQuery((cmd) =>
                        {
                            cmd.CommandText = commandText;

                            // add named parameters
                            cmd.Parameters.AddRange(CreateParameterArray(attributes));
                        });
                };
        }

        /// <summary>
        /// Gets the insert command.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnNames">The column names.</param>
        /// <returns></returns>
        public Func<IEnumerable<KeyValuePair<string, object>>, T> GetInsertCommand<T>(string tableName, params string[] columnNames)
        {
            ThrowIfTableNameIsEmpty(tableName);

            if (columnNames.Length == 0)
            {
                throw new ArgumentException("No column names specified. There should be at least one table column name specified.");
            }

            var commandText = CreateInsertCommandText(tableName, columnNames);

            return attributes =>
                {
                    ThrowIfAtrributesDontMatch(columnNames, attributes);

                    object id = null;

                    ExecuteOleDbCommand((cmd) =>
                        {
                            cmd.CommandText = commandText;

                            // add named parameters
                            cmd.Parameters.AddRange(CreateParameterArray(attributes));

                            cmd.ExecuteNonQuery();

                            cmd.CommandText = "SELECT @@identity";
                            OleDbDataReader dr = cmd.ExecuteReader();
                            while (dr != null && dr.Read())
                            {
                                id = dr[0];
                            }
                        });

                    return (T) id;
                };
        }

        public int GetRecordCount(string tableName)
        {
            int count = 0;

            ExecuteOleDbCommand(cmd =>
                {
                    cmd.CommandText = string.Format("SELECT COUNT(*) FROM [{0}]", tableName);

                    cmd.ExecuteNonQuery();

                    OleDbDataReader dr = cmd.ExecuteReader();
                    while (dr != null && dr.Read())
                    {
                        count = (int) dr[0];
                    }
                });

            return count;
        }

        /// <summary>
        /// Gets the connection string.
        /// </summary>
        protected internal virtual string ConnectionString
        {
            get
            {
                return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", Name);
            }
        }

        /// <summary>
        /// Executes a command on the database.
        /// </summary>
        /// <param name="action">The action.</param>
        protected internal virtual void ExecuteCommand(Action<OleDbConnection> action)
        {
            using (var conn = new OleDbConnection(ConnectionString))
            {
                conn.Open();
                action(conn);
            }
        }

        /// <summary>
        /// Executes a ole db command.
        /// </summary>
        /// <param name="action">The action.</param>
        protected internal virtual void ExecuteOleDbCommand(Action<OleDbCommand> action)
        {
            ExecuteCommand((conn) =>
                {
                    using (OleDbCommand cmd = conn.CreateCommand())
                    {
                        action(cmd);
                    }
                });
        }

        /// <summary>
        /// Executes a non query oledb command.
        /// </summary>
        /// <param name="action">The action.</param>
        protected internal virtual void ExecuteNonQuery(Action<OleDbCommand> action)
        {
            ExecuteOleDbCommand(cmd =>
                {
                    action(cmd);
                    cmd.ExecuteNonQuery();
                });
        }

        /// <summary>
        /// Executes the non query batch. (not tested yet...)
        /// </summary>
        /// <param name="actions">The actions.</param>
        protected internal virtual void ExecuteNonQueryBatch(IEnumerable<Action<OleDbCommand>> actions)
        {
            ExecuteCommand((conn) =>
                {
                    foreach (var action in actions)
                    {
                        using (OleDbCommand cmd = conn.CreateCommand())
                        {
                            action(cmd);
                            cmd.ExecuteNonQuery();
                        }
                    }
                });
        }

        /// <summary>
        /// Gets the data table.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <returns></returns>
        protected internal DataTable GetDataTable(string tableName)
        {
            ThrowIfTableNameIsEmpty(tableName);

            var dataTable = new DataTable(tableName);

            ExecuteCommand((conn) =>
                {
                    var query = CreateSelectCommandText(tableName);
                    var adapter = new OleDbDataAdapter(query, conn);
                    adapter.Fill(dataTable);
                });

            return dataTable;
        }

        /// <summary>
        /// Throws if table name is empty.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        internal static void ThrowIfTableNameIsEmpty(string tableName)
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                throw new ArgumentException("tableName");
            }
        }

        /// <summary>
        /// Throws if atrributes dont match.
        /// </summary>
        /// <param name="columnNames">The column names.</param>
        /// <param name="attributes">The attributes.</param>
        internal static void ThrowIfAtrributesDontMatch(string[] columnNames, IEnumerable<KeyValuePair<string, object>> attributes)
        {
            int attrCount = 0;
            foreach (var attribute in attributes)
            {
                if (!columnNames.Contains(attribute.Key))
                {
                    string message = string.Format("Invalid attribute list. The attribute '{0}' is not specified in the set of column names",
                                                   attribute.Key);

                    throw new ArgumentException(message);
                }

                attrCount++;
            }

            if (attrCount != columnNames.Length)
            {
                throw new ArgumentException(
                    "Invalid attribute list. The length of the attribute list is not the same as list length of the column names");
            }
        }

        /// <summary>
        /// Creates the select command text.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <returns></returns>
        internal static string CreateSelectCommandText(string tableName)
        {
            return "SELECT * FROM [" + tableName + "]";
        }

        /// <summary>
        /// Creates the update command text.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnNames">The column names.</param>
        /// <returns></returns>
        internal static string CreateUpdateCommandText(string tableName, string[] columnNames)
        {
            // create sql update part with placeholders
            // sql template: UPDATE [tableName] SET [col1] = ?, [col2] = ? WHERE [idCol] = ?
            IEnumerable<string> columns = columnNames.Select(attr => attr).Take(columnNames.Length - 1);
            var formattedColumns = CreateSqlColumnsPart(columns, "[{0}] = ?");
            string idField = columnNames.Last();
            return string.Format("UPDATE [{0}] SET {1} WHERE [{2}] = ?", tableName, formattedColumns, idField);
        }

        /// <summary>
        /// Creates the insert command text.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnNames">The column names.</param>
        /// <returns></returns>
        internal static string CreateInsertCommandText(string tableName, string[] columnNames)
        {
            // create sql update part with placeholders
            // sql template: INSERT INTO [tableName] ([col1], [col2]) VALUES (@col1, @col2)
            var columns = CreateSqlColumnsPart(columnNames.Select(attr => attr), "[{0}]");
            var valueParams = CreateSqlColumnsPart(columnNames.Select(attr => attr), "@{0}");
            return string.Format("INSERT INTO [{0}] ({1}) VALUES ({2})", tableName, columns, valueParams);
        }

        /// <summary>
        /// Converts a string list to a csv string line using the specified string format pattern
        /// </summary>
        /// <param name="attributes">The attributes.</param>
        /// <param name="pattern">The pattern.</param>
        /// <returns></returns>
        internal static string CreateSqlColumnsPart(IEnumerable<string> attributes, string pattern)
        {
            return string.Join(", ", attributes.Select(attr => string.Format(pattern, attr)).ToArray());
        }

        /// <summary>
        /// Converts an key value pair list to an oledb parameter array.
        /// </summary>
        /// <param name="attributes">The attributes.</param>
        /// <returns></returns>
        internal static OleDbParameter[] CreateParameterArray(IEnumerable<KeyValuePair<string, object>> attributes)
        {
            return attributes.Select(attr => new OleDbParameter("@" + attr.Key, attr.Value)).ToArray();
        }

        private static void CopyStream(Stream input, Stream output)
        {
            // Insert null checking here for production
            var buffer = new byte[8192];

            int bytesRead;
            while ((bytesRead = input.Read(buffer, 0, buffer.Length)) > 0)
            {
                output.Write(buffer, 0, bytesRead);
            }
        }

        private static void CreateFile(Assembly assembly, string resourceName, string path)
        {
            using (Stream input = assembly.GetManifestResourceStream(resourceName))
            {
                using (Stream output = File.Create(path))
                {
                    CopyStream(input, output);
                }
            }
        }
    }
}