AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/SmartOps/SmartOpsKpiAtomicBuildService.cs

using Admin.NET.Core;
using Admin.NET.Plugin.AiDOP.DataPlatform.S1Refresh;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.SmartOps;

/// <summary>
/// 从 DWD/STD 事实写入 KPI 原子聚合层（最小颗粒度，不做二次汇总）。
/// </summary>
public class SmartOpsKpiAtomicBuildService : ITransient
{
    private readonly ISqlSugarClient _db;

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

    public async Task<int> BuildOrderDeliveryDomainForAllDatesAsync(string? batchId, CancellationToken cancellationToken = default)
    {
        AidopTenantMigration.EnsureKpiAtomicTable(_db);
        var scopes = await _db.Ado.SqlQueryAsync<S1MdpScopeRow>(
            """
            SELECT DISTINCT tenant_id AS TenantId, COALESCE(NULLIF(factory_id, 0), 1) AS FactoryId
            FROM mdp_std_so
            WHERE tenant_id > 0 AND order_date IS NOT NULL
            """);
        var total = 0;
        foreach (var scope in scopes)
        {
            cancellationToken.ThrowIfCancellationRequested();
            total += await BuildOrderDeliveryDomainForAllDatesAsync(scope.TenantId, scope.FactoryId, batchId, cancellationToken);
        }
        return total;
    }

    public async Task<int> BuildOrderDeliveryDomainForAllDatesAsync(
        long tenantId,
        long factoryId,
        string? batchId,
        CancellationToken cancellationToken = default)
    {
        if (tenantId <= 0 || factoryId <= 0)
            throw new InvalidOperationException("S1 Atomic 必须指定有效 tenantId/factoryId");
        AidopTenantMigration.EnsureKpiAtomicTable(_db);
        var dates = await _db.Ado.SqlQueryAsync<DateTime>(
            """
            SELECT DISTINCT DATE(order_date) AS D
            FROM mdp_std_so
            WHERE order_date IS NOT NULL
              AND tenant_id=@TenantId
              AND COALESCE(NULLIF(factory_id, 0), 1)=@FactoryId
            ORDER BY D
            """,
            new { TenantId = tenantId, FactoryId = factoryId });

        var total = 0;
        foreach (var date in dates)
        {
            cancellationToken.ThrowIfCancellationRequested();
            total += await BuildOrderDeliveryDomainAsync(date, tenantId, factoryId, batchId, cancellationToken);
        }

        return total;
    }

    public async Task<int> BuildWorkScheduleDomainForAllDatesAsync(string? batchId, CancellationToken cancellationToken = default)
    {
        AidopTenantMigration.EnsureKpiAtomicTable(_db);
        var scopes = await _db.Ado.SqlQueryAsync<S1MdpScopeRow>(
            """
            SELECT DISTINCT tenant_id AS TenantId, COALESCE(NULLIF(factory_id, 0), 1) AS FactoryId
            FROM dwd_order_schedule_trans
            WHERE tenant_id > 0 AND stat_date IS NOT NULL
            """);
        var total = 0;
        foreach (var scope in scopes)
        {
            cancellationToken.ThrowIfCancellationRequested();
            total += await BuildWorkScheduleDomainForAllDatesAsync(scope.TenantId, scope.FactoryId, batchId, cancellationToken);
        }
        return total;
    }

    public async Task<int> BuildWorkScheduleDomainForAllDatesAsync(
        long tenantId,
        long factoryId,
        string? batchId,
        CancellationToken cancellationToken = default)
    {
        if (tenantId <= 0 || factoryId <= 0)
            throw new InvalidOperationException("S2 Atomic 必须指定有效 tenantId/factoryId");
        AidopTenantMigration.EnsureKpiAtomicTable(_db);
        var dates = await _db.Ado.SqlQueryAsync<DateTime>(
            """
            SELECT DISTINCT stat_date AS D
            FROM dwd_order_schedule_trans
            WHERE stat_date IS NOT NULL
              AND tenant_id=@TenantId
              AND COALESCE(NULLIF(factory_id, 0), 1)=@FactoryId
            ORDER BY D
            """,
            new { TenantId = tenantId, FactoryId = factoryId });

        var total = 0;
        foreach (var date in dates)
        {
            cancellationToken.ThrowIfCancellationRequested();
            total += await BuildWorkScheduleDomainAsync(date, tenantId, factoryId, batchId, cancellationToken);
        }

        return total;
    }

    public async Task<int> BuildSupplyPurchaseDomainForAllDatesAsync(string? batchId, CancellationToken cancellationToken = default)
    {
        AidopTenantMigration.EnsureKpiAtomicTable(_db);
        var scopes = await _db.Ado.SqlQueryAsync<S1MdpScopeRow>(
            """
            SELECT DISTINCT tenant_id AS TenantId, COALESCE(NULLIF(factory_id, 0), 1) AS FactoryId
            FROM dwd_supplier_delivery
            WHERE tenant_id > 0 AND stat_date IS NOT NULL
            """);
        var total = 0;
        foreach (var scope in scopes)
        {
            cancellationToken.ThrowIfCancellationRequested();
            total += await BuildSupplyPurchaseDomainForAllDatesAsync(scope.TenantId, scope.FactoryId, batchId, cancellationToken);
        }
        return total;
    }

    public async Task<int> BuildSupplyPurchaseDomainForAllDatesAsync(
        long tenantId,
        long factoryId,
        string? batchId,
        CancellationToken cancellationToken = default)
    {
        if (tenantId <= 0 || factoryId <= 0)
            throw new InvalidOperationException("S3 Atomic 必须指定有效 tenantId/factoryId");
        AidopTenantMigration.EnsureKpiAtomicTable(_db);
        var dates = await _db.Ado.SqlQueryAsync<DateTime>(
            """
            SELECT DISTINCT stat_date AS D
            FROM dwd_supplier_delivery
            WHERE stat_date IS NOT NULL
              AND tenant_id=@TenantId
              AND COALESCE(NULLIF(factory_id, 0), 1)=@FactoryId
            ORDER BY D
            """,
            new { TenantId = tenantId, FactoryId = factoryId });

        var total = 0;
        foreach (var date in dates)
        {
            cancellationToken.ThrowIfCancellationRequested();
            total += await BuildSupplyPurchaseDomainAsync(date, tenantId, factoryId, batchId, cancellationToken);
        }

        return total;
    }

    public async Task<int> BuildInventoryDomainForAllDatesAsync(string? batchId, CancellationToken cancellationToken = default)
    {
        AidopTenantMigration.EnsureKpiAtomicTable(_db);
        var scopes = await _db.Ado.SqlQueryAsync<S1MdpScopeRow>(
            """
            SELECT DISTINCT tenant_id AS TenantId, factory_id AS FactoryId
            FROM dwd_material_readiness
            WHERE tenant_id > 0 AND stat_date IS NOT NULL
            """);
        var total = 0;
        foreach (var scope in scopes)
        {
            cancellationToken.ThrowIfCancellationRequested();
            total += await BuildInventoryDomainForAllDatesAsync(scope.TenantId, scope.FactoryId, batchId, cancellationToken);
        }
        return total;
    }

    public async Task<int> BuildInventoryDomainForAllDatesAsync(
        long tenantId,
        long factoryId,
        string? batchId,
        CancellationToken cancellationToken = default)
    {
        if (tenantId <= 0 || factoryId <= 0)
            throw new InvalidOperationException("S3 Atomic 必须指定有效 tenantId/factoryId");
        AidopTenantMigration.EnsureKpiAtomicTable(_db);
        var dates = await _db.Ado.SqlQueryAsync<DateTime>(
            """
            SELECT DISTINCT stat_date AS D
            FROM dwd_material_readiness
            WHERE stat_date IS NOT NULL
              AND tenant_id=@TenantId
              AND factory_id=@FactoryId
            ORDER BY D
            """,
            new { TenantId = tenantId, FactoryId = factoryId });

        var total = 0;
        foreach (var date in dates)
        {
            cancellationToken.ThrowIfCancellationRequested();
            total += await BuildInventoryDomainAsync(date, tenantId, factoryId, batchId, cancellationToken);
        }

        return total;
    }

    public async Task<int> BuildOrderDeliveryDomainAsync(
        DateTime statDate,
        long tenantId,
        long factoryId,
        string? batchId,
        CancellationToken cancellationToken = default)
    {
        if (tenantId <= 0 || factoryId <= 0)
            throw new InvalidOperationException("S1 Atomic 必须指定有效 tenantId/factoryId");
        AidopTenantMigration.EnsureKpiAtomicTable(_db);
        var date = statDate.Date;
        var now = DateTime.Now;
        var affected = 0;

        affected += await _db.Ado.ExecuteCommandAsync(
            """
            DELETE FROM ado_smart_ops_kpi_atomic_day
            WHERE domain_code = @Domain
              AND stat_date = @StatDate
              AND tenant_id = @TenantId
              AND factory_id = @FactoryId
            """,
            new { Domain = SmartOpsKpiAggregateRuleRegistry.DomainOrderDelivery, StatDate = date, TenantId = tenantId, FactoryId = factoryId });

        foreach (var metricCode in new[] { "S1_L1_001", "S7_L1_001", "S9_L1_002" })
        {
            affected += await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_smart_ops_kpi_atomic_day
                (tenant_id, factory_id, domain_code, metric_code, metric_level, stat_date,
                 customer_code, product_code, order_no, production_line,
                 sum_value, sample_count, batch_id, calc_time, grain, is_deleted)
                SELECT
                    so.tenant_id,
                    COALESCE(NULLIF(so.factory_id, 0), 1),
                    @Domain,
                    @MetricCode,
                    1,
                    DATE(so.order_date),
                    IFNULL(so.customer_no, ''),
                    IFNULL(so.item_code, ''),
                    IFNULL(so.order_no, ''),
                    '',
                    ROUND(SUM(TIMESTAMPDIFF(HOUR, so.order_date,
                        COALESCE(so.promised_delivery_date, so.capacity_date, so.material_ready_date, so.plan_delivery_date)) / 24), 6),
                    COUNT(1),
                    @BatchId,
                    @Now,
                    'day',
                    0
                FROM mdp_std_so so
                WHERE so.order_date IS NOT NULL
                  AND COALESCE(so.promised_delivery_date, so.capacity_date, so.material_ready_date, so.plan_delivery_date) IS NOT NULL
                  AND COALESCE(so.promised_delivery_date, so.capacity_date, so.material_ready_date, so.plan_delivery_date) >= so.order_date
                  AND DATE(so.order_date) = @StatDate
                  AND so.tenant_id = @TenantId
                  AND COALESCE(NULLIF(so.factory_id, 0), 1) = @FactoryId
                GROUP BY so.tenant_id, COALESCE(NULLIF(so.factory_id, 0), 1),
                         DATE(so.order_date), IFNULL(so.customer_no, ''), IFNULL(so.item_code, ''), IFNULL(so.order_no, '')
                """,
                new
                {
                    Domain = SmartOpsKpiAggregateRuleRegistry.DomainOrderDelivery,
                    MetricCode = metricCode,
                    StatDate = date,
                    BatchId = batchId,
                    Now = now,
                    TenantId = tenantId,
                    FactoryId = factoryId
                });
        }

        foreach (var metricCode in new[] { "S1_L1_002", "S7_L1_002", "S9_L1_003" })
        {
            affected += await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_smart_ops_kpi_atomic_day
                (tenant_id, factory_id, domain_code, metric_code, metric_level, stat_date,
                 customer_code, product_code, order_no, production_line,
                 numerator_value, denominator_value, batch_id, calc_time, grain, is_deleted)
                SELECT
                    so.tenant_id,
                    COALESCE(NULLIF(so.factory_id, 0), 1),
                    @Domain,
                    @MetricCode,
                    1,
                    DATE(so.order_date),
                    IFNULL(so.customer_no, ''),
                    IFNULL(so.item_code, ''),
                    IFNULL(so.order_no, ''),
                    '',
                    SUM(CASE WHEN TIMESTAMPDIFF(HOUR, so.order_date,
                        COALESCE(so.promised_delivery_date, so.capacity_date, so.material_ready_date, so.plan_delivery_date)) <= 72 THEN 1 ELSE 0 END),
                    COUNT(1),
                    @BatchId,
                    @Now,
                    'day',
                    0
                FROM mdp_std_so so
                WHERE so.order_date IS NOT NULL
                  AND COALESCE(so.promised_delivery_date, so.capacity_date, so.material_ready_date, so.plan_delivery_date) IS NOT NULL
                  AND DATE(so.order_date) = @StatDate
                  AND so.tenant_id = @TenantId
                  AND COALESCE(NULLIF(so.factory_id, 0), 1) = @FactoryId
                GROUP BY so.tenant_id, COALESCE(NULLIF(so.factory_id, 0), 1),
                         DATE(so.order_date), IFNULL(so.customer_no, ''), IFNULL(so.item_code, ''), IFNULL(so.order_no, '')
                """,
                new
                {
                    Domain = SmartOpsKpiAggregateRuleRegistry.DomainOrderDelivery,
                    MetricCode = metricCode,
                    StatDate = date,
                    BatchId = batchId,
                    Now = now,
                    TenantId = tenantId,
                    FactoryId = factoryId
                });
        }

        foreach (var metricCode in new[] { "S1_L1_003", "S7_L1_003" })
        {
            affected += await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_smart_ops_kpi_atomic_day
                (tenant_id, factory_id, domain_code, metric_code, metric_level, stat_date,
                 customer_code, product_code, order_no, production_line,
                 numerator_value, denominator_value, batch_id, calc_time, grain, is_deleted)
                SELECT
                    so.tenant_id,
                    COALESCE(NULLIF(so.factory_id, 0), 1),
                    @Domain,
                    @MetricCode,
                    1,
                    DATE(so.order_date),
                    IFNULL(so.customer_no, ''),
                    IFNULL(so.item_code, ''),
                    IFNULL(so.order_no, ''),
                    '',
                    COUNT(1),
                    GREATEST(COUNT(DISTINCT NULLIF(so.planner_no, '')), 1),
                    @BatchId,
                    @Now,
                    'day',
                    0
                FROM mdp_std_so so
                WHERE so.order_date IS NOT NULL
                  AND DATE(so.order_date) = @StatDate
                  AND so.tenant_id = @TenantId
                  AND COALESCE(NULLIF(so.factory_id, 0), 1) = @FactoryId
                GROUP BY so.tenant_id, COALESCE(NULLIF(so.factory_id, 0), 1),
                         DATE(so.order_date), IFNULL(so.customer_no, ''), IFNULL(so.item_code, ''), IFNULL(so.order_no, '')
                """,
                new
                {
                    Domain = SmartOpsKpiAggregateRuleRegistry.DomainOrderDelivery,
                    MetricCode = metricCode,
                    StatDate = date,
                    BatchId = batchId,
                    Now = now,
                    TenantId = tenantId,
                    FactoryId = factoryId
                });
        }

        return affected;
    }

    public async Task<int> BuildSupplyPurchaseDomainAsync(
        DateTime statDate, long tenantId, long factoryId, string? batchId, CancellationToken cancellationToken = default)
    {
        if (tenantId <= 0 || factoryId <= 0)
            throw new InvalidOperationException("S3 Atomic 必须指定有效 tenantId/factoryId");
        AidopTenantMigration.EnsureKpiAtomicTable(_db);
        var date = statDate.Date;
        var now = DateTime.Now;
        var affected = 0;

        affected += await _db.Ado.ExecuteCommandAsync(
            """
            DELETE FROM ado_smart_ops_kpi_atomic_day
            WHERE domain_code = @Domain
              AND stat_date = @StatDate
              AND tenant_id = @TenantId
              AND factory_id = @FactoryId
            """,
            new { Domain = SmartOpsKpiAggregateRuleRegistry.DomainSupplyPurchase, StatDate = date, TenantId = tenantId, FactoryId = factoryId });

        foreach (var metricCode in new[] { "S3_L1_001", "S4_L1_001" })
        {
            affected += await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_smart_ops_kpi_atomic_day
                (tenant_id, factory_id, domain_code, metric_code, metric_level, stat_date,
                 supplier_code, material_code, po_no,
                 sum_value, sample_count, batch_id, calc_time, grain, is_deleted)
                SELECT tenant_id, 1, @Domain, @MetricCode, 1, DATE(request_date),
                       IFNULL(supplier_code, ''), IFNULL(item_code, ''), IFNULL(po_no, ''),
                       ROUND(SUM(TIMESTAMPDIFF(HOUR, request_date, submit_date) / 24), 6),
                       COUNT(1), @BatchId, @Now, 'day', 0
                FROM mdp_std_delivery_schedule
                WHERE request_date IS NOT NULL AND submit_date IS NOT NULL AND submit_date >= request_date
                  AND DATE(request_date) = @StatDate
                  AND tenant_id = @TenantId
                GROUP BY tenant_id, DATE(request_date), IFNULL(supplier_code, ''), IFNULL(item_code, ''), IFNULL(po_no, '')
                """,
                new
                {
                    Domain = SmartOpsKpiAggregateRuleRegistry.DomainSupplyPurchase,
                    MetricCode = metricCode,
                    StatDate = date,
                    BatchId = batchId,
                    Now = now,
                    TenantId = tenantId,
                    FactoryId = factoryId
                });
        }

        foreach (var metricCode in new[] { "S4_L1_002", "S3_L1_002" })
        {
            affected += await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_smart_ops_kpi_atomic_day
                (tenant_id, factory_id, domain_code, metric_code, metric_level, stat_date,
                 supplier_code, material_code, po_no,
                 numerator_value, denominator_value, batch_id, calc_time, grain, is_deleted)
                SELECT tenant_id, factory_id, @Domain, @MetricCode, 1, d.stat_date,
                       IFNULL(d.supplier_code, ''), IFNULL(d.item_code, ''), IFNULL(d.po_no, ''),
                       SUM(CASE WHEN IFNULL(d.receipt_qty, 0) >= IFNULL(d.order_qty, 0) AND IFNULL(d.order_qty, 0) > 0 THEN 1
                                WHEN d.delivery_status = 'COMPLETED' THEN 1 ELSE 0 END),
                       SUM(CASE WHEN IFNULL(d.order_qty, 0) > 0 THEN 1 ELSE 0 END),
                       @BatchId, @Now, 'day', 0
                FROM dwd_supplier_delivery d
                WHERE d.stat_date = @StatDate AND d.tenant_id = @TenantId AND d.factory_id = @FactoryId
                GROUP BY d.tenant_id, d.factory_id, d.stat_date, IFNULL(d.supplier_code, ''), IFNULL(d.item_code, ''), IFNULL(d.po_no, '')
                HAVING SUM(CASE WHEN IFNULL(d.order_qty, 0) > 0 THEN 1 ELSE 0 END) > 0
                """,
                new
                {
                    Domain = SmartOpsKpiAggregateRuleRegistry.DomainSupplyPurchase,
                    MetricCode = metricCode,
                    StatDate = date,
                    BatchId = batchId,
                    Now = now,
                    TenantId = tenantId,
                    FactoryId = factoryId
                });
        }

        foreach (var metricCode in new[] { "S4_L1_003", "S3_L1_003" })
        {
            affected += await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_smart_ops_kpi_atomic_day
                (tenant_id, factory_id, domain_code, metric_code, metric_level, stat_date,
                 supplier_code, material_code, po_no,
                 numerator_value, denominator_value, batch_id, calc_time, grain, is_deleted)
                SELECT tenant_id, factory_id, @Domain, @MetricCode, 1, d.stat_date,
                       IFNULL(d.supplier_code, ''), IFNULL(d.item_code, ''), IFNULL(d.po_no, ''),
                       SUM(IFNULL(d.receipt_qty, 0)),
                       GREATEST(COUNT(DISTINCT NULLIF(d.supplier_code, '')), 1),
                       @BatchId, @Now, 'day', 0
                FROM dwd_supplier_delivery d
                WHERE d.stat_date = @StatDate AND d.tenant_id = @TenantId AND d.factory_id = @FactoryId
                GROUP BY d.tenant_id, d.factory_id, d.stat_date, IFNULL(d.supplier_code, ''), IFNULL(d.item_code, ''), IFNULL(d.po_no, '')
                """,
                new
                {
                    Domain = SmartOpsKpiAggregateRuleRegistry.DomainSupplyPurchase,
                    MetricCode = metricCode,
                    StatDate = date,
                    BatchId = batchId,
                    Now = now,
                    TenantId = tenantId,
                    FactoryId = factoryId
                });
        }

        return affected;
    }

    public async Task<int> BuildWorkScheduleDomainAsync(
        DateTime statDate, long tenantId, long factoryId, string? batchId, CancellationToken cancellationToken = default)
    {
        if (tenantId <= 0 || factoryId <= 0)
            throw new InvalidOperationException("S2 Atomic 必须指定有效 tenantId/factoryId");
        AidopTenantMigration.EnsureKpiAtomicTable(_db);
        var date = statDate.Date;
        var now = DateTime.Now;
        var affected = 0;
        var domain = SmartOpsKpiAggregateRuleRegistry.DomainWorkSchedule;

        affected += await _db.Ado.ExecuteCommandAsync(
            """
            DELETE FROM ado_smart_ops_kpi_atomic_day
            WHERE domain_code = @Domain AND stat_date = @StatDate
              AND tenant_id = @TenantId AND factory_id = @FactoryId
            """,
            new { Domain = domain, StatDate = date, TenantId = tenantId, FactoryId = factoryId });

        // 口径归位（S6-L1-KPI-CONTRACT-RESOLUTION-1）：S2 排程周期只属 S2_L1_001，
        //   不再别名写 S6_L1_001（订单制造周期≠排程周期，S6 L1 由 Phase 2 订单口径实现）。
        foreach (var metricCode in new[] { "S2_L1_001" })
        {
            affected += await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_smart_ops_kpi_atomic_day
                (tenant_id, factory_id, domain_code, metric_code, metric_level, stat_date,
                 product_code, order_no, production_line, work_order_no,
                 sum_value, sample_count, batch_id, calc_time, grain, is_deleted)
                SELECT tenant_id, factory_id, @Domain, @MetricCode, 1, stat_date,
                       IFNULL(item_code, ''), IFNULL(sales_order_no, ''), IFNULL(prod_line, ''), IFNULL(work_order, ''),
                       ROUND(SUM(schedule_cycle_days), 6), COUNT(1), @BatchId, @Now, 'day', 0
                FROM dwd_order_schedule_trans
                WHERE stat_date = @StatDate AND tenant_id = @TenantId AND COALESCE(NULLIF(factory_id, 0), 1) = @FactoryId AND schedule_cycle_days IS NOT NULL AND schedule_cycle_days >= 0
                GROUP BY tenant_id, factory_id, stat_date, IFNULL(item_code, ''), IFNULL(sales_order_no, ''),
                         IFNULL(prod_line, ''), IFNULL(work_order, '')
                """,
                new { Domain = domain, MetricCode = metricCode, StatDate = date, BatchId = batchId, Now = now, TenantId = tenantId, FactoryId = factoryId });
        }

        // 口径归位：S2 排程满足率只属 S2_L1_002，不再别名写 S6_L1_002。
        foreach (var metricCode in new[] { "S2_L1_002" })
        {
            affected += await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_smart_ops_kpi_atomic_day
                (tenant_id, factory_id, domain_code, metric_code, metric_level, stat_date,
                 product_code, order_no, production_line, work_order_no,
                 numerator_value, denominator_value, batch_id, calc_time, grain, is_deleted)
                SELECT tenant_id, factory_id, @Domain, @MetricCode, 1, stat_date,
                       IFNULL(item_code, ''), IFNULL(sales_order_no, ''), IFNULL(prod_line, ''), IFNULL(work_order, ''),
                       SUM(IFNULL(schedule_satisfaction_flag, 0)), COUNT(1), @BatchId, @Now, 'day', 0
                FROM dwd_order_schedule_trans
                WHERE stat_date = @StatDate AND tenant_id = @TenantId AND COALESCE(NULLIF(factory_id, 0), 1) = @FactoryId
                GROUP BY tenant_id, factory_id, stat_date, IFNULL(item_code, ''), IFNULL(sales_order_no, ''),
                         IFNULL(prod_line, ''), IFNULL(work_order, '')
                """,
                new { Domain = domain, MetricCode = metricCode, StatDate = date, BatchId = batchId, Now = now, TenantId = tenantId, FactoryId = factoryId });
        }

        // 口径归位：S2 排程人效只属 S2_L1_003，不再别名写 S6_L1_003（订单制造人效由 Phase 2 订单口径实现）。
        foreach (var metricCode in new[] { "S2_L1_003", "S9_L1_004" })
        {
            affected += await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_smart_ops_kpi_atomic_day
                (tenant_id, factory_id, domain_code, metric_code, metric_level, stat_date,
                 product_code, order_no, production_line, work_order_no,
                 numerator_value, denominator_value, batch_id, calc_time, grain, is_deleted)
                SELECT tenant_id, factory_id, @Domain, @MetricCode, 1, stat_date,
                       IFNULL(item_code, ''), IFNULL(sales_order_no, ''), IFNULL(prod_line, ''), IFNULL(work_order, ''),
                       COUNT(1), GREATEST(SUM(CASE WHEN IFNULL(resource_person_count, 0) > 0 THEN resource_person_count ELSE 1 END), 1),
                       @BatchId, @Now, 'day', 0
                FROM dwd_order_schedule_trans
                WHERE stat_date = @StatDate AND tenant_id = @TenantId AND COALESCE(NULLIF(factory_id, 0), 1) = @FactoryId
                GROUP BY tenant_id, factory_id, stat_date, IFNULL(item_code, ''), IFNULL(sales_order_no, ''),
                         IFNULL(prod_line, ''), IFNULL(work_order, '')
                """,
                new { Domain = domain, MetricCode = metricCode, StatDate = date, BatchId = batchId, Now = now, TenantId = tenantId, FactoryId = factoryId });
        }

        return affected;
    }

    public async Task<int> BuildInventoryDomainAsync(
        DateTime statDate, long tenantId, long factoryId, string? batchId, CancellationToken cancellationToken = default)
    {
        if (tenantId <= 0 || factoryId <= 0)
            throw new InvalidOperationException("S3 Atomic 必须指定有效 tenantId/factoryId");
        AidopTenantMigration.EnsureKpiAtomicTable(_db);
        var date = statDate.Date;
        var now = DateTime.Now;
        var affected = 0;
        var domain = SmartOpsKpiAggregateRuleRegistry.DomainInventory;

        affected += await _db.Ado.ExecuteCommandAsync(
            """
            DELETE FROM ado_smart_ops_kpi_atomic_day
            WHERE domain_code = @Domain AND stat_date = @StatDate
              AND tenant_id = @TenantId AND factory_id = @FactoryId
            """,
            new { Domain = domain, StatDate = date, TenantId = tenantId, FactoryId = factoryId });

        affected += await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO ado_smart_ops_kpi_atomic_day
            (tenant_id, factory_id, domain_code, metric_code, metric_level, stat_date,
             warehouse_code, material_code, work_order_no,
             sum_value, sample_count, batch_id, calc_time, grain, is_deleted)
            SELECT tenant_id, factory_id, @Domain, 'S5_L1_001', 1, stat_date,
                   '', IFNULL(component_item_code, ''), IFNULL(work_order, ''),
                   ROUND(SUM(GREATEST(TIMESTAMPDIFF(DAY, stat_date, IFNULL(need_date, stat_date)), 0)), 6),
                   COUNT(1), @BatchId, @Now, 'day', 0
            FROM dwd_material_readiness
            WHERE stat_date = @StatDate AND tenant_id = @TenantId AND factory_id = @FactoryId
            GROUP BY tenant_id, factory_id, stat_date, IFNULL(component_item_code, ''), IFNULL(work_order, '')
            """,
            new { Domain = domain, StatDate = date, BatchId = batchId, Now = now, TenantId = tenantId, FactoryId = factoryId });

        affected += await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO ado_smart_ops_kpi_atomic_day
            (tenant_id, factory_id, domain_code, metric_code, metric_level, stat_date,
             warehouse_code, material_code, work_order_no,
             numerator_value, denominator_value, batch_id, calc_time, grain, is_deleted)
            SELECT tenant_id, factory_id, @Domain, 'S5_L1_002', 1, stat_date,
                   '', IFNULL(component_item_code, ''), IFNULL(work_order, ''),
                   SUM(CASE WHEN IFNULL(shortage_qty, 0) <= 0 OR UPPER(IFNULL(ready_status, '')) IN ('READY', 'SUFFICIENT', 'OK') THEN 1 ELSE 0 END),
                   COUNT(1), @BatchId, @Now, 'day', 0
            FROM dwd_material_readiness
            WHERE stat_date = @StatDate AND tenant_id = @TenantId AND factory_id = @FactoryId
            GROUP BY tenant_id, factory_id, stat_date, IFNULL(component_item_code, ''), IFNULL(work_order, '')
            """,
            new { Domain = domain, StatDate = date, BatchId = batchId, Now = now, TenantId = tenantId, FactoryId = factoryId });

        affected += await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO ado_smart_ops_kpi_atomic_day
            (tenant_id, factory_id, domain_code, metric_code, metric_level, stat_date,
             warehouse_code, material_code, work_order_no,
             numerator_value, denominator_value, batch_id, calc_time, grain, is_deleted)
            SELECT tenant_id, factory_id, @Domain, 'S5_L1_003', 1, stat_date,
                   '', IFNULL(component_item_code, ''), IFNULL(work_order, ''),
                   COUNT(1), GREATEST(COUNT(DISTINCT NULLIF(supplier_code, '')), 1),
                   @BatchId, @Now, 'day', 0
            FROM dwd_material_readiness
            WHERE stat_date = @StatDate AND tenant_id = @TenantId AND factory_id = @FactoryId
            GROUP BY tenant_id, factory_id, stat_date, IFNULL(component_item_code, ''), IFNULL(work_order, '')
            """,
            new { Domain = domain, StatDate = date, BatchId = batchId, Now = now, TenantId = tenantId, FactoryId = factoryId });

        affected += await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO ado_smart_ops_kpi_atomic_day
            (tenant_id, factory_id, domain_code, metric_code, metric_level, stat_date,
             warehouse_code, material_code, work_order_no,
             sum_value, sample_count, batch_id, calc_time, grain, is_deleted)
            SELECT tenant_id, factory_id, @Domain, 'S9_L1_005', 1, stat_date,
                   '', IFNULL(component_item_code, ''), IFNULL(work_order, ''),
                   ROUND(SUM(GREATEST(IFNULL(shortage_qty, 0), 0)), 6),
                   COUNT(1), @BatchId, @Now, 'day', 0
            FROM dwd_material_readiness
            WHERE stat_date = @StatDate AND tenant_id = @TenantId AND factory_id = @FactoryId
            GROUP BY tenant_id, factory_id, stat_date, IFNULL(component_item_code, ''), IFNULL(work_order, '')
            """,
            new { Domain = domain, StatDate = date, BatchId = batchId, Now = now, TenantId = tenantId, FactoryId = factoryId });

        return affected;
    }
}