
CREATE TYPE proof_age AS ENUM ( 'lt1', 'lt3', 'lt5', 'gte5');
CREATE TYPE proof_type AS ENUM ( 'yki_intermediate', 'yki_advanced', 'other', 'course_english_degree', 'completed_finnish_upper_secondary', 'completed_finnish_degree');
CREATE TYPE device_type AS ENUM ( 'pc', 'mobile', 'tablet', 'unknown');
CREATE TYPE miniexam_response_type AS ENUM ( 'trans_defn', 'topic', 'donotknow');
CREATE TYPE miniexam_response_lang AS ENUM ( 'en', 'hu', 'ru', 'fi');
CREATE TYPE marker AS ENUM ( 'ann1', 'ann2', 'corr', 'final');


CREATE TABLE miniexam_mark(miniexam_response_id INTEGER NOT NULL, marker marker NOT NULL, mark VARCHAR NOT NULL);
CREATE TABLE miniexam_response(id INTEGER PRIMARY KEY, participant_id INTEGER NOT NULL, word VARCHAR NOT NULL, "type" miniexam_response_type NOT NULL, lang miniexam_response_lang, response VARCHAR);
CREATE TABLE selfassess_response(session_id INTEGER NOT NULL, word VARCHAR NOT NULL, time_usecs BIGINT NOT NULL, rating INTEGER NOT NULL);
CREATE TABLE selfassess_session(id INTEGER PRIMARY KEY, participant_id INTEGER NOT NULL, device device_type NOT NULL, time_secs INTEGER NOT NULL, "day" INTEGER NOT NULL);
CREATE TABLE participant_language(participant_id INTEGER NOT NULL, "language" VARCHAR NOT NULL, "level" INTEGER NOT NULL);
CREATE TABLE participant(id INTEGER PRIMARY KEY, cefr_selfassess_speaking INTEGER, cefr_selfassess_writing INTEGER, cefr_selfassess_listening_comprehension INTEGER, cefr_selfassess_reading_comprehension INTEGER, cefr_proof_speaking INTEGER, cefr_proof_writing INTEGER, cefr_proof_listening_comprehension INTEGER, cefr_proof_reading_comprehension INTEGER, lived_in_finland INTEGER, proof_age proof_age, proof_type proof_type, miniexam_time_secs INTEGER, miniexam_day INTEGER);



