ALTER TABLE users ADD last_name varchar(20);
COMMIT WORK;
ALTER TABLE users ADD address3 varchar(50);
COMMIT WORK;
ALTER TABLE users ADD asha_id varchar(15);
COMMIT WORK;
ALTER TABLE users ADD ssn varchar(15);
COMMIT WORK;
ALTER TABLE users ADD provider_refnum varchar(20);
COMMIT WORK;

UPDATE users SET first_name = '';
UPDATE users SET middle_name = '';
UPDATE users SET last_name = '';
UPDATE users SET address3 = '';
UPDATE users SET asha_id = '';
UPDATE users SET ssn = '';
UPDATE users SET provider_refnum = '';
COMMIT WORK;

ALTER TABLE modules ADD module_before_quiz int; 
COMMIT WORK;

UPDATE modules SET module_before_quiz = 0;
COMMIT WORK;

CREATE TABLE questions_evaluation 
(
q_id int NOT NULL,
c_id int NOT NULL,
type varchar NOT NULL,
question varchar NOT NULL,
alt_1 varchar NOT NULL,
alt_2 varchar NOT NULL,
alt_3 varchar NOT NULL,
alt_4 varchar NOT NULL,
alt_5 varchar NOT NULL,
PRIMARY KEY  (q_id),
FOREIGN KEY  (c_id) REFERENCES courses
);

CREATE TABLE evaluation_statistics 
(
c_id int NOT NULL,
q_id int,
q_id2 int,
alt_1 int NOT NULL,
alt_2 int NOT NULL,
alt_3 int NOT NULL,
alt_4 int NOT NULL,
alt_5 int NOT NULL,
FOREIGN KEY  (c_id) REFERENCES courses
);

COMMIT WORK;

CREATE SEQUENCE questions_evaluation_seq;

COMMIT WORK;

"CREATE PROCEDURE questions_evaluation_proc
	(i int)
	RETURNS (ret int)
BEGIN
	IF i IS NULL THEN
		EXEC SEQUENCE questions_evaluation_seq.NEXT INTO ret;
	ELSE
		ret := i;
	END IF
END";

COMMIT WORK;

"CREATE TRIGGER questions_evaluation_trig ON questions_evaluation
	BEFORE INSERT
	REFERENCING NEW q_id as new_id
BEGIN
	EXEC SQL PREPARE c1 CALL questions_evaluation_proc(?);
	EXEC SQL EXECUTE c1 USING (new_id) INTO (new_id);
	EXEC SQL FETCH c1;
	EXEC SQL CLOSE c1;
	EXEC SQL DROP c1;
END";

COMMIT WORK;


ALTER TABLE courses ADD default_evaluation int;
COMMIT WORK;
ALTER TABLE courses ADD ceu_description varchar(50);
COMMIT WORK;
ALTER TABLE courses ADD c_num varchar(10);
COMMIT WORK;
UPDATE courses SET default_evaluation = 1;
COMMIT WORK;
UPDATE courses SET ceu_description = '';
COMMIT WORK;

CREATE TABLE default_evaluation 
(
q_id int NOT NULL,
question varchar NOT NULL,
type varchar NOT NULL,
alt_1 varchar NOT NULL,
alt_2 varchar NOT NULL,
alt_3 varchar NOT NULL,
alt_4 varchar NOT NULL,
alt_5 varchar NOT NULL
);

COMMIT WORK;

CREATE SEQUENCE default_evaluation_seq;

COMMIT WORK;

"CREATE PROCEDURE default_evaluation_proc
	(i int)
	RETURNS (ret int)
BEGIN
	IF i IS NULL THEN
		EXEC SEQUENCE default_evaluation_seq.NEXT INTO ret;
	ELSE
		ret := i;
	END IF
END";

COMMIT WORK;

"CREATE TRIGGER default_evaluation_trig ON default_evaluation
	BEFORE INSERT
	REFERENCING NEW q_id as new_id
BEGIN
	EXEC SQL PREPARE c1 CALL default_evaluation_proc(?);
	EXEC SQL EXECUTE c1 USING (new_id) INTO (new_id);
	EXEC SQL FETCH c1;
	EXEC SQL CLOSE c1;
	EXEC SQL DROP c1;
END";

COMMIT WORK;

INSERT INTO default_evaluation (question,type,alt_1,alt_2,alt_3,alt_4,alt_5) VALUES ('The content of the material presented in this course was','MC','Not at all useful','Somewhat useful','Very useful','empty','empty');
INSERT INTO default_evaluation (question,type,alt_1,alt_2,alt_3,alt_4,alt_5) VALUES ('The module quizzes were','MC','Too hard','About right','Too easy','empty','empty');
INSERT INTO default_evaluation (question,type,alt_1,alt_2,alt_3,alt_4,alt_5) VALUES ('The time required to complete this course was','MC','Too long','About right','Too short','empty','empty');
INSERT INTO default_evaluation (question,type,alt_1,alt_2,alt_3,alt_4,alt_5) VALUES ('This experience will support my ability to pursue the goal of AAC','MC','Disagree','Neutral','Agree','empty','empty');
INSERT INTO default_evaluation (question,type,alt_1,alt_2,alt_3,alt_4,alt_5) VALUES ('I will recommend that others participate in this activity','MC','Disagree','Neutral','Agree','empty','empty');
INSERT INTO default_evaluation (question,type,alt_1,alt_2,alt_3,alt_4,alt_5) VALUES ('Are you an ASHA member','TF','True','False','empty','empty','empty');

COMMIT WORK;

DROP TABLE zips;
COMMIT WORK;

CREATE TABLE zips (
	zip VARCHAR(5) NOT NULL,
	state VARCHAR(2) NOT NULL,
	latitude VARCHAR(10) NOT NULL,
	longitude VARCHAR(10) NOT NULL,
	UNIQUE ( zip )
);

COMMIT WORK;