package com.erp.customer.repository;

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

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;

/**
 * 客户数据访问接口
 */
@Mapper
public interface CustomerRepository extends BaseMapper<Customer> {
    
    /**
     * 根据客户编码查询
     */
    @Select("SELECT * FROM customers WHERE customer_code = #{customerCode} AND deleted_at IS NULL")
    Customer findByCustomerCode(@Param("customerCode") String customerCode);
    
    /**
     * 根据租户ID和客户编码查询
     */
    @Select("SELECT * FROM customers WHERE tenant_id = #{tenantId} AND customer_code = #{customerCode} AND deleted_at IS NULL")
    Customer findByTenantIdAndCustomerCode(@Param("tenantId") Long tenantId, @Param("customerCode") String customerCode);
    
    /**
     * 分页查询客户列表
     */
    @Select("SELECT * FROM customers WHERE tenant_id = #{tenantId} AND deleted_at IS NULL " +
            "ORDER BY created_at DESC LIMIT #{limit} OFFSET #{offset}")
    List<Customer> findByTenantId(@Param("tenantId") Long tenantId, @Param("offset") Long offset, @Param("limit") Integer limit);
    
    /**
     * 根据客户等级查询
     */
    @Select("SELECT * FROM customers WHERE tenant_id = #{tenantId} AND level = #{level} AND deleted_at IS NULL " +
            "ORDER BY created_at DESC LIMIT #{limit} OFFSET #{offset}")
    List<Customer> findByTenantIdAndLevel(@Param("tenantId") Long tenantId, @Param("level") String level, 
                                           @Param("offset") Long offset, @Param("limit") Integer limit);
    
    /**
     * 根据客户状态查询
     */
    @Select("SELECT * FROM customers WHERE tenant_id = #{tenantId} AND status = #{status} AND deleted_at IS NULL " +
            "ORDER BY created_at DESC LIMIT #{limit} OFFSET #{offset}")
    List<Customer> findByTenantIdAndStatus(@Param("tenantId") Long tenantId, @Param("status") String status,
                                            @Param("offset") Long offset, @Param("limit") Integer limit);
    
    /**
     * 根据负责销售查询
     */
    @Select("SELECT * FROM customers WHERE tenant_id = #{tenantId} AND sales_id = #{salesId} AND deleted_at IS NULL " +
            "ORDER BY created_at DESC LIMIT #{limit} OFFSET #{offset}")
    List<Customer> findByTenantIdAndSalesId(@Param("tenantId") Long tenantId, @Param("salesId") Long salesId,
                                             @Param("offset") Long offset, @Param("limit") Integer limit);
    
    /**
     * 模糊搜索客户名称
     */
    @Select("SELECT * FROM customers WHERE tenant_id = #{tenantId} AND customer_name LIKE CONCAT('%', #{keyword}, '%') " +
            "AND deleted_at IS NULL ORDER BY created_at DESC LIMIT #{limit} OFFSET #{offset}")
    List<Customer> searchByCustomerName(@Param("tenantId") Long tenantId, @Param("keyword") String keyword,
                                        @Param("offset") Long offset, @Param("limit") Integer limit);
    
    /**
     * 查询客户的所有分店
     */
    @Select("SELECT * FROM customers WHERE parent_id = #{parentId} AND deleted_at IS NULL " +
            "ORDER BY relationship_level ASC, created_at DESC")
    List<Customer> findBranchesByParentId(@Param("parentId") Long parentId);
    
    /**
     * 查询客户的上级（总店）
     */
    @Select("SELECT * FROM customers WHERE id = (SELECT parent_id FROM customers WHERE id = #{customerId} AND deleted_at IS NULL) " +
            "AND deleted_at IS NULL")
    Customer findParentByCustomerId(@Param("customerId") Long customerId);
    
    /**
     * 统计客户总数
     */
    @Select("SELECT COUNT(*) FROM customers WHERE tenant_id = #{tenantId} AND deleted_at IS NULL")
    Long countByTenantId(@Param("tenantId") Long tenantId);
    
    /**
     * 统计各等级客户数量
     */
    @Select("SELECT level, COUNT(*) as count FROM customers WHERE tenant_id = #{tenantId} AND deleted_at IS NULL " +
            "GROUP BY level")
    List<Object[]> countByTenantIdGroupByLevel(@Param("tenantId") Long tenantId);
    
    /**
     * 统计各状态客户数量
     */
    @Select("SELECT status, COUNT(*) as count FROM customers WHERE tenant_id = #{tenantId} AND deleted_at IS NULL " +
            "GROUP BY status")
    List<Object[]> countByTenantIdGroupByStatus(@Param("tenantId") Long tenantId);
    
    /**
     * 统计各销售负责的客户数量
     */
    @Select("SELECT sales_id, sales_name, COUNT(*) as count FROM customers WHERE tenant_id = #{tenantId} " +
            "AND deleted_at IS NULL GROUP BY sales_id, sales_name ORDER BY count DESC")
    List<Object[]> countByTenantIdGroupBySales(@Param("tenantId") Long tenantId);
    
    /**
     * 查询最近联系过的客户
     */
    @Select("SELECT * FROM customers WHERE tenant_id = #{tenantId} AND last_contact_at >= #{since} " +
            "AND deleted_at IS NULL ORDER BY last_contact_at DESC")
    List<Customer> findRecentlyContacted(@Param("tenantId") Long tenantId, @Param("since") LocalDateTime since);
    
    /**
     * 查询超过指定天数未联系客户
     */
    @Select("SELECT * FROM customers WHERE tenant_id = #{tenantId} AND (last_contact_at IS NULL OR last_contact_at < #{before}) " +
            "AND deleted_at IS NULL ORDER BY last_contact_at ASC")
    List<Customer> findLongTimeNoContact(@Param("tenantId") Long tenantId, @Param("before") LocalDateTime before);
    
    /**
     * 统计客户总余额
     */
    @Select("SELECT COALESCE(SUM(balance), 0) FROM customers WHERE tenant_id = #{tenantId} AND deleted_at IS NULL")
    BigDecimal sumBalanceByTenantId(@Param("tenantId") Long tenantId);
    
    /**
     * 查询VIP客户列表
     */
    @Select("SELECT * FROM customers WHERE tenant_id = #{tenantId} AND level = 'VIP' AND deleted_at IS NULL " +
            "ORDER BY rating DESC, balance DESC LIMIT #{limit}")
    List<Customer> findTopVipCustomers(@Param("tenantId") Long tenantId, @Param("limit") Integer limit);
    
    /**
     * 查询客户总店
     */
    @Select("SELECT * FROM customers WHERE id = (SELECT parent_id FROM customers WHERE id = #{customerId} AND deleted_at IS NULL) " +
            "AND is_headquarters = true AND deleted_at IS NULL")
    Customer findHeadquartersByCustomerId(@Param("customerId") Long customerId);
    
    /**
     * 查询所有层级路径包含指定客户ID的记录（查找所有关联）
     */
    @Select("SELECT * FROM customers WHERE level_path LIKE CONCAT('%/', #{customerId}, '/%') AND deleted_at IS NULL")
    List<Customer> findRelatedByLevelPath(@Param("customerId") Long customerId);
}
