AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Service/S8/OrderFlow/S8OrderReviewKpiService.cs

using Admin.NET.Plugin.AiDOP.Dto.S8.OrderFlow;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using Admin.NET.Plugin.AiDOP.Order;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.Service.S8.OrderFlow;

/// <summary>
/// S8 Stage-1「订单评审」实际耗时只读服务。
///
/// <para><b>数据来源边界（Principle 2）</b>：本服务<b>只读 <c>dwd_order_review_kpi</c></b>，
/// 不触碰 <c>aidop_action_run_log</c> / <c>crm_seorder</c> / <c>crm_seorderentry</c> /
/// <c>ApprovalFlow*</c>。配对与过滤已在 <see cref="ActionEventMdpSyncService"/> 的中台链路内完成。</para>
///
/// <para><b>租户边界（Principle 1）</b>：租户一律由 <see cref="AidopTenantScope.ResolveOrThrow"/>
/// 从认证后的 JWT 解析，<b>不接受前端传入的 tenantId</b>，无默认回退。</para>
/// </summary>
public class S8OrderReviewKpiService : ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly ActionEventMdpSyncService _syncService;

    public S8OrderReviewKpiService(
        ISqlSugarClient db,
        UserManager userManager,
        ActionEventMdpSyncService syncService)
    {
        _db = db;
        _userManager = userManager;
        _syncService = syncService;
    }

    /// <summary>分页查询本租户的订单评审耗时。</summary>
    public async Task<AdoS8OrderReviewKpiListDto> GetListAsync(AdoS8OrderReviewKpiQueryDto query)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var page = query.Page <= 0 ? 1 : query.Page;
        var pageSize = query.PageSize is <= 0 or > 200 ? 20 : query.PageSize;

        var where = "`tenant_id` = @TenantId";
        var ps = new List<SugarParameter> { new("@TenantId", tenantId) };

        if (!string.IsNullOrWhiteSpace(query.OrderNo))
        {
            where += " AND `sales_order_no` LIKE @OrderNo";
            ps.Add(new SugarParameter("@OrderNo", $"%{query.OrderNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(query.CalculationStatus))
        {
            where += " AND `calculation_status` = @CalcStatus";
            ps.Add(new SugarParameter("@CalcStatus", query.CalculationStatus.Trim().ToUpperInvariant()));
        }

        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(*) FROM `dwd_order_review_kpi` WHERE {where}", ps);

        ps.Add(new SugarParameter("@Skip", (page - 1) * pageSize));
        ps.Add(new SugarParameter("@Take", pageSize));

        var rows = await _db.Ado.SqlQueryAsync<AdoS8OrderReviewKpiRowDto>($"""
            SELECT `sales_order_id`        AS SalesOrderId,
                   `sales_order_no`        AS SalesOrderNo,
                   `review_start_time`     AS ReviewStartTime,
                   `review_end_time`       AS ReviewEndTime,
                   `actual_hours`          AS ActualHours,
                   `calculation_status`    AS CalculationStatus,
                   `review_success_count`  AS ReviewSuccessCount,
                   `confirm_success_count` AS ConfirmSuccessCount,
                   `review_failed_count`   AS ReviewFailedCount,
                   `confirm_failed_count`  AS ConfirmFailedCount,
                   `review_action_run_id`  AS ReviewActionRunId,
                   `confirm_action_run_id` AS ConfirmActionRunId,
                   `calc_time`             AS CalcTime
              FROM `dwd_order_review_kpi`
             WHERE {where}
             ORDER BY `review_start_time` DESC, `sales_order_id` DESC
             LIMIT @Skip, @Take
            """, ps);

        return new AdoS8OrderReviewKpiListDto
        {
            List = rows ?? [],
            Total = total,
            Page = page,
            PageSize = pageSize
        };
    }

    /// <summary>
    /// 本租户 Stage-1 评审耗时汇总。
    /// <para>均值只对 <c>calculation_status='OK'</c> 求 —— 未闭环订单不按 0 参与，避免把 KPI 拉低成假象。</para>
    /// </summary>
    public async Task<AdoS8OrderReviewKpiSummaryDto> GetSummaryAsync()
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);

        var list = await _db.Ado.SqlQueryAsync<AdoS8OrderReviewKpiSummaryDto>("""
            SELECT COUNT(*)                                                     AS TotalOrders,
                   SUM(`calculation_status` = 'OK')                             AS OkCount,
                   SUM(`calculation_status` = 'IN_PROGRESS')                    AS InProgressCount,
                   SUM(`calculation_status` = 'NO_START')                       AS NoStartCount,
                   SUM(`calculation_status` = 'INVALID')                        AS InvalidCount,
                   SUM(`calculation_status` = 'INVALID_TIME_SEQUENCE')          AS InvalidTimeSequenceCount,
                   ROUND(AVG(CASE WHEN `calculation_status` = 'OK' THEN `actual_hours` END), 2) AS AvgActualHours,
                   MIN(CASE WHEN `calculation_status` = 'OK' THEN `actual_hours` END)           AS MinActualHours,
                   MAX(CASE WHEN `calculation_status` = 'OK' THEN `actual_hours` END)           AS MaxActualHours,
                   MAX(`calc_time`)                                             AS LastCalcTime
              FROM `dwd_order_review_kpi`
             WHERE `tenant_id` = @TenantId
            """, new List<SugarParameter> { new("@TenantId", tenantId) });

        return list?.FirstOrDefault() ?? new AdoS8OrderReviewKpiSummaryDto();
    }

    /// <summary>
    /// 手动触发链路重算：源 → STG（增量 + RUNNING 回捞）→ STD → DWD KPI。
    /// 只对当前登录租户生效；幂等，可重复调用。
    /// </summary>
    public async Task<AdoS8OrderReviewKpiRefreshResultDto> RefreshAsync(CancellationToken cancellationToken = default)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var r = await _syncService.RunAsync(tenantId, cancellationToken);
        return new AdoS8OrderReviewKpiRefreshResultDto
        {
            BatchId = r.BatchId,
            StageRows = r.StageRows,
            StageRepulledRunning = r.StageRepulledRunning,
            StandardRows = r.StandardRows,
            KpiRows = r.KpiRows
        };
    }
}