
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) collate utf8_general_ci,
  mobile varchar(25),
  address varchar(255),
  country varchar(30),
  lang varchar(3),
  image longblob,
  content_type char(255) DEFAULT NULL,
  last_login int(11) NOT NULL DEFAULT 0,
  added_by text,
  PRIMARY KEY (user_id)
) engine=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

drop table if exists imported_users;
CREATE TABLE imported_users (
  ID int(11) NOT NULL auto_increment,
  user_id int(11),
  users text,
  PRIMARY KEY (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,
  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,
  pos int(2),
  isDeleted int(1) default 0,
  PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
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,
  maxpoints double(10,2) default NULL,
  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 imported_questions;
CREATE TABLE imported_questions (
  ID int(11) NOT NULL auto_increment,
  user_id int(11),
  questions text,
  PRIMARY KEY (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',
  modification_time timestamp NOT NULL default CURRENT_TIMESTAMP,
  global_properties 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,
  lre_keywords text,
  maxpoints double(10,2) default NULL,
  zipID int(11) default NULL,
  isDeleted int(1) default 0,
  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 assessments;
CREATE TABLE assessments (
  ID int(11) NOT NULL auto_increment,
  owner int(11) default NULL,
  title char(255) default NULL,
  creation_time timestamp NOT NULL default 0,
  modification_time timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  description text,
  questions text,
  groups text,
  start_day date default 0,
  start_time time default 0,
  finish_day date default 0,
  finish_time time default 0,
  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,
  assessment_type varchar(255),
  email_content text,
  is_anonymous int(1) default 1,
  hasCertificate int(1) default 1,
  isDeleted int(1) default NULL,
  PRIMARY KEY (ID),
  UNIQUE KEY ID (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

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,
  feedback mediumtext,
  answer_response text,
  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,
  ques_order text,
  quesOnPage int(4) default NULL,
  pos int(11) default NULL,
  unAnsweredQues text,
  start_time datetime,
  end_time datetime,
  elapsed_time time,
  remaining_time time,
  attempt_nr int(3) default NULL,
  score double(10,2) default 0,
  isFeedback int(1) default 0,
  feedback mediumtext,
  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,
  pos int(11) default NULL,
  unAnsweredQues text,
  start_time datetime,
  end_time datetime,
  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,
  answer_response text,
  PRIMARY KEY(ID),
  UNIQUE KEY ID (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

drop table if exists templates;
CREATE TABLE templates (
  ID int(11) NOT NULL auto_increment,
  name varchar(255) default NULL,
  body text,
  owner int(11),
  PRIMARY KEY(ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO templates(name, body, owner) VALUES ('template1', '<body style="background-color: #f2f3fc;"><br /><br /><br /><br /><br /><br /><h1 style="text-align: center;">Certificate nr. [certificate_nr]</h1><br /><br /><h2 style="text-align: center;">[candidate_name]</h2><br /><br /><h3 style="text-align: center;">[certificate_name]</h3><br /><br /><br /><br /><br /><div style="text-align: center;">[annotation]<br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><div style="text-align: right;">[scorer_name]<br />[date]<br /><br /><br /><br /><br /></div></div></body>', 2);

drop table if exists certificates;
CREATE TABLE certificates (
  ID int(11) NOT NULL auto_increment,
  assessment_id int(11),
  isScoresRequired int(1),
  scoresOrPercent char(10),
  required_result double(10,2),
  name varchar(255),
  annotation text,
  template_id int(11),
  isNumberingUnique int(1),
  canRequest int(1),
  PRIMARY KEY(ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

drop table if exists user_certificates;
CREATE TABLE user_certificates (
  ID int(11) NOT NULL auto_increment,
  cert_num int(11),
  name varchar(255),
  user_id int(11),
  assessment_id int(11),
  assessment_quiz int(11),
  attempt_nr int(3),
  content text,
  PRIMARY KEY(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,
  type varchar(255) default NULL,
  county varchar(255),
  town varchar(255),
  members mediumtext,
  PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

drop table if exists schools_properties;
CREATE TABLE schools_properties (
  ID int(1) NOT NULL,
  url varchar(255),
  types text,
  PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO schools_properties VALUES (1, 'http://www.ekk.edu.ee/ehis/failid/koolide_kontaktid.xls', ''); 
