/*
 * Copyright 2016-present the IoT DC3 original author or authors.
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 */

-- Point-value observability layer: continuous aggregates over the
-- dc3_point_value hypertable, plus a device-metadata view for Grafana.
--
-- Design notes:
--   * Two-level hierarchical cagg: 1-minute raw rollup -> 1-hour rollup.
--     The 1h cagg is built ON TOP of the 1m cagg so long-range dashboard
--     queries (7d / 30d) re-aggregate already-summarized buckets instead
--     of scanning the full hypertable.
--   * Both caggs are realtime aggregates (materialized_only=false): recent
--     samples that fall in the not-yet-materialized tail are merged live,
--     so dashboards see fresh data without waiting for the refresh policy.
--   * Aggregation slices by (tenant_id, driver_id, device_id, point_id),
--     matching the hypertable's compress_segmentby. This is the natural
--     drill-down axis for "per driver / per device / per point" observation.
--   * num_value carries the best-effort numeric projection of cal_value;
--     AVG/MIN/MAX/SUM only touch rows where it is NOT NULL (mirrors the
--     raw-table partial index idx_point_value_num_time). first()/last()
--     retain cal_value for string-typed points so they remain observable.
--   * point and device relate via profile_id only (a profile groups many
--     devices AND many points); point_value rows already carry both ids,
--     so dashboards JOIN metadata by those ids rather than a static map.

SET search_path TO dc3_history, public;

-- ----------------------------
-- 1-minute continuous aggregate
-- ----------------------------
-- One row per (tenant, driver, device, point, minute). Powers the
-- per-point trend panel and the global ingest-rate panel.
CREATE MATERIALIZED VIEW cagg_point_value_1m
    WITH (timescaledb.continuous = true) AS
SELECT time_bucket(INTERVAL '1 minute', create_time) AS bucket,
       tenant_id,
       driver_id,
       device_id,
       point_id,
       count(*)                                      AS sample_count,
       count(num_value)                              AS num_count,
       avg(num_value)                                AS num_avg,
       min(num_value)                                AS num_min,
       max(num_value)                                AS num_max,
       sum(num_value)                                AS num_sum,
       first(cal_value, create_time)                 AS cal_first,
       last(cal_value, create_time)                  AS cal_last
FROM dc3_point_value
GROUP BY bucket, tenant_id, driver_id, device_id, point_id
WITH NO DATA;

-- Drill-down index mirrors the dashboard filter shape.
CREATE INDEX idx_cagg_pv_1m_lookup
    ON cagg_point_value_1m (tenant_id, device_id, point_id, bucket DESC);

-- Realtime aggregate: also read the raw tail not yet materialized.
ALTER MATERIALIZED VIEW cagg_point_value_1m
SET(timescaledb.materialized_only = false);

-- ----------------------------
-- 1-hour continuous aggregate (hierarchical)
-- ----------------------------
-- Built on the 1m cagg, so a 30-day range scans ~720 hourly rows per point
-- instead of ~43k minute rows. One row per (tenant, driver, device, point, hour).
CREATE MATERIALIZED VIEW cagg_point_value_1h
    WITH (timescaledb.continuous = true) AS
SELECT time_bucket(INTERVAL '1 hour', bucket) AS bucket,
       tenant_id,
       driver_id,
       device_id,
       point_id,
       sum(sample_count)                      AS sample_count,
       sum(num_count)                         AS num_count,
       sum(num_sum) / NULLIF(sum(num_count), 0) ::DOUBLE PRECISION
                                                 AS num_avg,
       min(num_min)                           AS num_min,
       max(num_max)                           AS num_max,
       sum(num_sum)                           AS num_sum,
       first(cal_first, bucket)               AS cal_first,
       last(cal_last, bucket)                 AS cal_last
FROM cagg_point_value_1m
-- GROUP BY the first output expression explicitly. The source view also has a
-- column named bucket, so GROUP BY bucket would resolve to the minute bucket
-- instead of the hourly time_bucket expression and invalidate the hierarchy.
GROUP BY 1, tenant_id, driver_id, device_id, point_id
WITH NO DATA;

CREATE INDEX idx_cagg_pv_1h_lookup
    ON cagg_point_value_1h (tenant_id, device_id, point_id, bucket DESC);

ALTER MATERIALIZED VIEW cagg_point_value_1h
SET(timescaledb.materialized_only = false);

-- TimescaleDB exposes continuous aggregates as views in the PostgreSQL catalog.
COMMENT ON VIEW cagg_point_value_1m IS 'One-minute point-value continuous aggregate';
COMMENT ON VIEW cagg_point_value_1h IS 'One-hour hierarchical point-value continuous aggregate';

-- ----------------------------
-- Refresh policies
-- ----------------------------
-- Background refresh of the materialized window. Realtime mode still
-- answers queries from the live tail; the policy only backfills durable
-- buckets. Raw samples are kept 30 days (05-iot-dc3-history.sql, S16 tiered
-- lifecycle); the refresh offsets predate that on purpose so rebuilding the
-- materialized tiers after a drop still folds whatever raw history exists.
SELECT add_continuous_aggregate_policy('cagg_point_value_1m',
                                       start_offset => INTERVAL '7 days',
                                       end_offset => INTERVAL '1 minute',
                                       schedule_interval => INTERVAL '1 minute');

SELECT add_continuous_aggregate_policy('cagg_point_value_1h',
                                       start_offset => INTERVAL '180 days',
                                       end_offset => INTERVAL '5 minutes',
                                       schedule_interval => INTERVAL '5 minutes');

-- S16 tiered lifecycle: the minute tier ages out after one year; the hour tier
-- is kept forever. The timescale adapter registers the same policy with
-- if_not_exists, so embedded and standalone deployments converge.
SELECT add_retention_policy('cagg_point_value_1m', INTERVAL '365 days');

-- ----------------------------
-- Device metadata view for Grafana
-- ----------------------------
-- Deterministic join (device.driver_id is a single value): attaches the
-- driver name to each device so dashboard device pickers show the owning
-- driver without an extra join. Point/profile names are joined ad-hoc in
-- dashboard queries via the point_id/profile_id on each value row.
-- enable_flag is inverted in this schema: 0 = enabled, 1 = disabled.
CREATE OR REPLACE VIEW v_device_metadata AS
SELECT d.id             AS device_id,
       d.tenant_id,
       d.device_name,
       d.device_code,
       d.driver_id,
       d.profile_id,
       d.enable_flag,
       drv.driver_name,
       drv.driver_code,
       drv.service_name AS driver_service
FROM dc3_manager.dc3_device d
         LEFT JOIN dc3_manager.dc3_driver drv
                   ON drv.id = d.driver_id
                       AND drv.tenant_id = d.tenant_id
                       AND drv.deleted = 0
WHERE d.deleted = 0;

COMMENT ON VIEW v_device_metadata IS 'Tenant-scoped device and logical driver metadata for observability queries';