soc/project_data/sql/0.1.1/soc/soc.sql

-- soc v0.1.1 版本升级脚本

DELIMITER ??
DROP PROCEDURE IF EXISTS schema_change ??
CREATE PROCEDURE schema_change()
BEGIN

    -- 磁盘表增加磁盘原始状态值
    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'iot_dvr_disk'
                    AND column_name = 'origin_state') THEN
        alter table iot_dvr_disk
            add origin_state int null comment '原始状态:0:正常,1:不存在,2:未格式化,3:休眠,4:正在维修 ,5:报警 ,6:错误 7:排除, 9:未知' after state;
    END IF;

    -- 磁盘日志表增加磁盘原始状态值
    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'iot_dvr_disk_log'
                    AND column_name = 'origin_state') THEN
        alter table iot_dvr_disk_log
            add origin_state int null comment '原始状态:0:正常,1:不存在,2:未格式化,3:休眠,4:正在维修 ,5:报警 ,6:错误 7:排除, 9:未知' after state;
    END IF;

    -- 录像完整性检查表增加录像类型
    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'mediator_video_integrity_check'
                    AND column_name = 'record_type') THEN
        alter table mediator_video_integrity_check
            add record_type int null comment '0: 定时 |  1：移动侦测' after plan_days;
    END IF;

    -- 录像完整性检查表增加原始录像索引
    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'mediator_video_integrity_check'
                    AND column_name = 'record_span') THEN

        alter table mediator_video_integrity_check
            add record_span varchar(225) null comment '原始录像索引' after record_type;
    END IF;

    -- 录像完整性检查日志表增加录像类型
    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'mediator_video_integrity_check_log'
                    AND column_name = 'record_type') THEN

        alter table mediator_video_integrity_check_log
            add record_type int null comment '0: 定时 |  1：移动侦测' after plan_days;
    END IF;

    -- 录像完整性检查日志表增加原始录像索引
    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'mediator_video_integrity_check_log'
                    AND column_name = 'record_span') THEN

        alter table mediator_video_integrity_check_log
            add record_span varchar(225) null comment '原始录像索引' after record_type;
    END IF;


    -- 磁盘日志表增加磁盘原始状态值
    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'iot_dvr_disk'
                    AND column_name = 'iot_token') THEN
    alter table iot_dvr_disk
        add iot_token varchar(50) null comment 'iot服务唯一编码 token' after state;
    END IF;

    -- 子系统表添加iot_token字段
    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'iot_alarm_subsystem'
                    AND column_name = 'iot_token') THEN
    alter table iot_alarm_subsystem
        add iot_token varchar(50) null comment 'iot服务唯一编码 token' after update_by;
    END IF;

    -- 防区表添加iot_token字段
    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'iot_alarm_defence_area'
                    AND column_name = 'iot_token') THEN
    alter table iot_alarm_defence_area
        add iot_token varchar(50) null comment 'iot服务唯一编码 token' after update_by;
    END IF;


    -- 系统报警属性字段表添加property_name字段
    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'iot_alarm_system_field'
                    AND column_name = 'property_name') THEN
        alter table iot_alarm_system_field
            add property_name varchar(32) null comment '属性名称' after name;
        update iot_alarm_system_field set property_name = 'alarm';
        update iot_alarm_system_field set property_name = 'temperature' where sys_field_code = '4183_1';
        update iot_alarm_system_field set property_name = 'humidity' where sys_field_code = '4183_2';
    END IF;

    -- 视频诊断记录表修改录像质量诊断图片存储字段类型
    ALTER TABLE `mediator_video_diagnosis_record`
        MODIFY COLUMN `img_url` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '录像质量诊断图片' AFTER `detail_info`;
    -- 视频诊断日志表修改录像质量诊断图片存储字段类型
    ALTER TABLE `mediator_video_diagnosis_log`
        MODIFY COLUMN `img_url` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '录像质量诊断图片' AFTER `detail_info`;

    -- 防区表删除CategoryId字段
    IF  EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'iot_alarm_defence_area'
                    AND column_name = 'category_id') THEN
        ALTER TABLE `soc`.`iot_alarm_defence_area`
            DROP COLUMN `category_id`;
    END IF;

    -- 防区表增加module_address字段
    IF NOT EXISTS(SELECT *
               FROM information_schema.columns
               WHERE table_schema = DATABASE()
                 AND table_name = 'iot_alarm_defence_area'
                 AND column_name = 'module_address') THEN
        ALTER TABLE   `iot_alarm_defence_area`
            ADD COLUMN `module_address` varchar(12) NULL AFTER `iot_token`;
    END IF;


END ??
DELIMITER ;
CALL schema_change();
-- 新增ups设备表
drop table if exists iot_ups;
create table if not exists iot_ups
(
    id                       bigint        not null
        primary key,
    org_id                   bigint        null comment '机构id',
    org_name                 varchar(255)  null comment '机构名称',
    org_path                 varchar(255)  null comment '机构path',
    host_code                varchar(64)   null comment '视频物联网检测主机唯一标识',
    device_name              varchar(100)  null comment '设备名称',
    device_type              varchar(32)   null comment '设备类型',
    device_code              varchar(64)   null comment '设备code',
    ups_unique_code          varchar(225)  null comment 'ups唯一标识,机构code+主机code+deviceCode组成设备唯一',
    info                     varchar(2000) null comment '属性信息数组',
    deleted                  int default 0 null comment '0正常1删除',
    state_update_time        datetime      null comment '最后一次状态更新时间',
    update_time              datetime      null comment '更新时间,最后一次数据上传时间',
    create_time              datetime      null comment '创建时间',
    create_by                varchar(255)  null comment '创建人',
    update_by                varchar(32)   null comment '修改人',
    source                   int default 1 null comment '设备来源：0:平台主动新增,1：iot推送',
    status                   int default 2 null comment '设备告警状态: 0:正常,1:异常,2:未知',
    mains_electricity_status int default 2 null comment 'ups市电状态,0:正常1:异常2:未知',
    low_pressure_status      int default 2 null comment 'ups电池电压低告警,0:正常1:异常2:未知',
    constraint idx_unique_ups_code unique (ups_unique_code)
)
    comment 'ups表信息' row_format = DYNAMIC;

-- 新增ups设备日志表
drop table if exists iot_ups_data_log;
create table if not exists iot_ups_data_log
(
    id                       bigint        not null
        primary key,
    org_id                   bigint        null comment '机构id',
    org_name                 varchar(255)  null comment '机构名称',
    org_path                 varchar(255)  null comment '机构path',
    host_code                varchar(64)   null comment '视频物联网检测主机唯一标识',
    device_name              varchar(100)  null comment '设备名称',
    device_type              varchar(32)   null comment '设备类型',
    device_code              varchar(64)   null comment '设备code',
    ups_unique_code          varchar(225)  null comment 'ups唯一标识,机构code+主机code+deviceCode组成设备唯一',
    info                     varchar(2000) null comment '属性信息数组',
    deleted                  int default 0 null comment '0正常1删除',
    state_update_time        datetime      null comment '最后一次状态更新时间',
    update_time              datetime      null comment '更新时间,最后一次数据上传时间',
    create_time              datetime      null comment '创建时间',
    create_by                varchar(255)  null comment '创建人',
    update_by                varchar(32)   null comment '修改人',
    source                   int default 1 null comment '设备来源：0:平台主动新增,1：iot推送',
    status                   int default 2 null comment '设备告警状态: 0:正常,1:异常,2:未知',
    mains_electricity_status int default 2 null comment 'ups市电状态,0:正常1:异常2:未知',
    low_pressure_status      int default 2 null comment 'ups电池电压低告警,0:正常1:异常2:未知',
    ups_id                   bigint        null comment 'ups表id',
    constraint idx_unique_ups_code unique (ups_unique_code)
)
    comment 'ups表信息' row_format = DYNAMIC;

-- 新增ups设备属性表
drop table if exists iot_ups_attr;
create table if not exists  iot_ups_attr
(
    id         int auto_increment
        primary key,
    rule_key   varchar(50)   null,
    rule_value int           null,
    unit       varchar(50)   null,
    start      int default 0 null
)
    comment 'ups设备属性' row_format = DYNAMIC;
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS市电状态', 0, null, 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS电池电压低告警', 0, null, 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS故障', 0, null, 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS电池电压', null, 'V', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS类型', 0, null, 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS电池电流', null, 'A', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS温度', null, '℃', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS电池容量', null, 'mAh', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS测试状态', 0, null, 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS电池剩余时间', null, '分钟', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS旁路状态', 0, null, 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS通信中断告警', 0, null, 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输入相电压Ua', null, 'V', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输入相电压Ub', null, 'V', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输入相电压Uc', null, 'V', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输出相电压Ua', null, 'V', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输出相电压Ub', null, 'V', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输出相电压Uc', null, 'V', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输入相电流Ia', null, 'A', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输入相电流Ib', null, 'A', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输入相电流Ic', null, 'A', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输出相电流Ia', null, 'A', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输出相电流Ib', null, 'A', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输出相电流Ic', null, 'A', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输入频率', null, 'Hz', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输出频率', null, 'Hz', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS报警启动', 0, null, 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS关机状态', 0, null, 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS负载率A相', null, '%', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS负载率B相', null, '%', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS负载率C相', null, '%', 0);
INSERT INTO iot_ups_attr (rule_key, rule_value, unit, start) VALUES ('UPS输入故障电压', null, 'V', 0);


drop table  if exists iot_breaker;
CREATE TABLE `iot_breaker` (
                               `id` bigint NOT NULL DEFAULT '0',
                               `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                               `org_id` bigint DEFAULT NULL,
                               `org_path` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                               `org_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                               `unique_code` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'orgCode+hostCode+deviceCode组成设备唯一',
                               `deleted` int DEFAULT NULL COMMENT '0正常1删除',
                               `status` int DEFAULT NULL COMMENT '0断电1通电2异常',
                               `type` int DEFAULT NULL COMMENT '1市电8小时回路2UPS8小时回路',
                               `device_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备code07市电控制线08ups控制线',
                               `breaker_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '控制器名称',
                               `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
                               `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
                               `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
                               `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
                               PRIMARY KEY (`id`) USING BTREE,
                               KEY `unique_code` (`unique_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

drop table  if exists iot_breaker_data;
CREATE TABLE `iot_breaker_data` (
                                    `id` bigint NOT NULL,
                                    `unique_code` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '回路控制器的唯一标识',
                                    `status` int DEFAULT NULL COMMENT '0断电1通电2异常',
                                    `update_time` datetime DEFAULT NULL COMMENT '数据上报时间',
                                    PRIMARY KEY (`id`) USING BTREE,
                                    KEY `unique_code` (`unique_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

drop table  if exists iot_breaker_data_log;
CREATE TABLE `iot_breaker_data_log` (
                                        `id` bigint NOT NULL,
                                        `unique_code` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '回路控制器的唯一标识',
                                        `status` int DEFAULT NULL COMMENT '0断电1通电2异常',
                                        `update_time` datetime DEFAULT NULL COMMENT '数据上报时间',
                                        `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                        PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

drop table  if exists iot_breaker_device;
CREATE TABLE `iot_breaker_device` (
                                      `id` bigint NOT NULL,
                                      `org_id` bigint DEFAULT NULL,
                                      `org_path` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                      `org_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                      `host_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '视频物联网检测主机唯一标识',
                                      `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备名称',
                                      `device_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备code（外网平台用于匹配8小时回路名称）',
                                      `device_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备类型编码: 4186',
                                      `device_unique_code` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'orgCode+hostCode+deviceCode组成设备唯一',
                                      `status` int DEFAULT NULL COMMENT '状态标识（0/1）0：断电 | 1：通电 | 2：未知',
                                      `deleted` int DEFAULT NULL COMMENT '0正常1删除',
                                      `breaker_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '回路电相名同步的名称',
                                      `breaker_alias_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '回路电相名用户取的名称',
                                      `enable` int DEFAULT NULL COMMENT '0启用1禁用',
                                      `breaker_unique_code` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '控制器唯一标识',
                                      `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
                                      `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
                                      `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
                                      `update_time` datetime DEFAULT NULL COMMENT '数据更新时间',
                                      PRIMARY KEY (`id`) USING BTREE,
                                      KEY `device_unique_code` (`device_unique_code`) USING BTREE,
                                      KEY `breaker_unique_code` (`breaker_unique_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT=' 8小时回路信息同步表';

drop table  if exists iot_breaker_device_data;
CREATE TABLE `iot_breaker_device_data` (
                                           `id` bigint NOT NULL,
                                           `device_unique_code` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'orgCode+hostCode+deviceCode组成设备唯一',
                                           `update_time` datetime DEFAULT NULL COMMENT '数据更新时间',
                                           `status` int DEFAULT NULL COMMENT '状态标识（0/1）0：断电 | 1：通电',
                                           PRIMARY KEY (`id`) USING BTREE,
                                           KEY `device_unique_code` (`device_unique_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='8小时回路信息同步数据表';

drop table  if exists iot_breaker_device_to_area;
CREATE TABLE `iot_breaker_device_to_area` (
                                              `device_unique_code` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '回路的唯一标识',
                                              `code` int DEFAULT NULL COMMENT '区域code',
                                              KEY `device_unique_code` (`device_unique_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='回路绑定的区域';

drop table  if exists iot_power_off_alarm;
CREATE TABLE `iot_power_off_alarm` (
                                       `id` bigint NOT NULL,
                                       `org_id` bigint DEFAULT NULL,
                                       `org_path` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                       `org_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                       `power_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                       `power_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '市电总回路三相归类的编码',
                                       `is_do` int DEFAULT '0' COMMENT '是否处理0未处理1已处理',
                                       `do_time` datetime DEFAULT NULL COMMENT '处理时间',
                                       `alarm_time` datetime DEFAULT NULL COMMENT '告警时间',
                                       `is_normal` int DEFAULT NULL COMMENT '0正常停电，1异常停电',
                                       `remark` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '描述',
                                       `type` bit(1) DEFAULT NULL COMMENT '0:断电记录,1:通电记录',
                                       `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '通电备注',
                                       `recovery_time` datetime DEFAULT NULL COMMENT '恢复时间',
                                       PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='停电告警';

-- 新增空调设备表
drop table if exists iot_air_conditioner;
CREATE TABLE `iot_air_conditioner` (
                                       `id` bigint NOT NULL DEFAULT '0',
                                       `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                       `org_id` bigint DEFAULT NULL,
                                       `org_path` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                       `org_name` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                       `host_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '主机code',
                                       `unique_code` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'orgCode+hostCode+deviceCode组成设备唯一',
                                       `status` int DEFAULT NULL COMMENT '0正常1告警2未知',
                                       `device_type` varchar(50) DEFAULT NULL COMMENT '设备类型',
                                       `device_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备code07市电控制线08ups控制线',
                                       `info` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '空调状态详情',
                                       `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
                                       `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
                                       `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
                                       `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
                                       `deleted` int DEFAULT NULL COMMENT '0正常1删除',
                                       PRIMARY KEY (`id`) USING BTREE,
                                       KEY `unique_code` (`unique_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

-- 新增空调设备日志表
drop table if exists iot_air_conditioner_log;
CREATE TABLE `iot_air_conditioner_log` (
                                           `id` bigint NOT NULL DEFAULT '0',
                                           `air_conditioner_id` bigint NOT NULL DEFAULT '0',
                                           `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                           `org_id` bigint DEFAULT NULL,
                                           `org_path` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                           `org_name` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                           `host_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '主机code',
                                           `unique_code` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'orgCode+hostCode+deviceCode组成设备唯一',
                                           `status` int DEFAULT NULL COMMENT '0正常1告警2未知',
                                           `device_type` varchar(50) DEFAULT NULL COMMENT '设备类型',
                                           `device_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备code07市电控制线08ups控制线',
                                           `info` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '空调状态详情',
                                           `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
                                           `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
                                           `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
                                           `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
                                           PRIMARY KEY (`id`) USING BTREE,
                                           KEY `unique_code` (`unique_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

drop table if exists iot_server_info;
CREATE TABLE `iot_server_info` (
       `id` bigint DEFAULT NULL COMMENT '主键',
       `iot_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名称',
       `iot_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '服务编码(全局唯一)',
       `org_id` bigint DEFAULT NULL COMMENT '组织机构id',
       `org_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '组织机构名称',
       `org_path` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '组织机构路径',
       `iot_status` int DEFAULT NULL COMMENT '链接状态:0:未知(未启用),1:在线,2:离线',
       `last_connect_time` datetime DEFAULT NULL COMMENT '最近一次链接时间',
       `iot_ip` varchar(125) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'ip地址',
       `register_code` varchar(125) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '临时注册码',
       `register_code_status` int DEFAULT NULL COMMENT '临时注册码状态,0:未使用,1:已使用',
       `enable` int DEFAULT 0 COMMENT '是否启用,0:正常,1:停用',
       `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
       `create_time` datetime DEFAULT NULL COMMENT '创建时间',
       `update_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人',
       `update_time` datetime DEFAULT NULL COMMENT '修改时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='iot客户端信息';











-- ----------------------------
-- 部署中心新增表及视图脚本
-- ----------------------------

-- ----------------------------
-- Table structure for deploy_app_info
-- ----------------------------
DROP TABLE IF EXISTS `deploy_app_info`;
CREATE TABLE `deploy_app_info`  (
                                    `id` int(11) NOT NULL AUTO_INCREMENT,
                                    `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '产品名称',
                                    `code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '部署中心分配的标识【0-9】组成',
                                    `app_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用唯一id',
                                    `app_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用类型：BackgroundServices、Deskto、SystemServices、Tool',
                                    `app_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用名称',
                                    `version` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用版本号',
                                    `download_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '安装包下载地址',
                                    `certificate_authority` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '安装签名',
                                    `hash_algorithm` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'SHA256' COMMENT '安装签名算法，目前只支持SHA256',
                                    `hash` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '安装包的hash值',
                                    `os_platforms` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用支持操作系统类型：json数组',
                                    `os_architectures` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用支持系统架构：json数组',
                                    `start` varchar(3000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '启动参数：json格式数据',
                                    `stop` varchar(3000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '停止参数',
                                    `run_at_startup` tinyint(1) NULL DEFAULT NULL COMMENT '随操作系统启动',
                                    `guard_enabled` tinyint(1) NULL DEFAULT NULL COMMENT '由agent守护',
                                    `liveness_probe` varchar(3000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用运行探针：json格式数据',
                                    `log_path` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '应用日志目录',
                                    `host_info_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '前置机信息id',
                                    `sort_app` int(5) NULL DEFAULT NULL COMMENT 'app排序',
                                    `wait_for_ready` int(5) NULL DEFAULT NULL COMMENT '延时启动时间',
                                    `hotfixes` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '补丁包json数组',
                                    PRIMARY KEY (`id`) USING BTREE,
                                    INDEX idx_host_info_id ( `host_info_id` )
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for deploy_app_info_extend
-- ----------------------------
DROP TABLE IF EXISTS `deploy_app_info_extend`;
CREATE TABLE `deploy_app_info_extend` (
                                          `id` int(11) NOT NULL AUTO_INCREMENT,
                                          `product_name` varchar(255) NOT NULL COMMENT '产品名称',
                                          `code` varchar(255) NOT NULL COMMENT '部署中心分配的标识【0-9】组成',
                                          `app_id` varchar(255) NOT NULL COMMENT '应用唯一id',
                                          `app_type` varchar(255) NOT NULL COMMENT '应用类型：BackgroundServices、Deskto、SystemServices、Tool',
                                          `app_name` varchar(255) NOT NULL COMMENT '应用名称',
                                          `version` varchar(255) NOT NULL COMMENT '应用版本号',
                                          `download_url` varchar(255) DEFAULT NULL COMMENT '安装包下载地址',
                                          `certificate_authority` varchar(255) DEFAULT NULL COMMENT '安装签名',
                                          `hash_algorithm` varchar(255) DEFAULT 'SHA256' COMMENT '安装签名算法，目前只支持SHA256',
                                          `hash` varchar(2000) DEFAULT NULL COMMENT '安装包的hash值',
                                          `os_platforms` varchar(255) NOT NULL COMMENT '应用支持操作系统类型：json数组',
                                          `os_architectures` varchar(255) NOT NULL COMMENT '应用支持系统架构：json数组',
                                          `start` varchar(3000) NOT NULL COMMENT '启动参数：json格式数据',
                                          `stop` varchar(3000) NOT NULL COMMENT '停止参数',
                                          `run_at_startup` tinyint(1) DEFAULT NULL COMMENT '随操作系统启动',
                                          `guard_enabled` tinyint(1) DEFAULT NULL COMMENT '由agent守护',
                                          `liveness_probe` varchar(3000) NOT NULL COMMENT '应用运行探针：json格式数据',
                                          `log_path` varchar(255) DEFAULT NULL COMMENT '应用日志目录',
                                          `host_info_id` varchar(255) NOT NULL COMMENT '前置机信息id',
                                          `sort_app` int(11) DEFAULT NULL COMMENT 'app排序',
                                          `wait_for_ready` int(11) DEFAULT NULL COMMENT '延时启动时间',
                                          `hotfixes` text COMMENT '补丁包json数组',
                                          PRIMARY KEY (`id`) USING BTREE,
                                          INDEX idx_host_info_id ( `host_info_id` )
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for deploy_app_run_info
-- ----------------------------
DROP TABLE IF EXISTS `deploy_app_run_info`;
CREATE TABLE `deploy_app_run_info` (
                                       `id` int(11) NOT NULL AUTO_INCREMENT,
                                       `code` varchar(255) DEFAULT NULL COMMENT '白令海的标识',
                                       `app_id` varchar(255) DEFAULT NULL COMMENT '应用唯一id',
                                       `app_type` varchar(255) DEFAULT NULL COMMENT '应用类型',
                                       `app_name` varchar(255) DEFAULT NULL COMMENT '应用名称',
                                       `version` varchar(255) DEFAULT NULL COMMENT '应用版本号',
                                       `running` bigint(1) DEFAULT NULL COMMENT '应用是否正在运行',
                                       `process_id` varchar(255) DEFAULT NULL COMMENT '进程id',
                                       `start_time` datetime DEFAULT NULL COMMENT '应用启动时间',
                                       `stage` varchar(255) DEFAULT NULL COMMENT '应用部署阶段',
                                       `status` varchar(255) DEFAULT NULL COMMENT '应用部署该阶段的状态',
                                       `description` varchar(255) DEFAULT NULL COMMENT '描述信息',
                                       `host_id` varchar(255) NOT NULL COMMENT '主机id',
                                       `hotfixes` varchar(255) DEFAULT NULL COMMENT '补丁包',
                                       PRIMARY KEY (`id`),
                                       INDEX idx_host_id ( `host_id` )
) ENGINE=InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for deploy_app_run_info_extend
-- ----------------------------
DROP TABLE IF EXISTS `deploy_app_run_info_extend`;
CREATE TABLE `deploy_app_run_info_extend` (
                                              `id` int(11) NOT NULL AUTO_INCREMENT,
                                              `code` varchar(255) DEFAULT NULL COMMENT '白令海的标识',
                                              `app_id` varchar(255) DEFAULT NULL COMMENT '应用唯一id',
                                              `app_type` varchar(255) DEFAULT NULL COMMENT '应用类型',
                                              `app_name` varchar(255) DEFAULT NULL COMMENT '应用名称',
                                              `version` varchar(255) DEFAULT NULL COMMENT '应用版本号',
                                              `running` bigint(20) DEFAULT NULL COMMENT '应用是否正在运行',
                                              `process_id` varchar(255) DEFAULT NULL COMMENT '进程id',
                                              `start_time` datetime DEFAULT NULL COMMENT '应用启动时间',
                                              `stage` varchar(255) DEFAULT NULL COMMENT '应用部署阶段',
                                              `status` varchar(255) DEFAULT NULL COMMENT '应用部署该阶段的状态',
                                              `description` varchar(255) DEFAULT NULL COMMENT '描述信息',
                                              `host_id` varchar(255) NOT NULL COMMENT '主机id',
                                              `hotfixes` varchar(255) DEFAULT NULL COMMENT '补丁包',
                                              PRIMARY KEY (`id`) USING BTREE,
                                              INDEX idx_host_id ( `host_id` ) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for deploy_batch_host_info
-- ----------------------------
DROP TABLE IF EXISTS `deploy_batch_host_info`;
CREATE TABLE `deploy_batch_host_info` (
                                          `id` varchar(50) NOT NULL,
                                          `batch_id` varchar(50) DEFAULT NULL COMMENT '批次id',
                                          `batch_code` varchar(100) DEFAULT NULL COMMENT '批次号',
                                          `org_name` varchar(50) DEFAULT NULL COMMENT '机构名称',
                                          `org_id` varchar(50) DEFAULT NULL COMMENT '机构id',
                                          `host_id` varchar(50) DEFAULT NULL COMMENT '主机id',
                                          `host_name` varchar(100) DEFAULT NULL COMMENT '主机名称',
                                          `status` int(1) DEFAULT NULL COMMENT '状态：0进行中，1成功，2失败',
                                          `host_ip` varchar(100) DEFAULT NULL COMMENT '主机ip',
                                          `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间',
                                          `finish_time` datetime DEFAULT NULL COMMENT '结束时间',
                                          PRIMARY KEY (`id`),
                                          INDEX idx_host_id ( `host_id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ----------------------------
-- Table structure for deploy_front_task
-- ----------------------------
DROP TABLE IF EXISTS `deploy_front_task`;
CREATE TABLE `deploy_front_task` (
                                     `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键',
                                     `host_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主机id',
                                     `task_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '任务类型：rebootNode-重启计算机、updateAppSetting-设置agent信息、startApp-启动应用、stopApp-停止应用、restartApp-重启应用、uploadAppList-推送应用清单、uploadLog-上传日志',
                                     `task_status` int(1) NOT NULL DEFAULT 0 COMMENT '任务状态：0-待执行，1-成功，2-失败，3-执行中,4-已发送',
                                     `task_status_desc` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '任务状态描述',
                                     `task_start_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '任务开始时间',
                                     `task_end_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '任务结束时间',
                                     `arguments` varchar(255) DEFAULT NULL COMMENT '任务参数json',
                                     `sort_app` int(11) DEFAULT NULL COMMENT '应用启动顺序',
                                     PRIMARY KEY (`id`) USING BTREE,
                                     INDEX idx_host_id ( `host_id` )
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for deploy_host_info
-- ----------------------------
DROP TABLE IF EXISTS `deploy_host_info`;
CREATE TABLE `deploy_host_info`  (
                                     `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '主键',
                                     `hostName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主机名称',
                                     `hostIp` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主机ip',
                                     `hostSystem` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主机操作系统类型',
                                     `hostFrame` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作系统架构',
                                     `hostMac` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'MAC地址',
                                     `hostOrg` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所属机构id',
                                     `hostStatus` int(1) NULL DEFAULT NULL COMMENT '主机状态：1-在线 2-离线',
                                     `accessToken` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '令牌',
                                     `expiresIn` int(255) NULL DEFAULT NULL COMMENT '令牌有效期',
                                     `tokenType` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '类型 默认Bearer',
                                     `scope` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '授权范围',
                                     `agentVersion` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '白令海版本',
                                     `isPush` int(1) NULL DEFAULT NULL COMMENT '是否推送清单，0，未推送，1已推送',
                                     `tokenCreateTime` bigint(20) NULL DEFAULT NULL COMMENT '令牌生成时间',
                                     `coreTimeStamp` bigint(20) NULL DEFAULT 0 COMMENT '部署中心时间戳',
                                     `frontTimeStamp` bigint(20) NULL DEFAULT 0 COMMENT '前置机时间戳',
                                     `upload_version` varchar(255) NULL DEFAULT NULL comment '前置机Agent需要升级的版本',
                                     `register_date` datetime DEFAULT NULL COMMENT '注册时间',
                                     `encryption` int(1) DEFAULT '0' COMMENT '是否开启数据传输加密，0.不开启 1.开启',
                                     PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for deploy_host_zip_info
-- ----------------------------
DROP TABLE IF EXISTS `deploy_host_zip_info`;
CREATE TABLE `deploy_host_zip_info` (
                                        `id` varchar(50) NOT NULL,
                                        `host_name` varchar(50) DEFAULT NULL COMMENT '主机名称',
                                        `host_id` varchar(50) DEFAULT NULL COMMENT '主机id',
                                        `org_id` varchar(50) DEFAULT NULL COMMENT '机构id',
                                        `zip_version` varchar(100) DEFAULT NULL COMMENT '当前版本',
                                        `target_version` varchar(100) DEFAULT NULL COMMENT '目标版本',
                                        `status` varchar(100) DEFAULT NULL COMMENT '状态',
                                        `org_name` varchar(100) DEFAULT NULL COMMENT '主机名称',
                                        `target_zip_id` varchar(50) DEFAULT NULL COMMENT '目标版本id',
                                        `batch_code` varchar(50) DEFAULT NULL COMMENT '最新批次号',
                                        `host_ip` varchar(100) DEFAULT NULL COMMENT '主机ip',
                                        `update_time` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新数据的时间(插入新数据为null)',
                                        PRIMARY KEY (`id`),
                                        INDEX idx_host_id ( `host_id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for deploy_operation_param
-- ----------------------------
DROP TABLE IF EXISTS `deploy_operation_param`;
CREATE TABLE `deploy_operation_param` (
                                          `id` int(11) NOT NULL,
                                          `server_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '服务名称',
                                          `server_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '服务标识',
                                          `param_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '启动参数名称',
                                          `param_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '启动参数标识',
                                          `param_value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '启动参数值',
                                          `status` int(2) NULL DEFAULT NULL COMMENT '状态：0 未启用 1.启用',
                                          PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of deploy_operation_param
-- ----------------------------
INSERT INTO `deploy_operation_param` VALUES (1, '主机iot服务', 'VIMD', '数据上报平台IP', '--pushIp=', NULL, 1);
INSERT INTO `deploy_operation_param` VALUES (2, '主机iot服务', 'VIMD', '数据上报平台端口', '--pushP=', '8102', 1);

-- ----------------------------
-- Table structure for deploy_package_info
-- ----------------------------
DROP TABLE IF EXISTS `deploy_package_info`;
CREATE TABLE `deploy_package_info` (
                                       `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键id',
                                       `package_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '升级包名称',
                                       `server_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '服务code',
                                       `server_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '服务名称',
                                       `status` tinyint(2) NULL DEFAULT NULL COMMENT '状态：0.未启用 1.启用',
                                       `package_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '升级包存放路径',
                                       `package_version` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '升级包版本号',
                                       `upload_time` datetime(0) NULL DEFAULT NULL COMMENT '上传时间',
                                       PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for deploy_upgrade_batch_info
-- ----------------------------
DROP TABLE IF EXISTS `deploy_upgrade_batch_info`;
CREATE TABLE `deploy_upgrade_batch_info` (
                                             `id` varchar(50) NOT NULL,
                                             `zip_id` varchar(50) NOT NULL COMMENT '升级包id',
                                             `org_size` int(10) DEFAULT NULL COMMENT '机构数量',
                                             `host_size` int(10) DEFAULT NULL COMMENT '主机数量',
                                             `status` int(1) DEFAULT '0' COMMENT '批次升级状态',
                                             `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建日期',
                                             `batch_code` varchar(100) DEFAULT NULL COMMENT '升级批次号',
                                             `zip_version` varchar(100) DEFAULT NULL COMMENT '升级包名称',
                                             `finish_time` datetime(0) NULL COMMENT '结束时间',
                                             PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for deploy_upgrade_task
-- ----------------------------
DROP TABLE IF EXISTS `deploy_upgrade_task`;
CREATE TABLE `deploy_upgrade_task` (
                                       `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '升级任务主键',
                                       `batch_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '批次号',
                                       `task_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '任务号',
                                       `host_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '主机id',
                                       `deploy_stages` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部署步骤：download-下载，install-安装，Ready-就绪，Uninstall-卸载，Cleanup-清理',
                                       `deploy_status` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部署状态：InProgress-进行中，Successed-成功，Failed-失败',
                                       `deploy_description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '任务执行状态描述',
                                       `task_start_time` datetime(0) NULL DEFAULT NULL COMMENT '任务开始时间',
                                       `task_end_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '任务结束时间',
                                       `app_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '应用标识',
                                       `app_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '服务类型名',
                                       `now_version` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '当前版本号',
                                       `targe_version` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '目标版本号',
                                       `app_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '应用code',
                                       `task_status` int(1) NULL DEFAULT NULL COMMENT '任务状态：0-进行中，1-成功，2-失败',
                                       `zip_id` varchar(50) DEFAULT NULL COMMENT '升级任务使用的是哪个升级包',
                                       `hotfix_version` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '补丁包',
                                       PRIMARY KEY (`id`) USING BTREE,
                                       INDEX idx_host_id ( `host_id` )
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for deploy_upgrade_task_his
-- ----------------------------
DROP TABLE IF EXISTS `deploy_upgrade_task_his`;
CREATE TABLE `deploy_upgrade_task_his`  (
                                            `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '升级任务主键',
                                            `batch_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '批次号',
                                            `task_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '任务号',
                                            `host_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '主机id',
                                            `deploy_stages` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部署步骤：download-下载，install-安装，Ready-就绪，Uninstall-卸载，Cleanup-清理',
                                            `deploy_status` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部署状态：InProgress-进行中，Successed-成功，Failed-失败',
                                            `deploy_description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '任务执行状态描述',
                                            `task_start_time` datetime(0) NULL DEFAULT NULL COMMENT '任务开始时间',
                                            `task_end_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '任务结束时间',
                                            `app_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '应用标识',
                                            `app_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '服务类型名',
                                            `now_version` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '当前版本号',
                                            `targe_version` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '目标版本号',
                                            `app_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '应用code',
                                            `task_status` int(1) NULL DEFAULT NULL COMMENT '任务状态：0-进行中，1-成功，2-失败',
                                            `zip_id` varchar(50) DEFAULT NULL COMMENT '升级任务使用的是哪个升级包',
                                            `hotfix_version` varchar(255) DEFAULT NULL COMMENT '补丁包',
                                            PRIMARY KEY (`id`) USING BTREE,
                                            INDEX idx_host_id ( `host_id` )
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for deploy_upload_app_info
-- ----------------------------
DROP TABLE IF EXISTS `deploy_upload_app_info`;
CREATE TABLE `deploy_upload_app_info` (
                                          `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '升级应用app主键',
                                          `down_path` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '物理路径',
                                          `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '产品名称',
                                          `code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '部署中心分配的标识【0-9】组成',
                                          `app_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用唯一id',
                                          `app_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '应用类型：BackgroundServices、Deskto、SystemServices、Tool',
                                          `app_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用名称',
                                          `version` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用版本号',
                                          `download_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '安装包下载地址',
                                          `certificate_authority` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '安装签名',
                                          `hash_algorithm` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'SHA256' COMMENT '安装签名算法，目前只支持SHA256',
                                          `hash` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '安装包的hash值',
                                          `os_platforms` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用支持操作系统类型：json数组',
                                          `os_architectures` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用支持系统架构：json数组',
                                          `start` varchar(3000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '启动参数：json格式数据',
                                          `stop` varchar(3000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '停止参数',
                                          `run_at_startup` tinyint(1) NULL DEFAULT NULL COMMENT '随操作系统启动',
                                          `guard_enabled` tinyint(1) NULL DEFAULT NULL COMMENT '由agent守护',
                                          `liveness_probe` varchar(3000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '应用运行探针：json格式数据',
                                          `log_path` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '应用日志目录',
                                          `zip_id` varchar(50) DEFAULT NULL COMMENT '来源那个zip包',
                                          `limit_version` varchar(255) DEFAULT NULL COMMENT '限制版本',
                                          `sort_app` int(5) NULL DEFAULT NULL COMMENT 'app排序',
                                          `wait_for_ready` int(5) NULL DEFAULT NULL COMMENT '延时启动时间',
                                          `type` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'app' COMMENT '包类型：app-整包；hotfix-补丁包',
                                          `overrides` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '[]' COMMENT '被替代补丁版本字符串json数组集合 这个hotfix替代了哪些hotfix.生成安装部署清单时，清确被替代项不被下发',
                                          `hotfix_version` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '补丁包版本',
                                          `dependencies` varchar(1000) DEFAULT NULL COMMENT '/依赖项，生成安装部署清单时，请确保依赖项来排列',
                                          PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


-- ----------------------------
-- Table structure for deploy_zip_pack_info
-- ----------------------------
DROP TABLE IF EXISTS `deploy_zip_pack_info`;
CREATE TABLE `deploy_zip_pack_info` (
                                        `id` varchar(50) NOT NULL COMMENT '主键',
                                        `zip_version` varchar(50) DEFAULT NULL,
                                        `zip_service` varchar(100) DEFAULT NULL COMMENT '服务类型',
                                        `up_version` varchar(50) DEFAULT NULL COMMENT '针对升级版本',
                                        `publish_date` varchar(50) DEFAULT NULL COMMENT '发布时间',
                                        `upload_date` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '上传时间',
                                        `upload_user` varchar(50) DEFAULT NULL COMMENT '上传人',
                                        `zip_size` varchar(50) DEFAULT NULL COMMENT 'zip包大小',
                                        `zip_path` varchar(255) DEFAULT NULL COMMENT 'zip解压解析完后存放的路径',
                                        `zip_name` varchar(100) DEFAULT NULL COMMENT '压缩包名称',
                                        `host_type` varchar(255) DEFAULT NULL COMMENT '升级包使用主机类型',
                                        `host_type_name` varchar(255) DEFAULT NULL COMMENT '升级包使用主机类型名称',
                                        PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for deploy_operation_log
-- ----------------------------
DROP TABLE IF EXISTS `deploy_operation_log`;
CREATE TABLE `deploy_operation_log`  (
                                         `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
                                         `user_id` bigint(0) NULL DEFAULT NULL COMMENT '用户id',
                                         `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作人',
                                         `organize_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所属机构',
                                         `path` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '机构路径',
                                         `org_id` bigint(0) NULL DEFAULT NULL COMMENT '机构ID',
                                         `host_ip` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主机IP',
                                         `host_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主机名称',
                                         `host_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主机ID',
                                         `target_version` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '目标版本号',
                                         `mirror_version` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '当前版本号',
                                         `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作描述',
                                         `opera_time` datetime(0) NULL DEFAULT NULL COMMENT '操作时间',
                                         `opera_result` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作结果',
                                         `reserve_field1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '预备字段1',
                                         `reserve_field2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '预备字段2',
                                         PRIMARY KEY (`id`) USING BTREE,
                                         INDEX `IDX_PATH`(`organize_name`) USING BTREE,
                                         INDEX `IDX_MIRROR_VERSION`(`mirror_version`) USING BTREE,
                                         INDEX `IDX_TARGET_VERSION`(`target_version`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;



DROP TABLE IF EXISTS `iot_device_info`;
CREATE TABLE `iot_device_info`  (
                                    `id` bigint(0) NOT NULL,
                                    `device_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备类型',
                                    `device_product` varchar(62) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备品牌',
                                    `device_model` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备型号',
                                    `device_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备编码',
                                    `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备名称',
                                    `iot_token` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'iot服务唯一编码',
                                    `org_id` bigint(0) NULL DEFAULT NULL COMMENT '机构id',
                                    `org_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '机构名称',
                                    `org_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '机构路径',
                                    `net_status` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '网络状态:0:未知,1:在线,2:离线,3:异常',
                                    `host_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '主机编码（硬盘，通道都有层级）',
                                    `deleted` int(0) NULL DEFAULT NULL COMMENT '0:未删除，1：删除',
                                    `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
                                    `update_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
                                    `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
                                    `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人',
                                    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '设备信息' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for iot_device_info_extend
-- ----------------------------
DROP TABLE IF EXISTS `iot_device_info_extend`;
CREATE TABLE `iot_device_info_extend`  (
           `id` bigint(0) NOT NULL COMMENT '主键',
           `device_id` bigint(0) NOT NULL COMMENT '设备id',
           `user_name` varchar(100) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '登录用户名',
           `password` varchar(100) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '登录密码',
           `port` int(0) NULL DEFAULT NULL COMMENT '端口',
           `net_address` varchar(20) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT 'ip地址',
           `deleted` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '删除状态',
           `iot_token` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'iot服务token',
           `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
           `update_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
           `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
           `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人',
           PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `iot_device_status`;
CREATE TABLE `iot_device_status` (
         `id` bigint NOT NULL,
         `device_id` bigint DEFAULT NULL COMMENT '设备id',
         `device_product` varchar(62) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备品牌',
         `device_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备编码',
         `device_type` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备类型',
         `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备名称',
         `iot_token` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot服务唯一编码',
         `unique_code` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备编码',
         `org_id` bigint DEFAULT NULL COMMENT '机构id',
         `org_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
         `org_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构路径',
         `info` text COLLATE utf8mb4_general_ci COMMENT '设备状态数据',
         `state_update_time` datetime DEFAULT NULL COMMENT '最后一次状态更新时间',
         `state_start_time` datetime DEFAULT NULL COMMENT '状态更新开始时间',
         `state` int DEFAULT 2 COMMENT '告警状态,0:正常,1:告警,2:未知',
         `create_time` datetime DEFAULT NULL COMMENT '创建时间',
         `update_time` datetime DEFAULT NULL COMMENT '修改时间',
         `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
         `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人',
         PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

drop table if exists iot_device_status_log;
CREATE TABLE `iot_device_status_log` (
         `id` bigint NOT NULL,
         `device_status_id` bigint DEFAULT NULL COMMENT '设备id',
         `device_id` bigint DEFAULT NULL COMMENT '设备id',
         `device_product` varchar(62) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备品牌',
         `device_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备编码',
         `device_type` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备类型',
         `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备名称',
         `iot_token` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot服务唯一编码',
         `unique_code` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备编码',
         `org_id` bigint DEFAULT NULL COMMENT '机构id',
         `org_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
         `org_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构路径',
         `info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '设备状态数据',
         `state` int DEFAULT '2' COMMENT '告警状态,0:正常,1:告警,2:未知',
         `create_time` datetime DEFAULT NULL COMMENT '创建时间',
         PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- ----------------------------
-- view structure for hv_org
-- ----------------------------
drop view if exists hv_org;
CREATE VIEW `hv_org` AS
select id as org_id,code as org_code,short_name as org_name,parent_id,sort as order_no,path,deleted as is_deleted,type
from sys_org;

-- ----------------------------
-- view structure for hv_host_org
-- ----------------------------
drop view if exists hv_host_org;
CREATE VIEW `hv_host_org` AS
SELECT
    r.iot_code AS register_code,
    r.org_id,
    o.org_code,
    o.org_name,
    o.parent_id,
    o.order_no,
    o.path,
    o.is_deleted,
    o.type,
    h.hostName,
    h.hostIp,
    h.hostSystem,
    h.hostMac,
    h.accessToken,
    h.expiresIn,
    h.tokenCreateTime,
    h.coreTimeStamp,
    h.frontTimeStamp
FROM
    deploy_host_info h
    LEFT JOIN iot_server_info r ON r.iot_code = h.id
    LEFT JOIN hv_org o ON r.org_id = o.org_id;

-- ----------------------------
-- 系统配置：部署中心允许主机重新注册
-- ----------------------------
delete from sys_config where config_key = 'DEPLOY_REGISTER_AGAIN';
INSERT INTO `sys_config`(config_name,config_key,config_value,config_type,create_by,create_time,update_by,update_time,remark)
VALUES ('部署中心：允许主机重新注册', 'DEPLOY_REGISTER_AGAIN', '1', 'Y', '超级管理员', '2024-07-02 10:25:25', '', null, '0:否,1:是');

-- ----------------------------
-- 菜单配置：部署中心菜单初始化
-- ----------------------------
delete from sys_menu where  id in ('01806204625679527938','01806205123841208322','01806205680513429506','01806206030435823618','01806206236392927233','01808375108978040834');
INSERT INTO `sys_menu` VALUES ('01806204625679527938', '部署中心', '0', '5', 'deploy', null, null, '1', '0', 'M', '0', '0', '', '1', 'monitor', null, '超级管理员', '2024-06-27 13:55:15', '超级管理员', '2024-06-27 13:55:37', '');
INSERT INTO `sys_menu` VALUES ('01806205123841208322', '主机管理', '1806204625679527938', '1', 'hostInfo', 'deploy/hostInfo/index', null, '1', '0', 'C', '0', '0', 'deploy:hostInfo:list', '1', 'monitor', null, '超级管理员', '2024-06-27 13:57:13', '', null, '');
INSERT INTO `sys_menu` VALUES ('01806205680513429506', '升级包管理', '1806204625679527938', '2', 'package', 'deploy/package/index', null, '1', '0', 'C', '0', '0', 'deploy:package:list', '1', 'zip', null, '超级管理员', '2024-06-27 13:59:26', '', null, '');
INSERT INTO `sys_menu` VALUES ('01806206030435823618', '升级状态查询', '1806204625679527938', '3', 'upgradeStatus', 'deploy/upgradeStatus/index', null, '1', '0', 'C', '0', '0', 'deploy:upgradeStatus:list', '1', 'documentation', null, '超级管理员', '2024-06-27 14:00:50', '', null, '');
INSERT INTO `sys_menu` VALUES ('01806206236392927233', '白令海管理', '1806204625679527938', '4', 'bering', 'deploy/bering/index', null, '1', '0', 'C', '0', '0', 'deploy:bering:list', '1', 'component', null, '超级管理员', '2024-06-27 14:01:39', '', null, '');
INSERT INTO `sys_menu` VALUES ('01808375108978040834', '日志', '1806204625679527938', '5', 'log', 'deploy/log/index', null, '1', '0', 'C', '0', '0', 'deploy:log:list', '1', 'log', null, '超级管理员', '2024-07-03 13:39:58', '', null, '');

-- 新增巡检主机接入服务菜单
delete from sys_menu where  id in ('1810216137011572738');
INSERT INTO sys_menu (id, menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, platform_type, icon, image_path, create_by, create_time, update_by, update_time, remark) VALUES (1810216137011572738, '巡检主机接入', 1747911340288892930, 0, 'server/center', 'iot/server/index', null, 1, 0, 'C', '0', '0', null, '1', 'tree', null, '景远超', '2024-07-08 15:35:39', '', null, '');
-- 巡检主机接入菜单角色授权
delete from sys_role_menu where sys_role_menu.menu_id = 1810216137011572738;
insert into sys_role_menu select  id,1810216137011572738 from sys_role where org_type in (1,2,3,4);

-- 新增设备管理菜单
delete from sys_menu where id in ('01815673314170544129');
INSERT INTO sys_menu(`id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `platform_type`, `icon`, `image_path`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (01815673314170544129, '设备管理', 1747911340288892930, 8, 'deviceInfo', 'iot/deviceInfo/index', NULL, 1, 0, 'C', '0', '0', 'iot:deviceInfo:list', '1', 'client', NULL, '超级管理员', '2024-07-23 17:00:26', '超级管理员', '2024-07-23 17:01:21', '');


-- 部署中心字典
delete from sys_dict_type where dict_type='deploy_upgrade_status';
delete from sys_dict_type where dict_type='deploy_bering_status';
delete from sys_dict_type where dict_type='deploy_service_status';
delete from sys_dict_type where dict_type='iot_brand_type';
delete from sys_dict_type where dict_type='iot_device_type';
delete from sys_dict_type where dict_type='iot_net_status';
INSERT INTO `sys_dict_type`(`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('部署中心升级状态', 'deploy_upgrade_status', '0', '超级管理员', '2024-06-27 14:34:58', '', NULL, NULL);
INSERT INTO `sys_dict_type`(`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('部署中心白令海状态', 'deploy_bering_status', '0', '超级管理员', '2024-06-27 14:37:50', '', NULL, NULL);
INSERT INTO `sys_dict_type`(`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('部署中心服务状态', 'deploy_service_status', '0', '超级管理员', '2024-06-27 14:35:26', '', NULL, NULL);
INSERT INTO sys_dict_type (`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('设备品牌信息', 'iot_brand_type', '0', '超级管理员', '2024-07-10 14:30:23', '超级管理员', '2024-07-10 15:24:37', '设备管理品牌信息');
INSERT INTO sys_dict_type (`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('设备类型', 'iot_device_type', '0', '超级管理员', '2024-07-10 15:24:28', '', NULL, '设备管理设备类型');
INSERT INTO sys_dict_type (`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('设备在线状态', 'iot_net_status', '0', '超级管理员', '2024-07-10 15:26:24', '', NULL, NULL);




delete from sys_dict_data where dict_type='deploy_upgrade_status';
delete from sys_dict_data where dict_type='deploy_bering_status';
delete from sys_dict_data where dict_type='deploy_service_status';
delete from sys_dict_data where dict_type='iot_brand_type';
delete from sys_dict_data where dict_type='iot_device_type';
delete from sys_dict_data where dict_type='iot_net_status';
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '进行中', '0', 'deploy_upgrade_status', NULL, 'default', 'N', '0', '超级管理员', '2024-06-27 14:39:08', '', NULL, NULL);
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (1, '成功', '1', 'deploy_upgrade_status', NULL, 'default', 'N', '0', '超级管理员', '2024-06-27 14:39:21', '', NULL, NULL);
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2, '失败', '2', 'deploy_upgrade_status', NULL, 'default', 'N', '0', '超级管理员', '2024-06-27 14:39:32', '', NULL, NULL);
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (3, '重试中', '3', 'deploy_upgrade_status', NULL, 'default', 'N', '0', '超级管理员', '2024-06-27 14:39:39', '', NULL, NULL);
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '异常', '0', 'deploy_service_status', NULL, 'default', 'N', '0', '超级管理员', '2024-06-27 14:40:45', '', NULL, NULL);
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (1, '正常', '1', 'deploy_service_status', NULL, 'default', 'N', '0', '超级管理员', '2024-06-27 14:40:55', '', NULL, NULL);
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '在线', '1', 'deploy_bering_status', NULL, 'default', 'N', '0', '超级管理员', '2024-06-27 14:42:30', '', NULL, NULL);
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (1, '离线', '2', 'deploy_bering_status', NULL, 'default', 'N', '0', '超级管理员', '2024-06-27 14:42:36', '', NULL, NULL);

INSERT INTO  sys_dict_data(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '海康', 'VGSII_Hik', 'iot_brand_type', NULL, 'default', 'N', '0', '超级管理员', '2024-07-10 14:30:54', '', NULL, NULL);
INSERT INTO  sys_dict_data(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (1, '大华', 'VGSII_DaHua', 'iot_brand_type', NULL, 'default', 'N', '0', '超级管理员', '2024-07-10 14:31:16', '', NULL, NULL);
INSERT INTO  sys_dict_data(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (1, 'DVS监控主机', '1', 'iot_device_type', NULL, 'default', 'N', '0', '超级管理员', '2024-07-10 15:25:30', '', NULL, NULL);
INSERT INTO  sys_dict_data(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2, '摄像头', '2', 'iot_device_type', NULL, 'default', 'N', '0', '超级管理员', '2024-07-10 15:25:39', '', NULL, NULL);
INSERT INTO  sys_dict_data(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '未知', '0', 'iot_net_status', NULL, 'default', 'N', '0', '超级管理员', '2024-07-10 15:27:03', '', NULL, NULL);
INSERT INTO  sys_dict_data(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (1, '在线', '1', 'iot_net_status', NULL, 'default', 'N', '0', '超级管理员', '2024-07-10 15:27:13', '', NULL, NULL);
INSERT INTO  sys_dict_data(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2, '离线', '2', 'iot_net_status', NULL, 'default', 'N', '0', '超级管理员', '2024-07-10 15:27:23', '', NULL, NULL);
INSERT INTO  sys_dict_data(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (3, '异常', '3', 'iot_net_status', NULL, 'default', 'N', '0', '超级管理员', '2024-07-10 15:27:35', '', NULL, NULL);

-- 新增iot服务链接状态字典
delete from sys_dict_type where dict_type='iot_connect_status';
delete from sys_dict_data where dict_type='iot_connect_status';
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, update_by, update_time, remark) VALUES ('iot服务状态', 'iot_connect_status', '0', '超级管理员', '2024-07-08 15:45:42', '', null, null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (0, '未启用', '0', 'iot_connect_status', null, 'default', 'N', '0', '超级管理员', '2024-07-08 15:46:15', '', null, null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (1, '在线', '1', 'iot_connect_status', null, 'default', 'N', '0', '超级管理员', '2024-07-08 15:46:27', '', null, null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (2, '离线', '2', 'iot_connect_status', null, 'default', 'N', '0', '超级管理员', '2024-07-08 15:46:40', '', null, null);

delete from iot_alarm_system_field;
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (129, 4184, '水浸', '4184', '水浸告警', 'alarm', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (130, 4182, '烟感', '4182', '烟雾告警', 'alarm', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (131, 4183, '温湿度', '4183_1', '环境温度', 'temperature', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', '°C', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (132, 4183, '温湿度', '4183_2', '环境湿度', 'humidity', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', '%RH', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (133, 41885, '燃气', '41885', '可燃气体告警', 'alarm', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', '', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (134, 4188, '门磁', '4188', '门磁开关状态', 'alarm', '{\"0\":\"门已打开\",\"1\":\"门已关闭\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', '', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (135, 4181, '红外', '4181', '红外告警', 'alarm', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', '', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (136, 41881, '盗情', '41881', '被盗告警', 'alarm', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (137, 415, '空调', '415_1', '电流', 'current', '{}', '{}', 'FLOAT', '浮点型', 'A', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (138, 415, '空调', '415_2', '电压', 'voltage', '{}', '{}', 'FLOAT', '浮点型', 'V', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (139, 415, '空调', '415_3', '回风温度', 'returnAirTemperature', '{}', '{}', 'FLOAT', '浮点型', '°C', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (140, 415, '空调', '415_4', '室内温度', 'indoorAirTemperature', '{}', '{}', 'FLOAT', '浮点型', '°C', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (141, 415, '空调', '415_5', '室外温度', 'indoorAirTemperature', '{}', '{}', 'FLOAT', '浮点型', '°C', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (142, 415, '空调', '415_6', '温度', 'temperature', '{}', '{}', 'FLOAT', '浮点型', '°C', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (143, 415, '空调', '415_7', '湿度', 'humidity', '{}', '{}', 'FLOAT', '浮点型', '%RH', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (144, 415, '空调', '415_8', '室内湿度', 'indoorHumidity', '{}', '{}', 'FLOAT', '浮点型', '%RH', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (145, 408, 'UPS', '408_1', 'UPS输入相电压Ua', 'upsInputPhaseVoltageUa', '{}', '{}', 'FLOAT', '浮点型', 'V', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (146, 408, 'UPS', '408_2', 'UPS输入相电压Ub', 'upsInputPhaseVoltageUb', '{}', '{}', 'FLOAT', '浮点型', 'V', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (147, 408, 'UPS', '408_3', 'UPS输入相电压Uc', 'upsInputPhaseVoltageUc', '{}', '{}', 'FLOAT', '浮点型', 'V', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (148, 408, 'UPS', '408_4', 'UPS输入频率', 'upsInputFrequency', '{}', '{}', 'FLOAT', '浮点型', 'Hz', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (149, 408, 'UPS', '408_5', 'UPS输出相电压Ua', 'upsOutputPhaseVoltageUa', '{}', '{}', 'FLOAT', '浮点型', 'V', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (150, 408, 'UPS', '408_6', 'UPS输出相电压Ub', 'upsOutputPhaseVoltageUb', '{}', '{}', 'FLOAT', '浮点型', 'V', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (151, 408, 'UPS', '408_7', 'UPS输出相电压Uc', 'upsOutputPhaseVoltageUc', '{}', '{}', 'FLOAT', '浮点型', 'V', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (152, 408, 'UPS', '408_8', 'UPS输出频率', 'upsOutputFrequency', '{}', '{}', 'FLOAT', '浮点型', 'Hz', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (153, 408, 'UPS', '408_9', '交流输入中断告警', 'acInputInterruptAlarm', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (154, 408, 'UPS', '408_10', 'UPS主机温度', 'upsHostTemperature', '{}', '{}', 'FLOAT', '浮点型', '°C', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (155, 408, 'UPS', '408_1', '旁路状态', 'bypassState', '{}', '{}', 'TEXT', '文本型', NULL, 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (156, 408, 'UPS', '408_2', 'UPS类型', 'upsType', '{}', '{}', 'TEXT', '文本型', NULL, 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (157, 408, 'UPS', '408_3', 'UPS电池电流', 'upsBatteryCurrent', '{}', '{}', 'FLOAT', '浮点型', 'A', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (158, 408, 'UPS', '408_4', '电池容量', 'batteryCapacity', '{}', '{}', 'FLOAT', '浮点型', '%', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (159, 408, 'UPS', '408_5', '电池剩余备用时间', 'batteryRemainingStandbyTime', '{}', '{}', 'FLOAT', '浮点型', 's', 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (160, 408, 'UPS', '408_6', '电池电压低告警', 'lowBatteryVoltageAlarm', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, NULL, NULL, NULL, NULL);
INSERT INTO  `iot_alarm_system_field`(`id`, `source_type`, `source_type_des`, `sys_field_code`, `name`, `property_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (161, 4186, '8小时回路', '4186', '8小时回路通电状态', 'status', '{\"0\": \"断电\",\"1\": \"通电\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, NULL, NULL, NULL, NULL);

delete from sys_dict_data where dict_type = 'sensor_alarm_status' and dict_value='2';
insert into sys_dict_data (dict_sort, dict_label, dict_value, dict_type, list_class, status, create_by, create_time) values (2, '未配置告警规则', '2', 'sensor_alarm_status', 'default', '0', '', sysdate());

drop table if exists iot_daily_check_data;
create table iot_daily_check_data
(
    id          bigint       not null comment 'id'
        primary key,
    org_id      bigint       null comment '机构id',
    org_name    varchar(125) null comment '机构名称',
    org_path    varchar(125) null comment '机构path',
    iot_code    varchar(125) null comment '主机token',
    file_name   varchar(225) null comment '文件名称',
    user_name   varchar(255) null comment '执行人',
    record_date datetime     null comment '自检时间',
    data        text         null comment '自检结果,json形式',
    create_time datetime     null comment '创建时间',
    update_time datetime     null comment '修改时间'
)
    comment '网点设备健康度日志表';
-- 新增网点日常自检日志菜单
delete from sys_menu where id ='01831141820470513666';
INSERT INTO `sys_menu`(`id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `platform_type`, `icon`, `image_path`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (01831141820470513666, '设备健康度', 1747911340288892930, 9, 'dailyCheck', 'iot/dailycheck/index', NULL, 1, 0, 'C', '0', '0', '', '1', 'dashboard', NULL, '超级管理员', '2024-09-04 09:26:41', '超级管理员', '2024-09-05 10:28:46', '');
delete from sys_role_menu where menu_id='01831141820470513666';
insert into sys_role_menu select  id,'01831141820470513666' from sys_role where org_type in (1,2,3,4);

-- 上下班规则表
drop table if exists iot_work_rule;
CREATE TABLE `iot_work_rule` (
                                 `id` bigint NOT NULL ,
                                 `work_type` int DEFAULT NULL COMMENT '上下班规则：1上班规则，2下班规则',
                                 `work_rule_type` int DEFAULT NULL COMMENT '上下班规则类型：1布撤防规则，2回路规则',
                                 `rule_obj_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '防区或者回路id',
                                 `rule_obj_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '防区或者回路名称',
                                 `rule_obj_status` int DEFAULT NULL COMMENT '防区：布防，撤防，回路：通电，断电',
                                 `iot_token` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot_toke',
                                 `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                 `update_time` datetime DEFAULT NULL COMMENT '更新时间',
                                 `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '规则描述',
                                 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='上下班规则表';

-- 上下班规则和子系统关联表
drop table if exists iot_work_rule_item;
CREATE TABLE `iot_work_rule_item` (
                                      `id` bigint NOT NULL,
                                      `rule_id` bigint NOT NULL COMMENT '规则id',
                                      `obj_id` bigint DEFAULT NULL COMMENT '对象id',
                                      `zone_id` int DEFAULT NULL COMMENT '区域编号',
                                      `alarm_host` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '主机编号',
                                      `iot_token` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot_token',
                                      PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='报警主机防区和上下班规则关系表';