--- report.sql  -*- SQL -*-
-- Create Iroca PostgreSQL 15 database objects specific to reporting.

--- Ivan Shmakov, 2022

-- To the extent possible under law, the author(s) have dedicated
-- all copyright and related and neighboring rights to this software
-- to the public domain worldwide.  This software is distributed
-- without any warranty.

-- You should have received a copy of the CC0 Public Domain Dedication
-- along with this software.  If not, see
-- <http://creativecommons.org/publicdomain/zero/1.0/>.

--- History:

-- 0.3  2022-12-15 04:32Z
--      (sensor-latest-js): No longer join the removed reading table
--      to get timestamps, which are now provided by sensor-data.
--      (sensor_bin_avg_perc): Likewise.
--      (threshold-json): New view.
--      (ksen-warning-log-json): Likewise.

-- 0.2  2022-12-13 21:50:26Z
--      (sfn.TH_lRmD900K6qfcKwckbyTr0mChblyiRJmtDipSRsiM.sql)
--      (sensor-json): Also include the kind column from a left
--      outer join with kind-sensor; also include bunch and kind
--      in the JSON object.
--      (sensor_bin_avg_perc_json): New function.

-- 0.1  2022-12-12 21:20:38Z
--      (sfn.nKzWNARySOYxIWRr9Z2FZL49kpnO-mBht3YeJPleouI.sql)
--      Initial revision.

--- Code:

BEGIN;

CREATE SCHEMA IF NOT EXISTS rajye;



--- Sensors

CREATE OR REPLACE VIEW "bunch-sensors"
    AS  SELECT bunch, parent, array_agg (sensor)
            FROM hodlo.bunch b
                INNER JOIN hodlo.sensor s USING (bunch)
            GROUP BY (bunch);

--  NB: in the following views, we omit the primary key from the JSON
--      returned in favor of the caller using jsonb_object_agg with
--      the primary keys becoming the resulting JSON object keys.

CREATE OR REPLACE VIEW "kind-json"
    AS  SELECT  *,
                jsonb_build_object ('name', name,
                                    'description', description)
                    AS "kind-json"
            FROM hodlo.kind;

CREATE OR REPLACE VIEW "sensor-json"
    AS  SELECT  *,
                jsonb_build_object ('name', name,
                                    'bunch', bunch, 'kind', kind,
                                    'description', description)
                    AS "sensor-json"
            FROM hodlo.sensor
                    -- NB: questionable, but it works for now
                    LEFT OUTER JOIN hodlo."kind-sensor"
                        USING (sensor);

CREATE OR REPLACE VIEW "kind-sensor-json"
    AS  SELECT  *,
                jsonb_build_object ('kind', kind, 'name', sensor.name,
                                    'description', description)
                    AS "kind-sensor-json"
            FROM hodlo."kind-sensor"
                INNER JOIN hodlo.sensor USING (sensor);

CREATE OR REPLACE VIEW "bunch-sensors-json-1"
    AS  SELECT  b.*,
                jsonb_object_agg (sensor, "sensor-json") 
                    AS "bunch-sensors-json"
            FROM hodlo.bunch b
                INNER JOIN "sensor-json" USING (bunch)
        GROUP BY (bunch);

CREATE OR REPLACE VIEW "bunch-sensors-json"
    AS  SELECT  b.*,
                jsonb_build_object ('name', b.name, 'parent', b.parent,
                                    'description', b.description,
                                    'sensors',
                                    COALESCE   ("bunch-sensors-json",
                                                '{}'::jsonb))
                    AS "bunch-sensors-json"
            FROM hodlo.bunch b
                LEFT OUTER JOIN "bunch-sensors-json-1" USING (bunch);

CREATE OR REPLACE VIEW "sensor-latest-json"
    AS  SELECT  sd.*,
                jsonb_build_object ('timestamp', timestamp,
                                    'bvalue', bvalue,
                                    'dvalue', dvalue)
                    AS "sensor-latest-json"
            FROM hodlo.sensor s,
                LATERAL
                   (SELECT *
                        FROM hodlo."sensor-data" sd
                        WHERE sd.sensor = s.sensor
                        ORDER BY timestamp DESC
                        LIMIT 1) sd;

CREATE OR REPLACE FUNCTION sensor_bin_avg_perc (bin interval,
                                                ori timestamp, 
                                                perc double precision[])
    RETURNS TABLE  ("timestamp" timestamp,
                    sensor int,
                    avg double precision,
                    perc double precision[]) AS $$
    SELECT  date_bin (bin, timestamp, ori) AS ts,
            sensor,
            avg (dvalue),
            percentile_disc (perc) WITHIN GROUP (ORDER BY dvalue)
        FROM hodlo."kind-sensor-data"
        GROUP BY (ts, sensor);
$$  LANGUAGE SQL;

CREATE OR REPLACE FUNCTION sensor_bin_avg_perc_json (bin interval,
                                                     ori timestamp, 
                                                     perc double precision[])
    RETURNS TABLE  ("timestamp" timestamp,
                    sensor int,
                    avg double precision,
                    perc double precision[],
                    "json" jsonb) AS $$
    SELECT  timestamp, sensor, avg, perc,
            jsonb_build_object ('timestamp', timestamp,
                                'sensor', sensor,
                                'avg', avg, 'perc', perc)
        FROM sensor_bin_avg_perc (bin, ori, perc);
$$  LANGUAGE SQL;


--- Warning thresholds

-- NB: we flatten ksen-warning into the resulting JSON here

CREATE OR REPLACE VIEW "threshold-json"
    AS  SELECT  *,
                jsonb_build_object ('sensor', sensor,
                                    'assert', assert,
                                    'release', release,
                                    'description', description,
                                    'updated', updated)
                    AS "threshold-json"
            FROM hodlo.threshold
                    LEFT OUTER JOIN hodlo."ksen-warning"
                        USING (threshold);

CREATE OR REPLACE VIEW "ksen-warning-log-json"
    AS  SELECT  *,
                jsonb_build_object ('threshold', threshold,
                                    'start', start, 'stop', stop,
                                    'updated', updated)
                    AS "ksen-warning-log-json"
            FROM hodlo."ksen-warning-log";


--- End transaction

END;

--- report.sql ends here
