Create Database and Tables

DROP DATABASE IF EXISTS martech;
CREATE DATABASE martech;
USE martech;

CREATE ROWSTORE REFERENCE TABLE cities (
  city_id bigint(20) NOT NULL,
  city_name text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  center geographypoint NOT NULL,
  diameter double DEFAULT NULL,
  PRIMARY KEY (city_id)
); 

CREATE TABLE locations (
  city_id bigint(20) NOT NULL,
  subscriber_id bigint(20) NOT NULL,
  ts datetime(6) NOT NULL SERIES TIMESTAMP,
  lonlat geographypoint NOT NULL,
  olc_8 text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  SHARD KEY __SHARDKEY (city_id,subscriber_id),
  SORT KEY ts (ts),
  KEY city_id (city_id,subscriber_id) USING HASH,
  KEY olc_8 (olc_8) USING HASH
);

CREATE TABLE notifications (
  ts datetime(6) NOT NULL SERIES TIMESTAMP,
  city_id bigint(20) NOT NULL,
  subscriber_id bigint(20) NOT NULL,
  offer_id bigint(20) NOT NULL,
  cost_cents bigint(20) NOT NULL,
  lonlat geographypoint NOT NULL,
  SHARD KEY __SHARDKEY (city_id,subscriber_id),
  SORT KEY ts (ts)
);

CREATE ROWSTORE REFERENCE TABLE offers (
  offer_id bigint(20) NOT NULL AUTO_INCREMENT,
  customer text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  enabled tinyint(1) NOT NULL DEFAULT 1,
  notification_zone geography NOT NULL,
  segment_ids JSON COLLATE utf8mb4_bin NOT NULL,
  notification_content text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  notification_target text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  maximum_bid_cents bigint(20) NOT NULL,
  PRIMARY KEY (offer_id),
  KEY customer (customer),
  KEY notification_target (notification_target),
  KEY notification_zone (notification_zone) WITH (RESOLUTION = 8)
);

CREATE TABLE purchases (
  city_id bigint(20) NOT NULL,
  subscriber_id bigint(20) NOT NULL,
  ts datetime(6) NOT NULL SERIES TIMESTAMP,
  vendor text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  SHARD KEY __SHARDKEY (city_id,subscriber_id),
  SORT KEY ts (ts),
  KEY vendor (vendor) USING HASH
);

CREATE TABLE requests (
  city_id bigint(20) NOT NULL,
  subscriber_id bigint(20) NOT NULL,
  ts datetime(6) NOT NULL SERIES TIMESTAMP,
  domain text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  SHARD KEY __SHARDKEY (city_id,subscriber_id),
  SORT KEY ts (ts),
  KEY domain (domain) USING HASH
);

CREATE ROWSTORE REFERENCE TABLE segments (
  segment_id bigint(20) NOT NULL,
  valid_interval enum('minute','hour','day','week','month') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  filter_kind enum('olc_8','request','purchase') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  filter_value text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (segment_id),
  UNIQUE KEY valid_interval (valid_interval,filter_kind,filter_value),
  KEY filter_kind (filter_kind,filter_value)
);

CREATE ROWSTORE TABLE sessions (
  session_id text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  is_controller tinyint(1) NOT NULL DEFAULT 0,
  expires_at datetime(6) NOT NULL,
  PRIMARY KEY (session_id)
);

CREATE ROWSTORE TABLE subscriber_segments (
  city_id bigint(20) NOT NULL,
  subscriber_id bigint(20) NOT NULL,
  segment_id bigint(20) NOT NULL,
  expires_at datetime(6) NOT NULL,
  PRIMARY KEY (city_id,subscriber_id,segment_id),
  SHARD KEY city_id (city_id,subscriber_id)
);

CREATE ROWSTORE TABLE subscribers (
  city_id bigint(20) NOT NULL,
  subscriber_id bigint(20) NOT NULL,
  current_location geographypoint NOT NULL,
  PRIMARY KEY (city_id,subscriber_id),
  KEY current_location (current_location)
);

CREATE ROWSTORE TABLE subscribers_last_notification (
  city_id bigint(20) NOT NULL,
  subscriber_id bigint(20) NOT NULL,
  last_notification datetime(6) DEFAULT NULL,
  PRIMARY KEY (city_id,subscriber_id),
  KEY last_notification (last_notification)
);

CREATE ROWSTORE TABLE worldcities (
  city_id bigint(20) NOT NULL,
  city_name text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  center geographypoint NOT NULL,
  PRIMARY KEY (city_id),
  KEY center (center)
);