CREATE TABLE Corpus
	(name text,
	 filepath text,
	 id serial PRIMARY KEY);

CREATE TABLE Words
	(corpus_id int REFERENCES Corpus,
	 passage_id int,
	 idx int,
	 word text, 
	 parsed_word text,
	 PRIMARY KEY (corpus_id, passage_id, idx));

CREATE TABLE Images
	(filepath text UNIQUE,
	 height integer,
	 width integer,
	 source smallint,
	 source_url text,
	 id serial PRIMARY KEY);
	
CREATE TABLE Dictionary
	(phrase text,
	 image_id integer REFERENCES Images,
	 query_string text,
	 count integer DEFAULT 1,
	 PRIMARY KEY (phrase, image_id));

-- Note corpusID and passageID should really refer to Words table in some way
-- Need to establish integer -> arrow direction mapping
CREATE TABLE Pictures
	(username text,
	 corpus_id integer REFERENCES Corpus,
	 passage_id integer,
	 arrow_dir smallint,
	 id serial PRIMARY KEY);

-- Need to establish integer -> label mapping
CREATE TABLE Labels
	(picture_id integer REFERENCES Pictures,
	 image_id integer REFERENCES Images,
	 label smallint,
	 position integer,
	 start_idx integer,
	 end_idx integer,
	 id serial PRIMARY KEY,
	 CONSTRAINT valid_span CHECK (end_idx >= start_idx));
	 

GRANT SELECT ON Dictionary TO "www-user";
GRANT INSERT ON Dictionary TO "www-user";

GRANT SELECT ON Images TO "www-user";
GRANT INSERT ON Images TO "www-user";
GRANT USAGE ON SEQUENCE Images_id_seq TO "www-user";

GRANT SELECT ON Words TO "www-user";

GRANT SELECT ON Labels TO "www-user";
GRANT INSERT ON Labels TO "www-user";
GRANT USAGE ON SEQUENCE Labels_id_seq TO "www-user";

GRANT SELECT ON Pictures TO "www-user";
GRANT INSERT ON Pictures TO "www-user";
GRANT USAGE ON SEQUENCE Pictures_id_seq TO "www-user";

GRANT UPDATE ON Dictionary TO "www-user";

SELECT setval('corpus_id_seq',1,'f');
SELECT setval('labels_id_seq',1,'f');
SELECT setval('pictures_id_seq',1,'f');


