I purchased a new MacBook Pro M3 and transferred my data from Time Machine. The spotlight issue persisted regardless all the recommendation that came from this thread. I end-up calling Apple.
I had conversations with 3 Apple tech support reps yesterday.
The first one was trying to convince me that everything is working as suppose to. When I refused to buy his story he escalated me to 2nd rep.
This one promised that he knows how to fix it and convinced me to jump through the hoops.
1) diskutil resetUserPermissions / 'id -u'
2) shutdown and start in recovery mode
3) while in recovery mode, start terminal and run: repairhomepermissions
4) quit terminal and reinstall operating system
The process took some time (1h 40 min), so he disconnected before its completion, promising that the issue will be fixed.
The issue was not resolved regardless. So I called Apple again.
The 3rd rep asked me to:
1) go to spotlight Privacy and add Macintosh HD
2) then close it.
3) wait 2 minutes (but it seemed we wanted 3 or 4)
4) go to spotlight Privacy and remove Macintosh HD
5) restart computer.
It worked !!! the spotlight start showing results for individual folders.
3rd time apparently has its charm.