using System;
using Microsoft.Extensions.Logging;
using AyaNova.Models;
using System.Linq;
using System.Collections.Generic;


namespace AyaNova.Util
{


    internal static class DbUtil
    {
        private static string _RawAyaNovaConnectionString;
        private static string _dbConnectionString;
        private static string _dbName;
        private static string _dbUserName;
        private static string _dbPassword;
        private static string _dbServer;

        #region parse connection string
        internal static void ParseConnectionString(ILogger _log, string AyaNovaConnectionString)
        {

            if (string.IsNullOrWhiteSpace(AyaNovaConnectionString))
            {
                _log.LogDebug("There is no database server connection string set, AYANOVA_DB_CONNECTION is missing or empty.  Will use default: \"Server=localhost;Username=postgres;Database=AyaNova;\"");
                AyaNovaConnectionString = "Server=localhost;Username=postgres;Database=AyaNova;";
            }

            _RawAyaNovaConnectionString = AyaNovaConnectionString;
            var builder = new System.Data.Common.DbConnectionStringBuilder();
            builder.ConnectionString = AyaNovaConnectionString;

            if (!builder.ContainsKey("database"))
            {
                _log.LogDebug("There is no database name specified (\"Database=<NAME>\") in connection string.  Will use default: \"Database=AyaNova;\"");
                builder.Add("database", "AyaNova");
            }

            //Keep track of default values
            _dbConnectionString = builder.ConnectionString;
            if (builder.ContainsKey("database"))
                _dbName = builder["database"].ToString();

            if (builder.ContainsKey("username"))
                _dbUserName = builder["username"].ToString();

            if (builder.ContainsKey("password"))
                _dbPassword = builder["password"].ToString();

            if (builder.ContainsKey("server"))
                _dbServer = builder["server"].ToString();

            _log.LogDebug("AyaNova will use the following connection string: {0}", PasswordRedactedConnectionString(_dbConnectionString));


        }


        ///////////////////////////////////////////
        //clean out password from connection string
        //for log purposes
        private static string PasswordRedactedConnectionString(string cs)
        {
            var nStart = 0;
            var nStop = 0;
            var lwrcs = cs.ToLowerInvariant();
            nStart = lwrcs.IndexOf("password");
            if (nStart == -1)
            {
                //no password, just return it
                return cs;
            }
            //find terminating semicolon
            nStop = lwrcs.IndexOf(";", nStart);
            if (nStop == -1 || nStop == lwrcs.Length)
            {
                //no terminating semicolon or that is the final character in the string
                return cs.Substring(0, nStart + 9) + "[redacted];";
            }
            else
            {
                //not the last thing in the string so return the whole string minus the password part
                return cs.Substring(0, nStart + 9) + "[redacted];" + cs.Substring(nStop + 1);
            }
        }
        #endregion

        #region Connection utilities
        ///////////////////////////////////////////
        //Verify that server exists
        //
        private static string AdminConnectionString
        {
            get
            {
                return _dbConnectionString.Replace(_dbName, "postgres");
            }

        }

        ///////////////////////////////////////////
        //Connection string without password
        //
        internal static string DisplayableConnectionString
        {
            get
            {
                return PasswordRedactedConnectionString(_dbConnectionString);
            }

        }



        #endregion

        #region DB verification

        ///////////////////////////////////////////
        //Verify that server exists
        // spend up to 5 minutes waiting for it to come up before bailing
        //
        internal static bool DatabaseServerExists(ILogger log, string logPrepend)
        {

            try
            {
                //Try every 10 seconds for 30 tries before giving up (5 minutes total)

                var maxRetryAttempts = 30;
                var pauseBetweenFailures = TimeSpan.FromSeconds(10);
                RetryHelper.RetryOnException(maxRetryAttempts, pauseBetweenFailures, log, logPrepend + DisplayableConnectionString, () =>
                 {
                     using (var conn = new Npgsql.NpgsqlConnection(AdminConnectionString))
                     {

                         conn.Open();
                         conn.Close();
                     }
                 });
            }
            catch
            {
                return false;
            }
            return true;

        }




        ///////////////////////////////////////////
        //Verify that database exists, if not, then create it
        //
        internal static bool EnsureDatabaseExists(ILogger _log)
        {
            _log.LogDebug("Ensuring database exists. Connection string is: \"{0}\"", DisplayableConnectionString);

            using (var conn = new Npgsql.NpgsqlConnection(_dbConnectionString))
            {
                try
                {
                    conn.Open();
                    conn.Close();
                }
                catch (Exception e)
                {

                    //if it's a db doesn't exist that's ok, we'll create it, not an error
                    if (e is Npgsql.PostgresException)
                    {
                        if (((Npgsql.PostgresException)e).SqlState == "3D000")
                        {
                            //create the db here
                            using (var cnCreate = new Npgsql.NpgsqlConnection(AdminConnectionString))
                            {
                                cnCreate.Open();

                                // Create the database desired
                                using (var cmd = new Npgsql.NpgsqlCommand())
                                {

                                    cmd.Connection = cnCreate;
                                    cmd.CommandText = "CREATE DATABASE \"" + _dbName + "\";";
                                    cmd.ExecuteNonQuery();
                                    _log.LogInformation("Database \"{0}\" created successfully!", _dbName);
                                }
                                cnCreate.Close();
                            }
                        }
                        else
                        {
                            var err = string.Format("Database server connection failed. Connection string is: \"{0}\"", DisplayableConnectionString);
                            _log.LogCritical(e, "BOOT: E1000 - " + err);
                            err = err + "\nError reported was: " + e.Message;
                            throw new ApplicationException(err);
                        }

                    }

                }

            }
            return true;

        }


        #endregion

        #region DB utilities
        ///////////////////////////////////////////
        // Drop and re-create db
        // This is the NUCLEAR option and 
        // completely ditches the DB and all user uploaded files
        //
        internal static void DropAndRecreateDb(ILogger _log)
        {
            _log.LogInformation("Dropping and recreating Database \"{0}\"", _dbName);

            //clear all connections so that the database can be dropped
            Npgsql.NpgsqlConnection.ClearAllPools();

            using (var conn = new Npgsql.NpgsqlConnection(AdminConnectionString))
            {
                conn.Open();

                // Create the database desired
                using (var cmd = new Npgsql.NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "DROP DATABASE \"" + _dbName + "\";";
                    cmd.ExecuteNonQuery();
                    cmd.Connection = conn;
                    cmd.CommandText = "CREATE DATABASE \"" + _dbName + "\";";
                    cmd.ExecuteNonQuery();
                    _log.LogInformation("Database re-created successfully!");
                }
                conn.Close();
            }

            //final cleanup step is to erase user uploaded files
            FileUtil.EraseEntireContentsOfUserFilesFolder();
        }


        /////////////////////////////////////////////////////////
        // Erase all user entered data from the db
        // This is called by seeder for trial seeding purposes
        //
        internal static void EmptyBizDataFromDatabaseForSeedingOrImporting(ILogger _log)
        {


            _log.LogInformation("Erasing Database \"{0}\"", _dbName);
            AyaNova.Api.ControllerHelpers.ApiServerState apiServerState = (AyaNova.Api.ControllerHelpers.ApiServerState)ServiceProviderProvider.Provider.GetService(typeof(AyaNova.Api.ControllerHelpers.ApiServerState));

            apiServerState.SetClosed("Erasing database");

            //clear all connections so that the database can be dropped
            Npgsql.NpgsqlConnection.ClearAllPools();

            using (var conn = new Npgsql.NpgsqlConnection(_dbConnectionString))
            {
                conn.Open();

                //Delete from user options table first
                using (var cmd = new Npgsql.NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "delete from  \"auseroptions\" where UserId <> 1;";
                    cmd.ExecuteNonQuery();
                }

                //Delete from users table
                using (var cmd = new Npgsql.NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "delete from  \"auser\" where id <> 1;";
                    cmd.ExecuteNonQuery();
                }

                //REMOVE ALL DATA with few exceptions of manager user, license, schema tables 
                //and job logs because this is called by job code
                EraseTable("afileattachment", conn);
                EraseTable("awidget", conn);
                EraseTable("aevent", conn);
                EraseTable("adatafilter", conn);
                EraseTable("aformcustom", conn);
                EraseTable("asearchkey", conn);
                EraseTable("asearchdictionary", conn);
                EraseTable("atag", conn);

                conn.Close();
            }

            //If we got here then it's safe to erase the attachment files
            FileUtil.EraseEntireContentsOfUserFilesFolder();

            apiServerState.ResumePriorState();

        }


        ///////////////////////////////////////////
        // Erase all data from the table specified
        //
        private static void EraseTable(string sTable, Npgsql.NpgsqlConnection conn)
        {
            using (var cmd = new Npgsql.NpgsqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = "TRUNCATE \"" + sTable + "\" RESTART IDENTITY CASCADE;";
                cmd.ExecuteNonQuery();
            }
        }


        ///////////////////////////////////////////
        // Check if DB is empty 
        // CALLED BY LICENSE CONTROLLER AND LICENSE.CS FOR TRIAL Request check
        // Also called by Import
        internal static bool DBIsEmpty(AyContext ctx, ILogger _log)
        {
            //TODO: This needs to be way more thorough, only the main tables though, no need to get crazy with it
            //just stuff that would be shitty to have to re-enter

            _log.LogDebug("DB empty check");

            //An empty db contains only one User 
            if (ctx.User.Count() > 1) return false;

            //No clients
            //if(ctx.Client.Count()>0) return false;

            //No units
            //if(ctx.Unit.Count()>0) return false;

            //No parts
            //if(ctx.Part.Count()>0) return false;

            //No workorders
            //if(ctx.Workorder.Count()>0) return false;


            return true;
        }


        ///////////////////////////////////////////
        // Ensure the db is not modified
        //
        internal static void CheckFingerPrint(long ExpectedColumns, long ExpectedIndexes, ILogger _log)
        {
            _log.LogDebug("Checking DB integrity");

            long actualColumns = 0;
            long actualIndexes = 0;

            using (var conn = new Npgsql.NpgsqlConnection(_dbConnectionString))
            {
                conn.Open();

                using (var command = conn.CreateCommand())
                {
                    //Count all columns in all our tables
                    command.CommandText = "SELECT count(*) FROM information_schema.columns where table_schema='public'";

                    using (var result = command.ExecuteReader())
                    {
                        if (result.HasRows)
                        {
                            //check the values
                            result.Read();
                            actualColumns = result.GetInt64(0);
                        }
                        else
                        {
                            var err = "E1030 - Database integrity check failed, could not obtain column data. Contact support.";
                            _log.LogCritical(err);

                            throw new ApplicationException(err);
                        }
                    }
                }

                using (var command = conn.CreateCommand())
                {
                    //Count all indexes in all our tables
                    command.CommandText = "select Count(*) from pg_indexes where schemaname='public'";

                    using (var result = command.ExecuteReader())
                    {
                        if (result.HasRows)
                        {
                            //check the values
                            result.Read();
                            actualIndexes = result.GetInt64(0);
                        }
                        else
                        {
                            var err = "E1030 - Database integrity check failed, could not obtain index data. Contact support.";
                            _log.LogCritical(err);
                            throw new ApplicationException(err);
                        }
                    }
                }
                conn.Close();

                if (ExpectedColumns != actualColumns || ExpectedIndexes != actualIndexes)
                {
                    var err = string.Format("E1030 - Database integrity check failed (C{0}I{1})", actualColumns, actualIndexes);
                    _log.LogCritical(err);
                    throw new ApplicationException(err);
                }
            }
        }


        ///////////////////////////////////////////
        // Given a table name return the count of records in that table
        // Used for metrics 
        //
        ///
        internal static long CountOfRecords(string TableName)
        {
            long ret = 0;

            using (var conn = new Npgsql.NpgsqlConnection(_dbConnectionString))
            {
                conn.Open();

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = $"SELECT count(*) FROM {TableName}";
                    using (var result = command.ExecuteReader())
                    {
                        if (result.HasRows)
                        {
                            result.Read();
                            ret = result.GetInt64(0);
                        }
                    }
                }
                conn.Close();
            }
            return ret;
        }


        ///////////////////////////////////////////
        // Returns all table names that are ours in current schema
        //
        ///
        internal static List<string> GetAllTablenames()
        {

            List<string> ret = new List<string>();
            using (var conn = new Npgsql.NpgsqlConnection(_dbConnectionString))
            {
                conn.Open();

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "SELECT table_name  FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';";
                    using (var result = command.ExecuteReader())
                    {
                        if (result.HasRows)
                        {
                            while (result.Read())
                            {
                                ret.Add(result.GetString(0));
                            }
                        }
                    }
                }
                conn.Close();
            }
            return ret;
        }






        #endregion



    }//eoc

}//eons