using Admin.NET.Plugin.AiDOP.ProcurementExecution.Dto;
using Admin.NET.Plugin.AiDOP.ProcurementExecution.Entity;
using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.ProcurementExecution;

/// <summary>
/// S4 供应商发货单（列表 + 表单）
/// </summary>
[ApiDescriptionSettings(Order = 321, Description = "S4供应商发货单")]
[Route("api/ProcurementExecution")]
[AllowAnonymous]
[NonUnify]
public class SupplierShipmentService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly SqlSugarRepository<ScmShd> _masterRep;
    private readonly SqlSugarRepository<ScmShdzb> _detailRep;
    private readonly UserManager _userManager;
    private const int ShpcSerialWidth = 3; // yyMMdd + 3位流水：260508001
    private const int LabelXhStart = 10001;

    public SupplierShipmentService(
        ISqlSugarClient db,
        SqlSugarRepository<ScmShd> masterRep,
        SqlSugarRepository<ScmShdzb> detailRep,
        UserManager userManager)
    {
        _db = db;
        _masterRep = masterRep;
        _detailRep = detailRep;
        _userManager = userManager;
    }

    [DisplayName("供应商发货单列表")]
    [HttpGet("supplier-shipment/list")]
    public async Task<object> GetList([FromQuery] SupplierShipmentListInput input)
    {
        var pars = new List<SugarParameter>();
        var conditions = new List<string> { "IFNULL(m.state, 1) <> 0" };
        if (!string.IsNullOrWhiteSpace(input.JhshrqFrom))
        {
            conditions.Add("m.jhshrq >= @jhshrqFrom");
            pars.Add(new SugarParameter("@jhshrqFrom", input.JhshrqFrom.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.Gysmc))
        {
            conditions.Add("m.sh_purchase_name LIKE @gysmc");
            pars.Add(new SugarParameter("@gysmc", $"%{input.Gysmc.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Shddh))
        {
            conditions.Add("m.shddh LIKE @shddh");
            pars.Add(new SugarParameter("@shddh", $"%{input.Shddh.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Wldh))
        {
            conditions.Add("m.wldh LIKE @wldh");
            pars.Add(new SugarParameter("@wldh", $"%{input.Wldh.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.PoBill))
        {
            conditions.Add("m.po_bill LIKE @poBill");
            pars.Add(new SugarParameter("@poBill", $"%{input.PoBill.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ShMaterialCode))
        {
            conditions.Add("m.sh_material_code LIKE @shMaterialCode");
            pars.Add(new SugarParameter("@shMaterialCode", $"%{input.ShMaterialCode.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Shzt))
        {
            conditions.Add("m.shzt = @shzt");
            pars.Add(new SugarParameter("@shzt", input.Shzt.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.Shpc))
        {
            conditions.Add("m.shpc LIKE @shpc");
            pars.Add(new SugarParameter("@shpc", $"%{input.Shpc.Trim()}%"));
        }

        var where = $" WHERE {string.Join(" AND ", conditions)} ";
        var orderBy = BuildListOrderBy(input.SortField, input.SortOrder);
        var offset = (input.Page - 1) * input.PageSize;
        var wrapped = $"{BuildListSql()} {where}";

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(*) FROM ({wrapped}) t", pars);
        var list = await _db.Ado.SqlQueryAsync<SupplierShipmentListRow>(
            $"SELECT * FROM ({wrapped}) t {orderBy} LIMIT {input.PageSize} OFFSET {offset}", pars);

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

    [DisplayName("获取发货单详情")]
    [HttpGet("supplier-shipment/{id:long}")]
    public async Task<object> GetDetail(long id)
    {
        var master = await _masterRep.GetFirstAsync(x => x.Id == id) ?? throw Oops.Oh("发货单不存在");
        var details = await _detailRep.AsQueryable().Where(x => x.Glid == id.ToString()).OrderBy(x => x.Hh).ToListAsync();
        return new
        {
            id = master.Id,
            shddh = master.Shddh,
            jhshrq = master.Jhshrq,
            wlsc = master.Wlsc,
            yjdhrq = master.Yjdhrq,
            shPurchaseName = master.ShPurchaseName,
            shPurchaseNum = master.ShPurchaseNum,
            wldh = master.Wldh,
            sfpc = master.Sfpc ?? 0,
            chbg = master.Chbg,
            pcsm = master.Pcsm,
            state = master.State ?? 1,
            details = details.Select(d => new
            {
                id = d.Id,
                hh = d.Hh,
                poBill = d.PoBill,
                poBillLine = d.PoBillLine,
                orderType = d.OrderType,
                shMaterialCode = d.ShMaterialCode,
                shMaterialName = d.ShMaterialName,
                th = d.Th,
                shDeliveryQuantity = d.ShDeliveryQuantity,
                bzsl = d.Bzsl,
                bqsl = d.Bqsl,
                shMaterialDw = d.ShMaterialDw,
                scrq = d.Scrq,
                scph = d.Scph,
                remarks = d.Remarks,
                djsl = d.Djsl,
                jybb = d.Jybb,
                jhdbh = d.Jhdbh
            })
        };
    }

    [DisplayName("发货单新增草稿")]
    [HttpGet("supplier-shipment/create-draft")]
    public async Task<object> GetCreateDraft([FromQuery] string ids)
    {
        var pars = new List<SugarParameter> { new("@ids", ids) };
        var rows = await _db.Ado.SqlQueryAsync<SupplierShipmentDraftRow>(
            """
            SELECT
                CAST(IFNULL(ds.id, p.RecID) AS CHAR(50)) AS sourceId,
                ds.suppliercode AS gysdm,
                ds.supplier AS gysmc,
                p.PurOrd AS poBill,
                p.Line AS poBillLine,
                p.Potype AS orderType,
                p.ItemNum AS shMaterialCode,
                im.Descr AS shMaterialName,
                IFNULL(im.Drawing, p.Drawing) AS th,
                IFNULL(ds.SchedQty, p.QtyOrded - p.RctQty - p.ReceiptQty + p.QtyReturned) AS shDeliveryQuantity,
                p.StdPackQty AS bzsl,
                CASE
                    WHEN IFNULL(p.StdPackQty, 0) > 0 THEN CEILING(IFNULL(ds.SchedQty, p.QtyOrded - p.RctQty - p.ReceiptQty + p.QtyReturned) / p.StdPackQty)
                    ELSE 0
                END AS bqsl,
                p.UM AS shMaterialDw,
                (p.QtyOrded - p.RctQty - p.ReceiptQty + p.QtyReturned) AS djsl,
                ds.DSNum AS jhdbh,
                im.Descr1 AS shMaterialGgxh
            FROM PurOrdDetail p
            LEFT JOIN srm_polist_ds ds ON p.PurOrd = ds.ponumber AND p.Line = ds.poline
            LEFT JOIN ItemMaster im ON p.ItemNum = im.ItemNum
            WHERE FIND_IN_SET(CAST(IFNULL(ds.id, p.RecID) AS CHAR(50)), REPLACE(IFNULL(@ids, ''), ' ', '')) > 0
            ORDER BY p.PurOrd, p.Line
            """, pars);

        if (rows.Count == 0)
            throw Oops.Oh("未找到可生成的交货明细");

        return new
        {
            shddh = string.Empty,
            jhshrq = DateTime.Now.ToString("yyyy-MM-dd"),
            wlsc = string.Empty,
            yjdhrq = string.Empty,
            shPurchaseName = rows[0].Gysmc,
            shPurchaseNum = rows[0].Gysdm,
            wldh = string.Empty,
            sfpc = 0,
            chbg = string.Empty,
            pcsm = string.Empty,
            details = rows.Select((r, i) => new
            {
                id = (long?)null,
                hh = i + 1,
                poBill = r.PoBill,
                poBillLine = r.PoBillLine?.ToString(),
                orderType = r.OrderType,
                shMaterialCode = r.ShMaterialCode,
                shMaterialName = r.ShMaterialName,
                th = r.Th,
                shDeliveryQuantity = r.ShDeliveryQuantity,
                bzsl = r.Bzsl,
                bqsl = r.Bqsl ?? 0,
                shMaterialDw = r.ShMaterialDw,
                scrq = string.Empty,
                scph = string.Empty,
                remarks = string.Empty,
                djsl = r.Djsl,
                jybb = string.Empty,
                jhdbh = r.Jhdbh
            })
        };
    }

    [DisplayName("保存发货单")]
    [ApiDescriptionSettings(Name = "SaveSupplierShipment"), HttpPost("supplier-shipment/save")]
    public async Task<object> Save([FromBody] SupplierShipmentSaveInput input)
    {
        var now = DateTime.Now;
        var userId = _userManager.UserId.ToString();
        var userName = _userManager.Account ?? "system";
        var shipDate = string.IsNullOrWhiteSpace(input.Jhshrq) ? now.ToString("yyyy-MM-dd") : input.Jhshrq!.Trim();

        if (input.Id is null or 0)
        {
            var newId = YitIdHelper.NextId();
            var shddh = await AllocateShddhAsync(input.ShPurchaseNum ?? string.Empty);
            var entity = new ScmShd
            {
                Id = newId,
                Shddh = shddh,
                Jhshrq = shipDate,
                Wlsc = input.Wlsc,
                Yjdhrq = input.Yjdhrq,
                ShPurchaseName = input.ShPurchaseName,
                ShPurchaseNum = input.ShPurchaseNum,
                Wldh = input.Wldh,
                Sfpc = input.Sfpc ?? 0,
                Chbg = input.Chbg,
                Pcsm = input.Pcsm,
                State = 1,
                Shzt = "待收",
                Tjrid = userId,
                Tjrxm = userName,
                Tjrq = now.ToString("yyyy-MM-dd")
            };
            await _masterRep.InsertAsync(entity);
            await SaveDetailsAsync(newId, input.Details);
            return new { id = newId, message = "新增成功", shddh = entity.Shddh };
        }

        var master = await _masterRep.GetFirstAsync(x => x.Id == input.Id.Value) ?? throw Oops.Oh("发货单不存在");
        master.Jhshrq = shipDate;
        master.Wlsc = input.Wlsc;
        master.Yjdhrq = input.Yjdhrq;
        master.ShPurchaseName = input.ShPurchaseName;
        master.ShPurchaseNum = input.ShPurchaseNum;
        master.Wldh = input.Wldh;
        master.Sfpc = input.Sfpc ?? 0;
        master.Chbg = input.Chbg;
        master.Pcsm = input.Pcsm;
        await _masterRep.UpdateAsync(master);
        await SaveDetailsAsync(input.Id.Value, input.Details);
        return new { id = input.Id, message = "编辑成功", shddh = master.Shddh };
    }

    [DisplayName("删除发货单")]
    [ApiDescriptionSettings(Name = "DeleteSupplierShipment"), HttpPost("supplier-shipment/delete")]
    public async Task<object> Delete([FromBody] SupplierShipmentDeleteInput input)
    {
        var master = await _masterRep.GetFirstAsync(x => x.Id == input.Id) ?? throw Oops.Oh("发货单不存在");
        master.State = 0;
        await _masterRep.UpdateAsync(master);
        return new { message = "删除成功" };
    }

    [DisplayName("生成标签（预留）")]
    [HttpPost("supplier-shipment/generate-label")]
    public async Task<object> GenerateLabel([FromBody] SupplierShipmentOperationInput input)
    {
        if (input.Id <= 0)
            throw Oops.Oh("缺少发货单ID（id）");

        var userName = _userManager.Account ?? "system";

        var master = await _masterRep.GetFirstAsync(x => x.Id == input.Id) ?? throw Oops.Oh("发货单不存在");
        if (string.IsNullOrWhiteSpace(master.Shddh))
            throw Oops.Oh("发货单缺少发货单编号（shddh）");

        var shddh = master.Shddh.Trim();
        var gysmc = master.ShPurchaseName ?? string.Empty;
        var gysdm = master.ShPurchaseNum ?? string.Empty;

        // Domain（工厂编码）
        var domain = await _db.Ado.GetStringAsync(
            "SELECT Domain FROM GeneralizedCodeMaster WHERE FldName='SystemConfig' AND Val='CompanyCode' LIMIT 1");
        domain = string.IsNullOrWhiteSpace(domain) ? string.Empty : domain.Trim();

        string? lockKey = null;
        try
        {
            var tran = await _db.Ado.UseTranAsync(async () =>
            {
                // 1) 归档旧标签
                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO scm_shbqhis
                    (glid, sh_material_code, sh_material_name, sh_material_ggxh, sh_delivery_quantity,
                     sh_material_dw, remarks, bzsl, order_type, po_billno, shdh, shdhh, scrq, scph, xh,
                     gysdm, gysmc, po_billline, bbh, th, yt, ccrq, shpc, jhdbh, jhdhh)
                    SELECT
                     glid, sh_material_code, sh_material_name, sh_material_ggxh, sh_delivery_quantity,
                     sh_material_dw, remarks, bzsl, order_type, po_billno, shdh, shdhh, scrq, scph, xh,
                     gysdm, gysmc, po_billline, bbh, th, yt, ccrq, shpc, jhdbh, jhdhh
                    FROM scm_shbq WHERE shdh=@shdh;
                    """,
                    new List<SugarParameter> { new("@shdh", shddh) });

                await _db.Ado.ExecuteCommandAsync(
                    "DELETE FROM scm_shbq WHERE shdh=@shdh",
                    new List<SugarParameter> { new("@shdh", shddh) });

                // 2) 需要补 shpc 的明细（按物料+供应商批号去重）
                var needShpc = await _db.Ado.SqlQueryAsync<NeedShpcRow>(
                    """
                    SELECT DISTINCT
                        b.sh_material_code AS wlbm,
                        IFNULL(b.scph, '') AS scph
                    FROM scm_shd a
                    INNER JOIN scm_shdzb b ON a.id = b.glid
                    WHERE a.shddh = @shddh
                      AND IFNULL(b.sh_material_code, '') <> ''
                      AND IFNULL(b.shpc, '') = '';
                    """,
                    new List<SugarParameter> { new("@shddh", shddh) });

                if (needShpc.Count > 0)
                {
                    // 并发安全：按天加锁 + 取最大流水 + 批量生成
                    var prefix = DateTime.Now.ToString("yyMMdd");
                    lockKey = $"scm_shpc_seq_{prefix}";
                    await AcquireMySqlLockAsync(lockKey, 10);

                    var nextSerial = await GetNextDailySerialAsync(prefix, domain);
                    var pairs = needShpc
                        .OrderBy(x => x.Wlbm ?? string.Empty)
                        .ThenBy(x => x.Scph ?? string.Empty)
                        .Select((x, idx) => new
                        {
                            x.Wlbm,
                            x.Scph,
                            Shpc = $"{prefix}{(nextSerial + idx).ToString().PadLeft(ShpcSerialWidth, '0')}"
                        })
                        .ToList();

                    // 更新 scm_shdzb.shpc（同一发货单下：物料+供应商批号相同的行统一批次号）
                    foreach (var p in pairs)
                    {
                        await _db.Ado.ExecuteCommandAsync(
                            """
                            UPDATE scm_shdzb b
                            INNER JOIN scm_shd a ON a.id = b.glid
                            SET b.shpc = @shpc
                            WHERE a.shddh = @shddh
                              AND b.sh_material_code = @wlbm
                              AND IFNULL(b.scph, '') = IFNULL(@scph, '')
                              AND IFNULL(b.shpc, '') = '';
                            """,
                            new List<SugarParameter>
                            {
                                new("@shpc", p.Shpc),
                                new("@shddh", shddh),
                                new("@wlbm", p.Wlbm ?? string.Empty),
                                new("@scph", p.Scph ?? string.Empty),
                            });
                    }

                    // 兼容：如果线上存在 scm_shdshph，则同步写入（仓库脚本里未包含该表）
                    var shdshphExists = await TableExistsAsync("scm_shdshph");
                    if (shdshphExists)
                    {
                        foreach (var p in pairs)
                        {
                            await _db.Ado.ExecuteCommandAsync(
                                """
                                INSERT INTO scm_shdshph (shpc, wlbm, scph, shdh, xh, gysdm, create_time)
                                SELECT @shpc, @wlbm, @scph, @shdh, @xh, @gysdm, NOW()
                                FROM DUAL
                                WHERE NOT EXISTS (
                                    SELECT 1 FROM scm_shdshph
                                    WHERE shdh=@shdh AND wlbm=@wlbm AND IFNULL(scph,'')=IFNULL(@scph,'')
                                );
                                """,
                                new List<SugarParameter>
                                {
                                    new("@shpc", p.Shpc),
                                    new("@wlbm", p.Wlbm ?? string.Empty),
                                    new("@scph", p.Scph ?? string.Empty),
                                    new("@shdh", shddh),
                                    new("@xh", p.Shpc),
                                    new("@gysdm", gysdm),
                                });
                        }
                    }

                    // 推进 NbrControl.NextValue，确保续号（仅当没有历史 shpc 且使用了 NbrControl 作为起点时也能正确续）
                    // 这里直接推进到“本批次最后一个流水号”，下次取号将从 NextValue+1 开始。
                    var lastSerial = nextSerial + pairs.Count - 1;
                    await BumpNbrControlNextValueAsync(domain, lastSerial);
                }

                // 3) 补 jhdhh（同一发货单：为空的明细按 id 递增，起始 max(jhdhh) or 1000）
                var shdid = await _db.Ado.GetLongAsync(
                    "SELECT id FROM scm_shd WHERE shddh=@shddh LIMIT 1",
                    new List<SugarParameter> { new("@shddh", shddh) });

                var startNo = await _db.Ado.GetIntAsync(
                    "SELECT IFNULL(MAX(CAST(IFNULL(jhdhh,'') AS SIGNED)), 1000) FROM scm_shdzb WHERE glid=@glid",
                    new List<SugarParameter> { new("@glid", shdid.ToString()) });

                var jhdhhNeed = await _db.Ado.SqlQueryAsync<IdOnlyRow>(
                    "SELECT id FROM scm_shdzb WHERE glid=@glid AND IFNULL(jhdhh,'')='' ORDER BY id",
                    new List<SugarParameter> { new("@glid", shdid.ToString()) });

                var seq = startNo;
                foreach (var r in jhdhhNeed)
                {
                    seq++;
                    await _db.Ado.ExecuteCommandAsync(
                        "UPDATE scm_shdzb SET jhdhh=@jhdhh WHERE id=@id",
                        new List<SugarParameter>
                        {
                            new("@jhdhh", seq.ToString()),
                            new("@id", r.Id)
                        });
                }

                // 4) 生成 scm_shbq（按 bqsl 拆分；每箱 bzsl，最后一箱余数）
                var sourceRows = await _db.Ado.SqlQueryAsync<LabelSourceRow>(
                    """
                    SELECT
                        a.shddh AS shddh,
                        b.id AS detailId,
                        b.glid AS glid,
                        b.sh_material_code AS wlbm,
                        b.sh_material_name AS wlmc,
                        b.sh_material_ggxh AS ggxh,
                        b.sh_delivery_quantity AS shsl,
                        b.sh_material_dw AS dw,
                        b.remarks AS bz,
                        b.bzsl AS bzsl,
                        b.bqsl AS bqsl,
                        b.order_type AS ddlx,
                        b.po_bill AS ddh,
                        CAST(IFNULL(b.po_billline, '0') AS SIGNED) AS po_billline,
                        b.hh AS hh,
                        b.scrq AS scrq,
                        b.scph AS scph,
                        IFNULL(im.Drawing, pd.Drawing) AS th,
                        IFNULL(im.Rev, pd.Rev) AS bbh,
                        a.sh_purchase_name AS gysmc,
                        a.sh_purchase_num AS gysdm,
                        pm.Usage AS usage,
                        b.ccrq AS ccrq,
                        a.jhshrq AS jhshrq,
                        b.jhdbh AS jhdbh,
                        b.jhdhh AS jhdhh,
                        b.shpc AS shpc
                    FROM scm_shd a
                    INNER JOIN scm_shdzb b ON a.id = b.glid
                    LEFT JOIN PurOrdMaster pm ON pm.PurOrd = b.po_bill
                    LEFT JOIN PurOrdDetail pd ON pd.PurOrd = b.po_bill AND pd.Line = b.po_billline
                    LEFT JOIN ItemMaster im ON im.ItemNum = b.sh_material_code
                    WHERE a.shddh = @shddh;
                    """,
                    new List<SugarParameter> { new("@shddh", shddh) });

                foreach (var row in sourceRows)
                {
                    var totalQty = row.Shsl ?? 0m;
                    var packQty = row.Bzsl ?? 0m;
                    var labelCnt = (int)Math.Max(0, row.Bqsl ?? 0m);
                    if (totalQty <= 0 || labelCnt <= 0)
                        continue;

                    var xh = LabelXhStart;
                    var remainQty = totalQty;
                    var remainLabels = labelCnt;

                    // 多箱：前 N-1 箱按包装数量
                    while (remainLabels > 1)
                    {
                        await InsertLabelAsync(shddh, row, packQty, xh, gysdm, gysmc);
                        remainQty -= packQty;
                        remainLabels--;
                        xh++;
                    }

                    if (remainQty > 0)
                    {
                        await InsertLabelAsync(shddh, row, remainQty, xh, gysdm, gysmc);
                    }
                }

                // 5) 更新发货单状态
                await _db.Ado.ExecuteCommandAsync(
                    "UPDATE scm_shd SET shzt='待收', state=2, dycs=0 WHERE shddh=@shddh",
                    new List<SugarParameter> { new("@shddh", shddh) });

                // 6) MissedPrint：作废旧未打印（U）
                await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE MissedPrint SET
                        PurOrd = CONCAT('作废_', IFNULL(PurOrd, '')),
                        BarCode = CONCAT(CAST(RecID AS CHAR(20)), '_', IFNULL(BarCode, '')),
                        Status = 'C',
                        RelatedBarCode = '',
                        UpdateTime = NOW(),
                        UpdateUser = @u
                    WHERE Domain = @domain
                      AND ShipperNbr = @shddh
                      AND Status = 'U';
                    """,
                    new List<SugarParameter>
                    {
                        new("@u", userName),
                        new("@domain", domain),
                        new("@shddh", shddh),
                    });

                // 7) MissedPrint：插入待打印（U）
                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO MissedPrint
                    (
                        Domain, Site, PrintTime,
                        ItemNum, Descr, Product, Carton, OrdNbr,
                        PackingQty, Qty, Location, Status,
                        Supply, LotSerial, CartonQty, BarCode,
                        MoldNum, SuppLotSerial, ShipperNbr, ShipperLine,
                        ProdDate, PurOrd, PurLine, PurQty, WorkOrd,
                        LabelFormat, StandItem, EffSize, GP12CheckedQty, NetWeight,
                        Remark, CreateTime, UpdateTime, CreateUser, UpdateUser,
                        LevelChar, PurOrdDetBatchNbr, FirmString5, ExpireDate,
                        Printer, Company, Checker, Position
                    )
                    SELECT
                        @domain AS Domain,
                        @domain AS Site,
                        NULL AS PrintTime,
                        s.sh_material_code AS ItemNum,
                        s.sh_material_name AS Descr,
                        s.sh_material_ggxh AS Product,
                        RIGHT(s.xh, 4) AS Carton,
                        s.po_billno AS OrdNbr,
                        s.bzsl AS PackingQty,
                        s.sh_delivery_quantity AS Qty,
                        NULL AS Location,
                        'U' AS Status,
                        s.gysdm AS Supply,
                        s.shpc AS LotSerial,
                        1 AS CartonQty,
                        s.xh AS BarCode,
                        NULL AS MoldNum,
                        s.scph AS SuppLotSerial,
                        s.shdh AS ShipperNbr,
                        s.shdhh AS ShipperLine,
                        STR_TO_DATE(s.scrq, '%Y-%m-%d') AS ProdDate,
                        s.po_billno AS PurOrd,
                        s.po_billline AS PurLine,
                        s.sh_delivery_quantity AS PurQty,
                        NULL AS WorkOrd,
                        'cl01' AS LabelFormat,
                        s.po_billno AS StandItem,
                        s.po_billline AS EffSize,
                        s.sh_delivery_quantity AS GP12CheckedQty,
                        0 AS NetWeight,
                        s.remarks AS Remark,
                        NOW() AS CreateTime,
                        NOW() AS UpdateTime,
                        @u AS CreateUser,
                        @u AS UpdateUser,
                        s.bbh AS LevelChar,
                        s.jhdbh AS PurOrdDetBatchNbr,
                        p.ActiveRlseID1 AS FirmString5,
                        CASE
                            WHEN i.SuppWarranty = 1 THEN
                                CASE
                                    WHEN UPPER(i.WarrantyCode)='D' THEN DATE_ADD(STR_TO_DATE(s.scrq, '%Y-%m-%d'), INTERVAL IFNULL(i.DaysBetweenPM,0) DAY)
                                    WHEN UPPER(i.WarrantyCode)='Y' THEN DATE_ADD(STR_TO_DATE(s.scrq, '%Y-%m-%d'), INTERVAL IFNULL(i.DaysBetweenPM,0) YEAR)
                                    WHEN UPPER(i.WarrantyCode)='M' THEN DATE_ADD(STR_TO_DATE(s.scrq, '%Y-%m-%d'), INTERVAL IFNULL(i.DaysBetweenPM,0) MONTH)
                                    ELSE DATE_ADD(STR_TO_DATE(s.scrq, '%Y-%m-%d'), INTERVAL IFNULL(i.DaysBetweenPM,0) DAY)
                                END
                            ELSE NULL
                        END AS ExpireDate,
                        '' AS Printer,
                        '' AS Company,
                        '' AS Checker,
                        '' AS Position
                    FROM scm_shbq s
                    LEFT JOIN PurOrdDetail p ON p.PurOrd = s.po_billno AND p.Line = s.po_billline
                    LEFT JOIN ItemMaster i ON i.ItemNum = s.sh_material_code
                    WHERE s.shdh = @shddh;
                    """,
                    new List<SugarParameter>
                    {
                        new("@domain", domain),
                        new("@u", userName),
                        new("@shddh", shddh),
                    });
            });

            if (!tran.IsSuccess)
            {
                var msg = $"生成标签失败，{tran.ErrorMessage}";
                return new { success = false, msg, message = msg };
            }

            var ok = "生成标签成功！";
            return new { success = true, msg = ok, message = ok };
        }
        finally
        {
            if (!string.IsNullOrWhiteSpace(lockKey))
            {
                await ReleaseMySqlLockAsync(lockKey);
            }
        }
    }

    private async Task InsertLabelAsync(string shddh, LabelSourceRow row, decimal qty, int xh, string gysdm, string gysmc)
    {
        var jhdhh3 = Right(row.Jhdhh, 3);
        var xh4 = Right(xh.ToString(), 4);
        var bar = $"{shddh}{jhdhh3}{xh4}";

        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO scm_shbq
            (glid, sh_material_code, sh_material_name, sh_material_ggxh, sh_delivery_quantity,
             sh_material_dw, remarks, bzsl, order_type, po_billno, shdh, shdhh, scrq, scph, xh,
             gysdm, gysmc, po_billline, bbh, th, yt, ccrq, shpc, jhdbh, jhdhh)
            VALUES
            (@glid, @wlbm, @wlmc, @ggxh, @qty,
             @dw, @bz, @bzsl, @ddlx, @ddh, @shdh, @hh, @scrq, @scph, @xh,
             @gysdm, @gysmc, @pohh, @bbh, @th, @yt, @ccrq, @shpc, @jhdbh, @jhdhh);
            """,
            new List<SugarParameter>
            {
                new("@glid", row.DetailId),
                new("@wlbm", row.Wlbm ?? string.Empty),
                new("@wlmc", row.Wlmc ?? string.Empty),
                new("@ggxh", row.Ggxh ?? string.Empty),
                new("@qty", qty),
                new("@dw", row.Dw ?? string.Empty),
                new("@bz", row.Bz ?? string.Empty),
                new("@bzsl", row.Bzsl ?? 0m),
                new("@ddlx", row.Ddlx ?? string.Empty),
                new("@ddh", row.Ddh ?? string.Empty),
                new("@shdh", shddh),
                new("@hh", row.Hh ?? 0),
                new("@scrq", row.Scrq ?? string.Empty),
                new("@scph", row.Scph ?? string.Empty),
                new("@xh", bar),
                new("@gysdm", gysdm),
                new("@gysmc", gysmc),
                new("@pohh", row.PoBillLine ?? 0),
                new("@bbh", row.Bbh ?? string.Empty),
                new("@th", row.Th ?? string.Empty),
                new("@yt", row.Usage ?? string.Empty),
                new("@ccrq", row.Ccrq ?? string.Empty),
                new("@shpc", row.Shpc ?? string.Empty),
                new("@jhdbh", row.Jhdbh ?? string.Empty),
                new("@jhdhh", row.Jhdhh ?? string.Empty),
            });
    }

    private static string Right(string? s, int len)
    {
        if (string.IsNullOrEmpty(s)) return new string('0', len);
        var t = s.Trim();
        return t.Length <= len ? t.PadLeft(len, '0') : t[^len..];
    }

    private async Task AcquireMySqlLockAsync(string key, int timeoutSeconds)
    {
        var ok = await _db.Ado.GetIntAsync("SELECT GET_LOCK(@k, @t)", new List<SugarParameter>
        {
            new("@k", key),
            new("@t", timeoutSeconds),
        });
        if (ok != 1)
            throw Oops.Oh($"生成批次号锁等待超时（{key}）");
    }

    private Task ReleaseMySqlLockAsync(string key)
        => _db.Ado.ExecuteCommandAsync("SELECT RELEASE_LOCK(@k)", new List<SugarParameter> { new("@k", key) });

    /// <summary>
    /// 发货单号：供应商编码-yyyyMMdd-四位流水（同日同供应商递增，保存时分配）。
    /// </summary>
    private async Task<string> AllocateShddhAsync(string supplierCode)
    {
        var code = (supplierCode ?? string.Empty).Trim().Replace("-", string.Empty);
        if (string.IsNullOrEmpty(code))
            throw Oops.Oh("请先填写供应商编号，保存时将用于生成发货单号");

        var date = DateTime.Now.ToString("yyyyMMdd");
        var prefix = $"{code}-{date}-";
        if (prefix.Length > 240)
            throw Oops.Oh("供应商编码过长，无法生成发货单号");

        var lockKey = $"scm_shddh_{code}_{date}";
        if (lockKey.Length > 64)
            lockKey = lockKey[..64];

        await AcquireMySqlLockAsync(lockKey, 15);
        try
        {
            var maxSeq = await _db.Ado.GetIntAsync(
                """
                SELECT IFNULL(MAX(CAST(RIGHT(shddh, 4) AS UNSIGNED)), 0)
                FROM scm_shd
                WHERE shddh LIKE CONCAT(@pfx, '%')
                  AND CHAR_LENGTH(shddh) = CHAR_LENGTH(@pfx) + 4
                  AND RIGHT(shddh, 4) REGEXP '^[0-9]{4}$'
                """,
                new List<SugarParameter> { new("@pfx", prefix) });

            var next = maxSeq + 1;
            if (next > 9999)
                throw Oops.Oh("当日该供应商发货单流水号已超过 9999");

            return $"{prefix}{next:D4}";
        }
        finally
        {
            await ReleaseMySqlLockAsync(lockKey);
        }
    }

    private async Task<int> GetNextDailySerialAsync(string prefix, string domain)
    {
        // 从已存在的批次号中取当天最大流水：yyMMdd + 3位
        // 优先 scm_shdzb，其次 scm_shbq（兼容历史数据来源）
        var max1 = await _db.Ado.GetStringAsync(
            "SELECT MAX(shpc) FROM scm_shdzb WHERE shpc LIKE @pfx",
            new List<SugarParameter> { new("@pfx", $"{prefix}%") });
        var max2 = await _db.Ado.GetStringAsync(
            "SELECT MAX(shpc) FROM scm_shbq WHERE shpc LIKE @pfx",
            new List<SugarParameter> { new("@pfx", $"{prefix}%") });

        var max = string.CompareOrdinal(max1 ?? string.Empty, max2 ?? string.Empty) >= 0 ? max1 : max2;
        if (string.IsNullOrWhiteSpace(max) || max!.Length < prefix.Length + ShpcSerialWidth)
        {
            // 兜底：读取 NbrControl（NbrType='WoLot'）的 NextValue + 1
            // 说明：有些环境可能会清理历史批次号表；此时用 NbrControl 保证连续。
            var nv = await _db.Ado.GetIntAsync(
                """
                SELECT IFNULL(NextValue, 0)
                FROM NbrControl
                WHERE NbrType='WoLot'
                  AND (
                        domain_code = @d
                        OR Domain = @d
                        OR IFNULL(@d,'') = ''
                      )
                ORDER BY RecID
                LIMIT 1
                """,
                new List<SugarParameter> { new("@d", domain ?? string.Empty) });
            // 用户要求：NextValue+1 为下一流水号起点
            return Math.Max(1, nv + 1);
        }

        var suffix = max.Substring(prefix.Length);
        return int.TryParse(suffix, out var n) ? n + 1 : 1;
    }

    private async Task BumpNbrControlNextValueAsync(string domain, int lastSerial)
    {
        // 保守推进：仅当新值更大时更新，避免其它流程并发推进导致回退。
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE NbrControl
            SET NextValue = CASE
                WHEN IFNULL(NextValue, 0) < @v THEN @v
                ELSE IFNULL(NextValue, 0)
            END,
                UpdateTime = NOW()
            WHERE NbrType = 'WoLot'
              AND (
                    domain_code = @d
                    OR Domain = @d
                    OR IFNULL(@d,'') = ''
                  );
            """,
            new List<SugarParameter>
            {
                new("@d", domain ?? string.Empty),
                new("@v", lastSerial),
            });
    }

    private async Task<bool> TableExistsAsync(string tableName)
    {
        var cnt = await _db.Ado.GetIntAsync(
            """
            SELECT COUNT(*)
            FROM information_schema.tables
            WHERE table_schema = DATABASE()
              AND table_name = @t
            """,
            new List<SugarParameter> { new("@t", tableName) });
        return cnt > 0;
    }

    private sealed class NeedShpcRow
    {
        public string? Wlbm { get; set; }
        public string? Scph { get; set; }
    }

    private sealed class IdOnlyRow
    {
        public long Id { get; set; }
    }

    private sealed class LabelSourceRow
    {
        public string? Shddh { get; set; }
        public long DetailId { get; set; }
        public string? Glid { get; set; }
        public string? Wlbm { get; set; }
        public string? Wlmc { get; set; }
        public string? Ggxh { get; set; }
        public decimal? Shsl { get; set; }
        public string? Dw { get; set; }
        public string? Bz { get; set; }
        public decimal? Bzsl { get; set; }
        public decimal? Bqsl { get; set; }
        public string? Ddlx { get; set; }
        public string? Ddh { get; set; }
        public int? PoBillLine { get; set; }
        public int? Hh { get; set; }
        public string? Scrq { get; set; }
        public string? Scph { get; set; }
        public string? Th { get; set; }
        public string? Bbh { get; set; }
        public string? Usage { get; set; }
        public string? Ccrq { get; set; }
        public string? Jhdbh { get; set; }
        public string? Jhdhh { get; set; }
        public string? Shpc { get; set; }
    }

    [DisplayName("打印送货单（预留）")]
    [HttpPost("supplier-shipment/print-shipping-note")]
    public Task<object> PrintShippingNote([FromBody] SupplierShipmentOperationInput input)
        => Task.FromResult<object>(new { message = $"发货单{input.Id}：功能预留，暂未启用" });

    [DisplayName("打印标签（预留）")]
    [HttpPost("supplier-shipment/print-label")]
    public Task<object> PrintLabel([FromBody] SupplierShipmentOperationInput input)
        => Task.FromResult<object>(new { message = $"发货单{input.Id}：功能预留，暂未启用" });

    [DisplayName("发货单标签数据（scm_shbq）")]
    [HttpGet("supplier-shipment/label-data")]
    public async Task<object> GetLabelData([FromQuery] string shddh)
    {
        if (string.IsNullOrWhiteSpace(shddh))
            throw Oops.Oh("缺少发货单编号（shddh）");

        var pars = new List<SugarParameter> { new("@shddh", shddh.Trim()) };
        const string sql = """
                           SELECT DISTINCT
                               glid,
                               sh_material_code AS wlbm,
                               sh_material_name AS wlmc,
                               CONCAT(IFNULL(sh_material_name, ''), IFNULL(sh_material_ggxh, '')) AS ggxh,
                               sh_delivery_quantity AS zxsl,
                               sh_material_dw AS dw,
                               remarks,
                               bzsl AS bz,
                               order_type AS ddlx,
                               po_billno AS ddh,
                               shdh AS shdh,
                               shdhh AS shdhh,
                               DATE_FORMAT(scrq, '%Y.%m.%d') AS scrq,
                               scph,
                               xh,
                               gysmc,
                               th,
                               bbh,
                               yt,
                               ccrq,
                               shpc
                           FROM scm_shbq
                           WHERE shdh = @shddh
                           """;

        var list = await _db.Ado.SqlQueryAsync<SupplierShipmentLabelRow>(sql, pars);
        return new { list };
    }

    private async Task SaveDetailsAsync(long masterId, List<SupplierShipmentDetailInput> inputDetails)
    {
        var dbDetails = await _detailRep.AsQueryable().Where(x => x.Glid == masterId.ToString()).ToListAsync();
        var dbById = dbDetails.ToDictionary(x => x.Id);
        var inputIds = new HashSet<long>(inputDetails.Where(d => d.Id is > 0).Select(d => d.Id!.Value));

        for (var i = 0; i < inputDetails.Count; i++)
        {
            var d = inputDetails[i];
            if (d.Id is > 0 && dbById.TryGetValue(d.Id.Value, out var existing))
            {
                existing.Hh = d.Hh ?? (i + 1);
                existing.PoBill = d.PoBill;
                existing.PoBillLine = d.PoBillLine;
                existing.OrderType = d.OrderType;
                existing.ShMaterialCode = d.ShMaterialCode;
                existing.ShMaterialName = d.ShMaterialName;
                existing.Th = d.Th;
                existing.ShDeliveryQuantity = d.ShDeliveryQuantity;
                existing.Bzsl = d.Bzsl;
                existing.Bqsl = d.Bqsl;
                existing.ShMaterialDw = d.ShMaterialDw;
                existing.Scrq = d.Scrq;
                existing.Scph = d.Scph;
                existing.Remarks = d.Remarks;
                existing.Djsl = d.Djsl;
                existing.Jybb = d.Jybb;
                existing.Jhdbh = d.Jhdbh;
                await _detailRep.UpdateAsync(existing);
            }
            else
            {
                var detail = new ScmShdzb
                {
                    Id = YitIdHelper.NextId(),
                    Glid = masterId.ToString(),
                    Hh = d.Hh ?? (i + 1),
                    PoBill = d.PoBill,
                    PoBillLine = d.PoBillLine,
                    OrderType = d.OrderType,
                    ShMaterialCode = d.ShMaterialCode,
                    ShMaterialName = d.ShMaterialName,
                    Th = d.Th,
                    ShDeliveryQuantity = d.ShDeliveryQuantity,
                    Bzsl = d.Bzsl,
                    Bqsl = d.Bqsl,
                    ShMaterialDw = d.ShMaterialDw,
                    Scrq = d.Scrq,
                    Scph = d.Scph,
                    Remarks = d.Remarks,
                    Djsl = d.Djsl,
                    Jybb = d.Jybb,
                    Jhdbh = d.Jhdbh
                };
                await _detailRep.InsertAsync(detail);
            }
        }

        foreach (var old in dbDetails.Where(x => !inputIds.Contains(x.Id)))
        {
            await _detailRep.DeleteAsync(x => x.Id == old.Id);
        }
    }

    private static string BuildListOrderBy(string? sortField, string? sortOrder)
    {
        var map = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["shddh"] = "t.shddh",
            ["poBill"] = "t.po_bill",
            ["jhshrq"] = "t.jhshrq",
            ["shMaterialCode"] = "t.sh_material_code",
            ["shMaterialName"] = "t.sh_material_name",
            ["shDeliveryQuantity"] = "t.sh_delivery_quantity",
            ["sfpc"] = "t.sfpc",
            ["pcrksl"] = "t.pcrksl",
            ["shPurchaseName"] = "t.sh_purchase_name",
            ["shpc"] = "t.shpc",
            ["scph"] = "t.scph",
            ["wldh"] = "t.wldh",
            ["dycs"] = "t.dycs",
            ["shzt"] = "t.shzt"
        };
        var field = map.TryGetValue(sortField ?? string.Empty, out var sqlField) ? sqlField : "t.mid";
        var order = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return $" ORDER BY {field} {order} ";
    }

    private static string BuildListSql() => """
        SELECT
            m.mid,
            m.id,
            m.sh_purchase_id,
            m.sh_purchase_name,
            m.sh_purchase_num,
            m.sh_purchase_address,
            m.sh_purchase_lxr,
            m.sh_purchase_phone,
            m.client,
            m.delivery_Address,
            m.expected_consignee,
            m.consignee_phone,
            m.estimated_delivery_date,
            m.po_billno,
            m.shddh,
            m.jhshrq,
            m.tjrid,
            m.tjrxm,
            m.tjrq,
            m.scbq,
            m.chbg,
            m.sfpc,
            m.pcsm,
            m.wlsc,
            m.yjdhrq,
            m.state,
            m.shzt,
            m.wldh,
            m.dycs,
            m.gys,
            m.sh_material_code,
            m.sh_material_name,
            m.sh_material_ggxh,
            m.hh,
            m.po_bill,
            m.shpc,
            m.scph,
            m.sh_delivery_quantity,
            m.th,
            n.rksl,
            n.bhgsl,
            n.thsl,
            n.zshl,
            n.Delivery,
            n.pc,
            l.pcrksl,
            po.potype,
            po.Usage
        FROM
        (
            SELECT
                a.id mid,
                b.id,
                a.sh_purchase_id,
                a.sh_purchase_name,
                a.sh_purchase_num,
                a.sh_purchase_address,
                a.sh_purchase_lxr,
                a.sh_purchase_phone,
                a.client,
                a.delivery_Address,
                a.expected_consignee,
                a.consignee_phone,
                a.estimated_delivery_date,
                a.po_billno,
                a.shddh,
                a.jhshrq,
                a.tjrid,
                a.tjrxm,
                a.tjrq,
                a.scbq,
                a.chbg,
                a.sfpc,
                a.pcsm,
                a.wlsc,
                a.yjdhrq,
                a.state,
                IFNULL(a.shzt, '待收') shzt,
                a.wldh,
                a.dycs,
                CONCAT(IFNULL(a.sh_purchase_num, ''), IFNULL(a.sh_purchase_name, '')) gys,
                b.sh_material_code,
                b.sh_material_name,
                b.sh_material_ggxh,
                b.hh,
                b.po_bill,
                c.shpc,
                b.scph,
                b.sh_delivery_quantity,
                b.th
            FROM scm_shd a
            LEFT JOIN scm_shdzb b ON a.id = b.glid
            LEFT JOIN (SELECT DISTINCT glid, shpc FROM scm_shbq) c ON b.id = c.glid
        ) m
        LEFT JOIN
        (
            SELECT
                Delivery,
                LotSerial pc,
                SUM(ReceiptQty) zshl,
                AVG(yssl) rksl,
                SUM(QtyReturn) bhgsl,
                SUM(QtyReturned) thsl
            FROM vscm_cgshrk
            GROUP BY Delivery, LotSerial
        ) n ON m.shddh = n.Delivery AND m.shpc = n.pc
        LEFT JOIN
        (
            SELECT LotSerial, SUM(QtyChange) pcrksl
            FROM InvTransHist
            WHERE QtyChange > 0 AND Reason LIKE '%收货'
            GROUP BY LotSerial
        ) l ON m.shpc = l.LotSerial
        LEFT JOIN PurOrdMaster po ON m.po_bill = po.purord
        """;

    private sealed class SupplierShipmentListRow
    {
        public long Mid { get; set; }
        public long Id { get; set; }
        public string? ShPurchaseName { get; set; }
        public string? ShPurchaseNum { get; set; }
        public string? Shddh { get; set; }
        public string? Jhshrq { get; set; }
        public int? Sfpc { get; set; }
        public string? Wldh { get; set; }
        public int? Dycs { get; set; }
        public string? Shzt { get; set; }
        public string? ShMaterialCode { get; set; }
        public string? ShMaterialName { get; set; }
        public decimal? ShDeliveryQuantity { get; set; }
        public decimal? Pcrksl { get; set; }
        public string? PoBill { get; set; }
        public string? Usage { get; set; }
        public string? Shpc { get; set; }
        public string? Scph { get; set; }
        public string? Th { get; set; }
        public int? State { get; set; }
    }

    private sealed class SupplierShipmentDraftRow
    {
        public string? SourceId { get; set; }
        public string? Gysdm { get; set; }
        public string? Gysmc { get; set; }
        public string? PoBill { get; set; }
        public int? PoBillLine { get; set; }
        public string? OrderType { get; set; }
        public string? ShMaterialCode { get; set; }
        public string? ShMaterialName { get; set; }
        public string? Th { get; set; }
        public decimal? ShDeliveryQuantity { get; set; }
        public decimal? Bzsl { get; set; }
        public decimal? Bqsl { get; set; }
        public string? ShMaterialDw { get; set; }
        public decimal? Djsl { get; set; }
        public string? Jhdbh { get; set; }
        public string? ShMaterialGgxh { get; set; }
    }

    private sealed class SupplierShipmentLabelRow
    {
        public long? Glid { get; set; }
        public string? Wlbm { get; set; }
        public string? Wlmc { get; set; }
        public string? Ggxh { get; set; }
        public decimal? Zxsl { get; set; }
        public string? Dw { get; set; }
        public string? Remarks { get; set; }
        public decimal? Bz { get; set; }
        public string? Ddlx { get; set; }
        public string? Ddh { get; set; }
        public string? Shdh { get; set; }
        public string? Shdhh { get; set; }
        public string? Scrq { get; set; }
        public string? Scph { get; set; }
        public string? Xh { get; set; }
        public string? Gysmc { get; set; }
        public string? Th { get; set; }
        public string? Bbh { get; set; }
        public string? Yt { get; set; }
        public string? Ccrq { get; set; }
        public string? Shpc { get; set; }
    }
}