# 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`. |
| `app/custom-screens/melt-shop-overview/` | **Cross-screen consumer** — the *Must Under Cover* box. Reads `CAS.CoverHeats` + `CAS.CoverHeatsEvents` via `LAD.GetMeltshopOverviewWeb` (last result set). See *Must Under Cover box* below. |

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

| Path | Role |
|---|---|
| `mes-db/CAS/Tables/CoverSensorMap.sql` | Maps remote `DeviceId` (BIGINT) → `(CoverNumber, DisplayName)`. One device per cover. 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. |
| `mes-db/LAD/Stored Procedures/GetMeltshopOverviewWeb.sql` | **Cross-schema consumer (LAD).** Last result set (Table7) feeds the *Must Under Cover* box; joins `CAS.CoverHeats` / `CAS.CoverHeatsEvents`. Carries the assigned-then-unassigned safeguard. |

### 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` UNIQUE | Internal cover identifier (1, 2, 3, …). Used as the join key with the other 3 tables. One device per cover. |
| `DisplayName` | `VARCHAR(50)` | Friendly name (`'Cover 3'`) surfaced in the UI. |
| `Active` | `BIT` default 1 | Soft-disable a device without deleting the row. Job ignores `Active = 0`. |
| | | UNIQUE constraint on `(CoverNumber)` — a cover can't be claimed by two devices. |

**Seeding is now automatic for well-named devices.** `Job_TrackCoverTemps`
step 1b inserts any source device named `'Cover NN'` into this table on the
tick it first appears (see the job docs above). A manual seed is only needed
for devices whose `Name` doesn't follow that pattern, or to override the
auto-derived `CoverNumber`. 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), Temperature REAL
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.
- `Temperature` is the single thermocouple reading for the cover, in °F.
- `CoverName` is the cover-level name, pulled from the map's `DisplayName`
  with a `'Cover N'` fallback if the device isn't mapped.
- 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 (and `Device.Name`) to `#Remote`.
1b. **Auto-map new covers.** Any device whose `Name` looks like
   `'Cover NN'` and isn't already in `CoverSensorMap` is inserted into the
   map now, *before* routing, so its reading is picked up on this same
   tick. Additive / idempotent / cover-conflict-safe (one device per
   cover). Name is used for discovery only, never routing.
2. Filter to `rn = 1` per device and INNER JOIN `CoverSensorMap`
   (Active=1). Unmapped devices are dropped — they surface in the log.
   (After 1b, the only devices left unmapped are those whose Name
   doesn't match the `'Cover NN'` pattern.)
3. Project each cover's latest reading into `#PerCover` (one row per cover).
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 6 counters:
   - source mode (`local` / `linked [Leap_1]`)
   - rows inserted into `CoverTemps`
   - **new covers auto-mapped this tick**
   - 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(Temperature)` since the assignment.
- Columns: `CoverNumber`, `HeatNumber`, `Started`, `TimeUnderCover`,
  `Temperature`, `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`, `Temperature`,
`Status`. The covers-temperatures screen pivots into one `C{N}` temperature
column per cover client-side (tinted by `C{N}ST` status), 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).

---

## Must Under Cover box (melt-shop-overview consumer)

The **Must Under Cover** box lives on the `melt-shop-overview` screen, not on
this one, but it is a read consumer of this subsystem. It is fed by the **last
result set (Table7) of `LAD.GetMeltshopOverviewWeb`** — a cross-schema query
that joins the `CAS` cover tables.

**What it lists:** every heat currently on a visible ladle position whose latest
`LAB.ChemistrySamples` hydrogen (`H1`/`H2`) is `> 1.5`.

- `UnderCover = 0` → **red** (left): high-H heat not under a cover. The operator
  drags it onto a cover to assign (opens the assign-cover modal →
  `CAS.UpdateCoverHeat`).
- `UnderCover = 1` → **green** (right): heat currently under a cover
  (`CoverHeats.CoverId IS NOT NULL`).

**Safeguard — assigned-then-unassigned suppression.** When a heat is unassigned,
`UpdateCoverHeat` sets `CoverHeats.HeatName = NULL`, which on its own would flip
`UnderCover` back to 0 and re-surface the heat in the red column, nagging the
operator about a heat that already finished its cover cycle. Table7 therefore
suppresses a heat that has any `'U'` event **and** is not currently under a cover:

```sql
AND (
    covhe.CoverId IS NOT NULL                       -- currently under a cover -> keep (green)
    OR NOT EXISTS (                                 -- else only if it never completed a cycle
        SELECT 1 FROM CAS.CoverHeatsEvents che WITH (NOLOCK)
        WHERE che.HeatName = h.[Name] AND che.[Action] = 'U'
    )
)
```

The `CoverId IS NOT NULL` arm keeps a genuine **re-assignment** visible in green.
`CAS.CoverHeatsEvents` is the source of truth — clearing a heat's events (see
*Reset one heat's cover history* below) makes it eligible for the box again.

---

## 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)

If the source device is named `Cover NN`, **just wait** — the job's step 1b
auto-maps it on the next tick (within 30 s), seeds a placeholder `CoverHeats`
row, starts populating `CoverTemps`, and the realtime grid picks it up.

To map it manually (e.g. the name doesn't match, or you want to back-fill
before the next tick):

1. `SELECT * FROM OPENQUERY([Leap_1], 'SELECT Id, Name FROM Covers.dbo.Device')`
   to find the `DeviceId`.
2. Insert a row into `CoverSensorMap` (one device per cover):
   ```sql
   INSERT INTO CAS.CoverSensorMap (DeviceId, CoverNumber, DisplayName, Active)
   VALUES (<deviceId>, <coverN>, 'Cover <N>', 1);
   ```

### Bulk-seed new covers from the source `Device.Name`

To back-fill several covers at once before the next tick, this re-runnable
helper discovers every source device named `Cover NN`, derives
`(CoverNumber, DisplayName)` from the name, and inserts only the rows not
already in `CoverSensorMap`.

This is the **seed** step only — it parses `Device.Name` once so the
job's by-`DeviceId` routing rule still holds (the job never reads Name).
It is environment-agnostic (`DB_ID('Covers')` branch, same as the job),
idempotent, never claims a `CoverNumber` already held by another device,
and supports a dry-run via `@commit = 0`.

```sql
/* Seed CAS.CoverSensorMap from source devices named 'Cover NN' (one sensor
   per cover). Re-runnable. @commit = 0 previews; @commit = 1 inserts. */
SET NOCOUNT ON;
DECLARE @commit BIT = 1;   -- 0 = preview only, 1 = perform INSERT

-- 1) Pull candidate devices from the source (env-agnostic, like the job)
CREATE TABLE #Dev (DeviceId BIGINT NOT NULL, DeviceName NVARCHAR(200) NULL);

IF DB_ID('Covers') IS NOT NULL
    INSERT INTO #Dev (DeviceId, DeviceName)
    EXEC sp_executesql N'
        SELECT DISTINCT CAST(dev.Id AS BIGINT), dev.Name
        FROM [Covers].dbo.Device dev
        WHERE dev.Name LIKE ''Cover%'' AND dev.Name LIKE ''%[0-9]%''';
ELSE
    INSERT INTO #Dev (DeviceId, DeviceName)
    EXEC sp_executesql N'
        SELECT src.DeviceId, src.DeviceName
        FROM OPENQUERY([Leap_1], ''
            SELECT DISTINCT CAST(dev.Id AS BIGINT) AS DeviceId, dev.Name AS DeviceName
            FROM [Covers].dbo.Device dev
            WHERE dev.Name LIKE ''''Cover%'''' AND dev.Name LIKE ''''%[0-9]%''''
        '') AS src';

-- 2) Parse Name -> (CoverNumber, DisplayName). One device per cover.
;WITH parsed AS (
    SELECT DeviceId, DeviceName,
           SUBSTRING(DeviceName, PATINDEX('%[0-9]%', DeviceName), 50) AS Tail
    FROM #Dev
),
candidates AS (
    SELECT DeviceId, DeviceName,
           TRY_CAST(LEFT(Tail, PATINDEX('%[^0-9]%', Tail + 'X') - 1) AS INT) AS CoverNumber
    FROM parsed
)
SELECT DeviceId, DeviceName, CoverNumber,
       'Cover ' + CAST(CoverNumber AS VARCHAR(10)) AS DisplayName,
       ROW_NUMBER() OVER (PARTITION BY CoverNumber ORDER BY DeviceId) AS rnCover
INTO #Candidates
FROM candidates
WHERE CoverNumber IS NOT NULL;

-- 3) Preview the new rows (and surface anything skipped)
PRINT '--- NEW rows to insert ---';
SELECT c.DeviceId, c.CoverNumber, c.DisplayName, c.DeviceName
FROM #Candidates c
WHERE c.rnCover = 1
  AND NOT EXISTS (SELECT 1 FROM CAS.CoverSensorMap m WHERE m.DeviceId = c.DeviceId)
  AND NOT EXISTS (SELECT 1 FROM CAS.CoverSensorMap m WHERE m.CoverNumber = c.CoverNumber)
ORDER BY c.CoverNumber;

PRINT '--- Cover already held by a DIFFERENT device (skipped - review!) ---';
SELECT c.DeviceId, c.DeviceName, c.CoverNumber, m.DeviceId AS ExistingDeviceId
FROM #Candidates c
JOIN CAS.CoverSensorMap m ON m.CoverNumber = c.CoverNumber
WHERE c.rnCover = 1 AND m.DeviceId <> c.DeviceId
ORDER BY c.CoverNumber;

-- 4) Insert (guarded; idempotent)
IF @commit = 1
BEGIN
    INSERT INTO CAS.CoverSensorMap (DeviceId, CoverNumber, DisplayName, Active)
    SELECT c.DeviceId, c.CoverNumber, c.DisplayName, 1
    FROM #Candidates c
    WHERE c.rnCover = 1
      AND NOT EXISTS (SELECT 1 FROM CAS.CoverSensorMap m WHERE m.DeviceId = c.DeviceId)
      AND NOT EXISTS (SELECT 1 FROM CAS.CoverSensorMap m WHERE m.CoverNumber = c.CoverNumber);
    PRINT '--- Inserted ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' new row(s). ---';
END
ELSE PRINT '--- DRY RUN (@commit = 0): nothing inserted. ---';

DROP TABLE #Candidates;
DROP TABLE #Dev;
```

Within 30 s the next job tick seeds the `CoverHeats` placeholder rows
and starts populating `CoverTemps`; the realtime grid picks the new
covers up on its next refresh.

### Backfill `CoverTemps` history from the source

The job only inserts the *latest* reading per cover each tick, so a freshly
(re)built `CoverTemps` starts empty. To preload history — e.g. after dropping
and recreating the table — pull the last N hours of readings straight from
the source `Covers` DB. Run it **after** `CoverSensorMap` is seeded (unmapped
devices are skipped). Env-agnostic and idempotent (`NOT EXISTS` on the PK), so
it's safe to re-run; change the window via `@hoursBack`.

```sql
/* Backfill CAS.CoverTemps with the last @hoursBack hours of readings per
   cover from the source Covers DB. PREREQ: CoverSensorMap seeded.
   Re-runnable; auto-routes local (dev) vs OPENQUERY([Leap_1]) (prod). */
SET NOCOUNT ON;
DECLARE @hoursBack INT = 12;

-- 1) Pull the window of readings from the source into a temp table.
CREATE TABLE #Remote (
    DeviceId         BIGINT   NOT NULL,
    TemperatureC     FLOAT    NULL,
    ReadingTimestamp DATETIME NULL
);

IF DB_ID('Covers') IS NOT NULL
BEGIN
    -- Local path (dev / SRD-MESDEV): Covers DB on the same instance.
    DECLARE @sqlLocal NVARCHAR(MAX) = N'
        SELECT CAST(dev.Id AS BIGINT),
               CAST(reading.Quantity AS FLOAT),
               reading.ReadingTimestamp
        FROM [Covers].dbo.Reading reading
        JOIN [Covers].dbo.Device  dev ON dev.Id = reading.DeviceId
        WHERE reading.ReadingTimestamp >= DATEADD(HOUR, -@hoursBack, GETUTCDATE())';

    INSERT INTO #Remote (DeviceId, TemperatureC, ReadingTimestamp)
    EXEC sp_executesql @sqlLocal, N'@hoursBack INT', @hoursBack = @hoursBack;
END
ELSE
BEGIN
    -- Remote path (prod / SRD-MES): Covers reached via [Leap_1]. The window
    -- filter runs on the remote box; doubled quotes for the nested string.
    DECLARE @openq NVARCHAR(MAX) = N'
        INSERT INTO #Remote (DeviceId, TemperatureC, ReadingTimestamp)
        SELECT src.DeviceId, src.TemperatureC, src.ReadingTimestamp
        FROM OPENQUERY([Leap_1], ''
            SELECT CAST(dev.Id AS BIGINT)          AS DeviceId,
                   CAST(reading.Quantity AS FLOAT) AS TemperatureC,
                   reading.ReadingTimestamp        AS ReadingTimestamp
            FROM [Covers].dbo.Reading reading
            JOIN [Covers].dbo.Device  dev ON dev.Id = reading.DeviceId
            WHERE reading.ReadingTimestamp >= DATEADD(HOUR, -'
                + CAST(@hoursBack AS NVARCHAR(10)) + N', GETUTCDATE())
        '') AS src';

    EXEC sp_executesql @openq;
END

-- 2) Map device -> cover, convert C -> F, insert (skipping existing rows).
;WITH Mapped AS (
    SELECT
        m.CoverNumber,
        ISNULL(m.DisplayName, 'Cover ' + CAST(m.CoverNumber AS VARCHAR(10))) AS CoverName,
        r.ReadingTimestamp,
        ROUND((r.TemperatureC * 9.0 / 5.0) + 32.0, 2) AS TemperatureF
    FROM #Remote r
    JOIN CAS.CoverSensorMap m
        ON m.DeviceId = r.DeviceId
       AND m.Active   = 1
    WHERE r.ReadingTimestamp IS NOT NULL
),
Dedup AS (
    SELECT CoverNumber,
           MAX(CoverName)    AS CoverName,
           ReadingTimestamp,
           MAX(TemperatureF) AS TemperatureF
    FROM Mapped
    GROUP BY CoverNumber, ReadingTimestamp
)
INSERT INTO CAS.CoverTemps (CoverNumber, CoverName, [Timestamp], [Status], Temperature)
SELECT d.CoverNumber, LEFT(d.CoverName, 20), d.ReadingTimestamp, 'G', d.TemperatureF
FROM Dedup d
WHERE NOT EXISTS (
    SELECT 1 FROM CAS.CoverTemps t
    WHERE t.CoverNumber = d.CoverNumber
      AND t.[Timestamp] = d.ReadingTimestamp
);

PRINT '--- Backfilled ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' CoverTemps rows. ---';

DROP TABLE #Remote;
```

It inserts **every** reading in the window (full history curve), not just the
latest; the covers-temperatures screen still buckets to 10 min for display.

### 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).

### Reset one heat's cover history (re-test Must Under Cover)

A heat that was assigned then unassigned is suppressed from the *Must Under
Cover* box by the safeguard above. To make it eligible again on a test box,
release any current assignment and wipe its audit rows. `CoverTemps` is
per-cover — leave it alone.

```sql
-- inspect first
SELECT * FROM CAS.CoverHeats       WHERE HeatName = 'A275472';
SELECT * FROM CAS.CoverHeatsEvents WHERE HeatName = 'A275472' ORDER BY [Datetime];

BEGIN TRAN;
UPDATE CAS.CoverHeats SET HeatName = NULL, isAvailable = 1 WHERE HeatName = 'A275472';
DELETE FROM CAS.CoverHeatsEvents WHERE HeatName = 'A275472';
COMMIT;   -- ROLLBACK if the inspect showed something unexpected
```

If the name is stored padded/suffixed, match with `RTRIM(HeatName) = 'A275472'`
or `LIKE 'A275472%'`.

### 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)

- **Route by ID, not by Name — but auto-DISCOVER by Name.** Once a
  device is in `CoverSensorMap` it is matched by `DeviceId` forever
  (step 2 never reads Name). This is the invariant that survived the
  old bug where a gateway reset nulled `Device.Name` and broke the
  pipeline. The job's step **1b** *does* read `Name`, but only to
  auto-insert brand-new `'Cover NN'` devices into the map (additive,
  idempotent, one device per cover, never claims a `CoverNumber` already
  held by another device). A null Name therefore can't break an existing
  cover — it only pauses auto-discovery of new ones. Do not let Name
  influence the *routing* of an already-mapped device.
- **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`.
- **Must Under Cover box suppresses by `'U'` event.** A heat with any
  `CoverHeatsEvents` `'U'` row that is not currently under a cover is hidden
  from the box (`LAD.GetMeltshopOverviewWeb` Table7), so a covered-then-removed
  heat doesn't re-nag. Don't revert this to a `CoverHeats`-only check — that
  reintroduces the re-nag.

---

## 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. |
