#region License

// Copyright 2005-2019 Paul Kohler (https://github.com/paulkohler/minisqlquery). All rights reserved.
// This source code is made available under the terms of the GNU Lesser General Public License v3.0
// https://github.com/paulkohler/minisqlquery/blob/master/LICENSE

#endregion

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;

namespace MiniSqlQuery.Core
{
    /// <summary>
    /// 	Helper class to encapsulate query execution
    /// </summary>
    public class QueryRunner
    {
        /// <summary>
        /// 	The connection string value.
        /// </summary>
        private readonly string _connectionString;

        /// <summary>
        /// 	The enable query batching value.
        /// </summary>
        private readonly bool _enableQueryBatching;

        private readonly int _commandTimeout;

        /// <summary>
        /// 	The provider factory.
        /// </summary>
        private readonly DbProviderFactory _factory;

        private DbCommand _command;

        /// <summary>
        /// 	Initializes a new instance of the <see cref = "QueryRunner" /> class.
        /// </summary>
        /// <param name = "factory">The factory.</param>
        /// <param name = "connectionString">The connection string.</param>
        /// <param name = "enableQueryBatching">The enable query batching.</param>
        /// <param name="commandTimeout"></param>
        public QueryRunner(DbProviderFactory factory, string connectionString, bool enableQueryBatching, int commandTimeout)
        {
            _factory = factory;
            _connectionString = connectionString;
            _enableQueryBatching = enableQueryBatching;
            _commandTimeout = commandTimeout;
            Messages = string.Empty;
        }

        /// <summary>
        /// 	The batch progress.
        /// </summary>
        public event EventHandler<BatchProgressEventArgs> BatchProgress;

        /// <summary>
        /// 	Gets or sets the <see cref="QueryBatch"/> for this query.
        /// </summary>
        /// <value>The query batch.</value>
        public QueryBatch Batch { get; protected set; }

        /// <summary>
        /// 	Gets or sets Exception if any.
        /// </summary>
        /// <value>The exception.</value>
        public DbException Exception { get; protected set; }

        /// <summary>
        /// 	Gets or sets a value indicating whether the query runner is busy.
        /// </summary>
        /// <value>The is busy value.</value>
        public bool IsBusy { get; set; }

        /// <summary>
        /// 	Gets or sets the messages if any.
        /// </summary>
        /// <value>The messages.</value>
        public string Messages { get; protected set; }

        /// <summary>
        /// Creates an instance of a query runner for the specified database.
        /// </summary>
        /// <param name="factory">The factory.</param>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="enableQueryBatching">The enable query batching.</param>
        /// <param name="commandTimeout">The command timeout.</param>
        /// <returns>
        /// A <see cref="QueryRunner"/> instance acording to the parameters.
        /// </returns>
        /// <remarks>
        /// 	<example>
        /// var runner = QueryRunner.Create(DbProviderFactories.GetFactory("System.Data.SqlClient"), connStr, true);
        /// runner.ExecuteQuery("select * from Employees\r\nGO\r\nSelect * from Products");
        /// // runner.Batch.Queries.Count == 2 //
        /// </example>
        /// </remarks>
        public static QueryRunner Create(DbProviderFactory factory, string connectionString, bool enableQueryBatching, int commandTimeout)
        {
            if (factory.GetType().Name == "SqlClientFactory")
            {
                return new SqlQueryRunner(factory, connectionString, enableQueryBatching, commandTimeout);
            }

            // otherwise ise the default
            return new QueryRunner(factory, connectionString, enableQueryBatching, commandTimeout);
        }

        /// <summary>
        /// 	Tests the database connection using the specified provider.
        /// </summary>
        /// <param name = "providerName">Name of the provider.</param>
        /// <param name = "connectionString">The connection string.</param>
        /// <returns>If the connection was successful, null; otherwise the exception object.</returns>
        public static Exception TestDbConnection(string providerName, string connectionString)
        {
            try
            {
                DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
                using (DbConnection connection = factory.CreateConnection())
                {
                    connection.ConnectionString = connectionString;
                    connection.Open();
                    if (connection.State == ConnectionState.Open)
                    {
                        return null;
                    }
                }
            }
            catch (Exception e)
            {
                return e;
            }

            throw new InvalidOperationException("Connection test failed");
        }

        /// <summary>
        /// 	Executes the <paramref name = "sql" /> query.
        /// </summary>
        /// <param name = "sql">The SQL to execute.</param>
        public void ExecuteQuery(string sql)
        {
            ValidateState();

            DbConnection dbConnection = null;
            DbDataAdapter adapter = null;
            _command = null;
            Query query;

            // In the case of connection errors the error messages were getting lost, provide a default batch object:
            Batch = new QueryBatch(sql);

            try
            {
                IsBusy = true;

                dbConnection = _factory.CreateConnection();
                dbConnection.ConnectionString = _connectionString;
                dbConnection.Open();

                Messages = string.Empty;
                SubscribeToMessages(dbConnection);

                if (_enableQueryBatching)
                {
                    Batch = QueryBatch.Parse(sql);
                }
                else
                {
                    Batch = new QueryBatch(sql);
                }

                Batch.StartTime = DateTime.Now;
                adapter = _factory.CreateDataAdapter();
                _command = dbConnection.CreateCommand();
                _command.CommandType = CommandType.Text;
                SetCommandTimeout(_command, _commandTimeout);
                adapter.SelectCommand = _command;

                int queryCount = Batch.Queries.Count;
                for (int i = 0; i < queryCount; i++)
                {
                    query = Batch.Queries[i];
                    _command.CommandText = query.Sql;
                    query.Result = new DataSet("Batch " + (i + 1));
                    query.StartTime = DateTime.Now;
                    adapter.Fill(query.Result);
                    query.EndTime = DateTime.Now;
                    OnBatchProgress(new BatchProgressEventArgs(query, queryCount, i + 1));
                }
            }
            catch (DbException dbException)
            {
                HandleBatchException(dbException);
            }
            finally
            {
                if (Batch != null)
                {
                    Batch.EndTime = DateTime.Now;
                }

                if (adapter != null)
                {
                    adapter.Dispose();
                }

                if (_command != null)
                {
                    _command.Dispose();
                }

                IsBusy = false;
                UnsubscribeFromMessages(dbConnection);
            }

            if (Batch != null)
            {
                Batch.Messages = Messages;
            }
        }

        /// <summary>
        /// Cancel the executing command (if busy).
        /// </summary>
        /// <remarks>
        /// Note that this relies on the implementation of the DbCommand.Cancel operation.
        /// </remarks>
	    public void Cancel()
        {
            if (IsBusy && _command != null)
            {
                _command.Cancel();
            }
        }

        /// <summary>
        /// Sets the command timeout, currently only tested against MSSQL.
        /// </summary>
        /// <param name="cmd">The command.</param>
        /// <param name="commandTimeout">The command timeout.</param>
        private void SetCommandTimeout(IDbCommand cmd, int commandTimeout)
        {
            if (_factory is SqlClientFactory)
            {
                if (cmd == null)
                {
                    throw new ArgumentNullException("cmd");
                }
                cmd.CommandTimeout = commandTimeout;
            }
            else
            {
                Trace.WriteLine("Command Timeout only supported by SQL Client (so far)");
            }
        }

        /// <summary>
        /// 	The handle batch exception.
        /// </summary>
        /// <param name = "dbException">The db exception.</param>
        protected virtual void HandleBatchException(DbException dbException)
        {
            Exception = dbException;
            Messages += dbException.Message + Environment.NewLine;
        }

        /// <summary>
        /// 	The on batch progress.
        /// </summary>
        /// <param name = "e">The events.</param>
        protected void OnBatchProgress(BatchProgressEventArgs e)
        {
            EventHandler<BatchProgressEventArgs> progress = BatchProgress;
            if (progress != null)
            {
                progress(this, e);
            }
        }

        /// <summary>
        /// 	The subscribe to messages.
        /// </summary>
        /// <param name = "connection">The connection.</param>
        protected virtual void SubscribeToMessages(DbConnection connection)
        {
        }

        /// <summary>
        /// 	The unsubscribe from messages.
        /// </summary>
        /// <param name = "connection">The connection.</param>
        protected virtual void UnsubscribeFromMessages(DbConnection connection)
        {
        }

        /// <summary>
        /// 	Ensures that there is enough information available to the class to execute a query.
        /// </summary>
        /// <exception cref = "InvalidOperationException">If there is no connection, "Supply a connection."</exception>
        /// <exception cref = "InvalidOperationException">If there is no connection, "Supply a provider."</exception>
        private void ValidateState()
        {
            if (string.IsNullOrEmpty(_connectionString))
            {
                throw new InvalidOperationException("Supply a connection.");
            }

            if (_factory == null)
            {
                throw new InvalidOperationException("Supply a provider.");
            }
        }
    }
}