AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/DataPlatform/Wms/WmsBaseDataService.cs

using System.Text.Json;
using Admin.NET.Plugin.AiDOP.DataPlatform.Executors;
using Admin.NET.Plugin.AiDOP.Entity.DataPlatform;
using Microsoft.Extensions.Logging;
using SqlSugar;

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

/// <summary>
/// WP7 §2：WMS 六张基础数据表只读查询 + Outbox UPSERT 回写编排。
/// 不直写 165；执行侧实时量列禁止入队。
/// </summary>
[ApiDescriptionSettings(Order = 326, Description = "WMS基础数据维护")]
[Route("api/aidop/wms-base")]
[AllowAnonymous]
[NonUnify]
public class WmsBaseDataService : IDynamicApiController, ITransient
{
    public const string TargetSource = "DOPDEMORQ_SQLSERVER";

    private static readonly HashSet<string> AllowedTables = new(StringComparer.OrdinalIgnoreCase)
    {
        "DepartmentMaster", "EmployeeMaster", "LocationMaster",
        "LocationShelfMaster", "EmpWorkDutyMaster", "LinePrinter"
    };

    private static readonly HashSet<string> ShelfForbiddenWriteCols = new(StringComparer.OrdinalIgnoreCase)
    {
        "QtyOnHand", "UsedVolumeCapacity", "UsedWeightCapacity", "ContainerStatus"
    };

    private static readonly Dictionary<string, string[]> BusinessKeys = new(StringComparer.OrdinalIgnoreCase)
    {
        ["DepartmentMaster"] = ["Domain", "Department"],
        ["EmployeeMaster"] = ["Domain", "Employee"],
        ["LocationMaster"] = ["Domain", "Location"],
        ["LocationShelfMaster"] = ["Domain", "Location", "InvShelf"],
        ["EmpWorkDutyMaster"] = ["Domain", "RecID"],
        ["LinePrinter"] = ["Domain", "Line", "BarType"]
    };

    private readonly MdpSourceScopeFactory _scopeFactory;
    private readonly MdpOutboxEnqueueService _enqueue;
    private readonly ILogger<WmsBaseDataService> _logger;

    public WmsBaseDataService(
        MdpSourceScopeFactory scopeFactory,
        MdpOutboxEnqueueService enqueue,
        ILogger<WmsBaseDataService> logger)
    {
        _scopeFactory = scopeFactory;
        _enqueue = enqueue;
        _logger = logger;
    }

    [DisplayName("基础数据分页查询")]
    [HttpGet("page")]
    public async Task<object> Page(
        [FromQuery] string table,
        [FromQuery] string? domain,
        [FromQuery] string? keyword,
        [FromQuery] int page = 1,
        [FromQuery] int pageSize = 50,
        CancellationToken ct = default)
    {
        ValidateTable(table);
        if (page < 1) page = 1;
        if (pageSize < 1 || pageSize > 200) pageSize = 50;

        var db = await _scopeFactory.GetScopeAsync(TargetSource, ct);
        var d = string.IsNullOrWhiteSpace(domain) ? "8010" : domain.Trim();
        var (sql, countSql, pars) = BuildListSql(table, d, keyword);
        var total = Convert.ToInt32(await db.Ado.GetScalarAsync(countSql, pars));
        var offset = (page - 1) * pageSize;
        var pageSql = sql + $" ORDER BY RecID OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY";
        var rows = await db.Ado.SqlQueryAsync<dynamic>(pageSql, pars);
        return new { table, domain = d, total, page, pageSize, list = rows };
    }

    [DisplayName("基础数据 UPSERT 入队")]
    [HttpPost("upsert")]
    public async Task<object> Upsert([FromBody] WmsBaseUpsertInput input, CancellationToken ct = default)
    {
        if (input == null) throw Oops.Oh("body 不能为空");
        ValidateTable(input.Table);
        if (input.Row == null || input.Row.Count == 0) throw Oops.Oh("row 不能为空");

        var row = NormalizeRow(input.Table, input.Row);
        ValidateEmpWorkDutyRow(input.Table, row);
        var keys = ExtractKeys(input.Table, row);
        if (keys.Count == 0) throw Oops.Oh("缺少业务键");

        // LocationMaster.Storer 变更影响派单：记审计日志
        if (string.Equals(input.Table, "LocationMaster", StringComparison.OrdinalIgnoreCase)
            && row.TryGetValue("Storer", out var newStorer))
        {
            _logger.LogWarning(
                "WMS LocationMaster.Storer 变更入队 Domain={Domain} Location={Location} Storer={Storer}",
                keys.GetValueOrDefault("Domain"), keys.GetValueOrDefault("Location"), newStorer);
        }

        var insert = new Dictionary<string, object?>(row, StringComparer.OrdinalIgnoreCase);
        var update = new Dictionary<string, object?>(row, StringComparer.OrdinalIgnoreCase);
        // identity / 业务键不进 UPDATE SET
        foreach (var k in keys.Keys) update.Remove(k);
        update.Remove("RecID");
        insert.Remove("RecID"); // identity

        if (update.Count == 0 && insert.Count == 0)
            throw Oops.Oh("没有可写入列");

        var payload = new Dictionary<string, object?>
        {
            ["op"] = "UPSERT",
            ["table"] = input.Table,
            ["keys"] = keys,
            ["insert"] = insert,
            ["update"] = update,
            ["expect"] = new Dictionary<string, object?>()
        };
        var json = JsonSerializer.Serialize(payload);
        var keyParts = string.Join("|", keys.OrderBy(x => x.Key, StringComparer.OrdinalIgnoreCase)
            .Select(kv => $"{kv.Key}={kv.Value}"));
        // 业务键幂等：去掉 payload 哈希尾巴，同业务对象重复 upsert 命中同一 idem_key（WP10 S1 / F5）
        var idem = $"wmsbase|{input.Table}|{keyParts}";

        var item = new MdpOutbox
        {
            TenantId = input.TenantId,
            TargetSourceCode = TargetSource,
            ActionCode = $"WMS_BASE_{input.Table.ToUpperInvariant()}",
            IdemKey = idem.Length > 200 ? idem[..200] : idem,
            PayloadJson = json
        };
        var enqueued = await _enqueue.TryEnqueueAsync(item, ct);
        return new { enqueued, idemKey = item.IdemKey, table = input.Table, keys };
    }

    private static (string sql, string countSql, SugarParameter[] pars) BuildListSql(
        string table, string domain, string? keyword)
    {
        var pars = new List<SugarParameter> { new("@d", domain) };
        var where = "Domain=@d";
        if (!string.IsNullOrWhiteSpace(keyword))
        {
            var kw = $"%{keyword.Trim()}%";
            pars.Add(new SugarParameter("@kw", kw));
            where += table.ToLowerInvariant() switch
            {
                "departmentmaster" => " AND (Department LIKE @kw OR Descr LIKE @kw)",
                "employeemaster" => " AND (Employee LIKE @kw OR Name LIKE @kw OR UserName LIKE @kw)",
                "locationmaster" => " AND (Location LIKE @kw OR Descr LIKE @kw OR Storer LIKE @kw)",
                "locationshelfmaster" => " AND (Location LIKE @kw OR InvShelf LIKE @kw OR Descr LIKE @kw)",
                "empworkdutymaster" => " AND (Employee LIKE @kw OR ItemNum1 LIKE @kw OR ItemNum2 LIKE @kw OR Location LIKE @kw OR Duty LIKE @kw)",
                "lineprinter" => " AND (Line LIKE @kw OR BarType LIKE @kw OR Pinter LIKE @kw OR Storer LIKE @kw)",
                _ => ""
            };
        }

        var sql = $"SELECT * FROM [{table}] WHERE {where}";
        var countSql = $"SELECT COUNT(1) FROM [{table}] WHERE {where}";
        return (sql, countSql, pars.ToArray());
    }

    private static Dictionary<string, object?> NormalizeRow(string table, Dictionary<string, object?> row)
    {
        var result = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);
        foreach (var kv in row)
        {
            if (string.IsNullOrWhiteSpace(kv.Key)) continue;
            if (string.Equals(table, "LocationShelfMaster", StringComparison.OrdinalIgnoreCase)
                && ShelfForbiddenWriteCols.Contains(kv.Key))
                continue;
            result[kv.Key] = UnwrapJson(kv.Value);
        }

        if (!result.ContainsKey("UpdateTime"))
            result["UpdateTime"] = DateTime.Now;

        // WP7 §3.6：职责区间边界归一
        if (string.Equals(table, "EmpWorkDutyMaster", StringComparison.OrdinalIgnoreCase))
        {
            foreach (var col in new[] { "ItemNum1", "ItemNum2" })
            {
                if (result.TryGetValue(col, out var v) && v is string s)
                    result[col] = s.Trim().ToUpperInvariant();
            }
        }
        return result;
    }

    /// <summary>WP7 §3.6 B5：强制 ItemNum 区间字符集与方向。</summary>
    private static void ValidateEmpWorkDutyRow(string table, Dictionary<string, object?> row)
    {
        if (!string.Equals(table, "EmpWorkDutyMaster", StringComparison.OrdinalIgnoreCase)) return;
        static string Norm(object? v) => (v?.ToString() ?? "").Trim().ToUpperInvariant();
        var a = Norm(row.GetValueOrDefault("ItemNum1"));
        var b = Norm(row.GetValueOrDefault("ItemNum2"));
        if (a.Length is < 1 or > 24 || b.Length is < 1 or > 24
            || !System.Text.RegularExpressions.Regex.IsMatch(a, "^[0-9A-Z]+$")
            || !System.Text.RegularExpressions.Regex.IsMatch(b, "^[0-9A-Z]+$"))
            throw Oops.Oh("EmpWorkDutyMaster.ItemNum1/ItemNum2 须为 1–24 位 [0-9A-Z]（已自动 Trim/大写）");
        if (string.Compare(a, b, StringComparison.Ordinal) > 0)
            throw Oops.Oh("EmpWorkDutyMaster 区间反向：ItemNum1 不得大于 ItemNum2");
        row["ItemNum1"] = a;
        row["ItemNum2"] = b;
    }

    private static Dictionary<string, object?> ExtractKeys(string table, Dictionary<string, object?> row)
    {
        var keys = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);
        var isDuty = string.Equals(table, "EmpWorkDutyMaster", StringComparison.OrdinalIgnoreCase);
        var recId = ReadInt64(row, "RecID");
        var dutyIsNew = isDuty && (recId == null || recId <= 0);

        foreach (var k in BusinessKeys[table])
        {
            if (dutyIsNew && string.Equals(k, "RecID", StringComparison.OrdinalIgnoreCase))
                continue;
            if (!row.TryGetValue(k, out var v) || v == null || (v is string s && string.IsNullOrWhiteSpace(s)))
                throw Oops.Oh($"缺少业务键 {k}");
            keys[k] = v;
        }

        if (dutyIsNew)
        {
            // 新建行：自然键做 EXISTS；INSERT 不含 RecID（identity）
            foreach (var nk in new[] { "Domain", "Employee", "ItemNum1", "ItemNum2", "Location", "DutyType" })
            {
                if (!row.TryGetValue(nk, out var v) || v == null || (v is string s && string.IsNullOrWhiteSpace(s)))
                    throw Oops.Oh($"EmpWorkDutyMaster 新建缺少自然键 {nk}");
                keys[nk] = v;
            }
        }

        return keys;
    }

    private static object? UnwrapJson(object? v)
    {
        if (v is JsonElement je)
        {
            return je.ValueKind switch
            {
                JsonValueKind.Null or JsonValueKind.Undefined => null,
                JsonValueKind.String => je.GetString(),
                JsonValueKind.True => true,
                JsonValueKind.False => false,
                JsonValueKind.Number => je.TryGetInt64(out var l) ? l : je.GetDecimal(),
                _ => je.GetRawText()
            };
        }
        return v;
    }

    private static long? ReadInt64(Dictionary<string, object?> row, string key)
    {
        if (!row.TryGetValue(key, out var v) || v == null) return null;
        v = UnwrapJson(v);
        return v switch
        {
            long l => l,
            int i => i,
            short s => s,
            decimal d => (long)d,
            string str when long.TryParse(str, out var p) => p,
            _ => null
        };
    }

    private static void ValidateTable(string? table)
    {
        if (string.IsNullOrWhiteSpace(table) || !AllowedTables.Contains(table))
            throw Oops.Oh($"不支持的表：{table}");
    }

    private static string StableHash(string s)
    {
        var hash = System.Security.Cryptography.SHA256.HashData(System.Text.Encoding.UTF8.GetBytes(s));
        return Convert.ToHexString(hash.AsSpan(0, 8));
    }
}

public sealed class WmsBaseUpsertInput
{
    public string Table { get; set; } = "";
    public long TenantId { get; set; }
    public Dictionary<string, object?> Row { get; set; } = new(StringComparer.OrdinalIgnoreCase);
}