using Yitter.IdGenerator;
using Admin.NET.Plugin.AiDOP.Entity.S0.Sales;

namespace Admin.NET.Plugin.AiDOP.Order;

/// <summary>
/// 产品设计服务（常规/非标；合同维度跟踪设计负责人、图号、BOM、工艺、图纸计划/实际时间）
/// 路由前缀：/api/Order/productdesign/...
///
/// 【租户安全边界】需认证访问（已移除类级 <c>[AllowAnonymous]</c>）；租户一律经
/// <c>AidopTenantScope.ResolveOrThrow</c> 从认证后 JWT 解析，无有效租户即拒绝，
/// 不读前端 tenantId、无默认回退（原实现：类级匿名 + 直接取 <c>_userManager.TenantId</c>，
/// 匿名时 TenantId=0 使租户过滤失效/查不到数据）。
/// </summary>
[ApiDescriptionSettings(Order = 255, Description = "产品设计")]
[Route("api/Order")]
[NonUnify]
public class ProductDesignService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly SqlSugarRepository<ProductDesign> _designRep;
    private readonly SqlSugarRepository<ProductDesignBom> _bomRep;
    private readonly SqlSugarRepository<ProductDesignRouting> _routingRep;
    private readonly UserManager _userManager;

    public ProductDesignService(
        ISqlSugarClient db,
        SqlSugarRepository<ProductDesign> designRep,
        SqlSugarRepository<ProductDesignBom> bomRep,
        SqlSugarRepository<ProductDesignRouting> routingRep,
        UserManager userManager)
    {
        _db = db;
        _designRep = designRep;
        _bomRep = bomRep;
        _routingRep = routingRep;
        _userManager = userManager;
    }

    [DisplayName("获取产品设计列表")]
    [HttpGet("productdesign/list")]
    public async Task<object> GetProductDesignList([FromQuery] ProductDesignListInput input)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var q = _db.Queryable<ProductDesign>()
            .Where(u => u.TenantId == tenantId)
            .WhereIF(!string.IsNullOrWhiteSpace(input.BillNo), u => u.BillNo.Contains(input.BillNo!.Trim()))
            .WhereIF(!string.IsNullOrWhiteSpace(input.ContractNo), u => u.ContractNo != null && u.ContractNo.Contains(input.ContractNo!.Trim()))
            .WhereIF(input.ProductKind is 1 or 2, u => u.ProductKind == input.ProductKind!.Value)
            .WhereIF(!string.IsNullOrWhiteSpace(input.DesignLeadName), u => u.DesignLeadName != null && u.DesignLeadName.Contains(input.DesignLeadName!.Trim()));

        var paged = await q.OrderByDescending(u => u.Id).ToPagedListAsync(input.Page, input.PageSize);

        var list = paged.Items.Select(u => new
        {
            id = u.Id,
            billNo = u.BillNo,
            contractNo = u.ContractNo,
            productKind = u.ProductKind,
            designLeadAccount = u.DesignLeadAccount,
            designLeadName = u.DesignLeadName,
            drawingNo = u.DrawingNo,
            drawingPlanStart = u.DrawingPlanStart?.ToString("yyyy-MM-dd HH:mm"),
            drawingPlanEnd = u.DrawingPlanEnd?.ToString("yyyy-MM-dd HH:mm"),
            drawingActualStart = u.DrawingActualStart?.ToString("yyyy-MM-dd HH:mm"),
            drawingActualEnd = u.DrawingActualEnd?.ToString("yyyy-MM-dd HH:mm"),
            drawingDesignCycle = u.DrawingDesignCycle,
            applicant = u.Applicant,
            applyDate = u.ApplyDate?.ToString("yyyy-MM-dd"),
            productModel = u.ProductModel,
            itemNum = u.ItemNum,
            productName = u.ProductName,
            createTime = u.CreateTime?.ToString("yyyy-MM-dd HH:mm:ss"),
            updateTime = u.UpdateTime?.ToString("yyyy-MM-dd HH:mm:ss"),
        }).ToList();

        return new { total = paged.Total, page = input.Page, pageSize = input.PageSize, list };
    }

    [DisplayName("获取产品设计详情")]
    [HttpGet("productdesign/{id:long}")]
    public async Task<object> GetProductDesignDetail(long id)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var m = await _designRep.GetFirstAsync(u => u.Id == id && u.TenantId == tenantId)
                ?? throw Oops.Oh("产品设计记录不存在");

        var boms = await _bomRep.GetListAsync(u => u.ProductDesignId == id && u.TenantId == tenantId);
        var routings = await _routingRep.GetListAsync(u => u.ProductDesignId == id && u.TenantId == tenantId);

        return new
        {
            id = m.Id,
            billNo = m.BillNo,
            contractNo = m.ContractNo,
            productKind = m.ProductKind,
            designLeadAccount = m.DesignLeadAccount,
            designLeadName = m.DesignLeadName,
            drawingNo = m.DrawingNo,
            drawingPlanStart = m.DrawingPlanStart?.ToString("yyyy-MM-dd HH:mm:ss"),
            drawingPlanEnd = m.DrawingPlanEnd?.ToString("yyyy-MM-dd HH:mm:ss"),
            drawingActualStart = m.DrawingActualStart?.ToString("yyyy-MM-dd HH:mm:ss"),
            drawingActualEnd = m.DrawingActualEnd?.ToString("yyyy-MM-dd HH:mm:ss"),
            drawingDesignCycle = m.DrawingDesignCycle,
            applicant = m.Applicant,
            applyDate = m.ApplyDate?.ToString("yyyy-MM-dd"),
            productModel = m.ProductModel,
            itemNum = m.ItemNum,
            productName = m.ProductName,
            language = m.Language,
            lineRemark = m.LineRemark,
            createUser = m.CreateUser,
            createTime = m.CreateTime?.ToString("yyyy-MM-dd HH:mm:ss"),
            updateUser = m.UpdateUser,
            updateTime = m.UpdateTime?.ToString("yyyy-MM-dd HH:mm:ss"),
            boms = boms.OrderBy(x => x.Seq).ThenBy(x => x.Id).Select(x => new
            {
                id = x.Id.ToString(),
                parentBomId = x.ParentBomId.HasValue ? x.ParentBomId.Value.ToString() : null,
                seq = x.Seq,
                itemNum = x.ItemNum,
                itemName = x.ItemName,
                processCode = x.ProcessCode,
                fixedLossQty = x.FixedLossQty,
                batchNo = x.BatchNo,
            }),
            routings = routings.OrderBy(x => x.Seq).ThenBy(x => x.Id).Select(x => new
            {
                id = x.Id,
                seq = x.Seq,
                opName = x.OpName,
                opCode = x.OpCode,
                isKeyProcess = x.IsKeyProcess,
                productionLine = x.ProductionLine,
                routeCode = x.RouteCode,
            }),
        };
    }

    [DisplayName("保存产品设计")]
    [ApiDescriptionSettings(Name = "SaveProductDesign"), HttpPost("productdesign/save")]
    public async Task<object> SaveProductDesign([FromBody] ProductDesignSaveInput input)
    {
        if (input.ProductKind is not (1 or 2))
            throw Oops.Oh("产品类型无效，请选择常规产品或非标产品");

        // 产品编码必填
        if (string.IsNullOrWhiteSpace(input.ItemNum))
            throw Oops.Oh("产品编码不能为空");

        // BOM 子表至少一条
        if (input.Boms == null || input.Boms.Count == 0)
            throw Oops.Oh("制造 BOM 至少需要一条数据");

        // 工艺子表至少一条
        if (input.Routings == null || input.Routings.Count == 0)
            throw Oops.Oh("工艺路线至少需要一条数据");

        // BOM 物料编码必填
        if (input.Boms.Any(b => string.IsNullOrWhiteSpace(b.ItemNum)))
            throw Oops.Oh("制造 BOM 中物料编码不能为空");

        // 工艺工序代码必填
        if (input.Routings.Any(r => string.IsNullOrWhiteSpace(r.OpCode)))
            throw Oops.Oh("工艺路线中工序代码不能为空");

        var now = DateTime.Now;
        var user = _userManager.Account ?? "system";
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);

        await _db.Ado.BeginTranAsync();
        try
        {
            long designId;
            if (input.Id is null or 0)
            {
                var month = now.ToString("yyyyMM");
                var maxSeq = await _db.Ado.GetIntAsync(
                    $"SELECT IFNULL(MAX(CAST(SUBSTRING(BillNo, 9) AS UNSIGNED)), 0) FROM ado_product_design WHERE BillNo LIKE 'PD{month}%' AND tenant_id = @TenantId",
                    new SugarParameter("@TenantId", tenantId));
                var billNo = $"PD{month}{(maxSeq + 1):D4}";

                designId = YitIdHelper.NextId();
                var entity = MapMaster(new ProductDesign { Id = designId, BillNo = billNo, TenantId = tenantId }, input, user, now, isNew: true);
                await _designRep.InsertAsync(entity);

                await SaveBomsAsync(designId, tenantId, input.Boms ?? new List<ProductDesignBomInput>(), isNew: true);
                await SaveRoutingsAsync(designId, tenantId, input.Routings ?? new List<ProductDesignRoutingInput>(), isNew: true);

                await _db.Ado.CommitTranAsync();
                return new { id = designId, billNo, message = "新增成功" };
            }

            var existing = await _designRep.GetFirstAsync(u => u.Id == input.Id!.Value && u.TenantId == tenantId)
                           ?? throw Oops.Oh("产品设计记录不存在");

            designId = existing.Id;
            MapMaster(existing, input, user, now, isNew: false);
            await _designRep.UpdateAsync(existing);

            await SaveBomsAsync(designId, tenantId, input.Boms ?? new List<ProductDesignBomInput>(), isNew: false);
            await SaveRoutingsAsync(designId, tenantId, input.Routings ?? new List<ProductDesignRoutingInput>(), isNew: false);

            await _db.Ado.CommitTranAsync();
            return new { id = designId, billNo = existing.BillNo, message = "编辑成功" };
        }
        catch
        {
            await _db.Ado.RollbackTranAsync();
            throw;
        }
    }

    [DisplayName("删除产品设计")]
    [ApiDescriptionSettings(Name = "DeleteProductDesign"), HttpPost("productdesign/delete")]
    public async Task<object> DeleteProductDesign([FromBody] ProductDesignDeleteInput input)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var entity = await _designRep.GetFirstAsync(u => u.Id == input.Id && u.TenantId == tenantId)
                     ?? throw Oops.Oh("产品设计记录不存在");

        await _db.Ado.BeginTranAsync();
        try
        {
            await _bomRep.DeleteAsync(u => u.ProductDesignId == entity.Id && u.TenantId == tenantId);
            await _routingRep.DeleteAsync(u => u.ProductDesignId == entity.Id && u.TenantId == tenantId);
            await _designRep.DeleteAsync(u => u.Id == entity.Id);
            await _db.Ado.CommitTranAsync();
            return new { message = "删除成功" };
        }
        catch
        {
            await _db.Ado.RollbackTranAsync();
            throw;
        }
    }

    [DisplayName("根据产品编码获取BOM和工艺")]
    [HttpGet("productdesign/bom-routing")]
    public async Task<BomAndRoutingOutput> GetBomAndRouting([FromQuery] string itemNum)
    {
        if (string.IsNullOrWhiteSpace(itemNum))
            return new BomAndRoutingOutput();

        // 🔴 租户硬门：本方法的两段裸 SQL 走 _db.Ado.SqlQueryAsync，**完全绕过** SqlSugar 的
        //    QueryFilter（后者只作用于 Queryable<T>），必须自己带 tenant_id。
        //    租户经 AidopTenantScope.ResolveOrThrow 严格解析（仅认证后 JWT），无有效租户直接拒绝。
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);

        // 查询图纸设计周期
        var drawingDesignCycle = await _db.Queryable<AdoS0ItemMaster>()
            .Where(x => x.TenantId == tenantId && x.ItemNum == itemNum && x.IsActive == true)
            .Select(x => x.DrawingDesign)
            .FirstAsync();

        // 🔴 七处 tenant_id 谓词缺一不可（2026-09-08 实测，逐条见下）。
        //
        //    **只在最外层 WHERE 过滤是无效的**，有三层原因：
        //    ① 语法上不成立 —— CTE 的列清单里根本没有 tenant_id；
        //    ② 即便把 tenant_id 加进 CTE 列再只在外层过滤，仍会残留"血统不明的自家行" ——
        //       实测料号 3124C0015：外层过滤版比正确版多出 15/15/150 行，这些行自身的
        //       tenant_id 等于查询租户、外层删不掉，但它们是靠"经过外租户的边"才被走到的节点
        //       （ItemMaster 有 663 个跨租户重码，递归中间层一旦串到别人的图上就会这样回落）；
        //    ③ 最外层的 LEFT JOIN ItemMaster 本身就是独立放大器 —— 实测 452 行 CTE 经它膨胀到 1120 行。
        //
        //    ⚠️ ④⑤⑦ 三处**必须放在 ON 而不是 WHERE**：放 WHERE 会把无匹配的 NULL 行滤掉，
        //    LEFT JOIN 退化成 INNER JOIN，没有物料主数据/产线的 BOM 行会整行消失。
        var bomSql = @"
WITH RECURSIVE temp(ParentItem,ComponentItem,op,qty,StructureType,QtyConsumed) AS (
    SELECT ParentItem,ComponentItem,op,qty,StructureType,QtyConsumed
    FROM ProductStructureMaster WHERE ParentItem=@itemNum AND tenant_id=@tenantId
    UNION ALL
    SELECT c.ParentItem,c.ComponentItem,c.op,
           CAST(c.qty+(c.qty*(c.Scrap+c.QtyExchd)/100) AS DECIMAL(15,8)) AS qty,
           c.StructureType,c.QtyConsumed
    FROM ProductStructureMaster c
    INNER JOIN temp p ON c.ParentItem=p.ComponentItem
    INNER JOIN ItemMaster parentIm ON c.ParentItem=parentIm.ItemNum AND parentIm.PurMfg<>'P'
        AND parentIm.tenant_id=@tenantId
    WHERE c.tenant_id=@tenantId
)
SELECT psm.ParentItem,psm.ComponentItem AS ItemNum,im.Descr AS ItemName,
       CASE WHEN IFNULL(pso.Op,0)=0 THEN psm.Op ELSE pso.Op END AS Op,
       psm.qty AS Qty,psm.StructureType,im.EMTType AS EmtType,psm.QtyConsumed
FROM temp psm
LEFT JOIN ItemMaster im ON psm.ComponentItem=im.ItemNum
    AND im.tenant_id=@tenantId
LEFT JOIN ProductStructureOp pso ON pso.ParentItem=psm.ParentItem
    AND pso.ComponentItem=psm.ComponentItem AND pso.ProductItem=@itemNum
    AND pso.tenant_id=@tenantId";

        var routingSql = @"
SELECT r.Descr,r.Op,r.ParentOp,CAST(r.MilestoneOp AS CHAR(5)) AS MilestoneOp,p.Line,r.RouteCode
FROM RoutingOpDetail as r left join ProdLineDetail as p on r.RoutingCode=p.Part and r.Op=p.Op
    and p.tenant_id=@tenantId
WHERE r.RoutingCode=@itemNum AND r.tenant_id=@tenantId ORDER BY r.Op";

        var boms = await _db.Ado.SqlQueryAsync<BomQueryRow>(bomSql, new { itemNum, tenantId });
        var routings = await _db.Ado.SqlQueryAsync<RoutingQueryRow>(routingSql, new { itemNum, tenantId });

        return new BomAndRoutingOutput
        {
            Boms = boms,
            Routings = routings,
            DrawingDesignCycle = drawingDesignCycle
        };
    }

    private static ProductDesign MapMaster(ProductDesign entity, ProductDesignSaveInput input, string user, DateTime now, bool isNew)
    { 
        entity.ContractNo = string.IsNullOrWhiteSpace(input.ContractNo) ? null : input.ContractNo.Trim();
        entity.ProductKind = input.ProductKind;
        entity.DesignLeadAccount = string.IsNullOrWhiteSpace(input.DesignLeadAccount) ? null : input.DesignLeadAccount.Trim();
        entity.DesignLeadName = string.IsNullOrWhiteSpace(input.DesignLeadName) ? null : input.DesignLeadName.Trim();
        entity.DrawingNo = string.IsNullOrWhiteSpace(input.DrawingNo) ? null : input.DrawingNo.Trim();
        entity.DrawingPlanStart = ParseOptionalDate(input.DrawingPlanStart);
        entity.DrawingPlanEnd = ParseOptionalDate(input.DrawingPlanEnd);
        entity.DrawingActualStart = ParseOptionalDate(input.DrawingActualStart);
        entity.DrawingActualEnd = ParseOptionalDate(input.DrawingActualEnd);
        entity.DrawingDesignCycle = input.DrawingDesignCycle;
        entity.Applicant = string.IsNullOrWhiteSpace(input.Applicant) ? null : input.Applicant.Trim();
        entity.ApplyDate = ParseOptionalDate(input.ApplyDate);
        entity.ProductModel = string.IsNullOrWhiteSpace(input.ProductModel) ? null : input.ProductModel.Trim();
        entity.ItemNum = string.IsNullOrWhiteSpace(input.ItemNum) ? null : input.ItemNum.Trim();
        entity.ProductName = string.IsNullOrWhiteSpace(input.ProductName) ? null : input.ProductName.Trim();
        entity.Language = string.IsNullOrWhiteSpace(input.Language) ? null : input.Language.Trim();
        entity.Qty = null;
        entity.LineRemark = string.IsNullOrWhiteSpace(input.LineRemark) ? null : input.LineRemark.Trim();

        if (isNew)
        {
            entity.CreateUser = user;
            entity.CreateTime = now;
            entity.IsActive = 1;
        }
        else
        {
            entity.UpdateUser = user;
            entity.UpdateTime = now;
        }

        return entity;
    }

    private static DateTime? ParseOptionalDate(string? s)
    {
        if (string.IsNullOrWhiteSpace(s)) return null;
        return DateTime.TryParse(s.Trim(), out var dt) ? dt : null;
    }

    private async Task SaveBomsAsync(long designId, long tenantId, List<ProductDesignBomInput> items, bool isNew)
    {
        var dbRows = isNew ? new List<ProductDesignBom>()
            : await _bomRep.GetListAsync(u => u.ProductDesignId == designId && u.TenantId == tenantId);
        var dbById = dbRows.ToDictionary(u => u.Id);

        if (items.Any(x => !x.Id.HasValue))
            throw Oops.Oh("BOM 每行必须包含 Id（已保存行为正数，新行请使用负数临时 Id）");

        var ordered = OrderBomsForSave(items);
        var tempToReal = new Dictionary<long, long>();
        var keptIds = new HashSet<long>();
        var idx = 0;

        foreach (var d in ordered)
        {
            idx++;
            var seq = d.Seq ?? idx;
            var parentResolved = ResolveBomParentId(d.ParentBomId, tempToReal);

            if (d.Id is > 0 && dbById.TryGetValue(d.Id.Value, out var existing))
            {
                if (existing.ProductDesignId != designId) continue;
                existing.ParentBomId = parentResolved;
                existing.Seq = seq;
                existing.ItemNum = string.IsNullOrWhiteSpace(d.ItemNum) ? null : d.ItemNum.Trim();
                existing.ItemName = string.IsNullOrWhiteSpace(d.ItemName) ? null : d.ItemName.Trim();
                existing.ProcessCode = string.IsNullOrWhiteSpace(d.ProcessCode) ? null : d.ProcessCode.Trim();
                existing.Qty = d.Qty;
                existing.FixedLossQty = d.FixedLossQty;
                existing.BatchNo = string.IsNullOrWhiteSpace(d.BatchNo) ? null : d.BatchNo.Trim();
                await _bomRep.UpdateAsync(existing);
                keptIds.Add(existing.Id);
            }
            else if (d.Id is < 0)
            {
                var newId = YitIdHelper.NextId();
                tempToReal[d.Id.Value] = newId;
                var row = new ProductDesignBom
                {
                    Id = newId,
                    ProductDesignId = designId,
                    TenantId = tenantId,
                    ParentBomId = parentResolved,
                    Seq = seq,
                    ItemNum = string.IsNullOrWhiteSpace(d.ItemNum) ? null : d.ItemNum.Trim(),
                    ItemName = string.IsNullOrWhiteSpace(d.ItemName) ? null : d.ItemName.Trim(),
                    ProcessCode = string.IsNullOrWhiteSpace(d.ProcessCode) ? null : d.ProcessCode.Trim(),
                    Qty = d.Qty,
                    FixedLossQty = d.FixedLossQty,
                    BatchNo = string.IsNullOrWhiteSpace(d.BatchNo) ? null : d.BatchNo.Trim(),
                };
                await _bomRep.InsertAsync(row);
                keptIds.Add(newId);
            }
        }

        foreach (var row in dbRows.Where(u => !keptIds.Contains(u.Id)))
            await _bomRep.DeleteAsync(u => u.Id == row.Id && u.TenantId == tenantId);
    }

    /// <summary>按父子顺序排列：父（含库中已有 Id）先于子；负数临时 Id 先于引用它的子行。</summary>
    private static List<ProductDesignBomInput> OrderBomsForSave(List<ProductDesignBomInput> items)
    {
        var withId = items.Where(x => x.Id.HasValue).ToList();
        var inBatch = withId.Select(x => x.Id!.Value).ToHashSet();
        var done = new HashSet<long>();
        var result = new List<ProductDesignBomInput>();
        var remaining = withId.ToList();

        bool CanTake(ProductDesignBomInput r)
        {
            var p = r.ParentBomId;
            if (p is null or 0) return true;
            if (p < 0) return done.Contains(p.Value);
            if (inBatch.Contains(p.Value)) return done.Contains(p.Value);
            return true;
        }

        while (remaining.Count > 0)
        {
            var batch = remaining.Where(CanTake).ToList();
            if (batch.Count == 0)
                throw Oops.Oh("BOM 父子关系无效（循环或缺失父节点）");
            foreach (var b in batch)
            {
                result.Add(b);
                remaining.Remove(b);
                if (b.Id.HasValue)
                    done.Add(b.Id.Value);
            }
        }

        return result;
    }

    private static long? ResolveBomParentId(long? parentBomId, Dictionary<long, long> tempToReal)
    {
        if (parentBomId is null or 0) return null;
        if (parentBomId.Value < 0)
        {
            if (tempToReal.TryGetValue(parentBomId.Value, out var real))
                return real;
            throw Oops.Oh("BOM 父节点未找到，请检查父子顺序");
        }

        return parentBomId;
    }

    private async Task SaveRoutingsAsync(long designId, long tenantId, List<ProductDesignRoutingInput> items, bool isNew)
    {
        var dbRows = isNew ? new List<ProductDesignRouting>()
            : await _routingRep.GetListAsync(u => u.ProductDesignId == designId && u.TenantId == tenantId);
        var dbById = dbRows.ToDictionary(u => u.Id);
        var inputIds = new HashSet<long>(items.Where(d => d.Id is > 0).Select(d => d.Id!.Value));

        var idx = 0;
        foreach (var d in items)
        {
            idx++;
            var seq = d.Seq ?? idx;

            if (d.Id is > 0 && dbById.TryGetValue(d.Id.Value, out var existing))
            {
                if (existing.ProductDesignId != designId) continue;
                existing.Seq = seq;
                existing.OpName = string.IsNullOrWhiteSpace(d.OpName) ? null : d.OpName.Trim();
                existing.OpCode = string.IsNullOrWhiteSpace(d.OpCode) ? null : d.OpCode.Trim();
                existing.ParentOpCode = null;
                existing.IsKeyProcess = d.IsKeyProcess;
                existing.ProductionLine = string.IsNullOrWhiteSpace(d.ProductionLine) ? null : d.ProductionLine.Trim();
                existing.RouteCode = string.IsNullOrWhiteSpace(d.RouteCode) ? null : d.RouteCode.Trim();
                await _routingRep.UpdateAsync(existing);
            }
            else
            {
                var row = new ProductDesignRouting
                {
                    Id = YitIdHelper.NextId(),
                    ProductDesignId = designId,
                    TenantId = tenantId,
                    Seq = seq,
                    OpName = string.IsNullOrWhiteSpace(d.OpName) ? null : d.OpName.Trim(),
                    OpCode = string.IsNullOrWhiteSpace(d.OpCode) ? null : d.OpCode.Trim(),
                    ParentOpCode = null,
                    IsKeyProcess = d.IsKeyProcess,
                    ProductionLine = string.IsNullOrWhiteSpace(d.ProductionLine) ? null : d.ProductionLine.Trim(),
                    RouteCode = string.IsNullOrWhiteSpace(d.RouteCode) ? null : d.RouteCode.Trim(),
                };
                await _routingRep.InsertAsync(row);
            }
        }

        foreach (var toDelete in dbRows.Where(u => !inputIds.Contains(u.Id)))
            await _routingRep.DeleteAsync(u => u.Id == toDelete.Id && u.TenantId == tenantId);
    }

    /// <summary>获取合同下拉选项</summary>
    [DisplayName("获取合同下拉选项")]
    [HttpGet("productdesign/contract-options")]
    public async Task<List<object>> GetContractOptions([FromQuery] string? keyword)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var sql = string.IsNullOrWhiteSpace(keyword)
            ? "SELECT BillNo, CONCAT(BillNo, ' | ', IFNULL(Title,'')) AS Label FROM ado_contract_review WHERE tenant_id = @tenantId ORDER BY RecID DESC LIMIT 50"
            : "SELECT BillNo, CONCAT(BillNo, ' | ', IFNULL(Title,'')) AS Label FROM ado_contract_review WHERE tenant_id = @tenantId AND (BillNo LIKE @kw OR Title LIKE @kw) ORDER BY RecID DESC LIMIT 50";

        var rows = await _db.Ado.SqlQueryAsync<ContractOptionRow>(sql,
            new { tenantId, kw = string.IsNullOrWhiteSpace(keyword) ? null : $"%{keyword.Trim()}%" });
        return rows.Select(r => (object)new { value = r.BillNo, label = r.Label }).ToList();
    }

    /// <summary>获取用户下拉选项</summary>
    [DisplayName("获取用户下拉选项")]
    [HttpGet("productdesign/user-options")]
    public async Task<List<object>> GetUserOptions([FromQuery] string? keyword)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var sql = string.IsNullOrWhiteSpace(keyword)
            ? "SELECT Id, Account, RealName FROM SysUser WHERE TenantId = @tenantId AND Status = 1 ORDER BY OrderNo LIMIT 200"
            : "SELECT Id, Account, RealName FROM SysUser WHERE TenantId = @tenantId AND Status = 1 AND (Account LIKE @kw OR RealName LIKE @kw) ORDER BY OrderNo LIMIT 200";

        var rows = await _db.Ado.SqlQueryAsync<UserOptionRow>(sql,
            new { tenantId, kw = string.IsNullOrWhiteSpace(keyword) ? null : $"%{keyword.Trim()}%" });
        return rows.Select(r => (object)new { value = r.Account, label = $"{r.RealName} ({r.Account})" }).ToList();
    }

    /// <summary>获取产线下拉选项</summary>
    [DisplayName("获取产线下拉选项")]
    [HttpGet("productdesign/line-options")]
    public async Task<List<object>> GetLineOptions([FromQuery] string? keyword)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var sql = string.IsNullOrWhiteSpace(keyword)
            ? "SELECT Line, `Describe` FROM LineMaster WHERE tenant_id = @tenantId AND IsActive = 1 ORDER BY Line LIMIT 200"
            : "SELECT Line, `Describe` FROM LineMaster WHERE tenant_id = @tenantId AND IsActive = 1 AND (Line LIKE @kw OR `Describe` LIKE @kw) ORDER BY Line LIMIT 200";

        var rows = await _db.Ado.SqlQueryAsync<LineOptionRow>(sql,
            new { tenantId, kw = string.IsNullOrWhiteSpace(keyword) ? null : $"%{keyword.Trim()}%" });
        return rows.Select(r => (object)new { value = r.Line, label = $"{r.Line} | {r.Describe}" }).ToList();
    }

    private sealed class ContractOptionRow
    {
        public string BillNo { get; set; } = string.Empty;
        public string Label { get; set; } = string.Empty;
    }

    private sealed class UserOptionRow
    {
        public long Id { get; set; }
        public string Account { get; set; } = string.Empty;
        public string RealName { get; set; } = string.Empty;
    }

    private sealed class LineOptionRow
    {
        public string Line { get; set; } = string.Empty;
        public string Describe { get; set; } = string.Empty;
    }
}