using System.Security.Claims;
using Npgsql;
using NpgsqlRest;
using NpgsqlRest.HttpFiles;
using NpgsqlRest.TsClient;
using Serilog;
using NpgsqlRest.SqlFileSource;
using Microsoft.AspNetCore.Antiforgery;
using Microsoft.AspNetCore.DataProtection;
using NpgsqlRest.UploadHandlers;
using NpgsqlRest.Auth;
using NpgsqlRest.OpenAPI;

namespace NpgsqlRestClient;

public class App
{
    private readonly Config _config;
    private readonly Builder _builder;
    
    public App(Config config, Builder builder)
    {
        _config = config;
        _builder = builder;
    }
    
    public void Configure(WebApplication app, Action started, bool forwardedHeadersEnabled)
    {
        app.Lifetime.ApplicationStarted.Register(started);

        // Forwarded headers MUST be first - before any IP-based decisions
        if (forwardedHeadersEnabled)
        {
            app.UseForwardedHeaders();
        }

        if (_builder.UseHttpsRedirection)
        {
            app.UseHttpsRedirection();
        }
        if (_builder.UseHsts)
        {
            app.UseHsts();
        }

        if (_builder.LoggingEnabled is true)
        {
            app.UseSerilogRequestLogging();
        }
    }

    public void ConfigureSecurityHeaders(WebApplication app, SecurityHeadersConfig? config, bool antiForgeryUsed)
    {
        if (config is null)
        {
            return;
        }

        app.Use(async (HttpContext context, RequestDelegate next) =>
        {
            var headers = context.Response.Headers;

            if (config.XContentTypeOptions is not null)
            {
                headers["X-Content-Type-Options"] = config.XContentTypeOptions;
            }
            // Skip X-Frame-Options if Antiforgery is enabled (it already sets this header)
            if (config.XFrameOptions is not null && !antiForgeryUsed)
            {
                headers["X-Frame-Options"] = config.XFrameOptions;
            }
            if (config.ReferrerPolicy is not null)
            {
                headers["Referrer-Policy"] = config.ReferrerPolicy;
            }
            if (config.ContentSecurityPolicy is not null)
            {
                headers["Content-Security-Policy"] = config.ContentSecurityPolicy;
            }
            if (config.PermissionsPolicy is not null)
            {
                headers["Permissions-Policy"] = config.PermissionsPolicy;
            }
            if (config.CrossOriginOpenerPolicy is not null)
            {
                headers["Cross-Origin-Opener-Policy"] = config.CrossOriginOpenerPolicy;
            }
            if (config.CrossOriginEmbedderPolicy is not null)
            {
                headers["Cross-Origin-Embedder-Policy"] = config.CrossOriginEmbedderPolicy;
            }
            if (config.CrossOriginResourcePolicy is not null)
            {
                headers["Cross-Origin-Resource-Policy"] = config.CrossOriginResourcePolicy;
            }

            await next(context);
        });
    }

    public void ConfigureStaticFiles(WebApplication app, NpgsqlRestAuthenticationOptions options)
    {
        var staticFilesCfg = _config.Cfg.GetSection("StaticFiles");
        if (_config.Exists(staticFilesCfg) is false || _config.GetConfigBool("Enabled", staticFilesCfg) is false)
        {
            return;
        }

        app.UseDefaultFiles();

        string[]? authorizePaths = _config.GetConfigEnumerable("AuthorizePaths", staticFilesCfg)?.ToArray();
        string? unauthorizedRedirectPath = _config.GetConfigStr("UnauthorizedRedirectPath", staticFilesCfg);
        string? unauthorizedReturnToQueryParameter = _config.GetConfigStr("UnauthorizedReturnToQueryParameter", staticFilesCfg);

        var parseCfg = staticFilesCfg.GetSection("ParseContentOptions");
        
        bool parse = true;
        if (_config.Exists(parseCfg) is false || _config.GetConfigBool("Enabled", parseCfg) is false)
        {
            parse = false;
        }

        var filePaths = _config.GetConfigEnumerable("FilePaths", parseCfg)?.ToArray();
        var antiforgeryFieldNameTag = _config.GetConfigStr("AntiforgeryFieldName", parseCfg);
        var antiforgeryTokenTag = _config.GetConfigStr("AntiforgeryToken", parseCfg);
        var availableClaims = _config.GetConfigNameDefaults("AvailableClaims", parseCfg);
        var availableEnvVars = _config.GetConfigNameDefaults("AvailableEnvVars", parseCfg);

        var antiforgery = app.Services.GetService<IAntiforgery>();

        AppStaticFileMiddleware.ConfigureStaticFileMiddleware(
            parse,
            filePaths,
            options,
            _config.GetConfigBool("CacheParsedFile", parseCfg, true),
            antiforgeryFieldNameTag,
            antiforgeryTokenTag,
            antiforgery,
            _config.GetConfigEnumerable("Headers", parseCfg)?.ToArray() ?? ["Cache-Control: no-store, no-cache, must-revalidate", "Pragma: no-cache", "Expires: 0"],
            authorizePaths,
            unauthorizedRedirectPath,
            unauthorizedReturnToQueryParameter,
            availableClaims,
            availableEnvVars,
            _builder.ClientLogger);

        app.UseMiddleware<AppStaticFileMiddleware>();
        _builder.ClientLogger?.LogDebug("Serving static files from {WebRootPath}. Parsing following file path patterns: {filePaths}", app.Environment.WebRootPath, filePaths);
    }

    public string CreateUrl(Routine routine, NpgsqlRestOptions options) =>
        string.Concat(
            string.IsNullOrEmpty(options.UrlPathPrefix) ? "/" : string.Concat("/", options.UrlPathPrefix.Trim('/')),
            routine.Schema == "public" ? "" : routine.Schema.Trim(Consts.DoubleQuote).Trim('/'),
            "/",
            routine.Name.Trim(Consts.DoubleQuote).Trim('/'),
            "/");

    public (NpgsqlRestAuthenticationOptions options, IConfigurationSection authCfg) CreateNpgsqlRestAuthenticationOptions(
        WebApplication app,
        string? dataProtectionName)
    {
        var authCfg = _config.NpgsqlRestCfg.GetSection("AuthenticationOptions");
        if (_config.Exists(authCfg) is false)
        {
            return (new NpgsqlRestAuthenticationOptions(), authCfg);
        }
        
        var basicAuthCfg = authCfg.GetSection("BasicAuth");
        BasicAuthOptions basicAuth = new()
        {
            Enabled = _config.GetConfigBool("Enabled", basicAuthCfg, false),
            Realm = _config.GetConfigStr("Realm", basicAuthCfg) ?? BasicAuthOptions.DefaultRealm,
            Users = _config.GetConfigDict(basicAuthCfg.GetSection("Users")) ?? new Dictionary<string, string>(),
            ChallengeCommand = _config.GetConfigStr("ChallengeCommand", basicAuthCfg),
            UseDefaultPasswordHasher = _config.GetConfigBool("UseDefaultPasswordHasher", basicAuthCfg, true),
            SslRequirement = _config.GetConfigEnum<SslRequirement?>("SslRequirement", basicAuthCfg) ?? SslRequirement.Required
        };

        if (basicAuth.Enabled is true && _builder.SslEnabled is false)
        {
            if (basicAuth.SslRequirement == SslRequirement.Required)
            {
                throw new InvalidOperationException("Basic authentication with SslRequirement 'Required' cannot be used when SSL is disabled.");
            }
            if (basicAuth.SslRequirement == SslRequirement.Warning)
            {
                _builder.ClientLogger?.LogWarning("Using Basic Authentication when SSL is disabled.");
            }
            else if (basicAuth.SslRequirement == SslRequirement.Ignore)
            {
                _builder.ClientLogger?.LogDebug("WARNING: Using Basic Authentication when SSL is disabled.");
            }
        }

        if (basicAuth.Enabled is true)
        {
            _builder.ClientLogger?.LogDebug("Basic Authentication enabled with realm {Realm}", basicAuth.Realm);
        }
        
        var provider = app.Services.GetService<IDataProtectionProvider>();
        IDataProtector? protector = dataProtectionName is null ? null : provider?.CreateProtector(dataProtectionName);

        var result = new NpgsqlRestAuthenticationOptions()
        {
            DefaultAuthenticationType = _config.GetConfigStr("DefaultAuthenticationType", authCfg),

            StatusColumnName = _config.GetConfigStr("StatusColumnName", authCfg) ?? "status",
            SchemeColumnName = _config.GetConfigStr("SchemeColumnName", authCfg) ?? "scheme",
            BodyColumnName = _config.GetConfigStr("BodyColumnName", authCfg) ?? "body",
            ResponseTypeColumnName = _config.GetConfigStr("ResponseTypeColumnName", authCfg) ?? "application/json",

            DefaultUserIdClaimType = _config.GetConfigStr("DefaultUserIdClaimType", authCfg) ?? "user_id",
            DefaultNameClaimType = _config.GetConfigStr("DefaultNameClaimType", authCfg) ?? "user_name",
            DefaultRoleClaimType = _config.GetConfigStr("DefaultRoleClaimType", authCfg) ?? "user_roles",

            SerializeAuthEndpointsResponse = _config.GetConfigBool("SerializeAuthEndpointsResponse", authCfg, false),
            ObfuscateAuthParameterLogValues = _config.GetConfigBool("ObfuscateAuthParameterLogValues", authCfg, true),
            HashColumnName = _config.GetConfigStr("HashColumnName", authCfg) ?? "hash",
            PasswordParameterNameContains = _config.GetConfigStr("PasswordParameterNameContains", authCfg) ?? "pass",

            PasswordVerificationFailedCommand = _config.GetConfigStr("PasswordVerificationFailedCommand", authCfg),
            PasswordVerificationSucceededCommand = _config.GetConfigStr("PasswordVerificationSucceededCommand", authCfg),
            UseUserContext = _config.GetConfigBool("UseUserContext", authCfg, false),
            ContextKeyClaimsMapping = _config.GetConfigDict(authCfg.GetSection("ContextKeyClaimsMapping")) ?? new()
            {
                { "request.user_id", "user_id" },
                { "request.user_name", "user_name" },
                { "request.user_roles" , "user_roles" },
            },
            ClaimsJsonContextKey = _config.GetConfigStr("ClaimsJsonContextKey", authCfg),
            IpAddressContextKey = _config.GetConfigStr("IpAddressContextKey", authCfg) ?? "request.ip_address",
            UseUserParameters = _config.GetConfigBool("UseUserParameters", authCfg, false),
            ParameterNameClaimsMapping = _config.GetConfigDict(authCfg.GetSection("ParameterNameClaimsMapping")) ?? new()
            {
                { "_user_id" , "user_id" },
                { "_user_name" , "user_name" },
                { "_user_roles" , "user_roles" },
            },
            ClaimsJsonParameterName = _config.GetConfigStr("ClaimsJsonParameterName", authCfg) ?? "_user_claims",
            IpAddressParameterName = _config.GetConfigStr("IpAddressParameterName", authCfg) ?? "_ip_address",

            BasicAuth = basicAuth,
            DefaultDataProtector = protector,
            CustomLoginHandler = CreateJwtLoginHandler()
        };

        var requiresAuth = _config.GetConfigBool("RequiresAuthorization", _config.NpgsqlRestCfg, true);
        if (requiresAuth || !string.IsNullOrEmpty(result.DefaultAuthenticationType))
        {
            if (result.ParameterNameClaimsMapping.Count > 0)
            {
                _builder.ClientLogger?.LogDebug(
                    "UserParameters claim mapping (parameter name -> claim type): {Mapping}",
                    string.Join(", ", result.ParameterNameClaimsMapping.Select(kv => $"{kv.Key} -> {kv.Value}")));
            }
            if (result.ContextKeyClaimsMapping.Count > 0)
            {
                _builder.ClientLogger?.LogDebug(
                    "UserContext claim mapping (context key -> claim type): {Mapping}",
                    string.Join(", ", result.ContextKeyClaimsMapping.Select(kv => $"{kv.Key} -> {kv.Value}")));
            }
        }

        return (result, authCfg);
    }

    private Func<HttpContext, ClaimsPrincipal, string?, Task<bool>>? CreateJwtLoginHandler()
    {
        // Two ways for JWT to be configured: the main scheme (Auth:JwtAuth=true) or one+ Jwt-type
        // entries under Auth:Schemes. Any combination produces a non-null handler.
        if (_builder.JwtTokenConfig is null && _builder.AdditionalJwtTokenConfigs.Count == 0)
        {
            return null;
        }

        if (_builder.JwtTokenConfig is not null)
        {
            JwtLoginHandler.Initialize(_builder.JwtTokenConfig);
        }
        foreach (var additional in _builder.AdditionalJwtTokenConfigs)
        {
            JwtLoginHandler.Register(additional);
        }

        return async (context, principal, scheme) =>
        {
            // Short-circuit if the login function returned a non-JWT scheme — let the cookie/bearer
            // pipelines handle it. When scheme is null, fall through to JwtLoginHandler which uses
            // the default (main) scheme.
            if (scheme is not null && !JwtLoginHandler.IsScheme(scheme))
            {
                return false;
            }

            return await JwtLoginHandler.HandleLoginAsync(context, principal, scheme);
        };
    }

    /// <summary>
    /// Builds the <see cref="OpenApiOptions"/> from the <c>NpgsqlRest:OpenApiOptions</c> config
    /// section. Returns null when the section is absent or <c>Enabled</c> is false — callers add the
    /// OpenAPI plugin only on a non-null result. Extracted from <see cref="Configure"/> so the
    /// config-to-options wiring (filename / paths / filters / servers / security schemes) is
    /// independently testable from the WebApplication setup.
    /// </summary>
    public OpenApiOptions? BuildOpenApiOptions(string? connectionString)
    {
        var openApiCfg = _config.NpgsqlRestCfg.GetSection("OpenApiOptions");
        if (openApiCfg is null || _config.GetConfigBool("Enabled", openApiCfg) is not true)
        {
            return null;
        }

        var openApi = new OpenApiOptions
        {
            FileName = _config.GetConfigStr("FileName", openApiCfg),
            UrlPath = _config.GetConfigStr("UrlPath", openApiCfg),
            FileOverwrite = _config.GetConfigBool("FileOverwrite", openApiCfg, true),
            DocumentTitle = _config.GetConfigStr("DocumentTitle", openApiCfg),
            DocumentVersion = _config.GetConfigStr("DocumentVersion", openApiCfg) ?? "1.0.0",
            DocumentDescription = _config.GetConfigStr("DocumentDescription", openApiCfg),
            ConnectionString = connectionString,
            AddCurrentServer = _config.GetConfigBool("AddCurrentServer", openApiCfg, true),
            IncludeSchemas = _config.GetConfigEnumerable("IncludeSchemas", openApiCfg)?.ToArray(),
            ExcludeSchemas = _config.GetConfigEnumerable("ExcludeSchemas", openApiCfg)?.ToArray(),
            NameSimilarTo = _config.GetConfigStr("NameSimilarTo", openApiCfg),
            NameNotSimilarTo = _config.GetConfigStr("NameNotSimilarTo", openApiCfg),
            RequiresAuthorizationOnly = _config.GetConfigBool("RequiresAuthorizationOnly", openApiCfg, false),
        };

        if (openApi.UrlPath is null && openApi.FileName is null)
        {
            // Caller will log the warning. Return the options as-is so the warning fires through
            // the same code path it always has.
            return openApi;
        }

        var serversCfg = openApiCfg.GetSection("Servers");
        var servers = new List<OpenApiServer>(3);
        foreach (var serverSection in serversCfg.GetChildren())
        {
            var url = _config.GetConfigStr("Url", serverSection);
            if (url is null)
            {
                continue;
            }
            servers.Add(new OpenApiServer
            {
                Url = _config.GetConfigStr("Url", serverSection)!,
                Description = _config.GetConfigStr("Description", serverSection)
            });
        }
        if (servers.Count > 0)
        {
            openApi.Servers = servers.ToArray();
        }

        var securitySchemesCfg = openApiCfg.GetSection("SecuritySchemes");
        var securitySchemes = new List<OpenApiSecurityScheme>(2);
        foreach (var schemeSection in securitySchemesCfg.GetChildren())
        {
            var name = _config.GetConfigStr("Name", schemeSection);
            var type = _config.GetConfigEnum<OpenApiSecuritySchemeType?>("Type", schemeSection);
            if (name is null || type is null)
            {
                continue;
            }

            var scheme = new OpenApiSecurityScheme
            {
                Name = name,
                Type = type.Value,
                Description = _config.GetConfigStr("Description", schemeSection)
            };

            // HTTP scheme configuration (Bearer/Basic)
            if (type == OpenApiSecuritySchemeType.Http)
            {
                scheme.Scheme = _config.GetConfigEnum<HttpAuthScheme?>("Scheme", schemeSection);
                scheme.BearerFormat = _config.GetConfigStr("BearerFormat", schemeSection);
            }

            // API Key configuration (Cookie/Header/Query)
            if (type == OpenApiSecuritySchemeType.ApiKey)
            {
                scheme.In = _config.GetConfigStr("In", schemeSection);
                scheme.ApiKeyLocation = _config.GetConfigEnum<ApiKeyLocation?>("ApiKeyLocation", schemeSection);
            }

            securitySchemes.Add(scheme);
        }

        if (securitySchemes.Count > 0)
        {
            openApi.SecuritySchemes = securitySchemes.ToArray();
        }

        return openApi;
    }

    public Action<RoutineEndpoint?>? CreateEndpointCreatedHandler(IConfigurationSection authCfg)
    {
        if (_config.Exists(authCfg) is false)
        {
            return null;
        }
        var loginPath = _config.GetConfigStr("LoginPath", authCfg);
        var logoutPath = _config.GetConfigStr("LogoutPath", authCfg);
        if (loginPath is null && logoutPath is null)
        {
            return null;
        }
        return endpoint =>
        {
            if (endpoint is null)
            {
                return;
            }
            if (loginPath is not null && string.Equals(endpoint.Path, loginPath, StringComparison.OrdinalIgnoreCase))
            {
                endpoint.Login = true;
            }
            if (logoutPath is not null && string.Equals(endpoint.Routine.Name, logoutPath, StringComparison.OrdinalIgnoreCase))
            {
                endpoint.Login = true;
            }
        };
    }
    
    public Action<NpgsqlConnection, RoutineEndpoint, HttpContext>? BeforeConnectionOpen(string connectionString, NpgsqlRestAuthenticationOptions options)
    {
        if (_config.UseJsonApplicationName is false)
        {
            return null;
        }

        // Extract the application name to avoid capturing _builder reference
        var applicationName = _builder.Instance.Environment.ApplicationName;
        var headerName = _config.GetConfigStr("ExecutionIdHeaderName", _config.NpgsqlRestCfg) ?? "X-Execution-Id";

        _builder.ClientLogger?.LogDebug("Using JsonApplicationName {{\"app\":\"{applicationName}\",\"uid\":\"<{UserIdClaimType}>\",\"id\":\"<{headerName}>\"}}",
            applicationName,
            options.DefaultUserIdClaimType,
            headerName);

        return (connection, endpoint, context) =>
        {
            var uid = context.User.FindFirstValue(options.DefaultUserIdClaimType);
            var executionId = context.Request.Headers[headerName].FirstOrDefault();
            connection.ConnectionString = new NpgsqlConnectionStringBuilder(connectionString)
            {
                ApplicationName = string.Concat("{\"app\":\"", applicationName,
                        "\",\"uid\":", uid is null ? "null" : string.Concat("\"", uid, "\""),
                        ",\"id\":", executionId is null ? "null" : string.Concat("\"", executionId, "\""),
                        "}")
            }.ConnectionString;
        };
    }

    public List<IEndpointCreateHandler> CreateCodeGenHandlers(string connectionString, string[] args)
    {
        List<IEndpointCreateHandler> handlers = new(3);
        if (args.Any(a => string.Equals(a, "--endpoints", StringComparison.OrdinalIgnoreCase)))
        {
            handlers.Add(new EndpointCapture());
        }
        var httpFilecfg = _config.NpgsqlRestCfg.GetSection("HttpFileOptions");
        if (httpFilecfg is not null && _config.GetConfigBool("Enabled", httpFilecfg) is true)
        {
            handlers.Add(new HttpFile(new HttpFileOptions
            {
                Name = _config.GetConfigStr("Name", httpFilecfg),
                Option = _config.GetConfigEnum<HttpFileOption?>("Option", httpFilecfg) ?? HttpFileOption.File,
                NamePattern = _config.GetConfigStr("NamePattern", httpFilecfg) ?? "{0}_{1}",
                CommentHeader = _config.GetConfigEnum<CommentHeader?>("CommentHeader", httpFilecfg) ?? CommentHeader.Simple,
                CommentHeaderIncludeComments = _config.GetConfigBool("CommentHeaderIncludeComments", httpFilecfg, true),
                FileMode = _config.GetConfigEnum<HttpFileMode?>("FileMode", httpFilecfg) ?? HttpFileMode.Schema,
                FileOverwrite = _config.GetConfigBool("FileOverwrite", httpFilecfg, true),
                ConnectionString = connectionString
            }));
            _builder.ClientLogger?.LogDebug("HTTP file generation enabled. Name={Name}",
                _config.GetConfigStr("Name", httpFilecfg) ?? "generated from connection string");
        }
        
        var openApi = BuildOpenApiOptions(connectionString);
        if (openApi is not null)
        {
            if (openApi.UrlPath is null && openApi.FileName is null)
            {
                _builder.ClientLogger?.LogWarning("OpenAPI generation is disabled because both FileName and UrlPath are not set.");
            }
            else
            {
                handlers.Add(new OpenApi(openApi));
                _builder.ClientLogger?.LogDebug("OpenAPI generation enabled. FileName={FileName}, UrlPath={UrlPath}",
                    openApi.FileName ?? "not set", openApi.UrlPath ?? "not set");
            }
        }

        var tsClientCfg = _config.NpgsqlRestCfg.GetSection("ClientCodeGen");
        if (tsClientCfg is not null && _config.GetConfigBool("Enabled", tsClientCfg) is true)
        {
            var ts = new TsClientOptions
            {
                FilePath = _config.GetConfigStr("FilePath", tsClientCfg),
                FileOverwrite = _config.GetConfigBool("FileOverwrite", tsClientCfg, true),
                IncludeHost = _config.GetConfigBool("IncludeHost", tsClientCfg, true),
                CustomHost = tsClientCfg.GetSection("CustomHost").Value is not null
                    ? (_config.GetConfigStr("CustomHost", tsClientCfg) ?? "")
                    : null,
                CommentHeader = _config.GetConfigEnum<CommentHeader?>("CommentHeader", tsClientCfg) ?? CommentHeader.Simple,
                CommentHeaderIncludeComments = _config.GetConfigBool("CommentHeaderIncludeComments", tsClientCfg, true),
                BySchema = _config.GetConfigBool("BySchema", tsClientCfg, true),
                IncludeStatusCode = _config.GetConfigBool("IncludeStatusCode", tsClientCfg, true),
                CreateSeparateTypeFile = _config.GetConfigBool("CreateSeparateTypeFile", tsClientCfg, true),
                ImportBaseUrlFrom = _config.GetConfigStr("ImportBaseUrlFrom", tsClientCfg),
                ImportParseQueryFrom = _config.GetConfigStr("ImportParseQueryFrom", tsClientCfg),
                IncludeParseUrlParam = _config.GetConfigBool("IncludeParseUrlParam", tsClientCfg),
                IncludeParseRequestParam = _config.GetConfigBool("IncludeParseRequestParam", tsClientCfg),
                UseRoutineNameInsteadOfEndpoint = _config.GetConfigBool("UseRoutineNameInsteadOfEndpoint", tsClientCfg),
                DefaultJsonType = _config.GetConfigStr("DefaultJsonType", tsClientCfg) ?? "string",
                ExportUrls = _config.GetConfigBool("ExportUrls", tsClientCfg),
                SkipTypes = _config.GetConfigBool("SkipTypes", tsClientCfg),
                UniqueModels = _config.GetConfigBool("UniqueModels", tsClientCfg),
                XsrfTokenHeaderName = _config.GetConfigStr("XsrfTokenHeaderName", tsClientCfg),
                ExportEventSources = _config.GetConfigBool("ExportEventSources", tsClientCfg, true),
                CustomImports = _config.GetConfigEnumerable("CustomImports", tsClientCfg)?.ToArray() ?? [],
                IncludeSchemaInNames = _config.GetConfigBool("IncludeSchemaInNames", tsClientCfg, true),
                ErrorExpression = _config.GetConfigStr("ErrorExpression", tsClientCfg) ?? "await response.json()",
                ErrorType = _config.GetConfigStr("ErrorType", tsClientCfg) ?? "{status: number; title: string; detail?: string | null} | undefined",
            };

            Dictionary<string, string> customHeaders = [];
            foreach (var section in tsClientCfg.GetSection("CustomHeaders").GetChildren())
            {
                if (section?.Value is null)
                {
                    continue;
                }
                customHeaders.Add(section.Key, section.Value!);
            }
            ts.CustomHeaders = customHeaders;

            var headerLines = _config.GetConfigEnumerable("HeaderLines", tsClientCfg);
            if (headerLines is not null)
            {
                ts.HeaderLines = [.. headerLines];
            }

            var skipRoutineNames = _config.GetConfigEnumerable("SkipRoutineNames", tsClientCfg);
            if (skipRoutineNames is not null)
            {
                ts.SkipRoutineNames = [.. skipRoutineNames];
            }

            var skipFunctionNames = _config.GetConfigEnumerable("SkipFunctionNames", tsClientCfg);
            if (skipFunctionNames is not null)
            {
                ts.SkipFunctionNames = [.. skipFunctionNames];
            }

            var skipPaths = _config.GetConfigEnumerable("SkipPaths", tsClientCfg);
            if (skipPaths is not null)
            {
                ts.SkipPaths = [.. skipPaths];
            }

            var skipSchemas = _config.GetConfigEnumerable("SkipSchemas", tsClientCfg);
            if (skipSchemas is not null)
            {
                ts.SkipSchemas = [.. skipSchemas];
            }

            handlers.Add(new TsClient(ts));
            _builder.ClientLogger?.LogDebug("TypeScript client code generation enabled. FilePath={FilePath}", ts.FilePath);
        }

        return handlers;
    }

    public List<IEndpointSource> CreateEndpointSources()
    {
        var sources = new List<IEndpointSource>(2);

        var routineOptionsCfg = _config.NpgsqlRestCfg.GetSection("RoutineOptions");
        if (routineOptionsCfg.Exists() is true && _config.GetConfigBool("Enabled", routineOptionsCfg, true) is false)
        {
            _builder.ClientLogger?.LogDebug("{name} PostgreSQL Source is disabled", nameof(RoutineSource));
        }
        else
        {
            var source = new RoutineSource();
            if (routineOptionsCfg.Exists() is true)
            {
                var customTypeParameterSeparator = _config.GetConfigStr("CustomTypeParameterSeparator", routineOptionsCfg);
                if (customTypeParameterSeparator is not null)
                {
                    source.CustomTypeParameterSeparator = customTypeParameterSeparator;
                }
                var includeLanguages = _config.GetConfigEnumerable("IncludeLanguages", routineOptionsCfg);
                if (includeLanguages is not null)
                {
                    source.IncludeLanguages = [.. includeLanguages];
                }
                var excludeLanguages = _config.GetConfigEnumerable("ExcludeLanguages", routineOptionsCfg);
                if (excludeLanguages is not null)
                {
                    source.ExcludeLanguages = [.. excludeLanguages];
                }
                source.NestedJsonForCompositeTypes = _config.GetConfigBool("NestedJsonForCompositeTypes", routineOptionsCfg);
                source.ResolveNestedCompositeTypes = _config.GetConfigBool("ResolveNestedCompositeTypes", routineOptionsCfg, true);
            }
            sources.Add(source);
            _builder.ClientLogger?.LogDebug("Using {name} PostrgeSQL Source", nameof(RoutineSource));
        }

        var sqlFileSourceCfg = _config.NpgsqlRestCfg.GetSection("SqlFileSource");
        if (sqlFileSourceCfg.Exists() is true && _config.GetConfigBool("Enabled", sqlFileSourceCfg, true) is true)
        {
            var filePattern = _config.GetConfigStr("FilePattern", sqlFileSourceCfg) ?? "";
            if (!string.IsNullOrEmpty(filePattern))
            {
                var opts = new SqlFileSourceOptions
                {
                    FilePattern = filePattern,
                    CommentScope = _config.GetConfigEnum<CommentScope>("CommentScope", sqlFileSourceCfg),
                    UnnamedSingleColumnSet = _config.GetConfigBool("UnnamedSingleColumnSet", sqlFileSourceCfg, true),
                    SkipNonQueryCommands = _config.GetConfigBool("SkipNonQueryCommands", sqlFileSourceCfg, true),
                };
                if (_config.GetConfigStr("CommentsMode", sqlFileSourceCfg) is not null)
                {
                    opts.CommentsMode = _config.GetConfigEnum<CommentsMode>("CommentsMode", sqlFileSourceCfg);
                }
                if (_config.GetConfigStr("ErrorMode", sqlFileSourceCfg) is not null)
                {
                    opts.ErrorMode = _config.GetConfigEnum<ParseErrorMode>("ErrorMode", sqlFileSourceCfg);
                }
                var resultPrefix = _config.GetConfigStr("ResultPrefix", sqlFileSourceCfg);
                if (resultPrefix is not null)
                {
                    opts.ResultPrefix = resultPrefix;
                }
                var sqlFileSource = new SqlFileSource(opts)
                {
                    NestedJsonForCompositeTypes = _config.GetConfigBool("NestedJsonForCompositeTypes", sqlFileSourceCfg)
                };
                sources.Add(sqlFileSource);
                _builder.ClientLogger?.LogDebug("Using {name} PostgreSQL Source with pattern {pattern}", nameof(SqlFileSource), filePattern);
            }
        }

        return sources;
    }

    public NpgsqlRestUploadOptions CreateUploadOptions()
    {
        var uploadCfg = _config.NpgsqlRestCfg.GetSection("UploadOptions");
        if (uploadCfg.Exists() is false)
        {
            return new NpgsqlRestUploadOptions();
        }

        var result = new NpgsqlRestUploadOptions
        {
            Enabled = _config.GetConfigBool("Enabled", uploadCfg),
            LogUploadEvent = _config.GetConfigBool("LogUploadEvent", uploadCfg, true),
            LogUploadParameters = _config.GetConfigBool("LogUploadParameters", uploadCfg, false),
            DefaultUploadHandler = _config.GetConfigStr("DefaultUploadHandler", uploadCfg) ?? "large_object",
            UseDefaultUploadMetadataParameter = _config.GetConfigBool("UseDefaultUploadMetadataParameter", uploadCfg, false),
            DefaultUploadMetadataParameterName = _config.GetConfigStr("DefaultUploadMetadataParameterName", uploadCfg) ?? "_upload_metadata",
            UseDefaultUploadMetadataContextKey = _config.GetConfigBool("UseDefaultUploadMetadataContextKey", uploadCfg, false),
            DefaultUploadMetadataContextKey = _config.GetConfigStr("DefaultUploadMetadataContextKey", uploadCfg) ?? "request.upload_metadata",
        };

        var uploadHandlersCfg = uploadCfg.GetSection("UploadHandlers");
        UploadHandlerOptions uploadHandlerOptions;
        if (uploadHandlersCfg.Exists() is false)
        {
            uploadHandlerOptions = new();
        }
        else
        {
            uploadHandlerOptions = new()
            {
                StopAfterFirstSuccess = _config.GetConfigBool("StopAfterFirstSuccess", uploadHandlersCfg, false),
                IncludedMimeTypePatterns = _config.GetConfigStr("IncludedMimeTypePatterns", uploadHandlersCfg).SplitParameter(),
                ExcludedMimeTypePatterns = _config.GetConfigStr("ExcludedMimeTypePatterns", uploadHandlersCfg).SplitParameter(),
                BufferSize = _config.GetConfigInt("BufferSize", uploadHandlersCfg) ?? 8192,
                TextTestBufferSize = _config.GetConfigInt("TextTestBufferSize", uploadHandlersCfg) ?? 4096,
                TextNonPrintableThreshold = _config.GetConfigInt("TextNonPrintableThreshold", uploadHandlersCfg) ?? 5,

                LargeObjectEnabled = _config.GetConfigBool("LargeObjectEnabled", uploadHandlersCfg, true),
                LargeObjectKey = _config.GetConfigStr("LargeObjectKey", uploadHandlersCfg) ?? "large_object",
                LargeObjectCheckText = _config.GetConfigBool("LargeObjectCheckText", uploadHandlersCfg, false),
                LargeObjectCheckImage = _config.GetConfigBool("LargeObjectCheckImage", uploadHandlersCfg, false),

                FileSystemEnabled = _config.GetConfigBool("FileSystemEnabled", uploadHandlersCfg, true),
                FileSystemKey = _config.GetConfigStr("FileSystemKey", uploadHandlersCfg) ?? "file_system",
                FileSystemPath = _config.GetConfigStr("FileSystemPath", uploadHandlersCfg) ?? "/tmp/uploads",
                FileSystemUseUniqueFileName = _config.GetConfigBool("FileSystemUseUniqueFileName", uploadHandlersCfg, true),
                FileSystemCreatePathIfNotExists = _config.GetConfigBool("FileSystemCreatePathIfNotExists", uploadHandlersCfg, true),
                FileSystemCheckText = _config.GetConfigBool("FileSystemCheckText", uploadHandlersCfg, false),
                FileSystemCheckImage = _config.GetConfigBool("FileSystemCheckImage", uploadHandlersCfg, false),

                CsvUploadEnabled = _config.GetConfigBool("CsvUploadEnabled", uploadHandlersCfg, true),
                CsvUploadKey = _config.GetConfigStr("CsvUploadKey", uploadHandlersCfg) ?? "csv",
                CsvUploadCheckFileStatus = _config.GetConfigBool("CsvUploadCheckFileStatus", uploadHandlersCfg, true),
                CsvUploadDelimiterChars = _config.GetConfigStr("CsvUploadDelimiterChars", uploadHandlersCfg) ?? ",",
                CsvUploadHasFieldsEnclosedInQuotes = _config.GetConfigBool("CsvUploadHasFieldsEnclosedInQuotes", uploadHandlersCfg, true),
                CsvUploadSetWhiteSpaceToNull = _config.GetConfigBool("CsvUploadSetWhiteSpaceToNull", uploadHandlersCfg, true),
                CsvUploadRowCommand = _config.GetConfigStr("CsvUploadRowCommand", uploadHandlersCfg) ?? "call process_csv_row($1,$2,$3,$4)",
                RowCommandUserClaimsKey = _config.GetConfigStr("RowCommandUserClaimsKey", uploadHandlersCfg) ?? "claims",
            };
            var imageTypes = _config.GetConfigStr("AllowedImageTypes", uploadHandlersCfg)?.ParseImageTypes(null);
            if (imageTypes is not null)
            {
                uploadHandlerOptions.AllowedImageTypes = imageTypes.Value;
            }
        }
        result.DefaultUploadHandlerOptions = uploadHandlerOptions;

        result.UploadHandlers = result.CreateUploadHandlers();

        if (_config.GetConfigBool("ExcelUploadEnabled", uploadHandlersCfg, true))
        {
            // Initialize ExcelDataReader encoding provider
            System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);

            ExcelUploadOptions.Instance.ExcelSheetName = _config.GetConfigStr("ExcelSheetName", uploadHandlersCfg) ?? null;
            ExcelUploadOptions.Instance.ExcelAllSheets = _config.GetConfigBool("ExcelAllSheets", uploadHandlersCfg, false);
            ExcelUploadOptions.Instance.ExcelTimeFormat = _config.GetConfigStr("ExcelTimeFormat", uploadHandlersCfg) ?? "HH:mm:ss";
            ExcelUploadOptions.Instance.ExcelDateFormat = _config.GetConfigStr("ExcelDateFormat", uploadHandlersCfg) ?? "yyyy-MM-dd";
            ExcelUploadOptions.Instance.ExcelDateTimeFormat = _config.GetConfigStr("ExcelDateTimeFormat", uploadHandlersCfg) ?? "yyyy-MM-dd HH:mm:ss";
            ExcelUploadOptions.Instance.ExcelRowDataAsJson = _config.GetConfigBool("ExcelRowDataAsJson", uploadHandlersCfg, false);
            ExcelUploadOptions.Instance.ExcelUploadRowCommand = _config.GetConfigStr("ExcelUploadRowCommand", uploadHandlersCfg) ?? "call process_excel_row($1,$2,$3,$4)";

            result?.UploadHandlers?.Add(
                _config.GetConfigStr("ExcelKey", uploadHandlersCfg) ?? "excel", 
                strategy => new ExcelUploadHandler(result, strategy));
        }

        if (result?.UploadHandlers is not null && result.UploadHandlers.Count > 1)
        {
            _builder.ClientLogger?.LogDebug("Using {Keys} upload handlers where {DefaultUploadHandler} is default.", result.UploadHandlers.Keys, result.DefaultUploadHandler);
            foreach (var uploadHandler in result.UploadHandlers)
            {
                _builder.ClientLogger?.LogTrace("Upload handler {Key} has following parameters: {Parameters}", 
                    uploadHandler.Key, uploadHandler.Value(null!).SetType(uploadHandler.Key).Parameters);
            }
        }
        return result!;
    }

    public Dictionary<string, NpgsqlRest.TableFormatHandlers.ITableFormatHandler>? CreateTableFormatHandlers()
    {
        var cfg = _config.NpgsqlRestCfg.GetSection("TableFormatOptions");
        if (cfg.Exists() is false)
        {
            return null;
        }
        if (_config.GetConfigBool("Enabled", cfg, false) is false)
        {
            return null;
        }

        var result = new Dictionary<string, NpgsqlRest.TableFormatHandlers.ITableFormatHandler>();

        if (_config.GetConfigBool("HtmlEnabled", cfg, true))
        {
            var handler = new NpgsqlRest.TableFormatHandlers.HtmlTableFormatHandler
            {
                Header = _config.GetConfigStr("HtmlHeader", cfg) ??
                    "<style>table{font-family:Calibri,Arial,sans-serif;font-size:11pt;border-collapse:collapse}" +
                    "th,td{border:1px solid #d4d4d4;padding:4px 8px}" +
                    "th{background-color:#f5f5f5;font-weight:600}</style>",
                Footer = _config.GetConfigStr("HtmlFooter", cfg)
            };
            var htmlKey = _config.GetConfigStr("HtmlKey", cfg) ?? "html";
            result.Add(htmlKey, handler);
            _builder.ClientLogger?.LogDebug("Table format handler for {Key} is enabled.", htmlKey);
        }

        if (_config.GetConfigBool("ExcelEnabled", cfg, true))
        {
            var excelHandler = new ExcelTableFormatHandler
            {
                SheetName = _config.GetConfigStr("ExcelSheetName", cfg),
                DateTimeFormat = _config.GetConfigStr("ExcelDateTimeFormat", cfg),
                NumericFormat = _config.GetConfigStr("ExcelNumericFormat", cfg)
            };
            var excelKey = _config.GetConfigStr("ExcelKey", cfg) ?? "excel";
            result.Add(excelKey, excelHandler);
            _builder.ClientLogger?.LogDebug("Table format handler for {Key} is enabled.", excelKey);
        }

        if (result.Count == 0)
        {
            return null;
        }

        _builder.ClientLogger?.LogDebug("Using {Count} table format handler(s): {Keys}", result.Count, result.Keys);
        return result;
    }

    public void ConfigureThreadPool()
    {
        var threadPoolCfg = _config.Cfg.GetSection("ThreadPool");
        if (threadPoolCfg.Exists() is false)
        {
            return;
        }

        var minWorkerThreads = _config.GetConfigInt("MinWorkerThreads", threadPoolCfg);
        var minCompletionPortThreads = _config.GetConfigInt("MinCompletionPortThreads", threadPoolCfg);
        if (minWorkerThreads is not null || minCompletionPortThreads is not null)
        {
            if (minWorkerThreads is null || minCompletionPortThreads is null)
            {
                ThreadPool.GetMinThreads(out var minWorkerThreadsTmp, out var minCompletionPortThreadsTmp);
                minWorkerThreads ??= minWorkerThreadsTmp;
                minCompletionPortThreads ??= minCompletionPortThreadsTmp;
            }
            ThreadPool.SetMinThreads(workerThreads: minWorkerThreads.Value, completionPortThreads: minCompletionPortThreads.Value);
            _builder.ClientLogger?.LogDebug("ThreadPool minimum worker threads to {MinWorkerThreads} and minimum completion port threads to {MinCompletionPortThreads}",
                minWorkerThreads, minCompletionPortThreads);
        }

        var maxWorkerThreads = _config.GetConfigInt("MaxWorkerThreads", threadPoolCfg);
        var maxCompletionPortThreads = _config.GetConfigInt("MaxCompletionPortThreads", threadPoolCfg);
        if (maxWorkerThreads is not null || maxCompletionPortThreads is not null)
        {
            if (maxWorkerThreads is null || maxCompletionPortThreads is null)
            {
                ThreadPool.GetMaxThreads(out var maxWorkerThreadsTmp, out var maxCompletionPortThreadsTmp);
                maxWorkerThreads ??= maxWorkerThreadsTmp;
                maxCompletionPortThreads ??= maxCompletionPortThreadsTmp;
            }
            ThreadPool.SetMaxThreads(workerThreads: maxWorkerThreads.Value, completionPortThreads: maxCompletionPortThreads.Value);
            _builder.ClientLogger?.LogDebug("ThreadPool maximum worker threads to {MaxWorkerThreads} and maximum completion port threads to {MaxCompletionPortThreads}",
                maxWorkerThreads, maxCompletionPortThreads);
        }
    }
}