CREATE TABLE compounds(
	compound_id serial PRIMARY KEY ,
	name TEXT NOT NULL DEFAULT '',
	definition TEXT NOT NULL,
	definition_checksum TEXT NOT NULL UNIQUE,
	format TEXT NOT NULL 
);
CREATE INDEX compounds_name ON compounds(name);


ALTER SEQUENCE compounds_compound_id_seq RESTART 200;
INSERT INTO compounds(format,definition,definition_checksum)
	VALUES('junk','this is to shift the id numbers to ferret out index bugs','junk');

CREATE TABLE descriptor_types (
	descriptor_type_id serial PRIMARY KEY,
	descriptor_type TEXT NOT NULL UNIQUE
);

CREATE TABLE descriptors (
	descriptor_id serial PRIMARY KEY,
	descriptor_type_id INTEGER REFERENCES descriptor_types(descriptor_type_id) ON DELETE RESTRICT,
	descriptor TEXT NOT NULL,
	descriptor_checksum TEXT NOT NULL,
	UNIQUE(descriptor_type_id,descriptor_checksum)
);


CREATE TABLE compound_descriptors (
	compound_id INTEGER REFERENCES compounds(compound_id) ON DELETE CASCADE ON UPDATE CASCADE,
	descriptor_id INTEGER REFERENCES descriptors(descriptor_id),
	priority INTEGER NOT NULL DEFAULT 0,
	UNIQUE(compound_id,descriptor_id)
);
CREATE INDEX compound_descriptors_descriptor_id_key ON compound_descriptors(descriptor_id);
CREATE INDEX compound_descriptors_compound_id_key ON compound_descriptors(compound_id);


CREATE VIEW compounds_grouped_by_descriptors AS
	SELECT	descriptor_id, 
				trim(leading '{' from 
					trim( trailing '}' from
						array_agg(compound_id)::varchar)) AS compound_ids 
	FROM compound_descriptors
	GROUP BY descriptor_id
	HAVING count(compound_id) > 1
;