##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS Taxonomy (
       taxonomyId     INT(10) NOT NULL,
       name	VARCHAR(255) DEFAULT NULL,		
 
       PRIMARY KEY (taxonomyId)
);


##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS Scaffold (
  scaffoldId 	int(10) unsigned NOT NULL auto_increment,
  chr_num 	int(10) unsigned default '1',
  isCircular 	int(1) default '1',
  length 	int(10) unsigned default NULL,
  file 		varchar(32) default NULL,
  isGenomic 	int(1) default NULL,
  gi int(10) 	unsigned default NULL,
  taxonomyId 	int(10) default '0',
  comment 	varchar(255) default NULL,
  isActive	int(1) default '1',

  PRIMARY KEY  (scaffoldId),
  KEY Indx_Scaffold_file (file)
);  

##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS Position (
       posId          INT UNSIGNED NOT NULL AUTO_INCREMENT ,
       strand         CHAR(1), /* could be enum */
       start          INT UNSIGNED,
       stop           INT UNSIGNED,
       scaffoldId     INT UNSIGNED,
       objectId	      INT(2) UNSIGNED DEFAULT 1,			

       PRIMARY KEY (posId),
       FOREIGN KEY (scaffoldId) REFERENCES Scaffold (scaffoldId)
);

##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS ObjectDescr (
	objectId INT(2) UNSIGNED NOT NULL,
	description VARCHAR(255),

	PRIMARY KEY(objectId)
);

INSERT INTO ObjectDescr (objectId, description) values (1, 'ORF');

##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS ORF (
       orfId INT UNSIGNED NOT NULL,
       version INT(2) UNSIGNED NOT NULL DEFAULT 1,
       priority TINYINT UNSIGNED,
       created DATE,
       locusId INT UNSIGNED,
       posId INT UNSIGNED,
       evidence  VARCHAR(20),
       cogInfoId INT UNSIGNED, -- to be deleted


       PRIMARY KEY (orfId, version),
       	
       FOREIGN KEY (posId) REFERENCES Position (posId),

       KEY orfId (orfId),
       KEY orfId_version (orfId, version)	
);

##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS COG (
	orfId INT UNSIGNED NOT NULL,
	version INT(2) UNSIGNED NOT NULL DEFAULT 1,
	cogInfoId INT UNSIGNED NOT NULL,
	
	FOREIGN KEY (cogInfoId) REFERENCES COGInfo (cogInfoId),
	FOREIGN KEY (orfId, version) REFERENCES ORF (orfId, version)
		
	
);

##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS COGInfo (
	cogInfoId	INT UNSIGNED NOT NULL,
	funCode		CHAR(5) NOT NULL,
	description	VARCHAR(100),

        PRIMARY KEY (cogInfoId)
);

##-------------------------------------------------------------
##-------------------------------------------------------------


CREATE TABLE IF NOT EXISTS COGFun (
	funCode	CHAR(1),
	description	VARCHAR(70),
	funGroup	VARCHAR(50)
);


##-------------------------------------------------------------
# type:
# 0 - ptt_name
# 1 - ptt_synonym
# 2 - gi 
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS Synonym (
       name          VARCHAR(100),
       orfId             INT UNSIGNED NOT NULL,
       version 	         INT(2) UNSIGNED NOT NULL DEFAULT 1,
       type              INT(2) unsigned, 	
       created		 DATE,
       source 		 VARCHAR(50),		

       FOREIGN KEY (orfId, version) REFERENCES ORF (orfId, version)
);

##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS ExternalRef (
       name              VARCHAR(100),
       orfId             INT UNSIGNED NOT NULL,
       version 	         INT(2) UNSIGNED NOT NULL DEFAULT 1,

       FOREIGN KEY (orfId, version) REFERENCES ORF (orfId, version)
);

##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS Annotation (
       annotationId INT UNSIGNED NOT NULL AUTO_INCREMENT ,
       description  VARCHAR(255),
       source       VARCHAR(255),
       created      DATE,
       orfId        INT UNSIGNED NOT NULL,
       version      INT(2) UNSIGNED NOT NULL DEFAULT 1,	


       PRIMARY KEY (annotationId),
       FOREIGN KEY (orfId, version) REFERENCES ORF (orfId, version)
);

##-------------------------------------------------------------
##-------------------------------------------------------------


CREATE TABLE IF NOT EXISTS ScaffoldSeq (
       scaffoldId   INT(10) UNSIGNED,
       sequence     LONGBLOB, 	

       FOREIGN KEY (scaffold) REFERENCES Scaffold (scaffoldId)
);

##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS ORFSeq (
       orfId   INT(10) UNSIGNED,
       version INT(2) UNSIGNED NOT NULL DEFAULT 1,	
       sequence     LONGBLOB, 	

       FOREIGN KEY (orfId, version) REFERENCES ORF (orfId, version)
);

##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS AASeq (
       orfId   INT(10) UNSIGNED,
       version INT(2) UNSIGNED NOT NULL DEFAULT 1,	
       sequence     LONGBLOB, 	

       FOREIGN KEY (orfId, version) REFERENCES ORF (orfId, version)
);


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~





##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS BestHit (
       query_orfId     INT UNSIGNED,
       subject_orfId   INT UNSIGNED,
       evalue          FLOAT(4),
       length          INT UNSIGNED,

       FOREIGN KEY (query_orfId) REFERENCES ORF (orfId),
       FOREIGN KEY (subject_orfId) REFERENCES ORF (orfId)
);

##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE IF NOT EXISTS Ortholog (
       orf1 INT UNSIGNED NOT NULL,
       version1 INT(2) UNSIGNED NOT NULL DEFAULT 1,
 
       orf2 INT UNSIGNED,
       version2 INT(2) UNSIGNED NOT NULL DEFAULT 1,	

       FOREIGN KEY (orfId1, version1) REFERENCES ORF (orfId, version),
       FOREIGN KEY (orfId2, version2) REFERENCES ORF (orfId, version)

);


CREATE TABLE InterPRO (
       posId          INT UNSIGNED,
       orfId          INT UNSIGNED
);

##-------------------------------------------------------------
##-------------------------------------------------------------

CREATE TABLE EC (
       orfId          INT UNSIGNED,
       posId          INT UNSIGNED
);
