/* 
    Script to make a selection for the INTAS915 project
    The aim is to get around 300 seconds of speech for 
    each speaker for both Spontaneous speech (Informal and Retold)
    and Read Speech (Text reading, Sentences, and when unavoidable,
    Pseudo Sentences (M66O).
 */
DROP TABLE intas915;

SELECT id, speaker, recnr, texttype, style, duration,
CASE 
 	WHEN  style IN ('I', 'R') THEN TEXT('Spont') 
 	WHEN  NOT style IN ('I', 'R') THEN TEXT('Read') 
END AS ReadSpont
INTO TABLE intas915
FROM translitphoneme
WHERE
    (speaker = 'F20N' AND style in ('I', 'R')) OR
    (speaker = 'F28G' AND style in ('I')
    AND chunkid ~ '^F28G1VI[1-6]$') OR
    (speaker = 'F40L' AND style in ('I')
    AND chunkid ~ '^F40L1VI[1-7]$') OR
    (speaker = 'F60E' AND style in ('I')
    AND chunkid ~ '^F60E1VI[1-9][0-5]*$') OR
    (speaker = 'M15R' AND style in ('I', 'R')) OR
    (speaker = 'M40K' AND style in ('I', 'R')) OR
    (speaker = 'M56H' AND style in ('I', 'R')) OR
    (speaker = 'M66O' AND style in ('I', 'R')) OR
    (speaker = 'F20N' AND texttype = 'V' AND style in ('T')
    AND chunkid ~ '^F20N2VT[1-9][0-8]*$' AND id !~ '^F20N2VT18[C-I]$') OR
    (speaker = 'F28G' AND texttype = 'V' AND style in ('T')
    AND chunkid ~ '^F28G2VT[1-6]$') OR
    (speaker = 'F40L' AND texttype = 'V' AND style in ('T')
    AND chunkid ~ '^F40L2VT[1-8]$' AND id !~ '^F40L2VT8[DEG]$') OR
    (speaker = 'F60E' AND texttype = 'V' AND style in ('T')
    AND chunkid ~ '^F60E2VT[1-9][0-1]*$') OR
    (speaker = 'M15R' AND style in ('T')) OR
    (speaker = 'M40K' AND style in ('T')) OR
    (speaker = 'M56H' AND style in ('T') AND
    (chunkid !~ '^M56H1FT[4-9]$' AND chunkid !~ '^M56H1FT1[0-9]$')) OR
    (speaker = 'M66O' AND style in ('PS'))
    ;
    
GRANT SELECT ON intas915 TO anonymous;
CREATE INDEX intas915_index on intas915 (id);
    
    
