<?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.unilife.mapper.ScheduleMapper">
    <resultMap id="scheduleResultMap" type="com.unilife.model.entity.Schedule">
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="title" property="title"/>
        <result column="description" property="description"/>
        <result column="start_time" property="startTime"/>
        <result column="end_time" property="endTime"/>
        <result column="location" property="location"/>
        <result column="is_all_day" property="isAllDay"/>
        <result column="reminder" property="reminder"/>
        <result column="color" property="color"/>
        <result column="status" property="status"/>
        <result column="created_at" property="createdAt"/>
        <result column="updated_at" property="updatedAt"/>
    </resultMap>
    
    <insert id="insert" parameterType="com.unilife.model.entity.Schedule" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO schedules (
            user_id, title, description, start_time, end_time, location, 
            is_all_day, reminder, color, status, created_at, updated_at
        ) VALUES (
            #{userId}, #{title}, #{description}, #{startTime}, #{endTime}, #{location}, 
            #{isAllDay}, #{reminder}, #{color}, #{status}, NOW(), NOW()
        )
    </insert>
    
    <select id="getById" resultMap="scheduleResultMap">
        SELECT id, user_id, title, description, start_time, end_time, location, 
               is_all_day, reminder, color, status, created_at, updated_at
        FROM schedules
        WHERE id = #{id} AND status != 0
    </select>
    
    <select id="getListByUserId" resultMap="scheduleResultMap">
        SELECT id, user_id, title, description, start_time, end_time, location, 
               is_all_day, reminder, color, status, created_at, updated_at
        FROM schedules
        WHERE user_id = #{userId} AND status != 0
        ORDER BY start_time ASC
    </select>
    
    <select id="getListByUserIdAndTimeRange" resultMap="scheduleResultMap">
        SELECT id, user_id, title, description, start_time, end_time, location, 
               is_all_day, reminder, color, status, created_at, updated_at
        FROM schedules
        WHERE user_id = #{userId} 
          AND status != 0
          AND (
              (start_time &lt;= #{endTime} AND end_time &gt;= #{startTime})
              OR (is_all_day = 1 AND DATE(start_time) &lt;= DATE(#{endTime}) AND DATE(end_time) &gt;= DATE(#{startTime}))
          )
        ORDER BY start_time ASC
    </select>
    
    <update id="update" parameterType="com.unilife.model.entity.Schedule">
        UPDATE schedules
        SET title = #{title},
            description = #{description},
            start_time = #{startTime},
            end_time = #{endTime},
            location = #{location},
            is_all_day = #{isAllDay},
            reminder = #{reminder},
            color = #{color},
            updated_at = NOW()
        WHERE id = #{id} AND user_id = #{userId}
    </update>
    
    <update id="delete">
        UPDATE schedules
        SET status = 0,
            updated_at = NOW()
        WHERE id = #{id} AND user_id = #{userId}
    </update>
    
    <select id="checkConflict" resultType="java.lang.Integer">
        SELECT COUNT(*)
        FROM schedules
        WHERE user_id = #{userId} 
          AND status != 0
          AND (
              (start_time &lt; #{endTime} AND end_time &gt; #{startTime})
          )
          <if test="excludeScheduleId != null">
              AND id != #{excludeScheduleId}
          </if>
    </select>
    
    <select id="getSchedulesToRemind" resultMap="scheduleResultMap">
        SELECT id, user_id, title, description, start_time, end_time, location, 
               is_all_day, reminder, color, status, created_at, updated_at
        FROM schedules
        WHERE status != 0
          AND reminder IS NOT NULL
          AND DATE_ADD(#{currentTime}, INTERVAL reminder MINUTE) &gt;= start_time
          AND DATE_ADD(#{currentTime}, INTERVAL reminder + #{reminderMinutes} MINUTE) &lt;= start_time
        ORDER BY start_time ASC
    </select>
</mapper>