-- updated 040423 DDH

-- Map: rearrangement->maprearrangement->map
-- Correct_name: removed
-- Other_name: removed
-- Reference_germplasm: removed
-- Remark: removed
-- Variant: removed
-- Found: removed
-- Absent: removed
-- Mutagen: removed
-- Phenotype: removed
-- Haplo_insufficiency: removed
-- Qualifier: removed
-- Author: removed
-- Date: removed
-- Proximal_breakpoint: removed
-- Distal_breakpoint: removed
-- 2Point: removed
-- 3Point: removed
-- Df_Dup: removed
-- In_situ: removed

create table rearrangement (
	id integer auto_increment primary key,
	name varchar(255),
	-- Location
	location_colleagueid integer
) ENGINE=InnoDB;
create index name_index on rearrangement (name);
create index location_colleagueid_index on rearrangement (location_colleagueid);

create table rearrangementtype (
        id integer auto_increment primary key,
        rearrangementid integer,
	-- Type
	type varchar(100)
) ENGINE=InnoDB;
create index rearrangementid_index on rearrangementtype (rearrangementid);

create table rearrangementnearbymarker (
	id integer auto_increment primary key,
	rearrangementid integer,
	-- Proximal_marker, Distal_marker
	type varchar(100),
	locusid integer
) ENGINE=InnoDB;
create index rearrangementid_index on rearrangementnearbymarker (rearrangementid);
create index locusid_index on rearrangementnearbymarker (locusid);

create table rearrangementcontainslocus (
	id integer auto_increment primary key,
	rearrangementid integer,
	-- Contains, Does_not_contain
	type varchar(100),
	locusid integer
) ENGINE=InnoDB;
create index rearrangementid_index on rearrangementcontainslocus (rearrangementid);
create index locusid_index on rearrangementcontainslocus (locusid);

create table rearrangementgermplasm (
	id integer auto_increment primary key,
	rearrangementid integer,
	-- Germplasm
	germplasmid integer
) ENGINE=InnoDB;
create index rearrangementid_index on rearrangementgermplasm (rearrangementid);
create index germplasmid_index on rearrangementgermplasm (germplasmid);

create table rearrangementreference (
	id integer auto_increment primary key,
	rearrangementid integer,
	-- Reference
	referenceid integer
) ENGINE=InnoDB;
create index rearrangementid_index on rearrangementreference (rearrangementid);
create index referenceid_index on rearrangementreference (referenceid);