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")
);