-- Base schema copied from cloud-beta.
-- Creates application tables, indexes and TimescaleDB hypertable for history.

CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE SCHEMA IF NOT EXISTS public;

CREATE TABLE IF NOT EXISTS public.edge (
  id varchar(100) NOT NULL,
  name text NOT NULL,
  parent_id varchar(100),
  CONSTRAINT edge_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.tag (
  id varchar(100) NOT NULL,
  name text NOT NULL,
  min double precision,
  max double precision,
  comment text NOT NULL,
  unit_of_measurement text NOT NULL,
  precision integer,
  tag_group varchar(255),
  CONSTRAINT tag_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.current (
  id integer GENERATED BY DEFAULT AS IDENTITY,
  edge varchar(100) NOT NULL,
  tag varchar(100) NOT NULL,
  value double precision,
  "createdAt" timestamp(3) without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
  "updatedAt" timestamp(3) without time zone NOT NULL,
  CONSTRAINT current_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.history (
  edge varchar(100) NOT NULL,
  "timestamp" timestamp(3) without time zone NOT NULL,
  tag varchar(100) NOT NULL,
  value double precision NOT NULL,
  "createdAt" timestamp(3) without time zone DEFAULT now() NOT NULL
);

SELECT create_hypertable(
  'public.history',
  'timestamp',
  chunk_time_interval => INTERVAL '1 day',
  if_not_exists => TRUE
);

CREATE TABLE IF NOT EXISTS public.camera (
  edge varchar NOT NULL,
  protocol varchar NOT NULL,
  source varchar NOT NULL,
  CONSTRAINT camera_pkey PRIMARY KEY (edge, protocol, source)
);

CREATE UNIQUE INDEX IF NOT EXISTS current_edge_tag_key
  ON public.current USING btree (edge, tag);

CREATE INDEX IF NOT EXISTS current_edge_idx
  ON public.current USING btree (edge);

CREATE INDEX IF NOT EXISTS current_tag_idx
  ON public.current USING btree (tag);

CREATE INDEX IF NOT EXISTS history_edge_tag_timestamp_idx
  ON public.history USING btree (edge, tag, "timestamp" DESC);

CREATE INDEX IF NOT EXISTS camera_edge_idx
  ON public.camera USING btree (edge);