SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = '+02:00';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

CREATE TABLE `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `publish` date DEFAULT NULL,
  `unpublish` date DEFAULT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  `author` int(11) DEFAULT NULL,
  `author_external` varchar(255) DEFAULT NULL,
  `photographer` int(11) DEFAULT NULL,
  `photographer_external` varchar(255) DEFAULT NULL,
  `views` int(11) NOT NULL,
  `mainphoto` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `author` (`author`),
  KEY `mainphoto` (`mainphoto`),
  KEY `photographer` (`photographer`),
  CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`author`) REFERENCES `users` (`id`),
  CONSTRAINT `articles_ibfk_2` FOREIGN KEY (`mainphoto`) REFERENCES `photos` (`id`) ON DELETE SET NULL,
  CONSTRAINT `articles_ibfk_3` FOREIGN KEY (`photographer`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `articles_articles_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `article` int(11) NOT NULL,
  `tag` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `article_tag` (`article`,`tag`),
  KEY `tag` (`tag`),
  CONSTRAINT `articles_articles_tags_ibfk_1` FOREIGN KEY (`article`) REFERENCES `articles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `articles_articles_tags_ibfk_2` FOREIGN KEY (`tag`) REFERENCES `articles_tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `articles_contents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `article` int(11) NOT NULL,
  `language` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `perex` text NOT NULL,
  `content` mediumtext NOT NULL,
  `source` varchar(255) DEFAULT NULL,
  `source_url` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `article_language` (`article`,`language`),
  KEY `language` (`language`),
  CONSTRAINT `articles_contents_ibfk_1` FOREIGN KEY (`article`) REFERENCES `articles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `articles_contents_ibfk_2` FOREIGN KEY (`language`) REFERENCES `languages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `articles_tags`;
CREATE TABLE `articles_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `show` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `articles_tags_names`;
CREATE TABLE `articles_tags_names` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag` int(11) NOT NULL,
  `language` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tag_language` (`tag`,`language`),
  UNIQUE KEY `language_name` (`language`,`name`),
  CONSTRAINT `articles_tags_names_ibfk_2` FOREIGN KEY (`tag`) REFERENCES `articles_tags` (`id`) ON DELETE CASCADE,
  CONSTRAINT `articles_tags_names_ibfk_1` FOREIGN KEY (`language`) REFERENCES `languages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
