Auditing a Query for Sargability Failures

A teammate wrote this query to find recently active premium users and can't figure out why it's slow despite indexes existing on users(email), users(signup_date), and users(account_tier):

SELECT id, email, account_tier
FROM users
WHERE UPPER(email) LIKE '%@ACME.COM'
  AND EXTRACT(YEAR FROM signup_date) = 2024
  AND account_tier + 0 = 3;

1. Identify every sargability failure in this query — there is more than one, and they are not all the same kind of failure. For each, explain the specific mechanism that breaks index usage.
2. Rewrite the query so every predicate is sargable against a reasonably designed index, and specify what index (or indexes) you'd create to support it.
3. One of the three predicates may remain fundamentally hard to make fully sargable even after your best rewrite. Identify which one, explain why, and describe the best available fix.