<?php
// This file contains the database schema version 13.02
if (!isset($migrations) || !is_object($migrations))
	die("This file cannot be executed directly");
if ($migrations->GetVersion() > 0)
	throw new Exception("The database is already initialized");
//===========================================================================================

// quiz
$table_descr = array(
	'id'	=>	"IDENTITY",
	'title'	=>	"VARCHAR(255) NULL",
	'description'	=>	"VARCHAR_MAX NULL",
	'created_by'	=>	"INT NOT_NULL DEFAULT 0",
	'created_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
	'pass_percent'	=>	"INT NOT_NULL DEFAULT 0",
	'show_answers'	=>	"BOOL NOT_NULL DEFAULT 0",
	'status'	=>	"INT NOT_NULL DEFAULT 0",
	'qualification_code_id'	=>	"INT NOT_NULL DEFAULT 0",
	'max_retakes'	=>	"INT NULL",
	'show_results'	=>	"BOOL NOT_NULL DEFAULT 0",
	'training_record'	=>	"BOOL NOT_NULL DEFAULT 0",
	'shuffle_options'	=>	"BOOL NOT_NULL DEFAULT 0",
	'show_help_text'	=>	"BOOL NOT_NULL DEFAULT 0",
	'is_new'	=>	"BOOL NULL DEFAULT 0",
	'notification_sent'	=>	"BOOL NULL DEFAULT 1",
	'notify_users'	=>	"BOOL NOT_NULL DEFAULT 0",
);

$db->CreateTable('quiz', $table_descr, true);
$db->CreateIndex('quiz', 'idx_quiz_users', 'created_by');



// quiz_anonymous
$table_descr = array(
	'user_id'	=>	"VARCHAR(255) NULL",
	'object_type'	=>	"INT NOT_NULL DEFAULT 0",
	'object_id'	=>	"INT NOT_NULL DEFAULT 0",
);

$db->CreateTable('quiz_anonymous', $table_descr, true);
$db->CreateIndex('quiz_anonymous', 'idx_object_type_id', 'object_type', 'object_id');



// quiz_answer
$table_descr = array(
	'id'	=>	"IDENTITY",
	'attempt_id'	=>	"INT NOT_NULL DEFAULT 0",
	'question_id'	=>	"INT NOT_NULL DEFAULT 0",
	'created_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
);

$db->CreateTable('quiz_answer', $table_descr, true);
$db->CreateIndex('quiz_answer', 'idx_attempt_id', 'attempt_id');
$db->CreateIndex('quiz_answer', 'idx_question_id', 'question_id');



// quiz_answer_choice
$table_descr = array(
	'id'	=>	"IDENTITY",
	'user_id'	=>	"INT NOT_NULL DEFAULT 0",
	'attempt_id'	=>	"INT NOT_NULL DEFAULT 0",
	'question_id'	=>	"INT NOT_NULL DEFAULT 0",
	'option_id'	=>	"INT NOT_NULL DEFAULT 0",
	'created_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
);

$db->CreateTable('quiz_answer_choice', $table_descr, true);
$db->CreateIndex('quiz_answer_choice', 'idx_question_id', 'question_id');
$db->CreateIndex('quiz_answer_choice', 'idx_option_id', 'option_id');



// quiz_answer_property
$table_descr = array(
	'id'	=>	"IDENTITY",
	'answer_id'	=>	"INT NOT_NULL DEFAULT 0",
	'property_key'	=>	"VARCHAR(100) NULL",
	'property_value'	=>	"VARCHAR_MAX NULL",
);

$db->CreateTable('quiz_answer_property', $table_descr, true);
$db->CreateIndex('quiz_answer_property', 'idx_answer_id', 'answer_id');



// quiz_attempt
$table_descr = array(
	'id'	=>	"IDENTITY",
	'user_id'	=>	"INT NOT_NULL DEFAULT 0",
	'object_id'	=>	"INT NOT_NULL DEFAULT 0",
	'attempt'	=>	"INT NOT_NULL DEFAULT 0",
	'created_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
	'completed_date'	=>	"INT_DATE NULL",
	'last_access_date'	=>	"INT_DATE NULL",
	'has_passed'	=>	"BOOL NOT_NULL DEFAULT 0",
	'is_best'	=>	"BOOL NOT_NULL DEFAULT 0",
	'object_type'	=>	"INT NOT_NULL DEFAULT 140",
);

$db->CreateTable('quiz_attempt', $table_descr, true);
$db->CreateIndex('quiz_attempt', 'idx_option_user', 'user_id');
$db->CreateIndex('quiz_attempt', 'idx_object_type_id', 'object_type', 'object_id');
$db->CreateIndex('quiz_attempt', 'idx_attempt', 'attempt');



// quiz_attempt_property
$table_descr = array(
	'id'	=>	"IDENTITY",
	'attempt_id'	=>	"INT NOT_NULL DEFAULT 0",
	'attempt_key'	=>	"VARCHAR(100) NULL",
	'attempt_value'	=>	"VARCHAR_MAX NULL",
);

$db->CreateTable('quiz_attempt_property', $table_descr, true);
$db->CreateIndex('quiz_attempt_property', 'idx_attempt_id', 'attempt_id');



// quiz_option
$table_descr = array(
	'id'	=>	"IDENTITY",
	'question_id'	=>	"INT NOT_NULL DEFAULT 0",
	'type'	=>	"INT NOT_NULL DEFAULT 0",
	'option_text'	=>	"VARCHAR_MAX NULL",
	'points'	=>	"INT NULL DEFAULT NULL",
	'correct'	=>	"INT NULL DEFAULT NULL",
	'sort_order'	=>	"INT NULL DEFAULT NULL",
	'created_by'	=>	"INT NOT_NULL DEFAULT 0",
	'created_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
);

$db->CreateTable('quiz_option', $table_descr, true);
$db->CreateIndex('quiz_option', 'idx_option_question', 'question_id');



// quiz_poll
$table_descr = array(
	'id'	=>	"IDENTITY",
	'created_by'	=>	"INT NOT_NULL DEFAULT 0",
	'created_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
	'start_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
	'end_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
	'anonymous'	=>	"BOOL NOT_NULL DEFAULT 0",
	'status'	=>	"INT NOT_NULL DEFAULT 0",
	'copy_email'	=>	"BOOL NOT_NULL DEFAULT 0",
	'show_results'	=>	"BOOL NOT_NULL DEFAULT 0",
	'completion_msg'	=>	"VARCHAR_MAX NULL",
	'multiple'	=>	"BOOL NOT_NULL DEFAULT 0",
	'description'	=>	"VARCHAR_MAX NULL",
	'has_notification'	=>	"INT NOT_NULL DEFAULT 0",
	'notification_sent'	=>	"BOOL NULL DEFAULT 0",
	'end_notification_sent'	=>	"BOOL NULL DEFAULT 0",
	'is_new'	=>	"BOOL NULL DEFAULT 0",
	'notify_on_submit'	=>	"INT NOT_NULL DEFAULT 0",
);

$db->CreateTable('quiz_poll', $table_descr, true);
$db->CreateIndex('quiz_poll', 'idx_poll_users', 'created_by');
$db->CreateIndex('quiz_poll', 'idx_status', 'status');
$db->CreateIndex('quiz_poll', 'idx_created_date', 'created_date');
$db->CreateIndex('quiz_poll', 'idx_start_date', 'start_date');
$db->CreateIndex('quiz_poll', 'idx_end_date', 'end_date', 'show_results', 'has_notification', 'end_notification_sent');



// quiz_poll_question_junction
$table_descr = array(
	'id'	=>	"IDENTITY",
	'poll_id'	=>	"INT NOT_NULL DEFAULT 0",
	'question_id'	=>	"INT NOT_NULL DEFAULT 0",
	'sort_order'	=>	"INT NOT_NULL DEFAULT 0",
	'created_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
);

$db->CreateTable('quiz_poll_question_junction', $table_descr, true);
$db->CreateIndex('quiz_poll_question_junction', 'idx_poll_fk', 'poll_id');
$db->CreateIndex('quiz_poll_question_junction', 'idx_question_fk', 'question_id');



// quiz_question
$table_descr = array(
	'id'	=>	"IDENTITY",
	'text'	=>	"VARCHAR(1000) NULL",
	'type'	=>	"INT NOT_NULL DEFAULT 0",
	'threshold'	=>	"INT NULL",
	'created_by'	=>	"INT NOT_NULL DEFAULT 0",
	'created_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
	'description'	=>	"VARCHAR_MAX NULL",
	'is_mandatory'	=>	"BOOL NULL DEFAULT 0",
);

$db->CreateTable('quiz_question', $table_descr, true);
$db->CreateIndex('quiz_question', 'idx_question_users', 'created_by');



// quiz_question_junction
$table_descr = array(
	'id'	=>	"IDENTITY",
	'quiz_id'	=>	"INT NOT_NULL DEFAULT 0",
	'question_id'	=>	"INT NOT_NULL DEFAULT 0",
	'sort_order'	=>	"INT NOT_NULL DEFAULT 0",
	'created_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
);

$db->CreateTable('quiz_question_junction', $table_descr, true);
$db->CreateIndex('quiz_question_junction', 'idx_quiz_fk', 'quiz_id');
$db->CreateIndex('quiz_question_junction', 'idx_question_fk', 'question_id');



// quiz_section
$table_descr = array(
	'id'	=>	"IDENTITY",
	'survey_id'	=>	"INT NOT_NULL DEFAULT 0",
	'title'	=>	"VARCHAR(255) NULL",
	'description'	=>	"VARCHAR_MAX NULL",
	'sort_order'	=>	"INT NOT_NULL DEFAULT 0",
);

$db->CreateTable('quiz_section', $table_descr, true);
$db->CreateIndex('quiz_section', 'idx_survey_id', 'survey_id');



// quiz_section_question
$table_descr = array(
	'id'	=>	"IDENTITY",
	'section_id'	=>	"INT NOT_NULL DEFAULT 0",
	'question_id'	=>	"INT NOT_NULL DEFAULT 0",
	'sort_order'	=>	"INT NOT_NULL DEFAULT 0",
	'created_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
);

$db->CreateTable('quiz_section_question', $table_descr, true);



// quiz_survey
$table_descr = array(
	'id'	=>	"IDENTITY",
	'title'	=>	"VARCHAR(255) NULL",
	'description'	=>	"VARCHAR_MAX NULL",
	'created_by'	=>	"INT NOT_NULL DEFAULT 0",
	'created_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
	'start_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
	'end_date'	=>	"INT_DATE NOT_NULL DEFAULT 0",
	'anonymous'	=>	"BOOL NOT_NULL DEFAULT 0",
	'multiple'	=>	"BOOL NOT_NULL DEFAULT 0",
	'status'	=>	"INT NOT_NULL DEFAULT 0",
	'copy_email'	=>	"BOOL NOT_NULL DEFAULT 0",
	'show_results'	=>	"BOOL NOT_NULL DEFAULT 0",
	'completion_msg'	=>	"VARCHAR_MAX NULL",
	'has_notification'	=>	"INT NOT_NULL DEFAULT 0",
	'notification_sent'	=>	"BOOL NULL DEFAULT 0",
	'end_notification_sent'	=>	"BOOL NULL DEFAULT 0",
	'is_new'	=>	"BOOL NULL DEFAULT 0",
	'notify_on_submit'	=>	"INT NOT_NULL DEFAULT 0",
);

$db->CreateTable('quiz_survey', $table_descr, true);
$db->CreateIndex('quiz_survey', 'idx_survey_users', 'created_by');
$db->CreateIndex('quiz_survey', 'idx_status', 'status');
$db->CreateIndex('quiz_survey', 'idx_created_date', 'created_date');
$db->CreateIndex('quiz_survey', 'idx_start_date', 'start_date', 'notification_sent');
$db->CreateIndex('quiz_survey', 'idx_end_date', 'end_date', 'show_results', 'has_notification', 'end_notification_sent');





//===========================================================================================
$migrations->SetVersion('13.02');
