AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/DataPlatform/Sequence/NbrSequenceService.cs

using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.DataPlatform.Sequence;

/// <summary>
/// 在 165 dopdemorq 上原子分配单号（不调用存储过程）。
/// 通过 mdp_source=DOPDEMORQ_SQLSERVER 取连接；源须启用且具备写权限。
/// </summary>
public sealed class NbrSequenceService : INbrSequenceService, ITransient
{
    public const string DefaultSourceCode = "DOPDEMORQ_SQLSERVER";
    private const int MaxLocateRetries = 3;

    private readonly MdpSourceScopeFactory _scopeFactory;

    public NbrSequenceService(MdpSourceScopeFactory scopeFactory)
    {
        _scopeFactory = scopeFactory;
    }

    public async Task<IReadOnlyList<string>> AllocateAsync(
        string nbrType,
        int increment = 1,
        string domain = "8010",
        string userNo = "",
        CancellationToken ct = default)
    {
        if (string.IsNullOrWhiteSpace(nbrType))
            throw new ArgumentException("nbrType 不能为空", nameof(nbrType));
        if (increment <= 0)
            throw new ArgumentOutOfRangeException(nameof(increment), "increment 必须 > 0");
        if (string.IsNullOrWhiteSpace(domain))
            throw new ArgumentException("domain 不能为空", nameof(domain));

        var db = await _scopeFactory.GetScopeAsync(DefaultSourceCode, ct);
        var now = DateTime.Now;

        var ctrl = await db.Ado.SqlQuerySingleAsync<NbrControlRow>(
            @"SELECT Domain, NbrType, ISNULL(NbrPre1,'') AS NbrPre1, ISNULL(NbrPre2,'') AS NbrPre2, ISNULL(NbrPre3,'') AS NbrPre3,
                     ISNULL(DateType,'') AS DateType, IniValue, NextValue AS CtrlNextValue, MinValue, MaxValue,
                     AllowReset, ResetValue, AllowSkip
                FROM NbrControl
               WHERE Domain=@domain AND NbrType=@nbrType",
            new SugarParameter("@domain", domain),
            new SugarParameter("@nbrType", nbrType));

        if (ctrl == null)
            throw new InvalidOperationException($"NbrControl 未配置：Domain={domain}, NbrType={nbrType}");

        var dateType = ctrl.DateType?.Trim() ?? "";
        string normalizedFmt = "";
        if (dateType.Length > 0)
        {
            var fmtRow = await db.Ado.SqlQuerySingleAsync<NbrDateFormatRow>(
                @"SELECT DateFormat FROM NbrDateFormat
                   WHERE Domain=@domain AND DateType=@dateType",
                new SugarParameter("@domain", domain),
                new SugarParameter("@dateType", dateType));
            // DateType 大小写不一致时（如 wo=yyyymmdd）再忽略大小写查一次
            if (fmtRow == null)
            {
                var all = await db.Ado.SqlQueryAsync<NbrDateFormatRow>(
                    "SELECT DateType, DateFormat FROM NbrDateFormat WHERE Domain=@domain",
                    new SugarParameter("@domain", domain));
                fmtRow = all.FirstOrDefault(x =>
                    string.Equals(x.DateType, dateType, StringComparison.OrdinalIgnoreCase));
            }
            normalizedFmt = NbrSequenceFormatter.NormalizeDateFormat(fmtRow?.DateFormat);
        }

        var (nbrDate, nbrDateStr) = NbrSequenceFormatter.ResolvePeriod(normalizedFmt, now);
        var padWidth = NbrSequenceFormatter.ResolvePadWidth(ctrl.MaxValue);
        var pre1 = ctrl.NbrPre1 ?? "";
        var pre2 = ctrl.NbrPre2 ?? "";
        var pre3 = ctrl.NbrPre3 ?? "";

        for (var attempt = 0; attempt < MaxLocateRetries; attempt++)
        {
            ct.ThrowIfCancellationRequested();
            var day = await LocateDayInfoAsync(db, domain, nbrType, normalizedFmt, nbrDate);
            if (day == null)
            {
                await TryInsertDayInfoAsync(db, ctrl, domain, nbrType, nbrDate, userNo);
                continue;
            }

            // 溢出预检：当前最后发出号 + 控制表 NextValue（余量）> MaxValue 时走重置/失败
            // 真正步进量是 increment；此处用 day.NextValue + increment 判断是否越界
            var projected = day.NextValue + increment;
            if (projected > ctrl.MaxValue)
            {
                if (ctrl.AllowReset)
                {
                    var resetTo = ctrl.ResetValue > 0 ? ctrl.ResetValue : ctrl.IniValue;
                    // 重置后首号 = resetTo，发出后 NextValue = resetTo - 1 + increment
                    // 用条件 UPDATE 确保仍在溢出态
                    var resetAffected = await db.Ado.ExecuteCommandAsync(
                        @"UPDATE NbrDayInfo WITH (ROWLOCK)
                             SET NextValue = @newNext, UpdateTime=GETDATE(), UpdateUser=@user
                           WHERE RecID=@recId AND NextValue=@oldNext",
                        new SugarParameter("@newNext", resetTo - 1 + increment),
                        new SugarParameter("@user", userNo ?? ""),
                        new SugarParameter("@recId", day.RecID),
                        new SugarParameter("@oldNext", day.NextValue));
                    if (resetAffected == 0) continue;
                    return NbrSequenceFormatter.BuildRange(pre1, pre2, pre3, nbrDateStr, (long)resetTo, increment, padWidth);
                }

                throw new InvalidOperationException(
                    $"创建单号失败,单号超过最大数，请重置：Domain={domain}, NbrType={nbrType}, NextValue={day.NextValue}, MaxValue={ctrl.MaxValue}");
            }

            // 原子递增：OUTPUT deleted.NextValue = 本次号段起点（最后已发）
            var lastIssued = await db.Ado.SqlQuerySingleAsync<decimal?>(
                @"UPDATE NbrDayInfo WITH (ROWLOCK)
                     SET NextValue = NextValue + @increment,
                         UpdateTime = GETDATE(),
                         UpdateUser = @user
                OUTPUT deleted.NextValue
                 WHERE RecID = @recId",
                new SugarParameter("@increment", increment),
                new SugarParameter("@user", userNo ?? ""),
                new SugarParameter("@recId", day.RecID));

            if (lastIssued == null)
                continue; // 行被并发删/改，重试

            var first = (long)lastIssued.Value + 1;
            // 格式化前再校验宽度（陷阱 #6）
            return NbrSequenceFormatter.BuildRange(pre1, pre2, pre3, nbrDateStr, first, increment, padWidth);
        }

        throw new InvalidOperationException($"单号分配失败：无法定位/更新 NbrDayInfo（Domain={domain}, NbrType={nbrType}）");
    }

    private static async Task<NbrDayInfoRow?> LocateDayInfoAsync(
        ISqlSugarClient db, string domain, string nbrType, string normalizedFmt, DateTime nbrDate)
    {
        if (normalizedFmt.Length == 0)
        {
            return await db.Ado.SqlQuerySingleAsync<NbrDayInfoRow>(
                @"SELECT TOP 1 RecID, Domain, NbrType, Today, NextValue, MaxValue, IniValue
                    FROM NbrDayInfo
                   WHERE Domain=@domain AND NbrType=@nbrType
                   ORDER BY RecID",
                new SugarParameter("@domain", domain),
                new SugarParameter("@nbrType", nbrType));
        }

        var dateKey = nbrDate.ToString("yyyy-MM-dd");
        return await db.Ado.SqlQuerySingleAsync<NbrDayInfoRow>(
            @"SELECT TOP 1 RecID, Domain, NbrType, Today, NextValue, MaxValue, IniValue
                FROM NbrDayInfo
               WHERE Domain=@domain AND NbrType=@nbrType
                 AND CONVERT(varchar(10), Today, 23) = @nbrDate
               ORDER BY RecID",
            new SugarParameter("@domain", domain),
            new SugarParameter("@nbrType", nbrType),
            new SugarParameter("@nbrDate", dateKey));
    }

    private static async Task TryInsertDayInfoAsync(
        ISqlSugarClient db, NbrControlRow ctrl, string domain, string nbrType, DateTime nbrDate, string userNo)
    {
        // 从 NbrControl 整行复制；NextValue 先置 IniValue-1，首次原子递增后发出 IniValue
        // 并发下可能双插，靠后续重新定位收敛（165 不可加唯一索引）
        var dateKey = nbrDate.ToString("yyyy-MM-dd");
        await db.Ado.ExecuteCommandAsync(
            @"IF NOT EXISTS (
                  SELECT 1 FROM NbrDayInfo
                   WHERE Domain=@domain AND NbrType=@nbrType
                     AND (@hasDate=0 OR CONVERT(varchar(10),Today,23)=@nbrDate)
              )
              INSERT INTO NbrDayInfo
                (Domain, NbrType, Today, NextValue, MaxValue, IniValue, MinValue,
                 CreateTime, CreateUser, UpdateTime, UpdateUser)
              SELECT Domain, NbrType, @today, IniValue - 1, MaxValue, IniValue, MinValue,
                     GETDATE(), @user, GETDATE(), @user
                FROM NbrControl
               WHERE Domain=@domain AND NbrType=@nbrType",
            new SugarParameter("@domain", domain),
            new SugarParameter("@nbrType", nbrType),
            new SugarParameter("@hasDate", string.IsNullOrEmpty(ctrl.DateType) ? 0 : 1),
            new SugarParameter("@nbrDate", dateKey),
            new SugarParameter("@today", nbrDate.Date),
            new SugarParameter("@user", userNo ?? ""));
    }

    private sealed class NbrControlRow
    {
        public string Domain { get; set; } = "";
        public string NbrType { get; set; } = "";
        public string NbrPre1 { get; set; } = "";
        public string NbrPre2 { get; set; } = "";
        public string NbrPre3 { get; set; } = "";
        public string DateType { get; set; } = "";
        public decimal IniValue { get; set; }
        public decimal CtrlNextValue { get; set; }
        public decimal MinValue { get; set; }
        public decimal MaxValue { get; set; }
        public bool AllowReset { get; set; }
        public decimal ResetValue { get; set; }
        public bool AllowSkip { get; set; }
    }

    private sealed class NbrDateFormatRow
    {
        public string? DateType { get; set; }
        public string? DateFormat { get; set; }
    }

    private sealed class NbrDayInfoRow
    {
        public int RecID { get; set; }
        public string Domain { get; set; } = "";
        public string NbrType { get; set; } = "";
        public DateTime? Today { get; set; }
        public decimal NextValue { get; set; }
        public decimal MaxValue { get; set; }
        public decimal IniValue { get; set; }
    }
}