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

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

/// <summary>
/// S8「订单执行档案」<b>真实订单</b> Read Model。
///
/// <para><b>数据链</b>：
/// <c>mdp_std_so</c>（GROUP BY tenant_id + order_id）→ 订单 Header
/// → LEFT JOIN <c>dwd_order_review_kpi</c> → ORDER_REVIEW 阶段事实
/// → LEFT JOIN <c>dwd_product_design_kpi</c> → PRODUCT_DESIGN 阶段事实
/// → 固定五阶段骨架 → 前端档案页。</para>
///
/// <para><b>PRINCIPLE 1 · 租户隔离</b>：租户只来自 <see cref="AidopTenantScope.ResolveOrThrow"/>
/// 解析的登录上下文；所有查询与 JOIN 均带 <c>tenant_id</c>，不接受前端传入 tenantId，
/// 无默认租户、无 Factory 代偿。</para>
///
/// <para><b>PRINCIPLE 2 · 只走数据中台</b>：本服务只读 <c>mdp_std_so</c> 与
/// <c>dwd_order_review_kpi</c>，不触碰 <c>crm_seorder</c> / <c>crm_seorderentry</c> /
/// <c>CustMaster</c> / <c>aidop_action_run_log</c>。</para>
///
/// <para><b>与 SEED 严格隔离</b>：本服务<b>完全不读</b> <c>ado_s8_order_flow_*</c>。
/// 真实订单的 Stage 2~5 一律返回空字段，绝不从 SEED 拼回来；SEED 基线由
/// <see cref="S8OrderFlowService"/> 原样保留，供 fixture / 回归使用。</para>
/// </summary>
public class S8RealOrderArchiveService : ITransient
{
    /// <summary>本 Read Model 的数据来源标识，用于前端区分 REAL / SEED，禁止两路混用。</summary>
    public const string RealDataSource = "MDP_STD_SO";

    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly S8Stage3ProcurementReadService _stage3;

    public S8RealOrderArchiveService(
        ISqlSugarClient db,
        UserManager userManager,
        S8Stage3ProcurementReadService stage3)
    {
        _db = db;
        _userManager = userManager;
        _stage3 = stage3;
    }

    /// <summary>
    /// 订单 Header 投影。
    ///
    /// <para><b>聚合函数只是 SQL 投影手段，不是业务规则</b> —— <c>mdp_std_so</c> 是订单行粒度，
    /// 而这几列已实证在同一 <c>order_id</c> 的所有行内取值完全一致（实测差异计数为 0），
    /// 因此 <c>MAX()</c> 只是「从若干相同值里取一个」，不隐含「取最大」的业务含义。</para>
    ///
    /// <para><b>行级字段禁止升格为 Header</b>：<c>progress</c> / <c>factory_id</c> /
    /// <c>item_name</c> / <c>item_spec</c> 逐行不同，故不在本投影内。</para>
    /// </summary>
    private const string HeaderProjection = """
        SELECT `tenant_id`,
               `order_id`,
               MAX(`order_no`)       AS `order_no`,
               MAX(`customer_id`)    AS `customer_id`,
               MAX(`customer_no`)    AS `customer_no`,
               MAX(`customer_name`)  AS `customer_name`,
               MAX(`order_date`)     AS `order_date`,
               MAX(`order_type`)     AS `order_type`,
               MAX(`order_status`)   AS `order_status`,
               MAX(`flow_state`)     AS `flow_state`
          FROM `mdp_std_so`
         WHERE `tenant_id` = @TenantId
           AND `order_id` IS NOT NULL
           AND `order_id` <> 0
           AND `deleted_flag` = 0
         GROUP BY `tenant_id`, `order_id`
        """;

    /// <summary>
    /// 真实订单列表。一张销售订单一行。
    /// <para>ORDER_REVIEW 事实用 <b>LEFT JOIN</b> 挂上 —— 有订单但尚未评审的订单
    /// 必须照常出现在档案里，不能被 INNER JOIN 过滤掉。</para>
    /// </summary>
    public async Task<List<AdoS8OrderFlowOrderListItemDto>> GetRealOrdersAsync(AdoS8OrderFlowOrderQueryDto query)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);

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

        // 订单号 / 客户 模糊检索（order_no 仅作展示与搜索，不参与身份判定）
        if (!string.IsNullOrWhiteSpace(query.Keyword))
        {
            where += " AND (h.`order_no` LIKE @Kw OR h.`customer_name` LIKE @Kw OR h.`customer_no` LIKE @Kw)";
            ps.Add(new SugarParameter("@Kw", $"%{query.Keyword.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(query.CustomerCode))
        {
            where += " AND h.`customer_no` = @CustNo";
            ps.Add(new SugarParameter("@CustNo", query.CustomerCode.Trim()));
        }

        var rows = await _db.Ado.SqlQueryAsync<RealOrderRow>($"""
            SELECT h.`order_id`        AS SalesOrderId,
                   h.`order_no`        AS OrderNo,
                   h.`customer_id`     AS CustomerId,
                   h.`customer_no`     AS CustomerNo,
                   h.`customer_name`   AS CustomerName,
                   h.`order_date`      AS OrderDate,
                   h.`order_type`      AS OrderType,
                   h.`order_status`    AS OrderStatus,
                   h.`flow_state`      AS FlowState,
                   k.`review_start_time`  AS ReviewStartTime,
                   k.`review_end_time`    AS ReviewEndTime,
                   k.`actual_hours`       AS ActualHours,
                   k.`calculation_status` AS CalculationStatus,
                   p.`product_design_start_time` AS DesignStartTime,
                   p.`product_design_end_time`   AS DesignEndTime,
                   p.`actual_hours`              AS DesignActualHours,
                   p.`calculation_status`        AS DesignCalculationStatus
              FROM ({HeaderProjection}) h
              LEFT JOIN `dwd_order_review_kpi` k
                     ON k.`tenant_id` = h.`tenant_id`
                    AND k.`sales_order_id` = h.`order_id`
              LEFT JOIN `dwd_product_design_kpi` p
                     ON p.`tenant_id` = h.`tenant_id`
                    AND p.`sales_order_id` = h.`order_id`
             WHERE {where}
             ORDER BY h.`order_date` DESC, h.`order_id` DESC
            """, ps);

        return (rows ?? []).Select(MapRealOrder).ToList();
    }

    /// <summary>
    /// 单张真实订单详情（含固定五阶段骨架）。
    /// <para><b>定位 Authority = <c>tenant_id + salesOrderId</c></b>，不接受订单号定位。</para>
    /// </summary>
    public async Task<AdoS8OrderFlowOrderDetailDto?> GetRealOrderAsync(long salesOrderId)
    {
        if (salesOrderId <= 0) return null;
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);

        var rows = await _db.Ado.SqlQueryAsync<RealOrderRow>($"""
            SELECT h.`order_id`        AS SalesOrderId,
                   h.`order_no`        AS OrderNo,
                   h.`customer_id`     AS CustomerId,
                   h.`customer_no`     AS CustomerNo,
                   h.`customer_name`   AS CustomerName,
                   h.`order_date`      AS OrderDate,
                   h.`order_type`      AS OrderType,
                   h.`order_status`    AS OrderStatus,
                   h.`flow_state`      AS FlowState,
                   k.`review_start_time`  AS ReviewStartTime,
                   k.`review_end_time`    AS ReviewEndTime,
                   k.`actual_hours`       AS ActualHours,
                   k.`calculation_status` AS CalculationStatus,
                   p.`product_design_start_time` AS DesignStartTime,
                   p.`product_design_end_time`   AS DesignEndTime,
                   p.`actual_hours`              AS DesignActualHours,
                   p.`calculation_status`        AS DesignCalculationStatus
              FROM ({HeaderProjection}) h
              LEFT JOIN `dwd_order_review_kpi` k
                     ON k.`tenant_id` = h.`tenant_id`
                    AND k.`sales_order_id` = h.`order_id`
              LEFT JOIN `dwd_product_design_kpi` p
                     ON p.`tenant_id` = h.`tenant_id`
                    AND p.`sales_order_id` = h.`order_id`
             WHERE h.`order_id` = @SalesOrderId
             LIMIT 1
            """, new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@SalesOrderId", salesOrderId)
            });

        var row = rows?.FirstOrDefault();
        if (row == null) return null;

        var item = MapRealOrder(row);
        var detail = new AdoS8OrderFlowOrderDetailDto
        {
            SalesOrderId = item.SalesOrderId,
            OrderNo = item.OrderNo,
            CustomerId = item.CustomerId,
            OrderDate = item.OrderDate,
            OrderType = item.OrderType,
            OrderStatus = item.OrderStatus,
            FlowState = item.FlowState,
            OrderCode = item.OrderCode,
            CustomerCode = item.CustomerCode,
            CustomerName = item.CustomerName,
            WorkflowStatus = item.WorkflowStatus,
            CurrentOrderFlowCode = item.CurrentOrderFlowCode,
            CurrentOrderFlowName = item.CurrentOrderFlowName,
            CurrentStatus = item.CurrentStatus,
            DataSource = item.DataSource,
            Lifecycle = BuildFiveStageSkeleton(row, await LoadStage3SummaryAsync(salesOrderId)),
        };
        return detail;
    }

    /// <summary>
    /// 取材料采购阶段的摘要，供五阶段矩阵使用。
    ///
    /// <para><b>复用同一个 Authority</b>：直接调 <see cref="S8Stage3ProcurementReadService"/>，
    /// 不另写第二份业务 SQL，也不从 SEED / 贴源层 / 源库重算。矩阵与详情面板因此不可能给出
    /// 互相矛盾的结论 —— 它们本来就是同一次计算的两种投影。</para>
    ///
    /// <para><b>只在单订单详情里调用</b>。订单列表（<c>GetRealOrdersAsync</c>）刻意不调，
    /// 否则 N 张订单会变成 N 次采购归因查询。</para>
    ///
    /// <para>取数失败时返回 <c>null</c>，由调用方标成 <c>UNKNOWN</c> ——
    /// 技术故障绝不能被渲染成「未到达」这种业务结论。</para>
    /// </summary>
    private async Task<AdoS8Stage3ProcurementSummaryDto?> LoadStage3SummaryAsync(long salesOrderId)
    {
        try
        {
            var stage3 = await _stage3.GetStage3Async(salesOrderId);
            return stage3?.Summary;
        }
        catch (Exception)
        {
            return null;
        }
    }


    /// <summary>
    /// 材料采购阶段的数据态。
    ///
    /// <para><b>永远不会返回「已完成」</b>：采购摘要的口径是 ATTRIBUTED_ONLY —— 只统计能可靠
    /// 归属到该订单的采购行，不是该订单的完整采购需求集合。即便已归属的行全部完成，
    /// 也不能推出「材料采购阶段已完成」，那要等完整需求集合的 Authority 建立之后才谈得上。</para>
    ///
    /// <para>摘要为 null 表示取数失败（不是没有数据），故落 <c>UNKNOWN</c> 而非 <c>NO_DATA</c>。</para>
    /// </summary>
    public static string ResolveStage3DataState(AdoS8Stage3ProcurementSummaryDto? stage3)
    {
        if (stage3 == null) return AdoS8StageDataState.Unknown;
        return stage3.AttributedPoLineCount > 0
            ? AdoS8StageDataState.InProgress
            : AdoS8StageDataState.NoData;
    }

    // ══════════════════════════════════════════════════════════════════════
    // 映射
    // ══════════════════════════════════════════════════════════════════════

    /// <summary>
    /// 行 → 列表项。
    /// <para><b>没有真实 Authority 的字段一律留空/留 null</b>：ProductName / ProductLine /
    /// Region / Priority / CustomerType / TargetCycleDays / ActualCycleDays / 各类偏差。
    /// 绝不用 SEED、PPT 常量、随机默认值或按客户名推断来填 —— 真实订单配假 KPI 比没有更危险。</para>
    /// </summary>
    private static AdoS8OrderFlowOrderListItemDto MapRealOrder(RealOrderRow r) => new()
    {
        SalesOrderId = r.SalesOrderId,
        OrderNo = r.OrderNo,
        CustomerId = r.CustomerId,
        OrderDate = r.OrderDate,
        OrderType = r.OrderType,
        OrderStatus = r.OrderStatus,
        FlowState = r.FlowState,

        // OrderCode 在既有契约里承担「展示码」角色，REAL 下等于真实订单号；身份看 SalesOrderId。
        OrderCode = r.OrderNo ?? string.Empty,
        CustomerCode = r.CustomerNo ?? string.Empty,
        CustomerName = r.CustomerName ?? string.Empty,

        // 本批只有 ORDER_REVIEW 有事实，当前阶段恒定指向它；不推断"订单走到哪一步"。
        CurrentOrderFlowCode = OrderFlowConstants.ORDER_REVIEW_PLAN_CALC,
        CurrentOrderFlowName = OrderFlowConstants.ResolveName(OrderFlowConstants.ORDER_REVIEW_PLAN_CALC),
        CurrentStatus = string.Empty,

        // 订单状态用真实 Header Authority 映射，不猜。
        WorkflowStatus = string.Equals(r.OrderStatus, "CLOSED", StringComparison.OrdinalIgnoreCase)
            ? "completed"
            : "in_progress",

        DataSource = RealDataSource,

        // ↓ 以下无真实 Authority，显式留空 —— 前端渲染 "--"
        ProductName = string.Empty,
        ProductLine = string.Empty,
        CustomerType = string.Empty,
        Region = string.Empty,
        Priority = string.Empty,
        ScenarioCode = string.Empty,
        ExceptionStatus = string.Empty,
        ReleaseAt = null,
        TargetCycleDays = null,
        ActualCycleDays = null,
        CurrentCycleDays = null,
        NodeVarianceDays = null,
        CumulativeVarianceDays = null,
        ResponseMinutes = null,
        ProcessingMinutes = null,
        TotalLossMinutes = null,
    };

    /// <summary>
    /// 固定五阶段骨架。
    /// <para><b>五阶段是 S8 的业务设计，不由「有没有数据」决定</b> —— 因此 Stage 2~5
    /// 必须存在但字段为空；本批只有 ORDER_REVIEW 有真实事实。</para>
    /// </summary>
    public static List<AdoS8OrderFlowStageDto> BuildFiveStageSkeleton(
        RealOrderRow r,
        AdoS8Stage3ProcurementSummaryDto? stage3)
    {
        var list = new List<AdoS8OrderFlowStageDto>();
        for (var i = 0; i < OrderFlowConstants.All.Count; i++)
        {
            var code = OrderFlowConstants.All[i];
            var isReview = code == OrderFlowConstants.ORDER_REVIEW_PLAN_CALC;
            var isDesign = code == OrderFlowConstants.PRODUCT_DESIGN;
            var isPurchase = code == OrderFlowConstants.MATERIAL_PURCHASE;

            list.Add(new AdoS8OrderFlowStageDto
            {
                OrderFlowCode = code,
                OrderFlowName = OrderFlowConstants.ResolveName(code),
                SortNo = i + 1,
                OwnerDept = null,

                // 目前只有 ORDER_REVIEW 与 PRODUCT_DESIGN 有真实事实；
                // Stage 3~5（材料采购 / 本体生产 / 总装发货）一律 null —— 即便这些域
                // 已经有真实业务数据（工单 / MRP / 采购 / 排产），本批也不顺手点亮。
                ActualStartAt = isReview ? r.ReviewStartTime : isDesign ? r.DesignStartTime : null,
                ActualEndAt = isReview ? r.ReviewEndTime : isDesign ? r.DesignEndTime : null,
                ActualHours = isReview ? r.ActualHours : isDesign ? r.DesignActualHours : null,
                CalculationStatus = isReview ? r.CalculationStatus : isDesign ? r.DesignCalculationStatus : null,

                // 目标/计划/偏差全部属 TargetHours 域，本批无 Authority → null
                PlannedDays = null,
                ActualDays = null,
                TargetAt = null,
                NodeVarianceDays = null,
                CumulativeVarianceDays = null,

                // 状态色本批不评级（红黄绿属另一批次）；统一 pending，由前端按时间细分「进行中 / 未到达」
                Status = "pending",
                Substeps = [],

                // 材料采购没有时间 Authority，但有真实的「已归属 / 已完成」计数。
                // 用数据态如实表达，绝不为了让矩阵好看而伪造 ActualStartAt / ActualEndAt。
                DataState = isPurchase ? ResolveStage3DataState(stage3) : null,
                DataScope = isPurchase && stage3 != null ? AdoS8Stage3Const.ScopeAttributedOnly : null,
                KnownItemCount = isPurchase ? stage3?.AttributedPoLineCount : null,
                CompletedItemCount = isPurchase ? stage3?.ProcurementCompletedCount : null,
            });
        }
        return list;
    }

    /// <summary>SQL 投影行，字段名与 SELECT 别名一一对应。</summary>
    public sealed class RealOrderRow
    {
        public long SalesOrderId { get; set; }
        public string? OrderNo { get; set; }
        public long? CustomerId { get; set; }
        public string? CustomerNo { get; set; }
        public string? CustomerName { get; set; }
        public DateTime? OrderDate { get; set; }
        public int? OrderType { get; set; }
        public string? OrderStatus { get; set; }
        public string? FlowState { get; set; }
        public DateTime? ReviewStartTime { get; set; }
        public DateTime? ReviewEndTime { get; set; }
        public decimal? ActualHours { get; set; }
        public string? CalculationStatus { get; set; }
        public DateTime? DesignStartTime { get; set; }
        public DateTime? DesignEndTime { get; set; }
        public decimal? DesignActualHours { get; set; }
        public string? DesignCalculationStatus { get; set; }
    }
}