AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/FinishedWarehouse/FqcSpecMaterialMapService.cs

using System.Text.RegularExpressions;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.FinishedWarehouse;

/// <summary>
/// S7 FQC 检规↔标准物料 派生映射服务（G1）。
///
/// 目的：解决"报检物料 → 检规"精确匹配。源 qms_ccjygf.wlbm 是多物料脏串（分隔符不统一 + 粘连 + 横杠区间），
/// 直接 LIKE 会误配（实证 31 token 互为子串）。本服务把 wlbm 清洗成 token，与 ItemMaster.ItemNum 精确匹配，
/// 落派生桥表 ado_s7_fqc_spec_material_map，供"报检物料→检规"稳定查询与人工订正。
///
/// 硬边界：① 绝不修改原始 qms_ccjygf.wlbm/sxrq；② 禁止 LIKE 子串匹配；③ 粘连不自动拆（词典缺片段），标 GLUE_UNRESOLVED；
/// ④ 全部按 ResolveTenantOrThrow 登录租户隔离，禁透传源行租户。
/// </summary>
[ApiDescriptionSettings(Order = 340, Description = "FQC检规物料映射")]
[Route("api/S7FqcSpecMap")]
[NonUnify]
public class FqcSpecMaterialMapService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

    public FqcSpecMaterialMapService(ISqlSugarClient db, UserManager userManager)
    {
        _db = db;
        _userManager = userManager;
    }

    private long ResolveTenantOrThrow() => AidopTenantScope.ResolveOrThrow(_userManager);

    /// <summary>wlbm 拆分正则：半/全角分号逗号、换行回车 tab、斜杠、半/全角冒号、空白。</summary>
    private static readonly Regex TokenSplitter = new(@"[;；,，\r\n\t/:：\s]+", RegexOptions.Compiled);

    /// <summary>纯物料码形态（字母数字，长度 4-12）；用于粘连/异常识别（非白名单，仅分级）。</summary>
    private static readonly Regex PlainCodeShape = new(@"^[A-Za-z0-9]{4,12}$", RegexOptions.Compiled);

    // ============================ 清洗重建 ============================

    /// <summary>
    /// 重建当前租户的检规↔物料映射（清洗 wlbm → 精确匹配 ItemMaster → 落桥表）。
    /// 只删除并重建自动派生行（is_manual=0）；人工绑定行（is_manual=1）原样保留。
    /// </summary>
    [DisplayName("重建检规物料映射")]
    [HttpPost("rebuild")]
    public async Task<object> Rebuild(CancellationToken ct)
    {
        var tenantId = ResolveTenantOrThrow();
        var now = DateTime.Now;
        var userId = _userManager.UserId;

        // 1) 读检规主表
        var specs = await _db.Ado.SqlQueryAsync<SpecRow>(
            "SELECT id AS Id, wjbh AS Wjbh, bb AS Bb, sxrq AS Sxrq, wlbm AS Wlbm FROM qms_ccjygf WHERE tenant_id=@t",
            new SugarParameter("@t", tenantId));

        // 2) 物料词典（当前租户 ItemMaster.ItemNum 精确集合）
        var itemSet = new HashSet<string>(
            await _db.Ado.SqlQueryAsync<string>(
                "SELECT DISTINCT ItemNum FROM ItemMaster WHERE tenant_id=@t AND ItemNum IS NOT NULL",
                new SugarParameter("@t", tenantId)),
            StringComparer.OrdinalIgnoreCase);

        var rows = new List<Dictionary<string, object>>();
        int exact = 0, notFound = 0, glue = 0, total = 0;

        foreach (var s in specs)
        {
            var norm = NormalizeDate(s.Sxrq);
            var seen = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
            var tokens = SplitTokens(s.Wlbm);
            int seq = 0;
            foreach (var raw in tokens)
            {
                var cleaned = raw.Trim();
                if (cleaned.Length == 0) continue;
                if (!seen.Add(cleaned)) continue; // 同检规内去重
                seq++;
                total++;

                string status;
                string? matched = null;
                if (itemSet.Contains(cleaned)) { status = "EXACT"; matched = cleaned; exact++; }
                else if (!PlainCodeShape.IsMatch(cleaned)) { status = "GLUE_UNRESOLVED"; glue++; } // 含区间/异常字符/超长=粘连或非单码
                else { status = "NOT_FOUND"; notFound++; }

                rows.Add(new Dictionary<string, object>
                {
                    ["id"] = YitIdHelper.NextId(),
                    ["tenant_id"] = tenantId,
                    ["jygfid"] = s.Id,
                    ["wjbh"] = s.Wjbh,
                    ["bb"] = s.Bb,
                    ["seq"] = seq,
                    ["raw_token"] = raw,
                    ["cleaned_token"] = cleaned,
                    ["material_code"] = (object?)matched ?? DBNull.Value,
                    ["match_status"] = status,
                    ["normalized_effective_date"] = (object?)norm ?? DBNull.Value,
                    ["note"] = DBNull.Value,
                    ["is_manual"] = 0,
                    ["status"] = 1,
                    ["create_time"] = now,
                    ["create_user_id"] = (object?)userId ?? DBNull.Value,
                });
            }
        }

        var tran = await _db.Ado.UseTranAsync(async () =>
        {
            // 只清自动派生行，人工绑定(is_manual=1)保留
            await _db.Ado.ExecuteCommandAsync(
                "DELETE FROM ado_s7_fqc_spec_material_map WHERE tenant_id=@t AND is_manual=0",
                new SugarParameter("@t", tenantId));
            if (rows.Count > 0)
                await BulkInsertMapRowsAsync(rows);
        });
        if (!tran.IsSuccess) throw tran.ErrorException ?? Oops.Oh("重建映射失败");

        return new
        {
            ok = true,
            specCount = specs.Count,
            token = total,
            exact,
            notFound,
            glueUnresolved = glue,
            exactRate = total == 0 ? 0 : Math.Round(exact * 100.0 / total, 1)
        };
    }

    // ============================ 查询/维护 ============================

    /// <summary>映射列表（维护用，可按物料/状态筛选）。</summary>
    [DisplayName("检规物料映射列表")]
    [HttpGet("page")]
    public async Task<object> Page([FromQuery] FqcSpecMapPageInput input)
    {
        var tenantId = ResolveTenantOrThrow();
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 20 : (input.PageSize > 200 ? 200 : input.PageSize);
        var where = new List<string> { "m.tenant_id=@t" };
        var pars = new List<SugarParameter> { new("@t", tenantId) };
        if (!string.IsNullOrWhiteSpace(input.MaterialCode))
        {
            where.Add("m.cleaned_token=@mc"); // 精确，禁 LIKE
            pars.Add(new SugarParameter("@mc", input.MaterialCode.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.MatchStatus))
        {
            where.Add("m.match_status=@ms");
            pars.Add(new SugarParameter("@ms", input.MatchStatus.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.Wjbh))
        {
            where.Add("m.wjbh=@wj");
            pars.Add(new SugarParameter("@wj", input.Wjbh.Trim()));
        }
        var whereSql = string.Join(" AND ", where);
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM ado_s7_fqc_spec_material_map m WHERE {whereSql}", pars);
        var list = await _db.Ado.SqlQueryAsync<FqcSpecMapRow>(
            $@"SELECT m.id AS Id, m.jygfid AS Jygfid, m.wjbh AS Wjbh, m.bb AS Bb, m.raw_token AS RawToken,
                     m.cleaned_token AS CleanedToken, m.material_code AS MaterialCode, m.match_status AS MatchStatus,
                     m.normalized_effective_date AS NormalizedEffectiveDate, m.is_manual AS IsManual, m.note AS Note
              FROM ado_s7_fqc_spec_material_map m WHERE {whereSql}
              ORDER BY m.match_status='EXACT' DESC, m.wjbh, m.seq
              LIMIT {pageSize} OFFSET {(page - 1) * pageSize}", pars);
        return new { total, page, pageSize, list };
    }

    /// <summary>人工绑定：把一个标准物料码绑定到指定检规（is_manual=1，脱敏环境订正用；不改源 wlbm）。</summary>
    [DisplayName("人工绑定检规物料")]
    [HttpPost("bind")]
    public async Task<object> Bind([FromBody] FqcSpecMapBindInput input)
    {
        var tenantId = ResolveTenantOrThrow();
        if (input == null || input.Jygfid <= 0 || string.IsNullOrWhiteSpace(input.MaterialCode))
            throw Oops.Oh("检规 id 与物料编码必填");
        var materialCode = input.MaterialCode.Trim();

        var spec = await _db.Ado.SqlQuerySingleAsync<SpecRow>(
            "SELECT id AS Id, wjbh AS Wjbh, bb AS Bb, sxrq AS Sxrq FROM qms_ccjygf WHERE id=@id AND tenant_id=@t LIMIT 1",
            new SugarParameter("@id", input.Jygfid), new SugarParameter("@t", tenantId));
        if (spec == null) throw Oops.Oh("检规不存在或不属于当前租户");

        var itemExists = await _db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM ItemMaster WHERE tenant_id=@t AND ItemNum=@mc",
            new SugarParameter("@t", tenantId), new SugarParameter("@mc", materialCode));
        if (itemExists == 0) throw Oops.Oh($"物料 {materialCode} 不在当前租户 ItemMaster，无法绑定");

        // 幂等：同租户+检规+物料+人工 唯一（raw_token 用 materialCode 承载）
        var dup = await _db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM ado_s7_fqc_spec_material_map WHERE tenant_id=@t AND jygfid=@j AND cleaned_token=@mc AND is_manual=1",
            new SugarParameter("@t", tenantId), new SugarParameter("@j", input.Jygfid), new SugarParameter("@mc", materialCode));
        if (dup > 0) return new { ok = true, message = "已存在该人工绑定" };

        await BulkInsertMapRowsAsync(new List<Dictionary<string, object>>
        {
            new()
            {
                ["id"] = YitIdHelper.NextId(),
                ["tenant_id"] = tenantId,
                ["jygfid"] = input.Jygfid,
                ["wjbh"] = spec.Wjbh,
                ["bb"] = spec.Bb,
                ["seq"] = 0,
                ["raw_token"] = materialCode,
                ["cleaned_token"] = materialCode,
                ["material_code"] = materialCode,
                ["match_status"] = "MANUAL",
                ["normalized_effective_date"] = (object?)NormalizeDate(spec.Sxrq) ?? DBNull.Value,
                ["note"] = input.Note,
                ["is_manual"] = 1,
                ["status"] = 1,
                ["create_time"] = DateTime.Now,
                ["create_user_id"] = _userManager.UserId,
            }
        });
        return new { ok = true };
    }

    /// <summary>解绑人工绑定。</summary>
    [DisplayName("解绑检规物料")]
    [HttpPost("unbind")]
    public async Task<object> Unbind([FromBody] FqcSpecMapUnbindInput input)
    {
        var tenantId = ResolveTenantOrThrow();
        if (input == null || input.Id <= 0) throw Oops.Oh("id 必填");
        var n = await _db.Ado.ExecuteCommandAsync(
            "DELETE FROM ado_s7_fqc_spec_material_map WHERE id=@id AND tenant_id=@t AND is_manual=1",
            new SugarParameter("@id", input.Id), new SugarParameter("@t", tenantId));
        return new { ok = n > 0 };
    }

    /// <summary>
    /// 按物料精确解析检规候选（供生成检验单调用）。返回 EXACT/MANUAL 命中的检规，多命中/多版本由调用方转人工选。
    /// </summary>
    [DisplayName("按物料解析检规")]
    [HttpGet("resolve")]
    public async Task<List<FqcSpecCandidate>> Resolve([FromQuery] string materialCode)
    {
        var tenantId = ResolveTenantOrThrow();
        if (string.IsNullOrWhiteSpace(materialCode)) throw Oops.Oh("物料编码必填");
        return await ResolveInternalAsync(tenantId, materialCode.Trim());
    }

    /// <summary>内部复用：按物料精确取检规候选（EXACT/MANUAL），按生效日期新→旧排序。</summary>
    public async Task<List<FqcSpecCandidate>> ResolveInternalAsync(long tenantId, string materialCode)
    {
        return await _db.Ado.SqlQueryAsync<FqcSpecCandidate>(
            @"SELECT g.id AS Jygfid, g.wjbh AS Wjbh, g.bb AS Bb, g.sxrq AS Sxrq,
                     m.normalized_effective_date AS NormalizedEffectiveDate, m.match_status AS MatchStatus
              FROM ado_s7_fqc_spec_material_map m
              JOIN qms_ccjygf g ON g.id=m.jygfid AND g.tenant_id=m.tenant_id
              WHERE m.tenant_id=@t AND m.status=1 AND m.material_code=@mc
                AND m.match_status IN ('EXACT','MANUAL')
              ORDER BY m.normalized_effective_date DESC, g.id DESC",
            new SugarParameter("@t", tenantId), new SugarParameter("@mc", materialCode));
    }

    // ============================ 辅助 ============================

    private static IEnumerable<string> SplitTokens(string? wlbm)
    {
        if (string.IsNullOrWhiteSpace(wlbm)) return Array.Empty<string>();
        return TokenSplitter.Split(wlbm).Where(x => !string.IsNullOrWhiteSpace(x));
    }

    /// <summary>
    /// 参数化分块批插桥表行。替代 db.Insertable(List&lt;Dictionary&gt;).AS(表)——后者对 nullable datetime 列
    /// (normalized_effective_date) 的 DBNull 会被 SqlSugar 字典批插按空串渲染，触发 MySQL
    /// "Incorrect datetime value: ''"。改用显式参数化多行 INSERT（SugarParameter 正确处理 DBNull→NULL）。
    /// </summary>
    private static readonly string[] MapColumns =
    {
        "id", "tenant_id", "jygfid", "wjbh", "bb", "seq", "raw_token", "cleaned_token", "material_code",
        "match_status", "normalized_effective_date", "note", "is_manual", "status", "create_time", "create_user_id"
    };

    private async Task BulkInsertMapRowsAsync(List<Dictionary<string, object>> rows)
    {
        if (rows == null || rows.Count == 0) return;
        const int chunkSize = 200;
        var colList = string.Join(", ", MapColumns);
        for (var start = 0; start < rows.Count; start += chunkSize)
        {
            var slice = rows.GetRange(start, Math.Min(chunkSize, rows.Count - start));
            var valueClauses = new List<string>(slice.Count);
            var pars = new List<SugarParameter>(slice.Count * MapColumns.Length);
            for (var r = 0; r < slice.Count; r++)
            {
                var placeholders = new string[MapColumns.Length];
                for (var c = 0; c < MapColumns.Length; c++)
                {
                    var name = $"@{MapColumns[c]}_{r}";
                    placeholders[c] = name;
                    var val = slice[r].TryGetValue(MapColumns[c], out var v) ? (v ?? DBNull.Value) : DBNull.Value;
                    pars.Add(new SugarParameter(name, val));
                }
                valueClauses.Add("(" + string.Join(", ", placeholders) + ")");
            }
            var sql = $"INSERT INTO ado_s7_fqc_spec_material_map ({colList}) VALUES {string.Join(", ", valueClauses)}";
            await _db.Ado.ExecuteCommandAsync(sql, pars);
        }
    }

    /// <summary>规范化 sxrq（容错 dot/双点/横杠/带时分秒/非日期），失败返回 null（不覆盖源）。</summary>
    private static DateTime? NormalizeDate(string? sxrq)
    {
        if (string.IsNullOrWhiteSpace(sxrq)) return null;
        var s = sxrq.Trim().Replace("..", ".").Replace('.', '-').Replace('/', '-');
        var datePart = s.Split(' ')[0];
        if (DateTime.TryParse(datePart, out var d)) return d.Date;
        return null;
    }

    private sealed class SpecRow
    {
        public long Id { get; set; }
        public string? Wjbh { get; set; }
        public string? Bb { get; set; }
        public string? Sxrq { get; set; }
        public string? Wlbm { get; set; }
    }
}

public class FqcSpecMapPageInput
{
    public string? MaterialCode { get; set; }
    public string? MatchStatus { get; set; }
    public string? Wjbh { get; set; }
    public int Page { get; set; } = 1;
    public int PageSize { get; set; } = 20;
}

public class FqcSpecMapRow
{
    public long Id { get; set; }
    public long Jygfid { get; set; }
    public string? Wjbh { get; set; }
    public string? Bb { get; set; }
    public string? RawToken { get; set; }
    public string? CleanedToken { get; set; }
    public string? MaterialCode { get; set; }
    public string? MatchStatus { get; set; }
    public DateTime? NormalizedEffectiveDate { get; set; }
    public int IsManual { get; set; }
    public string? Note { get; set; }
}

public class FqcSpecMapBindInput
{
    public long Jygfid { get; set; }
    public string MaterialCode { get; set; } = string.Empty;
    public string? Note { get; set; }
}

public class FqcSpecMapUnbindInput
{
    public long Id { get; set; }
}

public class FqcSpecCandidate
{
    public long Jygfid { get; set; }
    public string? Wjbh { get; set; }
    public string? Bb { get; set; }
    public string? Sxrq { get; set; }
    public DateTime? NormalizedEffectiveDate { get; set; }
    public string? MatchStatus { get; set; }
}