# Reporting & analytics (M5)

## Principles

- A value is shown only if a platform API returned it. Each value carries its **source** (the API field) and **fetched_at**, shown as a tooltip on every number.
- If a platform does not offer a metric, it is shown as **Not available**. If the platform offers it but no value has been fetched for the period, it is shown as **No data yet**. Neither state is ever shown as `0`.
- Every sync attempt writes a `metric_sync_runs` row, including skipped and failed runs, so the UI can explain why data is missing or old.

## Data model (tenant DB)

| Table | Purpose |
|---|---|
| `metric_sync_runs` | One row per account sync: trigger, status (`running/succeeded/partial/failed/skipped`), window, counts, metrics the platform does not offer, error code and message |
| `social_metrics` | Account metrics per platform day (`daily`) or per fetch day (`counter`, e.g. followers). Unique on account + metric + date |
| `post_metrics` | Latest lifetime value per published destination (`post_targets`). Unique on target + metric |
| `post_targets.metrics_synced_at / external_deleted_at` | Post sync bookkeeping; deleted posts keep their last values |
| `analytics_snapshots` | The frozen payload behind a generated report |
| `report_templates` / `report_exports` | Templates (manual, weekly or monthly) and generated files with a delivery log |

Metric definitions live in `config/metrics.php`. Adapters declare which metrics they read, and from which API field, in `ReadsInsights::insightMetrics()`. `MetricCatalog` joins the two.

## Sync

- `metrics:sync` (hourly) queues `SyncAccountMetrics` on the `sync` queue for active accounts not synced in the last `METRICS_SYNC_INTERVAL_MINUTES`. Jobs are spread `METRICS_SYNC_STAGGER_SECONDS` apart per tenant. Options: `--tenant`, `--force`, `--backfill`.
- The first sync of an account backfills `METRICS_BACKFILL_DAYS`. Later syncs re-fetch the last `METRICS_RESTATE_DAYS`, because platforms revise recent days. Upserts make this safe to repeat.
- Posts are refreshed for `METRICS_POST_WINDOW_DAYS` after publishing. When the platform reports a post as deleted (Graph 404 or error 100, or a YouTube video missing from the response), `external_deleted_at` is set and its last values are kept.
- Account metrics and post metrics are fetched separately. If one fails, the run is `partial`; if both fail, it is `failed`.
- The `read_insights` capability gates the sync. It requires the capability data (e.g. X is `configurable` because it depends on the paid API tier), integration readiness, the tenant feature flag, a healthy account, the required scopes, **and** an adapter that reads at least one metric. If any of these is missing, the run is `skipped` and records the reason.
- "Refresh now" on a dashboard queues a manual sync, limited to one per account every 15 minutes.
- `metrics:prune` (daily) removes account values older than `METRICS_RETENTION_DAYS` and sync runs older than 90 days.

### What each connector reads today

| Platform | Account | Post |
|---|---|---|
| Facebook Page | followers (`Page.followers_count`) | reactions, comments, shares (object fields) |
| Instagram professional | followers (`IGUser.followers_count`) | likes, comments (`IGMedia` fields; likes are omitted when the owner hides them) |
| YouTube | subscribers (`channel.statistics`; omitted when hidden) | views, likes, comments (`video.statistics`) |
| Others | `read_insights` shows as *configurable* until a connector is implemented | |

Insights-endpoint metrics (impressions, reach, views) change between Graph versions. They will be added once verified against the app's Graph version, not guessed. The sandbox provider exercises every metric, including realistic "Not available" gaps.

## Dashboards

`/analytics` (agency overview) → `/analytics/clients/{client}` (totals, daily chart, accounts with freshness and refresh, post performance, campaign comparison) → `/analytics/accounts/{account}` (metrics with source, sync history). `/analytics/metrics` lists definitions and which API field each platform's value comes from.

Date ranges (`last_7_days`, `last_30_days`, `last_month`, `this_month`, custom ≤ 1 year) are evaluated in the **client's timezone**. Posts are filtered by the UTC bounds of those local days. Daily account metrics use the platform's own day, as reported.

Client totals add up only the accounts that report a metric, with a note such as "Reported for 1 of 3 accounts". Engagements = likes + comments + shares + saves, using only the parts the platform reports; any missing parts are listed.

Freshness: *Up to date* (≤ `METRICS_STALE_AFTER_HOURS`), *Stale*, *Last sync had errors* (failed or partial), *Insights not available* (skipped), *Not synced yet*.

## Reports

- Templates per client: sections (summary, accounts, top posts, campaigns, data freshness), period, format, and optional weekly or monthly delivery at a local time to up to 10 recipients.
- Generation (`GenerateReport` job): `ReportBuilder` freezes the data into an `analytics_snapshots` payload, then `ReportRenderer` prints it. As a result PDF and XLSX always match, and a re-download is the same file that was sent.
- **PDF: DomPDF** (spike decision). Reports are tables and text, so a headless Chromium on workers is not worth the dependency and attack surface. Remote resources and PHP are disabled. Branding is the agency name, an accent colour and a footer (Settings → Report branding).
- **XLSX: OpenSpout**, one sheet per section. "Not available" and "No data yet" stay as text.
- Files are stored on the tenant disk under `reports/`. `reports:prune` deletes them after `REPORTS_RETENTION_DAYS` and marks the export `expired`.
- Downloads use `central.reports.download` (`/r/{tenant}/{export}`), a signed, expiring URL with the tenant in the signature and no session required. In-app links are minted per click after a `reports.export` check (15 minutes). E-mailed links last `REPORTS_EMAIL_LINK_TTL_DAYS`. Every download is counted and audited.
- `reports:run-scheduled` (every 15 minutes) moves `next_run_at` forward *before* queueing the report, so a crash cannot send it twice. The next run is computed in the template's timezone (DST-safe; day 29–31 falls back to the end of shorter months). Each recipient's delivery outcome is logged on the export.
- Client users see ready reports for their own clients at `/portal/reports`.