using Admin.NET.Core;
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 dates = await _db.Ado.SqlQueryAsync<DateTime>(
            """
            SELECT DISTINCT DATE(order_date) AS D
            FROM mdp_std_so
            WHERE order_date IS NOT NULL
            ORDER BY D
            """);

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

        return total;
    }

    public async Task<int> BuildWorkScheduleDomainForAllDatesAsync(string? batchId, CancellationToken cancellationToken = default)
    {
        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
            ORDER BY D
            """);

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

        return total;
    }

    public async Task<int> BuildSupplyPurchaseDomainForAllDatesAsync(string? batchId, CancellationToken cancellationToken = default)
    {
        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
            ORDER BY D
            """);

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

        return total;
    }

    public async Task<int> BuildInventoryDomainForAllDatesAsync(string? batchId, CancellationToken cancellationToken = default)
    {
        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
            ORDER BY D
            """);

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

        return total;
    }

    public async Task<int> BuildOrderDeliveryDomainAsync(DateTime statDate, string? batchId, CancellationToken cancellationToken = default)
    {
        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
            """,
            new { Domain = SmartOpsKpiAggregateRuleRegistry.DomainOrderDelivery, StatDate = date });

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

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

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

        return affected;
    }

    public async Task<int> BuildSupplyPurchaseDomainAsync(DateTime statDate, string? batchId, CancellationToken cancellationToken = default)
    {
        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
            """,
            new { Domain = SmartOpsKpiAggregateRuleRegistry.DomainSupplyPurchase, StatDate = date });

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

        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, 1, @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
                GROUP BY d.tenant_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
                });
        }

        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, 1, @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
                GROUP BY d.tenant_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
                });
        }

        return affected;
    }

    public async Task<int> BuildWorkScheduleDomainAsync(DateTime statDate, string? batchId, CancellationToken cancellationToken = default)
    {
        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
            """,
            new { Domain = domain, StatDate = date });

        foreach (var metricCode in new[] { "S2_L1_001", "S6_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 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 });
        }

        foreach (var metricCode in new[] { "S2_L1_002", "S6_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
                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 });
        }

        foreach (var metricCode in new[] { "S2_L1_003", "S6_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
                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 });
        }

        return affected;
    }

    public async Task<int> BuildInventoryDomainAsync(DateTime statDate, string? batchId, CancellationToken cancellationToken = default)
    {
        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
            """,
            new { Domain = domain, StatDate = date });

        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, 1, @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
            GROUP BY tenant_id, stat_date, IFNULL(component_item_code, ''), IFNULL(work_order, '')
            """,
            new { Domain = domain, StatDate = date, BatchId = batchId, Now = now });

        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, 1, @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
            GROUP BY tenant_id, stat_date, IFNULL(component_item_code, ''), IFNULL(work_order, '')
            """,
            new { Domain = domain, StatDate = date, BatchId = batchId, Now = now });

        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, 1, @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
            GROUP BY tenant_id, stat_date, IFNULL(component_item_code, ''), IFNULL(work_order, '')
            """,
            new { Domain = domain, StatDate = date, BatchId = batchId, Now = now });

        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, 1, @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
            GROUP BY tenant_id, stat_date, IFNULL(component_item_code, ''), IFNULL(work_order, '')
            """,
            new { Domain = domain, StatDate = date, BatchId = batchId, Now = now });

        return affected;
    }
}