# ISP360 Reporting Specification

**Version:** 1.0  
**Date:** 2026-06-13  
**Prepared by:** Polakis Technologies  

Companion: [DATABASE_DESIGN.md](DATABASE_DESIGN.md) | [RBAC_SPEC.md](RBAC_SPEC.md) | [PROJECT_STRUCTURE.md](PROJECT_STRUCTURE.md) | [UI_SHELL_PATTERN.md](UI_SHELL_PATTERN.md)

---

## 1. Overview

ISP360 provides a **comprehensive reporting suite** for ISP finance and operations teams: 14 built-in reports, a custom report builder, scheduled email delivery, and multi-format export (screen, CSV, XLSX, PDF).

All report pages live at the project root with the `report_` prefix and use the **admin UI shell** ([UI_SHELL_PATTERN.md](UI_SHELL_PATTERN.md)). Data is tenant-scoped, aggregated from operational tables and pre-computed rollup tables for performance.

### Core Class

| Class | File | Responsibility |
|-------|------|----------------|
| `ReportEngine` | `includes/ReportEngine.php` | Run reports, cache, export, schedule execution |

### Design Principles

| Principle | Implementation |
|-----------|----------------|
| Tenant isolation | Every query filters `tenant_id = tenant_context()` |
| Cache before compute | Check `report_cache` before heavy aggregation |
| Pre-aggregated rollups | Cron fills `*_aggregates_*` and `*_metrics_*` tables |
| Screen-first | Chart.js + DataTables on page; export on demand |
| Parameterized date ranges | `period` + optional `date_from`/`date_to` on all reports |
| No raw SQL from UI | Report builder uses whitelisted `report_definitions` templates |

### Report Access Roles

| Role | Access |
|------|--------|
| ISP Admin | Full — view, export, schedule, builder |
| Finance User | Full reporting permissions |
| Support Agent | `reports.view` only (read-only) |
| Technician | `reports.view` for network/PPPoE/hotspot/router reports |

---

## 2. Architecture

```mermaid
flowchart TB
    subgraph ui [Admin UI — report_*]
        Rev["report_revenue.php"]
        Usage["report_usage.php"]
        Builder["report_builder.php"]
        Sched["report_schedules.php"]
    end

    subgraph ajax [AJAX Layer]
        RevData["ajax/report_revenue_data.php"]
        Export["ajax/export_report.php"]
        SaveDef["ajax/save_report_definition.php"]
        SaveSched["ajax/save_report_schedule.php"]
    end

    subgraph core [ReportEngine]
        RE["ReportEngine.php"]
        Cache["report_cache lookup"]
        Runners["Report runners"]
        Exporters["CSV / XLSX / PDF"]
    end

    subgraph cron [Background]
        CacheCron["cron/report_cache.php"]
        DashCron["cron/dashboard_metrics.php"]
        AggCron["cron/revenue_aggregate.php"]
    end

    subgraph data [Database]
        Defs["report_definitions"]
        CacheTbl["report_cache"]
        SchedTbl["report_schedules"]
        Exports["report_exports"]
        Rollups["revenue_aggregates_daily"]
        Views["vw_revenue_by_period"]
    end

    Rev --> RevData
    RevData --> RE
    RE --> Cache
    Cache -->|miss| Runners
    Runners --> Views
    Runners --> Rollups
    RE --> CacheTbl
    Export --> Exporters
    Builder --> SaveDef
    Sched --> SaveSched
    CacheCron --> RE
    DashCron --> data
```

---

## 3. Database Tables

### 3.1 Report Infrastructure

| Table | Role |
|-------|------|
| `report_definitions` | Built-in + custom report metadata, query templates |
| `report_schedules` | Cron schedules, recipients, output format |
| `report_exports` | Generated file queue (CSV/XLSX/PDF) |
| `report_cache` | Cached JSON results keyed by parameters hash |

### 3.2 Pre-Aggregated Rollups

| Table | Granularity | Used By |
|-------|-------------|---------|
| `revenue_aggregates_daily` | Daily | `report_revenue`, `report_collections` |
| `revenue_aggregates_monthly` | Monthly | `report_revenue`, `report_tax` |
| `usage_aggregates_daily` | Daily per customer/package | `report_usage` |
| `usage_aggregates_monthly` | Monthly | `report_usage` |
| `network_metrics_hourly` | Hourly per router | `report_network`, `report_routers` |
| `customer_metrics_daily` | Daily tenant snapshot | `report_customers` |
| `dashboard_metrics` | Realtime/hourly/daily KPIs | `dashboard.php`, reports summary |

### 3.3 Views

| View | Purpose |
|------|---------|
| `vw_revenue_by_period` | Payment revenue by day/month/method |
| `vw_customer_balances` | Outstanding invoices + unallocated payments |
| `vw_voucher_inventory` | Voucher batch utilization |
| `vw_router_health` | Router online status + telemetry |
| `vw_tenant_dashboard_metrics` | Top-level tenant KPIs |
| `vw_active_pppoe_sessions` | Live PPPoE session detail |

### 3.4 Source Tables (Live Queries)

| Report | Primary Tables |
|--------|----------------|
| Collections | `payments`, `payment_allocations`, `invoices` |
| PPPoE | `pppoe_sessions`, `radacct` |
| Hotspot | `hotspot_sessions`, `hotspot_usage` |
| Subscriptions | `customer_subscriptions`, `packages` |
| Tax | `invoices`, `invoice_items`, `tax_rates` |
| Aging | `invoices`, `vw_customer_balances` |
| Tickets | `tickets`, `ticket_statuses`, `ticket_priorities` |
| Routers | `router_metrics`, `vw_router_health` |

---

## 4. Report Catalog

14 built-in reports mapped to `report_definitions.report_code` (seeded as `is_system = 1`).

| Page | Code | Category | Chart | Primary KPIs |
|------|------|----------|-------|--------------|
| `report_revenue.php` | `revenue` | billing | Line | Gross/net revenue, payment count, MoM growth |
| `report_collections.php` | `collections` | billing | Stacked bar | Collected vs outstanding, collection rate % |
| `report_aging.php` | `aging` | billing | Bar + table | AR buckets: 0-30, 31-60, 61-90, 90+ days |
| `report_tax.php` | `tax` | billing | Bar | Tax collected by rate, taxable vs exempt |
| `report_usage.php` | `usage` | network | Bar | Total GB, top customers, cap utilization |
| `report_pppoe.php` | `pppoe` | network | Line | Active sessions, new/disconnects, peak concurrent |
| `report_hotspot.php` | `hotspot` | network | Line | Sessions, unique users, data transferred |
| `report_network.php` | `network` | network | Multi-line | Bandwidth in/out, latency, packet loss |
| `report_routers.php` | `routers` | network | Gauge + table | Online %, CPU/memory, alert count |
| `report_customers.php` | `customers` | crm | Line | Active, new, churned, ARPU |
| `report_subscriptions.php` | `subscriptions` | crm | Doughnut | Active/suspended/expired by package |
| `report_vouchers.php` | `vouchers` | ops | Bar | Sold/used/expired by batch |
| `report_tickets.php` | `tickets` | ops | Bar | Open/closed, avg resolution time, by category |
| `report_builder.php` | — | custom | Configurable | User-defined |
| `report_schedules.php` | — | admin | — | Schedule management UI |

### 4.1 Common Report Parameters

All `ajax/report_*_data.php` endpoints accept:

| Parameter | Type | Default | Notes |
|-----------|------|---------|-------|
| `period` | enum | `month` | `day`, `week`, `month`, `quarter`, `year`, `custom` |
| `date_from` | date | — | Required when `period=custom` |
| `date_to` | date | — | Required when `period=custom` |
| `group_by` | enum | varies | `day`, `week`, `month`, `package`, `router`, etc. |
| `router_id` | int | — | Optional filter (network reports) |
| `package_id` | int | — | Optional filter (usage/subscription) |
| `customer_group_id` | int | — | Optional CRM filter |
| `refresh` | bool | false | Bypass cache when true |

### 4.2 Report Detail Specifications

#### Revenue (`report_revenue`)

**Data sources:** `vw_revenue_by_period`, `revenue_aggregates_daily`

**Charts:**
- Line: net revenue over time
- Doughnut: revenue by payment method (M-Pesa, cash, bank)

**Table columns:** Period, gross revenue, net revenue, tax, refunds, payment count

**Cache TTL:** 4 hours

---

#### Collections (`report_collections`)

**Data sources:** `payments`, `payment_allocations`, `invoices`, `revenue_aggregates_daily`

**Charts:**
- Stacked bar: collected amount vs outstanding balance per period

**KPIs:** Collection rate %, total collected MTD, outstanding AR

**Cache TTL:** 2 hours

---

#### AR Aging (`report_aging`)

**Data sources:** `invoices`, `vw_customer_balances`

**Buckets:**

| Bucket | Condition |
|--------|-----------|
| Current | `due_date >= CURDATE()` |
| 1-30 days | `DATEDIFF(CURDATE(), due_date)` 1–30 |
| 31-60 days | 31–60 |
| 61-90 days | 61–90 |
| 90+ days | > 90 |

**Charts:** Horizontal bar by bucket; DataTable of top debtors

**Cache TTL:** 1 hour

---

#### Usage (`report_usage`)

**Data sources:** `usage_aggregates_daily`, `usage_aggregates_monthly`

**Charts:**
- Bar: total GB in/out by day
- Top 10 customers table by usage

**Cache TTL:** 4 hours

---

#### PPPoE (`report_pppoe`)

**Data sources:** `pppoe_sessions`, `radacct`, `vw_active_pppoe_sessions`

**Charts:**
- Line: concurrent sessions over time
- Table: top users by bytes

**Cache TTL:** 30 minutes (more volatile)

---

#### Hotspot (`report_hotspot`)

**Data sources:** `hotspot_sessions`, `hotspot_usage`

**Metrics:** Unique MACs, session duration avg, bytes per user

**Cache TTL:** 30 minutes

---

#### Network (`report_network`)

**Data sources:** `network_metrics_hourly`

**Charts:**
- Dual-axis line: bandwidth in/out (bps)
- Line: avg latency, packet loss %

**Cache TTL:** 1 hour

---

#### Routers (`report_routers`)

**Data sources:** `vw_router_health`, `router_metrics`

**Charts:**
- Gauge: fleet online percentage
- Table: per-router CPU, memory, sessions, last check

**Cache TTL:** 15 minutes

---

#### Customers (`report_customers`)

**Data sources:** `customer_metrics_daily`

**Charts:**
- Line: active customers, new signups, churn
- KPI card: ARPU (average revenue per user)

**Cache TTL:** 4 hours

---

#### Subscriptions (`report_subscriptions`)

**Data sources:** `customer_subscriptions`, `packages`

**Charts:**
- Doughnut: subscriptions by package
- Table: expiring within 30 days

**Cache TTL:** 2 hours

---

#### Vouchers (`report_vouchers`)

**Data sources:** `vw_voucher_inventory`, `vouchers`

**Charts:**
- Bar: sold vs used vs expired per batch
- Utilization rate %

**Cache TTL:** 4 hours

---

#### Tax (`report_tax`)

**Data sources:** `invoices`, `invoice_items`, `tax_rates`

**Output:** Tax collected by `tax_code`, taxable base, effective rate

**Cache TTL:** 4 hours

---

#### Tickets (`report_tickets`)

**Data sources:** `tickets`, `ticket_statuses`, `ticket_categories`

**Metrics:** Open count, avg first response time, avg resolution time, SLA breach %

**Cache TTL:** 1 hour

---

## 5. Class: `ReportEngine`

**File:** `includes/ReportEngine.php`

### 5.1 Constructor

```php
class ReportEngine
{
    private PDO $pdo;
    private int $tenant_id;

    public function __construct(PDO $pdo, int $tenant_id);

    /**
     * Factory — resolve tenant from tenant_context().
     */
    public static function forTenant(PDO $pdo): self;
}
```

### 5.2 Core Methods

```php
/**
 * Run a built-in report by code.
 * Returns structured array: summary, chart, table, meta.
 */
public function run(string $report_code, array $parameters, bool $force_refresh = false): array;

/**
 * Get cached result or null if expired/missing.
 */
public function getCached(string $report_code, array $parameters): ?array;

/**
 * Store result in report_cache.
 */
public function setCache(string $report_code, array $parameters, array $result, int $ttl_seconds): void;

/**
 * Queue export job.
 */
public function queueExport(
    string $report_code,
    array $parameters,
    string $format,  // csv|xlsx|pdf|json
    int $requested_by
): int;  // export_id

/**
 * Generate export file synchronously (small reports).
 */
public function exportSync(string $report_code, array $parameters, string $format): string;  // file path

/**
 * Execute custom report from report_definitions.
 */
public function runCustom(int $report_id, array $parameters): array;
```

### 5.3 Report Runners (Internal)

Each built-in report has a dedicated runner method:

```php
private function runRevenue(array $params): array;
private function runCollections(array $params): array;
private function runAging(array $params): array;
private function runUsage(array $params): array;
private function runPppoe(array $params): array;
private function runHotspot(array $params): array;
private function runNetwork(array $params): array;
private function runRouters(array $params): array;
private function runCustomers(array $params): array;
private function runSubscriptions(array $params): array;
private function runVouchers(array $params): array;
private function runTax(array $params): array;
private function runTickets(array $params): array;
```

Runners return a standard envelope:

```php
[
    'summary' => [
        ['label' => 'Net Revenue', 'value' => 1250000, 'format' => 'currency'],
        ['label' => 'Payments', 'value' => 342, 'format' => 'number'],
    ],
    'charts' => [
        [
            'id' => 'revenue_trend',
            'type' => 'line',
            'labels' => ['2026-01', '2026-02', ...],
            'datasets' => [
                ['label' => 'Net Revenue', 'data' => [...], 'color' => '#2563eb'],
            ],
        ],
    ],
    'table' => [
        'columns' => ['Period', 'Gross', 'Net', 'Tax', 'Count'],
        'rows' => [...],
    ],
    'meta' => [
        'report_code' => 'revenue',
        'generated_at' => '2026-06-13T10:00:00',
        'from_cache' => false,
        'row_count' => 31,
        'period' => $params,
    ],
]
```

### 5.4 Cache Key Generation

```php
private function cacheKey(string $report_code, array $parameters): string
{
    $normalized = $this->normalizeParameters($parameters);
    $hash = hash('sha256', json_encode($normalized));
    return "rpt_{$this->tenant_id}_{$report_code}_{$hash}";
}
```

Stored in `report_cache`:
- `cache_key` — full key string
- `parameters_hash` — SHA256 of normalized params
- `result_json` — full envelope
- `expires_at` — `NOW() + TTL`

---

## 6. Cache Strategy

### 6.1 TTL by Report Volatility

| Tier | TTL | Reports |
|------|-----|---------|
| Realtime | 15 min | `routers`, `pppoe`, `hotspot` |
| Operational | 1 hour | `aging`, `network`, `tickets` |
| Financial | 2–4 hours | `revenue`, `collections`, `tax`, `customers`, `usage`, `vouchers`, `subscriptions` |

### 6.2 Cache Invalidation

| Trigger | Action |
|---------|--------|
| `refresh=1` query param | Skip cache read; overwrite on run |
| Payment recorded | Invalidate `revenue`, `collections` for tenant |
| Invoice issued | Invalidate `aging`, `collections`, `tax` |
| Cron `report_cache.php` | Warm high-traffic reports for all active tenants |
| Manual purge | Admin setting or `DELETE FROM report_cache WHERE tenant_id = ?` |

### 6.3 Cron: `cron/report_cache.php`

**Schedule:** `0 */4 * * *` (every 4 hours)

```php
// Pseudocode
foreach (active_tenants() as $tenant_id) {
    $engine = new ReportEngine($pdo, $tenant_id);
    foreach (['revenue', 'collections', 'customers', 'usage'] as $code) {
        $engine->run($code, ['period' => 'month'], false);
    }
}
```

### 6.4 Cron: `cron/dashboard_metrics.php`

**Schedule:** `*/15 * * * *`

Populates `dashboard_metrics` from live counts — feeds `dashboard.php` and report summary cards.

### 6.5 Cron: Revenue Aggregation (recommended)

**Schedule:** `0 2 * * *` (nightly)

Roll up previous day into `revenue_aggregates_daily` from `payments` + `invoices` — reduces report query cost.

---

## 7. Export Engine

### 7.1 Formats

| Format | Library | MIME |
|--------|---------|------|
| CSV | Native PHP `fputcsv` | `text/csv` |
| XLSX | PHPSpreadsheet (`vendor/`) | `application/vnd.openxmlformats-officedocument.spreadsheetml.sheet` |
| PDF | TCPDF or Dompdf (Wave 5) | `application/pdf` |
| JSON | `json_encode` | `application/json` |

### 7.2 Export Flow

```mermaid
sequenceDiagram
    participant U as Finance User
    participant UI as report_revenue.php
    participant AJAX as export_report.php
    participant RE as ReportEngine
    participant DB as report_exports

    U->>UI: Click Export XLSX
    UI->>AJAX: report_code + params + format
    AJAX->>RE: run() or getCached()
    alt Row count > 10000
        RE->>DB: INSERT report_exports queued
        RE-->>U: "Export queued — email when ready"
    else Small report
        RE->>RE: exportSync()
        RE-->>U: File download
    end
```

Large exports stored in `/uploads/reports/{tenant_id}/` with `expires_at` (7 days).

### 7.3 `ajax/export_report.php`

```php
permission_check('reports.export');
$tenant_id = require_tenant_context();
$report_code = $_POST['report_code'];
$format = $_POST['format'];  // csv|xlsx|pdf
$params = $_POST['parameters'] ?? [];

$engine = ReportEngine::forTenant($pdo);
$export_id = $engine->queueExport($report_code, $params, $format, getCurrentUserId());
```

---

## 8. Report Builder

**Page:** `report_builder.php`  
**Permission:** `reports.export` (or `reports.builder`)

### 8.1 Custom Report Definition

`report_definitions` for tenant-created reports:

```json
{
  "report_code": "custom_top_payers",
  "report_name": "Top 50 Payers",
  "report_category": "billing",
  "parameters_json": {
    "allowed_periods": ["month", "quarter", "year"],
    "filters": ["customer_group_id"]
  },
  "query_template": "SELECT ... FROM payments WHERE tenant_id = :tenant_id AND ...",
  "output_formats": ["screen", "csv", "xlsx"]
}
```

### 8.2 Builder UI

| Panel | Content |
|-------|---------|
| Left | Data source picker (whitelisted tables/views) |
| Center | Column selector, filters, grouping |
| Right | Preview table + save |

**Security:** Custom SQL is not free-form. Builder generates parameterized queries from a **whitelist**:

- `payments`, `invoices`, `customers`, `customer_subscriptions`
- `pppoe_sessions`, `hotspot_sessions`, `tickets`, `vouchers`
- All `vw_*` views and `*_aggregates_*` tables

`query_template` stored with bound parameter placeholders only — no string concatenation of user input.

### 8.3 Save Definition

`ajax/save_report_definition.php` validates:
- Unique `report_code` per tenant
- Whitelisted tables in generated SQL
- `permission_check('reports.export')`

---

## 9. Scheduled Reports

**Page:** `report_schedules.php`  
**Permission:** `reports.export` + `reports.schedule`

### 9.1 Schedule Record

```sql
INSERT INTO report_schedules (
    tenant_id, report_id, schedule_name, cron_expression,
    parameters_json, output_format, recipients_json, next_run_at
) VALUES (...);
```

**Example `recipients_json`:**

```json
{
  "emails": ["finance@acmeisp.co.ke", "admin@acmeisp.co.ke"],
  "attach_format": "xlsx"
}
```

### 9.2 Cron: Schedule Processor

**Script:** `cron/report_schedule_processor.php`  
**Schedule:** `*/5 * * * *`

```php
SELECT * FROM report_schedules
WHERE is_active = 1 AND next_run_at <= NOW();

foreach ($schedule as $s) {
    $engine->queueExport($report_code, $params, $s->output_format, null);
    // Email file via NotificationService
    // UPDATE last_run_at, next_run_at from cron_expression
}
```

### 9.3 Common Schedules

| Schedule | Cron | Use |
|----------|------|-----|
| Daily revenue | `0 6 * * *` | Morning finance email |
| Weekly collections | `0 7 * * 1` | Monday AR review |
| Monthly tax | `0 8 1 * *` | Tax filing support |

---

## 10. Admin UI Pattern

All `report_*.php` pages follow the admin shell:

```php
<?php
require_once __DIR__ . '/includes/config.php';
require_once __DIR__ . '/includes/db.php';
require_once __DIR__ . '/includes/auth.php';
require_once __DIR__ . '/includes/tenant.php';

requireLogin();
require_permission('reports.view');

$page_title = 'Revenue Report';
$report_code = 'revenue';

include __DIR__ . '/layouts/app-start.php';
?>

<!-- Filter bar: period, date range, group_by, Run, Export -->
<div class="report-filters card mb-3">...</div>

<!-- KPI summary cards -->
<div class="row g-3 mb-4" id="report-summary"></div>

<!-- Charts -->
<div class="row g-3 mb-4">
    <div class="col-lg-8"><div class="chart-card"><canvas id="chartPrimary"></canvas></div></div>
    <div class="col-lg-4"><div class="chart-card"><canvas id="chartSecondary"></canvas></div></div>
</div>

<!-- DataTable -->
<div class="card">
    <table id="reportTable" class="table table-striped"></table>
</div>

<script>
fetch('ajax/report_revenue_data.php?' + new URLSearchParams(filters))
    .then(r => r.json())
    .then(renderReport);
</script>

<?php include __DIR__ . '/layouts/app-end.php'; ?>
```

### 10.1 Shared Report CSS

Uses ISP360 design tokens from `assets/css/isp360-theme.css`:
- `.chart-card` — same as `dashboard.php`
- `.report-filters` — compact filter bar
- `.kpi-card` — summary metrics

### 10.2 Chart.js Conventions

| Report | Primary chart | Secondary |
|--------|---------------|-----------|
| Revenue | Line (trend) | Doughnut (by method) |
| Collections | Stacked bar | — |
| Aging | Horizontal bar (buckets) | — |
| Usage | Bar (GB/day) | — |
| Customers | Line (active/new) | — |
| Subscriptions | Doughnut (by package) | — |

Colors: ISP360 palette `#2563eb`, `#4f46e5`, `#0ea5e9`, `#10b981`, `#f59e0b`.

---

## 11. AJAX Endpoints

| Endpoint | Permission | Returns |
|----------|------------|---------|
| `ajax/report_revenue_data.php` | `reports.view` | Revenue envelope JSON |
| `ajax/report_collections_data.php` | `reports.view` | Collections envelope |
| `ajax/report_aging_data.php` | `reports.view` | Aging envelope |
| `ajax/report_usage_data.php` | `reports.view` | Usage envelope |
| `ajax/report_pppoe_data.php` | `reports.view` | PPPoE envelope |
| `ajax/report_hotspot_data.php` | `reports.view` | Hotspot envelope |
| `ajax/report_network_data.php` | `reports.view` | Network envelope |
| `ajax/report_routers_data.php` | `reports.view` | Routers envelope |
| `ajax/report_customers_data.php` | `reports.view` | Customers envelope |
| `ajax/report_subscriptions_data.php` | `reports.view` | Subscriptions envelope |
| `ajax/report_vouchers_data.php` | `reports.view` | Vouchers envelope |
| `ajax/report_tax_data.php` | `reports.view` | Tax envelope |
| `ajax/report_tickets_data.php` | `reports.view` | Tickets envelope |
| `ajax/export_report.php` | `reports.export` | File download or queue ID |
| `ajax/save_report_definition.php` | `reports.export` | Saved report_id |
| `ajax/save_report_schedule.php` | `reports.schedule` | Saved schedule_id |

**Standard AJAX response:**

```json
{
  "success": true,
  "data": { "summary": [], "charts": [], "table": {}, "meta": {} }
}
```

---

## 12. RBAC Permissions

| Code | Action |
|------|--------|
| `reports.view` | View all built-in reports on screen |
| `reports.export` | CSV/XLSX/PDF export |
| `reports.schedule` | Create/edit scheduled reports |
| `reports.builder` | Custom report builder (subset of export) |

**Page mapping:**

| Page | Permission |
|------|------------|
| All `report_*.php` except builder/schedules | `reports.view` |
| `report_builder.php` | `reports.export` |
| `report_schedules.php` | `reports.export` + `reports.schedule` |
| `ajax/export_report.php` | `reports.export` |

---

## 13. Security

| Risk | Mitigation |
|------|------------|
| Cross-tenant data leak | `tenant_id` on every query; `require_tenant_context()` |
| SQL injection in builder | Whitelist tables; parameterized queries only |
| Export path traversal | Files under `/uploads/reports/{tenant_id}/` only |
| Large query DoS | Row limit 50,000 per export; timeout 60s |
| PII in scheduled email | Recipients limited to tenant staff emails |
| Cache poisoning | Cache key includes tenant_id + params hash |

### 13.1 Super Admin

Super Admin with impersonation sees reports for `impersonate_tenant_id` only — never cross-tenant aggregate in UI (platform-wide reports are a separate future module).

---

## 14. Seed Data: System Report Definitions

Inserted at install for each tenant (or platform template copied on tenant provision):

| report_code | report_name | category |
|-------------|-------------|----------|
| `revenue` | Revenue Report | billing |
| `collections` | Collections Report | billing |
| `aging` | AR Aging Report | billing |
| `tax` | Tax Report | billing |
| `usage` | Data Usage Report | network |
| `pppoe` | PPPoE Sessions Report | network |
| `hotspot` | Hotspot Usage Report | network |
| `network` | Network Performance | network |
| `routers` | Router Health Report | network |
| `customers` | Customer Growth Report | crm |
| `subscriptions` | Subscription Report | crm |
| `vouchers` | Voucher Utilization | ops |
| `tickets` | Support Ticket Report | ops |

All seeded with `is_system = 1` — not deletable, but parameters customizable per tenant.

---

## 15. Menu Integration

Add to `includes/menu_config.php` under Reports header:

```php
['type' => 'header', 'label' => 'Reports'],
['type' => 'dropdown', 'label' => 'Reports', 'icon' => 'ri-bar-chart-box-line',
 'permission' => 'reports.view', 'items' => [
    ['label' => 'Revenue', 'url' => 'report_revenue.php', 'permission' => 'reports.view'],
    ['label' => 'Collections', 'url' => 'report_collections.php', 'permission' => 'reports.view'],
    ['label' => 'AR Aging', 'url' => 'report_aging.php', 'permission' => 'reports.view'],
    ['label' => 'Usage', 'url' => 'report_usage.php', 'permission' => 'reports.view'],
    ['label' => 'Customers', 'url' => 'report_customers.php', 'permission' => 'reports.view'],
    ['label' => 'Network', 'url' => 'report_network.php', 'permission' => 'reports.view'],
    ['label' => 'Report Builder', 'url' => 'report_builder.php', 'permission' => 'reports.export'],
    ['label' => 'Schedules', 'url' => 'report_schedules.php', 'permission' => 'reports.export'],
]],
```

---

## 16. Error Handling

| Error | HTTP | User Message |
|-------|------|--------------|
| No data in range | 200 | Empty state: "No data for selected period" |
| Invalid date range | 400 | "End date must be after start date" |
| Export too large | 202 | "Export queued — you will receive an email" |
| Cache DB error | 500 | Run report live; log warning |
| Unknown report code | 404 | "Report not found" |

---

## 17. Implementation Checklist (Wave 5)

- [ ] `includes/ReportEngine.php` with all 13 runners
- [ ] 14 `report_*.php` root pages
- [ ] 13 `ajax/report_*_data.php` endpoints
- [ ] `ajax/export_report.php`, `save_report_definition.php`, `save_report_schedule.php`
- [ ] Cron: `report_cache.php`, `report_schedule_processor.php`
- [ ] Nightly revenue/usage aggregation cron
- [ ] Seed `report_definitions` for system reports
- [ ] PHPSpreadsheet CSV/XLSX export
- [ ] PDF export (basic tabular)
- [ ] Menu entries in `menu_config.php`
- [ ] Integration tests: revenue report with cache hit/miss
- [ ] Scheduled report email via `NotificationService`

---

## 18. Related Documents

| Document | Relevance |
|----------|-----------|
| [UI_SPECIFICATIONS.md](UI_SPECIFICATIONS.md) | Per-report filter fields and chart types |
| [RBAC_SPEC.md](RBAC_SPEC.md) | `reports.*` permissions |
| [PROJECT_STRUCTURE.md](PROJECT_STRUCTURE.md) | File manifest |
| [UI_SHELL_PATTERN.md](UI_SHELL_PATTERN.md) | Admin page bootstrap |
| [PAYMENTS_SPEC.md](PAYMENTS_SPEC.md) | Revenue/collections data sources |
| [DATABASE_DESIGN.md](DATABASE_DESIGN.md) | Aggregate tables index |
