DROP TABLE IF EXISTS t_inspect_report;
CREATE TABLE t_inspect_report
(
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  status VARCHAR(16) NOT NULL COMMENT '报表状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT NOT NULL COMMENT '创建者ID',
  mender_id BIGINT COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT COMMENT '任务类型',
  inspect_id BIGINT NOT NULL COMMENT '巡检ID',
  period VARCHAR(100) NOT NULL COMMENT '报表周期',
  choose VARCHAR(100) NOT NULL COMMENT '今日/次日 本周/下周 本月/次月',
  week INT COMMENT '周/星期',
  day INT COMMENT '月/号',
  generate_time VARCHAR(255) NOT NULL COMMENT '生成时间',
  type VARCHAR(100) NOT NULL COMMENT '报表类型',
  template VARCHAR(100) COMMENT '报表模板',
  send_msg LONGTEXT NOT NULL COMMENT '发送人信息',
  title VARCHAR(255) COMMENT '报表标题',
  base_msg LONGTEXT COMMENT '基础信息',
  list_msg LONGTEXT COMMENT '列表信息',
  template_id VARCHAR(255) COMMENT '报表模型',
  PRIMARY KEY (id)
) COMMENT ='巡检报表表';

DROP TABLE IF EXISTS t_inspect_record;
CREATE TABLE t_inspect_record
(
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  key_stamp VARCHAR(100),
  routine_inspect_id BIGINT,
  timing_inspect_id BIGINT,
  total_time DOUBLE,
  type INT,
  user_name VARCHAR(255),
  warning BIT(1),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT NOT NULL COMMENT '创建者ID',
  mender_id BIGINT COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT COMMENT '任务类型',
  inspect_category VARCHAR(255) COMMENT '巡检分类',
  application_name VARCHAR(255),
  PRIMARY KEY (id)
) COMMENT ='巡检记录表';

DROP TABLE IF EXISTS compliance_record;
CREATE TABLE compliance_record
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  key_stamp VARCHAR(100),
  routine_check_id BIGINT(20),
  timing_check_id BIGINT(20),
  total_time DOUBLE,
  type INT(11),
  user_name VARCHAR(255),
  warning BIT(1),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) NOT NULL COMMENT '创建者ID',
  mender_id BIGINT(20) COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT(20) COMMENT '任务类型',
  compliance_Category VARCHAR(255) COMMENT '合规分类',
  item_num INT(20) COMMENT '基线检查项数量',
  host_num INT(20) COMMENT '服务器数量',
  no_pass_host_num INT(20) COMMENT '不合规服务器数量',
  no_pass_item_num INT(20) COMMENT '不合规基线项数量',
  pass_rates VARCHAR(255) COMMENT '合规通过率',
  application_name VARCHAR(255) COMMENT '应用系统名称',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS t_inspect_template;
CREATE TABLE t_inspect_template
(
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT NOT NULL COMMENT '创建者ID',
  mender_id BIGINT COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT COMMENT '任务类型',
  modifier_name VARCHAR(255),
  creator_name VARCHAR(255),
  department_code VARCHAR(20) COMMENT '数据所属部门',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS t_inspect_item;
CREATE TABLE t_inspect_item
(
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  approval_message VARCHAR(255),
  description VARCHAR(255),
  item_param VARCHAR(255),
  script_type VARCHAR(255),
  script_content TEXT,
  sensitive_param BIT(1),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT NOT NULL COMMENT '创建者ID',
  mender_id BIGINT COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  creator_name VARCHAR(255),
  modifier_name VARCHAR(255),
  owner_id BIGINT COMMENT '任务类型',
  timeout INT,
  script_param LONGTEXT COMMENT '输入参数',
  target_type VARCHAR(255) COMMENT '指标分类',
  target_rule LONGTEXT COMMENT '指标规则',
  script_id BIGINT COMMENT '脚本id',
  script_name VARCHAR(255) COMMENT '脚本名称',
  multi_compare TINYINT(1),
  department_code VARCHAR(20) COMMENT '数据所属部门',
  group_id VARCHAR(255) COMMENT '脚本分组信息',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS t_routine_inspect;
CREATE TABLE t_routine_inspect
(
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT NOT NULL COMMENT '创建者ID',
  mender_id BIGINT COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT COMMENT '任务类型',
  creator_name VARCHAR(255),
  modifier_name VARCHAR(255),
  type VARCHAR(255) COMMENT '巡检分类',
  cmdb_name VARCHAR(255) COMMENT '应用系统名称',
  inspect_category VARCHAR(255),
  application_name VARCHAR(255),
  department_code VARCHAR(20) COMMENT '数据所属部门',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS t_timing_inspect;
CREATE TABLE t_timing_inspect
(
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  cron VARCHAR(255),
  routine_inspect_id BIGINT,
  running BIT(1),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT NOT NULL COMMENT '创建者ID',
  mender_id BIGINT COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT COMMENT '任务类型',
  creator_name VARCHAR(255),
  modifier_name VARCHAR(255),
  inspect_category VARCHAR(255),
  application_name VARCHAR(255),
  department_code VARCHAR(20) COMMENT '数据所属部门',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS t_inspect_warn_level;
CREATE TABLE t_inspect_warn_level
(
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  status VARCHAR(16) COMMENT '状态',
  warn_level VARCHAR(16) COMMENT '告警等级',
  color VARCHAR(16) COMMENT '告警颜色',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT NOT NULL COMMENT '创建者ID',
  mender_id BIGINT COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT COMMENT '任务类型',
  creator_name VARCHAR(255),
  modifier_name VARCHAR(255),
  department_code VARCHAR(20) COMMENT '数据所属部门',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS t_inspect_item_warn;
CREATE TABLE t_inspect_item_warn
(
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT NOT NULL COMMENT '创建者ID',
  mender_id BIGINT COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT COMMENT '任务类型',
  warn_id BIGINT COMMENT '告警模板ID',
  item_id BIGINT COMMENT '巡检项ID',
  creator_name VARCHAR(255),
  modifier_name VARCHAR(255),
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS t_ref_routine_inspect_group_to_inspect_item;
CREATE TABLE t_ref_routine_inspect_group_to_inspect_item
(
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  inspect_item_id BIGINT,
  routine_inspect_group_id BIGINT,
  routine_inspect_id BIGINT,
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT NOT NULL COMMENT '创建者ID',
  mender_id BIGINT COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT COMMENT '任务类型',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS t_inspect_item_template;
CREATE TABLE t_inspect_item_template
(
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT NOT NULL COMMENT '创建者ID',
  mender_id BIGINT COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT COMMENT '任务类型',
  template_id BIGINT COMMENT '巡检模板ID',
  item_id BIGINT COMMENT '巡检项ID',
  PRIMARY KEY (id)
);

-- 2021/12/20
DROP TABLE IF EXISTS compliance_routine_check;
CREATE TABLE compliance_routine_check
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) NOT NULL COMMENT '创建者ID',
  mender_id BIGINT(20) COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT(20) COMMENT '任务类型',
  creator_name VARCHAR(255),
  modifier_name VARCHAR(255),
  compliance_category VARCHAR(255) COMMENT '合规分类',
  application_name VARCHAR(255) COMMENT '应用系统名称',
  department_code VARCHAR(20) COMMENT '数据所属部门',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS compliance_timing;
CREATE TABLE compliance_timing
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  cron VARCHAR(255),
  routine_compliance_id BIGINT(20),
  running BIT(1),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) NOT NULL COMMENT '创建者ID',
  mender_id BIGINT(20) COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT(20) COMMENT '任务类型',
  compliance_category VARCHAR(255) COMMENT '合规分类',
  application_name VARCHAR(255) COMMENT '应用系统名称',
  creator_name VARCHAR(255),
  modifier_name VARCHAR(255),
  department_code VARCHAR(20) COMMENT '数据所属部门',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS compliance_item;
CREATE TABLE compliance_item
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  approval_message VARCHAR(255),
  description VARCHAR(255),
  item_param VARCHAR(255),
  script_type VARCHAR(255),
  script_content TEXT,
  sensitive_param BIT(1),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) NOT NULL COMMENT '创建者ID',
  mender_id BIGINT(20) COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  creator_name VARCHAR(255),
  modifier_name VARCHAR(255),
  owner_id BIGINT(20) COMMENT '任务类型',
  timeout INT(11),
  multi_compare TINYINT(1),
  item_type VARCHAR(255) COMMENT '基线分类',
  compliance_rule LONGTEXT COMMENT '基线规则',
  script_id BIGINT(20) COMMENT '脚本id',
  script_param LONGTEXT COMMENT '输入参数',
  script_name VARCHAR(255) COMMENT '脚本名称',
  department_code VARCHAR(20) COMMENT '数据所属部门',
  group_id VARCHAR(255) COMMENT '脚本分组信息',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS compliance_template;
CREATE TABLE compliance_template
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) NOT NULL COMMENT '创建者ID',
  mender_id BIGINT(20) COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT(20) COMMENT '任务类型',
  creator_name VARCHAR(255),
  modifier_name VARCHAR(255),
  department_code VARCHAR(20) COMMENT '数据所属部门',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS tbl_copdb_task;
CREATE TABLE tbl_copdb_task
(
  id INT(10) NOT NULL AUTO_INCREMENT,
  taskname VARCHAR(255) DEFAULT '' COMMENT '任务名称',
  tasktype VARCHAR(255) DEFAULT '' COMMENT '业务类型： 每种业务操作都是一种业务类型，比如文件备份恢复、启动项管理、…',
  status VARCHAR(255) COMMENT '0：待提交1：待审核 2：已审核  3：撤销 4：删除 ',
  createtime DATETIME COMMENT '创建时间',
  createuser VARCHAR(255) COMMENT '任务创建者',
  updatetime DATETIME,
  updateuser VARCHAR(255),
  rawtaskid INT(64) COMMENT '修改前taskid',
  remark VARCHAR(255) COMMENT '任务描述',
  isconfirm INT(1) COMMENT '是否确认 0：我已确认；1：我未确认',
  description LONGTEXT,
  name VARCHAR(255),
  iscompleted CHAR(3),
  isupload VARCHAR(5) DEFAULT '0',
  subtasks VARCHAR(4096),
  filepath VARCHAR(256),
  target LONGTEXT,
  execute_type VARCHAR(255),
  authen_mode VARCHAR(255),
  sudo VARCHAR(255),
  ident_id VARCHAR(255),
  account VARCHAR(255),
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS tbl_copdb_taskdetail;
CREATE TABLE tbl_copdb_taskdetail
(
  id INT(64) NOT NULL AUTO_INCREMENT,
  taskid INT(64),
  deviceid VARCHAR(255) COMMENT '设备id，设备唯一标识',
  agentip VARCHAR(255) COMMENT '设备ip',
  runresult LONGTEXT COMMENT '执行结果',
  runstatus VARCHAR(255) COMMENT '0：待执行  1：执行中 2：已执行 4：已删除 3：任务暂停',
  PRIMARY KEY (id),
  KEY INDEX_TASKDETAIL_TASKID (taskid),
  KEY INDEX_TASKDETAIL_DEVICEID (deviceid)
);

DROP TABLE IF EXISTS tbl_copdb_taskrule;
CREATE TABLE tbl_copdb_taskrule
(
  id INT(128) NOT NULL AUTO_INCREMENT,
  taskid INT(128),
  propertyid INT(128),
  value VARCHAR(2048),
  PRIMARY KEY (id),
  KEY INDEX_TASKRULE_TASKID (taskid)
);

DROP TABLE IF EXISTS tbl_copdb_standardfile;
CREATE TABLE tbl_copdb_standardfile
(
  id INT(64) NOT NULL AUTO_INCREMENT,
  filename VARCHAR(255) COMMENT '标准文件名称',
  createtime DATETIME,
  updatetime DATETIME,
  type VARCHAR(8) COMMENT '0 对比文件 1 软件包名称',
  filePath VARCHAR(255),
  creator VARCHAR(255),
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS tbl_copdb_device;
CREATE TABLE tbl_copdb_device
(
  ID BIGINT(20) NOT NULL AUTO_INCREMENT,
  deviceID VARCHAR(64) NOT NULL COMMENT '设备id',
  deviceTemplateID BIGINT(20),
  deviceName VARCHAR(100),
  cmdbDeviceName VARCHAR(100),
  updateDate DATETIME,
  updateUser VARCHAR(100),
  createDate DATETIME,
  createUser VARCHAR(100),
  remark VARCHAR(255),
  status INT(11),
  location VARCHAR(100),
  manageIP VARCHAR(100),
  deviceType INT(11) COMMENT '1: 物理机、\r\n            2:虚拟机、\r\n            3:交换机、\r\n            4:加密机、\r\n            5:负载均衡设备、\r\n            6:路由器、\r\n            7 :存储设备（NAS）、\r\n            8:应用系统、\r\n            9:应用子系统 、\r\n            10:db、\r\n            11: 容器 、\r\n            12:Pool\r\n            0:机房',
  source INT(11) COMMENT '属性来源  0：云运营平台（本系统）  1：CMDB、 2：ansible或SNMP   3：云资源管理平台\r\n            4：容器管理平台  5：分布存储管理平台  6：大数据管理平台',
  img VARCHAR(255),
  cols VARCHAR(50),
  rows VARCHAR(50) COMMENT '行',
  layer VARCHAR(50) COMMENT '层',
  ifsystem TINYINT(255),
  isdmz TINYINT(4) DEFAULT '0' COMMENT '0.非dmz 1.dmz',
  PRIMARY KEY (ID),
  KEY deviceType (deviceType),
  KEY INDEX_DEVICE_DEVICEID (deviceID)
);

DROP TABLE IF EXISTS tbl_copdb_devicegroup;
CREATE TABLE tbl_copdb_devicegroup
(
  id INT(11) NOT NULL AUTO_INCREMENT COMMENT ' 自增长健',
  groupname VARCHAR(255),
  createuser VARCHAR(255),
  createtime DATETIME,
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS tbl_copdb_taskresult;
CREATE TABLE tbl_copdb_taskresult
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  taskdetailid BIGINT(20),
  result VARCHAR(1024),
  filename VARCHAR(255),
  updatetime VARCHAR(255),
  delta VARCHAR(1024),
  recoverystatus VARCHAR(1024),
  deviceName VARCHAR(100),
  backuptype VARCHAR(1024),
  agentip VARCHAR(100),
  taskid VARCHAR(255),
  runstatus VARCHAR(255),
  status VARCHAR(1024),
  datasync INT(255),
  cid VARCHAR(255),
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS t_routine_inspect_group;
CREATE TABLE t_routine_inspect_group
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  ip_list_text VARCHAR(255),
  os_type INT(11),
  routine_inspect_id BIGINT(20),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) NOT NULL COMMENT '创建者ID',
  mender_id BIGINT(20) COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  target LONGTEXT,
  owner_id BIGINT(20) COMMENT '任务类型',
  timeout INT(11),
  execute_type VARCHAR(255),
  custom_params LONGTEXT,
  jump_server LONGTEXT,
  is_jump_server TINYINT(1),
  resource_type VARCHAR(255),
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS t_inspect_record_item;
CREATE TABLE t_inspect_record_item
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  group_name VARCHAR(255),
  inspect_item_id BIGINT(20),
  inspect_item_name VARCHAR(255),
  ip VARCHAR(255),
  job_instance_id BIGINT(20),
  log TEXT,
  record_id BIGINT(20),
  script_error_info TEXT,
  total_time DOUBLE,
  warning BIT(1),
  warning_info LONGTEXT,
  status VARCHAR(16) COMMENT '状态',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) NOT NULL COMMENT '创建者ID',
  mender_id BIGINT(20) COMMENT '修改者ID',
  owner_id BIGINT(20) COMMENT '任务类型',
  inspect_result LONGTEXT,
  max_warning VARCHAR(255),
  resource_id BIGINT(20),
  resource_name VARCHAR(255),
  is_jump_server TINYINT(1),
  jump_server_ip VARCHAR(255),
  target_rule TEXT,
  result LONGTEXT,
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS tbl_copdb_whitelist;
CREATE TABLE tbl_copdb_whitelist
(
  id INT(11) NOT NULL AUTO_INCREMENT,
  wlist VARCHAR(255),
  createtime DATETIME,
  createuser VARCHAR(255),
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS compliance_item_template;
CREATE TABLE compliance_item_template
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) NOT NULL COMMENT '创建者ID',
  mender_id BIGINT(20) COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT(20) COMMENT '任务类型',
  template_id BIGINT(20) COMMENT '合规模板ID',
  item_id BIGINT(20) COMMENT '合规基线ID',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS compliance_routine_compliance_group;
CREATE TABLE compliance_routine_compliance_group
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  ip_list_text VARCHAR(255),
  os_type INT(11),
  routine_compliance_id BIGINT(20),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) NOT NULL COMMENT '创建者ID',
  mender_id BIGINT(20) COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  target LONGTEXT,
  owner_id BIGINT(20) COMMENT '任务类型',
  timeout INT(11),
  execute_type VARCHAR(255),
  custom_params LONGTEXT,
  is_jump_server TINYINT(1) COMMENT '是否使用跳板机',
  jump_server LONGTEXT COMMENT '跳板机信息',
  resource_type VARCHAR(255),
  resource_id BIGINT(20),
  resource_name VARCHAR(255),
  jump_server_ip VARCHAR(255),
  target_rule TEXT,
  result LONGTEXT,
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS compliance_routine_group_to_compliance_item;
CREATE TABLE compliance_routine_group_to_compliance_item
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  compliance_item_id BIGINT(20),
  routine_compliance_group_id BIGINT(20),
  routine_compliance_id BIGINT(20),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) NOT NULL COMMENT '创建者ID',
  mender_id BIGINT(20) COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT(20) COMMENT '任务类型',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS compliance_record_item;
CREATE TABLE compliance_record_item
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  group_name VARCHAR(255),
  compliance_item_id BIGINT(20) COMMENT '基线ID',
  compliance_item_name VARCHAR(255) COMMENT '基线项名称',
  ip VARCHAR(255) COMMENT '主机ip',
  job_instance_id BIGINT(20),
  log TEXT,
  record_id BIGINT(20),
  script_error_info TEXT,
  total_time DOUBLE,
  pass BIT(1) COMMENT '是否合规',
  compliance_result LONGTEXT,
  compliance_rule VARCHAR(255) COMMENT '基线标准',
  warning_info VARCHAR(255) COMMENT '错误信息',
  status VARCHAR(16) COMMENT '状态',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) NOT NULL COMMENT '创建者ID',
  mender_id BIGINT(20) COMMENT '修改者ID',
  owner_id BIGINT(20) COMMENT '任务类型',
  max_warning VARCHAR(255),
  resource_id BIGINT(20),
  resource_name VARCHAR(255),
  is_jump_server TINYINT(1),
  jump_server_ip VARCHAR(255),
  target_rule TEXT,
  result LONGTEXT,
  PRIMARY KEY (id)
);

ALTER TABLE tbl_copdb_task
  MODIFY COLUMN id BIGINT(0) NOT NULL AUTO_INCREMENT FIRST,
  MODIFY COLUMN tasktype INT(11) NULL COMMENT '业务类型： 每种业务操作都是一种业务类型，比如文件备份恢复、启动项管理、…' AFTER taskname,
  MODIFY COLUMN status INT(11) NULL COMMENT '0：待提交1：待审核 2：已审核  3：撤销 4：删除 ' AFTER tasktype,
  MODIFY COLUMN rawtaskid INT(11) NULL COMMENT '修改前taskid' AFTER updateuser,
  MODIFY COLUMN isconfirm INT(11) NULL COMMENT '是否确认 0：我已确认；1：我未确认' AFTER remark;

ALTER TABLE tbl_copdb_task
  MODIFY COLUMN id INT(0) NOT NULL AUTO_INCREMENT FIRST;

ALTER TABLE tbl_copdb_task
  MODIFY COLUMN sudo TINYINT(1) NULL AFTER authen_mode;

ALTER TABLE tbl_copdb_task
  MODIFY COLUMN id BIGINT(0) NOT NULL AUTO_INCREMENT FIRST;

ALTER TABLE tbl_copdb_taskdetail
  MODIFY COLUMN taskid BIGINT(0) NULL AFTER id;

ALTER TABLE tbl_copdb_taskdetail
  MODIFY COLUMN id BIGINT(0) NOT NULL AUTO_INCREMENT FIRST;

ALTER TABLE tbl_copdb_taskrule
  MODIFY COLUMN id BIGINT(0) NOT NULL AUTO_INCREMENT FIRST;

ALTER TABLE tbl_copdb_taskrule
  MODIFY COLUMN taskid BIGINT(0) NULL AFTER id;

DROP TABLE IF EXISTS tbl_copdb_changeorder;
CREATE TABLE tbl_copdb_changeorder
(
  ID BIGINT(20) NOT NULL AUTO_INCREMENT,
  taskID BIGINT(20),
  createtime DATETIME,
  remark LONGTEXT,
  changeorderid VARCHAR(100),
  stepdescription LONGTEXT,
  workorderdecribe VARCHAR(500),
  workordertype VARCHAR(100),
  organizername VARCHAR(100),
  lastusermechanismid VARCHAR(100),
  threelevelname VARCHAR(100),
  updateDate DATETIME,
  statusName VARCHAR(50),
  createuserdepartmentid VARCHAR(50),
  lastuserbranchid VARCHAR(50),
  createusermechanismid VARCHAR(50),
  toconfigureid VARCHAR(50),
  emergencydegreestrname VARCHAR(50),
  envirronmentName VARCHAR(50),
  lastusergroupid VARCHAR(50),
  changeplanninggroupid VARCHAR(50),
  planningareaid VARCHAR(50),
  modification VARCHAR(50),
  changeplanninggroupname VARCHAR(50),
  onlinedate DATETIME,
  organizerid VARCHAR(50),
  lastuserdepartmentid VARCHAR(50),
  processinstid VARCHAR(50),
  lastuserdepartmentname VARCHAR(50),
  twolevelname VARCHAR(50),
  requesetid VARCHAR(50),
  createuserbranchid VARCHAR(50),
  createusername VARCHAR(50),
  status VARCHAR(50),
  expectedfinishtime DATETIME,
  createuserdepartmentname VARCHAR(50),
  requesetname VARCHAR(50),
  changesource VARCHAR(50),
  lastupdatetime VARCHAR(50),
  onelevelname VARCHAR(50),
  changesourcename VARCHAR(50),
  envirronment VARCHAR(50),
  PRIMARY KEY (ID)
);

ALTER TABLE tbl_copdb_changeorder
  CHANGE COLUMN ID id BIGINT(0) NOT NULL AUTO_INCREMENT FIRST,
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (id);

ALTER TABLE tbl_copdb_task
  MODIFY COLUMN iscompleted VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL AFTER name;

ALTER TABLE tbl_copdb_standardfile
  MODIFY COLUMN id BIGINT(0) NOT NULL AUTO_INCREMENT FIRST;

DROP TABLE IF EXISTS t_deploy_patch;
CREATE TABLE t_deploy_patch
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '任务名称',
  status VARCHAR(100) COMMENT '状态',
  type VARCHAR(255),
  execute_type VARCHAR(100) COMMENT '执行方式',
  execute_template BIGINT(20) COMMENT '执行模板',
  authen_mode VARCHAR(100) COMMENT '认证方式',
  sudo TINYINT(4) COMMENT '是否sudo',
  data LONGTEXT COMMENT '服务器集',
  choose_patch VARCHAR(255) COMMENT '选择补丁',
  patch_target LONGTEXT COMMENT '补丁列表',
  timing DATETIME COMMENT '定时时间',
  gmt_create DATETIME COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) COMMENT '创建人',
  mender_id BIGINT(20) COMMENT '修改人',
  is_deleted TINYINT(4),
  param LONGTEXT COMMENT '参数',
  is_timing TINYINT(4) COMMENT '是否是定时任务',
  instance_id BIGINT(20) COMMENT '任务实例id',
  remark VARCHAR(255),
  props VARCHAR(100),
  owner_id BIGINT(20),
  name VARCHAR(255) COMMENT '任务名称',
  choose_patch_path LONGTEXT,
  department_code VARCHAR(20) COMMENT '数据所属部门',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS t_deploy_patch_detail;
CREATE TABLE t_deploy_patch_detail
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) COMMENT '主机名称',
  host_ip VARCHAR(100) COMMENT '主机ip',
  patch_name VARCHAR(255) COMMENT '补丁名称',
  status VARCHAR(100) COMMENT '状态',
  error_info LONGTEXT COMMENT '报错信息',
  history_id BIGINT(20) COMMENT '历史id',
  gmt_create DATETIME,
  gmt_modify DATETIME,
  creator_id BIGINT(20),
  mender_id BIGINT(20),
  is_deleted TINYINT(4),
  node_id BIGINT(20),
  node_name VARCHAR(255) COMMENT '节点名称',
  owner_id BIGINT(20),
  remark VARCHAR(255),
  props VARCHAR(255),
  type VARCHAR(255),
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS t_deploy_patch_history;
CREATE TABLE t_deploy_patch_history
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) COMMENT '任务名称',
  type VARCHAR(100) COMMENT '类型',
  patch_num INT(11) COMMENT '补丁数',
  serve_num INT(11) COMMENT '服务器数',
  fail_success_total VARCHAR(100) COMMENT '失败-成功-总数',
  execute_template VARCHAR(255) COMMENT '编排模板',
  complete_time DATETIME COMMENT '完成时间',
  patch_id BIGINT(20) COMMENT '补丁表id',
  gmt_create DATETIME,
  gmt_modify DATETIME,
  creator_id BIGINT(20),
  mender_id BIGINT(20),
  is_deleted TINYINT(4),
  owner_id BIGINT(20),
  props VARCHAR(255),
  remark VARCHAR(255),
  status VARCHAR(255),
  department_code VARCHAR(20) COMMENT '数据所属部门',
  PRIMARY KEY (id)
);

ALTER TABLE t_inspect_record_item ADD job_node_id BIGINT;
ALTER TABLE compliance_record_item ADD job_node_id BIGINT;

DROP TABLE IF EXISTS t_inspect_record_warning;
CREATE TABLE t_inspect_record_warning
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  content TEXT,
  group_name VARCHAR(255),
  inspect_item_id BIGINT(20),
  inspect_item_name VARCHAR(255),
  ip VARCHAR(255),
  record_id BIGINT(20),
  status VARCHAR(16) COMMENT '状态',
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) NOT NULL COMMENT '创建者ID',
  mender_id BIGINT(20) COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT(20) COMMENT '任务类型',
  level INT(11),
  target_rule VARCHAR(255),
  inspect_key VARCHAR(255),
  value VARCHAR(255),
  value_name VARCHAR(255),
  resource_name VARCHAR(255),
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS t_inspect_report_detail;
CREATE TABLE t_inspect_report_detail
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  status VARCHAR(16),
  remark VARCHAR(255) COMMENT '描述',
  props VARCHAR(1024) COMMENT '其他属性',
  gmt_create DATETIME NOT NULL COMMENT '创建时间',
  gmt_modify DATETIME COMMENT '修改时间',
  creator_id BIGINT(20) NOT NULL COMMENT '创建者ID',
  mender_id BIGINT(20) COMMENT '修改者ID',
  is_deleted TINYINT(1) NOT NULL COMMENT '是否删除',
  owner_id BIGINT(20) COMMENT '任务类型',
  report_id BIGINT(20) NOT NULL COMMENT '报表ID',
  generate_time DATETIME NOT NULL COMMENT '生成时间',
  title VARCHAR(255) COMMENT '标题',
  base_msg LONGTEXT COMMENT '基础信息',
  list_msg LONGTEXT COMMENT '列表信息',
  PRIMARY KEY (id)
) COMMENT ='巡检报表详情表';


ALTER TABLE tbl_copdb_whitelist
MODIFY COLUMN id bigint(0) NOT NULL AUTO_INCREMENT FIRST;

CREATE TABLE tbl_copdb_taskinfo (
  id int(64) NOT NULL AUTO_INCREMENT,
  cid varchar(255) DEFAULT NULL COMMENT 'celery uuid',
  taskid int(32) DEFAULT NULL,
  tasktype varchar(255) DEFAULT NULL COMMENT '任务类型',
  runstatus varchar(255) DEFAULT NULL COMMENT '2.sucess. 1.pending  -1.failure',
  ansibleapi varchar(255) DEFAULT NULL COMMENT 'ansible服务ip',
  createtime datetime DEFAULT NULL,
  updatetime datetime DEFAULT NULL,
  qlogsid int(64) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=271 ;

ALTER TABLE tbl_copdb_taskinfo
MODIFY COLUMN id bigint(0) NOT NULL AUTO_INCREMENT FIRST;

ALTER TABLE tbl_copdb_taskinfo
MODIFY COLUMN taskid bigint(0) NULL DEFAULT NULL AFTER cid;

CREATE TABLE tbl_copdb_quartzjob (
  id int(64) NOT NULL AUTO_INCREMENT,
  createtime datetime DEFAULT NULL,
  updatetime datetime DEFAULT NULL,
  jobname varchar(255) DEFAULT NULL COMMENT 'taskid or name',
  jobstatus varchar(255) DEFAULT NULL COMMENT '0 不执行  1 执行',
  cronexpression varchar(255) DEFAULT NULL COMMENT 'cron',
  description longtext,
  methodname varchar(255) DEFAULT NULL COMMENT '执行方法名',
  jobid int(10) DEFAULT NULL COMMENT '定时任务id非主键id',
  jobgroup varchar(256) DEFAULT NULL COMMENT '定时任务分组',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=46 ;

ALTER TABLE tbl_copdb_quartzjob
MODIFY COLUMN id bigint(0) NOT NULL AUTO_INCREMENT FIRST;

ALTER TABLE tbl_copdb_taskinfo
MODIFY COLUMN tasktype int(255) NULL DEFAULT NULL COMMENT '任务类型' AFTER taskid;

ALTER TABLE tbl_copdb_taskresult
MODIFY COLUMN updatetime datetime(0) NULL DEFAULT NULL AFTER filename,
MODIFY COLUMN taskid bigint(255) NULL DEFAULT NULL AFTER agentip;

CREATE TABLE temp_file_copy (
  ip varchar(255) DEFAULT NULL,
  path varchar(255) DEFAULT NULL,
  task_id bigint(20) DEFAULT NULL,
  instance_id bigint(20) DEFAULT NULL
) ENGINE=InnoDB  ROW_FORMAT=COMPACT;

ALTER TABLE department
ADD COLUMN code varchar(255) NULL COMMENT '部门code' AFTER origin;

ALTER TABLE task_instance
ADD COLUMN department_code varchar(50) NULL COMMENT '数据所属部门' AFTER ros_id,
ADD COLUMN callback varchar(255) NULL AFTER department_code;

ALTER TABLE task_instance
ADD COLUMN source varchar(255) NULL COMMENT '任务来源：采集collection' AFTER callback;