108 lines
5.4 KiB
SQL
108 lines
5.4 KiB
SQL
-- Fitness Agent 数据库初始化
|
||
-- 食物热量库
|
||
CREATE TABLE IF NOT EXISTS food_library (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(100) NOT NULL COMMENT '食物名称',
|
||
category VARCHAR(50) NOT NULL COMMENT '分类：主食/肉类/蔬菜/水果/乳制品/饮品/零食/调味品',
|
||
heat DECIMAL(6,1) NOT NULL COMMENT '每100克热量(kcal)',
|
||
unit VARCHAR(20) DEFAULT '克' COMMENT '常用单位',
|
||
unit_weight DECIMAL(6,1) DEFAULT 100 COMMENT '单位对应的克数',
|
||
nature ENUM('温','热','平','凉','寒') DEFAULT '平' COMMENT '食物性质（中医）',
|
||
note TEXT COMMENT '备注/建议',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_name (name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='食物热量库';
|
||
|
||
-- 用户信息表
|
||
CREATE TABLE IF NOT EXISTS user_profiles (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id VARCHAR(50) NOT NULL UNIQUE COMMENT '用户标识（如微信ID）',
|
||
name VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
|
||
age INT DEFAULT 35 COMMENT '年龄',
|
||
gender ENUM('男','女') DEFAULT '男' COMMENT '性别',
|
||
height DECIMAL(5,1) DEFAULT 175 COMMENT '身高(cm)',
|
||
target_weight DECIMAL(5,1) DEFAULT 75 COMMENT '目标体重(kg)',
|
||
daily_target_heat INT DEFAULT 1200 COMMENT '每日目标热量(大卡)',
|
||
activity_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='用户信息表';
|
||
|
||
-- 饮食记录
|
||
CREATE TABLE IF NOT EXISTS diet_records (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id VARCHAR(50) NOT NULL COMMENT '用户标识',
|
||
record_date DATE NOT NULL COMMENT '记录日期',
|
||
meal_type ENUM('早餐','午餐','晚餐','加餐') NOT NULL COMMENT '餐次',
|
||
food_name VARCHAR(100) NOT NULL COMMENT '食物名称',
|
||
quantity DECIMAL(8,1) NOT NULL COMMENT '食用量',
|
||
unit VARCHAR(20) DEFAULT '克' COMMENT '单位',
|
||
estimated_heat DECIMAL(7,1) DEFAULT 0 COMMENT '估算热量(kcal)',
|
||
note VARCHAR(255) DEFAULT '' COMMENT '备注',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_user_date (user_id, record_date),
|
||
INDEX idx_record_date (record_date)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='饮食记录';
|
||
|
||
-- 身体数据记录（体重等）
|
||
CREATE TABLE IF NOT EXISTS body_records (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id VARCHAR(50) NOT NULL COMMENT '用户标识',
|
||
record_date DATE NOT NULL COMMENT '记录日期',
|
||
weight DECIMAL(5,1) NOT NULL COMMENT '体重(kg)',
|
||
waistline DECIMAL(5,1) DEFAULT NULL COMMENT '腰围(cm)',
|
||
body_fat DECIMAL(4,1) DEFAULT NULL COMMENT '体脂率(%)',
|
||
note VARCHAR(255) DEFAULT '' COMMENT '备注',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_user_date (user_id, record_date),
|
||
INDEX idx_user_date_order (user_id, record_date DESC)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='身体数据记录';
|
||
|
||
-- 运动记录
|
||
CREATE TABLE IF NOT EXISTS exercise_records (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id VARCHAR(50) NOT NULL COMMENT '用户标识',
|
||
record_date DATE NOT NULL COMMENT '记录日期',
|
||
time_slot ENUM('早晨','中午','下午','晚上') DEFAULT '早晨' COMMENT '时间段',
|
||
exercise_type VARCHAR(50) NOT NULL COMMENT '运动类型（如：八段锦）',
|
||
duration_minutes INT NOT NULL COMMENT '运动时长(分钟)',
|
||
estimated_burn DECIMAL(7,1) DEFAULT 0 COMMENT '估算消耗(kcal)',
|
||
note VARCHAR(255) DEFAULT '' COMMENT '备注/感受',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_user_date (user_id, record_date)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运动记录';
|
||
|
||
-- 健康状况日志
|
||
CREATE TABLE IF NOT EXISTS health_status_logs (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id VARCHAR(50) NOT NULL COMMENT '用户标识',
|
||
log_date DATE NOT NULL COMMENT '记录日期',
|
||
status_type VARCHAR(50) NOT NULL COMMENT '状态类型（上火/积食/疲劳/感冒/其他）',
|
||
severity ENUM('轻','中','重') DEFAULT '轻' COMMENT '严重程度',
|
||
symptom TEXT COMMENT '具体症状描述',
|
||
diagnosis VARCHAR(100) DEFAULT '' COMMENT '判断结论（如：肺热/胃火）',
|
||
suggestion TEXT COMMENT '建议方案',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_user_date (user_id, log_date)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康状况日志';
|
||
|
||
-- 周报记录
|
||
CREATE TABLE IF NOT EXISTS weekly_reports (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id VARCHAR(50) NOT NULL COMMENT '用户标识',
|
||
week_start DATE NOT NULL COMMENT '周起始日期',
|
||
week_end DATE NOT NULL COMMENT '周结束日期',
|
||
avg_weight DECIMAL(5,2) DEFAULT NULL COMMENT '平均体重(kg)',
|
||
weight_change DECIMAL(5,2) DEFAULT NULL COMMENT '体重变化(kg)',
|
||
avg_daily_heat DECIMAL(7,1) DEFAULT NULL COMMENT '日均热量摄入(kcal)',
|
||
heat_deficit DECIMAL(7,1) DEFAULT NULL COMMENT '日均热量缺口(kcal)',
|
||
exercise_days INT DEFAULT 0 COMMENT '运动天数',
|
||
diet_compliance_days INT DEFAULT 0 COMMENT '饮食达标天数',
|
||
plateau_flag TINYINT(1) DEFAULT 0 COMMENT '是否平台期(1=是)',
|
||
assessment TEXT COMMENT '评估总结',
|
||
suggestion TEXT COMMENT '下周建议',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_user_week (user_id, week_start)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='每周减脂报告';
|