<?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="com.fastbee.iot.mapper.TDDeviceLogMapper">

    <resultMap type="com.fastbee.iot.model.MonitorModel" id="MonitorResult">
        <result property="value"    column="log_value"    />
        <result property="time"    column="ts"    />
    </resultMap>

    <resultMap type="com.fastbee.iot.domain.DeviceLog" id="DeviceLogResult">
        <result property="logType"    column="log_type"    />
        <result property="logValue"    column="log_value"    />
        <result property="mode"    column="mode"    />
        <result property="deviceId"    column="device_id"    />
        <result property="deviceName"    column="device_name"    />
        <result property="serialNumber"    column="serial_number"    />
        <result property="identify"    column="identify"    />
        <result property="createBy"    column="create_by"    />
        <result property="isMonitor"    column="is_monitor"    />
        <result property="createTime"    column="ts"    />
        <result property="userId"    column="user_id"    />
        <result property="userName"    column="user_name"    />
        <result property="tenantId"    column="tenant_id"    />
        <result property="tenantName"    column="tenant_name"    />
        <result property="remark"    column="remark"    />
    </resultMap>

    <resultMap type="com.fastbee.iot.model.HistoryModel" id="HistoryResult">
        <result property="time"    column="ts"    />
        <result property="value"    column="log_value"    />
        <result property="identify" column="identify" />
        <result property="moderName" column="mode" />
    </resultMap>

    <update id="createDB">
        create database if not exists  ${database} vgroups 4;
    </update>

    <update id="createSTable">
        create STABLE  if not exists ${database}.device_log
        (ts timestamp,
        log_value BINARY(100),
        is_monitor TINYINT,
        log_type TINYINT,
        identify BINARY(100),
        mode TINYINT,
        remark BINARY(500),
        tenant_id BIGINT)
        TAGS(serial_number BINARY(50));
    </update>

    <insert id="save" parameterType="com.fastbee.iot.domain.DeviceLog" useGeneratedKeys="false">
        insert into ${database}.device_${device.serialNumber} using device_log
        tags (#{device.serialNumber})
        values (now,
            #{device.logValue},
            #{device.isMonitor},
            #{device.logType},
            #{device.identify},
            #{device.mode},
            #{device.remark},
            #{device.tenantId});
    </insert>

    <insert id="saveBatch" parameterType="com.fastbee.iot.tdengine.service.model.TdLogDto" useGeneratedKeys="false">
        insert into ${database}.device_${data.serialNumber} using device_log
        tags (#{data.serialNumber})
        values
        <foreach collection="data.list" separator=" " item="device" index="index">
            (now,
            #{device.logValue},
            #{device.isMonitor},
            #{device.logType},
            #{device.identify},
            #{device.mode},
            #{device.remark},
            #{device.tenantId})
        </foreach>
    </insert>

    <delete id="deleteDeviceLogByDeviceNumber" parameterType="com.fastbee.iot.domain.DeviceLog">
        DROP TABLE IF EXISTS ${database}.device_${serialNumber};
    </delete>

    <select id="selectPropertyLogCount" parameterType="com.fastbee.iot.domain.Device" resultType="Long">
        select count(mode) as propertyCount
        from ${database}.device_log
        where log_type=1
        <if test="device.tenantId != null"> and tenant_id = #{device.tenantId}</if>
    </select>

    <select id="selectFunctionLogCount" parameterType="com.fastbee.iot.domain.Device" resultType="Long">
        select count(mode) as functionCount
        from ${database}.device_log
        where log_type=2
        <if test="device.tenantId != null"> and tenant_id = #{device.tenantId}</if>
    </select>

    <select id="selectEventLogCount" parameterType="com.fastbee.iot.domain.Device" resultType="Long">
        select count(mode) as eventCount
        from ${database}.device_log
        where log_type=3
        <if test="device.tenantId != null"> and tenant_id = #{device.tenantId}</if>
    </select>

    <select id="selectMonitorLogCount" parameterType="com.fastbee.iot.domain.Device" resultType="Long">
        select count(mode) as monitorCount
        from ${database}.device_log
        where log_type=1 and is_monitor=1
        <if test="device.tenantId != null"> and tenant_id = #{device.tenantId}</if>
    </select>

    <select id="selectMonitorList" parameterType="com.fastbee.iot.domain.DeviceLog" resultMap="MonitorResult">
        select log_value, ts from ${database}.device_log
        <where>
             is_monitor=1
            <if test="device.serialNumber != null and device.serialNumber !=''"> and serial_number = #{device.serialNumber}</if>
            <if test="device.identify != null  and device.identify != ''"> and identify like  #{device.identify}</if>
            <if test="device.beginTime != null and device.beginTime != '' and device.endTime != null and device.endTime != ''"> and ts between #{device.beginTime} and #{device.endTime}</if>
        order by ts desc
        limit #{device.total}
        </where>
    </select>

    <select id="selectDeviceLogList" parameterType="com.fastbee.iot.domain.DeviceLog" resultMap="DeviceLogResult">
        select * from ${database}.device_log
        <where>
            <if test="device.isMonitor != null"> and is_monitor = #{device.isMonitor}</if>
            <if test="device.serialNumber != null and device.serialNumber !=''"> and serial_number = #{device.serialNumber}</if>
            <if test="device.logType != null "> and log_type = #{device.logType}</if>
            <if test="device.logType == null "> and log_type != 7</if>
            <if test="device.identify != null  and device.identify != ''"> and identify like #{device.identify}</if>
        </where>
        order by ts desc
    </select>

    <select id="selectEventLogList" parameterType="com.fastbee.iot.domain.DeviceLog" resultMap="DeviceLogResult">
        select * from ${database}.device_log
        <where>

            <if test="device.isMonitor != null"> and is_monitor = #{device.isMonitor}</if>
            <if test="device.logType != null "> and log_type = #{device.logType}</if>
            <if test="device.logType == null "> and log_type != 1
                and log_type != 2
                and log_type != 4
                and log_type != 7 </if>
            <if test="device.serialNumber != null and device.serialNumber !=''"> and serial_number = #{device.serialNumber}</if>
            <if test="device.identify != null  and device.identify != ''"> and identify like #{device.identify}</if>
            <if test="device.beginTime != null and device.beginTime != '' and device.endTime != null and device.endTime != ''">
                and ts between #{device.beginTime} and #{device.endTime}
            </if>
        </where>
        order by ts desc
    </select>

    <select id="selectHistoryList" parameterType="com.fastbee.iot.domain.DeviceLog" resultMap="HistoryResult">
        select ts,
               log_value,
               identify,
               mode
        from ${database}.device_log
        <where>
        <if test="device.beginTime != null and device.beginTime != '' and device.endTime != null and device.endTime != ''">
            and ts between #{device.beginTime} and #{device.endTime}
        </if>
            <if test="device.serialNumber != null and device.serialNumber !=''"> and serial_number = #{device.serialNumber}</if>
        </where>
        order by ts desc
    </select>

    <select id="listHistory" parameterType="com.fastbee.iot.domain.DeviceLog" resultMap="HistoryResult">
        select  ts,
        log_value,
        identify,
        mode
        from ${database}.device_log
        <where>
            <if test="device.beginTime != null and device.beginTime != '' and device.endTime != null and device.endTime != ''">
                and ts between #{device.beginTime} and #{device.endTime}
            </if>
            <if test="device.serialNumber != null and device.serialNumber !=''">
                and serial_number = #{device.serialNumber}
            </if>
            <if test="device.identityList != null and device.identityList.size > 0">
                and identify in
                <foreach collection="device.identityList" item="identify" open="(" separator="," close=")">
                    #{identify}
                </foreach>
            </if>
            <if test="device.logType != null">
                and log_type = #{device.logType}
            </if>
        </where>
        order by ts desc
    </select>

    <select id="listhistoryGroupByCreateTime" resultType="com.fastbee.iot.model.HistoryModel">
        select ts as time,  identify, log_value as value
        from ${database}.device_log
        <where>
            <if test="device.beginTime != null and device.beginTime != '' and device.endTime != null and device.endTime != ''">
                and ts between #{device.beginTime} and #{device.endTime}
            </if>
            <if test="device.serialNumber != null and device.serialNumber !=''"> and serial_number = #{device.serialNumber}</if>
        </where>
        order by ts desc
    </select>

    <select id="selectStatsValue" resultType="java.lang.String">
        select log_value
        from ${database}.device_log
        <where>
            <if test="device.logType != null">
                and log_type = #{device.logType}
            </if>
            <if test="device.beginTime != null and device.beginTime != '' and device.endTime != null and device.endTime != ''">
                and ts between #{device.beginTime} and #{device.endTime}
            </if>
            <if test="device.identify != null and device.identify != ''">
                and identify = #{device.identify}
            </if>
            <if test="device.serialNumber != null and device.serialNumber != ''">
                and serial_number = #{device.serialNumber}
            </if>
        </where>
    </select>

    <select id="countThingsModelInvoke" resultType="com.fastbee.iot.model.vo.ThingsModelLogCountVO">
        select identify identifier, count(identify) counts
        from ${database}.device_log
        where log_type = 2
        <if test="dataCenterParam.serialNumber != null and dataCenterParam.serialNumber != ''">
            and serial_number = #{dataCenterParam.serialNumber}
        </if>
        <if test="dataCenterParam.beginTime != null and dataCenterParam.beginTime != '' and dataCenterParam.endTime != null and dataCenterParam.endTime != ''">
            and ts between #{dataCenterParam.beginTime} and #{dataCenterParam.endTime}
        </if>
        group by identify
    </select>
</mapper>
