<?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.dora.mapper.CategoryMapper">

    <!-- 结果映射 -->
    <resultMap id="CategoryResultMap" type="com.dora.entity.Category">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="type" property="type"/>
        <result column="description" property="description"/>
        <result column="sort_order" property="sortOrder"/>
        <result column="is_enabled" property="isEnabled"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>

    <!-- 根据ID查询类目 -->
    <select id="selectById" resultMap="CategoryResultMap">
        SELECT 
            id, name, type, description, sort_order, is_enabled,
            create_time, update_time, is_deleted
        FROM category 
        WHERE id = #{id} AND is_deleted = 0
    </select>

    <!-- 根据类型查询启用的类目列表 -->
    <select id="selectEnabledByType" resultMap="CategoryResultMap">
        SELECT 
            id, name, type, description, sort_order, is_enabled,
            create_time, update_time, is_deleted
        FROM category 
        WHERE type = #{type} 
          AND is_enabled = 1 
          AND is_deleted = 0
        ORDER BY sort_order ASC, id ASC
    </select>

    <!-- 查询所有启用的类目列表 -->
    <select id="selectAllEnabled" resultMap="CategoryResultMap">
        SELECT 
            id, name, type, description, sort_order, is_enabled,
            create_time, update_time, is_deleted
        FROM category 
        WHERE is_enabled = 1 
          AND is_deleted = 0
        ORDER BY type ASC, sort_order ASC, id ASC
    </select>

    <!-- 分页查询类目列表（管理员用） -->
    <select id="selectByPage" resultMap="CategoryResultMap">
        SELECT 
            id, name, type, description, sort_order, is_enabled,
            create_time, update_time, is_deleted
        FROM category 
        WHERE is_deleted = 0
        <if test="type != null">
            AND type = #{type}
        </if>
        ORDER BY sort_order ASC, id ASC
        LIMIT #{offset}, #{limit}
    </select>

    <!-- 统计类目总数（管理员用） -->
    <select id="countByType" resultType="java.lang.Long">
        SELECT COUNT(*)
        FROM category 
        WHERE is_deleted = 0
        <if test="type != null">
            AND type = #{type}
        </if>
    </select>

    <!-- 统计所有类目总数 -->
    <select id="countAll" resultType="java.lang.Long">
        SELECT COUNT(*)
        FROM category 
        WHERE is_deleted = 0
    </select>

    <!-- 根据名称和类型查询类目（用于重名检查） -->
    <select id="selectByNameAndType" resultMap="CategoryResultMap">
        SELECT 
            id, name, type, description, sort_order, is_enabled,
            create_time, update_time, is_deleted
        FROM category 
        WHERE name = #{name} 
          AND type = #{type} 
          AND is_deleted = 0
    </select>

    <!-- 插入类目 -->
    <insert id="insert" parameterType="com.dora.entity.Category" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO category (
            name, type, description, sort_order, is_enabled,
            create_time, update_time, is_deleted
        ) VALUES (
            #{name}, #{type}, #{description}, #{sortOrder}, #{isEnabled},
            #{createTime}, #{updateTime}, #{isDeleted}
        )
    </insert>

    <!-- 更新类目 -->
    <update id="updateById" parameterType="com.dora.entity.Category">
        UPDATE category SET 
            name = #{name},
            description = #{description},
            sort_order = #{sortOrder},
            is_enabled = #{isEnabled},
            update_time = #{updateTime}
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- 逻辑删除类目 -->
    <update id="deleteById">
        UPDATE category SET 
            is_deleted = 1,
            update_time = NOW()
        WHERE id = #{id}
    </update>

    <!-- 批量更新排序 -->
    <update id="batchUpdateSortOrder">
        <foreach collection="categories" item="category" separator=";">
            UPDATE category SET 
                sort_order = #{category.sortOrder},
                update_time = NOW()
            WHERE id = #{category.id}
        </foreach>
    </update>

</mapper>
