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;

    -- 视频诊断记录表修改录像质量诊断图片存储字段类型
    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 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();


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:已使用',
       `password` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '上下班操作密码',
       `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;

drop table if exists iot_day_work;
CREATE TABLE `iot_day_work` (
            `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
            `host_data_id` bigint DEFAULT NULL COMMENT '主机上报数据主键id',
            `iot_code` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '主机token',
            `org_id` bigint NOT NULL COMMENT '机构id',
            `org_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
            `org_path` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构path层级',
            `data_status` int DEFAULT '0' COMMENT '数据状态：0、进行中；1、正常，2、异常',
            `data_type` int DEFAULT NULL COMMENT '数据类型：1：一键上班，2：一键下班',
            `create_time` datetime DEFAULT NULL COMMENT '数据创建时间',
            `update_time` datetime DEFAULT NULL COMMENT '数据最后一次更新时间',
            `work_date` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据生成 格式化字符串天：yyyy-MM-dd',
            PRIMARY KEY (`id`) USING BTREE,
            KEY `idx_iotCode_orgId` (`iot_code`,`org_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1834045749179330563 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='一键上下班';

drop table if exists iot_day_work_field;
CREATE TABLE `iot_day_work_field` (
          `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
          `iot_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '主机token',
          `source_type` int DEFAULT NULL COMMENT '数据分组类型：主机类，传感器类',
          `host_data_id` bigint NOT NULL COMMENT '一键上下班id',
          `inspection_type` bigint DEFAULT NULL COMMENT '数据类型：0、布撤防；1、用电数据；2、录像天数；3、录像完整性；4、动环数据；5、UPS主机数据',
          `data_content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据内容',
          `data_status` int DEFAULT NULL COMMENT '数据状态：0、异常；1、正常；2、进行中',
          `create_time` datetime DEFAULT NULL COMMENT '创建时间',
          `update_time` datetime DEFAULT NULL COMMENT '更新时间',
          `data_value` int DEFAULT NULL COMMENT '控制类的当前值：如布撤防 0 撤防，断电 1 布防，通电',
          `data_to_value` int DEFAULT NULL COMMENT '控制类目标值，如布撤防应布防 1，用电设备应断电0 ，应撤防0，应通电1',
          `device_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备id、code',
          `redirect_type` int DEFAULT NULL COMMENT '跳转类型',
          `is_controller` int DEFAULT NULL COMMENT '标识是否为控制类：0，非控制类，1控制类',
          `product_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
          `device_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
          `normal_count` int DEFAULT NULL COMMENT '正常数量',
          `abnormal_count` int DEFAULT NULL COMMENT '异常数量',
          `other_json` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '详细信息',
          PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=411 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

drop table if exists iot_day_work_operation;
CREATE TABLE `iot_day_work_operation` (
          `id` bigint NOT NULL AUTO_INCREMENT,
          `org_id` bigint DEFAULT NULL COMMENT '机构id',
          `org_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
          `iot_code` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '主机token',
          `host_data_id` bigint DEFAULT NULL COMMENT '主机上报数据主键id,上下班id，可以为空，单个操作时可以为空',
          `host_data_operation_id` bigint DEFAULT NULL COMMENT '主机上报操作数据主键id',
          `operation_type` int DEFAULT NULL COMMENT '操作类型：0.布撤防，1控电',
          `target_value` int DEFAULT NULL COMMENT '目标值：0 断电/撤防 1通电/布防',
          `operation_time` datetime DEFAULT NULL COMMENT '操作时间',
          `force_end_time` datetime DEFAULT NULL COMMENT '数据强制结束时间',
          `real_end_time` datetime DEFAULT NULL COMMENT '实际结束时间',
          `operation_status` int DEFAULT NULL COMMENT '数据状态：0 进行中，1已完成，2强制结束',
          `device_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备id、code',
          `product_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot 产品名称',
          `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot 设备名称',
          `operation_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作对象名称',
          PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1834045751335202819 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='上下班操作控制表';
-- ----------------------------
-- 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','01836595458769387522');
INSERT INTO `sys_menu` VALUES ('01806204625679527938', '部署中心', '0', '27', '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 ('01836595458769387522', '巡检主机接入', 1806204625679527938, 0, 'server/center', 'iot/server/index', NULL, '1', '0', 'C', '0', '0', NULL, '1', 'tree', NULL, '超级管理员', '2024-09-19 10:38:17', '', NULL, '');
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 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='报警主机防区和上下班规则关系表';

-- 新增一键上下班菜单
delete from sys_menu where id='01833329881870594050';
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 (01833329881870594050, '一键上下班', 1749608698379771905, 99, '/work', NULL, NULL, 1, 0, 'M', '0', '0', '', '0', 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAMgAAADICAYAAACtWK6eAAAAAXNSR0IArs4c6QAADwtJREFUeF7tneF15LYOhTmu4OX14DqyW84mRSRbxMuWs9463EN2K/C8w/FoLI8l4pIEKRC8+ZOcGJLIC3wEQGmkU+A/VIAK7CpwojZUgArsK0BAGB1UIKEAAWF4UAECwhigAmUKMIOU6cajJlGAgEziaE6zTAECUqYbj5pEAQIyiaM5zTIFCEiZbjxqEgUIyCSO5jTLFCAgZbrxqEkUICCTOJrTLFOAgJTp9uGo/3x7/hT/58NLuPz7/BB+P72EH4vhy0N4iv/968vj5d/8ZwwFCEiBnyIMpxD+uhx6fgUi+59TeIoARXAITbZ63Q4gIKDUNyhKgRCuczqHr4QFdEZHMwKSELs1FHuXjrD8++fj3x3jgJfaUYCAbAhzFBj3QyEox3NLQFY+sAIGQTkejGUEBOSqxH//9/z3+XRtvO34520k16aepVdf50wPSHXWOL1u3y47Uov7lp2p++3fy8bXQ/i9dPeLZRcB6aZAcdY4hadzCF9rt2cv1y+BRen63YQe+ELTZpBsOBoHZQks51P4XAvpwLHbZehTApIFR2Mw7r2cNbZY2nFLuCko0wHy27fn71D93xmMGlAISTtGpgIEhcNSwKEZxdKY24Vr/zNPAwgExyk8/fzy+Lm/G9JXJCTHeWQKQKAAMwrHEhrQHOIWMht3VZrcA4IE1ijlyeWezTl8T0aAcdBVo7fDyVwDggTUKHAssYDMKRASNXRcA/LbP8/nlFKjwbGei9RTjTw3tehWOJFbQKTSavQAQjIJ+5F6QlwCIsHhpQQR5xlC+PnHo0sf14c+dgaX4kmllaeVVYJk9EyJhXE7K3eAzBgwUj/CLFIOkDtAUtnD62oq9SNe510e9viRrgCRsofnlVSCxPPc8XDPt3QFyIzZA936ZRbJhyMe4QaQmbMH+jgKs0g+JG4AmT17LK6nDvkQJG8m657umLMxe7zpntSCj6BkB6iLDJIKihlr71QW8XQPKDvaCw5wAUgqIGasu1P3RQhIHiW+AZm0pEhu+U6qSR4Wb9bDA8Lyatv1zKqlSLw/zjUgM5cTLLMIyEUBqyvl8kbFxU2931/FMouABEvlFfQK087v191dPNiHwPQMXWJZAAQCY8MdPbafU2XWjLt7MBUrQ7eA9AgA6Qal5JDWkKTG10Mfaf4j/J2AFHqpFo7bZRuWO6kxzryBkePyoQE5qoRQg+PqqVaZJNWoExAME5+AHLQqY5JvW7WAhIDUeOT12LEB2XutT0NApN+7X1W9fVRncRHy9SrtviAFSAsg68PR3hlcAtLK+WJpJbwRXjq+xbj3gG5xLXvhXT8iApKhYTJ7gFkr+YIF8BwZQ96/kdrgWjnjGsV2WEBu9x+27jG8hB/aH7vU/M13z8fRL0B20miUoM8Z57CA5ExSw1bzpqTmuTTmxnPsK0BAwOjQfPiPz0mBohswIyCgE7TvufA5KVD4g80ICOiAXUAKm11t4MBp0CxTAQICCqa94vM5KVD4g80ICOgA7QzC56RA4Q82IyCgAwgIKJQzMwICOlQbED4nBQp/sBkBAR1AQEChnJkRENChPQHhc1KgUzqYERBQZAICCuXMjICADtW+b8FH0UHhDzYjIKADCAgolDMzAgI6VBuQeFn+VgMU/0AzAgKK3xMQL5+pBqU1bUZAQPcQEFAoZ2YEBHQoAQGFcmZGQECHEhBQKGdmBAR0KAEBhXJmRkBAhzYBJP5e/Bw+fRhC4W9MwKnQLEMBAgKK1eL3G9p358Gp0CxDAQICikVAQKGcmREQ0KEEBBTKmRkBAR3a4heALfoacDo0AxUgIKBQBAQUypkZAQEdSkBAoZyZERDQoQQEFMqZGQEBHUpAQKGcmREQ0KEEBBTKmRkBAR1KQEChnJkRENChBAQUypkZAQEdSkBAoZyZERDQoQQEFMqZGQEBHdriTYgtHl8Bp0MzUAECAgplDZB3n6BbPzJ/Ck+nl/AjTkv7M3SgVK7MCAjoTiuAJD8CujWXKzCEBXT0nRkBAXU7GhDpI6LSNPg6U0mh7b8TEFC3IwHJzhp7c+IvFUFvv5kREFCyowBJNfLg0N+bEZIs2QgIKFeLd+lKu1jqcFznynILdHoIgYCAWvUGJAbx+RT+goZ3Ck83u62XQGyc5HwKn399eXw7bsMmzjn+71N4G8eyQzbLLhkBgSIwhN6AIMPaC/I41oeX8CkJWKLUytoQcL5LRkCQSAy2AEFLJKlEuwfsdm8FzEL30qHjAiU3YUZAQDdYySC5QZjaAVsDIsEEyhRyx4ee9yg7AgIqbwGQkuBLlkvXMksLjkXKknGCbuhuZhqQd49TLDswBz1GYQGQn388FvkrlUWyNgMywhPZBMg43WGmRYK3Hi26okXn9tpNORqQmlVZ7UZjjuOd3G8xB0iJM3uAcjQgpdkjxnTWrtSHzjs8nUP4ut4SjgtYNJO2oT1kEVOAlMCx9mfNKistjkcCUjuvIkBOH8G410jM9A6yiBlARLGlCF79vTagti51JCA12WOZy973EPdkRVd/CT6NsWe4Xt3UDCC5DpSU0IbkKEC05pGjLwrHDb69zzi8lmHiHXvJl0f+3QQg0ipUKpBWcEl1fOl1kKyptQKj5WvJXFosHqU+1z7OBCCpQLl3GNog3oRSqoNbBIEESEmw7gUICkgJkMi9Fu3A7XU+84CkHCYF2FrE2lQ/AyA1QOZ+DGh5EDI+M7b208tDeJIeouwFR7zO0IDECfSC5AhASlbzmgxScz0UEPR5rxpYNQEyAUjtdzJQ0a9790VNY3dAlEpDdBerNiARH6JlXq+tewQkE4BovXMKzSYl5VZvQGoD9t750i5WTfaI15Ieijydw3ckILdstLXIGYcJQDR/zopCkhsQvQEpgTjl+BQgGgFYkh1yAlVbD/Ta5gEJBaUGBEnmeVsAkjpnLsAph0vb6CMAUhIHKAQpOxOAXFL0P8/nzYFmBvJyDm1IWgCyt8mgEbBrLSVANGCUSjiNYD0ii9gBpMHdWAQSNBhbAbJAsgRQi23OpA6FC1Buj7O3+MUHIePf4tau6C+lsebA6hoQdBsYgaQlIDkOK7HNuRFbcn4pQ22dM5UNpIa/530SM4C0vBuLNJBS+h4ZEOkHU7WvJRVX/nf7tuHp55fHz6U9E7KYlUC+d4wZQJJ9iMJDbyIkQvoeGpC9/i6EoNF/wIBklEjaPWkpNLYASfQhtSsHVAYUvgqndmylzkOPSzXQGoCIi891oFKWXs8HufGIzr/GzhQgLcusKBICyV6we8wgWmAjO1i515LeOlkT9DnHmgKkdZmFNu1bK53mzcwcB2nY7gVwbtBujaVm0SntQ3IyUa1+9gBJlFlaN4vEmnmj1BoakB1NNcorUcvCPseK3uYAkVYkrdVDqpvvV1crDitdEd8FMvB7c/Q6uTqi57WitzlALmVWw2Z9cZAEYrRD3zyoBS0aPJbspP6jNEsRkISXWzfry6XF8mBVamk9cWwpuGvHIulX0+NY2RQxmUF6NOtLcKAlAgH5iJMESGn2kHYca8DLXRTsAtKhWb9BkriRFm2iowlIHiC1QWxFb7OASD2CZt0vXSvunsUPx+y9SVBzLLkr3JH2u7pl3DHfGz8BATybLH8UnLAeglRqRUjCznczakoJQAbTJlu6aSwYBARwu7SyazgiZ1drb8gzAxI1WV7FFP9b63F9PmoCACJt+WrdOIR3tXbGPDsgoCuzzAgIKFfPLCICuTVm5VIPlMW9GZ/mzXBxz15EAvLDsAlIhicxUyv3QOJoze5iraWUglazF8nOIgQEi/oMq9a/gMwYyhiAiEGrHKQSkGuBa/f7c5w1iy0BKfC0FLTaWUS6S7xMgYAUOFM4xEqDPkyJtejZsxe5ZC3hDnu00QZTP9zGO2PrX0DmKDJED7JMyGIW4RZvTrjJtpbKq+EySO9eRMwiyr2PHD7+LQhIpY+lLKLdE1hzWKV85g+3VF4NmUHELFL4E89U5Gz1Ptogmo/cDgOUFr8jytmhehC0F2kRvC2eN+oQc0NdwmK2HhKQ6HVpG5a7S0OxcRls6080lCgyLCCSoNoPMpaIy2NwBSyWV8P2IIvszCJ4AFq3tFheDQ+I2LBzG9Y6F7fxpcqrI8vloUusqK6Umls07MNE3SADlSqBI3avFumGB0TMInwcxDwmFptzV4BIWYQNu11GLGcPFz0IG3a7wY+MzHL2cAWIWGqxYUfitauN9ezhDhCp1GLD3jX+xYtZzx7uABGzCBt2MWh7GYyQPVwCImURNuy9EEhfZ4Ts4RKQOClpdWKpdSwkkn+OvO9xr4yL+yBb7pZeJWrJCceGa9+rS3BYW7zcAsJSq2/go1eTALG2cLkFBCm1jnzGBw0oT3YSHNayh9seZB1ULLXsIDZKY75WzHUGiRNlqWUDECl7WCutFtXcA8J7IzYAGTF7TFFiLeEhvQSO/Ug7kEbNHlMBwlKrHQDSmUfNHlMBgpRaFndRpOCz/veRs8d0gCCQsNTSRS61izjCgjRFk752uVhqNXjxnG7IjXO20bPHlBkEuYHIBxp1ILT6ppKc2U2XQW67Wt+ev+991vliwx9Y5cTRpq219+yWTGhaQJBSi5CUhNTrMR6yx7Ql1uJ2FJJzCF9/fXl8Kg+X+Y4ceWt37a1pM8gigtRILnbc3cIhlzS1+ljJ1gynB0QqB26isSeBCfFSXk1fYq09Lq16bNxhPpJvaR8pexCQO58TEhyCPUtP2YOAbHgZgWSEO8D1oV52htHvnN/Pmj0IISkjYeMoaVdwtPKKGSQRGkgm4c7WewG9lVcERFg7RUi4s/VOQavf+KhJkSyxBPWk37SzH3kVMLmYDLyQEBBgeZEgYanl59ESNukAEPcmUvMZ7UdsQAuk2D3Ew4OJW5NjBgGjhP3IvlAem/NltgQEBCSasdTaFsvLg4nMIBkw7Jl6DoYSeaTMOnrpyQySGRWpfmTGHS3vCwYByQRkd0tz4K3MAgnkrV0nGxcEpDA63pUWE8Ih3fvwkk0JSCEgPCx972P03oO7WIzwagX2+jEv2SMKxAxSHSZzn+DDLpazcpOAzB3farOP2SSezNvLLQiIWojwRB4VICAevco5qSlAQNSk5Ik8KkBAPHqVc1JTgICoSckTeVSAgHj0KuekpsD/AX4xW33QnsNNAAAAAElFTkSuQmCC', NULL, '超级管理员', '2024-09-10 10:21:20', '超级管理员', '2024-09-11 17:13:25', '');
delete from sys_role_menu where menu_id=01833329881870594050;
insert into sys_role_menu select  id,01833329881870594050 from sys_role where org_type in (4);

-- 新增一键上下班记录菜单
delete from sys_menu where id='01833797085164892161';
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 (01833797085164892161, '上下班记录', 1749608698379771905, 100, '/workHistory', NULL, NULL, 1, 0, 'M', '0', '0', '', '0', 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAMgAAADICAYAAACtWK6eAAAAAXNSR0IArs4c6QAAC2RJREFUeF7tnet52zoMhqlMkCU8R5NxTjtEkiHajhNnDi/hCeLz6OYqjmxTIggRxJufLU0SH/AKvElsguO/x7+Hp/AZnsJD+NHJcApPjuX4Z3oT9uEzfHT/8BD2x/92e6+6NN4M76AI4QUYFnh+AOb4a/e64FdVFHUDCGAIxespvHkCxQUgj78Pr6EJL0IhQjWOIKkeEODIyLMDUKoGBDgywjFWXTkk1QICHApwjE004bnWla4qAQEORTgqh6ROQP4cThuEiO8mm26/5Lk2EaoDhOyxYYhWONSqDxCyx3aEVJhFqgKE7LEdG+eWK8siKoAMu9i9hu3Zp1x/7Zmqteep2uMUff/6M0he/1I0bDWbnuPKpeFD7yuNlTNxQM5HOloL1gZrLmGv1Vv5Wv4aOU1l4xHKDAcrRQCxfM7p+HMnosGaILTwm0eLc7pTeOsyjMDhyqTgsAxGF5xkjruMmsokl9YInEJeBYh5MAYhyR53+egKPP49vJsZLs+ZlJBRFgNi+okyFa/CJcm4cF9eyjwgo8krRgzRgNSSNc7hsUKs5aFVxy+qeSgOq2xLdvyjAOngOIX3Otw9WEEGiXZnVYCMVkfu19wFpEpxVjxJoqOpwoLVDLEufRMxirgJSLVwLHyKVBjzi0wyudQba+GdTHIVkCqHVTPLgEvGo7Ga11Su+odkP5q4+j7LdUAsbhCticyINLum2hp+4wKOO8PtWUDcCDON4natPMNRBWugTM7N+fo00pVFm2+AuITDWhTT3zwKzIwmvgOiNbSaOz07nNJcbX17UpjP+6yWL/mHQxZeXc/cSe/U08ULO3N5uuILINmyR8bTlpf2V7skudDR6sUV5nJdfPZzhnzfOLuw4ysgktmjzxBvGmf2p8HgYvVNPfojGlQA5NvDMNMHAadZ5AyIaPbYQCwgiQjiXEW29rc0KJMJ+z9AJLJHQcc3RIHPFViV1FvCqWjpkcNoUweISDBt/BSZizURuyoJ4mxmRJ5pytb+RcVic9DBLhlACoRj1A1IMoZmYXCcfS7x/sowGuoBSRxelZBib4UBkAhDstECTKwVIsOtEZDkygrOHlqrHrGOq6JcQfPMmw9FmSzy3CQ9XY2IBSgCaBaeNb75WOIdpiYkAmIoe1ydxCvv1AqEql4V42mHDfazJIxMnrCfwltSBil97rFU5POlnkt/WFN5xY+y5ZYtefrQhH2zmjKjw6vcTqH+shRIWoBKAsT48KosN9KbXAqsTgBthzpA1i7xFroGnkto6rWpQNIiVMvIWkBqm3/YdD+9vqdA6jwEQO4pzP+bVgBATLuPzudWAEByK0z9phXYBhCWeE0HjbfOr51ndwtZq34MIN5izLS9q2J8sBhATLuezscoACAxKlHGrQIA4tb1GB6jAIDEqEQZtwoAiFvXY3iMAgASoxJl3CoAIG5dj+ExCgBIjEqUcasAgLh1PYbHKAAgMSpRxq0CAOLW9RgeowCAxKhEGbcKpLx2y1kst2Hjx3AA8eNrLF2hAICsEI2f+FEAQK74mg/BFQTBhjcIA8hFHKR+6qWgsKqxK/vQ6F7NByCTMHr8c3gPITzVGFlV2aT44UEAGSIHOGwhpPVtNQBpLwGS+Ny9rfiy31ulLAIg/S1ZDK0MIqORRQBE4Bo5g7FVRZcBRMmNKedtlLpIMzMKAIhSWDDEUhJatpn98efuWbbK77UxxJK66z23p6j/UgEA0YwJhlmaagu0pXTHDBlk3Af5fXgNTXgRcB1V5FZAaYm3NQNApjvpQJI7tNPrV4QDQGbcNZzF+sGRk/RYFq1BGYyx72SQG17sdtj521SB43+7/ZYdAJAt1aft4hUAkOJdRAe3VABAtlSftotXAECKdxEd3FIBANlSfdouXgEAKd5FdHBLBVJewea7WFt6jrZVFAAQFZlpxKoCAGLVc/RbRQEAUZGZRqwqACBWPUe/VRQAEBWZacSqAgBi1XP0W0UBAFGRmUasKgAgVj1Hv1UUABAVmWnEqgIAYtVz9FtFAQBRkZlGrCoAIFY9R79VFAAQFZlpxKoCAGLVc/RbRQEAUZGZRqwqACBWPUe/VRQAEBWZacSqAgBi1XP0W0UBAFGRmUasKgAgVj1Hv1UUABAVmWnEqgIAMuO57qPVn+EpNIGvvJcR2f0HrJvwpv0x65Qrwqv87E/KE6OMWKq8F8rXIADIJJ6AwwhcipAAyBATwGEEjrGbSpAASH8P3VM4hXdjIeK7uwCi53+yh57Wki0df+4ayfrm6kp5eFYzSX/8c2izB9et5Y424foBRFjQa9VxR7qS0MLNAIiwoDcAIYMoaS3ZDIBIqnmjLuYgSkLLNrM//tw9y1b5vTbmIO0q1u/Da2jCS26xqV9UAQARlfNOZcxDNNUWaKsJzxrHTsggg6/IIgJBq1WF0h5Iaw6ATJwKJFoRntCOIhwAMuOn4YkxzkfYG0mIZaGf7sMpfLR1HX/tXoXqjK6GDBItFQU9KgAgHr2OzdEKAEi0VBT0qACAePQ6NkcrACDRUlHQowIA4tHr2BytAIBES0VBjwoAiEevY3O0AgASLRUFPSoAIB69js3RCgBItFQU9KrA2pPe1byT7tXx2B2nAIDE6UQppwoAiFPHY3acAgASpxOlnCoAIE4dj9lxCgBInE6UcqoAgDh1PGbHKQAgcTpRyqkCAOLU8ZgdpwCAxOlEKacKAIhTx2N2nAIAEqcTpZwqACBOHY/ZcQoASJxOlHKqAIA4dTxmxykAIHE6UcqpAgDi1PGYHacAgNzQqXvl0tGfxp0b1uQEkAuPDdcg/HB88+0+NOENWPrAAJAJINwR8uVpoXLNWekZBUAGD3Ff+kyoKl9YUyIsAJJ41VaJTpXsk8Z1y5L9la4LQPpxJnelX4sspQszpQNbqr7Hv4f3cAqLF2uq+uwPgNwIJ+fDLAAhg9x+2AIIGYQMQga5pgAZhAxCBrm9WUwGYf/jRoQwSQeQlB1TqdWSQutxv1nIEGvcKGzPXZ1Cu9zLX6uA88n5GAQAcoEDE3bgmIYEgFzJF91J3s/lG0Sm089D2HNI8asHAcR0RNP53AoASG6Fqd+0AgBi2n10PrcCAJJbYeo3rYAuICEE78enTUeLw87rHncHEIchZttkALHtP3qfWQF1QILzsz2Z/Un1wgoAiLCgVFePAimHWJu1s/vQdLu1z/XIiCW1KrANIEzUa42n6uxKSQLrM0grI/OQ6oKpRoPWzj/aUVIqIAyzaoyoimxKGV61rwo0SRWQRSoKpTpNWZ09Wjk6QFJfMGKyXmdkVWBV8sO/BaTVIYmygbTjr91rBZpiQiUKJD/4h0WoHpCVX537oiWvdlYSWnWYkRzTQzyPgMi8xw0kdUSXcSuS4ZiMimQBGSoOvPJpPMRsdn+4KOllzTd4Ly0eT6t3gIgNs6atkE1sRpnBXkuC0Zk/WXiaAiIzzLoUuAn78Bk+mMQbjLyCu3yGoh+1LP5q+03TJg/3MyBZsshcLwZguv96CPuxCF/hKDgaN+jat3sl2y/TPIT2Sj15IG6MfC4ByZNFNhCYJlFglQIXU4MvgKhlkVU950cokFmBmXnzHCBkkcx+oPpCFZg5fPsNkC6L/D68hia8FGoG3UIBeQWurLrOAgIk8vpTY8EK3NiSuAoI85GCHUrX5BS4s193ExAgkfMDNRWoQMRJ9LuAMNwq0LF0KV2BCDjaRqIAAZJ0f1BDQQosOAYVDQiQFORgurJegQVwLMogY4/OF9KwDLzeSfxSX4H2iFMIb0uPNC3KIFOrur2SHjH2S/TdTYuxCqwEY6x+NSDnjNKC0h4ikz5RGSsA5VBgToFEMMQAIasQn8UoMEDR9mfpUOqaDckZ5FrF5yFYW0DjmHIxXqIjWRXoIfj39xk+cr7B+j8s+dB7kjLmUAAAAABJRU5ErkJggg==', NULL, '超级管理员', '2024-09-11 17:17:50', '超级管理员', '2024-09-11 17:20:22', '');

delete from sys_role_menu where menu_id=01833797085164892161;
insert into sys_role_menu select  id,01833797085164892161 from sys_role where org_type in (1,2,3,4);

drop table if exists iot_server_product;
CREATE TABLE `iot_server_product` (
      `id` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
      `name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
      `display_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
      `enabled` tinyint(1) NOT NULL,
      `categories` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
      PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('01e50bb0-17cd-24bf-3db9-3a141541ea12', 'FSU_Theft', 'FSU 防盗传感器', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('055e9571-52b0-06ab-c359-3a14623b2d8d', 'FSU_DoPowerCollection', 'FSU 回路通电状态', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('06f8fc23-26c8-edc1-bd8b-3a141540c0fb', 'FSU_AirConditioner', 'FSU 空调', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('0f26e066-9e07-beb3-55bd-3a147bd0d913', 'FSU_DoPowerControlBoxButton', 'FSU DO8小时控电箱按钮', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('15367b47-d383-48aa-99ea-a1c5dc573056', 'FSU_DoPowerControl', 'FSU DO8小时控电', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('19ed86f7-780d-29ec-4bc7-3a13a7097ea1', 'VGSII_Hik', '海康视频存储设备2023年入围设备', 1, '[\r\n  \"Dvs\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('1b7adc13-af87-fe22-7d49-3a141540fad9', 'FSU_Water', 'FSU 水浸传感器', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('2369b2eb-f0ae-7f68-f49c-3a0ad342f60a', 'InAnter_BM1600NTSmall', '英安特BM1600NTSmall报警主机', 1, '[\r\n  \"AlarmHost\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('2f7b0f00-43b1-07b2-dfcd-3a0bba6d4ccb', 'Hik_DS19A', 'Hik_DS19A系列报警主机', 1, '[\r\n  \"AlarmHost\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('384287b5-f556-b9cc-e84e-3a1057095e04', 'HikModule', '海康报警网络模块', 1, '[\r\n  \"AlarmHost\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('51a1f179-8fcf-ea4f-988c-3a13f9904a04', 'FSU_Gateway', 'FSU主机', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('54eb3a1c-8f7d-4b9e-9a5c-2e8f66750000', 'FSU_SmartMeter', 'FSU 智能电表', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('649e9822-b487-6b9f-865b-3a0ad341b9c0', 'FengYe_H402', '枫叶HD402报警主机', 1, '[\r\n  \"AlarmHost\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('6acce932-9e54-e3d3-5241-3a13d1d3584a', 'DetectionHost', '物联网主机产品，VIMD服务', 1, '[\r\n  \"DetectionHost\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('6acce932-9e54-e3d3-5d4a-3a13d1d3584a', 'IoTServer', 'IoT内置产品，IoT服务', 1, '[\r\n  \"IoT\",\r\n  \"IoTServer\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('6ba7b810-9dad-11d1-80b4-00c04fd430c8', 'FSU_Infrared', 'FSU 红外传感器', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('73af892b-2bee-4126-b498-7460c483509d', 'FSU_ThreePhaseACVoltage', 'FSU 三相电电压传感器', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('783bb246-6ebe-7e69-e055-3a14adfe10c9', 'VGSII_DaHua_V1', '大华视频存储设备（通用设备）', 1, '[\r\n  \"Dvs\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('7a5f5e54-17d7-4c2e-99b2-e4d6181f8b62', 'FSU_Gas', 'FSU 燃气传感器', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('85659b02-4fe7-cb85-6b00-3a14153fec6e', 'FSU_Smoke', 'FSU 烟雾传感器', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('92467897-92ef-647f-e55e-3a13b7f3ca9a', 'VGSII_DaHua', '大华视频存储设备2023年入围设备', 1, '[\r\n  \"Dvs\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('98308508-be46-8eb3-ff40-3a14153f55f9', 'FSU_TemperatureAndHumidity', 'FSU 温湿度传感器', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('a1ae8610-70d1-b856-5bdf-3a141540732d', 'FSU_Ups', 'UPS', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('abce638c-33c6-bc91-39dd-3a1415401bcd', 'FSU_DoorMagnetic', 'FSU 门磁传感器', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('bed76841-d3ee-2fd0-64ed-3a0d374167a5', 'HengTong_CKWU01C', '恒通CKWU01C报警主机', 1, '[\r\n  \"AlarmHost\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('ce00cabd-b2c2-8532-f56f-3a10556f2bfe', 'MtaOCX', '迈特安网络报警模块', 1, '[\r\n  \"AlarmHost\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('cead84cb-7d99-1b7f-b309-3a0ca885fbe5', 'CrossProcessDemo', 'CrossProcessDemo', 0, '[\r\n  \"AlarmHost\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('e9d22150-a123-c88d-568e-3a0cd6d1b434', 'HoneywellOCX_IPM', 'HoneywellOCX_IPM', 1, '[\r\n  \"AlarmHost\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('f6a73ea2-1cc7-d7ff-8018-3a0ad3408d98', 'DaHuaAlarmHost', 'DaHua报警主机', 1, '[\r\n  \"AlarmHost\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('f81d4fae-7dec-11d0-a765-00a0c91e6bf6', 'FSU_RollingShutterDoor', 'FSU 卷帘门传感器', 1, '[\r\n  \"FSU\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`) VALUES ('f9c5d636-dac4-d515-dfbe-3a0d3da7d805', 'BOSCH_CMS', 'BOSCH_CMS报警主机', 1, '[\r\n  \"AlarmHost\"\r\n]');


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 = 'sys_device'
                    AND column_name = 'asset_no') THEN
        ALTER TABLE `sys_device`
            ADD COLUMN `asset_no` varchar(125) NULL COMMENT '资产编号' AFTER `device_name`,
            MODIFY COLUMN `device_type` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备类型' AFTER `asset_type`,
            ADD COLUMN `device_status` int(2) NULL COMMENT '设备状态' AFTER `device_type`,
            ADD COLUMN `device_model` varchar(255) NULL COMMENT '设备型号' AFTER `device_brand`,
            ADD COLUMN `address` varchar(255) NULL COMMENT '安装位置' AFTER `device_model`,
            ADD COLUMN `purchasing_time` date NULL COMMENT '采购时间' AFTER `address`,
            ADD COLUMN `use_time` datetime NULL COMMENT '开始使用时间' AFTER `maintenance_term`,
            ADD COLUMN `remove_time` datetime NULL COMMENT '移除时间' AFTER `use_time`,
            ADD COLUMN `scrap_time` datetime NULL COMMENT '报废时间' AFTER `remove_time`,
            ADD COLUMN `business_device_id` bigint NULL COMMENT '业务设备id' AFTER `definition`;
    END IF;
	IF NOT EXISTS(SELECT *
					  FROM information_schema.columns
					  WHERE table_schema = DATABASE()
						AND table_name = 'iot_daily_check_data'
						AND column_name = 'score') THEN
		ALTER TABLE `iot_daily_check_data`
			ADD COLUMN `score` float NULL COMMENT '分数' AFTER `data`;
	end if;
END ??
DELIMITER ;
CALL schema_change();

delete from sys_dict_type where dict_type='asset_status';
delete from sys_dict_data where dict_type='asset_status';
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, update_by, update_time, remark) VALUES ('资产状态', 'asset_status', '0', '超级管理员', '2024-09-27 15:57:25', '', 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', 'asset_status', null, 'default', 'N', '0', '超级管理员', '2024-09-27 15:58: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 (1, '使用中', '1', 'asset_status', null, 'default', 'N', '0', '超级管理员', '2024-09-27 15:59:01', '', 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', 'asset_status', null, 'default', 'N', '0', '超级管理员', '2024-09-27 15:59:11', '', 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', 'asset_status', null, 'default', 'N', '0', '超级管理员', '2024-09-27 15:59:22', '', null, null);

alter table sys_device modify asset_type varchar(225) null comment '资产类别';

alter table sys_device modify device_type varchar(225) null comment '设备类型';


drop table if exists sys_multi_layer_dictionary;
CREATE TABLE `sys_multi_layer_dictionary` (
      `id` bigint NOT NULL AUTO_INCREMENT,
      `code` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '编号',
      `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名称',
      `parent_id` bigint DEFAULT '0' COMMENT '上级',
      `type` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '分类',
      `type_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '分类名称',
      `level` int DEFAULT '1' COMMENT '层级',
      `sort` int DEFAULT '0' COMMENT '排序',
      `status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '状态（0正常 1停用）',
      `is_system` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '系统内置字段（0否 1是）',
      `path` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '层级路径',
      `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
      `create_time` datetime(6) DEFAULT NULL COMMENT '创建时间',
      `modified_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人名称',
      `update_time` datetime(6) DEFAULT NULL COMMENT '更新时间',
      `modified_id` bigint DEFAULT NULL COMMENT '最后修改人id',
      `deleted` int DEFAULT '0' COMMENT '删除',
      PRIMARY KEY (`id`) USING BTREE,
      KEY `idx_dictionary_parentId` (`parent_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1951 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (1, '04', '海康', -1, 'BrandModelType', '品牌型号类', 1, 0, '0', '0', '1-', '', '2024-10-09 14:27:11.000000', '超级管理员', '2024-10-09 14:26:14.156000', NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (2, '05', '大华', -1, 'BrandModelType', '品牌型号类', 1, 0, '0', '0', '2-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (3, '0801', 'Hik_DS19A', 1, 'BrandModelType', '品牌型号类', 2, 0, '0', '0', '1-3-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (4, '0901', 'VGSII_DaHua', 2, 'BrandModelType', '品牌型号类', 2, 0, '0', '0', '2-4-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (6, '226', '报警控制器', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '1', '3713670431768576-6-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (1001, '22', 'UPS', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '0', '3713670431768576-1001-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (1002, '23', '机柜', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '0', '3713670431768576-1002-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (1003, '24', '显示器', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '0', '3713670431768576-1003-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (1004, '25', '配电箱', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '0', '3713670431768576-1004-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (3396885624111104, '19', '指纹验证设备', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '0', '3713670431768576-3396885624111104-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (3396885854699520, '20', '人脸验证设备', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '0', '3713670431768576-3396885854699520-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (3396886337634304, '21', '网络交换机', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '0', '3713670431768576-3396886337634304-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (3713670431768576, '4', '主机设备类', -1, 'AssetDeviceType', '资产设备类', 1, 0, '0', '0', '3713670431768576-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (3713671510851584, '14', '监控主机', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '0', '3713670431768576-3713671510851584-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (3713671711473664, '18', '摄像头', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '0', '3713670431768576-3713671711473664-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (3713671920926720, '15', '报警主机', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '0', '3713670431768576-3713671920926720-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (3713672073773056, '16', '对讲主机', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '0', '3713670431768576-3713672073773056-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (3713672266842112, '17', '门禁主机', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '0', '3713670431768576-3713672266842112-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (3713672266842132, '26', '智能分析仪', 3713670431768576, 'AssetDeviceType', '资产设备类', 2, 0, '0', '0', '3713670431768576-3713672266842132-', NULL, '2024-10-09 14:27:11.000000', NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (1849277638232182785, '500', '霍尼韦尔', -1, 'BrandModelType', '品牌型号类', 1, 0, '0', '0', '1849277638232182785-', NULL, NULL, NULL, NULL, NULL, 0);
INSERT INTO `soc`.`sys_multi_layer_dictionary` (`id`, `code`, `name`, `parent_id`, `type`, `type_name`, `level`, `sort`, `status`, `is_system`, `path`, `remark`, `create_time`, `modified_name`, `update_time`, `modified_id`, `deleted`) VALUES (1849277638232182790, '600', '域适都', -1, 'BrandModelType', '品牌型号类', 1, 0, '0', '0', '1849277638232182790-', NULL, NULL, NULL, NULL, NULL, 0);


delete from sys_dict_type where dict_type='sys_yes_no_num';
delete from sys_dict_data where dict_type='sys_yes_no_num';
INSERT INTO `sys_dict_type` ( `dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('系统是否（数字）', 'sys_yes_no_num', '0', '超级管理员', '2024-09-26 16:55:47', '', 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', 'sys_yes_no_num', NULL, 'default', 'N', '0', '超级管理员', '2024-09-26 16:56:06', '', 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', 'sys_yes_no_num', NULL, 'default', 'N', '0', '超级管理员', '2024-09-26 16:56:13', '', NULL, NULL);

DELETE FROM `sys_menu` WHERE `id`=1839945558511747073;
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 (01839945558511747073, '物联看板', 1747911340288892930, 20, '/iot/board', 'iot/board/index', NULL, 1, 0, 'C', '0', '0', 'iot:board', '1', 'monitor', NULL, '超级管理员', '2024-09-28 16:33:14', '超级管理员', '2024-09-28 16:33:55', '');

DELETE from `sys_role_menu` where menu_id=1839945558511747073;
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (115, 1839945558511747073);
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (114, 1839945558511747073);
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (116, 1839945558511747073);
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (117, 1839945558511747073);
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (118, 1839945558511747073);
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (119, 1839945558511747073);
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (120, 1839945558511747073);


DELETE FROM `sys_menu` WHERE `id`=01840267252212912129;
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 (01840267252212912129, '多层字典', 1, 0, 'multilayerdict', 'system/multilayerdict/index', NULL, 1, 0, 'C', '0', '0', '', '1', 'dict', NULL, '超级管理员', '2024-09-29 13:50:03', '超级管理员', '2024-09-29 13:50:46', '');

drop table if exists core_notice_rule;
CREATE TABLE `core_notice_rule` (
                                    `id` bigint NOT NULL,
                                    `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '规则名称',
                                    `enable` int DEFAULT '1' COMMENT '是否启用：0.否 1.是',
                                    `org_id` bigint DEFAULT NULL,
                                    `is_deleted` int NOT NULL DEFAULT '0' COMMENT '是否删除：0.否 1.是',
                                    `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                    `update_time` datetime DEFAULT NULL COMMENT '更新时间',
                                    `remark` varchar(255) 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 core_notice_rule_item;
CREATE TABLE `core_notice_rule_item` (
                                         `id` bigint NOT NULL,
                                         `rule_id` bigint NOT NULL COMMENT '通知规则id',
                                         `org_id` bigint NOT NULL COMMENT '接收消息的组织机构id',
                                         `role_id` bigint DEFAULT NULL COMMENT '角色id',
                                         `user_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
                                         `send_sms` int DEFAULT NULL COMMENT '是否发送短信：0.否 1.是',
                                         `all_role_user` int NOT NULL DEFAULT '0' COMMENT '是否选择角色下所有用户：0.否 1.是',
                                         `product_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '传感器产品名称',
                                         `is_deleted` int NOT NULL DEFAULT '0' COMMENT '是否删除：0.否 1.是',
                                         PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

drop table if exists core_notice_rule_item_user;
CREATE TABLE `core_notice_rule_item_user` (
                                              `id` bigint NOT NULL AUTO_INCREMENT,
                                              `user_id` bigint DEFAULT NULL,
                                              `rule_item_id` bigint DEFAULT NULL,
                                              `rule_id` bigint DEFAULT NULL,
                                              PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=96 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

drop table if exists websocket_notice_log;
CREATE TABLE `websocket_notice_log` (
                                        `id` bigint NOT NULL,
                                        `iot_alarm_id` bigint DEFAULT NULL,
                                        `org_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
                                        `user_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
                                        `is_do` int DEFAULT NULL COMMENT '是否处理：0.否 1.是',
                                        `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                        `deal_time` datetime DEFAULT NULL COMMENT '处理时间',
                                        `deal_user_id` bigint DEFAULT NULL COMMENT '处理人',
                                        PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

drop table if exists iot_alarm_system_field;
CREATE TABLE `iot_alarm_system_field` (
      `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
      `product_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '产品类型',
      `product_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警源类型(设备类型) 中文',
      `product_property` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '系统属性code（报警类型编码,全表唯一）',
      `product_property_display_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '系统属性名称',
      `specs` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '属性规格信息，eg:{ \r\n"0": "门已关闭", \r\n"1": "门已打开" \r\n}',
      `operators` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '该属性拥有哪些操作符eg:{ \r\n"GT": "大于", \r\n"LT": "小于" \r\n}\r\n\r\nGT(大于)、GTE(大于等于)、LT(小于)、LTE(小于等\r\n于)、EQUALS(等于)、STARTS_WITH(以什么开始)、\r\nENDS_WITH(以什么结束)、CONTAIN(包含)',
      `type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据值类型，eg：”FLOAT“、"ENUM"',
      `type_des` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据值类型中午描述，eg：”浮点型“、"枚举"',
      `unit` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '属性单位，非必填',
      `enable` int NOT NULL DEFAULT '0' COMMENT '是否启用0 是1否',
      `alarm_rule_property` int NOT NULL DEFAULT '0' COMMENT '是否告警规则属性：0 否，1 是',
      `order` int NOT NULL DEFAULT '0' COMMENT '排序值',
      PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='系统报警属性字段表';

INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Water', '水浸', 'alarm', '水浸告警', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 1, 8);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Smoke', '烟感', 'alarm', '烟雾告警', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 1, 9);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_TemperatureAndHumidity', '温湿度', 'temperature', '环境温度', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', '°C', 1, 1, 1);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_TemperatureAndHumidity', '温湿度', 'humidity', '环境湿度', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', '%RH', 1, 1, 1);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Gas', '燃气', 'alarm', '可燃气体告警', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', '', 1, 1, 10);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_DoorMagnetic', '门磁', 'alarm', '门磁开关状态', '{\"0\":\"门已打开\",\"1\":\"门已关闭\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', '', 1, 1, 3);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Infrared', '红外', 'alarm', '红外告警', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', '', 1, 1, 2);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Theft', '防盗', 'alarm', '防盗告警', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 1, 4);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_AirConditioner', '空调', 'current', '电流', '{}', '{}', 'FLOAT', '浮点型', 'A', 1, 0, 0);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_AirConditioner', '空调', 'voltage', '电压', '{}', '{}', 'FLOAT', '浮点型', 'V', 1, 0, 0);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_AirConditioner', '空调', 'returnAirTemperature', '回风温度', '{}', '{}', 'FLOAT', '浮点型', '°C', 1, 0, 0);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_AirConditioner', '空调', 'indoorAirTemperature', '室内温度', '{}', '{}', 'FLOAT', '浮点型', '°C', 1, 0, 0);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_AirConditioner', '空调', 'indoorAirTemperature', '室外温度', '{}', '{}', 'FLOAT', '浮点型', '°C', 1, 0, 0);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_AirConditioner', '空调', 'temperature', '温度', '{}', '{}', 'FLOAT', '浮点型', '°C', 1, 0, 0);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_AirConditioner', '空调', 'humidity', '湿度', '{}', '{}', 'FLOAT', '浮点型', '%RH', 1, 0, 0);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_AirConditioner', '空调', 'indoorHumidity', '室内湿度', '{}', '{}', 'FLOAT', '浮点型', '%RH', 1, 0, 0);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'upsInputPhaseVoltageUa', 'UPS输入相电压Ua', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', 'V', 1, 1, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'upsInputPhaseVoltageUb', 'UPS输入相电压Ub', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', 'V', 1, 1, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'upsInputPhaseVoltageUc', 'UPS输入相电压Uc', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', 'V', 1, 1, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'upsInputFrequency', 'UPS输入频率', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', 'Hz', 1, 1, 0);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'upsOutputPhaseVoltageUa', 'UPS输出相电压Ua', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', 'V', 1, 1, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'upsOutputPhaseVoltageUb', 'UPS输出相电压Ub', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', 'V', 1, 1, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'upsOutputPhaseVoltageUc', 'UPS输出相电压Uc', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', 'V', 1, 1, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'upsOutputFrequency', 'UPS输出频率', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', 'Hz', 1, 1, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'acInputInterruptAlarm', '交流输入中断告警', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 1, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'upsHostTemperature', 'UPS主机温度', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', '°C', 1, 1, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'bypassState', '旁路状态', '{}', '{}', 'TEXT', '文本型', NULL, 1, 0, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'upsType', 'UPS类型', '{}', '{}', 'TEXT', '文本型', NULL, 1, 0, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'upsBatteryCurrent', 'UPS电池电流', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', 'A', 1, 1, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'batteryCapacity', '电池剩余备用时间', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', 's', 1, 1, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'batteryRemainingStandbyTime', '电池容量', '{}', '{\"GT\": \"大于\",\"GTE\": \"大于等于\",\"LT\": \"小于\",\"LTE\": \"小于等于\",\"EQUALS\": \"等于\"}', 'FLOAT', '浮点型', '%', 1, 1, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_Ups', 'UPS', 'lowBatteryVoltageAlarm', '电池电压低告警', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 1, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_DoPowerCollection', '回路', 'status', '回路通电状态', '{\"0\": \"断电\",\"1\": \"通电\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 0);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_DoPowerControlBoxButton', 'DO8小时控电箱按钮', 'status', '按钮状态', '{\"0\": \"弹起\",\"1\": \"按下\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 0);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_DoPowerCollection8Hour', '8小时回路', 'status', '8小时回路通电状态', '{\"0\": \"断电\",\"1\": \"通电\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 1, 6);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('FSU_DoPowerCollection24Hour', '24小时回路', 'status', '24小时回路缺相状态', '{\"0\": \"缺相\",\"1\": \"通电\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 1, 7);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_Humidity', '湿度传感器', 'status', '湿度', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 1);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_Temperature', '温度传感器', 'status', '温度', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 2);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_Smoke', '烟感传感器', 'status', '烟感', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 3);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_Infrared', '红外传感器', 'status', '红外', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 4);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_Soaking', '水浸传感器', 'status', '水浸', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 5);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_DoorSensor', '门磁传感器', 'status', '门磁', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 6);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_Other', '其他传感器', 'status', '其他', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 7);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_Displacement', '位移传感器', 'status', '位移', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 8);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_Broken', '破碎传感器', 'status', '破碎', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 9);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_Shock', '震动传感器', 'status', '震动', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 10);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_Tamper', '防拆传感器', 'status', '防拆', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 11);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_Intrusion', '入侵传感器', 'status', '入侵', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 12);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_IMDualIdentification', '红外微波双鉴传感器', 'status', '红外微波双鉴', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 13);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_Microwave', '微波传感器', 'status', '微波', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 14);
INSERT INTO `iot_alarm_system_field` (`product_type`, `product_name`, `product_property`, `product_property_display_name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `alarm_rule_property`, `order`) VALUES ('AlarmHostInput_EmergencyButton', '紧急按钮传感器', 'status', '紧急按钮', '{\"alarm\": \"报警\",\"bypassAlarm\": \"旁路报警\",\"bypass\": \"旁路\",\"normal\": \"正常\",\"activity\": \"活动\",\"unknow\": \"未知\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, 0, 15);

-- 修改物联设备状态表字段长度
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_device_status' AND column_name = 'device_type') THEN
        ALTER TABLE `iot_device_status`
            ADD COLUMN `device_type` varchar(100) COMMENT '设备类型';
    ELSE
        ALTER TABLE `iot_device_status`
            MODIFY COLUMN `device_type` varchar(100) COMMENT '设备类型';
    END IF;
END??
DELIMITER ;
CALL schema_change();

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_device_status_log' AND column_name = 'device_type') THEN
        ALTER TABLE `iot_device_status_log`
            ADD COLUMN `device_type` varchar(100) COMMENT '设备类型';
    ELSE
        ALTER TABLE `iot_device_status_log`
            MODIFY COLUMN `device_type` varchar(100) COMMENT '设备类型';
    END IF;
END??
DELIMITER ;
CALL schema_change();


-- 新增日志管理菜单(管理员使用功能)
delete from sys_menu where id=01843936562189307906;
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 (01843936562189307906, '日志管理', 2, 99, 'logManagement', 'system/logManagement/index', NULL, 1, 0, 'C', '0', '0', NULL, '1', 'server', NULL, '超级管理员', '2024-10-09 16:48:29', '', NULL, '');
drop table if exists `sys_server`;
CREATE TABLE `sys_server` (
      `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
      `server_ip` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '服务器ip',
      `server_token` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '看门猫token',
      `server_port` int DEFAULT NULL COMMENT '看门猫端口',
      `server_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '服务器名称',
      `remark` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
      `create_by` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建者',
      `create_time` datetime DEFAULT NULL COMMENT '创建时间',
      `update_by` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新者',
      `update_time` datetime DEFAULT NULL COMMENT '更新时间',
      PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='服务器维护';

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_alarm_rule'
                              AND column_name = 'org_id') THEN
        ALTER TABLE `iot_alarm_rule`
            ADD COLUMN `org_id` bigint NOT NULL DEFAULT '1' COMMENT '机构id';
        ALTER TABLE `iot_alarm_rule`
            ADD COLUMN `iot_code` varchar(225)  DEFAULT null COMMENT 'iotToken';
        ALTER TABLE `iot_alarm_rule`
            ADD COLUMN `server_name` varchar(225)  DEFAULT null COMMENT '服务名称';
    END IF;

    IF NOT EXISTS (SELECT * FROM information_schema.columns
                   WHERE table_schema = DATABASE()
                     AND table_name = 'iot_alarm_rule_source'
                     AND column_name = 'iot_code') THEN
        ALTER TABLE `iot_alarm_rule_source`
            ADD COLUMN `iot_code` varchar(225)  DEFAULT null COMMENT '主机token';
    END IF;

    IF NOT EXISTS (SELECT * FROM information_schema.columns
                   WHERE table_schema = DATABASE()
                     AND table_name = 'iot_alarm_rule_source'
                     AND column_name = 'product_type') THEN
        ALTER TABLE `iot_alarm_rule_source`
            CHANGE COLUMN `source_type` `product_type` varchar(225) NULL DEFAULT NULL COMMENT '报警源类型：4:烟雾传感器；5:温湿度采集器；6:水浸,7:门磁:窗磁,8:卷帘门门磁；9:地磁,10:燃气报警器,50:报警防区' AFTER `rule_id`;
    END IF;


    IF NOT EXISTS (SELECT * FROM information_schema.columns
                   WHERE table_schema = DATABASE()
                     AND table_name = 'iot_alarm_rule_source'
                     AND column_name = 'device_code') THEN
        ALTER TABLE `iot_alarm_rule_source`
            ADD COLUMN `device_code` varchar(255) NULL COMMENT '设备code' AFTER `product_type`;
    END IF;

    IF EXISTS (SELECT * FROM information_schema.columns
                   WHERE table_schema = DATABASE()
                     AND table_name = 'iot_alarm_rule_express'
                     AND column_name = 'source_type') THEN
        ALTER TABLE `iot_alarm_rule_express`
            DROP COLUMN `source_type`;
    END IF;



    IF NOT EXISTS (SELECT * FROM information_schema.columns
                   WHERE table_schema = DATABASE()
                     AND table_name = 'iot_alarm_rule_express'
                     AND column_name = 'product_type') THEN
        ALTER TABLE `iot_alarm_rule_express`
            ADD COLUMN `product_type` varchar(225)  DEFAULT null COMMENT '产品类型';
    END IF;


    IF  EXISTS (SELECT * FROM information_schema.columns
                   WHERE table_schema = DATABASE()
                     AND table_name = 'iot_alarm_rule_express'
                     AND column_name = 'fieldCode') THEN
        alter table iot_alarm_rule_express
            change fieldCode product_property varchar(50) not null comment '属性code';
    END IF;



END??
DELIMITER ;
CALL schema_change();

-- 通知规则菜单
delete from sys_menu where id=01836604852290682881;
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 (01836604852290682881, '通知规则', 1, 100, 'notice', 'notice/index', NULL, 1, 0, 'C', '0', '0', NULL, '1', 'skill', NULL, '超级管理员', '2024-09-19 11:15:37', '', NULL, '');

-- 修改sys_version表path字段类型和长度
ALTER TABLE `sys_version` MODIFY COLUMN `path` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '下载地址' AFTER `note`;
ALTER TABLE `iot_alarm_defence_area`
    MODIFY COLUMN `sensor_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '传感器类型' AFTER `defence_area_name`;

ALTER TABLE `iot_alarm_data`
    MODIFY COLUMN `source_type` varchar(64) NULL DEFAULT NULL COMMENT '报警源类型(设备类型)' AFTER `device_id`;

ALTER TABLE `iot_alarm_data`
    MODIFY COLUMN `source_type_des` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '报警源类型(设备类型) 中文' AFTER `source_type`;

delete from sys_config where config_key='ALARM_LOSE_DURATION';
INSERT INTO `sys_config`(`config_name`, `config_key`, `config_value`, `config_type`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('判定为完整录像的丢失时长', 'ALARM_LOSE_DURATION', '5', 'Y', '超级管理员', '2024-10-15 10:21:10', '', NULL, NULL);

-- 刷iot_alarm_data的历史数据，source_type和field_code字段
update iot_alarm_data set source_type = 'FSU_TemperatureAndHumidity',field_code='temperature' where source_type = '4183' and field_code = '4183_1';
update iot_alarm_data set source_type = 'FSU_TemperatureAndHumidity',field_code='humidity' where source_type = '4183' and field_code = '4183_2';
update iot_alarm_data set source_type = 'FSU_Smoke',field_code='alarm' where source_type = '4182' and field_code = '4182';
update iot_alarm_data set source_type = 'FSU_Water',field_code='alarm' where source_type = '4184' and field_code = '4184';
update iot_alarm_data set source_type = 'FSU_Infrared',field_code='alarm' where source_type = '4181' and field_code = '4181';
update iot_alarm_data set source_type = 'FSU_DoorMagnetic',field_code='alarm' where source_type = '4188' and field_code = '4188';
update iot_alarm_data set source_type = 'FSU_Gas',field_code='alarm' where source_type = '41885' and field_code = '41885';
update iot_alarm_data set source_type = 'FSU_Theft',field_code='alarm' where source_type = '41881' and field_code = '41881';

-- 删除脏数据
delete from iot_device_info where deleted= 1;

-- 修改短信发送表的type字段，将4181-41885修改为对应的productType
update sms_send_record set type = 'FSU_Infrared' where type = '4181';
update sms_send_record set type = 'FSU_Smoke' where type = '4182';
update sms_send_record set type = 'FSU_TemperatureAndHumidity' where type = '4183';
update sms_send_record set type = 'FSU_Water' where type = '4184';
update sms_send_record set type = 'FSU_DoorMagnetic' where type = '4188';
update sms_send_record set type = 'FSU_Theft' where type = '41881';
update sms_send_record set type = 'FSU_Gas' where type = '41885';

-- 短信提醒类型字典表修改
delete from sys_dict_type where dict_type = 'sms_notify_type';
INSERT INTO `sys_dict_type`(`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('短信提醒类型', 'sms_notify_type', '0', '超级管理员', '2024-06-06 14:15:13', '', NULL, NULL);

delete from sys_dict_data where dict_type = 'sms_notify_type';
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, '临期提醒', '10100', 'sms_notify_type', NULL, 'default', 'N', '0', '超级管理员', '2024-06-06 14:15:50', '', 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, '逾期提醒', '10200', 'sms_notify_type', NULL, 'default', 'N', '0', '超级管理员', '2024-06-06 14:16: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 (3, '水浸告警', 'FSU_Water', 'sms_notify_type', NULL, 'default', 'N', '0', '超级管理员', '2024-06-06 14:17: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 (4, '温湿度告警', 'FSU_TemperatureAndHumidity', 'sms_notify_type', NULL, 'default', 'N', '0', '超级管理员', '2024-06-06 14:17: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 (5, '烟感告警', 'FSU_Smoke', 'sms_notify_type', NULL, 'default', 'N', '0', '超级管理员', '2024-06-06 14:18:09', '', 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 (6, '红外告警', 'FSU_Infrared', 'sms_notify_type', NULL, 'default', 'N', '0', '超级管理员', '2024-06-06 14:18:28', '', 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 (7, '燃气告警', 'FSU_Gas', 'sms_notify_type', NULL, 'default', 'N', '0', '超级管理员', '2024-06-06 14:18:53', '', 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 (8, '防盗告警', 'FSU_Theft', 'sms_notify_type', NULL, 'default', 'N', '0', '超级管理员', '2024-06-06 14:19:44', '', 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 (9, '门磁告警', 'FSU_DoorMagnetic', 'sms_notify_type', NULL, 'default', 'N', '0', '超级管理员', '2024-06-06 14:20:53', '', NULL, NULL);

-- 处理历史设备数据
-- 修改历史设备数据为使用中
update  sys_device  set device_status =1 WHERE device_status is null;
update  sys_device d INNER JOIN iot_alarm_subsystem s on d.id = s.device_id set s.source =0 WHERE s.source is null;

-- 修改报警控制器的资产分类与设备类型;
update  sys_device d INNER JOIN iot_alarm_subsystem s on d.id = s.device_id set d.asset_type='3713670431768576',device_type='6';
-- 资产类型为技防的设备修改为主机设备类
update  sys_device d   set asset_type='3713670431768576' WHERE asset_type='2';
-- 修改设备类别为硬盘录像机的设备为 监控主机
update  sys_device d   set device_type='3713671510851584' WHERE device_type='2';
-- 修改设备类别为摄像头的设备为 摄像头
update  sys_device d   set device_type='3713671711473664' WHERE device_type='0';
-- 修改历史设备的品牌信息
update  sys_device  set device_brand = '1' WHERE device_brand like '%海康%';
update  sys_device  set device_brand = '1' WHERE device_brand like '%海康威视%';
update  sys_device  set device_brand = '2' WHERE device_brand like '%大华%';
update  sys_device  set device_brand = '1849277638232182785' WHERE device_brand like '%霍尼韦尔%';
update  sys_device  set device_brand = '1849277638232182790' WHERE device_brand like '%域适都%';
-- 处理设备品牌为空字符串的
update  sys_device  set device_brand = null WHERE device_brand ='';

delete from sys_config where config_key = 'TOKEN_LARGESCREEN_EXPIRETIME';
INSERT INTO `sys_config`(config_name,config_key,config_value,config_type,create_by,create_time,update_by,update_time,remark)
VALUES ('终端token过期时间（单位:分钟）', 'TOKEN_LARGESCREEN_EXPIRETIME', '1500', 'Y', null, null, '', null, null);

-- delete from sys_config where config_key = 'TOKEN_WEB_EXPIRETIME';
-- INSERT INTO `sys_config`(config_name,config_key,config_value,config_type,create_by,create_time,update_by,update_time,remark)
-- VALUES ('web token过期时间（单位:分钟）', 'TOKEN_WEB_EXPIRETIME', '1500', 'Y', null, null, '', null, null);
--
-- delete from sys_config where config_key = 'TOKEN_APP_EXPIRETIME';
-- INSERT INTO `sys_config`(config_name,config_key,config_value,config_type,create_by,create_time,update_by,update_time,remark)
-- VALUES ('app token过期时间（单位:分钟）', 'TOKEN_APP_EXPIRETIME', '1500', 'Y', null, null, '', null, null);

-- 升级脚本执行完成