SQL Query Optimizer

Act as a database engineer. Optimize this query.

Query:
```sql
#1<PASTE>
```

DB engine + version: #2<e.g. Postgres 15>
Table size (approx rows): #3<e.g. users ~50M, orders ~200M>
Current execution time: #4<MS or unknown>
What the query is supposed to return: #5<PLAIN ENGLISH>

Deliver:
1. The optimized query.
2. A bullet list of EVERY change you made, with one-line reason each.
3. Any indexes I should add (CREATE INDEX statements).
4. The expected complexity / cost change (e.g. "seq scan → index scan, O(N) → O(log N)").
5. Anything that depends on assumptions you can't verify — list those explicitly.