AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/DataPlatform/MdpSourceScopeFactory.cs

using System.Data.Common;
using Admin.NET.Plugin.AiDOP.Entity.DataPlatform;
using Microsoft.Extensions.Logging;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.DataPlatform;

/// <summary>
/// 按 mdp_source 动态获取只读 SqlSugar 连接域。
/// ConfigId 包含租户、数据源主键和配置更新时间。连接只看 conn_mode 与 db_*，不按来源编码分支。
/// </summary>
public sealed class MdpSourceScopeFactory : ITransient
{
    private const string ConfigIdPrefix = "mdp-src-";
    private readonly ISqlSugarClient _db;
    private readonly IMdpSourcePasswordResolver _passwords;
    private readonly ILogger<MdpSourceScopeFactory>? _logger;

    public MdpSourceScopeFactory(
        ISqlSugarClient db,
        IMdpSourcePasswordResolver passwords,
        ILogger<MdpSourceScopeFactory>? logger = null)
    {
        _db = db;
        _passwords = passwords ?? throw new ArgumentNullException(nameof(passwords));
        _logger = logger;
    }

    /// <summary>按 sourceCode 获取连接域；优先复用已注册 ConfigId。</summary>
    public async Task<ISqlSugarClient> GetScopeAsync(string sourceCode, CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(sourceCode))
            throw new ArgumentException("sourceCode 不能为空", nameof(sourceCode));

        var code = sourceCode.Trim();
        var source = await _db.Queryable<MdpSource>()
            .Where(x => x.SourceCode == code && x.Status == 1)
            .FirstAsync(cancellationToken)
            ?? throw new InvalidOperationException($"mdp_source 未找到启用源：{code}");

        return OpenScope(source);
    }

    /// <summary>
    /// 使用调用方已经按租户校验过的数据源，避免相同 source_code 在不同租户间串用连接。
    /// </summary>
    public Task<ISqlSugarClient> GetScopeAsync(MdpSource source, CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(source);
        cancellationToken.ThrowIfCancellationRequested();
        return Task.FromResult(OpenScope(source));
    }

    /// <summary>
    /// 按 system_code 取唯一一条启用的 DB 源。0 条或多条都抛错，禁止取第一条。
    /// </summary>
    public async Task<ISqlSugarClient> GetScopeBySystemCodeAsync(string systemCode, CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(systemCode))
            throw new ArgumentException("systemCode 不能为空", nameof(systemCode));

        var code = systemCode.Trim();
        var all = await _db.Queryable<MdpSource>()
            .Where(x => x.Status == 1 && x.SourceType == "DB")
            .ToListAsync(cancellationToken);
        var rows = all
            .Where(x => string.Equals(
                string.IsNullOrWhiteSpace(x.SystemCode) ? x.SourceCode : x.SystemCode,
                code,
                StringComparison.OrdinalIgnoreCase))
            .ToList();

        if (rows.Count == 0)
            throw new InvalidOperationException($"mdp_source 未找到启用的 DB 源：system_code={code}");
        if (rows.Count > 1)
            throw new InvalidOperationException(
                $"system_code={code} 命中多条 DB 源：{string.Join(",", rows.Select(x => x.SourceCode))}。请改用 GetScopeAsync(sourceCode)。");

        return OpenScope(rows[0]);
    }

    private ISqlSugarClient OpenScope(MdpSource source)
    {
        if (!string.Equals(source.SourceType, "DB", StringComparison.OrdinalIgnoreCase))
            throw new InvalidOperationException($"源 {source.SourceCode} 的 source_type={source.SourceType}，不是 DB，无法建 SqlSugar 连接");

        var mode = (source.ConnMode ?? "").Trim().ToUpperInvariant();
        if (mode == "SELF")
            return _db;
        if (mode != "EXTERNAL")
            throw new InvalidOperationException($"源 {source.SourceCode} 的 conn_mode='{source.ConnMode}' 无法解析。只接受 SELF / EXTERNAL。");

        if (string.IsNullOrWhiteSpace(source.DbHost) || string.IsNullOrWhiteSpace(source.DbName) || string.IsNullOrWhiteSpace(source.DbUser))
            throw new InvalidOperationException($"源 {source.SourceCode} 是 EXTERNAL，但缺少 db_host / db_name / db_user。请在数据源页面补齐，禁止回退主库。");

        // 更新时间进入 ConfigId：页面修改主机、账号或密码后立即使用新连接，
        // 不复用本进程里按旧配置创建的 SqlSugar 连接域。
        var configId = $"{ConfigIdPrefix}{source.TenantId}-{source.Id}-{source.UpdateTime.Ticks}";
        if (_db.AsTenant().IsAnyConnection(configId))
            return _db.AsTenant().GetConnectionScope(configId);

        var connStr = BuildConnectionString(source);
        var dbType = MapDbType(source.DbType);

        _db.AsTenant().AddConnection(new ConnectionConfig
        {
            ConfigId = configId,
            DbType = dbType,
            ConnectionString = connStr,
            InitKeyType = InitKeyType.Attribute,
            IsAutoCloseConnection = true,
            MoreSettings = new ConnMoreSettings
            {
                IsAutoRemoveDataCache = true
            }
        });

        return _db.AsTenant().GetConnectionScope(configId);
    }

    public ISqlSugarClient GetScope(string sourceCode) =>
        GetScopeAsync(sourceCode).GetAwaiter().GetResult();

    private static DbType MapDbType(string? dbType)
    {
        if (string.IsNullOrWhiteSpace(dbType)) return DbType.MySql;
        return dbType.Trim().ToUpperInvariant() switch
        {
            "MYSQL" => DbType.MySql,
            "SQLSERVER" or "MSSQL" => DbType.SqlServer,
            "ORACLE" => DbType.Oracle,
            "POSTGRES" or "POSTGRESQL" => DbType.PostgreSQL,
            _ => throw new NotSupportedException($"不支持的 db_type：{dbType}")
        };
    }

    private string BuildConnectionString(MdpSource source)
    {
        if (string.IsNullOrWhiteSpace(source.DbHost) || string.IsNullOrWhiteSpace(source.DbName))
            throw new InvalidOperationException($"源 {source.SourceCode} 缺少 db_host/db_name");

        var password = _passwords.Resolve(source.SourceCode, source.DbPasswordEnc);
        var port = source.DbPort;
        var user = source.DbUser ?? "";
        var dbType = MapDbType(source.DbType);
        var extraBody = SanitizeExtraParams(source.DbExtraParams, dbType, out var dropped);
        if (dropped.Count > 0)
            _logger?.LogWarning(
                "源 {SourceCode} 的 db_extra_params 含非法关键字 {Keys}，已忽略",
                source.SourceCode, string.Join(",", dropped));
        var extra = string.IsNullOrEmpty(extraBody) ? "" : ";" + extraBody;

        return dbType switch
        {
            DbType.SqlServer =>
                $"Server={source.DbHost}{(port is > 0 ? $",{port}" : "")};Database={source.DbName};User Id={user};Password={password};TrustServerCertificate=true;Encrypt=false{extra}",
            DbType.MySql =>
                $"Server={source.DbHost};Port={(port is > 0 ? port : 3306)};Database={source.DbName};Uid={user};Pwd={password};CharSet=utf8mb4;AllowLoadLocalInfile=true{extra}",
            DbType.PostgreSQL =>
                $"Host={source.DbHost};Port={(port is > 0 ? port : 5432)};Database={source.DbName};Username={user};Password={password}{extra}",
            DbType.Oracle =>
                $"Data Source={source.DbHost}:{(port is > 0 ? port : 1521)}/{source.DbName};User Id={user};Password={password}{extra}",
            _ => throw new NotSupportedException($"不支持的 db_type：{source.DbType}")
        };
    }

    /// <summary>
    /// 剔掉不属于连接串的键：SqlSugar 的 ConfigId，以及本方法已经写死的键。
    /// 按数据库类型分集合，避免一份黑名单误伤 MySQL / PostgreSQL / Oracle 的合法参数。
    /// </summary>
    internal static IReadOnlyList<string> IllegalExtraKeys(string? dbType, string? extra)
    {
        SanitizeExtraParams(extra, MapDbType(dbType), out var dropped);
        return dropped;
    }

    internal static string? SanitizeExtraParams(string? extra, DbType dbType, out List<string> dropped)
    {
        dropped = new List<string>();
        if (string.IsNullOrWhiteSpace(extra)) return null;

        DbConnectionStringBuilder parsed;
        try
        {
            parsed = new DbConnectionStringBuilder { ConnectionString = extra.Trim().TrimStart(';') };
        }
        catch (ArgumentException)
        {
            dropped.Add("(unparseable)");
            return null;
        }

        var reserved = ReservedConnectionKeys(dbType);
        var kept = new DbConnectionStringBuilder();
        foreach (string key in parsed.Keys)
        {
            if (IsSqlSugarOnlyKey(key) || reserved.Contains(key))
                dropped.Add(key);
            else
                kept[key] = parsed[key];
        }

        return kept.Count == 0 ? null : kept.ConnectionString;
    }

    internal static string? SanitizeStoredExtra(string? dbType, string? extra)
    {
        if (string.IsNullOrWhiteSpace(extra)) return null;
        return SanitizeExtraParams(extra, MapDbType(dbType), out _);
    }

    private static bool IsSqlSugarOnlyKey(string key) =>
        string.Equals(key, "ConfigId", StringComparison.OrdinalIgnoreCase);

    private static HashSet<string> ReservedConnectionKeys(DbType dbType) => dbType switch
    {
        DbType.SqlServer => new(StringComparer.OrdinalIgnoreCase)
        {
            "Server", "Data Source", "Address", "Addr", "Network Address",
            "Database", "Initial Catalog",
            "User Id", "UID", "User",
            "Password", "PWD",
            "TrustServerCertificate", "Encrypt"
        },
        DbType.MySql => new(StringComparer.OrdinalIgnoreCase)
        {
            "Server", "Host", "Port", "Database", "Uid", "User Id", "Pwd", "Password",
            "CharSet", "AllowLoadLocalInfile"
        },
        DbType.PostgreSQL => new(StringComparer.OrdinalIgnoreCase)
        {
            "Host", "Port", "Database", "Username", "User Id", "Password"
        },
        DbType.Oracle => new(StringComparer.OrdinalIgnoreCase)
        {
            "Data Source", "User Id", "Password"
        },
        _ => new(StringComparer.OrdinalIgnoreCase)
    };

}