
DELIMITER ??
DROP PROCEDURE IF EXISTS schema_change ??
CREATE PROCEDURE schema_change () BEGIN


	IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'iot_alarm_data'
                    AND column_name = 'do_by_user') THEN
        ALTER TABLE `iot_alarm_data`
            ADD COLUMN `do_by_user` varchar(255) NULL COMMENT '告警处置人处理人' AFTER `do_type`;
    END IF;

    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'iot_alarm_data'
                    AND column_name = 'do_content') THEN
        ALTER TABLE `iot_alarm_data`
            ADD COLUMN `do_content` varchar(255) NULL COMMENT '告警处置内容' AFTER `do_by_user`;
    END IF;


END ??
DELIMITER ;
CALL schema_change ();

DELETE from `sys_dict_type` WHERE  `dict_type`='alarm_deal_type';
DELETE from `sys_dict_data` WHERE  `dict_type`='alarm_deal_type';
INSERT INTO `sys_dict_type` (`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('告警处置类型', 'alarm_deal_type', '0', '超级管理员', '2024-04-02 18:02: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 (5, '误报', '2', 'alarm_deal_type', NULL, 'default', 'N', '0', '超级管理员', '2024-04-02 18:03:16', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2, '正常报警', '3', 'alarm_deal_type', NULL, 'default', 'N', '0', '超级管理员', '2024-04-02 18:03:35', '', 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, '报修', '4', 'alarm_deal_type', NULL, 'default', 'N', '0', '超级管理员', '2024-04-02 18:03:46', '', 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, '测试', '5', 'alarm_deal_type', NULL, 'default', 'N', '0', '超级管理员', '2024-04-02 18:03:56', '', NULL, NULL);

delete from `iot_alarm_system_field` WHERE  `source_type`='41881';
INSERT INTO `iot_alarm_system_field` (`source_type`, `source_type_des`, `sys_field_code`, `name`, `specs`, `operators`, `type`, `type_des`, `unit`, `enable`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES (41881, '盗情', '41881', '被盗告警', '{\"0\": \"正常\",\"1\": \"告警\"}', '{\"EQUALS\": \"等于\"}', 'ENUM', '枚举', NULL, 1, '2022-07-26 16:14:26', NULL, '2022-07-26 16:14:30', NULL);


delete from `sys_dict_data` WHERE  `dict_value`='41881' AND `dict_type`='sensor_device_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 (700, '防盗传感器', '41881', 'sensor_device_type', NULL, 'default', 'N', '0', NULL, now(), NULL, NULL, NULL);

delete from `sys_dict_data` WHERE  `dict_value`='41881' AND `dict_type`='app_sensor_device_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 ( 6, '被盗', '41881', 'app_sensor_device_type', NULL, 'default', 'N', '0', '超级管理员', '2024-03-04 16:23:49', '', NULL, NULL);

delete from `sys_dict_type` WHERE  `dict_type`='alarm_deal_status';
INSERT INTO `sys_dict_type` (`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('告警处置状态', 'alarm_deal_status', '0', '超级管理员', '2024-04-08 09:38:40', '', NULL, NULL);

delete from `sys_dict_data` WHERE  `dict_type`='alarm_deal_status';
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( 0, '未处置', '0', 'alarm_deal_status', NULL, 'default', 'N', '0', '超级管理员', '2024-04-08 09:39:03', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( 1, '已处置', '1', 'alarm_deal_status', NULL, 'default', 'N', '0', '超级管理员', '2024-04-08 09:39:12', '', NULL, NULL);

-- 给'泉州','宁德' 下辖网点未设置过角色的人员 新增 “网点安全员”角色
INSERT INTO sys_user_role (user_id,role_id,create_by,create_time)
SELECT u.id as user_id,122 as role_id, '超级管理员' create_by, NOW() as create_time
FROM
    sys_org org
        INNER JOIN sys_user u ON org.id = u.org_id
        LEFT JOIN sys_user_role ur ON u.id =ur.user_id
WHERE
        org.type = 4
  AND org.affiliated_area IN ('泉州','宁德')
  AND ur.user_id IS null