-- 查询当前数据库中的 Analytics RPC 函数状态

-- 1. 查询所有 get_teacher_analytics 相关函数
SELECT 
    routine_name as function_name,
    routine_type as type,
    data_type as return_type,
    routine_definition as definition,
    specific_name,
    routine_schema,
    security_type,
    is_deterministic,
    sql_data_access,
    routine_body,
    external_language
FROM information_schema.routines 
WHERE routine_schema = 'public' 
AND routine_name LIKE '%teacher_analytics%'
ORDER BY routine_name, specific_name;

-- 2. 查询函数的参数信息
SELECT 
    routine_name,
    parameter_name,
    data_type,
    parameter_mode,
    ordinal_position,
    parameter_default
FROM information_schema.parameters 
WHERE specific_schema = 'public' 
AND routine_name LIKE '%teacher_analytics%'
ORDER BY routine_name, ordinal_position;

-- 3. 查询所有 analytics 相关函数
SELECT 
    routine_name as function_name,
    routine_type as type,
    data_type as return_type,
    specific_name
FROM information_schema.routines 
WHERE routine_schema = 'public' 
AND (
    routine_name LIKE '%analytics%' OR 
    routine_name LIKE '%performers%' OR 
    routine_name LIKE '%chart_data%' OR 
    routine_name LIKE '%recent_activities%'
)
ORDER BY routine_name;

-- 4. 查询函数权限
SELECT 
    routine_name,
    grantor,
    grantee,
    privilege_type,
    is_grantable
FROM information_schema.routine_privileges 
WHERE routine_schema = 'public' 
AND routine_name IN ('get_teacher_analytics', 'get_top_performers', 'get_chart_data', 'get_recent_activities')
ORDER BY routine_name, grantee;

-- 5. 尝试调用现有的 get_teacher_analytics 函数（如果存在）
DO $$
DECLARE
    test_result jsonb;
    func_exists boolean := false;
BEGIN
    -- 检查函数是否存在
    SELECT EXISTS(
        SELECT 1 FROM information_schema.routines 
        WHERE routine_schema = 'public' 
        AND routine_name = 'get_teacher_analytics'
    ) INTO func_exists;
    
    IF func_exists THEN
        BEGIN
            -- 尝试调用无参数版本
            SELECT public.get_teacher_analytics() INTO test_result;
            RAISE NOTICE '✅ get_teacher_analytics() 调用成功: %', test_result;
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE '❌ get_teacher_analytics() 调用失败: %', SQLERRM;
            
            -- 尝试调用带参数版本
            BEGIN
                SELECT public.get_teacher_analytics(NULL, NULL, NULL) INTO test_result;
                RAISE NOTICE '✅ get_teacher_analytics(NULL, NULL, NULL) 调用成功: %', test_result;
            EXCEPTION WHEN OTHERS THEN
                RAISE NOTICE '❌ get_teacher_analytics(NULL, NULL, NULL) 调用也失败: %', SQLERRM;
            END;
        END;
    ELSE
        RAISE NOTICE '❌ 函数 get_teacher_analytics 不存在';
    END IF;
END;
$$;

-- 6. 显示当前数据库连接信息
SELECT 
    current_database() as database_name,
    current_schema() as current_schema,
    current_user as current_user,
    session_user as session_user,
    version() as postgres_version;

-- 7. 检查是否有冲突的函数定义
WITH function_conflicts AS (
    SELECT 
        routine_name,
        COUNT(*) as function_count,
        array_agg(specific_name) as specific_names,
        array_agg(data_type) as return_types
    FROM information_schema.routines 
    WHERE routine_schema = 'public' 
    AND routine_name IN ('get_teacher_analytics', 'get_top_performers', 'get_chart_data', 'get_recent_activities')
    GROUP BY routine_name
    HAVING COUNT(*) > 1
)
SELECT 
    routine_name as conflicted_function,
    function_count,
    specific_names,
    return_types
FROM function_conflicts;

-- 8. 生成清理建议
SELECT 
    '-- 清理建议：' as suggestion_type,
    'DROP FUNCTION IF EXISTS public.' || routine_name || '(' || 
    COALESCE(
        (SELECT string_agg(data_type, ', ' ORDER BY ordinal_position) 
         FROM information_schema.parameters p 
         WHERE p.specific_schema = r.routine_schema 
         AND p.specific_name = r.specific_name), 
        ''
    ) || ');' as cleanup_command
FROM information_schema.routines r
WHERE routine_schema = 'public' 
AND routine_name IN ('get_teacher_analytics', 'get_top_performers', 'get_chart_data', 'get_recent_activities')
ORDER BY routine_name;