-- 电池实时数据表（用于存储大量实时监测数据）
DROP TABLE IF EXISTS `bms_battery_data`;
CREATE TABLE `bms_battery_data` (
  `data_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '数据ID',
  `battery_id` bigint(20) NOT NULL COMMENT '电池ID',
  `box_id` bigint(20) NOT NULL COMMENT 'BOX设备ID（冗余字段，便于查询）',
  `battery_code` varchar(50) NOT NULL COMMENT '电池编号（冗余字段，便于查询）',
  `collect_time` datetime NOT NULL COMMENT '采集时间',
  `current_voltage` decimal(6,2) DEFAULT '0.00' COMMENT '当前电压(V)',
  `current_current` decimal(8,2) DEFAULT '0.00' COMMENT '当前电流(A)',
  `current_power` decimal(10,2) DEFAULT '0.00' COMMENT '当前功率(W)',
  `soc` decimal(5,2) DEFAULT '0.00' COMMENT '电量百分比(%)',
  `soh` decimal(5,2) DEFAULT '100.00' COMMENT '健康状态(%)',
  `remaining_capacity` decimal(8,2) DEFAULT '0.00' COMMENT '剩余容量(Ah)',
  `temperature` decimal(5,2) DEFAULT '0.00' COMMENT '温度(°C)',
  `internal_resistance` decimal(8,4) DEFAULT '0.0000' COMMENT '内阻(Ω)',
  `charge_cycles` int(11) DEFAULT '0' COMMENT '充电循环次数',
  `deep_discharge_count` int(11) DEFAULT '0' COMMENT '深度放电次数',
  `overcharge_count` int(11) DEFAULT '0' COMMENT '过充次数',
  `over_temperature_count` int(11) DEFAULT '0' COMMENT '过温次数',
  `runtime_status` tinyint(1) DEFAULT '1' COMMENT '运行状态（0离线 1正常 2充电中 3放电中 4故障 5维护）',
  `charge_status` tinyint(1) DEFAULT '0' COMMENT '充电状态（0未充电 1充电中 2充满 3充电故障）',
  `balance_status` tinyint(1) DEFAULT '0' COMMENT '均衡状态（0未均衡 1均衡中 2均衡完成）',
  `alarm_status` tinyint(1) DEFAULT '0' COMMENT '告警状态（0正常 1告警）',
  `alarm_info` text COMMENT '告警信息',
  `last_charge_time` datetime DEFAULT NULL COMMENT '最后充电时间',
  `last_discharge_time` datetime DEFAULT NULL COMMENT '最后放电时间',
  `last_balance_time` datetime DEFAULT NULL COMMENT '最后均衡时间',
  `data_source` varchar(50) DEFAULT 'MQTT' COMMENT '数据来源（MQTT、Modbus、CAN等）',
  `raw_data` text COMMENT '原始数据（JSON格式）',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`data_id`),
  KEY `idx_battery_id` (`battery_id`),
  KEY `idx_box_id` (`box_id`),
  KEY `idx_battery_code` (`battery_code`),
  KEY `idx_collect_time` (`collect_time`),
  KEY `idx_runtime_status` (`runtime_status`),
  KEY `idx_alarm_status` (`alarm_status`),
  KEY `idx_soc` (`soc`),
  KEY `idx_temperature` (`temperature`),
  KEY `idx_battery_collect_time` (`battery_id`, `collect_time`),
  CONSTRAINT `fk_battery_data_battery` FOREIGN KEY (`battery_id`) REFERENCES `bms_battery` (`battery_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='电池实时数据表';

-- 创建分区表（按月分区，提高查询性能）
-- ALTER TABLE `bms_battery_data` PARTITION BY RANGE (YEAR(collect_time)*100 + MONTH(collect_time)) (
--   PARTITION p202501 VALUES LESS THAN (202502),
--   PARTITION p202502 VALUES LESS THAN (202503),
--   PARTITION p202503 VALUES LESS THAN (202504),
--   PARTITION p202504 VALUES LESS THAN (202505),
--   PARTITION p202505 VALUES LESS THAN (202506),
--   PARTITION p202506 VALUES LESS THAN (202507),
--   PARTITION p202507 VALUES LESS THAN (202508),
--   PARTITION p202508 VALUES LESS THAN (202509),
--   PARTITION p202509 VALUES LESS THAN (202510),
--   PARTITION p202510 VALUES LESS THAN (202511),
--   PARTITION p202511 VALUES LESS THAN (202512),
--   PARTITION p202512 VALUES LESS THAN (202601),
--   PARTITION p_future VALUES LESS THAN MAXVALUE
-- );

-- 插入电池实时数据示例（最近24小时的数据）
INSERT INTO `bms_battery_data` VALUES 
-- BOX001-电池01的实时数据
(1, 1, 1, 'BAT001-01', '2025-01-22 10:30:00', 6.38, 1.2, 7.66, 85.2, 98.5, 85.2, 24.1, 0.0025, 156, 2, 0, 0, 2, 1, 0, 0, NULL, '2025-01-22 08:30:00', '2025-01-21 18:45:00', '2025-01-22 02:15:00', 'MQTT', '{"voltage":6.38,"current":1.2,"temperature":24.1,"soc":85.2}', '2025-01-22 10:30:00'),
(2, 1, 1, 'BAT001-01', '2025-01-22 10:25:00', 6.37, 1.1, 7.01, 85.0, 98.5, 85.0, 24.0, 0.0025, 156, 2, 0, 0, 2, 1, 0, 0, NULL, '2025-01-22 08:30:00', '2025-01-21 18:45:00', '2025-01-22 02:15:00', 'MQTT', '{"voltage":6.37,"current":1.1,"temperature":24.0,"soc":85.0}', '2025-01-22 10:25:00'),
(3, 1, 1, 'BAT001-01', '2025-01-22 10:20:00', 6.39, 1.3, 8.31, 85.4, 98.5, 85.4, 24.2, 0.0025, 156, 2, 0, 0, 2, 1, 0, 0, NULL, '2025-01-22 08:30:00', '2025-01-21 18:45:00', '2025-01-22 02:15:00', 'MQTT', '{"voltage":6.39,"current":1.3,"temperature":24.2,"soc":85.4}', '2025-01-22 10:20:00'),

-- BOX001-电池02的实时数据
(4, 2, 1, 'BAT001-02', '2025-01-22 10:30:00', 6.41, 1.3, 8.33, 86.1, 98.2, 86.1, 24.3, 0.0024, 156, 1, 0, 0, 2, 1, 0, 0, NULL, '2025-01-22 08:30:00', '2025-01-21 18:45:00', '2025-01-22 02:15:00', 'MQTT', '{"voltage":6.41,"current":1.3,"temperature":24.3,"soc":86.1}', '2025-01-22 10:30:00'),
(5, 2, 1, 'BAT001-02', '2025-01-22 10:25:00', 6.40, 1.2, 7.68, 85.9, 98.2, 85.9, 24.2, 0.0024, 156, 1, 0, 0, 2, 1, 0, 0, NULL, '2025-01-22 08:30:00', '2025-01-21 18:45:00', '2025-01-22 02:15:00', 'MQTT', '{"voltage":6.40,"current":1.2,"temperature":24.2,"soc":85.9}', '2025-01-22 10:25:00'),

-- BOX002-电池01的实时数据
(6, 9, 2, 'BAT002-01', '2025-01-22 10:25:00', 6.45, 1.5, 9.68, 92.1, 96.8, 92.1, 25.2, 0.0022, 89, 1, 0, 0, 2, 1, 0, 0, NULL, '2025-01-22 09:15:00', '2025-01-21 20:30:00', '2025-01-22 03:20:00', 'MQTT', '{"voltage":6.45,"current":1.5,"temperature":25.2,"soc":92.1}', '2025-01-22 10:25:00'),
(7, 9, 2, 'BAT002-01', '2025-01-22 10:20:00', 6.44, 1.4, 9.02, 91.9, 96.8, 91.9, 25.1, 0.0022, 89, 1, 0, 0, 2, 1, 0, 0, NULL, '2025-01-22 09:15:00', '2025-01-21 20:30:00', '2025-01-22 03:20:00', 'MQTT', '{"voltage":6.44,"current":1.4,"temperature":25.1,"soc":91.9}', '2025-01-22 10:20:00'),

-- BOX003-电池01的实时数据（故障状态）
(8, 21, 3, 'BAT003-01', '2025-01-22 09:45:00', 6.20, 0.0, 0.0, 78.5, 95.2, 78.5, 27.8, 0.0035, 234, 5, 1, 2, 4, 0, 0, 1, '电压过低告警', '2025-01-21 16:20:00', '2025-01-21 22:10:00', '2025-01-21 23:45:00', 'MQTT', '{"voltage":6.20,"current":0.0,"temperature":27.8,"soc":78.5,"alarm":"电压过低"}', '2025-01-22 09:45:00'),
(9, 21, 3, 'BAT003-01', '2025-01-22 09:40:00', 6.18, 0.0, 0.0, 78.3, 95.2, 78.3, 27.9, 0.0035, 234, 5, 1, 2, 4, 0, 0, 1, '电压过低告警', '2025-01-21 16:20:00', '2025-01-21 22:10:00', '2025-01-21 23:45:00', 'MQTT', '{"voltage":6.18,"current":0.0,"temperature":27.9,"soc":78.3,"alarm":"电压过低"}', '2025-01-22 09:40:00'),

-- BOX003-电池03的实时数据（高温故障）
(10, 23, 3, 'BAT003-03', '2025-01-22 09:45:00', 6.18, 0.0, 0.0, 77.8, 94.9, 77.8, 29.5, 0.0038, 234, 6, 2, 3, 4, 0, 0, 1, '温度过高告警,电压异常', '2025-01-21 16:20:00', '2025-01-21 22:10:00', '2025-01-21 23:45:00', 'MQTT', '{"voltage":6.18,"current":0.0,"temperature":29.5,"soc":77.8,"alarm":"温度过高,电压异常"}', '2025-01-22 09:45:00'),
(11, 23, 3, 'BAT003-03', '2025-01-22 09:40:00', 6.16, 0.0, 0.0, 77.6, 94.9, 77.6, 29.8, 0.0038, 234, 6, 2, 3, 4, 0, 0, 1, '温度过高告警,电压异常', '2025-01-21 16:20:00', '2025-01-21 22:10:00', '2025-01-21 23:45:00', 'MQTT', '{"voltage":6.16,"current":0.0,"temperature":29.8,"soc":77.6,"alarm":"温度过高,电压异常"}', '2025-01-22 09:40:00');