DROP SCHEMA IF EXISTS `blog-app`;
CREATE SCHEMA `blog-app`;
USE `blog-app`;

CREATE TABLE users (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(32)  NOT NULL UNIQUE,
  email        VARCHAR(254) NOT NULL UNIQUE,
  password     BINARY(60)   NOT NULL UNIQUE
);

CREATE TABLE active_tokens (
  id    INT UNSIGNED PRIMARY KEY,
  token CHAR(40) NOT NULL,
  FOREIGN KEY (id) REFERENCES users(id) ON DELETE CASCADE
                                        ON UPDATE CASCADE
);

CREATE TABLE forget_tokens (
  id    INT UNSIGNED PRIMARY KEY,
  token CHAR(40) NOT NULL,
  FOREIGN KEY (id) REFERENCES users(id) ON DELETE CASCADE
                                        ON UPDATE CASCADE
);

CREATE TABLE verify_tokens (
  id    INT UNSIGNED PRIMARY KEY,
  token CHAR(40) NOT NULL,
  FOREIGN KEY (id) REFERENCES users(id) ON DELETE CASCADE
                                        ON UPDATE CASCADE
);

CREATE TABLE posts (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title     VARCHAR(255) NOT NULL,
  content   TEXT         NOT NULL,
  author    VARCHAR(32)  NOT NULL,
  create_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
                                  ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (author) REFERENCES users(name) ON DELETE CASCADE
                                              ON UPDATE CASCADE
);

CREATE TABLE comments (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content    TEXT         NOT NULL,
  author     VARCHAR(32)  NOT NULL,
  create_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_at TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
                                   ON UPDATE CURRENT_TIMESTAMP,
  comment_to INT UNSIGNED NOT NULL,
  FOREIGN KEY (author)     REFERENCES users(name) ON DELETE CASCADE
                                                  ON UPDATE CASCADE,
  FOREIGN KEY (comment_to) REFERENCES posts(id)   ON DELETE CASCADE
);

CREATE TABLE tags (
  id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  keyword VARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE post_tag (
  post_id INT UNSIGNED NOT NULL,
  tag_id  INT UNSIGNED NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id)  REFERENCES tags(id)  ON DELETE CASCADE
);