-- ===================================================================
-- 用户消息系统完整设计方案
-- 支持设备消息、用户间消息、系统消息的统一消息机制
-- 适用于运动训练监测与AI评估平台
-- ===================================================================

-- 启用必要的扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ===================================================================
-- 1. 消息类型和基础配置
-- ===================================================================

-- 消息类型定义表
CREATE TABLE public.ak_message_types (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(32) NOT NULL UNIQUE, -- 类型代码
    name VARCHAR(64) NOT NULL, -- 类型名称
    description TEXT, -- 描述
    icon VARCHAR(64), -- 图标名称
    color VARCHAR(16), -- 颜色代码
    priority INT DEFAULT 0, -- 优先级 (数字越大优先级越高)
    is_system BOOLEAN DEFAULT false, -- 是否系统类型
    is_active BOOLEAN DEFAULT true, -- 是否启用
    auto_read_timeout INT, -- 自动标记为已读的超时时间(秒)
    retention_days INT DEFAULT 30, -- 消息保留天数
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_message_types IS '消息类型定义表';

-- 插入基础消息类型
INSERT INTO public.ak_message_types (code, name, description, priority, is_system, auto_read_timeout, retention_days) VALUES
('system', '系统消息', '系统自动发送的重要通知', 100, true, 0, 90),
('device', '设备消息', '设备发送的状态和数据消息', 80, true, 300, 7),
('training', '训练提醒', '训练计划和完成情况提醒', 70, true, 0, 30),
('social', '社交消息', '用户间的社交互动消息', 60, false, 0, 30),
('assignment', '作业通知', '作业发布、截止、批改通知', 75, true, 0, 60),
('achievement', '成就通知', '积分、成就、奖励通知', 50, true, 3600, 90),
('chat', '即时消息', '用户间的实时聊天消息', 90, false, 0, 7),
('announcement', '公告通知', '系统公告和重要通知', 95, true, 0, 180),
('reminder', '提醒消息', '定时提醒和事件提醒', 65, true, 0, 14),
('alert', '警报消息', '紧急情况和异常状态警报', 99, true, 0, 30);

-- ===================================================================
-- 2. 核心消息表结构
-- ===================================================================

-- 消息主表
CREATE TABLE public.ak_messages (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    message_type_id uuid REFERENCES public.ak_message_types(id) NOT NULL,
    
    -- 发送方信息
    sender_type VARCHAR(32) NOT NULL, -- user, device, system
    sender_id uuid, -- 发送者ID (用户ID、设备ID等，系统消息为NULL)
    sender_name VARCHAR(128), -- 发送者显示名称
    
    -- 接收方信息
    receiver_type VARCHAR(32) NOT NULL DEFAULT 'user', -- user, group, broadcast, class
    receiver_id uuid, -- 接收者ID
    
    -- 消息内容
    title VARCHAR(255), -- 消息标题
    content TEXT, -- 消息内容
    content_type VARCHAR(32) DEFAULT 'text', -- text, html, markdown, json
    
    -- 附件和多媒体
    attachments JSONB, -- 附件信息数组
    media_urls JSONB, -- 媒体文件URL数组
    
    -- 元数据和扩展信息
    metadata JSONB, -- 扩展元数据
    device_data JSONB, -- 设备相关数据
    location_data JSONB, -- 位置信息
    
    -- 消息属性
    priority INT DEFAULT 0, -- 消息优先级
    expires_at TIMESTAMP WITH TIME ZONE, -- 过期时间
    is_broadcast BOOLEAN DEFAULT false, -- 是否广播消息
    is_urgent BOOLEAN DEFAULT false, -- 是否紧急消息
    
    -- 会话和回复
    conversation_id uuid, -- 会话ID (群组聊天或对话线程)
    parent_message_id uuid REFERENCES public.ak_messages(id), -- 父消息ID (回复)
    thread_count INT DEFAULT 0, -- 回复数量
    
    -- 状态和统计
    status VARCHAR(32) DEFAULT 'sent', -- draft, sent, delivered, failed
    total_recipients INT DEFAULT 0, -- 总接收者数量
    delivered_count INT DEFAULT 0, -- 已送达数量
    read_count INT DEFAULT 0, -- 已读数量
    reply_count INT DEFAULT 0, -- 回复数量
    
    -- 投递配置
    delivery_options JSONB, -- 投递选项配置
    push_notification BOOLEAN DEFAULT true, -- 是否推送通知
    email_notification BOOLEAN DEFAULT false, -- 是否邮件通知
    sms_notification BOOLEAN DEFAULT false, -- 是否短信通知
    
    -- 时间戳
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    scheduled_at TIMESTAMP WITH TIME ZONE, -- 定时发送时间
    delivered_at TIMESTAMP WITH TIME ZONE -- 投递完成时间
);
COMMENT ON TABLE public.ak_messages IS '消息主表';

-- 为消息表创建索引
CREATE INDEX IF NOT EXISTS idx_messages_type ON public.ak_messages(message_type_id);
CREATE INDEX IF NOT EXISTS idx_messages_sender ON public.ak_messages(sender_type, sender_id);
CREATE INDEX IF NOT EXISTS idx_messages_receiver ON public.ak_messages(receiver_type, receiver_id);
CREATE INDEX IF NOT EXISTS idx_messages_conversation ON public.ak_messages(conversation_id);
CREATE INDEX IF NOT EXISTS idx_messages_parent ON public.ak_messages(parent_message_id);
CREATE INDEX IF NOT EXISTS idx_messages_status ON public.ak_messages(status);
CREATE INDEX IF NOT EXISTS idx_messages_priority ON public.ak_messages(priority DESC);
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON public.ak_messages(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_messages_urgent ON public.ak_messages(is_urgent) WHERE is_urgent = true;
CREATE INDEX IF NOT EXISTS idx_messages_scheduled ON public.ak_messages(scheduled_at) WHERE scheduled_at IS NOT NULL;

-- ===================================================================
-- 3. 消息接收和状态管理
-- ===================================================================

-- 消息接收记录表
CREATE TABLE public.ak_message_recipients (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    message_id uuid REFERENCES public.ak_messages(id) ON DELETE CASCADE,
    
    -- 接收者信息
    recipient_type VARCHAR(32) NOT NULL, -- user, device
    recipient_id uuid NOT NULL,
    recipient_name VARCHAR(128), -- 接收者显示名称
    
    -- 投递状态
    status VARCHAR(32) DEFAULT 'pending', -- pending, sent, delivered, read, failed
    delivery_method VARCHAR(32), -- push, email, sms, websocket
    
    -- 时间戳
    sent_at TIMESTAMP WITH TIME ZONE,
    delivered_at TIMESTAMP WITH TIME ZONE,
    read_at TIMESTAMP WITH TIME ZONE,
    replied_at TIMESTAMP WITH TIME ZONE,
    
    -- 投递详情
    delivery_attempts INT DEFAULT 0,
    last_attempt_at TIMESTAMP WITH TIME ZONE,
    failure_reason TEXT,
    device_token VARCHAR(255), -- 推送设备令牌
    
    -- 用户操作
    is_starred BOOLEAN DEFAULT false, -- 是否标星
    is_archived BOOLEAN DEFAULT false, -- 是否归档
    is_deleted BOOLEAN DEFAULT false, -- 是否删除
    deleted_at TIMESTAMP WITH TIME ZONE,
    
    -- 扩展信息
    read_duration_sec INT, -- 阅读时长
    interaction_data JSONB, -- 交互数据
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_message_recipients IS '消息接收记录表';

-- 为接收记录表创建索引
CREATE INDEX IF NOT EXISTS idx_recipients_message ON public.ak_message_recipients(message_id);
CREATE INDEX IF NOT EXISTS idx_recipients_recipient ON public.ak_message_recipients(recipient_type, recipient_id);
CREATE INDEX IF NOT EXISTS idx_recipients_status ON public.ak_message_recipients(status);
CREATE INDEX IF NOT EXISTS idx_recipients_starred ON public.ak_message_recipients(recipient_id) WHERE is_starred = true;
CREATE INDEX IF NOT EXISTS idx_recipients_unread ON public.ak_message_recipients(recipient_id, status) WHERE status != 'read';
CREATE UNIQUE INDEX IF NOT EXISTS idx_recipients_unique ON public.ak_message_recipients(message_id, recipient_type, recipient_id);

-- ===================================================================
-- 4. 群组和会话管理
-- ===================================================================

-- 消息群组表
CREATE TABLE public.ak_message_groups (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(128) NOT NULL,
    description TEXT,
    group_type VARCHAR(32) DEFAULT 'normal', -- normal, class, team, system, temporary
    
    -- 群组管理
    owner_id uuid REFERENCES public.ak_users(id),
    avatar_url TEXT,
    is_active BOOLEAN DEFAULT true,
    is_public BOOLEAN DEFAULT false, -- 是否公开群组
    
    -- 群组限制
    member_limit INT DEFAULT 500,
    message_limit_per_day INT DEFAULT 1000,
    file_size_limit_mb INT DEFAULT 100,
    
    -- 群组设置
    settings JSONB DEFAULT '{}', -- 群组设置
    permissions JSONB DEFAULT '{}', -- 权限设置
    
    -- 自动管理
    auto_archive_days INT DEFAULT 30, -- 自动归档天数
    auto_delete_days INT DEFAULT 90, -- 自动删除天数
    
    -- 关联信息
    related_class_id uuid REFERENCES public.ak_classes(id), -- 关联班级
    related_school_id uuid REFERENCES public.ak_schools(id), -- 关联学校
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_message_groups IS '消息群组表';

CREATE INDEX IF NOT EXISTS idx_groups_owner ON public.ak_message_groups(owner_id);
CREATE INDEX IF NOT EXISTS idx_groups_type ON public.ak_message_groups(group_type);
CREATE INDEX IF NOT EXISTS idx_groups_class ON public.ak_message_groups(related_class_id);
CREATE INDEX IF NOT EXISTS idx_groups_active ON public.ak_message_groups(is_active) WHERE is_active = true;

-- 群组成员表
CREATE TABLE public.ak_message_group_members (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id uuid REFERENCES public.ak_message_groups(id) ON DELETE CASCADE,
    user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
    
    -- 成员角色和权限
    role VARCHAR(32) DEFAULT 'member', -- owner, admin, moderator, member
    permissions JSONB DEFAULT '{}', -- 特殊权限
    
    -- 成员状态
    status VARCHAR(32) DEFAULT 'active', -- active, muted, banned, left
    nickname VARCHAR(64), -- 群内昵称
    
    -- 禁言管理
    is_muted BOOLEAN DEFAULT false,
    muted_until TIMESTAMP WITH TIME ZONE,
    muted_by uuid REFERENCES public.ak_users(id),
    mute_reason TEXT,
    
    -- 消息管理
    last_read_message_id uuid,
    last_read_at TIMESTAMP WITH TIME ZONE,
    unread_count INT DEFAULT 0,
    
    -- 通知设置
    notification_enabled BOOLEAN DEFAULT true,
    mention_only BOOLEAN DEFAULT false, -- 仅@时通知
    
    -- 成员统计
    message_count INT DEFAULT 0, -- 发送消息数
    join_count INT DEFAULT 1, -- 加入次数
    
    joined_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    left_at 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_message_group_members IS '群组成员表';

CREATE INDEX IF NOT EXISTS idx_group_members_group ON public.ak_message_group_members(group_id);
CREATE INDEX IF NOT EXISTS idx_group_members_user ON public.ak_message_group_members(user_id);
CREATE INDEX IF NOT EXISTS idx_group_members_active ON public.ak_message_group_members(group_id, status) WHERE status = 'active';
CREATE UNIQUE INDEX IF NOT EXISTS idx_group_members_unique ON public.ak_message_group_members(group_id, user_id);

-- ===================================================================
-- 5. 消息模板和自动化
-- ===================================================================

-- 消息模板表
CREATE TABLE public.ak_message_templates (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(64) NOT NULL UNIQUE,
    name VARCHAR(128) NOT NULL,
    description TEXT,
    
    -- 模板分类
    category VARCHAR(32) DEFAULT 'general', -- general, device, training, system
    message_type_id uuid REFERENCES public.ak_message_types(id),
    
    -- 模板内容
    title_template TEXT, -- 标题模板
    content_template TEXT NOT NULL, -- 内容模板
    
    -- 模板变量
    variables JSONB DEFAULT '{}', -- 变量定义
    required_variables TEXT[], -- 必需变量列表
    
    -- 模板设置
    is_active BOOLEAN DEFAULT true,
    is_system BOOLEAN DEFAULT false,
    language VARCHAR(10) DEFAULT 'zh-CN',
    
    -- 默认配置
    default_priority INT DEFAULT 0,
    default_push BOOLEAN DEFAULT true,
    default_email BOOLEAN DEFAULT false,
    
    -- 使用统计
    usage_count INT DEFAULT 0,
    last_used_at TIMESTAMP WITH TIME ZONE,
    
    created_by uuid REFERENCES public.ak_users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_message_templates IS '消息模板表';

CREATE INDEX IF NOT EXISTS idx_templates_category ON public.ak_message_templates(category);
CREATE INDEX IF NOT EXISTS idx_templates_type ON public.ak_message_templates(message_type_id);
CREATE INDEX IF NOT EXISTS idx_templates_active ON public.ak_message_templates(is_active) WHERE is_active = true;

-- 插入基础消息模板
INSERT INTO public.ak_message_templates (code, name, category, title_template, content_template, variables, required_variables) VALUES
-- 设备消息模板
('device_battery_low', '设备低电量提醒', 'device', 
 '设备电量不足', 
 '您的{{device_name}}电量仅剩{{battery_level}}%，请及时充电以确保正常使用。', 
 '{"device_name":"设备名称","battery_level":"电量百分比"}'::jsonb,
 ARRAY['device_name', 'battery_level']),

('device_offline', '设备离线通知', 'device',
 '设备连接异常',
 '您的{{device_name}}已离线超过{{offline_duration}}分钟，请检查设备状态和网络连接。',
 '{"device_name":"设备名称","offline_duration":"离线时长"}'::jsonb,
 ARRAY['device_name', 'offline_duration']),

-- 训练消息模板
('training_completed', '训练完成通知', 'training',
 '训练完成！',
 '恭喜完成{{activity_type}}训练！用时{{duration}}分钟，消耗{{calories}}卡路里，步数{{steps}}步。继续保持！',
 '{"activity_type":"运动类型","duration":"时长","calories":"卡路里","steps":"步数"}'::jsonb,
 ARRAY['activity_type', 'duration']),

('training_reminder', '训练提醒', 'training',
 '该运动了！',
 '根据您的训练计划，现在是{{activity_type}}时间。预计用时{{estimated_duration}}分钟，加油！',
 '{"activity_type":"运动类型","estimated_duration":"预计时长"}'::jsonb,
 ARRAY['activity_type']),

-- 作业消息模板
('assignment_published', '作业发布通知', 'assignment',
 '新作业：{{assignment_title}}',
 '老师发布了新作业"{{assignment_title}}"，截止时间：{{due_date}}。请及时完成。',
 '{"assignment_title":"作业标题","due_date":"截止时间","teacher_name":"老师姓名"}'::jsonb,
 ARRAY['assignment_title', 'due_date']),

('assignment_due_reminder', '作业截止提醒', 'assignment',
 '作业即将截止',
 '作业"{{assignment_title}}"将于{{hours_left}}小时后截止，请抓紧时间完成。',
 '{"assignment_title":"作业标题","hours_left":"剩余小时"}'::jsonb,
 ARRAY['assignment_title', 'hours_left']),

-- 系统消息模板
('system_maintenance', '系统维护通知', 'system',
 '系统维护通知',
 '系统将于{{maintenance_time}}进行维护，预计耗时{{duration}}小时。维护期间部分功能可能无法使用，请提前安排。',
 '{"maintenance_time":"维护时间","duration":"维护时长"}'::jsonb,
 ARRAY['maintenance_time', 'duration']),

('achievement_unlocked', '成就解锁', 'achievement',
 '🎉 恭喜解锁新成就！',
 '恭喜您解锁了"{{achievement_name}}"成就！获得{{points}}积分奖励。{{description}}',
 '{"achievement_name":"成就名称","points":"积分奖励","description":"成就描述"}'::jsonb,
 ARRAY['achievement_name', 'points']);

-- ===================================================================
-- 6. 用户消息偏好设置
-- ===================================================================

-- 用户消息偏好设置表
CREATE TABLE public.ak_user_message_preferences (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE UNIQUE,
    
    -- 全局设置
    global_enabled BOOLEAN DEFAULT true,
    do_not_disturb BOOLEAN DEFAULT false,
    quiet_hours_start TIME DEFAULT '22:00:00',
    quiet_hours_end TIME DEFAULT '07:00:00',
    timezone VARCHAR(50) DEFAULT 'Asia/Shanghai',
    
    -- 通知方式偏好
    push_enabled BOOLEAN DEFAULT true,
    email_enabled BOOLEAN DEFAULT false,
    sms_enabled BOOLEAN DEFAULT false,
    
    -- 消息类型偏好 (JSON格式存储各类型的设置)
    type_preferences JSONB DEFAULT '{}',
    
    -- 群组消息设置
    group_message_preview BOOLEAN DEFAULT true,
    mention_keywords TEXT[], -- 关键词提醒
    
    -- 消息过滤
    spam_filter_enabled BOOLEAN DEFAULT true,
    block_unknown_senders BOOLEAN DEFAULT false,
    
    -- 自动操作
    auto_mark_read_delay INT DEFAULT 0, -- 自动标记已读延迟(秒)
    auto_archive_days INT DEFAULT 30, -- 自动归档天数
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_user_message_preferences IS '用户消息偏好设置表';

CREATE INDEX IF NOT EXISTS idx_user_preferences_user ON public.ak_user_message_preferences(user_id);

-- 为每个用户创建默认消息偏好设置的函数
CREATE OR REPLACE FUNCTION public.create_default_message_preferences()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.ak_user_message_preferences (user_id, type_preferences)
    VALUES (NEW.id, '{
        "system": {"enabled": true, "push": true, "email": false},
        "device": {"enabled": true, "push": true, "email": false},
        "training": {"enabled": true, "push": true, "email": false},
        "social": {"enabled": true, "push": true, "email": false},
        "assignment": {"enabled": true, "push": true, "email": true},
        "achievement": {"enabled": true, "push": true, "email": false},
        "chat": {"enabled": true, "push": true, "email": false},
        "announcement": {"enabled": true, "push": true, "email": true},
        "reminder": {"enabled": true, "push": true, "email": false},
        "alert": {"enabled": true, "push": true, "email": true}
    }'::jsonb);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器，为新用户自动创建消息偏好设置
CREATE TRIGGER tr_create_user_message_preferences
    AFTER INSERT ON public.ak_users
    FOR EACH ROW
    EXECUTE FUNCTION public.create_default_message_preferences();

-- ===================================================================
-- 7. 消息统计和分析
-- ===================================================================

-- 消息统计表
CREATE TABLE public.ak_message_stats (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- 统计维度
    date_bucket DATE NOT NULL,
    hour_bucket INT, -- 0-23小时
    message_type_id uuid REFERENCES public.ak_message_types(id),
    sender_type VARCHAR(32),
    
    -- 消息统计
    total_sent INT DEFAULT 0,
    total_delivered INT DEFAULT 0,
    total_read INT DEFAULT 0,
    total_replied INT DEFAULT 0,
    total_failed INT DEFAULT 0,
    
    -- 性能统计
    avg_delivery_time_ms INT, -- 平均投递时间
    avg_read_time_sec INT, -- 平均阅读时间
    avg_response_time_sec INT, -- 平均响应时间
    
    -- 设备统计 (仅设备消息)
    device_online_count INT DEFAULT 0,
    device_offline_count INT DEFAULT 0,
    device_error_count INT DEFAULT 0,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_message_stats IS '消息统计表';

CREATE INDEX IF NOT EXISTS idx_message_stats_date ON public.ak_message_stats(date_bucket);
CREATE INDEX IF NOT EXISTS idx_message_stats_hour ON public.ak_message_stats(date_bucket, hour_bucket);
CREATE INDEX IF NOT EXISTS idx_message_stats_type ON public.ak_message_stats(message_type_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_message_stats_unique ON public.ak_message_stats(date_bucket, COALESCE(hour_bucket, -1), COALESCE(message_type_id::text, ''), COALESCE(sender_type, ''));

-- ===================================================================
-- 8. 消息处理函数和触发器
-- ===================================================================

-- 发送消息的核心函数
CREATE OR REPLACE FUNCTION public.send_message(
    p_message_type_code VARCHAR(32),
    p_sender_type VARCHAR(32),
    p_sender_id UUID,
    p_receiver_type VARCHAR(32),
    p_receiver_id UUID,
    p_title TEXT,
    p_content TEXT,
    p_template_code VARCHAR(64) DEFAULT NULL,
    p_template_variables JSONB DEFAULT NULL,
    p_priority INT DEFAULT 0,
    p_metadata JSONB DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
    v_message_id UUID;
    v_message_type_id UUID;
    v_template_record RECORD;
    v_final_title TEXT;
    v_final_content TEXT;
BEGIN
    -- 获取消息类型ID
    SELECT id INTO v_message_type_id 
    FROM public.ak_message_types 
    WHERE code = p_message_type_code AND is_active = true;
    
    IF v_message_type_id IS NULL THEN
        RAISE EXCEPTION '消息类型不存在或未启用: %', p_message_type_code;
    END IF;
    
    -- 如果使用模板，处理模板内容
    IF p_template_code IS NOT NULL THEN
        SELECT * INTO v_template_record 
        FROM public.ak_message_templates 
        WHERE code = p_template_code AND is_active = true;
        
        IF v_template_record.id IS NOT NULL THEN
            -- 使用模板标题和内容
            v_final_title := COALESCE(p_title, v_template_record.title_template);
            v_final_content := COALESCE(p_content, v_template_record.content_template);
            
            -- 处理模板变量替换
            IF p_template_variables IS NOT NULL THEN
                -- 这里可以实现模板变量替换逻辑
                -- 简化版本：直接使用传入的内容
                v_final_title := COALESCE(p_title, v_template_record.title_template);
                v_final_content := COALESCE(p_content, v_template_record.content_template);
            END IF;
            
            -- 更新模板使用统计
            UPDATE public.ak_message_templates 
            SET usage_count = usage_count + 1, last_used_at = now()
            WHERE id = v_template_record.id;
        END IF;
    ELSE
        v_final_title := p_title;
        v_final_content := p_content;
    END IF;
    
    -- 创建消息记录
    INSERT INTO public.ak_messages (
        message_type_id, sender_type, sender_id, receiver_type, receiver_id,
        title, content, priority, metadata, status
    ) VALUES (
        v_message_type_id, p_sender_type, p_sender_id, p_receiver_type, p_receiver_id,
        v_final_title, v_final_content, p_priority, p_metadata, 'sent'
    ) RETURNING id INTO v_message_id;
    
    -- 创建接收记录
    PERFORM public.create_message_recipients(v_message_id, p_receiver_type, p_receiver_id);
    
    RETURN v_message_id;
END;
$$ LANGUAGE plpgsql;

-- 创建消息接收记录的函数
CREATE OR REPLACE FUNCTION public.create_message_recipients(
    p_message_id UUID,
    p_receiver_type VARCHAR(32),
    p_receiver_id UUID
)
RETURNS VOID AS $$
DECLARE
    v_recipient_record RECORD;
    v_recipient_count INT := 0;
BEGIN
    -- 根据接收者类型创建接收记录
    CASE p_receiver_type
        WHEN 'user' THEN
            -- 单个用户
            INSERT INTO public.ak_message_recipients (message_id, recipient_type, recipient_id, status)
            VALUES (p_message_id, 'user', p_receiver_id, 'sent');
            v_recipient_count := 1;
            
        WHEN 'group' THEN
            -- 群组消息：为所有活跃成员创建接收记录
            INSERT INTO public.ak_message_recipients (message_id, recipient_type, recipient_id, status)
            SELECT p_message_id, 'user', gm.user_id, 'sent'
            FROM public.ak_message_group_members gm
            WHERE gm.group_id = p_receiver_id AND gm.status = 'active';
            
            GET DIAGNOSTICS v_recipient_count = ROW_COUNT;
            
        WHEN 'class' THEN
            -- 班级消息：为班级所有学生创建接收记录
            INSERT INTO public.ak_message_recipients (message_id, recipient_type, recipient_id, status)
            SELECT p_message_id, 'user', u.id, 'sent'
            FROM public.ak_users u
            WHERE u.class_id = p_receiver_id;
            
            GET DIAGNOSTICS v_recipient_count = ROW_COUNT;
            
        WHEN 'broadcast' THEN
            -- 广播消息：为所有用户创建接收记录
            INSERT INTO public.ak_message_recipients (message_id, recipient_type, recipient_id, status)
            SELECT p_message_id, 'user', u.id, 'sent'
            FROM public.ak_users u
            WHERE u.id IS NOT NULL;
            
            GET DIAGNOSTICS v_recipient_count = ROW_COUNT;
    END CASE;
    
    -- 更新消息的接收者统计
    UPDATE public.ak_messages 
    SET total_recipients = v_recipient_count
    WHERE id = p_message_id;
END;
$$ LANGUAGE plpgsql;

-- 标记消息为已读的函数
CREATE OR REPLACE FUNCTION public.mark_message_read(
    p_message_id UUID,
    p_user_id UUID
)
RETURNS BOOLEAN AS $$
DECLARE
    v_row_count INTEGER;
BEGIN
    UPDATE public.ak_message_recipients 
    SET 
        status = 'read',
        read_at = now(),
        updated_at = now()
    WHERE message_id = p_message_id 
      AND recipient_type = 'user' 
      AND recipient_id = p_user_id 
      AND status != 'read';
    
    GET DIAGNOSTICS v_row_count = ROW_COUNT;
    
    IF v_row_count > 0 THEN
        -- 更新消息的已读统计
        UPDATE public.ak_messages 
        SET read_count = read_count + 1
        WHERE id = p_message_id;
        
        RETURN true;
    END IF;
    
    RETURN false;
END;
$$ LANGUAGE plpgsql;

-- ===================================================================
-- 9. 设备消息专用功能
-- ===================================================================

-- 设备消息发送函数
CREATE OR REPLACE FUNCTION public.send_device_message(
    p_device_id UUID,
    p_user_id UUID,
    p_message_type VARCHAR(32),
    p_title TEXT,
    p_content TEXT,
    p_device_data JSONB DEFAULT NULL,
    p_urgent BOOLEAN DEFAULT false
)
RETURNS UUID AS $$
DECLARE
    v_message_id UUID;
    v_priority INT := 0;
BEGIN
    -- 设置优先级
    IF p_urgent THEN
        v_priority := 90;
    ELSE
        v_priority := 50;
    END IF;
    
    -- 发送消息
    SELECT public.send_message(
        'device',
        'device', 
        p_device_id,
        'user',
        p_user_id,
        p_title,
        p_content,
        NULL,
        NULL,
        v_priority,
        jsonb_build_object('device_data', p_device_data, 'message_type', p_message_type)
    ) INTO v_message_id;
    
    -- 更新消息的设备相关字段
    UPDATE public.ak_messages 
    SET 
        device_data = p_device_data,
        is_urgent = p_urgent
    WHERE id = v_message_id;
    
    RETURN v_message_id;
END;
$$ LANGUAGE plpgsql;

-- ===================================================================
-- 10. 视图和查询函数
-- ===================================================================

-- 用户消息列表视图
CREATE OR REPLACE VIEW public.vw_user_messages AS
SELECT 
    m.id,
    m.title,
    m.content,
    m.created_at,
    m.is_urgent,
    m.priority,
    
    -- 消息类型信息
    mt.name as message_type_name,
    mt.icon as message_type_icon,
    mt.color as message_type_color,
    
    -- 发送者信息
    m.sender_type,
    m.sender_name,
    CASE 
        WHEN m.sender_type = 'user' THEN u.username
        WHEN m.sender_type = 'device' THEN d.device_name
        ELSE '系统'
    END as sender_display_name,
    
    -- 接收状态
    mr.recipient_id as user_id,
    mr.status as read_status,
    mr.read_at,
    mr.is_starred,
    mr.is_archived,
    
    -- 会话信息
    m.conversation_id,
    m.parent_message_id,
    m.reply_count

FROM public.ak_messages m
JOIN public.ak_message_types mt ON m.message_type_id = mt.id
JOIN public.ak_message_recipients mr ON m.id = mr.message_id
LEFT JOIN public.ak_users u ON m.sender_type = 'user' AND m.sender_id = u.id
LEFT JOIN public.ak_devices d ON m.sender_type = 'device' AND m.sender_id = d.id
WHERE mr.recipient_type = 'user' AND mr.is_deleted = false;

COMMENT ON VIEW public.vw_user_messages IS '用户消息列表视图';

-- 获取用户未读消息数量的函数
CREATE OR REPLACE FUNCTION public.get_unread_message_count(p_user_id UUID)
RETURNS TABLE(
    message_type_code VARCHAR(32),
    message_type_name VARCHAR(64),
    unread_count BIGINT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        mt.code,
        mt.name,
        COUNT(*) as unread_count
    FROM public.ak_message_recipients mr
    JOIN public.ak_messages m ON mr.message_id = m.id
    JOIN public.ak_message_types mt ON m.message_type_id = mt.id
    WHERE mr.recipient_id = p_user_id 
      AND mr.recipient_type = 'user'
      AND mr.status != 'read'
      AND mr.is_deleted = false
    GROUP BY mt.code, mt.name
    ORDER BY unread_count DESC;
END;
$$ LANGUAGE plpgsql;

-- ===================================================================
-- 11. 示例数据和测试
-- ===================================================================

-- 插入示例消息 (仅在开发环境使用)
DO $$
DECLARE
    v_user_id UUID;
    v_device_id UUID;
    v_message_id UUID;
BEGIN
    -- 获取第一个用户和设备ID作为示例
    SELECT id INTO v_user_id FROM public.ak_users LIMIT 1;
    SELECT id INTO v_device_id FROM public.ak_devices LIMIT 1;
    
    IF v_user_id IS NOT NULL THEN
        -- 发送系统欢迎消息
        SELECT public.send_message(
            'system',
            'system',
            NULL,
            'user',
            v_user_id,
            '欢迎使用运动训练平台！',
            '欢迎来到我们的运动训练监测平台！在这里您可以记录训练数据、查看AI分析报告、与同学交流互动。祝您运动愉快！',
            NULL,
            NULL,
            100,
            '{"welcome_message": true}'::jsonb
        ) INTO v_message_id;
        
        -- 如果有设备，发送设备消息示例
        IF v_device_id IS NOT NULL THEN
            SELECT public.send_device_message(
                v_device_id,
                v_user_id,
                'status_update',
                '设备连接成功',
                '您的运动手环已成功连接，可以开始记录运动数据了。',
                '{"battery_level": 85, "firmware_version": "1.2.3"}'::jsonb,
                false
            ) INTO v_message_id;
        END IF;
    END IF;
END $$;

-- ===================================================================
-- 结束
-- ===================================================================

COMMENT ON SCHEMA public IS '运动训练监测平台 - 完整消息系统';

-- 输出创建完成信息
DO $$
BEGIN
    RAISE NOTICE '===================================================================';
    RAISE NOTICE '用户消息系统创建完成！';
    RAISE NOTICE '主要功能：';
    RAISE NOTICE '1. 设备向用户发送消息 (电量、状态、数据等)';
    RAISE NOTICE '2. 用户间消息 (聊天、群组、社交互动)';
    RAISE NOTICE '3. 系统消息 (通知、公告、提醒等)';
    RAISE NOTICE '4. 消息模板和自动化';
    RAISE NOTICE '5. 用户偏好设置和通知管理';
    RAISE NOTICE '6. 消息统计和分析';
    RAISE NOTICE '===================================================================';
    RAISE NOTICE '核心表格：';
    RAISE NOTICE '- ak_messages: 消息主表';
    RAISE NOTICE '- ak_message_recipients: 消息接收记录';
    RAISE NOTICE '- ak_message_groups: 消息群组';
    RAISE NOTICE '- ak_message_templates: 消息模板';
    RAISE NOTICE '- ak_user_message_preferences: 用户偏好设置';
    RAISE NOTICE '===================================================================';
    RAISE NOTICE '核心函数：';
    RAISE NOTICE '- send_message(): 发送通用消息';
    RAISE NOTICE '- send_device_message(): 发送设备消息';
    RAISE NOTICE '- mark_message_read(): 标记消息已读';
    RAISE NOTICE '- get_unread_message_count(): 获取未读消息统计';
    RAISE NOTICE '===================================================================';
END $$;