-- =====================================================
-- 恢复正常权限模式（从开发模式切换到生产模式）
-- 清理开发模式的开放权限，恢复角色基础的权限控制
-- =====================================================

-- 清理开发模式的开放策略
DO $$
DECLARE
    table_names TEXT[] := ARRAY[
        'ak_message_types', 'ak_messages', 'ak_message_recipients',
        'ak_message_groups', 'ak_message_group_members', 'ak_message_templates',
        'ak_user_message_preferences', 'ak_message_stats'
    ];
    table_name TEXT;
    r RECORD;
BEGIN
    RAISE NOTICE '🔒 开始恢复正常权限模式...';
    
    -- 删除所有开发模式策略
    FOREACH table_name IN ARRAY table_names LOOP
        FOR r IN 
            SELECT policyname 
            FROM pg_policies 
            WHERE schemaname = 'public' 
            AND tablename = table_name
            AND (policyname LIKE 'dev_%' OR policyname LIKE 'authenticated_full_%')
        LOOP
            EXECUTE format('DROP POLICY IF EXISTS %I ON public.%I', r.policyname, table_name);
            RAISE NOTICE '  删除开发策略: %.%', table_name, r.policyname;
        END LOOP;
    END LOOP;
    
    RAISE NOTICE '✅ 开发模式策略已清理';
END $$;

-- 清理开发便捷函数
DROP FUNCTION IF EXISTS public.dev_send_message(TEXT, TEXT, UUID, TEXT);
DROP FUNCTION IF EXISTS public.dev_create_group(TEXT, TEXT, BOOLEAN);
DROP FUNCTION IF EXISTS public.dev_add_to_group(UUID, UUID, TEXT);
DROP FUNCTION IF EXISTS public.dev_show_my_messages();
DROP FUNCTION IF EXISTS public.send_message_simple(TEXT, TEXT, UUID, TEXT, TEXT, UUID);
DROP FUNCTION IF EXISTS public.create_group_simple(TEXT, TEXT, BOOLEAN, UUID);
DROP FUNCTION IF EXISTS public.join_group_simple(UUID, UUID);

-- =============================================================================
-- 恢复基于角色的权限策略
-- =============================================================================

-- 1. 消息类型权限策略
CREATE POLICY "message_types_view" ON public.ak_message_types
    FOR SELECT USING (
        is_active = true AND (
            get_user_role(auth.uid()) = 'admin' OR
            get_user_role(auth.uid()) = 'teacher' OR
            get_user_role(auth.uid()) = 'student'
        )
    );

CREATE POLICY "message_types_manage" ON public.ak_message_types
    FOR ALL USING (get_user_role(auth.uid()) = 'admin')
    WITH CHECK (get_user_role(auth.uid()) = 'admin');

-- 2. 消息主表权限策略
CREATE POLICY "messages_send" ON public.ak_messages
    FOR INSERT WITH CHECK (
        sender_id = auth.uid() AND
        get_user_role(auth.uid()) IN ('admin', 'teacher', 'student')
    );

CREATE POLICY "messages_view_own" ON public.ak_messages
    FOR SELECT USING (
        sender_id = auth.uid() OR 
        receiver_id = auth.uid() OR
        get_user_role(auth.uid()) = 'admin'
    );

CREATE POLICY "messages_update_own" ON public.ak_messages
    FOR UPDATE USING (
        sender_id = auth.uid() OR
        get_user_role(auth.uid()) = 'admin'
    ) WITH CHECK (
        sender_id = auth.uid() OR
        get_user_role(auth.uid()) = 'admin'
    );

-- 3. 消息接收者权限策略
CREATE POLICY "recipients_view_own" ON public.ak_message_recipients
    FOR SELECT USING (
        user_id = auth.uid() OR
        get_user_role(auth.uid()) = 'admin'
    );

CREATE POLICY "recipients_update_own" ON public.ak_message_recipients
    FOR UPDATE USING (user_id = auth.uid())
    WITH CHECK (user_id = auth.uid());

-- 4. 消息群组权限策略
CREATE POLICY "groups_view_public" ON public.ak_message_groups
    FOR SELECT USING (
        is_public = true OR
        get_user_role(auth.uid()) = 'admin' OR
        EXISTS (
            SELECT 1 FROM public.ak_message_group_members 
            WHERE group_id = id AND user_id = auth.uid() AND status = 'active'
        )
    );

CREATE POLICY "groups_create" ON public.ak_message_groups
    FOR INSERT WITH CHECK (
        created_by = auth.uid() AND
        get_user_role(auth.uid()) IN ('admin', 'teacher')
    );

CREATE POLICY "groups_manage_own" ON public.ak_message_groups
    FOR UPDATE USING (
        created_by = auth.uid() OR
        get_user_role(auth.uid()) = 'admin'
    ) WITH CHECK (
        created_by = auth.uid() OR
        get_user_role(auth.uid()) = 'admin'
    );

-- 5. 群组成员权限策略
CREATE POLICY "group_members_view" ON public.ak_message_group_members
    FOR SELECT USING (
        user_id = auth.uid() OR
        get_user_role(auth.uid()) = 'admin' OR
        is_group_member(group_id, auth.uid())
    );

CREATE POLICY "group_members_join" ON public.ak_message_group_members
    FOR INSERT WITH CHECK (
        user_id = auth.uid() AND
        (
            get_user_role(auth.uid()) = 'admin' OR
            EXISTS (
                SELECT 1 FROM public.ak_message_groups 
                WHERE id = group_id AND is_public = true
            )
        )
    );

CREATE POLICY "group_members_leave" ON public.ak_message_group_members
    FOR UPDATE USING (user_id = auth.uid())
    WITH CHECK (user_id = auth.uid());

-- 6. 消息模板权限策略
CREATE POLICY "templates_view" ON public.ak_message_templates
    FOR SELECT USING (
        is_active = true AND (
            is_public = true OR
            created_by = auth.uid() OR
            get_user_role(auth.uid()) = 'admin'
        )
    );

CREATE POLICY "templates_manage_own" ON public.ak_message_templates
    FOR ALL USING (
        created_by = auth.uid() OR
        get_user_role(auth.uid()) = 'admin'
    ) WITH CHECK (
        created_by = auth.uid() OR
        get_user_role(auth.uid()) = 'admin'
    );

-- 7. 用户偏好权限策略
CREATE POLICY "preferences_own" ON public.ak_user_message_preferences
    FOR ALL USING (user_id = auth.uid())
    WITH CHECK (user_id = auth.uid());

-- 8. 消息统计权限策略
CREATE POLICY "stats_view_own" ON public.ak_message_stats
    FOR SELECT USING (
        user_id = auth.uid() OR
        get_user_role(auth.uid()) = 'admin'
    );

CREATE POLICY "stats_manage_own" ON public.ak_message_stats
    FOR ALL USING (
        user_id = auth.uid() OR
        get_user_role(auth.uid()) = 'admin'
    ) WITH CHECK (
        user_id = auth.uid() OR
        get_user_role(auth.uid()) = 'admin'
    );

-- =============================================================================
-- 验证权限恢复
-- =============================================================================

DO $$
DECLARE
    dev_policy_count INTEGER;
    normal_policy_count INTEGER;
    dev_function_count INTEGER;
BEGIN
    -- 检查开发模式策略是否已清理
    SELECT COUNT(*) INTO dev_policy_count
    FROM pg_policies 
    WHERE schemaname = 'public' 
    AND (policyname LIKE 'dev_%' OR policyname LIKE 'authenticated_full_%');
    
    -- 检查正常策略数量
    SELECT COUNT(*) INTO normal_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 dev_function_count
    FROM information_schema.routines
    WHERE routine_schema = 'public'
    AND routine_name LIKE 'dev_%';
    
    RAISE NOTICE '🔒 权限恢复统计:';
    RAISE NOTICE '  - 残留开发策略: % 个', dev_policy_count;
    RAISE NOTICE '  - 正常权限策略: % 个', normal_policy_count;
    RAISE NOTICE '  - 残留开发函数: % 个', dev_function_count;
    
    IF dev_policy_count = 0 AND dev_function_count = 0 THEN
        RAISE NOTICE '🎯 权限已成功恢复到正常模式！';
        RAISE NOTICE '✅ 现在基于用户角色(admin/teacher/student)进行权限控制';
        RAISE NOTICE '📋 确保已正确设置用户角色和相关函数';
    ELSE
        RAISE NOTICE '⚠️ 权限恢复可能不完整，请检查';
    END IF;
END $$;

-- 完成提示
SELECT 
    '🔒 正常权限模式' as mode,
    '已恢复' as status,
    '基于角色的权限控制已启用' as description,
    'admin/teacher/student角色权限生效' as info;