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

        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`, `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`,
                IFNULL(b.`StandardStaffCount`, 0),
                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,
                IFNULL(b.`Rate`, 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(IFNULL(b.`Line`, ''), 8),
                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;
    }
}