feat: Order ai catalog items based on catalog priority

What does this MR do and why?

This MR improves the discoverability of AI catalog items by implementing a three-tier priority-based sorting system. Previously, AI catalog items were ordered with only two priorities: foundational items first, then all remaining items.

New sorting order:

1. GitLab foundational agents & External Agent - GitLab-maintained agents like Planner and Security Analyst. External agents like Claude Agent by GitLab and Codex Agent by GitLab
2. GitLab foundational flows - Built-in flows maintained by GitLab
3. User's authorized project items - Agents/flows created in projects the user has access to
4. All other public items - Community-created content

This change is controlled by a feature flag (ai_catalog_priority_sort) that is enabled by default.

Impact:

• Users can now easily discover both GitLab's trusted foundational items and their organization's custom agents/flows
• Applies automatically to both Explore-level catalog pages and Group/Project > Automate pages

Issue #587379

References

Screenshots or screen recordings

User having access of below 3 projects (expand this), so these project's items will be visible in priority.

Explore Level	Group Level	Project Level

How to set up and validate locally

1. Enable the feature flag: Feature.enable(:ai_catalog_priority_sort)
2. Navigate to the AI Catalog: /explore/ai-catalog/agents or /explore/ai-catalog/flows
3. Verify the sorting order:
   • Foundational agents/flows appear first
   • Items from your authorised projects appear second
   • Other public items appear last
4. Test at group & project level: Navigate to a project's Automate page and verify the same sorting applies
5. Verify above behaviour on SaaS and SM mode, with toggling feature flag ON and OFF

Note: For foundational agent we are not creating item_consumer records, so they are not visible on Group/Project > Automate pages

Database review

1. Query plan for explore level page - https://postgres.ai/console/gitlab/gitlab-production-main/sessions/48506/commands/145729
2. Query plan for project/group level page - https://console.postgres.ai/gitlab/gitlab-production-main/sessions/48506/commands/145732

Discussion about different approach - !222430 (merged)

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.