using Masuit.Tools;
using NewPdaSqlServer.DB;
using NewPdaSqlServer.Dto.service;
using NewPdaSqlServer.entity;
using NewPdaSqlServer.util;
using SqlSugar;

namespace NewPdaSqlServer.service.Warehouse;

/// <summary>
///     MES物料表管理类
/// </summary>
public class MesItemTblManager : Repository<MesItemTbl>
{
    /// <summary>
    ///     获取生产退料单号列表
    /// </summary>
    /// <returns>退料单号列表</returns>
    public List<string> GetSCTLBillNo(WarehouseQuery query)
    {
        var list = Db.Queryable<MesItemTbl>()
            .Where(s => (s.Tbl013 ?? 0) == 1 // 审核通过
                        && (s.Tbl020 ?? 0) == 0
                        && s.Tbl008 == query.Type)
            .Select(s => s.BillNo).ToList();

        return list;
    }

    /// <summary>
    ///     根据单号获取MES物料表明细
    /// </summary>
    /// <param name="query">仓库查询参数</param>
    /// <returns>物料明细列表</returns>
    public List<MesItemTblDetail> GetMesItemTblDetailByBillNo(
        WarehouseQuery query)
    {
        // 关联查询物料表、物料明细表和物料基础信息表
        var mesItemTblDetails = Db
            .Queryable<MesItemTbl, MesItemTblDetail, MesItems>(
                (a, b, c) => new JoinQueryInfos(
                    JoinType.Left,
                    a.Id == b.Tlmid,
                    JoinType.Left,
                    b.Tld009 == c.Id)
            ).Where((a, b, c) => a.BillNo == query.billNo)
            .Select<MesItemTblDetail>((a, b, c) => new MesItemTblDetail
            {
                BillNo = a.BillNo,
                Tlid = b.Tlid,
                Tlmid = b.Tlmid,
                Tld005 = b.Tld005 ?? 0, // 待退数量
                Tld006 = b.Tld006 ?? 0, // 已退数量
                Tld009 = b.Tld009,
                ItemNo = c.ItemNo, // 物料编号
                ItemName = c.ItemName, // 物料名称
                ItemModel = c.ItemModel // 物料型号
            }).ToList();

        // 筛选出待退数量大于已退数量的记录
        var itemTblDetails = mesItemTblDetails
            .Where(s => (s.Tld005 ?? 0) - (s.Tld006 ?? 0) > 0).ToList();

        return itemTblDetails;
    }

    /// <summary>
    ///     生产退料扫描库位
    /// </summary>
    /// <param name="query">仓库查询参数</param>
    /// <returns>库位信息</returns>
    public WarehouseQuery SctlScanDepots(WarehouseQuery query)
    {
        if (query.DepotCode.IsNullOrEmpty())
            throw new NullReferenceException("请扫库位条码");

        // 关联查询库位分区和库位信息
        var warehouseQuery = Db.Queryable<MesDepotSections, MesDepots>((a, b) =>
                new JoinQueryInfos(JoinType.Inner, a.DepotGuid == b.Guid))
            .Where((a, b) => a.DepotSectionCode == query.DepotCode)
            .Select<WarehouseQuery>((a, b) => new WarehouseQuery
            {
                DepotSectionName = a.DepotSectionName,
                DepotName = b.DepotName,
                DepotCode = b.DepotCode
            }).First();

        if (warehouseQuery == null)
            throw new Exception("库位编码" + query.DepotCode + " 不存在，请确认！");

        return warehouseQuery;
    }

    /// <summary>
    ///     生产退料扫描条码
    /// </summary>
    /// <param name="query">仓库查询参数</param>
    /// <returns>处理结果</returns>
    public WarehouseQuery SctlScanBarcode(WarehouseQuery query)
    {
        var p_item_barcode = query.barcode; // 物料条码
        var p_bill_no = query.billNo; // 单据号
        var p_section_code = query.DepotCode; // 库位编码
        var c_user = query.userName; // 用户名

        var p_bill_type_id = 100; // 单据类型ID
        var p_transction_no = 104; // 交易编号

        // 验证库位条码
        if (p_section_code.IsNullOrEmpty()) throw new Exception("请扫库位条码！");

        // 获取库位信息
        var c_depot_code = Db.Queryable<MesDepotSections, MesDepots>((a, b) =>
                new JoinQueryInfos(JoinType.Inner, a.DepotGuid == b.Guid))
            .Where((a, b) => a.DepotSectionCode == p_section_code)
            .Select((a, b) => b.DepotId).First();

        if (!c_depot_code.HasValue)
            throw new Exception("库位编码" + p_section_code + " 不存在，请确认！");

        // 获取库位分区信息
        var mesDepotSections = Db.Queryable<MesDepotSections>()
            .Where(a => a.DepotSectionCode == p_section_code).First();

        if (mesDepotSections == null)
            throw new Exception("库位编码" + p_section_code + " 不存在，请确认！");

        // 获取库位基础信息
        var c_mes_depots = Db.Queryable<MesDepots>()
            .Where(b => b.Guid == mesDepotSections.DepotGuid).First();

        if (c_mes_depots == null)
            throw new Exception("库位编码" + p_section_code + " 不存在，请确认！");

        // 检查条码是否已入库
        var c_num = Db.Queryable<MesInvItemIns, MesInvItemInCDetails>((a, b) =>
                new JoinQueryInfos(JoinType.Inner, a.Guid == b.ItemInId))
            .Where((a, b) => b.ItemBarcode == p_item_barcode
                             && a.BillTypeId == p_bill_type_id &&
                             a.TransctionNo == p_transction_no.ToString())
            .Count();

        if (c_num > 0) throw new Exception("此条码已扫入库，勿重复扫描！");

        c_num = Db.Queryable<MesInvItemStocks>()
            .Where(t => t.ItemBarcode == p_item_barcode).Count();

        if (c_num > 0) throw new Exception("此条码已扫入库，勿重复扫描！");

        // 获取条码信息
        var c_mes_inv_item_barcodes = Db.Queryable<MesInvItemBarcodes>()
            .Where(t => t.ItemBarcode == p_item_barcode).First();

        if (c_mes_inv_item_barcodes == null)
            throw new Exception("此条码不属于该退料单，请核对！");

        if (c_mes_inv_item_barcodes.Memo != "生产退料")
            throw new Exception("此条码不是生产退料条码，不可使用生产退料模块！");

        // 获取退料单信息
        var C_MES_ITEM_TBL = Db.Queryable<MesItemTbl>()
            .Where(a => a.BillNo == c_mes_inv_item_barcodes.BillNo
                        && (a.Tbl013 ?? 0) == 1).First();

        if (C_MES_ITEM_TBL == null) throw new Exception("申请单已撤回,无法扫码!");

        if (C_MES_ITEM_TBL.Tbl020 == 1) throw new Exception("扫码完成,申请单已完结!");

        // 获取退料单明细
        var C_MES_ITEM_TBL_DETAIL = Db.Queryable<MesItemTblDetail>()
            .Where(a => a.Tlmid == C_MES_ITEM_TBL.Id
                        && a.Tld010 == c_mes_inv_item_barcodes.WorkLine)
            .First();

        if (C_MES_ITEM_TBL_DETAIL == null)
            throw new Exception("条码不属于该申请单明细,无法扫码!");

        var c_quantity = c_mes_inv_item_barcodes.Quantity;

        var c_bill_no = "";
        var c_id = Guid.Empty;

        // 使用事务处理数据更新
        UseTransaction(db =>
        {
            // 查询入库单
            var mesInvItemIns = db.Queryable<MesInvItemIns>()
                .Where(d =>
                    d.Status == 0 && d.TransctionNo ==
                                  p_transction_no.ToString()
                                  && d.CbillNo == c_mes_inv_item_barcodes.BillNo
                                  && d.DepotsId == c_depot_code
                                  && d.InsDate.Value.ToString("yyyyMMdd") ==
                                  DateTime.Now.ToString("yyyyMMdd")).First();

            var totalResult = 0;

            // 如果入库单不存在则创建新的入库单
            if (mesInvItemIns == null)
            {
                c_bill_no = BillNo.GetBillNo("IN(入库单)");

                c_id = Guid.NewGuid();

                totalResult += db.Insertable(new MesInvItemIns
                {
                    Guid = c_id,
                    BillNo = c_bill_no,
                    BillTypeId = p_bill_type_id,
                    UserNoBack = c_user,
                    Reason = C_MES_ITEM_TBL.Tbl005,
                    Remark = C_MES_ITEM_TBL.Tbl006,
                    InsDate = DateTime.Now,
                    // DepotsCode = c_depot_code,
                    DepotsId = c_depot_code,
                    TransctionNo = p_transction_no.ToString(),
                    CreateBy = c_user,
                    CreateDate = DateTime.Now,
                    LastupdateBy = c_user,
                    LastupdateDate = DateTime.Now,
                    CbillNo = C_MES_ITEM_TBL.BillNo,
                    RbillNo = C_MES_ITEM_TBL.Tbl002
                }).IgnoreColumns(true).ExecuteCommand();
            }
            else
            {
                c_id = mesInvItemIns.Guid;
                c_bill_no = mesInvItemIns.BillNo;
            }

            // 检查是否为合并打印条码
            var hbdy = c_mes_inv_item_barcodes.Hbdy ?? 0;
            if (hbdy == 1) throw new Exception("不支持合并打印的条码:" + p_item_barcode);

            // 插入入库单明细
            totalResult += db.Insertable(new MesInvItemInCDetails
            {
                Guid = Guid.NewGuid(),
                ItemInId = c_id,
                BillNo = c_bill_no,
                ItemBarcode = p_item_barcode,
                Quantity = c_quantity,
                BarcodeFlag = true,
                EpFlag = true,
                WorkType = 1,
                ItemId = c_mes_inv_item_barcodes.ItemId,
                ItemNo = c_mes_inv_item_barcodes.ItemNo,
                LotNo = c_mes_inv_item_barcodes.LotNo,
                SuppId = c_mes_inv_item_barcodes.SuppId,
                SuppNo = c_mes_inv_item_barcodes.SuppNo,
                DepotId = c_mes_depots.DepotId,
                DepotCode = c_mes_depots.DepotCode,
                DepotSectionCode = p_section_code,
                ItemSname = c_mes_inv_item_barcodes.ItemSname,
                Unit = c_mes_inv_item_barcodes.Unit,
                CreateBy = c_user,
                CreateDate = DateTime.Now,
                LastupdateBy = c_user,
                LastupdateDate = DateTime.Now,
                Remark = c_mes_inv_item_barcodes.Memo,
                Ebeln = c_mes_inv_item_barcodes.Mblnr,
                EbelnLineNo = c_mes_inv_item_barcodes.Zeile,
                WorkNo = c_mes_inv_item_barcodes.WorkNo,
                WorkLine = c_mes_inv_item_barcodes.WorkLine,
                CbillNo = c_mes_inv_item_barcodes.BillNo,
                UrgentFlag = c_mes_inv_item_barcodes.UrgentFlag,
                BoardStyle = c_mes_inv_item_barcodes.BoardStyle,
                TaskNo = c_mes_inv_item_barcodes.TaskNo,
                RbillNo = C_MES_ITEM_TBL.Tbl002
            }).IgnoreColumns(true).ExecuteCommand();

            // 插入业务记录
            totalResult += db.Insertable(new MesInvBusiness2
            {
                Guid = Guid.NewGuid(),
                Status = 1,
                BillTypeId = p_bill_type_id,
                TransactionCode = p_transction_no.ToString(),
                BusinessType = 1,
                ItemBarcode = p_item_barcode,
                ItemNo = c_mes_inv_item_barcodes.ItemNo,
                LotNo = c_mes_inv_item_barcodes.LotNo,
                EpFlag = true,
                Quantity = c_mes_inv_item_barcodes.Quantity,
                ToInvDepotsCode = c_mes_depots.DepotCode,
                InvDepotId = c_depot_code,
                ToInvDepotSectionsCode = p_section_code,
                Description = "生产退料",
                CreateBy = c_user,
                CreateDate = DateTime.Now,
                LastupdateBy = c_user,
                LastupdateDate = DateTime.Now,
                TaskNo = c_mes_inv_item_barcodes.TaskNo,
                BillNo = c_bill_no,
                WorkNo = c_mes_inv_item_barcodes.WorkNo,
                WorkLine = c_mes_inv_item_barcodes.WorkLine,
                SuppId = c_mes_inv_item_barcodes.SuppId,
                SuppNo = c_mes_inv_item_barcodes.SuppNo,
                ItemId = c_mes_inv_item_barcodes.ItemId
            }).IgnoreColumns(true).ExecuteCommand();

            // 插入库存记录
            totalResult += db.Insertable(new MesInvItemStocks
            {
                Guid = Guid.NewGuid(),
                TaskNo = c_mes_inv_item_barcodes.TaskNo,
                ItemBarcode = p_item_barcode,
                ItemNo = c_mes_inv_item_barcodes.ItemNo,
                LotNo = c_mes_inv_item_barcodes.LotNo,
                Quantity = c_mes_inv_item_barcodes.Quantity,
                EpFlag = c_mes_inv_item_barcodes.EpFlag.Value
                    ? (byte)1
                    : (byte)0,
                DepotId = c_mes_depots.DepotId,
                DepotsCode = c_mes_depots.DepotCode,
                DepotSectionsCode = p_section_code,
                CheckDate = c_mes_inv_item_barcodes.CreateDate,
                IndepDate = DateTime.Now,
                BoardStyle = c_mes_inv_item_barcodes.BoardStyle,
                WorkNo = c_mes_inv_item_barcodes.WorkNo,
                WorkLine = c_mes_inv_item_barcodes.WorkLine,
                SuppNo = c_mes_inv_item_barcodes.SuppNo,
                ItemId = c_mes_inv_item_barcodes.ItemId
            }).IgnoreColumns(true).ExecuteCommand();

            // 根据退料类型(良品退料、来料不良退料、作业不良退料)更新相关数据
            if (C_MES_ITEM_TBL.Tbl005 is "良品退料" or "来料不良退料")
            {
                // 良品退料 - 更新工单表(WOMDAB)相关数量
                if (C_MES_ITEM_TBL.Tbl005 == "良品退料")
                    totalResult += Db.Updateable<Womdab>()
                        .SetColumns(it => new Womdab
                        {
                            Dab007 = it.Dab007 -
                                     c_mes_inv_item_barcodes.Quantity, // 减少工单数量
                            Dab022 = (it.Dab022 ?? 0) +
                                     c_mes_inv_item_barcodes.Quantity, // 增加退料数量
                            LpTl = (it.LpTl ?? 0) +
                                   (int)c_mes_inv_item_barcodes
                                       .Quantity, // 增加良品退料数量
                            Dab020 = (it.Dab020 ?? 0) -
                                     c_mes_inv_item_barcodes.Quantity // 减少已发料数量
                        })
                        .Where(it => it.Dab001 == c_mes_inv_item_barcodes.WorkNo
                                     && it.Dab002 == c_mes_inv_item_barcodes
                                         .WorkLine
                                     && it.Dab003 == c_mes_inv_item_barcodes
                                         .ItemId.ToString())
                        .ExecuteCommand();
                // 来料不良退料 - 更新工单表(WOMDAB)相关数量
                else if (C_MES_ITEM_TBL.Tbl005 == "来料不良退料")
                    totalResult += Db.Updateable<Womdab>()
                        .SetColumns(it => new Womdab
                        {
                            Dab007 = it.Dab007 -
                                     c_mes_inv_item_barcodes.Quantity, // 减少工单数量
                            Dab022 = (it.Dab022 ?? 0) +
                                     c_mes_inv_item_barcodes.Quantity, // 增加退料数量
                            LlBl = (it.LlBl ?? 0) +
                                   (int)c_mes_inv_item_barcodes
                                       .Quantity, // 增加来料不良数量
                            Dab020 = (it.Dab020 ?? 0) -
                                     c_mes_inv_item_barcodes.Quantity // 减少已发料数量
                        })
                        .Where(it => it.Dab001 == c_mes_inv_item_barcodes.WorkNo
                                     && it.Dab002 == c_mes_inv_item_barcodes
                                         .WorkLine
                                     && it.Dab003 == c_mes_inv_item_barcodes
                                         .ItemId.ToString())
                        .ExecuteCommand();

                // 更新退料单明细表已退数量
                totalResult += Db.Updateable<MesItemTblDetail>()
                    .SetColumns(it => new MesItemTblDetail
                    {
                        Tld006 = (it.Tld006 ?? 0) +
                                 (int)c_mes_inv_item_barcodes.Quantity // 增加已退数量
                    })
                    .Where(it => it.Tlmid == C_MES_ITEM_TBL.Id
                                 && it.Tld009 == c_mes_inv_item_barcodes.ItemId
                                 && it.Tld010 ==
                                 c_mes_inv_item_barcodes.WorkLine)
                    .ExecuteCommand();
            }
            // 作业不良退料 - 更新工单表和退料单明细表
            else if (C_MES_ITEM_TBL.Tbl005 == "作业不良退料")
            {
                totalResult += Db.Updateable<Womdab>()
                    .SetColumns(it => new Womdab
                    {
                        Dab022 = (it.Dab022 ?? 0) +
                                 c_mes_inv_item_barcodes.Quantity, // 增加退料数量
                        ZyBl = (it.ZyBl ?? 0) +
                               (int)c_mes_inv_item_barcodes
                                   .Quantity, // 增加作业不良数量
                        Dab020 = (it.Dab020 ?? 0) -
                                 c_mes_inv_item_barcodes.Quantity // 减少已发料数量
                    })
                    .Where(it => it.Dab001 == c_mes_inv_item_barcodes.WorkNo
                                 && it.Dab002 ==
                                 c_mes_inv_item_barcodes.WorkLine
                                 && it.Dab003 == c_mes_inv_item_barcodes.ItemId
                                     .ToString())
                    .ExecuteCommand();

                // 更新退料单明细表已退数量
                totalResult += Db.Updateable<MesItemTblDetail>()
                    .SetColumns(it => new MesItemTblDetail
                    {
                        Tld006 = (it.Tld006 ?? 0) +
                                 (int)c_mes_inv_item_barcodes.Quantity // 增加已退数量
                    })
                    .Where(it => it.Tlmid == C_MES_ITEM_TBL.Id
                                 && it.Tld009 == c_mes_inv_item_barcodes.ItemId
                                 && it.Tld010 ==
                                 c_mes_inv_item_barcodes.WorkLine)
                    .ExecuteCommand();
            }

            // 如果待退数量等于本次退料数量，则更新明细完成状态
            if ((C_MES_ITEM_TBL_DETAIL.Tld005 ?? 0) -
                (C_MES_ITEM_TBL_DETAIL.Tld006 ?? 0) ==
                c_mes_inv_item_barcodes.Quantity)
                totalResult += Db.Updateable<MesItemTblDetail>()
                    .SetColumns(it => new MesItemTblDetail
                        { Tld008 = 1 }) // 设置完成标志
                    .Where(it => it.Tlid == C_MES_ITEM_TBL_DETAIL.Tlid)
                    .ExecuteCommand();

            // 检查退料单是否所有明细都已完成
            var remainingCount = Db.Queryable<MesItemTbl, MesItemTblDetail>(
                    (a, b) =>
                        new JoinQueryInfos(JoinType.Left, a.Id == b.Tlmid))
                .Where((a, b) =>
                    a.BillNo == p_bill_no &&
                    (b.Tld005 ?? 0) - (b.Tld006 ?? 0) > 0)
                .Count();

            // 如果所有明细都已完成，则更新退料单状态为已完成
            if (remainingCount < 1)
                totalResult += Db.Updateable<MesItemTbl>()
                    .SetColumns(it => it.Tbl020 == 1) // 设置完成标志
                    .Where(it => it.BillNo == p_bill_no)
                    .ExecuteCommand();

            // 检查必要的插入操作是否都成功执行
            var minimumExpectedOperations = 3; // 至少需要执行的插入操作数
            if (totalResult < minimumExpectedOperations)
                throw new Exception(
                    $"关键数据插入失败，预期至少{minimumExpectedOperations}个操作，实际执行{totalResult}个操作");

            return totalResult;
        });

        query.itemNo = c_mes_inv_item_barcodes.ItemNo;
        query.Num = c_quantity.Value;
        return query;
    }
}