soc/project_data/sql/1.0.11/soc/soc.sql

-- v 1.0.11 版本升级脚本

drop table if exists `iot_websocket_msg`;
CREATE TABLE `iot_websocket_msg` (
                                     `id` varchar(125) COLLATE utf8mb4_general_ci NOT NULL COMMENT '消息id',
                                     `send_by` int DEFAULT NULL COMMENT '发送方;0:主机,1:平台',
                                     `event` varchar(125) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '事件类型',
                                     `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 '目标主机',
                                     `server_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '主机名称',
                                     `status` int DEFAULT NULL COMMENT '消息状态;0:处理中,1:处理完成,2:重试中,3:重试失败',
                                     `receive_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '收到消息内容',
                                     `reply_content` text COLLATE utf8mb4_general_ci COMMENT '回复内容',
                                     `ready_send_time` datetime DEFAULT NULL COMMENT '准备发送时间',
                                     `really_send_time` datetime DEFAULT NULL COMMENT '重试成功时间',
                                     `fire_time` datetime DEFAULT NULL COMMENT '触发时间',
                                     `max_retry_times` int DEFAULT NULL COMMENT '最大重试次数',
                                     `cur_retry_times` int DEFAULT NULL COMMENT '当前重试次数',
                                     `retry_interval` int DEFAULT NULL COMMENT '重试间隔,分钟',
                                     `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                     `update_time` datetime DEFAULT NULL COMMENT '最后一次修改时间',
                                     PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





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_server_product'
                        AND column_name = 'models') THEN
ALTER TABLE  `iot_server_product`
    ADD COLUMN `models` varchar(125) NULL AFTER `categories`;
END IF;

        IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'iot_server_product'
                        AND column_name = 'producer') THEN
ALTER TABLE  `iot_server_product`
    ADD COLUMN `producer` varchar(125) NULL AFTER `models`;
END IF;

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

IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'iot_device_info'
                        AND column_name = 'enable') THEN
ALTER TABLE `iot_device_info`
    ADD COLUMN `enable` int(11) NULL DEFAULT 0 COMMENT '0:启用，1：禁用' AFTER `update_by`;
end if;


        IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'iot_device_info'
                        AND column_name = 'device_brand') THEN
ALTER TABLE `iot_device_info`
    ADD COLUMN `device_brand` bigint NULL COMMENT '品牌' AFTER `device_product`;
end if;

        IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'iot_device_info'
                        AND column_name = 'sync_status') THEN
ALTER TABLE `iot_device_info`
    ADD COLUMN `sync_status` int(0) NULL DEFAULT 0 COMMENT '下发状态;0未下发,1下发中,2下发成功,3下发失败' AFTER `enable`;
update iot_device_info set sync_status = 2;
end if;


        IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'iot_alarm_subsystem'
                        AND column_name = 'enable') THEN
ALTER TABLE `iot_alarm_subsystem`
    ADD COLUMN `enable` int NULL DEFAULT 0 COMMENT '0:启用，1：禁用' AFTER `source`;
end if;

        IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'iot_alarm_subsystem_log'
                        AND column_name = 'enable') THEN
ALTER TABLE `iot_alarm_subsystem_log`
    ADD COLUMN `enable` int NULL DEFAULT 0 COMMENT '0:启用，1：禁用' AFTER `update_by`;
end if;

        IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'iot_alarm_defence_area'
                        AND column_name = 'enable') THEN
ALTER TABLE `iot_alarm_defence_area`
    ADD COLUMN `enable` int NULL DEFAULT 0 COMMENT '0:启用，1：禁用' AFTER `module_address`;
end if;

        IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'iot_device_info_extend'
                        AND column_name = 'listen_address') THEN
ALTER TABLE `iot_device_info_extend`
    ADD COLUMN `listen_address` varchar(255) NULL COMMENT '监听地址' AFTER `update_by`,
                ADD COLUMN `listen_port` int(11) NULL COMMENT '监听端口' AFTER `listen_address`,
                ADD COLUMN `device_mac` varchar(255) NULL COMMENT '设备mac地址' AFTER `listen_port`,
                ADD COLUMN `device_user_code` varchar(255) NULL COMMENT '主机操作码' AFTER `device_mac`,
                ADD COLUMN `iot_address` varchar(255) NULL COMMENT '控制地址' AFTER `device_user_code`,
                ADD COLUMN `host_device_type` varchar(255) NULL COMMENT '主机类型' AFTER `iot_address`;
end if;













END ??
DELIMITER ;
CALL schema_change();

-- 新增设备同步状态字典
delete from sys_dict_type where dict_type='sync_status';
delete from sys_dict_data where dict_type='sync_status';
INSERT INTO sys_dict_type(`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('设备同步状态', 'sync_status', '0', '超级管理员', '2024-11-13 16:21: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', 'sync_status', NULL, 'default', 'N', '0', '超级管理员', '2024-11-13 16:22:05', '', 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', 'sync_status', NULL, 'default', 'N', '0', '超级管理员', '2024-11-13 16:22:18', '', 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', 'sync_status', NULL, 'default', 'N', '0', '超级管理员', '2024-11-13 16:22:52', '', 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', 'sync_status', NULL, 'default', 'N', '0', '超级管理员', '2024-11-13 16:23:01', '', NULL, NULL);



-- 诊断绑定摄像机相关表
drop table if exists `iot_device_diagnose_mission`;
CREATE TABLE `iot_device_diagnose_mission` (
                                               `mission_id` varchar(70) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '任务ID',
                                               `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '任务名称',
                                               `begin_time` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '开始时间',
                                               `end_time` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '结束时间',
                                               `enabled` int NOT NULL COMMENT '是否启用：0-未启用，1-已启用',
                                               `cycle` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '诊断周期（小时）',
                                               `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                               `iot_token` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot服务唯一编码',
                                               PRIMARY KEY (`mission_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='摄像机诊断任务表';
-- 诊断任务默认
INSERT INTO `iot_device_diagnose_mission` VALUES ('5ed19826-dd47-421e-a632-b00ba9271c45', '默认', '00:00:00', '23:59:59', '1', '0.1','2024-11-18 17:03:54',null);

drop table if exists `iot_device_diagnose_mission_ch`;
CREATE TABLE `iot_device_diagnose_mission_ch` (
                                                  `id` bigint NOT NULL,
                                                  `mission_id` varchar(70) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '诊断任务id',
                                                  `iot_token` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot服务唯一编码',
                                                  `device_product` varchar(62) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '产品名称',
                                                  `dvs` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  COMMENT 'dvs的设备编码',
                                                  `channel` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  COMMENT '摄像机的编码',
                                                  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='摄像机诊断任务绑定关系表';

drop table if exists `iot_device_diagnose_threshold`;
CREATE TABLE `iot_device_diagnose_threshold` (
                                                 `threshold_id` bigint NOT NULL,
                                                 `threshold_code` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'MD5生成唯一码',
                                                 `threshold_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '阈值名称',
                                                 `nosignal` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '信号丢失',
                                                 `freeze` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '冻结',
                                                 `color` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '偏色',
                                                 `snow` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '雪花',
                                                 `covered` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '遮挡',
                                                 `luminance` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '亮度',
                                                 `roll` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '条纹',
                                                 `fuzzy` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '模糊',
                                                 `contrast` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '对比度',
                                                 `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                                 `diagnose_number` int DEFAULT NULL COMMENT '绑定通道数量',
                                                 `type` bigint DEFAULT NULL COMMENT '是否默认标识，1为默认，0为其他',
                                                 `begin_time` time DEFAULT NULL COMMENT '有效开始时间',
                                                 `end_time` time DEFAULT NULL COMMENT '有效结束时间',
                                                 `iot_token` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot服务唯一编码',
                                                 PRIMARY KEY (`threshold_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='摄像机诊断阈值表';
-- 诊断阈值默认
INSERT INTO `iot_device_diagnose_threshold` VALUES (1,'0cdd75ea0d73056dbd6727b96339f2f6', '默认', '@value==255', '@value>=250', '@value>=150', '@value>=7', '@value>=255', '@value>=225', '@value>=140', '@value>=253', '@value<=0',  '2024-11-18 17:03:54', '0', '1', '00:00:00', '23:59:59',null);

drop table if exists `iot_device_diagnose_threshold_ch`;
CREATE TABLE `iot_device_diagnose_threshold_ch` (
                                                    `id` bigint NOT NULL,
                                                    `threshold_code` varchar(70) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '诊断阈值id',
                                                    `iot_token` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot服务唯一编码',
                                                    `device_product` varchar(62) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备品牌',
                                                    `dvs` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  COMMENT 'dvs的设备编码',
                                                    `channel` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  COMMENT '摄像机的编码',
                                                    PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='摄像机诊断阈值绑定关系表';

delete from iot_server_product where id in ('0a3fa02d-c677-4f8a-a053-4e30e592df7e','20fa2a1a-a069-4975-8d64-091a39539e02');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('0a3fa02d-c677-4f8a-a053-4e30e592df7e', 'TALK_HOST', '对讲主机', 1, '[\r\n  \"Host_Talk\"\r\n]', '[\r\n  \"Normal\"\r\n]');
INSERT INTO `iot_server_product`(`id`, `name`, `display_name`, `enabled`, `categories`, `models`) VALUES ('20fa2a1a-a069-4975-8d64-091a39539e02', 'DOOR_HOST', '门禁主机', 1, '[\r\n  \"Host_Door\"\r\n]', '[\r\n  \"Normal\"\r\n]');

-- 摄像机诊断菜单
delete from `sys_menu` where id in (01747915341556742146,01761830909641408513);
INSERT INTO `sys_menu` VALUES (01747915341556742146, '视频诊断任务', 1747911340288892930, 3, 'diagnoseMission', 'iot/diagnoseMission', NULL, 1, 0, 'C', '0', '0', 'iot:diagnoseMission', '1', 'client', NULL, '超级管理员', '2024-11-18 17:22:07', '超级管理员', '2024-11-18 17:22:07', '');
INSERT INTO `sys_menu` VALUES (01761830909641408513, '视频诊断阈值', 1747911340288892930, 2, 'diagnoseThreshold', 'iot/diagnoseThreshold', NULL, 1, 0, 'C', '0', '0', 'iot:diagnoseThreshold', '1', 'client', NULL, '超级管理员', '2024-11-18 09:37:19', '超级管理员', '2024-11-18 09:37:19', '');

update sys_multi_layer_dictionary set is_system=1 where id in (1,2,3,4);
-- 新增预置品牌
delete from sys_multi_layer_dictionary where id in (1861655738759962626,1861655839484891137,1861655874030460930,1861656043241267202);
INSERT INTO `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 (1861655738759962626, '1', '丽泽', -1, 'BrandModelType', '品牌型号类', 1, 0, '0', '1', '1861655738759962626-', NULL, '2024-11-27 14:18:10.665000', NULL, NULL, NULL, 0);
INSERT INTO `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 (1861655839484891137, '1', '讯美', -1, 'BrandModelType', '品牌型号类', 1, 0, '0', '1', '1861655839484891137-', NULL, '2024-11-27 14:18:34.678000', NULL, NULL, NULL, 0);
INSERT INTO `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 (1861655874030460930, '1', '其他', -1, 'BrandModelType', '品牌型号类', 1, 0, '0', '1', '1861655874030460930-', NULL, '2024-11-27 14:18:42.919000', NULL, NULL, NULL, 0);
INSERT INTO `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 (1861656043241267202, '1', '世邦', -1, 'BrandModelType', '品牌型号类', 1, 0, '0', '1', '1861656043241267202-', NULL, '2024-11-27 14:19:23.262000', NULL, NULL, NULL, 0);

-- 录像模板和录像计划表
drop table if exists `iot_dvs_plantime`;
CREATE TABLE `iot_dvs_plantime` (
                                    `id` bigint NOT NULL,
                                    `dvs_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot_device_info表device_code',
                                    `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot_device_info表device_code',
                                    `type` int DEFAULT NULL COMMENT '当前时间片段录像计划类型（0-定时录像，1-移动侦测录像，2-报警录像，3-移动侦测或报警录像，4-移动侦测和报警录像）',
                                    `week` int DEFAULT NULL COMMENT '星期标识（星期1-7）',
                                    `start_time` datetime DEFAULT NULL COMMENT '录像片段开始时间',
                                    `end_time` datetime DEFAULT NULL COMMENT '录像片段结束时间',
                                    `iot_token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                    PRIMARY KEY (`id`),
                                    KEY `iot_token` (`iot_token`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

drop table if exists `iot_dvs_plantype`;
CREATE TABLE `iot_dvs_plantype` (
                                    `id` bigint NOT NULL,
                                    `dvs_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot_device_info表device_code',
                                    `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'iot_device_info表device_code',
                                    `plan_type` int DEFAULT NULL COMMENT '录像计划类型（0：全天定时录像；1：分时段定时录像；2：全天移动侦测；3：混合录像计划）',
                                    `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                    `update_time` datetime DEFAULT NULL COMMENT '更新时间',
                                    `min_time` datetime DEFAULT NULL COMMENT '录像时间段最小时间',
                                    `max_time` datetime DEFAULT NULL COMMENT '录像时间段最大时间',
                                    `isstart` int DEFAULT NULL COMMENT '是否启动录像（0，启动；1：未启动）',
                                    `fail_remark` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '失败原因',
                                    `iot_token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                    PRIMARY KEY (`id`),
                                    KEY `iot_token` (`iot_token`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

drop table if exists `iot_dvs_template`;
CREATE TABLE `iot_dvs_template` (
                                    `id` bigint NOT NULL,
                                    `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '录像模板名称',
                                    `type` int DEFAULT NULL COMMENT '生成类型：0.系统 1.手动',
                                    `template_md5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'name生成的MD5',
                                    `iot_token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                    PRIMARY KEY (`id`),
                                    UNIQUE KEY `iot_token` (`iot_token`,`template_md5`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

drop table if exists `iot_dvs_template_channel`;
CREATE TABLE `iot_dvs_template_channel` (
                                            `id` bigint NOT NULL,
                                            `template_md5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '录像模板md5',
                                            `dvs_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'deviceInfo表录像机device_code',
                                            `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'deviceInfo表通道device_code',
                                            `create_time` datetime DEFAULT NULL,
                                            `iot_token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                            PRIMARY KEY (`id`),
                                            KEY `iot_token` (`iot_token`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

alter table mediator_video_integrity_check
    modify record_span longtext null comment '原始录像索引';
alter table mediator_video_integrity_check_log
    modify record_span longtext null comment '原始录像索引';


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', '480', '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', '480', 'Y', null, null, '', null, null);

ALTER TABLE `iot_alarm_subsystem_log`
    MODIFY COLUMN `org_path` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '机构' AFTER `alarm_host_code`;
-- 升级脚本执行完成