create database pets_database;

drop table if exists usertable;
CREATE TABLE usertable (
  user_id int(11) NOT NULL auto_increment,
  username char(50) NOT NULL,
  password varchar(32) NOT NULL,
  PRIMARY KEY (user_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

drop table if exists grouptable;
CREATE TABLE grouptable (
  user_id int(11) NOT NULL,
  username char(50) NOT NULL,
  group_id char(20) NOT NULL,
  PRIMARY KEY (user_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

drop table if exists profiles;
CREATE TABLE profiles (
  user_id int(11) NOT NULL,
  username char(50),  
  password varchar(32),
  user_role char(20) NOT NULL,
  first_name varchar(255),
  last_name varchar(255),
  email varchar(255),
  mobile varchar(25),
  address varchar(255),
  country varchar(30),
  lang varchar(3),
  image longblob,
  added_by int(11),
  PRIMARY KEY (user_id)
) engine=MyISAM DEFAULT CHARSET=utf8;

drop table if exists subgroups;
CREATE TABLE subgroups(
  group_id int(11) NOT NULL auto_increment,
  title char(100) default NULL,
  owner int(11) default NULL,
  members text,
  status varchar(30) default 'private',
  attributes text,
  PRIMARY KEY  (group_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

drop table if exists subgroup_attributes;
CREATE TABLE subgroup_attributes (
  ID int(11) NOT NULL auto_increment,
  name varchar(255) default NULL,
  isDeleted int(1) default 0,
  PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO subgroup_attributes (name) VALUES ('Name');
INSERT INTO subgroup_attributes (name) VALUES ('School');
INSERT INTO subgroup_attributes (name) VALUES ('Class');

drop table if exists questions;
CREATE TABLE questions (
  ID int(11) NOT NULL auto_increment,
  owner int(11) default NULL,
  title varchar(1000) default NULL,
  ques_type varchar(255) default NULL,
  xmlname varchar(255) default NULL,
  html_uid varchar(255) default NULL,
  zipID int(11) default NULL,
  response_id varchar(255) default NULL,
  questiontext varchar(255) default NULL,
  html longtext,
  head mediumtext,
  body mediumtext,
  pointvariables char(255) default NULL,
  othervariables char(255) default NULL,
  keywords text,
  description text,
  difficulty varchar(255) default NULL,
  lang char(10) default NULL,
  licence varchar(255) default NULL,
  author varchar(255) default NULL,
  PRIMARY KEY  (ID),
  UNIQUE KEY ID (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

drop table if exists tests;
CREATE TABLE tests (
  ID int(11) NOT NULL auto_increment,
  owner int(11) default NULL,
  title char(255) default NULL,
  status char(10) default 'public',
  fulfillment_time time default NULL,
  global_properties int(1) default 0,
  randomization int(1) default 0,
  questions text,
  keywords text,
  description text,
  difficulty varchar(255) default NULL,
  lang char(10) default NULL,
  licence varchar(255) default NULL,
  zipID int(11) default NULL,
  PRIMARY KEY  (ID),
  UNIQUE KEY ID (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

drop table if exists assessments;
CREATE TABLE assessments (
  ID int(11) NOT NULL auto_increment,
  owner int(11) default NULL,
  title char(255) default NULL,
  group_id int(11) default NULL,
  tests text,
  start_day date,
  start_time time,
  finish_day date,
  finish_time time,
  is_time_limit int(1) default 0,
  time_limit time default NULL,
  randomization int(1) default 0,
  user_reactivation int(1) default 0,
  attempts int(3) default NULL,
  ques_on_page int(4) default 5,
  assessment_type varchar(255),
  PRIMARY KEY (ID),
  UNIQUE KEY ID (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

drop table if exists zipstore;
CREATE TABLE zipstore (
  ID int(11) NOT NULL auto_increment,
  zip longblob,
  filename char(255) collate utf8_bin default NULL,
  PRIMARY KEY  (ID)
) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

drop table if exists answers;
CREATE TABLE answers (
  ID int(11) NOT NULL auto_increment,
  user_id int(11),
  assessment_id int(11),
  quiz_id int(11),
  ques_id int(11),
  points double(10,2) default NULL,
  answerHtml longtext,
  answerHead mediumtext,
  answerBody mediumtext,
  PRIMARY KEY(ID),
  UNIQUE KEY ID (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

drop table if exists assessment_quizzes;
CREATE TABLE assessment_quizzes (
  ID int(11) NOT NULL auto_increment,
  user_id int(11) default NULL,
  assessment_id int(11) default NULL,
  quesOnPage int(4) default NULL,
  ques_id int(11) default NULL,
  pos int(11) default NULL,
  unAnsweredQues text,
  start_time datetime,
  end_time datetime,
  elapsed_time time,
  isCompleted int(1) default 0,
  PRIMARY KEY (ID),
  UNIQUE KEY ID (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

drop table if exists questionnaires;
CREATE TABLE questionnaires (
  token char(11) NOT NULL,
  user_id int(11) default NULL,
  assessment_id int(11) default NULL,
  quesOnPage int(4) default NULL,
  ques_id int(11) default NULL,
  pos int(11) default NULL,
  unAnsweredQues text,
  start_time datetime,
  end_time datetime,
  elapsed_time time,
  isCompleted int(1) default 0,
  PRIMARY KEY (token),
  UNIQUE KEY token (token)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

drop table if exists questionnaire_answers;
CREATE TABLE questionnaire_answers (
  ID int(11) NOT NULL auto_increment,
  token char(11),
  assessment_id int(11),
  ques_id int(11),
  answerHtml longtext,
  answerHead mediumtext,
  answerBody mediumtext,
  PRIMARY KEY(ID),
  UNIQUE KEY ID (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

drop table if exists schools;
CREATE TABLE schools (
  ID int(11) NOT NULL auto_increment,
  name varchar(255) default NULL,
  members mediumtext,
  PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO schools (name) VALUES ('Keila Gümnaasium');
INSERT INTO schools (name) VALUES ('Kolga Keskkool');
INSERT INTO schools (name) VALUES ('Tallinna Reaalkool');
INSERT INTO schools (name) VALUES ('Tallinna Tehnikagümnaasium');
