-- =====================================================
-- 快速修复策略冲突
-- 解决 "policy already exists" 错误
-- =====================================================

-- 清理 user_roles 表的所有可能策略
DO $$
DECLARE
    r RECORD;
BEGIN
    -- 删除 user_roles 表的所有策略
    FOR r IN 
        SELECT policyname 
        FROM pg_policies 
        WHERE schemaname = 'public' 
        AND tablename = 'user_roles'
    LOOP
        EXECUTE format('DROP POLICY IF EXISTS %I ON public.user_roles', r.policyname);
    END LOOP;
    
    RAISE NOTICE '✅ 已清理 user_roles 表的所有策略';
END $$;

-- 清理所有消息相关表的策略（如果需要）
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 $$;

-- 验证策略清理结果
DO $$
DECLARE
    policy_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO policy_count
    FROM pg_policies 
    WHERE schemaname = 'public' 
    AND tablename IN ('user_roles', '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');
    
    IF policy_count = 0 THEN
        RAISE NOTICE '🎉 策略清理完成: 所有冲突策略已删除';
    ELSE
        RAISE NOTICE '⚠️ 仍有 % 个策略存在，可能需要手动检查', policy_count;
    END IF;
END $$;

-- 完成消息
SELECT 
    '✅ 策略冲突已解决' as status,
    '现在可以重新运行您的SQL脚本' as message;