using DbAPI.Errors;
using DbAPI.Logging;
using DTS.Common.Interface.Database;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;

namespace DbAPI.Connections
{
    /// <summary>
    /// internal class, no real reason to export this class, it handles all the details for the interface we exported
    /// Implements <see cref="IConnections"/>
    /// <inheritdoc cref="IConnections"/>
    /// </summary>
    internal class ConnectionManager : IConnections
    {
        private static readonly object LOGIN_LOCK = new object();
        private List<Tuple<IUserDbRecord, IConnectionDetails>> _loggedInUsers = new List<Tuple<IUserDbRecord, IConnectionDetails>>();
        private static readonly object CONNECT_LOCK = new object();
        private List<IConnectionDetails> _connections = new List<IConnectionDetails>();
        private void AddLoggedInUser(IConnectionDetails con, IUserDbRecord user)
        {
            lock (LOGIN_LOCK)
            {
                _loggedInUsers.Add(new Tuple<IUserDbRecord, IConnectionDetails>(user, con));
            }
        }
        /// <summary>
        /// removes all logged in users and all database connections
        /// </summary>
        public void ClearConnections()
        {
            lock (LOGIN_LOCK)
            {
                _loggedInUsers.Clear();
            }
            lock (CONNECT_LOCK)
            {
                _connections.Clear();
            }
        }
        public ulong ConnectToDb(IConnectionDetails details)
        {
            try
            {
                LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, $"ConnectToDb {details}");
                var ret = Connect(details);
                LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, $"Connection result {ErrorCodes.ResultToString(ret)}");
                return ret;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Exception: {ex.Message}");
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Connections, ex.Message);
                return ErrorCodes.ERROR_UNKNOWN;
            }
        }

        public IConnectionDetails[] GetActiveConnections()
        {
            lock (CONNECT_LOCK)
            {
                return _connections.ToArray();
            }
        }
        public ulong LoginUserHash(IConnectionDetails connection, string user, string hash, out IUserDbRecord userObject)
        {
            userObject = null;
            try
            {
                var res = User.User.GetUser(connection, out var foundUser, user);
                if (res != ErrorCodes.ERROR_SUCCESS)
                {
                    return ErrorCodes.ERROR_LOGINFAILED;
                }

                if (hash != foundUser.Password)
                {
                    return ErrorCodes.ERROR_LOGINFAILED;
                }
                userObject = foundUser;
                AddLoggedInUser(connection, userObject);
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Login, $"Login {connection} Error: {ex.Message}");
            }
            return ErrorCodes.ERROR_UNKNOWN;
        }
        public ulong LoginUser(IConnectionDetails connection, string user, string password, out IUserDbRecord userObject)
        {
            userObject = null;
            try
            {
                if (null == connection)
                {
                    return ErrorCodes.ERROR_MISSING_PARAMETER;
                }
                if (string.IsNullOrEmpty(user))
                {
                    return ErrorCodes.ERROR_MISSING_PARAMETER;
                }
                var res = User.User.GetUser(connection, out var foundUser, user);
                if (res != ErrorCodes.ERROR_SUCCESS)
                {
                    return ErrorCodes.ERROR_LOGINFAILED;
                }
                var b = Encoding.UTF8.GetBytes(string.Format("{0}_{1}", password, user));
                var sha2 = SHA256.Create();
                var hashed = sha2.ComputeHash(b);
                var hashedEncoded = Convert.ToBase64String(hashed);
                if (hashedEncoded != foundUser.Password)
                {
                    return ErrorCodes.ERROR_LOGINFAILED;
                }
                userObject = foundUser;
                AddLoggedInUser(connection, userObject);
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Login, $"Login {connection} Error: {ex.Message}");
            }
            return ErrorCodes.ERROR_UNKNOWN;
        }

        public Tuple<IUserDbRecord, IConnectionDetails>[] GetLoggedInUsers()
        {
            lock (LOGIN_LOCK)
            {
                return _loggedInUsers.ToArray();
            }
        }
        private ulong Connect(IConnectionDetails details)
        {
            LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, $"Connecting: {details}");
            var copy = details.Clone();
            ulong result;
            if (copy.UsingCentralizedDb)
            {
                result = ConnectRemote(details);
            }
            else
            {
                result = ConnectLocal(copy);
            }
            if (result == Errors.ErrorCodes.ERROR_SUCCESS)
            {
                AddConnection(copy);
            }
            LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, $"Connect {details} result: {ErrorCodes.ResultToString(result)}");
            return result;
        }
        private void AddConnection(IConnectionDetails details)
        {
            lock (CONNECT_LOCK)
            {
                _connections.Add(details);
            }
        }
        private ulong ConnectRemote(IConnectionDetails details)
        {
            return ErrorCodes.ERROR_SUCCESS;
        }
        private ulong ConnectLocal(IConnectionDetails details)
        {
            try
            {
                try
                {
                    LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, "Stopping instance");
                    StopInstance(details, DTS.Common.Utilities.Logging.APILogger.Log);
                }
                catch (Exception ex)
                {
                    LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, $"StopInstance {details} - {ex.Message}");
                    //29362 Don't re-throw exception if there was no instance to stop - just keep going
                    if (!(ex is DTS.Common.Utils.Database.SqlServerLocalDbException) ||
                        (ex as DTS.Common.Utils.Database.SqlServerLocalDbException).Error != DTS.Common.Utils.Database.SqlServerLocalDbException.Errors.LocalDbDoesntExist)
                    {
                        throw ex;
                    }
                }

                try
                {
                    LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, "Deleting instance");
                    DeleteInstance(details, DTS.Common.Utilities.Logging.APILogger.Log);
                }
                catch (Exception ex)
                {
                    LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, $"DeleteInstance {details} - {ex.Message}");
                    //29362 Don't re-throw exception if there was no instance to delete - just keep going
                    if (!(ex is DTS.Common.Utils.Database.SqlServerLocalDbException) ||
                        (ex as DTS.Common.Utils.Database.SqlServerLocalDbException).Error != DTS.Common.Utils.Database.SqlServerLocalDbException.Errors.LocalDbDoesntExist)
                    {
                        throw ex;
                    }
                }

                LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, "Creating instance");
                CreateInstance(details, DTS.Common.Utilities.Logging.APILogger.Log);
                LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, "Starting instance");
                StartInstance(details, DTS.Common.Utilities.Logging.APILogger.Log);
                LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, "Attaching databases");
                AttachDatabases(details, DTS.Common.Utilities.Logging.APILogger.Log);
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, $"ConnectLocal {details} - {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }

            return ErrorCodes.ERROR_SUCCESS;
        }

        private static readonly object PROCESS_LOCK = new object();
        private static readonly StringBuilder sb = new StringBuilder();

        private static void OutputHandler(object sendingProcess, DataReceivedEventArgs outLine)
        {
            if (outLine.Data != null)
            {
                if (string.IsNullOrWhiteSpace(outLine.Data))
                {
                    sb.Append("\r\n");
                }

                sb.Append(outLine.Data);
            }
        }
        private static readonly StringBuilder sbErrors = new StringBuilder();
        /// <summary>
        /// starts a process with a given command and logs
        /// </summary>
        /// <param name="sqlLocalDbExeFileName"></param>
        /// <param name="command"></param>
        /// <param name="log"></param>
        /// <returns></returns>
        private static string SqlCommandProcessor(string sqlLocalDbExeFileName, string command, DTS.Common.Utils.Database.LogDelegate log)
        {
            LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, $"{command}");
            var resultString = string.Empty;
            lock (PROCESS_LOCK)
            {
                sb.Clear();
                sbErrors.Clear();
                var process = new Process
                {
                    StartInfo =
                    {
                        FileName = sqlLocalDbExeFileName,
                        Arguments = command,
                        LoadUserProfile = true,
                        UseShellExecute = false,
                        RedirectStandardOutput = true,
                        RedirectStandardError = true,
                        CreateNoWindow = true
                    }
                };
                //* Set ONLY ONE handler here.
                process.OutputDataReceived += OutputHandler;
                process.ErrorDataReceived += Process_ErrorDataReceived;
                //* Start process
                process.Start();
                //* Read one element asynchronously
                process.BeginErrorReadLine();
                //* Read the other one synchronously
                var output = process.StandardOutput.ReadToEnd();
                Console.WriteLine(output);
                log?.Invoke($"Result of {command} command is: {output}");

                process.WaitForExit();
                if (sb.Length > 0)
                {
                    resultString = sb.ToString();
                }
                if (sbErrors.Length > 0)
                {
                    resultString = sbErrors.ToString();
                }
            }
            LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, $"{resultString}");
            return resultString;
        }

        private static void Process_ErrorDataReceived(object sender, DataReceivedEventArgs e)
        {
            if (!string.IsNullOrWhiteSpace(e.Data))
            {
                sbErrors.AppendLine(e.Data);
            }
        }

        private string ProcessSqlLocalDbCommand(string command, string localDbPath, DTS.Common.Utils.Database.LogDelegate log)
        {
            if (localDbPath == string.Empty)
            {
                //SQL Server LocalDb is not installed so display error and go away
                throw new FileNotFoundException();
            }
            var sqlLocalDbExeFileName = localDbPath + "SqlLocalDB.exe";
            return SqlCommandProcessor(sqlLocalDbExeFileName, command, log);
        }

        private void StopInstance(IConnectionDetails details, DTS.Common.Utils.Database.LogDelegate log)
        {
            var resultString = ProcessSqlLocalDbCommand($"stop {details.InstanceName}", details.SqlDbPath, log);
            if (resultString.Length != 0)
            {
                ////29362 If the error is because the instance doesn't exist, throw that type of Exception
                LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, $"Stop result: {resultString}");
                //if (resultString.Contains(DTS.Common.Strings.Strings.SQLLocalDBInstanceDoesNotExist))
                //{
                throw new DTS.Common.Utils.Database.SqlServerLocalDbException(DTS.Common.Utils.Database.SqlServerLocalDbException.Errors.LocalDbDoesntExist, resultString);
                //}
                //else
                //{
                //    throw new Exception(resultString);
                //}
            }
        }
        private void DeleteInstance(IConnectionDetails details, DTS.Common.Utils.Database.LogDelegate log)
        {
            var resultString = ProcessSqlLocalDbCommand($"delete {details.InstanceName}", details.SqlDbPath, log);
            if (resultString.Length != 0)
            {
                ////29362 If the error is because the instance doesn't exist, throw that type of Exception
                LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, $"Delete result: {resultString}");
                //if (resultString.Contains(DTS.Common.Strings.Strings.SQLLocalDBInstanceDoesNotExist))
                //{
                throw new DTS.Common.Utils.Database.SqlServerLocalDbException(DTS.Common.Utils.Database.SqlServerLocalDbException.Errors.LocalDbDoesntExist, resultString);
                //}
                //else
                //{
                //    throw new Exception(resultString);
                //}
            }
        }
        private void CreateInstance(IConnectionDetails details, DTS.Common.Utils.Database.LogDelegate log)
        {
            var resultString = ProcessSqlLocalDbCommand($"create {details.InstanceName}", details.SqlDbPath, log);
            if (resultString.Length != 0)
            {
                throw new Exception(resultString);
            }
        }
        private void StartInstance(IConnectionDetails details, DTS.Common.Utils.Database.LogDelegate log)
        {
            var resultString = ProcessSqlLocalDbCommand($"start {details.InstanceName}", details.SqlDbPath, log);
            if (resultString.Length != 0)
            {
                throw new Exception(resultString);
            }
        }
        private static string BatchCommandProcessor(string batchFileName,
            string dbName,
            string sqlDbFileName,
            string sqlLogFileName,
            string fullSqlcmdPath,
            DTS.Common.Utils.Database.LogDelegate log)
        {
            var resultString = string.Empty;
            lock (PROCESS_LOCK)
            {
                sb.Clear();
                sbErrors.Clear();
                var fi = new FileInfo(batchFileName);
                var fi2 = new FileInfo(sqlLogFileName);
                var fi3 = new FileInfo(sqlDbFileName);
                var process = new Process
                {
                    StartInfo =
                    {
                        FileName = fi.FullName,
                        Arguments = $"{dbName} \"{fi3.FullName}\" \"{fi2.FullName}\" {fullSqlcmdPath}",
                        LoadUserProfile = true,
                        UseShellExecute = false,
                        RedirectStandardOutput = true,
                        RedirectStandardError = true,
                        CreateNoWindow = true
                    }
                };
                //* Set ONLY ONE handler here.
                process.OutputDataReceived += OutputHandler;
                process.ErrorDataReceived += Process_ErrorDataReceived;
                //* Start process
                process.Start();
                //* Read one element asynchronously
                process.BeginErrorReadLine();
                //* Read the other one synchronously
                var output = process.StandardOutput.ReadToEnd();
                Console.WriteLine(output);
                log?.Invoke($"Result of attach {dbName} using {sqlDbFileName} and {sqlLogFileName} is:");
                log?.Invoke(output);
                process.WaitForExit();
                if (sb.Length > 0)
                {
                    resultString = sb.ToString();
                }
                if (sbErrors.Length > 0)
                {
                    resultString += sbErrors.ToString();
                }
            }

            return resultString;
        }


        /// <summary>
        /// attaches to a given database
        /// throws DbNotAttached exception
        /// </summary>
        private static void AttachDatabase(IConnectionDetails details, string dbName, DTS.Common.Utils.Database.LogDelegate log)
        {
            const string SqlCmdExe = "sqlcmd.exe";

            var dbFileName = Path.Combine(details.DbFolderPath, dbName) + ".mdf";
            var logFileName = Path.Combine(details.DbFolderPath, dbName) + "_log.ldf";
            var batchFileName = details.AttachDbsBatPath;
            log?.Invoke($"ODBCToolsPath is {details.ODBCToolsPath}");
            var fullSqlcmdPath = Path.Combine(details.ODBCToolsPath, SqlCmdExe); //e.g. $"\"C:\\Program Files\\Microsoft SQL Server\\Client SDK\\ODBC\\110\\Tools\\Binn\\sqlcmd.exe\""
            if (!File.Exists(fullSqlcmdPath))
            {
                log?.Invoke($"sqlcmd.exe DOES NOT EXIST at {fullSqlcmdPath}");
            }
            var resultString = BatchCommandProcessor(batchFileName, dbName, dbFileName, logFileName, $"\"{fullSqlcmdPath}\"", log);
            if (resultString.Length != 0)
            {
                throw new Exception(resultString);
            }
        }
        private void AttachDatabases(IConnectionDetails details, DTS.Common.Utils.Database.LogDelegate log)
        {
            //Attach the DataPRO database                        
            AttachDatabase(details, details.DbName, log);

            //Attach the ISO database            
            AttachDatabase(details, "ISO", log);
        }

        internal static ulong GetSqlCommand(IConnectionDetails con, out SqlCommand cmd, string commandText = "")
        {
            try
            {
                cmd = new SqlCommand();
                cmd.Connection = new SqlConnection(con.GetConnectionString());
                cmd.Connection.Open();
                cmd.CommandText = commandText;
                LogManager.Log(TraceEventType.Information, LogManager.LogEvents.Connections, $"CommandText is {commandText}");
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                cmd = null;
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Connections, $"GetSqlCommand, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
        }
        public bool IsUserLoggedIn(IUserDbRecord user, IConnectionDetails details)
        {
            lock (LOGIN_LOCK)
            {
                return _loggedInUsers.Exists(item => item.Item1 == user && item.Item2 == details);
            }
        }
    }
}