<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.dromara.rfid.mapper.RfidReadRecordMapper">

    <resultMap type="org.dromara.rfid.domain.vo.RfidReadRecordVo" id="RfidReadRecordResult">
    </resultMap>

    <!-- 查询列表（单表） -->
    <select id="selectCustomRfidReadRecordVoList" resultMap="RfidReadRecordResult">
        select t.id,
               t.device_id,
               d.device_code as deviceCode,
               d.device_name as deviceName,
               t.read_status,
               TRIM(t.barcode) as barcode,
               t.record_time,
               t.alarm_flag,
               t.alarm_level,
               t.alarm_type,
               t.alarm_action
        from ${tableName} t
        left join rfid_device d on t.device_id = d.id
        <if test="ew != null">
            ${ew.customSqlSegment}
        </if>
        order by t.record_time desc
    </select>

    <!-- 查询列表（多表联合查询，跨日期范围） -->
    <!-- 优化：先在子查询中过滤数据，再关联设备表，减少 JOIN 数据量 -->
    <select id="selectCustomRfidReadRecordVoListMultiTable" resultMap="RfidReadRecordResult">
        select combined.id,
               combined.device_id,
               d.device_code as deviceCode,
               d.device_name as deviceName,
               combined.read_status,
               TRIM(combined.barcode) as barcode,
               combined.record_time,
               combined.alarm_flag,
               combined.alarm_level,
               combined.alarm_type,
               combined.alarm_action
        from (
            <foreach collection="tableNames" item="tbl" separator=" UNION ALL ">
                select t.id,
                       t.device_id,
                       t.read_status,
                       TRIM(t.barcode) as barcode,
                       t.record_time,
                       t.alarm_flag,
                       t.alarm_level,
                       t.alarm_type,
                       t.alarm_action
                from ${tbl} t
                <if test="ew != null">
                    ${ew.customSqlSegment}
                </if>
            </foreach>
        ) combined
        left join rfid_device d on combined.device_id = d.id
        order by combined.record_time desc
    </select>

    <!-- 根据ID查询详情 -->
    <select id="selectCustomRfidReadRecordVoById" resultMap="RfidReadRecordResult">
        select t.id,
               t.device_id,
               d.device_code as deviceCode,
               d.device_name as deviceName,
               t.read_status,
               TRIM(t.barcode) as barcode,
               t.record_time,
               t.alarm_flag,
               t.alarm_level,
               t.alarm_type,
               t.alarm_action
        from ${tableName} t
        left join rfid_device d on t.device_id = d.id
        where t.id = #{id}
    </select>

    <!-- 批量查询 - 根据ID列表 -->
    <select id="selectCustomRfidReadRecordVoByIds" resultMap="RfidReadRecordResult">
        select t.id,
               t.device_id,
               d.device_code as deviceCode,
               d.device_name as deviceName,
               t.read_status,
               TRIM(t.barcode) as barcode,
               t.record_time,
               t.alarm_flag,
               t.alarm_level,
               t.alarm_type,
               t.alarm_action
        from ${tableName} t
        left join rfid_device d on t.device_id = d.id
        where t.id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <!-- 统计查询（单表） -->
    <select id="countCustomRfidReadRecord" resultType="java.lang.Long">
        select count(1)
        from ${tableName} t
        <if test="ew != null">
            ${ew.customSqlSegment}
        </if>
    </select>

    <!-- 统计查询（多表） -->
    <select id="countCustomRfidReadRecordMultiTable" resultType="java.lang.Long">
        select sum(cnt)
        from (
            <foreach collection="tableNames" item="tbl" separator=" UNION ALL ">
                select count(1) as cnt from ${tbl} t
                <if test="ew != null">
                    ${ew.customSqlSegment}
                </if>
            </foreach>
        ) tmp
    </select>

    <!-- 分页查询（单表） -->
    <select id="selectCustomRfidReadRecordVoPage" resultMap="RfidReadRecordResult">
        select t.id,
               t.device_id,
               d.device_code as deviceCode,
               d.device_name as deviceName,
               t.read_status,
               TRIM(t.barcode) as barcode,
               t.record_time,
               t.alarm_flag,
               t.alarm_level,
               t.alarm_type,
               t.alarm_action
        from ${tableName} t
        left join rfid_device d on t.device_id = d.id
        <if test="ew != null">
            ${ew.customSqlSegment}
        </if>
        order by t.record_time desc
    </select>

    <!-- 分页查询（多表联合查询，跨日期范围） -->
    <!-- 优化：先在子查询中过滤数据，再关联设备表，减少 JOIN 数据量 -->
    <select id="selectCustomRfidReadRecordVoPageMultiTable" resultMap="RfidReadRecordResult">
        select combined.id,
               combined.device_id,
               d.device_code as deviceCode,
               d.device_name as deviceName,
               combined.read_status,
               TRIM(combined.barcode) as barcode,
               combined.record_time,
               combined.alarm_flag,
               combined.alarm_level,
               combined.alarm_type,
               combined.alarm_action
        from (
            <foreach collection="tableNames" item="tbl" separator=" UNION ALL ">
                select t.id,
                       t.device_id,
                       t.read_status,
                       TRIM(t.barcode) as barcode,
                       t.record_time,
                       t.alarm_flag,
                       t.alarm_level,
                       t.alarm_type,
                       t.alarm_action
                from ${tbl} t
                <if test="ew != null">
                    ${ew.customSqlSegment}
                </if>
            </foreach>
        ) combined
        left join rfid_device d on combined.device_id = d.id
        order by combined.record_time desc
    </select>

    <!-- 单条插入 -->
    <insert id="insertRfidReadRecord">
        insert into ${tableName}(
            id,
            device_id,
            read_status,
            barcode,
            record_time,
            alarm_flag,
            alarm_level,
            alarm_type,
            alarm_action
        )
        values (
            #{entity.id},
            #{entity.deviceId},
            #{entity.readStatus},
            #{entity.barcode},
            #{entity.recordTime},
            #{entity.alarmFlag},
            #{entity.alarmLevel},
            #{entity.alarmType},
            #{entity.alarmAction}
        )
    </insert>

    <!-- 批量插入 -->
    <insert id="batchInsertRfidReadRecord">
        insert into ${tableName}(
            id,
            device_id,
            read_status,
            barcode,
            record_time,
            alarm_flag,
            alarm_level,
            alarm_type,
            alarm_action
        )
        values
        <foreach collection="list" item="item" separator=",">
            (
            #{item.id},
            #{item.deviceId},
            #{item.readStatus},
            #{item.barcode},
            #{item.recordTime},
            #{item.alarmFlag},
            #{item.alarmLevel},
            #{item.alarmType},
            #{item.alarmAction}
            )
        </foreach>
    </insert>

    <!-- 单条更新 -->
    <update id="updateRfidReadRecordById">
        update ${tableName}
        <set>
            <if test="entity.deviceId != null">
                device_id = #{entity.deviceId},
            </if>
            <if test="entity.readStatus != null and entity.readStatus != ''">
                read_status = #{entity.readStatus},
            </if>
            <if test="entity.barcode != null">
                barcode = #{entity.barcode},
            </if>
            <if test="entity.recordTime != null">
                record_time = #{entity.recordTime},
            </if>
            <if test="entity.alarmFlag != null">
                alarm_flag = #{entity.alarmFlag},
            </if>
            <if test="entity.alarmLevel != null">
                alarm_level = #{entity.alarmLevel},
            </if>
            <if test="entity.alarmType != null">
                alarm_type = #{entity.alarmType},
            </if>
            <if test="entity.alarmAction != null">
                alarm_action = #{entity.alarmAction},
            </if>
        </set>
        where id = #{entity.id}
    </update>

    <!-- 批量更新 -->
    <update id="batchUpdateRfidReadRecord">
        <foreach collection="list" item="item" separator=";">
            update ${tableName}
            <set>
                <if test="item.deviceId != null">
                    device_id = #{item.deviceId},
                </if>
                <if test="item.readStatus != null and item.readStatus != ''">
                    read_status = #{item.readStatus},
                </if>
                <if test="item.barcode != null">
                    barcode = #{item.barcode},
                </if>
                <if test="item.recordTime != null">
                    record_time = #{item.recordTime},
                </if>
                <if test="item.alarmFlag != null">
                    alarm_flag = #{item.alarmFlag},
                </if>
                <if test="item.alarmLevel != null">
                    alarm_level = #{item.alarmLevel},
                </if>
                <if test="item.alarmType != null">
                    alarm_type = #{item.alarmType},
                </if>
                <if test="item.alarmAction != null">
                    alarm_action = #{item.alarmAction},
                </if>
            </set>
            where id = #{item.id}
        </foreach>
    </update>

    <!-- 根据自定义条件删除 -->
    <delete id="deleteCustomRfidReadRecord">
        delete from ${tableName}
        <if test="ew != null">
            ${ew.customSqlSegment}
        </if>
    </delete>

    <!-- 根据ID列表批量删除 -->
    <delete id="deleteCustomRfidReadRecordByIds">
        delete from ${tableName}
        where id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!-- 检查是否存在（单表） -->
    <!-- 优化：使用 EXISTS + LIMIT 1，找到一条即返回 -->
    <select id="existsRfidReadRecord" resultType="java.lang.Boolean">
        select exists (
            select 1 from ${tableName} t
            <if test="ew != null">
                ${ew.customSqlSegment}
            </if>
            limit 1
        )
    </select>

    <!-- 检查是否存在（多表） -->
    <!-- 优化：使用 EXISTS + LIMIT 1，找到一条即返回，避免全表扫描 -->
    <select id="existsRfidReadRecordMultiTable" resultType="java.lang.Boolean">
        select exists (
            select 1 from (
                <foreach collection="tableNames" item="tbl" separator=" UNION ALL ">
                    select 1 from ${tbl} t
                    <if test="ew != null">
                        ${ew.customSqlSegment}
                    </if>
                    limit 1
                </foreach>
            ) tmp limit 1
        )
    </select>

    <!-- ==================== 看板专用查询 ==================== -->

    <!-- 查询每个设备的最新一条读取记录 -->
    <select id="selectLatestRecordByDevice" resultMap="RfidReadRecordResult">
        select t.id,
               t.device_id,
               d.device_code as deviceCode,
               d.device_name as deviceName,
               t.read_status,
               TRIM(t.barcode) as barcode,
               t.record_time,
               t.alarm_flag,
               t.alarm_level,
               t.alarm_type,
               t.alarm_action
        from ${tableName} t
        inner join (
            select device_id, max(record_time) as max_time
            from ${tableName}
            <where>
                <if test="deviceIds != null and deviceIds.size() > 0">
                    device_id in
                    <foreach collection="deviceIds" item="deviceId" open="(" separator="," close=")">
                        #{deviceId}
                    </foreach>
                </if>
            </where>
            group by device_id
        ) latest on t.device_id = latest.device_id and t.record_time = latest.max_time
        left join rfid_device d on t.device_id = d.id
        order by t.device_id
    </select>

    <!-- 按小时统计成功率 -->
    <select id="selectSuccessRateByHour" resultType="java.util.Map">
        select
            DATE_FORMAT(record_time, '%H:00') as timePoint,
            ROUND(SUM(CASE WHEN read_status = '1' THEN 1 ELSE 0 END) * 100.0 / COUNT(1), 2) as successRate,
            COUNT(1) as totalCount,
            SUM(CASE WHEN read_status = '1' THEN 1 ELSE 0 END) as successCount
        from ${tableName}
        where record_time between #{startTime} and #{endTime}
        group by DATE_FORMAT(record_time, '%H:00')
        order by timePoint
    </select>

    <!-- 查询告警记录列表 -->
    <select id="selectAlarmRecordList" resultMap="RfidReadRecordResult">
        select t.id,
               t.device_id,
               d.device_code as deviceCode,
               d.device_name as deviceName,
               l.location_alias as locationAlias,
               t.read_status,
               TRIM(t.barcode) as barcode,
               t.record_time,
               t.alarm_flag,
               t.alarm_level,
               t.alarm_type,
               t.alarm_action
        from ${tableName} t
        left join rfid_device d on t.device_id = d.id
        left join rfid_location l on d.location_id = l.id
        where t.alarm_flag = '1'
        order by t.record_time desc
        <if test="limit != null">
            limit #{limit}
        </if>
    </select>

    <!-- ==================== 采样查询（无索引依赖，大数据量优化） ==================== -->

    <!-- 采样查询（单表）：每 N 分钟取一条代表数据 -->
    <select id="selectWithSampling" resultMap="RfidReadRecordResult">
        select t1.id,
               t1.device_id,
               d.device_code as deviceCode,
               d.device_name as deviceName,
               t1.read_status,
               TRIM(t1.barcode) as barcode,
               t1.record_time,
               t1.alarm_flag,
               t1.alarm_level,
               t1.alarm_type,
               t1.alarm_action
        from ${tableName} t1
        left join rfid_device d on t1.device_id = d.id
        inner join (
            <!-- 子查询：按 device_id + time_slot 分组，取每个时间槽的最新记录 -->
            select
                t.device_id,
                <!-- 使用 UNIX_TIMESTAMP 将时间转为秒，再除以采样间隔（分钟*60）并取 FLOOR，得到离散的时间槽编号 -->
                FLOOR(UNIX_TIMESTAMP(t.record_time) / (#{samplingInterval} * 60)) as time_slot,
                MAX(t.record_time) as max_time
            from ${tableName} t
            <if test="ew != null">
                ${ew.customSqlSegment}
            </if>
            group by t.device_id, time_slot
        ) t2 on t1.device_id = t2.device_id and t1.record_time = t2.max_time
        order by t1.device_id asc, t1.record_time desc
    </select>

    <!-- 采样查询（多表）：每 N 分钟取一条代表数据 -->
    <select id="selectWithSamplingMultiTable" resultMap="RfidReadRecordResult">
        select sampled.id,
               sampled.device_id,
               d.device_code as deviceCode,
               d.device_name as deviceName,
               sampled.read_status,
               TRIM(sampled.barcode) as barcode,
               sampled.record_time,
               sampled.alarm_flag,
               sampled.alarm_level,
               sampled.alarm_type,
               sampled.alarm_action
        from (
            <foreach collection="tableNames" item="tbl" separator=" UNION ALL ">
                select t1.id,
                       t1.device_id,
                       t1.read_status,
                       TRIM(t1.barcode) as barcode,
                       t1.record_time,
                       t1.alarm_flag,
                       t1.alarm_level,
                       t1.alarm_type,
                       t1.alarm_action
                from ${tbl} t1
                inner join (
                    select
                        t.device_id,
                        <!-- 同上：将 record_time 映射到按采样间隔划分的时间槽，用于每个设备每个时间槽只保留一条最新记录 -->
                        FLOOR(UNIX_TIMESTAMP(t.record_time) / (#{samplingInterval} * 60)) as time_slot,
                        MAX(t.record_time) as max_time
                    from ${tbl} t
                    <if test="ew != null">
                        ${ew.customSqlSegment}
                    </if>
                    group by t.device_id, time_slot
                ) t2 on t1.device_id = t2.device_id and t1.record_time = t2.max_time
            </foreach>
        ) sampled
        left join rfid_device d on sampled.device_id = d.id
        order by sampled.device_id asc, sampled.record_time desc
    </select>

</mapper>
