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;

IF NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'core_out_in_record'
                        AND column_name = 'remark') THEN
ALTER TABLE `core_out_in_record`
    ADD COLUMN `remark` varchar(255) NULL COMMENT '备注信息' AFTER `update_time`;
END IF;

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

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`;
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;



        IF NOT EXISTS(SELECT DISTINCT INDEX_NAME
            FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'core_monitoring_task_monitor_info'
              AND INDEX_NAME = 'task_monitor_id_index') THEN
        ALTER TABLE `core_monitoring_task_monitor_info`
            ADD INDEX `task_monitor_id_index`(`task_monitor_id`) USING BTREE;
end if;


        IF NOT EXISTS(SELECT DISTINCT INDEX_NAME
            FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'core_monitoring_task_registration_monitor'
              AND INDEX_NAME = 'task_registration_id_index') THEN
        ALTER TABLE `core_monitoring_task_registration_monitor`
            ADD INDEX `task_registration_id_index`(`task_registration_id`) USING BTREE;
end if;


        IF NOT EXISTS(SELECT DISTINCT INDEX_NAME
            FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'core_register_book_pdf'
              AND INDEX_NAME = 'date_index') THEN
        ALTER TABLE `core_register_book_pdf`
            ADD INDEX `date_index`(`date`) USING BTREE;
end if;


        IF NOT EXISTS(SELECT DISTINCT INDEX_NAME
            FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'core_monitoring_task_registration'
              AND INDEX_NAME = 'taskId_index') THEN
ALTER TABLE `core_monitoring_task_registration`
    ADD INDEX `taskId_index`(`task_id`) USING BTREE;
end if;

    IF EXISTS(SELECT DISTINCT INDEX_NAME
            FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'core_message_center'
              AND INDEX_NAME = 'idx_userid_type') THEN
ALTER TABLE `core_message_center`
DROP INDEX `idx_userid_type`;
end if;

ALTER TABLE `core_message_center`
    ADD INDEX `idx_userid_type`(`user_id`, `type`) USING BTREE;

END ??
DELIMITER ;
CALL schema_change();

update iot_device_info set sync_status = 2;

-- 新增设备同步状态字典
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 (01747912341556742146,01747915341556742146,01761830909641408513,01858761159550345217);
INSERT INTO `sys_menu` VALUES (01747912341556742146, '规则配置', 1747911340288892930, 31, 'rule', NULL, NULL, 1, 0, 'M', '0', '0', '', '1', 'system', NULL, '超级管理员', '2024-11-18 13:55:15', '超级管理员', '2024-11-18 13:55:15', '');
INSERT INTO `sys_menu` VALUES (01747915341556742146, '视频诊断任务', 1747912341556742146, 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, '视频诊断阈值', 1747912341556742146, 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', '');
INSERT INTO `sys_menu` VALUES (01858761159550345217, '录像计划', 1747912341556742146, 4, 'videoChannelPlan', 'iot/videoChannelPlan/index', NULL, 1, 0, 'C', '0', '0', '', '1', 'date', NULL, '超级管理员', '2024-11-19 14:36:09', '超级管理员', '2024-11-21 11:20:07', '');

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：异常计划；4：分时段定时动检录像；5：分时段动检录像；-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(2000) 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`;

UPDATE `iot_server_product` SET `display_name` = 'Hik' WHERE `id` = '19ed86f7-780d-29ec-4bc7-3a13a7097ea1';
UPDATE `iot_server_product` SET `display_name` = 'DaHua_V1' WHERE `id` = '783bb246-6ebe-7e69-e055-3a14adfe10c9';
UPDATE `iot_server_product` SET `display_name` = 'DaHua' WHERE `id` = '92467897-92ef-647f-e55e-3a13b7f3ca9a';

drop table if exists `sys_oper_log_history`;
CREATE TABLE `sys_oper_log_history` (
                                        `oper_id` bigint NOT NULL AUTO_INCREMENT COMMENT '日志主键',
                                        `title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '模块标题',
                                        `business_type` int DEFAULT '0' COMMENT '业务类型（0其它 1新增 2修改 3删除）',
                                        `method` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '方法名称',
                                        `request_method` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '请求方式',
                                        `operator_type` int DEFAULT '0' COMMENT '操作类别（0其它 1后台用户 2手机端用户）',
                                        `oper_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '操作人员',
                                        `dept_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '部门名称',
                                        `oper_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '请求URL',
                                        `oper_ip` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '主机地址',
                                        `oper_location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '操作地点',
                                        `oper_param` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '请求参数',
                                        `json_result` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '返回参数',
                                        `status` int DEFAULT '0' COMMENT '操作状态（0正常 1异常）',
                                        `error_msg` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '错误消息',
                                        `oper_time` datetime DEFAULT NULL COMMENT '操作时间',
                                        `cost_time` bigint DEFAULT '0' COMMENT '消耗时间',
                                        `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
                                        `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                        `update_time` datetime DEFAULT NULL COMMENT '修改时间',
                                        `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人',
                                        PRIMARY KEY (`oper_id`) USING BTREE,
                                        KEY `idx_sys_oper_log_history_bt` (`business_type`) USING BTREE,
                                        KEY `idx_sys_oper_log_history_s` (`status`) USING BTREE,
                                        KEY `idx_sys_oper_log_history_ot` (`oper_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='操作日志记录';

-- 前端录像类型字典数据
delete from `sys_dict_type` where dict_type='video_plan_type';
INSERT INTO `sys_dict_type` (`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( '前端录像类型', 'video_plan_type', '0', '超级管理员', '2024-12-12 15:49:00', '', NULL, NULL);
delete from `sys_dict_data` where dict_type='video_plan_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 (0, '全天定时录像', '0', 'video_plan_type', NULL, 'default', 'N', '0', '超级管理员', '2024-12-12 15:51: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 (1, '分时段定时录像', '1', 'video_plan_type', NULL, 'default', 'N', '0', '超级管理员', '2024-12-12 15:51:43', '超级管理员', '2024-12-12 15:51:52', 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', 'video_plan_type', NULL, 'default', 'N', '0', '超级管理员', '2024-12-12 15:52: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', 'video_plan_type', NULL, 'default', 'N', '0', '超级管理员', '2024-12-12 15:52:23', '超级管理员', '2024-12-12 15:53:32', 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, '分时段定时动检', '4', 'video_plan_type', NULL, 'default', 'N', '0', '超级管理员', '2024-12-12 15:52:34', '超级管理员', '2024-12-12 15:53:51', 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, '分时段动检录像', '5', 'video_plan_type', NULL, 'default', 'N', '0', '超级管理员', '2024-12-12 15:54: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 (6, '设备不支持', '-2', 'video_plan_type', NULL, 'default', 'N', '0', '超级管理员', '2024-12-12 15:54:22', '', 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, '通讯失败', '-3', 'video_plan_type', NULL, 'default', 'N', '0', '超级管理员', '2024-12-12 15:54:42', '', NULL, NULL);

-- 新增2个字典 用于人员补充信息中的 现任职务 用于区区分 保卫部职务、监控中心职务
DELETE from sys_dict_type WHERE dict_type in ('security_current_position','monitoring_current_position');
INSERT INTO `sys_dict_type` (`dict_id`, `dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (20231113325, '保卫部职务', 'security_current_position', '0', '超级管理员', '2024-12-27 11:29:26', '', NULL, NULL);
INSERT INTO `sys_dict_type` (`dict_id`, `dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (20231113327, '监控中心职务', 'monitoring_current_position', '0', '超级管理员', '2024-12-27 11:30:16', '', NULL, NULL);
DELETE from sys_dict_data WHERE dict_type in ('security_current_position','monitoring_current_position');
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', 'security_current_position', NULL, 'default', 'N', '0', 'jwx', '2023-11-27 14:56: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 (3, '一般管理人员', '3', 'security_current_position', NULL, 'default', 'N', '0', 'jwx', '2023-11-27 14:56: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 (2, '监控中心负责人', '2', 'monitoring_current_position', NULL, 'default', 'N', '0', 'jwx', '2023-11-27 14:56: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 (4, '监控中心操作人员', '4', 'monitoring_current_position', NULL, 'default', 'N', '0', 'jwx', '2023-11-27 14:56: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 (5, '监控中心技术维护岗', '5', 'monitoring_current_position', NULL, 'default', 'N', '0', 'jwx', '2023-11-27 14:56:44', '', NULL, NULL);

-- 修改隐患问题表字段长度
ALTER TABLE `core_question`
    MODIFY COLUMN `check_content` varchar(1500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '检查内容' AFTER `check_item`;


-- 清除 2024 年的 业务提醒消息
DELETE from core_message_center WHERE create_time <'2025-01-01' AND type =2;

-- 清理历史脏数据： 原先选择了未通过原因，现在已经通过了选择是，导出还有未通过原因
UPDATE sys_user_information set certificate_fail_reason =NULL WHERE certificate_fail_reason is not null AND certificate_work='1';
-- 升级脚本执行完成