package com.njcn.rdms.module.project.dal.mysql.project.task;

import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.njcn.rdms.framework.common.pojo.PageResult;
import com.njcn.rdms.framework.mybatis.core.mapper.BaseMapperX;
import com.njcn.rdms.framework.mybatis.core.query.LambdaQueryWrapperX;
import com.njcn.rdms.module.project.controller.admin.project.task.vo.ProjectTaskStatusBoardReqVO;
import com.njcn.rdms.module.project.controller.admin.project.task.vo.ProjectTaskPageReqVO;
import com.njcn.rdms.module.project.controller.admin.project.task.vo.aggregate.ProjectTaskAggregatePageReqVO;
import com.njcn.rdms.module.project.controller.admin.project.task.vo.aggregate.ProjectTaskAggregateStatusBoardReqVO;
import com.njcn.rdms.module.project.dal.dataobject.project.task.ProjectTaskDO;
import lombok.Data;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Map;

@Mapper
public interface ProjectTaskMapper extends BaseMapperX<ProjectTaskDO> {

    default ProjectTaskDO selectByProjectIdAndExecutionIdAndId(Long projectId, Long executionId, Long taskId) {
        return selectOne(new LambdaQueryWrapperX<ProjectTaskDO>()
                .eq(ProjectTaskDO::getProjectId, projectId)
                .eq(ProjectTaskDO::getExecutionId, executionId)
                .eq(ProjectTaskDO::getId, taskId));
    }

    default PageResult<ProjectTaskDO> selectPageByExecutionId(Long projectId, Long executionId,
                                                              ProjectTaskPageReqVO reqVO) {
        Page<ProjectTaskDO> page = new Page<>(reqVO.getPageNo(), reqVO.getPageSize());
        IPage<ProjectTaskDO> ipage = doSelectPageByExecutionId(projectId, executionId, reqVO, page);
        return new PageResult<>(ipage.getRecords(), ipage.getTotal());
    }

    /**
     * 执行内任务分页查询。
     * <p>SQL 用 @Select 显式表别名 t，EXISTS 子查询用 t.id 关联 rdms_task_assignee；
     * 与项目级 aggregate page 同款风格。
     *
     * <p>involveUserId 非空时附加 (owner_id = involveUserId OR 活跃协办含 involveUserId)；
     * 与 ownerId 文档标注「二选一」由前端保证（不做后端互斥校验）。
     */
    @Select("""
            <script>
            SELECT t.*
            FROM rdms_task t
            <where>
                t.deleted = b'0'
                AND t.project_id = #{projectId}
                AND t.execution_id = #{executionId}
                <if test="reqVO.parentTaskId != null">
                    AND t.parent_task_id = #{reqVO.parentTaskId}
                </if>
                <if test="reqVO.ownerId != null">
                    AND t.owner_id = #{reqVO.ownerId}
                </if>
                <if test="reqVO.statusCode != null and reqVO.statusCode != ''">
                    AND t.status_code = #{reqVO.statusCode}
                </if>
                <if test="reqVO.priority != null and reqVO.priority != ''">
                    AND t.priority = #{reqVO.priority}
                </if>
                <if test="reqVO.updateTime != null and reqVO.updateTime.length == 2">
                    AND t.update_time BETWEEN #{reqVO.updateTime[0]} AND #{reqVO.updateTime[1]}
                </if>
                <if test="reqVO.keyword != null and reqVO.keyword != ''">
                    AND t.task_title LIKE CONCAT('%', #{reqVO.keyword}, '%')
                </if>
                <if test="reqVO.involveUserId != null">
                    AND (
                        t.owner_id = #{reqVO.involveUserId}
                        OR EXISTS (
                            SELECT 1 FROM rdms_task_assignee a
                            WHERE a.task_id = t.id
                              AND a.user_id = #{reqVO.involveUserId}
                              AND a.removed_at IS NULL
                              AND a.deleted = b'0'
                        )
                    )
                </if>
            </where>
            ORDER BY t.parent_task_id ASC, t.priority ASC, t.create_time DESC, t.id DESC
            </script>
            """)
    IPage<ProjectTaskDO> doSelectPageByExecutionId(
            @Param("projectId") Long projectId,
            @Param("executionId") Long executionId,
            @Param("reqVO") ProjectTaskPageReqVO reqVO,
            Page<ProjectTaskDO> page);

    default int updateStatusByIdAndStatus(Long id, String fromStatus, String toStatus, String lastStatusReason) {
        ProjectTaskDO update = new ProjectTaskDO();
        update.setStatusCode(toStatus);
        update.setLastStatusReason(lastStatusReason);
        return update(update, new LambdaQueryWrapperX<ProjectTaskDO>()
                .eq(ProjectTaskDO::getId, id)
                .eq(ProjectTaskDO::getStatusCode, fromStatus));
    }

    /**
     * 软删任务（按状态 CAS）。仅在 statusCode 与传入 fromStatus 匹配时才标 deleted=1。
     * 返回 1 表示成功；返回 0 视为并发修改。
     */
    default int deleteByIdAndStatus(Long id, String fromStatus) {
        return delete(new LambdaQueryWrapperX<ProjectTaskDO>()
                .eq(ProjectTaskDO::getId, id)
                .eq(ProjectTaskDO::getStatusCode, fromStatus));
    }

    /**
     * 仅更新实际开始/结束日期。显式 set，允许把字段清空为 null。
     */
    default int updateActualDatesById(Long id, LocalDate actualStartDate, LocalDate actualEndDate) {
        if (id == null) {
            return 0;
        }
        return update(null, new LambdaUpdateWrapper<ProjectTaskDO>()
                .set(ProjectTaskDO::getActualStartDate, actualStartDate)
                .set(ProjectTaskDO::getActualEndDate, actualEndDate)
                .eq(ProjectTaskDO::getId, id));
    }

    default Integer countChildrenNotInStatus(Long parentTaskId, List<String> terminalStatusCodes) {
        LambdaQueryWrapperX<ProjectTaskDO> queryWrapper = new LambdaQueryWrapperX<ProjectTaskDO>()
                .eq(ProjectTaskDO::getParentTaskId, parentTaskId);
        if (terminalStatusCodes != null && !terminalStatusCodes.isEmpty()) {
            queryWrapper.notIn(ProjectTaskDO::getStatusCode, terminalStatusCodes);
        }
        return Math.toIntExact(selectCount(queryWrapper));
    }

    /**
     * 统计指定执行下处于非终态的任务数。用于执行 complete 前置校验（要求所有任务必须终态）。
     */
    default Integer countByExecutionIdNotInStatus(Long executionId, List<String> terminalStatusCodes) {
        LambdaQueryWrapperX<ProjectTaskDO> queryWrapper = new LambdaQueryWrapperX<ProjectTaskDO>()
                .eq(ProjectTaskDO::getExecutionId, executionId);
        if (terminalStatusCodes != null && !terminalStatusCodes.isEmpty()) {
            queryWrapper.notIn(ProjectTaskDO::getStatusCode, terminalStatusCodes);
        }
        return Math.toIntExact(selectCount(queryWrapper));
    }

    /**
     * 统计指定项目下处于非终态的任务数。用于项目 complete 前置校验（TD-015，方案 B 进度维度）。
     */
    default Integer countNonTerminalByProjectId(Long projectId, List<String> terminalStatusCodes) {
        LambdaQueryWrapperX<ProjectTaskDO> queryWrapper = new LambdaQueryWrapperX<ProjectTaskDO>()
                .eq(ProjectTaskDO::getProjectId, projectId);
        if (terminalStatusCodes != null && !terminalStatusCodes.isEmpty()) {
            queryWrapper.notIn(ProjectTaskDO::getStatusCode, terminalStatusCodes);
        }
        return Math.toIntExact(selectCount(queryWrapper));
    }

    /**
     * 数指定父任务下的直接子任务（不区分状态）。用于"是否叶子任务"判定与进度汇总。
     */
    default int countChildrenByParentTaskId(Long parentTaskId) {
        if (parentTaskId == null) {
            return 0;
        }
        return Math.toIntExact(selectCount(new LambdaQueryWrapperX<ProjectTaskDO>()
                .eq(ProjectTaskDO::getParentTaskId, parentTaskId)));
    }

    /**
     * 取直接子任务的 progressRate 列表（用于父任务进度简单平均汇总）。
     * 只读取必要字段，避免拉回整行；逻辑删除的子任务由 BaseMapper 自动过滤。
     */
    default List<ProjectTaskDO> selectChildrenProgressByParentTaskId(Long parentTaskId,
                                                                     Collection<String> excludedStatusCodes) {
        if (parentTaskId == null) {
            return Collections.emptyList();
        }
        LambdaQueryWrapperX<ProjectTaskDO> queryWrapper = new LambdaQueryWrapperX<>();
        queryWrapper.select(ProjectTaskDO::getId, ProjectTaskDO::getProgressRate);
        queryWrapper.eq(ProjectTaskDO::getParentTaskId, parentTaskId);
        if (excludedStatusCodes != null && !excludedStatusCodes.isEmpty()) {
            queryWrapper.notIn(ProjectTaskDO::getStatusCode, excludedStatusCodes);
        }
        return selectList(queryWrapper);
    }

    /**
     * 执行详情进度：按当前执行下一级任务 progressRate 简单平均；无一级任务时 SQL 返回 null。
     */
    @Select("""
            <script>
            SELECT AVG(COALESCE(progress_rate, 0))
            FROM rdms_task
            WHERE deleted = b'0'
              AND project_id = #{projectId}
              AND execution_id = #{executionId}
              AND parent_task_id IS NULL
              <if test="excludedStatusCodes != null and excludedStatusCodes.size() > 0">
              AND status_code NOT IN
                <foreach collection="excludedStatusCodes" item="statusCode" open="(" separator="," close=")">#{statusCode}</foreach>
              </if>
            </script>
            """)
    BigDecimal selectRootTaskAvgProgressByExecutionId(@Param("projectId") Long projectId,
                                                      @Param("executionId") Long executionId,
                                                      @Param("excludedStatusCodes") Collection<String> excludedStatusCodes);

    /**
     * 执行分页进度：按当前页 executionId 批量聚合一级任务 progressRate，避免列表 N+1。
     */
    @Select("""
            <script>
            SELECT execution_id AS executionId, AVG(COALESCE(progress_rate, 0)) AS progressRate
            FROM rdms_task
            WHERE deleted = b'0'
              AND project_id = #{projectId}
              AND execution_id IN
                <foreach collection="executionIds" item="id" open="(" separator="," close=")">#{id}</foreach>
              AND parent_task_id IS NULL
              <if test="excludedStatusCodes != null and excludedStatusCodes.size() > 0">
              AND status_code NOT IN
                <foreach collection="excludedStatusCodes" item="statusCode" open="(" separator="," close=")">#{statusCode}</foreach>
              </if>
            GROUP BY execution_id
            </script>
            """)
    List<Map<String, Object>> selectRootTaskAvgProgressGroupByExecutionIds(
            @Param("projectId") Long projectId,
            @Param("executionIds") Collection<Long> executionIds,
            @Param("excludedStatusCodes") Collection<String> excludedStatusCodes);

    /**
     * 项目进度推算：跨执行聚合，按项目下所有根任务 progressRate 简单平均；无根任务时 SQL 返回 null。
     * 与执行口径一致（parent_task_id IS NULL + excludedStatusCodes），区别仅是不限定 execution_id。
     */
    @Select("""
            <script>
            SELECT AVG(COALESCE(progress_rate, 0))
            FROM rdms_task
            WHERE deleted = b'0'
              AND project_id = #{projectId}
              AND parent_task_id IS NULL
              <if test="excludedStatusCodes != null and excludedStatusCodes.size() > 0">
              AND status_code NOT IN
                <foreach collection="excludedStatusCodes" item="statusCode" open="(" separator="," close=")">#{statusCode}</foreach>
              </if>
            </script>
            """)
    BigDecimal selectRootTaskAvgProgressByProjectId(@Param("projectId") Long projectId,
                                                    @Param("excludedStatusCodes") Collection<String> excludedStatusCodes);

    /**
     * 执行详情完成态聚合：返回根任务总数与"已完成"数，用于 execution complete 按钮判定。
     * 与 selectRootTaskAvgProgressByExecutionId 共用同一根任务筛选口径（execution_id + parent_task_id IS NULL + excludedStatusCodes）。
     * 业务侧判定 totals > 0 && totals == completedCount 即视为"根任务全部已完成"，空集（totals = 0）按"不全部完成"处理。
     */
    @Select("""
            <script>
            SELECT COUNT(*) AS totals,
                   SUM(CASE WHEN status_code = #{completedStatusCode} THEN 1 ELSE 0 END) AS completedCount
            FROM rdms_task
            WHERE deleted = b'0'
              AND project_id = #{projectId}
              AND execution_id = #{executionId}
              AND parent_task_id IS NULL
              <if test="excludedStatusCodes != null and excludedStatusCodes.size() > 0">
              AND status_code NOT IN
                <foreach collection="excludedStatusCodes" item="statusCode" open="(" separator="," close=")">#{statusCode}</foreach>
              </if>
            </script>
            """)
    Map<String, Object> selectRootTaskCompletionStateByExecutionId(@Param("projectId") Long projectId,
                                                                   @Param("executionId") Long executionId,
                                                                   @Param("completedStatusCode") String completedStatusCode,
                                                                   @Param("excludedStatusCodes") Collection<String> excludedStatusCodes);

    /**
     * 执行分页完成态批量聚合：按 executionId 一次性返回 (totals, completedCount)，避免列表 N+1。
     * 筛选口径与 selectRootTaskCompletionStateByExecutionId 同源。
     */
    @Select("""
            <script>
            SELECT execution_id AS executionId,
                   COUNT(*) AS totals,
                   SUM(CASE WHEN status_code = #{completedStatusCode} THEN 1 ELSE 0 END) AS completedCount
            FROM rdms_task
            WHERE deleted = b'0'
              AND project_id = #{projectId}
              AND execution_id IN
                <foreach collection="executionIds" item="id" open="(" separator="," close=")">#{id}</foreach>
              AND parent_task_id IS NULL
              <if test="excludedStatusCodes != null and excludedStatusCodes.size() > 0">
              AND status_code NOT IN
                <foreach collection="excludedStatusCodes" item="statusCode" open="(" separator="," close=")">#{statusCode}</foreach>
              </if>
            GROUP BY execution_id
            </script>
            """)
    List<Map<String, Object>> selectRootTaskCompletionStateGroupByExecutionIds(
            @Param("projectId") Long projectId,
            @Param("executionIds") Collection<Long> executionIds,
            @Param("completedStatusCode") String completedStatusCode,
            @Param("excludedStatusCodes") Collection<String> excludedStatusCodes);

    /**
     * 仅更新单个任务的 progressRate，不动其他字段（避免污染 lastStatusReason 等）。
     */
    default int updateProgressRateById(Long id, BigDecimal progressRate) {
        ProjectTaskDO update = new ProjectTaskDO();
        update.setProgressRate(progressRate);
        return update(update, new LambdaQueryWrapperX<ProjectTaskDO>()
                .eq(ProjectTaskDO::getId, id));
    }

    default Integer countByProjectIdAndExecutionIdAndStatusCode(Long projectId, Long executionId,
                                                                ProjectTaskStatusBoardReqVO reqVO,
                                                                String statusCode) {
        return Math.toIntExact(doCountByProjectIdAndExecutionIdAndStatusCode(projectId, executionId, reqVO, statusCode));
    }

    /**
     * 执行内任务按状态计数（与 doSelectPageByExecutionId 同款过滤口径，含 involveUserId 协办分支）。
     */
    @Select("""
            <script>
            SELECT COUNT(*)
            FROM rdms_task t
            <where>
                t.deleted = b'0'
                AND t.project_id = #{projectId}
                AND t.execution_id = #{executionId}
                AND t.status_code = #{statusCode}
                <if test="reqVO.parentTaskId != null">
                    AND t.parent_task_id = #{reqVO.parentTaskId}
                </if>
                <if test="reqVO.ownerId != null">
                    AND t.owner_id = #{reqVO.ownerId}
                </if>
                <if test="reqVO.updateTime != null and reqVO.updateTime.length == 2">
                    AND t.update_time BETWEEN #{reqVO.updateTime[0]} AND #{reqVO.updateTime[1]}
                </if>
                <if test="reqVO.keyword != null and reqVO.keyword != ''">
                    AND t.task_title LIKE CONCAT('%', #{reqVO.keyword}, '%')
                </if>
                <if test="reqVO.involveUserId != null">
                    AND (
                        t.owner_id = #{reqVO.involveUserId}
                        OR EXISTS (
                            SELECT 1 FROM rdms_task_assignee a
                            WHERE a.task_id = t.id
                              AND a.user_id = #{reqVO.involveUserId}
                              AND a.removed_at IS NULL
                              AND a.deleted = b'0'
                        )
                    )
                </if>
            </where>
            </script>
            """)
    Long doCountByProjectIdAndExecutionIdAndStatusCode(
            @Param("projectId") Long projectId,
            @Param("executionId") Long executionId,
            @Param("reqVO") ProjectTaskStatusBoardReqVO reqVO,
            @Param("statusCode") String statusCode);

    /**
     * 收集执行下的所有任务 id（含子孙——子孙必然同 execution_id，所以一把抓即可）。
     * 用于"删除执行"时的级联软删。
     */
    default List<Long> selectIdsByExecutionId(Long executionId) {
        if (executionId == null) {
            return Collections.emptyList();
        }
        return selectList(new LambdaQueryWrapperX<ProjectTaskDO>()
                .eq(ProjectTaskDO::getExecutionId, executionId)
                .select(ProjectTaskDO::getId))
                .stream()
                .map(ProjectTaskDO::getId)
                .toList();
    }

    /**
     * 从给定任务出发，递归向下收集自身 + 所有子孙任务 id（递归 CTE）。
     * 用于"删除任务"时的级联软删。
     *
     * 任务表已逻辑删除的行不参与递归。
     */
    @Select("""
            WITH RECURSIVE descendants (id) AS (
                SELECT id FROM rdms_task
                 WHERE deleted = b'0'
                   AND id = #{taskId}
                UNION ALL
                SELECT t.id FROM rdms_task t
                  JOIN descendants d ON t.parent_task_id = d.id
                 WHERE t.deleted = b'0'
            )
            SELECT id FROM descendants
            """)
    List<Long> selectSelfAndDescendantIds(@Param("taskId") Long taskId);

    /**
     * 按 id 集合批量软删任务。@TableLogic 自动落 deleted=1。
     */
    default int deleteByIdIn(Collection<Long> ids) {
        if (ids == null || ids.isEmpty()) {
            return 0;
        }
        return delete(new LambdaQueryWrapperX<ProjectTaskDO>()
                .in(ProjectTaskDO::getId, ids));
    }

    /**
     * 执行下任务计数（用于删除预检）。
     */
    default int countByExecutionId(Long executionId) {
        if (executionId == null) {
            return 0;
        }
        return Math.toIntExact(selectCount(new LambdaQueryWrapperX<ProjectTaskDO>()
                .eq(ProjectTaskDO::getExecutionId, executionId)));
    }

    // ======================== 项目级跨执行聚合查询 ========================

    /**
     * 项目级跨执行任务分页查询。
     *
     * 语义:
     * - involveUserId 不为 null → 附加 (t.owner_id = ? OR exists active assignee user_id = ?)
     * - statusCodes 非空 → t.status_code IN (...)
     * - dueRange='overdue' 且 terminalStatusCodes 非空 → 排除终态
     */
    @Select("""
            <script>
            SELECT t.*
            FROM rdms_task t
            <where>
                t.project_id = #{projectId}
                AND t.deleted = b'0'
                <if test="reqVO.keyword != null and reqVO.keyword != ''">
                    AND t.task_title LIKE CONCAT('%', #{reqVO.keyword}, '%')
                </if>
                <if test="reqVO.executionIds != null and !reqVO.executionIds.isEmpty()">
                    AND t.execution_id IN
                    <foreach collection="reqVO.executionIds" item="eid" open="(" separator="," close=")">#{eid}</foreach>
                </if>
                <if test="reqVO.executionStatusCodes != null and !reqVO.executionStatusCodes.isEmpty()">
                    AND EXISTS (
                        SELECT 1 FROM rdms_project_execution e
                        WHERE e.id = t.execution_id
                          AND e.deleted = b'0'
                          AND e.status_code IN
                          <foreach collection="reqVO.executionStatusCodes" item="esc" open="(" separator="," close=")">#{esc}</foreach>
                    )
                </if>
                <if test="reqVO.executionInvolveUserId != null">
                    AND EXISTS (
                        SELECT 1 FROM rdms_project_execution e
                        WHERE e.id = t.execution_id
                          AND e.deleted = b'0'
                          AND (
                              e.owner_id = #{reqVO.executionInvolveUserId}
                              OR EXISTS (
                                  SELECT 1 FROM rdms_execution_assignee ea
                                  WHERE ea.execution_id = e.id
                                    AND ea.user_id = #{reqVO.executionInvolveUserId}
                                    AND ea.removed_at IS NULL
                                    AND ea.deleted = b'0'
                              )
                          )
                    )
                </if>
                <if test="reqVO.involveUserId != null">
                    AND (
                        t.owner_id = #{reqVO.involveUserId}
                        OR EXISTS (
                            SELECT 1 FROM rdms_task_assignee a
                            WHERE a.task_id = t.id
                              AND a.user_id = #{reqVO.involveUserId}
                              AND a.removed_at IS NULL
                              AND a.deleted = b'0'
                        )
                    )
                </if>
                <if test="reqVO.ownerId != null">
                    AND t.owner_id = #{reqVO.ownerId}
                </if>
                <if test="reqVO.statusCodes != null and !reqVO.statusCodes.isEmpty()">
                    AND t.status_code IN
                    <foreach collection="reqVO.statusCodes" item="sc" open="(" separator="," close=")">#{sc}</foreach>
                </if>
                <if test="reqVO.priority != null and reqVO.priority != ''">
                    AND t.priority = #{reqVO.priority}
                </if>
                <if test="reqVO.parentTaskId != null">
                    AND t.parent_task_id = #{reqVO.parentTaskId}
                </if>
                <if test="reqVO.dueRange == 'overdue'">
                    AND t.planned_end_date &lt; #{today}
                    <if test="terminalStatusCodes != null and !terminalStatusCodes.isEmpty()">
                        AND t.status_code NOT IN
                        <foreach collection="terminalStatusCodes" item="tc" open="(" separator="," close=")">#{tc}</foreach>
                    </if>
                </if>
                <if test="reqVO.dueRange == 'today'">
                    AND t.planned_end_date = #{today}
                </if>
                <if test="reqVO.dueRange == 'thisWeek'">
                    AND t.planned_end_date BETWEEN #{weekStart} AND #{weekEnd}
                </if>
                <if test="reqVO.updateTime != null and reqVO.updateTime.length == 2">
                    AND t.update_time BETWEEN #{reqVO.updateTime[0]} AND #{reqVO.updateTime[1]}
                </if>
            </where>
            <choose>
                <when test="reqVO.sortBy == 'priority'">ORDER BY t.priority</when>
                <when test="reqVO.sortBy == 'updateTime'">ORDER BY t.update_time</when>
                <when test="reqVO.sortBy == 'createTime'">ORDER BY t.create_time</when>
                <otherwise>ORDER BY t.planned_end_date</otherwise>
            </choose>
            <choose>
                <when test="reqVO.sortOrder == 'desc'">DESC</when>
                <otherwise>ASC</otherwise>
            </choose>
            , t.id DESC
            </script>
            """)
    IPage<ProjectTaskDO> selectAggregatePageByProjectId(
            @Param("projectId") Long projectId,
            @Param("reqVO") ProjectTaskAggregatePageReqVO reqVO,
            @Param("terminalStatusCodes") Collection<String> terminalStatusCodes,
            @Param("weekStart") LocalDate weekStart,
            @Param("weekEnd") LocalDate weekEnd,
            @Param("today") LocalDate today,
            Page<ProjectTaskDO> page);

    /**
     * 项目级跨执行任务按状态分组计数(status-board)。
     * 入参同 page 但去除分页 / sort / statusCodes 筛选。
     */
    @Select("""
            <script>
            SELECT t.status_code AS statusCode, COUNT(*) AS count
            FROM rdms_task t
            <where>
                t.project_id = #{projectId}
                AND t.deleted = b'0'
                <if test="reqVO.keyword != null and reqVO.keyword != ''">
                    AND t.task_title LIKE CONCAT('%', #{reqVO.keyword}, '%')
                </if>
                <if test="reqVO.executionIds != null and !reqVO.executionIds.isEmpty()">
                    AND t.execution_id IN
                    <foreach collection="reqVO.executionIds" item="eid" open="(" separator="," close=")">#{eid}</foreach>
                </if>
                <if test="reqVO.executionStatusCodes != null and !reqVO.executionStatusCodes.isEmpty()">
                    AND EXISTS (
                        SELECT 1 FROM rdms_project_execution e
                        WHERE e.id = t.execution_id
                          AND e.deleted = b'0'
                          AND e.status_code IN
                          <foreach collection="reqVO.executionStatusCodes" item="esc" open="(" separator="," close=")">#{esc}</foreach>
                    )
                </if>
                <if test="reqVO.executionInvolveUserId != null">
                    AND EXISTS (
                        SELECT 1 FROM rdms_project_execution e
                        WHERE e.id = t.execution_id
                          AND e.deleted = b'0'
                          AND (
                              e.owner_id = #{reqVO.executionInvolveUserId}
                              OR EXISTS (
                                  SELECT 1 FROM rdms_execution_assignee ea
                                  WHERE ea.execution_id = e.id
                                    AND ea.user_id = #{reqVO.executionInvolveUserId}
                                    AND ea.removed_at IS NULL
                                    AND ea.deleted = b'0'
                              )
                          )
                    )
                </if>
                <if test="reqVO.involveUserId != null">
                    AND (
                        t.owner_id = #{reqVO.involveUserId}
                        OR EXISTS (
                            SELECT 1 FROM rdms_task_assignee a
                            WHERE a.task_id = t.id
                              AND a.user_id = #{reqVO.involveUserId}
                              AND a.removed_at IS NULL
                              AND a.deleted = b'0'
                        )
                    )
                </if>
                <if test="reqVO.ownerId != null">
                    AND t.owner_id = #{reqVO.ownerId}
                </if>
                <if test="reqVO.priority != null and reqVO.priority != ''">
                    AND t.priority = #{reqVO.priority}
                </if>
                <if test="reqVO.parentTaskId != null">
                    AND t.parent_task_id = #{reqVO.parentTaskId}
                </if>
                <if test="reqVO.dueRange == 'overdue'">
                    AND t.planned_end_date &lt; #{today}
                    <if test="terminalStatusCodes != null and !terminalStatusCodes.isEmpty()">
                        AND t.status_code NOT IN
                        <foreach collection="terminalStatusCodes" item="tc" open="(" separator="," close=")">#{tc}</foreach>
                    </if>
                </if>
                <if test="reqVO.dueRange == 'today'">
                    AND t.planned_end_date = #{today}
                </if>
                <if test="reqVO.dueRange == 'thisWeek'">
                    AND t.planned_end_date BETWEEN #{weekStart} AND #{weekEnd}
                </if>
                <if test="reqVO.updateTime != null and reqVO.updateTime.length == 2">
                    AND t.update_time BETWEEN #{reqVO.updateTime[0]} AND #{reqVO.updateTime[1]}
                </if>
            </where>
            GROUP BY t.status_code
            </script>
            """)
    List<StatusCountRow> selectAggregateStatusCount(
            @Param("projectId") Long projectId,
            @Param("reqVO") ProjectTaskAggregateStatusBoardReqVO reqVO,
            @Param("terminalStatusCodes") Collection<String> terminalStatusCodes,
            @Param("weekStart") LocalDate weekStart,
            @Param("weekEnd") LocalDate weekEnd,
            @Param("today") LocalDate today);

    /**
     * summary 的 4 个数字一次查出来,避免 4 次扫表。
     *
     * 返回 Map 结构:
     *   overdue → Long
     *   dueToday → Long
     *   dueThisWeek → Long
     *   doneThisWeek → Long
     */
    @Select("""
            <script>
            SELECT
                CAST(SUM(CASE WHEN t.planned_end_date &lt; #{today}
                    <if test="terminalStatusCodes != null and !terminalStatusCodes.isEmpty()">
                        AND t.status_code NOT IN
                        <foreach collection="terminalStatusCodes" item="tc" open="(" separator="," close=")">#{tc}</foreach>
                    </if>
                    THEN 1 ELSE 0 END) AS SIGNED) AS overdue,
                CAST(SUM(CASE WHEN t.planned_end_date = #{today}
                    <if test="terminalStatusCodes != null and !terminalStatusCodes.isEmpty()">
                        AND t.status_code NOT IN
                        <foreach collection="terminalStatusCodes" item="tc" open="(" separator="," close=")">#{tc}</foreach>
                    </if>
                    THEN 1 ELSE 0 END) AS SIGNED) AS dueToday,
                CAST(SUM(CASE WHEN t.planned_end_date BETWEEN #{weekStart} AND #{weekEnd}
                    <if test="terminalStatusCodes != null and !terminalStatusCodes.isEmpty()">
                        AND t.status_code NOT IN
                        <foreach collection="terminalStatusCodes" item="tc" open="(" separator="," close=")">#{tc}</foreach>
                    </if>
                    THEN 1 ELSE 0 END) AS SIGNED) AS dueThisWeek,
                CAST(SUM(CASE WHEN t.actual_end_date BETWEEN #{weekStart} AND #{today}
                    AND t.status_code = #{completedStatusCode}
                    THEN 1 ELSE 0 END) AS SIGNED) AS doneThisWeek
            FROM rdms_task t
            <where>
                t.project_id = #{projectId}
                AND t.deleted = b'0'
                <if test="involveUserId != null">
                    AND (
                        t.owner_id = #{involveUserId}
                        OR EXISTS (
                            SELECT 1 FROM rdms_task_assignee a
                            WHERE a.task_id = t.id
                              AND a.user_id = #{involveUserId}
                              AND a.removed_at IS NULL
                              AND a.deleted = b'0'
                        )
                    )
                </if>
            </where>
            </script>
            """)
    Map<String, Long> selectAggregateSummaryCounts(
            @Param("projectId") Long projectId,
            @Param("involveUserId") Long involveUserId,
            @Param("terminalStatusCodes") Collection<String> terminalStatusCodes,
            @Param("completedStatusCode") String completedStatusCode,
            @Param("today") LocalDate today,
            @Param("weekStart") LocalDate weekStart,
            @Param("weekEnd") LocalDate weekEnd);

    /**
     * status-board 计数行 — 内嵌静态类,与 mapper 共享生命周期。
     */
    @Data
    class StatusCountRow {
        private String statusCode;
        private Long count;
    }

    // ======================== 工作台「我的项目」聚合计数 ========================

    /**
     * 接口一：当前用户(owner_id)在一批项目下的任务总数与未完成数（按 project_id 分组）。
     * totalCount=全部我负责任务；pendingCount=状态非终态（终态集为空则等于 totalCount）。
     * 返回 Map：projectId(Long) / totalCount(Long) / pendingCount(Long)。
     */
    @Select("""
            <script>
            SELECT project_id AS projectId,
                   CAST(COUNT(*) AS SIGNED) AS totalCount,
                   CAST(SUM(CASE WHEN 1 = 1
                       <if test="terminalStatusCodes != null and !terminalStatusCodes.isEmpty()">
                           AND status_code NOT IN
                           <foreach collection="terminalStatusCodes" item="tc" open="(" separator="," close=")">#{tc}</foreach>
                       </if>
                       THEN 1 ELSE 0 END) AS SIGNED) AS pendingCount
            FROM rdms_task
            WHERE deleted = b'0'
              AND owner_id = #{ownerId}
              AND project_id IN
              <foreach collection="projectIds" item="pid" open="(" separator="," close=")">#{pid}</foreach>
            GROUP BY project_id
            </script>
            """)
    List<Map<String, Object>> selectMyTaskCountGroupByProjectIds(
            @Param("ownerId") Long ownerId,
            @Param("projectIds") Collection<Long> projectIds,
            @Param("terminalStatusCodes") Collection<String> terminalStatusCodes);

    /**
     * 接口二：一批项目下的进行中任务数与逾期任务数（按 project_id 分组，一次扫表出两数）。
     * taskCount=状态非终态；overdueCount=planned_end_date &lt; today 且状态非终态。
     * 返回 Map：projectId(Long) / taskCount(Long) / overdueCount(Long)。
     */
    @Select("""
            <script>
            SELECT project_id AS projectId,
                   CAST(SUM(CASE WHEN 1 = 1
                       <if test="terminalStatusCodes != null and !terminalStatusCodes.isEmpty()">
                           AND status_code NOT IN
                           <foreach collection="terminalStatusCodes" item="tc" open="(" separator="," close=")">#{tc}</foreach>
                       </if>
                       THEN 1 ELSE 0 END) AS SIGNED) AS taskCount,
                   CAST(SUM(CASE WHEN planned_end_date &lt; #{today}
                       <if test="terminalStatusCodes != null and !terminalStatusCodes.isEmpty()">
                           AND status_code NOT IN
                           <foreach collection="terminalStatusCodes" item="tc" open="(" separator="," close=")">#{tc}</foreach>
                       </if>
                       THEN 1 ELSE 0 END) AS SIGNED) AS overdueCount
            FROM rdms_task
            WHERE deleted = b'0'
              AND project_id IN
              <foreach collection="projectIds" item="pid" open="(" separator="," close=")">#{pid}</foreach>
            GROUP BY project_id
            </script>
            """)
    List<Map<String, Object>> selectTaskAndOverdueCountGroupByProjectIds(
            @Param("projectIds") Collection<Long> projectIds,
            @Param("terminalStatusCodes") Collection<String> terminalStatusCodes,
            @Param("today") LocalDate today);

    /**
     * 接口二 members：一批项目下每个负责人(owner_id)的进行中任务数（按 project_id, owner_id 分组）。
     * 排除 owner_id 为空的任务。返回 Map：projectId(Long) / ownerId(Long) / activeTaskCount(Long)。
     */
    @Select("""
            <script>
            SELECT project_id AS projectId,
                   owner_id AS ownerId,
                   CAST(COUNT(*) AS SIGNED) AS activeTaskCount
            FROM rdms_task
            WHERE deleted = b'0'
              AND owner_id IS NOT NULL
              AND project_id IN
              <foreach collection="projectIds" item="pid" open="(" separator="," close=")">#{pid}</foreach>
              <if test="terminalStatusCodes != null and !terminalStatusCodes.isEmpty()">
                  AND status_code NOT IN
                  <foreach collection="terminalStatusCodes" item="tc" open="(" separator="," close=")">#{tc}</foreach>
              </if>
            GROUP BY project_id, owner_id
            </script>
            """)
    List<Map<String, Object>> selectActiveTaskCountGroupByProjectIdAndOwner(
            @Param("projectIds") Collection<Long> projectIds,
            @Param("terminalStatusCodes") Collection<String> terminalStatusCodes);

    default List<ProjectTaskDO> selectListByProjectId(Long projectId) {
        if (projectId == null) {
            return List.of();
        }
        return selectList(new LambdaQueryWrapperX<ProjectTaskDO>()
                .eq(ProjectTaskDO::getProjectId, projectId)
                .orderByAsc(ProjectTaskDO::getExecutionId)
                .orderByAsc(ProjectTaskDO::getId));
    }

    @Select("""
            <script>
            SELECT t.*
            FROM rdms_task t
            <where>
                t.deleted = b'0'
                AND (
                    t.owner_id = #{userId}
                    OR EXISTS (
                        SELECT 1 FROM rdms_task_assignee a
                        WHERE a.task_id = t.id
                          AND a.user_id = #{userId}
                          AND a.removed_at IS NULL
                          AND a.deleted = b'0'
                    )
                )
                AND t.planned_start_date IS NOT NULL
                AND t.planned_end_date IS NOT NULL
                AND t.planned_start_date &lt;= #{endDate}
                AND t.planned_end_date &gt;= #{startDate}
                <if test="excludedStatusCode != null and excludedStatusCode != ''">
                    AND t.status_code != #{excludedStatusCode}
                </if>
            </where>
            ORDER BY t.project_id ASC, t.execution_id ASC, t.priority ASC, t.id ASC
            </script>
            """)
    List<ProjectTaskDO> selectPlannedInvolvedListByUserIdAndOverlap(@Param("userId") Long userId,
                                                                    @Param("startDate") LocalDate startDate,
                                                                    @Param("endDate") LocalDate endDate,
                                                                    @Param("excludedStatusCode") String excludedStatusCode);

    @Select("""
            <script>
            SELECT t.*
            FROM rdms_task t
            <where>
                t.deleted = b'0'
                AND (
                    t.owner_id = #{userId}
                    OR EXISTS (
                        SELECT 1 FROM rdms_task_assignee a
                        WHERE a.task_id = t.id
                          AND a.user_id = #{userId}
                          AND a.removed_at IS NULL
                          AND a.deleted = b'0'
                    )
                )
                <if test="excludedStatusCode != null and excludedStatusCode != ''">
                    AND t.status_code != #{excludedStatusCode}
                </if>
            </where>
            ORDER BY t.project_id ASC, t.execution_id ASC, t.priority ASC, t.id ASC
            </script>
            """)
    List<ProjectTaskDO> selectInvolvedListByUserIdAndStatusNot(@Param("userId") Long userId,
                                                               @Param("excludedStatusCode") String excludedStatusCode);

    /**
     * 工作台「我的任务」：当前用户为负责人或在岗协办人的非终态任务（跨项目）。
     * involveType：owner=仅我负责 / collaborator=仅我协办（排除我同时是负责人的）/ 其他或 null=并集。
     * 排序：计划结束升序、空值最后，同日按创建时间升序，id 兜底。
     */
    @Select("""
            <script>
            SELECT t.*
            FROM rdms_task t
            <where>
                t.deleted = b'0'
                <choose>
                    <when test="involveType == 'owner'">
                        AND t.owner_id = #{userId}
                    </when>
                    <when test="involveType == 'collaborator'">
                        AND (t.owner_id IS NULL OR t.owner_id != #{userId})
                        AND EXISTS (
                            SELECT 1 FROM rdms_task_assignee a
                            WHERE a.task_id = t.id
                              AND a.user_id = #{userId}
                              AND a.removed_at IS NULL
                              AND a.deleted = b'0'
                        )
                    </when>
                    <otherwise>
                        AND (
                            t.owner_id = #{userId}
                            OR EXISTS (
                                SELECT 1 FROM rdms_task_assignee a
                                WHERE a.task_id = t.id
                                  AND a.user_id = #{userId}
                                  AND a.removed_at IS NULL
                                  AND a.deleted = b'0'
                            )
                        )
                    </otherwise>
                </choose>
                <if test="terminalStatusCodes != null and !terminalStatusCodes.isEmpty()">
                    AND t.status_code NOT IN
                    <foreach collection="terminalStatusCodes" item="tc" open="(" separator="," close=")">#{tc}</foreach>
                </if>
            </where>
            ORDER BY (t.planned_end_date IS NULL) ASC, t.planned_end_date ASC, t.create_time ASC, t.id ASC
            </script>
            """)
    List<ProjectTaskDO> selectMyUnfinishedInvolvedList(@Param("userId") Long userId,
                                                       @Param("involveType") String involveType,
                                                       @Param("terminalStatusCodes") Collection<String> terminalStatusCodes);

    /**
     * 团队负载:一批成员的非终态任务计数,按 (成员, 项目) 分组,一次扫表带出临期/逾期。
     * 身份口径与「我的任务」一致(负责人 ∪ 在岗协办);UNION ALL 两个视角,
     * 协办分支排除"自己同时是负责人"的行防双计(owner_id 为 NULL 时保留)。
     * 临期:today <= planned_end_date <= dueSoonEnd;逾期:planned_end_date < today。
     * 返回 Map:userId / projectId / taskCount / dueSoonCount / overdueCount(均 Long)。
     */
    @Select("""
            <script>
            SELECT x.user_id AS userId,
                   x.project_id AS projectId,
                   CAST(COUNT(*) AS SIGNED) AS taskCount,
                   CAST(SUM(CASE WHEN x.planned_end_date IS NOT NULL
                        AND x.planned_end_date &gt;= #{today}
                        AND x.planned_end_date &lt;= #{dueSoonEnd}
                        THEN 1 ELSE 0 END) AS SIGNED) AS dueSoonCount,
                   CAST(SUM(CASE WHEN x.planned_end_date IS NOT NULL
                        AND x.planned_end_date &lt; #{today}
                        THEN 1 ELSE 0 END) AS SIGNED) AS overdueCount
            FROM (
                SELECT t.owner_id AS user_id, t.project_id, t.planned_end_date
                FROM rdms_task t
                WHERE t.deleted = b'0'
                  AND t.owner_id IN
                  <foreach collection="userIds" item="uid" open="(" separator="," close=")">#{uid}</foreach>
                  <if test="terminalStatusCodes != null and !terminalStatusCodes.isEmpty()">
                      AND t.status_code NOT IN
                      <foreach collection="terminalStatusCodes" item="tc" open="(" separator="," close=")">#{tc}</foreach>
                  </if>
                UNION ALL
                SELECT a.user_id, t.project_id, t.planned_end_date
                FROM rdms_task t
                JOIN rdms_task_assignee a ON a.task_id = t.id
                  AND a.removed_at IS NULL
                  AND a.deleted = b'0'
                WHERE t.deleted = b'0'
                  AND a.user_id IN
                  <foreach collection="userIds" item="uid" open="(" separator="," close=")">#{uid}</foreach>
                  AND (t.owner_id IS NULL OR t.owner_id != a.user_id)
                  <if test="terminalStatusCodes != null and !terminalStatusCodes.isEmpty()">
                      AND t.status_code NOT IN
                      <foreach collection="terminalStatusCodes" item="tc" open="(" separator="," close=")">#{tc}</foreach>
                  </if>
            ) x
            GROUP BY x.user_id, x.project_id
            </script>
            """)
    List<Map<String, Object>> selectInvolvedLoadStatsByUserIds(
            @Param("userIds") Collection<Long> userIds,
            @Param("terminalStatusCodes") Collection<String> terminalStatusCodes,
            @Param("today") LocalDate today,
            @Param("dueSoonEnd") LocalDate dueSoonEnd);

    /**
     * 临期/逾期告警候选：计划完成日非空且 <= maxPlannedEndDate，状态不在排除集（终态+paused）。
     * excludeStatusCodes 为空时不排除任何状态（空集守卫，与既有口径一致）。
     * 设计见 docs/domains/project/2026-06-12-临期逾期告警-设计.html。
     */
    default List<ProjectTaskDO> selectDueAlertCandidateList(LocalDate maxPlannedEndDate,
                                                            Collection<String> excludeStatusCodes) {
        return selectList(new LambdaQueryWrapperX<ProjectTaskDO>()
                .isNotNull(ProjectTaskDO::getPlannedEndDate)
                .le(ProjectTaskDO::getPlannedEndDate, maxPlannedEndDate)
                .notIn(excludeStatusCodes != null && !excludeStatusCodes.isEmpty(),
                        ProjectTaskDO::getStatusCode, excludeStatusCodes));
    }

}
