namespace Admin.NET.Plugin.AiDOP.WorkOrder;

/// <summary>从标准工艺路线同步工单工序明细（WorkOrdRouting）。</summary>
public class WorkOrderRoutingSyncService : ITransient
{
    private readonly ISqlSugarClient _db;

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

    /// <summary>为租户内尚无工序明细的待排产工单补齐标准工艺路线。</summary>
    public async Task<int> EnsureAllPendingAsync(long tenantId, string account)
    {
        var workOrders = await _db.Ado.SqlQueryAsync<string>(
            """
            SELECT w.WorkOrd
            FROM WorkOrdMaster w
            WHERE w.tenant_id = @TenantId
              AND LOWER(IFNULL(w.Status, '')) IN ('p', 'r')
              AND IFNULL(w.WorkOrd, '') <> ''
              AND NOT EXISTS (
                  SELECT 1
                  FROM WorkOrdRouting wr
                  WHERE wr.tenant_id = w.tenant_id
                    AND wr.WorkOrd = w.WorkOrd
                    AND IFNULL(wr.IsActive, 0) = 1
              )
            ORDER BY w.RecID
            """,
            new SugarParameter("@TenantId", tenantId));

        var affected = 0;
        foreach (var workOrd in workOrders.Where(x => !string.IsNullOrWhiteSpace(x)).Distinct(StringComparer.OrdinalIgnoreCase))
            affected += await EnsureFromRoutingAsync(tenantId, workOrd, account);
        return affected;
    }

    /// <summary>
    /// 确保工单有工艺路线数据；若已有活跃行则跳过。
    /// 返回插入行数。
    /// </summary>
    public async Task<int> EnsureFromRoutingAsync(long tenantId, string workOrd, string account)
    {
        var existing = await _db.Ado.GetIntAsync(
            """
            SELECT COUNT(*) FROM WorkOrdRouting
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd AND IFNULL(IsActive, 0) = 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        if (existing > 0)
            return 0;

        var createUser = account.Length > 24 ? account[..24] : account;

        // 映射口径（G2）：
        //   StdRunTime = 1/Rate（小时/件）；RunCrew ← ProdLineDetail.RunCrew；
        //   Line/ProdLine ← TRIM(Line)；MachBdnRate 恢复成本语义（不再承载 Rate）
        var affected = await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO WorkOrdRouting (
                `Descr`, `Domain`, `ChargeCode`, `Machine`, `RunCrew`, `MilestoneOp`, `OP`, `StdOp`, `ItemNum`, `WorkCtr`,
                `Ufld1`, `Ufld3`, `Setup`, `MachinesperOp`, `Labor`, `RunTime`, `MachBdnRate`, `WorkCode`, `StdSetupTime`, `Engineer`,
                `WorkOrd`, `WorkOrdMasterRecID`, `ERPfld1`, `QtyOrded`, `ProcessOut`, `ProcessOutDay`, `ProcessOutSupp`,
                `IsActive`, `Status`, `ProdLine`, `Line`, `StdRunTime`,
                `CreateTime`, `CreateUser`, `tenant_id`, `CommentIndex`, `WaitTime`, `QtyComplete`, `ParentOp`
            )
            SELECT
                a.`Descr`,
                LEFT(TRIM(COALESCE(NULLIF(TRIM(IFNULL(w.`Domain`, '')), ''), NULLIF(TRIM(IFNULL(a.`Domain`, '')), ''), ' ')), 8),
                '',
                b.`InternalEquipmentCode`,
                CAST(IFNULL(NULLIF(TRIM(IFNULL(b.`RunCrew`, '')), ''), '0') AS DECIMAL(18, 4)),
                CAST(IFNULL(a.`MilestoneOp`, 0) AS UNSIGNED),
                IFNULL(a.`Op`, 0),
                a.`StdOp`,
                a.`RoutingCode`,
                LEFT(IFNULL(b.`Site`, ''), 8),
                '',
                '',
                IFNULL(a.`UDeci1`, 0),
                IFNULL(a.`UDeci2`, 0),
                IFNULL(a.`UDeci3`, 0),
                IFNULL(a.`UDeci3`, 0) / 3600.0,
                0,
                b.`OpType`,
                IFNULL(b.`SetupTime`, 0),
                b.`SkillNo`,
                w.`WorkOrd`,
                w.`RecID`,
                w.`ERPfld1`,
                IFNULL(w.`QtyOrded`, 0),
                CAST(IFNULL(a.`UDeci5`, 0) AS SIGNED),
                IFNULL(a.`ProcessOutDay`, 0),
                a.`ProcessOutSupp`,
                b'1',
                'r',
                LEFT(TRIM(IFNULL(b.`Line`, '')), 8),
                LEFT(TRIM(IFNULL(b.`Line`, '')), 8),
                CASE WHEN IFNULL(b.`Rate`, 0) > 0 THEN 1.0 / b.`Rate` ELSE NULL END,
                NOW(3),
                @CreateUser,
                w.`tenant_id`,
                CAST(IFNULL(a.`MilestoneOp`, 0) AS UNSIGNED),
                0, 0, 0
            FROM WorkOrdMaster w
            LEFT JOIN RoutingOpDetail a ON w.`ItemNum` = a.`RoutingCode`
            LEFT JOIN ProdLineDetail b ON a.`RoutingCode` = b.`Part` AND a.`Op` = b.`Op`
            WHERE w.`WorkOrd` = @WorkOrd
              AND w.`tenant_id` = @TenantId
              AND IFNULL(a.`IsActive`, 0) = 1
              AND a.`MilestoneOp` IS NOT NULL
            """,
            new SugarParameter("@WorkOrd", workOrd),
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@CreateUser", createUser));

        return affected;
    }
}