pipe-ner-server/pipe-network-service/sql/iot_equipment_management.sql

-- =============================================
-- 物联感知设备与管网基础设施管理
-- 包含：设备管理增强、管网基础信息、监控点管理、预警阈值
-- =============================================

-- -----------------------------------------------------
-- 1. 对现有表进行字段补充
-- -----------------------------------------------------

-- 补充设备基础表字段
ALTER TABLE `equipment_base` 
ADD COLUMN IF NOT EXISTS `equipment_location` VARCHAR(512) COMMENT '设备位置描述' AFTER `latitude`,
ADD COLUMN IF NOT EXISTS `install_time` DATETIME COMMENT '安装时间' AFTER `equipment_location`,
ADD COLUMN IF NOT EXISTS `maintainer` VARCHAR(128) COMMENT '设备维护人员' AFTER `install_time`,
ADD COLUMN IF NOT EXISTS `maintainer_phone` VARCHAR(32) COMMENT '联系方式' AFTER `maintainer`,
ADD COLUMN IF NOT EXISTS `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' AFTER `remark`,
ADD COLUMN IF NOT EXISTS `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' AFTER `create_time`;

-- 补充设备维护记录表字段
ALTER TABLE `equipment_maintain` 
ADD COLUMN IF NOT EXISTS `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' AFTER `maintain_remark`;

-- 补充设备状态表字段
ALTER TABLE `equipment_status` 
ADD COLUMN IF NOT EXISTS `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' AFTER `status_update_time`;

-- -----------------------------------------------------
-- 2. 管网基础信息表（5.2.4.1.2）
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pipe_network_base`;
CREATE TABLE `pipe_network_base` (
  `network_id` VARCHAR(64) NOT NULL COMMENT '管网设施ID（主键）',
  `network_code` VARCHAR(64) NOT NULL COMMENT '设施编码',
  `network_name` VARCHAR(256) NOT NULL COMMENT '设施名称',
  `network_type` VARCHAR(64) NOT NULL COMMENT '设施类型（GAS-燃气 DRAINAGE-排水 WATER_SUPPLY-供水 BRIDGE-桥梁 ROAD-道路等）',
  `network_level` VARCHAR(32) COMMENT '等级',
  `management_unit` VARCHAR(256) COMMENT '管养单位',
  `location` VARCHAR(512) COMMENT '位置描述',
  `longitude` DECIMAL(15, 10) COMMENT '经度',
  `latitude` DECIMAL(15, 10) COMMENT '纬度',
  `length` DECIMAL(10, 2) COMMENT '长度（米）',
  `diameter` DECIMAL(10, 2) COMMENT '管径（毫米）',
  `material` VARCHAR(128) COMMENT '材质',
  `build_date` DATE COMMENT '建设日期',
  `status` VARCHAR(32) DEFAULT 'NORMAL' COMMENT '状态（NORMAL-正常 MAINTENANCE-维护中 ABNORMAL-异常）',
  `remark` TEXT COMMENT '备注',
  `create_by` VARCHAR(128) COMMENT '创建人',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` VARCHAR(128) COMMENT '更新人',
  `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`network_id`),
  UNIQUE KEY `uk_network_code` (`network_code`),
  KEY `idx_network_type` (`network_type`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管网基础设施信息表';

-- -----------------------------------------------------
-- 3. 关键监控点位表（5.2.4.1.3）
-- -----------------------------------------------------
DROP TABLE IF EXISTS `monitor_point`;
CREATE TABLE `monitor_point` (
  `point_id` VARCHAR(64) NOT NULL COMMENT '监测点ID（主键）',
  `point_code` VARCHAR(64) NOT NULL COMMENT '监测点编码',
  `point_name` VARCHAR(256) NOT NULL COMMENT '监测点名称',
  `network_id` VARCHAR(64) COMMENT '关联管网设施ID',
  `point_type` VARCHAR(64) COMMENT '监测点类型（水位、流量、压力、温度等）',
  `location` VARCHAR(512) COMMENT '位置描述',
  `longitude` DECIMAL(15, 10) COMMENT '经度',
  `latitude` DECIMAL(15, 10) COMMENT '纬度',
  `installation_date` DATE COMMENT '安装日期',
  `status` VARCHAR(32) DEFAULT 'NORMAL' COMMENT '状态（NORMAL-正常 ABNORMAL-异常 OFFLINE-离线）',
  `remark` TEXT COMMENT '备注',
  `create_by` VARCHAR(128) COMMENT '创建人',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` VARCHAR(128) COMMENT '更新人',
  `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`point_id`),
  UNIQUE KEY `uk_point_code` (`point_code`),
  KEY `idx_network_id` (`network_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='关键监控点位表';

-- -----------------------------------------------------
-- 4. 设备-监控点关联表
-- -----------------------------------------------------
DROP TABLE IF EXISTS `equipment_point_rel`;
CREATE TABLE `equipment_point_rel` (
  `rel_id` VARCHAR(64) NOT NULL COMMENT '关联ID',
  `equipment_id` VARCHAR(64) NOT NULL COMMENT '设备ID',
  `point_id` VARCHAR(64) NOT NULL COMMENT '监测点ID',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`rel_id`),
  KEY `idx_equipment_id` (`equipment_id`),
  KEY `idx_point_id` (`point_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备-监控点关联表';

-- -----------------------------------------------------
-- 5. 补充现有预警阈值表字段
-- -----------------------------------------------------
-- 对已有的 warning_threshold 表补充字段（项目已有该表）
-- 注意：只保留阈值相关字段，不包含预警联动、通知等业务逻辑
ALTER TABLE `warning_threshold` 
ADD COLUMN IF NOT EXISTS `min_value` DECIMAL(10, 2) COMMENT '预警最小值，小于等于该值视为报警' AFTER `warning_code`,
ADD COLUMN IF NOT EXISTS `max_value` DECIMAL(10, 2) COMMENT '预警最大值，大于等于该值视为报警' AFTER `min_value`;

-- 移除旧字段（如果存在）
-- 注意：实际执行时请根据需要取消注释
-- ALTER TABLE `warning_threshold` DROP COLUMN IF EXISTS `warning_value`;
-- ALTER TABLE `warning_threshold` DROP COLUMN IF EXISTS `operator`;
-- ALTER TABLE `warning_threshold` DROP COLUMN IF EXISTS `device_name`;
-- ALTER TABLE `warning_threshold` DROP COLUMN IF EXISTS `device_type`;
-- ALTER TABLE `warning_threshold` DROP COLUMN IF EXISTS `equipment_id`;
-- ALTER TABLE `warning_threshold` DROP COLUMN IF EXISTS `point_id`;
-- ALTER TABLE `warning_threshold` DROP COLUMN IF EXISTS `monitor_type`;
-- ALTER TABLE `warning_threshold` DROP COLUMN IF EXISTS `warning_level`;

-- -----------------------------------------------------
-- 6. 设备测试记录表
-- -----------------------------------------------------
DROP TABLE IF EXISTS `equipment_test`;
CREATE TABLE `equipment_test` (
  `test_id` VARCHAR(64) NOT NULL COMMENT '测试记录ID',
  `equipment_id` VARCHAR(64) NOT NULL COMMENT '设备ID',
  `test_type` VARCHAR(64) COMMENT '测试类型（CONNECT-连接测试 DATA-数据传输 FUNCTION-功能测试）',
  `test_result` VARCHAR(32) COMMENT '测试结果（SUCCESS-成功 FAIL-失败）',
  `test_detail` TEXT COMMENT '测试详情',
  `test_time` DATETIME COMMENT '测试时间',
  `test_user` VARCHAR(128) COMMENT '测试人',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`test_id`),
  KEY `idx_equipment_id` (`equipment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备测试记录表';

-- -----------------------------------------------------
-- 7. 初始化数据
-- -----------------------------------------------------

-- 初始化管网设施类型字典
INSERT INTO `sys_dict_type` (`dict_id`, `dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES
(102, '管网设施类型', 'network_type', '0', 'admin', NOW(), '', NULL, '管网设施类型列表');

INSERT INTO `sys_dict_data` (`dict_code`, `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
(113, 1, '燃气管道', 'GAS', 'network_type', '', 'danger', 'N', '0', 'admin', NOW(), '', NULL, '燃气管道'),
(114, 2, '排水管道', 'DRAINAGE', 'network_type', '', 'primary', 'N', '0', 'admin', NOW(), '', NULL, '排水管道'),
(115, 3, '供水管道', 'WATER_SUPPLY', 'network_type', '', 'primary', 'N', '0', 'admin', NOW(), '', NULL, '供水管道'),
(116, 4, '热力管道', 'HEAT', 'network_type', '', 'warning', 'N', '0', 'admin', NOW(), '', NULL, '热力管道'),
(117, 5, '电力管道', 'ELECTRIC', 'network_type', '', 'info', 'N', '0', 'admin', NOW(), '', NULL, '电力管道'),
(118, 6, '通信管道', 'COMMUNICATION', 'network_type', '', 'info', 'N', '0', 'admin', NOW(), '', NULL, '通信管道'),
(119, 7, '桥梁', 'BRIDGE', 'network_type', '', 'default', 'N', '0', 'admin', NOW(), '', NULL, '桥梁'),
(120, 8, '道路', 'ROAD', 'network_type', '', 'default', 'N', '0', 'admin', NOW(), '', NULL, '道路');

-- 初始化监测点类型字典
INSERT INTO `sys_dict_type` (`dict_id`, `dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES
(103, '监测点类型', 'monitor_type', '0', 'admin', NOW(), '', NULL, '监测点类型列表');

INSERT INTO `sys_dict_data` (`dict_code`, `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
(121, 1, '水位监测', 'WATER_LEVEL', 'monitor_type', '', 'primary', 'N', '0', 'admin', NOW(), '', NULL, '水位监测'),
(122, 2, '流量监测', 'FLOW', 'monitor_type', '', 'primary', 'N', '0', 'admin', NOW(), '', NULL, '流量监测'),
(123, 3, '压力监测', 'PRESSURE', 'monitor_type', '', 'warning', 'N', '0', 'admin', NOW(), '', NULL, '压力监测'),
(124, 4, '温度监测', 'TEMPERATURE', 'monitor_type', '', 'info', 'N', '0', 'admin', NOW(), '', NULL, '温度监测'),
(125, 5, '气体监测', 'GAS', 'monitor_type', '', 'danger', 'N', '0', 'admin', NOW(), '', NULL, '气体监测'),
(126, 6, '视频监控', 'VIDEO', 'monitor_type', '', 'default', 'N', '0', 'admin', NOW(), '', NULL, '视频监控');