package com.yfd.monitor.storager.dao;

import com.yfd.monitor.gdw2019.bean.Device;
import com.yfd.monitor.vmanager.bean.ResourceBaceInfo;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 用于存储设备信息
 */
@Mapper
@Repository
public interface DeviceMapper {

    @Select("SELECT " +
            "deviceId, " +
            "coalesce(custom_name, name) as name, " +
            "password, " +
            "manufacturer, " +
            "model, " +
            "firmware, " +
            "transport," +
            "streamMode," +
            "ip," +
            "sdpIp," +
            "localIp," +
            "port," +
            "hostAddress," +
            "expires," +
            "registerTime," +
            "keepaliveTime," +
            "createTime," +
            "updateTime," +
            "charset," +
            "subscribeCycleForCatalog," +
            "subscribeCycleForMobilePosition," +
            "mobilePositionSubmissionInterval," +
            "subscribeCycleForAlarm," +
            "ssrcCheck," +
            "asMessageChannel," +
            "geoCoordSys," +
            "treeType," +
            "online," +
            "mediaServerId," +
            "(SELECT count(0) FROM mon_device_channel WHERE deviceId=mon_device.deviceId) as channelCount "+
            " FROM mon_device WHERE deviceId = #{deviceId}")
    Device getDeviceByDeviceId(String deviceId);

    @Insert("INSERT INTO mon_device (" +
                "deviceId, " +
                "name, " +
                "manufacturer, " +
                "model, " +
                "firmware, " +
                "transport," +
                "streamMode," +
                "ip," +
                "sdpIp," +
                "localIp," +
                "port," +
                "hostAddress," +
                "expires," +
                "registerTime," +
                "keepaliveTime," +
                "keepaliveIntervalTime," +
                "createTime," +
                "updateTime," +
                "charset," +
                "subscribeCycleForCatalog," +
                "subscribeCycleForMobilePosition," +
                "mobilePositionSubmissionInterval," +
                "subscribeCycleForAlarm," +
                "ssrcCheck," +
                "asMessageChannel," +
                "geoCoordSys," +
                "treeType," +
                "online" +
            ") VALUES (" +
                "#{deviceId}," +
                "#{name}," +
                "#{manufacturer}," +
                "#{model}," +
                "#{firmware}," +
                "#{transport}," +
                "#{streamMode}," +
                "#{ip}," +
                "#{sdpIp}," +
                "#{localIp}," +
                "#{port}," +
                "#{hostAddress}," +
                "#{expires}," +
                "#{registerTime}," +
                "#{keepaliveTime}," +
                "#{keepaliveIntervalTime}," +
                "#{createTime}," +
                "#{updateTime}," +
                "#{charset}," +
                "#{subscribeCycleForCatalog}," +
                "#{subscribeCycleForMobilePosition}," +
                "#{mobilePositionSubmissionInterval}," +
                "#{subscribeCycleForAlarm}," +
                "#{ssrcCheck}," +
                "#{asMessageChannel}," +
                "#{geoCoordSys}," +
                "#{treeType}," +
                "#{online}" +
            ")")
    int add(Device device);

    @Select("SELECT " +
            "count(1) as count" +
            " FROM iis_substation_patroldevice WHERE internationalId = #{deviceId}")
    int getRiisPatrolDevice(String deviceId);

    @Select("SELECT " +
            "patroldevice_id, " +
            "patroldevice_code, " +
            "rtspurl, " +
            "ipaddr, " +
            "channelinfo, " +
            "type " +
            " FROM iis_substation_patroldevice WHERE  internationalId=#{deviceId}")
    List<Map> getRiisPatrolDeviceByCode(String deviceId);

    @Select("SELECT " +
            "patroldevice_id, " +
            "patroldevice_code, " +
            "preset, " +
            "presettime, " +
            "DATE_FORMAT(changepresettime, '%Y-%m-%d %H:%i:%s') changepresettime " +
            " FROM iis_substation_patroldevice WHERE preset is not null and presettime is not null and changepresettime is not null and type='10' and online = '1' and working='0' ")
    List<Map> getRiisPatrolDevices();


    @Insert("INSERT INTO iis_substation_patroldevice (" +
            "patroldevice_id, " +  //UUID
            "patroldevice_code, " +//摄像机编号 与  视频摄像头编号一致
            "internationalId, " +//摄像头国标编码
            "patroldevice_name, " +//摄像机名称
            "manufacturer, " +   //生产厂家
            "device_model, " +  //设备型号
            "station_id, " +  //变电站地址
            "ipaddr," + //'以太网IP地址',
            "heart_beat_interval," + //'心跳间隔',
            "datastatus," +
            "lastmodifier," +
            "lastmodifydate," +
            "type," +
            "online" +
            ") VALUES (" +
            "#{id}," +
            "#{deviceId}," +
            "#{deviceId}," +
            "#{name}," +
            "#{manufacturer}," +
            "#{model}," +
            "#{stationId}," +
            "#{ip}," +
            "#{keepaliveIntervalTime}," +
            "'1'," +
            "'注册创建'," +
            "#{createTime}," +
            "10," +
            "1" +
            ")")
    int addRiisPatrolDevice(Device device);

    @Insert("INSERT INTO iis_substation (" +
            "patroldevice_id, " +  //UUID
            "patroldevice_code, " +//摄像机编号 与  视频摄像头编号一致
            "patroldevice_name, " +//摄像机名称
            "manufacturer, " +   //生产厂家
            "device_model, " +  //设备型号
            "station_id, " +  //变电站地址
            "ipaddr," + //'以太网IP地址',
            "heart_beat_interval," + //'心跳间隔',
            "datastatus," +
            "lastmodifier," +
            "lastmodifydate," +
            "type," +
            "online" +
            ") VALUES (" +
            "#{id}," +
            "#{deviceId}," +
            "#{name}," +
            "#{manufacturer}," +
            "#{model}," +
            "#{stationId}," +
            "#{ip}," +
            "#{keepaliveIntervalTime}," +
            "'1'," +
            "'注册创建'," +
            "#{createTime}," +
            "10," +
            "1" +
            ")")
    int addRiisStationNode(Device device);

    @Update(value = {" <script>" +
                "UPDATE mon_device " +
                "SET updateTime=#{updateTime}" +
                "<if test=\"name != null\">, name=#{name}</if>" +
                "<if test=\"manufacturer != null\">, manufacturer=#{manufacturer}</if>" +
                "<if test=\"model != null\">, model=#{model}</if>" +
                "<if test=\"firmware != null\">, firmware=#{firmware}</if>" +
                "<if test=\"transport != null\">, transport=#{transport}</if>" +
                "<if test=\"ip != null\">, ip=#{ip}</if>" +
                "<if test=\"localIp != null\">, localIp=#{localIp}</if>" +
                "<if test=\"port != null\">, port=#{port}</if>" +
                "<if test=\"hostAddress != null\">, hostAddress=#{hostAddress}</if>" +
                "<if test=\"online != null\">, online=#{online}</if>" +
                "<if test=\"registerTime != null\">, registerTime=#{registerTime}</if>" +
                "<if test=\"keepaliveTime != null\">, keepaliveTime=#{keepaliveTime}</if>" +
                "<if test=\"keepaliveIntervalTime != null\">, keepaliveIntervalTime=#{keepaliveIntervalTime}</if>" +
                "<if test=\"expires != null\">, expires=#{expires}</if>" +
                "WHERE deviceId=#{deviceId}"+
            " </script>"})
    int update(Device device);

    @Update(value = {" <script>" +
            "UPDATE iis_substation_patroldevice " +
            "SET lastmodifydate=#{updateTime}" +
//            "<if test=\"name != null\">, patroldevice_name=#{name}</if>" +
            "<if test=\"model != null\">, device_model=#{model}</if>" +
            "<if test=\"ip != null\">, ipaddr=#{ip}</if>" +
            "<if test=\"online != null\">, online=#{online}</if>" +
            "WHERE internationalId=#{deviceId}"+
            " </script>"})
    int updateRiisPatrolDevice(Device device);

    @Update(value = {" <script>" +
            "UPDATE iis_substation " +
            "SET lastmodifydate=#{updateTime}" +
            "<if test=\"online != null\">, online=#{online}</if>" +
            " WHERE node_id=#{deviceId}"+
            " </script>"})
    int updateRiisSubstation(Device device);


    @Select(
            " <script>" +
            "SELECT " +
            "deviceId, " +
            "coalesce(custom_name, name) as name, " +
            "password, " +
            "manufacturer, " +
            "model, " +
            "firmware, " +
            "transport," +
            "streamMode," +
            "ip," +
            "sdpIp," +
            "localIp," +
            "port," +
            "hostAddress," +
            "expires," +
            "registerTime," +
            "keepaliveTime," +
            "createTime," +
            "updateTime," +
            "charset," +
            "subscribeCycleForCatalog," +
            "subscribeCycleForMobilePosition," +
            "mobilePositionSubmissionInterval," +
            "subscribeCycleForAlarm," +
            "ssrcCheck," +
            "asMessageChannel," +
            "geoCoordSys," +
            "treeType," +
            "online," +
            "mediaServerId," +
            "(SELECT count(0) FROM mon_device_channel WHERE deviceId=de.deviceId) as channelCount  FROM mon_device de" +
            "<if test=\"online != null\"> where online=${online}</if>"+
            " </script>"
    )
    List<Device> getDevices(Boolean online);

    @Delete("DELETE FROM mon_device WHERE deviceId=#{deviceId}")
    int del(String deviceId);

    @Select("SELECT " +
            "deviceId, " +
            "coalesce(custom_name, name) as name, " +
            "password, " +
            "manufacturer, " +
            "model, " +
            "firmware, " +
            "transport," +
            "streamMode," +
            "ip," +
            "sdpIp," +
            "localIp," +
            "port," +
            "hostAddress," +
            "expires," +
            "registerTime," +
            "keepaliveTime," +
            "createTime," +
            "updateTime," +
            "charset," +
            "subscribeCycleForCatalog," +
            "subscribeCycleForMobilePosition," +
            "mobilePositionSubmissionInterval," +
            "subscribeCycleForAlarm," +
            "ssrcCheck," +
            "asMessageChannel," +
            "geoCoordSys," +
            "treeType," +
            "online " +
            " FROM mon_device WHERE online = 1")
    List<Device> getOnlineDevices();
    @Select("SELECT " +
            "deviceId, " +
            "coalesce(custom_name, name) as name, " +
            "password, " +
            "manufacturer, " +
            "model, " +
            "firmware, " +
            "transport," +
            "streamMode," +
            "ip," +
            "sdpIp," +
            "localIp," +
            "port," +
            "hostAddress," +
            "expires," +
            "registerTime," +
            "keepaliveTime," +
            "createTime," +
            "updateTime," +
            "charset," +
            "subscribeCycleForCatalog," +
            "subscribeCycleForMobilePosition," +
            "mobilePositionSubmissionInterval," +
            "subscribeCycleForAlarm," +
            "ssrcCheck," +
            "asMessageChannel," +
            "geoCoordSys," +
            "treeType," +
            "online" +
            " FROM mon_device WHERE ip = #{host} AND port=#{port}")
    Device getDeviceByHostAndPort(String host, int port);

    @Update(value = {" <script>" +
            "UPDATE mon_device " +
            "SET updateTime=#{updateTime}" +
            "<if test=\"name != null\">, custom_name=#{name}</if>" +
            "<if test=\"password != null\">, password=#{password}</if>" +
            "<if test=\"streamMode != null\">, streamMode=#{streamMode}</if>" +
            "<if test=\"ip != null\">, ip=#{ip}</if>" +
            "<if test=\"sdpIp != null\">, sdpIp=#{sdpIp}</if>" +
            "<if test=\"port != null\">, port=#{port}</if>" +
            "<if test=\"charset != null\">, charset=#{charset}</if>" +
            "<if test=\"subscribeCycleForCatalog != null\">, subscribeCycleForCatalog=#{subscribeCycleForCatalog}</if>" +
            "<if test=\"subscribeCycleForMobilePosition != null\">, subscribeCycleForMobilePosition=#{subscribeCycleForMobilePosition}</if>" +
            "<if test=\"mobilePositionSubmissionInterval != null\">, mobilePositionSubmissionInterval=#{mobilePositionSubmissionInterval}</if>" +
            "<if test=\"subscribeCycleForAlarm != null\">, subscribeCycleForAlarm=#{subscribeCycleForAlarm}</if>" +
            "<if test=\"ssrcCheck != null\">, ssrcCheck=#{ssrcCheck}</if>" +
            "<if test=\"asMessageChannel != null\">, asMessageChannel=#{asMessageChannel}</if>" +
            "<if test=\"geoCoordSys != null\">, geoCoordSys=#{geoCoordSys}</if>" +
            "<if test=\"treeType != null\">, treeType=#{treeType}</if>" +
            "<if test=\"mediaServerId != null\">, mediaServerId=#{mediaServerId}</if>" +
            "WHERE deviceId=#{deviceId}"+
            " </script>"})
    void updateCustom(Device device);

    @Insert("INSERT INTO mon_device (" +
            "deviceId, " +
            "custom_name, " +
            "password, " +
            "sdpIp, " +
            "createTime," +
            "updateTime," +
            "charset," +
            "ssrcCheck," +
            "asMessageChannel," +
            "geoCoordSys," +
            "treeType," +
            "online," +
            "mediaServerId" +
            ") VALUES (" +
            "#{deviceId}," +
            "#{name}," +
            "#{password}," +
            "#{sdpIp}," +
            "#{createTime}," +
            "#{updateTime}," +
            "#{charset}," +
            "#{ssrcCheck}," +
            "#{asMessageChannel}," +
            "#{geoCoordSys}," +
            "#{treeType}," +
            "#{online}," +
            "#{mediaServerId}" +
            ")")
    void addCustomDevice(Device device);

    @Select("select count(1) as total, sum(online) as online from mon_device")
    ResourceBaceInfo getOverview();

    @Select("select * from mon_device")
    List<Device> getAll();

    @Select("select * from mon_device where  asMessageChannel = 1")
    List<Device> queryDeviceWithAsMessageChannel();

    @Update(value = {"<script>" +
            "UPDATE iis_substation_patroldevice SET " +
            "<if test=\"preset == ''\">changepresettime=null</if> " +
            "<if test=\"preset != ''\">changepresettime=sysdate()</if> " +
            " WHERE patroldevice_code=#{id}" +
            " </script>"})
    int updatePatroldeviceTime(String id, String preset);

    @Select("SELECT " +
            "md.deviceId, " +
            "coalesce(md.custom_name, md.name) as name, " +
            "md.password, " +
            "md.manufacturer, " +
            "md.model, " +
            "md.firmware, " +
            "md.transport," +
            "md.streamMode," +
            "md.ip," +
            "md.sdpIp," +
            "md.localIp," +
            "md.port," +
            "md.hostAddress," +
            "md.expires," +
            "md.registerTime," +
            "md.keepaliveTime," +
            "md.createTime," +
            "md.updateTime," +
            "md.charset," +
            "md.subscribeCycleForCatalog," +
            "md.subscribeCycleForMobilePosition," +
            "md.mobilePositionSubmissionInterval," +
            "md.subscribeCycleForAlarm," +
            "md.ssrcCheck," +
            "md.asMessageChannel," +
            "md.geoCoordSys," +
            "md.treeType," +
            "md.online," +
            "md.mediaServerId," +
            "(SELECT count(0) FROM mon_device_channel WHERE deviceId=md.deviceId) as channelCount "+
            " FROM mon_device md INNER JOIN mon_device_channel dc on dc.deviceId=md.deviceId WHERE dc.deviceId = #{deviceId} and dc.channelId= #{channelId}")
    Device getDeviceByChannel(String deviceId, String channelId);

    @Select("SELECT " +
            "patroldevice_id, " +
            "patroldevice_code, " +
            "rtspurl, " +
            "ipaddr, " +
            "channelinfo " +
            "type " +
            " FROM iis_substation_patroldevice WHERE video_mode ='1' and robots_code=#{robotCode}")
    List<Map> getRiisPatrolDeviceByRobot(String robotCode);
}
