-- dem 7mar07: Added qtldatasource and qtldatacurated.

-- Linkage_data: removed
-- Nonsignificant_marker: removed
-- Significant_LOD_interval: removed
-- Effect_of_allele_substitution[1]: removed
-- Additivity_Dominance_ratio: removed

-- Position: later?

-- qtlspecies added, 2may05
-- qtlstudy added, 27mar08

create table qtl (
	id integer auto_increment primary key,
	name varchar(255),
	-- Trait_affected
	traitaffected_traitid integer,
	-- Chromosome_arm
	chromosomearm varchar(25),
	-- Nearest_marker
	nearestmarker_locusid integer,
	-- Significance_level
	significancelevel float,
	-- Map_label
	maplabel varchar(255)
) ENGINE=InnoDB;
create index name_index on qtl (name);
create index traitaffected_traitid_index on qtl (traitaffected_traitid);
create index nearestmarker_locusid_index on qtl (nearestmarker_locusid);

create table qtlreference (
        id integer auto_increment primary key,
        qtlid integer,
        -- Reference (?)
        referenceid integer
) ENGINE=InnoDB;
create index qtlid_index on qtlreference (qtlid);
create index referenceid_index on qtlreference (referenceid);

create table qtlgeneclass (
	id integer auto_increment primary key,
	qtlid integer,
	-- Gene_class
	geneclassid integer
) ENGINE=InnoDB;
create index qtlid_index on qtlgeneclass (qtlid);
create index geneclassid_index on qtlgeneclass (geneclassid);

create table qtltraitstudy (
	id integer auto_increment primary key,
	qtlid integer,
	-- Trait_study
	traitstudyid integer
) ENGINE=InnoDB;
create index qtlid_index on qtltraitstudy (qtlid);
create index traitstudyid_index on qtltraitstudy (traitstudyid);

create table qtlenvironment (
	id integer auto_increment primary key,
	qtlid integer,
	-- Environment
	environmentid integer,
	-- LOD_peak_height
	lodpeakheight float,
	-- LOD_threshold
	lodthreshold float,
	-- Phenotypic_R2
	phenotypicrsq float,
	-- Genetic_R2
	geneticrsq float,
	-- Effect_of_allele_substitution[0]
	allelesubstitutioneffect float,
	-- Higher_scoring_allele_from
	higherscoringallelefrom_germplasmid integer
) ENGINE=InnoDB;
create index qtlid_index on qtlenvironment (qtlid);
create index environmentid_index on qtlenvironment (environmentid);
create index higherscoringallelefrom_germplasmid_index on qtlenvironment (higherscoringallelefrom_germplasmid);

create table qtlmapdata (
	id integer auto_increment primary key,
	qtlid integer,
	-- Mapping_data
	mapdataid integer
) ENGINE=InnoDB;
create index qtlid_index on qtlmapdata (qtlid);
create index mapdataid_index on qtlmapdata (mapdataid);

create table qtlassociatedgene (
	id integer auto_increment primary key,
	qtlid integer,
	-- Associated_gene
	geneid integer
) ENGINE=InnoDB;
create index qtlid_index on qtlassociatedgene (qtlid);
create index geneid_index on qtlassociatedgene (geneid);

create table qtlsignificantmarker (
	id integer auto_increment primary key,
	qtlid integer,
	-- Significant_marker
	locusid integer
) ENGINE=InnoDB;
create index qtlid_index on qtlsignificantmarker (qtlid);
create index locusid_index on qtlsignificantmarker (locusid);

create table qtllodpeaklocation (
	id integer auto_increment primary key,
	qtlid integer,
	-- LOD_peak_location
	locusid integer
) ENGINE=InnoDB;
create index qtlid_index on qtllodpeaklocation (qtlid);
create index locusid_index on qtllodpeaklocation (locusid);

create table qtlsignificancebyenvironment (
	id integer auto_increment primary key,
	qtlid integer,
	-- Significance_x_Environment
	environmentid integer
) ENGINE=InnoDB;
create index qtlid_index on qtlsignificancebyenvironment (qtlid);
create index environmentid_index on qtlsignificancebyenvironment (environmentid);

create table qtlimage (
	id integer auto_increment primary key,
	qtlid integer,
	-- Image
	imageid integer
) ENGINE=InnoDB;
create index qtlid_index on qtlimage (qtlid);
create index imageid_index on qtlimage (imageid);

create table qtlremark (
	id integer auto_increment primary key,
	qtlid integer,
	-- Interactions, Comment
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index qtlid_index on qtlremark (qtlid);

create table qtlspecies (
	id integer auto_increment primary key,
	qtlid integer,
	-- Species
	speciesid integer
) ENGINE=InnoDB;
create index qtlid_index on qtlspecies (qtlid);
create index speciesid_index on qtlspecies (speciesid);

create table qtlsynonym (
	id integer auto_increment primary key,
	qtlid integer,
	-- Synonym
	type varchar(100),
	name varchar(100)
) ENGINE=InnoDB;
create index qtlid_index on qtlsynonym (qtlid);

create table qtldatasource (
	id integer auto_increment primary key,
	qtlid integer,
	-- Data_source
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index qtlid_index on qtldatasource (qtlid);
create index colleagueid_index on qtldatasource (colleagueid);

create table qtldatacurator (
	id integer auto_increment primary key,
	qtlid integer,
	-- Data_curated
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index qtlid_index on qtldatacurator (qtlid);
create index colleagueid_index on qtldatacurator (colleagueid);

create table qtlstudy (
        id integer auto_increment primary key,
        -- name should be the same as in the qtl table.
        name varchar(255)
) ENGINE=InnoDB;
create index name_index on qtlstudy (name);