-- =====================================================
-- 开放所有消息系统权限给已认证用户
-- 适用于开发/测试环境，允许所有登录用户操作消息系统
-- ⚠️ 生产环境请谨慎使用！
-- =====================================================

-- 清理现有的限制性策略
DO $$
DECLARE
    r RECORD;
BEGIN
    RAISE NOTICE '🧹 清理现有限制性策略';
    
    -- 删除所有消息相关表的策略
    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);
        RAISE NOTICE '  删除策略: %.%', r.tablename, r.policyname;
    END LOOP;
    
    RAISE NOTICE '✅ 策略清理完成';
END $$;

-- 确保RLS仍然启用（但策略将非常宽松）
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. 消息类型权限策略 - 所有已认证用户可以完全操作
CREATE POLICY "authenticated_full_access_message_types" ON public.ak_message_types
    FOR ALL USING (auth.role() = 'authenticated')
    WITH CHECK (auth.role() = 'authenticated');

-- 2. 消息主表权限策略 - 所有已认证用户可以完全操作
CREATE POLICY "authenticated_full_access_messages" ON public.ak_messages
    FOR ALL USING (auth.role() = 'authenticated')
    WITH CHECK (auth.role() = 'authenticated');

-- 3. 消息接收者权限策略 - 所有已认证用户可以完全操作
CREATE POLICY "authenticated_full_access_recipients" ON public.ak_message_recipients
    FOR ALL USING (auth.role() = 'authenticated')
    WITH CHECK (auth.role() = 'authenticated');

-- 4. 消息群组权限策略 - 所有已认证用户可以完全操作
CREATE POLICY "authenticated_full_access_groups" ON public.ak_message_groups
    FOR ALL USING (auth.role() = 'authenticated')
    WITH CHECK (auth.role() = 'authenticated');

-- 5. 群组成员权限策略 - 所有已认证用户可以完全操作
CREATE POLICY "authenticated_full_access_group_members" ON public.ak_message_group_members
    FOR ALL USING (auth.role() = 'authenticated')
    WITH CHECK (auth.role() = 'authenticated');

-- 6. 消息模板权限策略 - 所有已认证用户可以完全操作
CREATE POLICY "authenticated_full_access_templates" ON public.ak_message_templates
    FOR ALL USING (auth.role() = 'authenticated')
    WITH CHECK (auth.role() = 'authenticated');

-- 7. 用户偏好权限策略 - 所有已认证用户可以完全操作
CREATE POLICY "authenticated_full_access_preferences" ON public.ak_user_message_preferences
    FOR ALL USING (auth.role() = 'authenticated')
    WITH CHECK (auth.role() = 'authenticated');

-- 8. 消息统计权限策略 - 所有已认证用户可以完全操作
CREATE POLICY "authenticated_full_access_stats" ON public.ak_message_stats
    FOR ALL USING (auth.role() = 'authenticated')
    WITH CHECK (auth.role() = 'authenticated');

-- =============================================================================
-- 创建便捷的操作函数（可选）
-- =============================================================================

-- 快速发送消息函数（简化版）
CREATE OR REPLACE FUNCTION public.send_message_simple(
    message_type_name TEXT,
    receiver_type TEXT,
    receiver_id UUID,
    title TEXT,
    content TEXT,
    sender_id UUID DEFAULT auth.uid()
)
RETURNS UUID AS $$
DECLARE
    new_message_id UUID;
    type_id UUID;
BEGIN
    -- 获取消息类型ID
    SELECT id INTO type_id FROM public.ak_message_types 
    WHERE type_name = message_type_name LIMIT 1;
    
    IF type_id IS NULL THEN
        -- 如果类型不存在，创建一个
        INSERT INTO public.ak_message_types (type_name, display_name, is_active)
        VALUES (message_type_name, message_type_name, true)
        RETURNING id INTO type_id;
    END IF;
    
    -- 插入消息
    INSERT INTO public.ak_messages (
        message_type_id, sender_type, sender_id, receiver_type, receiver_id,
        title, content, status, created_by
    ) VALUES (
        type_id, 'user', sender_id, receiver_type, receiver_id,
        title, content, 'sent', sender_id
    ) RETURNING id INTO new_message_id;
    
    -- 如果是单用户消息，自动创建接收记录
    IF receiver_type = 'user' THEN
        INSERT INTO public.ak_message_recipients (
            message_id, user_id, status, created_by
        ) VALUES (
            new_message_id, receiver_id, 'unread', sender_id
        );
    END IF;
    
    RETURN new_message_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 快速创建群组函数
CREATE OR REPLACE FUNCTION public.create_group_simple(
    group_name TEXT,
    description TEXT DEFAULT '',
    is_public BOOLEAN DEFAULT true,
    creator_id UUID DEFAULT auth.uid()
)
RETURNS UUID AS $$
DECLARE
    new_group_id UUID;
BEGIN
    -- 创建群组
    INSERT INTO public.ak_message_groups (
        name, description, is_public, status, created_by
    ) VALUES (
        group_name, description, is_public, 'active', creator_id
    ) RETURNING id INTO new_group_id;
    
    -- 添加创建者为群组成员
    INSERT INTO public.ak_message_group_members (
        group_id, user_id, role, status, joined_at, created_by
    ) VALUES (
        new_group_id, creator_id, 'owner', 'active', NOW(), creator_id
    );
    
    RETURN new_group_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 快速加入群组函数
CREATE OR REPLACE FUNCTION public.join_group_simple(
    group_id UUID,
    user_id UUID DEFAULT auth.uid()
)
RETURNS BOOLEAN AS $$
BEGIN
    -- 直接加入群组（无需审批）
    INSERT INTO public.ak_message_group_members (
        group_id, user_id, role, status, joined_at, created_by
    ) VALUES (
        group_id, user_id, 'member', 'active', NOW(), user_id
    ) ON CONFLICT (group_id, user_id) DO UPDATE SET
        status = 'active',
        joined_at = NOW();
    
    RETURN true;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- =============================================================================
-- 验证和测试
-- =============================================================================

-- 验证策略创建结果
DO $$
DECLARE
    policy_count INTEGER;
    function_count INTEGER;
BEGIN
    RAISE NOTICE '🔍 验证开放权限策略';
    
    -- 检查策略数量
    SELECT COUNT(*) INTO policy_count
    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');
    
    -- 检查便捷函数
    SELECT COUNT(*) INTO function_count
    FROM information_schema.routines
    WHERE routine_schema = 'public'
    AND routine_name IN ('send_message_simple', 'create_group_simple', 'join_group_simple');
    
    RAISE NOTICE '📊 开放权限统计:';
    RAISE NOTICE '  - 开放策略数量: %', policy_count;
    RAISE NOTICE '  - 便捷函数数量: %', function_count;
    
    IF policy_count >= 8 THEN
        RAISE NOTICE '🎉 所有已认证用户现在可以完全操作消息系统！';
        RAISE NOTICE '⚠️ 注意：这是非常宽松的权限设置，仅适用于开发/测试环境';
    ELSE
        RAISE NOTICE '⚠️ 策略创建可能不完整，请检查';
    END IF;
END $$;

-- =============================================================================
-- 使用示例和说明
-- =============================================================================

/*
-- 使用示例：

-- 1. 发送简单消息
SELECT public.send_message_simple(
    'notification',                           -- 消息类型
    'user',                                   -- 接收者类型
    'eed3824b-bba1-4309-8048-19d17367c084',  -- 接收者ID
    '测试消息',                               -- 标题
    '这是一条测试消息内容'                    -- 内容
);

-- 2. 创建群组
SELECT public.create_group_simple(
    '测试群组',                               -- 群组名称
    '这是一个测试群组',                       -- 描述
    true                                      -- 是否公开
);

-- 3. 加入群组
SELECT public.join_group_simple('group-id-here');

-- 4. 直接查询所有数据（现在允许）
SELECT * FROM public.ak_messages ORDER BY created_at DESC LIMIT 10;
SELECT * FROM public.ak_message_groups WHERE is_public = true;
SELECT * FROM public.ak_message_types;

-- 5. 直接插入数据（现在允许）
INSERT INTO public.ak_message_types (type_name, display_name, description)
VALUES ('custom', '自定义类型', '用户创建的自定义消息类型');

-- 6. 直接更新数据（现在允许）
UPDATE public.ak_messages SET status = 'read' WHERE id = 'message-id-here';

-- 7. 直接删除数据（现在允许）
DELETE FROM public.ak_message_templates WHERE created_by = auth.uid();
*/

-- 完成消息
SELECT 
    '🔓 消息系统权限已全面开放' as status,
    '所有已认证用户现在可以操作所有消息系统功能' as message,
    '⚠️ 仅适用于开发/测试环境' as warning;