CREATE TABLE `Journal` (
	`id` int PRIMARY KEY,
	`title` varchar(255),
	`title_abbr` varchar(255),
	`title_translated` varchar(255),
	`subtitle` varchar(255),
	`issn` varchar(255),
	`eissn` varchar(255),
	`first_publish_date` datetime,
	`period` varchar(255),
	`url` varchar(255),
	`country` int,
	`publish_status` int,
	`status` int,
	`cover_image` varchar(255),
	`mission` longtext,
	`scope` longtext,
	`theme_id` int,
	FOREIGN KEY(`theme_id`) REFERENCES `Theme` (`id`)
);
CREATE TABLE `Article` (
	`id` int PRIMARY KEY,
	`status` varchar(255),
	`doi` varchar(255),
	`other_id` int,
	`journal_id` int,
	`title` varchar(255),
	`title_translated` varchar(255),
	`subtitle` varchar(255),
	`is_anonymous` bool,
	`pubdate` datetime,
	`pubdate_season` varchar(255),
	`issue_id` varchar(255),
	`part` varchar(255),
	`first_page` varchar(255),
	`last_page` varchar(255),
	`uri` varchar(255),
	`abstract` varchar(255),
	`abstract_translated` varchar(255),
	FOREIGN KEY(`journal_id`) REFERENCES `Journal` (`id`),
	FOREIGN KEY(`issue_id`) REFERENCES `Issue` (`id`)
);
CREATE TABLE `Institution` (
	`id` int PRIMARY KEY,
	`name` varchar(255),
	`address` varchar(255),
	`country` int,
	`address_lat` varchar(255),
	`adress_long` varchar(255),
	`phone` varchar(255),
	`fax` varchar(255),
	`email` varchar(255)
);
CREATE TABLE `Contact` (
	`id` int PRIMARY KEY,
	`title` varchar(255),
	`first_name` varchar(255),
	`last_name` varchar(255),
	`address` varchar(255),
	`country` int,
	`city` varchar(255),
	`phone` varchar(255),
	`fax` varchar(255),
	`email` varchar(255)
);
CREATE TABLE `InstitutionTypes` (
	`id` int PRIMARY KEY,
	`name` varchar(255),
	`description` varchar(255)
);
CREATE TABLE `ContactTypes` (
	`id` int PRIMARY KEY,
	`name` varchar(255),
	`description` varchar(255)
);
CREATE TABLE `Journal_Institution` (
	`id` int,
	`institution_id` int,
	`journal_id` int,
	`institution_type_id` int,
	FOREIGN KEY(`institution_id`) REFERENCES `Institution` (`id`),
	FOREIGN KEY(`journal_id`) REFERENCES `Journal` (`id`),
	FOREIGN KEY(`institution_type_id`) REFERENCES `InstitutionTypes` (`id`)
);
CREATE TABLE `Journal_Contact` (
	`id` int PRIMARY KEY,
	`journal_id` int,
	`contact_id` int,
	`contact_type_id` int,
	FOREIGN KEY(`journal_id`) REFERENCES `Journal` (`id`),
	FOREIGN KEY(`contact_id`) REFERENCES `Contact` (`id`),
	FOREIGN KEY(`contact_type_id`) REFERENCES `ContactTypes` (`id`)
);
CREATE TABLE `Issue` (
	`id` int PRIMARY KEY,
	`journal_id` int,
	`volume` varchar(255),
	`number` varchar(255),
	`title` varchar(255),
	`description` varchar(255),
	`year` varchar(255),
	`date_published` datetime,
	FOREIGN KEY(`journal_id`) REFERENCES `Journal` (`journal_id`)
);
CREATE TABLE `Subject` (
	`id` int PRIMARY KEY,
	`subject` varchar(255),
	`desc` varchar(255)
);
CREATE TABLE `Article_Subject` (
	`id` int PRIMARY KEY,
	`article_id` int,
	`subject_id` int,
	FOREIGN KEY(`article_id`) REFERENCES `Article` (`id`),
	FOREIGN KEY(`subject_id`) REFERENCES `Subject` (`id`)
);
CREATE TABLE `Keyword` (
	`id` int PRIMARY KEY,
	`keyword` varchar(255),
	`lang_id` int,
	FOREIGN KEY(`lang_id`) REFERENCES `Language` (`id`)
);
CREATE TABLE `Language` (
	`id` int,
	`language` varchar(255),
	`language_translated` varchar(255),
	`code` varchar(255)
);
CREATE TABLE `Article_Keyword` (
	`id` int,
	`article_id` int,
	`keyword_id` int,
	FOREIGN KEY(`article_id`) REFERENCES `Article` (`id`),
	FOREIGN KEY(`keyword_id`) REFERENCES `Keyword` (`id`)
);
CREATE TABLE `Author` (
	`id` int PRIMARY KEY,
	`name` varchar(255),
	`lastname` varchar(255),
	`middle_name` varchar(255),
	`name_translated` varchar(255),
	`middlename_translated` varchar(255),
	`lastname_translated` varchar(255),
	`initials` varchar(255),
	`email` varchar(255),
	`address` varchar(255),
	`institution_id` int,
	`country_id` int,
	`summary` varchar(255)
);
CREATE TABLE `Article_Author` (
	`id` int PRIMARY KEY,
	`article_id` int,
	`autohor_id` int,
	FOREIGN KEY(`article_id`) REFERENCES `Article` (`id`),
	FOREIGN KEY(`autohor_id`) REFERENCES `Author` (`id`)
);
CREATE TABLE `Theme` (
	`id` int PRIMARY KEY,
	`name` varchar(255),
	`content` longtext,
	`base_theme` bool
);
CREATE TABLE `User` (
	`id` int PRIMARY KEY,
	`username` varchar(255),
	`password` varchar(255),
	`email` varchar(255),
	`isActive` bool
);
CREATE TABLE `Role` (
	`id` int,
	`name` varchar(255),
	`role` varchar(255)
);
CREATE TABLE `User_Role` (
	`user_id` int,
	`role_id` int,
	FOREIGN KEY(`user_id`) REFERENCES `User` (`id`),
	FOREIGN KEY(`role_id`) REFERENCES `Role` (`id`)
);
CREATE TABLE `UserJournalRole` (
	`user_id` int,
	`journal_id` int,
	`role_id` int,
	FOREIGN KEY(`user_id`) REFERENCES `User` (`id`),
	FOREIGN KEY(`journal_id`) REFERENCES `Journal` (`id`),
	FOREIGN KEY(`role_id`) REFERENCES `Role` (`id`)
);