-- DMs
CREATE TABLE dms_temp_old(
  "id" varchar(255) NOT NULL,
  "user" varchar(255) NOT NULL,
  "service" varchar(20) NOT NULL,
  "content" text NOT NULL DEFAULT '',
  "embed" jsonb NOT NULL DEFAULT '{}',
  "added" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "published" timestamp,
  "file" jsonb NOT NULL,
  PRIMARY KEY (id, service)
);

CREATE TABLE unapproved_dms_temp_old(
  "import_id" varchar(255) NOT NULL,
  contributor_id varchar(255),
  "id" varchar(255) NOT NULL,
  "user" varchar(255) NOT NULL,
  "service" varchar(20) NOT NULL,
  "content" text NOT NULL DEFAULT '',
  "embed" jsonb NOT NULL DEFAULT '{}',
  "added" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "published" timestamp,
  "file" jsonb NOT NULL,
  PRIMARY KEY (id, service)
);

-- the old tables weren't dropped, apparently

CREATE TABLE dms(
  "hash" varchar NOT NULL,
  "user" varchar(255) NOT NULL,
  service varchar(20) NOT NULL,
  "content" text NOT NULL DEFAULT ''::text,
  embed jsonb NOT NULL DEFAULT '{}' ::jsonb,
  added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  published timestamp NULL,
  file jsonb NOT NULL,
  CONSTRAINT dms_pkey PRIMARY KEY ("hash", "user", service)
);

CREATE TABLE unapproved_dms(
  "hash" varchar NOT NULL,
  "user" varchar(255) NOT NULL,
  service varchar(20) NOT NULL,
  contributor_id varchar(255) NOT NULL,
  "content" text NOT NULL DEFAULT ''::text,
  embed jsonb NOT NULL DEFAULT '{}' ::jsonb,
  added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  published timestamp NULL,
  file jsonb NOT NULL,
  import_id varchar(255) NOT NULL,
  remote_user_id_hash varchar NULL,
  deleted_at timestamp NULL,
  CONSTRAINT unapproved_dms_pkey PRIMARY KEY ("hash", "user", service, contributor_id)
);

CREATE INDEX dm_idx ON dms_temp_old USING btree("user");

CREATE INDEX dms_user_idx ON dms_temp_old("user");

CREATE INDEX unapproved_dm_idx ON unapproved_dms USING btree("import_id");

CREATE INDEX unapproved_dms_contributor_id_user_idx ON unapproved_dms(contributor_id, "user");