namespace Admin.NET.Plugin.AiDOP.Production;

/// <summary>
/// 工单工序执行进度口径（唯一真相）：工单执行进度看板与工序进度明细共用，避免两处各写一套算法。
/// <para>
/// 流程件语义：产品需逐序加工，全部工序走完一遍才算完成，因此「完成件数」取末道工序完成量，
/// 而非各工序 QtyComplete 累加（165 报工会把当前工序之前的一段工序一并推进，累加值无业务意义）。
/// </para>
/// </summary>
internal static class WorkOrderExecutionProgressSql
{
    /// <summary>当前工序：优先正在生产（Status='W'）的一序；否则取已有完成量的最靠后一序。</summary>
    public static string CurrentOp(string routingMatch) => $"""
        SELECT r.OP AS Op, r.Descr AS Descr, IFNULL(r.QtyComplete, 0) AS QtyComplete
        FROM WorkOrdRouting r
        WHERE {routingMatch}
          AND IFNULL(r.IsActive, 1) = 1
          AND (UPPER(IFNULL(r.Status, '')) = 'W' OR IFNULL(r.QtyComplete, 0) > 0)
        ORDER BY (UPPER(IFNULL(r.Status, '')) = 'W') DESC, (r.OP + 0) DESC
        LIMIT 1
        """;

    /// <summary>末道工序：工艺路线最大 OP，作为「整单工艺走完」的判定序。</summary>
    public static string LastOp(string routingMatch) => $"""
        SELECT r.OP AS Op, r.Descr AS Descr, IFNULL(r.QtyComplete, 0) AS QtyComplete
        FROM WorkOrdRouting r
        WHERE {routingMatch}
          AND IFNULL(r.IsActive, 1) = 1
        ORDER BY (r.OP + 0) DESC
        LIMIT 1
        """;

    /// <summary>全工序完成量求和：仅作技术对账，不代表流程件进度。</summary>
    public static string OpSum(string routingMatch) => $"""
        SELECT SUM(IFNULL(r.QtyComplete, 0)) AS QtyOpComplete
        FROM WorkOrdRouting r
        WHERE {routingMatch}
          AND IFNULL(r.IsActive, 1) = 1
        """;

    /// <summary>统一的进度列表达式，供看板行与明细汇总共用。</summary>
    public static string Columns(string master, string cur, string last, string sum) => $"""
        {cur}.Op AS CurrentOp,
        {cur}.Descr AS CurrentOpName,
        IFNULL({cur}.QtyComplete, 0) AS CurrentOpQty,
        {last}.Op AS LastOp,
        {last}.Descr AS LastOpName,
        IFNULL({last}.QtyComplete, 0) AS LastOpQty,
        GREATEST(IFNULL({cur}.QtyComplete, 0) - IFNULL({last}.QtyComplete, 0), 0) AS QtyWip,
        IFNULL({sum}.QtyOpComplete, 0) AS QtyOpComplete,
        CASE
            WHEN IFNULL({master}.QtyOrded, 0) = 0 THEN 0
            ELSE ROUND(100.0 * IFNULL({last}.QtyComplete, 0) / {master}.QtyOrded, 4)
        END AS ProgressExec
        """;

    /// <summary>三个 LATERAL 关联，别名固定为 curOp / lastOp / opAgg。</summary>
    public static string Laterals(string routingMatch) => $"""
        LEFT JOIN LATERAL (
            {CurrentOp(routingMatch)}
        ) AS curOp ON TRUE
        LEFT JOIN LATERAL (
            {LastOp(routingMatch)}
        ) AS lastOp ON TRUE
        LEFT JOIN LATERAL (
            {OpSum(routingMatch)}
        ) AS opAgg ON TRUE
        """;
}