AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Manufacturing/S6ProcessInspectionService.cs

using Admin.NET.Plugin.AiDOP.Manufacturing.Dto;

namespace Admin.NET.Plugin.AiDOP.Manufacturing;

/// <summary>
/// S6 过程检验单 服务（Phase 1B）：生产指令 → 自动唯一解析过程检规 → 生成检验单 + 检规快照 → 只读详情。
///
/// prepare：入参仅 workOrderNo。后端自解析 CurrentTenant（AidopTenantScope）+ 加载生产指令（mdp_std_work_order_schedule）
///   → S6ProcessSpecResolver 按 (tenant, item_code, order_date) 唯一解析。
///   仅 MATCHED 才幂等生成/复用检验单 + 冻结检规快照（同一事务）；NO_MATCH/AMBIGUOUS/INVALID 一律不写库、原样返回状态。
/// 无人工检规选择；全程 tenant 显式过滤；快照生成后详情只读快照，不再回查 S0。
/// FACTORY_SCOPE = PARTIAL（S0 检规无 factory_id，安全边界为 Tenant；factory 仅随单据承载不做过滤）。
/// </summary>
[ApiDescriptionSettings(Order = 304, Description = "过程检验单")]
[Route("api/S6ProcessInspection")]
[NonUnify]
public class S6ProcessInspectionService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly S6ProcessSpecResolver _resolver;

    public S6ProcessInspectionService(ISqlSugarClient db, UserManager userManager, S6ProcessSpecResolver resolver)
    {
        _db = db;
        _userManager = userManager;
        _resolver = resolver;
    }

    private long ResolveTenantOrThrow() => Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);

    /// <summary>
    /// 准备过程检验单：解析检规，MATCHED 则幂等生成/复用检验单 + 快照，返回 billId + status。
    /// </summary>
    [DisplayName("准备过程检验单")]
    [HttpPost("prepare")]
    public async Task<S6ProcessInspectionPrepareResult> Prepare([FromBody] S6ProcessInspectionPrepareInput input)
    {
        var res = new S6ProcessInspectionPrepareResult();
        if (input == null || string.IsNullOrWhiteSpace(input.WorkOrderNo))
        {
            res.Status = S6SpecResolveStatus.NoMatch;
            res.Message = "缺少工单编号。";
            return res;
        }

        var tid = ResolveTenantOrThrow();
        var wo = input.WorkOrderNo.Trim();

        // 加载生产指令（严格租户；跨租户/不存在 → 明确拒绝，不泄漏）
        var pi = await _db.Ado.SqlQuerySingleAsync<ProdInstrRow>(
            """
            SELECT work_order AS WorkOrder, lot_serial AS LotSerial, item_code AS ItemCode, item_name AS ItemName,
                   specification AS Specification, factory_id AS FactoryId, order_date AS OrderDate
            FROM mdp_std_work_order_schedule
            WHERE tenant_id=@TenantId AND work_order=@Wo LIMIT 1
            """,
            new List<SugarParameter> { new("@TenantId", tid), new("@Wo", wo) });
        if (pi == null)
        {
            res.Status = S6SpecResolveStatus.NoMatch;
            res.Message = "未找到该工单的生产指令。";
            return res;
        }

        // ReferenceDate 取检验时点(now)：过程检验在当前执行，应采用"检验时点当前有效"的检规；
        // 生产指令 OrderDate 是下单时点，可能早于检规生效日(sxrj)，用它会漏掉当前有效检规。（§9 明确说明）
        var refDate = DateTime.Now;
        var resolve = await _resolver.ResolveAsync(tid, pi.ItemCode, refDate);
        res.Status = resolve.Status;
        res.Message = resolve.Message;
        res.SpecCode = resolve.SpecCode;
        res.SpecVersion = resolve.SpecVersion;
        res.CandidateCount = resolve.CandidateCount;

        if (resolve.Status != S6SpecResolveStatus.Matched || resolve.CandidateCount != 1 || resolve.MatchedSpecId is not { } specId)
            return res; // 非唯一解析：不写库

        // 幂等：已有 active(DRAFT/ACTIVE) 检验单 → 复用
        var existingId = await _db.Ado.SqlQuerySingleAsync<long?>(
            """
            SELECT id FROM ado_s6_process_inspection_bill
            WHERE tenant_id=@TenantId AND work_order_no=@Wo AND inspection_status IN ('DRAFT','ACTIVE')
            ORDER BY id DESC LIMIT 1
            """,
            new List<SugarParameter> { new("@TenantId", tid), new("@Wo", wo) });
        if (existingId is { } eid && eid > 0)
        {
            res.BillId = eid;
            res.Reused = true;
            return res;
        }

        // 生成：检验单 + 快照头 + 快照明细（同一事务，任一失败全回滚）
        var uid = _userManager.UserId;
        var uname = _userManager.RealName ?? _userManager.Account;
        try
        {
            _db.Ado.BeginTran();

            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_s6_process_inspection_bill
                (tenant_id, factory_id, work_order_no, lot_serial, item_code, item_name, specification,
                 spec_id, spec_code, spec_version, inspection_status, created_by, created_by_name)
                VALUES (@TenantId, @FactoryId, @Wo, @Lot, @ItemCode, @ItemName, @Spec,
                        @SpecId, @SpecCode, @SpecVersion, 'DRAFT', @Uid, @Uname)
                """,
                new List<SugarParameter>
                {
                    new("@TenantId", tid),
                    new("@FactoryId", (object?)pi.FactoryId ?? DBNull.Value),
                    new("@Wo", wo),
                    new("@Lot", (object?)pi.LotSerial ?? DBNull.Value),
                    new("@ItemCode", (object?)pi.ItemCode ?? DBNull.Value),
                    new("@ItemName", (object?)pi.ItemName ?? DBNull.Value),
                    new("@Spec", (object?)pi.Specification ?? DBNull.Value),
                    new("@SpecId", specId),
                    new("@SpecCode", (object?)resolve.SpecCode ?? DBNull.Value),
                    new("@SpecVersion", (object?)resolve.SpecVersion ?? DBNull.Value),
                    new("@Uid", (object?)uid ?? DBNull.Value),
                    new("@Uname", (object?)uname ?? DBNull.Value),
                });

            var billId = await _db.Ado.GetLongAsync("SELECT LAST_INSERT_ID()");

            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_s6_process_inspection_snapshot_head
                (tenant_id, bill_id, spec_id, spec_code, spec_version, effective_date, item_code, specification, source_tenant_id)
                VALUES (@TenantId, @BillId, @SpecId, @SpecCode, @SpecVersion, @Eff, @ItemCode, @Spec, @TenantId)
                """,
                new List<SugarParameter>
                {
                    new("@TenantId", tid),
                    new("@BillId", billId),
                    new("@SpecId", specId),
                    new("@SpecCode", (object?)resolve.SpecCode ?? DBNull.Value),
                    new("@SpecVersion", (object?)resolve.SpecVersion ?? DBNull.Value),
                    new("@Eff", (object?)resolve.EffectiveDate ?? DBNull.Value),
                    new("@ItemCode", (object?)pi.ItemCode ?? DBNull.Value),
                    new("@Spec", (object?)pi.Specification ?? DBNull.Value),
                });

            var lineCount = await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_s6_process_inspection_snapshot_line
                (tenant_id, bill_id, source_line_id, inspection_item, process_code, process_name, method,
                 spec_value, tech_standard, lower_limit, upper_limit, inspection_frequency, result_type, sort_no, sample_count)
                SELECT @TenantId, @BillId, z.id, z.jyxm, z.gxdh, z.gxmc, z.jyff,
                       z.jygg, z.jsbz, z.xx, z.sx, z.jypc, z.lrlx, NULL, z.sample_count
                FROM qms_gcjygfzb z
                WHERE z.tenant_id=@TenantId AND z.glid=@SpecId
                """,
                new List<SugarParameter> { new("@TenantId", tid), new("@BillId", billId), new("@SpecId", specId) });

            if (lineCount <= 0)
                throw Oops.Oh("过程检验规范无有效明细，无法生成检验单。");

            _db.Ado.CommitTran();
            res.BillId = billId;
            res.Reused = false;
            return res;
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
    }

    /// <summary>过程检验单只读详情（单据 + 快照头 + 快照明细）。跨租户/不存在返回 null。</summary>
    [DisplayName("过程检验单详情")]
    [HttpGet("detail")]
    public async Task<S6ProcessInspectionDetailDto?> GetDetail([FromQuery] long billId)
    {
        if (billId <= 0) return null;
        var tid = ResolveTenantOrThrow();
        var pars = new List<SugarParameter> { new("@TenantId", tid), new("@BillId", billId) };

        var dto = await _db.Ado.SqlQuerySingleAsync<S6ProcessInspectionDetailDto>(
            """
            SELECT b.id AS BillId, b.work_order_no AS WorkOrderNo, b.lot_serial AS LotSerial, b.item_code AS ItemCode,
                   b.item_name AS ItemName, b.specification AS Specification, b.spec_id AS SpecId, b.spec_code AS SpecCode,
                   b.spec_version AS SpecVersion, h.effective_date AS EffectiveDate, b.inspection_status AS InspectionStatus,
                   b.created_by_name AS CreatedByName, b.create_time AS CreateTime
            FROM ado_s6_process_inspection_bill b
            LEFT JOIN ado_s6_process_inspection_snapshot_head h ON h.bill_id=b.id AND h.tenant_id=@TenantId
            WHERE b.tenant_id=@TenantId AND b.id=@BillId LIMIT 1
            """, pars);
        if (dto == null) return null;

        dto.Lines = await _db.Ado.SqlQueryAsync<S6ProcessInspectionSnapshotLineDto>(
            """
            SELECT id AS Id, inspection_item AS InspectionItem, process_code AS ProcessCode, process_name AS ProcessName,
                   method AS Method, spec_value AS SpecValue, tech_standard AS TechStandard, lower_limit AS LowerLimit,
                   upper_limit AS UpperLimit, inspection_frequency AS InspectionFrequency, result_type AS ResultType,
                   sort_no AS SortNo, sample_count AS SampleCount
            FROM ado_s6_process_inspection_snapshot_line
            WHERE tenant_id=@TenantId AND bill_id=@BillId ORDER BY id
            """, pars);

        // 已录样本 + 后端计算的单项/整单判定（刷新可恢复）
        var samples = await LoadResultItemsAsync(tid, billId);
        var byLine = samples.GroupBy(s => s.SnapshotLineId).ToDictionary(g => g.Key, g => g.OrderBy(x => x.SampleIndex).ToList());
        var itemAgg = new List<(string, string)>();
        foreach (var line in dto.Lines)
        {
            var recorded = byLine.TryGetValue(line.Id, out var list) ? list : new List<ResultItemRow>();
            line.Samples = recorded.Select(r => new S6ProcessInspectionResultSampleDto
            {
                SampleIndex = r.SampleIndex,
                ActualNumeric = r.ActualNumeric,
                ActualNonNumeric = r.ActualNonNumeric,
                Judgement = r.Judgement,
                Remark = r.Remark
            }).ToList();
            var (status, result) = S6ProcessInspectionJudge.AggregateItem(line.SampleCount ?? 1, recorded.Select(r => r.Judgement ?? "").ToList());
            line.ItemStatus = status;
            line.ItemResult = result;
            itemAgg.Add((status, result));
        }
        var head = await _db.Ado.SqlQuerySingleAsync<ResultHeadRow>(
            "SELECT status AS Status, overall_result AS OverallResult FROM ado_s6_process_inspection_result WHERE tenant_id=@TenantId AND bill_id=@BillId LIMIT 1", pars);
        dto.OverallStatus = head?.Status ?? S6ItemStatus.NotStarted;
        dto.OverallResult = head?.OverallResult ?? S6ProcessInspectionJudge.AggregateOverall(itemAgg);
        return dto;
    }

    /// <summary>
    /// 保存检验结果（草稿，可部分）。逐样本 upsert（幂等 UK bill+line+sample），后端按快照自动判定，
    /// 重算单项/整单结果，结果头置 IN_PROGRESS；同一事务。前端传的任何判定/租户/限值一律忽略。
    /// </summary>
    [DisplayName("保存过程检验结果")]
    [HttpPost("saveResult")]
    public async Task<S6SaveResultOutput> SaveResult([FromBody] S6SaveResultInput input)
    {
        if (input == null || input.BillId <= 0)
            return new S6SaveResultOutput { Ok = false, Message = "缺少检验单。" };
        var tid = ResolveTenantOrThrow();
        var cfg = await LoadSnapshotConfigAsync(tid, input.BillId);
        if (cfg.Count == 0)
            return new S6SaveResultOutput { Ok = false, Message = "检验单不存在或无检规快照。" };
        var cfgById = cfg.ToDictionary(c => c.Id);

        var uid = _userManager.UserId;
        var uname = _userManager.RealName ?? _userManager.Account;
        var now = DateTime.Now;
        try
        {
            _db.Ado.BeginTran();
            var resultId = await EnsureResultHeaderAsync(tid, input.BillId, uid, uname, now);

            foreach (var s in input.Samples ?? new List<S6SaveResultSample>())
            {
                if (!cfgById.TryGetValue(s.SnapshotLineId, out var line)) continue;   // 只认本单快照行
                var required = line.SampleCount is > 0 ? line.SampleCount!.Value : 1;
                if (s.SampleIndex < 1 || s.SampleIndex > required) continue;           // 样本序号越界忽略
                var clear = string.IsNullOrWhiteSpace(s.ActualValue);
                var delPars = new List<SugarParameter> { new("@t", tid), new("@bill", input.BillId), new("@line", s.SnapshotLineId), new("@idx", s.SampleIndex) };
                if (clear)
                {
                    await _db.Ado.ExecuteCommandAsync(
                        "DELETE FROM ado_s6_process_inspection_result_item WHERE tenant_id=@t AND bill_id=@bill AND snapshot_line_id=@line AND sample_index=@idx", delPars);
                    continue;
                }
                var judge = S6ProcessInspectionJudge.JudgeSample(line.ResultType, s.ActualValue, line.LowerLimit, line.UpperLimit);
                var isNumeric = string.Equals((line.ResultType ?? "").Trim(), S6ResultType.Numeric, StringComparison.OrdinalIgnoreCase);
                object numVal = isNumeric && S6ProcessInspectionJudge.ParseNumeric(s.ActualValue) is { } n ? n : DBNull.Value;
                object nonNumVal = !isNumeric ? (object)(s.ActualValue?.Trim().ToUpperInvariant() ?? string.Empty) : DBNull.Value;
                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO ado_s6_process_inspection_result_item
                    (tenant_id, bill_id, result_id, snapshot_line_id, result_type, sample_index, actual_numeric, actual_non_numeric, judgement, remark, inspector_id, inspection_time)
                    VALUES (@t,@bill,@rid,@line,@rt,@idx,@num,@nonnum,@judge,@remark,@uid,@now)
                    ON DUPLICATE KEY UPDATE actual_numeric=VALUES(actual_numeric), actual_non_numeric=VALUES(actual_non_numeric),
                        judgement=VALUES(judgement), remark=VALUES(remark), inspector_id=VALUES(inspector_id), inspection_time=VALUES(inspection_time)
                    """,
                    new List<SugarParameter>
                    {
                        new("@t", tid), new("@bill", input.BillId), new("@rid", resultId), new("@line", s.SnapshotLineId),
                        new("@rt", (object?)line.ResultType ?? DBNull.Value), new("@idx", s.SampleIndex),
                        new("@num", numVal), new("@nonnum", nonNumVal), new("@judge", judge),
                        new("@remark", (object?)s.Remark ?? DBNull.Value), new("@uid", (object?)uid ?? DBNull.Value), new("@now", now)
                    });
            }

            var (overallStatus, overallResult) = await RecomputeAndPersistHeaderAsync(tid, input.BillId, complete: false, now);
            _db.Ado.CommitTran();
            return new S6SaveResultOutput { Ok = true, OverallStatus = overallStatus, OverallResult = overallResult };
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
    }

    /// <summary>
    /// 完成检验。校验所有项目均已录满（COMPLETED），否则 INSPECTION_INCOMPLETE；通过则置 COMPLETED + 整单判定 + completed_at。同一事务。
    /// </summary>
    [DisplayName("完成过程检验")]
    [HttpPost("complete")]
    public async Task<S6SaveResultOutput> Complete([FromBody] S6CompleteInput input)
    {
        if (input == null || input.BillId <= 0)
            return new S6SaveResultOutput { Ok = false, Message = "缺少检验单。" };
        var tid = ResolveTenantOrThrow();
        var cfg = await LoadSnapshotConfigAsync(tid, input.BillId);
        if (cfg.Count == 0)
            return new S6SaveResultOutput { Ok = false, Message = "检验单不存在或无检规快照。" };

        var itemAgg = await AggregateItemsAsync(tid, input.BillId, cfg);
        if (!S6ProcessInspectionJudge.AllItemsCompleted(itemAgg))
            throw Oops.Oh("INSPECTION_INCOMPLETE：仍有检验项目未录满样本，无法完成检验。");

        var now = DateTime.Now;
        try
        {
            _db.Ado.BeginTran();
            var (overallStatus, overallResult) = await RecomputeAndPersistHeaderAsync(tid, input.BillId, complete: true, now);
            _db.Ado.CommitTran();
            return new S6SaveResultOutput { Ok = true, OverallStatus = overallStatus, OverallResult = overallResult };
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
    }

    // ── 内部：快照配置 / 结果读取 / 聚合 ──
    private async Task<List<SnapCfgRow>> LoadSnapshotConfigAsync(long tid, long billId) =>
        await _db.Ado.SqlQueryAsync<SnapCfgRow>(
            """
            SELECT id AS Id, result_type AS ResultType, lower_limit AS LowerLimit, upper_limit AS UpperLimit, sample_count AS SampleCount
            FROM ado_s6_process_inspection_snapshot_line WHERE tenant_id=@t AND bill_id=@bill ORDER BY id
            """, new List<SugarParameter> { new("@t", tid), new("@bill", billId) });

    private async Task<List<ResultItemRow>> LoadResultItemsAsync(long tid, long billId) =>
        await _db.Ado.SqlQueryAsync<ResultItemRow>(
            """
            SELECT snapshot_line_id AS SnapshotLineId, sample_index AS SampleIndex, actual_numeric AS ActualNumeric,
                   actual_non_numeric AS ActualNonNumeric, judgement AS Judgement, remark AS Remark
            FROM ado_s6_process_inspection_result_item WHERE tenant_id=@t AND bill_id=@bill ORDER BY snapshot_line_id, sample_index
            """, new List<SugarParameter> { new("@t", tid), new("@bill", billId) });

    private async Task<List<(string Status, string Result)>> AggregateItemsAsync(long tid, long billId, List<SnapCfgRow> cfg)
    {
        var items = await LoadResultItemsAsync(tid, billId);
        var byLine = items.GroupBy(i => i.SnapshotLineId).ToDictionary(g => g.Key, g => g.Select(x => x.Judgement ?? "").ToList());
        return cfg.Select(c => S6ProcessInspectionJudge.AggregateItem(
            c.SampleCount is > 0 ? c.SampleCount!.Value : 1,
            byLine.TryGetValue(c.Id, out var js) ? js : new List<string>())).ToList();
    }

    private async Task<long> EnsureResultHeaderAsync(long tid, long billId, long? uid, string? uname, DateTime now)
    {
        var pars = new List<SugarParameter> { new("@t", tid), new("@bill", billId), new("@uid", (object?)uid ?? DBNull.Value), new("@uname", (object?)uname ?? DBNull.Value), new("@now", now) };
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO ado_s6_process_inspection_result (tenant_id, bill_id, work_order_no, item_code, status, inspector_id, inspector_name, started_at)
            SELECT @t, @bill, b.work_order_no, b.item_code, 'IN_PROGRESS', @uid, @uname, @now
            FROM ado_s6_process_inspection_bill b WHERE b.tenant_id=@t AND b.id=@bill
              AND NOT EXISTS (SELECT 1 FROM ado_s6_process_inspection_result r WHERE r.tenant_id=@t AND r.bill_id=@bill)
            """, pars);
        return await _db.Ado.SqlQuerySingleAsync<long>(
            "SELECT id FROM ado_s6_process_inspection_result WHERE tenant_id=@t AND bill_id=@bill LIMIT 1",
            new List<SugarParameter> { new("@t", tid), new("@bill", billId) });
    }

    private async Task<(string Status, string Result)> RecomputeAndPersistHeaderAsync(long tid, long billId, bool complete, DateTime now)
    {
        var cfg = await LoadSnapshotConfigAsync(tid, billId);
        var itemAgg = await AggregateItemsAsync(tid, billId, cfg);
        var overallResult = S6ProcessInspectionJudge.AggregateOverall(itemAgg);
        var anyRecorded = itemAgg.Any(i => i.Status != S6ItemStatus.NotStarted);
        var status = complete ? S6ItemStatus.Completed : (anyRecorded ? S6ItemStatus.InProgress : S6ItemStatus.NotStarted);
        var pars = new List<SugarParameter>
        {
            new("@t", tid), new("@bill", billId), new("@status", status), new("@overall", overallResult),
            new("@completedAt", complete ? now : (object)DBNull.Value)
        };
        // completed_at 与 status 严格一致：仅 COMPLETED 有值；一旦回退（再次保存改动）即清空，避免 IN_PROGRESS 却残留完成时间。
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE ado_s6_process_inspection_result
            SET status=@status, overall_result=@overall, completed_at=CASE WHEN @status='COMPLETED' THEN @completedAt ELSE NULL END, update_time=NOW()
            WHERE tenant_id=@t AND bill_id=@bill
            """, pars);
        return (status, overallResult);
    }

    private sealed class ProdInstrRow
    {
        public string? WorkOrder { get; set; }
        public string? LotSerial { get; set; }
        public string? ItemCode { get; set; }
        public string? ItemName { get; set; }
        public string? Specification { get; set; }
        public long? FactoryId { get; set; }
        public DateTime? OrderDate { get; set; }
    }

    private sealed class SnapCfgRow
    {
        public long Id { get; set; }
        public string? ResultType { get; set; }
        public string? LowerLimit { get; set; }
        public string? UpperLimit { get; set; }
        public int? SampleCount { get; set; }
    }

    private sealed class ResultItemRow
    {
        public long SnapshotLineId { get; set; }
        public int SampleIndex { get; set; }
        public decimal? ActualNumeric { get; set; }
        public string? ActualNonNumeric { get; set; }
        public string? Judgement { get; set; }
        public string? Remark { get; set; }
    }

    private sealed class ResultHeadRow
    {
        public string? Status { get; set; }
        public string? OverallResult { get; set; }
    }
}