'
BEGIN TRANSACTION;

--
-- Table: articles
--
CREATE TABLE articles (
  body varchar NOT NULL,
  createdAt datetime NOT NULL,
  description varchar NOT NULL,
  id INTEGER PRIMARY KEY NOT NULL,
  slug varchar NOT NULL,
  title varchar NOT NULL,
  updatedAt datetime NOT NULL,
  author_id int NOT NULL,
  FOREIGN KEY (author_id) REFERENCES users(id)
);

CREATE INDEX pk_id ON articles (id);

CREATE INDEX unique_slug ON articles (slug);

CREATE UNIQUE INDEX articles_idx ON articles (slug);

--
-- Table: article_tag_lists
--
CREATE TABLE article_tag_lists (
  id INTEGER PRIMARY KEY NOT NULL,
  value varchar NOT NULL,
  tagList_id int NOT NULL,
  FOREIGN KEY (tagList_id) REFERENCES articles(id)
);

CREATE INDEX pk_id02 ON article_tag_lists (id);

--
-- Table: comments
--
CREATE TABLE comments (
  body varchar NOT NULL,
  createdAt datetime NOT NULL,
  id INTEGER PRIMARY KEY NOT NULL,
  updatedAt datetime NOT NULL,
  author_id int NOT NULL,
  FOREIGN KEY (author_id) REFERENCES users(id)
);

CREATE INDEX pk_id03 ON comments (id);

--
-- Table: users
--
CREATE TABLE users (
  bio varchar NOT NULL,
  email varchar NOT NULL,
  id INTEGER PRIMARY KEY NOT NULL,
  image varchar NOT NULL,
  password_hash varchar NOT NULL,
  username varchar NOT NULL
);

CREATE INDEX pk_id04 ON users (id);

CREATE INDEX unique_username ON users (username);

CREATE UNIQUE INDEX users_idx ON users (username);

--
-- Table: user_follow_user_follows
--
CREATE TABLE user_follow_user_follows (
  follow_from_id int NOT NULL,
  follow_to_id int NOT NULL,
  PRIMARY KEY (follow_from_id, follow_to_id),
  FOREIGN KEY (follow_to_id) REFERENCES users(id),
  FOREIGN KEY (follow_from_id) REFERENCES users(id)
);

CREATE INDEX pk_follow_from_id_follow_to_id ON user_follow_user_follows (follow_from_id, follow_to_id);

COMMIT;'