
CREATE TABLE user (
	id INTEGER NOT NULL, 
	username VARCHAR(80) NOT NULL, 
	password VARCHAR(80) NOT NULL, 
	email VARCHAR(120) NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (username), 
	UNIQUE (email)
);
CREATE TABLE IF NOT EXISTS "quiz_questions" (
	"id"	INTEGER UNIQUE,
	"question_id"	INTEGER,
	"quiz_id"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT)
);
--CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE IF NOT EXISTS "quiz_types" (
	"id"	INTEGER UNIQUE,
	"name"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "question_files" (
	"id"	INTEGER UNIQUE,
	"quiz_id"	INTEGER,
	"file_name"	TEXT,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "subjects" (
	"id"	INTEGER UNIQUE,
	"name"	TEXT,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "quiz_results" (
	"id"	INTEGER UNIQUE,
	"file_name"	TEXT,
	"start_time"	REAL,
	"end_time"	REAL,
	"total_questions"	INTEGER,
	"score"	REAL,
	"timed"	INTEGER,
	"student_id"	INTEGER,
	"duration"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "config" (
	"data_path"	INTEGER
);
CREATE TABLE IF NOT EXISTS "question_choices" (
	"id"	INTEGER UNIQUE,
	"question_id"	INTEGER,
	"choice"	TEXT,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "quizes" (
	"id"	INTEGER UNIQUE,
	"name"	TEXT,
	"description"	TEXT,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "choice_types" (
	"id"	INTEGER UNIQUE,
	"name"	TEXT,
	"description"	TEXT,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "choice_type_lookup" (
	"id"	INTEGER NOT NULL UNIQUE,
	"choice_type_id"	INTEGER,
	"name"	INTEGER,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "question_updated" (
	"id"	INTEGER UNIQUE,
	"question_id"	INTEGER,
	"description"	INTEGER,
	"user_id"	INTEGER,
	"time_stamp"	NUMERIC,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "question_review" (
	"id"	INTEGER UNIQUE,
	"nec_version_id"	INTEGER NOT NULL,
	"notes"	TEXT NOT NULL,
	"user_id"	INTEGER NOT NULL,
	"time_stamp"	REAL NOT NULL,
	"question_id"	INTEGER NOT NULL,
	"action_type_id"	INTEGER NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "question_review_action_types" (
	"id"	INTEGER UNIQUE,
	"name"	TEXT,
	"description"	TEXT,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "state_rc" (
	"id"	INTEGER NOT NULL UNIQUE,
	"section"	TEXT NOT NULL,
	"title"	INTEGER NOT NULL,
	"state_id"	INTEGER NOT NULL,
	"rc_version_id"	INTEGER NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "state_ac" (
	"id"	INTEGER NOT NULL UNIQUE,
	"article"	TEXT NOT NULL,
	"title"	TEXT NOT NULL,
	"state_id"	INTEGER NOT NULL,
	"ac_version_id"	INTEGER NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "glossary_term_refs" (
	"id"	INTEGER NOT NULL UNIQUE,
	"type_id"	INTEGER NOT NULL,
	"term_id"	INTEGER NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "nec_language_fail" (
	"id"	INTEGER NOT NULL UNIQUE,
	"notes"	INTEGER NOT NULL,
	"nec_ref"	INTEGER NOT NULL,
	"nec_article_section_id"	INTEGER NOT NULL,
	"nec_version_id"	INTEGER NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "nec_glossary" (
	"id"	INTEGER NOT NULL UNIQUE,
	"term"	TEXT NOT NULL,
	"def"	TEXT,
	"nec_version_id"	INTEGER,
	"refs"	TEXT,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "nec_versions" (
	"id"	INTEGER UNIQUE,
	"year"	TEXT,
	"title"	TEXT,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "nec_tables" (
	"id"	INTEGER NOT NULL UNIQUE,
	"Name"	INTEGER NOT NULL,
	"description"	TEXT NOT NULL,
	"nec_version_id"	INTEGER NOT NULL,
	"nec_article_id"	INTEGER NOT NULL,
	"nec_chapter_id"	INTEGER NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "nec_chapters" (
	"id"	INTEGER NOT NULL UNIQUE,
	"number"	INTEGER NOT NULL,
	"title"	TEXT NOT NULL,
	"description"	TEXT NOT NULL,
	"nec_version_id"	INTEGER NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "nec_article_sections" (
	"id"	INTEGER NOT NULL UNIQUE,
	"number"	REAL NOT NULL,
	"description"	TEXT NOT NULL,
	"article_id"	INTEGER NOT NULL,
	"chapter_id"	INTEGER NOT NULL,
	"nec_version_id"	INTEGER NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "quiz_results_questions" (
	"id"	INTEGER UNIQUE,
	"q_id"	INTEGER,
	"quiz_result_id"	INTEGER,
	"wrong"	INTEGER DEFAULT 1,
	"marked"	INTEGER DEFAULT 0,
	"review_nbr"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "quiz_result_questions_time_intervals" (
	"id"	INTEGER NOT NULL UNIQUE,
	"quiz_id"	INTEGER,
	"qid"	INTEGER,
	"start_t"	REAL,
	"end_t"	REAL,
	"correct"	INTEGER,
	"user_id"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "questions" (
	"id"	INTEGER UNIQUE,
	"subject_id"	TEXT,
	"question"	TEXT,
	"answer"	TEXT,
	"notes"	TEXT,
	"refs"	TEXT,
	"choice_type"	INTEGER,
	"type_id"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "question_type" (
	"id"	INTEGER UNIQUE,
	"name"	TEXT,
	"description"	TEXT,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "question_type_ids" (
	"id"	INTEGER NOT NULL UNIQUE,
	"qid"	INTEGER,
	"qid_type_id"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "code_question_of_the_day" (
	"id"	INTEGER NOT NULL UNIQUE,
	"qid"	INTEGER NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "quiz_question_state" (
	"id"	INTEGER NOT NULL UNIQUE,
	"quiz_state_id"	INTEGER NOT NULL,
	"qid"	INTEGER NOT NULL,
	"q_choice"	INTEGER,
	"correct"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "quiz_state" (
	"id"	INTEGER NOT NULL UNIQUE,
	"user_id"	INTEGER NOT NULL,
	"quiz_id"	INTEGER NOT NULL,
	"start_datetime"	INTEGER NOT NULL,
	"end_datetime"	INTEGER NOT NULL,
	"quiz_question_state_id"	INTEGER NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "users" (
	"id"	INTEGER DEFAULT 1000 UNIQUE,
	"user_name"	TEXT NOT NULL UNIQUE,
	"admin"	INTEGER,
	"password"	BLOB,
	"salt"	BLOB,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "tmp_additional_nec_glossary" (
	"id"	INTEGER NOT NULL UNIQUE,
	"term"	TEXT,
	"definition"	TEXT,
	"nec_ver"	TEXT DEFAULT 'NEC_2011_HANDBOOK',
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "nec_articles" (
	"id"	INTEGER NOT NULL UNIQUE,
	"number"	INTEGER NOT NULL,
	"title"	TEXT NOT NULL,
	"definition"	TEXT,
	"chapter_id"	INTEGER,
	"nec_version_id"	INTEGER NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "wac_glossary" (
	"id"	INTEGER NOT NULL UNIQUE,
	"term"	TEXT NOT NULL,
	"definition"	TEXT NOT NULL,
	"term_referance_id"	INTEGER,
	"refs"	TEXT,
	"wac_version_id"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "rcw_glossary" (
	"id"	INTEGER NOT NULL UNIQUE,
	"term"	TEXT NOT NULL,
	"definition"	TEXT,
	"refs"	INTEGER,
	"rcw_version_id"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "quiz_state_types" (
        "id"    INTEGER NOT NULL UNIQUE,
        "name"  TEXT,
        PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "quiz_in_progress_state_times" (
        "id"    INTEGER NOT NULL UNIQUE,
        "quiz_id"       INTEGER,
        "in_progress_id"        INTEGER,
        "datetime"      STRING(19),
        "quiz_state_type_id"    INTEGER,
        PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE role (
   id INTEGER UNIQUE,
   name   VARCHAR(80),
   description   VARCHAR(255),
   UNIQUE (name),
   PRIMARY KEY(id AUTOINCREMENT)
);
CREATE TABLE roles_users (
   id INTEGER UNIQUE,
   user_id INTEGER,
   role_id INTEGER,
   PRIMARY KEY(id AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "quiz_in_progress_tmp" (
        "id"    INTEGER NOT NULL UNIQUE,
        "user_id"       INTEGER NOT NULL,
        "quiz_id"       INTEGER NOT NULL,
        "state_id"      INTEGER NOT NULL DEFAULT 1,
        PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "quiz_in_progress" (
        "id"    INTEGER NOT NULL UNIQUE,
        "user_id"       INTEGER NOT NULL,
        "quiz_id"       INTEGER NOT NULL,
        "state_id"      INTEGER NOT NULL DEFAULT 1,
"quiz_hh"       INTEGER,
"quiz_mm"INTEGER,
"quiz_ss"       INTEGER,
        PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "quiz_in_progress_questions_answered" (
   "id"INTEGER NOT NULL UNIQUE,
   "question_id"INTEGER NOT NULL,
   "latest_start_time"INTEGER,
   "duration_time"INTEGER,
   "choice"INTEGER NOT NULL,
   "in_progress_id"INTEGER,
   "visit_cnt"TEGER DEFAULT 1,
   "marked"INTEGER DEFAULT 0,
   PRIMARY KEY("id" AUTOINCREMENT)
   );
CREATE TABLE promo_codes (
        id INTEGER NOT NULL,
        code VARCHAR(64) NOT NULL,
        discount REAL NOT NULL,
        partner VARCHAR(128),
        description VARCHAR(512) NOT NULL,
        PRIMARY KEY (id),
        UNIQUE (code)
);
--CREATE VIEW "test_results" AS select id, file_name,  (end_time - start_time)/60 as mins_taken_for_test, student_id, total_questions as nbr_of_q, (end_time -start_time)/total_questions as sec_per_q, ((end_time -start_time)/total_questions)/60 as mins_per_q, score  from quiz_results
/* test_results(id,file_name,mins_taken_for_test,student_id,nbr_of_q,sec_per_q,mins_per_q,score) */;
CREATE TABLE quiz_in_progress_types (
	id INTEGER NOT NULL, 
	name VARCHAR(64) NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (name)
);
CREATE TABLE package_types (
	id INTEGER NOT NULL, 
	name VARCHAR(64) NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (name)
);
CREATE TABLE package_quizes (
        id INTEGER NOT NULL,
        p_id INTEGER NOT NULL, /* foreign key to packages */
        quiz_id INTEGER,
        PRIMARY KEY (id)
);
CREATE TABLE user_quizes (
        id INTEGER NOT NULL,
        user_id INTEGER,
        quiz_id INTEGER, /* packages_id */
        PRIMARY KEY (id)
);
CREATE TABLE user_packages (
        id INTEGER NOT NULL,
        user_id INTEGER,
        package_id INTEGER, /* packages_id */
        PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS "quiz_time_limits" (
        "id"    INTEGER NOT NULL UNIQUE,
        "quiz_id"       INTEGER NOT NULL,
	"quiz_hh"       INTEGER,
	"quiz_mm"	INTEGER,
	"quiz_ss"       INTEGER,
        PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE packages (
        id INTEGER NOT NULL,
	package_type_id INTEGER NOT NULL, /*foreignkey to package_types */
        name VARCHAR(64) NOT NULL,
        description VARCHAR(512) NOT NULL,
        PRIMARY KEY (id)
);

;; ------ test string --------

-- CREATE TABLE packages (
--         id INTEGER NOT NULL,
-- 	package_type_id INTEGER NOT NULL, /*foreignkey to package_types */
--         name VARCHAR(64) NOT NULL,
--         description VARCHAR(512) NOT NULL,
--         PRIMARY KEY (id)
-- );
-- CREATE TABLE promo_codes (
--         id INTEGER NOT NULL,
--         code VARCHAR(64) NOT NULL,
--         discount REAL NOT NULL,
--         partner VARCHAR(128),
--         description VARCHAR(512) NOT NULL,
--         PRIMARY KEY (id),
--         UNIQUE (code)
-- );
