soc/project_data/sql/0.0.6-iot/soc/soc.sql

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 = 'sys_device'
--                     AND column_name = 'category_id') THEN
--         ALTER TABLE sys_device
--             ADD COLUMN `category_id` bigint NULL COMMENT '设备分类' AFTER `definition`;
--     END IF;

--     IF NOT EXISTS(SELECT *
--                   FROM information_schema.columns
--                   WHERE table_schema = DATABASE()
--                     AND table_name = 'sys_device'
--                     AND column_name = 'host_code') THEN
--         ALTER TABLE sys_device
--             ADD COLUMN `host_code`  varchar(225) NULL COMMENT '主机code' AFTER `category_id`;
--     END IF;

--  设备表增加设备分类字段
--     IF NOT EXISTS(SELECT *
--                   FROM information_schema.columns
--                   WHERE table_schema = DATABASE()
--                     AND table_name = 'sys_device'
--                     AND column_name = 'device_code') THEN
--         ALTER TABLE `sys_device` 
-- 			ADD COLUMN `device_code` varchar(64) NULL COMMENT '设备编号' AFTER `org_name`,
-- 			ADD COLUMN `source` int NULL DEFAULT 0 COMMENT '来源，0或null：手动添加，1：主机平台,' AFTER `definition`;
-- 		update sys_device set device_code=UUID() WHERE device_code is null;
-- 		update sys_device set source=0 WHERE source is null;
--     END IF;
--     IF NOT EXISTS(SELECT *
--                   FROM information_schema.columns
--                   WHERE table_schema = DATABASE()
--                     AND table_name = 'sys_device'
--                     AND column_name = 'type') THEN
--         ALTER TABLE sys_device
--             ADD COLUMN `type`  varchar(25) NULL COMMENT '设备型号' AFTER `host_code`;
--     END IF;

--     IF NOT EXISTS(SELECT *
--                   FROM information_schema.columns
--                   WHERE table_schema = DATABASE()
--                     AND table_name = 'sys_device'
--                     AND column_name = 'ip') THEN
--         ALTER TABLE sys_device
--             ADD COLUMN `ip`  varchar(225) NULL COMMENT '设备ip' AFTER `type`;
--     END IF;

    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'sys_device'
                    AND column_name = 'org_path') THEN
        ALTER TABLE sys_device
            ADD COLUMN `org_path`  varchar(225) NULL COMMENT '机构path' AFTER `org_name`;
    END IF;



--     IF NOT EXISTS(SELECT *
--                   FROM information_schema.columns
--                   WHERE table_schema = DATABASE()
--                     AND table_name = 'sys_device'
--                     AND column_name = 'channel_code') THEN
--         ALTER TABLE sys_device
--             ADD COLUMN `channel_code`  varchar(225) NULL COMMENT '通道号' AFTER `host_code`;
--     END IF;

    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'core_protection'
                    AND column_name = 'status_change_time') THEN
        ALTER TABLE `core_protection` 
					ADD COLUMN `status_change_time` datetime NULL COMMENT '布撤防时间' AFTER `all_Hour`,
					ADD COLUMN `source` int NULL COMMENT '设备来源：0：设备登记；1：iot推送' AFTER `update_by`,
					MODIFY COLUMN `status_update_time` datetime NULL DEFAULT NULL COMMENT '布撤防更新时间' AFTER `all_Hour`
					COMMENT = '报警控制器（子系统）表';
		
    END IF;
	IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'core_protection_log'
                    AND column_name = 'status_change_time') THEN
        ALTER TABLE `core_protection_log` 
					ADD COLUMN `status_change_time` datetime NULL COMMENT '布撤防时间' AFTER `all_Hour`,
					MODIFY COLUMN `status_update_time` datetime NULL DEFAULT NULL COMMENT '布撤防更新时间' AFTER `all_Hour`
					COMMENT = '报警控制器（子系统）布撤防历史表';
		
    END IF;	
END ??
DELIMITER ;
CALL schema_change ();



drop table if exists mediator_video_days_check;
CREATE TABLE `mediator_video_days_check` (
     `id` bigint NOT NULL,
     `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
     `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
     `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
     `update_time` datetime DEFAULT NULL COMMENT '修改时间',
     `org_id` bigint DEFAULT '0' COMMENT '机构',
     `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
     `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
     `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道序号',
     `channel_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道的名称',
     `check_date` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '计划存储天数起止日期',
     `equipment_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机唯一标识',
     `lose_date` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '计划存储天数起止日期',
     `plan_days` int DEFAULT '0' COMMENT '计划存储总天数',
     `real_days` int DEFAULT '0' COMMENT '计划日期内实际存储总天数',
     `equipment_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
     `status` int DEFAULT NULL COMMENT '是否报警 0:未报警,1:报警',
     `earliest_video_time` datetime DEFAULT NULL COMMENT '最早录像日期',
     PRIMARY KEY (`id`) USING BTREE,
     KEY `idx_user_orgId` (`org_id`) USING BTREE,
     KEY `idx_user_orgPath` (`org_path`) USING BTREE,
     KEY `idx_code` (`equipment_code`(191),`channel_code`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='录像机天数检查';

drop table if exists mediator_video_days_check_log;
CREATE TABLE `mediator_video_days_check_log` (
         `id` bigint NOT NULL,
         `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
         `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
         `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
         `update_time` datetime DEFAULT NULL COMMENT '修改时间',
         `org_id` bigint DEFAULT '0' COMMENT '机构',
         `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
         `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
         `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道序号',
         `channel_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道的名称',
         `check_date` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '计划存储天数起止日期',
         `equipment_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机唯一标识',
         `lose_date` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '计划存储天数起止日期',
         `plan_days` int DEFAULT '0' COMMENT '计划存储总天数',
         `real_days` int DEFAULT '0' COMMENT '计划日期内实际存储总天数',
         `video_days_check_id` bigint DEFAULT NULL COMMENT '录像机天数检查ID',
         `status` int DEFAULT NULL,
         `earliest_video_time` datetime DEFAULT NULL COMMENT '最早录像日期',
         `equipment_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
         PRIMARY KEY (`id`) USING BTREE,
         KEY `idx_user_orgId` (`org_id`) USING BTREE,
         KEY `idx_user_orgPath` (`org_path`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='录像机天数检查日志';

drop table if exists mediator_video_integrity_check;
CREATE TABLE `mediator_video_integrity_check` (
          `id` bigint NOT NULL,
          `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
          `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
          `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
          `update_time` datetime DEFAULT NULL COMMENT '修改时间',
          `org_id` bigint DEFAULT '0' COMMENT '机构',
          `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
          `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
          `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道序号',
          `channel_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道的名称',
          `check_span` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '检查日期录像计划规则集合',
          `equipment_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机唯一标识',
          `lose_span` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '检查日期录像丢失情况集合',
          `record_date` date DEFAULT NULL COMMENT '录像情况检查日期',
          `device_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备名称',
          `status` int DEFAULT NULL COMMENT '0:正常 1:部分丢失 2:全部丢失',
          `equipment_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
          `plan_days` int DEFAULT NULL COMMENT '录像计划存储多少天',
          `lose_span_old` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '录像机唯一标识',
          PRIMARY KEY (`id`) USING BTREE,
          KEY `idx_user_orgId` (`org_id`) USING BTREE,
          KEY `idx_user_orgPath` (`org_path`) USING BTREE,
          KEY `idx_code` (`channel_code`(191),`equipment_code`(191)),
          KEY `idx_recordDate_orgPath_status` (`record_date`,`org_path`,`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='录像机完整性检查表';


drop table if exists mediator_video_diagnosis;
CREATE TABLE `mediator_video_diagnosis` (
    `id` bigint NOT NULL,
    `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
    `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
    `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
    `update_time` datetime DEFAULT NULL COMMENT '修改时间',
    `org_id` bigint DEFAULT '0' COMMENT '机构',
    `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
    `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
    `alarm_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '报警/恢复时间',
    `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道序号',
    `channel_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道的名称',
    `detail_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '质量当前状态详情',
    `equipment_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机唯一标识',
    `is_alarm` int DEFAULT NULL COMMENT '报警状态标识',
    `equipment_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_user_orgId` (`org_id`) USING BTREE,
    KEY `idx_user_orgPath` (`org_path`) USING BTREE,
    KEY `idx_code` (`channel_code`(191),`equipment_code`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='视频质量诊断结果';

drop table if exists mediator_video_diagnosis_log;
CREATE TABLE `mediator_video_diagnosis_log` (
        `id` bigint NOT NULL,
        `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
        `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
        `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
        `update_time` datetime DEFAULT NULL COMMENT '修改时间',
        `org_id` bigint DEFAULT '0' COMMENT '机构',
        `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
        `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
        `alarm_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '报警/恢复时间',
        `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道序号',
        `channel_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道的名称',
        `detail_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '质量当前状态详情',
        `equipment_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机唯一标识',
        `is_alarm` int DEFAULT NULL COMMENT '报警状态标识',
        `video_diagnosis_id` bigint DEFAULT NULL COMMENT '视频质量诊断结果ID',
        `equipment_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
        PRIMARY KEY (`id`) USING BTREE,
        KEY `idx_user_orgId` (`org_id`) USING BTREE,
        KEY `idx_user_orgPath` (`org_path`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='视频质量诊断结果';

drop table if exists mediator_video_diagnosis_record;
CREATE TABLE `mediator_video_diagnosis_record` (
           `id` bigint NOT NULL,
           `org_id` bigint NOT NULL COMMENT '机构id',
           `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
           `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
           `video_diagnosis_id` bigint DEFAULT NULL COMMENT '视频质量诊断id',
           `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
           `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
           `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
           `update_time` datetime DEFAULT NULL COMMENT '修改时间',
           `equipment_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备id',
           `channel_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通道名称',
           `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通道序号',
           `device_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备名称',
           `device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备id',
           `signal_lost` int NOT NULL DEFAULT '0' COMMENT '信号丢失  0: 正常 | 1：异常',
           `occlude` int NOT NULL DEFAULT '0' COMMENT '遮挡  0: 正常 | 1：异常',
           `brightness` int NOT NULL DEFAULT '0' COMMENT '亮度  0: 正常 | 1：异常',
           `color_cast` int NOT NULL DEFAULT '0' COMMENT '偏色  0: 正常 | 1：异常',
           `snowflake` int NOT NULL DEFAULT '0' COMMENT '雪花  0: 正常 | 1：异常',
           `stripe` int NOT NULL DEFAULT '0' COMMENT '条纹  0: 正常 | 1：异常',
           `contrast` int NOT NULL DEFAULT '0' COMMENT '对比度  0: 正常 | 1：异常',
           `blurry` int NOT NULL DEFAULT '0' COMMENT '模糊  0: 正常 | 1：异常',
           `equipment_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
           `alarm_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '报警/恢复时间',
           `is_alarm` int DEFAULT NULL COMMENT '报警状态标识',
           `detail_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '质量当前状态详情',
           PRIMARY KEY (`id`),
           KEY `idx_videodiagnosisrecord_updatetime` (`update_time`),
           KEY `idx_code` (`equipment_code`(191),`channel_code`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

drop table if exists mediator_north_error;
CREATE TABLE `mediator_north_error` (
     `id` bigint NOT NULL AUTO_INCREMENT,
     `create_time` datetime DEFAULT NULL COMMENT '数据生成时间',
     `error_data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '错误的json数据',
     `error_msg` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '错误描述',
     `interface_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '接口名称',
     `msg_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'msgId',
     `branch_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省份branchId',
     PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=19483123 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='北向接口_存储错误数据';

drop table if exists mediator_north_msg_id;
CREATE TABLE `mediator_north_msg_id` (
      `msg_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用msgId作为主键',
      `interface_path` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
      `interface_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
      `msg_date` datetime DEFAULT NULL,
      `ymd` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '年月日',
      PRIMARY KEY (`msg_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

drop table if exists mediator_category;
CREATE TABLE `mediator_category` (
      `id` bigint NOT NULL AUTO_INCREMENT,
      `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
      `parent_id` bigint DEFAULT '0' COMMENT '上级分类',
      `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
      `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
      `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
      `update_time` datetime DEFAULT NULL COMMENT '修改时间',
      `code` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'code',
      `deleted` int DEFAULT '0' COMMENT '删除',
      `level` int DEFAULT NULL COMMENT '层级',
      `type` bit(1) DEFAULT NULL COMMENT '0:消防,1:安防',
      `expire_day` int NOT NULL DEFAULT '0' COMMENT '过期天数',
      PRIMARY KEY (`id`) USING BTREE,
      KEY `idx_category_parentId` (`parent_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3713672266842158 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

delete  from mediator_category where id=3712199107215361;
INSERT INTO `mediator_category`(`id`, `name`, `parent_id`, `create_time`, `update_by`, `update_time`, `code`, `deleted`, `level`, `type`, `expire_day`) VALUES (3712199107215361, 'IOT BOX', 3712194117894144, NULL, NULL, NULL,  '1', 0, 2, NULL, 0);



drop table if exists mediator_alarm_code;
CREATE TABLE `mediator_alarm_code` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `parent_id` bigint DEFAULT NULL,
    `code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警代码',
    `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警名称',
    `create_time` datetime(6) DEFAULT NULL COMMENT '创建时间',
    `modified_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人名称',
    `update_time` datetime(6) DEFAULT NULL COMMENT '更新时间',
    `modified_id` bigint DEFAULT NULL COMMENT '最后修改人id',
    `deleted` int DEFAULT '0' COMMENT '删除',
    `auto_generate` int DEFAULT NULL COMMENT '是否自动生成运维单 1：是 0：否',
    `voice_alarm_times` int DEFAULT '0' COMMENT '声效告警提示次数',
    `light_alarm_times` int DEFAULT '0' COMMENT '光效告警提示次数',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


drop table if exists mediator_device_data;
CREATE TABLE `mediator_device_data` (
    `id` bigint NOT NULL,
    `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
    `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
    `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
    `update_time` datetime DEFAULT NULL COMMENT '修改时间',
    `org_id` bigint DEFAULT '0' COMMENT '机构',
    `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
    `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
    `device_id` bigint DEFAULT NULL COMMENT '设备id',
    `device_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '设备名称',
    `items` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '单设备类型上报的属性，参考下面属性数据',
    `multi` tinyint(1) DEFAULT NULL COMMENT '是否是多设备类型，如果为false，使用items\n属性(参考示例1)；如果true，使用\nmultiItems属性',
    `multi_items` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '多设备类型上报的属性，"键"为子设备\nid',
    `org_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '银行组织编码',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_user_orgId` (`org_id`) USING BTREE,
    KEY `idx_user_orgPath` (`org_path`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='设备数据表';

drop table if exists mediator_device_data_log;
CREATE TABLE `mediator_device_data_log` (
    `id` bigint NOT NULL,
    `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
    `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
    `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
    `update_time` datetime DEFAULT NULL COMMENT '修改时间',
    `org_id` bigint DEFAULT '0' COMMENT '机构',
    `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
    `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
    `device_id` bigint DEFAULT NULL COMMENT '设备id',
    `device_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '设备名称',
    `items` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '单设备类型上报的属性，参考下面属性数据',
    `multi` tinyint(1) DEFAULT NULL COMMENT '是否是多设备类型，如果为false，使用items\n属性(参考示例1)；如果true，使用\nmultiItems属性',
    `multi_items` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '多设备类型上报的属性，"键"为子设备\nid',
    `org_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '银行组织编码',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_user_orgId` (`org_id`) USING BTREE,
    KEY `idx_user_orgPath` (`org_path`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='设备数据日志表';


drop table if exists mediator_product;
CREATE TABLE `mediator_product` (
    `id` bigint NOT NULL,
    `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
    `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
    `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
    `update_time` datetime DEFAULT NULL COMMENT '修改时间',
    `interval_` bigint DEFAULT NULL COMMENT '时间间隔',
    `modify_time` datetime DEFAULT NULL COMMENT '修改时间',
    `multi` tinyint(1) DEFAULT '0' COMMENT '是否是多设备类型',
    `product_id` bigint DEFAULT NULL COMMENT '产品ID',
    `product_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '产品名称',
    `product_type` bigint DEFAULT NULL COMMENT '设备类型',
    `assets_type` bigint DEFAULT NULL COMMENT '资产类别',
    `STATUS` int DEFAULT '0' COMMENT '产品状态',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT=' 产品';

drop table if exists mediator_product_attributes;
CREATE TABLE `mediator_product_attributes` (
   `id` bigint NOT NULL,
   `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
   `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
   `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
   `update_time` datetime DEFAULT NULL COMMENT '修改时间',
   `attributes_id` bigint DEFAULT NULL COMMENT '产品属性ID',
   `code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '属性CODE',
   `modify_time` datetime DEFAULT NULL COMMENT '修改时间',
   `name_` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '属性名称',
   `product_id` bigint DEFAULT NULL COMMENT '产品ID',
   `specs` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '属性规格',
   `status` int DEFAULT '0' COMMENT '属性状态',
   `type_` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '属性类型',
   `type_desc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '属性介绍',
   `unit` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '属性单位',
   PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='产品属性';

drop table if exists mediator_product_device;
CREATE TABLE `mediator_product_device` (
   `id` bigint NOT NULL,
   `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
   `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
   `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
   `update_time` datetime DEFAULT NULL COMMENT '修改时间',
   `org_id` bigint DEFAULT '0' COMMENT '机构',
   `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
   `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
   `device_id` bigint DEFAULT NULL COMMENT '设备ID',
   `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '设备名称',
   `modify_time` datetime DEFAULT NULL COMMENT '修改时间',
   `product_id` bigint DEFAULT NULL COMMENT '产品ID',
   `status` int DEFAULT '0' COMMENT '设备状态',
   `brand_id` bigint DEFAULT NULL COMMENT '品牌id',
   `assets_type` bigint DEFAULT NULL COMMENT '资产类别',
   `product_type` bigint DEFAULT NULL COMMENT '设备类型',
   `termof_service` datetime DEFAULT NULL COMMENT '保修期限',
   `acceptof_date` datetime DEFAULT NULL COMMENT '验收日期',
   `position` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '安装位置',
   `type` tinyint DEFAULT '0' COMMENT '数据来源0web添加1北向',
   `online` tinyint DEFAULT '0' COMMENT '在线状态',
   `activestatus` tinyint DEFAULT '0' COMMENT '启用状态',
   `timeof_online` datetime DEFAULT NULL COMMENT '在线时间',
   `do_status` int DEFAULT '0' COMMENT '设备报警状态 0，正常1报警（待处理）2设备报修',
   `is_alarm` int DEFAULT '0' COMMENT '当天是否继续报警0是1否',
   `check_time` datetime DEFAULT NULL COMMENT '检查时间',
   `str_device_id` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警主机过来的设备id',
   `source` int DEFAULT '0' COMMENT '北向数据的来源0iot1报警主机',
   `battery` double(5,2) DEFAULT NULL COMMENT '剩余电量',
   `battery_voltage` double(5,2) DEFAULT NULL COMMENT '电池电压',
   `signal_strength` double(10,2) DEFAULT NULL COMMENT '信号强度',
   `off_time` datetime DEFAULT NULL COMMENT '离线时间',
   PRIMARY KEY (`id`) USING BTREE,
   KEY `idx_user_orgId` (`org_id`) USING BTREE,
   KEY `idx_user_orgPath` (`org_path`) USING BTREE,
   KEY `idx_str_device_id` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='产品设备表';

drop table if exists mediator_product_device_handle;
CREATE TABLE `mediator_product_device_handle` (
  `id` bigint NOT NULL,
  `device_id` bigint DEFAULT NULL COMMENT '设备id',
  `handle_time` datetime DEFAULT NULL COMMENT '处理时间',
  `check_time` datetime DEFAULT NULL COMMENT '检查时间',
  `status` int DEFAULT NULL COMMENT '处理结果2误报，3正常告警，4设备报修',
  `is_alarm` int DEFAULT NULL COMMENT '今天之内是否报警0否1是',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '描述',
  `repair_id` bigint DEFAULT NULL COMMENT '维修单id处理结果为报修时有值',
  `deal_batch` bit(1) DEFAULT b'0' COMMENT '是否批量处理告警,0:否,1:是',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='动环设备告警处理数据表';

drop table if exists mediator_product_device_log;
CREATE TABLE `mediator_product_device_log` (
   `id` bigint NOT NULL,
   `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
   `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
   `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
   `update_time` datetime DEFAULT NULL COMMENT '修改时间',
   `org_id` bigint DEFAULT '0' COMMENT '机构',
   `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
   `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
   `device_id` bigint DEFAULT NULL COMMENT '设备ID',
   `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '设备名称',
   `modify_time` datetime DEFAULT NULL COMMENT '修改时间',
   `product_device_id` bigint DEFAULT NULL COMMENT '产品设备ID',
   `product_id` bigint DEFAULT NULL COMMENT '产品ID',
   `status` int DEFAULT '0' COMMENT '设备状态',
   `assets_type` bigint DEFAULT NULL COMMENT '资产类别',
   `product_type` bigint DEFAULT NULL COMMENT '设备类型',
   PRIMARY KEY (`id`) USING BTREE,
   KEY `idx_user_orgId` (`org_id`) USING BTREE,
   KEY `idx_user_orgPath` (`org_path`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='产品设备日志表';

drop table if exists mediator_alarm_rule;
CREATE TABLE `mediator_alarm_rule` (
       `id` bigint NOT NULL DEFAULT '0',
       `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '规则名称',
       `enabled` int NOT NULL DEFAULT '0' COMMENT '是否启用',
       `isdeleted` int NOT NULL DEFAULT '0' COMMENT '是否删除',
       `type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'alarm：告警，inspection：一键巡检',
       `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注信息',
       `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
       `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
       `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
       `update_time` datetime DEFAULT NULL COMMENT '修改时间',
       PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='告警规则表';

drop table if exists mediator_alarm_rule_express;
CREATE TABLE `mediator_alarm_rule_express` (
       `id` bigint NOT NULL DEFAULT '0',
       `rule_id` bigint NOT NULL DEFAULT '0' COMMENT '规则编码',
       `fieldCode` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '属性code',
       `operator` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '操作符，GT(大于)、GTE(大于等于)、LT(小于)、LTE(小于等\r\n于)、EQUALS(等于)、STARTS_WITH(以什么开始)、\r\nENDS_WITH(以什么结束)、CONTAIN(包含)',
       `value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '对比值',
       `value_text` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '对比值',
       `source_type` int NOT NULL COMMENT '报警源类型：4：烟雾传感器；5： 温湿度采集器；6：水浸； 7：门磁、窗磁；8：卷帘门门磁；9：地磁；10燃气报警器；50：报警防区',
       `time_template_id` int DEFAULT NULL COMMENT '时间模板编码',
       `is_use_work_template` int DEFAULT NULL COMMENT '是否使用作息时间模板(0否1是)',
       `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
       `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
       `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
       `update_time` datetime DEFAULT NULL COMMENT '修改时间',
       PRIMARY KEY (`id`) USING BTREE,
       KEY `rule_id` (`rule_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='告警规则表达式表';

drop table if exists mediator_alarm_rule_source;
CREATE TABLE `mediator_alarm_rule_source` (
      `id` bigint NOT NULL,
      `org_id` bigint DEFAULT NULL COMMENT 'value_type=Device时有值为设备所属机构Id，否则为空',
      `rule_id` bigint DEFAULT NULL COMMENT '报警规则编码',
      `source_type` int DEFAULT NULL COMMENT '报警源类型：4:烟雾传感器；5:温湿度采集器；6:水浸,7:门磁:窗磁,8:卷帘门门磁；9:地磁,10:燃气报警器,50:报警防区',
      `value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'value_type=Device时有值为设备Id,否则为空',
      `value_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'AllDevice:所有设备，Device:单个设备',
      `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
      `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
      `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
      `update_time` datetime DEFAULT NULL COMMENT '修改时间',
      PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='告警规则与设备源关系表';

drop table if exists mediator_time_template;
CREATE TABLE `mediator_time_template` (
      `id` int NOT NULL,
      `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名称',
      `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '描述',
      `templateContent` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '时间模板内容，json 格式字符串',
      `enable` int NOT NULL DEFAULT '0' COMMENT '是否启用 0是1否',
      `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
      `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
      `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
      `update_time` datetime DEFAULT NULL COMMENT '修改时间',
      PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='时间模板表';

drop table if exists mediator_alarm_data;
CREATE TABLE `mediator_alarm_data` (
       `id` bigint NOT NULL,
       `rule_id` bigint DEFAULT NULL COMMENT '规则id',
       `device_id` bigint DEFAULT NULL COMMENT '设备id',
       `source_type` int DEFAULT NULL COMMENT '报警源类型(设备类型)：4：烟雾传感器；5： 温湿度采集器；6：水浸； 7：门磁、窗磁；8：卷帘门门磁；9：地磁；10燃气报警器；50：报警防区',
       `source_type_des` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警源类型(设备类型) 中文',
       `field_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '属性code',
       `time` datetime DEFAULT NULL COMMENT '告警时间',
       `operator` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作符，GT(大于)、GTE(大于等于)、LT(小于)、LTE(小于等	于)、EQUALS(等于)',
       `value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '对比值key',
       `value_text` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '对比值value',
       `content` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '告警内容',
       `alarm_value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '告警值',
       `is_do` int DEFAULT NULL COMMENT '0未处理1已处理',
       `do_time` datetime DEFAULT NULL COMMENT '处理时间',
       `do_type` tinyint DEFAULT '0' COMMENT '处理方式:0:为处理,1:(暂无)2:误报，3：正常告警，4：报修',
       PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='告警数据表';

drop table if exists mediator_alarm_data_newest;
CREATE TABLE `mediator_alarm_data_newest` (
      `id` bigint NOT NULL,
      `rule_id` bigint DEFAULT NULL COMMENT '规则id',
      `device_id` bigint DEFAULT NULL COMMENT '设备id',
      `source_type` int DEFAULT NULL COMMENT '报警源类型(设备类型)：4：烟雾传感器；5： 温湿度采集器；6：水浸； 7：门磁、窗磁；8：卷帘门门磁；9：地磁；10燃气报警器；50：报警防区',
      `source_type_des` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警源类型(设备类型) 中文',
      `field_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '属性code',
      `time` datetime DEFAULT NULL COMMENT '告警时间',
      `operator` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作符，GT(大于)、GTE(大于等于)、LT(小于)、LTE(小于等	于)、EQUALS(等于)',
      `value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '对比值key',
      `value_text` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '对比值value',
      `content` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '告警内容',
      `alarm_value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '告警值',
      `is_do` int DEFAULT NULL COMMENT '0未处理1已处理',
      `alarm_data_id` bigint NOT NULL COMMENT '原告警表的主键id',
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE KEY `deviceId_fieldCode_idx` (`device_id`,`field_code`),
      KEY `t_app_alarm_data_newest_device_id_time_index` (`device_id`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='动环设备最新告警数据表';


drop table if exists mediator_alarm_system_field;
CREATE TABLE `mediator_alarm_system_field` (
   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
   `source_type` int NOT NULL COMMENT '报警源类型(设备类型)：4：烟雾传感器；5： 温湿度采集器；6：水浸； 7：门磁、窗磁；8：卷帘门门磁；9：地磁；10燃气报警器；50：报警防区',
   `source_type_des` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警源类型(设备类型) 中文',
   `sys_field_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '系统属性code（报警类型编码,全表唯一）',
   `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '系统属性名称',
   `specs` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '属性规格信息，eg:{ \r\n"0": "门已关闭", \r\n"1": "门已打开" \r\n}',
   `operators` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '该属性拥有哪些操作符eg:{ \r\n"GT": "大于", \r\n"LT": "小于" \r\n}\r\n\r\nGT(大于)、GTE(大于等于)、LT(小于)、LTE(小于等\r\n于)、EQUALS(等于)、STARTS_WITH(以什么开始)、\r\nENDS_WITH(以什么结束)、CONTAIN(包含)',
   `type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据值类型，eg：”FLOAT“、"ENUM"',
   `type_des` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据值类型中午描述，eg：”浮点型“、"枚举"',
   `unit` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '属性单位，非必填',
   `enable` int NOT NULL DEFAULT '0' COMMENT '是否启用0 是1否',
   `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
   `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
   `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
   `update_time` datetime DEFAULT NULL COMMENT '修改时间',
   PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='系统报警属性字段表';

drop table if exists mediator_alarm_system_field_mapper_device;
CREATE TABLE `mediator_alarm_system_field_mapper_device` (
     `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
     `sys_field_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '系统属性code（报警类型编码,全表唯一）',
     `dev_field_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备属性编码',
     `dev_field_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备属性Id',
     `dev_field_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备属性名称',
     `ext` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '扩展字段后期可用存储系统属性值与设备值映射',
     `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
     `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
     `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
     `update_time` datetime DEFAULT NULL COMMENT '修改时间',
     PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='系统报警属性字段与设备上报数据属性关系表';

drop table if exists mediator_video_recorder_hard_disk_detection;
CREATE TABLE `mediator_video_recorder_hard_disk_detection` (
       `id` bigint NOT NULL,
       `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
       `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
       `update_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人名称',
       `update_time` datetime DEFAULT NULL COMMENT '修改时间',
       `org_id` bigint DEFAULT '0' COMMENT '机构',
       `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
       `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
       `alarm_time` datetime DEFAULT NULL COMMENT '报警/恢复时间',
       `detail_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '硬盘当前状态信息',
       `equipment_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机的唯一标识',
       `equipment_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机的名称',
       `status` int DEFAULT '0' COMMENT '报警状态',
       PRIMARY KEY (`id`) USING BTREE,
       KEY `idx_user_orgId` (`org_id`) USING BTREE,
       KEY `idx_user_orgPath` (`org_path`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT=' 录像机硬盘检测表';

drop table if exists mediator_video_recorder_hard_disk_detection_log;
CREATE TABLE `mediator_video_recorder_hard_disk_detection_log` (
       `id` bigint NOT NULL,
       `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
       `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
       `update_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人名称',
       `update_time` datetime DEFAULT NULL COMMENT '修改时间',
       `org_id` bigint DEFAULT '0' COMMENT '机构',
       `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
       `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
       `alarm_time` datetime DEFAULT NULL COMMENT '报警/恢复时间',
       `detail_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '硬盘当前状态信息',
       `equipment_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机的唯一标识',
       `equipment_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机的名称',
       `status` int DEFAULT '0' COMMENT '报警状态',
       `video_recorder_hard_disk_detection_id` bigint DEFAULT NULL COMMENT '录像机硬盘检测ID',
       PRIMARY KEY (`id`) USING BTREE,
       KEY `idx_user_orgId` (`org_id`) USING BTREE,
       KEY `idx_user_orgPath` (`org_path`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT=' 录像机硬盘检测日志表';

drop table if exists iot_defence_area_data;
CREATE TABLE `iot_defence_area_data` (
    `id` bigint NOT NULL,
    `organization_guid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '组织机构唯一标识',
    `defencearea_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警主机子系统唯一标识',
    `alarm_host_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警主机唯一标识',
    `input_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '传感器名称',
    `input_index` int DEFAULT NULL COMMENT '输入标识',
    `input_state` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'alarm:报警，bypass:旁路，normal:正常，activity：活动，unKnown：未知',
    `update_time` datetime DEFAULT NULL COMMENT '修改时间',
    `org_id` bigint DEFAULT NULL,
    `org_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `org_path` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `device_id` bigint DEFAULT NULL COMMENT '设备id',
    `str_device_id` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警主机过来的设备id',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `device_id` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='防区传感器报警信息，最新数据';

drop table if exists iot_defence_area_data_log;
CREATE TABLE `iot_defence_area_data_log` (
    `id` bigint NOT NULL,
    `organization_guid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '组织机构唯一标识',
    `defencearea_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警主机子系统唯一标识',
    `alarm_host_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警主机唯一标识',
    `input_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '传感器名称',
    `input_index` int DEFAULT NULL COMMENT '输入标识',
    `input_state` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'alarm:报警，bypass:旁路，normal:正常，activity：活动，unKnown：未知',
    `update_time` datetime DEFAULT NULL COMMENT '修改时间',
    `org_id` bigint DEFAULT NULL,
    `org_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `org_path` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `device_id` bigint DEFAULT NULL COMMENT '设备id',
    `sensor_data` bigint DEFAULT NULL COMMENT '设备id',
    `str_device_id` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警主机过来的设备id',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `index_deviceId` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='防区传感器报警信息，日志';

drop table if exists iot_defence_are_alarm_data;
CREATE TABLE `iot_defence_are_alarm_data` (
      `id` bigint NOT NULL,
      `organization_guid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '组织机构唯一标识',
      `defencearea_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警主机子系统唯一标识',
      `alarm_host_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警主机唯一标识',
      `input_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '传感器名称',
      `input_index` int DEFAULT NULL COMMENT '输入标识',
      `input_state` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'alarm:报警，bypass:旁路，normal:正常，activity：活动，unKnown：未知',
      `update_time` datetime DEFAULT NULL COMMENT '修改时间',
      `org_id` bigint DEFAULT NULL,
      `org_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
      `org_path` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
      `device_id` bigint DEFAULT NULL COMMENT '设备id',
      `str_device_id` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '报警主机过来的设备id',
      `isdo` int DEFAULT '0' COMMENT '处理状态 0:未处理,1:已处理(误报),2:已处理(无需处理),3:已处理(运维单)',
      PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='防区传感器报警信息报警表，记录报警';

drop table if exists mediator_network_device_detection;
CREATE TABLE `mediator_network_device_detection` (
     `id` bigint NOT NULL,
     `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
     `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
     `update_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人名称',
     `update_time` datetime DEFAULT NULL COMMENT '修改时间',
     `org_id` bigint DEFAULT '0' COMMENT '机构',
     `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
     `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
     `equipment_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机的唯一标识',
     `equipment_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机的名称',
     `status` int DEFAULT '0' COMMENT '网络状态',
     PRIMARY KEY (`id`) USING BTREE,
     KEY `idx_user_orgId` (`org_id`) USING BTREE,
     KEY `idx_user_orgPath` (`org_path`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT=' 网络设备检测';

drop table if exists mediator_network_device_detection_log;
CREATE TABLE `mediator_network_device_detection_log` (
     `id` bigint NOT NULL,
     `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
     `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
     `update_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人名称',
     `update_time` datetime DEFAULT NULL COMMENT '修改时间',
     `org_id` bigint DEFAULT '0' COMMENT '机构',
     `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
     `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
     `network_device_detection_id` bigint DEFAULT NULL COMMENT '网络设备检测ID',
     `equipment_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机的唯一标识',
     `equipment_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机的名称',
     `status` int DEFAULT '0' COMMENT '网络状态',
     PRIMARY KEY (`id`) USING BTREE,
     KEY `idx_user_orgId` (`org_id`) USING BTREE,
     KEY `idx_user_orgPath` (`org_path`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT=' 网络设备检测日志';