-- =================================================================
-- Dependencies:
--
-- :import feature from sequence
-- :import cvterm from cv
-- :import pub from pub
-- :import phenotype from phenotype
-- :import organism from organism
-- :import genotype from genetic
-- :import contact from contact
-- :import project from general
-- :import synonym
-- =================================================================
--

-- table contactprop
-- To add details of contact such as address, email, URL, etc
--
CREATE TABLE contactprop (
contactprop_id serial NOT NULL,
PRIMARY KEY (contactprop_id),
contact_id integer NOT NULL,
FOREIGN KEY (contact_id) REFERENCES contact (contact_id)
ON DELETE CASCADE,
cvterm_id integer NOT NULL,
FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id)
ON DELETE CASCADE,
value text,
rank integer not null default 0,
CONSTRAINT contactprop_c1 UNIQUE (contact_id, cvterm_id, rank)
);

-- table projectprop
--
CREATE TABLE projectprop (
projectprop_id serial NOT NULL,
PRIMARY KEY (projectprop_id),
project_id integer NOT NULL,
FOREIGN KEY (project_id) REFERENCES project (project_id)
ON DELETE CASCADE,
cvterm_id integer NOT NULL,
FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id)
ON DELETE CASCADE,
value text,
rank integer not null default 0,
CONSTRAINT projectprop_c1 UNIQUE (project_id, cvterm_id, rank)
);

-- table project_relationship
--
-- Note: A project can be composed of several smaller scale projects.
--
CREATE TABLE project_relationship (
project_relationship_id serial NOT NULL,
PRIMARY KEY (project_relationship_id),
subject_project_id integer NOT NULL,
FOREIGN KEY (subject_project_id) REFERENCES project (project_id)
ON DELETE CASCADE,
object_project_id integer NOT NULL,
FOREIGN KEY (object_project_id) REFERENCES project (project_id)
ON DELETE CASCADE,
type_id integer NOT NULL,
FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id)
ON DELETE RESTRICT,
CONSTRAINT project_relationship_c1 UNIQUE (subject_project_id, object_project_id, type_id)
);
COMMENT ON COLUMN project_relationship.type_id IS 'The type of relationship being stated, such as "is part of".

-- table stock_relationship_cvterm
--
-- For germplasm maintenance and pedigree data, stock_relatiohship.type_id will record cvterms such as
-- 'is a female parent of', 'a parent for mutation', 'is a group_id of', 'is a source_id of', etc.
-- The cvterms for higher categories such as 'generative', 'derivative' or 'maintenance' can be stored
-- in table stock_relationship_cvterm

CREATE TABLE stock_relationship_cvterm (
stock_relationship_cvterm_id SERIAL NOT NULL,
PRIMARY KEY (stock_relationship_cvterm_id),
stock_relatiohship_id integer NOT NULL,
FOREIGN KEY (stock_relationship_id) references stock_relationship (stock_relationship_id)
ON DELETE CASCADE,
cvterm_id integer NOT NULL,
FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id)
ON DELETE RESTRICT,
pub_id integer,
FOREIGN KEY (pub_id) REFERENCES pub (pub_id)
ON DELETE RESTRICT
);

-- table geolocation
--
-- The geo-referencable location of the stock, experiment, or cross.
CREATE TABLE geolocation (
geolocation_id serial NOT NULL,
PRIMARY KEY (geolocation_id),
description character varying(255),
coordinate_xml character varying(1024),
latitude real,
longitude real,
geodetic_datum character varying(32),
altitude real,
altitude_dev real,
postalcode character varying(32),
county character varying(64),
province character varying(64),
country character varying(64)
);
COMMENT ON TABLE geolocation IS 'The geo-referencable location of the stock. NOTE: This entity is subject to change as a more general and possibly more OpenGIS-compliant geolocation module may be introduced into Chado.';
COMMENT ON COLUMN geolocation.description IS 'A textual representation of the location, if this is the original georeference. Optional if the original georeference is available in lat/long coordinates.';
COMMENT ON COLUMN geolocation.coordinate_xml IS 'The georeference in XML format, preferably in GML.';
COMMENT ON COLUMN geolocation.latitude IS 'The decimal latitude coordinate of the georeference, using positive and negative sign to indicate N and S, respectively.';
COMMENT ON COLUMN geolocation.longitude IS 'The decimal longitude coordinate of the georeference, using positive and negative sign to indicate E and W, respectively.';
COMMENT ON COLUMN geolocation.geodetic_datum IS 'The geodetic system on which the geo-reference coordinates are based. For geo-references measured between 1984 and 2010, this will typically be WGS84.';
COMMENT ON COLUMN geolocation.altitude IS 'The altitude (elevation) of the location in meters. If the altitude is only known as a range, this is the average, and altitude_dev will hold half of the width of the range.';
COMMENT ON COLUMN geolocation.altitude_dev IS 'The possible deviation in altitude, in meters, from the average altitude for collected individuals. Will be empty (null) if the altitude is exact.';
COMMENT ON COLUMN geolocation.postalcode IS 'The postal code, or zipcode in the US, within which the georeference falls.';
COMMENT ON COLUMN geolocation.county IS 'The county (or equivalent local government unit) whithin which the georeference falls. This should probably rather be a foreign key to a cvterm, but there is an unresolved problem about the univocality constraint with location name ontologies, such as the Gazetteer.';
COMMENT ON COLUMN geolocation.province IS 'The province, or state, within which the georeference falls. This should probably rather be a foreign key to a cvterm, but there is an unresolved problem about the univocality constraint with location name ontologies, such as the Gazetteer.';
COMMENT ON COLUMN geolocation.country IS 'The country within which the georeference falls. This should probably rather be a foreign key to a cvterm, but there is an unresolved problem about the univocality constraint with location name ontologies, such as the Gazetteer.';

-- table crossexperiment
--
-- An experiment crossing two stocks. 
-- Though the pedigree data can be stored in stock_relationship table,
-- the crossexperiment table can store the name and type of the cross.
--
CREATE TABLE crossexperiment (
crossexperiment_id serial NOT NULL,
PRIMARY KEY (crossexperiment_id),
name character varying(255) NOT NULL,
expdate date,
experimenter_id integer,
FOREIGN KEY (experimenter_id) REFERENCES contact (contact_id)
ON DELETE RESTRICT,
geolocation_id integer,
FOREIGN KEY (geolocation_id) REFERENCES geolocation (geolocation_id)
ON DELETE RESTRICT,
type_id integer NOT NULL,
FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id)
ON DELETE RESTRICT,
CONSTRAINT crossexperiment_c1 UNIQUE (name)
);
COMMENT ON COLUMN crossexperiment.name IS 'Reference name for the cross, also known as the "brood name." Existing conventions for naming the cross use the "stock" type, listing the female "type" first. Thus, a backcross of a female F1 individual generated from a cross between a female H. erato cyrbia and a male H. himera by a male H. himera would be (HecHh)xHh_001, where 001 is the first replicate of this type of cross.';
COMMENT ON COLUMN crossexperiment.geolocation_id IS 'The geo-reference for where the experimental cross was conducted.';
COMMENT ON COLUMN crossexperiment.expdate IS 'The date of the cross experiment, typically the mating date.';
COMMENT ON COLUMN crossexperiment.experimenter_id IS 'The person who conducted the cross experiment.';
COMMENT ON COLUMN crossexperiment.type_id IS 'The type of cross, for example, F1, or F2, or backcross.';

-- table crossexperimentprop
--
-- examples for attribute/value pairs:
-- date_female_died,
-- days_mated,
-- days_laying,
-- num_eggs_hatched,
-- num_pupa,
-- num_adults,
-- num_females
CREATE TABLE crossexperimentprop (
crossexperimentprop_id serial NOT NULL,
PRIMARY KEY (crossexperimentprop_id),
crossexperiment_id integer NOT NULL,
FOREIGN KEY (crossexperiment_id) REFERENCES crossexperiment (crossexperiment_id)
ON DELETE CASCADE,
cvterm_id integer NOT NULL,
FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id)
ON DELETE CASCADE,
value text,
rank integer not null default 0,
CONSTRAINT crossexperimentprop_c1 UNIQUE (crossexperiment_id, cvterm_id, rank)
);
COMMENT ON TABLE crossexperimentprop IS 'Property/value associations for cross experiments.';
COMMENT ON COLUMN crossexperimentprop.crossexperiment_id IS 'The cross experiment to which the property applies.';
COMMENT ON COLUMN crossexperimentprop.cvterm_id IS 'The name of the property as a reference to a controlled vocabulary term.';
COMMENT ON COLUMN crossexperimentprop.value IS 'The value of the property.';
COMMENT ON COLUMN crossexperimentprop.rank IS 'The rank of the property value, if the property has an array of values.';

-- table crossexperiment_stock
--
-- To store the parents of the crossexperiment
-- Note: Though it is theoretically possible to conduct cross
-- experiments in a way that the female could also one out of multiple
-- individuals, there isn't a use case for this right now and we
-- therefore defer accommodating this until there is a requirement to
-- do so.
CREATE TABLE crossexperiment_stock (
crossexperiment_stock_id serial NOT NULL,
PRIMARY KEY (crossexperiment_stock_id),
crossexperiment_id integer NOT NULL,
FOREIGN KEY (crossexperiment_id) REFERENCES crossexperiment (crossexperiment_id)
ON DELETE CASCADE,
stock_id integer NOT NULL,
FOREIGN KEY (stock_id) REFERENCES stock (stock_id)
ON DELETE CASCADE,
type_id integer NOT NULL,
FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id)
ON DELETE RESTRICT,
CONSTRAINT crossexperiment_stock_c1 UNIQUE (crossexperiment_id, stock_id)
);
COMMENT ON TABLE crossexperiment_stock IS 'The parental stock(s) used in a crossexperiment. Some cross experiments are carried out by pairing multiple males to one or multiple female(s) so that the actual parent stocks of offspring may not necessarily be known a-priori. ';
COMMENT ON COLUMN crossexperiment_stock.crossexperiment_id IS 'The cross experiment in which the stock is used as a (possible) parent.';
COMMENT ON COLUMN crossexperiment_stock.stock_id IS 'The parental stock being used in the cross experiment. A specific stock can be associated with a crossexperiment only once. There may be multiple parental stocks of the same type in a cross experiment.';
COMMENT ON COLUMN crossexperiment_stock.type_id IS 'The type of the association of the stock, such as ''maternal parent'', or ''paternal parent''. Note that this is not necessarily redundant with the gender of the stock, for example consider plants.';

-- table crossexperiment_project
--
-- Note: Multiple cross can be conducted in one project, especially in plants, such as F1, F2, BC1, etc
-- The table crossexperiment_project links the crossexperiments to the project.
CREATE TABLE crossexperiment_project (
crossexperiment_project_id serial NOT NULL,
PRIMARY KEY (crossexperiment_project_id),
crossexperiment_id integer NOT NULL,
FOREIGN KEY (crossexperiment_id) REFERENCES crossexperiment (crossexperiment_id)
ON DELETE CASCADE,
project_id integer NOT NULL,
FOREIGN KEY (project_id) REFERENCES project (project_id)
ON DELETE CASCADE,
CONSTRAINT crossexperiment_project_c1 UNIQUE (crossexperiment_id, project_id)
);


-- table image (link out to a file name)
--
-- Link to an external image file
CREATE TABLE image (
image_id serial NOT NULL,
PRIMARY KEY (image_id),
identifier character varying(255),
uri character varying(1024) NOT NULL,
CONSTRAINT image_c1 UNIQUE (identifier),
CONSTRAINT image_c2 UNIQUE (uri)
);
COMMENT ON TABLE image IS 'Link to an external image';
COMMENT ON COLUMN image.identifier IS 'Unique identifier for the image, such as a LSID, or any other GUID';
COMMENT ON COLUMN image.uri IS 'URL or local file path to image';


-- table gtassay
--
-- Genotyping assay, or method of polymorphism detection
--

CREATE TABLE gtassay (
gtassay_id serial NOT NULL,
PRIMARY KEY (gtassay_id),
name character varying(255) NOT NULL,
species_id integer,
FOREIGN KEY (species_id) REFERENCES organism(organism_id)
ON DELETE RESTRICT,
image_id integer,
FOREIGN KEY (image_id) REFERENCES image (image_id)
ON DELETE RESTRICT,
type_id integer,
FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id)
ON DELETE RESTRICT,
CONSTRAINT gtassay_c1 UNIQUE (name)
);
COMMENT ON TABLE gtassay IS 'Genotyping assay, or method of polymorphism detection.';
COMMENT ON COLUMN gtassay.name IS 'Reference name of the genotyping assay';
COMMENT ON COLUMN gtassay.species_id IS 'The species on which the assay was first validated; this need not be the same as the one from which the specimen was obtained that is used in the genotyping experiment.';
COMMENT ON COLUMN gtassay.image_id IS 'The image documenting the quality and/or success of the assay; typically this will be a gel image, for example showing the purity of bands.';
COMMENT ON COLUMN gtassay.type_id IS 'The type of the assay. Usually this is the method of scoring the polymorphism (e.g., AFLP, SSCP, RFLP, size polymorphism).';

-- table gtassayprop
--
-- examples for attribute/value pairs:
-- specific_pcr_conditions,
-- annealing_temp,
-- microsat_repeat_type,
-- snp_type, (note: shouldn't this be a ref. to a feature?)
-- snp_position, (note: shouldn't this be a ref. to a feature?)
-- expected_length (of PCR product)
-- amplicon_contains_intron,
-- outcome_success
CREATE TABLE gtassayprop (
gtassayprop_id serial NOT NULL,
PRIMARY KEY (gtassayprop_id),
gtassay_id integer NOT NULL,
FOREIGN KEY (gtassay_id) REFERENCES gtassay (gtassay_id)
ON DELETE CASCADE,
cvterm_id integer NOT NULL,
FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id)
ON DELETE CASCADE,
value text,
rank integer not null default 0,
CONSTRAINT gtassayprop_c1 UNIQUE (gtassay_id, cvterm_id, rank)
);
COMMENT ON TABLE gtassayprop IS 'Property/value associations for genotyping assays.';
COMMENT ON COLUMN gtassayprop.gtassay_id IS 'The genotyping assay to which the property applies.';
COMMENT ON COLUMN gtassayprop.cvterm_id IS 'The name of the property as a reference to a controlled vocabulary term.';
COMMENT ON COLUMN gtassayprop.value IS 'The value of the property.';
COMMENT ON COLUMN gtassayprop.rank IS 'The rank of the property value, if the property has an array of values.';

-- table feature_gtassay
--
-- The table feature_gtassay links the gtassay (eg.molecular marker) to the locus
-- The same marker can be linked to multiple loci in multiple genetic maps.
--
CREATE TABLE feature_gtassay (
feature_gtassay_id serial NOT NULL,
PRIMARY KEY (feature_gtassay_id),
feature_id integer NOT NULL,
FOREIGN KEY (feature_id) REFERENCES feature (feature_id)
ON DELETE CASCADE,
gtassay_id integer NOT NULL,
FOREIGN KEY (gtassay_id) REFERENCES gtassay (gtassay_id)
ON DELETE CASCADE,
CONSTRAINT feature_gtassay_c1 UNIQUE (feature_id, gtassay_id)
);

--
-- table reagent
--
-- A reagent used in an assay, for example in a genotyping assay. The
-- most often used reagents will be PCR primers, but there are other
-- reagents used in genotyping assays too that characterize the
-- assay. For example,
-- rflp_enzyme,
-- aflp_adapte,
-- aflp_adapter_enzyme,
-- aflp_overhang_linker.
CREATE TABLE reagent (
reagent_id serial NOT NULL,
PRIMARY KEY (reagent_id),
name character varying (80) NOT NULL,
type_id integer NOT NULL,
FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id)
ON DELETE RESTRICT,
feature_id integer,
FOREIGN KEY (feature_id) REFERENCES feature (feature_id)
ON DELETE CASCADE,
CONSTRAINT reagent_c1 UNIQUE (name, type_id)
);
COMMENT ON TABLE reagent IS 'A reagent such as a primer, an enzyme, an adapter oligo, a linker oligo. Reagents are used in genotyping assays, or in any other kind of assay.';
COMMENT ON COLUMN reagent.name IS 'The name of the reagent. The name should be unique for a given type.';
COMMENT ON COLUMN reagent.type_id IS 'The type of the reagent, for example linker oligomer, or forward primer.';
COMMENT ON COLUMN reagent.feature_id IS 'If the reagent is a primer, the feature that it corresponds to. More generally, the corresponding feature for any reagent that has a sequence that maps to another sequence.';
--
-- table reagentprop
--
-- Property/value associations for reagents, such as Tm, Km, optimal
-- concentration or buffer, etc
CREATE TABLE reagentprop (
reagentprop_id serial NOT NULL,
PRIMARY KEY (reagentprop_id),
reagent_id integer NOT NULL,
FOREIGN KEY (reagent_id) REFERENCES reagent (reagent_id)
ON DELETE CASCADE,
cvterm_id integer NOT NULL,
FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id)
ON DELETE CASCADE,
value text,
rank integer not null default 0,
CONSTRAINT reagentprop_c1 UNIQUE (reagent_id, cvterm_id, rank)
);
COMMENT ON TABLE reagentprop IS 'Property/value associations for reagents.';
COMMENT ON COLUMN reagentprop.reagent_id IS 'The reagent to which the property applies.';
COMMENT ON COLUMN reagentprop.cvterm_id IS 'The name of the property as a reference to a controlled vocabulary term.';
COMMENT ON COLUMN reagentprop.value IS 'The value of the property.';
COMMENT ON COLUMN reagentprop.rank IS 'The rank of the property value, if the property has an array of values.';
--
-- table reagent_relationship
--
-- Relationships between reagents. Some reagents form a group; i.e.,
-- they are used all together or not at all. Examples are
-- adapter/linker/enzyme assay reagents.
CREATE TABLE reagent_relationship (
reagent_relationship_id serial NOT NULL,
PRIMARY KEY (reagent_relationship_id),
subject_reagent_id integer NOT NULL,
FOREIGN KEY (subject_reagent_id) REFERENCES reagent (reagent_id)
ON DELETE CASCADE,
object_reagent_id integer NOT NULL,
FOREIGN KEY (object_reagent_id) REFERENCES reagent (reagent_id)
ON DELETE CASCADE,
type_id integer NOT NULL,
FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id)
ON DELETE RESTRICT,
CONSTRAINT reagent_relationship_c1 UNIQUE (subject_reagent_id, object_reagent_id, type_id)
);
COMMENT ON TABLE reagent_relationship IS 'Relationships between reagents. Some reagents form a group; i.e., they are used all together or not at all. Examples are adapter/linker/enzyme assay reagents.';
COMMENT ON COLUMN reagent_relationship.subject_reagent_id IS 'The subject reagent in the relationship. In parent/child terminology, the subject is the child. For example, in "linkerA 3prime-overhang-linker enzymeA" linkerA is the subject, 3prime-overhand-linker is the type, and enzymeA is the object.';
COMMENT ON COLUMN reagent_relationship.object_reagent_id IS 'The object reagent in the relationship. In parent/child terminology, the object is the parent. For example, in "linkerA 3prime-overhang-linker enzymeA" linkerA is the subject, 3prime-overhand-linker is the type, and enzymeA is the object.';
COMMENT ON COLUMN reagent_relationship.type_id IS 'The type (or predicate) of the relationship. For example, in "linkerA 3prime-overhang-linker enzymeA" linkerA is the subject, 3prime-overhand-linker is the type, and enzymeA is the object.';
--
-- table gtassay_reagent
--
-- Reagents used by a genotyping assay. An assay may use multiple
-- reagents.
CREATE TABLE gtassay_reagent (
gtassay_reagent_id serial NOT NULL,
PRIMARY KEY (gtassay_reagent_id),
gtassay_id integer NOT NULL,
FOREIGN KEY (gtassay_id) REFERENCES gtassay (gtassay_id)
ON DELETE CASCADE,
reagent_id integer NOT NULL,
FOREIGN KEY (reagent_id) REFERENCES reagent (reagent_id)
ON DELETE CASCADE,
type_id integer NOT NULL,
CONSTRAINT gtassay_reagent_c1 UNIQUE (gtassay_id, reagent_id, type_id)
);
COMMENT ON TABLE gtassay_reagent IS 'Reagents used by a genotyping assay. An x may use multiple reagents.';
COMMENT ON COLUMN gtassay_reagent.gtassay_id IS 'The genotyping assay using the reagent.';
COMMENT ON COLUMN gtassay_reagent.reagent_id IS 'The reagent used by the genotyping assay.';
--COMMENT ON COLUMN gtassay_reagent.type_id IS 'The type or role in which the reagent is being used. For example, a primer may be used as a forward primer or a reverse primer, or a linker oligonucleotide may be used 3'' or 5''. Oftentimes, the type may be identical to the type of the reagent, though. A reagent can be used by one assay in the same role only once.';

-- table stocksample
-- Part of a stock or a clone of a stock that is used in an experiment
CREATE TABLE stocksample (
stocksample_id serial NOT NULL,
PRIMARY KEY (stocksample_id),
name character varying(255) NOT NULL,
description character varying(255),
identifier character varying(255),
stock_id integer NOT NULL,
FOREIGN KEY (stock_id) REFERENCES stock (stock_id)
ON DELETE CASCADE,
CONSTRAINT stocksample_c1 UNIQUE (name)
);

COMMENT ON TABLE stocksample IS 'Part of a stock or a clone of a stock that is used in an experiment';
COMMENT ON COLUMN stocksample.name IS 'Reference name for the stocksample, for tree breeding data, it may be composed of stock, orchard, and plot names. When the stocksample data is not relevant or not available, the name of the stock can be stored here.';
COMMENT ON COLUMN stocksample.description IS 'Description of the stocksample including information on stocksample quality, e.g. concentration, purity, etc.';
COMMENT ON COLUMN stocksample.identifier IS 'Identifier, for example a barcode, of the stocksample';
COMMENT ON COLUMN stocksample.stock_id IS 'stock used in the extraction or the corresponding stock for the clone';

-- create table stocksampleprop
-- stocksampleprop records various properties of the stocksample. 
-- For specimens of an insect, the properties may include
-- extract_type_id, tissue_type_id, storage_location, experimenter.
-- For tree breeding data, the properties may include
-- orchard name, main plot number, sub plot number, planting date, treatment, and rootstock_id.
--
CREATE TABLE stocksampleprop (
stocksampleprop_id serial NOT NULL,
PRIMARY KEY (stocksampleprop_id),
stocksample_id integer NOT NULL,
FOREIGN KEY (stocksample_id) REFERENCES stocksample (stocksample_id)
ON DELETE CASCADE,
cvterm_id integer NOT NULL,
FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id)
ON DELETE CASCADE,
value text,
rank integer not null default 0,
CONSTRAINT stocksampleprop_c1 UNIQUE (stocksample_id, cvterm_id, rank)
);
COMMENT ON TABLE stocksampleprop IS 'Property/value associations for stocksamples. This table can store the properties such as treatment';
COMMENT ON COLUMN stocksampleprop.stocksample_id IS 'The stocksample to which the property applies.';
COMMENT ON COLUMN stocksampleprop.cvterm_id IS 'The name of the property as a reference to a controlled vocabulary term.';
COMMENT ON COLUMN stocksampleprop.value IS 'The value of the property.';
COMMENT ON COLUMN stocksampleprop.rank IS 'The rank of the property value, if the property has an array of values.';


-- table gtexperiment
--
-- Experiment to assign the genotype of a sample of a stock determined by a particular gtassay, collected and assayed at a certain date.
-- For tree breeding data, a row of gtexperiment can correspond to an experiment to assign a genotype to a sample stock 
-- (a tree clone planted in a certan plot in an orchard and treated with a certain fertilizer), collected a certain date, and tested at a certain date using a certain molecular marker.
-- 

CREATE TABLE gtexperiment (
gtexperiment_id serial NOT NULL,
PRIMARY KEY (gtexperiment_id),
stocksample_id integer NOT NULL,
FOREIGN KEY (stocksample_id) REFERENCES stocksample (stocksample_id)
ON DELETE CASCADE,
collection_date date NOT NULL,
assay_date date NOT NULL,
gtassay_id integer NOT NULL,
FOREIGN KEY (gtassay_id) REFERENCES gtassay (gtassay_id)
ON DELETE RESTRICT,
genotype_id integer NOT NULL,
FOREIGN KEY (genotype_id) REFERENCES genotype (genotype_id)
ON DELETE RESTRICT,
geolocation_id integer,
FOREIGN KEY (geolocation_id) REFERENCES geolocation (geolocation_id)
ON DELETE RESTRICT,
project_id integer NOT NULL,
FOREIGN KEY (project_id) REFERENCES project (project_id)
ON DELETE RESTRICT,
experimenter_id integer,
FOREIGN KEY (experimenter_id) REFERENCES contact (contact_id)
ON DELETE RESTRICT,
notes character varying(255)
CONSTRAINT gtexperiment_c1 UNIQUE (stocksample_id, collection_date, assay_date, gtassay_id, genotype_id, project_id)
);
COMMENT ON COLUMN gtexperiment.stocksample_id IS 'The sample used in the experiment.';
COMMENT ON COLUMN gtexperiment.collection_date IS 'Date that the sample was collected';
COMMENT ON COLUMN gtexperiment.assay_date IS 'Date that the assay was performed';
COMMENT ON COLUMN gtexperiment.gtassay_id IS 'The genotyping assay used to determine the genotype.';
COMMENT ON COLUMN gtexperiment.genotype_id IS 'The genotype determined by the experiment.';
COMMENT ON COLUMN gtexperiment.project_id IS 'The project that the experiment is associated with';
COMMENT ON COLUMN gtexperiment.experimenter_id IS 'Person performing the experiment';
COMMENT ON COLUMN gtexperiment.notes IS 'Notes on the genotype assignement';

-- table ptassay
--
-- Method of polymorphism detection
CREATE TABLE ptassay (
ptassay_id serial NOT NULL,
PRIMARY KEY (ptassay_id),
name character varying(255) NOT NULL UNIQUE
);
COMMENT ON TABLE ptassay IS 'Phenotype determination assay';
COMMENT ON COLUMN ptassay.name IS 'Reference name of the phenotyping assay';

-- create table ptassayprop
CREATE TABLE ptassayprop (
ptassayprop_id serial NOT NULL,
PRIMARY KEY (ptassayprop_id),
ptassay_id integer NOT NULL,
FOREIGN KEY (ptassay_id) REFERENCES ptassay (ptassay_id)
ON DELETE CASCADE,
cvterm_id integer NOT NULL,
FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id)
ON DELETE CASCADE,
value text,
rank integer not null default 0,
CONSTRAINT ptassayprop_c1 UNIQUE (ptassay_id, cvterm_id, rank)
);
COMMENT ON TABLE ptassayprop IS 'Property/value associations for phenotyping assays.';
COMMENT ON COLUMN ptassayprop.ptassay_id IS 'The phenotyping assay to which the property applies.';
COMMENT ON COLUMN ptassayprop.cvterm_id IS 'The name of the property as a reference to a controlled vocabulary term.';
COMMENT ON COLUMN ptassayprop.value IS 'The value of the property.';
COMMENT ON COLUMN ptassayprop.rank IS 'The rank of the property value, if the property has an array of values.';

-- table ptexperiment
--
-- Experiment to assign the phenotype of a sample of a stock determined by a particular ptassay assayed at a certain date.
-- For tree breeding data, a row of gtexperiment can correspond to an experiment to assign a phenotype to a tree clone
-- from a certan orchard and a plot, collected a certain date, and tested at a certain date using a certain phenotyping assay.
-- 

CREATE TABLE ptexperiment (
ptexperiment_id serial NOT NULL,
PRIMARY KEY (ptexperiment_id),
stocksample_id integer NOT NULL,
FOREIGN KEY (stocksample_id) REFERENCES stocksample (stocksample_id)
ON DELETE CASCADE,
collection_date date NOT NULL,
assay_date date NOT NULL,
ptassay_id integer NOT NULL,
FOREIGN KEY (ptassay_id) REFERENCES ptassay (ptassay_id)
ON DELETE RESTRICT,
phenotype_id integer NOT NULL,
FOREIGN KEY (phenotype_id) REFERENCES phenotype (phenotype_id)
ON DELETE RESTRICT,
geolocation_id integer,
FOREIGN KEY (geolocation_id) REFERENCES geolocation (geolocation_id)
ON DELETE RESTRICT,
project_id integer NOT NULL,
FOREIGN KEY (project_id) REFERENCES project (project_id)
ON DELETE RESTRICT,
experimenter_id integer,
FOREIGN KEY (experimenter_id) REFERENCES contact (contact_id)
ON DELETE RESTRICT,
notes character varying(255)
CONSTRAINT ptexperiment_c1 UNIQUE (stocksample_id, collection_date, assay_date, ptassay_id, phenotype_id, project_id)
);
COMMENT ON COLUMN ptexperiment.stocksample_id IS 'The sample used in the experiment.'; 
COMMENT ON COLUMN ptexperiment.collection_date IS 'Date that the sample was collected';
COMMENT ON COLUMN ptexperiment.assay_date IS 'Date that the assay was performed';
COMMENT ON COLUMN ptexperiment.ptassay_id IS 'The phenotyping assay used to determine the phenotype.';
COMMENT ON COLUMN ptexperiment.phenotype_id IS 'The phenotype determined by the experiment.';
COMMENT ON COLUMN ptexperiment.project_id IS 'The project that the experiment is associated with';
COMMENT ON COLUMN ptexperiment.experimenter_id IS 'Person performing the experiment';
COMMENT ON COLUMN ptexperiment.notes IS 'Notes on the phenotype assignement';

-- create table stock_image
CREATE TABLE stock_image (
stock_image_id serial NOT NULL,
PRIMARY KEY (stock_image_id),
stock_id integer NOT NULL REFERENCES stock(stock_id) ON DELETE RESTRICT,
image_id integer NOT NULL REFERENCES image(image_id) ON DELETE RESTRICT,
UNIQUE (stock_id, image_id)
);