/*
============================================================
File name   : mysql_db_structure.sql
Begin       : 2004-04-28
Last Update : 2006-10-01

Description : TCExam database structure.
Database    : MySQL 4.1+

Author: Nicola Asuni
(c) Copyright:
              Tecnick.com S.r.l.
              Via Ugo Foscolo n.19
              09045 Quartu Sant'Elena (CA)
              ITALY
              www.tecnick.com
              info@tecnick.com

License: GNU GENERAL PUBLIC LICENSE v.2
         http://www.gnu.org/copyleft/gpl.html
============================================================
*/

/* Tables */

Create table tce_sessions (
	cpsession_id Varchar(32) NOT NULL,
	cpsession_expiry Datetime NOT NULL,
	cpsession_data Text NOT NULL,
 Primary Key (cpsession_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Create table tce_users (
	user_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
	user_name Varchar(255) NOT NULL,
	user_password Varchar(255) NOT NULL,
	user_email Varchar(255),
	user_regdate Datetime NOT NULL,
	user_ip Varchar(15) NOT NULL,
	user_firstname Varchar(255),
	user_lastname Varchar(255),
	user_birthdate Date,
	user_birthplace Varchar(255),
	user_regnumber Varchar(255),
	user_ssn Varchar(255),
	user_level Smallint(3) UNSIGNED NOT NULL DEFAULT 1,
	user_verifycode Varchar(32),
	UNIQUE (user_verifycode),
 Primary Key (user_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Create table tce_subjects (
	subject_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
	subject_name Varchar(255) NOT NULL,
	subject_description Text,
	subject_enabled Bool NOT NULL DEFAULT '0',
	subject_user_id Bigint UNSIGNED NOT NULL DEFAULT 1,
 Primary Key (subject_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Create table tce_questions (
	question_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
	question_subject_id Bigint UNSIGNED NOT NULL,
	question_description Text NOT NULL,
	question_type Smallint(3) UNSIGNED NOT NULL DEFAULT 1,
	question_difficulty Smallint NOT NULL DEFAULT 1,
	question_enabled Bool NOT NULL DEFAULT '0',
 Primary Key (question_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Create table tce_answers (
	answer_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
	answer_question_id Bigint UNSIGNED NOT NULL,
	answer_description Text NOT NULL,
	answer_isright Bool NOT NULL DEFAULT '0',
	answer_enabled Bool NOT NULL DEFAULT '0',
 Primary Key (answer_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Create table tce_tests (
	test_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
	test_name Varchar(255) NOT NULL,
	test_description Text NOT NULL,
	test_begin_time Datetime,
	test_end_time Datetime,
	test_duration_time Smallint(10) UNSIGNED NOT NULL DEFAULT 0,
	test_ip_range Varchar(255) NOT NULL DEFAULT '*.*.*.*',
	test_random_questions Bool NOT NULL DEFAULT '1',
	test_results_to_users Bool NOT NULL DEFAULT '0',
	test_score_right Decimal(10,3) DEFAULT 0,
	test_max_score Decimal(10,3) NOT NULL DEFAULT 0,
	test_user_id Bigint UNSIGNED NOT NULL DEFAULT 1,
 Primary Key (test_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Create table tce_test_subjects (
	subjset_tsubset_id Bigint UNSIGNED NOT NULL,
	subjset_subject_id Bigint UNSIGNED NOT NULL,
 Primary Key (subjset_tsubset_id,subjset_subject_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Create table tce_tests_users (
	testuser_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
	testuser_test_id Bigint UNSIGNED NOT NULL,
	testuser_user_id Bigint UNSIGNED NOT NULL,
	testuser_status Smallint UNSIGNED NOT NULL DEFAULT 0,
	testuser_creation_time Datetime NOT NULL,
	testuser_comment Text,
 Primary Key (testuser_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Create table tce_tests_logs (
	testlog_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
	testlog_testuser_id Bigint UNSIGNED NOT NULL,
	testlog_user_ip Varchar(15),
	testlog_question_id Bigint UNSIGNED NOT NULL,
	testlog_answer_text Text,
	testlog_score Decimal(10,3),
	testlog_creation_time Datetime,
	testlog_display_time Datetime,
	testlog_change_time Datetime,
 Primary Key (testlog_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Create table tce_tests_logs_answers (
	logansw_testlog_id Bigint UNSIGNED NOT NULL,
	logansw_answer_id Bigint UNSIGNED NOT NULL,
	logansw_selected Bool NOT NULL DEFAULT '0',
	logansw_order Smallint NOT NULL DEFAULT 1,
 Primary Key (logansw_testlog_id,logansw_answer_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Create table tce_user_groups (
	group_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
	group_name Varchar(255) NOT NULL,
	UNIQUE (group_name),
 Primary Key (group_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Create table tce_usrgroups (
	usrgrp_user_id Bigint UNSIGNED NOT NULL,
	usrgrp_group_id Bigint UNSIGNED NOT NULL,
 Primary Key (usrgrp_user_id,usrgrp_group_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Create table tce_testgroups (
	tstgrp_test_id Bigint UNSIGNED NOT NULL,
	tstgrp_group_id Bigint UNSIGNED NOT NULL,
 Primary Key (tstgrp_test_id,tstgrp_group_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Create table tce_test_subject_set (
	tsubset_id Bigint UNSIGNED NOT NULL AUTO_INCREMENT,
	tsubset_test_id Bigint UNSIGNED NOT NULL,
	tsubset_type Smallint NOT NULL DEFAULT 1,
	tsubset_difficulty Smallint NOT NULL DEFAULT 1,
	tsubset_quantity Smallint NOT NULL DEFAULT 1,
	tsubset_answers Smallint NOT NULL DEFAULT 0,
 Primary Key (tsubset_id)
) ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

/* Alternate Keys */

Alter table tce_users add unique ak_user_name (user_name);
Alter table tce_users add unique ak_user_regnumber (user_regnumber);
Alter table tce_users add unique ak_user_ssn (user_ssn);
Alter table tce_subjects add unique ak_subject_name (subject_name);
Alter table tce_questions add unique ak_question (question_subject_id,question_description(255));
Alter table tce_answers add unique ak_answer (answer_question_id,answer_description(255));
Alter table tce_tests add unique ak_test_name (test_name);
Alter table tce_tests_users add unique ak_testuser (testuser_test_id,testuser_user_id);
Alter table tce_tests_logs add unique ak_testuser_question (testlog_testuser_id,testlog_question_id);

/*  Foreign Keys */

Alter table tce_tests_users add Foreign Key (testuser_user_id) references tce_users (user_id) on delete cascade on update no action;
Alter table tce_tests add Foreign Key (test_user_id) references tce_users (user_id) on delete cascade on update no action;
Alter table tce_subjects add Foreign Key (subject_user_id) references tce_users (user_id) on delete cascade on update no action;
Alter table tce_usrgroups add Foreign Key (usrgrp_user_id) references tce_users (user_id) on delete cascade on update no action;
Alter table tce_questions add Foreign Key (question_subject_id) references tce_subjects (subject_id) on delete cascade on update no action;
Alter table tce_test_subjects add Foreign Key (subjset_subject_id) references tce_subjects (subject_id) on delete  restrict on update no action;
Alter table tce_answers add Foreign Key (answer_question_id) references tce_questions (question_id) on delete cascade on update no action;
Alter table tce_tests_logs add Foreign Key (testlog_question_id) references tce_questions (question_id) on delete  restrict on update no action;
Alter table tce_tests_logs_answers add Foreign Key (logansw_answer_id) references tce_answers (answer_id) on delete  restrict on update no action;
Alter table tce_tests_users add Foreign Key (testuser_test_id) references tce_tests (test_id) on delete cascade on update  restrict;
Alter table tce_testgroups add Foreign Key (tstgrp_test_id) references tce_tests (test_id) on delete cascade on update no action;
Alter table tce_test_subject_set add Foreign Key (tsubset_test_id) references tce_tests (test_id) on delete cascade on update no action;
Alter table tce_tests_logs add Foreign Key (testlog_testuser_id) references tce_tests_users (testuser_id) on delete cascade on update no action;
Alter table tce_tests_logs_answers add Foreign Key (logansw_testlog_id) references tce_tests_logs (testlog_id) on delete cascade on update no action;
Alter table tce_usrgroups add Foreign Key (usrgrp_group_id) references tce_user_groups (group_id) on delete cascade on update no action;
Alter table tce_testgroups add Foreign Key (tstgrp_group_id) references tce_user_groups (group_id) on delete cascade on update no action;
Alter table tce_test_subjects add Foreign Key (subjset_tsubset_id) references tce_test_subject_set (tsubset_id) on delete cascade on update no action;
