-- ============================================================================
-- AnalyzeNodeData.qbquery
-- PhloMetric Water Meter System: analytic MySQL queries for node data
-- Copyright (c) 2026 Competition Software. All rights reserved.
--
-- WHAT THIS IS
--   A toolkit of tested MySQL 8 queries that extract operational value from
--   the `phoa_water.readings` table your ingest service fills (Windows LAN
--   service or the self-hosted cloud ingest service; the schema is the same
--   where these queries touch it). Every query below was validated against a
--   live PhloMetric deployment; sample output in the comments is real data.
--
-- HOW TO RUN
--   mysql -u phoa_water -p phoa_water        (then paste a section)
--   or open this file in any MySQL client / MySQL Query Browser and run one
--   section at a time. Each section starts by SETting the @variables it uses;
--   edit those, never the query bodies.
--
-- THE TWO METER FAMILIES (this matters for which query to use)
--   GIF2006B ("sub-minute" nodes): the meter updates its broadcast register
--     on a sub-minute basis, so rows land ~30-60 s apart while water flows.
--     These nodes support the per-minute, cycle, and valve-level queries.
--   GIF2014W-OSE ("hourly" nodes): the meter refreshes its broadcast register
--     about once per hour at a fixed minute, so you get roughly one row per
--     hour in which water flowed. Use the daily / hourly / off-window queries;
--     per-minute analysis is not physically possible for this family.
--   Section 1 tells you which of your nodes is which, from the data alone.
--
-- CONVENTIONS
--   * volume_gal is the meter's cumulative register in gallons (0.1 gal
--     precision). Usage is always a difference of register values.
--   * gateway_received_at is the gateway clock at radio decode time; use it
--     for analysis, not logged_at (DB insert time).
--   * Unchanged rebroadcasts are deduplicated upstream: a row exists only
--     when the register moved. "No rows" means "no water", not "no signal"
--     (Section 8 confirms nodes are alive).
--   * All date ranges are @variables: @t0 inclusive, @t1 exclusive.
--   * Single-tenant databases need no cust_id filter. On a multi-tenant
--     cloud database add:  AND cust_id = <your id>  to each WHERE.
-- ============================================================================


-- ────────────────────────────────────────────────────────────────────────────
-- 1. NODE INVENTORY AND CADENCE FINGERPRINT
--    Which nodes are sub-minute (GIF2006B) vs hourly (GIF2014W-OSE), from the
--    data alone: the fraction of row-to-row gaps under 5 minutes. Sub-minute
--    nodes cluster near 1.0; hourly nodes sit well under 0.5 (their few small
--    gaps are multi-hour catch-up batches arriving together).
-- ────────────────────────────────────────────────────────────────────────────
SET @t0 = '2026-06-27', @t1 = '2026-07-27';

SELECT node_id, protocol, COUNT(*) AS rows_in_range,
       ROUND(AVG(gap_s < 300), 2) AS frac_small_gaps,
       CASE WHEN AVG(gap_s < 300) > 0.5
            THEN 'sub-minute (GIF2006B class)'
            ELSE 'hourly (GIF2014W-OSE class)' END AS cadence_class
FROM (
  SELECT node_id, protocol,
         TIMESTAMPDIFF(SECOND,
             LAG(gateway_received_at) OVER (PARTITION BY node_id
                                            ORDER BY gateway_received_at),
             gateway_received_at) AS gap_s
  FROM readings
  WHERE gateway_received_at >= @t0 AND gateway_received_at < @t1
) g
WHERE gap_s IS NOT NULL
GROUP BY node_id, protocol
ORDER BY node_id;
-- Example (live): node 1 frac 0.99 sub-minute; node 2 frac 0.15 hourly;
-- node 3 frac 0.25 hourly; nodes 4/5/7 frac 0.86-0.99 sub-minute.


-- ────────────────────────────────────────────────────────────────────────────
-- 2. DAILY USAGE PER NODE (works for BOTH families)
--    Take the register value standing AT each midnight boundary (the last
--    reading at-or-before it, "LOCF") and difference consecutive days.
--    IMPORTANT for hourly meters: do NOT use MAX-MIN within the day; a sparse
--    meter's first row of the day already contains usage that belongs to the
--    boundary, and MAX-MIN undercounts. The LOCF form below is exact for both
--    families. @lookback bounds how far back a sparse meter's last reading
--    may be; 45 days is generous.
--    Note: usage that straddles midnight (a 23:00 irrigation program running
--    past 00:00) splits across the two days, by design.
-- ────────────────────────────────────────────────────────────────────────────
SET @t0 = '2026-07-01', @t1 = '2026-07-09', @lookback = 45;

WITH RECURSIVE days AS (
  SELECT CAST(@t0 AS DATE) AS d
  UNION ALL SELECT d + INTERVAL 1 DAY FROM days WHERE d < CAST(@t1 AS DATE)
),
locf AS (
  SELECT dy.d, r.node_id, MAX(r.volume_gal) AS reg_at_midnight
  FROM days dy
  JOIN readings r
    ON r.gateway_received_at <  dy.d
   AND r.gateway_received_at >= dy.d - INTERVAL @lookback DAY
  GROUP BY dy.d, r.node_id
)
SELECT node_id, d AS day_ending_midnight,
       reg_at_midnight
         - LAG(reg_at_midnight) OVER (PARTITION BY node_id ORDER BY d)
         AS gallons_used
FROM locf
ORDER BY node_id, d;
-- Example (live, node 1): 591, 0, 1729, 604, 1735, 577, 3382 gal across
-- 7/2-7/9; the 0 is a no-watering day, the 3382 a night with an extra cycle.


-- ────────────────────────────────────────────────────────────────────────────
-- 3. IRRIGATION CYCLE LOG (sub-minute nodes)
--    Groups consecutive readings into "cycles": runs of flow separated by
--    more than @idle_gap_min of silence. Start, end, duration, gallons, and
--    average gpm per cycle. This is the query that surfaces unscheduled
--    "volunteer" watering: cycles at times nobody programmed.
-- ────────────────────────────────────────────────────────────────────────────
SET @node = 1, @t0 = '2026-07-01 12:00', @t1 = '2026-07-09 12:00',
    @idle_gap_min = 12, @min_cycle_gal = 50;

WITH d AS (
  SELECT gateway_received_at AS ts, volume_gal,
         TIMESTAMPDIFF(MINUTE,
             LAG(gateway_received_at) OVER (ORDER BY gateway_received_at),
             gateway_received_at) AS gap_min
  FROM readings
  WHERE node_id = @node
    AND gateway_received_at >= @t0 AND gateway_received_at < @t1
),
grp AS (
  SELECT ts, volume_gal,
         SUM(CASE WHEN gap_min IS NULL OR gap_min > @idle_gap_min
                  THEN 1 ELSE 0 END) OVER (ORDER BY ts) AS cycle_id
  FROM d
)
SELECT cycle_id,
       MIN(ts) AS cycle_start,
       MAX(ts) AS cycle_end,
       TIMESTAMPDIFF(MINUTE, MIN(ts), MAX(ts)) AS minutes,
       MAX(volume_gal) - MIN(volume_gal)       AS gallons,
       ROUND((MAX(volume_gal) - MIN(volume_gal))
             / NULLIF(TIMESTAMPDIFF(MINUTE, MIN(ts), MAX(ts)), 0), 1) AS avg_gpm
FROM grp
GROUP BY cycle_id
HAVING gallons >= @min_cycle_gal
ORDER BY cycle_start;
-- Example (live, node 1, 7/1-7/9): the scheduled 22:57 program appears as a
-- metronomic ~80 min / ~2302-2332 gal cycle on every watering night, and two
-- extra unscheduled cycles stand out: 7/1 22:00 (38 min, 801 gal) and
-- 7/8 21:27 (69 min, 1613 gal).


-- ────────────────────────────────────────────────────────────────────────────
-- 4. PER-MINUTE FLOW TRACE (sub-minute nodes)
--    Resamples the cumulative register onto a one-minute grid (LOCF at each
--    minute boundary) and differences it: gallons in each minute, with a
--    3-minute centered smoothing column. This is the raw material for the
--    valve queries below; it is also the chart-ready form of a cycle.
-- ────────────────────────────────────────────────────────────────────────────
SET @node = 1, @t0 = '2026-07-06 22:50', @t1 = '2026-07-07 00:25';
SET cte_max_recursion_depth = 10000;

WITH RECURSIVE mins AS (
  SELECT CAST(@t0 AS DATETIME) AS m
  UNION ALL SELECT m + INTERVAL 1 MINUTE FROM mins
  WHERE m < CAST(@t1 AS DATETIME)
),
grid AS (
  SELECT m.m,
         (SELECT MAX(r.volume_gal) FROM readings r
           WHERE r.node_id = @node
             AND r.gateway_received_at <= m.m
             AND r.gateway_received_at >= CAST(@t0 AS DATETIME) - INTERVAL 1 DAY
         ) AS reg
  FROM mins m
),
flow AS (
  SELECT m, reg - LAG(reg) OVER (ORDER BY m) AS gal_min FROM grid
)
SELECT m, gal_min,
       ROUND(AVG(gal_min) OVER (ORDER BY m
             ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 1) AS gpm_smooth3
FROM flow
WHERE gal_min IS NOT NULL;
-- Reading it: minute-to-minute values bounce (the register is whole gallons
-- and mesh delivery is bursty), but the smoothed column shows the valve
-- plateaus clearly, e.g. ~30 gpm, then ~25, ~21, ~32, ~36, ~31 across a
-- six-valve program.


-- ────────────────────────────────────────────────────────────────────────────
-- 5. VALVE TRANSITIONS (sub-minute nodes; the clever one)
--    Detects the moments individual irrigation valves open or close, from
--    the utility meter alone. An edge detector on the per-minute grid:
--    compare mean flow in the 5 minutes after each minute to the 5 minutes
--    before; a transition is where |change| >= @edge_gpm and is the local
--    extremum (suppression window of +/- 3 min).
--    Accuracy on live data: cycle start/stop and valve steps land within
--    +/- 2-3 minutes of the controller's programmed times. Tuning: raise
--    @edge_gpm if you get spurious edges, lower it if a small valve step
--    (a few gpm) goes unseen. Averaging several nights sharpens everything.
-- ────────────────────────────────────────────────────────────────────────────
SET @node = 1, @t0 = '2026-07-06 22:50', @t1 = '2026-07-07 00:25',
    @edge_gpm = 8;
SET cte_max_recursion_depth = 10000;

WITH RECURSIVE mins AS (
  SELECT CAST(@t0 AS DATETIME) AS m
  UNION ALL SELECT m + INTERVAL 1 MINUTE FROM mins
  WHERE m < CAST(@t1 AS DATETIME)
),
grid AS (
  SELECT m.m,
         (SELECT MAX(r.volume_gal) FROM readings r
           WHERE r.node_id = @node
             AND r.gateway_received_at <= m.m
             AND r.gateway_received_at >= CAST(@t0 AS DATETIME) - INTERVAL 1 DAY
         ) AS reg
  FROM mins m
),
flow AS (
  SELECT m, reg - LAG(reg) OVER (ORDER BY m) AS gal_min FROM grid
),
edges AS (
  SELECT m, gal_min,
         AVG(gal_min) OVER (ORDER BY m ROWS BETWEEN 1 FOLLOWING AND 5 FOLLOWING)
       - AVG(gal_min) OVER (ORDER BY m ROWS BETWEEN 4 PRECEDING AND 0 PRECEDING)
         AS edge
  FROM flow
  WHERE gal_min IS NOT NULL
)
SELECT m AS transition_at, ROUND(edge, 1) AS gpm_change
FROM (
  SELECT m, edge,
         MAX(ABS(edge)) OVER (ORDER BY m
             ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS local_max
  FROM edges
) p
WHERE ABS(edge) >= @edge_gpm AND ABS(edge) = local_max;
-- Example (live, node 1, 7/6 night; controller truth in parentheses):
--   22:59 +31.0  (cycle start 22:57, valve 1 on)
--   23:13 +10.8  (23:12 valve 1 -> 2)
--   23:28 -11.4  (23:27 valve 2 -> 3)
--   23:42 +22.4  (23:42 valve 3 -> 4)
--   23:50 +25.4  (23:52 valve 4 -> 5)
--   00:01  +8.6  (00:02 valve 5 -> 6)
--   00:17 -30.4  (cycle end 00:17)


-- ────────────────────────────────────────────────────────────────────────────
-- 6. VALVE STEP SUMMARY (sub-minute nodes)
--    Extends Section 5: cuts the window at each detected transition and
--    aggregates every segment into a row: start, duration, gallons, average
--    gpm. Compare a night's step table against the night before; a step
--    whose gpm moved by more than a few gpm is a valve with a new problem
--    (clog, partial close, broken head). Adjacent steps whose gpm differ by
--    only a few gpm are usually the same valve split by a spurious edge;
--    raise @edge_gpm or compare across nights.
-- ────────────────────────────────────────────────────────────────────────────
SET @node = 1, @t0 = '2026-07-06 22:50', @t1 = '2026-07-07 00:25',
    @edge_gpm = 8, @min_step_gal = 20;
SET cte_max_recursion_depth = 10000;

WITH RECURSIVE mins AS (
  SELECT CAST(@t0 AS DATETIME) AS m
  UNION ALL SELECT m + INTERVAL 1 MINUTE FROM mins
  WHERE m < CAST(@t1 AS DATETIME)
),
grid AS (
  SELECT m.m,
         (SELECT MAX(r.volume_gal) FROM readings r
           WHERE r.node_id = @node
             AND r.gateway_received_at <= m.m
             AND r.gateway_received_at >= CAST(@t0 AS DATETIME) - INTERVAL 1 DAY
         ) AS reg
  FROM mins m
),
flow AS (
  SELECT m, reg - LAG(reg) OVER (ORDER BY m) AS gal_min FROM grid
),
edges AS (
  SELECT m, gal_min,
         AVG(gal_min) OVER (ORDER BY m ROWS BETWEEN 1 FOLLOWING AND 5 FOLLOWING)
       - AVG(gal_min) OVER (ORDER BY m ROWS BETWEEN 4 PRECEDING AND 0 PRECEDING)
         AS edge
  FROM flow
  WHERE gal_min IS NOT NULL
),
marked AS (
  SELECT m, gal_min,
         CASE WHEN ABS(edge) >= @edge_gpm
               AND ABS(edge) = MAX(ABS(edge)) OVER (ORDER BY m
                   ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
              THEN 1 ELSE 0 END AS is_transition
  FROM edges
),
steps AS (
  SELECT m, gal_min, SUM(is_transition) OVER (ORDER BY m) AS step_id
  FROM marked
)
SELECT step_id,
       MIN(m)                  AS step_start,
       COUNT(*)                AS minutes,
       ROUND(SUM(gal_min), 0)  AS gallons,
       ROUND(AVG(gal_min), 1)  AS avg_gpm
FROM steps
GROUP BY step_id
HAVING gallons > @min_step_gal
ORDER BY step_start;
-- Example (live, node 1, 7/6 night): six valve plateaus resolve at roughly
-- 29, 25, 22, 26, 38, 33 gpm; per-valve reference values for this program
-- are 29.7 / 25.4 / 21.1 / 31.9 / 36.4 / 31.4 gpm.


-- ────────────────────────────────────────────────────────────────────────────
-- 7. HOURLY USAGE PROFILE AND OFF-WINDOW USAGE (hourly nodes)
--    For GIF2014W-OSE meters each row IS one hourly register step, so usage
--    per hour is just consecutive-row differences. The second query keeps
--    only usage OUTSIDE the expected irrigation window (@win_start to
--    @win_end, wrapping midnight): anything it returns is water at a time
--    nobody scheduled, at the meter's native hourly resolution.
-- ────────────────────────────────────────────────────────────────────────────
SET @node = 2, @t0 = '2026-07-20', @t1 = '2026-07-27';

SELECT DATE(gateway_received_at) AS day,
       HOUR(gateway_received_at) AS hr,
       ROUND(SUM(dv), 1)         AS gallons
FROM (
  SELECT gateway_received_at,
         volume_gal - LAG(volume_gal) OVER (ORDER BY gateway_received_at) AS dv
  FROM readings
  WHERE node_id = @node
    AND gateway_received_at >= CAST(@t0 AS DATETIME) - INTERVAL 3 DAY
    AND gateway_received_at <  @t1
) x
WHERE gateway_received_at >= @t0 AND dv > 0
GROUP BY day, hr
ORDER BY day, hr;
-- Example (live, node 2): every row lands in hour 23 or 0, i.e. the nightly
-- program; a healthy, quiet profile.

-- Off-window variant (21:00-02:00 window shown; wraps midnight):
SET @node = 2, @t0 = '2026-07-01', @t1 = '2026-07-27',
    @win_start = 21, @win_end = 2;

SELECT DATE(gateway_received_at) AS day,
       HOUR(gateway_received_at) AS hr,
       ROUND(SUM(dv), 1)         AS off_window_gallons
FROM (
  SELECT gateway_received_at,
         volume_gal - LAG(volume_gal) OVER (ORDER BY gateway_received_at) AS dv
  FROM readings
  WHERE node_id = @node
    AND gateway_received_at >= CAST(@t0 AS DATETIME) - INTERVAL 3 DAY
    AND gateway_received_at <  @t1
) x
WHERE gateway_received_at >= @t0 AND dv > 0
  AND NOT (   (@win_start > @win_end AND (HOUR(gateway_received_at) >= @win_start
                                       OR HOUR(gateway_received_at) <  @win_end))
           OR (@win_start <= @win_end AND HOUR(gateway_received_at) >= @win_start
                                      AND HOUR(gateway_received_at) <  @win_end))
GROUP BY day, hr
HAVING off_window_gallons > 0
ORDER BY day, hr;


-- ────────────────────────────────────────────────────────────────────────────
-- 8. LEAK SCREEN AND LINK HEALTH (both families)
--    Leak screen: how many distinct hours of each day did each meter move?
--    Irrigation uses 1-3 hours; a meter moving in @leak_hours+ hours per day
--    is either serving continuous domestic use or leaking. Volume disambiguates:
--    many hours + small total = trickle (suspect a leak or a running fixture);
--    many hours + large total = investigate immediately.
--    Link health: is every node still being heard, and how strong?
-- ────────────────────────────────────────────────────────────────────────────
SET @t0 = '2026-07-20', @t1 = '2026-07-27', @leak_hours = 12;

SELECT node_id, day, hours_with_flow, ROUND(gallons, 1) AS gallons,
       CASE WHEN hours_with_flow >= @leak_hours
            THEN 'near-continuous flow: leak or domestic-use pattern'
            ELSE 'ok' END AS verdict
FROM (
  SELECT node_id, DATE(gateway_received_at) AS day,
         COUNT(DISTINCT HOUR(gateway_received_at)) AS hours_with_flow,
         SUM(dv) AS gallons
  FROM (
    SELECT node_id, gateway_received_at,
           volume_gal - LAG(volume_gal) OVER (PARTITION BY node_id
                                              ORDER BY gateway_received_at) AS dv
    FROM readings
    WHERE gateway_received_at >= CAST(@t0 AS DATETIME) - INTERVAL 3 DAY
      AND gateway_received_at <  @t1
  ) x
  WHERE gateway_received_at >= @t0 AND dv > 0
  GROUP BY node_id, day
) y
ORDER BY hours_with_flow DESC, node_id, day;
-- Example (live): one node showed flow in 13-17 hours/day at 76-515 gal/day,
-- the signature of a residence meter (or a slow leak), while the irrigation
-- nodes showed 1-6 hours/day.

SET @t1 = '2026-07-27';

SELECT node_id,
       COUNT(*)                 AS readings_7d,
       ROUND(AVG(rssi_dbm), 1)  AS avg_rssi_dbm,
       MIN(rssi_dbm)            AS worst_rssi_dbm,
       MAX(gateway_received_at) AS last_heard
FROM readings
WHERE gateway_received_at >= CAST(@t1 AS DATETIME) - INTERVAL 7 DAY
GROUP BY node_id
ORDER BY node_id;
-- Note: rssi_dbm is the mesh hop the reading arrived on (node to gateway or
-- relay to gateway), not the meter's own radio link.

-- ============================================================================
-- End of AnalyzeNodeData.qbquery
-- ============================================================================