package com.erp.customer.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.erp.customer.entity.CustomerRelationship;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 客户关系数据访问接口
 */
@Mapper
public interface CustomerRelationshipRepository extends BaseMapper<CustomerRelationship> {
    
    /**
     * 根据客户ID查询所有关系
     */
    @Select("SELECT * FROM customer_relationships WHERE customer_id = #{customerId} AND deleted_at IS NULL")
    List<CustomerRelationship> findByCustomerId(@Param("customerId") Long customerId);
    
    /**
     * 根据父客户ID查询分店
     */
    @Select("SELECT * FROM customer_relationships WHERE parent_customer_id = #{parentCustomerId} " +
            "AND status = 'ACTIVE' AND deleted_at IS NULL ORDER BY relationship_level ASC, created_at ASC")
    List<CustomerRelationship> findActiveBranchesByParentId(@Param("parentCustomerId") Long parentCustomerId);
    
    /**
     * 根据层级查询
     */
    @Select("SELECT * FROM customer_relationships WHERE tenant_id = #{tenantId} AND relationship_level = #{level} " +
            "AND status = 'ACTIVE' AND deleted_at IS NULL ORDER BY created_at DESC")
    List<CustomerRelationship> findByTenantIdAndLevel(@Param("tenantId") Long tenantId, @Param("level") Integer level);
    
    /**
     * 查询总店的所有分店（所有层级）
     */
    @Select("SELECT * FROM customer_relationships WHERE parent_customer_id = #{headquartersId} " +
            "AND status = 'ACTIVE' AND deleted_at IS NULL ORDER BY relationship_level ASC, created_at ASC")
    List<CustomerRelationship> findAllBranchesByHeadquartersId(@Param("headquartersId") Long headquartersId);
    
    /**
     * 根据集团名称查询
     */
    @Select("SELECT * FROM customer_relationships WHERE tenant_id = #{tenantId} AND group_name = #{groupName} " +
            "AND status = 'ACTIVE' AND deleted_at IS NULL ORDER BY relationship_level ASC, created_at ASC")
    List<CustomerRelationship> findByGroupName(@Param("tenantId") Long tenantId, @Param("groupName") String groupName);
    
    /**
     * 统计客户的分店数量
     */
    @Select("SELECT COUNT(*) FROM customer_relationships WHERE parent_customer_id = #{customerId} " +
            "AND status = 'ACTIVE' AND deleted_at IS NULL")
    Long countActiveBranchesByCustomerId(@Param("customerId") Long customerId);
    
    /**
     * 查询关系类型为分店的客户
     */
    @Select("SELECT * FROM customer_relationships WHERE tenant_id = #{tenantId} AND relationship_type = 'BRANCH' " +
            "AND status = 'ACTIVE' AND deleted_at IS NULL ORDER BY created_at DESC")
    List<CustomerRelationship> findAllBranches(@Param("tenantId") Long tenantId);
    
    /**
     * 查询直接上下级关系
     */
    @Select("SELECT * FROM customer_relationships WHERE customer_id = #{customerId} AND is_direct_relationship = true " +
            "AND status = 'ACTIVE' AND deleted_at IS NULL")
    List<CustomerRelationship> findDirectRelationships(@Param("customerId") Long customerId);
    
    /**
     * 查询所有总店
     */
    @Select("SELECT * FROM customer_relationships WHERE tenant_id = #{tenantId} AND is_headquarters = true " +
            "AND status = 'ACTIVE' AND deleted_at IS NULL ORDER BY created_at DESC")
    List<CustomerRelationship> findAllHeadquarters(@Param("tenantId") Long tenantId);
    
    /**
     * 根据层级路径查询
     */
    @Select("SELECT * FROM customer_relationships WHERE level_path LIKE CONCAT('%/', #{customerId}, '/%') " +
            "AND status = 'ACTIVE' AND deleted_at IS NULL")
    List<CustomerRelationship> findRelatedByLevelPath(@Param("customerId") Long customerId);
}
