DROP TABLE gazeawd;
-- Locate the words linked to a gaze item
SELECT
	gaze.*,
	samespeakerstart.id AS samewordstart,
	samespeakerstart.chunk AS samechunkstart,
	otherspeakerstart.id AS otherwordstart,
	otherspeakerstart.chunk AS otherchunkstart,
	samespeakerend.id AS samewordend,
	samespeakerend.chunk AS samechunkend,
	otherspeakerend.id AS otherwordend,
	otherspeakerend.chunk AS otherchunkend
INTO TABLE gazeawd
FROM
	gaze
	LEFT OUTER JOIN
	awd AS samespeakerstart
	ON (
	gaze.file = samespeakerstart.file
	AND
	gaze.start >= samespeakerstart.start AND gaze.start < samespeakerstart.endtime
	)
	LEFT OUTER JOIN
	awd AS otherspeakerstart
	ON (
	substring(gaze.file from '[0-9]+') = substring(otherspeakerstart.file from '[0-9]+')
	AND
	gaze.speaker != otherspeakerstart.speaker
	AND
	gaze.start >= otherspeakerstart.start AND gaze.start < otherspeakerstart.endtime
	)
	LEFT OUTER JOIN
	awd AS samespeakerend
	ON (
	gaze.file = samespeakerend.file
	AND
	gaze.endtime >= samespeakerend.start AND gaze.endtime < samespeakerend.endtime
	)
	LEFT OUTER JOIN
	awd AS otherspeakerend
	ON (
	substring(gaze.file from '[0-9]+') = substring(otherspeakerend.file from '[0-9]+')
	AND
	gaze.speaker != otherspeakerend.speaker
	AND
	gaze.endtime >= otherspeakerend.start AND gaze.endtime < otherspeakerend.endtime
	)
;

GRANT SELECT ON gazeawd TO PUBLIC;

-- Description of table
DELETE FROM columndef WHERE tablename = 'gazeawd';

INSERT INTO columndef VALUES ('gazeawd', 'tabledescription','TEXT','Word and chunks linked to gaze');
INSERT INTO columndef VALUES ('gazeawd', 'num','INT4','Record number in this table');
INSERT INTO columndef VALUES ('gazeawd', 'speaker','TEXT','Speaker ID');
INSERT INTO columndef VALUES ('gazeawd', 'lang','TEXT','Language Code');
INSERT INTO columndef VALUES ('gazeawd', 'id','TEXT','Identification code of current item');
INSERT INTO columndef VALUES ('gazeawd', 'pos','INT4','Position of item in tier');
INSERT INTO columndef VALUES ('gazeawd', 'start','FLOAT8','Start time of item in file');
INSERT INTO columndef VALUES ('gazeawd', 'endtime','FLOAT8','End time of item in file');
INSERT INTO columndef VALUES ('gazeawd', 'previd ','TEXT','ID code of previous item');
INSERT INTO columndef VALUES ('gazeawd', 'nextid','TEXT','ID code of next item');
INSERT INTO columndef VALUES ('gazeawd', 'prevne','TEXT','ID code of previous Non-Empty item');
INSERT INTO columndef VALUES ('gazeawd', 'nextne','TEXT','ID code of next Non-Empty item');
INSERT INTO columndef VALUES ('gazeawd', 'file','TEXT','ID code of parent file');
INSERT INTO columndef VALUES ('gazeawd', 'tiername','TEXT','Name of the tier in the file');
INSERT INTO columndef VALUES ('gazeawd', 'tiertype','TEXT','Type of the tier');
INSERT INTO columndef VALUES ('gazeawd', 'tier','TEXT','ID code of parent tier');
INSERT INTO columndef VALUES ('gazeawd', 'chunk','TEXT','ID code of chunk (gaze movement)');
INSERT INTO columndef VALUES ('gazeawd', 'value','TEXT','Gaze direction "g" or away "x"');
INSERT INTO columndef VALUES ('gazeawd', 'samewordstart','TEXT','Same speaker word with gaze start');
INSERT INTO columndef VALUES ('gazeawd', 'samechunkstart','TEXT','Same speaker chunk with gaze start');
INSERT INTO columndef VALUES ('gazeawd', 'otherwordstart','TEXT','Other speaker word with gaze start');
INSERT INTO columndef VALUES ('gazeawd', 'otherchunkstart','TEXT','Other speaker chunk with gaze start');
INSERT INTO columndef VALUES ('gazeawd', 'samewordend','TEXT','Same speaker word with gaze end');
INSERT INTO columndef VALUES ('gazeawd', 'samechunkend','TEXT','Same speaker word chunk gaze end');
INSERT INTO columndef VALUES ('gazeawd', 'otherwordend','TEXT','Other speaker word with gaze end');
INSERT INTO columndef VALUES ('gazeawd', 'otherchunkend','TEXT','Other speaker word chunk gaze end');

