/*
 Source Server Type    : MySQL
 Source Server Version : 50721 (5.7.21-log)

 v2.0.0
*/

create database if not exists macrobox DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE macrobox;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for carbon
-- ----------------------------
DROP TABLE IF EXISTS `carbon`;
CREATE TABLE `carbon`  (
  `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `c_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名称',
  `c_type` int(2) NOT NULL COMMENT '分类，1-电能',
  `c_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '通信识别码',
  `stype` int(2) NOT NULL COMMENT '统计周期，0-日，1-月',
  `created_time` timestamp NULL DEFAULT NULL,
  `statics_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '周期性统计通道',
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '碳核算_设备表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of carbon
-- ----------------------------

-- ----------------------------
-- Table structure for carbon_ratio
-- ----------------------------
DROP TABLE IF EXISTS `carbon_ratio`;
CREATE TABLE `carbon_ratio`  (
  `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `carbon_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `created_time` timestamp NULL DEFAULT NULL,
  `target` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '指标名称',
  `ratio` decimal(10, 3) NOT NULL COMMENT '系数',
  `remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '指标中文名称',
  `times` decimal(10, 3) NULL DEFAULT NULL COMMENT '倍数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '碳核算_各设备系数表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of carbon_ratio
-- ----------------------------

-- ----------------------------
-- Table structure for carbon_ratio_value
-- ----------------------------
DROP TABLE IF EXISTS `carbon_ratio_value`;
CREATE TABLE `carbon_ratio_value`  (
  `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `carbon_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `created_time` timestamp NULL DEFAULT NULL,
  `sdate` date NULL DEFAULT NULL COMMENT '统计日期',
  `values` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '统计值',
  `target` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '指标名称',
  `remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '指标中文名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '碳计算指标值表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of carbon_ratio_value
-- ----------------------------

-- ----------------------------
-- Table structure for channel
-- ----------------------------
DROP TABLE IF EXISTS `channel`;
CREATE TABLE `channel`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `device_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备id',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '通道名称',
  `channel_code` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通道号CH',
  `history_data` int(2) NULL DEFAULT 1 COMMENT '0-历史数据不输出，1输出',
  `alarm_data` int(2) NULL DEFAULT 1 COMMENT '0-报警数据不输出，1输出',
  `data_type` int(2) NULL DEFAULT NULL COMMENT '0-模拟量，1-开关量',
  `yuansu` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '元素',
  `xishu` decimal(12, 6) NULL DEFAULT 1.000000 COMMENT '系数',
  `unit` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '单位',
  `min` decimal(11, 2) NULL DEFAULT NULL COMMENT '阈值下限',
  `max` decimal(11, 2) NULL DEFAULT NULL COMMENT '阈值上限',
  `norvalue` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '开关量常态值',
  `created_time` timestamp NULL DEFAULT NULL,
  `translate` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '转义值(v1|t1,v2|t2...)',
  `virtual_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '虚拟通道id，有值即为虚拟通道',
  `def_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典通道id，有值即为字典通道',
  `trap_channel_code` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '协议中trap的t开头通道号',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `device-c`(`device_id`, `channel_code`) USING BTREE,
  INDEX `device`(`device_id`) USING BTREE,
  INDEX `vid`(`virtual_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '设备通道' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of channel
-- ----------------------------

-- ----------------------------
-- Table structure for channel_def
-- ----------------------------
DROP TABLE IF EXISTS `channel_def`;
CREATE TABLE `channel_def`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `channel_code` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通道号',
  `channel_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '通道名',
  `created_time` timestamp NULL DEFAULT NULL,
  `remark` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `ctype` int(2) NULL DEFAULT 0 COMMENT '0-单通道转义，1-双通道转义',
  `ch1` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主通道号',
  `ch2` varchar(5) 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;

-- ----------------------------
-- Records of channel_def
-- ----------------------------

-- ----------------------------
-- Table structure for channel_defvalue
-- ----------------------------
DROP TABLE IF EXISTS `channel_defvalue`;
CREATE TABLE `channel_defvalue`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `def_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '字典id',
  `chvalue1` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通道1值，多个|隔开',
  `chvalue2` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '通道2值，多个|隔开',
  `created_time` timestamp NULL DEFAULT NULL,
  `tvalue` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '转义值',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `def`(`def_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '字典转义值' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of channel_defvalue
-- ----------------------------

-- ----------------------------
-- Table structure for current_data
-- ----------------------------
DROP TABLE IF EXISTS `current_data`;
CREATE TABLE `current_data`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `channel_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '通道id',
  `spot_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '站点id',
  `values` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '当前值（数值）',
  `show_value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '当前值（显示值，带单位）',
  `created_time` timestamp NULL DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `unit` varchar(5) 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 '设备id，通信识别码',
  `channel_code` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '通道号CH',
  `device_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备id',
  `alarm` int(255) NULL DEFAULT 0 COMMENT '0-不报警，1-报警',
  `data_type` int(2) NULL DEFAULT NULL COMMENT '0-模拟量，1-开关量',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `device-c`(`channel_code`, `device_id`) USING BTREE,
  UNIQUE INDEX `devicec-c`(`device_code`, `channel_code`) USING BTREE,
  INDEX `devicecode`(`device_code`) USING BTREE,
  INDEX `deviceid`(`device_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of current_data
-- ----------------------------

-- ----------------------------
-- Table structure for device
-- ----------------------------
DROP TABLE IF EXISTS `device`;
CREATE TABLE `device`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备名',
  `device_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备id，通信识别码',
  `out_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '对外输出时的通信识别码，不填采用device_code',
  `flag` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '标识',
  `spot_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '所属站点',
  `protocol_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '协议类型',
  `virtual_type` int(2) NOT NULL DEFAULT 0 COMMENT '0-实物设备，1-虚拟设备(虚拟设备没有协议类型)',
  `send_type` int(2) NULL DEFAULT 0 COMMENT '0-上传所有通道数据，1-上传选择通道的数据',
  `channel_timeout` int(2) NULL DEFAULT 0 COMMENT '0-超时不报警,1-报警',
  `created_time` timestamp NULL DEFAULT NULL,
  `timeout` int(4) NULL DEFAULT 1 COMMENT '超时时间/min',
  `remark` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `rstatus` int(2) NULL DEFAULT NULL COMMENT '0-超时，1正常',
  `sendtrap` int(2) NULL DEFAULT 0 COMMENT '0-不发送trap，1-发送(针对snmp协议设备)',
  `device_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备数据类型，ddr等',
  `nc_tcp` int(2) NULL DEFAULT NULL COMMENT '端口侦测类型，1-tcp，2-udp',
  `topic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'mqtt，主题（订阅）',
  `thirdset_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '第三方配置id',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `code`(`device_code`) USING BTREE,
  INDEX `spotid`(`spot_id`) USING BTREE,
  INDEX `protocal`(`protocol_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '设备表' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of device
-- ----------------------------

-- ----------------------------
-- Table structure for mqtt_def
-- ----------------------------
DROP TABLE IF EXISTS `mqtt_def`;
CREATE TABLE `mqtt_def`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `mqtt_field` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'mqtt接收数据字段名',
  `channel_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '通道号',
  `channel_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '通道名称',
  `created_time` timestamp NULL DEFAULT NULL,
  `channel_unit` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '单位',
  `mtype` int(2) NULL DEFAULT NULL COMMENT 'mqtt数据类型，1-pop节点数据',
  `def_value` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '默认值',
  `use_def` int(2) NULL DEFAULT NULL COMMENT '1-启动默认值，0-不启动默认值',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `channel`(`mqtt_field`, `channel_code`, `mtype`) USING BTREE COMMENT 'mqtt字段名，通道号唯一'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '定义mqtt接收的数据通道格式' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of mqtt_def
-- ----------------------------
INSERT INTO `mqtt_def` VALUES ('09ab22cf-2c5e-4a0f-b9cc-bd155ef23739', 'cpu_consume', '005', 'CPU使用率', '2023-06-09 15:54:32', '%', 4, '', 0);
INSERT INTO `mqtt_def` VALUES ('0e32ea2b-bc4e-4562-89e7-26966a380bbb', 'data_trand', '014', '已中继数据包', '2022-03-01 11:03:04', '', 1, '', 0);
INSERT INTO `mqtt_def` VALUES ('0f18d70a-3d05-407a-8b9b-9a086a5d57f0', 'sdlan_e2eupload', '024', 'SDLAN直连上行数据包总计', '2023-06-09 16:11:05', NULL, 4, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('135624ae-663e-4906-ba24-d19a7c36c54a', 'sdlan_relaydownload', '023', 'SDLAN中继下行数据包总计', '2023-06-09 16:10:44', NULL, 4, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('1512a25b-9867-431b-bd87-263c52b7a461', 'model', '002', '设备信息', '2023-06-09 15:52:42', NULL, 4, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('18d9909a-07a2-47df-bd89-349570661b34', 'tunnel_delay', '012', '隧道时延', '2023-06-09 16:02:40', 'ms', 4, '', 0);
INSERT INTO `mqtt_def` VALUES ('22b75e95-0cd9-4010-bd9e-168a4f844745', 'sdlan_relayupload', '022', 'SDLAN中继上行数据包总计', '2023-06-09 16:10:25', NULL, 4, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('242d7218-07a0-43f0-9b16-c7b5998044a5', 'sdlan_stdev', '017', 'SDLAN抖动', '2023-06-09 16:08:13', 'ms', 4, '', 0);
INSERT INTO `mqtt_def` VALUES ('3248168b-3bda-4e7c-8a41-f22a7d5a7533', 'cpu_use', '003', 'cpu消耗', '2022-03-01 11:00:36', '%', 1, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('339da86c-d0d1-41f7-97bb-c279bed3aac8', 'rez_last_time', '016', '最新设备认证时间', '2022-03-01 11:03:44', '', 1, '', 0);
INSERT INTO `mqtt_def` VALUES ('3a10251c-2a7c-4710-8efe-cd3fd5197759', 'sdlan_down', '010', 'SDLAN上行实时流量', '2023-06-09 15:58:13', 'Mb/s', 4, '', 0);
INSERT INTO `mqtt_def` VALUES ('4ae3c12e-05a7-11ee-876e-000c299cb4b3', 'pop_num', '018', '当前POP节点数', '2023-06-08 10:52:36', '', 1, '', 0);
INSERT INTO `mqtt_def` VALUES ('58207ee1-df36-46bb-9f15-7a6facebf67f', 'sdlan_pop', '015', 'SDLAN POP', '2023-06-09 16:06:11', NULL, 4, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('597178de-5cbc-4fe4-9cab-b0563b51adde', 'ip', '005', '出口公网ip', '2022-03-01 11:01:07', NULL, 1, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('5991116d-d9b2-4c07-9557-aa71efccbc82', 'service_time', '009', '服务启动时间', '2022-03-01 11:02:04', NULL, 1, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('5a204518-d072-4910-9193-057827a253b4', 'connect_error', '017', '连接错误', '2022-03-01 11:03:57', '', 1, '', 0);
INSERT INTO `mqtt_def` VALUES ('5b8353dd-457d-4e93-8120-3b4e722df39e', 'public_ip', '001', '出口公网IP', '2023-06-09 15:50:38', NULL, 4, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('5cb7c96c-94f6-46be-bcf0-6bde3d30d79d', 'rez_errornum', '013', '认证失败次数', '2022-03-01 11:02:49', NULL, 1, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('5d82678b-8454-480b-b702-b94197e81e77', 'start_time', '008', '累计时间', '2022-03-01 11:01:51', '', 1, '', 0);
INSERT INTO `mqtt_def` VALUES ('6076efd0-79c2-4a27-aeab-34acaa201582', 'sdlan_e2edownloadl', '025', 'SDLAN直连下行数据包总计', '2023-06-09 16:11:29', NULL, 4, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('665a48eb-8bb9-466f-ba18-f9a78f4af11d', 'sdlan_up', '009', 'SDLAN下行实时流量', '2023-06-09 15:57:21', 'Mb/s', 4, '', 0);
INSERT INTO `mqtt_def` VALUES ('72ff9f60-6ec2-405a-95bc-5cd1ddd2f39f', 'sdlan_downloadall', '021', 'SDLAN下行数据包总计', '2023-06-09 16:10:04', NULL, 4, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('75a88ae6-270f-403f-ba1e-2c3befee4ad2', 'cpu_temp', '004', 'CPU温度', '2023-06-09 15:53:31', '℃', 4, '', 0);
INSERT INTO `mqtt_def` VALUES ('7975cfd3-e8b7-450a-9035-b212fca6d68d', 'netgroup_num', '011', '注册用户网络数', '2022-03-01 11:02:20', '', 1, '', 0);
INSERT INTO `mqtt_def` VALUES ('79da7b27-7c37-4e68-85d5-074e649dd979', 'sdlan_ip', '014', 'SDLAN虚拟IP', '2023-06-09 16:05:47', NULL, 4, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('7af38678-d40c-4a5b-9bd6-d0ca36649140', 'state', '001', 'edge状态', '2023-12-26 14:26:22', NULL, 5, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('8790bb8a-c830-48a6-8267-50b485c77532', 'memory_consume', '006', '内存使用率', '2023-06-09 15:55:00', '%', 4, '', 0);
INSERT INTO `mqtt_def` VALUES ('8a89844d-93c0-497e-8d46-8495cb8f416d', 'sdlan_heartbeat', '018', '心跳等待', '2023-06-09 16:08:43', NULL, 4, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('8c595862-24e8-4fae-be4f-ef1703b223a2', 'sdlan_uploadall', '020', 'SDLAN上行数据包总计', '2023-06-09 16:09:44', NULL, 4, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('8f70ea55-1ef4-459d-ab7a-5b8a8fa97163', 'dw_l', '007', '下行流量', '2022-03-01 11:01:39', 'Mb/s', 1, '', 0);
INSERT INTO `mqtt_def` VALUES ('91663261-3658-4511-890b-e9ebc892ae69', 'tunnel_stdev', '013', '隧道抖动', '2023-06-09 16:04:12', 'ms', 4, '', 0);
INSERT INTO `mqtt_def` VALUES ('a5180b5e-8a7c-4a94-8982-a9e1e30d7af6', 'start_time', '003', '累计时间', '2023-06-09 15:53:08', '', 4, '', 0);
INSERT INTO `mqtt_def` VALUES ('ac049e0d-95f7-4e72-9ae4-f737d51af9c8', 'rezheng_num', '012', '上云设备认证次数', '2022-03-01 11:02:32', '', 1, '', 0);
INSERT INTO `mqtt_def` VALUES ('b6e0f86b-c171-4294-8aba-e636c4256363', 'sdlan_laste2e', '019', '最近连接时间', '2023-06-09 16:09:18', NULL, 4, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('c2e3c5f1-412f-4401-9263-ddd0f01333e3', 'ram_use', '004', '内存消耗', '2022-03-01 11:00:52', '%', 1, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('c3e349a0-18e9-4f85-9412-8aea0431ea90', 'last_data_time', '015', '最新数据中继时间', '2022-03-01 11:03:28', '', 1, '', 0);
INSERT INTO `mqtt_def` VALUES ('ca486433-7ece-4bbb-b714-7c0704785e85', 'physics_up', '007', '设备下行实时流量', '2023-06-09 15:56:18', 'Mb/s', 4, '', 0);
INSERT INTO `mqtt_def` VALUES ('d2b8af53-e698-4717-956c-5f4cb854ed5d', 'up_l', '006', '上行流量', '2022-03-01 11:01:23', 'Mb/s', 1, '', 0);
INSERT INTO `mqtt_def` VALUES ('d5b9740f-81a0-4eca-b58f-637484e8389a', 'sdlan_delay', '016', 'SDLAN时延', '2023-06-09 16:07:16', 'ms', 4, '', 0);
INSERT INTO `mqtt_def` VALUES ('e0db9f24-11ac-44cd-bce0-d14ecccb542d', 'device_num', '010', '当前上云设备数', '2022-03-01 15:17:22', '', 1, '', 0);
INSERT INTO `mqtt_def` VALUES ('e3262ed0-39a1-4655-865a-ee70cedc3355', 'state', '001', 'pop状态', '2023-06-08 16:14:26', '', 3, '1', 0);
INSERT INTO `mqtt_def` VALUES ('eb90f728-82a7-4ddc-8bfe-9c87b40a120d', 'physics_down', '008', '设备上行实时流量', '2023-06-09 15:56:44', 'Mb/s', 4, '', 0);
INSERT INTO `mqtt_def` VALUES ('ecdc19d1-8b5e-4ea4-8a80-c6a80ff75284', 'tunnel_pop', '011', '隧道POP', '2023-06-09 15:58:46', NULL, 4, NULL, 0);
INSERT INTO `mqtt_def` VALUES ('fd119712-9868-11ec-9129-1c1b0d276864', 'id', '001', 'id', '2022-02-28 15:35:23', '', 1, '', 0);
INSERT INTO `mqtt_def` VALUES ('fd177506-9868-11ec-9129-1c1b0d276864', 'pop_ip', '002', 'popIP', '2022-02-28 15:35:23', '', 1, '', 0);

-- ----------------------------
-- Table structure for net
-- ----------------------------
DROP TABLE IF EXISTS `net`;
CREATE TABLE `net`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '网关名称',
  `nettype` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '网关类型',
  `created_time` timestamp NULL DEFAULT NULL,
  `netid` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '网关id',
  `remark` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `id`(`netid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '网关表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of net
-- ----------------------------
INSERT INTO `net` VALUES ('b5228965-2d18-4bb8-b465-144857f955b9', '捷宸', '捷宸', '2023-06-15 11:24:15', NULL, 'test');

-- ----------------------------
-- Table structure for protocol
-- ----------------------------
DROP TABLE IF EXISTS `protocol`;
CREATE TABLE `protocol`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '协议名,mib名',
  `net_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '所属网关，捷宸设备id',
  `snmpv` int(4) NULL DEFAULT NULL COMMENT 'snmp协议版本',
  `stype` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '协议类型，snmp/捷宸等',
  `created_time` timestamp NULL DEFAULT NULL,
  `remark` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `public_net` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '获取公网IP文件路径',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '协议库' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of protocol
-- ----------------------------
INSERT INTO `protocol` VALUES ('a4c6a94f-40e2-4991-aa4a-c6aabe366180', 'pop', NULL, NULL, 'MQTT', '2023-06-08 14:21:32', NULL, NULL);
INSERT INTO `protocol` VALUES ('b734a31a-3cef-40cd-9aad-3df04ce36522', 'edge状态', '', NULL, 'MQTT', '2023-12-26 14:27:01', '', '');
INSERT INTO `protocol` VALUES ('b88e9b2f-2710-4e41-b21a-bdac5391a7da', 'pop状态', '', NULL, 'MQTT', '2023-06-08 15:00:47', '', '');
INSERT INTO `protocol` VALUES ('e7d69620-1919-4f02-8502-e04af6f674a7', 'CPE', NULL, NULL, 'MQTT', '2023-06-12 14:05:54', NULL, NULL);

-- ----------------------------
-- Table structure for protocol_c
-- ----------------------------
DROP TABLE IF EXISTS `protocol_c`;
CREATE TABLE `protocol_c`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `protocol_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '协议名',
  `mid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'oid或点表id',
  `channel_code` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通道号',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '监控名称',
  `created_time` timestamp NULL DEFAULT NULL,
  `unit` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '单位',
  `trap` int(2) NULL DEFAULT NULL COMMENT '0-数据list,1-trap(告警) list',
  `protocol_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '协议id',
  `data_type` int(2) NULL DEFAULT NULL COMMENT '0-模拟量，1-开关量',
  `translate` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '转义值(v1|t1,v2|t2...)',
  `third_key` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '第三方协议key值',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `pid-code`(`channel_code`, `protocol_id`) USING BTREE,
  UNIQUE INDEX `pid-mid`(`mid`, `protocol_id`, `third_key`) USING BTREE,
  INDEX `protocol`(`protocol_name`) USING BTREE,
  INDEX `protocol_id`(`protocol_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '协议通道' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of protocol_c
-- ----------------------------
INSERT INTO `protocol_c` VALUES ('00f4a3b2-a14d-417c-bed4-9814f9740ce1', 'CPE', '5969bfd6-90bf-40d3-8f77-4409796f7f04', '025', 'SDLAN直连下行数据包总计', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('04e4d5ae-aa80-49a4-ae22-4c4cbcb3634a', 'CPE', 'e9e6b56c-d8f2-47b3-b552-44364e4836fb', '015', 'SDLAN POP', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 1, '', '');
INSERT INTO `protocol_c` VALUES ('0ff646fb-fb55-44c4-822c-b025015455b0', 'pop', '4b6c35a0-bf2a-47a1-8178-728da35e6852', '004', '内存消耗', '2023-06-12 12:56:23', '%', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 0, '', '');
INSERT INTO `protocol_c` VALUES ('1a166603-ba7d-471c-9b3a-42668507a821', 'CPE', '3048dd38-ac23-4023-9d7d-fbb3402f6a91', '013', '隧道抖动', '2023-06-12 14:26:20', 'ms', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('244efc1b-9c86-4b37-bdba-ed2d7d02a17b', 'CPE', '76d26b13-6721-4044-a403-ae4c44fa71be', '011', '隧道POP', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 1, '', '');
INSERT INTO `protocol_c` VALUES ('2fe419b6-bae7-4f2d-9e56-1eadbddf2804', 'CPE', 'b382630a-dff6-46db-8c07-eb4c410b77e5', '017', 'SDLAN抖动', '2023-06-12 14:26:20', 'ms', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('390c7470-44e6-4e8d-b76a-02bf2b9b9b31', 'CPE', 'fe608697-322d-4084-b4fa-967d62d90038', '019', '最近连接时间', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 1, '', '');
INSERT INTO `protocol_c` VALUES ('3b3bfbb4-14d9-4592-be99-c05bd2fe0930', 'CPE', '42c18c3d-eeac-4f72-896e-e73bafe0fd17', '007', '设备下行实时流量', '2023-06-12 14:26:20', 'Mb/s', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('42a98bc0-8d12-4abf-8f5b-d516c670b02a', 'pop', 'a0f31f35-0a30-4e53-b25c-c3e582b511d8', '008', '累计时间', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 1, '', '');
INSERT INTO `protocol_c` VALUES ('42e3df92-1884-4316-92e2-e40b3e5c4c0b', 'CPE', '9b6d49f1-328b-4fcf-8c34-da66b6698734', '009', 'SDLAN下行实时流量', '2023-06-12 14:26:20', 'Mb/s', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('4d2eddf1-698e-4a2c-9bf1-0a0df63eab20', 'pop', 'ca464a1a-7b46-42d2-be3b-1228f0e26257', '013', '认证失败次数', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 0, '', '');
INSERT INTO `protocol_c` VALUES ('5abc10e5-266c-4e76-9a61-2469e90fcd1c', 'edge', '5d2619be-200b-4d9b-bfca-288e69863ec5', '001', 'edge状态', '2023-12-26 14:33:29', '', 0, 'b734a31a-3cef-40cd-9aad-3df04ce36522', 1, '', '');
INSERT INTO `protocol_c` VALUES ('5c79a524-b4d1-49f9-ae8b-aa24783b9c85', 'pop', '493b1c1a-62f5-49d6-b02a-b7112788dcc0', '001', 'id', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 1, '', '');
INSERT INTO `protocol_c` VALUES ('6335d22a-52f0-4ab4-89b5-f4b32bd091c0', 'pop', '2c81eae9-1151-4a70-aec3-1ed99dfed79d', '010', '当前上云设备数', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 0, '', '');
INSERT INTO `protocol_c` VALUES ('672fc807-c133-4ec5-82b5-a1a5c9268d52', 'CPE', 'ab5ee037-b595-41d5-976e-32cf0db9a919', '014', 'SDLAN虚拟IP', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 1, '', '');
INSERT INTO `protocol_c` VALUES ('6800679b-2bd6-40a8-be5e-0cb5397b8be9', 'pop', '23f8a725-2e9f-4829-acda-efddbd0afef4', '018', '当前POP节点数', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 0, '', '');
INSERT INTO `protocol_c` VALUES ('6c2f0c83-9df7-4237-9f2b-bd6f01c2d914', 'pop', '71646d5d-6a06-473e-8d29-52dbcd5c3472', '002', 'popIP', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 1, '', '');
INSERT INTO `protocol_c` VALUES ('7a934df8-9abb-44bc-b71a-5fc4404c5970', 'pop', '3238ae77-ecf0-4b83-96e9-b644d506aa79', '016', '最新设备认证时间', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 1, '', '');
INSERT INTO `protocol_c` VALUES ('80e02cf4-184f-4e04-99d9-c30e8f805cc5', 'CPE', '0de43829-e267-4024-b31c-71ddcfcb1508', '005', 'CPU使用率', '2023-06-12 14:26:20', '%', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('8a957bfb-5234-4cd9-ab0f-dc71fc8a0c3e', 'CPE', 'fd8fe158-ebfe-43d2-bedb-ec564af0d9ac', '021', 'SDLAN下行数据包总计', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('8b05cf68-8902-4543-b6d5-34e548753ffb', 'pop', 'b484dcba-94f4-4be8-a955-0ac160d3e9b4', '009', '服务启动时间', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 1, '', '');
INSERT INTO `protocol_c` VALUES ('8f028d82-e7b1-4627-a569-17d8d0fc4b69', 'CPE', '6248e42f-31b5-4a3d-8883-a7addf64c84f', '001', '出口公网IP', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 1, '', '');
INSERT INTO `protocol_c` VALUES ('927d1f1b-eeec-48e2-827d-83feb894e19f', 'pop', 'a8f8da99-79b0-45a8-93d5-209bca75cb84', '017', '连接错误', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 0, '', '');
INSERT INTO `protocol_c` VALUES ('959b4c99-78db-46e9-87bf-8d7733efa8df', 'CPE', 'cfe353cd-2b0b-4727-8fa2-1ebf1e0201c6', '016', 'SDLAN时延', '2023-06-12 14:26:20', 'ms', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('9796b446-2186-40e6-9604-7fdc1042bf49', 'CPE', 'e25c261b-6a64-49c3-adc8-bb220c9fb476', '012', '隧道时延', '2023-06-12 14:26:20', 'ms', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('a6206744-feb6-49fc-afbd-b030d88809d8', 'CPE', '7085cda6-6e97-4aee-a2ea-35da1bd72b6d', '010', 'SDLAN上行实时流量', '2023-06-12 14:26:20', 'Mb/s', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('b24f70c6-56d1-4369-b224-1016744741e9', 'pop', '6e058349-2dde-4e34-a7d5-d5c31ec2f4a9', '003', 'cpu消耗', '2023-06-12 12:56:23', '%', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 0, '', '');
INSERT INTO `protocol_c` VALUES ('b3d01ea4-9e9d-4746-8cfb-b759555f27bf', 'pop', '162cbb9a-fc33-4160-a548-61c9558d29fb', '005', '出口公网ip', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 1, '', '');
INSERT INTO `protocol_c` VALUES ('b4ad9400-b8d2-457e-965b-ba3ca816b2a4', 'CPE', '1df97b1a-a3dc-43aa-a2d2-691c6dcb3ab9', '023', 'SDLAN中继下行数据包总计', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('b4bbfb7b-ed6f-4ccc-8a93-f657ae9d472f', 'CPE', '2afad154-6124-4d5c-97a7-12bdcee045fe', '002', '设备信息', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 1, '', '');
INSERT INTO `protocol_c` VALUES ('b5e3f071-ee35-4adc-ac64-70ac874bc334', 'pop', '1aafbe15-d863-4a4f-8c07-0c43b1f17a53', '015', '最新数据中继时间', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 1, '', '');
INSERT INTO `protocol_c` VALUES ('b953bb7a-a128-4044-83b6-b2b89c462f93', 'CPE', '0139534d-22e1-460f-959e-1dfe0a75994f', '018', '心跳等待', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 1, '', '');
INSERT INTO `protocol_c` VALUES ('bdae7da3-8d05-4fe7-a5f0-7d2b444b6dd4', 'CPE', 'a4a59b66-4dd1-421f-b8fb-141cc6a1fc27', '024', 'SDLAN直连上行数据包总计', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('c4fd09e5-189b-443c-8bb6-6941267eb92a', 'CPE', '026b5409-7687-41db-9949-23f3019610ae', '003', '开机时间', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 1, '', '');
INSERT INTO `protocol_c` VALUES ('c54c061e-e361-4432-a01b-b867dd22d894', 'pop', '66ec30d1-cace-4197-98a5-f4c3a78da24b', '014', '已中继数据包', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 0, '', '');
INSERT INTO `protocol_c` VALUES ('de57622c-73e4-421c-9530-e471a1daedff', 'CPE', '28201488-05bd-4abe-ba35-aa16365a5e19', '008', '设备上行实时流量', '2023-06-12 14:26:20', 'Mb/s', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('df68e055-1426-4c16-9b96-f351ac9e005c', 'pop', '9212a14e-f92d-44a7-925d-fe5f57c0fe32', '011', '注册用户网络数', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 0, '', '');
INSERT INTO `protocol_c` VALUES ('e4de019a-75e6-461b-889d-36a5e80508ad', 'pop', '4bb583cc-caa9-4f33-8e52-8574384c232e', '012', '上云设备认证次数', '2023-06-12 12:56:23', '', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 0, '', '');
INSERT INTO `protocol_c` VALUES ('e5cc3310-f042-40a6-84f0-74777c50a1cf', 'CPE', '79dc76cc-e908-4198-a5be-a3ca52696b9c', '020', 'SDLAN上行数据包总计', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('ee5fb621-5a20-4e12-8ae6-901815434413', 'CPE', '60acca36-e3b9-490d-87a5-5a90e5f3f93a', '022', 'SDLAN中继上行数据包总计', '2023-06-12 14:26:20', '', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('ee83290a-6639-4baf-a3d0-47b81114f7bd', 'pop', 'a80e3ed3-db37-44e7-95ee-c7f625210f7e', '006', '上行流量', '2023-06-12 12:56:23', 'Mb/s', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 0, '', '');
INSERT INTO `protocol_c` VALUES ('ef641cd0-2c06-412d-86cd-617c24fb2478', 'pop', '5adcbf8b-f25f-4405-a238-3bde3c376683', '007', '下行流量', '2023-06-12 12:56:23', 'Mb/s', 0, 'a4c6a94f-40e2-4991-aa4a-c6aabe366180', 0, '', '');
INSERT INTO `protocol_c` VALUES ('fa81a759-8b06-477b-a97d-74a248e9dd7c', 'pop状态', 'ba0155cf-981f-449e-aeaa-34dc817b06e0', '001', 'pop状态', '2023-06-08 16:58:06', '', 0, 'b88e9b2f-2710-4e41-b21a-bdac5391a7da', 1, '', '');
INSERT INTO `protocol_c` VALUES ('fc7ba0d7-c9fe-4e7b-b90c-e9092085ba0d', 'CPE', 'f5fe604b-f596-4045-9292-13d1aba8f682', '004', 'CPU温度', '2023-06-12 14:26:20', '℃', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');
INSERT INTO `protocol_c` VALUES ('fd22516e-c5e2-4ad9-a09c-33cc055f7c20', 'CPE', 'b2371754-9ffa-4d2b-9d30-a4c8e0ed06b7', '006', '内存使用率', '2023-06-12 14:26:20', '%', 0, 'e7d69620-1919-4f02-8502-e04af6f674a7', 0, '', '');

-- ----------------------------
-- Table structure for reg
-- ----------------------------
DROP TABLE IF EXISTS `reg`;
CREATE TABLE `reg`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `keys` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '机器码',
  `created_time` timestamp NULL DEFAULT NULL,
  `regcode` varchar(255) 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 = COMPACT;

-- ----------------------------
-- Records of reg
-- ----------------------------
INSERT INTO `reg` VALUES ('b5d0e6a5-7f74-432d-bcc5-3c3e8de309ef', '3gbhc+tQD6LLY7+xX0f04kmeabx0OiN+UO99ZxGa1xE=', '2024-03-06 12:32:10', '4g2DLr2Z6hqKFkyvAlWitu4QbAM3Y9baDiunlBcGLTD+R5GR6KQqJaCtdruYiXn6I0OtOw0hDu7A5/e9nTIWftkup9AVgL+ujlca/QMB8nqDrJS5R0FckOQlmUpup8FATVTKztXlV5EtilUJ56VjcVj6gJDjp2jK2/qBa2+Z1So=');
INSERT INTO `reg` VALUES ('f08f6dc7-33f7-41a7-9c61-3e965810ef85', '3gbhc+tQD6LLY7+xX0f04kmeabx0OiN+1RoZHZQfNKA=', '2023-06-08 10:55:01', 'NGQGkQXuUnALrezAPsggxYCEL1I4o4Xpzj8QSBSCeoi4JRbiVz1HIakNF0RZ0jbOKByUOZVWrQFvcVyZpFMxPpmg8tkWd5ODtCpH+xHyXCy5eWgSd//1qm1ZayShJU2JRKvt7g1XMCo6kflCSpzoenXnP5y+yfwqd/2UpNOCnCI=');

-- ----------------------------
-- Table structure for sendto
-- ----------------------------
DROP TABLE IF EXISTS `sendto`;
CREATE TABLE `sendto`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ip` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '远端ip',
  `port` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '端口',
  `mode` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'sokect' COMMENT '传输方式默认sokect',
  `data_type` int(4) NOT NULL COMMENT '0-数据，1-报警',
  `times` int(4) NOT NULL COMMENT '输出间隔时间，秒.0-不发',
  `created_time` timestamp NULL DEFAULT NULL,
  `remark` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '描述',
  `status` int(2) NULL DEFAULT NULL COMMENT '0-连接超时，1-发送正常',
  `charset` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '发送编码格式',
  `thirdset_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '第三方配置id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '输出配置' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of sendto
-- ----------------------------

-- ----------------------------
-- Table structure for sendto_device
-- ----------------------------
DROP TABLE IF EXISTS `sendto_device`;
CREATE TABLE `sendto_device`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sendto_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `created_time` timestamp NULL DEFAULT NULL,
  `device_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '输出设备列表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of sendto_device
-- ----------------------------

-- ----------------------------
-- Table structure for settings
-- ----------------------------
DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '配置名称',
  `created_time` timestamp NULL DEFAULT NULL,
  `values` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '配置值',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `name-id`(`name`, `id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '基础信息' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of settings
-- ----------------------------
INSERT INTO `settings` VALUES ('0b955dad-4641-11ee-979e-000c299cb4b3', 'dataviewUrl', '2023-08-29 15:53:50', 'http://192.168.30.23:3390/visual/webapi/piping/pipings');
INSERT INTO `settings` VALUES ('1099fbea-46e4-41cc-8131-d8201c12aca0', 'logo', '2023-08-29 15:54:57', NULL);
INSERT INTO `settings` VALUES ('30961514-4641-11ee-979e-000c299cb4b3', 'carbonUnit', '2023-08-29 15:54:37', 't');
INSERT INTO `settings` VALUES ('393a1598-8f27-414b-ab26-5e21694fc194', 'serverId', '2023-08-29 15:54:57', NULL);
INSERT INTO `settings` VALUES ('be4d562c-5dfe-49e1-a0df-e09591288b09', 'deviceName', '2023-08-29 15:54:57', '万联智件');
INSERT INTO `settings` VALUES ('ca2b0aad-454a-11ee-979e-000c299cb4b3', 'carbon2dataview', '2023-08-28 10:33:29', '7');
INSERT INTO `settings` VALUES ('f746fd4b-37bc-43cc-b29c-ab019bf4463a', 'remark', '2023-08-29 15:54:57', '');

-- ----------------------------
-- Table structure for spot
-- ----------------------------
DROP TABLE IF EXISTS `spot`;
CREATE TABLE `spot`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '站点名称',
  `parent_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '上级站点id，跟站点为0',
  `created_time` timestamp NULL DEFAULT NULL,
  `remark` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `pid`(`parent_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '站点表' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of spot
-- ----------------------------

-- ----------------------------
-- Table structure for statics
-- ----------------------------
DROP TABLE IF EXISTS `statics`;
CREATE TABLE `statics`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `channel_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通道号',
  `channel_code` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通道名',
  `comm_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通信识别码',
  `stype` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '0-日，1-月',
  `remark` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `unit` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '单位',
  `created_time` timestamp NULL DEFAULT NULL,
  `tag1` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '自定义标签',
  `tag2` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '自定义标签',
  `tag3` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '自定义标签',
  `tag4` varchar(20) 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 = COMPACT;

-- ----------------------------
-- Records of statics
-- ----------------------------

-- ----------------------------
-- Table structure for statics_channel
-- ----------------------------
DROP TABLE IF EXISTS `statics_channel`;
CREATE TABLE `statics_channel`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `statics_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '统计id',
  `channel_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通道id',
  `created_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '统计通道' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of statics_channel
-- ----------------------------

-- ----------------------------
-- Table structure for statics_value
-- ----------------------------
DROP TABLE IF EXISTS `statics_value`;
CREATE TABLE `statics_value`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `statics_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '统计id',
  `values` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '统计值',
  `created_time` timestamp NULL DEFAULT NULL,
  `sdate` date NULL DEFAULT NULL COMMENT '统计日期',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq`(`statics_id`, `sdate`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '统计值' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of statics_value
-- ----------------------------

-- ----------------------------
-- Table structure for statics_values
-- ----------------------------
DROP TABLE IF EXISTS `statics_values`;
CREATE TABLE `statics_values`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `channel_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通道id',
  `sdate` date NULL DEFAULT NULL COMMENT '统计日期',
  `statics_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `values` decimal(16, 2) NULL DEFAULT NULL COMMENT '统计值',
  `created_time` timestamp NULL DEFAULT NULL,
  `first_value` decimal(16, 2) NULL DEFAULT NULL COMMENT '日期初始值',
  `step` int(4) NULL DEFAULT NULL COMMENT '计算周期-分钟',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `dateid`(`channel_id`, `sdate`, `statics_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '统计通道值' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of statics_values
-- ----------------------------

-- ----------------------------
-- Table structure for third_set
-- ----------------------------
DROP TABLE IF EXISTS `third_set`;
CREATE TABLE `third_set`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称',
  `plugin` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '服务魔方监测类型mxpluginname',
  `device_type_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '服务魔方设备IRM_DEVICE下AD_路径',
  `created_time` timestamp NULL DEFAULT NULL,
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `source` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '服务魔方',
  `status` int(2) NULL DEFAULT NULL COMMENT '是否使用，1-使用，2-停用',
  `field_ex1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '扩展字段1',
  `field_ex2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '扩展字段2',
  `field_ex3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '扩展字段3',
  `field_ex4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '扩展字段4',
  `field_ex5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '扩展字段5',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '第三方设置表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of third_set
-- ----------------------------

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `account` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '账号',
  `created_time` timestamp NULL DEFAULT NULL,
  `pwd` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '密码',
  `name` varchar(20) 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 = COMPACT;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('fadffag', 'admin', '2020-11-04 10:49:54', '123456', '管理员');

-- ----------------------------
-- Table structure for virtual_channel
-- ----------------------------
DROP TABLE IF EXISTS `virtual_channel`;
CREATE TABLE `virtual_channel`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通道名称',
  `channel_code` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '通道号',
  `data_type` int(2) NULL DEFAULT NULL COMMENT '0-模拟量，1-开关量',
  `yuansu` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '元素',
  `device_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备id',
  `remark` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `created_time` timestamp NULL DEFAULT NULL,
  `fx` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '计算函数',
  `unit` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '单位',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `device`(`device_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '虚拟通道' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of virtual_channel
-- ----------------------------

-- ----------------------------
-- Procedure structure for NewProc
-- ----------------------------
DROP PROCEDURE IF EXISTS `NewProc`;
delimiter ;;
CREATE PROCEDURE `NewProc`(a int)
begin
        -- declare sum int default 0;  -- default 是指定该变量的默认值
				declare i int default 1;
				DECLARE pid VARCHAR(36);
				DECLARE pcid VARCHAR(36);
				
				declare k int default 1;
				DECLARE pccode VARCHAR(3);
				
				DECLARE spotid VARCHAR(36);
				DECLARE deviceid VARCHAR(36);
				
    while i<=a DO -- 循环开始
			set pid = UUID();
			INSERT into protocol values(pid,CONCAT('协议',pid),'d8834329-5ed0-4b29-9254-ee5c1cf59972',null,'捷宸',CURRENT_TIME,'备注');
		  set deviceid = UUID();
		  INSERT INTO `device` VALUES (deviceid, CONCAT('设备',i), CONCAT('127.0.0.1:',i), CONCAT('',i%10+1), pid, 0, 0, 1, CURRENT_TIME, 1, '备注', 0, 0);

			while k<=300 DO
				set pcid = UUID();
				if k <10 then
					set pccode = CONCAT('00',k);
				ELSEIF k<100 THEN
					set pccode = CONCAT('0',k);
				ELSE
					set pccode = CONCAT('',k);
				end if;
				INSERT INTO `protocol_c` VALUES (pcid,CONCAT('协议',pid), UUID(), pccode, '测试', CURRENT_TIME, 'A', 0, pid);
				INSERT INTO `channel` VALUES (UUID(),deviceid,  CONCAT('通道',k), pccode, 1, 1, 0, '', 1.00, 'A', NULL, NULL, '', CURRENT_TIME, '', null);
				set k=k+1;
			end while;
			set k=1;
			set i=i+1;
    end while; -- 循环结束
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
