--- create.sql  -*- SQL -*-
-- Create the 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.5  2022-12-14
--      (kind_sensor_data_warn_tf): Fixed: qualify table names used
--      with explicit schemas.

-- 0.4  2022-12-14 17:55:48Z
--      (sfn.rJluuqN123ieRjqLQ2bfZqiJi1Rj6wQZykGQsRyM0hg.sql)
--      (vsensor): Fixed: specify explicit columns to join on (was:
--      using natural join; where extra columns, such as description
--      and name, were getting in the way.)  Include all currently
--      declared columns of the tables involved in the result.
--      (vbunch_insert): Replace rule if already exists.
--      (vsensor_insert): Likewise.
--      (ksen-warning-log): Do not create table if already exists.

-- 0.3  2022-12-14 16:24:31Z
--      (sfn.y5BW8QF4SzRk1Rb57ow3CavKZHTefgcEU29-0N3he00.sql)
--      No longer group sensor values into readings, as that provides
--      little benefit while increasing complexity and precluding
--      efficient table partitioning.
--      (sensor): Put unique constraint on bunch, name (was: name.)
--      (vbunch): New convenience view.
--      (vsensor): Also include bunch-name and kind-name columns.
--      (vbunch_insert): New rule.
--      (vsensor_insert): Likewise.
--      (reading): Removed table.
--      (bool-sensor-data): New timestamp column replaces reading,
--      and is now, together with sensor, the primary key.
--      (kind-sensor-data): Likewise.
--      (sensor-data): Updated accordingly.
--      (sensor_data_modify_tf): Likewise.
--      (sensor_data_modify_tf): Fixed: qualify table names used
--      with explicit schemas.

-- 0.2  2022-11-25 15:55:45Z
--      (sfn.XUhU0qozO259Se2hZvwuViKmUY4e9E6OY9Srhudq70M.sql)
--      (sensor_data_modify_tf): Fixed: return NEW (was: missing.)
--      (kind_sensor_data_warn_tf): Likewise.
--      (kind_sensor_data_warn_tf): Fixed: disambiguate threshold
--      as the column of the row being deleted.

-- 0.1  2022-11-23 19:25:42Z
--      (sfn.PFnx82JtqlRJ2z0Vo2opolD_1PzPbjKZGio8fYxOK-M.sql)
--      Initial revision.

--- Code:

BEGIN;

CREATE SCHEMA IF NOT EXISTS hodlo;



--- Sensors

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

CREATE TABLE IF NOT EXISTS bunch (
    bunch       INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name        TEXT NOT NULL UNIQUE,
    -- NB: we cannot DEFAULT parent to bunch here, hence no NOT NULL
    parent      INTEGER REFERENCES bunch (bunch),
    description TEXT
);

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

CREATE TABLE IF NOT EXISTS "kind-sensor" (
    sensor      INTEGER REFERENCES sensor (sensor) UNIQUE NOT NULL,
    kind        INTEGER REFERENCES kind (kind) NOT NULL
);


--- Convenience views

CREATE OR REPLACE VIEW vbunch
    AS  SELECT b.*, p.name AS "parent-name"
            FROM bunch b
                    LEFT OUTER JOIN bunch p ON (b.parent = p.bunch);

CREATE OR REPLACE RULE vbunch_insert
    AS ON INSERT TO vbunch
    DO INSTEAD
        INSERT INTO bunch (name, parent, description)
            VALUES (NEW.name,
                    COALESCE  (NEW.parent,
                               (SELECT bunch FROM bunch
                                    WHERE name = NEW."parent-name")),
                    NEW.description);

CREATE OR REPLACE VIEW vsensor
    AS  SELECT  s.*, kind,
                b.name AS "bunch-name",
                b.parent AS "bunch-parent",
                b.description AS "bunch-description",
                k.name AS "kind-name",
                k.description AS "kind-description"
            FROM sensor s
                    INNER JOIN bunch b USING (bunch)
                    NATURAL LEFT OUTER JOIN "kind-sensor"
                    LEFT OUTER JOIN kind k USING (kind);

CREATE OR REPLACE RULE vsensor_insert
    AS ON INSERT TO vsensor
    DO INSTEAD
       (INSERT INTO sensor (name, bunch, description)
            VALUES (NEW.name,
                    COALESCE  (NEW.bunch,
                               (SELECT bunch FROM bunch
                                    WHERE name = NEW."bunch-name")),
                    NEW.description);
        INSERT INTO "kind-sensor" (sensor, kind)
            SELECT  lastval (), k.kind
                FROM kind k
                WHERE  (NEW.kind = k.kind
                        OR NEW."kind-name" = k.name));


--- Sensor readings

CREATE TABLE IF NOT EXISTS "bool-sensor-data" (
    timestamp   TIMESTAMP NOT NULL DEFAULT now (),
    sensor      INTEGER REFERENCES sensor  (sensor)  NOT NULL,
    bvalue      BOOLEAN NOT NULL,
    PRIMARY KEY (timestamp, sensor)
);

CREATE TABLE IF NOT EXISTS "kind-sensor-data" (
    timestamp   TIMESTAMP NOT NULL DEFAULT now (),
    sensor      INTEGER REFERENCES "kind-sensor" (sensor) NOT NULL,
    dvalue      DOUBLE PRECISION NOT NULL,
    PRIMARY KEY (timestamp, sensor)
);

CREATE OR REPLACE VIEW "sensor-data"
    AS  SELECT *
            FROM "bool-sensor-data"
                NATURAL FULL OUTER JOIN "kind-sensor-data";

CREATE OR REPLACE FUNCTION sensor_data_modify_tf ()
    RETURNS trigger AS $$
    BEGIN
        IF NEW.bvalue IS NOT NULL THEN
            INSERT INTO hodlo."bool-sensor-data" (timestamp, sensor, bvalue)
                VALUES (NEW.timestamp, NEW.sensor, NEW.bvalue);
        END IF;
        IF NEW.dvalue IS NOT NULL THEN
            INSERT INTO hodlo."kind-sensor-data" (timestamp, sensor, dvalue)
                VALUES (NEW.timestamp, NEW.sensor, NEW.dvalue);
        END IF;
        RETURN NEW;
    END;
$$  LANGUAGE plpgsql;

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


--- Warning thresholds

CREATE TABLE IF NOT EXISTS threshold (
    threshold   INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    sensor      INTEGER REFERENCES "kind-sensor" (sensor) NOT NULL,
    assert      DOUBLE PRECISION NOT NULL,
    release     DOUBLE PRECISION NOT NULL,
    description TEXT,
    CHECK (assert <> release)
);

CREATE TABLE IF NOT EXISTS "ksen-warning" (
    threshold   INTEGER REFERENCES threshold (threshold) NOT NULL,
    updated     TIMESTAMP NOT NULL DEFAULT now (),
    UNIQUE (threshold)
);

CREATE OR REPLACE FUNCTION kind_sensor_data_warn_tf ()
    RETURNS trigger AS $$
    BEGIN
        DELETE FROM hodlo."ksen-warning"
            USING hodlo.threshold v
            WHERE  ("ksen-warning".threshold = v.threshold
                    AND sensor = NEW.sensor
                    AND CASE WHEN v.assert < v.release
                            THEN v.release < NEW.dvalue
                            ELSE v.release > NEW.dvalue
                        END);
        INSERT INTO hodlo."ksen-warning" (threshold)
            SELECT threshold
                FROM hodlo.threshold v
                WHERE  (sensor = NEW.sensor
                        AND CASE WHEN v.assert > v.release
                                THEN  v.assert < NEW.dvalue
                                ELSE  v.assert > NEW.dvalue
                            END)
            ON CONFLICT (threshold)
                DO UPDATE SET updated = DEFAULT;
        RETURN NEW;
    END;
$$  LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER "kind-sensor-data-warn"
    AFTER INSERT OR UPDATE ON "kind-sensor-data"
    FOR EACH ROW EXECUTE FUNCTION kind_sensor_data_warn_tf ();


--- Warning log

CREATE TABLE IF NOT EXISTS "ksen-warning-log" (
    threshold   INTEGER REFERENCES threshold (threshold) NOT NULL,
    start       TIMESTAMP NOT NULL,
    stop        TIMESTAMP NOT NULL,
    updated     TIMESTAMP NOT NULL DEFAULT now (),
    CHECK (start <= stop AND stop <= updated)
);


--- End transaction

END;

--- create.sql ends here
