-- Your SQL goes here
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 channel_preferences (
  id INTEGER NOT NULL PRIMARY KEY REFERENCES channels(id) ON DELETE CASCADE,
  prefix TEXT,
  locale TEXT,
  silent_mode BOOLEAN NOT NULL DEFAULT FALSE,
  markov_responses BOOLEAN NOT NULL DEFAULT FALSE,
  random_markov_responses BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS user_rights (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
  level INTEGER NOT NULL DEFAULT 1,

  CONSTRAINT unique_user_channel UNIQUE (user_id, channel_id)
);

CREATE TABLE IF NOT EXISTS actions (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
  command TEXT NOT NULL,
  arguments TEXT,
  full_message TEXT NOT NULL,
  sent_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS events (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  event_type INTEGER NOT NULL,
  is_massping BOOLEAN NOT NULL DEFAULT FALSE,
  message TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS event_subscriptions (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  event_id INTEGER NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS custom_commands (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  message TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS custom_command_aliases (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  command_id INTEGER NOT NULL REFERENCES custom_commands(id) ON DELETE CASCADE,
  name TEXT NOT NULL,

  CONSTRAINT unique_command_alias UNIQUE (command_id, name)
);

CREATE TABLE IF NOT EXISTS timers (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  message TEXT NOT NULL,
  interval_sec INTEGER NOT NULL,
  last_executed_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
);