<?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="todo">

  <sql id="todoSearchCondition">
    <if test="keyword != null and keyword != ''">
      AND (TITLE ILIKE '%' || #{keyword} || '%'
        OR DESCRIPTION ILIKE '%' || #{keyword} || '%')
    </if>
    <if test="status != null and status != ''">
      AND STATUS = #{status}
    </if>
    <if test="priority != null and priority != ''">
      AND PRIORITY = #{priority}
    </if>
    <if test="assigned_to != null and assigned_to != ''">
      AND ASSIGNED_TO = #{assigned_to}
    </if>
  </sql>

  <select id="getTodoList" parameterType="map" resultType="map">
    SELECT
        ID
        , TITLE
        , DESCRIPTION
        , PRIORITY
        , STATUS
        , ASSIGNED_TO
        , DUE_DATE
        , CREATED_DATE
        , UPDATED_DATE
        , COMPLETED_AT
        , IS_URGENT
        , DISPLAY_ORDER
    FROM TODO_ITEMS
    WHERE 1=1
    <include refid="todoSearchCondition"/>
    ORDER BY
        CASE WHEN IS_URGENT THEN 0 ELSE 1 END
        , CASE PRIORITY
            WHEN 'urgent' THEN 0
            WHEN 'high' THEN 1
            WHEN 'normal' THEN 2
            WHEN 'low' THEN 3
            ELSE 4
          END
        , DISPLAY_ORDER ASC
    <include refid="common.pagination"/>
  </select>

  <select id="getTodoListCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM TODO_ITEMS
    WHERE 1=1
    <include refid="todoSearchCondition"/>
  </select>

  <select id="getTodoInfo" parameterType="map" resultType="map">
    SELECT
        ID
        , TITLE
        , DESCRIPTION
        , PRIORITY
        , STATUS
        , ASSIGNED_TO
        , DUE_DATE
        , CREATED_DATE
        , UPDATED_DATE
        , COMPLETED_AT
        , IS_URGENT
        , DISPLAY_ORDER
    FROM TODO_ITEMS
    WHERE ID = #{id}
  </select>

  <insert id="insertTodo" parameterType="map">
    INSERT INTO TODO_ITEMS (
        ID
        , TITLE
        , DESCRIPTION
        , PRIORITY
        , STATUS
        , ASSIGNED_TO
        , DUE_DATE
        , IS_URGENT
        , DISPLAY_ORDER
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{id}
        , #{title}
        , #{description, jdbcType=VARCHAR}
        , COALESCE(#{priority, jdbcType=VARCHAR}, 'normal')
        , COALESCE(#{status, jdbcType=VARCHAR}, 'pending')
        , #{assigned_to, jdbcType=VARCHAR}
        , #{due_date, jdbcType=DATE}
        , COALESCE(#{is_urgent, jdbcType=BOOLEAN}, FALSE)
        , COALESCE((SELECT COALESCE(MAX(DISPLAY_ORDER), -1) + 1 FROM TODO_ITEMS), 0)
        , NOW()
        , NOW()
    )
  </insert>

  <update id="updateTodo" parameterType="map">
    UPDATE TODO_ITEMS
    SET UPDATED_DATE = NOW()
    <if test="title != null">, TITLE = #{title}</if>
    <if test="description != null">, DESCRIPTION = #{description}</if>
    <if test="priority != null">, PRIORITY = #{priority}</if>
    <if test="status != null">
      , STATUS = #{status}
      <if test='status == "completed"'>, COMPLETED_AT = NOW()</if>
    </if>
    <if test="assigned_to != null">, ASSIGNED_TO = #{assigned_to}</if>
    <if test="due_date != null">, DUE_DATE = #{due_date}</if>
    <if test="is_urgent != null">, IS_URGENT = #{is_urgent}</if>
    WHERE ID = #{id}
  </update>

  <delete id="deleteTodo" parameterType="map">
    DELETE FROM TODO_ITEMS
    WHERE ID = #{id}
  </delete>

  <update id="updateTodoOrder" parameterType="map">
    UPDATE TODO_ITEMS
    SET
        DISPLAY_ORDER = #{display_order}
        , UPDATED_DATE = NOW()
    WHERE ID = #{id}
  </update>

</mapper>
