--- logger.sql  -*- SQL -*-
-- Create (MQTT) logger-specific Iroca PostgreSQL 15 database objects.

--- 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.2  2022-12-14 22:20Z
--      (topic-data): Fixed: use an empty string for intermediate
--      array elements that are null (such as when some ordinals
--      are not used.)
--      (topic-data): Use timestamp in place of reading, which
--      is no longer provided by sensor-data.
--      (topic_data_modify_tf): Likewise.
--      (topic_data_modify_tf): Fixed: qualify table names used
--      with explicit schemas; use regexp_split_to_array (was:
--      regexp_split_to_table) and use the proper order of
--      its arguments (were: reversed); use dvalue as the name
--      of the kind-sensor-data table column (was: bvalue);
--      cast elements of the array of text to the types of the
--      target bvalue and dvalue columns.

-- 0.1  2022-12-01 12:48:01Z
--      (sfn.H1wc9u4A61yctS_Fam4qaShu-Y8lqVQ9Gpjc_DzuYkQ.sql)
--      Initial revision.

--- Code:

BEGIN;

CREATE SCHEMA IF NOT EXISTS logeo;


CREATE TABLE IF NOT EXISTS profile (
    profile     INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name        TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE IF NOT EXISTS topic (
    topic       INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name        TEXT NOT NULL UNIQUE,
    separator   TEXT NOT NULL DEFAULT ' ',
    description TEXT
);

CREATE TABLE IF NOT EXISTS "topic-sensors" (
    topic       INTEGER REFERENCES topic (topic) NOT NULL,
    ordinal     INTEGER NOT NULL CHECK (ordinal >= 1),
    sensor      INTEGER REFERENCES hodlo.sensor (sensor) NOT NULL,
    UNIQUE (topic, ordinal)
);

CREATE TABLE IF NOT EXISTS "profile-topics" (
    profile     INTEGER REFERENCES profile (profile) NOT NULL,
    topic       INTEGER REFERENCES topic (topic) NOT NULL,
    UNIQUE (profile, topic)
);

CREATE OR REPLACE VIEW vprofile
    AS SELECT  pt.*,
               p.name AS "profile-name",
               p.description AS "profile-description",
               tp.name AS "topic-name",
               tp.description AS "topic-description",
               tp.separator AS "separator"
           FROM profile p
               INNER JOIN "profile-topics" pt USING (profile)
               INNER JOIN topic tp USING (topic);

CREATE OR REPLACE VIEW "topic-ordinal"
    AS  SELECT  topic,
                generate_series (1, MAX (ts.ordinal)) AS ordinal
            FROM "topic-sensors" ts
            GROUP BY topic;

CREATE OR REPLACE VIEW "topic-data"
    AS  SELECT timestamp, topic,
                -- FIXME: provide a string separator for serialization
                --        (in addition to the RE used for parsing)
                array_to_string (array_agg (COALESCE (dvalue::text,
                                                      bvalue::text,
                                                      '')
                                            ORDER BY ordinal ASC),
                                 ',', '') AS data
            FROM "topic-sensors" ts
                    RIGHT OUTER JOIN "topic-ordinal" ot
                        USING (topic, ordinal)
                    INNER JOIN topic tp USING (topic)
                    INNER JOIN hodlo."sensor-data" sd USING (sensor)
            GROUP BY (timestamp, topic);

CREATE OR REPLACE FUNCTION topic_data_modify_tf ()
    RETURNS trigger AS $$
    DECLARE
        ts  timestamp;
        re  text;
        ary text[];
    BEGIN
        ts  := COALESCE (NEW.timestamp, CURRENT_TIMESTAMP);
        SELECT separator INTO STRICT re FROM topic tp
            WHERE tp.topic = NEW.topic;
        ary := regexp_split_to_array (NEW.data, re);
        INSERT INTO hodlo."bool-sensor-data" (timestamp, sensor, bvalue)
            SELECT ts, sensor, ary[ordinal]::boolean
                FROM logeo."topic-sensors"
                    LEFT OUTER JOIN hodlo."kind-sensor" USING (sensor)
                WHERE topic = NEW.topic AND kind IS NULL;
        INSERT INTO hodlo."kind-sensor-data" (timestamp, sensor, dvalue)
            SELECT ts, sensor, ary[ordinal]::double precision
                FROM logeo."topic-sensors"
                    INNER JOIN hodlo."kind-sensor" USING (sensor)
                WHERE topic = NEW.topic;
        RETURN NEW;
    END;
$$  LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER "topic-data-modify"
    INSTEAD OF INSERT ON "topic-data"
    FOR EACH ROW EXECUTE FUNCTION topic_data_modify_tf ();

END;

--- logger.sql ends here
