namespace Admin.NET.Plugin.AiDOP.Supply;

/// <summary>
/// 采购申请转 PO 服务。处理待处理 PR，生成 PurOrdMaster/Detail 并改挂 srm_po_occupy。
/// </summary>
public class PurchaseOrderTransferService : ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly NumberRuleService _numberRuleService;
    private readonly UserManager _userManager;

    public PurchaseOrderTransferService(ISqlSugarClient db, NumberRuleService numberRuleService, UserManager userManager)
    {
        _db = db;
        _numberRuleService = numberRuleService;
        _userManager = userManager;
    }

    public async Task<PurchaseOrderTransferResult> TransferGeneratedRequireGoodsAsync(List<PurchaseRequestMain> requests, string account)
    {
        var candidates = requests
            .Where(x => (x.State ?? 0) == 1
                        && !string.IsNullOrWhiteSpace(x.PrBillNo))
            .ToList();
        if (candidates.Count == 0) return new PurchaseOrderTransferResult();

        var createdOrders = new List<string>();
        var transferredPrIds = new List<long>();
        var poOccupyRehangedCount = 0;
        var groups = candidates
            .GroupBy(x => new
            {
                x.TenantId,
                x.CompanyId,
                x.FactoryId,
                x.PrPurchaseId,
                x.PrPurchaseNumber,
                x.PrPurchaseName,
                x.IsRequireGoods,
                SupplierType = x.SupplierType ?? string.Empty
            })
            .ToList();

        foreach (var group in groups)
        {
            var now = DateTime.Now;
            var rows = group.OrderBy(x => x.PrSarriveDate).ThenBy(x => x.PrBillNo).ToList();

            var supplier = await LoadSupplierContextAsync(group.Key.PrPurchaseId, rows[0].IcitemId, group.Key.TenantId);
            var supplierCode = supplier?.SupplierNumber ?? group.Key.PrPurchaseNumber;
            var supplierType = string.IsNullOrWhiteSpace(group.Key.SupplierType)
                ? supplier?.SupplierType ?? string.Empty
                : group.Key.SupplierType;
            var isOutsource = string.Equals(supplierType, "委外", StringComparison.OrdinalIgnoreCase);
            var isRequireGoods = group.Key.IsRequireGoods == 1;

            // 三类型分流：IsRequireGoods=0→PO(采购订单)；IsRequireGoods=1+委外→PW(委外加工订单)；IsRequireGoods=1+非委外→DO(要货令)
            string ruleCode, poType, reqBy, usage;
            if (!isRequireGoods)
            {
                ruleCode = "PO"; poType = "po"; reqBy = "PO"; usage = supplierType;
            }
            else if (isOutsource)
            {
                ruleCode = "PW"; poType = "PW"; reqBy = "DO"; usage = "委外加工";
            }
            else
            {
                ruleCode = "DO"; poType = "po"; reqBy = "DO"; usage = supplierType;
            }
            var buyer = ResolveBuyer(supplierType);

            var purOrdNumbers = await _numberRuleService.NextBatchInCurrentTransactionAsync(
                ruleCode, group.Key.TenantId.ToString(), 1, account);
            if (purOrdNumbers.Count == 0 || string.IsNullOrWhiteSpace(purOrdNumbers[0]))
                throw Oops.Oh($"当前{ruleCode}单号生成失败，请检查{ruleCode}编号规则维护。Domain={group.Key.TenantId}");
            var purOrd = purOrdNumbers[0].Trim();

            await InsertPurchaseOrderMasterAsync(purOrd, poType, reqBy, usage, buyer, supplierCode, now, account, group.Key.TenantId);
            var masterId = await _db.Ado.GetIntAsync(
                "SELECT IFNULL(MAX(RecID),0) FROM PurOrdMaster WHERE PurOrd=@PurOrd",
                new SugarParameter("@PurOrd", purOrd));
            if (masterId <= 0) throw Oops.Oh("采购订单主表生成失败。");

            for (var lineIndex = 0; lineIndex < rows.Count; lineIndex++)
            {
                var pr = rows[lineIndex];
                var line = lineIndex + 1;
                var (detailRecId, itemNum) = await InsertPurchaseOrderDetailAsync(purOrd, poType, masterId, line, pr, account);
                if (detailRecId > 0)
                    poOccupyRehangedCount += await RehangPoOccupyFromPrToDetailAsync(pr.Id, detailRecId, pr.TenantId, account);

                // 委外加工订单：BOM展开写入PurOrdDetailBatch
                if (isOutsource && detailRecId > 0 && !string.IsNullOrWhiteSpace(itemNum))
                {
                    await InsertPurOrdDetailBatchAsync(purOrd, poType, line, detailRecId, itemNum,
                        pr.PrAqty ?? pr.PrSqty ?? pr.PrRqty ?? 0, pr.TenantId, account, now);
                }

                pr.State = 4;
                pr.UpdateByName = account;
                pr.UpdateTime = now;
                transferredPrIds.Add(pr.Id);
            }

            createdOrders.Add(purOrd);
        }

        return new PurchaseOrderTransferResult
        {
            CreatedOrderCount = createdOrders.Count,
            TransferredPrCount = transferredPrIds.Count,
            PoOccupyRehangedCount = poOccupyRehangedCount,
            CreatedOrders = createdOrders,
            TransferredPrIds = transferredPrIds
        };
    }

    private async Task<SupplierContextRow?> LoadSupplierContextAsync(long? supplierId, long? icitemId, long tenantId)
    {
        if (supplierId is null or <= 0) return null;
        var rows = await _db.Ado.SqlQueryAsync<SupplierContextRow>(
            """
            SELECT
                IFNULL(sp.supplier_number, '') AS SupplierNumber,
                IFNULL(sp.supplier_type, '') AS SupplierType
            FROM srm_purchase sp
            WHERE sp.tenant_id = @TenantId
              AND sp.supplier_id = @SupplierId
              AND (@IcitemId IS NULL OR sp.icitem_id = @IcitemId)
            ORDER BY sp.Id
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@SupplierId", supplierId),
            new SugarParameter("@IcitemId", icitemId));
        return rows.FirstOrDefault();
    }

    private async Task<int> RehangPoOccupyFromPrToDetailAsync(long prId, int detailRecId, long tenantId, string account)
    {
        return await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE srm_po_occupy
            SET polist_id = @DetailRecId,
                update_by_name = @User,
                update_time = @Now
            WHERE tenant_id = @TenantId
              AND polist_id = @PrId
            """,
            new SugarParameter("@DetailRecId", detailRecId),
            new SugarParameter("@User", account),
            new SugarParameter("@Now", DateTime.Now),
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@PrId", prId));
    }

    private async Task InsertPurchaseOrderMasterAsync(
        string purOrd,
        string poType,
        string reqBy,
        string? usage,
        string buyer,
        string? supplierCode,
        DateTime now,
        string account,
        long tenantId)
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO PurOrdMaster
            (
                Confirming, CreditTermsInt, Disc, ExchRate, EstVal, ExchRate1, ExchRate2,
                FixedPrice, FixedRate, Frt, PartialOK, AmtPrepaid, PrintPO, PST, Recurr,
                `Release`, Revision, Scheduled, ServiceCharge, SpecialCharge, Taxable,
                Tax1, Tax2, Tax3, TransportDays, IsActive, IsConfirm, Potype, IsChanged,
                TaxIn, Amt, IsPriceChanged,
                Buyer, Domain, PurOrd, OrdDate, ReqBy, Status, Supp, CreateUser, CreateTime,
                UpdateUser, UpdateTime, `Usage`, FSTID, Typed, tenant_id
            )
            VALUES
            (
                0, 0, 0, 1, 0, 1, 1,
                0, 0, 0, 1, 0, 0, 0, 0,
                0, 0, 0, 0, 0, 1,
                0, 0, 0, 0, 1, 1, @PoType, 0,
                1, 0, 0,
                @Buyer, @Domain, @PurOrd, @Now, @ReqBy, '', @Supp, @CreateUser, @Now,
                @UpdateUser, @Now, @Usage, @FSTID, @Typed, @TenantId
            )
            """,
            new SugarParameter("@PoType", poType),
            new SugarParameter("@ReqBy", reqBy),
            new SugarParameter("@Buyer", buyer),
            new SugarParameter("@Domain", tenantId.ToString()),
            new SugarParameter("@PurOrd", purOrd),
            new SugarParameter("@Now", now),
            new SugarParameter("@Supp", supplierCode),
            new SugarParameter("@CreateUser", account),
            new SugarParameter("@UpdateUser", account),
            new SugarParameter("@Usage", usage),
            new SugarParameter("@FSTID", string.Equals(usage, "VMI", StringComparison.OrdinalIgnoreCase) ? "3" : string.Empty),
            new SugarParameter("@Typed", poType == "PW" ? "s" : string.Empty),
            new SugarParameter("@TenantId", tenantId <= 0 ? null : tenantId));
    }

    private async Task<(int detailRecId, string itemNum)> InsertPurchaseOrderDetailAsync(
        string purOrd, string poType, int masterId, int line, PurchaseRequestMain pr, string account)
    {
        var item = (await _db.Ado.SqlQueryAsync<ItemLookupRow>(
            """
            SELECT
                COALESCE(NULLIF(im.ItemNum,''), ic.number) AS ItemNum,
                COALESCE(NULLIF(im.Descr,''), ic.name) AS Descr,
                COALESCE(NULLIF(im.UM,''), ic.unit) AS UM,
                IFNULL(im.Location, '') AS Location,
                IFNULL(im.Rev, '') AS Rev,
                IFNULL(im.Drawing, '') AS Drawing
            FROM ic_item ic
            LEFT JOIN ItemMaster im ON ic.number = im.ItemNum
            WHERE ic.Id = @IcitemId
            LIMIT 1
            """,
            new SugarParameter("@IcitemId", pr.IcitemId))).FirstOrDefault();

        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO PurOrdDetail
            (
                QtyBO, RctCost, CreditTermsInt, UpdateCurrentCost, CumReceived1, CumReceived2,
                CumReceived3, CumReceived4, Disc, FixedPrice, InspectReq, SingleLot, SupplyPer,
                PurOrd, PST, PackingSlipQty, PayUMConv, PurCost, RctQty, QtyOrded, QtyReceived,
                QtyReturned, Active, QtyReleased, RctUMConversion, Scheduled, ScheduledChanged,
                SchedMRPReq, SafetyDays, SafetyHours, StdCost, Taxable, TaxIn, MaxTaxableAmt,
                TransportHours, UMConversion, VAT, IsActive, IsConfirm, Potype, IsChanged,
                TaxRate, IsRounding, ReceiptQty, BarCodeQty, IsClosed, QtyReturnedRefund, CumQtyBO,
                Line, ItemNum, Descr, UM, Rev, Drawing, Location, DueDate, NeedDate, LotSerial, PurOrdRecID, Status, Req,
                CreateUser, CreateTime, UpdateUser, UpdateTime, tenant_id
            )
            VALUES
            (
                0, 0, 0, 0, 0, 0,
                0, 0, 0, 0, 0, 0, 0,
                @PurOrd, 0, 0, 1, 0, 0, @QtyOrded, 0,
                0, 1, 0, 1, 0, 0,
                0, 0, 0, 0, 1, 1, 0,
                0, 1, 0, 1, 1, @PoType, 0,
                0, 0, 0, 0, 0, 0, 0,
                @Line, @ItemNum, @Descr, @UM, @Rev, @Drawing, @Location, @DueDate, @NeedDate, '', @PurOrdRecID, 'R', @Req,
                @CreateUser, @Now, @UpdateUser, @Now, @TenantId
            )
            """,
            new SugarParameter("@PurOrd", purOrd),
            new SugarParameter("@PoType", poType),
            new SugarParameter("@QtyOrded", pr.PrAqty ?? pr.PrSqty ?? pr.PrRqty ?? 0),
            new SugarParameter("@Line", line),
            new SugarParameter("@ItemNum", item?.ItemNum ?? pr.IcitemName ?? string.Empty),
            new SugarParameter("@Descr", item?.Descr ?? pr.IcitemName ?? string.Empty),
            new SugarParameter("@UM", item?.UM ?? pr.PrUnit ?? string.Empty),
            new SugarParameter("@Rev", item?.Rev ?? string.Empty),
            new SugarParameter("@Drawing", item?.Drawing ?? string.Empty),
            new SugarParameter("@Location", string.IsNullOrWhiteSpace(item?.Location) ? "1001" : item.Location),
            new SugarParameter("@DueDate", pr.PrSarriveDate),
            new SugarParameter("@NeedDate", pr.PrSarriveDate),
            new SugarParameter("@PurOrdRecID", masterId),
            new SugarParameter("@Req", pr.PrBillNo),
            new SugarParameter("@CreateUser", account),
            new SugarParameter("@UpdateUser", account),
            new SugarParameter("@Now", DateTime.Now),
            new SugarParameter("@TenantId", pr.TenantId <= 0 ? null : pr.TenantId));

        var detailRecId = await _db.Ado.GetIntAsync(
            "SELECT IFNULL(MAX(RecID),0) FROM PurOrdDetail WHERE PurOrd=@PurOrd AND Line=@Line",
            new SugarParameter("@PurOrd", purOrd),
            new SugarParameter("@Line", line));

        return (detailRecId, item?.ItemNum ?? pr.IcitemName ?? string.Empty);
    }

    /// <summary>委外加工订单BOM展开：查询ProductStructureMaster并写入PurOrdDetailBatch。</summary>
    private async Task InsertPurOrdDetailBatchAsync(
        string purOrd, string poType, int line, int detailRecId, string parentItem,
        decimal prQty, long tenantId, string account, DateTime now)
    {
        var components = await _db.Ado.SqlQueryAsync<BomComponentRow>(
            """
            SELECT
                psm.ComponentItem AS SuppItem,
                CAST(psm.Qty AS DECIMAL(18,5)) AS QtyPerUnit,
                IFNULL(psm.UM, im.UM) AS UM,
                IFNULL(im.ItemNum, psm.ComponentItem) AS ItemNum,
                IFNULL(im.Location, '') AS Location
            FROM ProductStructureMaster psm
            LEFT JOIN ItemMaster im ON psm.ComponentItem = im.ItemNum
            WHERE psm.ParentItem = @ParentItem
            ORDER BY psm.ComponentItem
            """,
            new SugarParameter("@ParentItem", parentItem));

        var batchNo = 1;
        foreach (var comp in components)
        {
            if (string.IsNullOrWhiteSpace(comp.SuppItem)) continue;
            var batchQty = Math.Round(prQty * comp.QtyPerUnit, 5);
            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO PurOrdDetailBatch
                (
                    Domain, PurOrd, Potype, Line, Batch, ItemNum, SuppItem, UM, Location,
                    QtyOrded, QtyBO, QtyReleased, QtyReceived, QtyReturned, LotSerial, PurOrdDetailRecID,
                    CreateUser, CreateTime, UpdateUser, UpdateTime, tenant_id
                )
                VALUES
                (
                    @Domain, @PurOrd, @Potype, @Line, @Batch, @ItemNum, @SuppItem, @UM, @Location,
                    @QtyOrded, @QtyBO, 0, 0, 0, '', @PurOrdDetailRecID,
                    @CreateUser, @CreateTime, @UpdateUser, @UpdateTime, @TenantId
                )
                """,
                new SugarParameter("@Domain", tenantId.ToString()),
                new SugarParameter("@PurOrd", purOrd),
                new SugarParameter("@Potype", poType),
                new SugarParameter("@Line", line),
                new SugarParameter("@Batch", batchNo),
                new SugarParameter("@ItemNum", comp.ItemNum ?? comp.SuppItem),
                new SugarParameter("@SuppItem", comp.SuppItem),
                new SugarParameter("@UM", comp.UM ?? string.Empty),
                new SugarParameter("@Location", string.IsNullOrWhiteSpace(comp.Location) ? "1001" : comp.Location),
                new SugarParameter("@QtyOrded", batchQty),
                new SugarParameter("@QtyBO", batchQty),
                new SugarParameter("@PurOrdDetailRecID", detailRecId),
                new SugarParameter("@CreateUser", account),
                new SugarParameter("@CreateTime", now),
                new SugarParameter("@UpdateUser", account),
                new SugarParameter("@UpdateTime", now),
                new SugarParameter("@TenantId", tenantId <= 0 ? null : tenantId));
            batchNo++;
        }
    }

    private static string ResolveBuyer(string? supplierType)
    {
        return supplierType switch
        {
            "研发" => "130",
            "ECR" => "170",
            _ => "110"
        };
    }

    private sealed class SupplierContextRow
    {
        public string? SupplierNumber { get; set; }
        public string? SupplierType { get; set; }
    }

    private sealed class ItemLookupRow
    {
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? UM { get; set; }
        public string? Location { get; set; }
        public string? Rev { get; set; }
        public string? Drawing { get; set; }
    }

    private sealed class BomComponentRow
    {
        public string? SuppItem { get; set; }
        public decimal QtyPerUnit { get; set; }
        public string? UM { get; set; }
        public string? ItemNum { get; set; }
        public string? Location { get; set; }
    }
}

public sealed class PurchaseOrderTransferResult
{
    public int CreatedOrderCount { get; set; }
    public int TransferredPrCount { get; set; }
    public int PoOccupyRehangedCount { get; set; }
    public List<string> CreatedOrders { get; set; } = new();
    public List<long> TransferredPrIds { get; set; } = new();
}