using GB5Shared.Connection;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.ReportOrchestration;
using GB5Shared.Enums.ReportOrchestration;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using Npgsql;
using System.Data;

namespace FrameworkBLL.ReportOrchestration
{
    /// <summary>
    /// Resolves a closed IDbConnection for the requested DataSourceType,
    /// using MSERVERCONFIG data from IServerConfigCache.
    /// Caches resolved ReportServerConfigDTO instances within the request scope.
    /// </summary>
    public sealed class ReportConnectionResolver : IReportConnectionResolver
    {
        private readonly IServerConfigCache _serverConfigCache;
        private readonly IApplicationConnection _appConnection;
        private readonly ILogger<ReportConnectionResolver> _logger;
        private readonly Dictionary<DataSourceType, ReportServerConfigDTO> _resolved = new();

        public ReportConnectionResolver(
            IServerConfigCache serverConfigCache,
            IApplicationConnection appConnection,
            ILogger<ReportConnectionResolver> logger)
        {
            _serverConfigCache = serverConfigCache;
            _appConnection     = appConnection;
            _logger            = logger;
        }

        public async Task<IDbConnection> ResolveConnectionAsync(
            LoginDTO loginDTO,
            DataSourceType dataSource,
            CancellationToken cancellationToken = default)
        {
            var config = await ResolveConfigAsync(loginDTO, dataSource, cancellationToken)
                .ConfigureAwait(false);

            return config.ProviderType switch
            {
                DatabaseProviderType.SqlServer =>
                    new SqlConnection(config.ConnectionString),
                DatabaseProviderType.PostgreSql =>
                    new NpgsqlConnection(config.ConnectionString),
                DatabaseProviderType.Oracle =>
                    throw new NotSupportedException("Oracle connections are not supported by the report orchestrator."),
                DatabaseProviderType.MySql =>
                    throw new NotSupportedException("MySQL connections are not supported by the report orchestrator."),
                _ => throw new NotSupportedException(
                    $"Unknown DatabaseProviderType: {config.ProviderType}")
            };
        }

        public async Task<ReportServerConfigDTO> ResolveConfigAsync(
            LoginDTO loginDTO,
            DataSourceType dataSource,
            CancellationToken cancellationToken = default)
        {
            if (dataSource == DataSourceType.Auto)
                throw new ArgumentException(
                    "DataSourceType.Auto must be resolved to a concrete source before calling ResolveConfigAsync.",
                    nameof(dataSource));

            if (_resolved.TryGetValue(dataSource, out var cached))
                return cached;

            var config = await BuildConfigAsync(loginDTO, dataSource, cancellationToken)
                .ConfigureAwait(false);

            _resolved[dataSource] = config;
            return config;
        }

        public bool IsDedicatedDbConfigured(LoginDTO loginDTO, DataSourceType dataSource)
        {
            // We can answer this synchronously from cache if already loaded,
            // otherwise it requires an async call — return false (unknown = fallback safe).
            if (_resolved.TryGetValue(dataSource, out var config))
                return config.IsDedicatedAnalyticsDb;
            return false;
        }

        // -----------------------------------------------------------------------

        private async Task<ReportServerConfigDTO> BuildConfigAsync(
            LoginDTO loginDTO,
            DataSourceType dataSource,
            CancellationToken cancellationToken)
        {
            // Always load the OLTP config first — it carries the ReportDB/ArchiveDB IDs
            var oltpConfig = await _serverConfigCache
                .GetAsync(loginDTO.ServerConfigId, cancellationToken).ConfigureAwait(false);

            int targetConfigId = dataSource switch
            {
                DataSourceType.Oltp      => loginDTO.ServerConfigId,
                DataSourceType.ReportDb  => oltpConfig.ReportServerConfigId,
                DataSourceType.ArchiveDb => oltpConfig.ArchiveServerConfigId,
                _ => loginDTO.ServerConfigId
            };

            bool dedicated = dataSource != DataSourceType.Oltp && targetConfigId != -1;

            if (!dedicated)
            {
                // Fall back to OLTP
                if (dataSource != DataSourceType.Oltp)
                    _logger.LogWarning(
                        "No dedicated {DataSource} DB configured for ServerConfigId={Id}; falling back to OLTP.",
                        dataSource, loginDTO.ServerConfigId);

                return BuildDto(oltpConfig, DataSourceType.Oltp, isDedicated: false);
            }

            var targetConfig = await _serverConfigCache
                .GetAsync(targetConfigId, cancellationToken).ConfigureAwait(false);

            return BuildDto(targetConfig, dataSource, isDedicated: true);
        }

        private static ReportServerConfigDTO BuildDto(
            CachedServerConfig cfg,
            DataSourceType resolvedSource,
            bool isDedicated)
        {
            string connStr = cfg.ProviderType switch
            {
                DatabaseProviderType.SqlServer =>
                    $"Data Source={cfg.ServerIp};" +
                    $"Initial Catalog={cfg.DatabaseName};" +
                    $"User Id={cfg.DatabaseUserName};" +
                    $"Password={cfg.DatabasePassword};" +
                    "Pooling=true;Connection Timeout=120;Max Pool Size=250;Connection Lifetime=20;" +
                    "Encrypt=False;TrustServerCertificate=True",

                DatabaseProviderType.PostgreSql =>
                    $"Host={cfg.ServerIp};Port=5432;" +
                    $"Database={cfg.DatabaseName};" +
                    $"Username={cfg.DatabaseUserName.ToLower()};" +
                    $"Password={cfg.DatabasePassword};" +
                    "Pooling=true;Timeout=120;Maximum Pool Size=250",

                _ => throw new NotSupportedException(
                    $"Provider {cfg.ProviderType} is not supported for report connections.")
            };

            return new ReportServerConfigDTO
            {
                ServerConfigId          = cfg.ServerConfigId,
                ProviderType            = cfg.ProviderType,
                ConnectionString        = connStr,
                DatabaseName            = cfg.DatabaseName,
                IsDedicatedAnalyticsDb  = isDedicated,
                ResolvedDataSource      = resolvedSource
            };
        }
    }
}