AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Controllers/S0/Quality/AdoS0QmsSpecImportController.cs

using System.ComponentModel.DataAnnotations;
using Admin.NET.Core;
using Microsoft.AspNetCore.Http;
using Admin.NET.Plugin.AiDOP.Dto.S0.Quality;
using Admin.NET.Plugin.AiDOP.Entity.S0.Quality;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using static Admin.NET.Plugin.AiDOP.Controllers.S0.Quality.AdoS0QmsControllerHelpers;

namespace Admin.NET.Plugin.AiDOP.Controllers.S0.Quality;

/// <summary>
/// S0 质量建模 - 4 个检验规范列表（原材料 / 过程 / FQC / OQC）Excel 模板导入。
/// 每个列表提供“下载导入模板”(GET import-template) 与“上传导入”(POST import) 两个端点。
/// 模板为一张平铺表：主表列 + 明细列，按“文件编号”分组还原为一条主单 + 多条明细。
/// 写入语义复刻各列表现有 Create（纯新增，不去重、不 upsert）。
///
/// 【全或无】UAT-S0-10 拍板（2026-09-02）：先做整份 preflight 校验，任一行或任一组不合格即整份拒绝、
/// 0 落库；全部通过后才进入写事务。必填契约与普通 Create 入口共用 AdoS0QmsSpecAggregateGuard。
/// </summary>
[ApiController]
[Route("api/s0/quality")]
[NonUnify]
public class AdoS0QmsSpecImportController : ControllerBase
{
    private readonly SqlSugarRepository<AdoS0QmsRawInspectionSpec> _rawRep;
    private readonly SqlSugarRepository<AdoS0QmsRawInspectionSpecEntry> _rawEntryRep;
    private readonly SqlSugarRepository<AdoS0QmsProcessInspectionSpec> _processRep;
    private readonly SqlSugarRepository<AdoS0QmsProcessInspectionSpecEntry> _processEntryRep;
    private readonly SqlSugarRepository<AdoS0QmsFqcInspectionSpec> _fqcRep;
    private readonly SqlSugarRepository<AdoS0QmsFqcInspectionSpecEntry> _fqcEntryRep;
    private readonly SqlSugarRepository<AdoS0QmsOqcInspectionSpec> _oqcRep;
    private readonly SqlSugarRepository<AdoS0QmsOqcInspectionSpecEntry> _oqcEntryRep;

    public AdoS0QmsSpecImportController(
        SqlSugarRepository<AdoS0QmsRawInspectionSpec> rawRep,
        SqlSugarRepository<AdoS0QmsRawInspectionSpecEntry> rawEntryRep,
        SqlSugarRepository<AdoS0QmsProcessInspectionSpec> processRep,
        SqlSugarRepository<AdoS0QmsProcessInspectionSpecEntry> processEntryRep,
        SqlSugarRepository<AdoS0QmsFqcInspectionSpec> fqcRep,
        SqlSugarRepository<AdoS0QmsFqcInspectionSpecEntry> fqcEntryRep,
        SqlSugarRepository<AdoS0QmsOqcInspectionSpec> oqcRep,
        SqlSugarRepository<AdoS0QmsOqcInspectionSpecEntry> oqcEntryRep)
    {
        _rawRep = rawRep;
        _rawEntryRep = rawEntryRep;
        _processRep = processRep;
        _processEntryRep = processEntryRep;
        _fqcRep = fqcRep;
        _fqcEntryRep = fqcEntryRep;
        _oqcRep = oqcRep;
        _oqcEntryRep = oqcEntryRep;
    }

    // ==================== 模板下载 ====================

    [HttpGet("raw-inspection-specs/import-template")]
    public Task<IActionResult> RawTemplate()
        => MiniExcelUtil.ExportExcelTemplate<AdoS0QmsRawInspectionSpecImportRow>("原材料检验规范导入模板");

    [HttpGet("process-inspection-specs/import-template")]
    public Task<IActionResult> ProcessTemplate()
        => MiniExcelUtil.ExportExcelTemplate<AdoS0QmsProcessLikeInspectionSpecImportRow>("过程检验规范导入模板");

    [HttpGet("fqc-inspection-specs/import-template")]
    public Task<IActionResult> FqcTemplate()
        => MiniExcelUtil.ExportExcelTemplate<AdoS0QmsProcessLikeInspectionSpecImportRow>("FQC检验规范导入模板");

    [HttpGet("oqc-inspection-specs/import-template")]
    public Task<IActionResult> OqcTemplate()
        => MiniExcelUtil.ExportExcelTemplate<AdoS0QmsProcessLikeInspectionSpecImportRow>("OQC检验规范导入模板");

    // ==================== 导入 ====================

    [HttpPost("raw-inspection-specs/import")]
    public async Task<IActionResult> RawImport(IFormFile file)
    {
        if (!AdoS0TenantScope.TryResolveRequired(out var tenantId, out var tenantError)) return tenantError!;
        if (file == null || file.Length == 0) return BadRequest(new { message = "请上传 Excel 文件" });
        var result = await RunImportAsync<AdoS0QmsRawInspectionSpecImportRow, AdoS0QmsRawInspectionSpec, AdoS0QmsRawInspectionSpecEntry>(
            file, r => r.FileNumber, AdoS0QmsSpecAggregateGuard.HasDetailValue, MergeRawHeader, RawRequiredHeaderFields, _rawRep, _rawEntryRep,
            (r, fn) => new AdoS0QmsRawInspectionSpec
            {
                FileNumber = fn,
                VersionNo = NullIfWhiteSpace(r.VersionNo),
                DrawingNo = NullIfWhiteSpace(r.DrawingNo),
                RawMaterialName = NullIfWhiteSpace(r.RawMaterialName),
                MaterialCode = NullIfWhiteSpace(r.MaterialCode),
                EffectiveDate = NullIfWhiteSpace(r.EffectiveDate),
                DrawingVersion = NullIfWhiteSpace(r.DrawingVersion),
                MaterialGrade = NullIfWhiteSpace(r.MaterialGrade),
                CavityOrMold = NullIfWhiteSpace(r.CavityOrMold),
                Attachment = NullIfWhiteSpace(r.Attachment),
                FileName = NullIfWhiteSpace(r.FileName),
                Title = NullIfWhiteSpace(r.Title),
            },
            m => m.Id,
            (r, mid) => new AdoS0QmsRawInspectionSpecEntry
            {
                MasterId = mid,
                Seq = r.Seq,
                InspectionItem = NullIfWhiteSpace(r.InspectionItem),
                InspectionStandard = NullIfWhiteSpace(r.InspectionStandard),
                InspectionMethod = NullIfWhiteSpace(r.InspectionMethod),
                SamplingScheme = NullIfWhiteSpace(r.SamplingScheme),
                UpperLimit = NullIfWhiteSpace(r.UpperLimit),
                LowerLimit = NullIfWhiteSpace(r.LowerLimit),
            },
            tenantId);
        return Ok(result);
    }

    [HttpPost("process-inspection-specs/import")]
    public async Task<IActionResult> ProcessImport(IFormFile file)
    {
        if (!AdoS0TenantScope.TryResolveRequired(out var tenantId, out var tenantError)) return tenantError!;
        if (file == null || file.Length == 0) return BadRequest(new { message = "请上传 Excel 文件" });
        var result = await RunImportAsync<AdoS0QmsProcessLikeInspectionSpecImportRow, AdoS0QmsProcessInspectionSpec, AdoS0QmsProcessInspectionSpecEntry>(
            file, r => r.FileNumber, AdoS0QmsSpecAggregateGuard.HasDetailValue, MergeProcessLikeHeader, ProcessLikeRequiredHeaderFields, _processRep, _processEntryRep,
            (r, fn) => new AdoS0QmsProcessInspectionSpec
            {
                FileNumber = fn,
                ApplicableModel = NullIfWhiteSpace(r.ApplicableModel),
                VersionNo = NullIfWhiteSpace(r.VersionNo),
                EffectiveDate = NullIfWhiteSpace(r.EffectiveDate),
                MaterialCode = NullIfWhiteSpace(r.MaterialCode),
                Attachment = NullIfWhiteSpace(r.Attachment),
                Attachment2 = NullIfWhiteSpace(r.Attachment2),
                Version = r.Version,
            },
            m => m.Id,
            (r, mid) => BuildProcessLikeEntry<AdoS0QmsProcessInspectionSpecEntry>(r, mid),
            tenantId);
        return Ok(result);
    }

    [HttpPost("fqc-inspection-specs/import")]
    public async Task<IActionResult> FqcImport(IFormFile file)
    {
        if (!AdoS0TenantScope.TryResolveRequired(out var tenantId, out var tenantError)) return tenantError!;
        if (file == null || file.Length == 0) return BadRequest(new { message = "请上传 Excel 文件" });
        var result = await RunImportAsync<AdoS0QmsProcessLikeInspectionSpecImportRow, AdoS0QmsFqcInspectionSpec, AdoS0QmsFqcInspectionSpecEntry>(
            file, r => r.FileNumber, AdoS0QmsSpecAggregateGuard.HasDetailValue, MergeProcessLikeHeader, ProcessLikeRequiredHeaderFields, _fqcRep, _fqcEntryRep,
            (r, fn) => new AdoS0QmsFqcInspectionSpec
            {
                FileNumber = fn,
                ApplicableModel = NullIfWhiteSpace(r.ApplicableModel),
                VersionNo = NullIfWhiteSpace(r.VersionNo),
                EffectiveDate = NullIfWhiteSpace(r.EffectiveDate),
                MaterialCode = NullIfWhiteSpace(r.MaterialCode),
                Attachment = NullIfWhiteSpace(r.Attachment),
                Attachment2 = NullIfWhiteSpace(r.Attachment2),
                Version = r.Version,
            },
            m => m.Id,
            (r, mid) => BuildProcessLikeEntry<AdoS0QmsFqcInspectionSpecEntry>(r, mid),
            tenantId);
        return Ok(result);
    }

    [HttpPost("oqc-inspection-specs/import")]
    public async Task<IActionResult> OqcImport(IFormFile file)
    {
        if (!AdoS0TenantScope.TryResolveRequired(out var tenantId, out var tenantError)) return tenantError!;
        if (file == null || file.Length == 0) return BadRequest(new { message = "请上传 Excel 文件" });
        var result = await RunImportAsync<AdoS0QmsProcessLikeInspectionSpecImportRow, AdoS0QmsOqcInspectionSpec, AdoS0QmsOqcInspectionSpecEntry>(
            file, r => r.FileNumber, AdoS0QmsSpecAggregateGuard.HasDetailValue, MergeProcessLikeHeader, ProcessLikeRequiredHeaderFields, _oqcRep, _oqcEntryRep,
            (r, fn) => new AdoS0QmsOqcInspectionSpec
            {
                FileNumber = fn,
                ApplicableModel = NullIfWhiteSpace(r.ApplicableModel),
                VersionNo = NullIfWhiteSpace(r.VersionNo),
                EffectiveDate = NullIfWhiteSpace(r.EffectiveDate),
                MaterialCode = NullIfWhiteSpace(r.MaterialCode),
                Attachment = NullIfWhiteSpace(r.Attachment),
                Attachment2 = NullIfWhiteSpace(r.Attachment2),
                Version = r.Version,
            },
            m => m.Id,
            (r, mid) => BuildProcessLikeEntry<AdoS0QmsOqcInspectionSpecEntry>(r, mid),
            tenantId);
        return Ok(result);
    }

    // ==================== 共用逻辑 ====================

    /// <summary>过程/FQC/OQC 三表明细结构一致，共用一个明细构造器（各表 Entry 字段名相同）。</summary>
    private static TEntry BuildProcessLikeEntry<TEntry>(AdoS0QmsProcessLikeInspectionSpecImportRow r, long masterId)
        where TEntry : class, new()
    {
        var entry = new TEntry();
        switch (entry)
        {
            case AdoS0QmsProcessInspectionSpecEntry p:
                p.MasterId = masterId;
                p.OperationCode = NullIfWhiteSpace(r.OperationCode);
                p.OperationName = NullIfWhiteSpace(r.OperationName);
                p.InspectionItem = NullIfWhiteSpace(r.InspectionItem);
                p.InspectionMethod = NullIfWhiteSpace(r.InspectionMethod);
                p.InspectionSpec = NullIfWhiteSpace(r.InspectionSpec);
                p.InspectionFrequency = NullIfWhiteSpace(r.InspectionFrequency);
                p.UpperLimit = NullIfWhiteSpace(r.UpperLimit);
                p.LowerLimit = NullIfWhiteSpace(r.LowerLimit);
                break;
            case AdoS0QmsFqcInspectionSpecEntry f:
                f.MasterId = masterId;
                f.OperationCode = NullIfWhiteSpace(r.OperationCode);
                f.OperationName = NullIfWhiteSpace(r.OperationName);
                f.InspectionItem = NullIfWhiteSpace(r.InspectionItem);
                f.InspectionMethod = NullIfWhiteSpace(r.InspectionMethod);
                f.InspectionSpec = NullIfWhiteSpace(r.InspectionSpec);
                f.InspectionFrequency = NullIfWhiteSpace(r.InspectionFrequency);
                f.UpperLimit = NullIfWhiteSpace(r.UpperLimit);
                f.LowerLimit = NullIfWhiteSpace(r.LowerLimit);
                break;
            case AdoS0QmsOqcInspectionSpecEntry o:
                o.MasterId = masterId;
                o.OperationCode = NullIfWhiteSpace(r.OperationCode);
                o.OperationName = NullIfWhiteSpace(r.OperationName);
                o.InspectionItem = NullIfWhiteSpace(r.InspectionItem);
                o.InspectionMethod = NullIfWhiteSpace(r.InspectionMethod);
                o.InspectionSpec = NullIfWhiteSpace(r.InspectionSpec);
                o.InspectionFrequency = NullIfWhiteSpace(r.InspectionFrequency);
                o.UpperLimit = NullIfWhiteSpace(r.UpperLimit);
                o.LowerLimit = NullIfWhiteSpace(r.LowerLimit);
                break;
        }
        return entry;
    }

    private async Task<AdoS0QmsSpecImportResultDto> RunImportAsync<TRow, TMaster, TEntry>(
        IFormFile file,
        Func<TRow, string?> keySelector,
        Func<TRow, bool> detailHasValue,
        Func<List<TRow>, TRow> headerMerger,
        Func<TRow, IReadOnlyList<(string Label, string? Value)>> requiredHeaderFields,
        SqlSugarRepository<TMaster> masterRep,
        SqlSugarRepository<TEntry> entryRep,
        Func<TRow, string, TMaster> masterFactory,
        Func<TMaster, long> idOf,
        Func<TRow, long, TEntry> entryFactory,
        long tenantId)
        where TRow : class, new()
        where TMaster : class, new()
        where TEntry : class, new()
    {
        var rows = (await MiniExcelUtil.GetImportExcelData<TRow>(file)).ToList();
        var result = new AdoS0QmsSpecImportResultDto();

        // ---------- Preflight（全或无）：本段完全不碰数据库 ----------
        var groups = BuildGroups(rows, keySelector, result);

        // 归组之后再合并头字段，然后按聚合校验必填——续行允许省略除文件编号外的头字段，
        // 逐行校验会把合法续行误判为空值非法，故必填只在这一层判。
        var merged = new Dictionary<SpecGroup<TRow>, TRow>();
        foreach (var g in groups)
        {
            var header = headerMerger(g.Rows);
            merged[g] = header;
            var itemCount = g.Rows.Count(detailHasValue);
            var error = AdoS0QmsSpecAggregateGuard.ValidateCreate(requiredHeaderFields(header), itemCount);
            if (error != null)
            {
                result.FailedRows++;
                result.RowMessages.Add($"文件编号「{g.FileNumber}」（第 {g.FirstRowNo} 行起）：{error}");
            }
        }

        if (groups.Count == 0 && result.FailedRows == 0)
        {
            // 空文件不报成功，否则用户会看到绿色的“新增 0 条规范”而误以为导入生效。
            result.Accepted = false;
            result.RowMessages.Add("未读取到任何数据行，请确认使用的是本页下载的导入模板且已填写数据。");
            result.Message = "导入已拒绝：文件中没有数据行，未写入任何数据。";
            return result;
        }

        if (result.FailedRows > 0)
        {
            result.Accepted = false;
            result.Message = $"导入已拒绝：共读取 {result.TotalRows} 行，发现 {result.FailedRows} 处不合格，未写入任何数据。请修正后重新导入。";
            return result;
        }

        // ---------- 全部通过后才进入写事务 ----------
        var tid = tenantId;
        var db = masterRep.Context;
        await db.Ado.BeginTranAsync();
        try
        {
            foreach (var g in groups)
            {
                var master = masterFactory(merged[g], g.FileNumber);
                typeof(TMaster).GetProperty(nameof(AdoS0QmsRawInspectionSpec.TenantId))?.SetValue(master, tid);
                await masterRep.AsInsertable(master).ExecuteReturnEntityAsync();
                var masterId = idOf(master);
                result.ImportedMasters++;

                foreach (var row in g.Rows)
                {
                    if (!detailHasValue(row)) continue;
                    var entry = entryFactory(row, masterId);
                    typeof(TEntry).GetProperty(nameof(AdoS0QmsRawInspectionSpecEntry.TenantId))?.SetValue(entry, tid);
                    await entryRep.AsInsertable(entry).ExecuteCommandAsync();
                    result.ImportedItems++;
                }
            }
            await db.Ado.CommitTranAsync();
        }
        catch
        {
            await db.Ado.RollbackTranAsync();
            throw;
        }

        result.Accepted = true;
        result.Message = $"导入完成：新增 {result.ImportedMasters} 条规范、{result.ImportedItems} 条明细，共读取 {result.TotalRows} 行。";
        return result;
    }

    /// <summary>
    /// 按“文件编号”分组：同一文件编号的多行合并为一条主单 + 多条明细。
    /// 文件编号是分组键，必须逐行出现——缺失的行无法归属任何主单，记为不合格（全或无语义下将导致整份拒绝）。
    /// 同时逐行做特性校验（[MaxLength]，按实测 DB 列长），把超长值挡在 DB 之外，避免透到 INSERT 变成裸 500。
    /// </summary>
    private static List<SpecGroup<TRow>> BuildGroups<TRow>(IEnumerable<TRow> rows, Func<TRow, string?> keySelector, AdoS0QmsSpecImportResultDto result)
        where TRow : class
    {
        var groups = new List<SpecGroup<TRow>>();
        var index = new Dictionary<string, SpecGroup<TRow>>();
        var rowNo = 1; // 表头占第 1 行，首个数据行为第 2 行
        foreach (var row in rows)
        {
            rowNo++;
            result.TotalRows++;

            var validationResults = new List<ValidationResult>();
            if (!Validator.TryValidateObject(row, new ValidationContext(row), validationResults, validateAllProperties: true))
            {
                foreach (var v in validationResults)
                {
                    result.FailedRows++;
                    result.RowMessages.Add($"第 {rowNo} 行：{v.ErrorMessage}");
                }
            }

            var key = keySelector(row)?.Trim();
            if (string.IsNullOrWhiteSpace(key))
            {
                result.FailedRows++;
                result.RowMessages.Add($"第 {rowNo} 行：文件编号为空，无法归属任何规范");
                continue;
            }
            if (!index.TryGetValue(key, out var g))
            {
                g = new SpecGroup<TRow> { FileNumber = key, FirstRowNo = rowNo };
                index[key] = g;
                groups.Add(g);
            }
            g.Rows.Add(row);
        }
        return groups;
    }

    // ==================== 头字段合并与必填集 ====================
    // 合并口径：同组内每个头字段取第一个非空值。续行留空不覆盖，也不因首行留空而丢值。

    private static string? FirstNonBlank<TRow>(List<TRow> rows, Func<TRow, string?> selector)
    {
        foreach (var row in rows)
        {
            var v = selector(row);
            if (!string.IsNullOrWhiteSpace(v)) return v;
        }
        return null;
    }

    private static AdoS0QmsRawInspectionSpecImportRow MergeRawHeader(List<AdoS0QmsRawInspectionSpecImportRow> rows) => new()
    {
        FileNumber = FirstNonBlank(rows, r => r.FileNumber),
        VersionNo = FirstNonBlank(rows, r => r.VersionNo),
        DrawingNo = FirstNonBlank(rows, r => r.DrawingNo),
        RawMaterialName = FirstNonBlank(rows, r => r.RawMaterialName),
        MaterialCode = FirstNonBlank(rows, r => r.MaterialCode),
        EffectiveDate = FirstNonBlank(rows, r => r.EffectiveDate),
        DrawingVersion = FirstNonBlank(rows, r => r.DrawingVersion),
        MaterialGrade = FirstNonBlank(rows, r => r.MaterialGrade),
        CavityOrMold = FirstNonBlank(rows, r => r.CavityOrMold),
        Attachment = FirstNonBlank(rows, r => r.Attachment),
        FileName = FirstNonBlank(rows, r => r.FileName),
        Title = FirstNonBlank(rows, r => r.Title),
    };

    private static AdoS0QmsProcessLikeInspectionSpecImportRow MergeProcessLikeHeader(List<AdoS0QmsProcessLikeInspectionSpecImportRow> rows) => new()
    {
        ApplicableModel = FirstNonBlank(rows, r => r.ApplicableModel),
        FileNumber = FirstNonBlank(rows, r => r.FileNumber),
        VersionNo = FirstNonBlank(rows, r => r.VersionNo),
        EffectiveDate = FirstNonBlank(rows, r => r.EffectiveDate),
        MaterialCode = FirstNonBlank(rows, r => r.MaterialCode),
        Attachment = FirstNonBlank(rows, r => r.Attachment),
        Attachment2 = FirstNonBlank(rows, r => r.Attachment2),
        Version = rows.Select(r => r.Version).FirstOrDefault(v => v.HasValue),
    };

    /// <summary>原材料检规必填集（UAT-S0-10 拍板）。</summary>
    private static IReadOnlyList<(string, string?)> RawRequiredHeaderFields(AdoS0QmsRawInspectionSpecImportRow r) =>
    [
        ("文件编号", r.FileNumber),
        ("物料编码", r.MaterialCode),
        ("原材料名称", r.RawMaterialName),
        ("版本", r.VersionNo),
    ];

    /// <summary>过程 / FQC / OQC 检规必填集（本轮不含「适用型号」，缺业务依据，见拍板第 1 条）。</summary>
    private static IReadOnlyList<(string, string?)> ProcessLikeRequiredHeaderFields(AdoS0QmsProcessLikeInspectionSpecImportRow r) =>
    [
        ("文件编号", r.FileNumber),
        ("物料编码", r.MaterialCode),
        ("版本", r.VersionNo),
    ];

    private sealed class SpecGroup<TRow>
    {
        public string FileNumber { get; init; } = string.Empty;

        /// <summary>该分组首次出现的 Excel 行号，仅用于错误定位。</summary>
        public int FirstRowNo { get; init; }

        public List<TRow> Rows { get; } = [];
    }
}