# 11 — Analytics

## Overview

KPI cards + bar/pie chart explorer. Powered by raw SQL aggregates over `users`, `events`, `vendor_services_locations`, and `services`.

**Status:** Live

The analytics surface is gated by permission group 3 ("Analytics Viewer" role + anyone with that permission). Charts are rendered by Highcharts on the client (via `analytics.js`).

---

## User Stories

| ID | As a | I want to | So that |
|----|------|-----------|---------|
| ANL-01 | Analytics Viewer | See the platform-wide KPI cards (clients, vendors, total users, locations, services, total/upcoming/today's/past/cancelled events, app vs web logins) | One-glance health |
| ANL-02 | Analytics Viewer | See a pie chart of bookings per service | Where demand concentrates |
| ANL-03 | Analytics Viewer | Filter a bar chart by a date range | Monthly trends |
| ANL-04 | Analytics Viewer | See per-month total events, cancelled events, total vendor sign-ups, total client sign-ups | Track growth |

---

## Screens & Flows

```
┌─────────────────────────────────┐
│ /event/analytics                │
│ analytics/index.blade.php       │
│  KPI cards (12)                 │
│  Pie: booked services           │
│  Bar: events + users per month  │
│  Date range picker              │
└──────────────┬──────────────────┘
               │ AJAX on load + on date filter
               ▼
┌──────────────────────────────────────────────────────────┐
│ /event/ajaxAnalyticsData                                 │
│ AnalyticsController::ajaxAnalyticsData()                 │
│  → 12-subquery single SQL → statistics + pie data        │
└──────────────────────────────────────────────────────────┘
                              │
                              ▼
┌──────────────────────────────────────────────────────────┐
│ /event/ajaxBarChartData?fromDate=&toDate=                │
│ AnalyticsController::barChartData()                      │
│  → month series + total events, cancelled events,        │
│     total_vendor, total_client per month                 │
└──────────────────────────────────────────────────────────┘
```

### Routes & Actions

| Route | Method | Handler | Description |
|-------|--------|---------|-------------|
| `/event/analytics` | GET | `EventController::analytics()` | Render dashboard |
| `/event/ajaxAnalyticsData` | GET | `AnalyticsController::ajaxAnalyticsData()` | KPI + pie data |
| `/event/ajaxBarChartData` | GET | `AnalyticsController::barChartData()` | Bar chart series |

---

## Data Model

### KPI Card Output (statistics block)

```php
{
  clients:            COUNT(users WHERE user_type='client'),
  vendors:            COUNT(users WHERE user_type='vendor'),
  total_users:        COUNT(users),
  locations:          COUNT(DISTINCT location_id FROM vendor_services_locations),
  services:           COUNT(services WHERE status='1'),
  total_events:       COUNT(events),
  upcomming_events:   COUNT(events WHERE event_date > CURDATE() AND is_cancelled='0'),
  todays_events:      COUNT(events WHERE event_date = CURDATE() AND is_cancelled='0'),
  past_events:        COUNT(events WHERE event_date < CURDATE() AND is_cancelled='0'),
  cancelled_events:   COUNT(events WHERE is_cancelled='1'),
  app_users:          COUNT(users WHERE last_login_from='app'),
  web_users:          COUNT(users WHERE last_login_from='web'),
}
```

### Pie Data

```php
[
  { name: <service.name>, y: COUNT(event_services WHERE service_id = service.id) },
  ...
]
```

(See `ServiceModel::bookedServiceCounts`.)

### Bar Data

```php
{
  series: [ "January", "February", ...],   // month labels covering [fromDate..toDate]
  column: {
    total:        [int, int, ...],
    canceled:     [int, int, ...],
    total_vendor: [int, int, ...],
    total_client: [int, int, ...],
  }
}
```

Derived from:
- `EventModel::barChartData($from, $to)` → `[{is_cancelled, event_month}, ...]`
- `Users::vendorData($from, $to)` → `[{vendors, event_month}, ...]`
- `Users::clientData($from, $to)` → `[{clients, event_month}, ...]`

---

## Validations & Business Rules

| Rule | Detail |
|------|--------|
| KPI numbers compute "now" — no date range | `ajaxAnalyticsData` ignores any query string |
| Bar chart respects `fromDate` + `toDate` | When omitted, queries produce NaN month inputs (see Known Issues) |
| Month series wraps across years | `getMonthSeries($startMonth, $endMonth)` — supports wrap-around (e.g. Nov → Feb) |
| Counts join via month-of-year, not year | Two events in Jan 2023 and Jan 2024 collapse into "January" — fine for short ranges, misleading for 12+ months |
| `cancelled_events` KPI does NOT depend on event_date | It counts all cancelled events ever |
| Active services only | `services WHERE status='1'` |

---

## API Endpoints

| Method | Path | Auth | Request | Response | Consumer |
|--------|------|------|---------|----------|----------|
| GET | `/event/analytics` | session + permission 3 | — | HTML | Browser |
| GET | `/event/ajaxAnalyticsData` | session (AJAX whitelisted) | — | `{ statistics: {...}, pie: [...] }` | Analytics page on load |
| GET | `/event/ajaxBarChartData` | session (AJAX whitelisted) | `fromDate?, toDate?` | `{ series: [...], column: {...} }` | Bar chart filter |

---

## Upstream Impact

- **`users` table** — counts by user_type and last_login_from.
- **`events` table** — total / today / future / past / cancelled counts.
- **`event_services` table** — pie chart per-service tallies.
- **`vendor_services_locations` table** — count of distinct location ids.
- **`services` table** — pie chart series + active-count KPI.

---

## Downstream Impact

- **Stakeholder dashboards** — KPI block is consumed by humans for daily reporting. Changing the JSON shape breaks the Blade rendering.
- **No external dashboards consume `/event/ajaxAnalyticsData`** — JS is local to this page.

---

## Impact of Changes

| If you change... | Risk to... | Level | Type |
|-----------------|------------|-------|------|
| Adding `users.deleted_at` (soft delete) | All KPI counts inflate by deleted users | High | Data |
| Adding `events.deleted_at` | Same — past/cancelled may double-count | High | Data |
| Renaming `is_cancelled` | 5 of 12 KPIs return zero | Critical | Data |
| Removing `last_login_from` column | `app_users`/`web_users` KPIs throw | High | Data |
| Changing `services.status` to enum string | `COUNT(...WHERE status='1')` returns 0 | High | Data |
| Adding new services rapidly | Pie chart becomes unreadable at >20 slices | Medium | UI |
| Reusing `barChartData` for a longer date range | Months across years collapse → misleading | Medium | Data |

---

## Known Issues

- **`barChartData` interpolates `$startDate` and `$endDate` directly into a raw SQL string** without binding. The values come from `$request->fromDate` and `$request->toDate` after `trim()`. **SQL injection risk** if those inputs ever bypass the date picker. **Fix owed**.
- **When no `fromDate`/`toDate` are provided**, `date('m', strtotime(null))` returns `01` (current month? depends on PHP version) — bar chart may show an unintended default.
- **Year is ignored** in bar-chart grouping (`MONTH(created_at)` only). A 14-month range double-counts months from year N and year N+1.
- **The KPI SQL is a single statement with 12 subqueries**. Reasonable now (small DB) but a performance trap as data grows.
- **`pie` has no max cap** — too many active services → unreadable pie.
- **No "online users" metric** — `last_login_from` tracks the *most recent* origin, not concurrency.