ALTER TYPE twinoid_user_id RENAME TO twinoid_user_id_old;
-- Twinoid id
CREATE DOMAIN twinoid_user_id AS u32;
-- User id local to a site
CREATE DOMAIN twinoid_site_user_id AS u32;
CREATE DOMAIN twinoid_site_id AS u32;
CREATE DOMAIN twinoid_site_info_id AS u32;
CREATE DOMAIN twinoid_site_name AS VARCHAR(32);
CREATE DOMAIN twinoid_site_host AS VARCHAR(32);
CREATE DOMAIN twinoid_site_icon_tag AS VARCHAR(32);
CREATE DOMAIN twinoid_stat_key AS VARCHAR(128);
CREATE DOMAIN twinoid_achievement_key AS VARCHAR(128);
CREATE DOMAIN url AS VARCHAR(2048);
CREATE DOMAIN html_fragment AS TEXT;
CREATE TYPE twinoid_site_status AS ENUM ('hide', 'soon', 'beta', 'open');
CREATE TYPE twinoid_site_icon_type AS ENUM ('icon');
CREATE TYPE twinoid_gender AS ENUM ('female', 'male');

CREATE TYPE old_twinoid_user_display_name AS (
  "value" twinoid_user_display_name,
  "until" instant
);
CREATE DOMAIN old_twinoid_user_display_name_array AS old_twinoid_user_display_name[];

-- This is a separate table so we can attach download
CREATE TABLE remote_image (
  "url" url NOT NULL,
  "created_at" INSTANT NOT NULL,
  PRIMARY KEY (url)
);

CREATE TABLE twinoid_site (
  "twinoid_site_id" twinoid_site_id NOT NULL,
  "retrieved_at" INSTANT NOT NULL,
  PRIMARY KEY (twinoid_site_id)
);

CREATE TABLE twinoid_site_history (
  "period" period_lower NOT NULL,
  "retrieved_at" instant_set NOT NULL,
  "twinoid_site_id" twinoid_site_id NOT NULL,
  "name" twinoid_site_name NOT NULL,
  "host" twinoid_site_host NOT NULL,
  "icon" url NOT NULL,
  "locale" locale_id NULL,
  "like_url" url NOT NULL,
  "like_title" html_fragment NULL,
  "status" twinoid_site_status NOT NULL,
  PRIMARY KEY (period, twinoid_site_id),
  EXCLUDE USING gist (twinoid_site_id WITH =, period WITH &&),
  CONSTRAINT site__twinoid_site__fk FOREIGN KEY (twinoid_site_id) REFERENCES twinoid_site(twinoid_site_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT icon__remote_image__fk FOREIGN KEY (icon) REFERENCES remote_image(url) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE twinoid_site_like_count (
  "period" period_lower NOT NULL,
  "retrieved_at" instant_set NOT NULL,
  "twinoid_site_id" twinoid_site_id NOT NULL,
  "like_count" u32 NOT NULL,
  PRIMARY KEY (period, twinoid_site_id),
  EXCLUDE USING gist (twinoid_site_id WITH =, period WITH &&),
  CONSTRAINT site__twinoid_site__fk FOREIGN KEY (twinoid_site_id) REFERENCES twinoid_site(twinoid_site_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE twinoid_site_info (
  "period" period_lower NOT NULL,
  "retrieved_at" instant_set NOT NULL,
  "twinoid_site_info_id" twinoid_site_info_id NOT NULL,
  "twinoid_site_id" twinoid_site_id NOT NULL,
  "locale" locale_id NOT NULL,
  "cover" url NOT NULL,
  "tag_line" html_fragment NULL,
  "description" html_fragment NOT NULL,
  "tid" html_fragment NOT NULL,
  PRIMARY KEY (period, twinoid_site_info_id),
  EXCLUDE USING gist (twinoid_site_info_id WITH =, period WITH &&),
  EXCLUDE USING gist (twinoid_site_id WITH =, locale WITH =, period WITH &&),
  CONSTRAINT cover__remote_image__fk FOREIGN KEY (cover) REFERENCES remote_image(url) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT site__twinoid_site__fk FOREIGN KEY (twinoid_site_id) REFERENCES twinoid_site(twinoid_site_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE twinoid_site_icon (
  "period" period_lower NOT NULL,
  "retrieved_at" instant_set NOT NULL,
  "twinoid_site_id" twinoid_site_id NOT NULL,
  "tag" twinoid_site_icon_tag NOT NULL,
  "alt_tag" twinoid_site_icon_tag NULL,
  "url" url NOT NULL,
  "type" twinoid_site_icon_type NOT NULL,
  "_rank" u32 NOT NULL,
  PRIMARY KEY (period, twinoid_site_id, tag),
  EXCLUDE USING gist (twinoid_site_id WITH =, _rank WITH =, period WITH &&),
  CONSTRAINT icon__remote_image__fk FOREIGN KEY (url) REFERENCES remote_image(url) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT site__twinoid_site__fk FOREIGN KEY (twinoid_site_id) REFERENCES twinoid_site(twinoid_site_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE twinoid_user (
  "twinoid_user_id" twinoid_user_id NOT NULL,
  "retrieved_at" INSTANT NOT NULL,
  PRIMARY KEY (twinoid_user_id)
);

CREATE TABLE twinoid_user_history (
  "period" period_lower NOT NULL,
  "retrieved_at" instant_set NOT NULL,
  "twinoid_user_id" twinoid_user_id NOT NULL,
  "picture" url NULL,
  "locale" locale_id NOT NULL,
  "title" html_fragment NULL,
  "like_url" url NOT NULL,
  "like_count" u32 NOT NULL,
  "gender" twinoid_gender NULL,
  "birthday" date NULL,
  PRIMARY KEY (period, twinoid_user_id),
  EXCLUDE USING gist (twinoid_user_id WITH =, period WITH &&),
  CONSTRAINT user__twinoid_user__fk FOREIGN KEY (twinoid_user_id) REFERENCES twinoid_user(twinoid_user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT picture__remote_image__fk FOREIGN KEY (picture) REFERENCES remote_image(url) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- One way contact state, `friend` in the Twinoid API implies a two-way contact.
-- `is_contact` indicates the state
-- - `true`: user `twinoid_user_id` added `target` to its contact
-- - `false`: user `twinoid_user_id` removed `target` from its contact
-- No entry means "unknown", so `false` is a stronger statement.
CREATE TABLE twinoid_contact (
  "period" period_lower NOT NULL,
  "retrieved_at" instant_set NOT NULL,
  "twinoid_user_id" twinoid_user_id NOT NULL,
  "target" twinoid_user_id NOT NULL,
  "is_contact" bool NOT NULL,
  PRIMARY KEY (period, "twinoid_user_id", "target"),
  EXCLUDE USING gist ("twinoid_user_id" WITH =, "target" WITH =, period WITH &&),
  CONSTRAINT user__twinoid_user__fk FOREIGN KEY ("twinoid_user_id") REFERENCES twinoid_user(twinoid_user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT target__twinoid_user__fk FOREIGN KEY ("target") REFERENCES twinoid_user(twinoid_user_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE twinoid_user_display_name_history (
  "period" period_lower NOT NULL,
  "retrieved_at" instant_set NOT NULL,
  "twinoid_user_id" twinoid_user_id NOT NULL,
  "display_name" twinoid_user_display_name NOT NULL,
  PRIMARY KEY (period, "twinoid_user_id"),
  EXCLUDE USING gist ("twinoid_user_id" WITH =, period WITH &&),
  CONSTRAINT user__twinoid_user__fk FOREIGN KEY ("twinoid_user_id") REFERENCES twinoid_user(twinoid_user_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Fields that can be controlled by the user (potentially breaking the API due to encoding issues)
CREATE TABLE twinoid_user_custom_fields (
  "period" period_lower NOT NULL,
  "retrieved_at" instant_set NOT NULL,
  "twinoid_user_id" twinoid_user_id NOT NULL,
  "description" html_fragment NULL,
  "status" html_fragment NULL,
  "old_names" old_twinoid_user_display_name[] NOT NULL,
  "city" text NULL,
  "country" text NULL,
  PRIMARY KEY (period, "twinoid_user_id"),
  EXCLUDE USING gist ("twinoid_user_id" WITH =, period WITH &&),
  CONSTRAINT user__twinoid_user__fk FOREIGN KEY ("twinoid_user_id") REFERENCES twinoid_user(twinoid_user_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Immutable site user
CREATE TABLE twinoid_site_user (
  "twinoid_site_id" twinoid_site_id NOT NULL,
  "twinoid_site_user_id" twinoid_site_user_id NOT NULL,
  "retrieved_at" INSTANT NOT NULL,
  PRIMARY KEY (twinoid_site_id, twinoid_site_user_id),
  CONSTRAINT site__twinoid_site__fk FOREIGN KEY ("twinoid_site_id") REFERENCES twinoid_site(twinoid_site_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Link from a Twinoid user to a site user.
CREATE TABLE twinoid_site_user_link (
  "period" period_lower NOT NULL,
  "retrieved_at" instant_set NOT NULL,
  "twinoid_user_id" twinoid_user_id NOT NULL,
  "twinoid_site_id" twinoid_site_id NOT NULL,
  -- `null` if not linked
  "twinoid_site_user_id" twinoid_site_user_id NULL,
  PRIMARY KEY (period, "twinoid_user_id", "twinoid_site_id"),
  EXCLUDE USING gist ("twinoid_user_id" WITH =, "twinoid_site_id" WITH =, period WITH &&),
  EXCLUDE USING gist ("twinoid_site_id" WITH =, "twinoid_site_user_id" WITH =, period WITH &&),
  CONSTRAINT user__twinoid_user__fk FOREIGN KEY ("twinoid_user_id") REFERENCES twinoid_user(twinoid_user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT site_user__twinoid_site_user__fk FOREIGN KEY ("twinoid_site_id", "twinoid_site_user_id") REFERENCES twinoid_site_user("twinoid_site_id", "twinoid_site_user_id") ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE twinoid_stat (
  "twinoid_site_id" twinoid_site_id NOT NULL,
  "twinoid_stat_key" twinoid_stat_key NOT NULL,
  "name" text NOT NULL,
  "description" html_fragment NULL,
  "icon" url NULL,
  "rare" i32 NOT NULL,
  "social" boolean NOT NULL,
  PRIMARY KEY ("twinoid_site_id", "twinoid_stat_key"),
  CONSTRAINT site__twinoid_site__fk FOREIGN KEY ("twinoid_site_id") REFERENCES twinoid_site("twinoid_site_id") ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT icon__remote_image__fk FOREIGN KEY ("icon") REFERENCES remote_image("url") ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE twinoid_achievement (
  "twinoid_site_id" twinoid_site_id NOT NULL,
  "twinoid_achievement_key" twinoid_achievement_key NOT NULL,
  "name" text NOT NULL,
  "stat" twinoid_stat_key NOT NULL,
  "score" i32 NOT NULL,
  "points" i32 NOT NULL,
  "npoints" float8 NOT NULL,
  "description" html_fragment NOT NULL,
  "rank" i32 NOT NULL,
  "data_type" text NOT NULL,
  "data_title" html_fragment NULL,
  "data_url" url NULL,
  "data_prefix" boolean NULL,
  "data_suffix" boolean NULL,
  PRIMARY KEY ("twinoid_site_id", "twinoid_achievement_key"),
  CONSTRAINT site__twinoid_site__fk FOREIGN KEY ("twinoid_site_id") REFERENCES twinoid_site("twinoid_site_id") ON DELETE RESTRICT ON UPDATE CASCADE,
-- Some achievements do not have a corresponding stat exposed in the API (such as the achievement `2_hippo_0` with stat `hippo`
-- CONSTRAINT stat__twinoid_stat__fk FOREIGN KEY ("twinoid_site_id", "stat") REFERENCES twinoid_stat("twinoid_site_id", "twinoid_stat_key") ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT data_url__remote_image__fk FOREIGN KEY ("data_url") REFERENCES remote_image("url") ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE DOMAIN twinoid_user_stat_map_id AS UUID;

-- Immutable stats (may be shared by different users)
CREATE TABLE twinoid_user_stat_map (
  "twinoid_user_stat_map_id" twinoid_user_stat_map_id NOT NULL,
  "twinoid_site_id" twinoid_site_id NOT NULL,
-- sha3_256(utf8(json(value)))
-- Where `value` is a map from the twinoid_stat_key to the score, sorted by key and json does not use any whitespace
-- {"bar":0,"foo":9}. It is prefixed by the site id.
  _sha3_256 BYTEA NOT NULL,
  PRIMARY KEY (twinoid_user_stat_map_id),
  UNIQUE (_sha3_256),
  UNIQUE (twinoid_user_stat_map_id, twinoid_site_id),
  CONSTRAINT site__twinoid_site__fk FOREIGN KEY ("twinoid_site_id") REFERENCES twinoid_site("twinoid_site_id") ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Content of twinoid_user_stat_map
CREATE TABLE twinoid_user_stat_map_item (
  twinoid_user_stat_map_id twinoid_user_stat_map_id NOT NULL,
  twinoid_site_id twinoid_site_id NOT NULL,
  twinoid_stat_key twinoid_stat_key NOT NULL,
  score i32 NOT NULL,
  PRIMARY KEY (twinoid_user_stat_map_id, twinoid_stat_key),
  CONSTRAINT twinoid_user_stat_map_item__map__fk FOREIGN KEY (twinoid_user_stat_map_id, twinoid_site_id) REFERENCES twinoid_user_stat_map(twinoid_user_stat_map_id, twinoid_site_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT twinoid_user_stat_map_item__achievement__fk FOREIGN KEY (twinoid_site_id, twinoid_stat_key) REFERENCES twinoid_stat(twinoid_site_id, twinoid_stat_key) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE DOMAIN twinoid_user_achievement_set_id AS UUID;

-- Immutable unlocked items state (may be shared by different users)
CREATE TABLE twinoid_user_achievement_set (
  "twinoid_user_achievement_set_id" twinoid_user_achievement_set_id NOT NULL,
  "twinoid_site_id" twinoid_site_id NOT NULL,
-- sha3_256(utf8(json(value)))
-- Where `value` is a sorted list of twinoid_achievement_key and json does not
-- use any whitespace. It is prefixed by the site id.
-- [1,["bar","foo"]]
  "_sha3_256" BYTEA NOT NULL,
  PRIMARY KEY ("twinoid_user_achievement_set_id"),
  UNIQUE ("_sha3_256"),
  UNIQUE (twinoid_user_achievement_set_id, twinoid_site_id),
  CONSTRAINT site__twinoid_site__fk FOREIGN KEY ("twinoid_site_id") REFERENCES twinoid_site("twinoid_site_id") ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Content of twinoid_user_achievement_set
CREATE TABLE twinoid_user_achievement_set_item (
  twinoid_user_achievement_set_id twinoid_user_achievement_set_id NOT NULL,
  twinoid_site_id twinoid_site_id NOT NULL,
  twinoid_achievement_key twinoid_achievement_key NOT NULL,
  PRIMARY KEY (twinoid_user_achievement_set_id, twinoid_achievement_key),
  CONSTRAINT twinoid_user_achievement_set_item__set__fk FOREIGN KEY (twinoid_user_achievement_set_id, twinoid_site_id) REFERENCES twinoid_user_achievement_set(twinoid_user_achievement_set_id, twinoid_site_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT twinoid_user_achievement_set_item__achievement__fk FOREIGN KEY (twinoid_site_id, twinoid_achievement_key) REFERENCES twinoid_achievement(twinoid_site_id, twinoid_achievement_key) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE twinoid_site_user_history (
  "period" period_lower NOT NULL,
  "retrieved_at" instant_set NOT NULL,
  "twinoid_site_id" twinoid_site_id NOT NULL,
  "twinoid_site_user_id" twinoid_site_user_id NOT NULL,
  "link" text NULL,
  "stats" twinoid_user_stat_map_id NULL,
  "achievements" twinoid_user_achievement_set_id NULL,
  "points" i32 NULL,
  "npoints" float8 NULL,
  PRIMARY KEY (period, "twinoid_site_id", "twinoid_site_user_id"),
  EXCLUDE USING gist ("twinoid_site_id" WITH =, "twinoid_site_user_id" WITH =, period WITH &&),
  CONSTRAINT user__twinoid_site_user__fk FOREIGN KEY ("twinoid_site_id", "twinoid_site_user_id") REFERENCES twinoid_site_user("twinoid_site_id", "twinoid_site_user_id") ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT stat_map__fk FOREIGN KEY ("stats", "twinoid_site_id") REFERENCES twinoid_user_stat_map("twinoid_user_stat_map_id", "twinoid_site_id") ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT achievement_set__fk FOREIGN KEY ("achievements", "twinoid_site_id") REFERENCES twinoid_user_achievement_set("twinoid_user_achievement_set_id", "twinoid_site_id") ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE twinoid_user_achievement_unlock_time (
  "twinoid_site_id" twinoid_site_id NOT NULL,
  "twinoid_site_user_id" twinoid_site_user_id NOT NULL,
  "twinoid_achievement_key" twinoid_achievement_key NOT NULL,
  "unlocked_at" instant NULL,
  PRIMARY KEY ("twinoid_site_id", "twinoid_achievement_key"),
  CONSTRAINT achievement_unlock__achievement__fk FOREIGN KEY ("twinoid_site_id", "twinoid_achievement_key") REFERENCES twinoid_achievement("twinoid_site_id", "twinoid_achievement_key") ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT user__twinoid_site_user__fk FOREIGN KEY ("twinoid_site_id", "twinoid_site_user_id") REFERENCES twinoid_site_user("twinoid_site_id", "twinoid_site_user_id") ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE twinoid_access_token (
  period PERIOD_LOWER NOT NULL,
  retrieved_at INSTANT_SET NOT NULL,
  _twinoid_access_token_hash BYTEA NOT NULL,
--
  twinoid_user_id twinoid_user_id NOT NULL,
  expired_at INSTANT NOT NULL,
--
  twinoid_access_token BYTEA NOT NULL,
  PRIMARY KEY (period, _twinoid_access_token_hash),
  EXCLUDE USING gist (_twinoid_access_token_hash WITH =, period WITH &&),
  EXCLUDE USING gist (twinoid_user_id WITH =, period WITH &&),
  CONSTRAINT twinoid_access_token__twinoid_user__fk FOREIGN KEY (twinoid_user_id) REFERENCES twinoid_user(twinoid_user_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE twinoid_refresh_token (
  period PERIOD_LOWER NOT NULL,
  retrieved_at INSTANT_SET NOT NULL,
  _twinoid_refresh_token_hash BYTEA NOT NULL,
--
  twinoid_user_id twinoid_user_id NOT NULL,
--
  twinoid_refresh_token BYTEA NOT NULL,
  PRIMARY KEY (period, _twinoid_refresh_token_hash),
  EXCLUDE USING gist (_twinoid_refresh_token_hash WITH =, period WITH &&),
  EXCLUDE USING gist (twinoid_user_id WITH =, period WITH &&),
  CONSTRAINT twinoid_refresh_token__twinoid_user__fk FOREIGN KEY (twinoid_user_id) REFERENCES twinoid_user(twinoid_user_id) ON DELETE RESTRICT ON UPDATE CASCADE
);