using Dapper;
using GB5Shared.Connection;
using GB5Shared.Enums.ReportOrchestration;
using GB5Shared.Query.FrameWork.DbConnection;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Npgsql;
using System.Data;

namespace FrameworkBLL.ReportOrchestration
{
    /// <summary>
    /// Per-request cache of MSERVERCONFIG rows keyed by SERVERCONFIGID.
    /// Loaded lazily from the system DB on first access; subsequent lookups
    /// within the same HTTP request are served from the in-memory dictionary.
    /// </summary>
    public sealed class ServerConfigCache : IServerConfigCache
    {
        private readonly IApplicationConnection _appConnection;
        private readonly IConfiguration _configuration;
        private readonly ILogger<ServerConfigCache> _logger;
        private readonly Dictionary<int, CachedServerConfig> _cache = new();

        public ServerConfigCache(
            IApplicationConnection appConnection,
            IConfiguration configuration,
            ILogger<ServerConfigCache> logger)
        {
            _appConnection = appConnection;
            _configuration = configuration;
            _logger        = logger;
        }

        public async Task<CachedServerConfig> GetAsync(
            int serverConfigId,
            CancellationToken cancellationToken = default)
        {
            if (_cache.TryGetValue(serverConfigId, out var cached))
                return cached;

            var row = await LoadFromSystemDbAsync(serverConfigId, cancellationToken)
                .ConfigureAwait(false);

            _cache[serverConfigId] = row;
            return row;
        }

        private async Task<CachedServerConfig> LoadFromSystemDbAsync(
            int serverConfigId,
            CancellationToken cancellationToken)
        {
            string systemConnStr = await _appConnection.Gb5SystemConnectionString()
                .ConfigureAwait(false);

            int dbType = _configuration.GetValue<int>("Gb5SystemDTO:DataBaseType", 0);

            using IDbConnection conn = dbType switch
            {
                1 => new NpgsqlConnection(systemConnStr),
                _ => new SqlConnection(systemConnStr)
            };

            var row = await conn.QueryFirstOrDefaultAsync<RawServerConfig>(
                ConnectionQueryBuilder.GET_MSERVERCONFIG_BY_ID,
                new { ServerConfigId = serverConfigId }).ConfigureAwait(false);

            if (row is null)
                throw new InvalidOperationException(
                    $"MSERVERCONFIG row not found for ServerConfigId={serverConfigId}.");

            string decryptedPwd = _appConnection.Decrypt(row.DatabasePassword, "GB5");

            _logger.LogDebug(
                "ServerConfigCache loaded ServerConfigId={Id} ProviderType={Provider}",
                row.ServerConfigId, row.ProviderType);

            return new CachedServerConfig
            {
                ServerConfigId        = row.ServerConfigId,
                ProviderType          = (DatabaseProviderType)row.ProviderType,
                DatabaseName          = row.DatabaseName,
                DatabaseUserName      = row.DatabaseUserName,
                DatabasePassword      = decryptedPwd,
                DatabasePort          = row.DatabasePort,
                ServerIp              = row.ServerIp,
                ServerMachineName     = row.ServerMachineName,
                ReportServerConfigId  = row.ReportServerConfigId,
                ArchiveServerConfigId = row.ArchiveServerConfigId,
                Nature                = row.Nature
            };
        }

        // Dapper projection — internal only, not exposed outside this class
        private sealed class RawServerConfig
        {
            public int    ServerConfigId        { get; init; }
            public byte   ProviderType          { get; init; }
            public string DatabaseName          { get; init; } = string.Empty;
            public string DatabaseUserName      { get; init; } = string.Empty;
            public string DatabasePassword      { get; init; } = string.Empty;
            public string DatabasePort          { get; init; } = string.Empty;
            public byte   Nature                { get; init; }
            public int    ReportServerConfigId  { get; init; } = -1;
            public int    ArchiveServerConfigId { get; init; } = -1;
            public string ServerIp              { get; init; } = string.Empty;
            public string ServerMachineName     { get; init; } = string.Empty;
        }
    }
}