using Admin.NET.Plugin.AiDOP.Infrastructure;
using Microsoft.Extensions.Logging;
using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.FinishedWarehouse;

/// <summary>
/// S7 FQC 成品报检（Phase 1C）。复刻旧系统"组织实验通知单"：选一张（完工）工单 → 回填 → 保存成 qms_fqcbj 报检。
///
/// 复用 S5 IqcTaskEntryService 范式：ResolveTenantOrThrow(登录租户，非透传源)、雪花 id、本库取号(FQCBJ+日期+序号,配 1A UNIQUE)、
/// UseTranAsync、写成功后触发 FQC refresh(解耦，失败不回滚业务)。
///
/// 回填可信边界(§十二)：wlbm/cpmc/cpxh/scph/sczld/sczldsl 一律由后端按用户选的 WorkOrd + ItemMaster 真实数据重读，
/// 不信前端提交值；人工字段 sl/yxj/jfbh/FCOMMENT 取前端。
/// 完工口径(§八)：不做无证据硬拦截，候选正常展示 + 完工标记由前端提示；仅缺 ItemNum/关键键 硬拒。
/// 只读 WorkOrdMaster/ItemMaster，只写 qms_fqcbj。本轮不实现"生成检验单"(Phase 1D)。
/// </summary>
[ApiDescriptionSettings(Order = 342, Description = "FQC成品报检")]
[Route("api/S7FqcApply")]
[NonUnify]
public class FqcApplyService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly FqcMdpSyncService _fqcMdpSyncService;
    private readonly FqcSpecMaterialMapService _specMap;
    private readonly ILogger _logger;

    public FqcApplyService(ISqlSugarClient db, UserManager userManager, FqcMdpSyncService fqcMdpSyncService, FqcSpecMaterialMapService specMap, ILoggerFactory loggerFactory)
    {
        _db = db;
        _userManager = userManager;
        _fqcMdpSyncService = fqcMdpSyncService;
        _specMap = specMap;
        _logger = loggerFactory.CreateLogger(nameof(FqcApplyService));
    }

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

    /// <summary>成品完工报检默认业务类型/单据类型（占位，实际字典未接，原样落库）。</summary>
    private const string FqcBillType = "FQC";

    // ============================ 完工工单候选 ============================

    /// <summary>
    /// 引用生产指令单：完工工单候选（WorkOrdMaster + ItemMaster 富化，只读，租户隔离）。
    /// 不做完工硬拦截；返回 qtyOrded/qtyCompleted/status 供前端行内提示。
    /// </summary>
    [DisplayName("完工工单候选")]
    [HttpGet("work-orders")]
    public async Task<object> WorkOrders([FromQuery] FqcWorkOrderQueryInput input)
    {
        var tenantId = ResolveTenantOrThrow();
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : (input.PageSize > 200 ? 200 : input.PageSize);
        var where = new List<string> { "w.tenant_id=@t", "w.ItemNum IS NOT NULL", "w.ItemNum<>''" };
        var pars = new List<SugarParameter> { new("@t", tenantId) };
        void Like(string? v, string col, string p) { if (!string.IsNullOrWhiteSpace(v)) { where.Add($"{col} LIKE @{p}"); pars.Add(new SugarParameter($"@{p}", $"%{v.Trim()}%")); } }
        Like(input.Batch, "w.Batch", "Batch");
        Like(input.WorkOrd, "w.WorkOrd", "WorkOrd");
        Like(input.MaterialCode, "w.ItemNum", "Mat");
        Like(input.LotSerial, "w.LotSerial", "Lot");
        if (!string.IsNullOrWhiteSpace(input.Status)) { where.Add("LOWER(w.Status)=@st"); pars.Add(new SugarParameter("@st", input.Status.Trim().ToLowerInvariant())); }
        var whereSql = string.Join(" AND ", where);
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM WorkOrdMaster w WHERE {whereSql}", pars);
        var list = await _db.Ado.SqlQueryAsync<FqcWorkOrderRow>(
            $@"SELECT w.RecID AS RecId, w.Batch AS Batch, w.WorkOrd AS WorkOrd, w.ItemNum AS MaterialCode,
                     i.Descr AS ProductName, i.Descr1 AS ProductModel, w.LotSerial AS LotSerial,
                     w.QtyOrded AS QtyOrded, w.QtyCompleted AS QtyCompleted, LOWER(w.Status) AS Status
              FROM WorkOrdMaster w
              LEFT JOIN ItemMaster i ON i.ItemNum=w.ItemNum AND i.tenant_id=w.tenant_id
              WHERE {whereSql}
              ORDER BY w.RecID DESC
              LIMIT {pageSize} OFFSET {(page - 1) * pageSize}", pars);
        return new { total, page, pageSize, list };
    }

    // ============================ 报检列表/详情 ============================

    /// <summary>成品报检列表（读 qms_fqcbj 本源，租户隔离）。</summary>
    [DisplayName("成品报检列表")]
    [HttpGet("page")]
    public async Task<object> Page([FromQuery] FqcApplyQueryInput input)
    {
        var tenantId = ResolveTenantOrThrow();
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : (input.PageSize > 200 ? 200 : input.PageSize);
        var where = new List<string> { "tenant_id=@t" };
        var pars = new List<SugarParameter> { new("@t", tenantId) };
        void Like(string? v, string col, string p) { if (!string.IsNullOrWhiteSpace(v)) { where.Add($"{col} LIKE @{p}"); pars.Add(new SugarParameter($"@{p}", $"%{v.Trim()}%")); } }
        Like(input.BillNo, "FBILLNO", "Bill");
        Like(input.MaterialCode, "wlbm", "Mat");
        Like(input.ProductionBatchNo, "scph", "Scph");
        Like(input.ProductionOrderNo, "sczld", "Sczld");
        var whereSql = string.Join(" AND ", where);
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM qms_fqcbj WHERE {whereSql}", pars);
        var list = await _db.Ado.SqlQueryAsync<FqcApplyRow>(
            $@"SELECT q.id AS Id, q.FBILLNO AS BillNo, q.wlbm AS MaterialCode, q.cpmc AS ProductName, q.cpxh AS ProductModel,
                     q.scph AS ProductionBatchNo, q.sczld AS ProductionOrderNo, q.sczldsl AS OrderQty, q.sl AS Qty,
                     q.yxj AS Priority, q.FAPPLYUSER AS Applicant, q.FAPPLYTIME AS ApplyTime, q.FINSPECTSTATUS AS InspectStatus, q.FCOMMENT AS Remark,
                     ib.id AS InspBillId, ib.FBILLNO AS InspBillNo
              FROM qms_fqcbj q
              LEFT JOIN qms_qcpp_inspbill ib ON ib.tenant_id=q.tenant_id AND ib.lydjbh=q.FBILLNO AND ib.hid=q.id
              WHERE {whereSql.Replace("tenant_id", "q.tenant_id").Replace("FBILLNO", "q.FBILLNO").Replace("wlbm", "q.wlbm").Replace("scph", "q.scph").Replace("sczld", "q.sczld")}
              ORDER BY q.FAPPLYTIME DESC, q.id DESC LIMIT {pageSize} OFFSET {(page - 1) * pageSize}", pars);
        return new { total, page, pageSize, list };
    }

    /// <summary>成品报检详情（读 qms_fqcbj 单行）。</summary>
    [DisplayName("成品报检详情")]
    [HttpGet("detail")]
    public async Task<FqcApplyRow?> Detail([FromQuery] long id)
    {
        if (id <= 0) return null;
        var tenantId = ResolveTenantOrThrow();
        return (await _db.Ado.SqlQueryAsync<FqcApplyRow>(
            @"SELECT id AS Id, FBILLNO AS BillNo, wlbm AS MaterialCode, cpmc AS ProductName, cpxh AS ProductModel,
                     scph AS ProductionBatchNo, sczld AS ProductionOrderNo, sczldsl AS OrderQty, sl AS Qty,
                     yxj AS Priority, jfbh AS FireNo, FAPPLYUSER AS Applicant, FAPPLYTIME AS ApplyTime,
                     FINSPECTSTATUS AS InspectStatus, FCOMMENT AS Remark
              FROM qms_fqcbj WHERE id=@id AND tenant_id=@t LIMIT 1",
            new SugarParameter("@id", id), new SugarParameter("@t", tenantId))).FirstOrDefault();
    }

    // ============================ 创建报检 ============================

    /// <summary>
    /// 从完工工单创建成品报检。回填字段后端按 WorkOrd+ItemMaster 真实数据重读(防前端篡改)，人工字段取前端。
    /// </summary>
    [DisplayName("创建成品报检")]
    [HttpPost("create")]
    public async Task<object> Create([FromBody] FqcApplyCreateInput input)
    {
        var tenantId = ResolveTenantOrThrow();
        if (input == null || input.WorkOrdRecId <= 0) throw Oops.Oh("请选择生产指令单（工单）");

        // 后端按用户选的 WorkOrd 重读真实数据（不信前端回填值）
        var wo = await _db.Ado.SqlQuerySingleAsync<WorkOrdGenRow>(
            @"SELECT w.RecID AS RecId, w.Batch AS Batch, w.ItemNum AS ItemNum, w.LotSerial AS LotSerial,
                     w.QtyOrded AS QtyOrded, i.Descr AS ProductName, i.Descr1 AS ProductModel
              FROM WorkOrdMaster w
              LEFT JOIN ItemMaster i ON i.ItemNum=w.ItemNum AND i.tenant_id=w.tenant_id
              WHERE w.RecID=@rid AND w.tenant_id=@t LIMIT 1",
            new SugarParameter("@rid", input.WorkOrdRecId), new SugarParameter("@t", tenantId));
        if (wo == null) throw Oops.Oh("工单不存在或不属于当前租户");
        if (string.IsNullOrWhiteSpace(wo.ItemNum)) throw Oops.Oh("工单缺物料编码，无法报检");

        var now = DateTime.Now;
        var applyUser = _userManager.RealName ?? _userManager.Account ?? _userManager.UserId.ToString();

        // 取号 + 并发唯一冲突有限重试（配 1A uk_qms_fqcbj_tenant_billno）
        Exception? lastEx = null;
        for (var attempt = 0; attempt < 5; attempt++)
        {
            var billNo = await AllocateApplyNoAsync(tenantId);
            var billId = YitIdHelper.NextId();
            try
            {
                await _db.Ado.ExecuteCommandAsync(
                    @"INSERT INTO qms_fqcbj
                        (id, tenant_id, FBILLNO, FBILLTYPE, FINSPECTSTATUS, FAPPLYUSER, FAPPLYTIME, FORGID, FCREATETIME,
                         wlbm, cpmc, cpxh, scph, sczld, sczldsl, sl, yxj, jfbh, FCOMMENT)
                      VALUES
                        (@id, @t, @no, @btype, '未检验', @user, @now, @org, @now,
                         @wlbm, @cpmc, @cpxh, @scph, @sczld, @sczldsl, @sl, @yxj, @jfbh, @comment)",
                    new SugarParameter("@id", billId),
                    new SugarParameter("@t", tenantId),
                    new SugarParameter("@no", billNo),
                    new SugarParameter("@btype", FqcBillType),
                    new SugarParameter("@user", applyUser),
                    new SugarParameter("@now", now),
                    new SugarParameter("@org", (object?)_userManager.OrgId ?? DBNull.Value),
                    new SugarParameter("@wlbm", wo.ItemNum),
                    new SugarParameter("@cpmc", (object?)wo.ProductName ?? DBNull.Value),
                    new SugarParameter("@cpxh", (object?)wo.ProductModel ?? DBNull.Value),
                    new SugarParameter("@scph", (object?)wo.LotSerial ?? DBNull.Value),
                    new SugarParameter("@sczld", (object?)wo.Batch ?? DBNull.Value),
                    new SugarParameter("@sczldsl", (object?)wo.QtyOrded ?? DBNull.Value),
                    new SugarParameter("@sl", (object?)input.Qty ?? DBNull.Value),
                    new SugarParameter("@yxj", (object?)NullIfEmpty(input.Priority) ?? DBNull.Value),
                    new SugarParameter("@jfbh", (object?)NullIfEmpty(input.FireNo) ?? DBNull.Value),
                    new SugarParameter("@comment", (object?)NullIfEmpty(input.Remark) ?? DBNull.Value));

                await TriggerRefreshSafeAsync();
                return new { ok = true, id = billId, billNo };
            }
            catch (Exception ex) when (IsDupKey(ex))
            {
                lastEx = ex; // 号撞了，重取重试
            }
        }
        throw Oops.Oh("报检单号并发冲突，请重试").WithData(lastEx?.Message);
    }

    // ============================ 生成检验单（Phase 1D） ============================

    /// <summary>
    /// 报检 → 生成检验单头 + 检规明细快照（同事务）。应用级幂等(lydjbh+hid) + DB UNIQUE(uk_qms_qcpp_inspbill_source) 双保险。
    /// 检规精确走 Phase 1A 桥表(禁 LIKE)；lrlx/上下限固化进 inspbillst；NonNumericalTypeOK/NG 是检验结果数量不从检规带值。
    /// 本轮不做检验录入/判定/提交(Phase 1E)。
    /// </summary>
    [DisplayName("生成检验单")]
    [HttpPost("generate-inspection")]
    public async Task<object> GenerateInspection([FromBody] FqcGenerateInput input)
    {
        var tenantId = ResolveTenantOrThrow();
        if (input == null || input.ApplyId <= 0) throw Oops.Oh("报检 id 非法");

        // 1) 报检存在 + 归属 + 关键字段
        var apply = await _db.Ado.SqlQuerySingleAsync<ApplyGenRow>(
            @"SELECT id AS Id, FBILLNO AS BillNo, wlbm AS Wlbm, cpmc AS Cpmc, cpxh AS Cpxh, scph AS Scph, sczld AS Sczld, sczldsl AS Sczldsl
              FROM qms_fqcbj WHERE id=@id AND tenant_id=@t LIMIT 1",
            new SugarParameter("@id", input.ApplyId), new SugarParameter("@t", tenantId));
        if (apply == null) throw Oops.Oh("报检不存在或不属于当前租户");
        if (string.IsNullOrWhiteSpace(apply.BillNo)) throw Oops.Oh("报检单号缺失");
        if (string.IsNullOrWhiteSpace(apply.Wlbm)) throw Oops.Oh("报检缺物料编码，无法生成检验单");

        // 2) 应用级幂等：一个报检(lydjbh=报检号, hid=报检id)只一张检验单
        var existing = await QueryExistingInspAsync(tenantId, apply.BillNo!, apply.Id);
        if (existing != null) return new { created = false, id = existing.Id, billNo = existing.BillNo };

        // 3) 精确解析检规（走桥表，禁 LIKE）
        var candidates = await _specMap.ResolveInternalAsync(tenantId, apply.Wlbm!);
        if (candidates.Count == 0) throw Oops.Oh("当前物料未配置成品检规映射，请先维护检规物料映射");
        var chosen = ChooseSpec(candidates, input.SpecId);

        // 4) 检规明细
        var details = await _db.Ado.SqlQueryAsync<SpecDetailRow>(
            @"SELECT id AS Id, xh AS Xh, jyxm AS Jyxm, UpperToleranceLimit AS UpperLimit, LowerToleranceLimit AS LowerLimit,
                     lrlx AS Lrlx, jsyq AS Jsyq, jyyqsbgj AS Jyyqsbgj, jyff AS Jyff, jytk AS Jytk, jsbz AS Jsbz, cyfa AS Cyfa, jl AS Jl, SizeSpecifications AS SizeSpecifications
              FROM qms_ccjygfzb WHERE glid=@g AND tenant_id=@t ORDER BY xh, id",
            new SugarParameter("@g", chosen.Jygfid), new SugarParameter("@t", tenantId));
        if (details.Count == 0) throw Oops.Oh("当前检规无有效检验项目，不能生成检验单");
        foreach (var d in details)
        {
            if (string.IsNullOrWhiteSpace(d.Jyxm)) throw Oops.Oh("检规存在空检验项目，无法生成检验单");
            if (d.Lrlx != null && d.Lrlx != "NUMERIC" && d.Lrlx != "NON_NUMERIC") throw Oops.Oh($"检规项目录入类型非法：{d.Jyxm}");
        }

        var now = DateTime.Now;
        for (var attempt = 0; attempt < 5; attempt++)
        {
            var billNo = await AllocateInspBillNoAsync(tenantId);
            var headId = YitIdHelper.NextId();
            var stRows = details.Select(d => new Dictionary<string, object>
            {
                ["id"] = YitIdHelper.NextId(),
                ["tenant_id"] = tenantId,
                ["glid"] = headId,
                ["xh"] = (object?)ParseXh(d.Xh) ?? DBNull.Value,
                ["jyxm"] = (object?)d.Jyxm ?? DBNull.Value,
                ["sx"] = (object?)d.UpperLimit ?? DBNull.Value,   // 数值上限快照
                ["xx"] = (object?)d.LowerLimit ?? DBNull.Value,   // 数值下限快照
                ["lrlx"] = (object?)d.Lrlx ?? DBNull.Value,       // 录入类型快照(NUMERIC/NON_NUMERIC)
                ["jsyq"] = (object?)d.Jsyq ?? DBNull.Value,
                ["jyyqsbgj"] = (object?)d.Jyyqsbgj ?? DBNull.Value,
                ["jyff"] = (object?)d.Jyff ?? DBNull.Value,
                ["jytk"] = (object?)d.Jytk ?? DBNull.Value,
                ["jsbz"] = (object?)d.Jsbz ?? DBNull.Value,
                ["cyfa"] = (object?)d.Cyfa ?? DBNull.Value,
                ["jl"] = (object?)d.Jl ?? DBNull.Value,
                ["SizeSpecifications"] = (object?)d.SizeSpecifications ?? DBNull.Value,
                // NonNumericalTypeOK/NG(检验结果数量) 与 j1..j50/pd 留空，等 Phase 1E 检验员录入；绝不从检规带值
            }).ToList();

            try
            {
                var tran = await _db.Ado.UseTranAsync(async () =>
                {
                    await _db.Ado.ExecuteCommandAsync(
                        @"INSERT INTO qms_qcpp_inspbill
                            (id, tenant_id, FBILLNO, lydjbh, hid, scph, sczld, sczldsl, wlmc, ggxh,
                             jgbh, jgbb, jygfid, sxrq, FBILLSTATUS, FCREATETIME)
                          VALUES
                            (@id, @t, @no, @ly, @hid, @scph, @sczld, @sczldsl, @wlmc, @ggxh,
                             @jgbh, @jgbb, @jygfid, @sxrq, '未检验', @now)",
                        new SugarParameter("@id", headId),
                        new SugarParameter("@t", tenantId),
                        new SugarParameter("@no", billNo),
                        new SugarParameter("@ly", apply.BillNo),
                        new SugarParameter("@hid", apply.Id),
                        new SugarParameter("@scph", (object?)apply.Scph ?? DBNull.Value),
                        new SugarParameter("@sczld", (object?)apply.Sczld ?? DBNull.Value),
                        new SugarParameter("@sczldsl", (object?)apply.Sczldsl ?? DBNull.Value),
                        new SugarParameter("@wlmc", (object?)apply.Cpmc ?? DBNull.Value),
                        new SugarParameter("@ggxh", (object?)apply.Cpxh ?? DBNull.Value),
                        new SugarParameter("@jgbh", (object?)chosen.Wjbh ?? DBNull.Value),
                        new SugarParameter("@jgbb", (object?)chosen.Bb ?? DBNull.Value),
                        new SugarParameter("@jygfid", chosen.Jygfid.ToString()),
                        new SugarParameter("@sxrq", (object?)chosen.NormalizedEffectiveDate ?? DBNull.Value),
                        new SugarParameter("@now", now));

                    await _db.Insertable(stRows).AS("qms_qcpp_inspbillst").ExecuteCommandAsync();
                });
                if (!tran.IsSuccess) throw tran.ErrorException ?? Oops.Oh("生成检验单失败");

                await TriggerRefreshSafeAsync();
                return new { created = true, id = headId, billNo };
            }
            catch (Exception ex) when (IsDup(ex, "uk_qms_qcpp_inspbill_source"))
            {
                // 并发：另一请求已生成 → 回查已有，返回 created=false
                var e2 = await QueryExistingInspAsync(tenantId, apply.BillNo!, apply.Id);
                if (e2 != null) return new { created = false, id = e2.Id, billNo = e2.BillNo };
                throw;
            }
            catch (Exception ex) when (IsDup(ex, "uk_qms_qcpp_inspbill_tenant_billno"))
            {
                // 号撞，重取重试
                _ = ex;
            }
        }
        throw Oops.Oh("检验单号并发冲突，请重试");
    }

    private async Task<ExistingInspRow?> QueryExistingInspAsync(long tenantId, string applyBillNo, long applyId)
    {
        return await _db.Ado.SqlQuerySingleAsync<ExistingInspRow>(
            "SELECT id AS Id, FBILLNO AS BillNo FROM qms_qcpp_inspbill WHERE tenant_id=@t AND lydjbh=@ly AND hid=@hid LIMIT 1",
            new SugarParameter("@t", tenantId), new SugarParameter("@ly", applyBillNo), new SugarParameter("@hid", applyId));
    }

    /// <summary>选检规：人工指定优先(须属该物料候选)；否则唯一 jygfid 直取；多版本取生效日期严格最新；同优先级歧义则拒。</summary>
    private static FqcSpecCandidate ChooseSpec(List<FqcSpecCandidate> candidates, long? specId)
    {
        if (specId.HasValue && specId.Value > 0)
            return candidates.FirstOrDefault(c => c.Jygfid == specId.Value) ?? throw Oops.Oh("所选检规不属于该物料的有效映射");
        var distinct = candidates.Select(c => c.Jygfid).Distinct().ToList();
        if (distinct.Count == 1) return candidates[0];
        // candidates 已按 normalized_effective_date DESC 排序：取严格最新，否则歧义拒绝(禁 First 偷取)
        var top = candidates[0];
        var second = candidates.FirstOrDefault(c => c.Jygfid != top.Jygfid);
        if (top.NormalizedEffectiveDate.HasValue &&
            (second?.NormalizedEffectiveDate == null || top.NormalizedEffectiveDate.Value > second.NormalizedEffectiveDate.Value))
            return top;
        throw Oops.Oh("当前物料存在多个有效成品检规，请先完成检规映射确认（人工绑定唯一检规）");
    }

    private static int? ParseXh(string? xh) => int.TryParse((xh ?? "").Trim(), out var n) ? n : null;

    /// <summary>检验单号 FQC+yyyyMMdd+4 位（配 1A uk_qms_qcpp_inspbill_tenant_billno + 上层重试）。</summary>
    private async Task<string> AllocateInspBillNoAsync(long tenantId)
    {
        var prefix = $"FQC{DateTime.Now:yyyyMMdd}";
        var max = await _db.Ado.SqlQuerySingleAsync<string>(
            "SELECT FBILLNO FROM qms_qcpp_inspbill WHERE tenant_id=@t AND FBILLNO LIKE @p ORDER BY FBILLNO DESC LIMIT 1",
            new SugarParameter("@t", tenantId), new SugarParameter("@p", prefix + "%"));
        if (string.IsNullOrWhiteSpace(max) || max.Length < 4) return $"{prefix}0001";
        return int.TryParse(max[^4..], out var n) && n < 9999 ? $"{prefix}{(n + 1):0000}" : $"{prefix}0001";
    }

    private static bool IsDup(Exception ex, string indexName)
    {
        var m = ex.Message + " " + (ex.InnerException?.Message ?? "");
        return (m.Contains("Duplicate", StringComparison.OrdinalIgnoreCase) || m.Contains("1062")) && m.Contains(indexName, StringComparison.OrdinalIgnoreCase);
    }

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

    /// <summary>报检号 FQCBJ+yyyyMMdd+4 位（本库租户内，配 1A UNIQUE + 上层重试）。</summary>
    private async Task<string> AllocateApplyNoAsync(long tenantId)
    {
        var prefix = $"FQCBJ{DateTime.Now:yyyyMMdd}";
        var max = await _db.Ado.SqlQuerySingleAsync<string>(
            "SELECT FBILLNO FROM qms_fqcbj WHERE tenant_id=@t AND FBILLNO LIKE @p ORDER BY FBILLNO DESC LIMIT 1",
            new SugarParameter("@t", tenantId), new SugarParameter("@p", prefix + "%"));
        if (string.IsNullOrWhiteSpace(max) || max.Length < 4) return $"{prefix}0001";
        return int.TryParse(max[^4..], out var n) && n < 9999 ? $"{prefix}{(n + 1):0000}" : $"{prefix}0001";
    }

    /// <summary>写成功后触发 FQC refresh（解耦，失败只记录不影响报检成立）。</summary>
    private async Task TriggerRefreshSafeAsync()
    {
        try { await _fqcMdpSyncService.RunFullAsync(CancellationToken.None, "APPLY_CREATE"); }
        catch (Exception ex) { _logger.LogWarning(ex, "[FqcApply] 报检已保存，但 FQC refresh 失败，可稍后手动刷新"); }
    }

    private static bool IsDupKey(Exception ex)
    {
        var m = ex.Message + " " + (ex.InnerException?.Message ?? "");
        return m.Contains("Duplicate", StringComparison.OrdinalIgnoreCase) || m.Contains("1062") || m.Contains("uk_qms_fqcbj");
    }

    private static string? NullIfEmpty(string? s) => string.IsNullOrWhiteSpace(s) ? null : s.Trim();
}

public class FqcWorkOrderQueryInput
{
    public string? Batch { get; set; }
    public string? WorkOrd { get; set; }
    public string? MaterialCode { get; set; }
    public string? LotSerial { get; set; }
    public string? Status { get; set; }
    public int Page { get; set; } = 1;
    public int PageSize { get; set; } = 10;
}

public class FqcWorkOrderRow
{
    public long RecId { get; set; }
    public string? Batch { get; set; }
    public string? WorkOrd { get; set; }
    public string? MaterialCode { get; set; }
    public string? ProductName { get; set; }
    public string? ProductModel { get; set; }
    public string? LotSerial { get; set; }
    public decimal? QtyOrded { get; set; }
    public decimal? QtyCompleted { get; set; }
    public string? Status { get; set; }
}

public class FqcApplyQueryInput
{
    public string? BillNo { get; set; }
    public string? MaterialCode { get; set; }
    public string? ProductionBatchNo { get; set; }
    public string? ProductionOrderNo { get; set; }
    public int Page { get; set; } = 1;
    public int PageSize { get; set; } = 10;
}

public class FqcApplyRow
{
    public long Id { get; set; }
    public string? BillNo { get; set; }
    public string? MaterialCode { get; set; }
    public string? ProductName { get; set; }
    public string? ProductModel { get; set; }
    public string? ProductionBatchNo { get; set; }
    public string? ProductionOrderNo { get; set; }
    public decimal? OrderQty { get; set; }
    public decimal? Qty { get; set; }
    public string? Priority { get; set; }
    public string? FireNo { get; set; }
    public string? Applicant { get; set; }
    public DateTime? ApplyTime { get; set; }
    public string? InspectStatus { get; set; }
    public string? Remark { get; set; }
    /// <summary>已生成的检验单 id/号（未生成为 null）</summary>
    public long? InspBillId { get; set; }
    public string? InspBillNo { get; set; }
}

public class FqcApplyCreateInput
{
    public long WorkOrdRecId { get; set; }
    public decimal? Qty { get; set; }
    public string? Priority { get; set; }
    public string? FireNo { get; set; }
    public string? Remark { get; set; }
}

internal sealed class WorkOrdGenRow
{
    public long RecId { get; set; }
    public string? Batch { get; set; }
    public string? ItemNum { get; set; }
    public string? LotSerial { get; set; }
    public decimal? QtyOrded { get; set; }
    public string? ProductName { get; set; }
    public string? ProductModel { get; set; }
    public long? ForgId { get; set; }
}

public class FqcGenerateInput
{
    /// <summary>报检 id（qms_fqcbj.id）</summary>
    public long ApplyId { get; set; }
    /// <summary>可选：人工指定检规 id（须属该物料桥表候选，后端二次校验）</summary>
    public long? SpecId { get; set; }
}

internal sealed class ApplyGenRow
{
    public long Id { get; set; }
    public string? BillNo { get; set; }
    public string? Wlbm { get; set; }
    public string? Cpmc { get; set; }
    public string? Cpxh { get; set; }
    public string? Scph { get; set; }
    public string? Sczld { get; set; }
    public decimal? Sczldsl { get; set; }
}

internal sealed class ExistingInspRow
{
    public long Id { get; set; }
    public string? BillNo { get; set; }
}

internal sealed class SpecDetailRow
{
    public long Id { get; set; }
    public string? Xh { get; set; }
    public string? Jyxm { get; set; }
    public string? UpperLimit { get; set; }
    public string? LowerLimit { get; set; }
    public string? Lrlx { get; set; }
    public string? Jsyq { get; set; }
    public string? Jyyqsbgj { get; set; }
    public string? Jyff { get; set; }
    public string? Jytk { get; set; }
    public string? Jsbz { get; set; }
    public string? Cyfa { get; set; }
    public string? Jl { get; set; }
    public string? SizeSpecifications { get; set; }
}