-- Create database tables

-- This script contains MySQL-specific (non-standard) SQL syntax.
-- Specifically in each of the CREATE TABLE statements below, the 
-- use of the "PRIMARY KEY" attribute to specify the primary key, and
-- the use of the AUTO_INCREMENT attribute, to specify that the primary
-- keys should be auto-generated by the database.
--
-- If you are not using MySQL, you will need to change at least those lines.
-- You may also have to change the types of the columns depending upon
-- what types your database supports.

USE surjey;

CREATE TABLE surveys (
  id            INT(6)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(40) NOT NULL,
  description   TEXT
);

CREATE TABLE sections (
  id            INT(6)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(30) NOT NULL,
  description   TEXT,
  position      INT(3)      NOT NULL,
  survey_id     INT(6)      NOT NULL
);

CREATE TABLE questions (
  id               INT(6)       NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name             VARCHAR(30) NOT NULL,
  label            VARCHAR(100) NOT NULL,
  position         INT(3)       NOT NULL,
  display_size     VARCHAR(10),
  answer_labels    TEXT, 
  section_id       INT(6)       NOT NULL,
  question_type_id INT(6)       NOT NULL
);

CREATE TABLE question_types (
  id            INT(6)       NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(30),
  description   TEXT
);

CREATE TABLE responses (
  id                 INT(6)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name               VARCHAR(30) NOT NULL,
  survey_id          INT(6)      NOT NULL,
  respondent_address VARCHAR(20) NOT NULL,
);

CREATE TABLE response_answers (
  id          INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(30) NOT NULL,
  response_id INT(6) NOT NULL,
  question_id INT(6) NOT NULL,
  answer      TEXT
);

CREATE TABLE test (
  id   INT(6)       NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30)
);


-- Init question_types table;

INSERT INTO question_types VALUES (null,"Text","A single-line text field.");
INSERT INTO question_types VALUES (null,"Textbox","A multi-line text box.");
INSERT INTO question_types VALUES (null,"Radio","A group of radio buttons (allows user to choose only one).");
INSERT INTO question_types VALUES (null,"Check","A group of checkboxes (allows user to choose more than one).");
INSERT INTO question_types VALUES (null,"Select","A menu (allows user to choose only one).");
INSERT INTO question_types VALUES (null,"Select Multiple","A list box (allows user to choose more than one).");
