CREATE TABLE IF NOT EXISTS tester_institutions (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS tester_assessments (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title varchar(255) NOT NULL,
    description TEXT,
    instructions TEXT,
    beginning datetime DEFAULT 0,
    ending datetime DEFAULT 0,
    creator bigint(20) UNSIGNED DEFAULT NULL,
    created TIMESTAMP DEFAULT NOW(),
    modified TIMESTAMP,
    proctor bigint(20) UNSIGNED DEFAULT NULL,
    time_limit time default NULL,
    randomization int(1) default 0,
    items_per_page ENUM('one','all') DEFAULT 'all',
    preview_for_proctor int(1) default 0,
    deleted BOOLEAN DEFAULT FALSE,
    locked BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (creator) REFERENCES tester_users (id) ON DELETE SET NULL,
    FOREIGN KEY (proctor) REFERENCES tester_users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS tester_candidates (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    creator bigint(20) UNSIGNED DEFAULT NULL,
    first_name VARCHAR(255) DEFAULT '',
    last_name VARCHAR(255) DEFAULT '',
    email VARCHAR(255) DEFAULT NULL,
    assessment bigint(20) UNSIGNED NOT NULL,
    token VARCHAR(40) DEFAULT '',
    beginning datetime DEFAULT 0,
    ending datetime DEFAULT 0,
    answered_count int(3) DEFAULT 0,
    gave_up BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (creator) REFERENCES tester_users (id) ON DELETE SET NULL,
    FOREIGN KEY (assessment) REFERENCES tester_assessments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS tester_tests (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title varchar(255) NOT NULL,
    description TEXT,
    language varchar(2) NOT NULL DEFAULT 'et',
    creator bigint(20) UNSIGNED DEFAULT NULL,
    created TIMESTAMP DEFAULT NOW(),
    modified TIMESTAMP,
    status ENUM('draft', 'final') DEFAULT 'draft',
    security ENUM('public', 'private') DEFAULT 'public',
    deleted BOOLEAN DEFAULT FALSE,
    locked BOOLEAN DEFAULT FALSE,
    assessment bigint(20) UNSIGNED DEFAULT NULL,
    FOREIGN KEY (creator) REFERENCES tester_users (id) ON DELETE SET NULL,
    FOREIGN KEY (assessment) REFERENCES tester_assessments (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS tester_test_sections (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title varchar(255) NOT NULL,
    position int(2) NOT NULL,
    test bigint(20) UNSIGNED DEFAULT NULL,
    deleted BOOLEAN DEFAULT FALSE,
    locked BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (test) REFERENCES tester_tests (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS tester_categories (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title varchar(255) NOT NULL,
    parent bigint(20) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS tester_tags (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS tester_tag_relations (
    tag bigint(20) UNSIGNED NOT NULL,
    object bigint(20) UNSIGNED NOT NULL,
    object_type varchar(20) NOT NULL,
    FOREIGN KEY (tag) REFERENCES tester_tags (id) ON DELETE CASCADE,
    PRIMARY KEY(tag, object, object_type)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS tester_base_item (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    type varchar(255) NOT NULL,
    title varchar(255) NOT NULL,
    item_body TEXT,
    description TEXT,
    language varchar(2) NOT NULL DEFAULT 'et',
    difficulty int(1) DEFAULT 1,
    category bigint(20) UNSIGNED DEFAULT NULL,
    creator bigint(20) UNSIGNED DEFAULT NULL,
    created TIMESTAMP DEFAULT NOW(),
    modified TIMESTAMP,
    status ENUM('draft', 'final') DEFAULT 'draft',
    security ENUM('public', 'private') DEFAULT 'public',
    deleted BOOLEAN DEFAULT FALSE,
    locked BOOLEAN DEFAULT FALSE,
    test_section bigint(20) UNSIGNED DEFAULT NULL,
    position int(3),
    FOREIGN KEY (category) REFERENCES tester_categories (id) ON DELETE SET NULL,
    FOREIGN KEY (creator) REFERENCES tester_users (id) ON DELETE SET NULL,
    FOREIGN KEY (test_section) REFERENCES tester_test_sections (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS tester_multiple_choice (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    base_id bigint(20) UNSIGNED NOT NULL,
    choices TEXT,
    score DOUBLE(4,1) DEFAULT 0,
    FOREIGN KEY (base_id) REFERENCES tester_base_item (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS tester_multiple_choice_answers (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    base_id bigint(20) UNSIGNED NOT NULL,
    candidate bigint(20) UNSIGNED NOT NULL,
    chosen TEXT, 
    score DOUBLE(4,1) DEFAULT 0,
    FOREIGN KEY (base_id) REFERENCES tester_base_item (id) ON DELETE CASCADE,
    FOREIGN KEY (candidate) REFERENCES tester_candidates (id) ON DELETE CASCADE,
    UNIQUE candidate_answer (base_id, candidate)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS tester_multiple_response (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    base_id bigint(20) UNSIGNED NOT NULL,
    choices TEXT,
    FOREIGN KEY (base_id) REFERENCES tester_base_item (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS tester_multiple_response_answers (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    base_id bigint(20) UNSIGNED NOT NULL,
    candidate bigint(20) UNSIGNED NOT NULL,
    chosen TEXT, 
    score DOUBLE(4,1) DEFAULT 0,
    FOREIGN KEY (base_id) REFERENCES tester_base_item (id) ON DELETE CASCADE,
    FOREIGN KEY (candidate) REFERENCES tester_candidates (id) ON DELETE CASCADE,
    UNIQUE candidate_answer (base_id, candidate)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

