DROP SEQUENCE questions_seq;
DROP SEQUENCE users_seq;
--DROP SEQUENCE results_seq;
DROP SEQUENCE security_seq;
DROP SEQUENCE modules_seq;
DROP SEQUENCE courses_seq;

DROP PROCEDURE questions_proc;
DROP PROCEDURE users_proc;
--DROP PROCEDURE results_proc;
DROP PROCEDURE security_proc;
DROP PROCEDURE modules_proc;
DROP PROCEDURE courses_proc;

DROP TABLE questions;
DROP TABLE results;
DROP TABLE security;
DROP TABLE users;
DROP TABLE modules;
DROP TABLE courses;

COMMIT WORK;



CREATE TABLE questions (
  q_id int NOT NULL, -- need to do own auto_incrementing
  m_id int NOT NULL,
--  required enum('yes','no') NOT NULL,
  required VARCHAR NOT NULL,
--  type enum('Multiple Choice','True or False','Essay') NOT NULL,
  type VARCHAR NOT NULL,
  weight tinyint(4) NOT NULL,
  question VARCHAR NOT NULL,
  answer VARCHAR NOT NULL,
  alt_1 VARCHAR NOT NULL,
  alt_2 VARCHAR NOT NULL,
  alt_3 VARCHAR NOT NULL,
  PRIMARY KEY  (q_id),
  FOREIGN KEY  (m_id) REFERENCES modules,
  CHECK (required in ('yes','no')),
  CHECK (type in ('Multiple Choice','True or False','Essay'))
);


CREATE SEQUENCE questions_seq;

COMMIT WORK;

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

COMMIT WORK;

"CREATE TRIGGER questions_trig ON questions
	BEFORE INSERT
	REFERENCING NEW q_id as new_id
BEGIN
	EXEC SQL PREPARE c1 CALL questions_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;

-- Add a few questions.

INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (1, 'no', 'Multiple Choice', 1, 'What does GNU stand for?', 'Gnu''s not Unix', 'General nondescript unix', 'Generic new Unix', 'Gotta new U-Boataaaa');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (1, 'yes', 'True or False', 1, 'Linux is a tradmark of Linus Torvalds', 'True', 'empty', 'empty', 'empty');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (1, 'yes', 'True or False', 3, 'Maplebot, by Adrian Montero, is a 1337 bot written in C', 'True', 'empty', 'empty', 'empty');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (1, 'no', 'Multiple Choice', 1, 'What package management software does Debian use?', 'APT, the advanced package took', 'RPM, the Redhat Package Manager', 'the Ports system', 'You have to compile from source');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (1, 'yes', 'True or False', 4, 'Redhat was the first Linux distribution', 'False', 'empty', 'empty', 'empty');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (1, 'yes', 'Multiple Choice', 2, 'What is the command to list all files in a directory in Linux?', 'ls -a', 'ls', 'ls -l', 'ls --color');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (1, 'yes', 'Multiple Choice', 2, 'What is the command to view the current process list?', 'ps', 'grep ', 'rm', 'ustat');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (1, 'no', 'True or False', 5, 'Emacs is a better editor than VI', 'True', 'empty', 'empty', 'empty');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (1, 'no', 'Multiple Choice', 2, 'Which of the below is NOT a linux command', 'mkp', 'cat', 'tar', 'mv');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (1, 'no', 'Multiple Choice', 1, 'What does the command ''grep'' do', 'searches for occuranes of a string in a file', 'copies files from one directory to another', 'groups similar files together', 'renames conflicting filenames');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (1, 'no', 'True or False', 1, 'Apache is an open source ftp server', 'False', 'empty', 'empty', 'empty');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (1, 'no', 'True or False', 2, 'CGI stands for Common Graphics Interface', 'False', 'empty', 'empty', 'empty');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (1, 'no', 'True or False', 2, 'Perl is the hackers swiss-army knife', 'True', 'empty', 'empty', 'empty');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (2, 'yes', 'True or False', 1, 'OS X is based of off OS 9', 'False', 'empty', 'empty', 'empty');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (2, 'yes', 'True or False', 2, 'The top of the line new imacs come with a built-in dvd drive and look like a lamp.', 'True', 'empty', 'empty', 'empty');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (2, 'yes', 'Multiple Choice', 1, 'It has been said that OS X has a nice interface.  What do you think is the reason for this?', 'Just look at it!  It''s badass!', 'Mac users are zealots', 'Mac designers spent a lot of time browsing themes.org', 'Who knows?');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (2, 'yes', 'Multiple Choice', 4, 'What is the codename of OS X''s kernel?', 'Darwin', 'Jehova', 'Morman', 'Christian');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (2, 'no', 'True or False', 1, 'BSOD stands for ''Blue Screen of Death''', 'True', 'empty', 'empty', 'empty');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (2, 'yes', 'Multiple Choice', 1, 'How do you shut down a windows 95 pc?', 'Click on the start menu, then click on Shutdown', 'Throw the PC out the window', 'Windows 95 is so stable, you never have to shut down', 'I have no clue');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (2, 'no', 'True or False', 3, 'Pretty much any configuration change to a win9x computer will require a reboot.', 'True', 'empty', 'empty', 'empty');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (2, 'yes', 'True or False', 1, 'Bill Gates is rich.', 'True', 'empty', 'empty', 'empty');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (2, 'no', 'Multiple Choice', 1, 'What kernel is windows XP based off of?', 'Windows NT', 'Windows 95', 'Windows CE', 'Windows 3.1');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3)  VALUES (2, 'yes', 'True or False', 1, 'Internet Explorer doesn''t come bundled with Windows XP', 'False', 'empty', 'empty', 'empty');
INSERT INTO questions (m_id,required,type,weight,question,answer,alt_1,alt_2,alt_3) VALUES (3, 'yes', 'True or False', 3, 'The Customer is always right.', 'True', 'empty', 'empty', 'empty');

-- END questions


COMMIT WORK;



CREATE TABLE users (
  user_id int NOT NULL, --auto_increment, have to do our own
  username varchar NOT NULL,
  password varchar NOT NULL,
  email varchar(100) NOT NULL,
  real_name varchar(30) NOT NULL,  -- this will be formatted  
  first_name varchar(20) NOT NULL, -- these will be lowercase   -- added 05/26/05
  middle_name varchar(20) NOT NULL, -- added 05/26/05
  last_name varchar(20) NOT NULL,   -- added 05/26/05
  g_id int NOT NULL,
  date_joined timestamp NOT NULL,
  admin tinyint(4) NOT NULL,
-- addition stuff:

  material_file varchar,

  zip VARCHAR(10) NOT NULL,
  zipplus4 VARCHAR(6),
  address1 VARCHAR(50) NOT NULL,
  address2 VARCHAR(50) NOT NULL,
  address3 VARCHAR(50) NOT NULL,  -- added 05/26/05
  country VARCHAR(50) NOT NULL,
  state VARCHAR(20) NOT NULL,
  city VARCHAR(30) NOT NULL,
  region VARCHAR(20) NOT NULL,
  degrees VARCHAR NOT NULL,
  company VARCHAR(30) NOT NULL,
  profession VARCHAR NOT NULL,
  position VARCHAR NOT NULL,
  disclosures VARCHAR NOT NULL,
  info_public tinyint(4) NOT NULL,
  phone VARCHAR,
  asha_id VARCHAR(15) NOT NULL,  -- added 05/26/05
  ssn VARCHAR(15) NOT NULL,   -- added 05/26/05
  provider_refnum VARCHAR(20) NOT NULL,   -- added 05/26/05
  

  PRIMARY KEY  (user_id)
--  UNIQUE KEY user_id_2 (user_id),
--  KEY user_id (user_id),
--  KEY g_id (g_id)
);

COMMIT WORK;

-- USERS: Buld the sequence, procedure, and trigger needed to do auto incrementing with the users table.

CREATE SEQUENCE users_seq;

COMMIT WORK;

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

COMMIT WORK;

"CREATE TRIGGER users_trig ON users
	BEFORE INSERT
	REFERENCING NEW user_id as new_id
BEGIN
	EXEC SQL PREPARE c1 CALL users_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;

--CALL users_proc(i); CALL users_proc(i);

INSERT INTO users VALUES (1, 'admin', 'AheqX0OeA2apo', 'none', 'Administrative user', 1, '2002-04-05 10:14:31', 1,'','','','','USA','OH','Wooster','Here','','',1);
INSERT INTO users VALUES (2, 'guest', 'YZ/gu.pNcd5sE', 'djresonance', 'guest user', 1, '2002-04-05 11:09:21', 0,'','','','','USA','OH','Cleveland','There','All','',1);


COMMIT WORK;

-- done with users, now onto results


CREATE TABLE results (
--  r_id int NOT NULL auto_increment,
  m_id int NOT NULL,
  user_id int NOT NULL,
  points int NOT NULL,
  points_possible int NOT NULL,
  score tinyint(4) NOT NULL,
  answers LONG VARCHAR NOT NULL,
  date_taken timestamp NOT NULL,
--  PRIMARY KEY  (r_id)
  PRIMARY KEY (user_id,m_id,date_taken),
  FOREIGN KEY (user_id) REFERENCES users
);

--CREATE SEQUENCE results_seq;

--COMMIT WORK;

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

--COMMIT WORK;

--"CREATE TRIGGER results_trig ON results
--	BEFORE INSERT
--	REFERENCING NEW r_id as new_id
--BEGIN
--	EXEC SQL PREPARE c1 CALL results_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 some sample data for the results table.

--INSERT INTO results VALUES (1, 2, 17, 25, 68, 'Question #1 (2): What is the command to view the current process list?:\r\nUser''s answer: ustat\r\nCorrect answer: ps\r\n\r\nQuestion #2 (1): Linux is a tradmark of Linus Torvalds:\r\nUser''s answer: True\r\nCorrect answer: True\r\n\r\nQuestion #3 (3): Maplebot, by Adrian Montero, is a 1337 bot written in C:\r\nUser''s answer: True\r\nCorrect answer: True\r\n\r\nQuestion #4 (4): Redhat was the first Linux distribution:\r\nUser''s answer: True\r\nCorrect answer: False\r\n\r\nQuestion #5 (2): What is the command to list all files in a directory in Linux?:\r\nUser''s answer: ls -a\r\nCorrect answer: ls -a\r\n\r\nQuestion #6 (5): Emacs isa  better editor than VI:\r\nUser''s answer: True\r\nCorrect answer: True\r\n\r\nQuestion #7 (2): Perl is the hackers swiss-army knife:\r\nUser''s answer: False\r\nCorrect answer: True\r\n\r\nQuestion #8 (2): Which of the below is NOT a linux command:\r\nUser''s answer: mkp\r\nCorrect answer: mkp\r\n\r\nQuestion #9 (2): CGI stands for Common Graphics Interface:\r\nUser''s answer: False\r\nCorrect answer: False\r\n\r\nQuestion #10 (2): What does GNU stand for?:\r\nUser''s answer: Gnu\\''s not Unix\r\nCorrect answer: Gnu''s not Unix\r\n\r\n', '2002-04-05 11:14:44');
--INSERT INTO results VALUES (2, 1, 2, 8, 25, 'Question #: 1 (2): The top of the line new imacs come with a built-in dvd drive and look like a lamp.:\nUser''s answer:  True\nCorrect answer:  True\n\nQuestion #: 2 (1): It has been said that OS X has a nice interface.  What do you think is the reason for this?:\nUser''s answer:  Mac users are zealots\nCorrect answer:  Just look at it!  It''s badass!\n\nQuestion #: 3 (4): What is the codename of OS X''s kernel?:\nUser''s answer:  Christian\nCorrect answer:  Darwin\n\nQuestion #: 4 (1): OS X is based of off OS 9:\nUser''s answer:  True\nCorrect answer:  False\n\n', '2002-05-14 11:07:14');


COMMIT WORK;


CREATE TABLE security (
  l_id int NOT NULL,
  user_id int NOT NULL,
  ip varchar(15) NOT NULL,
  page varchar(255) NOT NULL,
  date_occured timestamp NOT NULL,
  PRIMARY KEY  (l_id)
);

CREATE SEQUENCE security_seq;

COMMIT WORK;

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

COMMIT WORK;

"CREATE TRIGGER security_trig ON security
	BEFORE INSERT
	REFERENCING NEW l_id as new_id
BEGIN
	EXEC SQL PREPARE c1 CALL security_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;

CREATE TABLE courses (
  c_id int NOT NULL, --auto_increment,
  ceu FLOAT,
  name VARCHAR NOT NULL,
  description LONG VARCHAR NOT NULL,
  objectives LONG VARCHAR NOT NULL,

  times_viewed INT NOT NULL,
  asha INT NOT NULL,

  PRIMARY KEY (c_id)
);

CREATE SEQUENCE courses_seq;

COMMIT WORK;

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

COMMIT WORK;

"CREATE TRIGGER courses_trig ON courses
	BEFORE INSERT
	REFERENCING NEW c_id as new_id
BEGIN
	EXEC SQL PREPARE c1 CALL security_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;

--CALL courses_proc(i);

--INSERT INTO courses VALUES (1,'First','This is just the first one');

COMMIT WORK;

CREATE TABLE modules (
  m_id int NOT NULL, -- auto_increment,
  c_id int NOT NULL,
  name VARCHAR NOT NULL,
  prereq int NOT NULL,
  description VARCHAR NOT NULL,
  available VARCHAR,

-- stuff relating to the test
  title varchar(255) NOT NULL,
  notes VARCHAR NOT NULL,
  num_required int NOT NULL,
  num_random int NOT NULL,
--  start_date datetime NOT NULL default '0000-00-00 00:00:00',
--  end_date datetime NOT NULL default '0000-00-00 00:00:00',
--  g_id int NOT NULL default '0',

  times_taken int NOT NULL,

  material_file varchar,
  material_type varchar,
  reference int NOT NULL,  

  PRIMARY KEY  (m_id),
  FOREIGN KEY  (c_id) REFERENCES courses
);

CREATE SEQUENCE modules_seq;

COMMIT WORK;

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

COMMIT WORK;

"CREATE TRIGGER modules_trig ON modules
	BEFORE INSERT
	REFERENCING NEW m_id as new_id
BEGIN
	EXEC SQL PREPARE c1 CALL modules_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;

--CALL modules_proc(i);

--INSERT INTO modules VALUES (1,1,'Linux',0,'Linux quiz','This is the example linux quiz that comes with phpTest.  Better answer ''true'' to the ''emacs is better than VI'' question, it''s worth 5 points ;)\r\n\r\n ', 5, 5);

CREATE TABLE zips (
	zip VARCHAR NOT NULL,
	lon FLOAT,
	lat FLOAT,
	name VARCHAR,
	state VARCHAR,
	PRIMARY KEY(zip));

COMMIT WORK;