# CLAUDE.md — Covers Report screen (and the cover-tracking subsystem it anchors)

Agent-oriented reference. For a narrative / onboarding doc see
[`README.md`](./README.md) in the same folder.

This screen is the **anchor of the cover-tracking subsystem**. Although the
screen lives in `app/pages/caster/covers-report/`, the subsystem spans
multiple folders — front-end, components, DB tables, stored procedures,
a SQL Agent job, and a linked server. This document lists every piece
and the invariants that hold them together.

---

## Subsystem inventory

### Front-end (IHBox SPA)

| Path | Role |
|---|---|
| `app/pages/caster/covers-report/covers-report.js` | The screen. Two AG Grids (history + realtime), assign/unassign actions, 30 s realtime tick. |
| `app/pages/caster/covers-report/covers-report.aspx` | ASPX template (Bootstrap 2.x scaffolding, i18n placeholders, date pickers, refresh button). |
| `app/pages/caster/covers-report/covers-report.sql` | One-time deploy script: roles (`cr_read`, `cr_write`), `FrontEnd.Routes`/`Menu` insert, SP-secured-call extended properties. |
| `app/pages/caster/covers-temperatures/` | Sister screen: pivoted, auto-refresh, read-only temperatures monitor. |
| `app/components/modal-covers/modal-assign-cover.js` | Assign / Unassign / Edit-Alert modal. Calls `CAS.UpdateCoverHeat`. |
| `app/components/modal-temperatures-history/` | Per-cover chart modal opened from the realtime grid's 📈 icon. Calls `CAS.GetTemperaturesByCover`. |

### Database (mes-db, schema `CAS`)

| Path | Role |
|---|---|
| `mes-db/CAS/Tables/CoverSensorMap.sql` | Maps remote `DeviceId` (BIGINT) → `(CoverNumber, SensorSlot, DisplayName)`. Active flag for soft-disable. |
| `mes-db/CAS/Tables/CoverTemps.sql` | Time-series historian. Clustered PK on `(CoverNumber, Timestamp)`. |
| `mes-db/CAS/Tables/CoverHeats.sql` | Current state: one row per (cover, assignment-slot). HeatName NULL = placeholder. |
| `mes-db/CAS/Tables/CoverHeatsEvents.sql` | Append-only audit log. Identity PK + indexes on (HeatName, Datetime) and (CoverNumber, Datetime). |
| `mes-db/CAS/Stored Procedures/Job_TrackCoverTemps.sql` | The SQL Agent job. Pulls from Covers DB, populates `CoverTemps`, maintains `IsSensorLive`. |
| `mes-db/CAS/Stored Procedures/UpdateCoverHeat.sql` | Assign (`A`) / Unassign (`U`) / Edit-Alert (`E`). Transactional. UTC conversion via `@timeZoneCode`. |
| `mes-db/CAS/Stored Procedures/GetCoversReport.sql` | Two modes via `@realTime` BIT. Feeds the two grids on this screen. |
| `mes-db/CAS/Stored Procedures/GetAvailableCovers.sql` | Feeds the assign modal's two dropdowns. |
| `mes-db/CAS/Stored Procedures/GetLastCoverTemperatures.sql` | Long-format result; consumed by the covers-temperatures screen. |
| `mes-db/CAS/Stored Procedures/GetTemperaturesByCover.sql` | Per-cover last-200 readings for the chart modal. |

### Infrastructure

| | |
|---|---|
| **Linked server `[Leap_1]`** | Points at the SQL Server hosting the `Covers` database. On `SRD-MESDEV` Covers is on the same instance (loopback unused). On `SRD-MES` it's reached via this linked server using a fixed SQL login (e.g. `CoversTrackingUser`). See `Job_TrackCoverTemps.sql` for the routing logic. |
| **SQL Agent job** | `CAS.Job_TrackCoverTemps` — runs every 30 s. T-SQL step calls the proc with no parameters. |

---

## Data model

### `CAS.CoverSensorMap` — routing config

| Column | Type | Notes |
|---|---|---|
| `DeviceId` | `BIGINT` PK | The remote `Covers.dbo.Device.Id`. **MUST be BIGINT** — the gateway issues 19-digit IDs (`1453890822445…`) that overflow `INT`. |
| `CoverNumber` | `INT` | Internal cover identifier (1, 2, 3, …). Used as the join key with the other 3 tables. |
| `SensorSlot` | `INT` CHECK (1,2) | `1` writes to `CoverTemps.TempSensor1`, `2` writes to `TempSensor2`. |
| `DisplayName` | `VARCHAR(50)` | Per-sensor friendly name (`'Cover 3'` for slot 1, `'Cover 3B'` for slot 2). Slot 1's `DisplayName` is the cover-level name used elsewhere. |
| `Active` | `BIT` default 1 | Soft-disable a device without deleting the row. Job ignores `Active = 0`. |
| | | UNIQUE constraint on `(CoverNumber, SensorSlot)` — a slot can't be claimed twice. |

**Seeding the map is a manual step**, performed once per environment when
new devices appear. Discovery query:

```sql
SELECT * FROM OPENQUERY([Leap_1], '
    SELECT DISTINCT dev.Id AS DeviceId, dev.Name AS DeviceName
    FROM [Covers].dbo.Device dev
    ORDER BY dev.Id
');
```

Devices in `Covers.dbo.Device` but missing from `CoverSensorMap` get
counted in the job's log under `… N unmapped devices on the source`.

### `CAS.CoverTemps` — historian

```sql
CoverNumber INT, CoverName VARCHAR(20), [Timestamp] DATETIME,
[Status] CHAR(1), TempSensor1 REAL, TempSensor2 REAL, SequenceNumber INT
PRIMARY KEY CLUSTERED (CoverNumber, Timestamp)
```

- `[Timestamp]` is **UTC** (the gateway timestamps are passed through as-is).
- `[Status]` is `'G'` (good) or `'L'` (lost). The job currently only ever
  writes `'G'`; the `'L'` is conceptual / set by other systems.
- `CoverName` is the **cover-level** name, not per-sensor. Pulled from
  slot-1's `DisplayName` in the map, with `'Cover N'` fallback if slot 1
  isn't mapped.
- `SequenceNumber` is **unused** — dead column kept for backward compat,
  candidate for removal.
- Inserts are MERGE-style guarded by `NOT EXISTS` (and the PK enforces it).

### `CAS.CoverHeats` — current state

```sql
CoverId INT, HeatName VARCHAR(10) NULL,
isAvailable BIT DEFAULT 1, IsSensorLive BIT DEFAULT 1,
OutOfCoverAlertHours INT NULL
```

**This table mashes three concerns** and is intentional for now (would be
split into `Covers` + `CoverHeatAssignments` if/when it starts to hurt):

1. **Roster** — `HeatName IS NULL` rows are placeholders the job seeds when
   it first sees a new cover. They represent "this cover exists and is
   waiting for an assignment".
2. **Active assignments** — `HeatName IS NOT NULL` rows are current
   heat ↔ cover bindings.
3. **Capacity guard** — `isAvailable = 0` means the cover has the maximum
   number of concurrent heats (10 by default in `UpdateCoverHeat`).

**Flag ownership** (do not violate):

| Flag | Owner | What it means |
|---|---|---|
| `isAvailable` | `UpdateCoverHeat` ONLY | 1 = spare slots; 0 = at capacity. |
| `IsSensorLive` | `Job_TrackCoverTemps` ONLY | 1 = reading in last 30 min; 0 = stale. |

Available-for-assignment = `isAvailable = 1 AND IsSensorLive = 1` (this is
exactly the filter in `GetAvailableCovers`).

### `CAS.CoverHeatsEvents` — append-only audit

```sql
Id INT IDENTITY PK,
[Datetime] DATETIME DEFAULT GETUTCDATE(),  -- UTC
CoverNumber INT, HeatName VARCHAR(10),
[Description] VARCHAR(100),
[Action] CHAR(1),       -- 'A' Assign, 'U' Unassign, 'E' Edit-Alert
[CurrentUser] VARCHAR(100) NULL
```

Indexes: `(HeatName, Datetime DESC)` for per-heat scans (the dominant
read pattern in `GetCoversReport`), `(CoverNumber, Datetime DESC)` for
per-cover scans. Both `INCLUDE (HeatName/CoverNumber, Action)`.

**HARD RULE — do not mutate `[Datetime]` on existing rows.** The legacy
`UpdateCoverHeat` had a wildcard update at the end of every Assign that
rewrote `[Datetime]` on every event for any heat currently sitting on the
target cover, silently destroying the very history the screen displays.
That bug is fixed; do not reintroduce it.

---

## Stored procedures

All UTC convention per `mes-db/CLAUDE.md`. Each proc that displays times
to the UI accepts `@timeZoneCode CHAR(10)` and applies `IH.ToLocal` on
output; each proc that takes a local-time input applies `IH.ToUtc` before
storing.

### `CAS.Job_TrackCoverTemps()` — SQL Agent job

**Cadence:** 30 s (set in `msdb` when the Agent job was created; documented
in this README's parent context, not in code).

**Environment routing:** `IF DB_ID('Covers') IS NOT NULL` branches between
local cross-DB query (dev) and `OPENQUERY([Leap_1], …)` (prod). **Both
branches are wrapped in `sp_executesql`** so `CREATE PROCEDURE` does not
require either `[Covers]` to exist locally or `[Leap_1]` to be configured
at parse time — same proc file deploys to any server.

**Per-tick flow:**
1. Pull every device's latest reading to `#Remote`.
2. Filter to `rn = 1` per device and INNER JOIN `CoverSensorMap`
   (Active=1). Unmapped devices are dropped — they surface in the log.
3. Aggregate by `CoverNumber` into `#PerCover` (two sensors → one row).
4. `INSERT … WHERE NOT EXISTS` into `CoverTemps`.
5. `INSERT … WHERE NOT EXISTS` into `CoverHeats` for any cover seen for
   the first time (placeholder row, `HeatName = NULL`).
6. Maintain `IsSensorLive` via a single `UPDATE` with `CASE` — both
   directions in one statement (the original two-statement version broke
   because the CTE was out of scope for the second statement).
7. Log one line to `SYSTEM.LogMsg` with 5 counters:
   - source mode (`local` / `linked [Leap_1]`)
   - rows inserted into `CoverTemps`
   - new `CoverHeats` placeholder rows added
   - `IsSensorLive` flips this tick
   - unmapped devices on the source

### `CAS.UpdateCoverHeat(@CoverId, @HeatName, @action, @Started, @OutOfCoverAlertHours, @currentUser, @timeZoneCode)`

Called from `modal-assign-cover.js` for all three actions.

- **Transactional** via the savepoint pattern from `mes-db/CLAUDE.md`
  (nested-transaction-safe).
- `@Started` is **local time**; converted to UTC via
  `IH.ToUtc(@Started, @timeZoneCode)` before storage. When `@Started` is
  NULL the event uses `GETUTCDATE()` directly.
- Appends a row to `CoverHeatsEvents` including `@currentUser` in the new
  `CurrentUser` column.
- Calls `SYSTEM.AuditMsg` on success (TRY-wrapped so audit failures don't
  fail the proc).
- **Returns** `@result` (0 = success).

`@action` semantics:
- `'A'` — Assign. Reuses an existing `HeatName IS NULL` row on the cover
  if one exists, otherwise inserts. Capacity guard at 10 heats.
- `'U'` — Unassign. Sets `HeatName = NULL`, `isAvailable = 1`.
- `'E'` — Edit `OutOfCoverAlertHours` on an existing assignment. No state
  change to `HeatName` / `isAvailable`.

### `CAS.GetCoversReport(@start, @end, @realTime, @timeZoneCode)`

Two completely different result shapes gated by `@realTime`:

**`@realTime = 0`** — history mode, feeds the top grid.
- Aggregates `CoverHeatsEvents` by `HeatName`.
- Window: `@start..@end` normalised to inclusive day boundaries
  (`@startBound = CAST(@start AS DATE)`,
  `@endBound = DATEADD(DAY, 1, CAST(@end AS DATE))`).
- **Heats still under cover** (no `'U'` event yet) show up with
  `Ended = NULL`, `TimeUnderCover` running up to `GETUTCDATE()`, and
  `StillUnderCover = 1`. (Legacy proc required both `'A'` and `'U'`
  events; they're now optional.)
- Columns: `HeatNumber`, `CoverNumber`, `Started`, `Ended`,
  `TimeUnderCover`, `StillUnderCover`.

**`@realTime = 1`** — realtime mode, feeds the bottom grid.
- One row per currently-assigned `CoverHeats` row (`HeatName IS NOT NULL`).
- Three `OUTER APPLY`s replace the legacy correlated-subquery cluster
  (each lookup runs once and is reused):
  - `lastAssign` — latest `'A'` event for the heat (uses
    `IX_CoverHeatsEvents_HeatName_Datetime`).
  - `latestTemp` — latest `CoverTemps` row for the cover (clustered
    seek on `PK_CAS.CoverTemps`).
  - `peak` — `MAX(TempSensor1)` since the assignment.
- Columns: `CoverNumber`, `HeatNumber`, `Started`, `TimeUnderCover`,
  `TempSensor1`, `TempSensor2`, `Status`, `PeakTemperature`,
  `OutOfCoverAlertHours`, `OutOfCoverAlertActive`.

### `CAS.GetAvailableCovers()`

Two result sets:
1. **Covers**: `SELECT DISTINCT CoverId FROM CoverHeats WHERE isAvailable = 1 AND IsSensorLive = 1`.
   Trusts the cached flags rather than recomputing freshness — keep it
   this way.
2. **Heats**: top 50 from `MES.Heats` not currently in any `CoverHeats`
   row, ordered by `ProductionDate DESC`.

### `CAS.GetLastCoverTemperatures(@timeZoneCode, @bucketMinutes=10, @bucketsBack=12)`

**Long-format** output, one row per `(bucket, cover)`:
`Timestamp` (local), `TimestampUtc`, `CoverNumber`, `TempSensor1`,
`TempSensor2`, `Status`. The covers-temperatures screen pivots into
`C{N}S1 / C{N}S2 / C{N}ST` columns client-side, which is what lets new
covers appear without a page reload.

Default behaviour (12 buckets × 10 min) matches the legacy proc's display
window.

### `CAS.GetTemperaturesByCover(@CoverNumber)`

Returns the last 200 readings for one cover, timestamps already in local
time (`IH.ToLocal(..., NULL)`). Drives the per-cover chart modal opened
from the realtime grid's 📈 icon. No `@timeZoneCode` parameter (uses
NULL → server default).

---

## Front-end conventions specific to this screen

### `covers-report.js`

- Two `Backbone.Collection`s: `Items` (history) and `ItemsRealTime`
  (realtime). Each calls `CAS.GetCoversReport` with a different
  `@RealTime` value.
- Both fetches send `@timeZoneCode` from
  `app.models.user.get('timezoneCode')`. **Do not drop this** — without
  it `Started` / `Ended` come back UTC and the screen will display them
  as if they were local.
- The realtime grid uses AG Grid `groupDisplayType: 'groupRows'`. The
  `autoGroupColumnDef.valueFormatter` formats the group key as
  `'Cover N'` (matching `CoverName` in the DB and the headers on the
  covers-temperatures screen).
- `refreshInterval = 30` (seconds). Matches the SQL Agent cadence. Two
  inner places **must** use `this.refreshInterval` rather than a literal:
  - the modulo check `currentSeconds % this.refreshInterval === 0`
  - the countdown label `'Refresh In ' + this.refreshInterval`
- `resumeAutoRefresh()` (called when the assign/unassign modal closes)
  resets the countdown and **forces** a `refreshProcessesRt({reset:true})`
  immediately so the operator doesn't wait for the next tick.

### `modal-assign-cover.js`

- The Started input is `<input type="datetime-local">` — produces LOCAL
  time, no timezone. The screen must send `@timeZoneCode` so
  `UpdateCoverHeat` can `IH.ToUtc` it. **Do not drop this parameter.**
- The proc has `securedsp-CAS.UpdateCoverHeat = 'SendCurrentUser=TRUE'`
  registered (see `covers-report.sql`), so the secured-call framework
  auto-injects `@currentUser`. The modal does not pass it explicitly.

### `covers-temperatures.js` (sister screen)

- Consumes the long-format `GetLastCoverTemperatures` and pivots
  client-side. The pivot logic stashes discovered `coverNumbers` on the
  collection so `getDynamicColumns()` doesn't have to scrape attribute
  keys off a sample row.
- `collection_rt_ready` only calls `gridApi.setColumnDefs` when the cover
  set actually changes (compares `_lastCoverKey`), to avoid blowing away
  sort/scroll/filter state on every refresh.
- `refreshInterval = 60`. Lower-cadence than covers-report because the
  bucketed display only meaningfully changes every ~10 min anyway.

---

## Environment notes

### Dev: `SRD-MESDEV`

- `Covers` database is on this same SQL instance.
- `Job_TrackCoverTemps` runs the local branch (direct cross-DB query
  wrapped in `sp_executesql`).
- `[Leap_1]` linked server may not exist on dev — and **the proc does
  not require it to exist** on a server where the local branch fires.

### Prod: `SRD-MES`

- `Covers` database is on a different host.
- `[Leap_1]` linked server must exist and use a fixed SQL login
  (`CoversTrackingUser`) — Windows pass-through fails because the SQL
  Server is reached via NTLM and double-hop credential delegation isn't
  configured.
- `Job_TrackCoverTemps` runs the OPENQUERY branch.

The proc auto-routes via `DB_ID('Covers')` at runtime. **Do not** split
this into per-environment versions; the single-file design is intentional
so SSDT publishes the same dacpac to both environments.

---

## Common modifications — quick recipes

### Add a new cover (new device on the gateway)

1. `SELECT * FROM OPENQUERY([Leap_1], 'SELECT Id, Name FROM Covers.dbo.Device')`
   to find the `DeviceId`.
2. Insert a row into `CoverSensorMap`:
   ```sql
   INSERT INTO CAS.CoverSensorMap (DeviceId, CoverNumber, SensorSlot, DisplayName, Active)
   VALUES (<deviceId>, <coverN>, 1, 'Cover <N>', 1);
   ```
3. Next job tick (within 30 s):
   - inserts a placeholder `CoverHeats` row,
   - starts populating `CoverTemps`,
   - the screen's realtime grid picks it up.

### Add a B-sensor to an existing cover

```sql
INSERT INTO CAS.CoverSensorMap (DeviceId, CoverNumber, SensorSlot, DisplayName, Active)
VALUES (<deviceId>, <existingCoverN>, 2, 'Cover <N>B', 1);
```
Next tick, `TempSensor2` starts populating. The realtime grid's `S. B`
column will show values for that cover.

### Change the job cadence

`msdb.dbo.sp_update_schedule` against the job's schedule, setting
`@freq_subday_interval`. Then update the screen-side cadence to match:

```js
// covers-report.js
refreshInterval : <seconds>
```

(Don't forget the two inner `this.refreshInterval` references — the
modulo and the countdown label.)

### Change the temperatures bucket size

`CAS.GetLastCoverTemperatures` accepts `@bucketMinutes` and `@bucketsBack`.
The screen currently doesn't pass them (uses defaults: 10 min × 12). To
change without changing the screen, change the proc's defaults. To make
it user-configurable, add the params to `covers-temperatures.js`'s
collection fetch.

### Bump the capacity per cover (currently 10)

Hardcoded in `UpdateCoverHeat` as `@maximumHeats INT = 10`. Change there.
Consider also surfacing the value in the screen (currently it's not
displayed anywhere).

### Wipe and re-seed for a clean test slate

```sql
TRUNCATE TABLE CAS.CoverHeatsEvents;   -- skip if you want to keep audit
TRUNCATE TABLE CAS.CoverTemps;
TRUNCATE TABLE CAS.CoverHeats;
TRUNCATE TABLE CAS.CoverSensorMap;
-- … re-INSERT into CoverSensorMap with the current device list …
EXEC CAS.Job_TrackCoverTemps;
```

---

## Known design decisions (don't accidentally undo these)

- **Map by ID, not by Name.** The job used to parse `Device.Name`
  (`"COVER 1"`, `"COVER 1B"`); a gateway reset that nulled the Name
  column silently broke the pipeline. The `CoverSensorMap` table is the
  source of truth now and Names from the gateway are ignored. Do not
  reintroduce Name parsing.
- **DeviceId is `BIGINT`.** The gateway issues 19-digit IDs; `INT`
  overflows.
- **isAvailable is capacity, IsSensorLive is freshness, and they have
  separate single owners.** Do not let either proc write the other's
  flag.
- **No wildcard `[Datetime]` update on `CoverHeatsEvents`.** The legacy
  bug. See the HARD RULE above.
- **OPENQUERY inside `sp_executesql`.** Lets `CREATE PROCEDURE` succeed
  on any server regardless of whether `[Leap_1]` exists. If you "fix"
  this by unwrapping, the proc will fail to deploy to SRD-MESDEV.
- **30 s job cadence + 30 s screen cadence.** Tightly coupled by design.
  If you change one, change the other and update the modulo/label in
  `covers-report.js`.

---

## Where to look first when things go wrong

| Symptom | First place to look |
|---|---|
| A cover's temps stop appearing | `SYSTEM.Logs` for the latest `CAS.Job_TrackCoverTemps` line — counters tell you whether the job ran, how many temps it inserted, and how many devices it saw but couldn't map. |
| A new device isn't recognised | The job log line `… N unmapped devices on the source`. Then run the discovery query and add a `CoverSensorMap` row. |
| `Started` shows a 4 h offset | `@timeZoneCode` either not being passed by the screen or not being applied in the proc. Check both `modal-assign-cover.js` and `UpdateCoverHeat`. |
| `TimeUnderCover` starts at ~4 h | Same root cause — `@Started` stored as local instead of UTC. Look at the `IH.ToUtc` call in `UpdateCoverHeat`. |
| Realtime grid never updates after an assign | `resumeAutoRefresh()` in `covers-report.js` — should be calling `refreshProcessesRt({reset:true})`, not just `_refreshRt()`. |
| A cover is "available" in the modal but shouldn't be | Both `isAvailable` and `IsSensorLive` must be 1. Check `SELECT CoverId, isAvailable, IsSensorLive FROM CAS.CoverHeats`. |
| SSDT publish fails on `Leap_1` | The OPENQUERY branch must be wrapped in `sp_executesql`. See "Known design decisions" above. |
