<?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="dropFieldsVo.fieldName != null and dropFieldsVo.fieldName != ''">
            ${dropFieldsVo.fieldName}
        </if>
    </update>


    <!-- 需要提前建表。如果用插入记录时自动建表则会有概率出现问题：设备上报的属性值跟定义的属性值不同的情况-->
    <insert id="insertTable">
        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>

    <!--适用于设备监测页面，先根据监控单元获取设备信息，再根据设备来获取最新的数据-->
    <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>

        order by ${orderByFieldName}
        <if test="#{orderByMode} != null and #{orderByMode} != ''">
            ${orderByMode}
        </if>
        <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="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>
