package com.vote.system.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.sun.org.apache.bcel.internal.generic.NEW;
import com.vctgo.common.core.exception.ServiceException;
import com.vctgo.common.core.utils.poi.ExcelUtil;
import com.vctgo.system.api.RemoteBranchKindergartenService;
import com.vctgo.system.api.RemotePlatformUserService;
import com.vctgo.system.api.RemoteSysProvinceService;
import com.vctgo.system.api.domain.SysPlatformUser;
import com.vctgo.system.api.domain.SysProvince;
import com.vctgo.system.api.domain.SysUserTempalte;
import com.vctgo.system.api.domain.dto.SysBranchKindergartenDto;
import com.vote.system.domain.*;
import com.vote.system.domain.dto.VoteEvaluationTasksDto;
import com.vote.system.domain.pojo.VoteRegistrationExport;
import com.vote.system.domain.pojo.VoteSubmissionExport;
import com.vote.system.domain.pojo.VoteTaskExport;
import com.vote.system.domain.pojo.VoteZonesExport;
import com.vote.system.domain.vo.*;
import com.vote.system.mapper.*;
import com.vote.system.service.VoteDataStatisService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

import static cn.hutool.core.date.DateUtil.formatDate;
import static com.vote.system.util.ConstantUtils.*;


@Service
public class VoteDataStatisServiceImpl implements VoteDataStatisService {

    @Autowired
    private VoteRegistrationsMapper voteRegistrationsMapper;

    @Autowired
    private VoteEventsMapper voteEventsMapper;

    @Autowired
    private VoteEventWorkSettingsMapper voteEventWorkSettingsMapper;

    @Autowired
    private VoteEventEvaluationRoundsMapper voteEventEvaluationRoundsMapper;

    @Autowired
    private VoteEvaluationTasksMapper voteEvaluationTasksMapper;

    @Autowired
    private VoteSubmissionMapper voteSubmissionMapper;

    @Autowired
    private VoteEventRegistrationWorkScoresMapper voteEventRegistrationWorkScoresMapper;

    @Autowired
    private VoteEventCompetitionZonesMapper voteEventCompetitionZonesMapper;

    @Autowired
    private RemoteSysProvinceService remoteSysProvinceService;

    @Autowired
    private RemotePlatformUserService remotePlatformUserService;

    @Autowired
    private RemoteBranchKindergartenService remoteBranchKindergartenService;

    @Override
    public VoteEventRegistrationsVo getList(Long eventId) {
        // 查询指定 eventId 的报名记录
        VoteEvents voteEvents = voteEventsMapper.selectById(eventId);
        LambdaQueryWrapper<VoteRegistrations> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(VoteRegistrations::getEventId, eventId);
        List<VoteRegistrations> voteRegistrations = voteRegistrationsMapper.selectList(wrapper);
        List<SysProvince> sysProvinces = remoteSysProvinceService.getProvince().getData();
        Result result = getResult(sysProvinces);
        // 遍历报名记录，根据省份 ID 统计人数
        for (VoteRegistrations registration : voteRegistrations) {
            String provinceId = registration.getProvinceId();
            result.provinceCountMap.put(provinceId, result.provinceCountMap.getOrDefault(provinceId, 0) + 1);
        }
        // 构造结果列表
        List<VoteEventStatisVo> resultList = new ArrayList<>();
        // 遍历省份统计 Map，生成对应的 VoteEventRegistrationsVo 对象
        for (Map.Entry<String, Integer> entry : result.provinceCountMap.entrySet()) {
            VoteEventStatisVo eventStatisVo = new VoteEventStatisVo();
            // 从 provinceNameMap 中获取省份名称
            eventStatisVo.setProvince(result.provinceNameMap.getOrDefault(entry.getKey(), "未知省份"));
            eventStatisVo.setCount(entry.getValue());
            // 添加统计结果到列表中
            resultList.add(eventStatisVo);
        }
        VoteEventRegistrationsVo registrationsVo = new VoteEventRegistrationsVo();
        registrationsVo.setEventName(voteEvents.getName());
        registrationsVo.setVoteEventStatisVos(resultList);
        registrationsVo.setHeadcount(voteRegistrations.size());
        return registrationsVo;
    }

    //返回省份集合
    private static Result getResult(List<SysProvince> sysProvinces) {
        // 构建省份 ID 到省份名称的映射
        Map<String, String> provinceNameMap = new HashMap<>();
        for (SysProvince sysProvince : sysProvinces) {
            provinceNameMap.put(sysProvince.getProvinceId(), sysProvince.getProvince());
        }
        // 初始化一个 Map，用于存储每个省份的报名人数
        Map<String, Integer> provinceCountMap = new HashMap<>();
        return new Result(provinceNameMap, provinceCountMap);
    }

    private static class Result {
        public final Map<String, String> provinceNameMap;
        public final Map<String, Integer> provinceCountMap;

        public Result(Map<String, String> provinceNameMap, Map<String, Integer> provinceCountMap) {
            this.provinceNameMap = provinceNameMap;
            this.provinceCountMap = provinceCountMap;
        }
    }

    @Override
    public VoteEventSubmissionVo getEventSubmission(Long eventId) {
        // 1. 查询指定 eventId 的所有工作设置
        LambdaQueryWrapper<VoteEventWorkSettings> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(VoteEventWorkSettings::getEventId, eventId);
        List<VoteEventWorkSettings> workSettings = voteEventWorkSettingsMapper.selectList(wrapper);
        // 获取所有 workSettingId 列表
        List<Long> workSettingIds = workSettings.stream()
                .map(VoteEventWorkSettings::getId)
                .collect(Collectors.toList());
        // 如果没有工作设置，则直接返回空结果
        if (workSettingIds.isEmpty()) {
            return new VoteEventSubmissionVo();
        }
        // 2. 一次性查询所有该活动下的作品，并按 workSettingId 分组
        LambdaQueryWrapper<VoteSubmission> submissionWrapper = new LambdaQueryWrapper<>();
        submissionWrapper.eq(VoteSubmission::getEventId, eventId)
                .in(VoteSubmission::getWorkSettingsId, workSettingIds);
        List<VoteSubmission> submissions = voteSubmissionMapper.selectList(submissionWrapper);
        // 3. 获取用户的报名省份信息
        List<Long> userIds = submissions.stream()
                .map(VoteSubmission::getUserId)
                .collect(Collectors.toList());
        if (userIds.size() == 0) {
            return new VoteEventSubmissionVo();
        }
        // 查询报名记录
        LambdaQueryWrapper<VoteRegistrations> registrationWrapper = new LambdaQueryWrapper<>();
        registrationWrapper.in(VoteRegistrations::getUid, userIds);
        List<VoteRegistrations> registrations = voteRegistrationsMapper.selectList(registrationWrapper);
        // 4. 获取省份信息
        Map<String, String> provinceMap = getStringStringMap();
        // 5. 按 workSettingId 进行分组统计
        Map<Long, Long> submissionCountMap = submissions.stream()
                .collect(Collectors.groupingBy(VoteSubmission::getWorkSettingsId, Collectors.counting()));
        // 6. 按用户省份进行分组
        Map<String, List<VoteSubmission>> provinceSubmissionMap = new HashMap<>();
        for (VoteSubmission submission : submissions) {
            Long userId = submission.getUserId();
            registrations.stream()
                    .filter(reg -> reg.getUid().equals(userId))
                    .map(VoteRegistrations::getProvinceId)
                    .findFirst().ifPresent(provinceId -> provinceSubmissionMap
                            .computeIfAbsent(provinceId, k -> new ArrayList<>())
                            .add(submission));
        }
        // 7. 构造结果集合
        List<VoteProvinceDateVo> voteProvinceDateVos = new ArrayList<>();
        for (Map.Entry<String, List<VoteSubmission>> entry : provinceSubmissionMap.entrySet()) {
            String provinceId = entry.getKey();
            List<VoteSubmission> provinceSubmissions = entry.getValue();
            // 获取省份名称
            String provinceName = provinceMap.get(provinceId);

            // 按 workSettingId 统计每个 alias 的作品数量
            List<VoteWorkTypeStatsVo> voteWorkTypeStatsVos = new ArrayList<>();
            for (VoteEventWorkSettings setting : workSettings) {
                String alias = setting.getAlias();
                Long workSettingId = setting.getId();
                long count = provinceSubmissions.stream()
                        .filter(submission -> submission.getWorkSettingsId().equals(workSettingId))
                        .count();
                // 封装统计结果
                VoteWorkTypeStatsVo statsVo = new VoteWorkTypeStatsVo();
                statsVo.setAlias(alias);
                statsVo.setCount((int) count);
                voteWorkTypeStatsVos.add(statsVo);
            }
            // 封装省份数据
            VoteProvinceDateVo provinceDateVo = new VoteProvinceDateVo();
            provinceDateVo.setProvince(provinceName);
            provinceDateVo.setVoteWorkTypeStatsVos(voteWorkTypeStatsVos);
            voteProvinceDateVos.add(provinceDateVo);
        }
        // 构造 workSettingStats 数据
        List<VoteWorkTypeStatsVo> workSettingStatsVos = new ArrayList<>();
        for (VoteEventWorkSettings setting : workSettings) {
            Long workSettingId = setting.getId();
            String alias = setting.getAlias();
            Long count = submissionCountMap.getOrDefault(workSettingId, 0L);
            // 封装 WorkSettingStatsVo 对象
            VoteWorkTypeStatsVo statsVo = new VoteWorkTypeStatsVo();
            statsVo.setAlias(alias);
            statsVo.setCount(count.intValue());
            workSettingStatsVos.add(statsVo);
        }
        // 8. 构建返回的 VoteEventSubmissionVo 对象
        VoteEventSubmissionVo result = new VoteEventSubmissionVo();
        result.setSubmissionCount(submissions.size());
        result.setVoteProvinceDateVos(voteProvinceDateVos);
        result.setVoteWorkTypeStatsVos(workSettingStatsVos);
        return result;
    }


    /**
     * 专家评选任务统计
     */
    @Override
    public VoteEventOverviewVo getEventCategories(Long eventId) {
        VoteEventOverviewVo vo = new VoteEventOverviewVo();  // 封装前端返回类
        List<VoteCategoryVo> categoriesList = getCategoriesList(eventId);
        vo.setCategories(categoriesList);
        return vo;
    }

    /**
     * 活动人数导出
     */
    @Override
    public void getExport(HttpServletResponse response, Long eventId) {
        // 1. 获取活动信息
        VoteEvents voteEvents = voteEventsMapper.selectById(eventId);
        if (voteEvents == null) {
            throw new ServiceException("该活动不存在！");
        }
        Map<String, String> provinceMap = getStringStringMap();
        // 3. 获取注册数据
        LambdaQueryWrapper<VoteRegistrations> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(VoteRegistrations::getEventId, eventId);
        List<VoteRegistrations> registrationsList = voteRegistrationsMapper.selectList(wrapper);
        // 4. 封装导出数据
        List<VoteRegistrationExport> exports = registrationsList.stream()
                .map(registrations -> {
                    VoteRegistrationExport export = new VoteRegistrationExport();
                    export.setName(registrations.getName())
                            .setPhoneNumber(registrations.getPhone())
                            .setBelongRegion(registrations.getBelongRegion())
                            .setRegistrationTime(registrations.getCreatedAt())
                            .setProvinces(provinceMap.getOrDefault(registrations.getProvinceId(), "未知"));
                    // 获取幼儿园名称
                    SysBranchKindergartenDto data = remoteBranchKindergartenService.getById(registrations.getLocationId()).getData();
                    export.setKindergarten(data.getKindergartenName());
                    return export;
                })
                .collect(Collectors.toList());
        // 5. 按省份分组
        Map<String, List<VoteRegistrationExport>> groupByProvinces = exports.stream()
                .collect(Collectors.groupingBy(VoteRegistrationExport::getProvinces));
        // 6. 设置表头和数据提取函数
        List<String> headers = Arrays.asList("姓名", "手机号", "省市区", "幼儿园", "报名时间");
        Function<VoteRegistrationExport, Object[]> dataExtractor = registration -> new Object[]{
                registration.getName(),
                registration.getPhoneNumber(),
                registration.getBelongRegion(),
                registration.getKindergarten(),
                formatDate(registration.getRegistrationTime())
        };
        // 7. 导出 Excel
        ExcelUtil<VoteRegistrationExport> excelUtil = new ExcelUtil<>(VoteRegistrationExport.class);
        excelUtil.exportExcels(response, groupByProvinces, headers, dataExtractor, "报名人数数据" + new Date());
    }

    private Map<String, String> getStringStringMap() {
        // 2. 获取所有省份信息
        List<SysProvince> sysProvinces = remoteSysProvinceService.getProvince().getData();
        return sysProvinces.stream()
                .collect(Collectors.toMap(SysProvince::getProvinceId, SysProvince::getProvince));
    }


    /**
     * 作品数导出
     */
    @Override
    public void getWorksExport(HttpServletResponse response, Long eventId) {
        // 1. 查询指定 eventId 的所有作品
        LambdaQueryWrapper<VoteSubmission> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(VoteSubmission::getEventId, eventId);
        List<VoteSubmission> voteSubmissions = voteSubmissionMapper.selectList(wrapper);
        // 如果没有作品，抛出异常
        if (voteSubmissions.isEmpty()) {
            throw new ServiceException("导出失败！该活动下没有作品");
        }
        // 2. 获取所有用户的省份信息
        List<Long> userIds = voteSubmissions.stream()
                .map(VoteSubmission::getUserId)
                .distinct()
                .collect(Collectors.toList());
        // 查询所有相关的报名记录
        LambdaQueryWrapper<VoteRegistrations> registrationWrapper = new LambdaQueryWrapper<>();
        registrationWrapper.in(VoteRegistrations::getUid, userIds);
        List<VoteRegistrations> registrations = voteRegistrationsMapper.selectList(registrationWrapper);
        //查询该活动的所有类型别称
        LambdaQueryWrapper<VoteEventWorkSettings> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(VoteEventWorkSettings::getEventId, eventId);
        List<VoteEventWorkSettings> workSettings = voteEventWorkSettingsMapper.selectList(queryWrapper);
        //类型集合
        Map<Long, String> aliasMap = workSettings.stream()
                .collect(Collectors.toMap(VoteEventWorkSettings::getId, VoteEventWorkSettings::getAlias));
        // 3. 按省份分组数据
        Map<String, List<VoteSubmission>> submissionsByProvince = new HashMap<>();
        Map<Long, String> userIdToProvince = registrations.stream()
                .collect(Collectors.toMap(VoteRegistrations::getUid, VoteRegistrations::getProvinceId));
        for (VoteSubmission submission : voteSubmissions) {
            String provinceId = userIdToProvince.get(submission.getUserId());
            if (provinceId != null) {
                submissionsByProvince.computeIfAbsent(provinceId, k -> new ArrayList<>()).add(submission);
            }
        }
        // 4. 获取省份名称
        Map<String, String> provinceMap = getStringStringMap();
        // 6. 遍历分组数据，为每个省份创建一个 Sheet
        for (Map.Entry<String, List<VoteSubmission>> entry : submissionsByProvince.entrySet()) {
            String provinceId = entry.getKey();
            List<VoteSubmission> submissions = entry.getValue();
            // 准备数据
            List<VoteSubmissionExport> exports = submissions.stream()
                    .map(submission -> {
                        VoteSubmissionExport export = new VoteSubmissionExport();
                        export.setName(submission.getName());
                        export.setAils(aliasMap.getOrDefault(submission.getWorkSettingsId(), "未知类型"));
                        export.setCommitName(submission.getCommitName());
                        LambdaQueryWrapper<VoteRegistrations> lambdaQueryWrapper = new LambdaQueryWrapper<>();
                        lambdaQueryWrapper.eq(VoteRegistrations::getEventId, eventId).eq(VoteRegistrations::getUid, submission.getUserId());
                        VoteRegistrations voteRegistrations = voteRegistrationsMapper.selectOne(lambdaQueryWrapper);
                        export.setBelongRegion(voteRegistrations.getBelongRegion());
                        SysBranchKindergartenDto data = remoteBranchKindergartenService.getById(voteRegistrations.getLocationId()).getData();
                        export.setKindergarten(data.getKindergartenName());
                        export.setKindergarten(data.getKindergartenName());
                        export.setProvinces(provinceMap.getOrDefault(provinceId, "未知省份"));
                        export.setCommitTime(submission.getCreateAt());
                        return export;
                    })
                    .collect(Collectors.toList());
            // 5. 按省份分组
            Map<String, List<VoteSubmissionExport>> groupByProvinces = exports.stream()
                    .collect(Collectors.groupingBy(VoteSubmissionExport::getProvinces));
            // 6. 设置表头和数据提取函数
            List<String> headers = Arrays.asList("作品名称", "类型别名", "提交人", "省市", "幼儿园", "提交时间");
            Function<VoteSubmissionExport, Object[]> dataExtractor = submissionExport -> new Object[]{
                    submissionExport.getName(),
                    submissionExport.getAils(),
                    submissionExport.getCommitName(),
                    submissionExport.getProvinces(),
                    submissionExport.getKindergarten(),
                    formatDate(submissionExport.getCommitTime())
            };
            // 7. 导出数据到 Excel
            ExcelUtil<VoteSubmissionExport> voteSubmissionExportExcelUtil = new ExcelUtil<>(VoteSubmissionExport.class);
            voteSubmissionExportExcelUtil.exportExcels(response, groupByProvinces, headers, dataExtractor, "导出数据");
        }
    }


    public void getProgressExport(HttpServletResponse response, VoteEvaluationTasksDto voteEvaluationTasksDto) {
        // 1. 获取轮次信息
        VoteEventEvaluationRounds rounds = voteEventEvaluationRoundsMapper.selectById(voteEvaluationTasksDto.getRoundId());
        if (rounds == null) {
            throw new ServiceException("未找到相关轮次信息");
        }
        // 远程调用获取专家列表，减少多次调用
        List<SysPlatformUser> experts = Optional.ofNullable(remotePlatformUserService.processExpertList().getData())
                .orElse(Collections.emptyList());
        // 2. 获取轮次中的所有评委 ID
        String[] judges = rounds.getJudges().split(",");
        List<String> judgeIds = Arrays.asList(judges);
        // 3. 获取专家评分任务
        LambdaQueryWrapper<VoteEvaluationTasks> wrapper = new LambdaQueryWrapper<>();
        wrapper.in(VoteEvaluationTasks::getJudgeId, judgeIds);
        List<VoteEvaluationTasks> tasks = voteEvaluationTasksMapper.selectList(wrapper); // 专家任务
        List<Long> submissionIds = tasks.stream()
                .map(VoteEvaluationTasks::getSubmissionId)
                .collect(Collectors.toList()); // 专家任务中的作品ID
        // 4. 获取作品详情
        LambdaQueryWrapper<VoteSubmission> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.in(VoteSubmission::getId, submissionIds)
                .eq(VoteSubmission::getEventId, voteEvaluationTasksDto.getEventId())
                .eq(VoteSubmission::getWorkSettingsId, voteEvaluationTasksDto.getWorkSettingsId())
                .eq(VoteSubmission::getRoundId, voteEvaluationTasksDto.getRoundId());
        List<VoteSubmission> submissions = voteSubmissionMapper.selectList(queryWrapper); // 获取作品详情
        //5.封装数据
        List<VoteTaskExport> taskExports = submissions.stream().map(sub -> {
            VoteTaskExport taskExport = new VoteTaskExport();
            taskExport.setSubmissionName(sub.getName())
                    .setCommitName(sub.getCommitName())
                    .setSubmissionId(sub.getId());
            switch (sub.getAppraiseStatus()) {
                case WAITING_0:
                    taskExport.setStatus(WAITING);
                    break;
                case CURRENTLY_1:
                    taskExport.setStatus(CURRENTLY);
                    break;
                case ACCOMPLISH_2:
                    taskExport.setStatus(ACCOMPLISH);
                    break;
                default:
                    taskExport.setStatus(FORBIDDEN);
                    break;
            }
            LambdaQueryWrapper<VoteRegistrations> lambdaQueryWrapper = new LambdaQueryWrapper<>();
            lambdaQueryWrapper.eq(VoteRegistrations::getEventId, voteEvaluationTasksDto.getEventId()).eq(VoteRegistrations::getUid, sub.getUserId());
            VoteRegistrations voteRegistrations = voteRegistrationsMapper.selectOne(lambdaQueryWrapper);
            taskExport.setBelongRegion(voteRegistrations.getBelongRegion());
            SysBranchKindergartenDto data = remoteBranchKindergartenService.getById(voteRegistrations.getLocationId()).getData();
            taskExport.setKindergarten(data.getKindergartenName());
            LambdaQueryWrapper<VoteEventRegistrationWorkScores> workScoresLambdaQueryWrapper = new LambdaQueryWrapper<>();
            workScoresLambdaQueryWrapper.eq(VoteEventRegistrationWorkScores::getSubmissionId, sub.getId())
                    .eq(VoteEventRegistrationWorkScores::getEventId, sub.getEventId())
                    .eq(VoteEventRegistrationWorkScores::getWorkSettingsId, sub.getWorkSettingsId());
            VoteEventRegistrationWorkScores scores = voteEventRegistrationWorkScoresMapper.selectOne(workScoresLambdaQueryWrapper);
            if (scores == null) {
                taskExport.setScore("0");
                taskExport.setCreateTime(null);
            } else {
                taskExport.setScore(String.valueOf(scores.getScore()));
                taskExport.setCreateTime(scores.getCreatedAt());
            }
            return taskExport;
        }).collect(Collectors.toList());
        // 5. 根据专家 ID 对任务进行分组
        // 构建专家 ID 到名字的映射
        Map<String, String> expertIdToNameMap = experts.stream()
                .collect(Collectors.toMap(
                        expert -> String.valueOf(expert.getUserId()),
                        SysPlatformUser::getNickName
                ));
        // 5. 根据专家 ID 对任务进行分组，并将专家 ID 转换为专家名字
        Map<String, List<VoteTaskExport>> groupedByJudge = tasks.stream()
                .collect(Collectors.groupingBy(task -> {
                            // 根据专家 ID 从 Map 中获取名字
                            String judgeName = expertIdToNameMap.get(task.getJudgeId());
                            return judgeName != null ? judgeName : "未知专家"; // 如果找不到专家名，返回默认值
                        },
                        Collectors.collectingAndThen(
                                Collectors.mapping(task ->
                                                taskExports.stream()
                                                        .filter(submission -> submission.getSubmissionId().equals(task.getSubmissionId()))
                                                        .findFirst()
                                                        .orElse(null),
                                        Collectors.toList()
                                ),
                                list -> list.stream().filter(Objects::nonNull).collect(Collectors.toList()) // 过滤掉 null
                        )));
        // 6. 设置表头和数据提取函数
        List<String> headers = Arrays.asList("作品名称", "省份", "幼儿园", "提交人", "评分状态", "总分", "评分时间");
        Function<VoteTaskExport, Object[]> dataExtractor = submissionExport -> new Object[]{
                submissionExport.getSubmissionName(),
                submissionExport.getBelongRegion(),
                submissionExport.getKindergarten(),
                submissionExport.getCommitName(),
                submissionExport.getStatus(),
                submissionExport.getScore(),
                formatDate(submissionExport.getCreateTime())
        };
        ExcelUtil<VoteTaskExport> excelUtil = new ExcelUtil<>(VoteTaskExport.class);
        excelUtil.exportExcels(response, groupedByJudge, headers, dataExtractor, "导出数据");
    }


    /**
     * 晋级 评奖导出
     */
    @Override
    public void getPromotedAwardExport(HttpServletResponse response, VoteEvaluationTasksDto voteEvaluationTasksDto) {
        // 1. 获取轮次信息
        VoteEventEvaluationRounds rounds = voteEventEvaluationRoundsMapper.selectById(voteEvaluationTasksDto.getRoundId());
        if (rounds == null) {
            throw new ServiceException("未找到相关轮次信息");
        }
        // 2. 查询赛区信息
        LambdaQueryWrapper<VoteEventCompetitionZones> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(VoteEventCompetitionZones::getEventId, voteEvaluationTasksDto.getEventId())
                .eq(VoteEventCompetitionZones::getWorkSettingsId, voteEvaluationTasksDto.getWorkSettingsId())
                .eq(VoteEventCompetitionZones::getRoundId, voteEvaluationTasksDto.getRoundId());
        List<VoteEventCompetitionZones> zones = voteEventCompetitionZonesMapper.selectList(wrapper);

        // 3. 获取该活动下所有赛区包含的所有省份集合
        List<String> province = new ArrayList<>();
        zones.forEach(item -> province.add(item.getProvinces()));
        List<String> provinceIds = province.stream()
                .map(s -> s.split(","))       // 将每个字符串按逗号分割为数组
                .flatMap(Arrays::stream)      // 将多个数组展平为一个字符串流
                .collect(Collectors.toList()); // 将流收集为List

        // 4. 根据省份编码查询在该省份报名的用户
        LambdaQueryWrapper<VoteRegistrations> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.in(VoteRegistrations::getProvinceId, provinceIds)
                .eq(VoteRegistrations::getEventId, voteEvaluationTasksDto.getEventId());
        List<VoteRegistrations> registrations = voteRegistrationsMapper.selectList(queryWrapper);

        // 5. 根据报名用户查询作品
        List<Long> userIds = registrations.stream()
                .map(VoteRegistrations::getUid)
                .collect(Collectors.toList());
        LambdaQueryWrapper<VoteSubmission> submissionWrapper = new LambdaQueryWrapper<>();
        submissionWrapper.in(VoteSubmission::getUserId, userIds)
                .eq(VoteSubmission::getEventId, voteEvaluationTasksDto.getEventId())
                .eq(VoteSubmission::getWorkSettingsId, voteEvaluationTasksDto.getWorkSettingsId())
                .eq(VoteSubmission::getRoundId, voteEvaluationTasksDto.getRoundId())
                .eq(VoteSubmission::getPromoteStatus, "0"); // 只查询已晋级的作品
        List<VoteSubmission> submissions = voteSubmissionMapper.selectList(submissionWrapper);
        // 6. 封装导出数据
        List<VoteZonesExport> exportData = submissions.stream()
                .map(sub -> {
                    VoteZonesExport zonesExport = new VoteZonesExport();
                    zonesExport.setSubmissionName(sub.getName())
                            .setCommitName(sub.getCommitName());
                    // 查询报名信息
                    LambdaQueryWrapper<VoteRegistrations> regWrapper = new LambdaQueryWrapper<>();
                    regWrapper.eq(VoteRegistrations::getEventId, voteEvaluationTasksDto.getEventId())
                            .eq(VoteRegistrations::getUid, sub.getUserId());
                    VoteRegistrations voteRegistrations = voteRegistrationsMapper.selectOne(regWrapper);
                    zonesExport.setBelongRegion(voteRegistrations.getBelongRegion());
                    zonesExport.setProvinces(voteRegistrations.getProvinceId());
                    // 查询幼儿园信息
                    SysBranchKindergartenDto data = remoteBranchKindergartenService.getById(voteRegistrations.getLocationId()).getData();
                    zonesExport.setKindergarten(data.getKindergartenName());
                    // 查询评分
                    LambdaQueryWrapper<VoteEventRegistrationWorkScores> workScoresWrapper = new LambdaQueryWrapper<>();
                    workScoresWrapper.eq(VoteEventRegistrationWorkScores::getSubmissionId, sub.getId())
                            .eq(VoteEventRegistrationWorkScores::getEventId, sub.getEventId())
                            .eq(VoteEventRegistrationWorkScores::getWorkSettingsId, sub.getWorkSettingsId());
                    VoteEventRegistrationWorkScores scores = voteEventRegistrationWorkScoresMapper.selectOne(workScoresWrapper);
                    zonesExport.setScore(scores == null ? "0" : String.valueOf(scores.getScore()));
                    if ("award".equals(rounds.getType())) {
                        if (!"0".equals(sub.getAward())) {
                            zonesExport.setAwardName(sub.getAward());
                        }
                    }
                    return zonesExport;
                }).collect(Collectors.toList());
        // 7. 根据赛区名分组
        Map<String, List<VoteZonesExport>> groupedByZone = exportData.stream()
                .collect(Collectors.groupingBy(export -> zones.stream()
                        .filter(zone -> zone.getProvinces().contains(export.getProvinces()))
                        .findFirst()
                        .map(VoteEventCompetitionZones::getName)
                        .orElse("未知赛区")));
        // 6. 设置表头和数据提取函数
        List<String> headers = Arrays.asList("作品名", "省市", "幼儿园", "提交人", "总分");
        if ("award".equals(rounds.getType())) {
            headers.add("奖项");
        }
        Function<VoteZonesExport, Object[]> dataExtractor = expor -> new Object[]{
                expor.getSubmissionName(),
                expor.getBelongRegion(),
                expor.getKindergarten(),
                expor.getCommitName(),
                expor.getScore(),
        };
        ExcelUtil<VoteZonesExport> excelUtil = new ExcelUtil<>(VoteZonesExport.class);
        excelUtil.exportExcels(response, groupedByZone, headers, dataExtractor, "导出数据");
    }


    private List<VoteCategoryVo> getCategoriesList(Long eventId) {
        List<VoteCategoryVo> categoryVos = new ArrayList<>();

        // 远程调用获取专家列表，减少多次调用
        List<SysPlatformUser> experts = Optional.ofNullable(remotePlatformUserService.processExpertList().getData())
                .orElse(Collections.emptyList());

        // 查询所有轮次配置项，避免每个轮次单独查询
        List<VoteEventWorkSettings> workSettings = voteEventWorkSettingsMapper.selectList(
                new LambdaQueryWrapper<VoteEventWorkSettings>().eq(VoteEventWorkSettings::getEventId, eventId)
        );
        if (workSettings.size() == 0) {
            return categoryVos;
        }
        // 提前查询出所有评选轮次，避免多次 `selectById` 调用
        Set<Long> allRoundIds = workSettings.stream()
                .flatMap(item -> Arrays.stream(item.getRoundsId().split(",")))
                .map(Long::parseLong)
                .collect(Collectors.toSet());
        if (allRoundIds.isEmpty()) {
            return categoryVos;
        }
        //构建该活动设置伦次下的所有作品
        LambdaQueryWrapper<VoteSubmission> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(VoteSubmission::getEventId, eventId)
                .in(VoteSubmission::getRoundId, allRoundIds);
        List<VoteSubmission> voteSubmissions = voteSubmissionMapper.selectList(wrapper);
        // 批量查询轮次数据
        List<VoteEventEvaluationRounds> evaluationRoundsList = voteEventEvaluationRoundsMapper.selectBatchIds(allRoundIds);
        // 对每个工作设置进行处理
        workSettings.forEach(item -> {
            VoteCategoryVo categoryVo = new VoteCategoryVo();
            categoryVo.setWorkSettingsId(item.getId());
            categoryVo.setCategoryName(item.getAlias());
            String[] roundIds = item.getRoundsId().split(",");
            List<VoteRoundVo> roundVos = new ArrayList<>(); // 存储该类别的多个轮次信息
            Arrays.stream(roundIds).forEach(roundIdStr -> {
                Long roundId = Long.parseLong(roundIdStr);
                VoteEventEvaluationRounds evaluationRounds = evaluationRoundsList.stream()
                        .filter(round -> round.getId().equals(roundId))
                        .findFirst()
                        .orElse(null);
                if (evaluationRounds != null) {
                    // 封装每个轮次的信息
                    VoteRoundVo roundVo = new VoteRoundVo();
                    roundVo.setRoundName(String.valueOf(evaluationRounds.getRoundNumber()));
                    roundVo.setRoundId(evaluationRounds.getId());
                    roundVo.setType((String) evaluationRounds.getType());
                    // 轮次任务进度
                    String[] judges = evaluationRounds.getJudges().split(",");
                    List<String> judgesIds = Arrays.asList(judges);
                    Integer total = getJudgesTaskTotal(judgesIds, false, voteSubmissions); // 总任务数
                    Integer completed = getJudgesTaskTotal(judgesIds, true, voteSubmissions); // 完成任务数
                    roundVo.setTotalProgress(String.valueOf(total));
                    roundVo.setOkProgress(String.valueOf(completed));
                    Integer countOfAdvancedSubmissions = getCountOfAdvancedSubmissions(judgesIds, voteSubmissions);
                    roundVo.setReviewed(Optional.of(countOfAdvancedSubmissions).orElse(0));
                    // 晋级状态，如果是评奖轮次，也可以设置相关的奖项信息
                    if ("awards".equals(evaluationRounds.getType())) {
                        Integer promote = getAwardsSubmission(judgesIds); // 已晋级作品数
                        // 如果 promote 为 null，设置为 0
                        roundVo.setReviewed(Optional.of(promote).orElse(0));
                    }
                    // 专家评分进度
                    List<VoteJudgeProgressVo> judgeProgressVos = getJudgeProgressList(judgesIds, experts, voteSubmissions);
                    roundVo.setJudgeProgressVos(judgeProgressVos);

                    // 将当前轮次信息添加到类别的轮次列表中
                    roundVos.add(roundVo);
                }
            });
            // 将轮次信息列表添加到类别中
            categoryVo.setRounds(roundVos);
            categoryVos.add(categoryVo);
        });
        return categoryVos;
    }


    /**
     * 获取专家评分进度
     */
    private List<VoteJudgeProgressVo> getJudgeProgressList(List<String> judgeIds, List<SysPlatformUser> experts, List<VoteSubmission> voteSubmissions) {
        List<VoteJudgeProgressVo> judgeProgressVos = new ArrayList<>();
        List<Long> submissionIds = voteSubmissions.stream().map(VoteSubmission::getId).collect(Collectors.toList());//该活动轮次下的作品
        // 批量查询所有专家的任务
        List<VoteEvaluationTasks> allTasks = voteEvaluationTasksMapper.selectList(
                new LambdaQueryWrapper<VoteEvaluationTasks>().in(VoteEvaluationTasks::getJudgeId, judgeIds).in(VoteEvaluationTasks::getSubmissionId, submissionIds)
        );
        // 按专家ID分组任务列表
        Map<String, List<VoteEvaluationTasks>> tasksGroupedByJudge = allTasks.stream()
                .collect(Collectors.groupingBy(VoteEvaluationTasks::getJudgeId));
        // 根据专家ID遍历，统计进度
        judgeIds.forEach(judgeId -> {
            VoteJudgeProgressVo judgeProgressVo = new VoteJudgeProgressVo();
            List<VoteEvaluationTasks> tasks = tasksGroupedByJudge.getOrDefault(judgeId, Collections.emptyList());
            // 总任务数
            judgeProgressVo.setTotal(tasks.size());
            // 已完成任务数
            long completedTasks = tasks.stream().filter(task -> task.getStatus().equals(1)).count();
            judgeProgressVo.setCurrent((int) completedTasks);
            // 查找专家名称
            String expertName = experts.stream()
                    .filter(expert -> judgeId.equals(expert.getUserId().toString()))
                    .map(SysPlatformUser::getNickName)
                    .findFirst()
                    .orElse(null);
            judgeProgressVo.setJudgeName(expertName);
            judgeProgressVos.add(judgeProgressVo);
        });

        return judgeProgressVos;
    }

    /**
     * 获取晋级作品数
     */
    public int getCountOfAdvancedSubmissions(List<String> judgeIds, List<VoteSubmission> voteSubmissions) {
        if (judgeIds == null || judgeIds.isEmpty()) {
            return 0;
        }
        List<Long> suIds = voteSubmissions.stream().map(VoteSubmission::getId).collect(Collectors.toList());//该活动轮次下的作品
        List<Long> submissionIds = voteEvaluationTasksMapper.selectList(
                        new LambdaQueryWrapper<VoteEvaluationTasks>().in(VoteEvaluationTasks::getJudgeId, judgeIds)
                                .eq(VoteEvaluationTasks::getStatus, "1").in(VoteEvaluationTasks::getSubmissionId, suIds))
                .stream()
                .map(VoteEvaluationTasks::getSubmissionId)
                .collect(Collectors.toList());
        if (submissionIds.isEmpty()) {
            return 0;
        }
        return voteSubmissionMapper.selectCount(
                new LambdaQueryWrapper<VoteSubmission>().in(VoteSubmission::getId, submissionIds)
                        .eq(VoteSubmission::getPromoteStatus, PROMOTE_0)
        ).intValue();
    }

    public int getAwardsSubmission(List<String> judgeIds) {
        if (judgeIds == null || judgeIds.isEmpty()) {
            return 0;
        }
        List<Long> submissionIds = voteEvaluationTasksMapper.selectList(
                        new LambdaQueryWrapper<VoteEvaluationTasks>().in(VoteEvaluationTasks::getJudgeId, judgeIds)
                                .eq(VoteEvaluationTasks::getStatus, "1"))
                .stream()
                .map(VoteEvaluationTasks::getSubmissionId)
                .collect(Collectors.toList());
        if (submissionIds.isEmpty()) {
            return 0;
        }
        return voteSubmissionMapper.selectCount(
                new LambdaQueryWrapper<VoteSubmission>().in(VoteSubmission::getId, submissionIds)
                        .ne(VoteSubmission::getAward, PROMOTE_0)//不等于0 就是已经被评奖的作品
        ).intValue();
    }


    /**
     * 计算专家任务总数或完成数
     */
    private Integer getJudgesTaskTotal(List<String> judgeIds, boolean isCompleted, List<VoteSubmission> voteSubmissions) {
        if (judgeIds == null || judgeIds.isEmpty()) {
            return 0;
        }
        List<Long> submissionIds = voteSubmissions.stream().map(VoteSubmission::getId).collect(Collectors.toList());//该活动轮次下的作品
        LambdaQueryWrapper<VoteEvaluationTasks> wrapper = new LambdaQueryWrapper<>();
        wrapper.in(VoteEvaluationTasks::getJudgeId, judgeIds).in(VoteEvaluationTasks::getSubmissionId, submissionIds);
        if (isCompleted) {
            wrapper.eq(VoteEvaluationTasks::getStatus, "1");  // 已完成状态
        }
        return voteEvaluationTasksMapper.selectCount(wrapper).intValue();
    }


}
