USE alibaba;
DROP TABLE IF EXISTS `graphs`;
DROP TABLE IF EXISTS `nodes`;
DROP TABLE IF EXISTS `edges`;
DROP TABLE IF EXISTS `texts`;
DROP TABLE IF EXISTS `synonyms`;
DROP TABLE IF EXISTS `item_has_texts`;


DROP DATABASE alibaba;
DROP USER biologe@localhost;


CREATE DATABASE alibaba;
USE alibaba;

CREATE TABLE graphs (
	graphid		INT NOT NULL AUTO_INCREMENT, 
	name	    	VARCHAR(255) NOT NULL,
	description 	TEXT,
	date		DATE,
	PRIMARY KEY (graphid,name)
);

CREATE TABLE nodes (
	nodeid		INT NOT NULL AUTO_INCREMENT,
	graphid		int not null,
	graphnumber int not null,
	label		VARCHAR(255),
	entitytype	INT,
	x 		DOUBLE,
	y		DOUBLE,
	entityid	TEXT,
	deprecated	INT,
	primary key (nodeid)

);

CREATE TABLE edges (
	edgeid 		INT NOT NULL AUTO_INCREMENT,
	graphid		INT NOT NULL,
	graphnumber 	INT NOT NULL,
	label		VARCHAR(255),
	score		FLOAT,
	direction	INT,
	assotype	VARCHAR(255),
	sourcenode	INT NOT NULL,
	targetnode  	INT NOT NULL,
	deprecated	INT,	
	PRIMARY KEY (edgeid)
);

CREATE TABLE item_has_texts (
	itemid		INT NOT NULL,
	pubmedid	VARCHAR(255),
	sentno		INT NOT NULL,
	itemtype	INT
);

CREATE TABLE synonyms (
	synonymid	INT NOT NULL AUTO_INCREMENT,
	nodeid		INT NOT NULL,
	name		VARCHAR(255),
	PRIMARY KEY (synonymid)
);

CREATE TABLE texts (
	graphid		INT NOT NULL,
	pubmedid	VARCHAR(255),
	sentno		INT NOT NULL,
	text		TEXT,
	PRIMARY KEY (graphid,pubmedid,sentno)
);







CREATE USER biologe@localhost IDENTIFIED BY 'ecoli';
GRANT ALL ON kegg_db.* TO biologe@localhost;
GRANT ALL ON alibaba.* TO biologe@localhost;

# delete kegg reaction systems, which have no reactions
use kegg_db;
delete from reaction_system where sys_id not in (select sys_id from sys_contains_reac group by sys_id);