AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/SmartOps/KpiTargetExcelService.cs

using System.Security.Cryptography;
using System.Text.Json;
using Admin.NET.Plugin.AiDOP.Dto.SmartOps;
using Admin.NET.Plugin.AiDOP.Entity;
using Microsoft.AspNetCore.Http;
using MiniExcelLibs;
using MiniExcelLibs.Attributes;

namespace Admin.NET.Plugin.AiDOP.SmartOps;

public sealed class KpiTargetExcelRow
{
    [ExcelColumnName("指标编码 ★")] public string? MetricCode { get; set; }
    [ExcelColumnName("工厂编码")] public string? FactoryCode { get; set; }
    [ExcelColumnName("目标值 ★")] public string? TargetValue { get; set; }
    [ExcelColumnName("生效日期 ★")] public string? EffectiveFrom { get; set; }
    [ExcelColumnName("失效日期")] public string? EffectiveTo { get; set; }
    [ExcelColumnName("备注")] public string? Remark { get; set; }
}

public sealed class KpiTargetExcelService : ITransient
{
    private readonly IKpiTargetConfigStore _store;
    private readonly KpiTargetService _targets;
    private readonly UserManager _userManager;

    public KpiTargetExcelService(IKpiTargetConfigStore store, KpiTargetService targets, UserManager userManager)
    {
        _store = store;
        _targets = targets;
        _userManager = userManager;
    }

    public async Task<IActionResult> TemplateAsync()
    {
        var memory = new MemoryStream();
        await memory.SaveAsAsync(Array.Empty<KpiTargetExcelRow>(), sheetName: "目标");
        memory.Seek(0, SeekOrigin.Begin);
        return new FileStreamResult(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        {
            FileDownloadName = "kpi-target-template.xlsx"
        };
    }

    public async Task<KpiTargetImportPreviewDto> ValidateAsync(IFormFile file, long factoryIdFallback, CancellationToken ct = default)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        if (file == null || file.Length == 0)
            throw new KpiTargetValidationException("请上传 Excel 文件");
        if (file.Length > KpiTargetService.MaxImportBytes)
            throw new KpiTargetValidationException("文件超过 5MB");
        var ext = Path.GetExtension(file.FileName);
        if (!string.Equals(ext, ".xlsx", StringComparison.OrdinalIgnoreCase))
            throw new KpiTargetValidationException("仅支持 .xlsx");

        await using var stream = file.OpenReadStream();
        var sha = await Sha256Async(stream, ct);
        stream.Position = 0;
        if (await _store.CountCommittedHashAsync(tenantId, sha, ct) > 0)
            throw new KpiTargetConflictException("相同文件已成功导入，拒绝重复提交");

        var raw = (await MiniExcel.QueryAsync<KpiTargetExcelRow>(stream, sheetName: "目标")).ToList();
        if (raw.Count > KpiTargetService.MaxImportRows)
            throw new KpiTargetValidationException($"超过最大行数 {KpiTargetService.MaxImportRows}");

        var errors = new List<KpiTargetImportErrorDto>();
        var valid = new List<KpiTargetImportRow>();
        for (var i = 0; i < raw.Count; i++)
        {
            var rowNo = i + 2;
            var line = raw[i];
            try
            {
                valid.Add(await ParseRowAsync(tenantId, line, rowNo, factoryIdFallback, ct));
            }
            catch (Exception ex) when (ex is KpiTargetValidationException or KpiTargetNotFoundException or KpiTargetConflictException)
            {
                errors.Add(new KpiTargetImportErrorDto
                {
                    RowNo = rowNo,
                    MetricCode = line.MetricCode,
                    Code = ex.GetType().Name,
                    Message = ex.Message
                });
            }
        }

        DetectFileOverlaps(valid, errors);
        valid = valid.Where(v => errors.All(e => e.RowNo != v.RowNo)).ToList();

        foreach (var row in valid.ToList())
        {
            var existing = await _store.ListActiveScopeAsync(tenantId, row.FactoryId, row.MetricCode, ct);
            foreach (var cfg in existing)
            {
                if (KpiTargetRules.Overlaps(cfg.EffectiveFrom, cfg.EffectiveTo, row.EffectiveFrom, row.EffectiveTo))
                {
                    errors.Add(new KpiTargetImportErrorDto
                    {
                        RowNo = row.RowNo,
                        MetricCode = row.MetricCode,
                        Code = "OVERLAP_DB",
                        Message = $"与库中配置 {cfg.Id} 重叠"
                    });
                    valid.Remove(row);
                    break;
                }
            }
        }

        var now = DateTime.Now;
        var batch = await _store.InsertBatchAsync(new AdoSmartOpsKpiTargetImportBatch
        {
            BatchNo = $"KPI_TGT_{now:yyyyMMddHHmmss}_{Random.Shared.Next(1000, 9999)}",
            TenantId = tenantId,
            FactoryId = factoryIdFallback,
            FileName = file.FileName,
            FileSha256 = sha,
            Status = errors.Count == 0 && valid.Count > 0 ? "READY" : "VALIDATION_FAILED",
            TotalRows = raw.Count,
            ValidRows = valid.Count,
            ErrorRows = errors.Count,
            PayloadJson = KpiTargetService.SerializePayload(valid),
            ErrorJson = JsonSerializer.Serialize(errors),
            CreatedBy = _userManager.RealName ?? _userManager.Account,
            CreateTime = now
        }, ct);

        return new KpiTargetImportPreviewDto
        {
            BatchId = batch.Id,
            BatchNo = batch.BatchNo,
            Status = batch.Status,
            TotalRows = batch.TotalRows,
            ValidRows = batch.ValidRows,
            ErrorRows = batch.ErrorRows,
            Errors = errors
        };
    }

    public async Task<KpiTargetImportPreviewDto> CommitAsync(long batchId, CancellationToken ct = default)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var batch = await _store.GetBatchAsync(tenantId, batchId, ct)
            ?? throw new KpiTargetNotFoundException("导入批次不存在");
        if (batch.Status == "COMMITTED")
            throw new KpiTargetConflictException("该批次已提交，不能重复执行");
        if (batch.Status != "READY")
            throw new KpiTargetValidationException("只有预检通过的批次可以确认导入");

        var rows = KpiTargetService.DeserializePayload(batch.PayloadJson);
        foreach (var row in rows)
        {
            var existing = await _store.ListActiveScopeAsync(tenantId, row.FactoryId, row.MetricCode, ct);
            foreach (var cfg in existing)
            {
                if (KpiTargetRules.Overlaps(cfg.EffectiveFrom, cfg.EffectiveTo, row.EffectiveFrom, row.EffectiveTo))
                    throw new KpiTargetConflictException($"并发冲突：{row.MetricCode} 与配置 {cfg.Id} 重叠");
            }
        }

        try
        {
            foreach (var row in rows)
            {
                await _targets.CreateCoreAsync(tenantId, new KpiTargetCreateDto
                {
                    MetricCode = row.MetricCode,
                    FactoryId = row.FactoryId,
                    TargetValue = row.TargetValue,
                    EffectiveFrom = row.EffectiveFrom,
                    EffectiveTo = row.EffectiveTo,
                    ClosePrevious = false,
                    Remark = row.Remark
                }, KpiTargetConfigSource.TargetExcel, batch.BatchNo, _userManager.UserId, ct);
            }
            batch.Status = "COMMITTED";
            batch.CommitTime = DateTime.Now;
            await _store.UpdateBatchAsync(batch, ct);
        }
        catch
        {
            batch.Status = "FAILED";
            await _store.UpdateBatchAsync(batch, ct);
            throw;
        }

        return new KpiTargetImportPreviewDto
        {
            BatchId = batch.Id,
            BatchNo = batch.BatchNo,
            Status = batch.Status,
            TotalRows = batch.TotalRows,
            ValidRows = batch.ValidRows,
            ErrorRows = batch.ErrorRows
        };
    }

    public async Task<IActionResult> ErrorsAsync(long batchId, CancellationToken ct = default)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var batch = await _store.GetBatchAsync(tenantId, batchId, ct)
            ?? throw new KpiTargetNotFoundException("导入批次不存在");
        var errors = string.IsNullOrWhiteSpace(batch.ErrorJson)
            ? new List<KpiTargetImportErrorDto>()
            : JsonSerializer.Deserialize<List<KpiTargetImportErrorDto>>(batch.ErrorJson) ?? new();
        var memory = new MemoryStream();
        await memory.SaveAsAsync(errors, sheetName: "错误");
        memory.Seek(0, SeekOrigin.Begin);
        return new FileStreamResult(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        {
            FileDownloadName = $"{batch.BatchNo}-errors.xlsx"
        };
    }

    private async Task<KpiTargetImportRow> ParseRowAsync(long tenantId, KpiTargetExcelRow line, int rowNo, long factoryFallback, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(line.MetricCode))
            throw new KpiTargetValidationException("指标编码必填");
        if (!decimal.TryParse(line.TargetValue, out var target) || target <= 0)
            throw new KpiTargetValidationException("目标值必须大于 0");
        if (!DateTime.TryParse(line.EffectiveFrom, out var from))
            throw new KpiTargetValidationException("生效日期无效");
        DateTime? to = null;
        if (!string.IsNullOrWhiteSpace(line.EffectiveTo))
        {
            if (!DateTime.TryParse(line.EffectiveTo, out var toVal))
                throw new KpiTargetValidationException("失效日期无效");
            to = toVal.Date;
        }
        KpiTargetRules.EnsureRange(from, to);
        var kpi = await _store.FindKpiAsync(tenantId, line.MetricCode.Trim(), ct)
            ?? throw new KpiTargetNotFoundException("指标不存在或不属于当前租户");
        _ = kpi;
        var factoryId = factoryFallback;
        if (!string.IsNullOrWhiteSpace(line.FactoryCode))
        {
            if (!long.TryParse(line.FactoryCode.Trim(), out factoryId) || factoryId < 0)
                throw new KpiTargetValidationException("工厂编码无效");
        }
        return new KpiTargetImportRow
        {
            RowNo = rowNo,
            MetricCode = line.MetricCode.Trim(),
            FactoryId = factoryId,
            TargetValue = target,
            EffectiveFrom = from.Date,
            EffectiveTo = to,
            Remark = line.Remark
        };
    }

    private static void DetectFileOverlaps(List<KpiTargetImportRow> valid, List<KpiTargetImportErrorDto> errors)
    {
        for (var i = 0; i < valid.Count; i++)
        {
            for (var j = i + 1; j < valid.Count; j++)
            {
                var a = valid[i];
                var b = valid[j];
                if (a.MetricCode != b.MetricCode || a.FactoryId != b.FactoryId)
                    continue;
                if (!KpiTargetRules.Overlaps(a.EffectiveFrom, a.EffectiveTo, b.EffectiveFrom, b.EffectiveTo))
                    continue;
                errors.Add(new KpiTargetImportErrorDto
                {
                    RowNo = b.RowNo,
                    MetricCode = b.MetricCode,
                    Code = "OVERLAP_FILE",
                    Message = $"与本文件第 {a.RowNo} 行重叠"
                });
            }
        }
    }

    private static async Task<string> Sha256Async(Stream stream, CancellationToken ct)
    {
        using var sha = SHA256.Create();
        var hash = await sha.ComputeHashAsync(stream, ct);
        return Convert.ToHexString(hash).ToLowerInvariant();
    }
}