CREATE TABLE article (
  id int(11) NOT NULL,
  title varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  content text COLLATE utf8mb4_unicode_ci NOT NULL,
  published_at datetime DEFAULT NULL,
  image_file varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE article_category (
  article_id int(11) NOT NULL,
  category_id int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE category (
  id int(11) NOT NULL,
  name varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user` (
  id int(11) NOT NULL,
  username varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  password varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


ALTER TABLE article
  ADD PRIMARY KEY (id),
  ADD KEY title (title);

ALTER TABLE article_category
  ADD PRIMARY KEY (article_id,category_id),
  ADD KEY category_id (category_id);

ALTER TABLE category
  ADD PRIMARY KEY (id),
  ADD KEY name (name);

ALTER TABLE `user`
  ADD PRIMARY KEY (id),
  ADD UNIQUE KEY username (username);


ALTER TABLE article
  MODIFY id int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE category
  MODIFY id int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `user`
  MODIFY id int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE article_category
  ADD CONSTRAINT article_category_ibfk_1 FOREIGN KEY (article_id) REFERENCES article (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT article_category_ibfk_2 FOREIGN KEY (category_id) REFERENCES category (id) ON DELETE CASCADE ON UPDATE CASCADE;