/*
drop database krihvel;
create database krihvel;
*/
use krihvel;

/* plugins fw */
CREATE TABLE IF NOT EXISTS plugins (
    pluginID char(255) UNIQUE NOT NULL PRIMARY KEY, 
    installed boolean, 
    version char(255)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS pages (
    pluginID char(255),
    pageID char(255),
    title char(255),
    grps char(255),
    roles char(255),
    validators char(255),
    FOREIGN KEY (pluginID) REFERENCES plugins (pluginID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS types (
    pluginID char(255),
    typeID char(255),
    title char(255),
    icon char(255),
    icon_method char(255),
    task boolean,
    typeIdent char(255),
    FOREIGN KEY (pluginID) REFERENCES plugins (pluginID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS menus (
    pluginID char(255),
    pageID char(255),
    title char(255),
    grp char(255),
    page char(255),
    menu char(255),
    roles char(255),
    valid char(255),
    FOREIGN KEY (pluginID) REFERENCES plugins (pluginID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS schools (
    schoolID int(5) AUTO_INCREMENT PRIMARY KEY,
    schoolName char(255)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS users (
    id int(5) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    uname char(255) default NULL,
    pwd char(255) default NULL,
    roles char(6) default "000000",
    schoolID int(5) default NULL,
    approved BOOLEAN,
    FOREIGN KEY (schoolID) REFERENCES schools (schoolID)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS schooladmins (
    userID int(5) NOT NULL AUTO_INCREMENT,
    schoolID int(5),
    FOREIGN KEY (schoolID) REFERENCES schools(schoolID) ON DELETE CASCADE,
    FOREIGN KEY (userID) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS userinfo (
    id int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userid int(5) default NULL UNIQUE KEY,
    firstname char(255) DEFAULT "",
    lastname char(255) DEFAULT "",
    email char(255) default NULL,
    language char(3) default 'EST',
    sex enum('boy','girl') default 'boy',
    assisted BOOLEAN default true,
    animated BOOLEAN,
    homepage char(255) default NULL,
    tableviews BOOLEAN,
    FOREIGN KEY (userid) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS groups (
    groupID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    groupName char(255),
    schoolID int(5),
    FOREIGN KEY (schoolID) REFERENCES schools (schoolID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS grouprelations (
    relID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    groupID int(5),
    userID int(5),
    FOREIGN KEY (userID) REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY (groupID) REFERENCES groups (groupID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS folders (
    folderID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    parentFolder int(5),
    userID int(5),
    title varchar(1500),
    FOREIGN KEY (userID) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS exhibitions (
    exhibitionID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userID int(5),
    title varchar(1500),
    security ENUM('public', 'private') DEFAULT 'public',
    FOREIGN KEY (userID) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS exponates (
    exponateID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    answerID int(5),
    exhibitionID int(5),
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS resources (
    resourceID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    deleted int(1) DEFAULT 0,
    type char(255),
    folderid int(5) DEFAULT NULL,
    title char(255),
    userID int(5),
    created TIMESTAMP DEFAULT NOW(),
    modified TIMESTAMP,
    description TEXT,
    keywords TEXT,
    authors TEXT,
    rating decimal(2,1),
    state ENUM('draft', 'final') DEFAULT 'draft',
    security ENUM('public', 'private') DEFAULT 'public',
    waramuUID char(255),
    FOREIGN KEY (userID) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS crossword (
    resourceID int(5),
    mainQuestion char(255),
    mainQuestionCol int(3),
    questions TEXT,
    data TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS paper (
    resourceID int(5),
    data TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS gadget (
    resourceID int(5),
    gadget TEXT,
    data TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS video (
    resourceID int(5),
    video TEXT,
    data TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS pairs (
    resourceID int(5),
    data TEXT,
    obs1 TEXT,
    typs1 TEXT,
    obs2 TEXT,
    typs2 TEXT,
    gamerows TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS math (
    resourceID int(5),
    data TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS fillin (
    resourceID int(5),
    data TEXT,
    correct TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS draw (
    resourceID int(5),
    data TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS keyboard (
    resourceID int(5),
    data TEXT,
    speed INT DEFAULT 1,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS memory (
    resourceID int(5),
    data TEXT,
    images TEXT,
    imagesa TEXT,
    imagesb TEXT,
    level INT DEFAULT 1,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS dialogue (
    resourceID int(5),
    data TEXT,
    diatype char(25),
    maxfouls INT DEFAULT 6,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS millionaire (
    resourceID int(5),
    data TEXT,
    questions TEXT,
    answers TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS jeopardy (
    resourceID int(5),
    data TEXT,
    categories TEXT,
    last TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS hangman (
    resourceID int(5),
    data TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS clock (
    resourceID int(5),
    data TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS timeline (
    resourceID int(5),
    data TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS images (
    resourceID int(5),
    imageUID char(22),
    imageName char(255),
    size int(5),
    mime char(255),
    width int(1),
    height int(1),
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS assignments (
    assignmentID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    assignmentName char(255),
    groupID int(5),
    resourceID int(5),
    userID int(5),
    created TIMESTAMP DEFAULT NOW(),
    startline TIMESTAMP,
    deadline TIMESTAMP,
    FOREIGN KEY (groupID) REFERENCES groups (groupID) ON DELETE CASCADE,
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE,
    FOREIGN KEY (userID) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS assignment_relations (
    assRelID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    assignmentID int(5),
    userID int(5),
    FOREIGN KEY (userID) REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY (assignmentID) REFERENCES assignments (assignmentID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS answers (
    answerID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    resourceID int(5),
    assignmentID int(5),
    userID int(5),
    created TIMESTAMP DEFAULT NOW(),
    grade int(1),
    comment TEXT,
    exponate int(1) DEFAULT 0,
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE,
    FOREIGN KEY (userID) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS crossword_answers (
    answerID int(5),
    answer TEXT,
    correct TEXT,
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS paper_answers (
    answerID int(5),
    answer TEXT,
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS video_answers (
    answerID int(5),
    answer TEXT,
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS pairs_answers (
    answerID int(5),
    answer TEXT,
    correct TEXT,
    selection TEXT,
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS math_answers (
    answerID int(5),
    answer TEXT,
    correct TEXT,
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS fillin_answers (
    answerID int(5),
    answer TEXT,
    correct TEXT,
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS draw_answers (
    answerID int(5),
    data TEXT,
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS keyboard_answers (
    answerID int(5),
    correct TEXT,
    wrong TEXT,
    missed TEXT,
    percent TEXT,
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS memory_answers (
    answerID int(5),
    try TEXT,
    time TEXT,
    points TEXT,
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS hangman_answers (
    answerID int(5),
    feedback TEXT,
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS timeline_answers (
    answerID int(5),
    time TEXT,
    data TEXT,
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS dialogue_answers (
    answerID int(5),
    score INT DEFAULT 0,
    fouls INT DEFAULT 0,
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS millionaire_answers (
    answerID int(5),
    level INT DEFAULT 0,
    points INT DEFAULT 0,
    FOREIGN KEY (answerID) REFERENCES answers (answerID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

/* CHAT */
CREATE TABLE IF NOT EXISTS chatmessages (
    uid char(255),
    msg varchar(1000),
    sent TIMESTAMP DEFAULT NOW(),
    room INT DEFAULT 0
) ENGINE=MEMORY;

CREATE TABLE IF NOT EXISTS chatusers (
    uid char(255) PRIMARY KEY,
    login TIMESTAMP DEFAULT NOW(),
    logout TIMESTAMP DEFAULT '0000-00-00 00:00:00',
    roomid INT
) ENGINE=MEMORY;

CREATE TABLE IF NOT EXISTS chatrooms ( 
    ChatroomID INT PRIMARY KEY AUTO_INCREMENT,
    title varchar(500),
    topic varchar(1000)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS fti (
    resourceID INT PRIMARY KEY,
    content TEXT,
    FULLTEXT (content),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS gallery (
    resourceID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    images text
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS links (
    resourceID int PRIMARY KEY,
    link varchar(1000),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS comments (
    commentID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    resourceID int,
    userID int,
    created TIMESTAMP DEFAULT NOW(),
    comment TEXT,
    grade int,
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS targetgroup (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    resourceID int,
    targetgroup varchar(500),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS language (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    resourceID int,
    language varchar(500),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS resourcetype (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    resourceID int,
    resourcetype varchar(500),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS lre (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    resourceID int,
    lre varchar(500),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS difficulty (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    resourceID int,
    difficulty varchar(500),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS usedimages (
    resourceID int(5),
    imageUID char(22),
    FOREIGN KEY (resourceID) REFERENCES resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;