# ATIME CDR Reporting Tool

Repeatable, monthly call-activity reporting for **A TIME** (atime.org), built from
NetSapiens CDR data. Each month it pulls new CDRs, keeps a rolling 18 months in
SQLite, and emails an **A TIME-branded** **per-region** report (HTML + Excel) to
each region's recipients. Regions: **Cleveland, Florida, New York City**.

Runs on `ns-scripts.pressone.net` at `/opt/ns-scripts/atime-reports/`.

## Modules

```
config.py       API host (api.cloudsession.io), regions, division maps, crosswalk,
                branding, email + secrets loader (env -> secrets.json)
mapping.py      UserListWithReportingDept.xlsx -> queue_map + direct_map (+ ext names);
                live NS ext->department cache -> live_map (new users/queues)
pull.py         NetSapiens API -> SQLite (seed / range / incremental / prune_older_than);
                pull_departments() -> data/atime_departments.json live cache
analyze.py      classify every call -> JSON metrics blob (org-wide + per-region)
render_html.py  metrics + region -> self-contained branded HTML report
render_xlsx.py  metrics + region -> 14-tab Excel workbook
email_send.py   Postmark / Resend transactional send (dry-run writes .eml previews)
report.py       CLI / cron entry point
verify.py       independent row-wise recompute that cross-checks analyze.py
deploy/         systemd service + timer units
```

## What each monthly run does (`report.py --monthly`, fires the 1st, 06:00)

1. **Refresh the live department cache** (`pull_departments`) so new users/queues
   classify through the department → Reporting Division matrix immediately.
2. **Incremental pull** from `api.cloudsession.io` — everything newer than the DB's
   max timestamp (upsert by CDR id, so re-pulls never duplicate).
3. **Prune** CDRs older than 18 months (`RETENTION_MONTHS`).
4. **Analyze + email two reports** (per region, to that region's recipients):
   - the **previous month** ("June 2026") — no month-by-month trend section, since
     a single month has nothing to trend;
   - the **year-to-date** (Jan 1 → end of previous month). On **Jan 1** this window
     is the entire prior year, so it is sent once as the **"<year> Annual"** report
     (a separate YTD email would be an identical date range).

## Secrets — never commit these

Create `/opt/ns-scripts/atime-reports/secrets.json` (gitignored) from
`secrets.example.json`. It holds the NS token, the email API key, the from/reply-to
addresses, and the per-region recipient lists. Any key can also be supplied as an
env var (env wins). The mapping workbook (`data/UserListWithReportingDept.xlsx`) and
the SQLite DB are also gitignored — the workbook contains staff PII (names, emails,
voicemail PINs).

## First-time setup on the server

```bash
# 1. deploy code (git discipline: edit local -> commit -> push -> pull on server)
cd /opt/ns-scripts && git pull
cd /opt/ns-scripts/atime-reports

# 2. venv + deps
python3 -m venv venv
./venv/bin/pip install -r requirements.txt

# 3. secrets + mapping workbook (copied manually, never via git)
cp secrets.example.json secrets.json && $EDITOR secrets.json
scp UserListWithReportingDept.xlsx root@ns-scripts.pressone.net:/opt/ns-scripts/atime-reports/data/

# 4. seed history (one-time; from an existing export) OR pull a date range
./venv/bin/python report.py --seed /path/to/ATIME_CDR_2025_full.csv.gz
./venv/bin/python pull.py range "2025-01-01T00:00:00-05:00" "2026-12-31T23:59:59-05:00"

# 5. install the monthly timer
cp deploy/ns-atime-reports.{service,timer} /etc/systemd/system/
systemctl daemon-reload
systemctl enable --now ns-atime-reports.timer
systemctl list-timers ns-atime-reports.timer        # confirm next run
```

Cron alternative (instead of the timer):
```
0 6 1 * *  cd /opt/ns-scripts/atime-reports && ./venv/bin/python report.py --monthly
```

## Manual / ad-hoc

```bash
./venv/bin/python report.py --year 2025                  # all 3 regions, no email
./venv/bin/python report.py --ytd --region cleveland     # one region
./venv/bin/python report.py --pull --ytd --email         # refresh, report, email
ATIME_EMAIL_DRY_RUN=1 ./venv/bin/python report.py --monthly   # full flow, write .eml previews
./venv/bin/python verify.py 2026                         # independent number cross-check
```

Test a deploy run without sending: set `ATIME_EMAIL_DRY_RUN=1` (or leave the email
API key blank) — emails are written as `output/PREVIEW_*.eml` instead of sent.

## Key data conventions

- **PAGE_SIZE = 100** (mandatory). Larger pages silently strip ~60 fields.
- **Division assignment**: the **department → Reporting Division matrix**
  (`config.DEPT_CROSSWALK`, authoritative per A TIME, 2026-07) maps every NS
  department to exactly one Reporting Division, so a new user inherits a division
  through their department. Priority: queue/AA extension → direct user (department
  via matrix, else the legacy xlsx "Reporting Department" column) → CDR department
  text via the same matrix. All divisions in the matrix are reported (there is no
  excluded back-office bucket anymore).
- **Voicemail**: `call-term-to-uri` ~ `VMail` OR `call-term-match-uri` ~ `record-vmail`.
- **Caller geography**: `call-orig-from-uri` (`sip:+1?(\d{10})@`), ~97–98% of inbound.
- **Excluded from division metrics**: internal (dir 3), unmapped records, and the main
  AA IVR greeting leg (ext 998).
- **Region scope (Scope A)**: regional sections count *all* real inbound from a region,
  excluding only the ext-998 IVR double-count leg. Per-division tables stay division-scoped.
- **Regions** are defined in `config.REGIONS` — add one by adding area codes there.
  - Cleveland–Elyria MSA = 216 + 440 (Akron–Canton 330/234 excluded).
  - Florida = statewide FL area codes.
  - NYC = five boroughs (212/646/332/718/347/929/917).
- **Overall summary**: alongside answered/talk-time/unique-callers, the hero shows
  a combined **"Overall interactions (in + out)"** stat and **inbound overnight as
  a raw count** (10p–6a), with the percentage kept as a caption rather than the
  headline number.
- **Totals by division**: a compact table (Total In / Total Out / Grand Total) sits
  ahead of the detailed inbound/outbound division tables so those two numbers don't
  require flipping between tables; `Total In + Total Out == Grand Total` always
  holds, and `Total In` is the same figure as `Attempts` in the detailed table.
- **Medical Helpline case-manager hours** use 1-decimal precision (`fmt1` in
  render_html.py), not the integer rounding used for call counts — a case manager
  with e.g. 0.5 inbound hours previously rendered as "0h".

## Branding

Colors, font, and the org logo are pulled from **atime.org**'s live theme CSS (see
`config.BRAND` for full provenance notes) — not the old PressONE purple/orange
scheme. Two deliberate departures from a literal 1:1 copy, both noted inline in
`config.py`:
- **Font**: their heading face (P22MackinacPro) is a paid, licensed webfont we
  don't have rights to embed; **Inter** is used throughout instead (the free,
  open-license sibling of their actual body font, InterDisplay).
- **Colors**: their real palette is pastel-only (meant for large background
  fills, not text) and too low-contrast for report data-ink; the accent colors
  are the same hue, deepened for legibility. Their real heading/text color
  (`--color-grey-900` `#1A242A`) is used as-is.

The logo in the report header is spliced in from atime.org's actual
`main-logo.svg` asset (`_LOGO_SVG` in render_html.py) — if A TIME's real logo or
palette changes, re-pull both from the live site rather than hand-editing hex
values, so provenance stays accurate.