create database pets_database;

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;
INSERT INTO grouptable(user_id,username,group_id) VALUES (1,"manager","manager");
INSERT INTO grouptable(user_id,username,group_id) VALUES (2,"admin","admin");

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;

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');

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;

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; 

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;

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; 

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,
  PRIMARY KEY(ID),
  UNIQUE KEY ID (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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,
  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;

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;

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', ''); 
