package com.sinata.appointment.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sinata.appointment.constants.BizErrorCode;
import com.sinata.appointment.constants.MiniUserType;
import com.sinata.appointment.constants.MpStaffConstants;
import com.sinata.appointment.config.MiniStpUtil;
import com.sinata.appointment.dto.StaffCreateRequest;
import com.sinata.appointment.dto.StaffDetailVo;
import com.sinata.appointment.dto.StaffImportExcelRow;
import com.sinata.appointment.dto.StaffImportResultVo;
import com.sinata.appointment.dto.StaffListVo;
import com.sinata.appointment.dto.StaffStatusPatchRequest;
import com.sinata.appointment.dto.StaffUpdateRequest;
import com.sinata.appointment.entity.MpStaffGroup;
import com.sinata.appointment.entity.MpStaffGroupMember;
import com.sinata.appointment.entity.MpUser;
import com.sinata.appointment.entity.SysDept;
import com.sinata.appointment.exception.BizException;
import com.sinata.appointment.mapper.MpStaffGroupMapper;
import com.sinata.appointment.mapper.MpStaffGroupMemberMapper;
import com.sinata.appointment.mapper.MpUserMapper;
import com.sinata.appointment.mapper.SysDeptMapper;
import com.sinata.appointment.util.MobileMaskUtil;
import com.sinata.appointment.util.PhoneNormalize;
import com.sinata.appointment.util.UuidAssert;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.handler.WorkbookWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardCopyOption;
import java.util.ArrayDeque;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.support.ServletUriComponentsBuilder;

@Service
public class StaffManageApplicationService {

    private static final long AVATAR_MAX_SIZE = 5L * 1024 * 1024;
    private static final Set<String> AVATAR_EXTENSIONS =
            Set.of(".jpg", ".jpeg", ".png", ".webp", ".gif");

    private final MpUserMapper mpUserMapper;
    private final SysDeptMapper sysDeptMapper;
    private final MpStaffGroupMemberMapper mpStaffGroupMemberMapper;
    private final MpStaffGroupMapper mpStaffGroupMapper;

    public StaffManageApplicationService(
            MpUserMapper mpUserMapper,
            SysDeptMapper sysDeptMapper,
            MpStaffGroupMemberMapper mpStaffGroupMemberMapper,
            MpStaffGroupMapper mpStaffGroupMapper) {
        this.mpUserMapper = mpUserMapper;
        this.sysDeptMapper = sysDeptMapper;
        this.mpStaffGroupMemberMapper = mpStaffGroupMemberMapper;
        this.mpStaffGroupMapper = mpStaffGroupMapper;
    }

    public String uploadAvatar(MultipartFile file) {
        if (file == null || file.isEmpty()) {
            throw new BizException(BizErrorCode.AUTH_INVALID_CREDENTIALS, "头像文件不能为空");
        }
        if (file.getSize() > AVATAR_MAX_SIZE) {
            throw new BizException(BizErrorCode.AUTH_INVALID_CREDENTIALS, "头像文件不能超过5MB");
        }
        String ext = normalizeImageExtension(file.getOriginalFilename(), file.getContentType());
        Path dir = resolveAvatarUploadDir();
        String fileName = UUID.randomUUID().toString().replace("-", "") + ext;
        Path target = dir.resolve(fileName);
        try {
            Files.createDirectories(dir);
            Files.copy(file.getInputStream(), target, StandardCopyOption.REPLACE_EXISTING);
        } catch (IOException ex) {
            throw new BizException(BizErrorCode.AUTH_FORBIDDEN, "头像上传失败");
        }
        return ServletUriComponentsBuilder.fromCurrentContextPath()
                .path("/uploads/avatars/")
                .path(fileName)
                .toUriString();
    }

    public IPage<StaffListVo> page(
            long page,
            long size,
            Long deptId,
            String realName,
            String staffNo,
            String mobile,
            String position,
            Integer status,
            Long excludeStaffGroupId,
            Long staffGroupId) {
        LambdaQueryWrapper<MpUser> q = new LambdaQueryWrapper<>();
        q.eq(MpUser::getUserType, MiniUserType.EMPLOYEE).eq(MpUser::getDelFlag, 0);
        if (deptId != null) {
            Set<Long> deptScope = selfAndDescendantDeptIds(deptId);
            q.in(MpUser::getDeptId, deptScope);
        }
        q.like(StringUtils.hasText(realName), MpUser::getNickName, realName != null ? realName.trim() : null);
        q.like(StringUtils.hasText(staffNo), MpUser::getStaffNo, staffNo != null ? staffNo.trim() : null);
        q.like(StringUtils.hasText(mobile), MpUser::getMobile, mobile != null ? mobile.trim() : null);
        if ("LEADER".equalsIgnoreCase(position)) {
            q.eq(MpUser::getDeptLeaderFlag, true);
        } else if ("STAFF".equalsIgnoreCase(position)) {
            q.and(
                    w ->
                            w.eq(MpUser::getDeptLeaderFlag, false)
                                    .or()
                                    .isNull(MpUser::getDeptLeaderFlag));
        }
        q.eq(status != null, MpUser::getStatus, status);
        if (staffGroupId != null) {
            Set<Long> inGroup = mpUserIdsInStaffGroup(staffGroupId);
            if (inGroup.isEmpty()) {
                Page<StaffListVo> empty = new Page<>(page, size, 0);
                empty.setRecords(Collections.emptyList());
                return empty;
            }
            q.in(MpUser::getId, inGroup);
        }
        if (excludeStaffGroupId != null) {
            Set<Long> inGroup = mpUserIdsInStaffGroup(excludeStaffGroupId);
            if (!inGroup.isEmpty()) {
                q.notIn(MpUser::getId, inGroup);
            }
        }
        q.orderByDesc(MpUser::getCreateTime);
        Page<MpUser> p = mpUserMapper.selectPage(new Page<>(page, size), q);
        Set<Long> deptIds = new HashSet<>();
        Set<Long> userIds = new HashSet<>();
        for (MpUser u : p.getRecords()) {
            if (u.getDeptId() != null) {
                deptIds.add(u.getDeptId());
            }
            userIds.add(u.getId());
        }
        Map<Long, String> deptNames = deptNamesByIds(deptIds);
        Map<Long, String> groupDisplay = groupDisplayByMpUserIds(userIds);
        return p.convert(u -> toListVo(u, deptNames, groupDisplay.getOrDefault(u.getId(), "—")));
    }

    private Set<Long> mpUserIdsInStaffGroup(Long staffGroupId) {
        List<MpStaffGroupMember> rels =
                mpStaffGroupMemberMapper.selectList(
                        new LambdaQueryWrapper<MpStaffGroupMember>()
                                .eq(MpStaffGroupMember::getGroupId, staffGroupId)
                                .eq(MpStaffGroupMember::getDelFlag, 0));
        return rels.stream()
                .map(MpStaffGroupMember::getMpUserId)
                .filter(Objects::nonNull)
                .collect(Collectors.toSet());
    }

    /** 指定部门及其全部子部门 id（含自身），用于左侧部门树筛选员工。 */
    private Set<Long> selfAndDescendantDeptIds(Long rootDeptId) {
        List<SysDept> all =
                sysDeptMapper.selectList(
                        new LambdaQueryWrapper<SysDept>().eq(SysDept::getDelFlag, 0));
        Map<Long, List<SysDept>> byParent = new HashMap<>();
        for (SysDept d : all) {
            long pid = d.getParentId() == null ? 0L : d.getParentId();
            byParent.computeIfAbsent(pid, k -> new ArrayList<>()).add(d);
        }
        Set<Long> result = new HashSet<>();
        ArrayDeque<Long> queue = new ArrayDeque<>();
        queue.add(rootDeptId);
        while (!queue.isEmpty()) {
            Long id = queue.poll();
            if (!result.add(id)) {
                continue;
            }
            for (SysDept child : byParent.getOrDefault(id, List.of())) {
                queue.add(child.getId());
            }
        }
        return result;
    }

    private List<String> loadActiveGroupNames() {
        List<MpStaffGroup> list =
                mpStaffGroupMapper.selectList(
                        new LambdaQueryWrapper<MpStaffGroup>()
                                .eq(MpStaffGroup::getDelFlag, 0)
                                .orderByAsc(MpStaffGroup::getGroupName));
        List<String> names = new ArrayList<>();
        for (MpStaffGroup g : list) {
            if (StringUtils.hasText(g.getGroupName())) {
                names.add(g.getGroupName().trim());
            }
        }
        return names;
    }

    private Map<Long, String> groupDisplayByMpUserIds(Set<Long> mpUserIds) {
        Map<Long, String> out = new HashMap<>();
        if (mpUserIds == null || mpUserIds.isEmpty()) {
            return out;
        }
        List<MpStaffGroupMember> rels =
                mpStaffGroupMemberMapper.selectList(
                        new LambdaQueryWrapper<MpStaffGroupMember>()
                                .eq(MpStaffGroupMember::getDelFlag, 0)
                                .in(MpStaffGroupMember::getMpUserId, mpUserIds));
        if (rels.isEmpty()) {
            return out;
        }
        Set<Long> groupIds = new HashSet<>();
        for (MpStaffGroupMember r : rels) {
            groupIds.add(r.getGroupId());
        }
        Map<Long, String> gidToName = new HashMap<>();
        if (!groupIds.isEmpty()) {
            List<MpStaffGroup> groups =
                    mpStaffGroupMapper.selectList(
                            new LambdaQueryWrapper<MpStaffGroup>()
                                    .eq(MpStaffGroup::getDelFlag, 0)
                                    .in(MpStaffGroup::getId, groupIds));
            for (MpStaffGroup g : groups) {
                if (StringUtils.hasText(g.getGroupName())) {
                    gidToName.put(g.getId(), g.getGroupName().trim());
                }
            }
        }
        Map<Long, List<String>> uidToNames = new HashMap<>();
        for (MpStaffGroupMember r : rels) {
            String gn = gidToName.get(r.getGroupId());
            if (!StringUtils.hasText(gn)) {
                continue;
            }
            uidToNames.computeIfAbsent(r.getMpUserId(), k -> new ArrayList<>()).add(gn);
        }
        for (Map.Entry<Long, List<String>> e : uidToNames.entrySet()) {
            List<String> distinct = e.getValue().stream().distinct().toList();
            out.put(e.getKey(), String.join("、", distinct));
        }
        return out;
    }

    private List<String> loadGroupNamesForUser(Long mpUserId) {
        List<MpStaffGroupMember> rels =
                mpStaffGroupMemberMapper.selectList(
                        new LambdaQueryWrapper<MpStaffGroupMember>()
                                .eq(MpStaffGroupMember::getDelFlag, 0)
                                .eq(MpStaffGroupMember::getMpUserId, mpUserId));
        if (rels.isEmpty()) {
            return List.of();
        }
        Set<Long> groupIds =
                rels.stream().map(MpStaffGroupMember::getGroupId).collect(Collectors.toSet());
        List<MpStaffGroup> groups =
                mpStaffGroupMapper.selectList(
                        new LambdaQueryWrapper<MpStaffGroup>()
                                .eq(MpStaffGroup::getDelFlag, 0)
                                .in(MpStaffGroup::getId, groupIds)
                                .orderByAsc(MpStaffGroup::getGroupName));
        List<String> names = new ArrayList<>();
        for (MpStaffGroup g : groups) {
            if (StringUtils.hasText(g.getGroupName())) {
                names.add(g.getGroupName().trim());
            }
        }
        return names;
    }

    private void attachStaffToGroupsByNames(Long mpUserId, String rawGroup) {
        if (!StringUtils.hasText(rawGroup) || mpUserId == null) {
            return;
        }
        String[] parts = rawGroup.split("[,，;；、\\s]+");
        Set<String> seen = new HashSet<>();
        for (String part : parts) {
            if (!StringUtils.hasText(part)) {
                continue;
            }
            String name = part.trim();
            if (!seen.add(name)) {
                continue;
            }
            MpStaffGroup g =
                    mpStaffGroupMapper.selectOne(
                            new LambdaQueryWrapper<MpStaffGroup>()
                                    .eq(MpStaffGroup::getDelFlag, 0)
                                    .eq(MpStaffGroup::getGroupName, name));
            if (g == null) {
                throw new BizException(BizErrorCode.AUTH_INVALID_CREDENTIALS, "所属分组不存在: " + name);
            }
            MpStaffGroupMember exist =
                    mpStaffGroupMemberMapper.selectOne(
                            new LambdaQueryWrapper<MpStaffGroupMember>()
                                    .eq(MpStaffGroupMember::getGroupId, g.getId())
                                    .eq(MpStaffGroupMember::getMpUserId, mpUserId)
                                    .eq(MpStaffGroupMember::getDelFlag, 0));
            if (exist != null) {
                continue;
            }
            MpStaffGroupMember rel = new MpStaffGroupMember();
            rel.setGroupId(g.getId());
            rel.setMpUserId(mpUserId);
            mpStaffGroupMemberMapper.insert(rel);
        }
    }

    public StaffDetailVo detail(Long id, String uuid) {
        MpUser u = loadEmployeeWithUuid(id, uuid);
        return toDetailVo(u);
    }

    @Transactional(rollbackFor = Exception.class)
    public Map<String, Object> create(StaffCreateRequest req) {
        String mobileRaw = req.getMobile().trim();
        String mobileNorm = PhoneNormalize.mainland(mobileRaw);
        assertMobileFormat(mobileNorm);
        assertStaffNoUnique(trimToNull(req.getStaffNo()), null);
        validateDept(req.getDeptId());

        MpUser exist = findActiveByNormalizedMobile(mobileNorm);
        if (exist != null) {
            if (MiniUserType.EMPLOYEE.equals(exist.getUserType())) {
                throw new BizException(BizErrorCode.MP_STAFF_MOBILE_EXISTS, "手机号已存在");
            }
            if (MiniUserType.VISITOR.equals(exist.getUserType())) {
                upgradeVisitorToEmployee(exist, req, mobileRaw);
                return Map.of("id", exist.getId(), "uuid", exist.getUuid());
            }
        }

        MpUser u = new MpUser();
        u.setOpenId(MpStaffConstants.newPlaceholderOpenId());
        u.setUserType(MiniUserType.EMPLOYEE);
        u.setNickName(trimName(req.getNickName()));
        u.setStaffNo(trimToNull(req.getStaffNo()));
        u.setMobile(mobileRaw);
        u.setAvatarUrl(trimToNull(req.getAvatarUrl()));
        u.setDeptId(req.getDeptId());
        u.setDeptLeaderFlag(Boolean.TRUE.equals(req.getDeptLeader()));
        u.setStatus(1);
        mpUserMapper.insert(u);
        MpUser fresh = mpUserMapper.selectById(u.getId());
        if (fresh == null || !StringUtils.hasText(fresh.getUuid())) {
            throw new BizException(BizErrorCode.AUTH_FORBIDDEN, "创建后读取员工失败");
        }
        return Map.of("id", fresh.getId(), "uuid", fresh.getUuid());
    }

    @Transactional(rollbackFor = Exception.class)
    public void update(Long id, StaffUpdateRequest req) {
        MpUser u = loadEmployeeWithUuid(id, req.getUuid());
        String mobileRaw = req.getMobile().trim();
        String mobileNorm = PhoneNormalize.mainland(mobileRaw);
        assertMobileFormat(mobileNorm);
        assertStaffNoUnique(trimToNull(req.getStaffNo()), u.getId());
        validateDept(req.getDeptId());

        String oldNorm = PhoneNormalize.mainland(u.getMobile() == null ? "" : u.getMobile());
        if (!mobileNorm.equals(oldNorm)) {
            MpUser other = findActiveByNormalizedMobile(mobileNorm);
            if (other != null && !other.getId().equals(u.getId())) {
                if (MiniUserType.EMPLOYEE.equals(other.getUserType())) {
                    throw new BizException(BizErrorCode.MP_STAFF_MOBILE_EXISTS, "手机号已存在");
                }
                throw new BizException(BizErrorCode.MP_STAFF_MOBILE_EXISTS, "手机号已存在");
            }
        }

        u.setNickName(trimName(req.getNickName()));
        u.setStaffNo(trimToNull(req.getStaffNo()));
        u.setAvatarUrl(trimToNull(req.getAvatarUrl()));
        u.setMobile(mobileRaw);
        u.setDeptId(req.getDeptId());
        u.setDeptLeaderFlag(Boolean.TRUE.equals(req.getDeptLeader()));
        mpUserMapper.updateById(u);
    }

    @Transactional(rollbackFor = Exception.class)
    public void patchStatus(Long id, StaffStatusPatchRequest req) {
        MpUser u = loadEmployeeWithUuid(id, req.getUuid());
        int st = req.getStatus();
        if (st != 0 && st != 1) {
            throw new BizException(BizErrorCode.AUTH_INVALID_CREDENTIALS, "状态无效");
        }
        u.setStatus(st);
        mpUserMapper.updateById(u);
        if (st == 0) {
            kickMiniUserOffline(u.getId());
        }
    }

    @Transactional(rollbackFor = Exception.class)
    public void delete(Long id, String uuid) {
        MpUser u = loadEmployeeWithUuid(id, uuid);
        mpStaffGroupMemberMapper.delete(
                new LambdaQueryWrapper<MpStaffGroupMember>()
                        .eq(MpStaffGroupMember::getMpUserId, u.getId())
                        .eq(MpStaffGroupMember::getDelFlag, 0));
        u.setUserType(MiniUserType.VISITOR);
        u.setDeptId(null);
        u.setDeptLeaderFlag(false);
        u.setStaffNo(null);
        mpUserMapper.updateById(u);
        kickMiniUserOffline(u.getId());
    }

    private Path resolveAvatarUploadDir() {
        return Path.of(System.getProperty("user.dir"), "uploads", "avatars")
                .toAbsolutePath()
                .normalize();
    }

    private String normalizeImageExtension(String originalFilename, String contentType) {
        String ext = "";
        if (StringUtils.hasText(originalFilename)) {
            int idx = originalFilename.lastIndexOf('.');
            if (idx >= 0) {
                ext = originalFilename.substring(idx).toLowerCase();
            }
        }
        if (!AVATAR_EXTENSIONS.contains(ext)) {
            ext = switch (contentType == null ? "" : contentType.toLowerCase()) {
                case "image/jpeg", "image/jpg" -> ".jpg";
                case "image/png" -> ".png";
                case "image/webp" -> ".webp";
                case "image/gif" -> ".gif";
                default -> "";
            };
        }
        if (!AVATAR_EXTENSIONS.contains(ext)) {
            throw new BizException(BizErrorCode.AUTH_INVALID_CREDENTIALS, "仅支持 jpg、png、webp、gif 图片");
        }
        return ext;
    }

    /** 清除小程序端会话（仅 mini 域）。 */
    private void kickMiniUserOffline(Long mpUserId) {
        try {
            MiniStpUtil.logout(mpUserId);
        } catch (Exception ignored) {
        }
    }

    public byte[] importTemplateExcel() {
        List<SysDept> depts =
                sysDeptMapper.selectList(
                        new LambdaQueryWrapper<SysDept>()
                                .eq(SysDept::getDelFlag, 0)
                                .eq(SysDept::getStatus, 1)
                                .orderByAsc(SysDept::getSortOrder)
                                .orderByAsc(SysDept::getId));
        List<String> deptNames =
                depts.stream()
                        .map(SysDept::getDeptName)
                        .filter(StringUtils::hasText)
                        .map(String::trim)
                        .toList();
        List<String> groupNames = loadActiveGroupNames();

        List<StaffImportExcelRow> demo = new ArrayList<>();
        StaffImportExcelRow ex = new StaffImportExcelRow();
        ex.setRealName("张三");
        ex.setStaffNo("EMP001");
        ex.setMobile("13800138000");
        if (!deptNames.isEmpty()) {
            ex.setDept(deptNames.get(0));
        }
        ex.setPosition("员工");
        if (!groupNames.isEmpty()) {
            ex.setGroup(groupNames.get(0));
        }
        demo.add(ex);

        try (ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
            // 顶层不要绑定 StaffImportExcelRow.class，否则「字典」sheet 也会被写入员工列表头，下拉会含表头文案
            var writer =
                    EasyExcel.write(bos)
                            .excelType(ExcelTypeEnum.XLSX)
                            .registerWriteHandler(new HideDictSheetHandler())
                            .build();
            try {
                var dictSheet = EasyExcel.writerSheet("字典").needHead(false).build();
                writer.write(buildDictRows(deptNames, groupNames), dictSheet);

                var dataSheet =
                        EasyExcel.writerSheet("员工导入")
                                .head(StaffImportExcelRow.class)
                                .registerWriteHandler(
                                        new StaffTemplateDropdownHandler(deptNames, groupNames))
                                .build();
                writer.write(demo, dataSheet);
            } finally {
                writer.finish();
            }
            return bos.toByteArray();
        } catch (Exception e) {
            return new byte[0];
        }
    }

    public StaffImportResultVo importExcel(MultipartFile file) {
        StaffImportResultVo vo = new StaffImportResultVo();
        if (file == null || file.isEmpty()) {
            vo.getErrors().add("文件为空");
            return vo;
        }
        Map<String, Long> deptNameToId = new HashMap<>();
        List<SysDept> depts =
                sysDeptMapper.selectList(
                        new LambdaQueryWrapper<SysDept>()
                                .eq(SysDept::getDelFlag, 0)
                                .eq(SysDept::getStatus, 1));
        for (SysDept d : depts) {
            if (StringUtils.hasText(d.getDeptName())) {
                deptNameToId.put(d.getDeptName().trim(), d.getId());
            }
        }

        try (InputStream in = file.getInputStream()) {
            EasyExcel.read(in, StaffImportExcelRow.class, new AnalysisEventListener<StaffImportExcelRow>() {
                        int rowIndex = 1; // header is 0

                        @Override
                        public void invoke(StaffImportExcelRow row, AnalysisContext context) {
                            rowIndex++;
                            try {
                                handleImportRow(rowIndex, row, deptNameToId);
                                vo.setSuccess(vo.getSuccess() + 1);
                            } catch (StopRowException ignored) {
                            } catch (BizException e) {
                                vo.setFail(vo.getFail() + 1);
                                vo.getErrors().add("第" + rowIndex + "行: " + e.getMessage());
                            } catch (Exception e) {
                                vo.setFail(vo.getFail() + 1);
                                vo.getErrors().add("第" + rowIndex + "行: 解析失败");
                            }
                        }

                        @Override
                        public void doAfterAllAnalysed(AnalysisContext context) {}
                    })
                    .sheet(0)
                    .doRead();
        } catch (Exception e) {
            vo.getErrors().add("读取文件失败: " + e.getMessage());
        }
        return vo;
    }

    private void handleImportRow(int lineNo, StaffImportExcelRow row, Map<String, Long> deptNameToId) {
        String name = row.getRealName() == null ? "" : row.getRealName().trim();
        String staffNo = row.getStaffNo() == null ? "" : row.getStaffNo().trim();
        String mobile = row.getMobile() == null ? "" : row.getMobile().trim();
        String dept = row.getDept() == null ? "" : row.getDept().trim();
        String position = row.getPosition() == null ? "" : row.getPosition().trim();
        String groupCell = row.getGroup() == null ? "" : row.getGroup().trim();

        if (!StringUtils.hasText(name)
                && !StringUtils.hasText(staffNo)
                && !StringUtils.hasText(mobile)
                && !StringUtils.hasText(dept)
                && !StringUtils.hasText(position)
                && !StringUtils.hasText(groupCell)) {
            throw new StopRowException();
        }

        if (!StringUtils.hasText(name) || !StringUtils.hasText(mobile) || !StringUtils.hasText(dept)) {
            throw new BizException(BizErrorCode.AUTH_INVALID_CREDENTIALS, "员工姓名、联系方式、所属部门不能为空");
        }

        Long deptId = parseDeptId(dept, deptNameToId);
        boolean leader = parseDeptLeader(position);

        StaffCreateRequest req = new StaffCreateRequest();
        req.setNickName(name);
        req.setStaffNo(StringUtils.hasText(staffNo) ? staffNo : null);
        req.setMobile(mobile);
        req.setDeptId(deptId);
        req.setDeptLeader(leader);
        Map<String, Object> created = create(req);
        Object idObj = created.get("id");
        Long uid = idObj instanceof Number ? ((Number) idObj).longValue() : null;
        if (uid != null && StringUtils.hasText(groupCell)) {
            attachStaffToGroupsByNames(uid, groupCell);
        }
    }

    private static final class StopRowException extends RuntimeException {}

    private static List<List<String>> buildDictRows(List<String> deptNames, List<String> groupNames) {
        int max = Math.max(Math.max(deptNames.size(), groupNames.size()), 2);
        List<List<String>> rows = new ArrayList<>();
        for (int i = 0; i < max; i++) {
            String dept = i < deptNames.size() ? deptNames.get(i) : "";
            String pos = i == 0 ? "部门负责人" : (i == 1 ? "员工" : "");
            String grp = i < groupNames.size() ? groupNames.get(i) : "";
            rows.add(java.util.Arrays.asList(dept, pos, grp));
        }
        return rows;
    }

    private static final class HideDictSheetHandler implements WorkbookWriteHandler {
        @Override
        public void afterWorkbookCreate(WriteWorkbookHolder writeWorkbookHolder) {
            Workbook wb = writeWorkbookHolder.getWorkbook();
            int idx = wb.getSheetIndex("字典");
            if (idx >= 0) {
                wb.setSheetHidden(idx, true);
            }
        }
    }

    private static final class StaffTemplateDropdownHandler implements SheetWriteHandler {
        private final List<String> deptNames;
        private final List<String> groupNames;

        private StaffTemplateDropdownHandler(List<String> deptNames, List<String> groupNames) {
            this.deptNames = deptNames != null ? deptNames : List.of();
            this.groupNames = groupNames != null ? groupNames : List.of();
        }

        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            Sheet sheet = writeSheetHolder.getSheet();
            Workbook wb = writeWorkbookHolder.getWorkbook();

            if (!deptNames.isEmpty()) {
                Name deptRange = wb.createName();
                deptRange.setNameName("dept_list");
                deptRange.setRefersToFormula("字典!$A$1:$A$" + deptNames.size());
            }
            Name posRange = wb.createName();
            posRange.setNameName("pos_list");
            posRange.setRefersToFormula("字典!$B$1:$B$2");
            if (!groupNames.isEmpty()) {
                Name groupRange = wb.createName();
                groupRange.setNameName("group_list");
                groupRange.setRefersToFormula("字典!$C$1:$C$" + groupNames.size());
            }

            DataValidationHelper helper = sheet.getDataValidationHelper();
            int startRow = 1;
            int endRow = 500;

            if (!deptNames.isEmpty()) {
                DataValidationConstraint deptConstraint = helper.createFormulaListConstraint("dept_list");
                CellRangeAddressList deptAddr = new CellRangeAddressList(startRow, endRow, 3, 3);
                DataValidation dvDept = helper.createValidation(deptConstraint, deptAddr);
                dvDept.setShowErrorBox(true);
                sheet.addValidationData(dvDept);
            }

            DataValidationConstraint posConstraint = helper.createFormulaListConstraint("pos_list");
            CellRangeAddressList posAddr = new CellRangeAddressList(startRow, endRow, 4, 4);
            DataValidation dvPos = helper.createValidation(posConstraint, posAddr);
            dvPos.setShowErrorBox(true);
            sheet.addValidationData(dvPos);

            if (!groupNames.isEmpty()) {
                DataValidationConstraint gConstraint = helper.createFormulaListConstraint("group_list");
                CellRangeAddressList gAddr = new CellRangeAddressList(startRow, endRow, 5, 5);
                DataValidation dvG = helper.createValidation(gConstraint, gAddr);
                dvG.setShowErrorBox(true);
                sheet.addValidationData(dvG);
            }
        }
    }

    private static Long parseDeptId(String dept, Map<String, Long> deptNameToId) {
        if (!StringUtils.hasText(dept)) {
            return null;
        }
        String t = dept.trim();
        try {
            return Long.parseLong(t);
        } catch (Exception ignored) {
        }
        Long id = deptNameToId.get(t);
        if (id == null) {
            throw new BizException(BizErrorCode.AUTH_INVALID_CREDENTIALS, "所属部门不存在: " + t);
        }
        return id;
    }

    private static boolean parseDeptLeader(String position) {
        if (!StringUtils.hasText(position)) {
            return false;
        }
        String t = position.trim();
        if ("部门负责人".equals(t) || "LEADER".equalsIgnoreCase(t)) {
            return true;
        }
        if ("员工".equals(t) || "STAFF".equalsIgnoreCase(t)) {
            return false;
        }
        throw new BizException(BizErrorCode.AUTH_INVALID_CREDENTIALS, "所属职位无效: " + t);
    }

    private void upgradeVisitorToEmployee(MpUser exist, StaffCreateRequest req, String mobileRaw) {
        assertStaffNoUnique(trimToNull(req.getStaffNo()), exist.getId());
        validateDept(req.getDeptId());
        exist.setUserType(MiniUserType.EMPLOYEE);
        exist.setNickName(trimName(req.getNickName()));
        exist.setStaffNo(trimToNull(req.getStaffNo()));
        exist.setAvatarUrl(trimToNull(req.getAvatarUrl()));
        exist.setMobile(mobileRaw);
        exist.setDeptId(req.getDeptId());
        exist.setDeptLeaderFlag(Boolean.TRUE.equals(req.getDeptLeader()));
        exist.setStatus(1);
        mpUserMapper.updateById(exist);
    }

    private MpUser findActiveByNormalizedMobile(String norm) {
        if (!StringUtils.hasText(norm)) {
            return null;
        }
        List<MpUser> list =
                mpUserMapper.selectList(
                        new LambdaQueryWrapper<MpUser>()
                                .eq(MpUser::getDelFlag, 0)
                                .isNotNull(MpUser::getMobile));
        for (MpUser u : list) {
            if (norm.equals(PhoneNormalize.mainland(u.getMobile()))) {
                return u;
            }
        }
        return null;
    }

    private void assertStaffNoUnique(String staffNo, Long excludeUserId) {
        if (!StringUtils.hasText(staffNo)) {
            return;
        }
        MpUser o =
                mpUserMapper.selectOne(
                        new LambdaQueryWrapper<MpUser>()
                                .eq(MpUser::getStaffNo, staffNo)
                                .eq(MpUser::getDelFlag, 0));
        if (o != null && (excludeUserId == null || !o.getId().equals(excludeUserId))) {
            throw new BizException(BizErrorCode.MP_STAFF_NO_EXISTS, "员工编号已存在");
        }
    }

    private void validateDept(Long deptId) {
        SysDept d = sysDeptMapper.selectById(deptId);
        if (d == null || d.getDelFlag() != null && d.getDelFlag() == 1) {
            throw new BizException(BizErrorCode.DEPT_PARENT_INVALID, "部门不存在");
        }
        if (d.getStatus() == null || d.getStatus() != 1) {
            throw new BizException(BizErrorCode.DEPT_PARENT_INVALID, "部门已禁用");
        }
    }

    private static void assertMobileFormat(String norm) {
        if (norm.length() != 11 || !norm.chars().allMatch(Character::isDigit)) {
            throw new BizException(BizErrorCode.AUTH_INVALID_CREDENTIALS, "手机号须为11位数字");
        }
    }

    private MpUser loadEmployeeWithUuid(long id, String uuid) {
        MpUser u = mpUserMapper.selectById(id);
        if (u == null || u.getDelFlag() != null && u.getDelFlag() == 1) {
            throw new BizException(BizErrorCode.RESOURCE_VERIFY_FAILED, "资源不存在或无权访问");
        }
        if (!MiniUserType.EMPLOYEE.equals(u.getUserType())) {
            throw new BizException(BizErrorCode.RESOURCE_VERIFY_FAILED, "资源不存在或无权访问");
        }
        UuidAssert.match(u, uuid);
        return u;
    }

    private Map<Long, String> deptNamesByIds(Set<Long> ids) {
        if (ids.isEmpty()) {
            return Map.of();
        }
        List<SysDept> depts =
                sysDeptMapper.selectList(
                        new LambdaQueryWrapper<SysDept>().in(SysDept::getId, ids).eq(SysDept::getDelFlag, 0));
        Map<Long, String> m = new HashMap<>();
        for (SysDept d : depts) {
            m.put(d.getId(), d.getDeptName());
        }
        return m;
    }

    private StaffListVo toListVo(MpUser u, Map<Long, String> deptNames, String groupDisplay) {
        StaffListVo vo = new StaffListVo();
        vo.setId(u.getId());
        vo.setUuid(u.getUuid());
        vo.setRealName(u.getNickName());
        vo.setAvatarUrl(u.getAvatarUrl());
        vo.setStaffNo(u.getStaffNo());
        vo.setMobileMasked(MobileMaskUtil.mask(u.getMobile()));
        vo.setDeptId(u.getDeptId());
        vo.setDeptName(
                u.getDeptId() == null ? "—" : deptNames.getOrDefault(u.getDeptId(), "—"));
        vo.setPositionLabel(Boolean.TRUE.equals(u.getDeptLeaderFlag()) ? "部门负责人" : "员工");
        vo.setGroupDisplay(groupDisplay);
        vo.setStatus(u.getStatus() == null ? 1 : u.getStatus());
        vo.setCreateTime(u.getCreateTime());
        return vo;
    }

    private StaffDetailVo toDetailVo(MpUser u) {
        StaffDetailVo vo = new StaffDetailVo();
        vo.setId(u.getId());
        vo.setUuid(u.getUuid());
        vo.setNickName(u.getNickName());
        vo.setStaffNo(u.getStaffNo());
        vo.setAvatarUrl(u.getAvatarUrl());
        vo.setMobile(u.getMobile());
        vo.setDeptId(u.getDeptId());
        vo.setDeptLeader(Boolean.TRUE.equals(u.getDeptLeaderFlag()));
        vo.setStatus(u.getStatus() == null ? 1 : u.getStatus());
        vo.setGroupNames(loadGroupNamesForUser(u.getId()));
        return vo;
    }

    private static String trimName(String s) {
        if (!StringUtils.hasText(s)) {
            throw new BizException(BizErrorCode.AUTH_INVALID_CREDENTIALS, "员工名称不能为空");
        }
        return s.trim();
    }

    private static String trimToNull(String s) {
        if (!StringUtils.hasText(s)) {
            return null;
        }
        String t = s.trim();
        return t.isEmpty() ? null : t;
    }
}