
CREATE TABLE `author` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(100) NOT NULL,
  `image` varchar(260) NULL,
  `email` varchar(256) NOT NULL
) ENGINE='InnoDB';

CREATE TABLE `category` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(100) NOT NULL,
  `position` tinyint unsigned NOT NULL DEFAULT '0'
) ENGINE='InnoDB';

CREATE TABLE `post` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `title` varchar(100) NOT NULL,
  `resume` varchar(600) NOT NULL,
  `content` text NOT NULL,
  `published_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `views` mediumint unsigned NOT NULL DEFAULT '0'
) ENGINE='InnoDB';

-- UNIQUE email

ALTER TABLE `author`
ADD UNIQUE `email` (`email`);

-- ajout de données

INSERT INTO `author` (`name`, `image`, `email`)
VALUES ('Matthieu Mehdi', 'mm_avatar.jpg', 'matthieumehdi@gmail.com');

INSERT INTO `author` (`name`, `image`, `email`) VALUES
('Vincent', 'vinz.jpg', 'vincent@yahoo.com'),
('Julie', NULL, 'julie@gmail.com'),
('Alexandre', NULL, 'alexandre@oclock.io'),
('Xavier', NULL, 'xavier@oclock.io'),
('Lucie', NULL, 'lucie@oclock.io');

INSERT INTO `category` (`id`, `name`, `position`) VALUES
(1, 'Ma vie de dev', 4),
(2, 'Teamfront', 1),
(3, 'Teamback', 2),
(4, 'Collaboration', 3);
