<?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.provider.mapper.ServiceGenConfigMapper">
    
    <resultMap type="ServiceGenConfig" id="ServiceGenConfigResult">
        <result property="id"    column="id"    />
        <result property="departmentId"    column="department_id"    />
        <result property="taskTypes"    column="task_types"    />
        <result property="frequencyUnit"    column="frequency_unit"    />
        <result property="frequencyValue"    column="frequency_value"    />
        <result property="generateTime"    column="generate_time"    />
        <result property="createdAt"    column="created_at"    />
        <result property="updatedAt"    column="updated_at"    />
        <result property="companyId"    column="company_id"    />
        <result property="needAudit"    column="need_audit"    />
        <result property="companyName" column="company_name"/>
        <result property="departmentName" column="dept_name"/>
        <result property="auditInstanceId" column="audit_instance_id"/>
        <result property="auditStatus"     column="audit_status"/>
    </resultMap>

    <sql id="selectServiceGenConfigVo">
        SELECT
        c.id,
        c.department_id,
        c.task_types,
        c.frequency_unit,
        c.frequency_value,
        c.generate_time,
        c.created_at,
        c.updated_at,
        c.company_id,
        c.need_audit,
        c.audit_instance_id,
        c.audit_status,
        co.company_name,
        cd.dept_name
        FROM service_gen_config c
        LEFT JOIN company co ON co.company_id = c.company_id
        LEFT JOIN company_dept cd ON cd.dept_id = c.department_id
    </sql>


    <select id="selectServiceGenConfigList" parameterType="ServiceGenConfig" resultMap="ServiceGenConfigResult">
        <include refid="selectServiceGenConfigVo"/>
        <where>
            <!-- 这里我顺手把列名都加 c. 前缀，避免 join 后歧义 ✅ -->
            <if test="departmentId != null"> AND c.department_id = #{departmentId}</if>
            <if test="taskTypes != null and taskTypes != ''"> AND FIND_IN_SET(#{taskTypes}, c.task_types)</if>
            <if test="frequencyUnit != null"> AND c.frequency_unit = #{frequencyUnit}</if>
            <if test="frequencyValue != null"> AND c.frequency_value = #{frequencyValue}</if>
            <if test="generateTime != null"> AND c.generate_time = #{generateTime}</if>
            <if test="createdAt != null"> AND c.created_at = #{createdAt}</if>
            <if test="updatedAt != null"> AND c.updated_at = #{updatedAt}</if>
            <if test="companyId != null"> AND c.company_id = #{companyId}</if>
            <if test="needAudit != null"> AND c.need_audit = #{needAudit}</if>
            <!-- ✅ 可选：按审核状态筛选 -->
            <if test="auditStatus != null"> AND c.audit_status = #{auditStatus}</if>
        </where>
        order by c.created_at desc
    </select>
    
    <select id="selectServiceGenConfigById" parameterType="java.lang.Long" resultMap="ServiceGenConfigResult">
        <include refid="selectServiceGenConfigVo"/>
        WHERE c.id = #{id}
    </select>
        
    <insert id="insertServiceGenConfig" parameterType="ServiceGenConfig" useGeneratedKeys="true" keyProperty="id">
        insert into service_gen_config
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="departmentId != null and departmentId != ''">department_id,</if>
            <if test="taskTypes != null and taskTypes != ''">task_types,</if>
            <if test="frequencyUnit != null and frequencyUnit != ''">frequency_unit,</if>
            <if test="frequencyValue != null and frequencyValue != ''">frequency_value,</if>
            <if test="generateTime != null">generate_time,</if>
            <if test="createdAt != null">created_at,</if>
            <if test="updatedAt != null">updated_at,</if>
            <if test="companyId != null">company_id,</if>
            <if test="needAudit != null">need_audit,</if>
            <if test="auditInstanceId != null">audit_instance_id,</if>
            <if test="auditStatus != null">audit_status,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="departmentId != null and departmentId != ''">#{departmentId},</if>
            <if test="taskTypes != null and taskTypes != ''">#{taskTypes},</if>
            <if test="frequencyUnit != null and frequencyUnit != ''">#{frequencyUnit},</if>
            <if test="frequencyValue != null and frequencyValue != ''">#{frequencyValue},</if>
            <if test="generateTime != null">#{generateTime},</if>
            <if test="createdAt != null">#{createdAt},</if>
            <if test="updatedAt != null">#{updatedAt},</if>
            <if test="companyId != null">#{companyId},</if>
            <if test="needAudit != null">#{needAudit},</if>
            <if test="auditInstanceId != null">#{auditInstanceId},</if>
            <if test="auditStatus != null">#{auditStatus},</if>
         </trim>
    </insert>

    <update id="updateServiceGenConfig" parameterType="ServiceGenConfig">
        update service_gen_config
        <trim prefix="SET" suffixOverrides=",">
            <if test="departmentId != null and departmentId != ''">department_id = #{departmentId},</if>
            <if test="taskTypes != null and taskTypes != ''">task_types = #{taskTypes},</if>
            <if test="frequencyUnit != null and frequencyUnit != ''">frequency_unit = #{frequencyUnit},</if>
            <if test="frequencyValue != null and frequencyValue != ''">frequency_value = #{frequencyValue},</if>
            <if test="generateTime != null">generate_time = #{generateTime},</if>
            <if test="createdAt != null">created_at = #{createdAt},</if>
            <if test="updatedAt != null">updated_at = #{updatedAt},</if>
            <if test="companyId != null">company_id = #{companyId},</if>
            <if test="needAudit != null">need_audit = #{needAudit},</if>
            <!-- ✅ 新增：审核字段 -->
            <if test="auditInstanceId != null">audit_instance_id = #{auditInstanceId},</if>
            <if test="auditStatus != null">audit_status = #{auditStatus},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteServiceGenConfigById" parameterType="java.lang.Long">
        delete from service_gen_config where id = #{id}
    </delete>

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



    <select id="selectDueConfigs" resultType="com.fs.provider.domain.ServiceGenConfig">
        SELECT
        id, department_id, task_types, frequency_unit, frequency_value, generate_time,
        created_at, updated_at, company_id, need_audit, audit_instance_id, audit_status
        FROM service_gen_config
        WHERE 1=1

        <!-- ① 审核过滤：need_audit=0 或者 need_audit=1且审核通过 -->
        AND (
        need_audit = 0
        OR (need_audit = 1 AND audit_status = 1)
        )

        <!-- ② 周期过滤：天/周/月 -->
        AND (
        frequency_unit = 1
        OR (frequency_unit = 2 AND frequency_value = #{dow})
        OR (frequency_unit = 3 AND frequency_value = #{dom})
        )

        <!-- ③ 时间窗口过滤（处理跨天） -->
        <choose>
            <!-- 不跨天：start <= end -->
            <when test="crossDay == false">
                AND generate_time <![CDATA[ >= ]]> #{startTime}
                AND generate_time <![CDATA[ < ]]> #{endTime}
            </when>

            <!-- 跨天：start > end -->
            <otherwise>
                AND (
                generate_time <![CDATA[ >= ]]> #{startTime}
                OR generate_time <![CDATA[ < ]]> #{endTime}
                )
            </otherwise>
        </choose>

        ORDER BY company_id, department_id, id
    </select>
</mapper>