using System;
using Microsoft.Extensions.Logging;
using Sockeye.Models;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using System.Linq;

namespace Sockeye.Util
{


    internal static class DbUtil
    {
        private static string _RawSockeyeConnectionString;
        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 SockeyeConnectionString)
        {

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

            _RawSockeyeConnectionString = SockeyeConnectionString;
            var builder = new System.Data.Common.DbConnectionStringBuilder();
            builder.ConnectionString = SockeyeConnectionString;

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

            //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("Sockeye will use the following connection string: {0}", PasswordRedactedConnectionString(_dbConnectionString));


        }


        ///////////////////////////////////////////
        //clean out password from connection string
        //for log purposes
        internal 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

        ///////////////////////////////////////////
        // Get database server version
        //
        internal static string DBServerVersion(Sockeye.Models.AyContext ct)
        {
            using (var cmd = ct.Database.GetDbConnection().CreateCommand())
            {
                ct.Database.OpenConnection();
                cmd.CommandText = $"select version();";
                using (var dr = cmd.ExecuteReader())
                {
                    if (dr.Read())
                    {
                        if (dr.IsDBNull(0))
                            return "Unknown / no results";
                        else
                            return (dr.GetString(0));
                    }
                    else
                    {
                        return "Unknown / no results";
                    }
                }
            }
        }

        ///////////////////////////////////////////
        // Get database runtime parameter settings
        //
        internal static Dictionary<string, string> DBServerRunTimeParameters(Sockeye.Models.AyContext ct)
        {
            Dictionary<string, string> ret = new Dictionary<string, string>();
            using (var cmd = ct.Database.GetDbConnection().CreateCommand())
            {
                ct.Database.OpenConnection();
                cmd.CommandText = $"SHOW ALL;";
                using (var dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        string name = string.Empty; ;
                        string setting = string.Empty;
                        if (!dr.IsDBNull(0))
                            name = dr.GetString(0);
                        if (!dr.IsDBNull(1))
                            setting = dr.GetString(1);
                        ret.Add(name, setting);
                    }

                }
            }
            return ret;
        }


        ///////////////////////////////////////////
        //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 5 seconds for 60 tries before giving up (5 minutes total)

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

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

        ///////////////////////////////////////////////
        // Set global flag if db server is connectable
        //
        internal static void CheckDatabaseServerAvailable(ILogger log)
        {
            //Called by generator when db is down to check if it can connect
            if (CHECKING_DB_AVAILABLE) return;
            //don't check too often just fills log files for no reason
            if (DateTime.UtcNow - CHECKED_DB_AVAILABLE_LAST < CHECK_DB_AVAILABLE_EVERY_INTERVAL)
                return;
            CHECKING_DB_AVAILABLE = true;
            if (CHECKING_DB_LOG_DOWN_STATUS)
            {
                log.LogInformation("Database server unreachable; pausing Job processing");
                CHECKING_DB_LOG_DOWN_STATUS = false;
            }
            try
            {
                log.LogTrace("Database Down - checking if up yet");
                using (AyContext ct = ServiceProviderProvider.DBContext)
                {
                    var dummy = ct.GlobalBizSettings.FirstOrDefault(z => z.Id == 1);
                }
            }
            catch
            {
                return;
            }
            finally
            {
                CHECKED_DB_AVAILABLE_LAST = DateTime.UtcNow;
                CHECKING_DB_AVAILABLE = false;
            }
            //We have db available
            log.LogInformation("Database server has become available; resuming Job processing");
            ServerGlobalOpsSettingsCache.DBAVAILABLE = true;
            CHECKING_DB_LOG_DOWN_STATUS = true;
        }
        private static bool CHECKING_DB_AVAILABLE = false;
        private static bool CHECKING_DB_LOG_DOWN_STATUS = true;
        private static DateTime CHECKED_DB_AVAILABLE_LAST = DateTime.MinValue;
        private static TimeSpan CHECK_DB_AVAILABLE_EVERY_INTERVAL = new TimeSpan(0, 0, 30);


        ///////////////////////////////////////////////////////////
        // Check if exception means db server is unavailable
        // if so, flag global flag indicating it isn't
        //
        internal static void HandleIfDatabaseUnavailableTypeException(Exception ex)
        {
            if (ex == null) return;
            if (ex.Message.Contains("transient failure") && ex.Source.Contains("PostgreSQL"))
                ServerGlobalOpsSettingsCache.DBAVAILABLE = false;
        }


        ///////////////////////////////////////////
        //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 + "\" WITH ENCODING \"UTF8\" TEMPLATE=template0;";
                                    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 async Task DropAndRecreateDbAsync(ILogger _log)
        {
            _log.LogInformation("Dropping and creating Database \"{0}\"", _dbName);

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

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

                // Create the database desired
                using (var cmd = new Npgsql.NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "DROP DATABASE \"" + _dbName + "\";";
                    await cmd.ExecuteNonQueryAsync();
                    cmd.Connection = conn;
                    cmd.CommandText = "CREATE DATABASE \"" + _dbName + "\" WITH ENCODING \"UTF8\" TEMPLATE=template0;";
                    await cmd.ExecuteNonQueryAsync();
                    _log.LogDebug("Database created");
                }
                await conn.CloseAsync();
            }

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





        /////////////////////////////////////////////////////////
        // Erase all user entered data from the db
        // This is called by seeder for trial seeding purposes
        // and by v8 migrate and by license controller when erasing db
        internal static async Task EmptyBizDataFromDatabaseForSeedingOrImportingAsync(ILogger _log, bool keepTaxCodes = false)
        {
            //case 4221 
            //erase plan to use truncate table instead of slower delete method
            //- truncate table cascade deletes which removes all user accounts including superuser
            //- duplicate the auser and auseroption tables to a temporary table to preserve them
            //- do the full truncate
            //- select the superuser account back to the auser and useroptions
            //- discover where else this needs to be done
            //- $profit





            _log.LogInformation("Erasing Database \"{0}\"", _dbName);
            Sockeye.Api.ControllerHelpers.ApiServerState apiServerState = (Sockeye.Api.ControllerHelpers.ApiServerState)ServiceProviderProvider.Provider.GetService(typeof(Sockeye.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))
            {
                await conn.OpenAsync();

                //prepare to delete by removing foreign keys 
                using (var cmd = new Npgsql.NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "update auser set customerid=null;";
                    await cmd.ExecuteNonQueryAsync();

                    cmd.CommandText = "update auser set headofficeid=null;";
                    await cmd.ExecuteNonQueryAsync();

                    cmd.CommandText = "update auser set vendorid=null;";
                    await cmd.ExecuteNonQueryAsync();

                    cmd.CommandText = "update alicense set customerid=null;";
                    await cmd.ExecuteNonQueryAsync();




                    //DELIBERATELY NOT ERASED:
                    //NOTE: these tables commented below are deliberately not affected as it's assumed for a **COMPLETE** erase users will erase manually or use the config setting to completely drop the database
                    //For anything less than that it's assumed users are testing things out and want to keep these tables but erase the biz data
                    //aglobalopsbackupsettings 
                    //aglobalopsnotificationsettings
                    //aglobalbizsettings
                    //alogo
                    //areport


                    //await EraseTableAsync("aopsjoblog", conn, true);
                    // await EraseTableAsync("aopsjob", conn, true);


                }


                //Delete non stock translations
                using (var cmd = new Npgsql.NpgsqlCommand())
                {
                    cmd.Connection = conn;

                    //set to default translation so can delete all non default ones
                    cmd.CommandText = "update auseroptions set translationid=1;";
                    await cmd.ExecuteNonQueryAsync();

                    cmd.CommandText = "delete from atranslationitem where translationid > 4;";
                    await cmd.ExecuteNonQueryAsync();

                    cmd.CommandText = "delete from  atranslation where id > 4;";
                    await cmd.ExecuteNonQueryAsync();
                }

                //case 4221 truncate support
                //BACKUP USER AND DATA TO BE PRESERVED THAT TRUNCATE WILL CASCADE DELETE
                using (var cmd = new Npgsql.NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "CREATE TABLE auser_backup AS TABLE auser;";
                    await cmd.ExecuteNonQueryAsync();
                    cmd.CommandText = "CREATE TABLE auseroptions_backup AS TABLE auseroptions;";
                    await cmd.ExecuteNonQueryAsync();
                }

                //REMOVE ALL REMAINING DATA


                await TruncateTableAsync("afileattachment", conn);
                await TruncateTableAsync("aevent", conn);
                await TruncateTableAsync("adatalistsavedfilter", conn);
                await TruncateTableAsync("adatalistcolumnview", conn);
                await TruncateTableAsync("apicklisttemplate", conn, true);
                await TruncateTableAsync("aformcustom", conn);
                await TruncateTableAsync("asearchkey", conn);
                await TruncateTableAsync("asearchdictionary", conn);
                await TruncateTableAsync("atag", conn);


                await TruncateTableAsync("asubscriptionserver", conn);
                await TruncateTableAsync("apurchase", conn);
                await TruncateTableAsync("aproduct", conn);
                //await TruncateTableAsync("avendor", conn);
                await TruncateTableAsync("acustomernote", conn);
                await TruncateTableAsync("acustomer", conn);
                await TruncateTableAsync("aheadoffice", conn);

                await TruncateTableAsync("agzcase", conn);
                await TruncateTableAsync("atriallicenserequest", conn);
                await TruncateTableAsync("alicense", conn);


                //----- NOTIFICATION
                await TruncateTableAsync("ainappnotification", conn);
                await TruncateTableAsync("anotifyevent", conn);
                await TruncateTableAsync("anotifydeliverylog", conn);
                await TruncateTableAsync("anotifysubscription", conn);
                await TruncateTableAsync("acustomernotifyevent", conn);
                await TruncateTableAsync("acustomernotifydeliverylog", conn);
                await TruncateTableAsync("acustomernotifysubscription", conn);

                await TruncateTableAsync("amemo", conn);
                await TruncateTableAsync("areminder", conn);//depends on User
                await TruncateTableAsync("areview", conn);//depends on User

                await TruncateTableAsync("ametricmm", conn, true);
                await TruncateTableAsync("ametricdd", conn, true);
                await TruncateTableAsync("adashboardview", conn);

                await TruncateTableAsync("aintegration", conn);

                //############# WARNING: there can be unintended consequences easily if new tables or fields are added that REFERENCE other tables triggering a cascade delete unexpectedly
                //be sure about that before making changes and test thoroughly anything that calls this method:
                // the seeding and manual erase and v8-migrate code when making such changes


                //case 4221 truncate support
                //COPY BACK USER AND DATA TO BE PRESERVED THAT TRUNCATE WILL CASCADE DELETE
                using (var cmd = new Npgsql.NpgsqlCommand())
                {
                    //AT this point the truncate commands in erasetable above have caused all user and useroptions to be deleted
                    //so no need to clean out those tables, instead put our backup superuser back in again
                    cmd.Connection = conn;
                    cmd.CommandText = "INSERT INTO auser (active, name, lastlogin, login, password, salt , roles, currentauthtoken, "
                                       + "dlkey, dlkeyexpire, totpsecret, temptoken, twofactorenabled, passwordresetcode, passwordresetcodeexpire, usertype, "
                                       + "employeenumber, notes, wiki, customfields, tags, allowlogin) "
                                       + "SELECT active, name, lastlogin, login, password, salt , roles, currentauthtoken, "
                                       + "dlkey, dlkeyexpire, totpsecret, temptoken, twofactorenabled, passwordresetcode, passwordresetcodeexpire, usertype, "
                                       + "employeenumber, notes, wiki, customfields, tags, allowlogin "
                                       + "FROM auser_backup where id = 1;";
                    await cmd.ExecuteNonQueryAsync();

                    cmd.CommandText = "INSERT INTO auseroptions (userid, translationid, languageoverride, timezoneoverride, "
                                      + "currencyname, hour12, emailaddress, phone1, phone2, phone3, mapurltemplate, uicolor) "
                                      + "SELECT userid, translationid, languageoverride, timezoneoverride, "
                                      + "currencyname, hour12, emailaddress, phone1, phone2, phone3, mapurltemplate, uicolor "
                                      + "FROM auseroptions_backup where userid = 1;";
                    await cmd.ExecuteNonQueryAsync();

                    cmd.CommandText = "DROP TABLE IF EXISTS AUSEROPTIONS_BACKUP, AUSER_BACKUP;";
                    await cmd.ExecuteNonQueryAsync();
                }


                //final housekeeping
                using (var cmd = new Npgsql.NpgsqlCommand())
                {
                    cmd.Connection = conn;


                    cmd.CommandText = $"ALTER SEQUENCE agzcase_caseid_seq RESTART WITH 2;";
                    await cmd.ExecuteNonQueryAsync();


                }

                await conn.CloseAsync();
            }

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

            _log.LogInformation("Importing any missing stock Report templates");
            await Sockeye.Biz.PrimeData.PrimeReportTemplates();

            apiServerState.ResumePriorState();

            _log.LogInformation("Database erase completed");

        }


        ///////////////////////////////////////////
        // Truncate all data from the table specified
        //
        private static async Task TruncateTableAsync(string sTable, Npgsql.NpgsqlConnection conn, bool tableHasNoSequence = false)
        {
            using (var cmd = new Npgsql.NpgsqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = "TRUNCATE \"" + sTable + "\" RESTART IDENTITY CASCADE;";
                await cmd.ExecuteNonQueryAsync();

            }
        }

        ///////////////////////////////////////////
        // Erase all data from the table specified
        //
        private static async Task EraseTableAsync(string sTable, Npgsql.NpgsqlConnection conn, bool tableHasNoSequence = false)
        {
            //this variant is used for tables that don't need to be truncated due to smaller size
            //and would trigger need to backup referenced tables first
            using (var cmd = new Npgsql.NpgsqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = $"delete from  {sTable};";
                await cmd.ExecuteNonQueryAsync();
                if (!tableHasNoSequence)
                {
                    cmd.CommandText = $"ALTER SEQUENCE {sTable}_id_seq RESTART WITH 1;";
                    await cmd.ExecuteNonQueryAsync();
                }
            }
        }


        ///////////////////////////////////////////
        // Check if DB is empty 
        // CALLED BY LICENSE CONTROLLER AND LICENSE.CS FOR TRIAL Request check        
        internal static async Task<bool> DBIsEmptyAsync(AyContext ct, ILogger _log)
        {
            //For efficiency just check a few main tables just stuff that would be shitty to have to re-enter
            //Mostly user, customer and vendor cover it because nearly everything else requires those to have any sort of data at all

            _log.LogDebug("DB empty check");

            //An empty db contains only one User 
            if (await ct.User.LongCountAsync() > 1) return false;
            if (await ct.Customer.AnyAsync()) return false;

            if (await ct.HeadOffice.AnyAsync()) return false;


            return true;
        }



        ///////////////////////////////////////////
        // Check if DB has evaluation user accounts 
        // CALLED BY by login ping from client via notify controller       
        internal static async Task<bool> DBHasTrialUsersAsync(AyContext ct, ILogger _log)
        {
            _log.LogDebug("DB trial users presence check for pre-login ping");
            //There are 22 trial users (more but for internal use) in a trial database
            if (await ct.User.LongCountAsync() < 22) return false;

            //just check for a few for testing
            if (await ct.User.AsNoTracking()
            .Where(z =>
            z.Login == "BizAdmin" ||
            z.Login == "Service" ||
            z.Login == "Inventory" ||
            z.Login == "Accounting" ||
            z.Login == "Tech"
            ).LongCountAsync() < 5) return false;

            return true;
        }

        ///////////////////////////////////////////
        // Ensure the db is not modified
        //
        internal static async Task CheckFingerPrintAsync(
            long ExpectedColumns,
            long ExpectedIndexes,
            long ExpectedCheckConstraints,
            long ExpectedForeignKeyConstraints,
            long ExpectedViews,
            long ExpectedRoutines,
            ILogger _log)
        {
            _log.LogDebug("Checking DB integrity");

            long actualColumns = 0;
            long actualIndexes = 0;
            long actualCheckConstraints = 0;
            long actualForeignKeyConstraints = 0;
            long actualViews = 0;
            long actualRoutines = 0;

            //COLUMNS
            using (var conn = new Npgsql.NpgsqlConnection(_dbConnectionString))
            {
                await conn.OpenAsync();

                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 = await command.ExecuteReaderAsync())
                    {
                        if (result.HasRows)
                        {
                            //check the values
                            await result.ReadAsync();
                            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);
                        }
                    }
                }

                //INDEXES
                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 = await command.ExecuteReaderAsync())
                    {
                        if (result.HasRows)
                        {
                            //check the values
                            await result.ReadAsync();
                            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);
                        }
                    }
                }

                //CHECK CONSTRAINTS
                using (var command = conn.CreateCommand())
                {

                    command.CommandText = "SELECT count(*) FROM information_schema.check_constraints where constraint_schema='public'";

                    using (var result = await command.ExecuteReaderAsync())
                    {
                        if (result.HasRows)
                        {
                            //check the values
                            await result.ReadAsync();
                            actualCheckConstraints = result.GetInt64(0);
                        }
                        else
                        {
                            var err = "E1030 - Database integrity check failed, could not obtain CHECK CONSTRAINT data. Contact support.";
                            _log.LogCritical(err);
                            throw new ApplicationException(err);
                        }
                    }
                }

                //FOREIGN KEY CONSTRAINTS
                using (var command = conn.CreateCommand())
                {

                    command.CommandText = "SELECT count(*) FROM information_schema.referential_constraints where constraint_schema='public'";

                    using (var result = await command.ExecuteReaderAsync())
                    {
                        if (result.HasRows)
                        {
                            //check the values
                            await result.ReadAsync();
                            actualForeignKeyConstraints = result.GetInt64(0);
                        }
                        else
                        {
                            var err = "E1030 - Database integrity check failed, could not obtain FOREIGN KEY CONSTRAINT data. Contact support.";
                            _log.LogCritical(err);
                            throw new ApplicationException(err);
                        }
                    }
                }

                //VIEWS
                using (var command = conn.CreateCommand())
                {

                    command.CommandText = "SELECT count(*) FROM information_schema.views where table_schema='public'";

                    using (var result = await command.ExecuteReaderAsync())
                    {
                        if (result.HasRows)
                        {
                            //check the values
                            await result.ReadAsync();
                            actualViews = result.GetInt64(0);
                        }
                        else
                        {
                            var err = "E1030 - Database integrity check failed, could not obtain VIEW data. Contact support.";
                            _log.LogCritical(err);
                            throw new ApplicationException(err);
                        }
                    }
                }

                //ROUTINES
                using (var command = conn.CreateCommand())
                {

                    command.CommandText = "SELECT count(*) FROM information_schema.routines where routine_schema='public'";

                    using (var result = await command.ExecuteReaderAsync())
                    {
                        if (result.HasRows)
                        {
                            //check the values
                            await result.ReadAsync();
                            actualRoutines = result.GetInt64(0);
                        }
                        else
                        {
                            var err = "E1030 - Database integrity check failed, could not obtain ROUTINE data. Contact support.";
                            _log.LogCritical(err);
                            throw new ApplicationException(err);
                        }
                    }
                }
                await conn.CloseAsync();



                if (ExpectedColumns != actualColumns
                    || ExpectedIndexes != actualIndexes
                    || ExpectedCheckConstraints != actualCheckConstraints
                    || ExpectedForeignKeyConstraints != actualForeignKeyConstraints
                    || ExpectedRoutines != actualRoutines
                    || ExpectedViews != actualViews)
                {
                    var err = $"E1030 - Database integrity check failed (C{actualColumns}:I{actualIndexes}:CC{actualCheckConstraints}:FC{actualForeignKeyConstraints}:V{actualViews}:R{actualRoutines})";
                    _log.LogCritical(err);
                    throw new ApplicationException(err);
                }
                return;
            }
        }


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

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

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = $"SELECT count(*) FROM {TableName}";
                    using (var result = await command.ExecuteReaderAsync())
                    {
                        if (result.HasRows)
                        {
                            await result.ReadAsync();
                            ret = result.GetInt64(0);
                        }
                    }
                }
                await conn.CloseAsync();
            }
            return ret;
        }


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

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

                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 = await command.ExecuteReaderAsync())
                    {
                        if (result.HasRows)
                        {
                            while (await result.ReadAsync())
                            {
                                ret.Add(result.GetString(0));
                            }
                        }
                    }
                }
                await conn.CloseAsync();
            }
            return ret;
        }






        #endregion




    }//eoc

}//eons