using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using SqlSugar;

namespace MES.Service.service.Warehouse;

public class OpeningReceiptServer : RepositoryNoEntity
{
    private const string Factory = "1000";

    private const string Company = "1000";

    private const int billTypeId = 100;

    private const int transactionNo = 601;

    public MesInvItemBarcodes ScanInBarcodeQC(WarehouseQuery query)
    {
        if (string.IsNullOrWhiteSpace(query.sectionCode))
            throw new Exception("请扫库位条码！");

        var depotCode = GetDepotCode(query.sectionCode);
        if (depotCode == null)
            throw new Exception($"002[库位编码 {query.sectionCode} 不存在，请确认！");

        var checkBarcodeAlreadyReceived =
            CheckBarcodeAlreadyReceived(query.barcode);

        if (checkBarcodeAlreadyReceived > 0) throw new Exception("条码重复扫描，请核对！");

        if (CheckBarcodeInStock(query.barcode) > 0)
            throw new Exception("条码已在库存中，请核对！");

        var barcodeInfo = GetBarcodeInfo(query.barcode);
        if (barcodeInfo == null || barcodeInfo.ComeFlg != 0)
            throw new Exception("条码不是期初条码，无法用期初入库！");

        var inventoryItemInId = GetOrCreateInventoryItemInId(barcodeInfo,
            depotCode, query.userName, out var billNo);

        UseTransaction(db =>
        {
            // Insert records
            InsertInventoryDetails(inventoryItemInId, billNo, barcodeInfo,
                depotCode, query.sectionCode, query.userName);
            InsertBusinessRecord(barcodeInfo, depotCode, query.sectionCode,
                billNo,
                query.userName);
            InsertStockRecord(barcodeInfo, depotCode, query.sectionCode,
                query.userName);
            return 1;
        });

        return barcodeInfo;
    }

    public PurchaseInventory GetForm(WarehouseQuery query)
    {
        var mesInvItemBarcodes = Db.Queryable<MesInvItemBarcodes>()
            .Where(s => s.ItemBarcode == query.barcode).Count();

        if (mesInvItemBarcodes <= 0) throw new Exception("条码不存在");

        var mesInvItemInCDetails = Db.Queryable<MesInvItemInCDetails>()
            .Where(s => s.ItemBarcode == query.barcode).Single();

        var entity = new PurchaseInventory
        {
            ItemIns = GetMesInvItemIns(mesInvItemInCDetails.ItemInId),
            InvItemInCDetails =
                GetMesInvItemInCDetailsList(mesInvItemInCDetails.ItemInId),
            ItemStocks = Db.Queryable<MesInvItemStocks>()
                .Where(s => s.ItemBarcode == query.barcode).ToList()
        };

        return entity;
    }

    public MesInvItemIns GetMesInvItemIns(decimal id)
    {
        return Db.Queryable<MesInvItemIns>()
            .Where(s => s.Id == id).Single();
    }

    public List<MesInvItemInCDetails> GetMesInvItemInCDetailsList(decimal id)
    {
        return Db.Queryable<MesInvItemInCDetails, MesUnit>((a, b) =>
                new JoinQueryInfos(JoinType.Inner, a.Unit == b.Id.ToString()))
            .Where((a, b) => a.ItemInId == id)
            .Select((a, b) => new MesInvItemInCDetails
            {
                ItemBarcode = a.ItemBarcode,
                ItemNo = a.ItemNo,
                ItemSname = a.ItemSname,
                DepotSectionCode = a.DepotSectionCode,
                Quantity = a.Quantity,
                Unit = b.Fname,
                Remark = a.Remark
            })
            .ToList();
    }

    private void InsertInventoryDetails(decimal itemInId, string billNo,
        MesInvItemBarcodes barcodeInfo, string depotCode, string sectionCode,
        string user)
    {
        // Insert inventory details record
        var executeCommand = Db.Insertable(new MesInvItemInCDetails
        {
            ItemInId = itemInId,
            BillNo = billNo,
            ItemBarcode = barcodeInfo.ItemBarcode,
            Quantity = barcodeInfo.Quantity,
            BarcodeFlag = 1,
            EpFlag = 1,
            WorkType = 1,
            ItemNo = barcodeInfo.ItemNo,
            LotNo = barcodeInfo.LotNo,
            SuppNo = barcodeInfo.SuppNo,
            DepotCode = depotCode,
            DepotSectionCode = sectionCode,
            ItemSname = barcodeInfo.ItemSname,
            Unit = barcodeInfo.Unit,
            CreateBy = user,
            CreateDate = DateTime.Now,
            LastupdateBy = user,
            LastupdateDate = DateTime.Now,
            Remark = barcodeInfo.Memo,
            Factory = Factory,
            Company = Company,
            Ebeln = barcodeInfo.Mblnr,
            EbelnLineNo = barcodeInfo.Zeile,
            WorkNo = barcodeInfo.WorkNo,
            WorkLine = barcodeInfo.WorkLine,
            CbillNo = barcodeInfo.BillNo,
            UrgentFlag = barcodeInfo.UrgentFlag,
            BoardStyle = barcodeInfo.BoardStyle,
            TaskNo = barcodeInfo.TaskNo
        }).ExecuteCommand();

        if (executeCommand <= 0) throw new Exception("写入失败");
    }

    private decimal GetOrCreateInventoryItemInId(MesInvItemBarcodes barcodeInfo,
        string depotCode, string userName, out string billNo)
    {
        var inventory = Db.Queryable<MesInvItemIns>()
            .Where(d => d.InsDate >= DateTime.Today &&
                        d.InsDate < DateTime.Today.AddDays(1) &&
                        d.Sapstatus == 0 &&
                        d.Status == 0 &&
                        d.TransctionNo == transactionNo.ToString() &&
                        d.CbillNo == barcodeInfo.BillNo &&
                        d.SuppNo == barcodeInfo.SuppNo &&
                        d.DepotsCode == depotCode)
            .First();
        if (inventory != null)
        {
            billNo = inventory.BillNo;
            return inventory.Id;
        }

        var sql =
            $"SELECT getbillcode1('{Factory}','{Company}','QCRK') FROM DUAL;";
        billNo = Db.Ado.SqlQuerySingle<string>(sql);

        var executeReturnIdentity = Db.Insertable(new MesInvItemIns
        {
            BillNo = billNo,
            BillTypeId = billTypeId,
            InsDate = DateTime.Now,
            DepotsCode = depotCode,
            TransctionNo = transactionNo.ToString(),
            SuppNo = barcodeInfo.SuppNo,
            CreateBy = userName,
            CreateDate = DateTime.Now,
            LastupdateBy = userName,
            LastupdateDate = DateTime.Now,
            Factory = Factory,
            Company = Company,
            UrgentFlag = barcodeInfo.UrgentFlag,
            CbillNo = barcodeInfo.BillNo,
            Fstatus = 0
        }).ExecuteReturnIdentity();

        return executeReturnIdentity;
    }

    private MesInvItemBarcodes GetBarcodeInfo(string itemBarcode)
    {
        // Get barcode information
        return Db.Queryable<MesInvItemBarcodes>()
            .First(b => b.ItemBarcode == itemBarcode);
    }

    private int CheckBarcodeInStock(string itemBarcode)
    {
        // Check if the barcode is in stock
        return Db.Queryable<MesInvItemStocks>()
            .Where(stock => stock.ItemBarcode == itemBarcode).Count();
    }


    private int CheckBarcodeAlreadyReceived(string itemBarcode)
    {
        // Check if the barcode is already received
        return Db.Queryable<MesInvItemIns, MesInvItemInCDetails>(
                (ins, details) => new JoinQueryInfos(JoinType.Inner,
                    ins.Id == details.ItemInId))
            .Where((ins, details) => details.ItemBarcode == itemBarcode)
            .Count();
    }

    private string GetDepotCode(string sectionCode)
    {
        // This would be your query to get depot code based on section code
        return Db.Queryable<MesDepotSections, MesDepots>((d, t) =>
                new JoinQueryInfos(JoinType.Inner, d.Zuid.ToString() == t.Zuid))
            .Where((d, t) => d.DepotSectionCode == sectionCode)
            .Select<string>((d, t) => t.DepotCode)
            .First();
    }


    private void InsertStockRecord(MesInvItemBarcodes barcodeInfo,
        string depotCode,
        string sectionCode, string user)
    {
        var executeCommand = Db.Insertable(new MesInvItemStocks
        {
            TaskNo = barcodeInfo.TaskNo,
            ItemBarcode = barcodeInfo.ItemBarcode,
            ItemNo = barcodeInfo.ItemNo,
            LotNo = barcodeInfo.LotNo,
            Quantity = barcodeInfo.Quantity,
            EpFlag = barcodeInfo.EpFlag,
            DepotsCode = depotCode,
            DepotSectionsCode = sectionCode,
            CheckDate = barcodeInfo.CreateDate,
            IndepDate = barcodeInfo.CreateDate,
            Factory = Factory,
            Company = Company,
            BoardStyle = barcodeInfo.BoardStyle,
            WorkNo = barcodeInfo.WorkNo,
            WorkLine = barcodeInfo.WorkLine,
            SuppNo = barcodeInfo.SuppNo
        }).ExecuteCommand();

        if (executeCommand <= 0) throw new Exception("写入失败");
    }

    private void InsertBusinessRecord(MesInvItemBarcodes barcodeInfo,
        string depotCode, string sectionCode, string billNo, string user)
    {
        // Insert business record
        var executeCommand = Db.Insertable(new MesInvBusiness2
        {
            Status = 1,
            BillTypeId = billTypeId,
            TransactionCode = "601",
            BusinessType = 1,
            ItemBarcode = barcodeInfo.ItemBarcode,
            ItemNo = barcodeInfo.ItemNo,
            LotNo = barcodeInfo.LotNo,
            EpFlag = 1,
            Quantity = barcodeInfo.Quantity,
            ToInvDepotsCode = depotCode,
            ToInvDepotSectionsCode = sectionCode,
            CreateBy = user,
            CreateDate = DateTime.Now,
            LastupdateBy = user,
            LastupdateDate = DateTime.Now,
            Factory = Factory,
            Company = Company,
            TaskNo = barcodeInfo.TaskNo,
            BillNo = billNo,
            WorkNo = barcodeInfo.WorkNo,
            WorkLine = barcodeInfo.WorkLine,
            SuppNo = barcodeInfo.SuppNo
        }).ExecuteCommand();

        if (executeCommand <= 0) throw new Exception("写入失败");
    }
}