package com.njcn.rdms.module.project.service.performance;

import com.njcn.rdms.framework.common.pojo.CommonResult;
import com.njcn.rdms.framework.common.pojo.PageResult;
import com.njcn.rdms.framework.common.util.object.BeanUtils;
import com.njcn.rdms.framework.security.core.util.SecurityFrameworkUtils;
import com.njcn.rdms.module.project.constant.PerformanceExcelCellConstants;
import com.njcn.rdms.module.project.constant.PerformanceConstants;
import com.njcn.rdms.module.project.constant.SignOffConstants;
import com.njcn.rdms.module.project.constant.WorkReportConstants;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceMonthlyResultRespVO;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceSheetCreateReqVO;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceSheetExcelUpdateReqVO;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceSheetImportZipReqVO;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceSheetImportZipRespVO;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceSheetPageReqVO;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceSheetPrefillRespVO;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceSheetRespVO;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceSheetResponseRecordRespVO;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceSheetSignOffReqVO;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceSheetStatusActionReqVO;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceSheetStatusDictRespVO;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceSheetStatusLogRespVO;
import com.njcn.rdms.module.project.controller.admin.performance.vo.PerformanceSheetStatusTransitionRespVO;
import com.njcn.rdms.module.project.dal.dataobject.performance.PerformanceSheetDO;
import com.njcn.rdms.module.project.dal.dataobject.performance.PerformanceSheetResponseRecordDO;
import com.njcn.rdms.module.project.dal.dataobject.performance.PerformanceSheetSignOffDO;
import com.njcn.rdms.module.project.dal.dataobject.performance.PerformanceSheetSignOffRecordDO;
import com.njcn.rdms.module.project.dal.dataobject.performance.PerformanceSheetStatusLogDO;
import com.njcn.rdms.module.project.dal.dataobject.performance.PerformanceTemplateDO;
import com.njcn.rdms.module.project.dal.dataobject.signoff.EmployeeSignOffBatchDO;
import com.njcn.rdms.module.project.dal.dataobject.status.ObjectStatusModelDO;
import com.njcn.rdms.module.project.dal.dataobject.status.ObjectStatusTransitionDO;
import com.njcn.rdms.module.project.dal.mysql.performance.PerformanceSheetMapper;
import com.njcn.rdms.module.project.dal.mysql.performance.PerformanceSheetResponseRecordMapper;
import com.njcn.rdms.module.project.dal.mysql.performance.PerformanceSheetSignOffMapper;
import com.njcn.rdms.module.project.dal.mysql.performance.PerformanceSheetSignOffRecordMapper;
import com.njcn.rdms.module.project.dal.mysql.performance.PerformanceSheetStatusLogMapper;
import com.njcn.rdms.module.project.dal.mysql.performance.PerformanceTemplateMapper;
import com.njcn.rdms.module.project.dal.mysql.signoff.EmployeeSignOffBatchMapper;
import com.njcn.rdms.module.project.dal.mysql.status.ObjectStatusModelMapper;
import com.njcn.rdms.module.project.dal.mysql.status.ObjectStatusTransitionMapper;
import com.njcn.rdms.module.project.dal.mysql.workreport.monthly.MonthlyReportApprovalRecordMapper;
import com.njcn.rdms.module.project.enums.ErrorCodeConstants;
import com.njcn.rdms.module.project.framework.notify.NotifySendEvent;
import com.njcn.rdms.module.project.framework.notify.NotifyTemplateCodeConstants;
import com.njcn.rdms.module.project.service.signoff.EmployeeSignOffStatusService;
import com.njcn.rdms.module.project.service.status.StatusActionTextResolver;
import com.njcn.rdms.module.project.service.team.TeamDashboardAccessService;
import com.njcn.rdms.module.project.service.workreport.common.WorkReportCommonService;
import com.njcn.rdms.module.system.api.dept.DeptApi;
import com.njcn.rdms.module.system.api.dept.dto.DeptRespDTO;
import com.njcn.rdms.module.system.api.file.FileApi;
import com.njcn.rdms.module.system.api.file.dto.FileContentCreateReqDTO;
import com.njcn.rdms.module.system.api.user.AdminUserApi;
import com.njcn.rdms.module.system.api.user.UserManagementRelationApi;
import com.njcn.rdms.module.system.api.user.dto.AdminUserRespDTO;
import com.njcn.rdms.module.system.enums.notify.NotifyMessageLevelConstants;
import jakarta.annotation.Resource;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellCopyContext;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.PageMargin;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.PaneInformation;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.context.ApplicationEventPublisher;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import lombok.extern.slf4j.Slf4j;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.YearMonth;
import java.time.format.DateTimeParseException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.IdentityHashMap;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;

import static com.njcn.rdms.framework.common.exception.util.ServiceExceptionUtil.exception;
import static com.njcn.rdms.framework.common.exception.util.ServiceExceptionUtil.invalidParamException;

@Service
@Slf4j
public class PerformanceSheetServiceImpl implements PerformanceSheetService {

    private static final String EXCEL_CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    private static final Pattern ASSESSED_EMPLOYEE_TEXT_PATTERN = Pattern.compile("(被考核人\\s*[：:]\\s*)(.+)");
    private static final long CURRENT_TEMPLATE_CACHE_KEY = -1L;

    @Resource
    private PerformanceSheetMapper performanceSheetMapper;
    @Resource
    private PerformanceTemplateMapper performanceTemplateMapper;
    @Resource
    private PerformanceSheetStatusLogMapper performanceSheetStatusLogMapper;
    @Resource
    private PerformanceSheetResponseRecordMapper performanceSheetResponseRecordMapper;
    @Resource
    private PerformanceSheetSignOffMapper performanceSheetSignOffMapper;
    @Resource
    private PerformanceSheetSignOffRecordMapper performanceSheetSignOffRecordMapper;
    @Resource
    private EmployeeSignOffBatchMapper employeeSignOffBatchMapper;
    @Resource
    private MonthlyReportApprovalRecordMapper monthlyReportApprovalRecordMapper;
    @Resource
    private ObjectStatusModelMapper objectStatusModelMapper;
    @Resource
    private ObjectStatusTransitionMapper objectStatusTransitionMapper;
    @Resource
    private StatusActionTextResolver statusActionTextResolver;
    @Resource
    private EmployeeSignOffStatusService employeeSignOffStatusService;
    @Resource
    private AdminUserApi adminUserApi;
    @Resource
    private DeptApi deptApi;
    @Resource
    private UserManagementRelationApi userManagementRelationApi;
    @Resource
    private FileApi fileApi;
    @Resource
    private WorkReportCommonService workReportCommonService;
    @Resource
    private TeamDashboardAccessService teamDashboardAccessService;
    @Resource
    private ApplicationEventPublisher applicationEventPublisher;
    @Resource
    private TransactionTemplate transactionTemplate;

    @Override
    public PageResult<PerformanceSheetRespVO> getSheetPage(PerformanceSheetPageReqVO reqVO) {
        PageResult<PerformanceSheetDO> pageResult;
        if (reqVO.getEmployeeIds() != null) { //团队视角下进入
            List<Long> employeeIds = teamDashboardAccessService.resolveRequestedSubordinateUserIds(
                    reqVO.getEmployeeIds(), PerformanceConstants.PERMISSION_TEAM_DASHBOARD);
            pageResult = performanceSheetMapper.selectEmployeePage(employeeIds, reqVO);
        } else { //个人视角下进入
            pageResult = performanceSheetMapper.selectEmployeePage(SecurityFrameworkUtils.getLoginUserId(), reqVO);
        }
        return new PageResult<>(pageResult.getList().stream().map(this::toRespVO).toList(), pageResult.getTotal());
    }

    @Override
    public PerformanceSheetRespVO getSheet(Long id) {
        PerformanceSheetDO sheet = validateReadableSheet(id);
        return toRespVO(sheet);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Long createSheet(PerformanceSheetCreateReqVO reqVO) {
        String periodMonth = normalizePeriodMonth(reqVO.getPeriodMonth());
        PerformanceTemplateDO template = performanceTemplateMapper.selectCurrent();
        if (template == null) {
            throw exception(ErrorCodeConstants.PERFORMANCE_TEMPLATE_CURRENT_NOT_EXISTS);
        }
        if (performanceSheetMapper.selectByEmployeeIdAndPeriodMonth(reqVO.getEmployeeId(), periodMonth) != null) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_DUPLICATE);
        }

        AdminUserRespDTO employee = validateActiveUser(reqVO.getEmployeeId());
        AdminUserRespDTO manager = loadDirectManager(employee.getId());
        validateManagerOnly(manager.getId());
        DeptRespDTO dept = validateEmployeeDept(employee.getDeptId());

        PerformanceSheetDO sheet = new PerformanceSheetDO();
        sheet.setPeriodMonth(periodMonth);
        sheet.setEmployeeId(employee.getId());
        sheet.setEmployeeName(defaultText(employee.getNickname()));
        sheet.setEmployeeDeptId(employee.getDeptId());
        sheet.setEmployeeDeptName(dept.getName().trim());
        sheet.setDeptOrgType(dept.getOrgType().trim());
        sheet.setManagerId(manager.getId());
        sheet.setManagerName(defaultText(manager.getNickname()));
        sheet.setTemplateId(template.getId());
        sheet.setFileName(buildPerformanceFileName(periodMonth, employee.getNickname()));
        sheet.setFileVersion(0);
        sheet.setStatusCode(getInitialStatusCode());
        sheet.setSignOffStatus(SignOffConstants.STATUS_NOT_STARTED);
        performanceSheetMapper.insert(sheet);
        return sheet.getId();
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void updateExcel(Long id, PerformanceSheetExcelUpdateReqVO reqVO) {
        PerformanceSheetDO current = validateSheetExists(id);
        validateManagerOnly(current.getManagerId());
        validateEditable(current);

        PerformanceSheetDO update = new PerformanceSheetDO();
        update.setFileId(reqVO.getFileId());
        update.setFileName(reqVO.getFileName().trim());
        update.setFileVersion(reqVO.getFileVersion() + 1);
        update.setActualScoreTotal(normalizeScore(reqVO.getActualScoreTotal()));
        update.setBaseScoreTotal(normalizeScore(reqVO.getBaseScoreTotal()));
        update.setExtraScoreTotal(normalizeScore(reqVO.getExtraScoreTotal()));
        int updateCount = performanceSheetMapper.updateExcelByIdAndVersion(update, id, reqVO.getFileVersion());
        if (updateCount != 1) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_CONCURRENT_MODIFIED);
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteSheet(Long id) {
        PerformanceSheetDO sheet = validateSheetExists(id);
        validateManagerOnly(sheet.getManagerId());
        if (!PerformanceConstants.STATUS_DRAFT.equals(sheet.getStatusCode())) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_NOT_ALLOW_DELETE);
        }
        performanceSheetMapper.deleteById(id);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void send(Long id) {
        processManagerStatusAction(id, PerformanceConstants.ACTION_SEND);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void resend(Long id) {
        processManagerStatusAction(id, PerformanceConstants.ACTION_RESEND);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void confirm(Long id, PerformanceSheetStatusActionReqVO reqVO) {
        processEmployeeResponse(id, PerformanceConstants.ACTION_CONFIRM,
                normalizeNullableText(reqVO == null ? null : reqVO.getReason()));
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void reject(Long id, PerformanceSheetStatusActionReqVO reqVO) {
        String reason = normalizeNullableText(reqVO == null ? null : reqVO.getReason());
        if (!StringUtils.hasText(reason)) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_REJECT_REASON_REQUIRED);
        }
        PerformanceSheetDO sheet = processEmployeeResponse(id, PerformanceConstants.ACTION_REJECT, reason);
        publishPerformanceNotice(List.of(sheet.getManagerId()), NotifyTemplateCodeConstants.PERFORMANCE_REJECTED, sheet);
    }

    @Override
    public PerformanceSheetImportZipRespVO importZip(PerformanceSheetImportZipReqVO reqVO) throws Exception {
        MultipartFile file = reqVO.getFile();
        if (file == null || file.isEmpty()) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_IMPORT_ZIP_EMPTY);
        }
        String periodMonth = normalizePeriodMonth(reqVO.getPeriodMonth());
        CurrentOperatorProfile operatorProfile = loadCurrentOperatorProfile();
        validateBatchUnlocked(periodMonth, operatorProfile.deptId());
        List<Long> candidateEmployeeIds = resolveRequestedEmployeeIds(reqVO.getEmployeeIds(),
                PerformanceConstants.PERMISSION_SHEET_IMPORT);
        if (candidateEmployeeIds.isEmpty()) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_IMPORT_TARGET_EMPTY);
        }

        Map<String, List<PerformanceSheetDO>> sheetsByEmployeeName = performanceSheetMapper
                .selectListByEmployeeIdsAndPeriodMonth(candidateEmployeeIds, periodMonth)
                .stream()
                .collect(java.util.stream.Collectors.groupingBy(PerformanceSheetDO::getEmployeeName,
                        LinkedHashMap::new, java.util.stream.Collectors.toList()));
        if (sheetsByEmployeeName.isEmpty()) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_IMPORT_TARGET_EMPTY);
        }

        List<String> messages = new ArrayList<>();
        int workbookCount = 0;
        int sheetCount = 0;
        int successCount = 0;
        int skippedCount = 0;
        int failedCount = 0;
        List<ImportedWorkbookEntry> workbookEntries = readZipWorkbookEntries(file.getBytes());
        for (ImportedWorkbookEntry workbookEntry : workbookEntries) {
            if (shouldIgnoreImportedWorkbook(workbookEntry.displayName())) {
                skippedCount++;
                messages.add("跳过工作簿：" + workbookEntry.displayName() + "，原因：兼容忽略 0_绩效表汇总");
                log.info("导入绩效压缩包时跳过工作簿，workbookName={}, reason={}",
                        workbookEntry.displayName(), "兼容忽略 0_绩效表汇总");
                continue;
            }
            workbookCount++;
            try (ByteArrayInputStream inputStream = new ByteArrayInputStream(workbookEntry.content());
                 Workbook workbook = WorkbookFactory.create(inputStream)) {
                FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                DataFormatter formatter = new DataFormatter();
                for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                    Sheet importedSheet = workbook.getSheetAt(sheetIndex);
                    sheetCount++;
                    try {
                        importOneSheet(periodMonth, workbookEntry.displayName(), importedSheet, evaluator, formatter,
                                sheetsByEmployeeName);
                        successCount++;
                    } catch (Exception ex) {
                        failedCount++;
                        String message = "导入失败：" + workbookEntry.displayName() + " / " + importedSheet.getSheetName()
                                + "，原因：" + ex.getMessage();
                        messages.add(message);
                        log.warn("导入绩效 sheet 失败，workbookName={}, sheetName={}, reason={}",
                                workbookEntry.displayName(), importedSheet.getSheetName(), ex.getMessage(), ex);
                    }
                }
            }
        }
        PerformanceSheetImportZipRespVO respVO = new PerformanceSheetImportZipRespVO();
        respVO.setWorkbookCount(workbookCount);
        respVO.setSheetCount(sheetCount);
        respVO.setSuccessCount(successCount);
        respVO.setSkippedCount(skippedCount);
        respVO.setFailedCount(failedCount);
        respVO.setMessages(messages);
        return respVO;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Long signOff(PerformanceSheetSignOffReqVO reqVO) {
        String periodMonth = normalizePeriodMonth(reqVO.getPeriodMonth());
        CurrentOperatorProfile operatorProfile = loadCurrentOperatorProfile();
        validateBatchUnlocked(periodMonth, operatorProfile.deptId());

        boolean explicitSelection = reqVO.getEmployeeIds() != null && !reqVO.getEmployeeIds().isEmpty();
        List<Long> requestedEmployeeIds = resolveRequestedEmployeeIds(reqVO.getEmployeeIds(),
                PerformanceConstants.PERMISSION_SHEET_SIGN_OFF);
        if (requestedEmployeeIds.isEmpty()) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_SIGN_OFF_CANDIDATE_EMPTY);
        }

        List<PerformanceSheetDO> performanceSheets = performanceSheetMapper
                .selectListByEmployeeIdsAndPeriodMonth(requestedEmployeeIds, periodMonth);
        List<PerformanceSheetDO> eligiblePerformanceSheets = performanceSheets.stream()
                .filter(this::isEligibleForIssue)
                .toList();
        Map<Long, PerformanceSheetDO> performanceByEmployeeId = eligiblePerformanceSheets.stream()
                .collect(java.util.stream.Collectors.toMap(PerformanceSheetDO::getEmployeeId, item -> item,
                        (left, right) -> left, LinkedHashMap::new));

        List<com.njcn.rdms.module.project.dal.dataobject.workreport.monthly.MonthlyReportDO> monthlyReports =
                workReportCommonService.listMonthlyReportsForSignOff(requestedEmployeeIds, periodMonth);
        List<com.njcn.rdms.module.project.dal.dataobject.workreport.monthly.MonthlyReportDO> eligibleMonthlyReports =
                monthlyReports.stream()
                        .filter(this::isEligibleMonthlyReportForIssue)
                        .filter(report -> hasLatestApprovedRecord(report.getId()))
                        .toList();
        Map<Long, com.njcn.rdms.module.project.dal.dataobject.workreport.monthly.MonthlyReportDO> monthlyByEmployeeId =
                eligibleMonthlyReports.stream()
                        .collect(java.util.stream.Collectors.toMap(
                                com.njcn.rdms.module.project.dal.dataobject.workreport.monthly.MonthlyReportDO::getReporterId,
                                item -> item, (left, right) -> left, LinkedHashMap::new));

        List<Long> targetEmployeeIds = new ArrayList<>();
        if (explicitSelection) {
            for (Long employeeId : requestedEmployeeIds) {
                if (!performanceByEmployeeId.containsKey(employeeId) || !monthlyByEmployeeId.containsKey(employeeId)) {
                    throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_SIGN_OFF_CANDIDATE_EMPTY);
                }
                targetEmployeeIds.add(employeeId);
            }
        } else {
            for (Long employeeId : requestedEmployeeIds) {
                if (performanceByEmployeeId.containsKey(employeeId) && monthlyByEmployeeId.containsKey(employeeId)) {
                    targetEmployeeIds.add(employeeId);
                }
            }
            if (targetEmployeeIds.isEmpty()) {
                throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_SIGN_OFF_CANDIDATE_EMPTY);
            }
        }

        List<PerformanceSheetDO> targetSheets = targetEmployeeIds.stream()
                .map(performanceByEmployeeId::get)
                .filter(Objects::nonNull)
                .toList();
        List<com.njcn.rdms.module.project.dal.dataobject.workreport.monthly.MonthlyReportDO> targetReports =
                targetEmployeeIds.stream()
                        .map(monthlyByEmployeeId::get)
                        .filter(Objects::nonNull)
                        .toList();

        EmployeeSignOffBatchDO batch = new EmployeeSignOffBatchDO();
        batch.setPeriodMonth(periodMonth);
        batch.setDeptId(operatorProfile.deptId());
        batch.setDeptName(operatorProfile.deptName());
        batch.setOperatorUserId(operatorProfile.userId());
        batch.setOperatorName(operatorProfile.userName());
        batch.setLockedFlag(1);
        batch.setRemark(normalizeNullableText(reqVO.getRemark()));
        employeeSignOffBatchMapper.insert(batch);

        issuePerformanceSignOff(targetSheets, batch.getId());
        workReportCommonService.issueMonthlySignOff(targetReports, batch.getId());
        return batch.getId();
    }

    @Override
    public PerformanceDownloadFile download(Long id) {
        PerformanceSheetDO sheet = validateReadableSheet(id);
        return new PerformanceDownloadFile(defaultDownloadName(sheet), EXCEL_CONTENT_TYPE, readFileContent(sheet));
    }

    @Override
    public PerformanceDownloadFile batchDownload(Collection<Long> ids) {
        if (ids == null || ids.isEmpty()) {
            throw invalidParamException("绩效表 ID 不能为空");
        }
        List<PerformanceSheetDO> sheets = new ArrayList<>();
        for (Long id : ids) {
            sheets.add(validateReadableSheet(id));
        }
        return buildMergedWorkbookDownload(sheets);
    }

    @Override
    public PerformanceDownloadFile exportByCondition(PerformanceSheetPageReqVO reqVO) {
        List<Long> employeeIds;
        if (reqVO.getEmployeeIds() != null) {
            employeeIds = teamDashboardAccessService.resolveRequestedSubordinateUserIds(
                    reqVO.getEmployeeIds(), PerformanceConstants.PERMISSION_TEAM_DASHBOARD);
            Long loginUserId = SecurityFrameworkUtils.getLoginUserId();
            if (loginUserId != null) {
                LinkedHashSet<Long> mergedIds = new LinkedHashSet<>(employeeIds);
                mergedIds.add(loginUserId);
                employeeIds = new ArrayList<>(mergedIds);
            }
        } else {
            employeeIds = List.of(Objects.requireNonNull(SecurityFrameworkUtils.getLoginUserId()));
        }
        List<PerformanceSheetDO> sheets = performanceSheetMapper.selectExportList(reqVO, employeeIds);
        return buildMergedWorkbookDownload(sheets);
    }

    @Override
    public List<PerformanceSheetStatusLogRespVO> getStatusLogs(Long id) {
        validateReadableSheet(id);
        return BeanUtils.toBean(performanceSheetStatusLogMapper.selectListBySheetId(id),
                PerformanceSheetStatusLogRespVO.class);
    }

    @Override
    public List<PerformanceSheetResponseRecordRespVO> getResponseRecords(Long id) {
        validateReadableSheet(id);
        return BeanUtils.toBean(performanceSheetResponseRecordMapper.selectListBySheetId(id),
                PerformanceSheetResponseRecordRespVO.class);
    }

    @Override
    public PerformanceSheetPrefillRespVO getPrefillData(Long employeeId, String periodMonth) {
        String normalizedMonth = normalizePeriodMonth(periodMonth);
        AdminUserRespDTO employee = validateActiveUser(employeeId);
        AdminUserRespDTO manager = loadDirectManager(employee.getId());
        validateManagerOnly(manager.getId());

        PerformanceSheetPrefillRespVO respVO = new PerformanceSheetPrefillRespVO();
        PerformanceSheetDO previousSheet = performanceSheetMapper.selectLatestHistoryWithFile(employeeId, normalizedMonth);
        if (previousSheet == null) {
            return respVO;
        }

        try {
            Map<String, String> cellValues = readPrefillCellValues(previousSheet);
            if (cellValues.isEmpty()) {
                return respVO;
            }
            respVO.setSourceSheetId(previousSheet.getId());
            respVO.setSourcePeriodMonth(previousSheet.getPeriodMonth());
            respVO.setCellValues(cellValues);
        } catch (Exception ex) {
            log.warn("读取上一份绩效表预填充数据失败，sourceSheetId={}, employeeId={}, periodMonth={}",
                    previousSheet.getId(), employeeId, normalizedMonth, ex);
        }
        return respVO;
    }

    @Override
    public PerformanceMonthlyResultRespVO getMonthlyResult(Long employeeId, String periodMonth) {
        String normalizedMonth = normalizePeriodMonth(periodMonth);
        PerformanceSheetDO sheet = performanceSheetMapper.selectByEmployeeIdAndPeriodMonth(employeeId, normalizedMonth);
        PerformanceMonthlyResultRespVO respVO = new PerformanceMonthlyResultRespVO();
        respVO.setEmployeeId(employeeId);
        respVO.setPeriodMonth(normalizedMonth);
        if (sheet == null) {
            return respVO;
        }
        validateReadable(sheet);
        respVO.setSheetId(sheet.getId());
        respVO.setActualScoreTotal(sheet.getActualScoreTotal());
        respVO.setBaseScoreTotal(sheet.getBaseScoreTotal());
        respVO.setExtraScoreTotal(sheet.getExtraScoreTotal());
        respVO.setStatusCode(sheet.getStatusCode());
        return respVO;
    }

    @Override
    public List<PerformanceSheetStatusDictRespVO> getStatusDict() {
        return objectStatusModelMapper.selectListByObjectTypeEnabled(PerformanceConstants.STATUS_OBJECT_TYPE)
                .stream()
                .map(this::buildStatusDictRespVO)
                .toList();
    }

    @Override
    public List<PerformanceSheetStatusTransitionRespVO> getStatusTransitions() {
        List<String> statusCodes = objectStatusModelMapper
                .selectListByObjectTypeEnabled(PerformanceConstants.STATUS_OBJECT_TYPE)
                .stream()
                .map(ObjectStatusModelDO::getStatusCode)
                .toList();
        if (statusCodes.isEmpty()) {
            return List.of();
        }
        return objectStatusTransitionMapper
                .selectListByObjectTypeAndFromStatuses(PerformanceConstants.STATUS_OBJECT_TYPE, statusCodes)
                .stream()
                .map(this::buildStatusTransitionRespVO)
                .toList();
    }

    private void processManagerStatusAction(Long id, String actionType) {
        PerformanceSheetDO current = validateSheetExists(id);
        validateManagerOnly(current.getManagerId());
        ObjectStatusTransitionDO transition = validateTransition(current.getStatusCode(), actionType, null);
        if (current.getFileId() == null) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_FILE_REQUIRED);
        }
        PerformanceSheetDO update = new PerformanceSheetDO();
        update.setStatusCode(transition.getToStatusCode());
        update.setSentTime(LocalDateTime.now());
        int updateCount = performanceSheetMapper.updateStatusByIdAndStatus(update, id, current.getStatusCode());
        if (updateCount != 1) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_CONCURRENT_MODIFIED);
        }
        PerformanceSheetDO after = merge(current, update);
        writeStatusLog(after, actionType, current.getStatusCode(), transition.getToStatusCode(), null);
        publishPerformanceNotice(List.of(after.getEmployeeId()), NotifyTemplateCodeConstants.PERFORMANCE_SENT, after);
    }

    private PerformanceSheetDO processEmployeeResponse(Long id, String actionType, String reason) {
        PerformanceSheetDO current = validateSheetExists(id);
        validateEmployeeOnly(current.getEmployeeId());
        PerformanceEmployeeResponseContext responseContext = resolvePerformanceEmployeeResponseContext(current);
        ObjectStatusTransitionDO transition = validateTransition(current.getStatusCode(), actionType, reason);
        PerformanceSheetDO update = new PerformanceSheetDO();
        update.setStatusCode(transition.getToStatusCode());
        if (PerformanceConstants.STATUS_CONFIRMED.equals(transition.getToStatusCode())) {
            update.setConfirmedTime(LocalDateTime.now());
            if (responseContext.signOffFlow()) {
                update.setSignOffStatus(SignOffConstants.STATUS_EMPLOYEE_CONFIRMED);
            }
        } else if (PerformanceConstants.STATUS_REJECTED.equals(transition.getToStatusCode())) {
            update.setRejectedTime(LocalDateTime.now());
            update.setLastStatusReason(reason);
            if (responseContext.signOffFlow()) {
                update.setSignOffStatus(SignOffConstants.STATUS_EMPLOYEE_REJECTED);
            }
        }
        int updateCount = performanceSheetMapper.updateByIdAndStatusAndSignOffStatus(update, id, current.getStatusCode(),
                current.getSignOffStatus());
        if (updateCount != 1) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_CONCURRENT_MODIFIED);
        }
        if (responseContext.signOffFlow()) {
            updatePerformanceSignOffAfterEmployeeAction(current, responseContext.signOff(), actionType, reason);
        }
        PerformanceSheetDO after = merge(current, update);
        PerformanceSheetStatusLogDO statusLog = writeStatusLog(after, actionType, current.getStatusCode(),
                transition.getToStatusCode(), reason);
        writeResponseRecord(after, statusLog, actionType, current.getStatusCode(), transition.getToStatusCode(), reason);
        if (PerformanceConstants.ACTION_CONFIRM.equals(actionType)) {
            workReportCommonService.syncPerformanceResult(after.getEmployeeId(), after.getPeriodMonth(),
                    formatScoreForMonthlyResult(after.getActualScoreTotal()));
        }
        return after;
    }

    private PerformanceSheetStatusLogDO writeStatusLog(PerformanceSheetDO sheet, String actionType,
                                                       String fromStatus, String toStatus, String reason) {
        PerformanceSheetStatusLogDO statusLog = new PerformanceSheetStatusLogDO();
        statusLog.setSheetId(sheet.getId());
        statusLog.setActionType(actionType);
        statusLog.setFromStatus(fromStatus);
        statusLog.setToStatus(toStatus);
        statusLog.setReason(defaultText(reason));
        statusLog.setOperatorUserId(SecurityFrameworkUtils.getLoginUserId());
        statusLog.setOperatorName(defaultText(SecurityFrameworkUtils.getLoginUserNickname()));
        statusLog.setPeriodMonthSnapshot(sheet.getPeriodMonth());
        statusLog.setEmployeeNameSnapshot(sheet.getEmployeeName());
        statusLog.setRemark(buildRemark(sheet));
        performanceSheetStatusLogMapper.insert(statusLog);
        return statusLog;
    }

    private void writeResponseRecord(PerformanceSheetDO sheet, PerformanceSheetStatusLogDO statusLog,
                                     String actionType, String fromStatus, String toStatus, String opinion) {
        PerformanceSheetResponseRecordDO record = new PerformanceSheetResponseRecordDO();
        record.setSheetId(sheet.getId());
        record.setStatusLogId(statusLog.getId());
        record.setRoundNo(performanceSheetResponseRecordMapper.countBySheetId(sheet.getId()) + 1);
        record.setActionType(actionType);
        record.setFromStatus(fromStatus);
        record.setToStatus(toStatus);
        record.setOpinion(defaultText(opinion));
        record.setResponderUserId(SecurityFrameworkUtils.getLoginUserId());
        record.setResponderName(defaultText(SecurityFrameworkUtils.getLoginUserNickname()));
        performanceSheetResponseRecordMapper.insert(record);
    }

    private PerformanceSheetDO validateSheetExists(Long id) {
        PerformanceSheetDO sheet = id == null ? null : performanceSheetMapper.selectById(id);
        if (sheet == null) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_NOT_EXISTS);
        }
        return sheet;
    }

    private PerformanceSheetDO validateReadableSheet(Long id) {
        PerformanceSheetDO sheet = validateSheetExists(id);
        validateReadable(sheet);
        return sheet;
    }

    private void validateReadable(PerformanceSheetDO sheet) {
        Long loginUserId = SecurityFrameworkUtils.getLoginUserId();
        if (!Objects.equals(loginUserId, sheet.getEmployeeId())
                && !Objects.equals(loginUserId, sheet.getManagerId())
                && !teamDashboardAccessService.canReadSubordinateUser(
                        sheet.getEmployeeId(), PerformanceConstants.PERMISSION_TEAM_DASHBOARD)) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_READ_FORBIDDEN);
        }
    }

    private void validateEditable(PerformanceSheetDO sheet) {
        ObjectStatusModelDO statusModel = objectStatusModelMapper.selectByObjectTypeAndStatusCodeEnabled(
                PerformanceConstants.STATUS_OBJECT_TYPE, sheet.getStatusCode());
        if (statusModel == null) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_MODEL_NOT_EXISTS_OR_DISABLED);
        }
        if (!Boolean.TRUE.equals(statusModel.getAllowEdit())) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_NOT_ALLOW_EDIT);
        }
    }

    private void validateManagerOnly(Long managerId) {
        if (!Objects.equals(SecurityFrameworkUtils.getLoginUserId(), managerId)) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_MANAGER_ONLY);
        }
    }

    private void validateEmployeeOnly(Long employeeId) {
        if (!Objects.equals(SecurityFrameworkUtils.getLoginUserId(), employeeId)) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_EMPLOYEE_ONLY);
        }
    }

    private AdminUserRespDTO validateActiveUser(Long userId) {
        CommonResult<AdminUserRespDTO> result = adminUserApi.getUser(userId);
        AdminUserRespDTO user = result == null ? null : result.getCheckedData();
        if (user == null || user.getId() == null || !Objects.equals(user.getStatus(), 0)) {
            throw exception(ErrorCodeConstants.PERFORMANCE_EMPLOYEE_INVALID);
        }
        return user;
    }

    private AdminUserRespDTO loadDirectManager(Long employeeId) {
        CommonResult<AdminUserRespDTO> result = userManagementRelationApi.getDirectManager(employeeId);
        AdminUserRespDTO manager = result == null ? null : result.getCheckedData();
        if (manager == null || manager.getId() == null || !Objects.equals(manager.getStatus(), 0)) {
            throw exception(ErrorCodeConstants.PERFORMANCE_DIRECT_MANAGER_NOT_EXISTS);
        }
        return manager;
    }

    private DeptRespDTO validateEmployeeDept(Long deptId) {
        if (deptId == null) {
            throw exception(ErrorCodeConstants.PERFORMANCE_EMPLOYEE_DEPT_INVALID);
        }
        CommonResult<DeptRespDTO> result = deptApi.getDept(deptId);
        DeptRespDTO dept = result == null ? null : result.getCheckedData();
        if (dept == null || dept.getId() == null
                || !StringUtils.hasText(dept.getName())
                || !StringUtils.hasText(dept.getOrgType())) {
            throw exception(ErrorCodeConstants.PERFORMANCE_EMPLOYEE_DEPT_INVALID);
        }
        return dept;
    }

    private PerformanceSheetRespVO toRespVO(PerformanceSheetDO sheet) {
        PerformanceSheetRespVO respVO = BeanUtils.toBean(sheet, PerformanceSheetRespVO.class);
        respVO.setStatusName(statusName(sheet.getStatusCode()));
        String signOffStatus = StringUtils.hasText(sheet.getSignOffStatus())
                ? sheet.getSignOffStatus()
                : SignOffConstants.STATUS_NOT_STARTED;
        respVO.setSignOffStatus(signOffStatus);
        respVO.setSignOffStatusName(employeeSignOffStatusService.statusName(signOffStatus));
        return respVO;
    }

    private PerformanceDownloadFile buildMergedWorkbookDownload(List<PerformanceSheetDO> sheets) {
        try {
            return new PerformanceDownloadFile(buildMergedWorkbookDownloadName(sheets), EXCEL_CONTENT_TYPE,
                    buildMergedWorkbook(sheets));
        } catch (Exception ex) {
            throw new IllegalStateException("导出绩效汇总 Excel 失败", ex);
        }
    }

    private byte[] buildMergedWorkbook(List<PerformanceSheetDO> sheets) {
        try (XSSFWorkbook targetWorkbook = new XSSFWorkbook();
             ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            if (sheets == null || sheets.isEmpty()) {
                Sheet emptySheet = targetWorkbook.createSheet("绩效表汇总");
                Row row = emptySheet.createRow(0);
                row.createCell(0).setCellValue("暂无绩效数据");
            } else {
                Set<String> usedSheetNames = new HashSet<>();
                Map<Long, PerformanceTemplateDO> templateCache = new HashMap<>();
                Map<Long, byte[]> templateContentCache = new HashMap<>();
                for (PerformanceSheetDO performanceSheet : sheets) {
                    String targetSheetName = sanitizeSheetName(buildMergedSheetName(performanceSheet), usedSheetNames);
                    Sheet targetSheet = targetWorkbook.createSheet(targetSheetName);
                    try {
                        PerformanceTemplateDO template = resolveTemplateForMergedExport(performanceSheet, templateCache);
                        copyTemplateSheetToTarget(targetWorkbook, targetSheet, template, templateContentCache);
                    } catch (Exception ex) {
                        log.warn("复制绩效汇总模板失败，改为空白页继续导出。sheetId={}, employeeId={}, periodMonth={}, sourceFileId={}, sourceFileName={}, reason={}",
                                performanceSheet.getId(), performanceSheet.getEmployeeId(), performanceSheet.getPeriodMonth(),
                                performanceSheet.getFileId(), performanceSheet.getFileName(), ex.getMessage(), ex);
                        writeFallbackSheetHeader(targetSheet, performanceSheet);
                    }
                    fillAssessedEmployeeName(targetSheet, performanceSheet.getEmployeeName());
                    writeMergedExportCellValues(targetSheet, readMergedExportCellValuesSafely(performanceSheet));
                }
            }
            targetWorkbook.setForceFormulaRecalculation(true);
            targetWorkbook.write(outputStream);
            return outputStream.toByteArray();
        } catch (Exception ex) {
            throw new IllegalStateException("生成绩效汇总 Excel 失败", ex);
        }
    }

    private String buildMergedWorkbookDownloadName(List<PerformanceSheetDO> sheets) {
        if (sheets == null || sheets.isEmpty()) {
            return "绩效表汇总_" + LocalDate.now() + ".xlsx";
        }
        Set<String> periodMonths = new LinkedHashSet<>();
        for (PerformanceSheetDO sheet : sheets) {
            if (sheet != null && StringUtils.hasText(sheet.getPeriodMonth())) {
                periodMonths.add(sheet.getPeriodMonth().trim());
            }
        }
        if (periodMonths.size() == 1) {
            return "绩效表汇总_" + periodMonths.iterator().next() + "月.xlsx";
        }
        return "绩效表汇总_" + LocalDate.now() + ".xlsx";
    }

    private PerformanceTemplateDO resolveTemplateForMergedExport(PerformanceSheetDO performanceSheet,
                                                                 Map<Long, PerformanceTemplateDO> templateCache) {
        PerformanceTemplateDO template = null;
        Long templateId = performanceSheet.getTemplateId();
        if (templateId != null) {
            template = templateCache.get(templateId);
            if (!templateCache.containsKey(templateId)) {
                template = performanceTemplateMapper.selectById(templateId);
                templateCache.put(templateId, template);
            }
        }
        if (template == null) {
            template = templateCache.get(CURRENT_TEMPLATE_CACHE_KEY);
            if (!templateCache.containsKey(CURRENT_TEMPLATE_CACHE_KEY)) {
                template = performanceTemplateMapper.selectCurrent();
                templateCache.put(CURRENT_TEMPLATE_CACHE_KEY, template);
            }
            if (template != null && templateId != null) {
                log.warn("绩效汇总导出未找到原模板，已回退当前启用模板。sheetId={}, templateId={}",
                        performanceSheet.getId(), templateId);
            }
        }
        if (template == null) {
            throw exception(ErrorCodeConstants.PERFORMANCE_TEMPLATE_CURRENT_NOT_EXISTS);
        }
        if (template.getFileId() == null) {
            throw exception(ErrorCodeConstants.PERFORMANCE_FILE_NOT_EXISTS);
        }
        return template;
    }

    private void copyTemplateSheetToTarget(Workbook targetWorkbook, Sheet targetSheet, PerformanceTemplateDO template,
                                           Map<Long, byte[]> templateContentCache) throws Exception {
        byte[] templateContent = templateContentCache.get(template.getId());
        if (templateContent == null) {
            templateContent = readTemplateFileContent(template);
            templateContentCache.put(template.getId(), templateContent);
        }
        try (ByteArrayInputStream inputStream = new ByteArrayInputStream(templateContent);
             Workbook templateWorkbook = WorkbookFactory.create(inputStream)) {
            if (templateWorkbook.getNumberOfSheets() <= 0) {
                return;
            }
            copySheet(templateWorkbook, templateWorkbook.getSheetAt(0), targetWorkbook, targetSheet);
        }
    }

    private void writeFallbackSheetHeader(Sheet targetSheet, PerformanceSheetDO performanceSheet) {
        Row firstRow = targetSheet.getRow(0);
        if (firstRow == null) {
            firstRow = targetSheet.createRow(0);
        }
        firstRow.createCell(0).setCellValue("被考核人：" + defaultText(performanceSheet.getEmployeeName()));
        Row secondRow = targetSheet.getRow(1);
        if (secondRow == null) {
            secondRow = targetSheet.createRow(1);
        }
        secondRow.createCell(0).setCellValue("绩效月份：" + defaultText(performanceSheet.getPeriodMonth()));
        Row fourthRow = targetSheet.getRow(3);
        if (fourthRow == null) {
            fourthRow = targetSheet.createRow(3);
        }
        fourthRow.createCell(0).setCellValue("说明：模板复制失败，本页仅保留汇总字段。");
    }

    private void fillAssessedEmployeeName(Sheet targetSheet, String employeeName) {
        if (!StringUtils.hasText(employeeName)) {
            return;
        }
        int firstRowNum = targetSheet.getFirstRowNum();
        int lastRowNum = targetSheet.getLastRowNum();
        for (int rowIndex = firstRowNum; rowIndex <= lastRowNum; rowIndex++) {
            Row row = targetSheet.getRow(rowIndex);
            if (row == null) {
                continue;
            }
            short firstCellNum = row.getFirstCellNum();
            short lastCellNum = row.getLastCellNum();
            if (firstCellNum < 0 || lastCellNum < 0) {
                continue;
            }
            for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) {
                Cell cell = row.getCell(cellIndex);
                if (cell == null || cell.getCellType() != org.apache.poi.ss.usermodel.CellType.STRING) {
                    continue;
                }
                String cellValue = cell.getStringCellValue();
                if (!StringUtils.hasText(cellValue)) {
                    continue;
                }
                Matcher matcher = ASSESSED_EMPLOYEE_TEXT_PATTERN.matcher(cellValue);
                if (!matcher.find()) {
                    continue;
                }
                cell.setCellValue(matcher.replaceFirst("$1" + Matcher.quoteReplacement(defaultText(employeeName))));
            }
        }
    }

    private byte[] readTemplateFileContent(PerformanceTemplateDO template) {
        CommonResult<byte[]> result = fileApi.getFileContent(template.getFileId());
        byte[] content = result == null ? null : result.getCheckedData();
        if (content == null || content.length == 0) {
            throw exception(ErrorCodeConstants.PERFORMANCE_FILE_NOT_EXISTS);
        }
        return content;
    }

    private Map<String, String> readMergedExportCellValues(PerformanceSheetDO performanceSheet) throws Exception {
        return readSelectedCellValues(readFileContent(performanceSheet), PerformanceExcelCellConstants.MERGED_EXPORT_CELL_ADDRESSES);
    }

    private Map<String, String> readMergedExportCellValuesSafely(PerformanceSheetDO performanceSheet) {
        try {
            return readMergedExportCellValues(performanceSheet);
        } catch (Exception ex) {
            log.warn("读取绩效汇总导出单元格失败，已跳过该绩效表的数据回填。sheetId={}, employeeId={}, periodMonth={}, sourceFileId={}, sourceFileName={}, reason={}",
                    performanceSheet.getId(), performanceSheet.getEmployeeId(), performanceSheet.getPeriodMonth(),
                    performanceSheet.getFileId(), performanceSheet.getFileName(), ex.getMessage(), ex);
            return Map.of();
        }
    }

    private void writeMergedExportCellValues(Sheet targetSheet, Map<String, String> cellValues) {
        if (cellValues == null || cellValues.isEmpty()) {
            return;
        }
        for (Map.Entry<String, String> entry : cellValues.entrySet()) {
            String cellAddress = entry.getKey();
            String cellValue = entry.getValue();
            if (!StringUtils.hasText(cellAddress) || cellValue == null) {
                continue;
            }
            CellReference reference = new CellReference(cellAddress);
            Row row = targetSheet.getRow(reference.getRow());
            if (row == null) {
                row = targetSheet.createRow(reference.getRow());
            }
            Cell cell = row.getCell(reference.getCol());
            if (cell == null) {
                cell = row.createCell(reference.getCol());
            }
            if (cell.getCellType() == org.apache.poi.ss.usermodel.CellType.FORMULA) {
                cell.removeFormula();
            }
            BigDecimal scoreValue = parseScoreCellValue(cellAddress, cellValue);
            if (scoreValue != null) {
                cell.setCellValue(scoreValue.doubleValue());
            } else {
                cell.setCellValue(cellValue);
            }
        }
    }

    private BigDecimal parseScoreCellValue(String cellAddress, String cellValue) {
        if (!isScoreCellAddress(cellAddress) || !StringUtils.hasText(cellValue)) {
            return null;
        }
        String normalized = cellValue.trim().replace(",", "");
        try {
            return new BigDecimal(normalized);
        } catch (NumberFormatException ex) {
            return null;
        }
    }

    private boolean isScoreCellAddress(String cellAddress) {
        return PerformanceExcelCellConstants.ACTUAL_SCORE_CELLS.contains(cellAddress)
                || PerformanceExcelCellConstants.BASE_SCORE_CELLS.contains(cellAddress)
                || PerformanceExcelCellConstants.EXTRA_SCORE_CELLS.contains(cellAddress);
    }

    private String buildMergedSheetName(PerformanceSheetDO sheet) {
        return defaultText(sheet.getPeriodMonth()) + "_" + defaultText(sheet.getEmployeeName());
    }

    private String sanitizeSheetName(String rawName, Set<String> usedNames) {
        String name = StringUtils.hasText(rawName) ? rawName.trim() : "Sheet";
        name = name.replace("\\", "_")
                .replace("/", "_")
                .replace("?", "_")
                .replace("*", "_")
                .replace("[", "_")
                .replace("]", "_")
                .replace(":", "_");
        if (name.length() > 31) {
            name = name.substring(0, 31);
        }
        if (!StringUtils.hasText(name)) {
            name = "Sheet";
        }
        if (usedNames.add(name)) {
            return name;
        }
        int index = 2;
        while (true) {
            String suffix = "_" + index;
            int maxLength = 31 - suffix.length();
            String base = name.length() > maxLength ? name.substring(0, maxLength) : name;
            String candidate = base + suffix;
            if (usedNames.add(candidate)) {
                return candidate;
            }
            index++;
        }
    }

    private void copySheet(Workbook sourceWorkbook, Sheet sourceSheet, Workbook targetWorkbook, Sheet targetSheet) {
        if (!(sourceSheet instanceof XSSFSheet sourceXssfSheet) || !(targetSheet instanceof XSSFSheet targetXssfSheet)) {
            throw new IllegalStateException("绩效汇总导出仅支持 .xlsx 文件");
        }
        copySheetSettings(sourceSheet, targetSheet);
        CellCopyPolicy copyPolicy = new CellCopyPolicy();
        copyPolicy.setCopyCellValue(true);
        copyPolicy.setCopyCellStyle(true);
        copyPolicy.setCopyCellFormula(true);
        copyPolicy.setCopyHyperlink(true);
        copyPolicy.setCopyRowHeight(true);
        copyPolicy.setCopyMergedRegions(false);
        CellCopyContext cellCopyContext = new CellCopyContext();
        int firstRowNum = sourceXssfSheet.getFirstRowNum();
        int lastRowNum = sourceXssfSheet.getLastRowNum();
        int rangeStart = -1;
        List<Row> rangeRows = new ArrayList<>();
        for (int rowIndex = firstRowNum; rowIndex <= lastRowNum; rowIndex++) {
            Row sourceRow = sourceXssfSheet.getRow(rowIndex);
            if (sourceRow == null) {
                if (rangeStart >= 0 && !rangeRows.isEmpty()) {
                    targetXssfSheet.copyRows(rangeRows, rangeStart, copyPolicy, cellCopyContext);
                    rangeStart = -1;
                    rangeRows = new ArrayList<>();
                }
                continue;
            }
            if (rangeStart < 0) {
                rangeStart = rowIndex;
            }
            rangeRows.add(sourceRow);
        }
        if (rangeStart >= 0 && !rangeRows.isEmpty()) {
            targetXssfSheet.copyRows(rangeRows, rangeStart, copyPolicy, cellCopyContext);
        }
        syncRowStyles(sourceWorkbook, targetWorkbook, sourceSheet, targetSheet, new SheetCopyContext());
        copyMergedRegions(sourceSheet, targetSheet);
        copyColumnWidths(sourceSheet, targetSheet);
        copySheetComments(sourceSheet, targetSheet);
        targetSheet.setForceFormulaRecalculation(true);
    }

    private void copySheetComments(Sheet sourceSheet, Sheet targetSheet) {
        Map<CellAddress, ? extends Comment> comments = sourceSheet.getCellComments();
        if (comments == null || comments.isEmpty()) {
            return;
        }
        for (Map.Entry<CellAddress, ? extends Comment> entry : comments.entrySet()) {
            CellAddress cellAddress = entry.getKey();
            Row targetRow = targetSheet.getRow(cellAddress.getRow());
            if (targetRow == null) {
                continue;
            }
            Cell targetCell = targetRow.getCell(cellAddress.getColumn());
            if (targetCell == null) {
                continue;
            }
            copyCellComment(entry.getValue(), cellAddress.getRow(), cellAddress.getColumn(), targetCell);
        }
    }

    private void syncRowStyles(Workbook sourceWorkbook, Workbook targetWorkbook, Sheet sourceSheet, Sheet targetSheet,
                               SheetCopyContext copyContext) {
        int firstRowNum = sourceSheet.getFirstRowNum();
        int lastRowNum = sourceSheet.getLastRowNum();
        for (int rowIndex = firstRowNum; rowIndex <= lastRowNum; rowIndex++) {
            Row sourceRow = sourceSheet.getRow(rowIndex);
            Row targetRow = targetSheet.getRow(rowIndex);
            if (sourceRow == null || targetRow == null) {
                continue;
            }
            CellStyle sourceRowStyle = sourceRow.getRowStyle();
            if (sourceRowStyle != null) {
                targetRow.setRowStyle(cloneStyle(sourceWorkbook, targetWorkbook, sourceRowStyle, copyContext));
            }
        }
    }

    private CellStyle cloneStyle(Workbook sourceWorkbook, Workbook targetWorkbook, CellStyle sourceStyle,
                                 SheetCopyContext copyContext) {
        CellStyle cachedStyle = copyContext.styleCache.get(sourceStyle);
        if (cachedStyle != null) {
            return cachedStyle;
        }
        CellStyle newStyle = targetWorkbook.createCellStyle();
        copyCellStyleProperties(sourceWorkbook, targetWorkbook, sourceStyle, newStyle, copyContext);
        copyContext.styleCache.put(sourceStyle, newStyle);
        return newStyle;
    }

    private void copyCellStyleProperties(Workbook sourceWorkbook, Workbook targetWorkbook, CellStyle sourceStyle,
                                         CellStyle targetStyle, SheetCopyContext copyContext) {
        boolean xssfCloned = false;
        if (sourceStyle instanceof XSSFCellStyle sourceXssfStyle && targetStyle instanceof XSSFCellStyle targetXssfStyle) {
            try {
                targetXssfStyle.cloneStyleFrom(sourceXssfStyle);
                xssfCloned = true;
            } catch (RuntimeException ignored) {
                // fallback to generic property copy
            }
        }
        if (!xssfCloned) {
            targetStyle.setAlignment(sourceStyle.getAlignment());
            targetStyle.setVerticalAlignment(sourceStyle.getVerticalAlignment());
            targetStyle.setBorderBottom(sourceStyle.getBorderBottom());
            targetStyle.setBorderLeft(sourceStyle.getBorderLeft());
            targetStyle.setBorderRight(sourceStyle.getBorderRight());
            targetStyle.setBorderTop(sourceStyle.getBorderTop());
            targetStyle.setBottomBorderColor(sourceStyle.getBottomBorderColor());
            targetStyle.setLeftBorderColor(sourceStyle.getLeftBorderColor());
            targetStyle.setRightBorderColor(sourceStyle.getRightBorderColor());
            targetStyle.setTopBorderColor(sourceStyle.getTopBorderColor());
            targetStyle.setFillPattern(sourceStyle.getFillPattern());
            targetStyle.setFillForegroundColor(sourceStyle.getFillForegroundColor());
            targetStyle.setFillBackgroundColor(sourceStyle.getFillBackgroundColor());
            targetStyle.setHidden(sourceStyle.getHidden());
            targetStyle.setIndention(sourceStyle.getIndention());
            targetStyle.setLocked(sourceStyle.getLocked());
            targetStyle.setRotation(sourceStyle.getRotation());
            targetStyle.setShrinkToFit(sourceStyle.getShrinkToFit());
            targetStyle.setWrapText(sourceStyle.getWrapText());
            targetStyle.setQuotePrefixed(sourceStyle.getQuotePrefixed());
        }

        String dataFormatString = sourceStyle.getDataFormatString();
        if (StringUtils.hasText(dataFormatString)) {
            Short targetDataFormat = copyContext.dataFormatCache.get(dataFormatString);
            if (targetDataFormat == null) {
                DataFormat dataFormat = targetWorkbook.createDataFormat();
                targetDataFormat = dataFormat.getFormat(dataFormatString);
                copyContext.dataFormatCache.put(dataFormatString, targetDataFormat);
            }
            targetStyle.setDataFormat(targetDataFormat);
        }

        if (sourceStyle instanceof XSSFCellStyle sourceXssfStyle && targetStyle instanceof XSSFCellStyle targetXssfStyle) {
            applyXssfStyleColors(sourceXssfStyle, targetXssfStyle);
        }

        Font sourceFont = sourceWorkbook.getFontAt(sourceStyle.getFontIndex());
        if (sourceFont != null) {
            Font targetFont = copyContext.fontCache.get(sourceFont);
            if (targetFont == null) {
                targetFont = cloneFont(targetWorkbook, sourceFont);
                copyContext.fontCache.put(sourceFont, targetFont);
            }
            targetStyle.setFont(targetFont);
        }
    }

    private Font cloneFont(Workbook targetWorkbook, Font sourceFont) {
        Font targetFont = targetWorkbook.createFont();
        targetFont.setBold(sourceFont.getBold());
        targetFont.setCharSet(sourceFont.getCharSet());
        targetFont.setColor(sourceFont.getColor());
        targetFont.setFontHeight(sourceFont.getFontHeight());
        targetFont.setFontHeightInPoints(sourceFont.getFontHeightInPoints());
        targetFont.setFontName(sourceFont.getFontName());
        targetFont.setItalic(sourceFont.getItalic());
        targetFont.setStrikeout(sourceFont.getStrikeout());
        targetFont.setTypeOffset(sourceFont.getTypeOffset());
        targetFont.setUnderline(sourceFont.getUnderline());
        if (sourceFont instanceof XSSFFont sourceXssfFont && targetFont instanceof XSSFFont targetXssfFont) {
            targetXssfFont.setFamily(sourceXssfFont.getFamily());
            if (sourceXssfFont.getScheme() != null) {
                targetXssfFont.setScheme(sourceXssfFont.getScheme());
            }
            targetXssfFont.setThemeColor(sourceXssfFont.getThemeColor());
            XSSFColor xssfColor = sourceXssfFont.getXSSFColor();
            if (xssfColor != null) {
                targetXssfFont.setColor(xssfColor);
            }
        }
        return targetFont;
    }

    private void applyXssfStyleColors(XSSFCellStyle sourceStyle, XSSFCellStyle targetStyle) {
        XSSFColor fillForegroundColor = sourceStyle.getFillForegroundColorColor();
        if (fillForegroundColor != null) {
            targetStyle.setFillForegroundColor(fillForegroundColor);
        }
        XSSFColor fillBackgroundColor = sourceStyle.getFillBackgroundColorColor();
        if (fillBackgroundColor != null) {
            targetStyle.setFillBackgroundColor(fillBackgroundColor);
        }
        XSSFColor topBorderColor = sourceStyle.getTopBorderXSSFColor();
        if (topBorderColor != null) {
            targetStyle.setTopBorderColor(topBorderColor);
        }
        XSSFColor bottomBorderColor = sourceStyle.getBottomBorderXSSFColor();
        if (bottomBorderColor != null) {
            targetStyle.setBottomBorderColor(bottomBorderColor);
        }
        XSSFColor leftBorderColor = sourceStyle.getLeftBorderXSSFColor();
        if (leftBorderColor != null) {
            targetStyle.setLeftBorderColor(leftBorderColor);
        }
        XSSFColor rightBorderColor = sourceStyle.getRightBorderXSSFColor();
        if (rightBorderColor != null) {
            targetStyle.setRightBorderColor(rightBorderColor);
        }
    }

    private void copyCellComment(Comment sourceComment, int rowIndex, int columnIndex, Cell targetCell) {
        if (sourceComment == null) {
            return;
        }
        Sheet targetSheet = targetCell.getSheet();
        Drawing<?> drawing = targetSheet.getDrawingPatriarch();
        if (drawing == null) {
            drawing = targetSheet.createDrawingPatriarch();
        }
        CreationHelper creationHelper = targetSheet.getWorkbook().getCreationHelper();
        ClientAnchor anchor = creationHelper.createClientAnchor();
        anchor.setCol1(columnIndex);
        anchor.setCol2(columnIndex + 3);
        anchor.setRow1(rowIndex);
        anchor.setRow2(rowIndex + 4);

        Comment targetComment = drawing.createCellComment(anchor);
        targetComment.setAuthor(sourceComment.getAuthor());
        targetComment.setVisible(sourceComment.isVisible());
        targetComment.setString(creationHelper.createRichTextString(sourceComment.getString().getString()));
        targetCell.setCellComment(targetComment);
    }

    private void copySheetSettings(Sheet sourceSheet, Sheet targetSheet) {
        targetSheet.setDisplayGridlines(sourceSheet.isDisplayGridlines());
        targetSheet.setPrintGridlines(sourceSheet.isPrintGridlines());
        targetSheet.setDefaultRowHeight(sourceSheet.getDefaultRowHeight());
        targetSheet.setDefaultColumnWidth(sourceSheet.getDefaultColumnWidth());
        targetSheet.setHorizontallyCenter(sourceSheet.getHorizontallyCenter());
        targetSheet.setVerticallyCenter(sourceSheet.getVerticallyCenter());
        targetSheet.setMargin(PageMargin.LEFT, sourceSheet.getMargin(PageMargin.LEFT));
        targetSheet.setMargin(PageMargin.RIGHT, sourceSheet.getMargin(PageMargin.RIGHT));
        targetSheet.setMargin(PageMargin.TOP, sourceSheet.getMargin(PageMargin.TOP));
        targetSheet.setMargin(PageMargin.BOTTOM, sourceSheet.getMargin(PageMargin.BOTTOM));
        targetSheet.setZoom(40);
        PaneInformation paneInformation = sourceSheet.getPaneInformation();
        if (paneInformation != null && paneInformation.isFreezePane()) {
            targetSheet.createFreezePane(
                    paneInformation.getVerticalSplitPosition(),
                    paneInformation.getHorizontalSplitPosition(),
                    paneInformation.getVerticalSplitLeftColumn(),
                    paneInformation.getHorizontalSplitTopRow());
        }
    }

    private void copyColumnWidths(Sheet sourceSheet, Sheet targetSheet) {
        int maxColumnNum = 0;
        int firstRowNum = sourceSheet.getFirstRowNum();
        int lastRowNum = sourceSheet.getLastRowNum();
        for (int rowIndex = firstRowNum; rowIndex <= lastRowNum; rowIndex++) {
            Row row = sourceSheet.getRow(rowIndex);
            if (row != null && row.getLastCellNum() > maxColumnNum) {
                maxColumnNum = row.getLastCellNum();
            }
        }
        for (int columnIndex = 0; columnIndex < maxColumnNum; columnIndex++) {
            targetSheet.setColumnWidth(columnIndex, sourceSheet.getColumnWidth(columnIndex));
            targetSheet.setColumnHidden(columnIndex, sourceSheet.isColumnHidden(columnIndex));
        }
    }

    private void copyMergedRegions(Sheet sourceSheet, Sheet targetSheet) {
        int mergedRegionCount = sourceSheet.getNumMergedRegions();
        for (int i = 0; i < mergedRegionCount; i++) {
            CellRangeAddress region = sourceSheet.getMergedRegion(i);
            targetSheet.addMergedRegion(new CellRangeAddress(
                    region.getFirstRow(), region.getLastRow(), region.getFirstColumn(), region.getLastColumn()));
        }
    }

    private byte[] readFileContent(PerformanceSheetDO sheet) {
        if (sheet.getFileId() == null) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_FILE_REQUIRED);
        }
        CommonResult<byte[]> result = fileApi.getFileContent(sheet.getFileId());
        byte[] content = result == null ? null : result.getCheckedData();
        if (content == null || content.length == 0) {
            throw exception(ErrorCodeConstants.PERFORMANCE_FILE_NOT_EXISTS);
        }
        return content;
    }

    private Map<String, String> readPrefillCellValues(PerformanceSheetDO sheet) throws Exception {
        return readSelectedCellValues(readFileContent(sheet), PerformanceExcelCellConstants.PREFILL_CELL_ADDRESSES);
    }

    private Map<String, String> readSelectedCellValues(byte[] content, List<String> cellAddresses) throws Exception {
        try (ByteArrayInputStream inputStream = new ByteArrayInputStream(content);
             Workbook workbook = WorkbookFactory.create(inputStream)) {
            if (workbook.getNumberOfSheets() <= 0) {
                return Map.of();
            }
            Sheet sourceSheet = workbook.getSheetAt(0);
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            DataFormatter formatter = new DataFormatter();
            return readSelectedCellValues(sourceSheet, evaluator, formatter, cellAddresses);
        }
    }

    private Map<String, String> readSelectedCellValues(Sheet sheet, FormulaEvaluator evaluator,
                                                       DataFormatter formatter, List<String> cellAddresses) {
        Map<String, String> cellValues = new LinkedHashMap<>();
        for (String cellAddress : cellAddresses) {
            String value = readCellDisplayValue(sheet, evaluator, formatter, cellAddress);
            if (value != null) {
                cellValues.put(cellAddress, value);
            }
        }
        return cellValues;
    }

    private String readCellDisplayValue(Sheet sheet,
                                        FormulaEvaluator evaluator,
                                        DataFormatter formatter,
                                        String cellAddress) {
        CellReference reference = new CellReference(cellAddress);
        Row row = sheet.getRow(reference.getRow());
        if (row == null) {
            return null;
        }
        Cell cell = row.getCell(reference.getCol());
        if (cell == null) {
            return null;
        }
        String value = formatter.formatCellValue(cell, evaluator);
        return StringUtils.hasText(value) ? value.trim() : null;
    }

    private void importOneSheet(String periodMonth, String workbookName, Sheet importedSheet,
                                FormulaEvaluator evaluator, DataFormatter formatter,
                                Map<String, List<PerformanceSheetDO>> sheetsByEmployeeName) {
        transactionTemplate.executeWithoutResult(status -> {
            String sheetName = importedSheet.getSheetName();
            String employeeName = parseEmployeeNameFromSheetName(periodMonth, sheetName);
            List<PerformanceSheetDO> matchedSheets = sheetsByEmployeeName.getOrDefault(employeeName, List.of());
            if (matchedSheets.isEmpty()) {
                throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_IMPORT_TARGET_EMPTY);
            }
            if (matchedSheets.size() > 1) {
                throw invalidParamException("存在重名员工，无法唯一匹配：" + employeeName);
            }
            PerformanceSheetDO currentSheet = matchedSheets.get(0);
            Map<String, String> importedValues = readSelectedCellValues(importedSheet, evaluator, formatter,
                    PerformanceExcelCellConstants.MERGED_EXPORT_CELL_ADDRESSES);
            if (importedValues.isEmpty()) {
                throw invalidParamException("未读取到可回写的绩效单元格");
            }
            applyImportedSheetValues(currentSheet, workbookName, sheetName, importedValues);
        });
    }

    private void applyImportedSheetValues(PerformanceSheetDO currentSheet, String workbookName, String sheetName,
                                          Map<String, String> importedValues) {
        byte[] sourceContent = readFileContent(currentSheet);
        try (ByteArrayInputStream inputStream = new ByteArrayInputStream(sourceContent);
             Workbook workbook = WorkbookFactory.create(inputStream);
             ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            if (workbook.getNumberOfSheets() <= 0) {
                throw invalidParamException("原始绩效文件缺少 sheet 页");
            }
            Sheet targetSheet = workbook.getSheetAt(0);
            writeMergedExportCellValues(targetSheet, importedValues);
            workbook.setForceFormulaRecalculation(true);
            FormulaEvaluator targetEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
            targetEvaluator.evaluateAll();
            DataFormatter formatter = new DataFormatter();
            BigDecimal actualScoreTotal = parseScore(readCellDisplayValue(targetSheet, targetEvaluator, formatter,
                    PerformanceExcelCellConstants.ACTUAL_SCORE_TOTAL_CELL));
            BigDecimal baseScoreTotal = parseScore(readCellDisplayValue(targetSheet, targetEvaluator, formatter,
                    PerformanceExcelCellConstants.BASE_SCORE_TOTAL_CELL));
            BigDecimal extraScoreTotal = parseScore(readCellDisplayValue(targetSheet, targetEvaluator, formatter,
                    PerformanceExcelCellConstants.EXTRA_SCORE_TOTAL_CELL));

            workbook.write(outputStream);
            FileContentCreateReqDTO fileCreateReqDTO = new FileContentCreateReqDTO();
            fileCreateReqDTO.setContent(outputStream.toByteArray());
            fileCreateReqDTO.setName(defaultDownloadName(currentSheet));
            fileCreateReqDTO.setDirectory("rdms/performance");
            fileCreateReqDTO.setType(resolveExcelContentType(defaultDownloadName(currentSheet)));
            CommonResult<Long> createFileResult = fileApi.createFileByContent(fileCreateReqDTO);
            Long newFileId = createFileResult == null ? null : createFileResult.getCheckedData();
            if (newFileId == null) {
                throw new IllegalStateException("上传更新后的绩效文件失败");
            }

            PerformanceSheetDO update = new PerformanceSheetDO();
            update.setFileId(newFileId);
            update.setFileName(defaultDownloadName(currentSheet));
            update.setFileVersion(currentSheet.getFileVersion() == null ? 1 : currentSheet.getFileVersion() + 1);
            update.setActualScoreTotal(actualScoreTotal);
            update.setBaseScoreTotal(baseScoreTotal);
            update.setExtraScoreTotal(extraScoreTotal);
            int updateCount = performanceSheetMapper.updateExcelByIdAndVersion(update, currentSheet.getId(),
                    currentSheet.getFileVersion());
            if (updateCount != 1) {
                throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_CONCURRENT_MODIFIED);
            }
            workReportCommonService.syncPerformanceResult(currentSheet.getEmployeeId(), currentSheet.getPeriodMonth(),
                    formatScoreForMonthlyResult(actualScoreTotal));
            log.info("导入绩效 sheet 成功，workbookName={}, sheetName={}, sheetId={}, employeeId={}",
                    workbookName, sheetName, currentSheet.getId(), currentSheet.getEmployeeId());
        } catch (RuntimeException ex) {
            throw ex;
        } catch (Exception ex) {
            throw new IllegalStateException("回写绩效 Excel 失败", ex);
        }
    }

    private List<ImportedWorkbookEntry> readZipWorkbookEntries(byte[] zipBytes) throws IOException {
        List<Charset> charsets = List.of(StandardCharsets.UTF_8, Charset.forName("GBK"));
        Exception lastException = null;
        for (Charset charset : charsets) {
            try {
                return readZipWorkbookEntries(zipBytes, charset);
            } catch (Exception ex) {
                lastException = ex;
            }
        }
        if (lastException instanceof IOException ioException) {
            throw ioException;
        }
        throw new IOException("读取 zip 文件失败", lastException);
    }

    private List<ImportedWorkbookEntry> readZipWorkbookEntries(byte[] zipBytes, Charset charset) throws IOException {
        List<ImportedWorkbookEntry> workbookEntries = new ArrayList<>();
        try (ByteArrayInputStream inputStream = new ByteArrayInputStream(zipBytes);
             ZipInputStream zipInputStream = new ZipInputStream(inputStream, charset)) {
            ZipEntry entry;
            while ((entry = zipInputStream.getNextEntry()) != null) {
                if (entry.isDirectory()) {
                    continue;
                }
                String displayName = extractEntryDisplayName(entry.getName());
                if (!isExcelEntry(displayName)) {
                    continue;
                }
                ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
                zipInputStream.transferTo(outputStream);
                workbookEntries.add(new ImportedWorkbookEntry(displayName, outputStream.toByteArray()));
            }
        }
        return workbookEntries;
    }

    private String extractEntryDisplayName(String entryName) {
        if (!StringUtils.hasText(entryName)) {
            return "";
        }
        String normalized = entryName.replace("\\", "/");
        int index = normalized.lastIndexOf('/');
        return index >= 0 ? normalized.substring(index + 1) : normalized;
    }

    private boolean isExcelEntry(String fileName) {
        if (!StringUtils.hasText(fileName)) {
            return false;
        }
        String lowerCase = fileName.toLowerCase();
        return lowerCase.endsWith(".xlsx") || lowerCase.endsWith(".xlsm") || lowerCase.endsWith(".xls");
    }

    private boolean shouldIgnoreImportedWorkbook(String workbookName) {
        return StringUtils.hasText(workbookName) && workbookName.startsWith("0_绩效表汇总");
    }

    private String parseEmployeeNameFromSheetName(String periodMonth, String sheetName) {
        if (!StringUtils.hasText(sheetName)) {
            throw invalidParamException("sheet 名不能为空");
        }
        String normalizedName = sheetName.trim();
        int splitIndex = normalizedName.indexOf('_');
        if (splitIndex <= 0 || splitIndex >= normalizedName.length() - 1) {
            throw invalidParamException("sheet 名不符合“月份_姓名”规则：" + normalizedName);
        }
        String monthPart = normalizedName.substring(0, splitIndex).trim();
        if (!periodMonth.equals(monthPart)) {
            throw invalidParamException("sheet 月份与导入月份不一致：" + normalizedName);
        }
        String employeeName = normalizedName.substring(splitIndex + 1).trim();
        if (!StringUtils.hasText(employeeName)) {
            throw invalidParamException("sheet 名未包含员工姓名：" + normalizedName);
        }
        return employeeName;
    }

    private PerformanceEmployeeResponseContext resolvePerformanceEmployeeResponseContext(PerformanceSheetDO sheet) {
        if (!PerformanceConstants.STATUS_SENT.equals(sheet.getStatusCode())) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_SIGN_OFF_NOT_ALLOWED);
        }
        if (SignOffConstants.STATUS_PENDING_EMPLOYEE_CONFIRM.equals(sheet.getSignOffStatus())) {
            PerformanceSheetSignOffDO signOff = validatePerformanceSignOffExists(sheet.getId());
            if (!SignOffConstants.STATUS_PENDING_EMPLOYEE_CONFIRM.equals(signOff.getStatusCode())) {
                throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_SIGN_OFF_NOT_ALLOWED);
            }
            return new PerformanceEmployeeResponseContext(signOff, true);
        }
        if (SignOffConstants.STATUS_EMPLOYEE_REJECTED.equals(sheet.getSignOffStatus())) {
            PerformanceSheetSignOffDO signOff = validatePerformanceSignOffExists(sheet.getId());
            if (!SignOffConstants.STATUS_EMPLOYEE_REJECTED.equals(signOff.getStatusCode())) {
                throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_SIGN_OFF_NOT_ALLOWED);
            }
            return new PerformanceEmployeeResponseContext(signOff, false);
        }
        throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_SIGN_OFF_NOT_ALLOWED);
    }

    private PerformanceSheetSignOffDO validatePerformanceSignOffExists(Long sheetId) {
        PerformanceSheetSignOffDO signOff = performanceSheetSignOffMapper.selectByPerformanceSheetId(sheetId);
        if (signOff == null) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_SIGN_OFF_NOT_EXISTS);
        }
        return signOff;
    }

    private void updatePerformanceSignOffAfterEmployeeAction(PerformanceSheetDO current, PerformanceSheetSignOffDO signOff,
                                                             String actionType, String reason) {
        PerformanceSheetSignOffDO signOffUpdate = new PerformanceSheetSignOffDO();
        signOffUpdate.setStatusCode(PerformanceConstants.ACTION_CONFIRM.equals(actionType)
                ? SignOffConstants.STATUS_EMPLOYEE_CONFIRMED
                : SignOffConstants.STATUS_EMPLOYEE_REJECTED);
        signOffUpdate.setConfirmUserId(SecurityFrameworkUtils.getLoginUserId());
        signOffUpdate.setConfirmUserName(defaultText(SecurityFrameworkUtils.getLoginUserNickname()));
        signOffUpdate.setConfirmTime(LocalDateTime.now());
        signOffUpdate.setLastRejectReason(PerformanceConstants.ACTION_REJECT.equals(actionType) ? reason : null);
        int signOffUpdateCount = performanceSheetSignOffMapper.updateByPerformanceSheetIdAndStatus(signOffUpdate,
                current.getId(), signOff.getStatusCode());
        if (signOffUpdateCount != 1) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_CONCURRENT_MODIFIED);
        }
        writePerformanceSignOffRecord(signOff.getId(), current.getId(), signOff.getBatchNo(), actionType,
                signOff.getStatusCode(), signOffUpdate.getStatusCode(), reason);
    }

    private void issuePerformanceSignOff(List<PerformanceSheetDO> sheets, Long batchNo) {
        if (sheets == null || sheets.isEmpty()) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_SIGN_OFF_CANDIDATE_EMPTY);
        }
        LocalDateTime issueTime = LocalDateTime.now();
        Long operatorUserId = SecurityFrameworkUtils.getLoginUserId();
        String operatorName = defaultText(SecurityFrameworkUtils.getLoginUserNickname());
        for (PerformanceSheetDO sheet : sheets) {
            PerformanceSheetDO update = new PerformanceSheetDO();
            update.setSignOffStatus(SignOffConstants.STATUS_PENDING_EMPLOYEE_CONFIRM);
            int updateCount = performanceSheetMapper.updateByIdAndStatusAndSignOffStatus(update, sheet.getId(),
                    sheet.getStatusCode(), sheet.getSignOffStatus());
            if (updateCount != 1) {
                throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_CONCURRENT_MODIFIED);
            }
            PerformanceSheetSignOffDO currentSignOff = performanceSheetSignOffMapper.selectByPerformanceSheetId(sheet.getId());
            if (currentSignOff == null) {
                PerformanceSheetSignOffDO signOff = new PerformanceSheetSignOffDO();
                signOff.setPerformanceSheetId(sheet.getId());
                signOff.setBatchNo(batchNo);
                signOff.setStatusCode(SignOffConstants.STATUS_PENDING_EMPLOYEE_CONFIRM);
                signOff.setIssueUserId(operatorUserId);
                signOff.setIssueUserName(operatorName);
                signOff.setIssueTime(issueTime);
                performanceSheetSignOffMapper.insert(signOff);
                writePerformanceSignOffRecord(signOff.getId(), sheet.getId(), batchNo, SignOffConstants.ACTION_ISSUE,
                        SignOffConstants.STATUS_NOT_STARTED, SignOffConstants.STATUS_PENDING_EMPLOYEE_CONFIRM, null);
            } else {
                employeeSignOffStatusService.validateTransition(currentSignOff.getStatusCode(), SignOffConstants.ACTION_ISSUE, null);
                PerformanceSheetSignOffDO signOffUpdate = new PerformanceSheetSignOffDO();
                signOffUpdate.setStatusCode(SignOffConstants.STATUS_PENDING_EMPLOYEE_CONFIRM);
                signOffUpdate.setBatchNo(batchNo);
                signOffUpdate.setIssueUserId(operatorUserId);
                signOffUpdate.setIssueUserName(operatorName);
                signOffUpdate.setIssueTime(issueTime);
                signOffUpdate.setConfirmUserId(null);
                signOffUpdate.setConfirmUserName(null);
                signOffUpdate.setConfirmTime(null);
                signOffUpdate.setLastRejectReason(null);
                int signOffUpdateCount = performanceSheetSignOffMapper.updateByPerformanceSheetIdAndStatus(signOffUpdate,
                        sheet.getId(), currentSignOff.getStatusCode());
                if (signOffUpdateCount != 1) {
                    throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_CONCURRENT_MODIFIED);
                }
                writePerformanceSignOffRecord(currentSignOff.getId(), sheet.getId(), batchNo,
                        SignOffConstants.ACTION_ISSUE, currentSignOff.getStatusCode(),
                        SignOffConstants.STATUS_PENDING_EMPLOYEE_CONFIRM, null);
            }
        }
    }

    private void writePerformanceSignOffRecord(Long signOffId, Long sheetId, Long batchNo, String actionType,
                                               String fromStatus, String toStatus, String reason) {
        PerformanceSheetSignOffRecordDO record = new PerformanceSheetSignOffRecordDO();
        record.setSignOffId(signOffId);
        record.setPerformanceSheetId(sheetId);
        record.setBatchNo(batchNo);
        record.setActionType(actionType);
        record.setFromSignOffStatus(fromStatus);
        record.setToSignOffStatus(toStatus);
        record.setReason(reason);
        record.setOperatorUserId(SecurityFrameworkUtils.getLoginUserId());
        record.setOperatorName(defaultText(SecurityFrameworkUtils.getLoginUserNickname()));
        performanceSheetSignOffRecordMapper.insert(record);
    }

    private void validateBatchUnlocked(String periodMonth, Long deptId) {
        if (employeeSignOffBatchMapper.selectByPeriodMonthAndDeptId(periodMonth, deptId) != null) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_SIGN_OFF_BATCH_LOCKED);
        }
    }

    private List<Long> resolveRequestedEmployeeIds(Collection<Long> employeeIds, String permission) {
        return teamDashboardAccessService.resolveRequestedSubordinateUserIds(employeeIds, permission);
    }

    private boolean isEligibleForIssue(PerformanceSheetDO sheet) {
        return sheet != null
                && PerformanceConstants.STATUS_SENT.equals(sheet.getStatusCode())
                && isIssueAllowed(sheet.getSignOffStatus())
                && sheet.getFileId() != null;
    }

    private boolean isEligibleMonthlyReportForIssue(
            com.njcn.rdms.module.project.dal.dataobject.workreport.monthly.MonthlyReportDO report) {
        return report != null
                && WorkReportConstants.STATUS_APPROVED.equals(report.getStatusCode())
                && isIssueAllowed(report.getSignOffStatus());
    }

    private boolean hasLatestApprovedRecord(Long monthlyReportId) {
        return monthlyReportApprovalRecordMapper.selectLatestApprovedByMonthlyReportId(monthlyReportId) != null;
    }

    private boolean isIssueAllowed(String signOffStatus) {
        return !StringUtils.hasText(signOffStatus)
                || SignOffConstants.STATUS_NOT_STARTED.equals(signOffStatus);
    }

    private BigDecimal parseScore(String value) {
        if (!StringUtils.hasText(value)) {
            return null;
        }
        try {
            return normalizeScore(new BigDecimal(value.trim().replace(",", "")));
        } catch (NumberFormatException ex) {
            return null;
        }
    }

    private String formatScoreForMonthlyResult(BigDecimal score) {
        return score == null ? null : score.stripTrailingZeros().toPlainString();
    }

    private String resolveExcelContentType(String fileName) {
        return StringUtils.hasText(fileName) && fileName.toLowerCase().endsWith(".xls")
                ? "application/vnd.ms-excel"
                : EXCEL_CONTENT_TYPE;
    }

    private CurrentOperatorProfile loadCurrentOperatorProfile() {
        Long loginUserId = SecurityFrameworkUtils.getLoginUserId();
        CommonResult<AdminUserRespDTO> userResult = adminUserApi.getUser(loginUserId);
        AdminUserRespDTO user = userResult == null ? null : userResult.getCheckedData();
        if (user == null || user.getDeptId() == null) {
            throw invalidParamException("当前登录用户部门信息不完整");
        }
        CommonResult<DeptRespDTO> deptResult = deptApi.getDept(user.getDeptId());
        DeptRespDTO dept = deptResult == null ? null : deptResult.getCheckedData();
        if (dept == null || !StringUtils.hasText(dept.getName())) {
            throw invalidParamException("当前登录用户部门信息不完整");
        }
        return new CurrentOperatorProfile(loginUserId, defaultText(SecurityFrameworkUtils.getLoginUserNickname()),
                dept.getId(), dept.getName().trim());
    }

    private PerformanceSheetDO merge(PerformanceSheetDO source, PerformanceSheetDO update) {
        PerformanceSheetDO merged = BeanUtils.toBean(source, PerformanceSheetDO.class);
        if (update.getStatusCode() != null) {
            merged.setStatusCode(update.getStatusCode());
        }
        if (update.getSentTime() != null) {
            merged.setSentTime(update.getSentTime());
        }
        if (update.getConfirmedTime() != null) {
            merged.setConfirmedTime(update.getConfirmedTime());
        }
        if (update.getRejectedTime() != null) {
            merged.setRejectedTime(update.getRejectedTime());
        }
        if (update.getLastStatusReason() != null) {
            merged.setLastStatusReason(update.getLastStatusReason());
        }
        if (update.getSignOffStatus() != null) {
            merged.setSignOffStatus(update.getSignOffStatus());
        }
        return merged;
    }

    private void publishPerformanceNotice(Collection<Long> recipients, String templateCode, PerformanceSheetDO sheet) {
        if (recipients == null || recipients.isEmpty()) {
            return;
        }
        Map<String, Object> params = new LinkedHashMap<>();
        params.put("periodMonth", sheet.getPeriodMonth());
        params.put("employeeName", sheet.getEmployeeName());
        params.put("managerName", sheet.getManagerName());
        params.put("sheetId", sheet.getId());
        params.put("statusName", statusName(sheet.getStatusCode()));
        params.put("reason", sheet.getLastStatusReason());
        applicationEventPublisher.publishEvent(NotifySendEvent.of(new ArrayList<>(new LinkedHashSet<>(recipients)),
                templateCode, params, NotifyMessageLevelConstants.REMIND));
    }

    private ObjectStatusTransitionDO validateTransition(String fromStatus, String actionCode, String reason) {
        ObjectStatusTransitionDO transition = objectStatusTransitionMapper.selectByObjectTypeAndFromStatusAndAction(
                PerformanceConstants.STATUS_OBJECT_TYPE, fromStatus, actionCode);
        if (transition == null) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_ACTION_NOT_ALLOWED,
                    statusActionTextResolver.statusName(PerformanceConstants.STATUS_OBJECT_TYPE, fromStatus),
                    statusActionTextResolver.actionName(PerformanceConstants.STATUS_OBJECT_TYPE, actionCode));
        }
        if (Boolean.TRUE.equals(transition.getNeedReason()) && !StringUtils.hasText(reason)) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_ACTION_REASON_REQUIRED,
                    statusActionTextResolver.actionName(PerformanceConstants.STATUS_OBJECT_TYPE, actionCode));
        }
        ObjectStatusModelDO toModel = objectStatusModelMapper.selectByObjectTypeAndStatusCodeEnabled(
                PerformanceConstants.STATUS_OBJECT_TYPE, transition.getToStatusCode());
        if (toModel == null) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_MODEL_NOT_EXISTS_OR_DISABLED);
        }
        return transition;
    }

    private String getInitialStatusCode() {
        ObjectStatusModelDO statusModel = objectStatusModelMapper.selectInitialByObjectTypeEnabled(
                PerformanceConstants.STATUS_OBJECT_TYPE);
        if (statusModel == null || !StringUtils.hasText(statusModel.getStatusCode())) {
            throw exception(ErrorCodeConstants.PERFORMANCE_SHEET_STATUS_MODEL_NOT_EXISTS_OR_DISABLED);
        }
        return statusModel.getStatusCode();
    }

    private String normalizePeriodMonth(String periodMonth) {
        if (!StringUtils.hasText(periodMonth)) {
            throw invalidParamException("绩效月份不能为空");
        }
        try {
            return YearMonth.parse(periodMonth.trim()).toString();
        } catch (DateTimeParseException ex) {
            throw invalidParamException("绩效月份格式必须为 yyyy-MM");
        }
    }

    private BigDecimal normalizeScore(BigDecimal value) {
        return value.setScale(2, RoundingMode.HALF_UP);
    }

    private String buildPerformanceFileName(String periodMonth, String employeeName) {
        return periodMonth + "月-绩效表_" + defaultText(employeeName) + ".xlsx";
    }

    private String defaultDownloadName(PerformanceSheetDO sheet) {
        return StringUtils.hasText(sheet.getFileName())
                ? sheet.getFileName()
                : buildPerformanceFileName(sheet.getPeriodMonth(), sheet.getEmployeeName());
    }

    private String buildRemark(PerformanceSheetDO sheet) {
        return sheet.getPeriodMonth() + " " + defaultText(sheet.getEmployeeName()) + " 绩效表";
    }

    private String statusName(String statusCode) {
        return statusActionTextResolver.statusName(PerformanceConstants.STATUS_OBJECT_TYPE, statusCode);
    }

    private PerformanceSheetStatusDictRespVO buildStatusDictRespVO(ObjectStatusModelDO statusModel) {
        PerformanceSheetStatusDictRespVO respVO = new PerformanceSheetStatusDictRespVO();
        respVO.setStatusCode(statusModel.getStatusCode());
        respVO.setStatusName(statusModel.getStatusName());
        respVO.setSort(statusModel.getSort());
        respVO.setInitialFlag(statusModel.getInitialFlag());
        respVO.setTerminalFlag(statusModel.getTerminalFlag());
        respVO.setAllowEdit(statusModel.getAllowEdit());
        return respVO;
    }

    private PerformanceSheetStatusTransitionRespVO buildStatusTransitionRespVO(ObjectStatusTransitionDO transition) {
        PerformanceSheetStatusTransitionRespVO respVO = new PerformanceSheetStatusTransitionRespVO();
        respVO.setActionCode(transition.getActionCode());
        respVO.setActionName(transition.getActionName());
        respVO.setFromStatusCode(transition.getFromStatusCode());
        respVO.setToStatusCode(transition.getToStatusCode());
        respVO.setNeedReason(transition.getNeedReason());
        return respVO;
    }

    private String normalizeNullableText(String value) {
        if (!StringUtils.hasText(value)) {
            return null;
        }
        return value.trim();
    }

    private String defaultText(String value) {
        return StringUtils.hasText(value) ? value.trim() : "";
    }

    private static final class SheetCopyContext {

        private final Map<CellStyle, CellStyle> styleCache = new IdentityHashMap<>();
        private final Map<Font, Font> fontCache = new IdentityHashMap<>();
        private final Map<String, Short> dataFormatCache = new HashMap<>();
    }

    private record ImportedWorkbookEntry(String displayName, byte[] content) {
    }

    private record PerformanceEmployeeResponseContext(PerformanceSheetSignOffDO signOff, boolean signOffFlow) {
    }

    private record CurrentOperatorProfile(Long userId, String userName, Long deptId, String deptName) {
    }
}
