package com.erp.category.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.erp.category.entity.Category;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * 分类数据访问层
 * 
 * 提供分类的数据库操作，包括层级查询、路径更新等
 */
@Mapper
public interface CategoryRepository extends BaseMapper<Category> {
    
    /**
     * 根据租户ID和父ID查询子分类（按排序号排序）
     */
    @Select("SELECT * FROM categories WHERE tenant_id = #{tenantId} AND parent_id = #{parentId} AND deleted_at IS NULL ORDER BY sort_order ASC")
    List<Category> findByTenantIdAndParentId(@Param("tenantId") Long tenantId, @Param("parentId") Long parentId);
    
    /**
     * 根据租户ID查询根分类
     */
    @Select("SELECT * FROM categories WHERE tenant_id = #{tenantId} AND (parent_id IS NULL OR parent_id = 0) AND deleted_at IS NULL ORDER BY sort_order ASC")
    List<Category> findRootCategories(@Param("tenantId") Long tenantId);
    
    /**
     * 根据路径前缀查询所有子分类（包含自己）
     */
    @Select("SELECT * FROM categories WHERE tenant_id = #{tenantId} AND (path LIKE CONCAT(#{pathPrefix}, '%') OR id = #{parentId}) AND deleted_at IS NULL")
    List<Category> findByPathPrefix(@Param("tenantId") Long tenantId, @Param("pathPrefix") String pathPrefix, @Param("parentId") Long parentId);
    
    /**
     * 根据编码查询（跨租户唯一）
     */
    @Select("SELECT * FROM categories WHERE code = #{code} AND deleted_at IS NULL LIMIT 1")
    Category findByCode(@Param("code") String code);
    
    /**
     * 根据租户ID和编码查询
     */
    @Select("SELECT * FROM categories WHERE tenant_id = #{tenantId} AND code = #{code} AND deleted_at IS NULL LIMIT 1")
    Category findByTenantIdAndCode(@Param("tenantId") Long tenantId, @Param("code") String code);
    
    /**
     * 查询所有启用的分类
     */
    @Select("SELECT * FROM categories WHERE tenant_id = #{tenantId} AND status = 1 AND deleted_at IS NULL ORDER BY path ASC, sort_order ASC")
    List<Category> findAllEnabled(@Param("tenantId") Long tenantId);
    
    /**
     * 更新子分类数量
     */
    @Update("UPDATE categories SET children_count = (SELECT COUNT(*) FROM categories c WHERE c.parent_id = categories.id AND c.deleted_at IS NULL) WHERE id = #{id}")
    void updateChildrenCount(@Param("id") Long id);
    
    /**
     * 批量更新子分类数量
     */
    @Update("UPDATE categories SET children_count = (SELECT COUNT(*) FROM categories c WHERE c.parent_id = categories.id AND c.deleted_at IS NULL) WHERE id IN (#{ids})")
    void batchUpdateChildrenCount(@Param("ids") List<Long> ids);
    
    /**
     * 检查是否存在子分类
     */
    @Select("SELECT COUNT(*) > 0 FROM categories WHERE parent_id = #{parentId} AND deleted_at IS NULL")
    boolean hasChildren(@Param("parentId") Long parentId);
    
    /**
     * 检查分类编码是否存在（排除指定ID）
     */
    @Select("SELECT COUNT(*) > 0 FROM categories WHERE tenant_id = #{tenantId} AND code = #{code} AND id != #{excludeId} AND deleted_at IS NULL")
    boolean existsByCodeExcludingId(@Param("tenantId") Long tenantId, @Param("code") String code, @Param("excludeId") Long excludeId);
    
    /**
     * 获取分类的祖先ID列表
     */
    @Select("SELECT id FROM categories WHERE tenant_id = #{tenantId} AND FIND_IN_SET(id, REPLACE(#{path}, '/', ',')) > 0 AND deleted_at IS NULL ORDER BY level ASC")
    List<Long> findAncestorIds(@Param("tenantId") Long tenantId, @Param("path") String path);
}
