<?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.ruoyi.tdengine.mapper.TdEngineMapper">
    <update id="createDatabase" parameterType="String">
        create database if not exists #{databaseName}
    </update>

   <update id="createSuperTable">
       create table if not exists #{databaseName}.#{superTableName}
       (${firstFieldName} timestamp,
       <!--tdEngine不支持动态fields里的数据类型，只能使用choose-->
       <foreach item="item" collection="schemaFields" separator=","
                index="">
           <if test="item.fieldName != null and item.fieldName != ''">
               ${item.fieldName}
           </if>
           <if test="item.dataType != null and item.dataType != ''">
               <choose>
                   <when test="item.dataType == 'timestamp'">
                       timestamp
                   </when>
                   <when test="item.dataType == 'tinyint'">
                       tinyint
                   </when>
                   <when test="item.dataType == 'smallint'">
                       smallint
                   </when>
                   <when test="item.dataType == 'int'">
                       int
                   </when>
                   <when test="item.dataType == 'bigint'">
                       bigint
                   </when>
                   <when test="item.dataType == 'float'">
                       float
                   </when>
                   <when test="item.dataType == 'double'">
                       double
                   </when>
                   <when test="item.dataType == 'binary'">
                       binary
                   </when>
                   <when test="item.dataType == 'nchar'">
                       nchar
                   </when>
                   <when test="item.dataType == 'bool'">
                       bool
                   </when>
                   <when test="item.dataType == 'json'">
                       json
                   </when>
               </choose>
           </if>

           <if test="item.size != null">
               (#{item.size})
           </if>
       </foreach>
       ) tags
       <!--tdEngine不支持动态tags里的数据类型，只能使用choose-->
       <foreach item="item" collection="tagsFields" separator=","
                open="(" close=")" index="">
           <if test="item.fieldName != null and item.fieldName != ''">
               ${item.fieldName}
           </if>
           <if test="item.dataType != null and item.dataType != ''">
               <choose>
                   <when test="item.dataType == 'timestamp'">
                       timestamp
                   </when>
                   <when test="item.dataType == 'int'">
                       int
                   </when>
                   <when test="item.dataType == 'bigint'">
                       bigint
                   </when>
                   <when test="item.dataType == 'float'">
                       float
                   </when>
                   <when test="item.dataType == 'double'">
                       double
                   </when>
                   <when test="item.dataType == 'binary'">
                       binary
                   </when>
                   <when test="item.dataType == 'smallint'">
                       smallint
                   </when>
                   <when test="item.dataType == 'tinyint'">
                       tinyint
                   </when>
                   <when test="item.dataType == 'bool'">
                       bool
                   </when>
                   <when test="item.dataType == 'nchar'">
                       nchar
                   </when>
                   <when test="item.dataType == 'json'">
                       json
                   </when>
               </choose>
           </if>
           <if test="item.size != null">
               (#{item.size})
           </if>
       </foreach>
   </update>

    <update id="createTable">
        create table if not exists #{databaseName}.#{tableName}
                using #{databaseName}.#{superTableName}
                tags
        <foreach item="item" collection="tagsFieldValues" separator=","
                 open="(" close=")" index="">
            ${item.fieldValue}
        </foreach>
    </update>



    <update id="addSuperTableColumn">
        ALTER STABLE #{databaseName}.#{superTableName} ADD COLUMN
        <if test="addFieldsVo.fieldName != null and addFieldsVo.fieldName != ''">
            ${addFieldsVo.fieldName}
        </if>
        <if test="addFieldsVo.dataType != null and addFieldsVo.dataType != ''">
            <choose>
                <when test="addFieldsVo.dataType == 'timestamp'">
                    timestamp
                </when>
                <when test="addFieldsVo.dataType == 'int'">
                    int
                </when>
                <when test="addFieldsVo.dataType == 'bigint'">
                    bigint
                </when>
                <when test="addFieldsVo.dataType == 'float'">
                    float
                </when>
                <when test="addFieldsVo.dataType == 'double'">
                    double
                </when>
                <when test="addFieldsVo.dataType == 'binary'">
                    binary
                </when>
                <when test="addFieldsVo.dataType == 'smallint'">
                    smallint
                </when>
                <when test="addFieldsVo.dataType == 'tinyint'">
                    tinyint
                </when>
                <when test="addFieldsVo.dataType == 'bool'">
                    bool
                </when>
                <when test="addFieldsVo.dataType == 'nchar'">
                    nchar
                </when>
                <when test="addFieldsVo.dataType == 'json'">
                    json
                </when>
            </choose>
        </if>
        <if test="addFieldsVo.size != null">
            (#{addFieldsVo.size})
        </if>
    </update>

    <update id="dropSuperTableColumn">
        ALTER STABLE #{databaseName}.#{superTableName} DROP COLUMN
        <if test="dropField.fieldName != null and dropField.fieldName != ''">
            ${dropField.fieldName}
        </if>
    </update>

    <update id="alterTableTag">
        ALTER TABLE #{databaseName}.#{tableName} SET TAG #{tagName}=#{tagValue};
    </update>


    <!-- 需要提前建表。如果用插入记录时自动建表则会有概率出现问题：设备上报的属性值跟定义的属性值不同的情况-->
    <insert id="insertTable">
<!--不要换行，tdengine有时候解析会有问题-->
       insert into #{databaseName}.#{tableName} <foreach item="item" collection="schemaFields" separator=","
                 open="(" close=")" index="">${item.fieldName}</foreach>  values <foreach item="item" collection="schemaFields" separator=","
                 open="(" close=")" index="">#{item.fieldValue}</foreach>

        <!--insert into db_scene_1.t_device_2
        (
        ts
        ,
        rssi
        ,
        alias
        ,
        value1
        ,
        voltage
        )
        values
        (
        1689816999999,-80.1,
        'u_1'
        ,
        -25.6
        ,
        3.76
        )-->

    </insert>

    <!--适用于设备监测页面（废弃，可以getLatestDataByTags通过tag监控单元ID来获取最新信息），先根据监控单元获取设备信息，再根据设备来获取最新的数据-->
    <select id="getLatestData" parameterType="com.ruoyi.tdengine.api.domain.TdSelectDto"
            resultType="java.util.Map">
        select last(*) from #{databaseName}.#{tableName}
    </select>


    <!--适用于设备汇总页面，先分页获取设备信息，再获取所有此物模型下的最新信息（冗余，但每页只获取一次，不需要每个设备都获取一次
    如果需要根据时间周期获取最新的数据，则需要用select last_row()-->
    <select id="getLatestDataByTags" parameterType="com.ruoyi.tdengine.api.domain.TdSelectDto"
            resultType="java.util.Map">
        select last(*), last(${tagsName}) from #{databaseName}.#{superTableName} group by ${tagsName}
    </select>


    <select id="getHistoryData" parameterType="com.ruoyi.tdengine.api.domain.TdSelectDto" resultType="java.util.Map" >
        SELECT * FROM #{databaseName}.#{tableName}
        <where>
            <if test="#{firstFieldName} != null and #{firstFieldName} != '' and
            startTime!= 0 and endTime != 0">
                ${firstFieldName} BETWEEN #{startTime} AND #{endTime}
            </if>
        </where>

        <!--由于此处orderby是用${orderByFieldName}，而不是用#${orderByFieldName}，这样就会用sql注入风险，所以加上choose和判断条件-->
        <choose>
            <when test="(orderByFieldName=='ts') and (sort=='desc' or sort=='asc')">
                order by ${orderByFieldName}   ${sort}
            </when>

        </choose>


        <if test="offset != 0 and limit != 0 ">
            LIMIT #{offset},#{limit}
        </if>
    </select>


    <select id="getCountOfHistoryData" parameterType="com.ruoyi.tdengine.api.domain.TdSelectDto" resultType="java.util.Map" >
     <!--as后不要用常用单词，例如count会报错-->
     SELECT count(0) as dataCount FROM #{databaseName}.#{tableName}
        <where>
            <if test="#{firstFieldName} != null and #{firstFieldName} != '' and
            startTime!= 0 and endTime != 0">
                ${firstFieldName} BETWEEN #{startTime} AND #{endTime}
            </if>
        </where>
    </select>


    <select id="getOnlineDevicesByDay" parameterType="com.ruoyi.tdengine.api.domain.DeviceStatus" resultType="com.ruoyi.tdengine.api.domain.DeviceStatus" >
        select timetruncate(ts,1d) ts,devicetype,onlinestatus,devicecode from #{params.databaseName}.#{params.superTableName}
        where onlinestatus=1
        <if test="{params.beginTime}!= null and {params.endTime} != null">
            and ts BETWEEN #{params.beginTime} AND #{params.endTime}
        </if>
        <if test="sceneId != null "> and scene_id = #{sceneId}</if>
        group by timetruncate(ts,1d),devicetype,onlinestatus,devicecode
        order by ts
    </select>




    <!--
   <select id="checkTableExists" resultType="java.lang.Integer">
       SELECT COUNT(0) FROM #{dataBaseName}.#{tableName}
   </select>

   <select id="getRealtimeData" resultType="java.util.Map" parameterType="">
       SELECT #{fieldName},ts
       FROM #{dataBaseName}.#{tableName} LIMIT #{num}
   </select>
   <select id="getAggregateData" resultType="java.util.Map" parameterType="">
       SELECT #{aggregate}(${fieldName})
       FROM #{dataBaseName}.#{tableName} WHERE ts BETWEEN #{startTime} AND #{endTime} interval(${interval}) LIMIT #{num}
   </select>-->
</mapper>
