Purchase requests aging report

# Purchase requests aging

## Purpose
Analyze purchase requests pending approval or processing. Identify "stale" requests, measure average approval times by level, and locate bottlenecks in the workflow.

## Instructions

### Step 1 — Retrieve pending requests
```
Call get_requests with:
  - status: pending (or submitted, awaiting_approval — depending on available statuses)
  - Pagination to retrieve all open requests

For each request: id, requestedById, amount, currency, createdAt,
updatedAt, status, costCenterId, description.

Calculate the age: today - createdAt (in days).
```

### Step 2 — Sort by age (aging buckets)
```
Sort pending requests by age:
  🔴 Critical: > 30 days with no progress
  🟠 Urgent: 15-30 days
  🟡 To monitor: 7-14 days
  🟢 Recent: < 7 days (normal)

For each bucket: number of requests, total amount.
```

### Step 3 — Analysis by cost center
```
For pending requests:
  Group by costCenterId
  Calculate: nb pending requests + total amount + average age per CC

Call get_cost_centers to resolve the names.

Identify: which cost center has the most stuck requests?
```

### Step 4 — Analyze processing times (recently processed requests)
```
Call get_requests with:
  - status: approved or rejected (processed requests)
  - updatedAt: last 30 days

For each processed request:
  processing_time = updatedAt - createdAt (days)

Calculate:
  Overall average approval time
  Median time
  % processed in < 48h | < 7d | < 14d | > 14d

Compare: do recently stuck requests have a rising processing time vs history?
```

### Step 5 — Identify bottlenecks
```
For requests > 30 days:
  Call get_users for the requestedById and see if a pattern emerges:
    - Are they all from the same team?
    - Are they in the same cost center?
    - Is there a common approver who might be absent?

High-amount requests stuck: > 5,000€ for > 15 days → operational risk
```

### Step 6 — Aging report
```
Present:
1. Overview:
   Total pending requests: X (X€)
   Average approval time (last 30 days processed): X days

2. Aging buckets:
   🔴 > 30d: X requests (X€)
   🟠 15-30d: X requests (X€)
   🟡 7-14d: X requests (X€)
   🟢 < 7d: X requests (X€)

3. Top 5 oldest requests:
   Requester | Amount | Age (days) | Cost center | Description

4. Cost centers with the most stuck requests:
   Team | Nb pending | Total amount | Average age

5. Recommended actions:
   - Requests > 30d: urgent escalation to manager
   - Requests > 5,000€ for > 15d: priority processing
   - Identify if an approver is on leave / absent
```

## Points of attention
- Acceptable approval times vary by amount (< 500€: 24h, > 5,000€: 5d)
- Some requests may be intentionally held (budget freeze, strategic decision)
- Do not interpret an isolated aging case in isolation — look at overall patterns

## Example prompts
- "Which purchase requests have been awaiting approval for too long?"
- "What is our average approval time for purchase requests?"
- "Are there urgent stuck requests I need to prioritize?"