CREATE TABLE IF NOT EXISTS prefix_site (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    url char(255) NOT NULL UNIQUE KEY,
    title char(255) NOT NULL,
    description TEXT,
    founder bigint(20) UNSIGNED DEFAULT NULL,
    installed BOOLEAN DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_users (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(255) DEFAULT '',
    last_name VARCHAR(255) DEFAULT '',
    email VARCHAR(255) DEFAULT NULL,
    language VARCHAR(6) NOT NULL DEFAULT 'et',
    role INT(1) NOT NULL DEFAULT 1,
    registered DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    last_login DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_images (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    creator bigint(20) UNSIGNED default NULL,
    name char(255) DEFAULT "",
    type char(255) DEFAULT "",
    size INT DEFAULT 0,
    width INT DEFAULT 0,
    height INT DEFAULT 0,
    location char(255) DEFAULT "",
    created TIMESTAMP DEFAULT NOW(),
    locked BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_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,
    time_limit time default NULL,
    randomization int(1) default 0,
    items_per_page ENUM('one','all') DEFAULT 'all',
    deleted BOOLEAN DEFAULT FALSE,
    locked BOOLEAN DEFAULT FALSE,
    bos_assignment bigint(20) UNSIGNED DEFAULT NULL,
    bos_course bigint(20) UNSIGNED DEFAULT NULL,
    bos_course_title varchar(255) DEFAULT '',
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_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,
    bos_learner bigint(20) UNSIGNED DEFAULT NULL,
    bos_answer bigint(20) UNSIGNED DEFAULT NULL,
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE SET NULL,
    FOREIGN KEY (assessment) REFERENCES prefix_assessments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_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 prefix_users (id) ON DELETE SET NULL,
    FOREIGN KEY (assessment) REFERENCES prefix_assessments (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_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 prefix_tests (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_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 prefix_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 prefix_tag_relations (
    tag bigint(20) UNSIGNED NOT NULL,
    object bigint(20) UNSIGNED NOT NULL,
    object_type varchar(20) NOT NULL,
    FOREIGN KEY (tag) REFERENCES prefix_tags (id) ON DELETE CASCADE,
    PRIMARY KEY(tag, object, object_type)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_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 prefix_categories (id) ON DELETE SET NULL,
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE SET NULL,
    FOREIGN KEY (test_section) REFERENCES prefix_test_sections (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_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 prefix_base_item (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_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 prefix_base_item (id) ON DELETE CASCADE,
    FOREIGN KEY (candidate) REFERENCES prefix_candidates (id) ON DELETE CASCADE,
    UNIQUE candidate_answer (base_id, candidate)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_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 prefix_base_item (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_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 prefix_base_item (id) ON DELETE CASCADE,
    FOREIGN KEY (candidate) REFERENCES prefix_candidates (id) ON DELETE CASCADE,
    UNIQUE candidate_answer (base_id, candidate)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
