-- Posts (Discord)
CREATE TABLE discord_channels(
  channel_id text NOT NULL,
  server_id text NOT NULL,
  name text NOT NULL,
  parent_channel_id text NULL,
  topic text NULL,
  theme_color text NULL,
  is_nsfw bool NOT NULL,
  position int NOT NULL DEFAULT 0,
  icon_emoji text NULL,
  type int NOT NULL DEFAULT 0,
  CONSTRAINT discord_channels_pkey PRIMARY KEY (channel_id)
);

CREATE TABLE discord_posts(
  "id" varchar(255) NOT NULL,
  "author" jsonb NOT NULL,
  "server" varchar(255) NOT NULL,
  "channel" varchar(255) NOT NULL,
  "content" text NOT NULL DEFAULT '',
  "added" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "published" timestamp,
  "edited" timestamp,
  "embeds" jsonb[] NOT NULL,
  "mentions" jsonb[] NOT NULL,
  "attachments" jsonb[] NOT NULL,
  PRIMARY KEY (id, SERVER, channel)
);

CREATE TABLE discord_posts_revisions(
  revision_id serial4 NOT NULL,
  id varchar(255) NOT NULL,
  author jsonb NOT NULL,
  "server" varchar(255) NOT NULL,
  channel varchar(255) NOT NULL,
  "content" text NOT NULL DEFAULT ''::text,
  added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  published timestamp NULL,
  edited timestamp NULL,
  embeds _jsonb NOT NULL,
  mentions _jsonb NOT NULL,
  attachments _jsonb NOT NULL,
  CONSTRAINT discord_posts_revisions_pkey PRIMARY KEY (revision_id)
);

CREATE INDEX discord_channels_server_id_idx ON discord_channels USING btree(server_id);

CREATE INDEX discord_channels_parent_channel_id_idx ON discord_channels USING btree(parent_channel_id);

CREATE INDEX discord_id_idx ON discord_posts USING HASH ("id");

CREATE INDEX server_idx ON discord_posts USING HASH ("server");

CREATE INDEX discord_posts_server_channel_idx ON discord_posts USING btree(SERVER, channel);

CREATE INDEX discord_posts_channel_published_idx ON discord_posts USING btree(channel, published);

CREATE INDEX discord_posts_revisions_id_idx ON public.discord_posts_revisions USING btree(id);