package com.erp.customer.repository;

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

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

/**
 * 客户跟进记录数据访问接口
 */
@Mapper
public interface CustomerFollowUpRepository extends BaseMapper<CustomerFollowUp> {
    
    /**
     * 根据客户ID查询跟进记录
     */
    @Select("SELECT * FROM customer_follow_ups WHERE customer_id = #{customerId} AND deleted_at IS NULL " +
            "ORDER BY follow_up_at DESC LIMIT #{limit} OFFSET #{offset}")
    List<CustomerFollowUp> findByCustomerId(@Param("customerId") Long customerId, 
                                            @Param("offset") Long offset, @Param("limit") Integer limit);
    
    /**
     * 根据跟进人员查询
     */
    @Select("SELECT * FROM customer_follow_ups WHERE tenant_id = #{tenantId} AND follow_up_user_id = #{userId} " +
            "AND deleted_at IS NULL ORDER BY follow_up_at DESC LIMIT #{limit} OFFSET #{offset}")
    List<CustomerFollowUp> findByFollowUpUserId(@Param("tenantId") Long tenantId, @Param("userId") Long userId,
                                                 @Param("offset") Long offset, @Param("limit") Integer limit);
    
    /**
     * 根据跟进类型查询
     */
    @Select("SELECT * FROM customer_follow_ups WHERE tenant_id = #{tenantId} AND follow_type = #{followType} " +
            "AND deleted_at IS NULL ORDER BY follow_up_at DESC LIMIT #{limit} OFFSET #{offset}")
    List<CustomerFollowUp> findByFollowType(@Param("tenantId") Long tenantId, @Param("followType") String followType,
                                            @Param("offset") Long offset, @Param("limit") Integer limit);
    
    /**
     * 查询待跟进的记录
     */
    @Select("SELECT * FROM customer_follow_ups WHERE tenant_id = #{tenantId} AND status = 'PENDING' " +
            "AND next_follow_up_at <= #{before} AND deleted_at IS NULL ORDER BY next_follow_up_at ASC")
    List<CustomerFollowUp> findPendingFollowUps(@Param("tenantId") Long tenantId, @Param("before") LocalDateTime before);
    
    /**
     * 查询今天待跟进
     */
    @Select("SELECT * FROM customer_follow_ups WHERE tenant_id = #{tenantId} AND status = 'PENDING' " +
            "AND DATE(next_follow_up_at) = DATE(#{today}) AND deleted_at IS NULL ORDER BY next_follow_up_at ASC")
    List<CustomerFollowUp> findTodayFollowUps(@Param("tenantId") Long tenantId, @Param("today") LocalDateTime today);
    
    /**
     * 统计客户跟进次数
     */
    @Select("SELECT COUNT(*) FROM customer_follow_ups WHERE customer_id = #{customerId} AND deleted_at IS NULL")
    Long countByCustomerId(@Param("customerId") Long customerId);
    
    /**
     * 统计跟进人员跟进次数
     */
    @Select("SELECT COUNT(*) FROM customer_follow_ups WHERE tenant_id = #{tenantId} AND follow_up_user_id = #{userId} " +
            "AND deleted_at IS NULL")
    Long countByFollowUpUserId(@Param("tenantId") Long tenantId, @Param("userId") Long userId);
    
    /**
     * 查询时间范围内的跟进记录
     */
    @Select("SELECT * FROM customer_follow_ups WHERE tenant_id = #{tenantId} AND follow_up_at >= #{startAt} " +
            "AND follow_up_at <= #{endAt} AND deleted_at IS NULL ORDER BY follow_up_at DESC")
    List<CustomerFollowUp> findByDateRange(@Param("tenantId") Long tenantId, @Param("startAt") LocalDateTime startAt,
                                             @Param("endAt") LocalDateTime endAt);
    
    /**
     * 查询高意向客户
     */
    @Select("SELECT * FROM customer_follow_ups WHERE tenant_id = #{tenantId} AND intention_level = 'HIGH' " +
            "AND status != 'DONE' AND deleted_at IS NULL ORDER BY created_at DESC")
    List<CustomerFollowUp> findHighIntentionFollowUps(@Param("tenantId") Long tenantId);
    
    /**
     * 查询最近跟进记录
     */
    @Select("SELECT * FROM customer_follow_ups WHERE customer_id = #{customerId} AND deleted_at IS NULL " +
            "ORDER BY follow_up_at DESC LIMIT 1")
    CustomerFollowUp findLatestByCustomerId(@Param("customerId") Long customerId);
    
    /**
     * 统计各跟进类型数量
     */
    @Select("SELECT follow_type, COUNT(*) as count FROM customer_follow_ups WHERE tenant_id = #{tenantId} " +
            "AND deleted_at IS NULL GROUP BY follow_type")
    List<Object[]> countByTenantIdGroupByFollowType(@Param("tenantId") Long tenantId);
}
