-- ========================================
-- FastBee 设备监测报告引擎 SQL 脚本
-- 创建日期: 2026-08-16
-- 说明:
--   1. iot_product 增加监测分类编码列
--   2. 新增 iot_things_threshold / report_record 两张表（Word 模板不入库，随代码发布）
--   3. oss_config 插入 MinIO 默认配置（存储决策：未购买云 OSS，统一使用自建 MinIO）
--   4. 历史报表菜单与按钮权限
-- 注意: 存量库执行前请先备份；增量脚本可重复执行（使用 / INSERT IGNORE）
-- ========================================

-- ------------------------------------------------------------
-- 1. iot_product 增加监测分类编码列（MySQL 8.0.29 以下不支持 加列，故直接 ALTER）
-- ------------------------------------------------------------
ALTER TABLE `iot_product` ADD COLUMN `monitor_category_code` VARCHAR(64) DEFAULT NULL COMMENT '监测分类编码（CRACK_METER/DEEP_DISPLACEMENT/AXIAL_FORCE/STATIC_LEVEL/VIBRATING_WIRE/STRAIN_GAUGE/WATER_LEVEL/RANGING_METER/INCLINOMETER/THERMOMETER/RAIN_GAUGE）' AFTER `category_name`;

-- ============================================================
-- 1. 设备级基准点（相对测量用）
--    静力水准等设备的读数是相对基准点的，累计变化量需按
--    「(当前读数 − 基准点当前读数) − (初始读数 − 基准点初始读数)」计算。
--    deviceId 全局唯一，因此天然支持跨网关引用
--    （如 GW2 的监测点引用 GW1 下的参考点）。
--    留空表示该设备为绝对量测量，不参与基准点换算。
-- ============================================================
ALTER TABLE iot_device
    ADD COLUMN baseline_device_id BIGINT NULL COMMENT '基准点设备ID（相对测量用，留空=绝对量）' AFTER serial_number;


-- 存量产品按产品名回填监测分类
UPDATE `iot_product` SET `monitor_category_code` = 'CRACK_METER'
WHERE (`monitor_category_code` IS NULL OR `monitor_category_code` = '')
  AND (`product_name` LIKE '%裂缝%' OR `product_name` LIKE '%测缝%');

UPDATE `iot_product` SET `monitor_category_code` = 'DEEP_DISPLACEMENT'
WHERE (`monitor_category_code` IS NULL OR `monitor_category_code` = '')
  AND (`product_name` LIKE '%位移%' OR `product_name` LIKE '%测斜%');

UPDATE `iot_product` SET `monitor_category_code` = 'AXIAL_FORCE'
WHERE (`monitor_category_code` IS NULL OR `monitor_category_code` = '')
  AND (`product_name` LIKE '%轴力%' OR `product_name` LIKE '%应力%' OR `product_name` LIKE '%锚索%' OR `product_name` LIKE '%钢筋计%');

UPDATE `iot_product` SET `monitor_category_code` = 'STATIC_LEVEL'
WHERE (`monitor_category_code` IS NULL OR `monitor_category_code` = '')
  AND (`product_name` LIKE '%水准%');

UPDATE `iot_product` SET `monitor_category_code` = 'VIBRATING_WIRE'
WHERE (`monitor_category_code` IS NULL OR `monitor_category_code` = '')
  AND (`product_name` LIKE '%振弦%');

UPDATE `iot_product` SET `monitor_category_code` = 'STRAIN_GAUGE'
WHERE (`monitor_category_code` IS NULL OR `monitor_category_code` = '')
  AND (`product_name` LIKE '%应变%');

UPDATE `iot_product` SET `monitor_category_code` = 'WATER_LEVEL'
WHERE (`monitor_category_code` IS NULL OR `monitor_category_code` = '')
  AND (`product_name` LIKE '%水位%');

UPDATE `iot_product` SET `monitor_category_code` = 'RANGING_METER'
WHERE (`monitor_category_code` IS NULL OR `monitor_category_code` = '')
  AND (`product_name` LIKE '%测距%' OR `product_name` LIKE '%激光测距%');

UPDATE `iot_product` SET `monitor_category_code` = 'INCLINOMETER'
WHERE (`monitor_category_code` IS NULL OR `monitor_category_code` = '')
  AND (`product_name` LIKE '%倾角%' OR `product_name` LIKE '%倾斜%');

UPDATE `iot_product` SET `monitor_category_code` = 'THERMOMETER'
WHERE (`monitor_category_code` IS NULL OR `monitor_category_code` = '')
  AND (`product_name` LIKE '%温度%');

UPDATE `iot_product` SET `monitor_category_code` = 'RAIN_GAUGE'
WHERE (`monitor_category_code` IS NULL OR `monitor_category_code` = '')
  AND (`product_name` LIKE '%雨量%');

-- ------------------------------------------------------------
-- 2.1 报告阈值表（查询优先级：device_id > product_id > 全局默认 product_id=0）
-- ------------------------------------------------------------
CREATE TABLE `iot_things_threshold` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `product_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '产品ID（0=全局默认）',
  `device_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '设备ID（0=不限定设备）',
  `monitor_category_code` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '监测分类编码',
  `metric_identifier` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT '物模型标识符',
  `warning_value` decimal(20,6) DEFAULT NULL COMMENT '预警值',
  `alarm_value` decimal(20,6) DEFAULT NULL COMMENT '报警值',
  `control_value` decimal(20,6) DEFAULT NULL COMMENT '控制值',
  `warning_rate` DECIMAL(18, 6) DEFAULT NULL COMMENT '变化速率预警值（单位/天）',
  `alarm_rate` DECIMAL(18, 6) DEFAULT NULL COMMENT '变化速率报警值（单位/天）' ,
  `control_rate` DECIMAL(18, 6) DEFAULT NULL COMMENT '变化速率控制值（单位/天）',
  `unit` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '单位',
  `is_primary` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否主监测指标（1=报告以此物模型作为监测指标）',
  `create_by` varchar(64) COLLATE utf8_unicode_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) COLLATE utf8_unicode_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_threshold_device` (`device_id`, `metric_identifier`) USING BTREE,
  KEY `idx_threshold_product` (`product_id`, `metric_identifier`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='报告结构化阈值';

-- ------------------------------------------------------------
-- 2.1.1 iot_things_threshold 主监测指标列
--       全新库：上面 CREATE TABLE 已含 is_primary 列；
--       存量库：由下面的语句补列。用 information_schema 做存在性判断，可重复执行
--
--       背景：「监测指标」原先只能由 PrimaryMetricResolver 按关键词推断，
--       物模型命名不规范时会误判（现场把 GPS「经度」选成了轴力计的监测指标）。
--       新增该列后，用户在阈值配置页显式勾选即可，显式标记优先于任何关键词推断。
-- ------------------------------------------------------------
SET @exist_col := (SELECT COUNT(1) FROM information_schema.COLUMNS
                   WHERE TABLE_SCHEMA = DATABASE()
                     AND TABLE_NAME = 'iot_things_threshold'
                     AND COLUMN_NAME = 'is_primary');
SET @ddl_sql := IF(@exist_col = 0,
    'ALTER TABLE `iot_things_threshold` ADD COLUMN `is_primary` tinyint(1) NOT NULL DEFAULT 0 COMMENT ''是否主监测指标（1=报告以此物模型作为监测指标）'' AFTER `unit`',
    'SELECT ''skip: iot_things_threshold.is_primary already exists'' AS info');
PREPARE ddl_stmt FROM @ddl_sql;
EXECUTE ddl_stmt;
DEALLOCATE PREPARE ddl_stmt;

-- ------------------------------------------------------------
-- 2.2 Word 模板（不再建 report_template 表）
--     模板作为代码资产放在 fastbee-report/src/main/resources/templates/
--     下（DEVICE_${CATEGORY}_V2.docx），由 DefaultWordTemplateResolver
--     从 classpath 直接加载，随版本发布，不需要数据库表。
-- ------------------------------------------------------------

-- ------------------------------------------------------------
-- 2.3 报告生成记录表（保留期由 LogCleanTask#cleanLogs(days) 统一管理，按 create_time 倒推清理）
-- ------------------------------------------------------------
CREATE TABLE `report_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `report_no` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT '报告编号，如 REPORT-202608160001',
  `device_id` bigint(20) NOT NULL COMMENT '设备ID',
  `device_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '设备名称',
  `monitor_category_code` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '监测分类编码',
  `period` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '周期（DAY/WEEK/HALF_MONTH/MONTH/CUSTOM）',
  `start_time` datetime DEFAULT NULL COMMENT '统计开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '统计结束时间',
  `status` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '状态（CREATING/SUCCESS/FAILED）',
  `file_name` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '文件名称',
  `file_url` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '文件地址',
  `error_message` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '失败原因',
  `request_json` varchar(2048) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '原始生成请求JSON（失败重生成用，保留 groupId/deviceIds 口径）',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户ID',
  `create_by` varchar(64) COLLATE utf8_unicode_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) COLLATE utf8_unicode_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_device_time` (`device_id`, `create_time`) USING BTREE,
  UNIQUE KEY `uk_report_no` (`report_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='报告生成记录';

-- ------------------------------------------------------------
-- 2.4 report_record 原始请求列（失败重生成）
--     全新库：上面 CREATE TABLE 已含 request_json 列；
--     存量库：由下面的语句补列。用 information_schema 做存在性判断，可重复执行
-- ------------------------------------------------------------
SET @exist_col := (SELECT COUNT(1) FROM information_schema.COLUMNS
                   WHERE TABLE_SCHEMA = DATABASE()
                     AND TABLE_NAME = 'report_record'
                     AND COLUMN_NAME = 'request_json');
SET @ddl_sql := IF(@exist_col = 0,
    'ALTER TABLE `report_record` ADD COLUMN `request_json` varchar(2048) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ''原始生成请求JSON（失败重生成用，保留 groupId/deviceIds 口径）'' AFTER `error_message`',
    'SELECT ''skip: report_record.request_json already exists'' AS info');
PREPARE ddl_stmt FROM @ddl_sql;
EXECUTE ddl_stmt;
DEALLOCATE PREPARE ddl_stmt;

-- ------------------------------------------------------------
-- 3. oss_config：MinIO 默认配置（未购买云 OSS → 自建 MinIO）
--    endpoint：后端连接 MinIO 的 S3 API 地址（若后端与 MinIO 同机可改为 http://127.0.0.1:9000）
--    domain：为空时 getUrl() 返回 http://<endpoint>/<bucketName>；可配置 CDN/公网域名生成更友好的 URL
--    注意：端口 9000 需在安全组/防火墙放行；下载统一走后端代理，桶保持私有（access_policy=0）
-- ------------------------------------------------------------
UPDATE `oss_config` SET `status` = '1' WHERE `config_key` IN ('aliyun', 'qiniu');

INSERT INTO `oss_config`
(tenant_id, tenant_name, config_key, access_key, secret_key, bucket_name, prefix,
 endpoint, domain, is_https, region, access_policy, status, del_flag, create_by, create_time, update_by, update_time, remark)
VALUES
(1, 'admin', 'minio', 'supermeminio', 'minio20260909', 'fastbee', 'report',
 'http://47.121.180.229:9000', '', 'N', '', '0', '0', '0', 'admin', NOW(), 'admin', NOW(), 'MinIO默认存储');
INSERT INTO fastbee.oss_config
(tenant_id, tenant_name, config_key, access_key, secret_key, bucket_name, prefix,
 endpoint, `domain`, is_https, region, access_policy, status, ext1, del_flag, create_by, create_time, update_by, update_time, remark)
VALUES
(1, 'admin', 'minio', 'IQJELEHGNX45V1LS7TZY', 'VnnQhFXwUIQ19qT4PKvlIAQxSH9Rru0IzTiyZHg3', 'fastbee', '',
 'http://47.121.180.229:9000', '', 'N', '', '1', '0', '', '0', 'admin', NOW(), 'admin', NOW(), 'MinIO默认存储');
-- ------------------------------------------------------------
-- 4. 历史报表菜单与权限（数据中心 3343 下新增）
-- ------------------------------------------------------------
INSERT INTO `sys_menu`
(menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES
(3610, '历史报表', 3343, 4, 'reportHistory', 'dataCenter/reportHistory', NULL, 1, 0, 'C', '0', '0', 'iot:report:list', 'documentation', 'admin', NOW(), 'admin', NOW(), '设备监测报告历史报表'),
(3611, '报表生成', 3610, 1, '', NULL, NULL, 1, 0, 'F', '0', '0', 'iot:report:generate', '#', 'admin', NOW(), 'admin', NOW(), '生成并导出分析报告');

INSERT INTO `sys_menu_translate` (id, zh_cn, en_us) VALUES
(3610, '历史报表', 'Report history'),
(3611, '报表生成', 'Generate report');

-- 初始数据
INSERT INTO fastbee.iot_things_threshold (product_id, device_id, monitor_category_code, metric_identifier, warning_value, alarm_value, control_value, warning_rate, alarm_rate, control_rate, unit, create_by, create_time) VALUES(0, 0, 'DEEP_DISPLACEMENT', 'displacement', 20.000000, 30.000000, 50.000000, 2.000000, 3.000000, 5.000000, 'mm', 'admin', NOW());
INSERT INTO fastbee.iot_things_threshold (product_id, device_id, monitor_category_code, metric_identifier, warning_value, alarm_value, control_value, warning_rate, alarm_rate, control_rate, unit, create_by, create_time) VALUES(0, 0, 'STATIC_LEVEL', 'settlement', 15.000000, 25.000000, 40.000000, 1.500000, 2.000000, 3.000000, 'mm', 'admin', NOW());
INSERT INTO fastbee.iot_things_threshold (product_id, device_id, monitor_category_code, metric_identifier, warning_value, alarm_value, control_value, warning_rate, alarm_rate, control_rate, unit, create_by, create_time) VALUES(0, 0, 'CRACK_METER', 'crack_width', 0.300000, 0.500000, 1.000000, 0.050000, 0.100000, 0.200000, 'mm', 'admin', NOW());
INSERT INTO fastbee.iot_things_threshold (product_id, device_id, monitor_category_code, metric_identifier, warning_value, alarm_value, control_value, warning_rate, alarm_rate, control_rate, unit, create_by, create_time) VALUES(0, 0, 'AXIAL_FORCE', 'axial_force', NULL, NULL, NULL, NULL, NULL, NULL, 'kN', 'admin', NOW());
INSERT INTO fastbee.iot_things_threshold (product_id, device_id, monitor_category_code, metric_identifier, warning_value, alarm_value, control_value, warning_rate, alarm_rate, control_rate, unit, create_by, create_time) VALUES(0, 0, 'RANGING_METER', 'distance', 15.000000, 25.000000, 40.000000, 1.000000, 2.000000, 3.000000, 'mm', 'admin', NOW());
INSERT INTO fastbee.iot_things_threshold (product_id, device_id, monitor_category_code, metric_identifier, warning_value, alarm_value, control_value, warning_rate, alarm_rate, control_rate, unit, create_by, create_time) VALUES(0, 0, 'INCLINOMETER', 'inclination', 1.000000, 2.000000, 3.000000, 0.100000, 0.200000, 0.500000, '°', 'admin', NOW());
INSERT INTO fastbee.iot_things_threshold (product_id, device_id, monitor_category_code, metric_identifier, warning_value, alarm_value, control_value, warning_rate, alarm_rate, control_rate, unit, create_by, create_time) VALUES(0, 0, 'WATER_LEVEL', 'water_level', 0.500000, 1.000000, 2.000000, 0.500000, 1.000000, 1.500000, 'm', 'admin', NOW());
INSERT INTO fastbee.iot_things_threshold (product_id, device_id, monitor_category_code, metric_identifier, warning_value, alarm_value, control_value, warning_rate, alarm_rate, control_rate, unit, create_by, create_time) VALUES(0, 0, 'STRAIN_GAUGE', 'strain', 500.000000, 800.000000, 1000.000000, 50.000000, 100.000000, 200.000000, 'με', 'admin', NOW());
INSERT INTO fastbee.iot_things_threshold (product_id, device_id, monitor_category_code, metric_identifier, warning_value, alarm_value, control_value, warning_rate, alarm_rate, control_rate, unit, create_by, create_time) VALUES(0, 0, 'VIBRATING_WIRE', 'frequency', 30.000000, 40.000000, 50.000000, NULL, NULL, NULL, 'Hz', 'admin', NOW());
INSERT INTO fastbee.iot_things_threshold (product_id, device_id, monitor_category_code, metric_identifier, warning_value, alarm_value, control_value, warning_rate, alarm_rate, control_rate, unit, create_by, create_time) VALUES(0, 0, 'THERMOMETER', 'temperature', 10.000000, 15.000000, 20.000000, 2.000000, 3.000000, 5.000000, '℃', 'admin', NOW());
INSERT INTO fastbee.iot_things_threshold (product_id, device_id, monitor_category_code, metric_identifier, warning_value, alarm_value, control_value, warning_rate, alarm_rate, control_rate, unit, create_by, create_time) VALUES(0, 0, 'RAIN_GAUGE', 'rainfall', 25.000000, 50.000000, 100.000000, 25.000000, 50.000000, 100.000000, 'mm', 'admin', NOW());