-- http://www.phpmyadmin.net
--
-- converted to postgres by Harry Burgin, King Edward VI Grammar School, Louth, UK

-- --------------------------------------------------------
-- 
-- Table structure for table prefix_questionnaire
-- 

CREATE TABLE prefix_questionnaire (
    id SERIAL PRIMARY KEY,
    course int NOT NULL default 0,
    name varchar(255) NOT NULL default '',
    summary text NOT NULL default '',
    qtype INT NOT NULL default 0,
    respondenttype text check(respondenttype in ('fullname','anonymous')) NOT NULL DEFAULT 'fullname',
    resp_eligible text check(resp_eligible in ('all', 'students', 'teachers' )) NOT NULL DEFAULT 'all',
    resp_view INT NOT NULL default 0,
    opendate INT NOT NULL default 0,
    closedate INT NOT NULL default 0,
    resume INT NOT NULL default 0,
    navigate INT NOT NULL default 0,
    sid int NOT NULL default 0,
    timemodified int NOT NULL default 0
    );

CREATE INDEX prefix_questionnaire_sid_idx ON prefix_questionnaire (sid);

-- 
-- Table structure for table prefix_questionnaire_attempts
-- 

CREATE TABLE prefix_questionnaire_attempts (
    id SERIAL PRIMARY KEY,
    qid int NOT NULL default 0,
    userid int NOT NULL default 0,
    rid int check(rid >= 0) NOT NULL default 0,
    timemodified int NOT NULL default 0
    );

-- 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 SERIAL PRIMARY KEY,
    name VARCHAR(64) NOT NULL default ' ',
    owner VARCHAR(16) NOT NULL default ' ',
    realm VARCHAR(64) NOT NULL default ' ',
    public VARCHAR(1) check(public in ('Y','N')) NOT NULL DEFAULT 'Y',
    status INT NOT NULL DEFAULT 0,
    title VARCHAR(255) NOT NULL default ' ',
    email VARCHAR(64),
    subtitle TEXT,
    info TEXT,
    theme VARCHAR(64),
    thanks_page VARCHAR(255),
    thank_head VARCHAR(255),
    thank_body TEXT
    );

CREATE INDEX prefix_questionnaire_survey_owner_idx ON prefix_questionnaire_survey (owner);


-- types of questions
CREATE TABLE prefix_questionnaire_question_type (
    id SERIAL PRIMARY KEY,
    type VARCHAR(32) NOT NULL default ' ',
    has_choices VARCHAR(1) check(has_choices in ('Y','N')) NOT NULL default 'N',
    response_table VARCHAR(32) NOT NULL default ' '
    );


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


-- table of the questions for all the surveys
CREATE TABLE prefix_questionnaire_question (
    id SERIAL PRIMARY KEY,
    survey_id INT NOT NULL default 0,
    name VARCHAR(30) NOT NULL default ' ',
    type_id INT NOT NULL default 0,
    result_id INT,
    length INT NOT NULL DEFAULT 0,
    precise INT NOT NULL DEFAULT 0,
    position INT NOT NULL default 0,
    content TEXT NOT NULL default ' ',
    required VARCHAR(1) check(required in ('Y','N')) NOT NULL DEFAULT 'N',
    deleted VARCHAR(1) check(deleted in ('Y','N')) NOT NULL DEFAULT 'N',
    public VARCHAR(1) check(public in ('Y','N')) NOT NULL DEFAULT 'Y'
    );


-- table of the choices (possible answers) of each question
CREATE TABLE prefix_questionnaire_question_choice (
    id SERIAL PRIMARY KEY,
    question_id INT NOT NULL default 0,
    content TEXT NOT NULL default ' ',
    value TEXT
    );


---- ...............................................................
---- ..................... 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 SERIAL PRIMARY KEY,
    survey_id INT NOT NULL default 0,
    submitted INT NOT NULL DEFAULT 0,
    complete VARCHAR(1) check(complete in ('Y','N')) NOT NULL DEFAULT 'N',
    username VARCHAR(64)
    );


-- answers to boolean questions (yes/no)
CREATE TABLE prefix_questionnaire_response_bool (
    id SERIAL PRIMARY KEY,
    response_id INT NOT NULL default 0,
    question_id INT NOT NULL default 0,
    choice_id VARCHAR(1) check(choice_id in ('Y','N')) NOT NULL default 'N'
    );
CREATE INDEX prefix_questionnaire_response_bool_response_question_idx ON prefix_questionnaire_response_bool (response_id,question_id);


-- answers to single answer questions (radio, boolean, rate) (chose one of n)
CREATE TABLE prefix_questionnaire_response_single (
    id SERIAL PRIMARY KEY,
    response_id INT NOT NULL default 0,
    question_id INT NOT NULL default 0,
    choice_id INT NOT NULL default 0
    );
CREATE INDEX prefix_questionnaire_response_single_response_question_idx ON prefix_questionnaire_response_single (response_id,question_id);


-- answers to questions where multiple responses are allowed
-- (checkbox, select multiple)
CREATE TABLE prefix_questionnaire_response_multiple (
    id SERIAL PRIMARY KEY,
    response_id INT NOT NULL default 0,
    question_id INT NOT NULL default 0,
    choice_id INT NOT NULL default 0
    );


-- answers to rank questions
CREATE TABLE prefix_questionnaire_response_rank (
    id SERIAL PRIMARY KEY,
    response_id INT NOT NULL default 0,
    question_id INT NOT NULL default 0,
    choice_id INT NOT NULL default 0,
    rank INT NOT NULL default 0
    );
CREATE INDEX prefix_questionnaire_response_rank_response_question_idx ON prefix_questionnaire_response_rank (response_id,question_id,choice_id);


-- answers to any fill in the blank or essay question
CREATE TABLE prefix_questionnaire_response_text (
    id SERIAL PRIMARY KEY,
    response_id INT NOT NULL default 0,
    question_id INT NOT NULL default 0,
    response TEXT
    );
CREATE INDEX prefix_questionnaire_response_text_response_question_idx ON prefix_questionnaire_response_text (response_id,question_id);


-- answers to any Other: ___ questions
CREATE TABLE prefix_questionnaire_response_other (
    id SERIAL PRIMARY KEY,
    response_id INT NOT NULL default 0,
    question_id INT NOT NULL default 0,
    choice_id INT NOT NULL default 0,
    response TEXT
    );
CREATE INDEX prefix_questionnaire_response_other_response_question_idx ON prefix_questionnaire_response_other (response_id,question_id,choice_id);


-- answers to any date questions
CREATE TABLE prefix_questionnaire_response_date (
    id SERIAL PRIMARY KEY,
    response_id INT NOT NULL default 0,
    question_id INT NOT NULL default 0,
    response DATE
    );
CREATE INDEX prefix_questionnaire_response_date_response_question_idx ON prefix_questionnaire_response_date (response_id,question_id);
