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

namespace Admin.NET.Plugin.AiDOP.Production;

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

    public ProductionScheduleGenerationService(ISqlSugarClient db)
    {
        _db = db;
    }

    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>();

        foreach (var wo in workOrders)
        {
            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
            FROM WorkOrdMaster
            WHERE tenant_id = @TenantId
              AND LOWER(TRIM(IFNULL(Status,''))) IN ('p', 'r')
            ORDER BY IFNULL(Urgent, 0) DESC, IFNULL(Priority, 0) DESC, 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;
    }

    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; }
    }

    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; }
    }
}