using System.Linq;
using Admin.NET.Plugin.AiDOP.FinishedWarehouse.Dto;
using Admin.NET.Plugin.AiDOP.Infrastructure;

namespace Admin.NET.Plugin.AiDOP.FinishedWarehouse;

/// <summary>
/// S7 FQC 成品质量：检验任务列表 / 检验单(结果)列表 / 检验单详情 只读 list 服务。
///
/// 数据源（2026-07-30 起）：DOP 数据中台标准层（CTO 拍板：可接受批次快照延迟、全迁 std）：
///   - task-list  ：mdp_std_fqc_task（源 qms_fqcbj）
///   - result-list：mdp_std_fqc_result（源 qms_qcpp_inspbill + WorkOrdMaster 富化 SAP 工单号）
///   - detail     ：mdp_std_fqc_result 头 + mdp_std_fqc_result_detail 明细（glid=源头 id；j1..j50 样本 → Samples[]）
///   三 std 由 FqcMdpSyncService 从源同步标准化而来，且已双源（dopdemorq status=0 就位不启用）。
///
/// 只读边界：纯 SELECT + POST mdp/refresh 触发同步（RunFullAsync，只读源/仅写 mdp_*）；
///   绝不 INSERT/UPDATE/DELETE 业务表、不接 ApprovalFlow、不做录入/审核/处置。WP0-D4：按 tenant_id 过滤（std 保留源 tenant）。
///   id 语义：list 返回源主键(source_row_id)，detail 按 source_row_id/glid 关联，前端契约不变。
/// </summary>
[ApiDescriptionSettings(Order = 330, Description = "FQC检验单列表")]
[Route("api/S7FqcInspBill")]
[AllowAnonymous]
[NonUnify]
public class FqcInspBillService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly FqcMdpSyncService _fqcMdpSyncService;

    public FqcInspBillService(ISqlSugarClient db, UserManager userManager, FqcMdpSyncService fqcMdpSyncService)
    {
        _db = db;
        _userManager = userManager;
        _fqcMdpSyncService = fqcMdpSyncService;
    }

    /// <summary>检验任务列表 排序白名单（前端 prop → std 物理列，杜绝 orderBy 注入）。</summary>
    private static readonly Dictionary<string, string> TaskOrderWhitelist = new(StringComparer.OrdinalIgnoreCase)
    {
        ["applyTime"] = "t.apply_time",
        ["productionBatchNo"] = "t.production_batch_no",
        ["productionOrderNo"] = "t.production_order_no",
        ["materialCode"] = "t.material_code",
        ["priority"] = "t.priority",
        ["inspectStartTime"] = "t.inspect_start_time",
        ["inspectFinishTime"] = "t.inspect_finish_time",
        ["inspectProgress"] = "t.inspect_progress",
    };

    /// <summary>检验单列表 排序白名单（前端 prop → std 物理列）。</summary>
    private static readonly Dictionary<string, string> ResultOrderWhitelist = new(StringComparer.OrdinalIgnoreCase)
    {
        ["inspectTime"] = "a.inspect_time",
        ["billNo"] = "a.bill_no",
        ["productionBatchNo"] = "a.production_batch_no",
        ["materialCode"] = "a.material_code",
        ["inspector"] = "a.inspector",
        ["inspectQty"] = "a.inspect_qty",
        ["productionOrderNo"] = "a.production_order_no",
    };

    /// <summary>
    /// FQC 数据中台同步刷新（RunFullAsync：只读源 qms_fqcbj/qms_qcpp_inspbill/qms_qcpp_inspbillst，仅写 mdp_*）。
    /// </summary>
    [DisplayName("FQC数据中台刷新")]
    [HttpPost("mdp/refresh")]
    public async Task<object> RefreshMdp(CancellationToken cancellationToken)
    {
        var r = await _fqcMdpSyncService.RunFullAsync(cancellationToken, "MANUAL");
        return new { ok = true, batchId = r.BatchId, stageRows = r.StageRows, taskRows = r.TaskRows, resultRows = r.ResultRows, detailRows = r.DetailRows };
    }

    /// <summary>
    /// FQC 检验任务列表（只读分页查询，读标准层 mdp_std_fqc_task）。
    /// </summary>
    [DisplayName("FQC检验任务列表")]
    [HttpGet("task-list")]
    public async Task<object> GetTaskList([FromQuery] FqcTaskListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : (input.PageSize > 200 ? 200 : input.PageSize);
        var offset = (page - 1) * pageSize;

        var tenantId = QmsTenantScope.Resolve(input.TenantId, _userManager.TenantId);
        var where = new List<string> { "t.tenant_id = @TenantFilter" };
        var pars = new List<SugarParameter> { new("@TenantFilter", tenantId) };

        void AddLike(string? v, string col, string p)
        {
            if (string.IsNullOrWhiteSpace(v)) return;
            where.Add($"{col} LIKE @{p}");
            pars.Add(new SugarParameter($"@{p}", $"%{v.Trim()}%"));
        }

        AddLike(input.ProductionOrderNo, "t.production_order_no", "ProductionOrderNo");
        AddLike(input.ProductionBatchNo, "t.production_batch_no", "ProductionBatchNo");
        AddLike(input.MaterialCode, "t.material_code", "MaterialCode");
        AddLike(input.Priority, "t.priority", "Priority");
        AddLike(input.Inspector, "t.inspector", "Inspector");

        if (!string.IsNullOrWhiteSpace(input.InspectProgress))
        {
            where.Add("t.inspect_progress = @InspectProgress");
            pars.Add(new SugarParameter("@InspectProgress", input.InspectProgress.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.ApplyTimeStart))
        {
            where.Add("t.apply_time >= @ApplyTimeStart");
            pars.Add(new SugarParameter("@ApplyTimeStart", $"{input.ApplyTimeStart.Trim()} 00:00:00"));
        }
        if (!string.IsNullOrWhiteSpace(input.ApplyTimeEnd))
        {
            where.Add("t.apply_time <= @ApplyTimeEnd");
            pars.Add(new SugarParameter("@ApplyTimeEnd", $"{input.ApplyTimeEnd.Trim()} 23:59:59"));
        }

        var whereSql = string.Join(" AND ", where);
        var orderSql = ResolveOrder(input.OrderBy, input.OrderDir, TaskOrderWhitelist, "t.apply_time DESC, t.id DESC", "t.id");

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM mdp_std_fqc_task t WHERE {whereSql}", pars);

        var list = await _db.Ado.SqlQueryAsync<FqcTaskListRow>(
            $"""
            SELECT
                CAST(t.source_row_id AS SIGNED) AS Id,
                t.bill_no             AS BillNo,
                t.production_order_no AS ProductionOrderNo,
                t.apply_org_id        AS ApplyOrgId,
                t.apply_time          AS ApplyTime,
                t.material_code       AS MaterialCode,
                t.product_name        AS ProductName,
                t.product_model       AS ProductModel,
                t.production_batch_no AS ProductionBatchNo,
                t.order_qty           AS OrderQty,
                t.qty                 AS Qty,
                t.applicant           AS Applicant,
                t.remark              AS Remark,
                t.priority            AS Priority,
                t.inspect_start_time  AS InspectStartTime,
                t.inspect_finish_time AS InspectFinishTime,
                t.inspector           AS Inspector,
                t.inspect_progress    AS InspectProgress
            FROM mdp_std_fqc_task t
            WHERE {whereSql}
            ORDER BY {orderSql}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

        return new { total, page, pageSize, list };
    }

    /// <summary>
    /// FQC 检验单(结果)列表（只读分页查询，读标准层 mdp_std_fqc_result）。
    /// </summary>
    [DisplayName("FQC检验单结果列表")]
    [HttpGet("result-list")]
    public async Task<object> GetResultList([FromQuery] FqcResultListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : (input.PageSize > 200 ? 200 : input.PageSize);
        var offset = (page - 1) * pageSize;

        var tenantId = QmsTenantScope.Resolve(input.TenantId, _userManager.TenantId);
        var where = new List<string> { "a.tenant_id = @TenantFilter" };
        var pars = new List<SugarParameter> { new("@TenantFilter", tenantId) };

        void AddLike(string? v, string col, string p)
        {
            if (string.IsNullOrWhiteSpace(v)) return;
            where.Add($"{col} LIKE @{p}");
            pars.Add(new SugarParameter($"@{p}", $"%{v.Trim()}%"));
        }

        AddLike(input.BillNo, "a.bill_no", "BillNo");
        AddLike(input.ProductionBatchNo, "a.production_batch_no", "ProductionBatchNo");
        AddLike(input.MaterialCode, "a.material_code", "MaterialCode");
        AddLike(input.MaterialName, "a.material_name", "MaterialName");
        AddLike(input.Inspector, "a.inspector", "Inspector");
        AddLike(input.SourceBillNo, "a.source_bill_no", "SourceBillNo");
        AddLike(input.ProductionOrderNo, "a.production_order_no", "ProductionOrderNo");

        if (!string.IsNullOrWhiteSpace(input.Judgment))
        {
            where.Add("a.judgment = @Judgment");
            pars.Add(new SugarParameter("@Judgment", input.Judgment.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.InspectDateStart))
        {
            where.Add("a.inspect_time >= @InspectDateStart");
            pars.Add(new SugarParameter("@InspectDateStart", $"{input.InspectDateStart.Trim()} 00:00:00"));
        }
        if (!string.IsNullOrWhiteSpace(input.InspectDateEnd))
        {
            where.Add("a.inspect_time <= @InspectDateEnd");
            pars.Add(new SugarParameter("@InspectDateEnd", $"{input.InspectDateEnd.Trim()} 23:59:59"));
        }

        var whereSql = string.Join(" AND ", where);
        var orderSql = ResolveOrder(input.OrderBy, input.OrderDir, ResultOrderWhitelist, "a.inspect_time DESC, a.id DESC", "a.id");

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM mdp_std_fqc_result a WHERE {whereSql}", pars);

        var list = await _db.Ado.SqlQueryAsync<FqcResultListRow>(
            $"""
            SELECT
                CAST(a.source_row_id AS SIGNED) AS Id,
                a.bill_no             AS BillNo,
                a.inspect_time        AS InspectTime,
                a.production_batch_no AS ProductionBatchNo,
                a.material_code       AS MaterialCode,
                a.material_name       AS MaterialName,
                a.spec                AS Spec,
                a.inspector           AS Inspector,
                a.inspect_qty         AS InspectQty,
                a.source_bill_no      AS SourceBillNo,
                a.production_order_no AS ProductionOrderNo,
                a.order_qty           AS OrderQty,
                a.sap_work_order_no   AS SapWorkOrderNo,
                a.judgment            AS Judgment,
                a.qualified_qty       AS QualifiedQty,
                a.unqualified_qty     AS UnqualifiedQty
            FROM mdp_std_fqc_result a
            WHERE {whereSql}
            ORDER BY {orderSql}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

        return new { total, page, pageSize, list };
    }

    /// <summary>
    /// FQC 检验单只读详情（读标准层 mdp_std_fqc_result 头 + mdp_std_fqc_result_detail 明细，glid=源头 id）。
    /// 契约对齐 S6 IPQC：id&lt;=0 或未命中返回 null（HTTP 200/204）；扁平头 + Lines[]；样本 j1..j50 收敛 Samples[]。仅 SELECT。
    /// </summary>
    [DisplayName("FQC检验单详情")]
    [HttpGet("detail")]
    public async Task<FqcInspBillDetailDto?> GetDetail([FromQuery] long id, [FromQuery] long? tenantId)
    {
        if (id <= 0) return null;

        var tid = QmsTenantScope.Resolve(tenantId, _userManager.TenantId);
        var head = (await _db.Ado.SqlQueryAsync<FqcInspBillDetailDto>(
            """
            SELECT
                CAST(a.source_row_id AS SIGNED) AS Id,
                a.bill_no             AS BillNo,
                a.inspect_time        AS InspectTime,
                a.source_bill_no      AS SourceBillNo,
                a.inspector           AS Inspector,
                a.judgment            AS Judgment,
                a.material_code       AS MaterialCode,
                a.material_name       AS MaterialName,
                a.spec                AS Spec,
                a.production_batch_no AS ProductionBatchNo,
                a.production_order_no AS ProductionOrderNo,
                a.order_qty           AS OrderQty,
                a.sap_work_order_no   AS SapWorkOrderNo,
                a.inspect_qty         AS InspectQty,
                a.qualified_qty       AS QualifiedQty,
                a.unqualified_qty     AS UnqualifiedQty,
                a.fire_no             AS FireNo,
                a.inspect_spec_no     AS InspectSpecNo,
                a.inspect_spec_version AS InspectSpecVersion
            FROM mdp_std_fqc_result a
            WHERE a.source_row_id = @id AND a.tenant_id = @TenantFilter
            LIMIT 1
            """,
            new List<SugarParameter> { new("@id", id), new("@TenantFilter", tid) })).FirstOrDefault();

        if (head == null || head.Id <= 0) return null;

        var jSelect = string.Join(", ", Enumerable.Range(1, 50).Select(i => $"st.j{i}"));
        var lineRows = await _db.Ado.SqlQueryAsync<FqcInspBillDetailLineRow>(
            $"""
            SELECT
                st.id                 AS Id,
                st.seq_no             AS SeqNo,
                st.inspection_item    AS InspectionItem,
                st.accept_standard    AS AcceptStandard,
                st.tech_requirement   AS TechRequirement,
                st.sampling_plan      AS SamplingPlan,
                st.sample_qty         AS SampleQty,
                st.upper_limit        AS UpperLimit,
                st.lower_limit        AS LowerLimit,
                st.size_specification AS SizeSpecification,
                st.judgment           AS Judgment,
                st.non_numeric_ok     AS NonNumericOk,
                st.non_numeric_ng     AS NonNumericNg,
                st.remark             AS Remark,
                {jSelect}
            FROM mdp_std_fqc_result_detail st
            WHERE st.glid = @id AND st.tenant_id = @TenantFilter
            ORDER BY st.seq_no, st.id
            """,
            new List<SugarParameter> { new("@id", id), new("@TenantFilter", tid) });

        head.Lines = lineRows.Select(r => r.ToLine()).ToList();
        return head;
    }

    /// <summary>
    /// 排序解析：白名单命中则 "col dir, tie"，否则回落默认；方向仅 asc/desc，非法回落 desc。
    /// </summary>
    private static string ResolveOrder(string? orderBy, string? orderDir, Dictionary<string, string> whitelist, string fallback, string tie)
    {
        if (!string.IsNullOrWhiteSpace(orderBy) && whitelist.TryGetValue(orderBy.Trim(), out var col))
        {
            var dir = string.Equals(orderDir?.Trim(), "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
            return $"{col} {dir}, {tie} DESC";
        }
        return fallback;
    }
}