AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Production/ProductionScheduleGenerationService.cs

namespace Admin.NET.Plugin.AiDOP.Production;

using Admin.NET.Plugin.AiDOP.WorkOrder;

/// <summary>生产排程生成：为待排工单写入 PeriodSequenceDet（工作日历 + 工作中心冲突避让）。</summary>
public class ProductionScheduleGenerationService : ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly WorkOrderKittingCheckService _kittingCheck;

    public ProductionScheduleGenerationService(ISqlSugarClient db, WorkOrderKittingCheckService kittingCheck)
    {
        _db = db;
        _kittingCheck = kittingCheck;
    }

    public async Task<ScheduleGenerationResult> GenerateAsync(long tenantId, string? domain, string account)
    {
        var workOrders = await LoadPendingWorkOrdersAsync(tenantId);
        if (workOrders.Count == 0)
            return new ScheduleGenerationResult { Message = "没有待排产的工单（状态 p/r）" };

        return await ScheduleWorkOrdersAsync(tenantId, domain, account, workOrders);
    }

    public async Task<ScheduleGenerationResult> RegenerateForWorkOrderAsync(
        long tenantId,
        string workOrd,
        string? domain,
        string account)
    {
        var rows = await _db.Ado.SqlQueryAsync<PendingWorkOrderRow>(
            """
            SELECT RecID AS RecId, WorkOrd, ItemNum, `Domain`, QtyOrded, OrdDate, DueDate, Priority, Urgent
            FROM WorkOrdMaster
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd.Trim()));

        var wo = rows.FirstOrDefault();
        if (wo is null)
            return new ScheduleGenerationResult { Message = $"工单 {workOrd} 不存在" };

        return await ScheduleWorkOrdersAsync(tenantId, domain, account, new List<PendingWorkOrderRow> { wo });
    }

    private async Task<ScheduleGenerationResult> ScheduleWorkOrdersAsync(
        long tenantId,
        string? domain,
        string account,
        List<PendingWorkOrderRow> workOrders)
    {
        var now = DateTime.Now;
        var calendar = await LoadWorkCenterCalendarAsync(tenantId);
        var occupiedSlots = await LoadOccupiedSlotsAsync(tenantId);
        var usedCalendar = calendar.Count > 0;

        var scheduledCount = 0;
        var rowCount = 0;
        var skipped = new List<string>();
        // 按优先级顺序跟踪各物料已占用库存量（ItemNumber → 已占用数量）
        var consumedStock = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);

        // 清理旧的占用记录，确保每次排程重新计算
        const long scheduleBangId = 1;
        await _db.Ado.ExecuteCommandAsync(
            "DELETE FROM ic_item_stockoccupy WHERE tenant_id = @TenantId AND bang_id = @BangId",
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@BangId", scheduleBangId));
        await _db.Ado.ExecuteCommandAsync(
            "DELETE FROM srm_po_occupy WHERE tenant_id = @TenantId AND bang_id = @BangId",
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@BangId", scheduleBangId));

        foreach (var wo in workOrders)
        {
            // 1. 执行齐套检查，刷新资源检查记录（写入 b_examine_result / b_bom_child_examine）
            try
            {
                await _kittingCheck.CheckSingleAsync(tenantId, wo.WorkOrd, account, scheduleBangId);
            }
            catch (Exception ex)
            {
                skipped.Add($"齐套检查失败[{wo.WorkOrd}]: {ex.Message}");
            }

            // 2. 刷新齐套数量（LocationStock），扣减已被高优先级工单占用的库存
            var locationStock = await CalcLocationStockAsync(tenantId, wo.WorkOrd, wo.QtyOrded ?? 0, consumedStock);
            if (locationStock.HasValue)
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE WorkOrdMaster
                    SET LocationStock = @Stock, UpdateTime = @Now
                    WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
                    """,
                    new SugarParameter("@Stock", locationStock.Value),
                    new SugarParameter("@Now", now),
                    new SugarParameter("@TenantId", tenantId),
                    new SugarParameter("@WorkOrd", wo.WorkOrd));
            }

            var routings = await LoadRoutingsAsync(tenantId, wo.WorkOrd);
            if (routings.Count == 0)
            {
                skipped.Add($"{wo.WorkOrd}(无工艺路线)");
                continue;
            }

            var woDomain = ResolveDomain(wo.Domain, domain, tenantId);
            await DeactivateExistingScheduleAsync(tenantId, wo.WorkOrd, woDomain);

            var planStart = (wo.OrdDate ?? now).Date;
            var planEnd = (wo.DueDate ?? planStart.AddDays(Math.Max(routings.Count, 7))).Date;
            if (planEnd < planStart)
                planEnd = planStart;

            var cursor = planStart;
            var seq = 1;
            for (var i = 0; i < routings.Count; i++)
            {
                var routing = routings[i];
                DateTime planDate;
                if (usedCalendar)
                {
                    planDate = ResolveNextAvailablePlanDate(cursor, routing.WorkCtr, calendar, occupiedSlots);
                    cursor = planDate.AddDays(1);
                }
                else
                {
                    var spanDays = Math.Max((planEnd - planStart).Days, 0);
                    planDate = routings.Count <= 1
                        ? planStart
                        : planStart.AddDays(spanDays * i / Math.Max(routings.Count - 1, 1));
                }

                if (planDate > planEnd)
                    planEnd = planDate;

                var slotKey = BuildOccupancyKey(routing.WorkCtr, planDate);
                occupiedSlots.Add(slotKey);

                var recId = await NextPeriodRecIdAsync();
                await InsertScheduleRowAsync(
                    recId, woDomain, routing, wo, planDate, seq, account, now, tenantId);
                seq++;
                rowCount++;
            }

            scheduledCount++;
        }

        return new ScheduleGenerationResult
        {
            WorkOrderCount = scheduledCount,
            ScheduleRowCount = rowCount,
            SkippedWorkOrders = skipped,
            UsedWorkCenterCalendar = usedCalendar,
            Message = scheduledCount > 0
                ? $"已为 {scheduledCount} 个工单生成 {rowCount} 条工序排程"
                : "未生成排程，请确认工单已同步工艺路线"
        };
    }

    private async Task InsertScheduleRowAsync(
        int recId,
        string woDomain,
        RoutingRow routing,
        PendingWorkOrderRow wo,
        DateTime planDate,
        int seq,
        string account,
        DateTime now,
        long tenantId)
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO PeriodSequenceDet (
                RecID, `Domain`, Site, ItemNum, Line, Op, WorkCtr, ProdDate, PlanDate,
                Sequence, OrdQty, CompQty, WorkOrds, Status, Employee,
                CreateUser, CreateTime, UpdateUser, UpdateTime,
                IsActive, IsConfirm, BusinessID, tenant_id
            ) VALUES (
                @RecId, @Domain, @Site, @ItemNum, @Line, @Op, @WorkCtr, @ProdDate, @PlanDate,
                @Sequence, @OrdQty, 0, @WorkOrd, '', '',
                @User, @Now, @User, @Now,
                1, 0, @BusinessId, @TenantId
            )
            """,
            new SugarParameter("@RecId", recId),
            new SugarParameter("@Domain", woDomain.Length > 8 ? woDomain[..8] : woDomain),
            new SugarParameter("@Site", routing.Site ?? (object)DBNull.Value),
            new SugarParameter("@ItemNum", wo.ItemNum ?? string.Empty),
            new SugarParameter("@Line", routing.ProdLine ?? (object)DBNull.Value),
            new SugarParameter("@Op", routing.Op),
            new SugarParameter("@WorkCtr", routing.WorkCtr ?? (object)DBNull.Value),
            new SugarParameter("@ProdDate", planDate),
            new SugarParameter("@PlanDate", planDate),
            new SugarParameter("@Sequence", seq),
            new SugarParameter("@OrdQty", wo.QtyOrded ?? 0),
            new SugarParameter("@WorkOrd", wo.WorkOrd),
            new SugarParameter("@BusinessId", wo.RecId),
            new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
            new SugarParameter("@Now", now),
            new SugarParameter("@TenantId", tenantId));
    }

    private static DateTime ResolveNextAvailablePlanDate(
        DateTime start,
        string? workCtr,
        Dictionary<string, HashSet<int>> calendar,
        HashSet<string> occupiedSlots)
    {
        var date = start.Date;
        for (var guard = 0; guard < 366; guard++)
        {
            if (IsWorkDay(date, workCtr, calendar))
            {
                var key = BuildOccupancyKey(workCtr, date);
                if (!occupiedSlots.Contains(key))
                    return date;
            }
            date = date.AddDays(1);
        }
        return start.Date;
    }

    private static bool IsWorkDay(DateTime date, string? workCtr, Dictionary<string, HashSet<int>> calendar)
    {
        if (calendar.Count == 0)
            return true;

        var key = NormalizeWorkCtr(workCtr);
        if (!calendar.TryGetValue(key, out var days) || days.Count == 0)
            return date.DayOfWeek is not DayOfWeek.Saturday and not DayOfWeek.Sunday;

        var weekDay = (int)date.DayOfWeek;
        return days.Contains(weekDay);
    }

    private static string BuildOccupancyKey(string? workCtr, DateTime date) =>
        $"{NormalizeWorkCtr(workCtr)}|{date:yyyy-MM-dd}";

    private static string NormalizeWorkCtr(string? workCtr) =>
        string.IsNullOrWhiteSpace(workCtr) ? "*" : workCtr.Trim();

    private async Task<Dictionary<string, HashSet<int>>> LoadWorkCenterCalendarAsync(long tenantId)
    {
        var rows = await _db.Ado.SqlQueryAsync<CalendarRow>(
            """
            SELECT TRIM(IFNULL(WorkCtr,'')) AS WorkCtr, WeekDay
            FROM ShopCalendarWorkCtr
            WHERE tenant_id = @TenantId
              AND IFNULL(IsActive, 0) = 1
              AND IFNULL(IsWorkDay, 0) = 1
              AND WeekDay IS NOT NULL
            """,
            new SugarParameter("@TenantId", tenantId));

        var map = new Dictionary<string, HashSet<int>>(StringComparer.OrdinalIgnoreCase);
        foreach (var row in rows)
        {
            if (row.WeekDay is null) continue;
            var key = NormalizeWorkCtr(row.WorkCtr);
            if (!map.TryGetValue(key, out var set))
            {
                set = new HashSet<int>();
                map[key] = set;
            }
            set.Add(row.WeekDay.Value);
        }
        return map;
    }

    private async Task<HashSet<string>> LoadOccupiedSlotsAsync(long tenantId)
    {
        var rows = await _db.Ado.SqlQueryAsync<OccupiedSlotRow>(
            """
            SELECT TRIM(IFNULL(WorkCtr,'')) AS WorkCtr, DATE(PlanDate) AS PlanDate
            FROM PeriodSequenceDet
            WHERE tenant_id = @TenantId AND IFNULL(IsActive, 0) = 1 AND PlanDate IS NOT NULL
            """,
            new SugarParameter("@TenantId", tenantId));

        return rows
            .Where(x => x.PlanDate.HasValue)
            .Select(x => BuildOccupancyKey(x.WorkCtr, x.PlanDate!.Value))
            .ToHashSet(StringComparer.OrdinalIgnoreCase);
    }

    private async Task<List<PendingWorkOrderRow>> LoadPendingWorkOrdersAsync(long tenantId)
    {
        return await _db.Ado.SqlQueryAsync<PendingWorkOrderRow>(
            """
            SELECT RecID AS RecId, WorkOrd, ItemNum, `Domain`, QtyOrded, OrdDate, DueDate, Priority, Urgent,
                   IFNULL(Status, '') AS Status
            FROM WorkOrdMaster
            WHERE tenant_id = @TenantId
              AND LOWER(TRIM(IFNULL(Status,''))) IN ('w', 's', 'r', 'p')
            ORDER BY
              CASE LOWER(TRIM(IFNULL(Status,'')))
                WHEN 'w' THEN 1
                WHEN 's' THEN 1
                WHEN 'r' THEN 2
                WHEN 'p' THEN 3
                ELSE 4
              END,
              IFNULL(Urgent, 0) DESC,
              IFNULL(Priority, 0) ASC,
              DueDate, WorkOrd
            """,
            new SugarParameter("@TenantId", tenantId));
    }

    private async Task<List<RoutingRow>> LoadRoutingsAsync(long tenantId, string workOrd)
    {
        return await _db.Ado.SqlQueryAsync<RoutingRow>(
            """
            SELECT OP AS Op, ProdLine, WorkCtr, WorkCtr AS Site
            FROM WorkOrdRouting
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd AND IFNULL(IsActive, 0) = 1
            ORDER BY (OP + 0), OP
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
    }

    public async Task DeactivateExistingScheduleAsync(long tenantId, string workOrd, string domain)
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE PeriodSequenceDet
            SET IsActive = 0, UpdateTime = @Now
            WHERE tenant_id = @TenantId AND WorkOrds = @WorkOrd AND `Domain` = @Domain AND IFNULL(IsActive, 0) = 1
            """,
            new SugarParameter("@Now", DateTime.Now),
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd),
            new SugarParameter("@Domain", domain.Length > 8 ? domain[..8] : domain));
    }

    private static string ResolveDomain(string? woDomain, string? requestDomain, long tenantId)
    {
        if (!string.IsNullOrWhiteSpace(woDomain))
            return woDomain.Trim();
        if (!string.IsNullOrWhiteSpace(requestDomain))
            return requestDomain.Trim();
        return tenantId.ToString();
    }

    private async Task<int> NextPeriodRecIdAsync()
    {
        var max = await _db.Ado.GetIntAsync("SELECT IFNULL(MAX(RecID), 0) FROM PeriodSequenceDet");
        return max + 1;
    }

    public sealed class ScheduleGenerationResult
    {
        public int WorkOrderCount { get; set; }
        public int ScheduleRowCount { get; set; }
        public bool UsedWorkCenterCalendar { get; set; }
        public List<string> SkippedWorkOrders { get; set; } = new();
        public string Message { get; set; } = string.Empty;
    }

    /// <summary>
    /// 计算工单齐套数量：MIN(可用库存 / qty) 得可生产成品数，超过工单需求量则取工单需求量。
    /// 若工单领料单已全部发完料，则齐套数量 = 工单需求量。
    /// consumedStock 跟踪已被高优先级工单占用的库存量，避免重复占用。
    /// </summary>
    private async Task<decimal?> CalcLocationStockAsync(
        long tenantId, string workOrd, decimal qtyOrded,
        Dictionary<string, decimal> consumedStock)
    {
        // 检查领料单是否已全部发完料
        var allIssued = await _db.Ado.GetIntAsync(
            """
            SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
            FROM NbrMaster nm
            WHERE nm.tenant_id = @TenantId AND nm.WorkOrd = @WorkOrd
              AND nm.Type = 'SM' AND IFNULL(nm.IsActive, 0) = 1
              AND NOT EXISTS (
                  SELECT 1 FROM NbrDetail nd
                  WHERE nd.tenant_id = nm.tenant_id AND nd.Nbr = nm.Nbr
                    AND nd.Type = 'SM' AND IFNULL(nd.IsActive, 0) = 1
                    AND IFNULL(nd.QtyRec, 0) < IFNULL(nd.QtyOrd, 0)
              )
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        if (allIssued > 0 && qtyOrded > 0)
            return qtyOrded;

        // 从最新资源检查获取各物料的 use_qty 和 qty
        var materials = await _db.Ado.SqlQueryAsync<LocationStockMaterialRow>(
            """
            SELECT
                bce.item_number AS ItemNumber,
                IFNULL(bce.use_qty, 0) AS UseQty,
                IFNULL(bce.qty, 0) AS Qty
            FROM b_examine_result ber
            INNER JOIN b_bom_child_examine bce ON ber.Id = bce.examine_id AND bce.is_use = 1
            WHERE ber.tenant_id = @TenantId
              AND ber.IsDeleted = 0
              AND ber.morder_no = @WorkOrd
              AND ber.Id = (
                  SELECT br.Id FROM b_examine_result br
                  WHERE br.tenant_id = @TenantId AND br.morder_no = @WorkOrd AND br.IsDeleted = 0
                  ORDER BY br.create_time DESC LIMIT 1
              )
              AND IFNULL(bce.qty, 0) > 0
              AND IFNULL(bce.erp_cls, 3) = 3
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        if (materials.Count == 0)
            return null;

        // MIN(可用库存 / qty) = 扣减已占用后的瓶颈物料可生产成品数
        decimal? minProducible = null;
        foreach (var mat in materials)
        {
            if (mat.Qty <= 0) continue;
            var alreadyConsumed = consumedStock.TryGetValue(mat.ItemNumber, out var c) ? c : 0m;
            var availableUseQty = Math.Max(0m, mat.UseQty - alreadyConsumed);
            var producible = availableUseQty / mat.Qty;
            if (minProducible is null || producible < minProducible)
                minProducible = producible;
        }

        if (minProducible is null)
            return null;

        // 可生产数 > 工单需求量则取工单需求量
        var result = Math.Ceiling(minProducible.Value);
        if (qtyOrded > 0 && result > qtyOrded)
            result = qtyOrded;

        // 记录本工单占用的库存量
        foreach (var mat in materials)
        {
            if (mat.Qty <= 0) continue;
            var alreadyConsumed = consumedStock.TryGetValue(mat.ItemNumber, out var c) ? c : 0m;
            var availableUseQty = Math.Max(0m, mat.UseQty - alreadyConsumed);
            var consume = Math.Min(availableUseQty, result * mat.Qty);
            if (!consumedStock.ContainsKey(mat.ItemNumber))
                consumedStock[mat.ItemNumber] = 0m;
            consumedStock[mat.ItemNumber] += consume;
        }

        return result;
    }

    private sealed class LocationStockMaterialRow
    {
        public string ItemNumber { get; set; } = string.Empty;
        public decimal UseQty { get; set; }
        public decimal Qty { get; set; }
    }

    private sealed class PendingWorkOrderRow
    {
        public long RecId { get; set; }
        public string WorkOrd { get; set; } = string.Empty;
        public string? ItemNum { get; set; }
        public string? Domain { get; set; }
        public decimal? QtyOrded { get; set; }
        public DateTime? OrdDate { get; set; }
        public DateTime? DueDate { get; set; }
        public decimal? Priority { get; set; }
        public int? Urgent { get; set; }
        public string Status { get; set; } = string.Empty;
    }

    private sealed class RoutingRow
    {
        public int Op { get; set; }
        public string? ProdLine { get; set; }
        public string? WorkCtr { get; set; }
        public string? Site { get; set; }
    }

    private sealed class CalendarRow
    {
        public string? WorkCtr { get; set; }
        public int? WeekDay { get; set; }
    }

    private sealed class OccupiedSlotRow
    {
        public string? WorkCtr { get; set; }
        public DateTime? PlanDate { get; set; }
    }
}