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 CONSTRAINT Theme_id REFERENCES Theme(id)
);
CREATE TABLE "Article" (
	"id" int PRIMARY KEY,
	"status" varchar(255),
	"doi" varchar(255),
	"other_id" int,
	"journal_id" int CONSTRAINT Journal_id REFERENCES Journal(id),
	"title" varchar(255),
	"title_translated" varchar(255),
	"subtitle" varchar(255),
	"is_anonymous" bool,
	"pubdate" datetime,
	"pubdate_season" varchar(255),
	"issue_id" varchar(255) CONSTRAINT Issue_id REFERENCES Issue(id),
	"part" varchar(255),
	"first_page" varchar(255),
	"last_page" varchar(255),
	"uri" varchar(255),
	"abstract" varchar(255),
	"abstract_translated" varchar(255)
);
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 CONSTRAINT Institution_id REFERENCES Institution(id),
	"journal_id" int CONSTRAINT Journal_id REFERENCES Journal(id),
	"institution_type_id" int CONSTRAINT InstitutionTypes_id REFERENCES InstitutionTypes(id)
);
CREATE TABLE "Journal_Contact" (
	"id" int PRIMARY KEY,
	"journal_id" int CONSTRAINT Journal_id REFERENCES Journal(id),
	"contact_id" int CONSTRAINT Contact_id REFERENCES Contact(id),
	"contact_type_id" int CONSTRAINT ContactTypes_id REFERENCES ContactTypes(id)
);
CREATE TABLE "Issue" (
	"id" int PRIMARY KEY,
	"journal_id" int CONSTRAINT Journal_journal_id REFERENCES Journal(journal_id),
	"volume" varchar(255),
	"number" varchar(255),
	"title" varchar(255),
	"description" varchar(255),
	"year" varchar(255),
	"date_published" datetime
);
CREATE TABLE "Subject" (
	"id" int PRIMARY KEY,
	"subject" varchar(255),
	"desc" varchar(255)
);
CREATE TABLE "Article_Subject" (
	"id" int PRIMARY KEY,
	"article_id" int CONSTRAINT Article_id REFERENCES Article(id),
	"subject_id" int CONSTRAINT Subject_id REFERENCES Subject(id)
);
CREATE TABLE "Keyword" (
	"id" int PRIMARY KEY,
	"keyword" varchar(255),
	"lang_id" int CONSTRAINT Language_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 CONSTRAINT Article_id REFERENCES Article(id),
	"keyword_id" int CONSTRAINT 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 CONSTRAINT Article_id REFERENCES Article(id),
	"autohor_id" int CONSTRAINT Author_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 CONSTRAINT User_id REFERENCES User(id),
	"role_id" int CONSTRAINT Role_id REFERENCES Role(id)
);
CREATE TABLE "UserJournalRole" (
	"user_id" int CONSTRAINT User_id REFERENCES User(id),
	"journal_id" int CONSTRAINT Journal_id REFERENCES Journal(id),
	"role_id" int CONSTRAINT Role_id REFERENCES Role(id)
);