-- 新增设备日志表
CREATE TABLE IF NOT EXISTS `dev_log_record`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dev_id` bigint(20) NULL DEFAULT NULL COMMENT '设备id',
  `log_type` int(11) NULL DEFAULT NULL COMMENT '日志类型',
  `log_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '日志内容',
  `month` int(11) NULL DEFAULT NULL COMMENT '年月',
  `log_time` datetime(0) NULL DEFAULT NULL COMMENT '日志时间',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '入库时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `dev_id_index`(`dev_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '设备日志' ROW_FORMAT = Dynamic;

-- 新增设备原始报文表
CREATE TABLE IF NOT EXISTS `dev_packet`  (
  `id` bigint(0) NOT NULL,
  `access_id` varchar(50) COMMENT '设备接入id',
  `dev_sn` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备序列号',
  `dev_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备名称',
  `packet_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '报文名称',
  `packet_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '报文内容',
  `report_time` datetime(0) NULL DEFAULT NULL COMMENT '报文采集时间',
  `product_id` bigint(0) NULL DEFAULT NULL COMMENT '产品id',
  `product_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '产品名称',
  `protocol_id` bigint(0) NULL DEFAULT NULL COMMENT '协议id',
  `protocol_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '协议名称',
  `tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '租户id',
  `tenant_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '租户名称',
  `creator_id` bigint(0) NULL DEFAULT NULL COMMENT '创建人id',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `dev_id_index`(`access_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '设备原始报文表' ROW_FORMAT = Dynamic;

-- 指令任务表增加字段
ALTER TABLE `dev_command_task`
MODIFY COLUMN `status` tinyint(1) NOT NULL COMMENT '执行状态 0未执行 1已执行 2已终止' AFTER `command_params`,
ADD COLUMN `type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '指令任务类型: 0指令面板任务 1指令缓存任务' AFTER `update_time`,
ADD COLUMN `route` tinyint(1) NOT NULL COMMENT '下发通道: 0传输通道， 1配置通道' AFTER `type`,
ADD COLUMN `conf_protocol` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '配置协议版本号, 指令任务类型为指令缓存任务时需要记录' AFTER `route`;

-- 更新指令任务表的下发通道字段
UPDATE dev_command_task task LEFT JOIN dev_command_panel panel ON task.command_panel_id = panel.id SET task.route = panel.protocol_kind WHERE task.type = 0;

-- 设备运维表增加字段
ALTER TABLE `oper_dev_info`
ADD COLUMN `monitor_flag` tinyint(1) NULL DEFAULT 0 COMMENT '可业务监控  0否 1是' AFTER `update_time`,
ADD COLUMN `operate_flag` tinyint(1) NULL DEFAULT 1 COMMENT '可运维配置 0否 1是' AFTER `monitor_flag`;

-- 更新运维表的可业务监控、可运维配置字段
UPDATE oper_dev_info oper_dev
LEFT JOIN
dev_info dev ON oper_dev.sn = dev.sn
SET oper_dev.monitor_flag =
CASE WHEN dev.online is null
	THEN	0
	WHEN dev.online != 2
	THEN	1
	WHEN dev.online = 2
	THEN	0
END
,oper_dev.operate_flag =
CASE WHEN dev.online is null
	THEN	1
	WHEN dev.online != 2
	THEN	1
	WHEN dev.online = 2
	THEN	0
END;

-- 更新设备指令任务表access_id字段长度
ALTER TABLE `dev_command_task`
    MODIFY COLUMN `access_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '设备接入id' AFTER `id`;

-- 设备指令任务表增加设备sn字段
ALTER TABLE `dev_command_task`
    ADD COLUMN `sn` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备sn' AFTER `access_id`;

-- 更新指令任务旧数据的sn
update dev_command_task t left join  oper_dev_info od on t.access_id = od.connect_id set t.sn = od.sn where od.sn is not null and t.access_id is not null and t.sn = '';
update dev_command_task t left join  dev_info di on t.access_id = di.access_id set t.sn = di.sn where di.sn is not null and t.access_id is not null and t.sn = '';

-- 固件升级增加协议类型字段
ALTER TABLE `oper_firmware_upgrade_task`
    ADD COLUMN `protocol_type` tinyint(1) NULL DEFAULT 0 COMMENT '协议类型 0 GPRS-tcp,  1 GPRS-udp,  2 LAN-tcp,  3 LAN-udp' AFTER `stoping`;

-- 挂测升级增加协议类型字段
ALTER TABLE `oper_mount_upgrade_task`
    ADD COLUMN `protocol_type` tinyint(1) NULL DEFAULT 0 COMMENT '协议类型 0 GPRS-tcp,  1 GPRS-udp,  2 LAN-tcp,  3 LAN-udp' AFTER `finish_time`;

-- 原始报文表增加索引
ALTER TABLE `dev_packet`
    ADD INDEX `dev_sn_index`(`dev_sn`) USING BTREE,
    ADD INDEX `report_time_index`(`report_time`) USING BTREE;