A catalog-based photo manager is the right fit, and you do not need to reorganize your folders first. Searching by metadata works fine even if images stay in mixed folder structures.

From the answers, Adobe Lightroom is a strong option if you want robust cataloging, ratings, keywords, collections, and good long-term workflow support. Daminion was also suggested as a cataloging-focused tool, with the advantage that it syncs tags into image metadata so they travel with the files.

The key best practice is portability: prefer software that can write keywords/captions/ratings into standard image metadata (or sidecar files where appropriate), not only into its own database. That makes moves between computers or apps safer.

Be cautious about keeping all metadata in a completely separate index only: if files are renamed or moved, links can break. A database/catalog is useful for speed and features, but having metadata embedded or synced to the files is the safest backup of your organizational work.

For duplicates, many photo managers can help detect them during import or library cleanup. Consistent import naming and folder rules can also reduce future duplicates.