/*
  first version of SurveySez tables
  10/13/2011
  
  Here are a few notes on things below that may not be self evident:
  
  INDEXES: You'll see indexes below for example:
  
  INDEX SurveyID_index(SurveyID)
  
  Any field that has highly unique data that is either searched on or used as a join should be indexed, which speeds up a  
  search on a tall table, but potentially slows down an add or delete
  
  TIMESTAMP: MySQL currently only supports one date field per table to be automatically updated with the current time.  We'll use a 
  field in a few of the tables named LastUpdated:
  
  LastUpdated TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
  
  The other date oriented field we are interested in, DateAdded we'll do by hand on insert with the MySQL function NOW().
  
  CASCADES: In order to avoid orphaned records in deletion of a Survey, we'll want to get rid of the associated Q & A, etc. 
  We therefore want a 'cascading delete' in which the deletion of a Survey activates a 'cascade' of deletions in an 
  associated table.  Here's what the syntax looks like:  
  
  FOREIGN KEY (SurveyID) REFERENCES fl11_surveys(SurveyID) ON DELETE CASCADE
  
  The above is from the Questions table, which stores a foreign key, SurveyID in it.  This line of code tags the foreign key to 
  identify which associated records to delete.
  
  Be sure to check your cascades by deleting a survey and watch all the related table data disappear!
  
  
  Survey 2 Description: "Description of Survey 2: About 281"
  Q1:Do you think this class is hard?
  A1: Yes
  A2: No
  
  Q2: What grade do you expect to get in this class?
  A1: A
  A2: B
  A3: C
  A4: D
  A4: F
  
  
  
  
*/


SET foreign_key_checks = 0; #turn off constraints temporarily

#since constraints cause problems, drop tables first, working backward
DROP TABLE IF EXISTS fl11_responses_answers; 
DROP TABLE IF EXISTS fl11_responses;
DROP TABLE IF EXISTS fl11_answers;
DROP TABLE IF EXISTS fl11_questions;
DROP TABLE IF EXISTS fl11_surveys;
  
#all tables must be of type InnoDB to do transactions, foreign key constraints
CREATE TABLE fl11_surveys(
SurveyID INT UNSIGNED NOT NULL AUTO_INCREMENT,
AdminID INT UNSIGNED DEFAULT 0,
Title VARCHAR(255) DEFAULT '',
Description TEXT DEFAULT '',
DateAdded DATETIME,
LastUpdated TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
TimesViewed INT DEFAULT 0,
Status INT DEFAULT 0,
PRIMARY KEY (SurveyID)
)ENGINE=INNODB; 

#assigning first survey to AdminID == 1
INSERT INTO fl11_surveys VALUES (NULL,1,'Our First Survey','Description of Survey',NOW(),NOW(),0,0); 
INSERT INTO fl11_surveys VALUES (NULL,1,'Our Second Survey','Description of Survey 2: About 281',NOW(),NOW(),0,0); 
INSERT INTO fl11_surveys VALUES (NULL,1,'Our Third Survey','Description of Survey 3:About Your Studies',NOW(),NOW(),0,0); 

#foreign key field must match size and type, hence SurveyID is INT UNSIGNED
CREATE TABLE fl11_questions(
QuestionID INT UNSIGNED NOT NULL AUTO_INCREMENT,
SurveyID INT UNSIGNED DEFAULT 0,
Question TEXT DEFAULT '',
Description TEXT DEFAULT '',
DateAdded DATETIME,
LastUpdated TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (QuestionID),
INDEX SurveyID_index(SurveyID),
FOREIGN KEY (SurveyID) REFERENCES fl11_surveys(SurveyID) ON DELETE CASCADE
)ENGINE=INNODB;

#Survey 1 Questions
INSERT INTO fl11_questions VALUES (NULL,1,'Do You Like Our Website?','We really want to know!',NOW(),NOW());
INSERT INTO fl11_questions VALUES (NULL,1,'Do You Like Cookies?','We like cookies!',NOW(),NOW());
INSERT INTO fl11_questions VALUES (NULL,1,'Favorite Toppings?','We like chocolate!',NOW(),NOW());

#Survey 2 Questions
INSERT INTO fl11_questions VALUES (NULL,2,'Do you think this class is hard?','We think so!',NOW(),NOW());
INSERT INTO fl11_questions VALUES (NULL,2,'What grade do you expect to get in this class?','We will get a good grade!',NOW(),NOW());

#Survey 3 Questions
INSERT INTO fl11_questions VALUES (NULL,3,'Who is your favorite teacher at SCCC?','We like them all!',NOW(),NOW());
INSERT INTO fl11_questions VALUES (NULL,3,'What are you studying?','I want to learn everything!',NOW(),NOW());


CREATE TABLE fl11_answers(
AnswerID INT UNSIGNED NOT NULL AUTO_INCREMENT,
QuestionID INT UNSIGNED DEFAULT 0,
Answer TEXT DEFAULT '',
Description TEXT DEFAULT '',
DateAdded DATETIME,
LastUpdated TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
Status INT DEFAULT 0,
PRIMARY KEY (AnswerID),
INDEX QuestionID_index(QuestionID),
FOREIGN KEY (QuestionID) REFERENCES fl11_questions(QuestionID) ON DELETE CASCADE
)ENGINE=INNODB;

#Survey 1, Question 1 Answers
INSERT INTO fl11_answers VALUES (NULL,1,'Yes','',NOW(),NOW(),0);#1
INSERT INTO fl11_answers VALUES (NULL,1,'No','',NOW(),NOW(),0);#2

#Survey 1, Question 2 Answers
INSERT INTO fl11_answers VALUES (NULL,2,'Yes','',NOW(),NOW(),0);#3
INSERT INTO fl11_answers VALUES (NULL,2,'No','',NOW(),NOW(),0);#4
INSERT INTO fl11_answers VALUES (NULL,2,'Maybe','',NOW(),NOW(),0);#5

#Survey 1, Question 3 Answers
INSERT INTO fl11_answers VALUES (NULL,3,'Chocolate','',NOW(),NOW(),0);#6
INSERT INTO fl11_answers VALUES (NULL,3,'Butterscotch','',NOW(),NOW(),0);#7
INSERT INTO fl11_answers VALUES (NULL,3,'Pineapple','',NOW(),NOW(),0);#8

#Survey 2, Question 1 Answers
INSERT INTO fl11_answers VALUES (NULL,4,'Yes','',NOW(),NOW(),0);#9
INSERT INTO fl11_answers VALUES (NULL,4,'No','',NOW(),NOW(),0);#10

#Survey 2, Question 2 Answers
INSERT INTO fl11_answers VALUES (NULL,5,'A','',NOW(),NOW(),0);#11
INSERT INTO fl11_answers VALUES (NULL,5,'B','',NOW(),NOW(),0);#12
INSERT INTO fl11_answers VALUES (NULL,5,'C','',NOW(),NOW(),0);#13
INSERT INTO fl11_answers VALUES (NULL,5,'D','',NOW(),NOW(),0);#14
INSERT INTO fl11_answers VALUES (NULL,5,'F','',NOW(),NOW(),0);#15

#Survey 3, Question 1 Answers
INSERT INTO fl11_answers VALUES (NULL,6,'Bill Newman','',NOW(),NOW(),0);#16
INSERT INTO fl11_answers VALUES (NULL,6,'Sara Newman','',NOW(),NOW(),0);#17
INSERT INTO fl11_answers VALUES (NULL,6,'Mike Sinkula','',NOW(),NOW(),0);#18
INSERT INTO fl11_answers VALUES (NULL,6,'Raven Gildea','',NOW(),NOW(),0);#19
INSERT INTO fl11_answers VALUES (NULL,6,'Steve Conger','',NOW(),NOW(),0);#20

#Survey 3, Question 2 Answers
INSERT INTO fl11_answers VALUES (NULL,7,'Web Design','',NOW(),NOW(),0);#21
INSERT INTO fl11_answers VALUES (NULL,7,'Web Development','',NOW(),NOW(),0);#22
INSERT INTO fl11_answers VALUES (NULL,7,'Programming','',NOW(),NOW(),0);#23
INSERT INTO fl11_answers VALUES (NULL,7,'Databases','',NOW(),NOW(),0);#24
INSERT INTO fl11_answers VALUES (NULL,7,'Networking','',NOW(),NOW(),0);#25


CREATE TABLE fl11_responses(
ResponseID INT UNSIGNED NOT NULL AUTO_INCREMENT,
SurveyID INT UNSIGNED NOT NULL DEFAULT 0,
DateAdded DATETIME,
PRIMARY KEY (ResponseID),
INDEX SurveyID_index(SurveyID),
FOREIGN KEY (SurveyID) REFERENCES fl11_surveys(SurveyID) ON DELETE CASCADE
)ENGINE=INNODB;

#Three responses to Survey 1
INSERT INTO fl11_responses VALUES (NULL,1,NOW());
INSERT INTO fl11_responses VALUES (NULL,1,NOW());
INSERT INTO fl11_responses VALUES (NULL,1,NOW());

#Three responses to Survey 2
INSERT INTO fl11_responses VALUES (NULL,2,NOW());
INSERT INTO fl11_responses VALUES (NULL,2,NOW());
INSERT INTO fl11_responses VALUES (NULL,2,NOW());

#Three responses to Survey 3
INSERT INTO fl11_responses VALUES (NULL,3,NOW());
INSERT INTO fl11_responses VALUES (NULL,3,NOW());
INSERT INTO fl11_responses VALUES (NULL,3,NOW());

CREATE TABLE fl11_responses_answers(
RQID INT UNSIGNED NOT NULL AUTO_INCREMENT,
ResponseID INT UNSIGNED DEFAULT 0,
QuestionID INT DEFAULT 0,
AnswerID INT DEFAULT 0,
PRIMARY KEY (RQID),
INDEX ResponseID_index(ResponseID),
FOREIGN KEY (ResponseID) REFERENCES fl11_responses(ResponseID) ON DELETE CASCADE
)ENGINE=INNODB;

#Choices to Response 1 to Survey 1
INSERT INTO fl11_responses_answers VALUES (NULL,1,1,1);
INSERT INTO fl11_responses_answers VALUES (NULL,1,2,5);
INSERT INTO fl11_responses_answers VALUES (NULL,1,3,6);
INSERT INTO fl11_responses_answers VALUES (NULL,1,3,7);
INSERT INTO fl11_responses_answers VALUES (NULL,1,3,8);

#Choices to Response 2 to Survey 1
INSERT INTO fl11_responses_answers VALUES (NULL,2,1,2);
INSERT INTO fl11_responses_answers VALUES (NULL,2,2,4);
INSERT INTO fl11_responses_answers VALUES (NULL,2,3,6);
INSERT INTO fl11_responses_answers VALUES (NULL,2,3,7);

#Choices to Response 3 to Survey 1
INSERT INTO fl11_responses_answers VALUES (NULL,3,1,1);
INSERT INTO fl11_responses_answers VALUES (NULL,3,2,3);
INSERT INTO fl11_responses_answers VALUES (NULL,3,3,8);


#Choices to Response 1 to Survey 2
INSERT INTO fl11_responses_answers VALUES (NULL,4,4,10);
INSERT INTO fl11_responses_answers VALUES (NULL,4,5,12);

#Choices to Response 2 to Survey 2
INSERT INTO fl11_responses_answers VALUES (NULL,5,4,9);
INSERT INTO fl11_responses_answers VALUES (NULL,5,5,11);

#Choices to Response 3 to Survey 2
INSERT INTO fl11_responses_answers VALUES (NULL,6,4,9);
INSERT INTO fl11_responses_answers VALUES (NULL,6,5,13);


#Choices to Response 1 to Survey 3
INSERT INTO fl11_responses_answers VALUES (NULL,7,6,16);
INSERT INTO fl11_responses_answers VALUES (NULL,7,7,21);
INSERT INTO fl11_responses_answers VALUES (NULL,7,7,22);

#Choices to Response 2 to Survey 3
INSERT INTO fl11_responses_answers VALUES (NULL,8,6,18);
INSERT INTO fl11_responses_answers VALUES (NULL,8,7,21);

#Choices to Response 3 to Survey 3
INSERT INTO fl11_responses_answers VALUES (NULL,9,6,20);
INSERT INTO fl11_responses_answers VALUES (NULL,9,7,23);
INSERT INTO fl11_responses_answers VALUES (NULL,9,7,25);



SET foreign_key_checks = 1; #turn foreign key check back on
