text: --
  -- Copyright © 2016-2018 The Thingsboard Authors
  -- Modifications © 2017-2018 Hashmap, Inc
  --
  -- Licensed under the Apache License, Version 2.0 (the "License");
  -- you may not use this file except in compliance with the License.
  -- You may obtain a copy of the License at
  --
  --     http://www.apache.org/licenses/LICENSE-2.0
  --
  -- Unless required by applicable law or agreed to in writing, software
  -- distributed under the License is distributed on an "AS IS" BASIS,
  -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  -- See the License for the specific language governing permissions and
  -- limitations under the License.
  --
  
  DROP MATERIALIZED VIEW IF EXISTS tempus.device_by_tenant_and_name;
  DROP MATERIALIZED VIEW IF EXISTS tempus.device_by_tenant_and_search_text;
  DROP MATERIALIZED VIEW IF EXISTS tempus.device_by_tenant_by_type_and_search_text;
  DROP MATERIALIZED VIEW IF EXISTS tempus.device_by_customer_and_search_text;
  DROP MATERIALIZED VIEW IF EXISTS tempus.device_by_customer_by_type_and_search_text;
  DROP MATERIALIZED VIEW IF EXISTS tempus.device_types_by_tenant;
  
  DROP TABLE IF EXISTS tempus.device;
  
  CREATE TABLE IF NOT EXISTS tempus.device (
      id timeuuid,
      tenant_id timeuuid,
      customer_id timeuuid,
      name text,
      type text,
      search_text text,
      additional_info text,
      PRIMARY KEY (id, tenant_id, customer_id, type)
  );
  
  CREATE MATERIALIZED VIEW IF NOT EXISTS tempus.device_by_tenant_and_name AS
      SELECT *
      from tempus.device
      WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND name IS NOT NULL AND id IS NOT NULL
      PRIMARY KEY ( tenant_id, name, id, customer_id, type)
      WITH CLUSTERING ORDER BY ( name ASC, id DESC, customer_id DESC);
  
  CREATE MATERIALIZED VIEW IF NOT EXISTS tempus.device_by_tenant_and_search_text AS
      SELECT *
      from tempus.device
      WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
      PRIMARY KEY ( tenant_id, search_text, id, customer_id, type)
      WITH CLUSTERING ORDER BY ( search_text ASC, id DESC, customer_id DESC);
  
  CREATE MATERIALIZED VIEW IF NOT EXISTS tempus.device_by_tenant_by_type_and_search_text AS
      SELECT *
      from tempus.device
      WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
      PRIMARY KEY ( tenant_id, type, search_text, id, customer_id)
      WITH CLUSTERING ORDER BY ( type ASC, search_text ASC, id DESC, customer_id DESC);
  
  CREATE MATERIALIZED VIEW IF NOT EXISTS tempus.device_by_customer_and_search_text AS
      SELECT *
      from tempus.device
      WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
      PRIMARY KEY ( customer_id, tenant_id, search_text, id, type )
      WITH CLUSTERING ORDER BY ( tenant_id DESC, search_text ASC, id DESC );
  
  CREATE MATERIALIZED VIEW IF NOT EXISTS tempus.device_by_customer_by_type_and_search_text AS
      SELECT *
      from tempus.device
      WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
      PRIMARY KEY ( customer_id, tenant_id, type, search_text, id )
      WITH CLUSTERING ORDER BY ( tenant_id DESC, type ASC, search_text ASC, id DESC );
  
  DROP MATERIALIZED VIEW IF EXISTS tempus.asset_by_tenant_and_name;
  DROP MATERIALIZED VIEW IF EXISTS tempus.asset_by_tenant_and_search_text;
  DROP MATERIALIZED VIEW IF EXISTS tempus.asset_by_tenant_by_type_and_search_text;
  DROP MATERIALIZED VIEW IF EXISTS tempus.asset_by_customer_and_search_text;
  DROP MATERIALIZED VIEW IF EXISTS tempus.asset_by_customer_by_type_and_search_text;
  DROP MATERIALIZED VIEW IF EXISTS tempus.asset_types_by_tenant;
  
  DROP TABLE IF EXISTS tempus.asset;
  
  CREATE TABLE IF NOT EXISTS tempus.asset (
      id timeuuid,
      tenant_id timeuuid,
      customer_id timeuuid,
      name text,
      type text,
      search_text text,
      additional_info text,
      PRIMARY KEY (id, tenant_id, customer_id, type)
  );
  
  CREATE MATERIALIZED VIEW IF NOT EXISTS tempus.asset_by_tenant_and_name AS
      SELECT *
      from tempus.asset
      WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND name IS NOT NULL AND id IS NOT NULL
      PRIMARY KEY ( tenant_id, name, id, customer_id, type)
      WITH CLUSTERING ORDER BY ( name ASC, id DESC, customer_id DESC);
  
  CREATE MATERIALIZED VIEW IF NOT EXISTS tempus.asset_by_tenant_and_search_text AS
      SELECT *
      from tempus.asset
      WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
      PRIMARY KEY ( tenant_id, search_text, id, customer_id, type)
      WITH CLUSTERING ORDER BY ( search_text ASC, id DESC, customer_id DESC);
  
  CREATE MATERIALIZED VIEW IF NOT EXISTS tempus.asset_by_tenant_by_type_and_search_text AS
      SELECT *
      from tempus.asset
      WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
      PRIMARY KEY ( tenant_id, type, search_text, id, customer_id)
      WITH CLUSTERING ORDER BY ( type ASC, search_text ASC, id DESC, customer_id DESC);
  
  CREATE MATERIALIZED VIEW IF NOT EXISTS tempus.asset_by_customer_and_search_text AS
      SELECT *
      from tempus.asset
      WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
      PRIMARY KEY ( customer_id, tenant_id, search_text, id, type )
      WITH CLUSTERING ORDER BY ( tenant_id DESC, search_text ASC, id DESC );
  
  CREATE MATERIALIZED VIEW IF NOT EXISTS tempus.asset_by_customer_by_type_and_search_text AS
      SELECT *
      from tempus.asset
      WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
      PRIMARY KEY ( customer_id, tenant_id, type, search_text, id )
      WITH CLUSTERING ORDER BY ( tenant_id DESC, type ASC, search_text ASC, id DESC );
  
  CREATE TABLE IF NOT EXISTS tempus.entity_subtype (
      tenant_id timeuuid,
      entity_type text, // (DEVICE, ASSET)
      type text,
      PRIMARY KEY (tenant_id, entity_type, type)
  );
  
  CREATE TABLE IF NOT EXISTS tempus.alarm (
      id timeuuid,
      tenant_id timeuuid,
      type text,
      originator_id timeuuid,
      originator_type text,
      severity text,
      status text,
      start_ts bigint,
      end_ts bigint,
      ack_ts bigint,
      clear_ts bigint,
      details text,
      propagate boolean,
      PRIMARY KEY ((tenant_id, originator_id, originator_type), type, id)
  ) WITH CLUSTERING ORDER BY ( type ASC, id DESC);
  
  CREATE MATERIALIZED VIEW IF NOT EXISTS tempus.alarm_by_id AS
      SELECT *
      from tempus.alarm
      WHERE tenant_id IS NOT NULL AND originator_id IS NOT NULL AND originator_type IS NOT NULL AND type IS NOT NULL
      AND type IS NOT NULL AND id IS NOT NULL
      PRIMARY KEY (id, tenant_id, originator_id, originator_type, type)
      WITH CLUSTERING ORDER BY ( tenant_id ASC, originator_id ASC, originator_type ASC, type ASC);
  
  DROP MATERIALIZED VIEW IF EXISTS tempus.relation_by_type_and_child_type;
  DROP MATERIALIZED VIEW IF EXISTS tempus.reverse_relation;
  
  DROP TABLE IF EXISTS tempus.relation;
  
  CREATE TABLE IF NOT EXISTS tempus.relation (
      from_id timeuuid,
      from_type text,
      to_id timeuuid,
      to_type text,
      relation_type_group text,
      relation_type text,
      additional_info text,
      PRIMARY KEY ((from_id, from_type), relation_type_group, relation_type, to_id, to_type)
  ) WITH CLUSTERING ORDER BY ( relation_type_group ASC, relation_type ASC, to_id ASC, to_type ASC);
  
  CREATE MATERIALIZED VIEW IF NOT EXISTS tempus.relation_by_type_and_child_type AS
      SELECT *
      from tempus.relation
      WHERE from_id IS NOT NULL AND from_type IS NOT NULL AND relation_type_group IS NOT NULL AND relation_type IS NOT NULL AND to_id IS NOT NULL AND to_type IS NOT NULL
      PRIMARY KEY ((from_id, from_type), relation_type_group, relation_type, to_type, to_id)
      WITH CLUSTERING ORDER BY ( relation_type_group ASC, relation_type ASC, to_type ASC, to_id DESC);
  
  CREATE MATERIALIZED VIEW IF NOT EXISTS tempus.reverse_relation AS
      SELECT *
      from tempus.relation
      WHERE from_id IS NOT NULL AND from_type IS NOT NULL AND relation_type_group IS NOT NULL AND relation_type IS NOT NULL AND to_id IS NOT NULL AND to_type IS NOT NULL
      PRIMARY KEY ((to_id, to_type), relation_type_group, relation_type, from_id, from_type)
      WITH CLUSTERING ORDER BY ( relation_type_group ASC, relation_type ASC, from_id ASC, from_type ASC);
  