189 lines
8.9 KiB
PL/PgSQL
189 lines
8.9 KiB
PL/PgSQL
-- 训练偏好相关数据库表设计
|
|
-- 运动项目/类型表
|
|
CREATE TABLE public.ak_sport_types (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 运动类型ID
|
|
name VARCHAR(64) NOT NULL, -- 运动类型名称
|
|
description TEXT, -- 描述
|
|
icon_url TEXT, -- 图标URL
|
|
category VARCHAR(32), -- 分类 (有氧、力量、柔韧、球类等)
|
|
difficulty_level INT DEFAULT 1, -- 难度等级 1-5
|
|
calorie_rate FLOAT, -- 每分钟消耗卡路里率
|
|
is_active BOOLEAN DEFAULT true, -- 是否启用
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
|
);
|
|
COMMENT ON TABLE public.ak_sport_types IS '运动项目/类型表';
|
|
|
|
-- 插入基础运动类型数据
|
|
INSERT INTO public.ak_sport_types (name, description, category, difficulty_level, calorie_rate) VALUES
|
|
('跑步', '有氧运动，提高心肺功能', '有氧运动', 2, 10.0),
|
|
('游泳', '全身有氧运动，低冲击', '有氧运动', 3, 12.0),
|
|
('篮球', '团队球类运动，提高协调性', '球类运动', 3, 8.5),
|
|
('足球', '团队球类运动，全身锻炼', '球类运动', 3, 9.0),
|
|
('羽毛球', '球拍类运动，提高反应力', '球类运动', 2, 7.0),
|
|
('乒乓球', '球拍类运动，提高手眼协调', '球类运动', 2, 6.0),
|
|
('举重', '力量训练，增强肌肉', '力量训练', 4, 6.5),
|
|
('瑜伽', '柔韧性训练，身心放松', '柔韧训练', 1, 3.0),
|
|
('骑行', '有氧运动，下肢锻炼', '有氧运动', 2, 8.0),
|
|
('跳绳', '有氧运动，协调性训练', '有氧运动', 2, 12.0);
|
|
|
|
-- 用户训练目标表
|
|
CREATE TABLE public.ak_user_training_goals (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 目标ID
|
|
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
|
goal_type VARCHAR(32) NOT NULL, -- 目标类型 (weight_loss, muscle_gain, endurance, flexibility)
|
|
target_value FLOAT, -- 目标数值
|
|
current_value FLOAT DEFAULT 0, -- 当前数值
|
|
unit VARCHAR(16), -- 单位
|
|
target_date DATE, -- 目标达成日期
|
|
priority INT DEFAULT 1, -- 优先级 1-5
|
|
status VARCHAR(16) DEFAULT 'active', -- 状态 (active, paused, completed, cancelled)
|
|
description TEXT, -- 描述
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
|
);
|
|
COMMENT ON TABLE public.ak_user_training_goals IS '用户训练目标表';
|
|
CREATE INDEX idx_user_training_goals_user_id ON public.ak_user_training_goals(user_id);
|
|
|
|
-- 用户运动偏好表
|
|
CREATE TABLE public.ak_user_sport_preferences (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 偏好ID
|
|
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
|
sport_type_id uuid REFERENCES public.ak_sport_types(id) ON DELETE CASCADE, -- 运动类型ID
|
|
preference_level INT DEFAULT 3, -- 喜好程度 1-5 (1最不喜欢, 5最喜欢)
|
|
frequency_per_week INT DEFAULT 1, -- 每周频次
|
|
duration_minutes INT DEFAULT 30, -- 每次时长(分钟)
|
|
intensity_level INT DEFAULT 2, -- 强度等级 1-5 (1最轻松, 5最激烈)
|
|
notes TEXT, -- 备注
|
|
is_favorite BOOLEAN DEFAULT false, -- 是否为最爱运动
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
|
);
|
|
COMMENT ON TABLE public.ak_user_sport_preferences IS '用户运动偏好表';
|
|
CREATE INDEX idx_user_sport_preferences_user_id ON public.ak_user_sport_preferences(user_id);
|
|
CREATE INDEX idx_user_sport_preferences_sport_type ON public.ak_user_sport_preferences(sport_type_id);
|
|
CREATE UNIQUE INDEX idx_user_sport_unique ON public.ak_user_sport_preferences(user_id, sport_type_id);
|
|
|
|
-- 训练提醒设置表
|
|
CREATE TABLE public.ak_training_reminders (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 提醒ID
|
|
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
|
reminder_type VARCHAR(32) NOT NULL, -- 提醒类型 (daily, weekly, custom)
|
|
title VARCHAR(128) NOT NULL, -- 提醒标题
|
|
message TEXT, -- 提醒内容
|
|
trigger_time TIME, -- 触发时间
|
|
trigger_days VARCHAR(16), -- 触发星期 (1234567 表示周一到周日)
|
|
trigger_date DATE, -- 自定义日期触发
|
|
is_enabled BOOLEAN DEFAULT true, -- 是否启用
|
|
sound_enabled BOOLEAN DEFAULT true, -- 是否播放声音
|
|
vibrate_enabled BOOLEAN DEFAULT true, -- 是否震动
|
|
advance_minutes INT DEFAULT 0, -- 提前提醒分钟数
|
|
repeat_count INT DEFAULT 1, -- 重复次数
|
|
repeat_interval_minutes INT DEFAULT 5, -- 重复间隔(分钟)
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
|
);
|
|
COMMENT ON TABLE public.ak_training_reminders IS '训练提醒设置表';
|
|
CREATE INDEX idx_training_reminders_user_id ON public.ak_training_reminders(user_id);
|
|
|
|
-- 用户健身等级和经验表
|
|
CREATE TABLE public.ak_user_fitness_profile (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 档案ID
|
|
user_id uuid UNIQUE REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
|
fitness_level VARCHAR(16) DEFAULT 'beginner', -- 健身水平 (beginner, intermediate, advanced, expert)
|
|
experience_years FLOAT DEFAULT 0, -- 运动经验年数
|
|
injury_history TEXT, -- 伤病史
|
|
health_conditions TEXT, -- 健康状况
|
|
preferred_workout_time VARCHAR(16), -- 偏好训练时间 (morning, afternoon, evening)
|
|
available_equipment TEXT, -- 可用器材
|
|
workout_location_preference VARCHAR(32), -- 训练地点偏好 (home, gym, outdoor)
|
|
weekly_time_budget_minutes INT DEFAULT 180, -- 每周可用训练时间(分钟)
|
|
rest_day_preference VARCHAR(16), -- 休息日偏好
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
|
);
|
|
COMMENT ON TABLE public.ak_user_fitness_profile IS '用户健身等级和经验表';
|
|
|
|
-- 训练强度偏好设置表
|
|
CREATE TABLE public.ak_training_intensity_settings (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 设置ID
|
|
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
|
sport_type_id uuid REFERENCES public.ak_sport_types(id) ON DELETE CASCADE, -- 运动类型ID
|
|
heart_rate_zones JSONB, -- 心率区间设置 {"zone1": {"min": 120, "max": 140}, ...}
|
|
perceived_exertion_scale INT DEFAULT 5, -- 主观疲劳感量表 1-10
|
|
auto_adjust_intensity BOOLEAN DEFAULT true, -- 是否自动调整强度
|
|
max_workout_duration_minutes INT DEFAULT 60, -- 最大训练时长
|
|
cooldown_duration_minutes INT DEFAULT 5, -- 放松时长
|
|
warmup_duration_minutes INT DEFAULT 5, -- 热身时长
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
|
);
|
|
COMMENT ON TABLE public.ak_training_intensity_settings IS '训练强度偏好设置表';
|
|
CREATE INDEX idx_training_intensity_user_id ON public.ak_training_intensity_settings(user_id);
|
|
CREATE UNIQUE INDEX idx_training_intensity_unique ON public.ak_training_intensity_settings(user_id, sport_type_id);
|
|
|
|
-- 创建获取用户喜爱运动的函数
|
|
CREATE OR REPLACE FUNCTION get_user_favorite_sports(p_user_id uuid)
|
|
RETURNS TABLE(
|
|
sport_name VARCHAR,
|
|
preference_level INT,
|
|
frequency_per_week INT,
|
|
duration_minutes INT
|
|
) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
st.name,
|
|
usp.preference_level,
|
|
usp.frequency_per_week,
|
|
usp.duration_minutes
|
|
FROM public.ak_user_sport_preferences usp
|
|
JOIN public.ak_sport_types st ON usp.sport_type_id = st.id
|
|
WHERE usp.user_id = p_user_id
|
|
AND (usp.is_favorite = true OR usp.preference_level >= 4)
|
|
ORDER BY usp.preference_level DESC, usp.updated_at DESC;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- 创建获取训练建议的函数
|
|
CREATE OR REPLACE FUNCTION get_training_recommendations(p_user_id uuid)
|
|
RETURNS JSONB AS $$
|
|
DECLARE
|
|
user_profile public.ak_user_fitness_profile%ROWTYPE;
|
|
recommendations JSONB := '[]'::JSONB;
|
|
sport_rec JSONB;
|
|
BEGIN
|
|
-- 获取用户健身档案
|
|
SELECT * INTO user_profile
|
|
FROM public.ak_user_fitness_profile
|
|
WHERE user_id = p_user_id;
|
|
|
|
-- 基于用户偏好生成推荐
|
|
FOR sport_rec IN
|
|
SELECT jsonb_build_object(
|
|
'sport_name', st.name,
|
|
'category', st.category,
|
|
'recommended_duration',
|
|
CASE
|
|
WHEN user_profile.fitness_level = 'beginner' THEN 20
|
|
WHEN user_profile.fitness_level = 'intermediate' THEN 35
|
|
ELSE 45
|
|
END,
|
|
'recommended_frequency',
|
|
CASE
|
|
WHEN st.category = '有氧运动' THEN 3
|
|
WHEN st.category = '力量训练' THEN 2
|
|
ELSE 2
|
|
END
|
|
)
|
|
FROM public.ak_sport_types st
|
|
WHERE st.is_active = true
|
|
ORDER BY st.difficulty_level
|
|
LIMIT 5
|
|
LOOP
|
|
recommendations := recommendations || sport_rec;
|
|
END LOOP;
|
|
|
|
RETURN recommendations;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|