using Dapper;
using GB5Shared.DTO.Framework.CommonConfig;
using GB5Shared.DTO.Framework.HybridCache;
using Npgsql;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Framework.ServerConfig;
using GB5Shared.GB5Exception;
using GB5Shared.Query.FrameWork.DbConnection;
using GB5Shared.Telemetry.Database;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using Org.BouncyCastle.Crypto.Engines;
using Org.BouncyCastle.Crypto.Modes;
using Org.BouncyCastle.Crypto.Parameters;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Reflection.Metadata;
using System.Runtime.Serialization;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using static GB5Shared.DTO.Framework.Enum.FrameworkEnumDTO;
using static GB5Shared.GB5Constant.Constant;

namespace GB5Shared.Connection
{
    public class ApplicationConnection : IApplicationConnection
    {
        private readonly IOptionsSnapshot<Gb5SystemDTO> _DataBaseDTO;
        private readonly Microsoft.Extensions.Caching.Hybrid.HybridCache _cache;
        private readonly ILogger<ApplicationConnection> _logger;

        // ── Connection pool configuration (read once from appsettings) ───────────────
        // Defaults: MaxPoolSize=100 (suitable for SaaS multi-tenant; raise to 200+ for
        // dedicated on-prem single-tenant deployments via ConnectionPool:MaxPoolSize).
        // The old hardcoded value of 250 per tenant was safe for single-tenant but
        // unsustainable for SaaS deployments with 100+ tenants (25,000+ potential
        // SQL Server connections from a single app server).
        private readonly int _maxPoolSize;
        private readonly int _connectTimeoutSeconds;
        private readonly int _connectionLifetimeSeconds;

        // Sentinel stored in HybridCache when a connection name has no DB config.
        // Lets the cache store a positive result (not an exception) so HybridCache
        // doesn't re-query the DB on every request, while still throwing
        // DataNotFoundException dynamically for callers on every cache hit.
        private const string _connNotFoundSentinel = "\x00NOT_FOUND";

        // ── System connection string cache ───────────────────────────────────────────
        // Gb5SystemConnectionString() runs Regex + BouncyCastle AES-GCM decrypt on
        // every call. Called by SessionHeartbeatMiddleware on every authenticated
        // request. The system DB config is static at runtime (changes only on restart),
        // so we cache the result keyed on the raw (encrypted) config string.
        // If the config file is changed and reloaded, the key changes and the cache
        // transparently re-decrypts.
        private static readonly System.Collections.Concurrent.ConcurrentDictionary<string, string>
            _sysConnStrCache = new();
        // ─────────────────────────────────────────────────────────────────────────────

        public ApplicationConnection(
            IOptionsSnapshot<Gb5SystemDTO> DataBaseDTO,
            Microsoft.Extensions.Caching.Hybrid.HybridCache HybridCache,
            ILogger<ApplicationConnection> logger,
            IConfiguration configuration)
        {
            _DataBaseDTO = DataBaseDTO;
            _cache       = HybridCache;
            _logger      = logger;

            var poolSection          = configuration.GetSection("ConnectionPool");
            _maxPoolSize             = poolSection.GetValue<int>("MaxPoolSize",              100);
            _connectTimeoutSeconds   = poolSection.GetValue<int>("ConnectTimeoutSeconds",    120);
            _connectionLifetimeSeconds = poolSection.GetValue<int>("ConnectionLifetimeSeconds", 20);
        }
        // Two incompatible conventions exist for which LoginDTO field carries the real
        // MSERVERCONFIG.CONNECTIONNAME, and this resolver has to serve both:
        //   - AuthenticationBLL (real user sessions) never sets ConnectionDatabaseName at all —
        //     it puts the connection name into LoginDTO.DatabaseName instead (confusing given the
        //     name, but that's the live, working convention for every real request today).
        //   - Background-service tenant loops (SysJobExecutorQuartzJob, OutboxPublishQuartzJob,
        //     OrphanCleanup, WorkFlowBLL) correctly set ConnectionDatabaseName = tenant.ConnectionName
        //     and separately set DatabaseName to the actual physical database name — so for THESE,
        //     DatabaseName is the wrong field, which is what broke TRANSQC (ConnectionName) vs
        //     "TransactionTest" (its DatabaseName) — live-caught as a DataNotFoundException hitting
        //     OutboxPublishQuartzJob, WorkflowAutoApprove, and orphan cleanup simultaneously.
        // Preferring ConnectionDatabaseName when it's populated, and falling back to DatabaseName
        // when it isn't, satisfies both call shapes without touching the authentication flow.
        private static string ResolveConnectionName(LoginDTO LoginDTO) =>
            !string.IsNullOrWhiteSpace(LoginDTO.ConnectionDatabaseName)
                ? LoginDTO.ConnectionDatabaseName
                : LoginDTO.DatabaseName;

        // Same MSERVERCONFIG row DBConnectionStringCached resolves, just returning DbType
        // instead of a connection string — a tenant's own DATABASETYPE can differ from the
        // GB5 system's own default (a different physical server, a different RDBMS entirely).
        // Falls back to the global default only when no per-tenant row can be resolved at all,
        // so callers never hard-fail just because this specific lookup can't run.
        public async Task<int> DatabaseTypeCached(LoginDTO LoginDTO) =>
            await DatabaseTypeCached(ResolveConnectionName(LoginDTO));

        public async Task<int> DatabaseTypeCached(string ConnectionName)
        {
            try
            {
                string cacheKey = $"conn_dbtype:{ConnectionName}";
                var result = await _cache.GetOrCreateAsync(cacheKey, async (cancellationToken) =>
                {
                    var serverConfig = await DatabaseConnectionObjectConnectionName(ConnectionName);
                    return serverConfig != null ? (int)serverConfig.DbType : -1;
                });

                if (result != -1)
                    return result;

                _logger.LogWarning(
                    "DatabaseTypeCached: no server configuration found for connection name '{ConnectionName}' — " +
                    "falling back to the global default DataBaseType", ConnectionName);
                return _DataBaseDTO.Value.DataBaseType;
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex,
                    "DatabaseTypeCached: failed to resolve DbType for connection name '{ConnectionName}' — " +
                    "falling back to the global default DataBaseType", ConnectionName);
                return _DataBaseDTO.Value.DataBaseType;
            }
        }

        // Same MSERVERCONFIG row DatabaseTypeCached resolves, just returning the tenant's
        // Keycloak auth-resolution fields instead of DbType. Falls back to AuthMode=0 (Native)
        // — never expect/validate a Keycloak token for this tenant — both when no per-tenant row
        // can be resolved at all and when the row exists but AuthMode is Native, so callers never
        // hard-fail just because this lookup can't run.
        public async Task<TenantAuthConfigDTO> AuthConfigCached(LoginDTO LoginDTO) =>
            await AuthConfigCached(ResolveConnectionName(LoginDTO));

        public async Task<TenantAuthConfigDTO> AuthConfigCached(string ConnectionName)
        {
            var nativeDefault = new TenantAuthConfigDTO { AuthMode = 0 };
            try
            {
                string cacheKey = $"conn_authconfig:{ConnectionName}";
                return await _cache.GetOrCreateAsync(cacheKey, async (cancellationToken) =>
                {
                    var serverConfig = await DatabaseConnectionObjectConnectionName(ConnectionName);
                    if (serverConfig == null)
                        return nativeDefault;

                    return new TenantAuthConfigDTO
                    {
                        AuthMode = serverConfig.AuthMode,
                        KeycloakHost = serverConfig.KeycloakHost,
                        KeycloakRealm = serverConfig.KeycloakRealm,
                        KeycloakAdminVaultPath = serverConfig.KeycloakAdminVaultPath,
                        VaultAddress = serverConfig.VaultAddress
                    };
                });
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex,
                    "AuthConfigCached: failed to resolve auth config for connection name '{ConnectionName}' — " +
                    "falling back to AuthMode=Native", ConnectionName);
                return nativeDefault;
            }
        }

        public async Task<string> DBConnectionStringCached(LoginDTO LoginDTO)
        {
            try
            {
                string connectionName = ResolveConnectionName(LoginDTO);
                string cacheKey = CacheKeyGenerator.KeyGeneration(-1, Entity.ServerConfig, CacheKeyLevel.DB_SERVER, connectionName, LoginDTO, null);
                var result = await _cache.GetOrCreateAsync(cacheKey, async (cancellationToken) =>
                {
                    var cs = await FormConnectionString(connectionName);
                    if (string.IsNullOrEmpty(cs))
                    {
                        _logger.LogWarning("No server configuration found for connection name '{ConnectionName}'", connectionName);
                        return _connNotFoundSentinel;
                    }
                    _logger.LogInformation("DBConnectionstring resolved for connection name: {ConnectionName}", connectionName);
                    return cs;
                });

                if (result == _connNotFoundSentinel)
                    throw new DataNotFoundException($"No server configuration found for connection name: {connectionName}");

                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> DBConnectionStringConnectionName(LoginDTO LoginDTO)
        {
            try
            {
                // Same ConnectionName-vs-DatabaseName resolution as DBConnectionStringCached(LoginDTO)
                // above — see ResolveConnectionName's comment. This overload has no live callers today.
                string connectionName = ResolveConnectionName(LoginDTO);
                string cacheKey = CacheKeyGenerator.KeyGeneration(-1, Entity.ServerConfig, CacheKeyLevel.DB_SERVER, connectionName, LoginDTO, null!);
                var result = await _cache.GetOrCreateAsync(cacheKey, async (cancellationToken) =>
                {
                    var cs = await FormConnectionString(connectionName);
                    if (string.IsNullOrEmpty(cs))
                    {
                        _logger.LogWarning("No server configuration found for connection name '{ConnectionName}'", connectionName);
                        return _connNotFoundSentinel;
                    }
                    _logger.LogInformation("DBConnectionstring came here: {DBConnectionstring}", cs);
                    return cs;
                });

                if (result == _connNotFoundSentinel)
                    throw new DataNotFoundException($"No server configuration found for connection name: {connectionName}");

                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }


        public async Task<string> DBConnectionStringCached(string ConnectionName)
        {
            try
            {
                string cacheKey = $"conn_str:{ConnectionName}";
                var result = await _cache.GetOrCreateAsync(cacheKey, async (cancellationToken) =>
                {
                    var cs = await FormConnectionString(ConnectionName);
                    if (string.IsNullOrEmpty(cs))
                    {
                        _logger.LogWarning("No server configuration found for connection name: '{ConnectionName}'", ConnectionName);
                        return _connNotFoundSentinel;
                    }
                    _logger.LogInformation("DBConnectionstring resolved for ConnectionName: {ConnectionName}", ConnectionName);
                    return cs;
                });

                if (result == _connNotFoundSentinel)
                    throw new DataNotFoundException($"No server configuration found for connection name: {ConnectionName}");

                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<string> DBConnectionStringCachedConnectionName(string ConnectionName)
        {
            try
            {
                string cacheKey = $"conn_str_name:{ConnectionName}";
                var result = await _cache.GetOrCreateAsync(cacheKey, async (cancellationToken) =>
                {
                    var cs = await FormConnectionStringConnectionName(ConnectionName);
                    if (string.IsNullOrEmpty(cs))
                    {
                        _logger.LogWarning("No server configuration found for connection name: '{ConnectionName}'", ConnectionName);
                        return _connNotFoundSentinel;
                    }
                    _logger.LogInformation("DBConnectionstring resolved for ConnectionName: {ConnectionName}", ConnectionName);
                    return cs;
                });

                if (result == _connNotFoundSentinel)
                    throw new DataNotFoundException($"No server configuration found for connection name: {ConnectionName}");

                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<ServerConfigDTO> DatabaseConnectionObject(string ConnectionName)
        {
            try
            {
                var (DataBaseType, GB5SystemConnectionString) = await Gb5SystemConnectionInfo();

                _logger.LogInformation("GB5SystemConnectionString: {ConnectionString}", GB5SystemConnectionString);

                Activity.Current?.SetTag("Database.ConnectionName", ConnectionName);

                string sql = ConnectionQueryBuilder.LOAD_DIFFERENT_DATABASE_NAME_FOR_PRODUCTION_CONNECTIONNNAME;

                // ==========================================================
                //  GENERIC DB CONNECTION (SQL / PG)
                // ==========================================================
                using IDbConnection connection = DataBaseType switch
                {
                    DBTYPE.SQL => new SqlConnection(GB5SystemConnectionString),
                    DBTYPE.POSTGRESQL => new NpgsqlConnection(GB5SystemConnectionString),
                    _ => throw new NotSupportedException($"Unsupported DB Type: {DataBaseType}")
                };

                // ==========================================================
                //  EXECUTE QUERY USING DAPPER
                // ==========================================================
                var queryParams = new { CONNECTIONNAME = ConnectionName };
                using var dbActivity = DatabaseActivityHelper.StartDbActivity(sql, ConnectionName, queryParams);
                ServerConfigDTO? result;
                try
                {
                    result = await connection.QueryFirstOrDefaultAsync<ServerConfigDTO>(sql, queryParams);
                    dbActivity?.SetStatus(ActivityStatusCode.Ok);
                }
                catch (Exception dbEx)
                {
                    DatabaseActivityHelper.RecordDbError(dbActivity, dbEx);
                    throw;
                }

                string json = JsonConvert.SerializeObject(result);
                if (result == null)
                {
                    throw new DataNotFoundException($"No server configuration found for connection name: {ConnectionName}");
                }

                return result;
            }
            catch (SqlException sqlEx)
            {
                throw new DatabaseOperationException($"SQL Server query failed. {sqlEx.Message}", sqlEx);
            }
            catch (PostgresException pgEx)
            {
                throw new DatabaseOperationException(
                    $"PostgreSQL query failed: {pgEx.Message}",
                    pgEx
                );
            }

            catch (Exception ex)
            {
                throw new ApplicationException($"Unexpected error while retrieving server configuration.{ex.Message}", ex);
            }
        }

        // Cached reverse lookup — realm to tenant. Short TTL cache mirrors AuthConfigCached's own
        // pattern; a null result is NOT cached (an unresolved realm today might be provisioned a
        // moment later by Entitlement's onboarding flow, and this is a low-volume lookup — only
        // hit once per Keycloak-authenticated request's dual-mode bridge, not every query).
        public async Task<ServerConfigDTO?> DatabaseConnectionObjectByKeycloakRealmCached(string realm)
        {
            string cacheKey = $"conn_by_kcrealm:{realm}";
            var cached = await _cache.GetOrCreateAsync(cacheKey, async (cancellationToken) =>
            {
                var result = await DatabaseConnectionObjectByKeycloakRealm(realm);
                return result ?? _connNotFoundServerConfigSentinel;
            });
            return ReferenceEquals(cached, _connNotFoundServerConfigSentinel) ? null : cached;
        }

        private static readonly ServerConfigDTO _connNotFoundServerConfigSentinel = new();

        private async Task<ServerConfigDTO?> DatabaseConnectionObjectByKeycloakRealm(string realm)
        {
            try
            {
                var (DataBaseType, GB5SystemConnectionString) = await Gb5SystemConnectionInfo();

                string sql = ConnectionQueryBuilder.RESOLVE_TENANT_BY_KEYCLOAK_REALM;

                using IDbConnection connection = DataBaseType switch
                {
                    DBTYPE.SQL        => new SqlConnection(GB5SystemConnectionString),
                    DBTYPE.POSTGRESQL => new NpgsqlConnection(GB5SystemConnectionString),
                    _                 => throw new NotSupportedException($"Unsupported DB Type: {DataBaseType}")
                };

                var queryParams = new { Realm = realm };
                using var dbActivity = DatabaseActivityHelper.StartDbActivity(sql, realm, queryParams);
                ServerConfigDTO? result;
                try
                {
                    result = await connection.QueryFirstOrDefaultAsync<ServerConfigDTO>(sql, queryParams);
                    dbActivity?.SetStatus(ActivityStatusCode.Ok);
                }
                catch (Exception dbEx)
                {
                    DatabaseActivityHelper.RecordDbError(dbActivity, dbEx);
                    throw;
                }

                if (result == null)
                    _logger.LogWarning("DatabaseConnectionObjectByKeycloakRealm: no tenant found for Keycloak realm '{Realm}'", realm);

                return result;
            }
            catch (SqlException sqlEx)
            {
                throw new DatabaseOperationException($"SQL Server query failed. {sqlEx.Message}", sqlEx);
            }
            catch (PostgresException pgEx)
            {
                throw new DatabaseOperationException($"PostgreSQL query failed: {pgEx.Message}", pgEx);
            }
        }

        public async Task<ServerConfigDTO?> DatabaseConnectionObjectConnectionName(string ConnectionName)
        {
            try
            {
                var (DataBaseType, GB5SystemConnectionString) = await Gb5SystemConnectionInfo();

                Activity.Current?.SetTag("Database.ConnectionName", ConnectionName);

                string sql = ConnectionQueryBuilder.LOAD_DIFFERENT_DATABASE_NAME_FOR_PRODUCTION_CONNECTIONNNAME;

                using IDbConnection connection = DataBaseType switch
                {
                    DBTYPE.SQL        => new SqlConnection(GB5SystemConnectionString),
                    DBTYPE.POSTGRESQL => new NpgsqlConnection(GB5SystemConnectionString),
                    _                 => throw new NotSupportedException($"Unsupported DB Type: {DataBaseType}")
                };

                var queryParams = new { CONNECTIONNAME = ConnectionName };
                using var dbActivity = DatabaseActivityHelper.StartDbActivity(sql, ConnectionName, queryParams);
                ServerConfigDTO? result;
                try
                {
                    result = await connection.QueryFirstOrDefaultAsync<ServerConfigDTO>(sql, queryParams);
                    dbActivity?.SetStatus(ActivityStatusCode.Ok);
                }
                catch (Exception dbEx)
                {
                    DatabaseActivityHelper.RecordDbError(dbActivity, dbEx);
                    throw;
                }

                if (result == null)
                {
                    _logger.LogWarning("No server configuration found for connection name: {ConnectionName}", ConnectionName);
                    return null;
                }

                return result;
            }
            catch (SqlException sqlEx)
            {
                throw new DatabaseOperationException($"SQL Server query failed. {sqlEx.Message}", sqlEx);
            }
            catch (PostgresException pgEx)
            {
                throw new DatabaseOperationException($"PostgreSQL query failed: {pgEx.Message}", pgEx);
            }
            catch (Exception ex)
            {
                throw new ApplicationException($"Unexpected error while retrieving server configuration. {ex.Message}", ex);
            }
        }


        // Resolves which Gb5System config value is actually usable, instead of trusting
        // DataBaseType blindly. If the string matching the configured DataBaseType is
        // blank (e.g. a stale/copied config value), falls back to whichever of
        // Gb5System / Gb5SystemPG is actually populated so a config mismatch degrades
        // gracefully instead of hard-failing at startup.
        private (int EffectiveDataBaseType, string RawConnectionString) ResolveConfiguredGb5System()
        {
            var databaseConfig = _DataBaseDTO.Value;
            int configuredType = databaseConfig.DataBaseType;

            bool hasSql = !string.IsNullOrWhiteSpace(databaseConfig.Gb5System);
            bool hasPg  = !string.IsNullOrWhiteSpace(databaseConfig.Gb5SystemPG);

            if (configuredType == DBTYPE.SQL && hasSql)
                return (DBTYPE.SQL, databaseConfig.Gb5System);
            if (configuredType == DBTYPE.POSTGRESQL && hasPg)
                return (DBTYPE.POSTGRESQL, databaseConfig.Gb5SystemPG);
            if (configuredType == DBTYPE.POSTGRESQL && hasSql)
                return (DBTYPE.POSTGRESQL, databaseConfig.Gb5System); // legacy format: PG string stored under Gb5System

            // Configured DataBaseType has no matching value configured — dynamically
            // fall back to whichever connection string is actually populated.
            if (hasSql)
                return (DBTYPE.SQL, databaseConfig.Gb5System);
            if (hasPg)
                return (DBTYPE.POSTGRESQL, databaseConfig.Gb5SystemPG);

            throw new InvalidOperationException(
                $"Gb5System connection string is not configured for DataBaseType={configuredType}. " +
                "Check 'Gb5SystemDTO.Gb5System' (or 'Gb5SystemPG' for PostgreSQL) in appsettings.");
        }

        public async Task<(int DataBaseType, string ConnectionString)> Gb5SystemConnectionInfo()
        {
            var (effectiveType, rawConnectionString) = ResolveConfiguredGb5System();

            // Fast path: return cached decrypted string if the raw config hasn't changed.
            // The cache key is the raw (encrypted) string — if appsettings is reloaded with
            // a new password, the key changes and the decryption runs once for the new value.
            if (_sysConnStrCache.TryGetValue(rawConnectionString, out string? cached))
                return (effectiveType, cached);

            // Slow path: extract and decrypt password (runs at most once per unique config value)
            Match match = Regex.Match(rawConnectionString, @"Password=([^;]+)");
            if (!match.Success)
                throw new FormatException("Invalid connection string format. 'Password=' not found.");

            string encryptedPass = match.Groups[1].Value.Trim();
            string decryptedPass = Decrypt(encryptedPass, "GB5");
            string connectionString;

            if (effectiveType == DBTYPE.SQL)
            {
                connectionString = rawConnectionString
                    .Replace("Password=" + encryptedPass, "Password=" + decryptedPass)
                    + ";Encrypt=False;TrustServerCertificate=True";
            }
            else
            {
                connectionString = rawConnectionString
                    .Replace("Password=" + encryptedPass, "Password=" + decryptedPass);
            }

            // Cache and return — TryAdd is thread-safe; concurrent first calls produce
            // the same value, and TryGetValue wins on the next request
            _sysConnStrCache.TryAdd(rawConnectionString, connectionString);
            return (effectiveType, connectionString);
        }

        public async Task<string> Gb5SystemConnectionString()
        {
            var (_, connectionString) = await Gb5SystemConnectionInfo();
            return connectionString;
        }

        //private string TestGb5SystemConnectionString()
        //{
        //    try
        //    {
        //        var databaseConfig = _DataBaseDTO.Value;
        //        string Gb5System = databaseConfig.Gb5System;
        //        Match match = Regex.Match(Gb5System, @"Password=([^;]+)");
        //        if (!match.Success)
        //        {
        //            throw new FormatException("Invalid connection string format. 'Password=' not found.");
        //        }
        //        string EncryptedPass = match.Groups[1].Value.Trim();
        //        string DecryptedPass = Decrypt(EncryptedPass, "GB5");
        //        string ConnectionString = Gb5System.Replace("Password=" + EncryptedPass, "Password=" + DecryptedPass)
        //                + ";Encrypt=False;TrustServerCertificate=True";

        //        return Gb5System;
        //    }
        //    catch (Exception)
        //    {
        //        throw;
        //    }
        //}

        // Exact port of GB4's CommonFunctionFrameDAL.DecryptPasswordHash (MD5-hashed key + TripleDES/ECB) —
        // MGSP/MGST secrets were encrypted with this algorithm, not AES/SHA256, so it must match exactly.
        public string DecryptPasswordHash(string CipherString, bool UseHashing)
        {
            try
            {
                byte[] keyArray;
                byte[] toEncryptArray = Convert.FromBase64String(CipherString);
                string key = "Password";
                if (UseHashing)
                {
                    MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
                    keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
                    hashmd5.Clear();
                }
                else
                {
                    keyArray = UTF8Encoding.UTF8.GetBytes(key);
                }

                TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
                tdes.Key = keyArray;
                tdes.Mode = CipherMode.ECB;
                tdes.Padding = PaddingMode.PKCS7;

                ICryptoTransform cTransform = tdes.CreateDecryptor();
                byte[] resultArray = cTransform.TransformFinalBlock(
                                     toEncryptArray, 0, toEncryptArray.Length);
                tdes.Clear();
                return UTF8Encoding.UTF8.GetString(resultArray);
            }
            catch (Exception Error)
            {
                throw Error;
            }
        }


        public string Encrypt(string plaintext, string key = "GB5")
        {
            if (string.IsNullOrEmpty(key))
            {
                throw new ArgumentException("Key cannot be null or empty.");
            }

            // Derive a 32-byte key from the string using SHA-256
            using SHA256 sha256 = SHA256.Create();
            byte[] keyBytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(key));

            // Generate a 12-byte IV (nonce) for AES-GCM
            byte[] iv = new byte[12];
            using (var rng = RandomNumberGenerator.Create())
            {
                rng.GetBytes(iv);
            }

            byte[] plaintextBytes = Encoding.UTF8.GetBytes(plaintext);
            byte[] ciphertext = new byte[plaintextBytes.Length + 16]; // Extra 16 bytes for authentication tag

            // Encrypt using BouncyCastle AES-GCM
            GcmBlockCipher cipher = new(new AesEngine());
            AeadParameters parameters = new(new KeyParameter(keyBytes), 128, iv);
            cipher.Init(true, parameters);

            int len = cipher.ProcessBytes(plaintextBytes, 0, plaintextBytes.Length, ciphertext, 0);
            cipher.DoFinal(ciphertext, len); // DoFinal() already includes the authentication tag

            // Concatenate IV + ciphertext (which includes the tag)
            byte[] result = new byte[iv.Length + ciphertext.Length];
            Buffer.BlockCopy(iv, 0, result, 0, iv.Length);
            Buffer.BlockCopy(ciphertext, 0, result, iv.Length, ciphertext.Length);

            return Convert.ToBase64String(result);
        }




        public string Decrypt(string encryptedText, string key)
        {
            if (string.IsNullOrEmpty(key))
            {
                throw new ArgumentException("Key cannot be null or empty.");
            }

            byte[] encryptedBytes = Convert.FromBase64String(encryptedText);

            using SHA256 sha256 = SHA256.Create();
            byte[] keyBytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(key));

            // Extract IV and ciphertext
            byte[] iv = new byte[12];
            byte[] ciphertext = new byte[encryptedBytes.Length - iv.Length];

            Buffer.BlockCopy(encryptedBytes, 0, iv, 0, iv.Length);
            Buffer.BlockCopy(encryptedBytes, iv.Length, ciphertext, 0, ciphertext.Length);

            // Decrypt using BouncyCastle AES-GCM
            GcmBlockCipher cipher = new(new AesEngine());
            AeadParameters parameters = new(new KeyParameter(keyBytes), 128, iv);
            cipher.Init(false, parameters);

            byte[] decryptedBytes = new byte[ciphertext.Length - 16]; // Remove the 16-byte authentication tag
            int len = cipher.ProcessBytes(ciphertext, 0, ciphertext.Length, decryptedBytes, 0);
            cipher.DoFinal(decryptedBytes, len);

            return Encoding.UTF8.GetString(decryptedBytes);
        }

        private async Task<string> FormConnectionStringConnectionName(string ConnectionName)
        {
            ServerConfigDTO? ServerConfigDTO;
            try
            {
                ServerConfigDTO = await DatabaseConnectionObjectConnectionName(ConnectionName);
                if (ServerConfigDTO == null)
                    return string.Empty;

                string ConnectionString = "";
                if (ServerConfigDTO.DbType == DBType.SQL)
                {
                    string sqlPass;
                    try { sqlPass = Decrypt(ServerConfigDTO.DatabasePassword, "GB5"); }
                    catch (Exception ex) { throw new InvalidOperationException($"Failed to decrypt password for '{ServerConfigDTO.DatabaseName}'", ex); }
                    ConnectionString = "Data Source=" + ServerConfigDTO.ServerIP + ";" + "Initial Catalog=" + ServerConfigDTO.DatabaseName + ";" + "User Id=" + ServerConfigDTO.DatabaseUserName
                         + ";" + "Password=" + sqlPass + ";"
                         + $"Pooling=true;Connection Timeout={_connectTimeoutSeconds};Max Pool Size={_maxPoolSize};Connection Lifetime={_connectionLifetimeSeconds}"
                         + ";Encrypt=False;TrustServerCertificate=True";
                    _logger.LogInformation("ConnectionString for db : {ConnectionString}", ConnectionString);

                }
                else if (ServerConfigDTO.DbType == DBType.Oracle)
                {
                    throw new NotSupportedException("Oracle connection is not yet supported.");
                }
                else if (ServerConfigDTO.DbType == DBType.PostGre)
                {
                    string pgPass1;
                    try { pgPass1 = Decrypt(ServerConfigDTO.DatabasePassword, "GB5"); }
                    catch (Exception ex) { throw new InvalidOperationException($"Failed to decrypt password for '{ServerConfigDTO.DatabaseName}'", ex); }
                    var username = ServerConfigDTO.DatabaseUserName.ToLower();
                    ConnectionString =
                        "Host=" + ServerConfigDTO.ServerIP + ";" +
                        "Port=5432;" +
                        "Database=" + ServerConfigDTO.DatabaseName + ";" +
                        "Username=" + username + ";" +
                        "Password=" + pgPass1 + ";" +
                        $"Pooling=true;Timeout={_connectTimeoutSeconds};Maximum Pool Size={_maxPoolSize};";

                    _logger.LogInformation("PostgreSQL ConnectionString : {ConnectionString}", ConnectionString);
                }
                else if (ServerConfigDTO.DbType == DBType.MySQL)
                {
                    throw new NotSupportedException("MySQL connection is not yet supported.");
                }

                return ConnectionString;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"Error forming connection string for connection name: {ConnectionName}", ConnectionName);
                throw;
            }
        }


        private async Task<string> FormConnectionString(string ConnectionName)
        {
            ServerConfigDTO? ServerConfigDTO;
            try
            {
                ServerConfigDTO = await DatabaseConnectionObjectConnectionName(ConnectionName);
                if (ServerConfigDTO == null)
                    return string.Empty;

                string ConnectionString = "";
                if (ServerConfigDTO.DbType == DBType.SQL)
                {
                    string sqlPass;
                    try { sqlPass = Decrypt(ServerConfigDTO.DatabasePassword, "GB5"); }
                    catch (Exception ex) { throw new InvalidOperationException($"Failed to decrypt password for '{ServerConfigDTO.DatabaseName}'", ex); }
                    ConnectionString = "Data Source=" + ServerConfigDTO.ServerIP + ";" + "Initial Catalog=" + ServerConfigDTO.DatabaseName + ";" + "User Id=" + ServerConfigDTO.DatabaseUserName
                         + ";" + "Password=" + sqlPass + ";"
                         + $"Pooling=true;Connection Timeout={_connectTimeoutSeconds};Max Pool Size={_maxPoolSize};Connection Lifetime={_connectionLifetimeSeconds}"
                         + ";Encrypt=False;TrustServerCertificate=True";
                    _logger.LogInformation("ConnectionString for db : {ConnectionString}", ConnectionString);

                }
                else if (ServerConfigDTO.DbType == DBType.Oracle)
                {
                    throw new NotSupportedException("Oracle connection is not yet supported.");
                }
                else if (ServerConfigDTO.DbType == DBType.PostGre)
                {
                    string pgPass2;
                    try { pgPass2 = Decrypt(ServerConfigDTO.DatabasePassword, "GB5"); }
                    catch (Exception ex) { throw new InvalidOperationException($"Failed to decrypt password for '{ServerConfigDTO.DatabaseName}'", ex); }
                    var username = ServerConfigDTO.DatabaseUserName.ToLower();
                    ConnectionString =
                        "Host=" + ServerConfigDTO.ServerIP + ";" +
                        "Port=5432;" +
                        "Database=" + ServerConfigDTO.DatabaseName + ";" +
                        "Username=" + username + ";" +
                        "Password=" + pgPass2 + ";" +
                        $"Pooling=true;Timeout={_connectTimeoutSeconds};Maximum Pool Size={_maxPoolSize};";

                    _logger.LogInformation("PostgreSQL ConnectionString : {ConnectionString}", ConnectionString);
                }
                else if (ServerConfigDTO.DbType == DBType.MySQL)
                {
                    throw new NotSupportedException("MySQL connection is not yet supported.");
                }

                return ConnectionString;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"Error forming connection string for connection name: {ConnectionName}", ConnectionName);
                throw;
            }
        }




        //public async Task<int> DatabaseOffset(LoginDTO LoginDTO, byte DatabaseType)
        //{
        //    try
        //    {
        //        int DatabaseOffset = 0;

        //        string Sql = "";
        //        if (DatabaseType == DBType.SQL)
        //        {
        //            Sql = "SELECT DATEPART(TZ,SYSDATETIMEOFFSET()) as Dboffset";

        //            DatabaseOffset = await _queryExecutor.QuerySingleAsync<int>(
        //               LoginDTO,
        //            Sql,
        //               null
        //            );
        //        }
        //        else if (DatabaseType == DBType.Oracle)
        //        {
        //            Sql = "SELECT TZ_OFFSET(sessiontimezone) FROM DUAL";

        //            DatabaseOffset = await _queryExecutor.QuerySingleAsync<int>(
        //               LoginDTO,
        //            Sql,
        //               null
        //            );

        //            string TempOffset = Convert.ToString(DatabaseOffset).Trim();
        //            int Final = 0;
        //            negative
        //                string[] Split1 = TempOffset.Split(':');
        //            if (Convert.ToInt32(Split1[0]) * 60 > 0)
        //            {
        //                Final = Convert.ToInt32(Split1[0]) * 60 + Convert.ToInt32(Split1[1]);
        //            }
        //            else if (Convert.ToInt32(Split1[0]) * 60 < 0)
        //            {
        //                Final = Convert.ToInt32(Split1[0]) * 60 - Convert.ToInt32(Split1[1]);
        //            }
        //            else
        //            {
        //                Final = Convert.ToInt32(Split1[1]);
        //            }
        //            DatabaseOffset = Final;
        //        }
        //        else if (DatabaseType == DBType.PostGre)
        //        {
        //            Sql = "SELECT EXTRACT(TIMEZONE FROM now()) FROM now()";
        //            DatabaseOffset = await _queryExecutor.QuerySingleAsync<int>(
        //               LoginDTO,
        //            Sql,
        //               null
        //            );
        //            object TempOffset = tempvalue[0];
        //            int Offset = Convert.ToInt32(TempOffset);
        //            DatabaseOffset = DatabaseOffset / 60;
        //        }
        //        else if (DatabaseType == 3)//MySql
        //        {
        //        }
        //        return DatabaseOffset;
        //    }
        //    catch (Exception)
        //    {
        //        throw;
        //    }
        //}

        public string RandomGenerateAlphaNumeric(int Size)
        {
            const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
            var result = new char[Size];
            var randomBytes = new byte[Size];
            using (var rng = RandomNumberGenerator.Create())
            {
                rng.GetBytes(randomBytes);
            }
            for (int i = 0; i < Size; i++)
                result[i] = chars[randomBytes[i] % chars.Length];
            return new string(result);
        }

        public async Task<SqlConnection> GetDatabaseConnection(LoginDTO login)
        {
            try
            {
                string cacheKey = $"scheduler_conn_str:{login.DatabaseName}";
                string connStr = await _cache.GetOrCreateAsync(cacheKey, async (cancellationToken) =>
                {
                    ServerConfigDTO serverConfig = await DatabaseConnectionObject(login.DatabaseName);

                    if (serverConfig == null)
                        throw new ApplicationException($"Server configuration not found for database {login.DatabaseName}");

                    string template = _DataBaseDTO.Value.SchedulerTemplate;

                    if (string.IsNullOrWhiteSpace(template))
                        throw new ApplicationException("SchedulerTemplate connection string is not configured in appsettings.json.");

                    string decryptedPassword = Decrypt(serverConfig.DatabasePassword, "GB5");
                    var resolved = template
                        .Replace("{DatabaseName}", serverConfig.DatabaseName)
                        .Replace("{UserName}", serverConfig.DatabaseUserName)
                        .Replace("{Password}", decryptedPassword);

                    _logger.LogInformation("Scheduler connection string resolved for {DatabaseName}", login.DatabaseName);
                    return resolved;
                });

                SqlConnection connection = new SqlConnection(connStr);
                await connection.OpenAsync();

                _logger.LogInformation("Connected to DB: {DatabaseName}", login.DatabaseName);

                return connection;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Failed to create connection for {DatabaseName}", login.DatabaseName);
                throw;
            }
        }
        //public async Task<SqlConnection> GetSchedulerTemplateConnection(LoginDTO login)
        //{
        //    try
        //    {
        //        // 1️⃣ Get server configuration
        //        var serverConfig = await DatabaseConnectionObject(login.DatabaseName);
        //        if (serverConfig == null)
        //            throw new ApplicationException($"Server configuration not found for database {login.DatabaseName}");

        //        // 2️⃣ Get SchedulerTemplate from appsettings
        //        string template = _DataBaseDTO.Value.SchedulerTemplate;
        //        if (string.IsNullOrWhiteSpace(template))
        //            throw new ApplicationException("SchedulerTemplate is missing in appsettings.json.");

        //        // 3️⃣ Decrypt password safely
        //        string decryptedPassword = DecryptPass(serverConfig.DatabasePassword, "GB5");

        //        // 4️⃣ Build final connection string
        //        string connStr = template
        //            .Replace("{DatabaseName}", serverConfig.DatabaseName)
        //            .Replace("{UserName}", serverConfig.DatabaseUserName)
        //            .Replace("{Password}", decryptedPassword);

        //        // 5️⃣ Open connection
        //        var connection = new SqlConnection(connStr);
        //        await connection.OpenAsync();

        //        _logger.LogInformation("Connected to DB '{DatabaseName}' successfully.", serverConfig.DatabaseName);
        //        return connection;
        //    }
        //    catch (Exception ex)
        //    {
        //        _logger.LogError(ex, "Failed to create SchedulerTemplate connection for database '{DatabaseName}'", login.DatabaseName);
        //        throw;
        //    }
        //}

    }
}