-- @author Joel Aufrecht (joel@aufrecht.org)
-- @creation-date 2004-02-01
-- @cvs-id $Id: vocabulary-train-tables-create.sql,v 1.1 2004/02/23 23:00:07 joela Exp $
--

create table vocab_word_list (
        id              serial
                           primary key,
        user_id         integer
                          not null
                          references users,
        word_id         integer
                          not null
                          references vocab_word,
        locale          varchar(30)
                            references ad_locales,
        date_added      timestamptz,
        package_id      integer 
                          not null
                            references apm_packages,
        unique     (user_id, word_id, package_id)
);

comment on table vocab_word_list is '
Each record is one word which one user has been assigned.';

create table vocab_test (
        id              serial
                           primary key,
        test_type       varchar(30)
                          not null,
        test_subject    integer,
        question        text,
        answer          text,
        locale_a        varchar(30)
                            references ad_locales,
        locale_b        varchar(30)
                            references ad_locales,
        package_id      integer 
                          not null
                            references apm_packages
);

comment on table vocab_test is '
Each record is one test.  Question and Answer may be null, which implies they are derived; or populated, which could be unique data or caching, depending on the test type.';

comment on column vocab_test.test_subject is '
Depending on test_type, this is the id of a word, sentence, or other object.';

create table vocab_test_subject (
        test_id         integer
                          not null
                          references vocab_test (id),
        subject_id      integer,
        unique (test_id, subject_id)
);

comment on table vocab_test_subject is '
If a test has multiple subjects, store them here.';

create table vocab_user_test_map (
        id              serial
                           primary key,
        user_id         integer
                          not null
                          references users,
        test_id         integer
                          not null
                          references vocab_test (id),
        date_added      timestamptz,
        unique (user_id, test_id)
);

comment on table vocab_user_test_map is '
Each record is the assignment of one test to one user.';


create table vocab_test_result (
        test_id         integer
                          not null
                          references vocab_test (id),
        user_id         integer
                          not null
                          references users,
        date            timestamptz,
        answer1         varchar(500),
        result1         integer,
        answer2         varchar(500),
        result2         integer,
        answer3         varchar(500),
        result3         integer
);