using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.Supply;

/// <summary>
/// 采购申请合并服务。支持本次生成 PR 合并与租户待处理历史 PR 合并（对齐旧 PrAutoMerge2）。
/// </summary>
public class PurchaseRequestMergeService : ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly NumberRuleService _numberRuleService;

    public PurchaseRequestMergeService(ISqlSugarClient db, NumberRuleService numberRuleService)
    {
        _db = db;
        _numberRuleService = numberRuleService;
    }

    public PurchaseRequestMergeResult MergeGeneratedRequests(List<PurchaseRequestMain> requests) =>
        MergeInMemory(requests);

    /// <summary>合并租户内待处理历史 PR（state=1，5 周窗口）。</summary>
    public async Task<PurchaseRequestHistoricalMergeResult> MergeTenantPendingAsync(long tenantId, string account)
    {
        var tomorrow = DateTime.Today.AddDays(1);
        var windowEnd = GetWeekStart(DateTime.Today)?.AddDays(35) ?? DateTime.Today.AddDays(35);

        var pending = await _db.Ado.SqlQueryAsync<PurchaseRequestMain>(
            """
            SELECT
                Id, pr_billno AS PrBillNo, pr_purchaseid AS PrPurchaseId,
                pr_purchasenumber AS PrPurchaseNumber, pr_purchasename AS PrPurchaseName,
                pr_purchaser AS PrPurchaser, pr_purchaser_num AS PrPurchaserNum,
                pr_rqty AS PrRqty, pr_aqty AS PrAqty, pr_sqty AS PrSqty,
                icitem_id AS IcitemId, icitem_name AS IcitemName,
                pr_ssend_date AS PrSsendDate, pr_sarrive_date AS PrSarriveDate,
                pr_unit AS PrUnit, state AS State, pr_type AS PrType,
                currencytype AS CurrencyType, tenant_id AS TenantId,
                factory_id AS FactoryId, org_id AS OrgId, company_id AS CompanyId,
                IsRequireGoods, supplier_type AS SupplierType, IsDeleted
            FROM srm_pr_main
            WHERE tenant_id = @TenantId
              AND IFNULL(IsDeleted, 0) = 0
              AND IFNULL(state, 0) = 1
              AND IFNULL(analogcalcversion, '') = ''
              AND IFNULL(refer_pr_billno, '') = ''
              AND pr_ssend_date IS NOT NULL
              AND pr_ssend_date <= @WindowEnd
            ORDER BY pr_ssend_date, Id
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WindowEnd", windowEnd));

        if (pending.Count <= 1)
        {
            return new PurchaseRequestHistoricalMergeResult
            {
                PendingCount = pending.Count,
                MergedGroupCount = 0
            };
        }

        foreach (var pr in pending)
        {
            if (pr.PrSsendDate.HasValue && pr.PrSsendDate.Value.Date < tomorrow)
            {
                var shift = (tomorrow - pr.PrSsendDate.Value.Date).Days;
                pr.PrSsendDate = tomorrow;
                if (pr.PrSarriveDate.HasValue)
                    pr.PrSarriveDate = pr.PrSarriveDate.Value.AddDays(shift);
            }
        }

        var groups = pending
            .GroupBy(x => new
            {
                x.TenantId,
                x.CompanyId,
                x.FactoryId,
                x.IcitemId,
                x.PrPurchaseId,
                x.IsRequireGoods,
                SupplierType = x.SupplierType ?? string.Empty,
                WeekStart = GetWeekStart(x.PrSsendDate)
            })
            .Where(g => g.Count() > 1)
            .ToList();

        if (groups.Count == 0)
        {
            return new PurchaseRequestHistoricalMergeResult
            {
                PendingCount = pending.Count,
                MergedGroupCount = 0
            };
        }

        var now = DateTime.Now;
        var createdCount = 0;
        var closedCount = 0;
        var reducedCount = 0;

        foreach (var group in groups)
        {
            var rows = group.OrderBy(x => x.PrSsendDate).ThenBy(x => x.PrSarriveDate).ThenBy(x => x.Id).ToList();
            var merged = BuildMergedRow(rows, account, now);
            var numbers = await _numberRuleService.NextBatchInCurrentTransactionAsync(
                "PR", merged.TenantId.ToString(), 1, account);
            merged.PrBillNo = numbers.FirstOrDefault()?.Trim()
                ?? throw Oops.Oh("历史 PR 合并编号生成失败");
            merged.Id = YitIdHelper.NextId();

            await InsertMergedPurchaseRequestAsync(merged);

            foreach (var old in rows)
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE srm_pr_main
                    SET state = 0,
                        refer_pr_billno = @NewBillNo,
                        update_by_name = @User,
                        update_time = @Now
                    WHERE Id = @Id AND tenant_id = @TenantId
                    """,
                    new SugarParameter("@NewBillNo", merged.PrBillNo),
                    new SugarParameter("@User", account),
                    new SugarParameter("@Now", now),
                    new SugarParameter("@Id", old.Id),
                    new SugarParameter("@TenantId", tenantId));

                await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE srm_po_occupy
                    SET polist_id = @NewPrId,
                        update_by_name = @User,
                        update_time = @Now
                    WHERE tenant_id = @TenantId AND polist_id = @OldPrId
                    """,
                    new SugarParameter("@NewPrId", merged.Id),
                    new SugarParameter("@User", account),
                    new SugarParameter("@Now", now),
                    new SugarParameter("@TenantId", tenantId),
                    new SugarParameter("@OldPrId", old.Id));
            }

            createdCount++;
            closedCount += rows.Count;
            reducedCount += rows.Count - 1;
        }

        return new PurchaseRequestHistoricalMergeResult
        {
            PendingCount = pending.Count,
            MergedGroupCount = createdCount,
            ClosedPrCount = closedCount,
            CreatedPrCount = createdCount,
            ReducedCount = reducedCount
        };
    }

    private static PurchaseRequestMergeResult MergeInMemory(List<PurchaseRequestMain> requests)
    {
        if (requests.Count <= 1)
        {
            return new PurchaseRequestMergeResult
            {
                Requests = requests,
                OriginalCount = requests.Count,
                MergedCount = requests.Count
            };
        }

        var merged = requests
            .GroupBy(x => new
            {
                x.TenantId,
                x.CompanyId,
                x.FactoryId,
                x.IcitemId,
                x.PrPurchaseId,
                x.IsRequireGoods,
                SupplierType = x.SupplierType ?? string.Empty,
                WeekStart = GetWeekStart(x.PrSsendDate)
            })
            .Select(group =>
            {
                var rows = group.OrderBy(x => x.PrSsendDate).ThenBy(x => x.PrSarriveDate).ThenBy(x => x.Id).ToList();
                return BuildMergedRow(rows, rows.First().CreateByName ?? "system", DateTime.Now);
            })
            .ToList();

        return new PurchaseRequestMergeResult
        {
            Requests = merged,
            OriginalCount = requests.Count,
            MergedCount = merged.Count
        };
    }

    private static PurchaseRequestMain BuildMergedRow(List<PurchaseRequestMain> rows, string account, DateTime now)
    {
        var first = rows.First();
        return new PurchaseRequestMain
        {
            Id = first.Id,
            PrMono = first.PrMono,
            EntityId = first.EntityId,
            PrPurchaseId = first.PrPurchaseId,
            PrPurchaseNumber = first.PrPurchaseNumber,
            PrPurchaseName = first.PrPurchaseName,
            PrPurchaser = first.PrPurchaser,
            PrPurchaserNum = first.PrPurchaserNum,
            PrRqty = rows.Sum(x => x.PrRqty ?? 0),
            PrAqty = rows.Sum(x => x.PrAqty ?? 0),
            PrSqty = rows.Sum(x => x.PrSqty ?? 0),
            IcitemId = first.IcitemId,
            IcitemName = first.IcitemName,
            PrSsendDate = rows.Min(x => x.PrSsendDate),
            PrSarriveDate = rows.Min(x => x.PrSarriveDate),
            PrUnit = first.PrUnit,
            State = 1,
            PrType = first.PrType ?? 3,
            CurrencyType = first.CurrencyType,
            CreateByName = account,
            CreateTime = now,
            UpdateByName = account,
            UpdateTime = now,
            TenantId = first.TenantId,
            FactoryId = first.FactoryId,
            OrgId = first.OrgId,
            CompanyId = first.CompanyId,
            IsDeleted = false,
            IsRequireGoods = first.IsRequireGoods,
            SupplierType = first.SupplierType
        };
    }

    private async Task InsertMergedPurchaseRequestAsync(PurchaseRequestMain pr)
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO srm_pr_main
            (Id,pr_billno,pr_mono,entity_id,pr_purchaseid,pr_purchasenumber,pr_purchasename,pr_purchaser,pr_purchaser_num,
             pr_rqty,pr_aqty,pr_sqty,icitem_id,icitem_name,pr_ssend_date,pr_sarrive_date,pr_unit,state,pr_type,currencytype,
             create_by_name,create_time,update_by_name,update_time,tenant_id,factory_id,org_id,IsDeleted,company_id,IsRequireGoods,supplier_type)
            VALUES
            (@Id,@PrBillNo,@PrMono,@EntityId,@PrPurchaseId,@PrPurchaseNumber,@PrPurchaseName,@PrPurchaser,@PrPurchaserNum,
             @PrRqty,@PrAqty,@PrSqty,@IcitemId,@IcitemName,@PrSsendDate,@PrSarriveDate,@PrUnit,@State,@PrType,@CurrencyType,
             @CreateByName,@CreateTime,@UpdateByName,@UpdateTime,@TenantId,@FactoryId,@OrgId,0,@CompanyId,@IsRequireGoods,@SupplierType)
            """,
            new SugarParameter("@Id", pr.Id),
            new SugarParameter("@PrBillNo", pr.PrBillNo),
            new SugarParameter("@PrMono", pr.PrMono ?? (object)DBNull.Value),
            new SugarParameter("@EntityId", pr.EntityId ?? (object)DBNull.Value),
            new SugarParameter("@PrPurchaseId", pr.PrPurchaseId),
            new SugarParameter("@PrPurchaseNumber", pr.PrPurchaseNumber),
            new SugarParameter("@PrPurchaseName", pr.PrPurchaseName),
            new SugarParameter("@PrPurchaser", pr.PrPurchaser),
            new SugarParameter("@PrPurchaserNum", pr.PrPurchaserNum),
            new SugarParameter("@PrRqty", pr.PrRqty),
            new SugarParameter("@PrAqty", pr.PrAqty),
            new SugarParameter("@PrSqty", pr.PrSqty),
            new SugarParameter("@IcitemId", pr.IcitemId),
            new SugarParameter("@IcitemName", pr.IcitemName),
            new SugarParameter("@PrSsendDate", pr.PrSsendDate),
            new SugarParameter("@PrSarriveDate", pr.PrSarriveDate),
            new SugarParameter("@PrUnit", pr.PrUnit),
            new SugarParameter("@State", pr.State ?? 1),
            new SugarParameter("@PrType", pr.PrType ?? 3),
            new SugarParameter("@CurrencyType", pr.CurrencyType),
            new SugarParameter("@CreateByName", pr.CreateByName),
            new SugarParameter("@CreateTime", pr.CreateTime),
            new SugarParameter("@UpdateByName", pr.UpdateByName),
            new SugarParameter("@UpdateTime", pr.UpdateTime),
            new SugarParameter("@TenantId", pr.TenantId),
            new SugarParameter("@FactoryId", pr.FactoryId),
            new SugarParameter("@OrgId", pr.OrgId),
            new SugarParameter("@CompanyId", pr.CompanyId ?? 1000),
            new SugarParameter("@IsRequireGoods", pr.IsRequireGoods),
            new SugarParameter("@SupplierType", pr.SupplierType));
    }

    private static DateTime? GetWeekStart(DateTime? value)
    {
        if (!value.HasValue) return null;
        var date = value.Value.Date;
        var diff = ((int)date.DayOfWeek + 6) % 7;
        return date.AddDays(-diff);
    }

    /// <summary>
    /// 保留各工单独立 PR + 创建合并 PR（数量 = 总需求 - 库存 - 在途）。
    /// 仅当同组有 2 条以上 PR 时才创建合并 PR。
    /// </summary>
    public async Task<SplitMergeResult> SplitMergeWithRecalcAsync(
        List<PurchaseRequestMain> requests, string account)
    {
        var result = new SplitMergeResult();
        if (requests.Count == 0) return result;

        var groups = requests
            .GroupBy(x => new
            {
                x.TenantId,
                x.CompanyId,
                x.FactoryId,
                x.IcitemId,
                x.PrPurchaseId,
                x.IsRequireGoods,
                SupplierType = x.SupplierType ?? string.Empty,
                WeekStart = GetWeekStart(x.PrSsendDate)
            })
            .ToList();

        var now = DateTime.Now;
        var mergedList = new List<PurchaseRequestMain>();

        foreach (var group in groups)
        {
            var rows = group.OrderBy(x => x.PrSsendDate).ThenBy(x => x.PrSarriveDate).ThenBy(x => x.Id).ToList();

            // ① 保留各工单独立 PR
            result.IndividualRequests.AddRange(rows);

            // ② 仅当同组有 2+ 条时才创建合并 PR
            if (rows.Count >= 2)
            {
                var merged = BuildMergedRow(rows, account, now);

                // 重新计算合并 PR 数量：总需求 - 库存 - 在途
                var totalNeed = rows.Sum(x => x.PrRqty ?? 0);
                var stock = await QueryStockQtyAsync(rows[0].IcitemId, rows[0].TenantId);
                var transit = await QueryOpenPurchaseQtyAsync(rows[0].IcitemId, rows[0].TenantId);
                var actualShortage = Math.Max(0, totalNeed - stock - transit);

                merged.PrRqty = actualShortage;
                merged.PrAqty = actualShortage;
                merged.PrSqty = actualShortage;

                mergedList.Add(merged);
            }
        }

        result.MergedRequests = mergedList;
        return result;
    }

    private async Task<decimal> QueryStockQtyAsync(long icitemId, long tenantId)
    {
        var itemNumber = await _db.Ado.GetStringAsync(
            "SELECT number FROM ic_item WHERE Id = @Id LIMIT 1",
            new SugarParameter("@Id", icitemId));
        if (string.IsNullOrWhiteSpace(itemNumber)) return 0;

        return await _db.Ado.GetDecimalAsync(
            """
            SELECT COALESCE(SUM(
                CASE WHEN AvailStatusQty IS NOT NULL THEN AvailStatusQty
                     WHEN QtyOnHand IS NOT NULL THEN QtyOnHand ELSE 0 END
            ), 0)
            FROM InvMaster
            WHERE ItemNum = @ItemNum AND (tenant_id = @TenantId OR @TenantId = 0)
            """,
            new SugarParameter("@ItemNum", itemNumber),
            new SugarParameter("@TenantId", tenantId));
    }

    private async Task<decimal> QueryOpenPurchaseQtyAsync(long icitemId, long tenantId)
    {
        var itemNumber = await _db.Ado.GetStringAsync(
            "SELECT number FROM ic_item WHERE Id = @Id LIMIT 1",
            new SugarParameter("@Id", icitemId));
        if (string.IsNullOrWhiteSpace(itemNumber)) return 0;

        return await _db.Ado.GetDecimalAsync(
            """
            SELECT COALESCE(SUM(
                GREATEST(IFNULL(d.QtyOrded, 0) - IFNULL(d.QtyReceived, 0), 0)
            ), 0)
            FROM PurOrdDetail d
            INNER JOIN PurOrdMaster m ON m.RecID = d.PurOrdRecID
            WHERE d.ItemNum = @ItemNum AND m.tenant_id = @TenantId
              AND IFNULL(d.IsActive, 1) = 1 AND IFNULL(m.IsActive, 1) = 1
            """,
            new SugarParameter("@ItemNum", itemNumber),
            new SugarParameter("@TenantId", tenantId));
    }
}

public sealed class SplitMergeResult
{
    public List<PurchaseRequestMain> IndividualRequests { get; set; } = new();
    public List<PurchaseRequestMain> MergedRequests { get; set; } = new();
}

public sealed class PurchaseRequestMergeResult
{
    public List<PurchaseRequestMain> Requests { get; set; } = new();
    public int OriginalCount { get; set; }
    public int MergedCount { get; set; }
    public int ReducedCount => Math.Max(OriginalCount - MergedCount, 0);
}

public sealed class PurchaseRequestHistoricalMergeResult
{
    public int PendingCount { get; set; }
    public int MergedGroupCount { get; set; }
    public int ClosedPrCount { get; set; }
    public int CreatedPrCount { get; set; }
    public int ReducedCount { get; set; }
}