CREATE TABLE age(
	id_age MEDIUMINT NOT NULL AUTO_INCREMENT,
	gene_age varchar(15),
	unique key(id_age,gene_age),
	primary key(id_age)
)ENGINE=Innodb;

CREATE TABLE Family(
	id_family MEDIUMINT,
	figure int(3) NOT NULL,
	unique key(id_family,figure),
	primary key(id_family)
)ENGINE=Innodb;

CREATE TABLE pathway(
	id_pathway MEDIUMINT NOT NULL AUTO_INCREMENT,
	pathway varchar(50),
	unique key(id_pathway,pathway),
	primary key(id_pathway)
)ENGINE=Innodb;

CREATE TABLE network(
	id_network MEDIUMINT NOT NULL AUTO_INCREMENT,
	network varchar(50),
	unique key(id_network,network),
	primary key(id_network)
)ENGINE=Innodb;

CREATE TABLE Tier(
	id_tier MEDIUMINT NOT NULL AUTO_INCREMENT,
	type varchar(15) NOT NULL,
	pmid int(9) NOT NULL,
	primary key(id_tier,type,pmid)
)ENGINE=Innodb;

create table entity(
	id_entity MEDIUMINT NOT NULL AUTO_INCREMENT,
	`Gene Name` varchar(50) NOT NULL,
	`Synonym` varchar(50),
	`Uniprot Id` char(6) NOT NULL,
	`Uniprot Name` varchar(50),
	Description varchar(250),
	`Ensembl Id` varchar(15),
	Organism varchar(15),
	Displacement varchar(10) default NULL,
	class_tree varchar(15) default NULL,
	agreement ENUM ('NA','NO','YES','Unreliable'),
	single_gene_tree varchar(25) default NULL,
	multi_gene_tree ENUM ('NA','+','-') default 'NA',
	sequence text NOT NULL,
	id_age MEDIUMINT NOT NULL references age,
	id_family MEDIUMINT NOT NULL references Family,
	FOREIGN KEY (id_age) REFERENCES age(id_age) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (id_family) REFERENCES Family(id_family) ON UPDATE CASCADE ON DELETE CASCADE,
	PRIMARY KEY(id_entity)
)ENGINE=Innodb;

CREATE TABLE pathway_rel(
	id_entity MEDIUMINT NOT NULL ,
	id_pathway MEDIUMINT NOT NULL,
	primary key(id_pathway,id_entity),
	FOREIGN KEY (id_pathway) REFERENCES pathway(id_pathway) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (id_entity) REFERENCES entity(id_entity) ON UPDATE CASCADE ON DELETE CASCADE,
	unique key(id_entity,id_pathway)
	
)ENGINE=Innodb;

CREATE TABLE network_rel(
	id_entity MEDIUMINT NOT NULL ,
	id_network MEDIUMINT NOT NULL,
	primary key(id_network,id_entity),
	FOREIGN KEY (id_network) REFERENCES network(id_network) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (id_entity) REFERENCES entity(id_entity) ON UPDATE CASCADE ON DELETE CASCADE,
	unique key(id_entity,id_network)
	
)ENGINE=Innodb;

CREATE TABLE Tier_rel(
	id_entity MEDIUMINT NOT NULL ,
	id_tier MEDIUMINT NOT NULL,
	primary key(id_tier,id_entity),
	FOREIGN KEY (id_tier) REFERENCES Tier(id_tier) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (id_entity) REFERENCES entity(id_entity) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=Innodb;

CREATE TABLE pmid(
	id_pmid MEDIUMINT NOT NULL AUTO_INCREMENT,
	pmid int(9) NOT NULL,
	unique key(id_pmid,pmid),
	primary key(id_pmid)
	
)ENGINE=Innodb;

CREATE TABLE pmid_rel(
	id_entity MEDIUMINT NOT NULL ,
	id_pmid MEDIUMINT NOT NULL,
	primary key(id_pmid,id_entity),
	FOREIGN KEY (id_pmid) REFERENCES pmid(id_pmid) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (id_entity) REFERENCES entity(id_entity) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=Innodb;

CREATE TABLE path_network(
	id_pathway MEDIUMINT NOT NULL ,
	id_network MEDIUMINT NOT NULL,
	primary key(id_pathway,id_network),
	FOREIGN KEY (id_pathway) REFERENCES pathway(id_pathway) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (id_network) REFERENCES network(id_network) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=Innodb;

CREATE TABLE orthologs(
	id_org MEDIUMINT NOT NULL,
	org_code char(3) NOT NULL ,
	Organism varchar(50) NOT NULL,
	id_age MEDIUMINT NOT NULL,
	primary key(id_org),
	FOREIGN KEY (id_age) REFERENCES age(id_age) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=Innodb;

CREATE TABLE emergence(
	id_entity MEDIUMINT NOT NULL ,
	id_org MEDIUMINT NOT NULL,
	id_orth varchar(15) NOT NULL,
	link varchar(250) NOT NULL,
	primary key(id_orth),
	FOREIGN KEY (id_org) REFERENCES orthologs(id_org) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (id_entity) REFERENCES entity(id_entity) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=Innodb;

CREATE TABLE modification(
	id_modification MEDIUMINT NOT NULL AUTO_INCREMENT,
	modification varchar(15) NOT NULL,
	unique key(id_modification,modification),
	primary key(id_modification)
	
)ENGINE=Innodb;

CREATE TABLE `PTMs`(
	id_ptm MEDIUMINT NOT NULL AUTO_INCREMENT,
	target_id MEDIUMINT NOT NULL,
	modifier_id MEDIUMINT NOT NULL,
	id_modification MEDIUMINT NOT NULL,
	residue varchar(15) NOT NULL,
	pmid int(12) NOT NULL,
	primary key(id_ptm,target_id,modifier_id,id_modification,residue,pmid),
	FOREIGN KEY (target_id) REFERENCES entity(id_entity) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (modifier_id) REFERENCES entity(id_entity) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (id_modification) REFERENCES modification(id_modification) ON UPDATE CASCADE ON DELETE CASCADE
	
)ENGINE=Innodb;

-- CREATE TABLE residues(
--	id_ptm MEDIUMINT NOT NULL,
--	residue varchar(15) NOT NULL,
--	pmid int(12) NOT NULL,
--	primary key(id_ptm,residue,pmid),
--	FOREIGN KEY (id_ptm) REFERENCES `PTMs`(id_ptm) ON UPDATE CASCADE ON DELETE CASCADE
	
--)ENGINE=Innodb;

CREATE TABLE pdb(
	id_ptm MEDIUMINT NOT NULL,
	pdb_residue varchar(15) NOT NULL,
	pdb varchar(5) NOT NULL,
	pdb_chain varchar(2) NOT NULL,
	start MEDIUMINT NOT NULL DEFAULT 0,
	end MEDIUMINT NOT NULL DEFAULT 0,
	primary key(id_ptm,pdb,pdb_residue,pdb_chain),
	FOREIGN KEY (id_ptm) REFERENCES `PTMs`(id_ptm) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=Innodb;

