gb-lux/sql/migrations/2024-01-22_13-26_init.sql

CREATE TABLE organisations
(
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(50)  NOT NULL,
    role       INT2         NOT NULL DEFAULT 0, -- 0 - client, 1 - Yume, maybe more roles in the future
    created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users
(
    id              SERIAL PRIMARY KEY,
    organisation_id INT4         NOT NULL,
    permission      INT2,
    email           VARCHAR(50),
    first_name      VARCHAR(50),
    last_name       VARCHAR(50),
    photo_url       VARCHAR(200),
    created_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (organisation_id) REFERENCES organisations (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE keys
(
    id             SERIAL PRIMARY KEY,
    user_id        INT4         NOT NULL,
    kind           INT2         NOT NULL,
    identification VARCHAR(50)  NOT NULL,
    secret         VARCHAR(60),
    created_at     TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE sessions
(
    id         SERIAL PRIMARY KEY,
    token      VARCHAR(32)  NOT NULL,
    user_id    INT4         NOT NULL,
    key_id     INT4         NOT NULL,
    expires_at timestamp(3) without time zone,
    created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (key_id) REFERENCES keys (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE projects
(
    id              SERIAL PRIMARY KEY,
    organisation_id INT4         NOT NULL,
    name            VARCHAR(50)  NOT NULL,
    created_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (organisation_id) REFERENCES organisations (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE device_groups
(
    id            SERIAL PRIMARY KEY,
    project_id    INT4         NOT NULL,
    name          VARCHAR(50)  NOT NULL,
    force_status  INT2         NOT NULL DEFAULT 0, -- 0 - no force, 1 - force on, 2 - force off
    lux_threshold INT2,                            -- Triggers the light if the lux level is below this threshold
    created_at    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE device_group_daily_schedule
(
    id              SERIAL PRIMARY KEY,
    device_group_id INT4         NOT NULL,
    day_of_week     INT2         NOT NULL, -- 0 - Sunday, 1 - Monday, 2 - Tuesday, 3 - Wednesday, 4 - Thursday, 5 - Friday, 6 - Saturday
    start_time      TIME         NOT NULL,
    end_time        TIME         NOT NULL,
    created_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_group_id) REFERENCES device_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
);


-- We might need to separate light, gateways and sensors into different tables. For now, let's discuss it.
CREATE TABLE devices
(
    id              SERIAL PRIMARY KEY,
    uuid            VARCHAR(36)  NOT NULL,
    gbtb_id         VARCHAR(50)  NOT NULL,                           -- QUESTION: What type is gbtb_id? Is it a string or an integer?
    device_group_id INT4         NOT NULL,
    name            VARCHAR(50)  NOT NULL,
    lamppost_number VARCHAR(50),                                     -- QUESTION: What type is lamppost_number? Is it a string or an integer?
    index           INT2         NOT NULL DEFAULT 0,                 -- The index of the device in the sequence of devices in the device group / lampposts in the street. Used for ordering & switching on/off alternatively.
    started_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, -- for calculating the uptime/running hours
    lux_level       INT2,
    role            INT2         NOT NULL DEFAULT 0,                 -- 0 - lamppost light, 1 - spike light, 2 - motion sensor, 3 - gateway, maybe more roles in the future

    -- geolocation. We can use Postgres plugin "PostGIS" for this, but it's not necessary for now
    latitude        FLOAT4,
    longitude       FLOAT4,
    altitude        FLOAT4,

    status          INT2         NOT NULL DEFAULT 0,                 -- 0 - off, 1 - on, 2 - fault, maybe more statuses in the future
    created_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_group_id) REFERENCES device_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE measures
(
    id           SERIAL PRIMARY KEY,
    device_id    INT4         NOT NULL,
    measure_type INT2         NOT NULL DEFAULT 0, -- 0 - temperature, 1 - humidity, 2 - light, 3 - motion, 4 - voltage, 5 - power consumption, maybe more in the future
    value        INT4         NOT NULL,
    created_at   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE commands
(
    id              SERIAL PRIMARY KEY,
    organisation_id INT4         NOT NULL,
    originator_id   INT4,
    originator_type INT2         NOT NULL DEFAULT 0, -- 0 - user, 1 - device, 2 - device group, 3 - organisation, maybe more in the future
    command_type    INT2         NOT NULL,           -- 0 - on, 1 - off, 2 - force on, 3 - force off, 4 - reboot, 5 - reset, etc
    comment         VARCHAR(100),
    entity          INT4         NOT NULL,
    entity_type     INT2         NOT NULL DEFAULT 0, -- 0 - device, 1 - device group, 2 - organisation, 3 - user, maybe more in the future
    scheduled_at    TIMESTAMP(3),                    -- NULL if the command is executed immediately
    executed_at     TIMESTAMP(3),                    -- NULL if the command is not executed yet
    created_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (organisation_id) REFERENCES organisations (id) ON DELETE CASCADE ON UPDATE CASCADE
-- No other FKs, because we don't want to delete the history when the entity is deleted
);

CREATE TABLE alerts
(
    id              SERIAL PRIMARY KEY,
    organisation_id INT4         NOT NULL,
    alert_type      INT2         NOT NULL,           -- 0 - temperature, 1 - humidity, 2 - light, 3 - motion, 4 - voltage, 5 - power consumption, maybe more in the future
    entity          INT4         NOT NULL,
    entity_type     INT2         NOT NULL DEFAULT 0, -- 0 - device, 1 - device group, 2 - organisation, 3 - user, maybe more in the future
    created_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (organisation_id) REFERENCES organisations (id) ON DELETE CASCADE ON UPDATE CASCADE
-- No other FKs, because we don't want to delete the history when the entity is deleted
);