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

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>
/// S5 IQC 项目实例 + 逐样本 的只读读取器（Batch 3）。
///
/// 单独成类的原因：Detail（GET）与 save-detail（POST 回执）需要**完全相同**的行形状与汇总口径，
/// 放一处可避免两边 SQL 漂移。本类只读，不含任何写入。
///
/// 【租户边界】所有查询都显式带 tenant_id 谓词，且首列即 tenant_id（命中 uk/idx 最左前缀）。
/// 绝不依赖 billId / itemId 的全局唯一性作为隔离手段。
/// </summary>
public class IqcInspBillItemReader : ITransient
{
    private readonly ISqlSugarClient _db;

    public IqcInspBillItemReader(ISqlSugarClient db) => _db = db;

    /// <summary>
    /// 该检验单的项目实例条数。0 ⇒ LEGACY MODE（历史单 / NO_SPEC 新单 / MATCHED+0 明细新单，同一语义）。
    /// 判据只看数据，不看日期 / 单号 / 版本。
    /// </summary>
    public async Task<int> CountItemsAsync(long billId, long tenantId)
        => await _db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM ado_s5_iqc_insp_bill_item WHERE tenant_id=@TenantId AND inspection_bill_id=@BillId",
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@BillId", billId));

    /// <summary>
    /// 载入该检验单的全部项目实例（含其样本，按 seq/id 与 sample_index 升序）。
    /// itemCount=0 时返回空列表（不是 null）。
    /// </summary>
    public async Task<List<IqcInspBillItemLineRow>> LoadLinesAsync(long billId, long tenantId)
    {
        var pars = new List<SugarParameter>
        {
            new("@TenantId", tenantId),
            new("@BillId", billId),
        };

        var lines = await _db.Ado.SqlQueryAsync<IqcInspBillItemLineRow>(
            """
            SELECT
                id                  AS Id,
                seq                 AS Seq,
                source_spec_id      AS SourceSpecId,
                source_spec_item_id AS SourceSpecItemId,
                inspection_item     AS InspectionItem,
                inspection_standard AS InspectionStandard,
                method_tool         AS MethodTool,
                image_category      AS ImageCategory,
                sampling_scheme     AS SamplingScheme,
                spec_remark         AS SpecRemark,
                upper_limit         AS UpperLimit,
                lower_limit         AS LowerLimit,
                result_type         AS ResultType,
                item_judgement      AS ItemJudgement,
                judgement_source    AS JudgementSource,
                qualified_qty       AS QualifiedQty,
                unqualified_qty     AS UnqualifiedQty,
                result_remark       AS ResultRemark,
                last_result_by      AS LastResultBy,
                last_result_at      AS LastResultAt
            FROM ado_s5_iqc_insp_bill_item
            WHERE tenant_id = @TenantId AND inspection_bill_id = @BillId
            ORDER BY seq, id
            """,
            pars);

        if (lines.Count == 0) return lines;

        // 样本按单一次取（反规范化的 inspection_bill_id 使其免 JOIN），再按 item 分组
        var samples = await _db.Ado.SqlQueryAsync<SampleRow>(
            """
            SELECT
                id               AS Id,
                item_id          AS ItemId,
                sample_index     AS SampleIndex,
                sample_value_raw AS SampleValueRaw,
                sample_numeric   AS SampleNumeric,
                judgement        AS Judgement,
                remark           AS Remark,
                inspector_id     AS InspectorId,
                inspection_time  AS InspectionTime
            FROM ado_s5_iqc_insp_bill_item_sample
            WHERE tenant_id = @TenantId AND inspection_bill_id = @BillId
            ORDER BY item_id, sample_index
            """,
            pars);

        var byItem = samples.GroupBy(s => s.ItemId).ToDictionary(g => g.Key, g => g.ToList());
        foreach (var line in lines)
        {
            if (!byItem.TryGetValue(line.Id, out var list)) continue;
            line.Samples = list.Select(s => new IqcInspBillItemSampleRow
            {
                Id = s.Id,
                SampleIndex = s.SampleIndex,
                SampleValueRaw = s.SampleValueRaw,
                SampleNumeric = s.SampleNumeric,
                Judgement = s.Judgement,
                Remark = s.Remark,
                InspectorId = s.InspectorId,
                InspectionTime = s.InspectionTime,
            }).ToList();
        }
        return lines;
    }

    /// <summary>实时聚合汇总（不落表，避免与 item 行形成第二个判定真源）。</summary>
    public static IqcInspBillLineSummary Summarize(IReadOnlyList<IqcInspBillItemLineRow> lines)
    {
        var s = new IqcInspBillLineSummary { ItemCount = lines.Count };
        foreach (var l in lines)
        {
            switch (l.ItemJudgement)
            {
                case IqcJudgement.Pass: s.PassCount++; s.JudgedCount++; break;
                case IqcJudgement.Fail: s.FailCount++; s.JudgedCount++; break;
                case IqcJudgement.Invalid: s.InvalidCount++; break;
                default: s.UnjudgedCount++; break;
            }
        }
        return s;
    }

    /// <summary>样本原始行（含 ItemId 用于分组，不外泄到 API 契约）。</summary>
    private sealed class SampleRow
    {
        public long Id { get; set; }
        public long ItemId { get; set; }
        public int SampleIndex { get; set; }
        public string? SampleValueRaw { get; set; }
        public decimal? SampleNumeric { get; set; }
        public string? Judgement { get; set; }
        public string? Remark { get; set; }
        public long? InspectorId { get; set; }
        public DateTime? InspectionTime { get; set; }
    }
}