CREATE TABLE IF NOT EXISTS channels (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  alias_id INTEGER NOT NULL UNIQUE,
  alias_name TEXT NOT NULL UNIQUE,
  joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
  opted_out_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  alias_id INTEGER NOT NULL UNIQUE,
  alias_name TEXT NOT NULL UNIQUE,
  joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
  opted_out_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS words (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS channel_words (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    word_id BIGINT NOT NULL REFERENCES words(id) ON DELETE CASCADE,
    usage_count BIGINT NOT NULL DEFAULT 0,
    first_used_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
    last_used_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,

    UNIQUE (channel_id, user_id, word_id)
);

CREATE TABLE IF NOT EXISTS emotes (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  platform VARCHAR(16) NOT NULL,
  platform_id VARCHAR(64) NOT NULL,
  UNIQUE (platform, platform_id)
);