<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fs.survey.mapper.SurveyTaskMapper">

    <resultMap type="SurveyTask" id="SurveyTaskResult">
        <result property="id"    column="id"    />
        <result property="title"    column="title"    />
        <result property="description"    column="description"    />
        <result property="coverImage"    column="cover_image"    />
        <result property="formId"    column="form_id"    />
        <result property="category"    column="category"    />
        <result property="year"    column="year"    />
        <result property="totalPeriods"    column="total_periods"    />
        <result property="intervalDays"    column="interval_days"    />
        <result property="status"    column="status"    />
        <result property="sort"    column="sort"    />
        <result property="viewCount"    column="view_count"    />
        <result property="companyId"    column="company_id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="deleted"    column="deleted"    />
        <result property="periodStartTime"    column="period_start_time"    />
        <result property="periodEndTime"    column="period_end_time"    />
        <!-- 关联企业信息 -->
        <result property="companyName"    column="company_name"    />
        <!-- 新增：问卷名称 -->
        <result property="questionnaireName"    column="questionnaire_name"    />
        <result property="surveyQuestionnaireType" column="surveyQuestionnaireType"/>
        <result property="doctorId" column="doctor_id"/>
        <result property="doctorName" column="doctor_name"/>

    </resultMap>

    <sql id="selectSurveyTaskVo">
        select
        st.id,
        st.title,
        st.description,
        st.cover_image,
        st.form_id,
        st.category,
        st.year,
        st.total_periods,
        st.interval_days,
        st.status,
        st.sort,
        st.view_count,
        st.company_id,
        st.create_by,
        st.create_time,
        st.update_time,
        st.doctor_id,
        d.doctor_name,
        st.deleted,
        c.company_name,
        period_agg.min_start AS period_start_time,
        period_agg.max_end AS period_end_time,
        <!-- 新增：问卷名称 -->
        sq.name as questionnaire_name,
        sq.survey_questionnaire_type as surveyQuestionnaireType
        from survey_task st
        left join company c on st.company_id = c.company_id
        left join doctor d on st.doctor_id = d.id
        left join survey_questionnaire sq on st.form_id = sq.id and sq.del_flag = '0'
        LEFT JOIN (
        SELECT
        task_id,
        MIN(period_start_time) AS min_start,
        MAX(period_end_time) AS max_end,
        COUNT(*) AS total_count,
        SUM(CASE WHEN is_period = 1 THEN 1 ELSE 0 END) AS period_1_count
        FROM survey_task_period
        GROUP BY task_id
        ) period_agg ON period_agg.task_id = st.id
    </sql>

    <select id="selectSurveyTaskList" parameterType="SurveyTask" resultMap="SurveyTaskResult">
        <include refid="selectSurveyTaskVo"/>
        <where>
            <if test="title != null  and title != ''"> and st.title like concat('%', #{title}, '%')</if>
            <if test="description != null  and description != ''"> and st.description = #{description}</if>
            <if test="coverImage != null  and coverImage != ''"> and st.cover_image = #{coverImage}</if>
            <if test="formId != null "> and st.form_id = #{formId}</if>
            <if test="category != null  and category != ''"> and st.category = #{category}</if>
            <if test="year != null "> and st.year = #{year}</if>
            <if test="totalPeriods != null "> and st.total_periods = #{totalPeriods}</if>
            <if test="intervalDays != null "> and st.interval_days = #{intervalDays}</if>
            <if test="status != null "> and st.status = #{status}</if>
            <if test="sort != null "> and st.sort = #{sort}</if>
            <if test="viewCount != null "> and st.view_count = #{viewCount}</if>
            <if test="companyId != null "> and st.company_id = #{companyId}</if>
            <if test="doctorId != null">
                AND st.doctor_id = #{doctorId}
            </if>
            <if test="deleted != null "> and st.deleted = #{deleted}</if>
            <!-- 企业名称查询条件 -->
            <if test="companyName != null  and companyName != ''"> and c.company_name like concat('%', #{companyName}, '%')</if>
            <!-- 新增：问卷名称查询条件 -->
            <if test="questionnaireName != null  and questionnaireName != ''"> and sq.name like concat('%', #{questionnaireName}, '%')</if>
            <if test="timeStatus != null">
                <choose>
                    <when test="timeStatus == 'before'">
                        AND EXISTS (
                        SELECT 1
                        FROM survey_task_period stp
                        WHERE stp.task_id = st.id
                        AND stp.is_period = 0
                        AND NOW() BETWEEN stp.period_start_time AND stp.period_end_time
                        )
                    </when>
                    <when test="timeStatus == 'during'">
                        <!-- 兼容原逻辑：存在 is_period=1 的周期 -->
                        AND period_agg.period_1_count > 0
                        AND period_agg.period_1_count &lt; period_agg.total_count
                        <!-- 增强时间准确性：当前时间必须在某个周期时间窗口内 -->
                        AND EXISTS (
                        SELECT 1
                        FROM survey_task_period stp
                        WHERE stp.task_id = st.id
                        AND stp.period_start_time &lt;= NOW()
                        AND stp.period_end_time >= NOW()
                        )
                    </when>
                    <when test="timeStatus == 'after'">
                        AND period_agg.total_count IS NOT NULL
                        AND period_agg.total_count > 0
                        AND (
                        <!-- 原逻辑：所有周期都标记为 is_period=1 -->
                        period_agg.period_1_count = period_agg.total_count
                        OR
                        <!-- 新逻辑：当前时间已超过所有周期的结束时间 -->
                        (period_agg.max_end IS NOT NULL AND NOW() &gt; period_agg.max_end)
                        )
                    </when>
                </choose>
            </if>
        </where>
        order by st.sort asc, st.create_time desc
    </select>

    <select id="selectSurveyTaskById" parameterType="Long" resultMap="SurveyTaskResult">
        <include refid="selectSurveyTaskVo"/>
        where st.id = #{id}
    </select>

    <insert id="insertSurveyTask" parameterType="SurveyTask" useGeneratedKeys="true" keyProperty="id">
        insert into survey_task
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="title != null and title != ''">title,</if>
            <if test="description != null">description,</if>
            <if test="coverImage != null">cover_image,</if>
            <if test="formId != null">form_id,</if>
            <if test="category != null and category != ''">category,</if>
            <if test="year != null">year,</if>
            <if test="totalPeriods != null">total_periods,</if>
            <if test="intervalDays != null">interval_days,</if>
            <if test="status != null">status,</if>
            <if test="sort != null">sort,</if>
            <if test="viewCount != null">view_count,</if>
            <if test="companyId != null">company_id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="deleted != null">deleted,</if>
            <if test="doctorId != null">doctor_id,</if>

        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="title != null and title != ''">#{title},</if>
            <if test="description != null">#{description},</if>
            <if test="coverImage != null">#{coverImage},</if>
            <if test="formId != null">#{formId},</if>
            <if test="category != null and category != ''">#{category},</if>
            <if test="year != null">#{year},</if>
            <if test="totalPeriods != null">#{totalPeriods},</if>
            <if test="intervalDays != null">#{intervalDays},</if>
            <if test="status != null">#{status},</if>
            <if test="sort != null">#{sort},</if>
            <if test="viewCount != null">#{viewCount},</if>
            <if test="companyId != null">#{companyId},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="deleted != null">#{deleted},</if>
            <if test="doctorId != null">#{doctorId},</if>

        </trim>
    </insert>

    <update id="updateSurveyTask" parameterType="SurveyTask">
        update survey_task
        <trim prefix="SET" suffixOverrides=",">
            <if test="title != null and title != ''">title = #{title},</if>
            <if test="description != null">description = #{description},</if>
            <if test="coverImage != null">cover_image = #{coverImage},</if>
            <if test="formId != null">form_id = #{formId},</if>
            <if test="category != null and category != ''">category = #{category},</if>
            <if test="year != null">year = #{year},</if>
            <if test="totalPeriods != null">total_periods = #{totalPeriods},</if>
            <if test="intervalDays != null">interval_days = #{intervalDays},</if>
            <if test="status != null">status = #{status},</if>
            <if test="sort != null">sort = #{sort},</if>
            <if test="viewCount != null">view_count = #{viewCount},</if>
            <if test="companyId != null">company_id = #{companyId},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="deleted != null">deleted = #{deleted},</if>
            <if test="doctorId != null">doctor_id = #{doctorId},</if>

        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSurveyTaskById" parameterType="Long">
        delete from survey_task where id = #{id}
    </delete>

    <delete id="deleteSurveyTaskByIds" parameterType="String">
        delete from survey_task where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>