--- it87.sql  -*- SQL -*-
-- Create Iroca entities for recording it87 temperature readings.

--- 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.1  2022-12-15 07:45Z
--      Initial revision.

--- Code:

BEGIN;



--- Kinds, bunches, sensors

-- Create temperature kind
INSERT INTO kind (name, description)
    VALUES ('KtempC', 'Temperature, degrees Celsius')
    ON CONFLICT DO NOTHING;

--  NB: the names starting with T are used for demonstration and
--      testing; for production use, the suggested naming for
--      bunches and sensors is as follows:

--      S01             for Site 1 bunch;
--      S01/R203        for Room 203 at Site 1;
--      S01/R203/U45    for Unit 45 there;
--      Xtemp.1         for temperature sensor 1.

--      Similar schema can be used for MQTT topics as well.

-- Create root bunch
INSERT INTO bunch (name) VALUES ('T2022')
    ON CONFLICT DO NOTHING;
UPDATE bunch SET parent = bunch
    WHERE name = 'T2022' AND parent IS NULL;

-- Create subbunches
-- FIXME: cannot use ON CONFLICT as INSERT INTO vbunch goes via a rule
INSERT INTO vbunch (name, "parent-name")
    SELECT 'T2022.349', 'T2022'
        EXCEPT SELECT name, "parent-name" FROM vbunch;
INSERT INTO vbunch (name, "parent-name")
    SELECT 'T2022.349.it87', 'T2022.349'
        EXCEPT SELECT name, "parent-name" FROM vbunch;

-- Create temperature sensors
-- FIXME: currently requires one INSERT per sensor
INSERT INTO vsensor (name, "bunch-name", "kind-name")
    VALUES ('Tit87.1', 'T2022.349.it87', 'KtempC');
INSERT INTO vsensor (name, "bunch-name", "kind-name")
    VALUES ('Tit87.2', 'T2022.349.it87', 'KtempC');
INSERT INTO vsensor (name, "bunch-name", "kind-name")
    VALUES ('Tit87.3', 'T2022.349.it87', 'KtempC');

SELECT * FROM vbunch;
SELECT * FROM vsensor;
SELECT * FROM rajye."bunch-sensors-json";


--- Logging topics and profiles

INSERT INTO topic (name, separator)
    VALUES ('T2022/349/it87', ' +')
    ON CONFLICT DO NOTHING;
INSERT INTO "topic-sensors" (topic, ordinal, sensor)
    SELECT topic, ordinal, sensor
        FROM topic,
            LATERAL
               (SELECT row_number () OVER w AS ordinal, sensor
                    FROM vsensor
                    WHERE  ("bunch-name" = 'T2022.349.it87'
                            AND name LIKE 'Tit87.%')
                    WINDOW w AS (ORDER BY name ASC)) x
        WHERE name = 'T2022/349/it87'
    ON CONFLICT DO NOTHING;

-- Create a profile containing the just created topic
INSERT INTO profile (name)
    VALUES ('T2022.349.it87.P')
    ON CONFLICT DO NOTHING;
INSERT INTO "profile-topics" (profile, topic)
    VALUES    ((SELECT profile FROM profile
                    WHERE name = 'T2022.349.it87.P'),
               (SELECT topic FROM topic
                    WHERE name = 'T2022/349/it87'))
    ON CONFLICT DO NOTHING;

SELECT * FROM topic;
SELECT * FROM "topic-sensors";
SELECT * FROM vprofile;
SELECT * FROM "topic-data" ORDER BY timestamp DESC LIMIT 17;


--- End transaction

END;

--- it87.sql ends here
