# phpMyAdmin SQL Dump
# version 2.5.3
# http://www.phpmyadmin.net

# --------------------------------------------------------

#
# Table structure for table `prefix_questionnaire`
#

CREATE TABLE `prefix_questionnaire` (
  `id` int(11) NOT NULL auto_increment,
  `course` int(11) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `summary` text NOT NULL,
  `qtype` INT( 10 ) NOT NULL default 0,
  `respondenttype` enum('fullname','anonymous') NOT NULL DEFAULT 'fullname',
  `resp_eligible` enum( 'all', 'students', 'teachers' ) NOT NULL DEFAULT 'all',
  `resp_view` INT( 2 ) NOT NULL default 0,
  `opendate` INT( 10 ) NOT NULL,
  `closedate` INT( 10 ) NOT NULL,
  `resume` INT( 2 ) NOT NULL default '0',
  `navigate` INT( 2 ) NOT NULL default '0',
  `sid` int(11) NOT NULL default '0',
  `timemodified` int(10) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `sid` (`sid`)
) TYPE=MyISAM;

#
# Table structure for table `prefix_questionnaire_attempts`
#

CREATE TABLE `prefix_questionnaire_attempts` (
  `id` int(11) NOT NULL auto_increment,
  `qid` int(11) NOT NULL default '0',
  `userid` int(11) NOT NULL default '0',
  `rid` int(10) unsigned NOT NULL default '0',
  `timemodified` int(10) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

# Use this script to create and populate the phpESP tables. This
# should be executed _after_ the mysql_create.sql script. If you are
# upgrading an existing phpESP database, please read "docs/UPDATING"
# before doing anything else.
# 
# To execute this script via the mysql CLI, run:
#   mysql -u root -p phpesp < mysql_populate.sql
# If you used a database name other than "phpesp", use it in place
# of "phpesp" in the command line.
#

#-- ...............................................................
#-- ....................... USERS/GROUPS ..........................
#-- ...............................................................

# realm (group) table

#-- ...............................................................
#-- ..................... SURVEYS/FORMS ...........................
#-- ...............................................................

# table of different surveys available
CREATE TABLE prefix_questionnaire_survey (
    id			INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name		CHAR(64) NOT NULL,
    owner		CHAR(16) NOT NULL,
    realm		CHAR(64) NOT NULL,
    public		ENUM('Y','N') NOT NULL DEFAULT 'Y',
    status		INT UNSIGNED NOT NULL DEFAULT '0',
    title		CHAR(255) NOT NULL,
    email		CHAR(64),
    subtitle	TEXT,
    info		TEXT,
    theme		CHAR(64),
    thanks_page	CHAR(255),
    thank_head	CHAR(255),
    thank_body	TEXT,
    PRIMARY KEY (id),
    KEY `name` (`name`),
    KEY `owner` (`owner`)
);

# types of questions
CREATE TABLE prefix_questionnaire_question_type (
    id				INT UNSIGNED NOT NULL AUTO_INCREMENT,
    type			CHAR(32) NOT NULL,
    has_choices		ENUM('Y','N') NOT NULL,
    response_table	CHAR(32) NOT NULL,
    PRIMARY KEY (id)
);

# table of the questions for all the surveys
CREATE TABLE prefix_questionnaire_question (
    id			INT UNSIGNED NOT NULL AUTO_INCREMENT,
    survey_id	INT UNSIGNED NOT NULL,
    name		CHAR(30) NOT NULL,
    type_id		INT UNSIGNED NOT NULL,
    result_id	INT UNSIGNED,
    length		INT NOT NULL DEFAULT 0,
    precise		INT NOT NULL DEFAULT 0,
    position	INT UNSIGNED NOT NULL,
    content		TEXT NOT NULL,
    required	ENUM('Y','N') NOT NULL DEFAULT 'N',
    deleted		ENUM('Y','N') NOT NULL DEFAULT 'N',
    public		ENUM('Y','N') NOT NULL DEFAULT 'Y',
    PRIMARY KEY (id)
);

# table of the choices (possible answers) of each question
CREATE TABLE prefix_questionnaire_question_choice (
    id			INT UNSIGNED NOT NULL AUTO_INCREMENT,
    question_id	INT UNSIGNED NOT NULL,
    content		TEXT NOT NULL,
    value		TEXT,
    PRIMARY KEY (id)
);

#-- ...............................................................
#-- ..................... RESPONSE DATA ...........................
#-- ...............................................................

# this table holds info to distinguish one servey response from another
# (plus timestamp, and username if known)
CREATE TABLE prefix_questionnaire_response (
    id			INT UNSIGNED NOT NULL AUTO_INCREMENT,
    survey_id	INT UNSIGNED NOT NULL,
    submitted	INT(10) UNSIGNED NOT NULL DEFAULT 0,
    complete	ENUM('Y','N') NOT NULL DEFAULT 'N',
    username	CHAR(64),
    PRIMARY KEY (id)
);

# answers to boolean questions (yes/no)
CREATE TABLE prefix_questionnaire_response_bool (
    `id` int(11) NOT NULL auto_increment,
    response_id	INT UNSIGNED NOT NULL,
    question_id	INT UNSIGNED NOT NULL,
    choice_id	ENUM('Y','N') NOT NULL,
    PRIMARY KEY (id),
    KEY response_question (response_id,question_id)
);

# answers to single answer questions (radio, boolean, rate) (chose one of n)
CREATE TABLE prefix_questionnaire_response_single (
    `id` int(11) NOT NULL auto_increment,
    response_id	INT UNSIGNED NOT NULL,
    question_id	INT UNSIGNED NOT NULL,
    choice_id	INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    KEY response_question (response_id,question_id)
);

# answers to questions where multiple responses are allowed
# (checkbox, select multiple)
CREATE TABLE prefix_questionnaire_response_multiple (
    id			INT UNSIGNED NOT NULL AUTO_INCREMENT,
    response_id	INT UNSIGNED NOT NULL,
    question_id	INT UNSIGNED NOT NULL,
    choice_id	INT UNSIGNED NOT NULL,
    PRIMARY KEY(id)
);

# answers to rank questions
CREATE TABLE prefix_questionnaire_response_rank (
    `id` int(11) NOT NULL auto_increment,
    response_id	INT UNSIGNED NOT NULL,
    question_id	INT UNSIGNED NOT NULL,
    choice_id	INT UNSIGNED NOT NULL,
    rank		INT NOT NULL,
    PRIMARY KEY (id),
    KEY response_question (response_id,question_id,choice_id)
);

# answers to any fill in the blank or essay question
CREATE TABLE prefix_questionnaire_response_text (
    `id` int(11) NOT NULL auto_increment,
    response_id	INT UNSIGNED NOT NULL,
    question_id INT UNSIGNED NOT NULL,
    response	TEXT,
    PRIMARY KEY (id),
    KEY response_question (response_id,question_id)
);

# answers to any Other: ___ questions
CREATE TABLE prefix_questionnaire_response_other (
    `id` int(11) NOT NULL auto_increment,
    response_id	INT UNSIGNED NOT NULL,
    question_id INT UNSIGNED NOT NULL,
    choice_id	INT UNSIGNED NOT NULL,
    response	TEXT,
    PRIMARY KEY (id),
    KEY response_question (response_id,question_id,choice_id)
);

# answers to any date questions
CREATE TABLE prefix_questionnaire_response_date (
    `id` int(11) NOT NULL auto_increment,
    response_id	INT UNSIGNED NOT NULL,
    question_id INT UNSIGNED NOT NULL,
    response	DATE,
    PRIMARY KEY (id),
    KEY response_question (response_id,question_id)
);

# populate the types of questions
INSERT INTO prefix_questionnaire_question_type VALUES ('1','Yes/No','N','response_bool');
INSERT INTO prefix_questionnaire_question_type VALUES ('2','Text Box','N','response_text');
INSERT INTO prefix_questionnaire_question_type VALUES ('3','Essay Box','N','response_text');
INSERT INTO prefix_questionnaire_question_type VALUES ('4','Radio Buttons','Y','response_single');
INSERT INTO prefix_questionnaire_question_type VALUES ('5','Check Boxes','Y','response_multiple');
INSERT INTO prefix_questionnaire_question_type VALUES ('6','Dropdown Box','Y','response_single');
# INSERT INTO question_type VALUES ('7','Rating','N','response_rank');
INSERT INTO prefix_questionnaire_question_type VALUES ('8','Rate (scale 1..5)','Y','response_rank');
INSERT INTO prefix_questionnaire_question_type VALUES ('9','Date','N','response_date');
INSERT INTO prefix_questionnaire_question_type VALUES ('10','Numeric','N','response_text');
INSERT INTO prefix_questionnaire_question_type VALUES ('99','Page Break','N','');
INSERT INTO prefix_questionnaire_question_type VALUES ('100','Section Text','N','');
