package com.aiotagro.cattletrade.business.mapper;

import com.aiotagro.cattletrade.business.entity.XqClient;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
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;

/**
 * 项圈客户端Mapper接口
 *
 * @author System
 * @date 2025-01-16
 */
@Mapper
public interface XqClientMapper extends BaseMapper<XqClient> {

    /**
     * 查询项圈列表（带关联信息）
     */
    @Select("SELECT xc.id, xc.org_id, xc.uid, xc.sn AS deviceId, xc.sn, xc.state, " +
            "       xc.longitude, xc.latitude, xc.altitude, xc.gps_state, " +
            "       xc.nsat, xc.rsrp, xc.battery, xc.temperature, xc.steps, " +
            "       xc.acc_x, xc.acc_y, xc.acc_z, xc.bandge_status, xc.ver, " +
            "       xc.time, xc.uptime, xc.y_steps, xc.is_wear, xc.is_temperature, " +
            "       xc.source_id, xc.loctime, xc.subType, xc.frequency, " +
            "       xc.vehicle_id, xc.evening_frequency, xc.tenant_id, " +
            "       COALESCE(dd.delivery_id, 0) AS delivery_id, " +
            "       COALESCE(d.delivery_number, '未分配') AS delivery_number, " +
            "       COALESCE(d.license_plate, '未分配') AS license_plate " +
            "FROM xq_client xc " +
            "LEFT JOIN ( " +
            "    SELECT device_id, delivery_id, ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY id DESC) AS rn " +
            "    FROM delivery_device WHERE device_type = 3 " +
            ") dd ON CONCAT(xc.sn, '') = dd.device_id AND dd.rn = 1 " +
            "LEFT JOIN delivery d ON dd.delivery_id = d.id " +
            "WHERE (#{sn} IS NULL OR #{sn} = '' OR xc.sn LIKE CONCAT('%', #{sn}, '%')) " +
            "  AND (#{startNo} IS NULL OR #{startNo} = '' OR xc.sn >= #{startNo}) " +
            "  AND (#{endNo} IS NULL OR #{endNo} = '' OR xc.sn <= #{endNo}) " +
            "ORDER BY xc.id DESC")
    List<Map<String, Object>> selectXqClientListWithRelations(@Param("sn") String sn, 
                                                               @Param("startNo") String startNo, 
                                                               @Param("endNo") String endNo);
    
    /**
     * 查询未分配的智能项圈列表
     */
    @Select("SELECT xc.id, xc.org_id, xc.uid, xc.sn AS deviceId, xc.sn, xc.state, " +
            "       xc.longitude, xc.latitude, xc.altitude, xc.gps_state, " +
            "       xc.nsat, xc.rsrp, xc.battery, xc.temperature, xc.steps, " +
            "       xc.acc_x, xc.acc_y, xc.acc_z, xc.bandge_status, xc.ver, " +
            "       xc.time, xc.uptime, xc.y_steps, xc.is_wear, xc.is_temperature, " +
            "       xc.source_id, xc.loctime, xc.subType, xc.frequency, " +
            "       xc.vehicle_id, xc.evening_frequency, xc.tenant_id, " +
            "       0 AS delivery_id, '未分配' AS delivery_number, '未分配' AS license_plate " +
            "FROM xq_client xc " +
            "WHERE xc.deviceId NOT IN (" +
            "    SELECT DISTINCT device_id " +
            "    FROM delivery_device " +
            "    WHERE device_type = 3" +
            ") " +
            "AND (#{sn} IS NULL OR #{sn} = '' OR xc.sn LIKE CONCAT('%', #{sn}, '%')) " +
            "AND (#{startNo} IS NULL OR #{startNo} = '' OR xc.sn >= #{startNo}) " +
            "AND (#{endNo} IS NULL OR #{endNo} = '' OR xc.sn <= #{endNo}) " +
            "ORDER BY xc.id DESC")
    List<Map<String, Object>> selectUnassignedXqClientList(@Param("sn") String sn, 
                                                           @Param("startNo") String startNo, 
                                                           @Param("endNo") String endNo);
}

