-- Copyright (c) 2013-2018 Snowplow Analytics Ltd. All rights reserved.
--
-- This program is licensed to you under the Apache License Version 2.0,
-- and you may not use this file except in compliance with the Apache License Version 2.0.
-- You may obtain a copy of the Apache License Version 2.0 at http://www.apache.org/licenses/LICENSE-2.0.
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the Apache License Version 2.0 is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the Apache License Version 2.0 for the specific language governing permissions and limitations there under.
--
-- Version:     0.11.0
-- URL:         -
--
-- Authors:     Yali Sassoon, Alex Dean, Peter van Wesep, Fred Blundun, Konstantinos Servis, Mike Robins
-- Copyright:   Copyright (c) 2013-2018 Snowplow Analytics Ltd
-- License:     Apache License Version 2.0

-- Create the schema
CREATE SCHEMA atomic;

-- Create events table
CREATE TABLE atomic.events (
	-- App
	app_id varchar(255) encode ZSTD,
	platform varchar(255) encode ZSTD,
	-- Date/time
	etl_tstamp timestamp  encode ZSTD,
	collector_tstamp timestamp not null encode RAW,
	dvce_created_tstamp timestamp encode ZSTD,
	-- Event
	event varchar(128) encode ZSTD,
	event_id char(36) not null unique encode ZSTD,
	txn_id int encode ZSTD,
	-- Namespacing and versioning
	name_tracker varchar(128) encode ZSTD,
	v_tracker varchar(100) encode ZSTD,
	v_collector varchar(100) encode ZSTD not null,
	v_etl varchar(100) encode ZSTD not null,
	-- User and visit
	user_id varchar(255) encode ZSTD,
	user_ipaddress varchar(128) encode ZSTD,
	user_fingerprint varchar(128) encode ZSTD,
	domain_userid varchar(128) encode ZSTD,
	domain_sessionidx int encode ZSTD,
	network_userid varchar(128) encode ZSTD,
	-- Location
	geo_country char(2) encode ZSTD,
	geo_region char(3) encode ZSTD,
	geo_city varchar(75) encode ZSTD,
	geo_zipcode varchar(15) encode ZSTD,
	geo_latitude double precision encode ZSTD,
	geo_longitude double precision encode ZSTD,
	geo_region_name varchar(100) encode ZSTD,
	-- IP lookups
	ip_isp varchar(100) encode ZSTD,
	ip_organization varchar(128) encode ZSTD,
	ip_domain varchar(128) encode ZSTD,
	ip_netspeed varchar(100) encode ZSTD,
	-- Page
	page_url varchar(4096) encode ZSTD,
	page_title varchar(2000) encode ZSTD,
	page_referrer varchar(4096) encode ZSTD,
	-- Page URL components
	page_urlscheme varchar(16) encode ZSTD,
	page_urlhost varchar(255) encode ZSTD,
	page_urlport int encode ZSTD,
	page_urlpath varchar(3000) encode ZSTD,
	page_urlquery varchar(6000) encode ZSTD,
	page_urlfragment varchar(3000) encode ZSTD,
	-- Referrer URL components
	refr_urlscheme varchar(16) encode ZSTD,
	refr_urlhost varchar(255) encode ZSTD,
	refr_urlport int encode ZSTD,
	refr_urlpath varchar(6000) encode ZSTD,
	refr_urlquery varchar(6000) encode ZSTD,
	refr_urlfragment varchar(3000) encode ZSTD,
	-- Referrer details
	refr_medium varchar(25) encode ZSTD,
	refr_source varchar(50) encode ZSTD,
	refr_term varchar(255) encode ZSTD,
	-- Marketing
	mkt_medium varchar(255) encode ZSTD,
	mkt_source varchar(255) encode ZSTD,
	mkt_term varchar(255) encode ZSTD,
	mkt_content varchar(500) encode ZSTD,
	mkt_campaign varchar(255) encode ZSTD,
	-- Custom structured event
	se_category varchar(1000) encode ZSTD,
	se_action varchar(1000) encode ZSTD,
	se_label varchar(4096) encode ZSTD,
	se_property varchar(1000) encode ZSTD,
	se_value double precision encode ZSTD,
	-- Ecommerce
	tr_orderid varchar(255) encode ZSTD,
	tr_affiliation varchar(255) encode ZSTD,
	tr_total dec(18,2) encode ZSTD,
	tr_tax dec(18,2) encode ZSTD,
	tr_shipping dec(18,2) encode ZSTD,
	tr_city varchar(255) encode ZSTD,
	tr_state varchar(255) encode ZSTD,
	tr_country varchar(255) encode ZSTD,
	ti_orderid varchar(255) encode ZSTD,
	ti_sku varchar(255) encode ZSTD,
	ti_name varchar(255) encode ZSTD,
	ti_category varchar(255) encode ZSTD,
	ti_price dec(18,2) encode ZSTD,
	ti_quantity int encode ZSTD,
	-- Page ping
	pp_xoffset_min int encode ZSTD,
	pp_xoffset_max int encode ZSTD,
	pp_yoffset_min int encode ZSTD,
	pp_yoffset_max int encode ZSTD,
	-- User Agent
	useragent varchar(1000) encode ZSTD,
	-- Browser
	br_name varchar(50) encode ZSTD,
	br_family varchar(50) encode ZSTD,
	br_version varchar(50) encode ZSTD,
	br_type varchar(50) encode ZSTD,
	br_renderengine varchar(50) encode ZSTD,
	br_lang varchar(255) encode ZSTD,
	br_features_pdf boolean encode ZSTD,
	br_features_flash boolean encode ZSTD,
	br_features_java boolean encode ZSTD,
	br_features_director boolean encode ZSTD,
	br_features_quicktime boolean encode ZSTD,
	br_features_realplayer boolean encode ZSTD,
	br_features_windowsmedia boolean encode ZSTD,
	br_features_gears boolean encode ZSTD,
	br_features_silverlight boolean encode ZSTD,
	br_cookies boolean encode ZSTD,
	br_colordepth varchar(12) encode ZSTD,
	br_viewwidth int encode ZSTD,
	br_viewheight int encode ZSTD,
	-- Operating System
	os_name varchar(50) encode ZSTD,
	os_family varchar(50)  encode ZSTD,
	os_manufacturer varchar(50)  encode ZSTD,
	os_timezone varchar(255)  encode ZSTD,
	-- Device/Hardware
	dvce_type varchar(50)  encode ZSTD,
	dvce_ismobile boolean encode ZSTD,
	dvce_screenwidth int encode ZSTD,
	dvce_screenheight int encode ZSTD,
	-- Document
	doc_charset varchar(128) encode ZSTD,
	doc_width int encode ZSTD,
	doc_height int encode ZSTD,

	-- Currency
	tr_currency char(3) encode ZSTD,
	tr_total_base dec(18, 2) encode ZSTD,
	tr_tax_base dec(18, 2) encode ZSTD,
	tr_shipping_base dec(18, 2) encode ZSTD,
	ti_currency char(3) encode ZSTD,
	ti_price_base dec(18, 2) encode ZSTD,
	base_currency char(3) encode ZSTD,

	-- Geolocation
	geo_timezone varchar(64) encode ZSTD,

	-- Click ID
	mkt_clickid varchar(128) encode ZSTD,
	mkt_network varchar(64) encode ZSTD,

	-- ETL tags
	etl_tags varchar(500) encode ZSTD,

	-- Time event was sent
	dvce_sent_tstamp timestamp encode ZSTD,

	-- Referer
	refr_domain_userid varchar(128) encode ZSTD,
	refr_dvce_tstamp timestamp encode ZSTD,

	-- Session ID
	domain_sessionid char(128) encode ZSTD,

	-- Derived timestamp
	derived_tstamp timestamp encode ZSTD,

	-- Event schema
	event_vendor varchar(1000) encode ZSTD,
	event_name varchar(1000) encode ZSTD,
	event_format varchar(128) encode ZSTD,
	event_version varchar(128) encode ZSTD,

	-- Event fingerprint
	event_fingerprint varchar(128) encode ZSTD,

	-- True timestamp
	true_tstamp timestamp encode ZSTD,

	CONSTRAINT event_id_0110_pk PRIMARY KEY(event_id)
)
DISTSTYLE KEY
DISTKEY (event_id)
SORTKEY (collector_tstamp);

COMMENT ON TABLE "atomic"."events" IS '0.11.0';
