package com.rj.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.rj.dto.LbBuyerTradeStats;
import com.rj.entity.LbDailyUserTrade;
import com.rj.entity.LbDepartmentUser;
import com.rj.entity.LbOrderRow;
import com.rj.entity.LbPurchaseApply;
import com.rj.entity.LbUser;
import com.rj.mapper.LbDailyUserTradeMapper;
import com.rj.mapper.LbDepartmentUserMapper;
import com.rj.mapper.LbOrderRowMapper;
import com.rj.mapper.LbPurchaseApplyMapper;
import com.rj.mapper.LbUserMapper;
import com.rj.service.ILbDepartmentUserService;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.awt.Color;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

@Slf4j
@Service
public class LbDepartmentUserServiceImpl extends ServiceImpl<LbDepartmentUserMapper, LbDepartmentUser>
        implements ILbDepartmentUserService {

    private static final int PURCHASE_APPLY_BATCH_SIZE = 200;

    private static final String ORDER_DATA_TYPE_DETAIL = "detail_data";

    private static final DateTimeFormatter ORDER_DAY_TIME_FMT =
            DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    private static final DateTimeFormatter ORDER_DAY_FMT = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    @Autowired
    private LbDailyUserTradeMapper lbDailyUserTradeMapper;
    @Autowired
    private LbPurchaseApplyMapper lbPurchaseApplyMapper;
    @Autowired
    private LbUserMapper lbUserMapper;
    @Autowired
    private LbOrderRowMapper lbOrderRowMapper;

    @Override
    public Map<String, Object> add(LbDepartmentUser entity) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (entity.getUserId() == null || entity.getUserId().trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "userId不能为空");
                return result;
            }
            if (entity.getTenantId() == null || entity.getTenantId().trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "tenantId不能为空");
                return result;
            }
            if (entity.getName() == null || entity.getName().trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "name不能为空");
                return result;
            }

            if (entity.getId() == null || entity.getId().trim().isEmpty()) {
                entity.setId(UUID.randomUUID().toString());
            }
            LocalDateTime now = LocalDateTime.now();
            entity.setCreateTime(now);
            entity.setUpdateTime(now);
            if (entity.getIsActive() == null) {
                entity.setIsActive(1);
            }

            boolean ok = this.save(entity);
            result.put("success", ok);
            result.put("message", ok ? "新增成功" : "新增失败");
            if (ok) {
                result.put("data", entity);
            }
            return result;
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "新增异常：" + e.getMessage());
            return result;
        }
    }

    @Override
    public Map<String, Object> update(LbDepartmentUser entity) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (entity.getId() == null || entity.getId().trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "id不能为空");
                return result;
            }
            entity.setUpdateTime(LocalDateTime.now());
            boolean ok = this.updateById(entity);
            result.put("success", ok);
            result.put("message", ok ? "编辑成功" : "编辑失败");
            if (ok) {
                result.put("data", this.getById(entity.getId()));
            }
            return result;
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "编辑异常：" + e.getMessage());
            return result;
        }
    }

    @Override
    public Map<String, Object> deleteById(String id) {
        Map<String, Object> result = new HashMap<>();
        try {
            boolean ok = this.removeById(id);
            result.put("success", ok);
            result.put("message", ok ? "删除成功" : "删除失败");
            return result;
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "删除异常：" + e.getMessage());
            return result;
        }
    }

    @Override
    public Map<String, Object> pageQuery(Integer current,
                                         Integer size,
                                         String tenantId,
                                         String userId,
                                         String parentId,
                                         String name,
                                         String phone,
                                         String industry,
                                         Integer isActive) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (current == null || current < 1) {
                current = 1;
            }
            if (size == null || size < 1) {
                size = 10;
            }

            LambdaQueryWrapper<LbDepartmentUser> queryWrapper = new LambdaQueryWrapper<>();
            if (tenantId != null && !tenantId.trim().isEmpty()) {
                queryWrapper.eq(LbDepartmentUser::getTenantId, tenantId.trim());
            }
            if (userId != null && !userId.trim().isEmpty()) {
                queryWrapper.eq(LbDepartmentUser::getUserId, userId.trim());
            }
            if (parentId != null && !parentId.trim().isEmpty()) {
                queryWrapper.eq(LbDepartmentUser::getParentId, parentId.trim());
            }
            if (name != null && !name.trim().isEmpty()) {
                queryWrapper.like(LbDepartmentUser::getName, name.trim());
            }
            if (phone != null && !phone.trim().isEmpty()) {
                queryWrapper.like(LbDepartmentUser::getPhone, phone.trim());
            }
            if (industry != null && !industry.trim().isEmpty()) {
                queryWrapper.like(LbDepartmentUser::getIndustry, industry.trim());
            }
            if (isActive != null) {
                queryWrapper.eq(LbDepartmentUser::getIsActive, isActive);
            }
            queryWrapper.orderByDesc(LbDepartmentUser::getUpdateTime)
                    .orderByDesc(LbDepartmentUser::getCreateTime);

            Page<LbDepartmentUser> page = this.page(new Page<>(current, size), queryWrapper);
            result.put("success", true);
            result.put("message", "查询成功");
            result.put("data", page.getRecords());
            result.put("total", page.getTotal());
            result.put("current", page.getCurrent());
            result.put("size", page.getSize());
            result.put("pages", page.getPages());
            return result;
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "查询异常：" + e.getMessage());
            return result;
        }
    }

    @Override
    public Map<String, Object> bindParentUser(String parentUserId,
                                              String parentUserName,
                                              String childUserId,
                                              String childUserName,
                                              String tenantId) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (parentUserId == null || parentUserId.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "父用户id不能为空");
                return result;
            }
            if (childUserId == null || childUserId.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "子用户id不能为空");
                return result;
            }
            if (tenantId == null || tenantId.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "租户id不能为空");
                return result;
            }

            String parentUserIdTrim = parentUserId.trim();
            String childUserIdTrim = childUserId.trim();
            String tenantIdTrim = tenantId.trim();

            // 第1步：根据子用户id查询目标记录
            LambdaQueryWrapper<LbDepartmentUser> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(LbDepartmentUser::getTenantId, tenantIdTrim)
                    .eq(LbDepartmentUser::getUserId, childUserIdTrim);
            if (childUserName != null && !childUserName.trim().isEmpty()) {
                queryWrapper.eq(LbDepartmentUser::getName, childUserName.trim());
            }
            LbDepartmentUser childUser = this.getOne(queryWrapper, false);
            if (childUser == null) {
                result.put("success", false);
                result.put("message", "未查询到对应子用户");
                return result;
            }

            // 第2步：设置父用户id
            childUser.setParentId(parentUserIdTrim);
            if (parentUserName != null && !parentUserName.trim().isEmpty()) {
                childUser.setParentName(parentUserName.trim());
            }
            childUser.setUpdateTime(LocalDateTime.now());

            // 第3步：更新数据库表
            boolean ok = this.updateById(childUser);
            result.put("success", ok);
            result.put("message", ok ? "绑定父用户成功" : "绑定父用户失败");
            if (ok) {
                Map<String, Object> data = new HashMap<>();
                data.put("parentUserId", parentUserIdTrim);
                data.put("parentUserName", parentUserName);
                data.put("childUserId", childUserIdTrim);
                data.put("childUserName", childUser.getName());
                data.put("tenantId", tenantIdTrim);
                data.put("record", this.getById(childUser.getId()));
                result.put("data", data);
            }
            return result;
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "绑定父子用户异常：" + e.getMessage());
            return result;
        }
    }

    @Override
    public Map<String, Object> syncFromDailyUserTrade(LocalDate startDate, LocalDate endDate) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (startDate == null || endDate == null) {
                result.put("success", false);
                result.put("message", "开始日期和结束日期不能为空");
                return result;
            }
            if (startDate.isAfter(endDate)) {
                result.put("success", false);
                result.put("message", "开始日期不能大于结束日期");
                return result;
            }

            List<LbDailyUserTrade> tradeList = lbDailyUserTradeMapper.selectList(

                    new LambdaQueryWrapper<LbDailyUserTrade>()
                            .ge(LbDailyUserTrade::getReportDate, startDate)
                            .le(LbDailyUserTrade::getReportDate, endDate)
                            .ge(LbDailyUserTrade::getDailyBuyAmt, 1)
                            .isNotNull(LbDailyUserTrade::getNickname)
            );
            if (tradeList == null || tradeList.isEmpty()) {
                result.put("success", true);
                result.put("message", "无可同步数据");
                result.put("insertedCount", 0);
                result.put("skippedCount", 0);
                result.put("sourceCount", 0);
                result.put("startDate", startDate);
                result.put("endDate", endDate);
                return result;
            }

            List<LbDepartmentUser> existedUsers = this.list();
            Map<String, LbDepartmentUser> existingUserMap = new HashMap<>();
            for (LbDepartmentUser user : existedUsers) {
                if (user.getName() != null && !user.getName().trim().isEmpty()) {
                    existingUserMap.put(user.getName().trim(), user);
                }
            }

            Map<String, LbDailyUserTrade> latestTradeByNickname = new HashMap<>();
            List<LbDepartmentUser> toInsert = new ArrayList<>();
            List<LbDepartmentUser> toUpdate = new ArrayList<>();
            int skippedCount = 0;
            LocalDateTime now = LocalDateTime.now();
            Set<String> candidateNicknameSet = new HashSet<>();
            for (LbDailyUserTrade trade : tradeList) {
                String nickname = trade.getNickname() == null ? null : trade.getNickname().trim();
                if (nickname == null || nickname.isEmpty()) {
                    skippedCount++;
                    continue;
                }
                candidateNicknameSet.add(nickname);
                LbDailyUserTrade prev = latestTradeByNickname.get(nickname);
                if (prev == null || isTradeReportDateNewer(trade, prev)) {
                    latestTradeByNickname.put(nickname, trade);
                }
            }

            Map<String, LbPurchaseApply> phoneMap = buildPurchaseApplyPhoneMap(candidateNicknameSet);
            for (Map.Entry<String, LbDailyUserTrade> entry : latestTradeByNickname.entrySet()) {
                String nickname = entry.getKey();
                LbDailyUserTrade trade = entry.getValue();
                LbDepartmentUser entity = existingUserMap.get(nickname);
                boolean isInsert = false;
                if (entity == null) {
                    entity = new LbDepartmentUser();
                    entity.setId(UUID.randomUUID().toString());
                    entity.setCreateTime(now);
                    entity.setIsActive(1);
                    isInsert = true;
                }
                entity.setTenantId(trade.getTenantId());
                entity.setUserId(trade.getUserId());
                entity.setName(nickname);
                entity.setParentId(trade.getPromoterId());
                entity.setParentName(trade.getPromoterName());

                LbPurchaseApply purchaseApply = phoneMap.get(buildPhoneKey(trade.getTenantId(), nickname));
                if (purchaseApply != null && purchaseApply.getApplyPhone() != null) {
                    String phone = purchaseApply.getApplyPhone().trim();
                    entity.setPhone(phone.isEmpty() ? null : phone);
                    entity.setJoinDate(purchaseApply.getApplyDate());
                }
                applyLatestTradeFields(entity, trade);
                entity.setUpdateTime(now);
                if (isInsert) {
                    toInsert.add(entity);
                } else {
                    toUpdate.add(entity);
                }
            }

            if (!toInsert.isEmpty()) {
                this.saveBatch(toInsert);
            }
            if (!toUpdate.isEmpty()) {
                this.updateBatchById(toUpdate);
            }

            result.put("success", true);
            result.put("message", "同步完成");
            result.put("insertedCount", toInsert.size());
            result.put("updatedCount", toUpdate.size());
            result.put("skippedCount", skippedCount);
            result.put("sourceCount", tradeList.size());
            result.put("startDate", startDate);
            result.put("endDate", endDate);
            return result;
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "同步异常：" + e.getMessage());
            return result;
        }
    }

    private Map<String, LbPurchaseApply> buildPurchaseApplyPhoneMap(Set<String> applyUserSet) {
        Map<String, LbPurchaseApply> phoneMap = new HashMap<>();
        if (applyUserSet == null || applyUserSet.isEmpty()) {
            return phoneMap;
        }

        List<String> applyUserList = new ArrayList<>(applyUserSet);
        for (int i = 0; i < applyUserList.size(); i += PURCHASE_APPLY_BATCH_SIZE) {
            int end = Math.min(i + PURCHASE_APPLY_BATCH_SIZE, applyUserList.size());
            List<String> batchUsers = applyUserList.subList(i, end);

            List<LbPurchaseApply> purchaseApplyList = lbPurchaseApplyMapper.selectList(
                    new LambdaQueryWrapper<LbPurchaseApply>()
                            .in(LbPurchaseApply::getApplyUser, batchUsers)
                            .isNotNull(LbPurchaseApply::getApplyPhone)
                            .orderByDesc(LbPurchaseApply::getUpdateTime)
                            .orderByDesc(LbPurchaseApply::getCreateTime)
            );
            for (LbPurchaseApply item : purchaseApplyList) {
                if (item.getApplyUser() == null || item.getApplyPhone() == null) {
                    continue;
                }
                String applyUser = item.getApplyUser().trim();
                String phone = item.getApplyPhone().trim();
                if (applyUser.isEmpty() || phone.isEmpty()) {
                    continue;
                }
                String key = buildPhoneKey(item.getTenantId(), applyUser);
                // 已按更新时间倒序，首次写入即最新手机号
                phoneMap.putIfAbsent(key, item);
            }
        }
        return phoneMap;
    }

    private String buildPhoneKey(String tenantId, String applyUser) {
        String tenantPart = tenantId == null ? "" : tenantId.trim();
        String userPart = applyUser == null ? "" : applyUser.trim();
        return tenantPart + "#" + userPart;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Map<String, Object> syncFromLbUser(String tenantId) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (tenantId == null || tenantId.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "租户id不能为空");
                return result;
            }
            String tenantIdTrim = tenantId.trim();

            List<LbUser> lbUsers = lbUserMapper.selectList(
                    new LambdaQueryWrapper<LbUser>().eq(LbUser::getTenantId, tenantIdTrim));
            if (lbUsers == null || lbUsers.isEmpty()) {
                result.put("success", true);
                result.put("message", "无可同步用户数据");
                result.put("insertedCount", 0);
                result.put("updatedCount", 0);
                result.put("sourceCount", 0);
                result.put("tenantId", tenantIdTrim);
                return result;
            }

            Map<Long, LbUser> userById = new HashMap<>();
            for (LbUser user : lbUsers) {
                if (user.getId() != null) {
                    userById.put(user.getId(), user);
                }
            }

            List<LbDepartmentUser> existedUsers = this.list(
                    new LambdaQueryWrapper<LbDepartmentUser>().eq(LbDepartmentUser::getTenantId, tenantIdTrim));
            Map<String, LbDepartmentUser> existingByUserId = new HashMap<>();
            if (existedUsers != null) {
                for (LbDepartmentUser deptUser : existedUsers) {
                    if (deptUser.getUserId() != null && !deptUser.getUserId().trim().isEmpty()) {
                        existingByUserId.put(deptUser.getUserId().trim(), deptUser);
                    }
                }
            }

            Map<String, LbBuyerTradeStats> tradeStatsByUserId = loadBuyerTradeStatsFromOrderRow(tenantIdTrim);

            List<LbDepartmentUser> toInsert = new ArrayList<>();
            List<LbDepartmentUser> toUpdate = new ArrayList<>();
            LocalDateTime now = LocalDateTime.now();
            for (LbUser lbUser : lbUsers) {
                if (lbUser.getId() == null) {
                    continue;
                }
                String userId = String.valueOf(lbUser.getId());
                LbDepartmentUser entity = existingByUserId.get(userId);
                boolean isInsert = false;
                if (entity == null) {
                    entity = new LbDepartmentUser();
                    entity.setId(UUID.randomUUID().toString());
                    entity.setCreateTime(now);
                    isInsert = true;
                }

                entity.setTenantId(tenantIdTrim);
                entity.setUserId(userId);
                entity.setName(resolveDisplayName(lbUser));
                entity.setPhone(lbUser.getMobile());
                if (lbUser.getJoinTime() != null) {
                    entity.setJoinDate(lbUser.getJoinTime().toLocalDate());
                }

                Long pid = lbUser.getPid();
                if (pid != null && pid > 0) {
                    entity.setParentId(String.valueOf(pid));
                    LbUser parent = userById.get(pid);
                    if (parent != null) {
                        entity.setParentName(resolveDisplayName(parent));
                    } else if (lbUser.getPname() != null && !lbUser.getPname().trim().isEmpty()) {
                        entity.setParentName(lbUser.getPname().trim());
                    }
                } else {
                    entity.setParentId(null);
                    entity.setParentName(null);
                }

                entity.setOrgChain(buildOrgChain(lbUser.getId(), userById));


                LbBuyerTradeStats tradeStats = tradeStatsByUserId.get(userId);
                if (tradeStats != null) {
                    entity.setLastTradeDate(parseOrderTimeToDate(tradeStats.getPayTime()));
                    entity.setLastBuyAmt(tradeStats.getTotalMoney());
                    entity.setIsActive(1);
                } else {
                    entity.setLastTradeDate(null);
                    entity.setLastBuyAmt(null);
                    entity.setIsActive(0);
                }
                entity.setUpdateTime(now);

                if (isInsert) {
                    toInsert.add(entity);
                } else {
                    toUpdate.add(entity);
                }
            }
            if (!toInsert.isEmpty()) {
                this.saveBatch(toInsert);
            }
            if (!toUpdate.isEmpty()) {
                this.updateBatchById(toUpdate);
            }

            result.put("success", true);
            result.put("message", "同步完成");
            result.put("insertedCount", toInsert.size());
            result.put("updatedCount", toUpdate.size());
            result.put("sourceCount", lbUsers.size());
            result.put("tenantId", tenantIdTrim);
            return result;
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "同步异常：" + e.getMessage());
            return result;
        }
    }

    private static final String ORG_CHAIN_NODE_SEPARATOR = "->";

    /**
     * 沿 lb_user.pid 向上追溯，构建从直接上级到根上级的链条。
     * 格式：id,name,phone->id,name,phone->...（节点内逗号分隔，节点间箭头连接）
     */
    private String buildOrgChain(Long userId, Map<Long, LbUser> userById) {
        LbUser current = userById.get(userId);
        if (current == null) {
            return null;
        }

        List<String> nodes = new ArrayList<>();
        Set<Long> visited = new HashSet<>();
        Long pid = current.getPid();
        while (pid != null && pid > 0) {
            if (!visited.add(pid)) {
                break;
            }
            Long currentPid = pid;
            LbUser parent = userById.get(currentPid);
            String name;
            String phone;
            if (parent != null) {
                name = sanitizeOrgChainField(resolveDisplayName(parent));
                phone = sanitizeOrgChainField(parent.getMobile());
                pid = parent.getPid();
            } else {
                name = "";
                phone = "";
                pid = null;
            }
            nodes.add(formatOrgChainNode(currentPid, name, phone));
        }
        return nodes.isEmpty() ? null : String.join(ORG_CHAIN_NODE_SEPARATOR, nodes);
    }

    /**
     * 节点格式：id；有 name/phone 时才追加逗号，避免末尾出现多余的 , 或 ,,
     */
    private static String formatOrgChainNode(Long currentPid, String name, String phone) {
        StringBuilder sb = new StringBuilder(String.valueOf(currentPid));
        if (!name.isEmpty()) {
            sb.append(',').append(name);
        }
        if (!phone.isEmpty()) {
            sb.append(',').append(phone);
        }
        return sb.toString();
    }

    private static String sanitizeOrgChainField(String value) {
        if (value == null) {
            return "";
        }
        return value.trim()
                .replace(",", "")
                .replace(ORG_CHAIN_NODE_SEPARATOR, "");
    }

    private static String resolveDisplayName(LbUser user) {
        if (user.getNickname() != null && !user.getNickname().trim().isEmpty()) {
            return user.getNickname().trim();
        }
        if (user.getUsername() != null && !user.getUsername().trim().isEmpty()) {
            return user.getUsername().trim();
        }
        return user.getId() == null ? null : String.valueOf(user.getId());
    }

    @Override
    public Map<String, Object> getDepartmentUserTree(String tenantId, Integer isActive, LocalDate joinDate, LocalDate lastTradeDate) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (tenantId == null || tenantId.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "租户id不能为空");
                return result;
            }
            String tenantIdTrim = tenantId.trim();

            List<LbUser> lbUsers = lbUserMapper.selectList(
                    new LambdaQueryWrapper<LbUser>().eq(LbUser::getTenantId, tenantIdTrim));
            if (lbUsers == null || lbUsers.isEmpty()) {
                result.put("success", true);
                result.put("message", "查询成功");
                result.put("data", new ArrayList<>());
                result.put("total", 0);
                result.put("tenantId", tenantIdTrim);
                return result;
            }

            Map<String, LbUser> userById = new LinkedHashMap<>();
            for (LbUser lbUser : lbUsers) {
                if (lbUser.getId() != null) {
                    userById.put(String.valueOf(lbUser.getId()), lbUser);
                }
            }

            Map<String, LbBuyerTradeStats> tradeStatsByUserId = loadBuyerTradeStatsFromOrderRow(tenantIdTrim);
            boolean hasFilter = isActive != null || joinDate != null || lastTradeDate != null;

            Set<String> matchedUserIds = new HashSet<>();
            Map<String, LocalDate> joinDateByUserId = new HashMap<>();
            Map<String, LocalDate> lastTradeDateByUserId = new HashMap<>();
            Map<String, BigDecimal> lastBuyAmtByUserId = new HashMap<>();
            Map<String, Integer> isActiveByUserId = new HashMap<>();
            for (LbUser lbUser : lbUsers) {
                if (lbUser.getId() == null) {
                    continue;
                }
                String userId = String.valueOf(lbUser.getId());
                LocalDate userJoinDate = lbUser.getJoinTime() != null
                        ? lbUser.getJoinTime().toLocalDate() : null;
                LbBuyerTradeStats tradeStats = tradeStatsByUserId.get(userId);
                LocalDate userLastTradeDate = tradeStats == null
                        ? null : parseOrderTimeToDate(tradeStats.getPayTime());
                BigDecimal userLastBuyAmt = tradeStats == null ? null : tradeStats.getTotalMoney();
                int userIsActive = tradeStats == null ? 0 : 1;

                joinDateByUserId.put(userId, userJoinDate);
                lastTradeDateByUserId.put(userId, userLastTradeDate);
                lastBuyAmtByUserId.put(userId, userLastBuyAmt);
                isActiveByUserId.put(userId, userIsActive);

                if (!hasFilter || matchesTreeFilter(
                        userIsActive, userJoinDate, userLastTradeDate, isActive, joinDate, lastTradeDate)) {
                    matchedUserIds.add(userId);
                }
            }

            Set<String> visibleUserIds = hasFilter
                    ? includeAncestorUserIds(matchedUserIds, userById)
                    : new HashSet<>(userById.keySet());

            Map<String, Map<String, Object>> nodeByUserId = new LinkedHashMap<>();
            Map<String, String> parentByUserId = new HashMap<>();
            for (String userId : visibleUserIds) {
                LbUser lbUser = userById.get(userId);
                if (lbUser == null) {
                    continue;
                }
                nodeByUserId.put(userId, toLbUserTreeNode(
                        lbUser,
                        joinDateByUserId.get(userId),
                        lastTradeDateByUserId.get(userId),
                        lastBuyAmtByUserId.get(userId),
                        isActiveByUserId.getOrDefault(userId, 0)));

                String parentUserId = resolveLbUserParentId(lbUser, userById);
                if (parentUserId != null && visibleUserIds.contains(parentUserId)) {
                    parentByUserId.put(userId, parentUserId);
                }
            }

            List<Map<String, Object>> treeRoots = new ArrayList<>();
            for (Map.Entry<String, Map<String, Object>> entry : nodeByUserId.entrySet()) {
                String userId = entry.getKey();
                Map<String, Object> node = entry.getValue();
                String parentUserId = parentByUserId.get(userId);
                Map<String, Object> parentNode = parentUserId == null ? null : nodeByUserId.get(parentUserId);
                if (parentNode == null) {
                    treeRoots.add(node);
                    continue;
                }
                @SuppressWarnings("unchecked")
                List<Map<String, Object>> children = (List<Map<String, Object>>) parentNode.get("children");
                children.add(node);
            }
            sortDepartmentUserTreeByName(treeRoots);
            List<Map<String, Object>> treeData = skipFirstTreeLevel(treeRoots);

            result.put("success", true);
            result.put("message", "查询成功");
            result.put("data", treeData);
            result.put("total", countTreeNodes(treeData));
            result.put("tenantId", tenantIdTrim);
            return result;
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "查询树形结构异常：" + e.getMessage());
            return result;
        }
    }

    /**
     * 去掉第 1 级根节点，将其 children 提升为返回给前端的顶层。
     */
    private static List<Map<String, Object>> skipFirstTreeLevel(List<Map<String, Object>> treeRoots) {
        List<Map<String, Object>> result = new ArrayList<>();
        for (Map<String, Object> root : treeRoots) {
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> children = (List<Map<String, Object>>) root.get("children");
            if (children != null && !children.isEmpty()) {
                result.addAll(children);
            }
        }
        sortDepartmentUserTreeByName(result);
        return result;
    }

    private static int countTreeNodes(List<Map<String, Object>> nodes) {
        int count = 0;
        for (Map<String, Object> node : nodes) {
            count++;
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> children = (List<Map<String, Object>>) node.get("children");
            if (children != null && !children.isEmpty()) {
                count += countTreeNodes(children);
            }
        }
        return count;
    }

    private static boolean matchesTreeFilter(int userIsActive,
                                             LocalDate userJoinDate,
                                             LocalDate userLastTradeDate,
                                             Integer isActive,
                                             LocalDate joinDate,
                                             LocalDate lastTradeDate) {
        if (isActive != null && userIsActive != isActive) {
            return false;
        }
        if (joinDate != null && (userJoinDate == null || !userJoinDate.isAfter(joinDate))) {
            return false;
        }
        return lastTradeDate == null
                || (userLastTradeDate != null && userLastTradeDate.isAfter(lastTradeDate));
    }

    /**
     * 筛选命中用户时，沿 lb_user.pid 向上补全祖先，避免子节点因上级被筛掉而挂错层级。
     */
    private static Set<String> includeAncestorUserIds(Set<String> userIds, Map<String, LbUser> userById) {
        Set<String> expanded = new HashSet<>(userIds);
        for (String userId : userIds) {
            LbUser current = userById.get(userId);
            Set<Long> visited = new HashSet<>();
            while (current != null) {
                Long pid = current.getPid();
                if (pid == null || pid <= 0 || !visited.add(pid)) {
                    break;
                }
                String parentUserId = String.valueOf(pid);
                if (!userById.containsKey(parentUserId)) {
                    break;
                }
                expanded.add(parentUserId);
                current = userById.get(parentUserId);
            }
        }
        return expanded;
    }

    /**
     * 从 lb_user.pid 解析上级 userId；pid 必须在同租户用户索引中存在。
     */
    private static String resolveLbUserParentId(LbUser user, Map<String, LbUser> userById) {
        if (user == null || user.getPid() == null || user.getPid() <= 0) {
            return null;
        }
        String parentUserId = String.valueOf(user.getPid());
        return userById.containsKey(parentUserId) ? parentUserId : null;
    }

    /**
     * 一次 SQL 从 lb_order_row 汇总每位买家的最新已支付订单统计。
     */
    private Map<String, LbBuyerTradeStats> loadBuyerTradeStatsFromOrderRow(String tenantId) {
        List<LbBuyerTradeStats> rows = lbOrderRowMapper.selectLatestPaidStatsByTenantId(
                tenantId, ORDER_DATA_TYPE_DETAIL);
        Map<String, LbBuyerTradeStats> statsByUserId = new HashMap<>();
        if (rows == null || rows.isEmpty()) {
            return statsByUserId;
        }
        for (LbBuyerTradeStats row : rows) {
            if (row.getBuyerId() == null) {
                continue;
            }
            statsByUserId.put(String.valueOf(row.getBuyerId()), row);
        }
        return statsByUserId;
    }

    private static Map<String, Object> toLbUserTreeNode(LbUser user,
                                                       LocalDate joinDate,
                                                       LocalDate lastTradeDate,
                                                       BigDecimal lastBuyAmt,
                                                       int isActive) {
        Map<String, Object> node = new LinkedHashMap<>();
        node.put("userId", user.getId() == null ? null : String.valueOf(user.getId()));
        node.put("name", trimToNull(resolveDisplayName(user)));
        node.put("phone", trimToNull(user.getMobile()));
        Long pid = user.getPid();
        node.put("parentId", pid == null || pid <= 0 ? null : String.valueOf(pid));
        node.put("parentName", trimToNull(user.getPname()));
        node.put("joinDate", joinDate);
        node.put("lastTradeDate", lastTradeDate);
        node.put("lastBuyAmt", lastBuyAmt);
        node.put("isActive", isActive);
        node.put("children", new ArrayList<Map<String, Object>>());
        return node;
    }

    private static void sortDepartmentUserTreeByName(List<Map<String, Object>> nodes) {
        nodes.sort(Comparator.comparing(
                (Map<String, Object> m) -> (String) m.get("name"),
                Comparator.nullsLast(String::compareTo)));
        for (Map<String, Object> node : nodes) {
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> children = (List<Map<String, Object>>) node.get("children");
            if (children != null && !children.isEmpty()) {
                sortDepartmentUserTreeByName(children);
            }
        }
    }

    private static String trimToNull(String value) {
        if (value == null) {
            return null;
        }
        String trimmed = value.trim();
        return trimmed.isEmpty() ? null : trimmed;
    }

    private static boolean isTradeReportDateNewer(LbDailyUserTrade trade, LbDailyUserTrade prev) {
        LocalDate reportDate = trade.getReportDate();
        LocalDate prevReportDate = prev.getReportDate();
        if (reportDate == null) {
            return false;
        }
        if (prevReportDate == null) {
            return true;
        }
        return reportDate.isAfter(prevReportDate);
    }

    private static boolean isPaidOrderRow(LbOrderRow row) {
        String payTime = row.getPayTime();
        return payTime != null && !payTime.trim().isEmpty();
    }

    private static boolean isOrderPayTimeNewer(LbOrderRow order, LbOrderRow prev) {
        LocalDateTime payTime = parseOrderTime(payTimeOrBuyTime(order));
        LocalDateTime prevPayTime = parseOrderTime(payTimeOrBuyTime(prev));
        if (payTime == null) {
            return false;
        }
        if (prevPayTime == null) {
            return true;
        }
        int cmp = payTime.compareTo(prevPayTime);
        if (cmp != 0) {
            return cmp > 0;
        }
        Long id = order.getId();
        Long prevId = prev.getId();
        if (id == null) {
            return false;
        }
        if (prevId == null) {
            return true;
        }
        return id > prevId;
    }

    private static String payTimeOrBuyTime(LbOrderRow row) {
        if (row.getPayTime() != null && !row.getPayTime().trim().isEmpty()) {
            return row.getPayTime().trim();
        }
        return row.getBuyTime() == null ? null : row.getBuyTime().trim();
    }

    private static LocalDateTime parseOrderTime(String timeText) {
        if (timeText == null || timeText.isEmpty()) {
            return null;
        }
        try {
            return LocalDateTime.parse(timeText, ORDER_DAY_TIME_FMT);
        } catch (DateTimeParseException ignored) {
            // continue
        }
        try {
            return LocalDate.parse(timeText.substring(0, Math.min(10, timeText.length())), ORDER_DAY_FMT)
                    .atStartOfDay();
        } catch (DateTimeParseException ignored) {
            // continue
        }
        try {
            return LocalDateTime.parse(timeText, DateTimeFormatter.ISO_LOCAL_DATE_TIME);
        } catch (DateTimeParseException e) {
            return null;
        }
    }

    private static LocalDate parseOrderTimeToDate(String timeText) {
        LocalDateTime dateTime = parseOrderTime(timeText);
        return dateTime == null ? null : dateTime.toLocalDate();
    }

    /**
     * 按最近交易日写入 last_trade_date、last_buy_amt；若新交易日更晚则覆盖，否则保留原值。
     */
    private static void applyLatestTradeFields(LbDepartmentUser entity, LbDailyUserTrade trade) {
        LocalDate reportDate = trade.getReportDate();
        LocalDate existingDate = entity.getLastTradeDate();
        if (existingDate == null || (reportDate != null && reportDate.isAfter(existingDate))) {
            entity.setLastTradeDate(reportDate);
            entity.setLastBuyAmt(trade.getDailyBuyAmt());
            return;
        }
        if (reportDate != null && reportDate.equals(existingDate)) {
            entity.setLastBuyAmt(trade.getDailyBuyAmt());
        }
    }

    @Override
    public void exportUsersExcel(String tenantId, List<String> userIds, HttpServletResponse response) {
        try {
            if (tenantId == null || tenantId.trim().isEmpty()) {
                response.sendError(HttpServletResponse.SC_BAD_REQUEST, "tenantId不能为空");
                return;
            }
            if (userIds == null || userIds.isEmpty()) {
                response.sendError(HttpServletResponse.SC_BAD_REQUEST, "用户ID列表不能为空");
                return;
            }

            String tenantIdTrim = tenantId.trim();
            List<String> orderedUserIds = new ArrayList<>();
            Set<String> seen = new HashSet<>();
            for (String rawId : userIds) {
                if (rawId == null) {
                    continue;
                }
                String userId = rawId.trim();
                if (userId.isEmpty() || !seen.add(userId)) {
                    continue;
                }
                orderedUserIds.add(userId);
            }
            if (orderedUserIds.isEmpty()) {
                response.sendError(HttpServletResponse.SC_BAD_REQUEST, "用户ID列表不能为空");
                return;
            }

            Map<String, LbDepartmentUser> deptUserByUserId = new HashMap<>();
            List<LbDepartmentUser> deptUsers = this.list(
                    new LambdaQueryWrapper<LbDepartmentUser>()
                            .eq(LbDepartmentUser::getTenantId, tenantIdTrim)
                            .in(LbDepartmentUser::getUserId, orderedUserIds));
            if (deptUsers != null) {
                for (LbDepartmentUser deptUser : deptUsers) {
                    if (deptUser.getUserId() != null && !deptUser.getUserId().trim().isEmpty()) {
                        deptUserByUserId.put(deptUser.getUserId().trim(), deptUser);
                    }
                }
            }

            List<Long> buyerIds = new ArrayList<>();
            Map<String, Long> buyerIdByUserId = new HashMap<>();
            for (String userId : orderedUserIds) {
                try {
                    Long buyerId = Long.parseLong(userId);
                    buyerIds.add(buyerId);
                    buyerIdByUserId.put(userId, buyerId);
                } catch (NumberFormatException ignored) {
                    // 非数字 userId 无法关联订单，仍导出用户基础信息
                }
            }

            Map<String, OrderBuyStats> statsByUserId = new HashMap<>();
            if (!buyerIds.isEmpty()) {
                List<LbOrderRow> orderRows = lbOrderRowMapper.selectList(
                        new LambdaQueryWrapper<LbOrderRow>()
                                .eq(LbOrderRow::getTenantId, tenantIdTrim)
                                .eq(LbOrderRow::getDataType, ORDER_DATA_TYPE_DETAIL)
                                .in(LbOrderRow::getBuyerId, buyerIds)
                                .isNotNull(LbOrderRow::getPayTime));
                if (orderRows != null) {
                    Map<Long, String> userIdByBuyerId = new HashMap<>();
                    for (Map.Entry<String, Long> entry : buyerIdByUserId.entrySet()) {
                        userIdByBuyerId.put(entry.getValue(), entry.getKey());
                    }
                    for (LbOrderRow row : orderRows) {
                        if (!isPaidOrderRow(row) || row.getBuyerId() == null) {
                            continue;
                        }
                        String userId = userIdByBuyerId.get(row.getBuyerId());
                        if (userId == null) {
                            continue;
                        }
                        statsByUserId
                                .computeIfAbsent(userId, k -> new OrderBuyStats())
                                .accumulate(row);
                    }
                }
            }

            try (Workbook workbook = new XSSFWorkbook()) {
                Sheet sheet = workbook.createSheet("部门用户");

                CellStyle headerStyle = createExportHeaderStyle(workbook);
                CellStyle dataStyle = createExportDataStyle(workbook);

                String[] headers = {
                        "用户ID", "用户名", "手机号", "加入时间",
                        "最早buy_time", "最晚buy_time", "累计交易金额buy_amt"
                };
                Row headerRow = sheet.createRow(0);
                headerRow.setHeightInPoints(22);
                for (int i = 0; i < headers.length; i++) {
                    createExportCell(headerRow, i, headers[i], headerStyle);
                }

                int rowIndex = 1;
                for (String userId : orderedUserIds) {
                    LbDepartmentUser deptUser = deptUserByUserId.get(userId);
                    OrderBuyStats stats = statsByUserId.get(userId);

                    Row row = sheet.createRow(rowIndex++);
                    row.setHeightInPoints(20);
                    createExportCell(row, 0, userId, dataStyle);
                    createExportCell(row, 1, deptUser == null ? "" : nullToEmpty(deptUser.getName()), dataStyle);
                    createExportCell(row, 2, deptUser == null ? "" : nullToEmpty(deptUser.getPhone()), dataStyle);
                    createExportCell(row, 3, formatJoinDate(deptUser == null ? null : deptUser.getJoinDate()), dataStyle);
                    createExportCell(row, 4, stats == null ? "" : nullToEmpty(stats.earliestBuyTime), dataStyle);
                    createExportCell(row, 5, stats == null ? "" : nullToEmpty(stats.latestBuyTime), dataStyle);
                    createExportCell(row, 6, stats == null ? "" : formatAmount(stats.totalBuyAmt), dataStyle);
                }

                adjustExportColumnWidths(sheet, headers);

                String filename = "部门用户导出_" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) + ".xlsx";
                String encoded = URLEncoder.encode(filename, StandardCharsets.UTF_8).replaceAll("\\+", "%20");

                response.setCharacterEncoding(StandardCharsets.UTF_8.name());
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename*=UTF-8''" + encoded);

                try (ServletOutputStream os = response.getOutputStream()) {
                    workbook.write(os);
                    os.flush();
                }
            }
        } catch (Exception e) {
            log.error("部门用户导出异常", e);
            try {
                response.reset();
                response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                response.setCharacterEncoding(StandardCharsets.UTF_8.name());
                response.setContentType("text/plain;charset=UTF-8");
                response.getWriter().write("导出异常：" + e.getMessage());
            } catch (Exception ignored) {
                // ignore
            }
        }
    }

    private static final class OrderBuyStats {
        private String earliestBuyTime;
        private LocalDateTime earliestBuyDateTime;
        private String latestBuyTime;
        private LocalDateTime latestBuyDateTime;
        private BigDecimal totalBuyAmt = BigDecimal.ZERO;

        void accumulate(LbOrderRow row) {
            if (row.getTotalMoney() != null) {
                totalBuyAmt = totalBuyAmt.add(row.getTotalMoney());
            }
            String buyTimeText = row.getBuyTime() == null ? null : row.getBuyTime().trim();
            if (buyTimeText == null || buyTimeText.isEmpty()) {
                return;
            }
            LocalDateTime buyDateTime = parseOrderTime(buyTimeText);
            if (buyDateTime == null) {
                return;
            }
            if (earliestBuyDateTime == null || buyDateTime.isBefore(earliestBuyDateTime)) {
                earliestBuyDateTime = buyDateTime;
                earliestBuyTime = buyTimeText;
            }
            if (latestBuyDateTime == null || buyDateTime.isAfter(latestBuyDateTime)) {
                latestBuyDateTime = buyDateTime;
                latestBuyTime = buyTimeText;
            }
        }
    }

    private static String formatJoinDate(LocalDate joinDate) {
        return joinDate == null ? "" : joinDate.format(DateTimeFormatter.ISO_LOCAL_DATE);
    }

    private static String formatAmount(BigDecimal amount) {
        if (amount == null) {
            return "";
        }
        return amount.stripTrailingZeros().toPlainString();
    }

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

    private static CellStyle createExportHeaderStyle(Workbook workbook) {
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        ((XSSFCellStyle) headerStyle).setFillForegroundColor(new XSSFColor(new Color(255, 217, 102), null));
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        return headerStyle;
    }

    private static CellStyle createExportDataStyle(Workbook workbook) {
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setBorderTop(BorderStyle.THIN);
        dataStyle.setBorderBottom(BorderStyle.THIN);
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setBorderRight(BorderStyle.THIN);
        return dataStyle;
    }

    private static void createExportCell(Row row, int col, String text, CellStyle style) {
        Cell cell = row.createCell(col);
        cell.setCellValue(text);
        cell.setCellStyle(style);
    }

    private static void adjustExportColumnWidths(Sheet sheet, String[] headers) {
        int columnCount = headers.length;
        int[] maxWidths = new int[columnCount];
        for (int i = 0; i < columnCount; i++) {
            maxWidths[i] = calcExportDisplayWidth(headers[i]);
        }
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            for (int c = 0; c < columnCount; c++) {
                Cell cell = row.getCell(c);
                if (cell != null && cell.getCellType() == CellType.STRING) {
                    maxWidths[c] = Math.max(maxWidths[c], calcExportDisplayWidth(cell.getStringCellValue()));
                }
            }
        }
        for (int c = 0; c < columnCount; c++) {
            int width = (maxWidths[c] + 2) * 256;
            sheet.setColumnWidth(c, Math.min(width, 255 * 256));
        }
    }

    private static int calcExportDisplayWidth(String text) {
        if (text == null || text.isEmpty()) {
            return 0;
        }
        int width = 0;
        for (char ch : text.toCharArray()) {
            width += ch > 127 ? 2 : 1;
        }
        return width;
    }
}
