Shift coverage daily

1-- core.sona.shift_coverage_daily
2-- Rota coverage per location and shift type: how many shifts were planned, how many
3-- were filled by your own staff, how many by an agency, and how many are still open.
4-- Covers upcoming shifts as well as past ones, so it doubles as a look-ahead of
5-- open shifts to fill.
6--
7-- Grain: one row per local shift date x org unit x shift type.
8-- Depends on: staging.sona.shifts, staging.sona.org_units
9--
10-- Shifts are dated in the org unit's own time zone (org_units.timezone, falling
11-- back to UTC), so a night shift starting at 22:00 local is on the day it starts.
12--
13-- FILLED, AGENCY, OPEN. A shift with an agency on it counts as agency. Otherwise
14-- it is filled when it is linked to an employment period or a user, and open when
15-- it is linked to neither. Sona's own wording for these states may differ;
16-- check one week against the rota view and adjust the CASE if it does.
17--
18-- Draft copies of shifts (draft_of_shift_id set) and travel gaps between visits are
19-- not shifts and are left out of the counts; travel time is reported separately.
20-- Hours are start to finish and include breaks.
21
22WITH shifts AS (
23    SELECT
24        s.*,
25        DATE(s.starts_at, COALESCE(o.timezone, 'UTC'))          AS shift_date,
26        TIMESTAMP_DIFF(s.finishes_at, s.starts_at, MINUTE) / 60.0 AS shift_hours,
27        CASE
28            WHEN s.agency_id IS NOT NULL                                        THEN 'agency'
29            WHEN s.employment_period_id IS NOT NULL
30              OR s.user_working_shift_id IS NOT NULL                            THEN 'filled'
31            ELSE 'open'
32        END                                                     AS fill_status
33    FROM {{staging.sona.shifts}} s
34    LEFT JOIN {{staging.sona.org_units}} o
35      ON o.org_unit_id = s.org_unit_id
36    WHERE s.draft_of_shift_id IS NULL
37)
38
39SELECT
40    s.shift_date,