Feature: Aggregation Pipeline ↔ SQL Joins/Group By Support

**Summary**
Extend the converter to handle **SQL joins, GROUP BY, and aggregate functions** and map them to MongoDB’s **aggregation pipeline** (and vice versa). This expands beyond basic `SELECT`/`WHERE`/`LIMIT` to cover real-world analytics and reporting queries.

---

## Why

* Current library is great for simple CRUD-like conversions, but most production workloads need aggregation.
* Developers migrating between relational DBs and MongoDB often need to convert **joins, grouping, and aggregates**.
* Bridges a major feature gap: SQL analytics → Mongo `$lookup`, `$group`, `$project`, `$sort`, `$limit`.

---

## Scope (MVP)

### SQL → Mongo

* Recognize **JOIN** clauses → translate to `$lookup` (with `as`, `localField`, `foreignField`).
* Translate `GROUP BY` + aggregates:

* `COUNT(*)` → `{ $sum: 1 }`
* `SUM(col)` → `{ $sum: "$col" }`
* `AVG(col)` → `{ $avg: "$col" }`
* `MIN/MAX(col)` → `{ $min: "$col" }` / `{ $max: "$col" }`.
* Support `ORDER BY`, `LIMIT`, `OFFSET` → `$sort`, `$limit`, `$skip`.

Example:

```sql
SELECT u.name, COUNT(o.id) AS total_orders
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 30
GROUP BY u.name
ORDER BY total_orders DESC
LIMIT 5;
```

→

```python
[
{"$match": {"age": {"$gt": 30}}},
{"$lookup": {
"from": "orders",
"localField": "id",
"foreignField": "user_id",
"as": "orders"
}},
{"$unwind": "$orders"},
{"$group": {"_id": "$name", "total_orders": {"$sum": 1}}},
{"$sort": {"total_orders": -1}},
{"$limit": 5}
]
```

---

### Mongo → SQL

* Convert `$lookup` to `JOIN` (inner or left outer depending on `preserveNullAndEmptyArrays`).
* Convert `$group` to `GROUP BY` + aggregates.
* Convert `$project` to `SELECT` expressions.
* Convert `$sort`, `$skip`, `$limit` accordingly.

---

## Acceptance Criteria

* SQL `JOIN + GROUP BY` queries convert to valid MongoDB pipelines.
* Mongo pipelines with `$lookup` + `$group` roundtrip back into equivalent SQL.
* Tests include:

* Simple join (users → orders).
* Aggregation only (SUM/COUNT).
* Combination of `WHERE`, `JOIN`, `GROUP BY`, `ORDER BY`, `LIMIT`.

---

## Implementation Notes

* Update parser to capture `JOIN` and `GROUP BY`.
* Extend `sql_to_mongo` to emit aggregation arrays (instead of simple `find`).
* Add `mongo_to_sql` mappings for `$lookup` and `$group`.
* Backward compatibility: if no joins/aggregates, keep current simpler behavior.
* Add **`mode="pipeline"` option** to force full pipeline output even for simple queries.

---

## Tasks

* [ ] Extend SQL parser to handle `JOIN` and `GROUP BY`.
* [ ] Implement SQL → Mongo `$lookup` mapping.
* [ ] Implement SQL → Mongo `$group` (COUNT, SUM, AVG, MIN, MAX).
* [ ] Implement Mongo `$lookup/$group` → SQL JOIN/GROUP BY.
* [ ] Add roundtrip unit tests.
* [ ] Update README with aggregation examples.

---