-- ============================================================
-- 芯导-LIMS 第三批需求 DDL
--   ⑤ 数据采集与处理：清洗规则 + 验证记录 + 接口自动采集配置
--   ⑥ 实时监控与预警：告警记录 + 监控项判定字段
-- 库：lims-ruoyi
-- ============================================================

-- ============================================================
-- ⑤ 数据清洗规则
-- ============================================================
DROP TABLE IF EXISTS `lims_clean_rule`;
CREATE TABLE `lims_clean_rule` (
  `id`             BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `interface_id`   BIGINT        DEFAULT NULL COMMENT '接口ID(NULL=全局规则)',
  `interface_code` VARCHAR(64)   DEFAULT NULL COMMENT '接口编码(冗余)',
  `rule_name`      VARCHAR(255)  DEFAULT NULL COMMENT '规则名称',
  `rule_type`      VARCHAR(32)   DEFAULT NULL COMMENT '类型(empty-空值处理,range-范围校验,outlier-异常值剔除,duplicate-重复剔除,unit-单位换算)',
  `target_field`   VARCHAR(64)   DEFAULT 'collectionValue' COMMENT '作用字段(collectionValue/dataType/unit)',
  `condition_expr` VARCHAR(500)  DEFAULT NULL COMMENT '条件表达式(如 <0 或 >1000，或 3sigma)',
  `action_type`    VARCHAR(32)   DEFAULT NULL COMMENT '动作(replace-替换,clamp-截断到边界,discard-丢弃整条,flag-仅标记)',
  `action_value`   VARCHAR(255)  DEFAULT NULL COMMENT '动作参数(替换值/边界值)',
  `priority`       INT           DEFAULT 100 COMMENT '优先级(数字小的先执行)',
  `enabled`        CHAR(1)       DEFAULT '1' COMMENT '是否启用(1-启用,0-停用)',
  `remark`         VARCHAR(500)  DEFAULT NULL COMMENT '备注',
  `del_flag`       CHAR(1)       DEFAULT '0' COMMENT '删除标志(0-正常,2-删除)',
  `tenant_id`      BIGINT        DEFAULT NULL COMMENT '租户ID',
  `create_time`    DATETIME      DEFAULT NULL COMMENT '创建时间',
  `create_user`    INT           DEFAULT NULL COMMENT '创建用户',
  `update_time`    DATETIME      DEFAULT NULL COMMENT '修改时间',
  `update_user`    INT           DEFAULT NULL COMMENT '修改用户',
  PRIMARY KEY (`id`),
  KEY `idx_interface_id` (`interface_id`),
  KEY `idx_enabled` (`enabled`),
  KEY `idx_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='LIMS-数据清洗规则';

-- ============================================================
-- ⑤ 采集数据验证记录
-- ============================================================
DROP TABLE IF EXISTS `lims_collection_verify`;
CREATE TABLE `lims_collection_verify` (
  `id`              BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `collection_id`   BIGINT        DEFAULT NULL COMMENT '采集记录ID(lims_data_collection.id)',
  `collection_code` VARCHAR(64)   DEFAULT NULL COMMENT '采集编号(冗余)',
  `rule_id`         BIGINT        DEFAULT NULL COMMENT '规则ID',
  `rule_name`       VARCHAR(255)  DEFAULT NULL COMMENT '规则名称(冗余)',
  `rule_type`       VARCHAR(32)   DEFAULT NULL COMMENT '规则类型',
  `passed`          CHAR(1)       DEFAULT '1' COMMENT '是否通过(1-通过,0-不通过)',
  `expected`        VARCHAR(255)  DEFAULT NULL COMMENT '期望/规则描述',
  `actual`          VARCHAR(255)  DEFAULT NULL COMMENT '实际值',
  `message`         VARCHAR(1000) DEFAULT NULL COMMENT '说明',
  `verify_time`     DATETIME      DEFAULT NULL COMMENT '验证时间',
  `del_flag`        CHAR(1)       DEFAULT '0' COMMENT '删除标志(0-正常,2-删除)',
  `tenant_id`       BIGINT        DEFAULT NULL COMMENT '租户ID',
  `create_time`     DATETIME      DEFAULT NULL COMMENT '创建时间',
  `create_user`     INT           DEFAULT NULL COMMENT '创建用户',
  `update_time`     DATETIME      DEFAULT NULL COMMENT '修改时间',
  `update_user`     INT           DEFAULT NULL COMMENT '修改用户',
  PRIMARY KEY (`id`),
  KEY `idx_collection_id` (`collection_id`),
  KEY `idx_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='LIMS-采集数据验证记录';

-- ============================================================
-- ⑥ 告警记录（预警闭环）
-- ============================================================
DROP TABLE IF EXISTS `lims_alert_record`;
CREATE TABLE `lims_alert_record` (
  `id`               BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `monitor_id`       BIGINT        DEFAULT NULL COMMENT '监控项ID(lims_realtime_monitor.id)',
  `monitor_code`     VARCHAR(64)   DEFAULT NULL COMMENT '监控编号(冗余)',
  `monitor_target`   VARCHAR(255)  DEFAULT NULL COMMENT '监控对象(冗余)',
  `monitor_type`     VARCHAR(32)   DEFAULT NULL COMMENT '监控类型(冗余)',
  `alert_level`      VARCHAR(32)   DEFAULT NULL COMMENT '告警级别(low/medium/high)',
  `alert_value`      DECIMAL(18,4) DEFAULT NULL COMMENT '触发值',
  `unit`             VARCHAR(32)   DEFAULT NULL COMMENT '单位',
  `upper_limit`      DECIMAL(18,4) DEFAULT NULL COMMENT '当时上限',
  `lower_limit`      DECIMAL(18,4) DEFAULT NULL COMMENT '当时下限',
  `alert_message`    VARCHAR(1000) DEFAULT NULL COMMENT '告警描述',
  `source`           VARCHAR(32)   DEFAULT 'auto' COMMENT '来源(auto-定时扫描,manual-手动检查,collect-采集同步)',
  `status`           VARCHAR(32)   DEFAULT 'pending' COMMENT '状态(pending-待处理,handling-处理中,resolved-已处理,ignored-已忽略)',
  `handler`          VARCHAR(64)   DEFAULT NULL COMMENT '处理人',
  `handle_time`      DATETIME      DEFAULT NULL COMMENT '处理时间',
  `handle_measure`   VARCHAR(1000) DEFAULT NULL COMMENT '处理措施',
  `alert_time`       DATETIME      DEFAULT NULL COMMENT '告警时间',
  `del_flag`         CHAR(1)       DEFAULT '0' COMMENT '删除标志(0-正常,2-删除)',
  `tenant_id`        BIGINT        DEFAULT NULL COMMENT '租户ID',
  `create_time`      DATETIME      DEFAULT NULL COMMENT '创建时间',
  `create_user`      INT           DEFAULT NULL COMMENT '创建用户',
  `update_time`      DATETIME      DEFAULT NULL COMMENT '修改时间',
  `update_user`      INT           DEFAULT NULL COMMENT '修改用户',
  PRIMARY KEY (`id`),
  KEY `idx_monitor_id` (`monitor_id`),
  KEY `idx_status` (`status`),
  KEY `idx_alert_time` (`alert_time`),
  KEY `idx_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='LIMS-告警记录';

-- ============================================================
-- 改动现有表
-- ============================================================

-- ⑤ 接口：自动采集配置
ALTER TABLE `lims_data_interface`
  ADD COLUMN `auto_enabled`      CHAR(1)     DEFAULT '0' COMMENT '是否自动采集(1-启用,0-停用)' AFTER `status`,
  ADD COLUMN `collect_cron`      VARCHAR(64) DEFAULT NULL COMMENT '采集间隔cron(如 0 */5 * * * ?)' AFTER `auto_enabled`,
  ADD COLUMN `last_collect_time` DATETIME    DEFAULT NULL COMMENT '上次采集时间' AFTER `collect_cron`,
  ADD COLUMN `last_collect_result` VARCHAR(500) DEFAULT NULL COMMENT '上次采集结果' AFTER `last_collect_time`;

-- ⑤ 采集记录：清洗与验证状态
ALTER TABLE `lims_data_collection`
  ADD COLUMN `clean_status` VARCHAR(32)   DEFAULT 'pending' COMMENT '清洗状态(pending-待清洗,cleaned-已清洗,discarded-已丢弃,failed-清洗失败)' AFTER `process_method`,
  ADD COLUMN `clean_log`    VARCHAR(2000) DEFAULT NULL COMMENT '清洗日志(逐条记录执行了哪些规则)' AFTER `clean_status`,
  ADD COLUMN `verify_status` VARCHAR(32)  DEFAULT 'pending' COMMENT '验证状态(pending-待验证,passed-通过,failed-未通过)' AFTER `clean_log`,
  ADD COLUMN `verify_msg`   VARCHAR(1000) DEFAULT NULL COMMENT '验证说明' AFTER `verify_status`;

-- ⑥ 监控项：判定相关
ALTER TABLE `lims_realtime_monitor`
  ADD COLUMN `last_check_time` DATETIME DEFAULT NULL COMMENT '上次判定时间' AFTER `monitor_time`,
  ADD COLUMN `alert_count`     INT      DEFAULT 0    COMMENT '累计触发告警次数' AFTER `last_check_time`,
  ADD COLUMN `source_collection_id` BIGINT DEFAULT NULL COMMENT '数据来源采集记录ID' AFTER `alert_count`;

-- ============================================================
-- 预置几条常用清洗规则（全局，接口ID为空）
-- ============================================================
INSERT INTO `lims_clean_rule` (`rule_name`, `rule_type`, `target_field`, `condition_expr`, `action_type`, `action_value`, `priority`, `enabled`, `remark`) VALUES
('空值丢弃', 'empty',      'collectionValue', 'null',    'discard', NULL,  10, '1', '采集数值为空时整条丢弃'),
('负值截断为零', 'range',  'collectionValue', '<0',      'clamp',   '0',   20, '1', '浓度/压力等不应为负，截断到 0'),
('极大值标记', 'outlier',  'collectionValue', '>1000000','flag',    NULL,  30, '1', '超过一百万视为可疑，仅标记不丢弃');

-- ============================================================
-- 校验
-- ============================================================
SELECT table_name, table_comment
FROM information_schema.tables
WHERE table_schema = 'lims-ruoyi'
  AND table_name IN ('lims_clean_rule','lims_collection_verify','lims_alert_record')
ORDER BY table_name;

SELECT column_name, column_type, column_comment
FROM information_schema.columns
WHERE table_schema = 'lims-ruoyi' AND table_name = 'lims_data_collection'
  AND column_name IN ('clean_status','clean_log','verify_status','verify_msg')
ORDER BY ordinal_position;

SELECT column_name, column_type
FROM information_schema.columns
WHERE table_schema = 'lims-ruoyi' AND table_name = 'lims_data_interface'
  AND column_name IN ('auto_enabled','collect_cron','last_collect_time','last_collect_result');

SELECT column_name, column_type
FROM information_schema.columns
WHERE table_schema = 'lims-ruoyi' AND table_name = 'lims_realtime_monitor'
  AND column_name IN ('last_check_time','alert_count','source_collection_id');

-- ============================================================
-- ⑤⑥ 定时任务（RuoYi 的 sys_job，配置后到「系统监控→定时任务」里能看到并启停）
-- ============================================================
DELETE FROM `sys_job` WHERE `invoke_target` IN ('limsTask.autoCollect', 'limsTask.checkAlerts');

INSERT INTO `sys_job` (`job_name`, `job_group`, `invoke_target`, `cron_expression`, `misfire_policy`, `concurrent`, `status`, `create_by`, `create_time`, `remark`) VALUES
('LIMS自动采集', 'LIMS', 'limsTask.autoCollect', '0 */5 * * * ?', '3', '1', '1', 'admin', NOW(), '扫描 auto_enabled=1 的接口，逐个采集并按清洗规则处理'),
('LIMS监控预警扫描', 'LIMS', 'limsTask.checkAlerts', '0 * * * * ?', '3', '1', '1', 'admin', NOW(), '先清洗待处理采集数据，再扫描所有监控项判定越限并生成告警');

SELECT job_id, job_name, invoke_target, cron_expression, status FROM `sys_job` WHERE `job_group` = 'LIMS';