977 lines
41 KiB
PL/PgSQL
977 lines
41 KiB
PL/PgSQL
-- 运动训练监测与AI评估平台表结构设计（Supabase/PostgreSQL规范，含中文注释、索引、外键约束）
|
||
-- 支持的语言表
|
||
CREATE TABLE public.ak_languages (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 语言ID
|
||
code VARCHAR(10) NOT NULL UNIQUE, -- 语言代码 (如 en-US, zh-CN)
|
||
name VARCHAR(50) NOT NULL, -- 语言名称
|
||
native_name VARCHAR(50) NOT NULL, -- 本地语言名称
|
||
is_active BOOLEAN DEFAULT true, -- 是否启用
|
||
is_default BOOLEAN DEFAULT false, -- 是否默认语言
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_languages IS '支持的语言表';
|
||
|
||
-- 地区表
|
||
CREATE TABLE public.ak_regions (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 地区ID
|
||
name VARCHAR(128) NOT NULL, -- 地区名称
|
||
parent_id uuid REFERENCES public.ak_regions(id), -- 上级地区ID
|
||
level INT, -- 地区级别 1省 2市 3区县 4街道
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_regions IS '地区表';
|
||
COMMENT ON COLUMN public.ak_regions.name IS '地区名称';
|
||
COMMENT ON COLUMN public.ak_regions.parent_id IS '上级地区ID';
|
||
COMMENT ON COLUMN public.ak_regions.level IS '地区级别';
|
||
|
||
-- 学校表
|
||
CREATE TABLE public.ak_schools (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 学校ID
|
||
name VARCHAR(128) NOT NULL, -- 学校名称
|
||
region_id uuid REFERENCES public.ak_regions(id), -- 所属地区
|
||
type VARCHAR(32), -- 学校类型
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_schools IS '学校表';
|
||
COMMENT ON COLUMN public.ak_schools.region_id IS '所属地区';
|
||
|
||
-- 年级表
|
||
CREATE TABLE public.ak_grades (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 年级ID
|
||
school_id uuid REFERENCES public.ak_schools(id) ON DELETE CASCADE, -- 所属学校
|
||
name VARCHAR(32) NOT NULL, -- 年级名称
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_grades IS '年级表';
|
||
COMMENT ON COLUMN public.ak_grades.school_id IS '所属学校';
|
||
|
||
-- 班级表
|
||
CREATE TABLE public.ak_classes (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 班级ID
|
||
grade_id uuid REFERENCES public.ak_grades(id) ON DELETE CASCADE, -- 所属年级
|
||
name VARCHAR(32) NOT NULL, -- 班级名称
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_classes IS '班级表';
|
||
COMMENT ON COLUMN public.ak_classes.grade_id IS '所属年级';
|
||
|
||
-- 用户表
|
||
CREATE TABLE public.ak_users (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 用户ID
|
||
username VARCHAR(64) UNIQUE NOT NULL, -- 用户名
|
||
email VARCHAR(128) UNIQUE NOT NULL, -- 邮箱
|
||
password_hash VARCHAR(256) NOT NULL, -- 密码哈希
|
||
gender VARCHAR(16) DEFAULT 'other', -- 性别
|
||
birthday DATE, -- 生日
|
||
height_cm INT, -- 身高
|
||
weight_kg INT, -- 体重
|
||
bio TEXT, -- biography
|
||
phone VARCHAR(32), -- 手机号码
|
||
avatar_url TEXT, -- 头像
|
||
region_id uuid REFERENCES public.ak_regions(id), -- 所属地区
|
||
school_id uuid REFERENCES public.ak_schools(id), -- 所属学校
|
||
grade_id uuid REFERENCES public.ak_grades(id), -- 所属年级
|
||
class_id uuid REFERENCES public.ak_classes(id), -- 所属班级
|
||
role VARCHAR(32) DEFAULT 'student', -- 角色
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 更新时间
|
||
auth_id UUID REFERENCES auth.users(id) ON DELETE CASCADE, -- Supabase Auth ID
|
||
preferred_language uuid REFERENCES public.ak_languages(id) -- 用户偏好语言
|
||
);
|
||
COMMENT ON TABLE public.ak_users IS '用户表';
|
||
COMMENT ON COLUMN public.ak_users.username IS '用户名';
|
||
COMMENT ON COLUMN public.ak_users.email IS '邮箱';
|
||
COMMENT ON COLUMN public.ak_users.gender IS '性别';
|
||
COMMENT ON COLUMN public.ak_users.region_id IS '所属地区';
|
||
COMMENT ON COLUMN public.ak_users.school_id IS '所属学校';
|
||
COMMENT ON COLUMN public.ak_users.grade_id IS '所属年级';
|
||
COMMENT ON COLUMN public.ak_users.class_id IS '所属班级';
|
||
COMMENT ON COLUMN public.ak_users.role IS '用户角色';
|
||
COMMENT ON COLUMN public.ak_users.preferred_language IS '用户偏好语言';
|
||
|
||
CREATE INDEX idx_users_region_id ON public.ak_users(region_id);
|
||
CREATE INDEX idx_users_school_id ON public.ak_users(school_id);
|
||
CREATE INDEX idx_users_grade_id ON public.ak_users(grade_id);
|
||
CREATE INDEX idx_users_class_id ON public.ak_users(class_id);
|
||
|
||
-- 用户联系方式表
|
||
CREATE TABLE public.ak_user_contacts (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 联系方式ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
contact_type VARCHAR(32) NOT NULL, -- 联系方式类型
|
||
contact_value VARCHAR(128) NOT NULL, -- 联系方式内容
|
||
is_primary BOOLEAN DEFAULT false, -- 是否主联系方式
|
||
remark VARCHAR(128), -- 备注
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_user_contacts IS '用户联系方式表';
|
||
COMMENT ON COLUMN public.ak_user_contacts.contact_type IS '联系方式类型';
|
||
COMMENT ON COLUMN public.ak_user_contacts.contact_value IS '联系方式内容';
|
||
|
||
CREATE INDEX idx_user_contacts_user_id ON public.ak_user_contacts(user_id);
|
||
|
||
-- 用户个人展示设置表
|
||
CREATE TABLE public.ak_user_profiles (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 个人展示ID
|
||
user_id uuid UNIQUE REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
avatar_url TEXT, -- 头像S3地址
|
||
background_url TEXT, -- 背景图/视频S3地址
|
||
declaration TEXT, -- 个人宣言
|
||
status_text TEXT, -- 近况
|
||
status_media_url TEXT, -- 近况配图/视频S3地址
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
COMMENT ON TABLE public.ak_user_profiles IS '用户个人展示设置表';
|
||
|
||
-- 用户-班级多对多关系
|
||
CREATE TABLE public.ak_user_classes (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 关系ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
class_id uuid REFERENCES public.ak_classes(id) ON DELETE CASCADE, -- 班级ID
|
||
role VARCHAR(32), -- 角色
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_user_classes IS '用户-班级多对多关系表';
|
||
|
||
CREATE INDEX idx_user_classes_user_id ON public.ak_user_classes(user_id);
|
||
CREATE INDEX idx_user_classes_class_id ON public.ak_user_classes(class_id);
|
||
|
||
-- 设备表
|
||
CREATE TABLE public.ak_devices (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 设备ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
device_type VARCHAR(32) NOT NULL, -- 设备类型
|
||
device_name VARCHAR(64), -- 设备名称
|
||
device_mac VARCHAR(64), -- 设备MAC
|
||
bind_time TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 绑定时间
|
||
status VARCHAR(16) DEFAULT 'active', -- 状态
|
||
extra JSONB -- 扩展信息
|
||
);
|
||
COMMENT ON TABLE public.ak_devices IS '设备表';
|
||
|
||
CREATE INDEX idx_devices_user_id ON public.ak_devices(user_id);
|
||
|
||
-- 训练计划表
|
||
CREATE TABLE public.ak_training_plans (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 计划ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
plan_name VARCHAR(128) NOT NULL, -- 计划名称
|
||
description TEXT, -- 描述
|
||
start_date DATE, -- 开始日期
|
||
end_date DATE, -- 结束日期
|
||
status VARCHAR(16) DEFAULT 'active', -- 状态
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
COMMENT ON TABLE public.ak_training_plans IS '训练计划表';
|
||
|
||
CREATE INDEX idx_training_plans_user_id ON public.ak_training_plans(user_id);
|
||
|
||
-- 训练计划明细表
|
||
CREATE TABLE public.ak_training_plan_items (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 明细ID
|
||
plan_id uuid REFERENCES public.ak_training_plans(id) ON DELETE CASCADE, -- 计划ID
|
||
item_date DATE NOT NULL, -- 明细日期
|
||
activity_type VARCHAR(64), -- 活动类型
|
||
target_value FLOAT, -- 目标数值
|
||
unit VARCHAR(16), -- 单位
|
||
description TEXT, -- 描述
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_training_plan_items IS '训练计划明细表';
|
||
|
||
CREATE INDEX idx_training_plan_items_plan_id ON public.ak_training_plan_items(plan_id);
|
||
|
||
-- 训练记录表
|
||
CREATE TABLE public.ak_training_records (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 记录ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
plan_item_id uuid REFERENCES public.ak_training_plan_items(id), -- 计划明细ID
|
||
device_id uuid REFERENCES public.ak_devices(id), -- 设备ID
|
||
activity_type VARCHAR(64), -- 活动类型
|
||
start_time TIMESTAMP WITH TIME ZONE, -- 开始时间
|
||
end_time TIMESTAMP WITH TIME ZONE, -- 结束时间
|
||
duration_sec INT, -- 时长
|
||
distance_km FLOAT, -- 距离
|
||
calories FLOAT, -- 卡路里
|
||
steps INT, -- 步数
|
||
avg_heart_rate INT, -- 平均心率
|
||
max_heart_rate INT, -- 最大心率
|
||
data JSONB, -- 详细原始数据
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_training_records IS '训练记录表';
|
||
|
||
CREATE INDEX idx_training_records_user_id ON public.ak_training_records(user_id);
|
||
CREATE INDEX idx_training_records_plan_item_id ON public.ak_training_records(plan_item_id);
|
||
|
||
-- 体征监测数据表
|
||
CREATE TABLE public.ak_biometric_data (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 数据ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
device_id uuid REFERENCES public.ak_devices(id), -- 设备ID
|
||
data_type VARCHAR(32), -- 数据类型
|
||
value FLOAT, -- 数值
|
||
unit VARCHAR(16), -- 单位
|
||
measured_at TIMESTAMP WITH TIME ZONE, -- 测量时间
|
||
extra JSONB -- 扩展
|
||
);
|
||
COMMENT ON TABLE public.ak_biometric_data IS '体征监测数据表';
|
||
|
||
CREATE INDEX idx_biometric_data_user_id ON public.ak_biometric_data(user_id);
|
||
CREATE INDEX idx_biometric_data_device_id ON public.ak_biometric_data(device_id);
|
||
|
||
-- 视频分析结果表
|
||
CREATE TABLE public.ak_video_analysis (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 分析ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
device_id uuid REFERENCES public.ak_devices(id), -- 设备ID
|
||
record_id uuid REFERENCES public.ak_training_records(id), -- 训练记录ID
|
||
video_url TEXT, -- 视频地址
|
||
analysis_type VARCHAR(32), -- 分析类型
|
||
result JSONB, -- AI分析结果
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_video_analysis IS '视频分析结果表';
|
||
|
||
-- AI 评估报告表
|
||
CREATE TABLE public.ak_ai_reports (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 报告ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
record_id uuid REFERENCES public.ak_training_records(id), -- 训练记录ID
|
||
report_type VARCHAR(32), -- 报告类型
|
||
content JSONB, -- 评估内容
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_ai_reports IS 'AI评估报告表';
|
||
|
||
-- 消息/通知表
|
||
CREATE TABLE public.ak_notifications (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 通知ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
title VARCHAR(128), -- 标题
|
||
content TEXT, -- 内容
|
||
status VARCHAR(16) DEFAULT 'unread', -- 状态
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_notifications IS '消息/通知表';
|
||
|
||
-- 设备事件日志表
|
||
CREATE TABLE public.ak_device_events (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 日志ID
|
||
device_id uuid REFERENCES public.ak_devices(id) ON DELETE CASCADE, -- 设备ID
|
||
event_type VARCHAR(32), -- 事件类型
|
||
event_time TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 事件时间
|
||
data JSONB -- 事件数据
|
||
);
|
||
COMMENT ON TABLE public.ak_device_events IS '设备事件日志表';
|
||
|
||
-- 用户反馈表
|
||
CREATE TABLE public.ak_user_feedback (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 反馈ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
content TEXT, -- 反馈内容
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_user_feedback IS '用户反馈表';
|
||
|
||
-- 运动报告表
|
||
CREATE TABLE public.ak_sport_reports (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 报告ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
title VARCHAR(128) NOT NULL, -- 标题
|
||
content_md TEXT NOT NULL, -- Markdown内容
|
||
cover_url TEXT, -- 封面图片
|
||
visibility VARCHAR(16) DEFAULT 'private', -- 可见性
|
||
visible_until TIMESTAMP WITH TIME ZONE, -- 定时可见截止
|
||
allow_comment BOOLEAN DEFAULT true, -- 允许评论
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
COMMENT ON TABLE public.ak_sport_reports IS '运动报告表';
|
||
|
||
CREATE INDEX idx_sport_reports_user_id ON public.ak_sport_reports(user_id);
|
||
|
||
-- 运动报告分享记录表
|
||
CREATE TABLE public.ak_sport_report_shares (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 分享ID
|
||
report_id uuid REFERENCES public.ak_sport_reports(id) ON DELETE CASCADE, -- 报告ID
|
||
shared_by uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 分享人
|
||
shared_to uuid REFERENCES public.ak_users(id), -- 被分享人
|
||
share_type VARCHAR(16) DEFAULT 'link', -- 分享类型
|
||
share_token VARCHAR(64), -- 分享链接token
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
||
expired_at TIMESTAMP WITH TIME ZONE -- 过期时间
|
||
);
|
||
COMMENT ON TABLE public.ak_sport_report_shares IS '运动报告分享记录表';
|
||
|
||
CREATE INDEX idx_sport_report_shares_report_id ON public.ak_sport_report_shares(report_id);
|
||
CREATE INDEX idx_sport_report_shares_shared_by ON public.ak_sport_report_shares(shared_by);
|
||
|
||
-- 积分账户表
|
||
CREATE TABLE public.ak_user_points (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 账户ID
|
||
user_id uuid UNIQUE REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
total_points INT DEFAULT 0, -- 总积分
|
||
available_points INT DEFAULT 0, -- 可用积分
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
COMMENT ON TABLE public.ak_user_points IS '积分账户表';
|
||
|
||
-- 积分变动明细表
|
||
CREATE TABLE public.ak_user_point_logs (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 明细ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
change_type VARCHAR(32) NOT NULL, -- 变动类型
|
||
points INT NOT NULL, -- 积分数
|
||
reason TEXT, -- 变动原因
|
||
related_id uuid, -- 关联业务ID
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_user_point_logs IS '积分变动明细表';
|
||
|
||
CREATE INDEX idx_user_point_logs_user_id ON public.ak_user_point_logs(user_id);
|
||
|
||
-- 积分规则表
|
||
CREATE TABLE public.ak_point_rules (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 规则ID
|
||
rule_name VARCHAR(64) NOT NULL, -- 规则名称
|
||
event_code VARCHAR(64) NOT NULL, -- 事件标识
|
||
points INT NOT NULL, -- 积分数
|
||
limit_per_day INT, -- 每日上限
|
||
description TEXT, -- 描述
|
||
is_active BOOLEAN DEFAULT true, -- 是否启用
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
COMMENT ON TABLE public.ak_point_rules IS '积分规则表';
|
||
|
||
-- 积分交易记录表
|
||
CREATE TABLE public.ak_point_transactions (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 交易ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
transaction_type VARCHAR(32) NOT NULL, -- 交易类型 (earn, spend, refund等)
|
||
points INT NOT NULL, -- 积分数量
|
||
description TEXT, -- 交易描述
|
||
reference_id uuid, -- 关联业务ID (如训练记录ID、商品ID等)
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_point_transactions IS '积分交易记录表';
|
||
COMMENT ON COLUMN public.ak_point_transactions.transaction_type IS '交易类型：earn(获得), spend(消费), refund(退还)等';
|
||
COMMENT ON COLUMN public.ak_point_transactions.points IS '积分数量，正数表示获得，负数表示消费';
|
||
COMMENT ON COLUMN public.ak_point_transactions.reference_id IS '关联业务ID，如训练记录ID、商品ID等';
|
||
|
||
CREATE INDEX idx_point_transactions_user_id ON public.ak_point_transactions(user_id);
|
||
CREATE INDEX idx_point_transactions_type ON public.ak_point_transactions(transaction_type);
|
||
CREATE INDEX idx_point_transactions_created_at ON public.ak_point_transactions(created_at);
|
||
|
||
-- 作业表
|
||
CREATE TABLE public.ak_assignments (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 作业ID
|
||
teacher_id uuid REFERENCES public.ak_users(id) ON DELETE SET NULL, -- 老师ID
|
||
title VARCHAR(128) NOT NULL, -- 作业标题
|
||
project_name VARCHAR(128), -- 作业所属项目/主题
|
||
status VARCHAR(32) DEFAULT 'active', -- 作业状态
|
||
start_time TIMESTAMP WITH TIME ZONE, -- 开始时间
|
||
end_time TIMESTAMP WITH TIME ZONE, -- 结束时间
|
||
description TEXT, -- 作业描述
|
||
class_id uuid REFERENCES public.ak_classes(id) ON DELETE CASCADE, -- 班级ID
|
||
due_date TIMESTAMP WITH TIME ZONE, -- 截止时间
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
COMMENT ON TABLE public.ak_assignments IS '作业表';
|
||
|
||
CREATE INDEX idx_assignments_teacher_id ON public.ak_assignments(teacher_id);
|
||
CREATE INDEX idx_assignments_class_id ON public.ak_assignments(class_id);
|
||
|
||
-- 作业提交表
|
||
CREATE TABLE public.ak_assignment_submissions (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 提交ID
|
||
assignment_id uuid REFERENCES public.ak_assignments(id) ON DELETE CASCADE, -- 作业ID
|
||
student_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 学生ID
|
||
submit_time TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 提交时间
|
||
content_md TEXT, -- Markdown作业内容
|
||
attachment_url TEXT, -- 附件S3地址
|
||
score FLOAT, -- 分数
|
||
feedback TEXT, -- 反馈
|
||
status VARCHAR(16) DEFAULT 'submitted', -- 状态
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
COMMENT ON TABLE public.ak_assignment_submissions IS '作业提交表';
|
||
|
||
CREATE INDEX idx_assignment_submissions_assignment_id ON public.ak_assignment_submissions(assignment_id);
|
||
CREATE INDEX idx_assignment_submissions_student_id ON public.ak_assignment_submissions(student_id);
|
||
|
||
-- 作业统计表
|
||
CREATE TABLE public.ak_assignment_stats (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 统计ID
|
||
assignment_id uuid REFERENCES public.ak_assignments(id) ON DELETE CASCADE, -- 作业ID
|
||
total_students INT, -- 总学生数
|
||
submitted_count INT, -- 已提交数
|
||
reviewed_count INT, -- 已批改数
|
||
avg_score FLOAT, -- 平均分
|
||
last_updated TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
COMMENT ON TABLE public.ak_assignment_stats IS '作业统计表';
|
||
|
||
-- 教学资源表
|
||
CREATE TABLE public.ak_teaching_resources (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 资源ID
|
||
teacher_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 教师ID
|
||
type VARCHAR(32) NOT NULL, -- 资源类型：video(视频), document(文档), plan(计划), image(图片)等
|
||
title VARCHAR(128) NOT NULL, -- 资源标题
|
||
description TEXT, -- 资源描述
|
||
content_url TEXT, -- 资源内容URL (S3地址等)
|
||
tags JSONB, -- 标签数组 (JSON格式)
|
||
is_public BOOLEAN DEFAULT false, -- 是否公开
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
COMMENT ON TABLE public.ak_teaching_resources IS '教学资源表';
|
||
COMMENT ON COLUMN public.ak_teaching_resources.type IS '资源类型：video(视频), document(文档), plan(计划), image(图片)等';
|
||
COMMENT ON COLUMN public.ak_teaching_resources.tags IS '标签数组，JSON格式存储';
|
||
COMMENT ON COLUMN public.ak_teaching_resources.is_public IS '是否公开，public资源可被其他教师查看';
|
||
|
||
CREATE INDEX idx_teaching_resources_teacher_id ON public.ak_teaching_resources(teacher_id);
|
||
CREATE INDEX idx_teaching_resources_type ON public.ak_teaching_resources(type);
|
||
CREATE INDEX idx_teaching_resources_is_public ON public.ak_teaching_resources(is_public);
|
||
|
||
-- 课程安排表
|
||
CREATE TABLE public.ak_class_schedules (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 课程安排ID
|
||
teacher_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 教师ID
|
||
school_id uuid REFERENCES public.ak_schools(id) ON DELETE CASCADE, -- 学校ID
|
||
class_id uuid REFERENCES public.ak_classes(id) ON DELETE CASCADE, -- 班级ID
|
||
subject VARCHAR(64) NOT NULL, -- 科目名称
|
||
day_of_week INT NOT NULL CHECK (day_of_week >= 1 AND day_of_week <= 7), -- 星期几 (1=周一, 7=周日)
|
||
start_time TIME NOT NULL, -- 开始时间
|
||
end_time TIME NOT NULL, -- 结束时间
|
||
location VARCHAR(128), -- 上课地点
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
COMMENT ON TABLE public.ak_class_schedules IS '课程安排表';
|
||
COMMENT ON COLUMN public.ak_class_schedules.day_of_week IS '星期几，1=周一，2=周二...7=周日';
|
||
COMMENT ON COLUMN public.ak_class_schedules.subject IS '科目名称，如体育、数学等';
|
||
|
||
CREATE INDEX idx_class_schedules_teacher_id ON public.ak_class_schedules(teacher_id);
|
||
CREATE INDEX idx_class_schedules_school_id ON public.ak_class_schedules(school_id);
|
||
CREATE INDEX idx_class_schedules_class_id ON public.ak_class_schedules(class_id);
|
||
CREATE INDEX idx_class_schedules_day_of_week ON public.ak_class_schedules(day_of_week);
|
||
|
||
-- 互动记录表
|
||
CREATE TABLE public.ak_interactions (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 互动ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID (发起互动的用户)
|
||
target_type VARCHAR(32) NOT NULL, -- 目标类型：training_record, assignment_submission, teaching_resource等
|
||
target_id uuid NOT NULL, -- 目标ID (被互动的对象ID)
|
||
interaction_type VARCHAR(32) NOT NULL, -- 互动类型：comment(评论), like(点赞), share(分享)等
|
||
content TEXT, -- 互动内容 (如评论内容，点赞时可为空)
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
COMMENT ON TABLE public.ak_interactions IS '互动记录表';
|
||
COMMENT ON COLUMN public.ak_interactions.target_type IS '目标类型：training_record(训练记录), assignment_submission(作业提交), teaching_resource(教学资源)等';
|
||
COMMENT ON COLUMN public.ak_interactions.interaction_type IS '互动类型：comment(评论), like(点赞), share(分享)等';
|
||
|
||
CREATE INDEX idx_interactions_user_id ON public.ak_interactions(user_id);
|
||
CREATE INDEX idx_interactions_target ON public.ak_interactions(target_type, target_id);
|
||
CREATE INDEX idx_interactions_type ON public.ak_interactions(interaction_type);
|
||
|
||
-- 老师权限管理表
|
||
CREATE TABLE public.ak_teacher_roles (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 角色ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
school_id uuid REFERENCES public.ak_schools(id), -- 学校ID
|
||
class_id uuid REFERENCES public.ak_classes(id), -- 班级ID
|
||
role VARCHAR(32) DEFAULT 'teacher', -- 角色
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_teacher_roles IS '老师权限管理表';
|
||
|
||
CREATE INDEX idx_teacher_roles_user_id ON public.ak_teacher_roles(user_id);
|
||
CREATE INDEX idx_teacher_roles_school_id ON public.ak_teacher_roles(school_id);
|
||
CREATE INDEX idx_teacher_roles_class_id ON public.ak_teacher_roles(class_id);
|
||
|
||
-- 角色定义表
|
||
CREATE TABLE public.ak_roles (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 角色ID
|
||
name VARCHAR(32) NOT NULL UNIQUE, -- 角色名称
|
||
description TEXT, -- 角色描述
|
||
level INT NOT NULL, -- 角色级别（数字越大权限越高）
|
||
is_system BOOLEAN DEFAULT false, -- 是否为系统角色
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_roles IS '角色定义表';
|
||
|
||
-- 插入基础角色
|
||
INSERT INTO public.ak_roles (name, description, level, is_system) VALUES
|
||
('student', '学生用户', 10, true),
|
||
('teacher', '教师用户', 20, true),
|
||
('school_admin', '学校管理员', 30, true),
|
||
('region_admin', '区域管理员', 40, true),
|
||
('system_admin', '系统管理员', 100, true);
|
||
|
||
-- 权限定义表
|
||
CREATE TABLE public.ak_permissions (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 权限ID
|
||
code VARCHAR(64) NOT NULL UNIQUE, -- 权限代码
|
||
name VARCHAR(128) NOT NULL, -- 权限名称
|
||
description TEXT, -- 权限描述
|
||
resource_type VARCHAR(32) NOT NULL, -- 资源类型
|
||
action VARCHAR(32) NOT NULL, -- 操作类型：create, read, update, delete, manage
|
||
is_system BOOLEAN DEFAULT false, -- 是否为系统权限
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_permissions IS '权限定义表';
|
||
|
||
-- 角色-权限关联表
|
||
CREATE TABLE public.ak_role_permissions (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 关联ID
|
||
role_id uuid REFERENCES public.ak_roles(id) ON DELETE CASCADE, -- 角色ID
|
||
permission_id uuid REFERENCES public.ak_permissions(id) ON DELETE CASCADE, -- 权限ID
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_role_permissions IS '角色-权限关联表';
|
||
CREATE UNIQUE INDEX idx_role_permission_unique ON public.ak_role_permissions(role_id, permission_id);
|
||
CREATE INDEX idx_role_permissions_role_id ON public.ak_role_permissions(role_id);
|
||
CREATE INDEX idx_role_permissions_permission_id ON public.ak_role_permissions(permission_id);
|
||
|
||
-- 用户-角色关联表
|
||
CREATE TABLE public.ak_user_roles (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 关联ID
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID
|
||
role_id uuid REFERENCES public.ak_roles(id) ON DELETE CASCADE, -- 角色ID
|
||
scope_type VARCHAR(32), -- 作用域类型：global, region, school, grade, class
|
||
scope_id uuid, -- 作用域ID
|
||
created_by uuid REFERENCES public.ak_users(id), -- 创建人
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 创建时间
|
||
);
|
||
COMMENT ON TABLE public.ak_user_roles IS '用户-角色关联表';
|
||
CREATE INDEX idx_user_roles_user_id ON public.ak_user_roles(user_id);
|
||
CREATE INDEX idx_user_roles_role_id ON public.ak_user_roles(role_id);
|
||
COMMENT ON COLUMN public.ak_user_roles.scope_type IS '作用域类型，指定角色在哪个范围内有效';
|
||
COMMENT ON COLUMN public.ak_user_roles.scope_id IS '作用域ID，对应scope_type的记录ID';
|
||
|
||
-- 辅助函数：检查用户是否拥有指定权限
|
||
CREATE OR REPLACE FUNCTION public.has_permission(user_id uuid, permission_code text)
|
||
RETURNS boolean AS $$
|
||
DECLARE
|
||
has_perm boolean;
|
||
BEGIN
|
||
SELECT EXISTS(
|
||
SELECT 1 FROM public.ak_user_roles ur
|
||
JOIN public.ak_role_permissions rp ON ur.role_id = rp.role_id
|
||
JOIN public.ak_permissions p ON rp.permission_id = p.id
|
||
WHERE ur.user_id = $1 AND p.code = $2
|
||
) INTO has_perm;
|
||
|
||
RETURN has_perm;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 辅助函数：检查用户是否拥有指定作用域的权限
|
||
CREATE OR REPLACE FUNCTION public.has_scoped_permission(
|
||
user_id uuid,
|
||
permission_code text,
|
||
scope_type text,
|
||
scope_id uuid
|
||
)
|
||
RETURNS boolean AS $$
|
||
DECLARE
|
||
has_perm boolean;
|
||
BEGIN
|
||
-- 检查全局权限
|
||
SELECT EXISTS(
|
||
SELECT 1 FROM public.ak_user_roles ur
|
||
JOIN public.ak_role_permissions rp ON ur.role_id = rp.role_id
|
||
JOIN public.ak_permissions p ON rp.permission_id = p.id
|
||
WHERE ur.user_id = $1
|
||
AND p.code = $2
|
||
AND (ur.scope_type IS NULL OR ur.scope_type = 'global')
|
||
) INTO has_perm;
|
||
|
||
IF has_perm THEN
|
||
RETURN true;
|
||
END IF;
|
||
|
||
-- 检查特定作用域的权限
|
||
SELECT EXISTS(
|
||
SELECT 1 FROM public.ak_user_roles ur
|
||
JOIN public.ak_role_permissions rp ON ur.role_id = rp.role_id
|
||
JOIN public.ak_permissions p ON rp.permission_id = p.id
|
||
WHERE ur.user_id = $1
|
||
AND p.code = $2
|
||
AND ur.scope_type = $3
|
||
AND ur.scope_id = $4
|
||
) INTO has_perm;
|
||
|
||
RETURN has_perm;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 修改用户表触发器，自动分配学生角色
|
||
CREATE OR REPLACE FUNCTION public.handle_new_user()
|
||
RETURNS TRIGGER AS $$
|
||
DECLARE
|
||
student_role_id uuid;
|
||
new_user_id uuid;
|
||
BEGIN
|
||
-- 创建基本用户记录
|
||
INSERT INTO public.ak_users (auth_id, username, email, role)
|
||
VALUES (new.id, new.email, new.email, 'student')
|
||
RETURNING id INTO new_user_id;
|
||
|
||
-- 获取学生角色ID
|
||
SELECT id INTO student_role_id FROM public.ak_roles WHERE name = 'student';
|
||
|
||
-- 分配学生角色
|
||
IF student_role_id IS NOT NULL THEN
|
||
INSERT INTO public.ak_user_roles (user_id, role_id, scope_type)
|
||
VALUES (new_user_id, student_role_id, 'global');
|
||
END IF;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 示例权限数据
|
||
INSERT INTO public.ak_permissions (code, name, resource_type, action, is_system, description) VALUES
|
||
-- 学生权限
|
||
('student.profile.read', '查看个人资料', 'profile', 'read', true, '查看自己的个人资料'),
|
||
('student.profile.update', '更新个人资料', 'profile', 'update', true, '更新自己的个人资料'),
|
||
('student.training.read', '查看训练记录', 'training', 'read', true, '查看自己的训练记录'),
|
||
('student.training.create', '创建训练记录', 'training', 'create', true, '创建自己的训练记录'),
|
||
|
||
-- 教师权限
|
||
('teacher.student.read', '查看学生资料', 'student', 'read', true, '查看所教班级学生的资料'),
|
||
('teacher.class.read', '查看班级信息', 'class', 'read', true, '查看所教班级信息'),
|
||
('teacher.assignment.manage', '管理作业', 'assignment', 'manage', true, '创建、修改班级作业'),
|
||
('teacher.report.read', '查看学生报告', 'report', 'read', true, '查看学生训练报告'),
|
||
|
||
-- 学校管理员权限
|
||
('school_admin.teacher.manage', '管理教师', 'teacher', 'manage', true, '管理学校教师账号'),
|
||
('school_admin.class.manage', '管理班级', 'class', 'manage', true, '管理学校班级'),
|
||
('school_admin.grade.manage', '管理年级', 'grade', 'manage', true, '管理学校年级'),
|
||
('school_admin.report.school', '学校统计报表', 'report', 'read', true, '查看学校统计报表'),
|
||
|
||
-- 系统管理员权限
|
||
('admin.system.manage', '系统管理', 'system', 'manage', true, '系统管理权限'),
|
||
('admin.users.manage', '用户管理', 'users', 'manage', true, '管理所有用户'),
|
||
('admin.roles.manage', '角色权限管理', 'roles', 'manage', true, '管理角色和权限');
|
||
|
||
-- 为角色分配基础权限
|
||
INSERT INTO public.ak_role_permissions (role_id, permission_id)
|
||
SELECT r.id, p.id FROM public.ak_roles r, public.ak_permissions p
|
||
WHERE
|
||
(r.name = 'student' AND p.code LIKE 'student.%') OR
|
||
(r.name = 'teacher' AND (p.code LIKE 'teacher.%' OR p.code LIKE 'student.%')) OR
|
||
(r.name = 'school_admin' AND (p.code LIKE 'school_admin.%' OR p.code LIKE 'teacher.%' OR p.code LIKE 'student.%')) OR
|
||
(r.name = 'system_admin' AND p.code LIKE '%');
|
||
|
||
-- 为所有数据表添加行级安全策略的辅助函数
|
||
CREATE OR REPLACE FUNCTION public.current_user_has_permission(permission_code text)
|
||
RETURNS boolean AS $$
|
||
DECLARE
|
||
user_id uuid;
|
||
BEGIN
|
||
-- 获取当前用户的ID
|
||
SELECT id INTO user_id FROM public.ak_users WHERE auth_id = auth.uid();
|
||
|
||
IF user_id IS NULL THEN
|
||
RETURN false;
|
||
END IF;
|
||
|
||
RETURN public.has_permission(user_id, permission_code);
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 示例：为班级表添加RLS策略
|
||
ALTER TABLE public.ak_classes ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- 系统管理员和学校管理员可以管理所有班级
|
||
CREATE POLICY "管理员可以管理所有班级" ON public.ak_classes
|
||
USING (public.current_user_has_permission('admin.system.manage') OR
|
||
public.current_user_has_permission('school_admin.class.manage'));
|
||
|
||
-- 教师可以查看他们教授的班级
|
||
CREATE POLICY "教师可以查看所教授的班级" ON public.ak_classes
|
||
FOR SELECT
|
||
USING (EXISTS (
|
||
SELECT 1 FROM public.ak_teacher_roles tr
|
||
JOIN public.ak_users u ON tr.user_id = u.id
|
||
WHERE u.auth_id = auth.uid() AND tr.class_id = ak_classes.id
|
||
));
|
||
|
||
-- 添加i18n国际化支持
|
||
|
||
-- 插入基础语言
|
||
INSERT INTO public.ak_languages (code, name, native_name, is_active, is_default) VALUES
|
||
('zh-CN', 'Chinese (Simplified)', '简体中文', true, true),
|
||
('en-US', 'English (US)', 'English (US)', true, false);
|
||
|
||
-- 数据翻译基础表 - 用于继承
|
||
CREATE TABLE public.ak_translations (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 翻译ID
|
||
record_id uuid NOT NULL, -- 关联记录ID
|
||
language_id uuid REFERENCES public.ak_languages(id) ON DELETE CASCADE, -- 语言ID
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
COMMENT ON TABLE public.ak_translations IS '数据翻译基础表(用于继承)';
|
||
CREATE UNIQUE INDEX idx_translations_record_language ON public.ak_translations (record_id, language_id);
|
||
CREATE INDEX idx_translations_language ON public.ak_translations (language_id);
|
||
|
||
-- 区域名称翻译表
|
||
CREATE TABLE public.ak_region_translations (
|
||
region_name VARCHAR(128) NOT NULL, -- 区域名称
|
||
CONSTRAINT ak_region_translations_pk PRIMARY KEY (id)
|
||
) INHERITS (public.ak_translations);
|
||
COMMENT ON TABLE public.ak_region_translations IS '区域名称翻译表';
|
||
ALTER TABLE public.ak_region_translations
|
||
ADD CONSTRAINT fk_region_translations_record
|
||
FOREIGN KEY (record_id)
|
||
REFERENCES public.ak_regions(id) ON DELETE CASCADE;
|
||
|
||
-- 学校名称翻译表
|
||
CREATE TABLE public.ak_school_translations (
|
||
school_name VARCHAR(128) NOT NULL, -- 学校名称
|
||
school_type VARCHAR(32), -- 学校类型
|
||
CONSTRAINT ak_school_translations_pk PRIMARY KEY (id)
|
||
) INHERITS (public.ak_translations);
|
||
COMMENT ON TABLE public.ak_school_translations IS '学校名称翻译表';
|
||
ALTER TABLE public.ak_school_translations
|
||
ADD CONSTRAINT fk_school_translations_record
|
||
FOREIGN KEY (record_id)
|
||
REFERENCES public.ak_schools(id) ON DELETE CASCADE;
|
||
|
||
-- 应用翻译表 (用于前端界面文本)
|
||
CREATE TABLE public.ak_app_translations (
|
||
key VARCHAR(255) NOT NULL, -- 翻译键
|
||
text TEXT NOT NULL, -- 翻译文本
|
||
language_id uuid REFERENCES public.ak_languages(id) ON DELETE CASCADE, -- 语言ID
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
COMMENT ON TABLE public.ak_app_translations IS '应用翻译表(前端界面文本)';
|
||
ALTER TABLE public.ak_app_translations ADD PRIMARY KEY (key, language_id);
|
||
CREATE INDEX idx_app_translations_language ON public.ak_app_translations (language_id);
|
||
|
||
-- 获取当前用户语言的函数
|
||
CREATE OR REPLACE FUNCTION public.get_user_language()
|
||
RETURNS uuid AS $$
|
||
DECLARE
|
||
lang_id uuid;
|
||
default_lang_id uuid;
|
||
BEGIN
|
||
-- 查询用户的首选语言
|
||
SELECT u.preferred_language INTO lang_id
|
||
FROM public.ak_users u
|
||
WHERE u.auth_id = auth.uid();
|
||
|
||
-- 如果没有设置或用户未登录，返回默认语言
|
||
IF lang_id IS NULL THEN
|
||
SELECT id INTO default_lang_id
|
||
FROM public.ak_languages
|
||
WHERE is_default = true
|
||
LIMIT 1;
|
||
|
||
RETURN default_lang_id;
|
||
END IF;
|
||
|
||
RETURN lang_id;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 获取翻译文本的函数 (通用版本)
|
||
CREATE OR REPLACE FUNCTION public.get_translation(
|
||
p_table_name text,
|
||
p_record_id uuid,
|
||
p_column_name text,
|
||
p_language_id uuid DEFAULT NULL
|
||
)
|
||
RETURNS text AS $$
|
||
DECLARE
|
||
result text;
|
||
lang_id uuid;
|
||
default_lang_id uuid;
|
||
query_text text;
|
||
BEGIN
|
||
-- 确定使用的语言ID
|
||
IF p_language_id IS NULL THEN
|
||
lang_id := public.get_user_language();
|
||
ELSE
|
||
lang_id := p_language_id;
|
||
END IF;
|
||
|
||
-- 构建查询语句动态获取翻译
|
||
query_text := 'SELECT t.' || quote_ident(p_column_name) ||
|
||
' FROM public.' || quote_ident('ak_' || p_table_name || '_translations') || ' t ' ||
|
||
'WHERE t.record_id = $1 AND t.language_id = $2';
|
||
|
||
-- 执行查询
|
||
EXECUTE query_text INTO result USING p_record_id, lang_id;
|
||
|
||
-- 如果没有找到翻译，尝试使用默认语言
|
||
IF result IS NULL THEN
|
||
SELECT id INTO default_lang_id
|
||
FROM public.ak_languages
|
||
WHERE is_default = true
|
||
LIMIT 1;
|
||
|
||
IF default_lang_id != lang_id THEN
|
||
EXECUTE query_text INTO result USING p_record_id, default_lang_id;
|
||
END IF;
|
||
END IF;
|
||
|
||
RETURN result;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 获取区域翻译名称的便捷函数
|
||
CREATE OR REPLACE FUNCTION public.get_region_name(
|
||
p_region_id uuid,
|
||
p_language_id uuid DEFAULT NULL
|
||
)
|
||
RETURNS text AS $$
|
||
DECLARE
|
||
translated_name text;
|
||
original_name text;
|
||
BEGIN
|
||
-- 尝试获取翻译
|
||
translated_name := public.get_translation('region', p_region_id, 'region_name', p_language_id);
|
||
|
||
-- 如果没有翻译，返回原始名称
|
||
IF translated_name IS NULL THEN
|
||
SELECT name INTO original_name
|
||
FROM public.ak_regions
|
||
WHERE id = p_region_id;
|
||
|
||
RETURN original_name;
|
||
END IF;
|
||
|
||
RETURN translated_name;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 获取学校翻译名称的便捷函数
|
||
CREATE OR REPLACE FUNCTION public.get_school_name(
|
||
p_school_id uuid,
|
||
p_language_id uuid DEFAULT NULL
|
||
)
|
||
RETURNS text AS $$
|
||
DECLARE
|
||
translated_name text;
|
||
original_name text;
|
||
BEGIN
|
||
-- 尝试获取翻译
|
||
translated_name := public.get_translation('school', p_school_id, 'school_name', p_language_id);
|
||
|
||
-- 如果没有翻译，返回原始名称
|
||
IF translated_name IS NULL THEN
|
||
SELECT name INTO original_name
|
||
FROM public.ak_schools
|
||
WHERE id = p_school_id;
|
||
|
||
RETURN original_name;
|
||
END IF;
|
||
|
||
RETURN translated_name;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 获取前端界面文本翻译的函数
|
||
CREATE OR REPLACE FUNCTION public.get_ui_text(
|
||
p_key text,
|
||
p_language_id uuid DEFAULT NULL
|
||
)
|
||
RETURNS text AS $$
|
||
DECLARE
|
||
result text;
|
||
lang_id uuid;
|
||
default_lang_id uuid;
|
||
BEGIN
|
||
-- 确定使用的语言ID
|
||
IF p_language_id IS NULL THEN
|
||
lang_id := public.get_user_language();
|
||
ELSE
|
||
lang_id := p_language_id;
|
||
END IF;
|
||
|
||
-- 获取翻译
|
||
SELECT text INTO result
|
||
FROM public.ak_app_translations
|
||
WHERE key = p_key AND language_id = lang_id;
|
||
|
||
-- 如果没有找到翻译，尝试使用默认语言
|
||
IF result IS NULL THEN
|
||
SELECT id INTO default_lang_id
|
||
FROM public.ak_languages
|
||
WHERE is_default = true
|
||
LIMIT 1;
|
||
|
||
IF default_lang_id != lang_id THEN
|
||
SELECT text INTO result
|
||
FROM public.ak_app_translations
|
||
WHERE key = p_key AND language_id = default_lang_id;
|
||
END IF;
|
||
END IF;
|
||
|
||
-- 如果仍然没有找到翻译，返回键名
|
||
IF result IS NULL THEN
|
||
RETURN p_key;
|
||
END IF;
|
||
|
||
RETURN result;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 创建翻译视图示例：区域多语言视图
|
||
CREATE OR REPLACE VIEW public.vw_regions_i18n AS
|
||
SELECT
|
||
r.id,
|
||
r.parent_id,
|
||
r.level,
|
||
r.created_at,
|
||
l.id as language_id,
|
||
l.code as language_code,
|
||
COALESCE(
|
||
rt.region_name,
|
||
r.name
|
||
) as name
|
||
FROM
|
||
public.ak_regions r
|
||
CROSS JOIN
|
||
public.ak_languages l
|
||
LEFT JOIN
|
||
public.ak_region_translations rt ON r.id = rt.record_id AND l.id = rt.language_id;
|
||
COMMENT ON VIEW public.vw_regions_i18n IS '区域多语言视图';
|
||
|
||
/*
|
||
i18n国际化系统说明：
|
||
|
||
1. 核心设计：
|
||
- 通过ak_languages表定义支持的语言
|
||
- 主表存储默认语言(通常为中文)数据
|
||
- 翻译表以主表ID为外键，存储其他语言的翻译
|
||
|
||
2. 翻译表结构：
|
||
- 基础翻译表ak_translations包含通用字段
|
||
- 特定内容的翻译表继承基础表并添加特定字段
|
||
- 组合索引确保每个记录的每种语言只有一个翻译
|
||
|
||
3. 用户语言偏好：
|
||
- 用户表增加preferred_language字段存储语言偏好
|
||
- get_user_language()函数获取当前用户的语言设置
|
||
|
||
4. 获取翻译的方式：
|
||
- 直接SQL查询翻译表
|
||
- 使用辅助函数如get_translation(), get_region_name()
|
||
- 通过多语言视图(如vw_regions_i18n)
|
||
|
||
5. 前端界面文本翻译：
|
||
- ak_app_translations表存储UI文本翻译
|
||
- get_ui_text()函数根据key获取对应语言的文本
|
||
*/
|