-- =============================================================================
-- 简化版 Supabase 消息系统权限策略
-- 不依赖 user_type 或 role 列，仅使用JWT中的信息
-- =============================================================================

-- 清理所有现有策略
DO $$
DECLARE
    r RECORD;
BEGIN
    -- 删除所有消息相关表的策略
    FOR r IN 
        SELECT schemaname, tablename, policyname 
        FROM pg_policies 
        WHERE schemaname = 'public' 
        AND tablename IN ('ak_messages', 'ak_message_recipients', 'ak_message_groups', 
                         'ak_message_group_members', 'ak_message_templates', 
                         'ak_user_message_preferences', 'ak_message_stats', 'ak_message_types')
    LOOP
        EXECUTE format('DROP POLICY IF EXISTS %I ON %I.%I', 
                      r.policyname, r.schemaname, r.tablename);
    END LOOP;
    
    RAISE NOTICE '🧹 已清理所有现有权限策略';
END $$;

-- 重新启用行级安全策略
ALTER TABLE public.ak_message_types ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_message_recipients ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_message_groups ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_message_group_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_message_templates ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_user_message_preferences ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_message_stats ENABLE ROW LEVEL SECURITY;

-- =============================================================================
-- 1. 消息类型权限策略 (ak_message_types)
-- =============================================================================

-- 所有认证用户可以查看消息类型
CREATE POLICY "authenticated_can_view_message_types" ON public.ak_message_types
    FOR SELECT USING (auth.role() = 'authenticated');

-- 管理员可以修改消息类型
CREATE POLICY "admins_can_modify_message_types" ON public.ak_message_types
    FOR ALL USING (
        auth.jwt() ->> 'user_role' = 'admin'
    );

-- =============================================================================
-- 2. 消息主表权限策略 (ak_messages)
-- =============================================================================

-- 用户可以查看自己相关的消息
CREATE POLICY "users_can_view_own_messages" ON public.ak_messages
    FOR SELECT USING (
        -- 用户是发送者
        (sender_type = 'user' AND sender_id = auth.uid()) 
        OR
        -- 用户是接收者（单用户消息）
        (receiver_type = 'user' AND receiver_id = auth.uid())
        OR
        -- 群组消息（检查是否为群组成员）
        (receiver_type = 'group' AND receiver_id IN (
            SELECT group_id FROM public.ak_message_group_members 
            WHERE user_id = auth.uid() AND status = 'active'
        ))
        OR
        -- 广播消息所有人可见
        (receiver_type = 'broadcast')
    );

-- 用户可以发送消息
CREATE POLICY "users_can_send_messages" ON public.ak_messages
    FOR INSERT WITH CHECK (
        -- 普通用户只能以自己的身份发送
        (sender_type = 'user' AND sender_id = auth.uid())
        OR
        -- 管理员可以发送系统消息
        (sender_type = 'system' AND auth.jwt() ->> 'user_role' = 'admin')
    );

-- 用户可以更新自己发送的消息
CREATE POLICY "users_can_update_own_messages" ON public.ak_messages
    FOR UPDATE USING (
        sender_type = 'user' AND sender_id = auth.uid()
    );

-- 用户可以删除自己发送的消息
CREATE POLICY "users_can_delete_own_messages" ON public.ak_messages
    FOR DELETE USING (
        sender_type = 'user' AND sender_id = auth.uid()
    );

-- 教师额外权限：可以查看更多消息（基于JWT角色）
CREATE POLICY "teachers_can_view_more_messages" ON public.ak_messages
    FOR SELECT USING (
        auth.jwt() ->> 'user_role' = 'teacher'
    );

-- =============================================================================
-- 3. 消息接收记录权限策略 (ak_message_recipients)
-- =============================================================================

-- 用户只能查看自己的接收记录
CREATE POLICY "users_can_view_own_recipients" ON public.ak_message_recipients
    FOR SELECT USING (
        recipient_type = 'user' AND recipient_id = auth.uid()
    );

-- 系统可以创建接收记录
CREATE POLICY "system_can_create_recipients" ON public.ak_message_recipients
    FOR INSERT WITH CHECK (
        -- 消息发送者可以创建接收记录
        message_id IN (
            SELECT id FROM public.ak_messages 
            WHERE sender_id = auth.uid() OR sender_type = 'system'
        )
        OR
        -- 管理员可以创建任何接收记录
        auth.jwt() ->> 'user_role' = 'admin'
    );

-- 用户可以更新自己的接收记录
CREATE POLICY "users_can_update_own_recipients" ON public.ak_message_recipients
    FOR UPDATE USING (
        recipient_type = 'user' AND recipient_id = auth.uid()
    );

-- 教师可以查看更多接收记录（用于教学管理）
CREATE POLICY "teachers_can_view_student_recipients" ON public.ak_message_recipients
    FOR SELECT USING (
        auth.jwt() ->> 'user_role' = 'teacher'
    );

-- =============================================================================
-- 4. 消息群组权限策略 (ak_message_groups)
-- =============================================================================

-- 用户可以查看自己参与的群组
CREATE POLICY "users_can_view_own_groups" ON public.ak_message_groups
    FOR SELECT USING (
        -- 群组所有者
        owner_id = auth.uid()
        OR
        -- 群组成员
        id IN (
            SELECT group_id FROM public.ak_message_group_members 
            WHERE user_id = auth.uid() AND status = 'active'
        )
        OR
        -- 公开群组
        is_public = true
    );

-- 认证用户可以创建群组
CREATE POLICY "authenticated_can_create_groups" ON public.ak_message_groups
    FOR INSERT WITH CHECK (
        owner_id = auth.uid()
    );

-- 群组所有者可以更新群组
CREATE POLICY "owners_can_update_groups" ON public.ak_message_groups
    FOR UPDATE USING (
        owner_id = auth.uid()
    );

-- 群组所有者可以删除群组
CREATE POLICY "owners_can_delete_groups" ON public.ak_message_groups
    FOR DELETE USING (
        owner_id = auth.uid()
    );

-- =============================================================================
-- 5. 群组成员权限策略 (ak_message_group_members)
-- =============================================================================

-- 群组成员可以查看同组成员
CREATE POLICY "members_can_view_group_members" ON public.ak_message_group_members
    FOR SELECT USING (
        -- 查看自己的成员记录
        user_id = auth.uid()
        OR
        -- 查看同组其他成员
        group_id IN (
            SELECT group_id FROM public.ak_message_group_members 
            WHERE user_id = auth.uid() AND status = 'active'
        )
    );

-- 群组所有者可以管理成员
CREATE POLICY "owners_can_manage_members" ON public.ak_message_group_members
    FOR ALL USING (
        group_id IN (
            SELECT id FROM public.ak_message_groups 
            WHERE owner_id = auth.uid()
        )
        OR
        -- 用户可以管理自己的成员记录
        user_id = auth.uid()
    );

-- =============================================================================
-- 6. 消息模板权限策略 (ak_message_templates)
-- =============================================================================

-- 用户可以查看活跃的模板
CREATE POLICY "users_can_view_active_templates" ON public.ak_message_templates
    FOR SELECT USING (
        is_active = true AND (
            -- 公共模板
            is_system = true
            OR
            -- 自己创建的模板
            created_by = auth.uid()
            OR
            -- 管理员可以查看所有
            auth.jwt() ->> 'user_role' = 'admin'
        )
    );

-- 认证用户可以创建模板
CREATE POLICY "authenticated_can_create_templates" ON public.ak_message_templates
    FOR INSERT WITH CHECK (
        created_by = auth.uid()
    );

-- 用户可以更新自己的模板
CREATE POLICY "users_can_update_own_templates" ON public.ak_message_templates
    FOR UPDATE USING (
        created_by = auth.uid() OR auth.jwt() ->> 'user_role' = 'admin'
    );

-- =============================================================================
-- 7. 用户偏好设置权限策略 (ak_user_message_preferences)
-- =============================================================================

-- 用户只能管理自己的偏好设置
CREATE POLICY "users_can_manage_own_preferences" ON public.ak_user_message_preferences
    FOR ALL USING (
        user_id = auth.uid()
    );

-- =============================================================================
-- 8. 消息统计权限策略 (ak_message_stats)
-- =============================================================================

-- 管理员和教师可以查看统计数据
CREATE POLICY "authorized_can_view_stats" ON public.ak_message_stats
    FOR SELECT USING (
        auth.jwt() ->> 'user_role' IN ('admin', 'teacher')
    );

-- 只有管理员可以修改统计数据
CREATE POLICY "admins_can_modify_stats" ON public.ak_message_stats
    FOR ALL USING (
        auth.jwt() ->> 'user_role' = 'admin'
    );

-- =============================================================================
-- 9. 安全辅助函数
-- =============================================================================

-- 检查用户是否为群组成员
CREATE OR REPLACE FUNCTION public.is_group_member_simple(group_uuid UUID, user_uuid UUID)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM public.ak_message_group_members 
        WHERE group_id = group_uuid 
        AND user_id = user_uuid 
        AND status = 'active'
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 检查用户是否可以访问消息
CREATE OR REPLACE FUNCTION public.can_access_message_simple(message_uuid UUID, user_uuid UUID)
RETURNS BOOLEAN AS $$
DECLARE
    msg_record RECORD;
BEGIN
    SELECT sender_type, sender_id, receiver_type, receiver_id 
    INTO msg_record
    FROM public.ak_messages 
    WHERE id = message_uuid;
    
    IF NOT FOUND THEN
        RETURN FALSE;
    END IF;
    
    -- 检查访问权限
    RETURN (
        -- 发送者
        (msg_record.sender_type = 'user' AND msg_record.sender_id = user_uuid)
        OR
        -- 接收者
        (msg_record.receiver_type = 'user' AND msg_record.receiver_id = user_uuid)
        OR
        -- 群组成员
        (msg_record.receiver_type = 'group' AND public.is_group_member_simple(msg_record.receiver_id, user_uuid))
        OR
        -- 广播消息
        (msg_record.receiver_type = 'broadcast')
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- =============================================================================
-- 10. 测试权限策略
-- =============================================================================

-- 创建测试函数
CREATE OR REPLACE FUNCTION public.test_permissions_simple()
RETURNS TABLE(
    test_name TEXT,
    result TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        'Policy Count Check'::TEXT,
        CASE 
            WHEN (SELECT COUNT(*) FROM pg_policies WHERE schemaname = 'public') > 0 
            THEN '✅ 权限策略已创建'
            ELSE '❌ 没有找到权限策略'
        END::TEXT;
        
    RETURN QUERY
    SELECT 
        'RLS Status Check'::TEXT,
        CASE 
            WHEN (SELECT COUNT(*) FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'ak_%' AND rowsecurity = true) > 0
            THEN '✅ RLS已启用'
            ELSE '❌ RLS未启用'
        END::TEXT;
END;
$$ LANGUAGE plpgsql;

-- 运行测试
SELECT * FROM public.test_permissions_simple();

-- 输出完成信息
DO $$
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '=============================================================';
    RAISE NOTICE '✅ 简化版权限策略创建完成！';
    RAISE NOTICE '=============================================================';
    RAISE NOTICE '🔧 特点：';
    RAISE NOTICE '   • 不依赖任何特定的用户表列';
    RAISE NOTICE '   • 仅使用JWT中的用户角色信息';
    RAISE NOTICE '   • 支持基本的消息权限控制';
    RAISE NOTICE '   • 教师和管理员有额外权限';
    RAISE NOTICE '=============================================================';
    RAISE NOTICE '📋 JWT 要求：';
    RAISE NOTICE '   • user_role: admin | teacher | student';
    RAISE NOTICE '   • 确保JWT包含正确的角色信息';
    RAISE NOTICE '=============================================================';
    RAISE NOTICE '🎯 测试用户：';
    RAISE NOTICE '   • 教师: 7bf7378e-a027-473e-97ac-3460ed3f170a';
    RAISE NOTICE '   • 学生: eed3824b-bba1-4309-8048-19d17367c084';
    RAISE NOTICE '=============================================================';
END $$;