package com.aiotagro.cattletrade.business.mapper;

import org.apache.ibatis.annotations.CacheNamespace;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * Member Driver Mapper
 *
 * @author System
 * @date 2025-01-16
 */
@Mapper
@CacheNamespace(flushInterval = 0) // Disable cache to force fresh queries
public interface MemberDriverMapper {

    /**
     * 查询司机列表（关联member表获取手机号）
     */
    @Select("SELECT md.id, md.member_id, md.username, " +
            "md.driving_license, md.driver_license, md.record_code, " +
            "md.car_img, md.id_card, md.remark, md.create_time, m.mobile, m.status " +
            "FROM member_driver md " +
            "LEFT JOIN member m ON md.member_id = m.id " +
            "ORDER BY md.id DESC")
    List<Map<String, Object>> selectDriverList();

    /**
     * 根据用户名搜索司机列表（关联member表获取手机号）
     */
    @Select("<script>" +
            "SELECT md.id, md.member_id, md.username, " +
            "md.driving_license, md.driver_license, md.record_code, " +
            "md.car_img, md.id_card, md.remark, md.create_time, m.mobile, m.status " +
            "FROM member_driver md " +
            "LEFT JOIN member m ON md.member_id = m.id " +
            "<if test='username != null and username != \"\"'>" +
            "WHERE md.username LIKE CONCAT('%', #{username}, '%') OR m.mobile LIKE CONCAT('%', #{username}, '%') " +
            "</if>" +
            "ORDER BY md.id DESC " +
            "</script>")
    List<Map<String, Object>> selectDriverListByUsername(@Param("username") String username);

    /**
     * 根据用户名和手机号搜索司机列表（支持精确查询）
     */
    @Select("SELECT md.id, md.member_id, md.username, " +
            "md.driving_license, md.driver_license, md.record_code, " +
            "md.car_img, md.id_card, md.remark, md.create_time, m.mobile, m.status " +
            "FROM member_driver md " +
            "LEFT JOIN member m ON md.member_id = m.id " +
            "WHERE m.mobile = #{mobile} " +
            "ORDER BY md.id DESC")
    List<Map<String, Object>> selectDriverListByMobile(@Param("mobile") String mobile);

    /**
     * 根据用户名和手机号搜索司机列表（支持精确查询）
     */
    @Select("<script>" +
            "SELECT md.id, md.member_id, md.username, " +
            "md.driving_license, md.driver_license, md.record_code, " +
            "md.car_img, md.id_card, md.remark, md.create_time, m.mobile, m.status " +
            "FROM member_driver md " +
            "LEFT JOIN member m ON md.member_id = m.id " +
            "WHERE 1=1 " +
            "<if test='username != null and username != \"\"'>" +
            "  AND md.username LIKE CONCAT('%', #{username}, '%') " +
            "</if>" +
            "<if test='mobile != null and mobile != \"\"'>" +
            "  AND m.mobile = #{mobile} " +
            "</if>" +
            "ORDER BY md.id DESC " +
            "</script>")
    List<Map<String, Object>> selectDriverListBySearch(@Param("username") String username, 
                                                       @Param("mobile") String mobile, 
                                                       @Param("mobileExact") Boolean mobileExact);

    /**
     * 根据司机ID查询司机信息（关联member表获取手机号）
     */
    @Select("SELECT md.id, md.member_id, md.username, " +
            "md.driving_license, md.driver_license, md.record_code, " +
            "md.car_img, md.id_card, md.remark, md.create_time, m.mobile, m.status " +
            "FROM member_driver md " +
            "LEFT JOIN member m ON md.member_id = m.id " +
            "WHERE md.id = #{driverId}")
    Map<String, Object> selectDriverById(@Param("driverId") Integer driverId);

    /**
     * 批量根据司机ID查询司机信息（关联member表获取手机号）
     * 用于优化性能，避免 N+1 查询问题
     */
    @Select("<script>" +
            "SELECT md.id, md.member_id, md.username, " +
            "md.driving_license, md.driver_license, md.record_code, " +
            "md.car_img, md.id_card, md.remark, md.create_time, m.mobile, m.status " +
            "FROM member_driver md " +
            "LEFT JOIN member m ON md.member_id = m.id " +
            "WHERE md.id IN " +
            "<foreach collection='driverIds' item='id' open='(' separator=',' close=')'>" +
            "   #{id}" +
            "</foreach>" +
            "</script>")
    List<Map<String, Object>> selectDriverByIds(@Param("driverIds") List<Integer> driverIds);

    /**
     * 根据车牌号查询司机信息（已废弃：司机表不再有车牌号字段）
     * 该方法保留是为了兼容性，但会返回 null
     */
    @Select("SELECT md.id, md.member_id, md.username, " +
            "md.driving_license, md.driver_license, md.record_code, " +
            "md.car_img, md.id_card, md.remark, md.create_time, m.mobile, m.status " +
            "FROM member_driver md " +
            "LEFT JOIN member m ON md.member_id = m.id " +
            "WHERE 1=0 " +
            "ORDER BY md.create_time DESC LIMIT 1")
    Map<String, Object> selectDriverByPlate(@Param("licensePlate") String licensePlate);

    /**
     * 新增司机信息
     */
    @org.apache.ibatis.annotations.Insert("INSERT INTO member_driver (member_id, username, " +
            "driver_license, driving_license, car_img, record_code, id_card, remark, create_time) " +
            "VALUES (#{memberId}, #{username}, #{driverLicense}, #{drivingLicense}, " +
            "#{carImg}, #{recordCode}, #{idCard}, #{remark}, NOW())")
    int insertDriver(@Param("memberId") Integer memberId, @Param("username") String username, 
                     @Param("driverLicense") String driverLicense, 
                     @Param("drivingLicense") String drivingLicense, @Param("carImg") String carImg, 
                     @Param("recordCode") String recordCode, @Param("idCard") String idCard, @Param("remark") String remark);

    /**
     * 更新司机信息
     */
    @org.apache.ibatis.annotations.Update("UPDATE member_driver SET username = #{username}, " +
            "driver_license = #{driverLicense}, driving_license = #{drivingLicense}, car_img = #{carImg}, " +
            "record_code = #{recordCode}, id_card = #{idCard}, remark = #{remark} WHERE id = #{id}")
    int updateDriver(@Param("id") Integer id, @Param("username") String username,
                    @Param("driverLicense") String driverLicense, @Param("drivingLicense") String drivingLicense,
                    @Param("carImg") String carImg, @Param("recordCode") String recordCode, @Param("idCard") String idCard, @Param("remark") String remark);

    /**
     * 根据司机姓名和车牌号查询司机信息（已废弃：司机表不再有车牌号字段）
     * 该方法保留是为了兼容性，但会返回 null
     */
    @Select("SELECT md.id, md.member_id, md.username, " +
            "md.driving_license, md.driver_license, md.record_code, " +
            "md.car_img, md.id_card, md.remark, md.create_time, m.mobile " +
            "FROM member_driver md " +
            "LEFT JOIN member m ON md.member_id = m.id " +
            "WHERE 1=0")
    Map<String, Object> selectDriverByNameAndPlate(@Param("driverName") String driverName, 
                                                  @Param("licensePlate") String licensePlate);

    /**
     * 删除司机信息
     */
    @org.apache.ibatis.annotations.Delete("DELETE FROM member_driver WHERE id = #{id}")
    int deleteDriver(@Param("id") Integer id);
}

