# Task 12 — Stats Aggregations (Real SQL)

Replace the TODO placeholders in `StatsService` with real queries.

## File to modify

```
apps/api/src/modules/stats/stats.service.ts
```

## What to fill in

The `cityStats()` method already returns shape; finish the missing fields.
And `myStats()` and `leaderboard()` need real queries.

### 1. Top contributors (city)

```ts
const top = await this.prisma.$queryRaw<
  Array<{ userId: number; fullName: string; viharCount: bigint; totalKm: number }>
>`
  SELECT
    u.user_id AS userId,
    u.full_name AS fullName,
    COUNT(DISTINCT vv.vihar_id) AS viharCount,
    COALESCE(SUM(v.actual_distance_km), 0) AS totalKm
  FROM user u
  JOIN vihar_volunteer vv ON vv.user_id = u.user_id AND vv.is_active = 1
  JOIN vihar v ON v.vihar_id = vv.vihar_id
  WHERE u.city_id = ${cityId}
    AND v.status IN ('completed', 'auto_closed')
    AND v.vihar_date BETWEEN ${from} AND ${to}
  GROUP BY u.user_id, u.full_name
  ORDER BY totalKm DESC, viharCount DESC
  LIMIT 10
`;
return top.map((r) => ({
  userId: r.userId,
  fullName: r.fullName,
  viharCount: Number(r.viharCount),
  totalKm: Number(r.totalKm),
}));
```

### 2. Vihars by locality

```ts
const byLocality = await this.prisma.vihar.groupBy({
  by: ['departureLocalityId'],
  where: { cityId, viharDate: { gte: from, lte: to } },
  _count: { _all: true },
});
const localities = await this.prisma.locality.findMany({
  where: { cityId },
  select: { localityId: true, name: true },
});
const lookup = new Map(localities.map((l) => [l.localityId, l.name]));
return byLocality.map((g) => ({
  localityId: g.departureLocalityId,
  name: g.departureLocalityId ? lookup.get(g.departureLocalityId) ?? 'Unknown' : 'Unassigned',
  count: g._count._all,
}));
```

### 3. Vihars by samuday — same pattern, swap `departureLocalityId` for `samudayId`.

### 4. Daily trend (last 30 days)

```ts
const trend = await this.prisma.$queryRaw<Array<{ date: Date; count: bigint }>>`
  SELECT vihar_date AS date, COUNT(*) AS count
  FROM vihar
  WHERE city_id = ${cityId}
    AND vihar_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
  GROUP BY vihar_date
  ORDER BY vihar_date ASC
`;
return trend.map((r) => ({ date: r.date.toISOString().slice(0, 10), count: Number(r.count) }));
```

### 5. Pending allocations / approvals

```ts
const pendingAllocations = await this.prisma.vihar.count({
  where: {
    cityId,
    status: 'planned',
    viharDate: { gte: new Date() },
    allocations: { none: { isActive: true } },
  },
});

const pendingApprovals = await this.prisma.viharVolunteer.count({
  where: {
    distanceApprovalStatus: 'pending_captain',
    vihar: { cityId },
  },
});
```

### 6. myStats — comparison vs previous period

```ts
const previousFrom = new Date(from.getTime() - (to.getTime() - from.getTime() + 1));
const previousTo = new Date(from.getTime() - 1);

const previousCount = await this.prisma.viharVolunteer.count({
  where: {
    userId,
    isActive: true,
    vihar: {
      status: { in: ['completed', 'auto_closed'] },
      viharDate: { gte: previousFrom, lte: previousTo },
    },
  },
});

const pctChange =
  previousCount === 0
    ? null
    : Math.round(((inPeriod.length - previousCount) / previousCount) * 100);
```

### 7. myStats — rank

```ts
const all = await this.prisma.$queryRaw<Array<{ userId: number; totalKm: number }>>`
  SELECT u.user_id AS userId, COALESCE(SUM(v.actual_distance_km), 0) AS totalKm
  FROM user u
  LEFT JOIN vihar_volunteer vv ON vv.user_id = u.user_id AND vv.is_active = 1
  LEFT JOIN vihar v ON v.vihar_id = vv.vihar_id
    AND v.status IN ('completed','auto_closed')
  WHERE u.city_id = (SELECT city_id FROM user WHERE user_id = ${userId})
    AND u.is_volunteer = 1
    AND u.is_active = 1
  GROUP BY u.user_id
  ORDER BY totalKm DESC
`;
const myIndex = all.findIndex((r) => r.userId === userId);
return myIndex === -1 ? null : { position: myIndex + 1, total: all.length };
```

### 8. leaderboard

Mirror of top contributors but parameterized by `metric`:
- `'km'` → ORDER BY totalKm DESC
- `'count'` → ORDER BY viharCount DESC
- `'streak'` → defer to Phase 2 (computing streaks needs windowing)

For Phase 1, treat `'streak'` same as `'count'` and add a TODO.

## Acceptance

1. Hit `GET /api/stats/city?period=this_month` after seeding some test vihars.
   All fields populated, no zeros where there shouldn't be.
2. `GET /api/stats/me?period=this_month` returns lifetime, current, comparison,
   rank, recent.
3. `GET /api/stats/leaderboard?period=this_month&metric=km` returns top 10.
4. Captain dashboard tiles show real numbers.
5. With zero vihars, all numbers are 0 (not null, not undefined, not NaN).

## Gotchas

- BigInt from raw queries: `COUNT(*)` returns BigInt in MySQL via Prisma raw.
  Always wrap in `Number()`.
- Decimal columns (`actualDistanceKm`) come back as Prisma `Decimal` objects.
  Coerce via `Number(d)`.
- `_count` filtered counts only work when the relation is explicitly named —
  double-check Prisma version compatibility.
- Date math: `new Date(from.getTime() - (...))` works in pure JS; don't use
  `date-fns` here, keep server lean.

## Out of scope

- Frequent route pairs heatmap — Phase 2
- Per-volunteer GPS heatmap — Phase 2
- Real-time / streaming stats — Phase 2 (current is fine for monthly reports)