-- Combine gaze and turns
DROP TABLE gazetaketurn;

-- Locate gazes that start in the silence before
-- a speaker takes the turn
SELECT
    properturnswitch.num,
    properturnswitch.id,
    properturnswitch.speaker,
    properturnswitch.nextid,
    properturnswitch.nextspeaker,
    properturnswitch.delay,
    gazeawd.speaker AS gazespeaker,
    gazeawd.id AS gazeid,
    gazeawd.start AS gazestart,
    gazeawd.endtime AS gazeend,
    gazeawd.start - ackawd.endtime AS gazedelay,
    gazeawd.value AS value
INTO TABLE gazetaketurn
FROM
    properturnswitch
    JOIN
    ackawd
    ON (ackawd.id = properturnswitch.id)
    RIGHT OUTER JOIN
    gazeawd
    ON (gazeawd.otherchunkstart = ackawd.id
	OR gazeawd.otherchunkstart = ackawd.nextid)
WHERE
    gazeawd.value IN ('g', 'd')
;

GRANT SELECT ON gazetaketurn TO PUBLIC;

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

INSERT INTO columndef VALUES ('gazetaketurn', 'tabledescription','TEXT','Gaze linked to same speaker takes turn');

INSERT INTO columndef VALUES ('gazetaketurn', 'Num', 'INT4', 'Record number in this table');
INSERT INTO columndef VALUES ('gazetaketurn', 'ID', 'TEXT', 'Chunk ID');
INSERT INTO columndef VALUES ('gazetaketurn', 'Speaker', 'TEXT', 'Speaker ID');
INSERT INTO columndef VALUES ('gazetaketurn', 'NextID', 'TEXT', 'ID of next chunk');
INSERT INTO columndef VALUES ('gazetaketurn', 'NextSpeaker', 'TEXT', 'Speaker ID of next speaker');
INSERT INTO columndef VALUES ('gazetaketurn', 'delay', 'FLOAT8', 'Delay of next chunk (next start - current end)');
INSERT INTO columndef VALUES ('gazetaketurn', 'gazespeaker','TEXT','Gaze Speaker ID');
INSERT INTO columndef VALUES ('gazetaketurn', 'gazeid','TEXT','Identification code of current gaze item');
INSERT INTO columndef VALUES ('gazetaketurn', 'gazestart','FLOAT8','Start time of item in file');
INSERT INTO columndef VALUES ('gazetaketurn', 'gazeend','FLOAT8','End time of item in file');
INSERT INTO columndef VALUES ('gazetaketurn', 'gazedelay','FLOAT8','Delay between gaze-start and next chunk');
INSERT INTO columndef VALUES ('gazetaketurn', 'value','FLOAT8','Gaze value (g)');

-- Combine gaze and turns
DROP TABLE gazegiveturn;

-- Locate gazes that start in the silence before
-- a speaker takes the turn
SELECT
    properturnswitch.num,
    properturnswitch.id,
    properturnswitch.speaker,
    properturnswitch.nextid,
    properturnswitch.nextspeaker,
    properturnswitch.delay,
    gazeawd.speaker AS gazespeaker,
    gazeawd.id AS gazeid,
    gazeawd.start AS gazestart,
    gazeawd.endtime AS gazeend,
    gazeawd.start - ackawd.endtime AS gazedelay,
    gazeawd.value AS value
INTO TABLE gazegiveturn
FROM
    properturnswitch
    JOIN
    ackawd
    ON (ackawd.id = properturnswitch.id)
    RIGHT OUTER JOIN
    gazeawd
    ON (gazeawd.samechunkstart = ackawd.id
	OR gazeawd.samechunkstart = ackawd.nextid)
WHERE
    gazeawd.value IN ('g', 'd')
;

GRANT SELECT ON gazegiveturn TO PUBLIC;

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

INSERT INTO columndef VALUES ('gazegiveturn', 'tabledescription','TEXT','Gaze linked to same speaker takes turn');

INSERT INTO columndef VALUES ('gazegiveturn', 'Num', 'INT4', 'Record number in this table');
INSERT INTO columndef VALUES ('gazegiveturn', 'ID', 'TEXT', 'Chunk ID');
INSERT INTO columndef VALUES ('gazegiveturn', 'Speaker', 'TEXT', 'Speaker ID');
INSERT INTO columndef VALUES ('gazegiveturn', 'NextID', 'TEXT', 'ID of next chunk');
INSERT INTO columndef VALUES ('gazegiveturn', 'NextSpeaker', 'TEXT', 'Speaker ID of next speaker');
INSERT INTO columndef VALUES ('gazegiveturn', 'delay', 'FLOAT8', 'Delay of next chunk (next start - current end)');
INSERT INTO columndef VALUES ('gazegiveturn', 'gazespeaker','TEXT','Gaze Speaker ID');
INSERT INTO columndef VALUES ('gazegiveturn', 'gazeid','TEXT','Identification code of current gaze item');
INSERT INTO columndef VALUES ('gazegiveturn', 'gazestart','FLOAT8','Start time of item in file');
INSERT INTO columndef VALUES ('gazegiveturn', 'gazeend','FLOAT8','End time of item in file');
INSERT INTO columndef VALUES ('gazegiveturn', 'gazedelay','FLOAT8','Delay between gaze-start and next chunk');
INSERT INTO columndef VALUES ('gazegiveturn', 'value','FLOAT8','Gaze value (g)');
