520 lines
12 KiB
JavaScript
520 lines
12 KiB
JavaScript
-- =============================================================================
|
||
-- Supabase 消息系统客户端配置和使用示例
|
||
-- JavaScript/TypeScript 客户端代码示例
|
||
-- =============================================================================
|
||
|
||
/*
|
||
1. Supabase 客户端初始化
|
||
*/
|
||
|
||
import { createClient } from '@supabase/supabase-js'
|
||
|
||
const supabaseUrl = 'your-supabase-url'
|
||
const supabaseAnonKey = 'your-supabase-anon-key'
|
||
|
||
export const supabase = createClient(supabaseUrl, supabaseAnonKey, {
|
||
auth: {
|
||
autoRefreshToken: true,
|
||
persistSession: true,
|
||
detectSessionInUrl: true
|
||
},
|
||
realtime: {
|
||
params: {
|
||
eventsPerSecond: 10
|
||
}
|
||
}
|
||
})
|
||
|
||
/*
|
||
2. 用户认证和角色设置
|
||
*/
|
||
|
||
// 登录函数 - 需要在JWT中包含用户角色
|
||
export async function signInUser(email: string, password: string) {
|
||
const { data, error } = await supabase.auth.signInWithPassword({
|
||
email,
|
||
password
|
||
})
|
||
|
||
if (error) throw error
|
||
|
||
// 获取用户角色信息（从数据库或其他来源）
|
||
const userRole = await getUserRole(data.user.id)
|
||
|
||
// 更新用户元数据包含角色信息
|
||
await supabase.auth.updateUser({
|
||
data: {
|
||
user_role: userRole,
|
||
role: userRole
|
||
}
|
||
})
|
||
|
||
return data
|
||
}
|
||
|
||
// 获取用户角色
|
||
async function getUserRole(userId: string): Promise<string> {
|
||
// 从用户表获取角色信息
|
||
const { data, error } = await supabase
|
||
.from('ak_users')
|
||
.select('role, user_type')
|
||
.eq('id', userId)
|
||
.single()
|
||
|
||
if (error) throw error
|
||
|
||
return data.role || data.user_type || 'student'
|
||
}
|
||
|
||
/*
|
||
3. 消息查询 - 利用RLS自动过滤
|
||
*/
|
||
|
||
// 获取用户消息列表（RLS会自动过滤权限）
|
||
export async function getUserMessages(userId: string, limit = 20) {
|
||
const { data, error } = await supabase
|
||
.from('ak_messages')
|
||
.select(`
|
||
id,
|
||
title,
|
||
content,
|
||
created_at,
|
||
is_urgent,
|
||
sender_name,
|
||
ak_message_types (
|
||
name,
|
||
icon,
|
||
color
|
||
),
|
||
ak_message_recipients!inner (
|
||
status,
|
||
read_at,
|
||
is_starred,
|
||
is_archived
|
||
)
|
||
`)
|
||
.eq('ak_message_recipients.recipient_id', userId)
|
||
.eq('ak_message_recipients.recipient_type', 'user')
|
||
.eq('ak_message_recipients.is_deleted', false)
|
||
.order('created_at', { ascending: false })
|
||
.limit(limit)
|
||
|
||
if (error) throw error
|
||
return data
|
||
}
|
||
|
||
// 获取未读消息数量
|
||
export async function getUnreadCount(userId: string) {
|
||
const { data, error } = await supabase
|
||
.from('ak_message_recipients')
|
||
.select('message_id', { count: 'exact' })
|
||
.eq('recipient_id', userId)
|
||
.eq('recipient_type', 'user')
|
||
.neq('status', 'read')
|
||
.eq('is_deleted', false)
|
||
|
||
if (error) throw error
|
||
return data?.length || 0
|
||
}
|
||
|
||
// 获取消息类型未读统计
|
||
export async function getUnreadCountByType(userId: string) {
|
||
const { data, error } = await supabase
|
||
.rpc('get_unread_message_count', { p_user_id: userId })
|
||
|
||
if (error) throw error
|
||
return data
|
||
}
|
||
|
||
/*
|
||
4. 发送消息 - 使用系统函数
|
||
*/
|
||
|
||
// 发送消息（教师发送给学生）
|
||
export async function sendMessage(messageData: {
|
||
messageType: string
|
||
receiverType: string
|
||
receiverId: string
|
||
title: string
|
||
content: string
|
||
priority?: number
|
||
metadata?: any
|
||
}) {
|
||
const { data, error } = await supabase
|
||
.rpc('send_message', {
|
||
p_message_type_code: messageData.messageType,
|
||
p_sender_type: 'user',
|
||
p_sender_id: (await supabase.auth.getUser()).data.user?.id,
|
||
p_receiver_type: messageData.receiverType,
|
||
p_receiver_id: messageData.receiverId,
|
||
p_title: messageData.title,
|
||
p_content: messageData.content,
|
||
p_priority: messageData.priority || 50,
|
||
p_metadata: messageData.metadata || null
|
||
})
|
||
|
||
if (error) throw error
|
||
return data
|
||
}
|
||
|
||
// 教师发送作业通知
|
||
export async function sendAssignmentNotification(
|
||
studentId: string,
|
||
assignmentTitle: string,
|
||
dueDate: string,
|
||
description: string
|
||
) {
|
||
return await sendMessage({
|
||
messageType: 'assignment',
|
||
receiverType: 'user',
|
||
receiverId: studentId,
|
||
title: `新作业：${assignmentTitle}`,
|
||
content: `${description}\n\n截止时间：${dueDate}`,
|
||
priority: 85,
|
||
metadata: {
|
||
assignment_title: assignmentTitle,
|
||
due_date: dueDate,
|
||
type: 'assignment_notification'
|
||
}
|
||
})
|
||
}
|
||
|
||
/*
|
||
5. 消息操作 - 标记已读、标星等
|
||
*/
|
||
|
||
// 标记消息为已读
|
||
export async function markMessageAsRead(messageId: string, userId: string) {
|
||
const { data, error } = await supabase
|
||
.rpc('mark_message_read', {
|
||
p_message_id: messageId,
|
||
p_user_id: userId
|
||
})
|
||
|
||
if (error) throw error
|
||
return data
|
||
}
|
||
|
||
// 标记消息为星标
|
||
export async function toggleMessageStar(messageId: string, userId: string, isStarred: boolean) {
|
||
const { data, error } = await supabase
|
||
.from('ak_message_recipients')
|
||
.update({ is_starred: isStarred })
|
||
.eq('message_id', messageId)
|
||
.eq('recipient_id', userId)
|
||
.eq('recipient_type', 'user')
|
||
|
||
if (error) throw error
|
||
return data
|
||
}
|
||
|
||
// 归档消息
|
||
export async function archiveMessage(messageId: string, userId: string) {
|
||
const { data, error } = await supabase
|
||
.from('ak_message_recipients')
|
||
.update({ is_archived: true })
|
||
.eq('message_id', messageId)
|
||
.eq('recipient_id', userId)
|
||
.eq('recipient_type', 'user')
|
||
|
||
if (error) throw error
|
||
return data
|
||
}
|
||
|
||
/*
|
||
6. 群组消息功能
|
||
*/
|
||
|
||
// 获取用户群组列表
|
||
export async function getUserGroups(userId: string) {
|
||
const { data, error } = await supabase
|
||
.from('ak_message_groups')
|
||
.select(`
|
||
id,
|
||
name,
|
||
description,
|
||
group_type,
|
||
is_public,
|
||
created_at,
|
||
ak_message_group_members!inner (
|
||
role,
|
||
status,
|
||
joined_at
|
||
)
|
||
`)
|
||
.eq('ak_message_group_members.user_id', userId)
|
||
.eq('ak_message_group_members.status', 'active')
|
||
.eq('is_active', true)
|
||
|
||
if (error) throw error
|
||
return data
|
||
}
|
||
|
||
// 发送群组消息
|
||
export async function sendGroupMessage(
|
||
groupId: string,
|
||
title: string,
|
||
content: string,
|
||
metadata?: any
|
||
) {
|
||
return await sendMessage({
|
||
messageType: 'chat',
|
||
receiverType: 'group',
|
||
receiverId: groupId,
|
||
title: title,
|
||
content: content,
|
||
priority: 60,
|
||
metadata: {
|
||
...metadata,
|
||
message_type: 'group_chat'
|
||
}
|
||
})
|
||
}
|
||
|
||
/*
|
||
7. 实时订阅 - 自动受RLS策略保护
|
||
*/
|
||
|
||
// 订阅用户消息更新
|
||
export function subscribeToUserMessages(userId: string, callback: (payload: any) => void) {
|
||
return supabase
|
||
.channel('user-messages')
|
||
.on(
|
||
'postgres_changes',
|
||
{
|
||
event: '*',
|
||
schema: 'public',
|
||
table: 'ak_message_recipients',
|
||
filter: `recipient_id=eq.${userId}`
|
||
},
|
||
callback
|
||
)
|
||
.subscribe()
|
||
}
|
||
|
||
// 订阅群组消息更新
|
||
export function subscribeToGroupMessages(groupId: string, callback: (payload: any) => void) {
|
||
return supabase
|
||
.channel(`group-${groupId}`)
|
||
.on(
|
||
'postgres_changes',
|
||
{
|
||
event: 'INSERT',
|
||
schema: 'public',
|
||
table: 'ak_messages',
|
||
filter: `receiver_id=eq.${groupId}`
|
||
},
|
||
callback
|
||
)
|
||
.subscribe()
|
||
}
|
||
|
||
// 订阅新消息通知
|
||
export function subscribeToNewMessages(userId: string, callback: (payload: any) => void) {
|
||
return supabase
|
||
.channel('new-messages')
|
||
.on(
|
||
'postgres_changes',
|
||
{
|
||
event: 'INSERT',
|
||
schema: 'public',
|
||
table: 'ak_message_recipients',
|
||
filter: `recipient_id=eq.${userId}`
|
||
},
|
||
async (payload) => {
|
||
// 获取完整消息信息
|
||
const { data: message } = await supabase
|
||
.from('ak_messages')
|
||
.select(`
|
||
*,
|
||
ak_message_types (name, icon, color)
|
||
`)
|
||
.eq('id', payload.new.message_id)
|
||
.single()
|
||
|
||
callback({ ...payload, message })
|
||
}
|
||
)
|
||
.subscribe()
|
||
}
|
||
|
||
/*
|
||
8. 权限检查辅助函数
|
||
*/
|
||
|
||
// 检查用户是否可以访问消息
|
||
export async function canAccessMessage(messageId: string, userId: string): Promise<boolean> {
|
||
const { data, error } = await supabase
|
||
.rpc('can_access_message', {
|
||
message_uuid: messageId,
|
||
user_uuid: userId
|
||
})
|
||
|
||
if (error) return false
|
||
return data || false
|
||
}
|
||
|
||
// 检查用户是否为群组成员
|
||
export async function isGroupMember(groupId: string, userId: string): Promise<boolean> {
|
||
const { data, error } = await supabase
|
||
.rpc('is_group_member', {
|
||
group_uuid: groupId,
|
||
user_uuid: userId
|
||
})
|
||
|
||
if (error) return false
|
||
return data || false
|
||
}
|
||
|
||
/*
|
||
9. 教师端特殊功能
|
||
*/
|
||
|
||
// 教师获取学生消息统计
|
||
export async function getStudentMessageStats(teacherId: string, studentId: string) {
|
||
// 首先验证教师权限（可以根据业务逻辑调整）
|
||
const { data: teacherUser } = await supabase.auth.getUser()
|
||
if (!teacherUser?.user || teacherUser.user.id !== teacherId) {
|
||
throw new Error('权限不足：只能查看自己负责的学生数据')
|
||
}
|
||
|
||
// 获取学生消息统计
|
||
const { data, error } = await supabase
|
||
.from('ak_message_recipients')
|
||
.select(`
|
||
status,
|
||
read_at,
|
||
created_at,
|
||
ak_messages (
|
||
title,
|
||
created_at,
|
||
ak_message_types (name)
|
||
)
|
||
`)
|
||
.eq('recipient_id', studentId)
|
||
.order('created_at', { ascending: false })
|
||
.limit(50)
|
||
|
||
if (error) throw error
|
||
return data
|
||
}
|
||
|
||
// 教师发送班级公告
|
||
export async function sendClassAnnouncement(
|
||
classId,
|
||
title,
|
||
content,
|
||
isUrgent = false
|
||
) {
|
||
return await sendMessage({
|
||
messageType: 'announcement',
|
||
receiverType: 'class',
|
||
receiverId: classId,
|
||
title: title,
|
||
content: content,
|
||
priority: isUrgent ? 95 : 80,
|
||
metadata: {
|
||
type: 'class_announcement',
|
||
is_urgent: isUrgent
|
||
}
|
||
})
|
||
}
|
||
|
||
/*
|
||
10. 数据类型定义（JSDoc 风格）
|
||
*/
|
||
|
||
/**
|
||
* @typedef {Object} MessageData
|
||
* @property {string} id
|
||
* @property {string} title
|
||
* @property {string} content
|
||
* @property {string} created_at
|
||
* @property {boolean} is_urgent
|
||
* @property {string} sender_name
|
||
* @property {Object} message_type
|
||
* @property {string} message_type.name
|
||
* @property {string} message_type.icon
|
||
* @property {string} message_type.color
|
||
* @property {Object} recipient_info
|
||
* @property {string} recipient_info.status
|
||
* @property {string|null} recipient_info.read_at
|
||
* @property {boolean} recipient_info.is_starred
|
||
* @property {boolean} recipient_info.is_archived
|
||
*/
|
||
|
||
/**
|
||
* @typedef {Object} GroupData
|
||
* @property {string} id
|
||
* @property {string} name
|
||
* @property {string} description
|
||
* @property {string} group_type
|
||
* @property {boolean} is_public
|
||
* @property {string} member_role
|
||
* @property {string} member_status
|
||
*/
|
||
|
||
// 错误处理包装器
|
||
export function handleSupabaseError(error) {
|
||
console.error('Supabase Error:', error)
|
||
|
||
if (error.code === 'PGRST116') {
|
||
throw new Error('权限不足：您无权执行此操作')
|
||
} else if (error.code === 'PGRST301') {
|
||
throw new Error('数据不存在或无权访问')
|
||
} else {
|
||
throw new Error(error.message || '操作失败')
|
||
}
|
||
}
|
||
|
||
/*
|
||
使用示例：
|
||
|
||
// 教师端登录
|
||
const teacherUser = await signInUser('teacher@example.com', 'password')
|
||
|
||
// 获取教师消息
|
||
const teacherMessages = await getUserMessages('7bf7378e-a027-473e-97ac-3460ed3f170a')
|
||
|
||
// 教师发送作业通知给学生
|
||
await sendAssignmentNotification(
|
||
'eed3824b-bba1-4309-8048-19d17367c084',
|
||
'运动数据分析报告',
|
||
'2024-01-20 23:59',
|
||
'请分析您近一周的运动数据并提交报告'
|
||
)
|
||
|
||
// 学生端登录
|
||
const studentUser = await signInUser('student@example.com', 'password')
|
||
|
||
// 获取学生消息
|
||
const studentMessages = await getUserMessages('eed3824b-bba1-4309-8048-19d17367c084')
|
||
|
||
// 标记消息为已读
|
||
await markMessageAsRead(messageId, 'eed3824b-bba1-4309-8048-19d17367c084')
|
||
|
||
// 订阅实时消息
|
||
const subscription = subscribeToUserMessages(
|
||
'eed3824b-bba1-4309-8048-19d17367c084',
|
||
(payload) => {
|
||
console.log('新消息:', payload)
|
||
// 更新UI显示新消息
|
||
}
|
||
)
|
||
*/
|
||
|
||
export default {
|
||
supabase,
|
||
signInUser,
|
||
getUserMessages,
|
||
getUnreadCount,
|
||
sendMessage,
|
||
markMessageAsRead,
|
||
toggleMessageStar,
|
||
subscribeToUserMessages,
|
||
subscribeToNewMessages,
|
||
sendAssignmentNotification,
|
||
sendClassAnnouncement,
|
||
handleSupabaseError
|
||
}
|