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

namespace Admin.NET.Plugin.AiDOP.Production;

/// <summary>
/// 可执行生产日计划（PeriodSequenceDet + 排产/工单等）
/// 路由前缀：/api/Production/daily-plan/...
/// </summary>
[ApiDescriptionSettings(Order = 266, Description = "可执行生产日计划")]
[Route("api/Production")]
[AllowAnonymous]
[NonUnify]
public class ExecutableDailyPlanService : IDynamicApiController, ITransient
{
    /// <summary>
    /// S8 Stage-2「产品设计」的 End 事件码。
    ///
    /// <para>语义：某张真实销售订单<b>首次</b>有至少一条日计划由「未下达」成功变为
    /// <c>Status='Y'</c>，即该订单首次具备可执行生产日计划。</para>
    ///
    /// <para>Stage-2 End 取本事件 <b>FIRST SUCCESS 的 end_time</b>（不是 start_time）——
    /// end_time 写在业务状态提交成功之后，与 Stage-1 的 <c>S1_DELIVERY_CONFIRM</c> 同口径。</para>
    ///
    /// <para>⚠️ 动作码前缀 <c>S2_</c> 只是本系统的动作命名，<b>不代表 S8 五阶段归属</b>；
    /// 本事件服务于 S8 的 PRODUCT_DESIGN 阶段。</para>
    /// </summary>
    public const string ActionDailyPlanRelease = "S2_DAILY_PLAN_RELEASE";

    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly AidopActionRunLogWriter _runLog;

    public ExecutableDailyPlanService(
        ISqlSugarClient db,
        UserManager userManager,
        AidopActionRunLogWriter runLog)
    {
        _db = db;
        _userManager = userManager;
        _runLog = runLog;
    }

    /// <summary>分页列表 GET /api/Production/daily-plan/list</summary>
    [DisplayName("可执行生产日计划列表")]
    [HttpGet("daily-plan/list")]
    public async Task<object> GetList([FromQuery] ExecutableDailyPlanListInput input)
    {
        var tenantId = AidopTenantHelper.Resolve(App.HttpContext);
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };

        var innerWhere = new List<string>
        {
            "p.OrdQty > 0",
            "p.IsActive = 1",
            "LOWER(IFNULL(w.Status,'')) NOT IN ('', 'c', 'p')",
            "p.tenant_id = @TenantId"
        };
        if (!string.IsNullOrWhiteSpace(input.PlanDateMin))
        {
            var pdm = input.PlanDateMin.Trim();
            if (pdm.Length > 10) pdm = pdm[..10];
            innerWhere.Add("DATE(p.PlanDate) >= @PlanDateMin");
            pars.Add(new SugarParameter("@PlanDateMin", pdm));
        }
        else
        {
            innerWhere.Add("DATE(p.PlanDate) >= CURDATE()");
        }
        if (!string.IsNullOrWhiteSpace(input.PlanDateMax))
        {
            var pdx = input.PlanDateMax.Trim();
            if (pdx.Length > 10) pdx = pdx[..10];
            innerWhere.Add("DATE(p.PlanDate) <= @PlanDateMax");
            pars.Add(new SugarParameter("@PlanDateMax", pdx));
        }
        if (!string.IsNullOrWhiteSpace(input.Domain))
        {
            innerWhere.Add("p.`Domain` = @Domain");
            pars.Add(new SugarParameter("@Domain", input.Domain.Trim()));
        }

        if (!string.IsNullOrWhiteSpace(input.WorkOrds))
        {
            innerWhere.Add("(p.WorkOrds) LIKE @WorkOrds");
            pars.Add(new SugarParameter("@WorkOrds", $"%{input.WorkOrds.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            innerWhere.Add("(p.ItemNum) LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Batch))
        {
            innerWhere.Add("(w.Batch) LIKE @Batch");
            pars.Add(new SugarParameter("@Batch", $"%{input.Batch.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.WoStatus))
        {
            innerWhere.Add("LOWER(IFNULL(w.Status,'')) = @WoStatus");
            pars.Add(new SugarParameter("@WoStatus", input.WoStatus.Trim().ToLowerInvariant()));
        }
        if (!string.IsNullOrWhiteSpace(input.IsLabor))
        {
            if (string.Equals(input.IsLabor, "yes", StringComparison.OrdinalIgnoreCase))
                innerWhere.Add("IFNULL(p.Status,'') <> ''");
            else if (string.Equals(input.IsLabor, "no", StringComparison.OrdinalIgnoreCase))
                innerWhere.Add("IFNULL(p.Status,'') = ''");
        }
        if (!string.IsNullOrWhiteSpace(input.WorkCtr))
        {
            innerWhere.Add("(sm.WorkCtr) = @WorkCtr");
            pars.Add(new SugarParameter("@WorkCtr", input.WorkCtr.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.OccupyEquipmentType))
        {
            innerWhere.Add("(IFNULL(sm.OccupyInternalEquipmentCode,'') LIKE @Occ OR IFNULL(sm.MoldTypeCode,'') LIKE @Occ)");
            var occ = $"%{input.OccupyEquipmentType.Trim()}%";
            pars.Add(new SugarParameter("@Occ", occ));
        }
        if (!string.IsNullOrWhiteSpace(input.OpStdOp))
        {
            innerWhere.Add("(CONCAT(CAST(sm.Op AS CHAR),' ',IFNULL(sm.OpDescr,'')) LIKE @OpStdOp");
            pars.Add(new SugarParameter("@OpStdOp", $"%{input.OpStdOp.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.WorkStartFrom))
        {
            innerWhere.Add("sm.WorkStartTime >= @WorkStartFrom");
            pars.Add(new SugarParameter("@WorkStartFrom", input.WorkStartFrom.Trim()));
        }

        var baseSql = BuildListBaseSql(string.Join(" AND ", innerWhere));
        var orderClause = ResolveListOrder(input.OrderBy, input.Sort);

        var offset = (input.Page - 1) * input.PageSize;
        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(*) FROM ({baseSql}) AS t", pars);
        var list = await _db.Ado.SqlQueryAsync<ExecutableDailyPlanListRow>(
            $"SELECT * FROM ({baseSql}) AS t ORDER BY {orderClause} LIMIT {input.PageSize} OFFSET {offset}", pars);

        return new { total, page = input.Page, pageSize = input.PageSize, list };
    }

    /// <summary>下达 POST /api/Production/daily-plan/release</summary>
    [DisplayName("日计划下达")]
    [HttpPost("daily-plan/release")]
    public async Task<object> Release([FromBody] ExecutableDailyPlanReleaseInput input)
    {
        var domain = input.Domain.Trim();
        var ids = input.Ids.Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
            .Select(s => long.TryParse(s, out var id) ? id : (long?)null)
            .Where(x => x.HasValue)
            .Select(x => x!.Value)
            .ToArray();
        if (ids.Length == 0)
            throw Oops.Oh("请选择有效的行主键");

        var account = _userManager.Account ?? "system";
        var now = DateTime.Now;
        var tenantId = AidopTenantHelper.Resolve(App.HttpContext);
        if (tenantId <= 0)
            throw Oops.Oh("无法确定当前租户，日计划下达已拒绝");   // fail closed，绝不按默认租户写业务状态
        const string releasedFlag = "Y";

        var idList = string.Join(",", ids);

        // ── 1. 只挑【本次真正会发生状态跃迁】的计划行，并当场解析其销售订单身份 ──
        // 为什么在业务写路径里直读源表：事件要记录的就是"源系统刚刚发生了什么"，
        // 让 release 事务反向依赖 mdp_std_so 才能完成会把方向搞反（Source → MDP → S8，
        // 不是 Source → MDP → 再决定能不能 release）。S8 的「只读中台」约束管的是消费侧。
        //
        // ID 桥（全程 tenant 内，禁止 SalesJob 文本归因）：
        //   PeriodSequenceDet.WorkOrds → WorkOrdMaster.WorkOrd
        //     → WorkOrdMaster.BusinessID (= crm_seorderentry.Id)
        //       → crm_seorderentry.seorder_id (= crm_seorder.Id)
        var transitions = await _db.Ado.SqlQueryAsync<PlanReleaseTransitionRow>(
            $"""
            SELECT p.RecID          AS PlanRecId,
                   p.WorkOrds       AS WorkOrd,
                   o.Id             AS SalesOrderId,
                   o.bill_no        AS SalesOrderNo
              FROM PeriodSequenceDet p
              JOIN WorkOrdMaster w      ON w.tenant_id = p.tenant_id AND w.WorkOrd = p.WorkOrds
              JOIN crm_seorderentry e   ON e.tenant_id = p.tenant_id AND e.Id = w.BusinessID
              JOIN crm_seorder o        ON o.tenant_id = p.tenant_id AND o.Id = e.seorder_id
             WHERE p.tenant_id = @TenantId
               AND p.`Domain`  = @Domain
               AND p.RecID IN ({idList})
               AND IFNULL(p.`Status`, '') <> @Flag
            """,
            new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@Domain", domain),
                new("@Flag", releasedFlag)
            });

        // ── 2. 按销售订单去重：一次 release 内，同一订单最多一条里程碑事件 ──
        // 不是一条计划行一条事件 —— Stage KPI 的 Grain 是订单，不是工序行。
        var byOrder = (transitions ?? [])
            .GroupBy(t => t.SalesOrderId)
            .Select(g => new
            {
                SalesOrderId = g.Key,
                SalesOrderNo = g.First().SalesOrderNo,
                PlanRecIds = g.Select(x => x.PlanRecId).Distinct().ToList(),
                WorkOrders = g.Select(x => x.WorkOrd).Where(x => !string.IsNullOrEmpty(x)).Distinct().ToList()
            })
            .ToList();

        // ── 3. 无真实跃迁 → 不写任何里程碑 ──
        // 重复点击下达（目标行已是 'Y'）在这里被拦住：普通重复 UPDATE 不是业务里程碑。
        if (byOrder.Count == 0)
        {
            var repeated = await _db.Ado.ExecuteCommandAsync(
                $"""
                UPDATE PeriodSequenceDet
                SET UpdateUser = @UpdateUser, UpdateTime = @UpdateTime
                WHERE RecID IN ({idList}) AND `Domain` = @Domain AND tenant_id = @TenantId
                  AND IFNULL(`Status`, '') = @Flag
                """,
                new List<SugarParameter>
                {
                    new("@UpdateUser", account),
                    new("@UpdateTime", now),
                    new("@Domain", domain),
                    new("@TenantId", tenantId),
                    new("@Flag", releasedFlag)
                });
            return new { message = repeated > 0 ? "所选计划已处于下达状态，未产生新的下达里程碑" : "没有可下达的计划行",
                         affected = 0, milestoneCount = 0 };
        }

        // ── 4. 先开事件（RUNNING），再做业务写；成败与事务结果严格一致 ──
        var runLogIds = new Dictionary<long, long>();
        foreach (var ord in byOrder)
            runLogIds[ord.SalesOrderId] = await _runLog.StartAsync(
                ActionDailyPlanRelease, tenantId, "crm_seorder", ord.SalesOrderId, ord.SalesOrderNo);

        int n;
        try
        {
            // 只更新"本次确实要跃迁"的那些行，不把已下达行卷进来
            var transitionIds = string.Join(",", byOrder.SelectMany(o => o.PlanRecIds).Distinct());
            n = await _db.Ado.ExecuteCommandAsync(
                $"""
                UPDATE PeriodSequenceDet
                SET `Status` = @Flag,
                    UpdateUser = @UpdateUser,
                    UpdateTime = @UpdateTime
                WHERE RecID IN ({transitionIds})
                  AND `Domain` = @Domain
                  AND tenant_id = @TenantId
                  AND IFNULL(`Status`, '') <> @Flag
                """,
                new List<SugarParameter>
                {
                    new("@Flag", releasedFlag),
                    new("@UpdateUser", account),
                    new("@UpdateTime", now),
                    new("@Domain", domain),
                    new("@TenantId", tenantId)
                });
        }
        catch (Exception ex)
        {
            // 业务写失败 → 每张订单的事件都必须落 FAILED，绝不允许留下 SUCCESS 里程碑
            foreach (var ord in byOrder)
                await _runLog.FailedAsync(runLogIds[ord.SalesOrderId], ex.Message,
                    new { salesOrderId = ord.SalesOrderId, salesOrderNo = ord.SalesOrderNo });
            throw;
        }

        // ── 5. 业务写成功后才 SUCCESS —— end_time 即 Stage-2 End Authority ──
        foreach (var ord in byOrder)
            await _runLog.SuccessAsync(runLogIds[ord.SalesOrderId],
                $"日计划下达：订单 {ord.SalesOrderNo}，计划行 {ord.PlanRecIds.Count} 条，工单 {ord.WorkOrders.Count} 个",
                new
                {
                    salesOrderId = ord.SalesOrderId,
                    salesOrderNo = ord.SalesOrderNo,
                    releasedPlanCount = ord.PlanRecIds.Count,
                    planRecIds = ord.PlanRecIds,
                    workOrderCount = ord.WorkOrders.Count
                });

        return new { message = "下达成功", affected = n, milestoneCount = byOrder.Count };
    }

    /// <summary>日计划行 → 销售订单的归因投影（仅用于 release 事件，走 ID 桥不走单号文本）。</summary>
    private sealed class PlanReleaseTransitionRow
    {
        public long PlanRecId { get; set; }
        public string? WorkOrd { get; set; }
        public long SalesOrderId { get; set; }
        public string? SalesOrderNo { get; set; }
    }

    /// <summary>工单状态 PATCH POST /api/Production/daily-plan/patch-wo-status</summary>
    [DisplayName("可执行日计划-工单状态")]
    [HttpPost("daily-plan/patch-wo-status")]
    public async Task<object> PatchWoStatus([FromBody] ExecutableDailyPlanWoStatusInput input)
    {
        var st = input.Status.Trim().ToLowerInvariant();
        if (st is not ("w" or "r" or "c" or "p"))
            throw Oops.Oh("状态不合法");

        var tenantId = AidopTenantHelper.Resolve(App.HttpContext);
        var n = await _db.Ado.ExecuteCommandAsync(
            $"""
            UPDATE WorkOrdMaster
            SET `Status` = @St,
                UpdateTime = @Now,
                UpdateUser = @User
            WHERE WorkOrd = @WorkOrd AND `Domain` = @Domain AND tenant_id = @TenantId
            """,
            new List<SugarParameter>
            {
                new("@St", st),
                new("@Now", DateTime.Now),
                new("@User", _userManager.Account ?? "system"),
                new("@WorkOrd", input.WorkOrd.Trim()),
                new("@Domain", input.Domain.Trim()),
                new("@TenantId", tenantId)
            });
        if (n == 0)
            throw Oops.Oh("工单不存在");
        return new { message = "状态已更新" };
    }

    private static string BuildListBaseSql(string innerWhere) => $"""
        SELECT
            GREATEST(IFNULL(p.CompQty, 0), IFNULL(act.comp_qty, 0)) AS CompQty,
            GREATEST(IFNULL(p.RejectQty, 0), IFNULL(act.reject_qty, 0)) AS RejectQty,
            sm.WorkStartTime AS WorkStartTime,
            sm.DeviceAllocationCount AS DeviceAllocationCount,
            p.RecID AS Id,
            sm.WorkEndTime AS WorkEndTime,
            pd.InternalEquipmentCode AS Line,
            line.line_describe AS LineDescribe,
            p.WorkOrds AS WorkOrds,
            p.ItemNum AS ItemNum,
            i.Descr AS Descr,
            i.PkgCode AS Languages,
            w.LotSerial AS LotSerial,
            CASE WHEN IFNULL(p.Status,'') = '' THEN '否' ELSE '是' END AS IsLabor,
            pd.Rate AS Rate,
            p.Op AS Op,
            p.PlanDate AS PlanDate,
            p.OrdQty AS OrdQty,
            pd.RunCrew AS RunCrew,
            LOWER(w.Status) AS Status,
            w.Batch AS Batch,
            p.Status AS PStatus,
            m.MaterialSituation AS MaterialSituation,
            CASE WHEN DATE_FORMAT(p.PlanDate,'%Y-%m-%d') = DATE_FORMAT(CURDATE(),'%Y-%m-%d') THEN 1 ELSE 0 END AS IsUpdateQty,
            CASE WHEN DATE_FORMAT(p.PlanDate,'%Y-%m-%d') > DATE_FORMAT(CURDATE(),'%Y-%m-%d') THEN 1 ELSE 0 END AS IsUpdateTime,
            sm.SetupTime AS SetupTime,
            sm.SetupTime * 60 AS SetupTimeInMinutes,
            pd.Site AS Site,
            CONCAT(IFNULL(sm.WorkCtr,''), '  ', IFNULL(wm.Descr,'')) AS Descr0,
            w.DueDate AS DueDate,
            pd.OpType AS OpType,
            CASE WHEN pd.OpType = 'M'
                 THEN TIMESTAMPDIFF(MINUTE, sm.WorkStartTime, sm.WorkEndTime) / 60.0 * IFNULL(sm.DeviceAllocationCount, 0)
                 ELSE 0 END AS DeviceWorkHours,
            CASE WHEN IFNULL(pd.OpType,'') <> 'M'
                 THEN TIMESTAMPDIFF(MINUTE, sm.WorkStartTime, sm.WorkEndTime) / 60.0 * IFNULL(sm.DeviceAllocationCount, 0)
                 ELSE TIMESTAMPDIFF(MINUTE, sm.WorkStartTime, sm.WorkEndTime) / 60.0 * IFNULL(sm.DeviceAllocationCount, 0) * IFNULL(pd.StandardStaffCount, 0) END AS ManWorkHours,
            CASE WHEN sm.WorkEndTime > w.DueDate THEN 'WorkOrds:#FF7F50'
                 WHEN m.MaterialSituation = '仓库不齐套' AND LOWER(IFNULL(w.Status,'')) = 'r' THEN 'WorkOrds:#FCDD18'
                 ELSE 'WorkOrds:#FFFFFF;' END AS Background,
            CONCAT(CAST(sm.Op AS CHAR), ' ', IFNULL(sm.OpDescr,'')) AS OpStdOp,
            IF(IFNULL(r.RunCrew, 0) > 0, r.RunCrew, pd.StandardStaffCount) AS StandardStaffCount,
            i.Drawing AS Drawing,
            w.QtyOrded AS QtyOrded,
            w.ReleaseDate AS ReleaseDate,
            (
                SELECT GROUP_CONCAT(DISTINCT el.MachineName ORDER BY el.MachineName SEPARATOR '/')
                FROM EquipmentList el
                WHERE pd.InternalEquipmentCode IS NOT NULL
                  AND (
                    el.InternalEquipmentCode = pd.InternalEquipmentCode
                    OR FIND_IN_SET(el.InternalEquipmentCode, REPLACE(REPLACE(IFNULL(pd.InternalEquipmentCode,''), '+', ','), '/', ',')) > 0
                  )
            ) AS MachineName,
            IF(w.Rev IS NULL OR w.Rev = '', i.Rev, w.Rev) AS Rev,
            w.Eff AS Eff,
            p.`Domain` AS Domain
        FROM PeriodSequenceDet p
        LEFT JOIN LineMaster line ON p.Line = line.Line AND (line.tenant_id = p.tenant_id OR p.tenant_id IS NULL)
        LEFT JOIN ItemMaster i ON p.ItemNum = i.ItemNum AND (p.tenant_id = i.tenant_id OR p.tenant_id IS NULL)
        LEFT JOIN WorkOrdMaster w ON p.WorkOrds = w.WorkOrd AND (w.tenant_id = p.tenant_id OR p.tenant_id IS NULL)
        LEFT JOIN WorkOrdRouting r ON p.Op = r.OP AND p.ItemNum = r.ItemNum AND p.WorkOrds = r.WorkOrd AND (r.tenant_id = p.tenant_id OR p.tenant_id IS NULL)
        LEFT JOIN mes_morder m ON w.WorkOrd = m.morder_no AND CAST(w.`Domain` AS CHAR(64)) = CAST(m.factory_id AS CHAR(64)) AND m.tenant_id = w.tenant_id
        LEFT JOIN ProdLineDetail pd ON p.ItemNum = pd.Part AND p.Line = pd.Line AND p.Op = pd.Op AND (pd.tenant_id = p.tenant_id OR p.tenant_id IS NULL)
        LEFT JOIN ScheduleResultOpMaster sm ON sm.PeriodDetRecId = p.RecID
        LEFT JOIN WorkCtrMaster wm ON wm.WorkCtr = sm.WorkCtr AND (wm.tenant_id = sm.tenant_id OR sm.tenant_id IS NULL)
        LEFT JOIN ado_psd_op_actual act
               ON act.tenant_id = IFNULL(p.tenant_id, 0)
              AND act.domain = IFNULL(p.`Domain`, '')
              AND act.work_ord = p.WorkOrds
              AND act.op = p.Op
        WHERE {innerWhere}
        """;

    private static string ResolveListOrder(string? orderBy, string? sort)
    {
        var desc = string.Equals(sort?.Trim(), "desc", StringComparison.OrdinalIgnoreCase);
        var dir = desc ? "DESC" : "ASC";
        var key = orderBy?.Trim().ToLowerInvariant();
        var col = key switch
        {
            "workords" => "t.WorkOrds",
            "itemnum" => "t.ItemNum",
            "plandate" => "t.PlanDate",
            "status" => "t.Status",
            "workstarttime" => "t.WorkStartTime",
            "lotserial" => "t.LotSerial",
            _ => "t.PlanDate"
        };
        // 默认（无排序参数时）按工单号、工序、计划日期升序
        if (string.IsNullOrWhiteSpace(key))
            return "t.WorkOrds ASC, t.Op ASC, t.PlanDate ASC";
        return $"{col} {dir}, t.Id DESC";
    }

    private sealed class ExecutableDailyPlanListRow
    {
        public decimal? CompQty { get; set; }
        public decimal? RejectQty { get; set; }
        public DateTime? WorkStartTime { get; set; }
        public int? DeviceAllocationCount { get; set; }
        public long Id { get; set; }
        public DateTime? WorkEndTime { get; set; }
        public string? Line { get; set; }
        public string? LineDescribe { get; set; }
        public string? WorkOrds { get; set; }
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? Languages { get; set; }
        public string? LotSerial { get; set; }
        public string? IsLabor { get; set; }
        public decimal? Rate { get; set; }
        public int? Op { get; set; }
        public DateTime? PlanDate { get; set; }
        public decimal? OrdQty { get; set; }
        /// <summary>ProdLineDetail.RunCrew 为班组/编码类字符串，勿用 decimal。</summary>
        public string? RunCrew { get; set; }
        public string? Status { get; set; }
        public string? Batch { get; set; }
        public string? PStatus { get; set; }
        public string? MaterialSituation { get; set; }
        public int IsUpdateQty { get; set; }
        public int IsUpdateTime { get; set; }
        public decimal? SetupTime { get; set; }
        public decimal? SetupTimeInMinutes { get; set; }
        public string? Site { get; set; }
        public string? Descr0 { get; set; }
        public DateTime? DueDate { get; set; }
        public string? OpType { get; set; }
        public decimal? DeviceWorkHours { get; set; }
        public decimal? ManWorkHours { get; set; }
        public string? Background { get; set; }
        public string? OpStdOp { get; set; }
        public decimal? StandardStaffCount { get; set; }
        public string? Drawing { get; set; }
        public decimal? QtyOrded { get; set; }
        public DateTime? ReleaseDate { get; set; }
        public string? MachineName { get; set; }
        public string? Rev { get; set; }
        public DateTime? Eff { get; set; }
        public string? Domain { get; set; }
    }
}