140 lines
8.0 KiB
SQL
140 lines
8.0 KiB
SQL
-- ================================================================
|
||
-- Fitness Agent 健康管理模块 - 数据库迁移脚本
|
||
-- 新增：体检报告、异常指标、禁忌规则、安全预警、复查提醒
|
||
-- ================================================================
|
||
|
||
-- 1. 体检报告记录表
|
||
CREATE TABLE IF NOT EXISTS health_checkups (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id VARCHAR(50) NOT NULL COMMENT '用户标识',
|
||
checkup_date DATE NOT NULL COMMENT '体检日期',
|
||
hospital VARCHAR(100) DEFAULT '' COMMENT '体检机构/医院',
|
||
summary TEXT COMMENT '体检总结/结论',
|
||
overall_status ENUM('正常','基本正常','异常','需复查') DEFAULT '基本正常' COMMENT '总体评价',
|
||
report_file VARCHAR(255) DEFAULT '' COMMENT '报告文件路径/URL',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_user_date (user_id, checkup_date DESC),
|
||
INDEX idx_status (user_id, overall_status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='体检报告记录';
|
||
|
||
-- 2. 异常指标记录表
|
||
CREATE TABLE IF NOT EXISTS health_abnormal_indicators (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id VARCHAR(50) NOT NULL COMMENT '用户标识',
|
||
checkup_id BIGINT DEFAULT NULL COMMENT '关联体检报告ID',
|
||
indicator_name VARCHAR(100) NOT NULL COMMENT '指标名称（如：总胆固醇、尿酸、ALT）',
|
||
indicator_category VARCHAR(50) NOT NULL COMMENT '指标分类（血常规/肝功能/肾功能/血脂/血糖/尿酸/血压/其他）',
|
||
result_value VARCHAR(100) NOT NULL COMMENT '检测结果值',
|
||
unit VARCHAR(30) DEFAULT '' COMMENT '单位（如：mmol/L、μmol/L）',
|
||
normal_range VARCHAR(100) DEFAULT '' COMMENT '正常参考范围',
|
||
deviation ENUM('偏高','偏低','临界偏高','临界偏低') NOT NULL COMMENT '偏离方向',
|
||
severity ENUM('注意','警示','危险') DEFAULT '注意' COMMENT '严重程度',
|
||
status ENUM('待处理','观察中','已复查','已正常','需持续关注') DEFAULT '待处理' COMMENT '处理状态',
|
||
doctor_advice TEXT COMMENT '医生建议',
|
||
ai_analysis TEXT COMMENT 'AI分析解读',
|
||
first_detected DATE NOT NULL COMMENT '首次发现日期',
|
||
resolved_date DATE DEFAULT NULL COMMENT '恢复正常日期',
|
||
note TEXT COMMENT '备注',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_user_indicator (user_id, indicator_name),
|
||
INDEX idx_user_status (user_id, status),
|
||
INDEX idx_severity (user_id, severity),
|
||
INDEX idx_checkup (checkup_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='异常指标记录';
|
||
|
||
-- 3. 用户禁忌规则表
|
||
CREATE TABLE IF NOT EXISTS health_restrictions (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id VARCHAR(50) NOT NULL COMMENT '用户标识',
|
||
indicator_id BIGINT DEFAULT NULL COMMENT '关联异常指标ID（可为空，表示通用禁忌）',
|
||
restriction_type ENUM('饮食','运动','作息','用药','其他') NOT NULL COMMENT '禁忌类型',
|
||
rule_name VARCHAR(100) NOT NULL COMMENT '规则名称（如：高嘌呤食物、剧烈运动）',
|
||
forbidden_items TEXT NOT NULL COMMENT '禁忌清单（JSON数组或逗号分隔）',
|
||
reason TEXT COMMENT '禁忌原因',
|
||
start_date DATE NOT NULL COMMENT '生效日期',
|
||
end_date DATE DEFAULT NULL COMMENT '失效日期（NULL=长期有效）',
|
||
priority ENUM('低','中','高','紧急') DEFAULT '中' COMMENT '优先级',
|
||
is_active TINYINT(1) DEFAULT 1 COMMENT '是否生效',
|
||
violation_consequence TEXT COMMENT '违反后果说明',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_user_active (user_id, is_active),
|
||
INDEX idx_indicator (indicator_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户禁忌规则';
|
||
|
||
-- 4. 安全预警日志表
|
||
CREATE TABLE IF NOT EXISTS health_alerts (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id VARCHAR(50) NOT NULL COMMENT '用户标识',
|
||
indicator_id BIGINT DEFAULT NULL COMMENT '关联异常指标ID',
|
||
alert_type ENUM('饮食违规','运动风险','指标恶化','复查逾期','禁忌冲突','综合预警') NOT NULL COMMENT '预警类型',
|
||
severity ENUM('提示','警告','危险') NOT NULL COMMENT '严重级别',
|
||
title VARCHAR(200) NOT NULL COMMENT '预警标题',
|
||
detail TEXT NOT NULL COMMENT '预警详情',
|
||
related_context TEXT COMMENT '相关上下文（JSON）',
|
||
action_required VARCHAR(500) DEFAULT '' COMMENT '建议操作',
|
||
status ENUM('未处理','已处理','已忽略') DEFAULT '未处理' COMMENT '处理状态',
|
||
resolved_at TIMESTAMP NULL DEFAULT NULL COMMENT '处理时间',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_user_status (user_id, status),
|
||
INDEX idx_severity (severity),
|
||
INDEX idx_created (created_at DESC)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='安全预警日志';
|
||
|
||
-- 5. 复查提醒表
|
||
CREATE TABLE IF NOT EXISTS health_reminders (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id VARCHAR(50) NOT NULL COMMENT '用户标识',
|
||
indicator_id BIGINT DEFAULT NULL COMMENT '关联异常指标ID',
|
||
reminder_type ENUM('复查提醒','复诊提醒','用药提醒','生活调整','其他') NOT NULL COMMENT '提醒类型',
|
||
title VARCHAR(200) NOT NULL COMMENT '提醒标题',
|
||
description TEXT COMMENT '提醒内容',
|
||
due_date DATE NOT NULL COMMENT '预计执行日期',
|
||
remind_before_days INT DEFAULT 3 COMMENT '提前提醒天数',
|
||
repeat_interval ENUM('不重复','每周','每月','每季度','每半年','每年') DEFAULT '不重复' COMMENT '重复周期',
|
||
repeat_count INT DEFAULT 0 COMMENT '已重复次数',
|
||
max_repeats INT DEFAULT 0 COMMENT '最大重复次数（0=不限）',
|
||
status ENUM('待执行','已执行','已过期','已取消') DEFAULT '待执行' COMMENT '执行状态',
|
||
completed_at TIMESTAMP NULL DEFAULT NULL COMMENT '实际完成时间',
|
||
result TEXT COMMENT '执行结果/复查结论',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_user_due (user_id, due_date),
|
||
INDEX idx_user_status (user_id, status),
|
||
INDEX idx_due_date (due_date, status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='复查提醒';
|
||
|
||
-- 6. 指标变化追踪表（用于趋势分析）
|
||
CREATE TABLE IF NOT EXISTS health_indicator_trends (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id VARCHAR(50) NOT NULL COMMENT '用户标识',
|
||
indicator_name VARCHAR(100) NOT NULL COMMENT '指标名称',
|
||
indicator_category VARCHAR(50) NOT NULL COMMENT '指标分类',
|
||
record_date DATE NOT NULL COMMENT '检测日期',
|
||
result_value VARCHAR(100) NOT NULL COMMENT '检测值',
|
||
unit VARCHAR(30) DEFAULT '' COMMENT '单位',
|
||
normal_range VARCHAR(100) DEFAULT '' COMMENT '参考范围',
|
||
source ENUM('体检报告','自主检测','医院就诊') DEFAULT '体检报告' COMMENT '数据来源',
|
||
note VARCHAR(255) DEFAULT '' COMMENT '备注',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_user_indicator (user_id, indicator_name, record_date),
|
||
UNIQUE KEY uk_user_indicator_date (user_id, indicator_name, record_date)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='指标变化追踪';
|
||
|
||
-- 7. 用户异常指标概要表（汇总最新状态）
|
||
CREATE TABLE IF NOT EXISTS user_health_summary (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id VARCHAR(50) NOT NULL UNIQUE COMMENT '用户标识',
|
||
abnormal_count INT DEFAULT 0 COMMENT '当前异常指标数',
|
||
critical_count INT DEFAULT 0 COMMENT '高危指标数',
|
||
pending_reminders INT DEFAULT 0 COMMENT '待处理提醒数',
|
||
active_restrictions INT DEFAULT 0 COMMENT '生效禁忌数',
|
||
last_checkup_date DATE DEFAULT NULL COMMENT '最近体检日期',
|
||
last_review_at TIMESTAMP NULL DEFAULT NULL COMMENT '最近AI审核时间',
|
||
overall_risk_level ENUM('低','中','高') DEFAULT '低' COMMENT '综合风险等级',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户健康概要';
|