-- =====================================================
-- 完整清理脚本 - 解决所有冲突
-- 同时处理触发器、函数、策略冲突
-- =====================================================

-- 第一步：清理触发器
DO $$
BEGIN
    RAISE NOTICE '🧹 第一步：清理触发器';
    
    -- 删除用户角色相关触发器
    DROP TRIGGER IF EXISTS trigger_update_user_roles_updated_at ON public.user_roles CASCADE;
    DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users CASCADE;
    
    -- 删除可能存在的旧版本触发器
    DROP TRIGGER IF EXISTS update_user_roles_updated_at ON public.user_roles CASCADE;
    DROP TRIGGER IF EXISTS handle_new_user_trigger ON auth.users CASCADE;
    
    RAISE NOTICE '✅ 触发器清理完成';
END $$;

-- 第二步：清理函数（处理参数名称冲突）
DO $$
BEGIN
    RAISE NOTICE '🧹 第二步：清理函数（解决参数冲突）';
    
    -- 删除所有可能冲突的函数（按依赖顺序）
    DROP FUNCTION IF EXISTS public.batch_update_user_roles(JSONB, UUID) CASCADE;
    DROP FUNCTION IF EXISTS public.batch_update_user_roles(JSONB) CASCADE;
    DROP FUNCTION IF EXISTS public.update_user_role(UUID, TEXT, UUID, JSONB) CASCADE;
    DROP FUNCTION IF EXISTS public.update_user_role(UUID, TEXT, UUID) CASCADE;
    DROP FUNCTION IF EXISTS public.update_user_role(UUID, TEXT) CASCADE;
    DROP FUNCTION IF EXISTS public.get_user_role(UUID) CASCADE;
    DROP FUNCTION IF EXISTS public.get_user_role() CASCADE;
    DROP FUNCTION IF EXISTS public.sync_user_role_metadata(UUID) CASCADE;
    DROP FUNCTION IF EXISTS public.user_has_permission(TEXT, UUID) CASCADE;
    DROP FUNCTION IF EXISTS public.can_access_resource(TEXT, UUID, TEXT, UUID) CASCADE;
    DROP FUNCTION IF EXISTS public.update_user_roles_updated_at() CASCADE;
    DROP FUNCTION IF EXISTS public.handle_new_user() CASCADE;
    DROP FUNCTION IF EXISTS public.test_message_permissions(UUID) CASCADE;
    DROP FUNCTION IF EXISTS public.test_deployment() CASCADE;
    
    RAISE NOTICE '✅ 函数清理完成（解决参数名称冲突）';
END $$;

-- 第三步：清理策略
DO $$
DECLARE
    r RECORD;
BEGIN
    RAISE NOTICE '🧹 第三步：清理策略';
    
    -- 清理 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);
        RAISE NOTICE '  删除策略: %', r.policyname;
    END LOOP;
    
    -- 清理消息相关表的策略
    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 $$;

-- 第四步：清理视图（如果存在冲突）
DO $$
DECLARE
    r RECORD;
BEGIN
    RAISE NOTICE '🧹 第四步：清理视图和错误策略';
    
    -- 删除可能错误创建在视图上的策略
    FOR r IN 
        SELECT schemaname, tablename, policyname 
        FROM pg_policies 
        WHERE schemaname = 'public' 
        AND tablename IN (
            SELECT table_name 
            FROM information_schema.views 
            WHERE table_schema = 'public'
        )
    LOOP
        BEGIN
            EXECUTE format('DROP POLICY IF EXISTS %I ON %I.%I', 
                          r.policyname, r.schemaname, r.tablename);
            RAISE NOTICE '  删除了视图上的错误策略: %.%', r.tablename, r.policyname;
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE '  无法删除策略: %.% (这是正常的)', r.tablename, r.policyname;
        END;
    END LOOP;
    
    -- 重新创建视图
    DROP VIEW IF EXISTS public.user_roles_with_email CASCADE;
    DROP VIEW IF EXISTS public.user_roles_detailed CASCADE;
    
    RAISE NOTICE '✅ 视图清理完成';
END $$;

-- 第五步：验证清理结果
DO $$
DECLARE
    trigger_count INTEGER;
    policy_count INTEGER;
    function_count INTEGER;
BEGIN
    RAISE NOTICE '🔍 第五步：验证清理结果';
    
    -- 检查触发器
    SELECT COUNT(*) INTO trigger_count
    FROM information_schema.triggers
    WHERE trigger_schema = 'public'
    AND trigger_name IN ('trigger_update_user_roles_updated_at', 'on_auth_user_created');
    
    -- 检查策略
    SELECT COUNT(*) INTO policy_count
    FROM pg_policies 
    WHERE schemaname = 'public' 
    AND tablename IN ('user_roles', 'ak_messages', 'ak_message_recipients');
    
    -- 检查核心函数
    SELECT COUNT(*) INTO function_count
    FROM information_schema.routines
    WHERE routine_schema = 'public'
    AND routine_name IN ('get_user_role', 'user_has_permission', 'sync_user_role_metadata');
    
    RAISE NOTICE '📊 清理统计:';
    RAISE NOTICE '  - 剩余触发器: %', trigger_count;
    RAISE NOTICE '  - 剩余策略: %', policy_count;
    RAISE NOTICE '  - 核心函数: %', function_count;
    
    IF trigger_count = 0 AND policy_count = 0 THEN
        RAISE NOTICE '🎉 清理成功！可以安全地重新部署';
    ELSE
        RAISE NOTICE '⚠️ 部分组件仍存在，请检查是否需要进一步清理';
    END IF;
END $$;

-- 第六步：重置RLS状态（确保表可以重新配置策略）
DO $$
DECLARE
    table_record RECORD;
BEGIN
    RAISE NOTICE '🔄 第六步：重置RLS状态';
    
    -- 对所有相关表禁用然后重新启用RLS
    FOR table_record IN 
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = 'public' 
        AND table_name 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')
    LOOP
        EXECUTE format('ALTER TABLE public.%I DISABLE ROW LEVEL SECURITY', table_record.table_name);
        EXECUTE format('ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY', table_record.table_name);
        RAISE NOTICE '  重置RLS: %', table_record.table_name;
    END LOOP;
    
    RAISE NOTICE '✅ RLS状态重置完成';
END $$;

-- 完成消息
DO $$
BEGIN
    RAISE NOTICE '🎉 完整清理完成！';
    RAISE NOTICE '📋 下一步：';
    RAISE NOTICE '  1. 运行您的主要部署脚本';
    RAISE NOTICE '  2. 验证所有功能正常';
    RAISE NOTICE '  3. 测试权限和角色分配';
END $$;

-- 最终状态检查
SELECT 
    '🔧 清理完成' as status,
    '系统已准备好重新部署' as message,
    (SELECT COUNT(*) FROM pg_policies WHERE schemaname = 'public') as remaining_policies,
    (SELECT COUNT(*) FROM information_schema.triggers WHERE trigger_schema = 'public') as remaining_triggers;