using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using Janus.Configuration;
using Janus.Extensions;
using TechTalk.SpecFlow;

namespace Janus.Database
{
    /// <summary>
    /// Contains utility methods for interacting with the MxConnect database.
    /// </summary>
    public class DatabaseUtil
    {
        private static readonly Regex _sqlSplitRegex = new Regex(@"^\s*GO\s* ($ | \-\-.*$)", 
            RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled);
        
        /// <summary>
        /// Executes the specified named SQL script and returns the result.
        /// </summary>
        /// <param name="name">The name of the SQL script to execute.</param>
        /// <param name="parameters">A collection of key/value pairs representing the script parameters.</param>
        /// <returns>The value returned by the specified SQL script.</returns>
        public static String ExecuteSqlQueryScriptWithSingleResult(String name, params KeyValuePair<String, Object>[] parameters)
        {
            var sql = ReadSqlScript("Scripts", name);
            var result = String.Empty;

            var testRunConfig = ScenarioContext.Current.GetTestRunConfig();
            ExecuteQuery(testRunConfig.EnvironmentConnectionString, sql, parameters, (reader) =>
            {
                result = reader[0].ToString();
            });

            return result;
        }

        /// <summary>
        /// Executes the specified named SQL script and returns the result as a SpecFlow <see cref="Table"/> object.
        /// </summary>
        /// <param name="name">The name of the SQL script to execute.</param>
        /// <param name="parameters">A collection of key/value pairs representing the script parameters.</param>
        /// <returns>The table of values returned by the specified SQL script.</returns>
        public static Table ExecuteSqlQueryScript(String name, params KeyValuePair<String, Object>[] parameters)
        {
            var sql = ReadSqlScript("Scripts", name);
            var result = default(Table);

            var testRunConfig = ScenarioContext.Current.GetTestRunConfig();
            ExecuteQuery(testRunConfig.EnvironmentConnectionString, sql, parameters, (reader) =>
            {
                if (result == null)
                {
                    var header = Enumerable.Range(0, reader.FieldCount).Select(x => reader.GetName(x)).ToArray();
                    result = new Table(header);
                }
                var cells = Enumerable.Range(0, reader.FieldCount).Select(x => String.Format("{0}", reader[x])).ToArray();
                result.AddRow(cells);
            });

            return result;
        }

        /// <summary>
        /// Executes the specified named non-query SQL script.
        /// </summary>
        /// <param name="name">The name of the SQL script to execute.</param>
        /// <param name="parameters">A collection of key/value pairs representing the script parameters.</param>
        public static void ExecuteNonQueryScript(String name, params KeyValuePair<String, Object>[] parameters)
        {
            var sql = ReadSqlScript("Scripts", name);

            var testRunConfig = ScenarioContext.Current.GetTestRunConfig();
            ExecuteNonQuery(testRunConfig.EnvironmentConnectionString, sql, parameters);
        }

        /// <summary>
        /// Executes the specified environment-specific SQL setup script.
        /// </summary>
        /// <param name="config">The current test run configuration.</param>
        /// <param name="name">The name of the script to execute.</param>
        /// <param name="parameters">A collection of key/value pairs representing the script parameters.</param>
        public static void ExecuteGlobalSetupScript(TestRunConfig config, String name, params KeyValuePair<String, Object>[] parameters)
        {
            var sql = ReadSqlScript(Path.Combine("Setup", "global"), name);
            ExecuteNonQuery(config.EnvironmentConnectionString, sql, parameters);
        }

        /// <summary>
        /// Executes the specified globally-defined SQL setup script.
        /// </summary>
        /// <param name="config">The current test run configuration.</param>
        /// <param name="name">The name of the script to execute.</param>
        /// <param name="parameters">A collection of key/value pairs representing the script parameters.</param>
        public static void ExecuteEnvironmentSetupScript(TestRunConfig config, String name, params KeyValuePair<String, Object>[] parameters)
        {
            var sql = ReadSqlScript(Path.Combine("Setup", config.Environment), name);
            ExecuteNonQuery(config.EnvironmentConnectionString, sql, parameters);
        }

        public static void ExecuteGlobalCleanup(TestRunConfig config)
        {
            ExecuteNonQuery(config.EnvironmentConnectionString, "DELETE FROM tbConfigurationSettingValue WHERE ConfigurationTemplateID = (SELECT ConfigurationTemplateId FROM tbConfigurationTemplate WHERE Name = 'Janus Automation')", null);
        }

        /// <summary>
        /// Executes the specified SQL query script.
        /// </summary>
        private static void ExecuteQuery(String connectionString, String sql, KeyValuePair<String, Object>[] parameters, Action<IDataReader> action)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                using (var command = new SqlCommand(sql, connection, transaction))
                {
                    command.CommandTimeout = (Int32)TimeSpan.FromMinutes(15).TotalSeconds;

                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                            command.Parameters.Add(new SqlParameter(parameter.Key, parameter.Value ?? DBNull.Value));
                    }

                    using (var reader = command.ExecuteReader())
                    {
                        while (!reader.IsClosed)
                        {
                            while (reader.Read())
                            {
                                action(reader);
                            }

                            if (!reader.NextResult())
                                reader.Close();
                        }
                    }

                    transaction.Commit();
                }
            }
        }

        /// <summary>
        /// Executes the specified SQL non-query script.
        /// </summary>
        private static void ExecuteNonQuery(String connectionString, String sql, KeyValuePair<String, Object>[] parameters)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    var parts = SplitScript(sql);
                    foreach (var part in parts)
                    {
                        using (var command = new SqlCommand(part, connection, transaction))
                        {
                            command.CommandTimeout = (Int32)TimeSpan.FromMinutes(15).TotalSeconds;

                            if (parameters != null)
                            {
                                foreach (var parameter in parameters)
                                    command.Parameters.Add(new SqlParameter(parameter.Key, (Object)parameter.Value ?? DBNull.Value));
                            }

                            command.ExecuteNonQuery();
                        }
                    }

                    transaction.Commit();
                }
            }
        }

        /// <summary>
        /// Reads the SQL script with the specified name.
        /// </summary>
        /// <param name="name">The name of the script to read.</param>
        /// <returns>The script text.</returns>
        private static String ReadSqlScript(String directory, String name)
        {
            return File.ReadAllText(Path.Combine("Database", directory, name));
        }

        /// <summary>
        /// Splits the specified script into parts separated by GO commands.
        /// </summary>
        private static IEnumerable<String> SplitScript(String sql)
        {
            return _sqlSplitRegex.Split(sql).Where(x => !String.IsNullOrWhiteSpace(x)).Select(x => x.Trim()).ToList();
        }
    }
}