package com.njcn.gather.aireport.testreport.component;

import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import com.njcn.common.pojo.enums.response.CommonResponseEnum;
import com.njcn.common.pojo.exception.BusinessException;
import com.njcn.gather.aireport.clientunit.mapper.ClientUnitMapper;
import com.njcn.gather.aireport.clientunit.pojo.constant.ClientUnitConst;
import com.njcn.gather.aireport.clientunit.pojo.po.ClientUnitPO;
import com.njcn.gather.aireport.common.storage.AiReportMinioStorageService;
import com.njcn.gather.aireport.testdevice.mapper.TestDeviceMapper;
import com.njcn.gather.aireport.testdevice.pojo.constant.TestDeviceConst;
import com.njcn.gather.aireport.testdevice.pojo.po.TestDevicePO;
import com.njcn.gather.aireport.testdevice.pojo.vo.TestDeviceTypeNameVO;
import com.njcn.gather.aireport.testreport.mapper.TestReportGroupReportMapper;
import com.njcn.gather.aireport.testreport.mapper.TestReportMapper;
import com.njcn.gather.aireport.testreport.mapper.TestReportPointMapper;
import com.njcn.gather.aireport.testreport.pojo.constant.TestReportConst;
import com.njcn.gather.aireport.testreport.pojo.po.TestReportGroupReportPO;
import com.njcn.gather.aireport.testreport.pojo.po.TestReportPO;
import com.njcn.gather.aireport.testreport.pojo.po.TestReportPointPO;
import com.njcn.gather.aireport.testreport.pojo.vo.TestReportLedgerImportResultVO;
import com.njcn.gather.aireport.testreport.pojo.vo.TestReportLedgerImportStageVO;
import com.njcn.gather.aireport.testreport.pojo.vo.TestReportLedgerValidateAttachmentVO;
import com.njcn.gather.aireport.testreport.pojo.vo.TestReportLedgerValidatePointVO;
import com.njcn.gather.aireport.testreport.pojo.vo.TestReportLedgerValidateResultVO;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.time.LocalDateTime;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;

@Component
public class TestReportLedgerImportService {

    private static final ObjectMapper OBJECT_MAPPER = new ObjectMapper();
    private static final String BATCH_STATUS_VALIDATED = "validated";

    private final TestReportMapper testReportMapper;
    private final TestReportPointMapper testReportPointMapper;
    private final TestReportGroupReportMapper testReportGroupReportMapper;
    private final TestDeviceMapper testDeviceMapper;
    private final ClientUnitMapper clientUnitMapper;
    private final TestReportLedgerExcelParser testReportLedgerExcelParser;
    private final TestReportStorageService testReportStorageService;

    public TestReportLedgerImportService(TestReportMapper testReportMapper,
                                         TestReportPointMapper testReportPointMapper,
                                         TestReportGroupReportMapper testReportGroupReportMapper,
                                         TestDeviceMapper testDeviceMapper,
                                         ClientUnitMapper clientUnitMapper,
                                         TestReportLedgerExcelParser testReportLedgerExcelParser,
                                         TestReportStorageService testReportStorageService) {
        this.testReportMapper = testReportMapper;
        this.testReportPointMapper = testReportPointMapper;
        this.testReportGroupReportMapper = testReportGroupReportMapper;
        this.testDeviceMapper = testDeviceMapper;
        this.clientUnitMapper = clientUnitMapper;
        this.testReportLedgerExcelParser = testReportLedgerExcelParser;
        this.testReportStorageService = testReportStorageService;
    }

    public TestReportLedgerValidateResultVO validateLedgerSession(String sessionId, MultipartFile[] files) {
        Map<String, MultipartFile> fileMap = normalizeFileMap(files);
        TestReportStorageService.UploadedBatch uploadedBatch = testReportStorageService.saveValidateSessionFiles(sessionId, fileMap);
        List<String> sessionFileNames = testReportStorageService.listValidateSessionFileNames(sessionId);
        Set<String> sessionFileNameSet = new HashSet<String>(sessionFileNames);
        String summaryFileName = resolveSummaryFileName(sessionFileNameSet);
        TestReportLedgerValidateResultVO result = new TestReportLedgerValidateResultVO();
        result.setSessionId(sessionId);
        result.setTempStoragePath(uploadedBatch.getTempStoragePath());
        result.setUploadedFileCount(sessionFileNames.size());
        result.setCanContinueUpload(Boolean.TRUE);
        result.setSummaryFileName(summaryFileName);
        result.setSummaryFilePresent(summaryFileName != null);
        if (!Boolean.TRUE.equals(result.getSummaryFilePresent())) {
            result.setSuccess(Boolean.FALSE);
            result.getFailReasons().add("校验文件阶段：缺少台账信息汇总.xls或台账信息汇总.xlsx");
            persistValidateSessionResult(sessionId, result);
            return result;
        }
        try (AiReportMinioStorageService.StoredObject storedObject =
                     testReportStorageService.getValidateSessionObject(sessionId, summaryFileName);
             InputStream inputStream = storedObject.getInputStream()) {
            TestReportLedgerExcelParser.ParsedLedger ledger = testReportLedgerExcelParser.parseForValidate(
                    summaryFileName, inputStream);
            Map<String, TestDevicePO> dbDeviceMap = loadExistingDevices(collectDeviceNos(ledger));
            Map<String, ClientUnitPO> dbClientMap = loadExistingClients(collectClientNames(ledger));
            result.getRequiredSheets().addAll(ledger.getRequiredSheets());
            result.getExistingSheets().addAll(ledger.getExistingSheets());
            result.getMissingSheets().addAll(ledger.getMissingSheets());
            fillPointValidationResult(result, ledger, sessionFileNameSet, dbDeviceMap, dbClientMap);
            result.getOrphanAttachmentNames().addAll(resolveOrphanAttachmentNames(ledger, sessionFileNameSet));
            result.setSuccess(result.getMissingSheets().isEmpty() && result.getMissingAttachmentCount() != null
                    && result.getMissingAttachmentCount() == 0 && !hasReferenceErrors(result.getPoints()));
            if (!result.getMissingSheets().isEmpty()) {
                result.getFailReasons().add("校验文件阶段：缺少必需Sheet，" + String.join("、", result.getMissingSheets()));
            }
            if (result.getMissingAttachmentCount() != null && result.getMissingAttachmentCount() > 0) {
                result.getFailReasons().add("校验文件阶段：存在缺失附件，可继续上传后再次校验");
            }
            appendReferenceFailReasons(result);
            persistValidateSessionResult(sessionId, result);
            return result;
        } catch (BusinessException exception) {
            throw exception;
        } catch (Exception exception) {
            throw new BusinessException(CommonResponseEnum.FAIL, "校验文件阶段：读取台账汇总excel失败");
        }
    }

    public TestReportLedgerImportResultVO validateLedger(TestReportPO report, MultipartFile[] files, String userId) {
        Map<String, MultipartFile> fileMap = normalizeFileMap(files);
        String batchId = UUID.randomUUID().toString();
        TestReportStorageService.UploadedBatch uploadedBatch = testReportStorageService.saveTempBatchFiles(report.getId(), batchId, fileMap);
        try {
            String summaryFileName = resolveSummaryFileName(fileMap.keySet());
            if (summaryFileName == null) {
                throw new BusinessException(CommonResponseEnum.FAIL, "校验文件阶段：缺少台账信息汇总.xls或台账信息汇总.xlsx");
            }
            TestReportLedgerExcelParser.ParsedLedger ledger = parseLedgerFromBatch(report.getId(), batchId, fileMap.keySet());
            Map<String, TestDevicePO> dbDeviceMap = loadExistingDevices(collectDeviceNos(ledger));
            Map<String, ClientUnitPO> dbClientMap = loadExistingClients(collectClientNames(ledger));
            validatePointReferences(ledger.getPoints(), dbDeviceMap, dbClientMap, ledger.toDeviceMap(), ledger.toClientMap());
            writeValidatedBatchMeta(report.getId(), batchId, uploadedBatch, ledger, userId);
            return buildValidateSuccessResult(batchId, uploadedBatch.getTempStoragePath(), ledger, fileMap.size());
        } catch (RuntimeException exception) {
            testReportStorageService.deleteTempBatchQuietly(report.getId(), batchId);
            throw exception;
        }
    }

    public TestReportLedgerImportResultVO importLedger(TestReportPO report, String batchId, String userId) {
        BatchMeta batchMeta = loadValidatedBatchMeta(report.getId(), batchId);
        TestReportLedgerExcelParser.ParsedLedger ledger = parseLedgerFromBatch(report.getId(), batchId, batchMeta.toFileNameSet());

        Map<String, TestDevicePO> dbDeviceMap = loadExistingDevices(collectDeviceNos(ledger));
        Map<String, ClientUnitPO> dbClientMap = loadExistingClients(collectClientNames(ledger));
        BaseDataSummary baseDataSummary = maintainBaseData(ledger, dbDeviceMap, dbClientMap, userId);

        List<TestReportPointPO> oldPoints = loadNormalPoints(report.getId());
        List<TestReportGroupReportPO> oldGroupReports = loadNormalGroupReports(report.getId());
        deleteOldFiles(oldPoints, oldGroupReports, report.getData());
        removeOldRows(report.getId());

        String summaryStoragePath = testReportStorageService.saveLedgerSummaryFromTemp(report.getId(), batchId,
                batchMeta.getSummaryFileName());
        int groupCount = saveNewPoints(report.getId(), batchId, ledger.getPoints(), userId);
        LocalDateTime now = LocalDateTime.now();
        report.setData(buildSnapshotJson(ledger, summaryStoragePath, baseDataSummary, groupCount));
        report.setReportGenerateState(TestReportConst.REPORT_GENERATE_STATE_PENDING);
        report.setUpdateBy(userId);
        report.setUpdateTime(now);
        testReportMapper.updateById(report);
        testReportStorageService.deleteTempBatchQuietly(report.getId(), batchId);
        return buildImportSuccessResult(batchId, batchMeta.getTempStoragePath(), ledger, baseDataSummary,
                batchMeta.getUploadedFileCount(), groupCount);
    }

    private TestReportLedgerExcelParser.ParsedLedger parseLedgerFromBatch(String reportId, String batchId, Collection<String> fileNames) {
        String summaryFileName = resolveSummaryFileName(fileNames);
        if (StrUtil.isBlank(summaryFileName)) {
            throw new BusinessException(CommonResponseEnum.FAIL, "校验文件阶段：缺少台账信息汇总.xls或台账信息汇总.xlsx");
        }
        try (AiReportMinioStorageService.StoredObject storedObject =
                     testReportStorageService.getTempBatchObject(reportId, batchId, summaryFileName);
             InputStream inputStream = storedObject.getInputStream()) {
            return testReportLedgerExcelParser.parse(summaryFileName, inputStream,
                    buildAttachmentNameSet(fileNames));
        } catch (Exception exception) {
            throw new BusinessException(CommonResponseEnum.FAIL, "校验文件阶段：读取台账汇总excel失败");
        }
    }

    private Set<String> buildAttachmentNameSet(Collection<String> fileNames) {
        Set<String> attachmentNames = new HashSet<String>();
        if (fileNames == null || fileNames.isEmpty()) {
            return attachmentNames;
        }
        for (String fileName : fileNames) {
            if (StrUtil.isBlank(fileName) || TestReportConst.isLedgerSummaryFileName(fileName)) {
                continue;
            }
            attachmentNames.add(fileName);
        }
        return attachmentNames;
    }

    private String resolveSummaryFileName(Collection<String> fileNames) {
        if (fileNames == null || fileNames.isEmpty()) {
            return null;
        }
        String matchedFileName = null;
        for (String fileName : fileNames) {
            if (!TestReportConst.isLedgerSummaryFileName(fileName)) {
                continue;
            }
            if (matchedFileName != null && !matchedFileName.equals(fileName)) {
                throw new BusinessException(CommonResponseEnum.FAIL,
                        "校验文件阶段：台账汇总文件仅允许上传一个，文件名必须为台账信息汇总.xls或台账信息汇总.xlsx");
            }
            matchedFileName = fileName;
        }
        return matchedFileName;
    }

    private void fillPointValidationResult(TestReportLedgerValidateResultVO result,
                                           TestReportLedgerExcelParser.ParsedLedger ledger,
                                           Set<String> sessionFileNameSet,
                                           Map<String, TestDevicePO> dbDeviceMap,
                                           Map<String, ClientUnitPO> dbClientMap) {
        int missingAttachmentCount = 0;
        Map<String, TestReportLedgerExcelParser.ParsedDeviceRow> sheetDeviceMap = ledger.toDeviceMap();
        Map<String, TestReportLedgerExcelParser.ParsedClientRow> sheetClientMap = ledger.toClientMap();
        for (TestReportLedgerExcelParser.ParsedPointRow pointRow : ledger.getPoints()) {
            TestReportLedgerValidatePointVO pointVO = new TestReportLedgerValidatePointVO();
            pointVO.setRowNo(pointRow.getRowNo());
            pointVO.setSubstationName(pointRow.getSubstationName());
            pointVO.setPointName(pointRow.getPointName());
            pointVO.setPointKey(pointRow.getSubstationName() + "|" + pointRow.getPointName());

            TestReportLedgerValidateAttachmentVO excelAttachment = buildAttachmentRow(
                    pointRow.getExcelAttachmentName(), "excel", sessionFileNameSet.contains(pointRow.getExcelAttachmentName()));

            pointVO.getAttachments().add(excelAttachment);

            int pointMissingCount = 0;
            if (Boolean.TRUE.equals(excelAttachment.getMissing())) {
                pointMissingCount++;
            }
            if (StrUtil.isNotBlank(pointRow.getWordAttachmentName())) {
                TestReportLedgerValidateAttachmentVO wordAttachment = buildAttachmentRow(
                        pointRow.getWordAttachmentName(), "word", sessionFileNameSet.contains(pointRow.getWordAttachmentName()));
                pointVO.getAttachments().add(wordAttachment);
                if (Boolean.TRUE.equals(wordAttachment.getMissing())) {
                    pointMissingCount++;
                }
            }
            pointVO.setMissingAttachmentCount(pointMissingCount);
            fillReferenceValidationResult(pointVO, pointRow, dbDeviceMap, dbClientMap, sheetDeviceMap, sheetClientMap);
            pointVO.setComplete(pointMissingCount == 0
                    && !Boolean.FALSE.equals(pointVO.getTestDeviceNoValid())
                    && !Boolean.FALSE.equals(pointVO.getClientUnitNameValid()));
            missingAttachmentCount += pointMissingCount;
            result.getPoints().add(pointVO);
        }
        result.setPointCount(result.getPoints().size());
        result.setMissingAttachmentCount(missingAttachmentCount);
    }

    private void fillReferenceValidationResult(TestReportLedgerValidatePointVO pointVO,
                                               TestReportLedgerExcelParser.ParsedPointRow pointRow,
                                               Map<String, TestDevicePO> dbDeviceMap,
                                               Map<String, ClientUnitPO> dbClientMap,
                                               Map<String, TestReportLedgerExcelParser.ParsedDeviceRow> sheetDeviceMap,
                                               Map<String, TestReportLedgerExcelParser.ParsedClientRow> sheetClientMap) {
        if (StrUtil.isBlank(pointRow.getTestDeviceNo())) {
            pointVO.setTestDeviceNoValid(Boolean.TRUE);
            pointVO.setTestDeviceNoMessage("检测设备编号为空，跳过校验");
        } else if (dbDeviceMap.containsKey(pointRow.getTestDeviceNo()) || sheetDeviceMap.containsKey(pointRow.getTestDeviceNo())) {
            pointVO.setTestDeviceNoValid(Boolean.TRUE);
            pointVO.setTestDeviceNoMessage("检测设备编号校验通过");
        } else {
            pointVO.setTestDeviceNoValid(Boolean.FALSE);
            pointVO.setTestDeviceNoMessage("第" + pointRow.getRowNo() + "行检测设备编号在数据库和检测设备sheet中都不存在");
        }
        if (StrUtil.isBlank(pointRow.getClientUnitName())) {
            pointVO.setClientUnitNameValid(Boolean.TRUE);
            pointVO.setClientUnitNameMessage("委托单位名称为空，跳过校验");
        } else if (dbClientMap.containsKey(pointRow.getClientUnitName()) || sheetClientMap.containsKey(pointRow.getClientUnitName())) {
            pointVO.setClientUnitNameValid(Boolean.TRUE);
            pointVO.setClientUnitNameMessage("委托单位名称校验通过");
        } else {
            pointVO.setClientUnitNameValid(Boolean.FALSE);
            pointVO.setClientUnitNameMessage("第" + pointRow.getRowNo() + "行委托单位名称在数据库和委托单位sheet中都不存在");
        }
    }

    private TestReportLedgerValidateAttachmentVO buildAttachmentRow(String attachmentName, String attachmentType,
                                                                    boolean uploaded) {
        TestReportLedgerValidateAttachmentVO attachmentVO = new TestReportLedgerValidateAttachmentVO();
        attachmentVO.setAttachmentName(attachmentName);
        attachmentVO.setAttachmentType(attachmentType);
        attachmentVO.setReferenced(Boolean.TRUE);
        attachmentVO.setUploaded(uploaded);
        attachmentVO.setMissing(!uploaded);
        attachmentVO.setReuploadAllowed(Boolean.TRUE);
        attachmentVO.setMessage(uploaded ? "已上传" : "缺少附件，可继续上传");
        return attachmentVO;
    }

    private int countWordAttachments(TestReportLedgerExcelParser.ParsedLedger ledger) {
        if (ledger == null || ledger.getPoints() == null || ledger.getPoints().isEmpty()) {
            return 0;
        }
        int count = 0;
        for (TestReportLedgerExcelParser.ParsedPointRow point : ledger.getPoints()) {
            if (StrUtil.isNotBlank(point.getWordAttachmentName())) {
                count++;
            }
        }
        return count;
    }

    private List<String> resolveOrphanAttachmentNames(TestReportLedgerExcelParser.ParsedLedger ledger, Set<String> sessionFileNameSet) {
        Set<String> referencedNames = new HashSet<String>();
        for (TestReportLedgerExcelParser.ParsedPointRow pointRow : ledger.getPoints()) {
            if (StrUtil.isNotBlank(pointRow.getExcelAttachmentName())) {
                referencedNames.add(pointRow.getExcelAttachmentName());
            }
            if (StrUtil.isNotBlank(pointRow.getWordAttachmentName())) {
                referencedNames.add(pointRow.getWordAttachmentName());
            }
        }
        List<String> orphanAttachmentNames = new java.util.ArrayList<String>();
        for (String fileName : sessionFileNameSet) {
            if (TestReportConst.isLedgerSummaryFileName(fileName)) {
                continue;
            }
            if (!referencedNames.contains(fileName)) {
                orphanAttachmentNames.add(fileName);
            }
        }
        return orphanAttachmentNames;
    }

    private void persistValidateSessionResult(String sessionId, TestReportLedgerValidateResultVO result) {
        try {
            testReportStorageService.writeValidateSessionResult(sessionId, OBJECT_MAPPER.writeValueAsString(result));
        } catch (Exception exception) {
            throw new BusinessException(CommonResponseEnum.FAIL, "校验文件阶段：保存校验结果失败");
        }
    }

    private boolean hasReferenceErrors(List<TestReportLedgerValidatePointVO> points) {
        if (points == null || points.isEmpty()) {
            return false;
        }
        for (TestReportLedgerValidatePointVO point : points) {
            if (point == null) {
                continue;
            }
            if (Boolean.FALSE.equals(point.getTestDeviceNoValid()) || Boolean.FALSE.equals(point.getClientUnitNameValid())) {
                return true;
            }
        }
        return false;
    }

    private void appendReferenceFailReasons(TestReportLedgerValidateResultVO result) {
        if (result == null || result.getPoints() == null || result.getPoints().isEmpty()) {
            return;
        }
        boolean hasDeviceError = false;
        boolean hasClientError = false;
        for (TestReportLedgerValidatePointVO point : result.getPoints()) {
            if (point == null) {
                continue;
            }
            if (Boolean.FALSE.equals(point.getTestDeviceNoValid())) {
                hasDeviceError = true;
            }
            if (Boolean.FALSE.equals(point.getClientUnitNameValid())) {
                hasClientError = true;
            }
        }
        if (hasDeviceError) {
            result.getFailReasons().add("监测点信息中存在检测设备编号未匹配到数据库或检测设备sheet的记录");
        }
        if (hasClientError) {
            result.getFailReasons().add("监测点信息中存在委托单位名称未匹配到数据库或委托单位sheet的记录");
        }
    }

    private void writeValidatedBatchMeta(String reportId, String batchId, TestReportStorageService.UploadedBatch uploadedBatch,
                                         TestReportLedgerExcelParser.ParsedLedger ledger, String userId) {
        BatchMeta batchMeta = new BatchMeta();
        batchMeta.setBatchId(batchId);
        batchMeta.setReportId(reportId);
        batchMeta.setStatus(BATCH_STATUS_VALIDATED);
        batchMeta.setSummaryFileName(ledger.getSummaryFileName());
        batchMeta.setTempStoragePath(uploadedBatch.getTempStoragePath());
        batchMeta.setUploadedFileCount(uploadedBatch.getStoredFileMap().size());
        batchMeta.setUploadedFileNames(uploadedBatch.getStoredFileMap().keySet().stream().collect(Collectors.toList()));
        batchMeta.setValidatedBy(userId);
        batchMeta.setValidatedTime(LocalDateTime.now().toString());
        try {
            testReportStorageService.writeTempBatchMeta(reportId, batchId, OBJECT_MAPPER.writeValueAsString(batchMeta));
        } catch (Exception exception) {
            throw new BusinessException(CommonResponseEnum.FAIL, "校验文件阶段：保存批次元数据失败");
        }
    }

    private BatchMeta loadValidatedBatchMeta(String reportId, String batchId) {
        if (StrUtil.isBlank(batchId)) {
            throw new BusinessException(CommonResponseEnum.FAIL, "导入批次ID不能为空");
        }
        String metaText = testReportStorageService.readTempBatchMeta(reportId, batchId);
        if (StrUtil.isBlank(metaText)) {
            throw new BusinessException(CommonResponseEnum.FAIL, "导入批次不存在或未通过校验");
        }
        try {
            BatchMeta batchMeta = OBJECT_MAPPER.readValue(metaText, BatchMeta.class);
            if (!reportId.equals(batchMeta.getReportId()) || !BATCH_STATUS_VALIDATED.equals(batchMeta.getStatus())) {
                throw new BusinessException(CommonResponseEnum.FAIL, "导入批次不存在或未通过校验");
            }
            return batchMeta;
        } catch (BusinessException exception) {
            throw exception;
        } catch (Exception exception) {
            throw new BusinessException(CommonResponseEnum.FAIL, "读取导入批次信息失败");
        }
    }

    private Map<String, MultipartFile> normalizeFileMap(MultipartFile[] files) {
        if (files == null || files.length == 0) {
            throw new BusinessException(CommonResponseEnum.FAIL, "选择文件阶段：导入文件不能为空");
        }
        Map<String, MultipartFile> fileMap = new LinkedHashMap<String, MultipartFile>();
        for (MultipartFile file : files) {
            if (file == null || file.isEmpty()) {
                continue;
            }
            String fileName = normalizeFileName(file.getOriginalFilename());
            if (fileName == null) {
                throw new BusinessException(CommonResponseEnum.FAIL, "选择文件阶段：文件名不能为空");
            }
            if (fileMap.containsKey(fileName)) {
                throw new BusinessException(CommonResponseEnum.FAIL, "选择文件阶段：存在重复文件名 " + fileName);
            }
            fileMap.put(fileName, file);
        }
        if (fileMap.isEmpty()) {
            throw new BusinessException(CommonResponseEnum.FAIL, "选择文件阶段：导入文件不能为空");
        }
        return fileMap;
    }

    private void validatePointReferences(List<TestReportLedgerExcelParser.ParsedPointRow> points,
                                         Map<String, TestDevicePO> dbDeviceMap,
                                         Map<String, ClientUnitPO> dbClientMap,
                                         Map<String, TestReportLedgerExcelParser.ParsedDeviceRow> sheetDeviceMap,
                                         Map<String, TestReportLedgerExcelParser.ParsedClientRow> sheetClientMap) {
        for (TestReportLedgerExcelParser.ParsedPointRow point : points) {
            if (StrUtil.isNotBlank(point.getTestDeviceNo())
                    && !dbDeviceMap.containsKey(point.getTestDeviceNo())
                    && !sheetDeviceMap.containsKey(point.getTestDeviceNo())) {
                throw new BusinessException(CommonResponseEnum.FAIL,
                        "校验文件阶段：第" + point.getRowNo() + "行检测设备编号在数据库和检测设备sheet中都不存在");
            }
            if (StrUtil.isNotBlank(point.getClientUnitName())
                    && !dbClientMap.containsKey(point.getClientUnitName())
                    && !sheetClientMap.containsKey(point.getClientUnitName())) {
                throw new BusinessException(CommonResponseEnum.FAIL,
                        "校验文件阶段：第" + point.getRowNo() + "行委托单位名称在数据库和委托单位sheet中都不存在");
            }
        }
    }

    private BaseDataSummary maintainBaseData(TestReportLedgerExcelParser.ParsedLedger ledger,
                                             Map<String, TestDevicePO> dbDeviceMap,
                                             Map<String, ClientUnitPO> dbClientMap,
                                             String userId) {
        BaseDataSummary summary = new BaseDataSummary();
        Map<String, String> typeNameIdMap = loadDeviceTypeNameIdMap();
        LocalDateTime now = LocalDateTime.now();
        for (TestReportLedgerExcelParser.ParsedDeviceRow deviceRow : ledger.getDevices()) {
            TestDevicePO existingDevice = dbDeviceMap.get(deviceRow.getNo());
            if (existingDevice != null) {
                summary.setDeviceReuseCount(summary.getDeviceReuseCount() + 1);
                continue;
            }
            String typeName = normalizeFileName(deviceRow.getTypeName());
            String typeId = StrUtil.isBlank(typeName) ? null : typeNameIdMap.get(typeName);
            if (StrUtil.isNotBlank(typeName) && StrUtil.isBlank(typeId)) {
                throw new BusinessException(CommonResponseEnum.FAIL,
                        "基础信息维护阶段：检测设备sheet第" + deviceRow.getRowNo() + "行设备型号不存在");
            }
            TestDevicePO device = new TestDevicePO();
            device.setId(UUID.randomUUID().toString());
            device.setType(typeId);
            device.setNo(deviceRow.getNo());
            device.setValidityPeriod(deviceRow.getValidityPeriod());
            device.setValidityReport(deviceRow.getValidityReportName());
            device.setState(resolveDeviceState(deviceRow.getStateName(), deviceRow.getRowNo()));
            device.setStatus(TestDeviceConst.STATUS_NORMAL);
            device.setCreateBy(userId);
            device.setCreateTime(now);
            device.setUpdateBy(userId);
            device.setUpdateTime(now);
            testDeviceMapper.insert(device);
            dbDeviceMap.put(device.getNo(), device);
            summary.setDeviceAddedCount(summary.getDeviceAddedCount() + 1);
        }

        for (TestReportLedgerExcelParser.ParsedClientRow clientRow : ledger.getClients()) {
            ClientUnitPO existingClient = dbClientMap.get(clientRow.getName());
            if (existingClient != null) {
                summary.setClientReuseCount(summary.getClientReuseCount() + 1);
                continue;
            }
            ClientUnitPO client = new ClientUnitPO();
            client.setId(UUID.randomUUID().toString());
            client.setName(clientRow.getName());
            client.setContactName(defaultEmpty(clientRow.getContactName()));
            client.setPhonenumber(defaultEmpty(clientRow.getPhonenumber()));
            client.setAddress(clientRow.getAddress());
            client.setStatus(ClientUnitConst.STATUS_NORMAL);
            client.setCreateBy(userId);
            client.setCreateTime(now);
            client.setUpdateBy(userId);
            client.setUpdateTime(now);
            clientUnitMapper.insert(client);
            dbClientMap.put(client.getName(), client);
            summary.setClientAddedCount(summary.getClientAddedCount() + 1);
        }
        return summary;
    }

    private int saveNewPoints(String reportId, String batchId, List<TestReportLedgerExcelParser.ParsedPointRow> points,
                              String userId) {
        LocalDateTime now = LocalDateTime.now();
        Set<Integer> groupNos = new HashSet<Integer>();
        int sortNo = 1;
        for (TestReportLedgerExcelParser.ParsedPointRow pointRow : points) {
            TestReportPointPO point = new TestReportPointPO();
            point.setId(UUID.randomUUID().toString());
            point.setReportId(reportId);
            point.setGroupNo(pointRow.getGroupNo());
            point.setSubstationName(pointRow.getSubstationName());
            point.setPointName(pointRow.getPointName());
            point.setExcelAttachmentName(pointRow.getExcelAttachmentName());
            point.setExcelStoragePath(testReportStorageService.savePointExcelFromTemp(reportId, batchId,
                    pointRow.getExcelAttachmentName()));
            point.setSortNo(sortNo++);
            point.setDeleted(TestReportConst.DELETED_NO);
            point.setCreateBy(userId);
            point.setCreateTime(now);
            point.setUpdateBy(userId);
            point.setUpdateTime(now);
            testReportPointMapper.insert(point);
            if (pointRow.getGroupNo() != null) {
                groupNos.add(pointRow.getGroupNo());
            }
        }
        return groupNos.size();
    }

    private void deleteOldFiles(List<TestReportPointPO> oldPoints, List<TestReportGroupReportPO> oldGroupReports, String data) {
        for (TestReportPointPO point : oldPoints) {
            testReportStorageService.deleteQuietly(point.getExcelStoragePath());
            testReportStorageService.deleteQuietly(point.getWordStoragePath());
        }
        for (TestReportGroupReportPO groupReport : oldGroupReports) {
            testReportStorageService.deleteQuietly(groupReport.getReportStoragePath());
        }
        testReportStorageService.deleteQuietly(resolveSummaryStoragePath(data));
    }

    private void removeOldRows(String reportId) {
        LambdaQueryWrapper<TestReportPointPO> pointWrapper = new LambdaQueryWrapper<TestReportPointPO>();
        pointWrapper.eq(TestReportPointPO::getReportId, reportId);
        testReportPointMapper.delete(pointWrapper);

        LambdaQueryWrapper<TestReportGroupReportPO> groupReportWrapper = new LambdaQueryWrapper<TestReportGroupReportPO>();
        groupReportWrapper.eq(TestReportGroupReportPO::getReportId, reportId);
        testReportGroupReportMapper.delete(groupReportWrapper);
    }

    private String buildSnapshotJson(TestReportLedgerExcelParser.ParsedLedger ledger, String summaryStoragePath,
                                     BaseDataSummary summary, int groupCount) {
        ObjectNode root = OBJECT_MAPPER.createObjectNode();
        root.put("version", "2.0");
        root.put("importMode", "summary-excel-with-base-data");
        root.put("summaryFileName", ledger.getSummaryFileName());
        root.put("summaryStoragePath", summaryStoragePath);

        ObjectNode sheetSummary = root.putObject("sheetSummary");
        sheetSummary.put("pointSheet", TestReportConst.LEDGER_POINT_SHEET_NAME);
        sheetSummary.put("deviceSheetPresent", ledger.getExistingSheets().contains(TestReportConst.LEDGER_DEVICE_SHEET_NAME));
        sheetSummary.put("clientSheetPresent", ledger.getExistingSheets().contains(TestReportConst.LEDGER_CLIENT_SHEET_NAME));

        root.put("rowCount", ledger.getPoints().size());
        root.put("pointCount", ledger.getPoints().size());
        root.put("groupCount", groupCount);

        ArrayNode groupSummary = root.putArray("groupSummary");
        Map<Integer, Long> groupCounter = ledger.getPoints().stream()
                .collect(Collectors.groupingBy(TestReportLedgerExcelParser.ParsedPointRow::getGroupNo,
                        LinkedHashMap::new, Collectors.counting()));
        for (Map.Entry<Integer, Long> entry : groupCounter.entrySet()) {
            ObjectNode groupNode = groupSummary.addObject();
            if (entry.getKey() == null) {
                groupNode.putNull("groupNo");
            } else {
                groupNode.put("groupNo", entry.getKey());
            }
            groupNode.put("count", entry.getValue());
        }

        ObjectNode baseDataSummary = root.putObject("baseDataSummary");
        baseDataSummary.put("deviceAddedCount", summary.getDeviceAddedCount());
        baseDataSummary.put("deviceReuseCount", summary.getDeviceReuseCount());
        baseDataSummary.put("clientAddedCount", summary.getClientAddedCount());
        baseDataSummary.put("clientReuseCount", summary.getClientReuseCount());
        return root.toString();
    }

    private TestReportLedgerImportResultVO buildValidateSuccessResult(String batchId, String tempStoragePath,
                                                                      TestReportLedgerExcelParser.ParsedLedger ledger,
                                                                      int uploadedFileCount) {
        TestReportLedgerImportResultVO result = new TestReportLedgerImportResultVO();
        result.setBatchId(batchId);
        result.setCurrentStage("文件校验");
        result.setSuccess(Boolean.TRUE);
        result.setSummaryFileName(ledger.getSummaryFileName());
        result.setTempStoragePath(tempStoragePath);
        result.setUploadedFileCount(uploadedFileCount);
        result.setTotalFileCount(uploadedFileCount);
        result.setPointCount(ledger.getPoints().size());
        result.setExcelAttachmentCount(ledger.getPoints().size());
        result.setWordAttachmentCount(countWordAttachments(ledger));
        result.getStages().add(buildStage("文件选择", "文件选择完成"));
        result.getStages().add(buildStage("文件校验", "文件校验通过"));
        return result;
    }

    private TestReportLedgerImportResultVO buildImportSuccessResult(String batchId, String tempStoragePath,
                                                                    TestReportLedgerExcelParser.ParsedLedger ledger,
                                                                    BaseDataSummary summary, int uploadedFileCount,
                                                                    int groupCount) {
        TestReportLedgerImportResultVO result = new TestReportLedgerImportResultVO();
        result.setBatchId(batchId);
        result.setCurrentStage("完成");
        result.setSuccess(Boolean.TRUE);
        result.setSummaryFileName(ledger.getSummaryFileName());
        result.setTempStoragePath(tempStoragePath);
        result.setUploadedFileCount(uploadedFileCount);
        result.setTotalFileCount(uploadedFileCount);
        result.setPointCount(ledger.getPoints().size());
        result.setExcelAttachmentCount(ledger.getPoints().size());
        result.setWordAttachmentCount(countWordAttachments(ledger));
        result.setDeviceAddedCount(summary.getDeviceAddedCount());
        result.setDeviceReuseCount(summary.getDeviceReuseCount());
        result.setClientAddedCount(summary.getClientAddedCount());
        result.setClientReuseCount(summary.getClientReuseCount());
        result.setGroupCount(groupCount);
        result.getStages().add(buildStage("文件选择", "文件选择完成"));
        result.getStages().add(buildStage("文件校验", "已复用已校验批次"));
        result.getStages().add(buildStage("文件导入", "正式文件和点位数据已导入"));
        result.getStages().add(buildStage("基础信息维护", "基础信息维护完成"));
        result.getStages().add(buildStage("完成", "导入完成"));
        return result;
    }

    private TestReportLedgerImportStageVO buildStage(String stage, String message) {
        TestReportLedgerImportStageVO stageVO = new TestReportLedgerImportStageVO();
        stageVO.setStage(stage);
        stageVO.setSuccess(Boolean.TRUE);
        stageVO.setMessage(message);
        return stageVO;
    }

    private String resolveSummaryStoragePath(String data) {
        if (StrUtil.isBlank(data)) {
            return null;
        }
        try {
            String summaryStoragePath = OBJECT_MAPPER.readTree(data).path("summaryStoragePath").asText();
            return StrUtil.isBlank(summaryStoragePath) ? null : summaryStoragePath;
        } catch (Exception exception) {
            return null;
        }
    }

    private List<TestReportPointPO> loadNormalPoints(String reportId) {
        LambdaQueryWrapper<TestReportPointPO> wrapper = new LambdaQueryWrapper<TestReportPointPO>();
        wrapper.eq(TestReportPointPO::getReportId, reportId)
                .eq(TestReportPointPO::getDeleted, TestReportConst.DELETED_NO);
        return testReportPointMapper.selectList(wrapper);
    }

    private List<TestReportGroupReportPO> loadNormalGroupReports(String reportId) {
        LambdaQueryWrapper<TestReportGroupReportPO> wrapper = new LambdaQueryWrapper<TestReportGroupReportPO>();
        wrapper.eq(TestReportGroupReportPO::getReportId, reportId)
                .eq(TestReportGroupReportPO::getDeleted, TestReportConst.DELETED_NO);
        return testReportGroupReportMapper.selectList(wrapper);
    }

    private Map<String, TestDevicePO> loadExistingDevices(Collection<String> deviceNos) {
        if (deviceNos.isEmpty()) {
            return new HashMap<String, TestDevicePO>();
        }
        LambdaQueryWrapper<TestDevicePO> wrapper = new LambdaQueryWrapper<TestDevicePO>();
        wrapper.in(TestDevicePO::getNo, deviceNos)
                .eq(TestDevicePO::getStatus, TestDeviceConst.STATUS_NORMAL);
        return testDeviceMapper.selectList(wrapper).stream()
                .collect(Collectors.toMap(TestDevicePO::getNo, item -> item, (first, second) -> first, HashMap::new));
    }

    private Map<String, ClientUnitPO> loadExistingClients(Collection<String> clientNames) {
        if (clientNames.isEmpty()) {
            return new HashMap<String, ClientUnitPO>();
        }
        LambdaQueryWrapper<ClientUnitPO> wrapper = new LambdaQueryWrapper<ClientUnitPO>();
        wrapper.in(ClientUnitPO::getName, clientNames)
                .eq(ClientUnitPO::getStatus, ClientUnitConst.STATUS_NORMAL);
        return clientUnitMapper.selectList(wrapper).stream()
                .collect(Collectors.toMap(ClientUnitPO::getName, item -> item, (first, second) -> first, HashMap::new));
    }

    private Collection<String> collectDeviceNos(TestReportLedgerExcelParser.ParsedLedger ledger) {
        Set<String> deviceNos = new HashSet<String>();
        for (TestReportLedgerExcelParser.ParsedPointRow point : ledger.getPoints()) {
            if (StrUtil.isNotBlank(point.getTestDeviceNo())) {
                deviceNos.add(point.getTestDeviceNo());
            }
        }
        for (TestReportLedgerExcelParser.ParsedDeviceRow device : ledger.getDevices()) {
            deviceNos.add(device.getNo());
        }
        return deviceNos;
    }

    private Collection<String> collectClientNames(TestReportLedgerExcelParser.ParsedLedger ledger) {
        Set<String> clientNames = new HashSet<String>();
        for (TestReportLedgerExcelParser.ParsedPointRow point : ledger.getPoints()) {
            if (StrUtil.isNotBlank(point.getClientUnitName())) {
                clientNames.add(point.getClientUnitName());
            }
        }
        for (TestReportLedgerExcelParser.ParsedClientRow client : ledger.getClients()) {
            clientNames.add(client.getName());
        }
        return clientNames;
    }

    private Map<String, String> loadDeviceTypeNameIdMap() {
        List<TestDeviceTypeNameVO> typeRows = testDeviceMapper.selectNormalDeviceTypes();
        if (typeRows == null || typeRows.isEmpty()) {
            return Collections.emptyMap();
        }
        Map<String, String> result = new HashMap<String, String>();
        for (TestDeviceTypeNameVO typeRow : typeRows) {
            if (typeRow != null && StrUtil.isNotBlank(typeRow.getName()) && StrUtil.isNotBlank(typeRow.getId())) {
                result.put(typeRow.getName(), typeRow.getId());
            }
        }
        return result;
    }

    private String resolveDeviceState(String stateName, Integer rowNo) {
        String normalizedState = normalizeFileName(stateName);
        if (normalizedState == null) {
            return null;
        }
        if ("01".equals(normalizedState) || "在运".equals(normalizedState)) {
            return TestDeviceConst.STATE_RUNNING;
        }
        if ("02".equals(normalizedState) || "退运".equals(normalizedState)) {
            return TestDeviceConst.STATE_RETIRED;
        }
        throw new BusinessException(CommonResponseEnum.FAIL,
                "基础信息维护阶段：检测设备sheet第" + rowNo + "行设备状态不正确");
    }

    private String defaultEmpty(String value) {
        return value == null ? "" : value;
    }

    private String normalizeFileName(String fileName) {
        String normalized = fileName == null ? null : fileName.trim();
        return StrUtil.isBlank(normalized) ? null : normalized;
    }

    private static class BaseDataSummary {
        private int deviceAddedCount;
        private int deviceReuseCount;
        private int clientAddedCount;
        private int clientReuseCount;

        public int getDeviceAddedCount() {
            return deviceAddedCount;
        }

        public void setDeviceAddedCount(int deviceAddedCount) {
            this.deviceAddedCount = deviceAddedCount;
        }

        public int getDeviceReuseCount() {
            return deviceReuseCount;
        }

        public void setDeviceReuseCount(int deviceReuseCount) {
            this.deviceReuseCount = deviceReuseCount;
        }

        public int getClientAddedCount() {
            return clientAddedCount;
        }

        public void setClientAddedCount(int clientAddedCount) {
            this.clientAddedCount = clientAddedCount;
        }

        public int getClientReuseCount() {
            return clientReuseCount;
        }

        public void setClientReuseCount(int clientReuseCount) {
            this.clientReuseCount = clientReuseCount;
        }
    }

    public static class BatchMeta {
        private String batchId;
        private String reportId;
        private String status;
        private String summaryFileName;
        private String tempStoragePath;
        private Integer uploadedFileCount;
        private List<String> uploadedFileNames;
        private String validatedBy;
        private String validatedTime;

        public Set<String> toFileNameSet() {
            return uploadedFileNames == null ? Collections.<String>emptySet() : new HashSet<String>(uploadedFileNames);
        }

        public String getBatchId() {
            return batchId;
        }

        public void setBatchId(String batchId) {
            this.batchId = batchId;
        }

        public String getReportId() {
            return reportId;
        }

        public void setReportId(String reportId) {
            this.reportId = reportId;
        }

        public String getStatus() {
            return status;
        }

        public void setStatus(String status) {
            this.status = status;
        }

        public String getSummaryFileName() {
            return summaryFileName;
        }

        public void setSummaryFileName(String summaryFileName) {
            this.summaryFileName = summaryFileName;
        }

        public String getTempStoragePath() {
            return tempStoragePath;
        }

        public void setTempStoragePath(String tempStoragePath) {
            this.tempStoragePath = tempStoragePath;
        }

        public Integer getUploadedFileCount() {
            return uploadedFileCount;
        }

        public void setUploadedFileCount(Integer uploadedFileCount) {
            this.uploadedFileCount = uploadedFileCount;
        }

        public List<String> getUploadedFileNames() {
            return uploadedFileNames;
        }

        public void setUploadedFileNames(List<String> uploadedFileNames) {
            this.uploadedFileNames = uploadedFileNames;
        }

        public String getValidatedBy() {
            return validatedBy;
        }

        public void setValidatedBy(String validatedBy) {
            this.validatedBy = validatedBy;
        }

        public String getValidatedTime() {
            return validatedTime;
        }

        public void setValidatedTime(String validatedTime) {
            this.validatedTime = validatedTime;
        }
    }
}
