--#已更新
ALTER TABLE `newfiber_standard_termite`.`ter_project_info`
    ADD COLUMN `production_users` VARCHAR(512) NULL COMMENT '生产人员' AFTER `field_explain`,
ADD COLUMN `install_users` VARCHAR(512) NULL COMMENT '安装人员' AFTER `production_users`,
ADD COLUMN `analysis_users` VARCHAR(512) NULL COMMENT '分析人员' AFTER `install_users`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
    ADD COLUMN `category` VARCHAR(32) NULL DEFAULT 'project' COMMENT '分类（生产 production | 项目 project）' AFTER `id`;

ALTER TABLE `newfiber_standard_termite`.`ter_project_info`
    ADD COLUMN `center_point` VARCHAR(128) NULL COMMENT '地图中心点' AFTER `analysis_users`;

--#已更新 0527
ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
    ADD COLUMN `first_warn_datetime` DATETIME NULL COMMENT '首次报警时间（连续3次手动确认则报警）' AFTER `last_pic_time`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data`
    ADD COLUMN `heating` VARCHAR(1) NULL DEFAULT '2' COMMENT '是否加热（1是 | 2否）' AFTER `druing`;
ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data_realtime`
    ADD COLUMN `heating` VARCHAR(1) NULL DEFAULT '2' COMMENT '是否加热（1是 | 2否）' AFTER `druing`;
ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data`
    ADD COLUMN `druing_datetime` DATETIME NULL COMMENT '投药时间' AFTER `druing`,
ADD COLUMN `heating_datetime` DATETIME NULL COMMENT '加热时间' AFTER `heating`;
ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data_realtime`
    ADD COLUMN `druing_datetime` DATETIME NULL COMMENT '投药时间' AFTER `druing`,
ADD COLUMN `heating_datetime` DATETIME NULL COMMENT '加热时间' AFTER `heating`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
    ADD COLUMN `remark` VARCHAR(45) NULL AFTER `first_warn_datetime`,
ADD COLUMN `status` VARCHAR(16) NULL AFTER `remark`,
ADD COLUMN `create_by` VARCHAR(64) NULL AFTER `status`,
ADD COLUMN `create_time` DATETIME NULL AFTER `create_by`,
ADD COLUMN `update_by` VARCHAR(64) NULL AFTER `create_time`,
ADD COLUMN `update_time` DATETIME NULL AFTER `update_by`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data`
    ADD COLUMN `remark` VARCHAR(45) NULL AFTER `camera`,
ADD COLUMN `status` VARCHAR(16) NULL AFTER `remark`,
ADD COLUMN `create_by` VARCHAR(64) NULL AFTER `status`,
ADD COLUMN `create_time` DATETIME NULL AFTER `create_by`,
ADD COLUMN `update_by` VARCHAR(64) NULL AFTER `create_time`,
ADD COLUMN `update_time` DATETIME NULL AFTER `update_by`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data_realtime`
    ADD COLUMN `remark` VARCHAR(45) NULL AFTER `camera`,
ADD COLUMN `status` VARCHAR(16) NULL AFTER `remark`,
ADD COLUMN `create_by` VARCHAR(64) NULL AFTER `status`,
ADD COLUMN `create_time` DATETIME NULL AFTER `create_by`,
ADD COLUMN `update_by` VARCHAR(64) NULL AFTER `create_time`,
ADD COLUMN `update_time` DATETIME NULL AFTER `update_by`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data`
    ADD COLUMN `del_flag` CHAR(1) NULL AFTER `update_time`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data_realtime`
    ADD COLUMN `del_flag` CHAR(1) NULL AFTER `update_time`;

update ter_device_info
set del_flag = 0;
update ter_device_monitoring_data_realtime
set del_flag = 0;
update ter_device_monitoring_data
set del_flag = 0;

--已更新 0603
ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
    ADD COLUMN `software` varchar(255) DEFAULT NULL COMMENT '软件版本' AFTER `first_warn_datetime`,
 ADD COLUMN  `hardware` varchar(255) DEFAULT NULL COMMENT '硬件版本' AFTER `software`,
 ADD COLUMN  `voltage` varchar(255) DEFAULT NULL COMMENT '电压' AFTER `hardware`,
 ADD COLUMN  `temperature` varchar(255) DEFAULT NULL COMMENT '温度' AFTER `voltage`,
 ADD COLUMN  `rssi` varchar(100) DEFAULT NULL  COMMENT '信号质量' AFTER `temperature`,
 ADD COLUMN  `ap` varchar(100) DEFAULT NULL COMMENT '中继名称'  AFTER `rssi`,
 ADD COLUMN  `camera` varchar(100) DEFAULT NULL  COMMENT '摄像头' AFTER `ap`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
    ADD UNIQUE INDEX `index2` (`sn` ASC);

ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
    ADD COLUMN `extend_config` VARCHAR(1024) NULL COMMENT '扩展配置（JSON）' AFTER `lonandlat`,
CHANGE COLUMN `del_flag` `del_flag` VARCHAR(1) NULL DEFAULT '0' COMMENT '逻辑删除  0：否 1：是' AFTER `remark`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
    ADD COLUMN `sync_state` VARCHAR(32) NULL DEFAULT 'success' COMMENT '同步状态（成功 success | 失败 fail）' AFTER `camera`;

-- 已更新 0613
CREATE TABLE `thi_jingchu_device_config`
(
    `id`                  bigint(20) NOT NULL COMMENT '主键id',
    `sn`                  varchar(100) DEFAULT NULL COMMENT '设备sn',
    `order`               varchar(32)  DEFAULT NULL COMMENT '白蚁设备安装的点位顺序（自定义6位数）',
    `upload_confirm_flag` char(1)      DEFAULT '0' COMMENT '是否上传有白蚁数据（减少误报（0 否 | 1 是））',
    `remark`              varchar(45)  DEFAULT NULL,
    `status`              varchar(16)  DEFAULT NULL,
    `create_by`           varchar(64)  DEFAULT NULL,
    `create_time`         datetime     DEFAULT NULL,
    `update_by`           varchar(64)  DEFAULT NULL,
    `update_time`         datetime     DEFAULT NULL,
    `del_flag`            char(1)      DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `index2` (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='三方对接-荆楚平台设备信息';

CREATE TABLE `thi_jingchu_project_config`
(
    `id`           bigint(20) NOT NULL COMMENT '主键id',
    `project_code` varchar(100) DEFAULT NULL COMMENT '项目编号',
    `mn_no`        varchar(64)  DEFAULT NULL COMMENT '设备编码',
    `station_id`   int(11) DEFAULT NULL COMMENT '中心站地址',
    `station_name` varchar(128) DEFAULT NULL COMMENT '站点名称',
    `device_id`    int(11) DEFAULT NULL COMMENT '设备id',
    `device_name`  varchar(64)  DEFAULT NULL COMMENT '设备名称',
    `remark`       varchar(45)  DEFAULT NULL,
    `status`       varchar(16)  DEFAULT NULL,
    `create_by`    varchar(64)  DEFAULT NULL,
    `create_time`  datetime     DEFAULT NULL,
    `update_by`    varchar(64)  DEFAULT NULL,
    `update_time`  datetime     DEFAULT NULL,
    `del_flag`     char(1)      DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `index2` (`project_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='三方对接-荆楚平台项目信息';


-- 已更新 0826
ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
    ADD COLUMN `location_type` VARCHAR(45) NULL DEFAULT 'outer' COMMENT '位置类型（内圈 inner | 外圈 outer）' AFTER `project_code`;


-- 已更新 2024/11/06 加白蚁防治图片上传结果同步功能、项目添加oemConfigApi字段、设备添加上传周期
ALTER TABLE newfiber_standard_termite.ter_project_info ADD column `oem_config_api` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'OEM厂商的配置读取接口地址';
ALTER TABLE newfiber_standard_termite.ter_project_info CHANGE oem_config_api oem_config_api varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT 'OEM厂商的配置读取接口地址' AFTER heatmap;

ALTER TABLE newfiber_standard_termite.ter_device_info ADD column `upload_cycle_hour` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '防治图片上传周期（小时）';
ALTER TABLE newfiber_standard_termite.ter_device_info ADD column `last_sync_time` datetime COLLATE utf8mb4_bin DEFAULT NULL COMMENT '防治图片最后上传同步时间';
ALTER TABLE newfiber_standard_termite.ter_device_info ADD column `auto_heat` varchar(64) COLLATE utf8mb4_bin DEFAULT 'enable' COMMENT '默认enable 加热-enable | 不加热-disabled';
ALTER TABLE newfiber_standard_termite.ter_device_info CHANGE upload_cycle_hour upload_cycle_hour varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '防治图片上传周期（小时）' AFTER sync_state;
ALTER TABLE newfiber_standard_termite.ter_device_info CHANGE last_sync_time last_sync_time datetime NULL COMMENT '防治图片最后上传同步时间' AFTER upload_cycle_hour;
ALTER TABLE newfiber_standard_termite.ter_device_info CHANGE auto_heat auto_heat varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 'enable' NULL COMMENT '默认enable 加热-enable | 不加热-disabled' AFTER last_sync_time;

CREATE TABLE `ter_device_monitoring_sync_record` (
  `id` bigint(20) NOT NULL COMMENT '主键id',
  `sn` varchar(100) DEFAULT NULL COMMENT '设备sn',
  `upload_cycle_hour` varchar(64) DEFAULT NULL COMMENT '上传周期（小时）',
  `update_result` varchar(32) DEFAULT NULL COMMENT '上传状态 成功-success | 失败-fail',
  `uptime` datetime DEFAULT NULL COMMENT '上传时间',
  `picture_url` varchar(500) DEFAULT NULL COMMENT '成功后图片地址',
  `remark` varchar(45) DEFAULT NULL,
  `status` varchar(16) DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_by` varchar(64) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `del_flag` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='白蚁防治-监测图片上传结果记录表';