<?php
$_db_migration_to = '01.01';
if (!isset($migrations) || !is_object($migrations))
	die("This file cannot be executed directly");
$migrations->CheckValid($_db_migration_to);
//===========================================================================================



$migrations->Run('01_quiz_setup.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$columns = 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',
);

// create the table using the following:
$db->CreateTable('quiz', $columns, false);

// create any indexes you feel are necessary as follows:
$db->CreateIndex('quiz', 'idx_quiz_users', 'created_by');
DB_UPDATE_FILE
);


$migrations->Run('02_question_setup.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$columns = 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'
);

// create the table using the following:
$db->CreateTable('quiz_question', $columns, false);

// create any indexes you feel are necessary as follows:
$db->CreateIndex('quiz_question', 'idx_question_users', 'created_by');
DB_UPDATE_FILE
);


$migrations->Run('03_quiz_question_junction_setup.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$columns = array (
	'id' 			=> 'IDENTITY',
	'quiz_id'		=> 'INT NOT_NULL DEFAULT 0',
	'question_id'	=> 'INT NOT_NULL DEFAULT 0',
	'sort_order'	=> 'INT NOT_NULL DEFAULT 0',
	'date_created'  => 'INT_DATE NOT_NULL DEFAULT 0'
);

// create the table using the following:
$db->CreateTable('quiz_question_junction', $columns, false);

// create any indexes you feel are necessary as follows:
$db->CreateIndex('quiz_question_junction', 'idx_quiz_fk', 'quiz_id');
$db->CreateIndex('quiz_question_junction', 'idx_question_fk', 'question_id');
DB_UPDATE_FILE
);


$migrations->Run('04_options_setup.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$columns = array (
	'id' 			=> 'IDENTITY',
	'question_id'	=> "INT NOT_NULL DEFAULT 0",
	'type'			=> 'INT NOT_NULL DEFAULT 0',
	'option_text'	=> 'VARCHAR(255) NULL',
	'points'		=> 'INT NOT_NULL DEFAULT 0',
	'correct'		=> 'BOOL NOT_NULL DEFAULT 0',
	'sort_order'	=> 'INT NOT_NULL DEFAULT 0',
	'created_by'	=> 'INT NOT_NULL DEFAULT 0',
	'created_date'  => 'INT_DATE NOT_NULL DEFAULT 0'
);

// create the table using the following:
$db->CreateTable('quiz_option', $columns, false);

// create any indexes you feel are necessary as follows:
$db->CreateIndex('quiz_option', 'idx_option_question', 'question_id');
DB_UPDATE_FILE
);


$migrations->Run('05_audit_setup.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$columns = array (
	'id' 				=> 'IDENTITY',
	'user_id'			=> "INT NOT_NULL DEFAULT 0",
	'quiz_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'
);

// create the table using the following:
$db->CreateTable('quiz_audit', $columns, false);

// create any indexes you feel are necessary as follows:
$db->CreateIndex('quiz_audit', 'idx_option_user', 'user_id');
DB_UPDATE_FILE
);


$migrations->Run('06_audit_property_setup.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$columns = array (
	'id' 			=> 'IDENTITY',
	'audit_id'		=> "INT NOT_NULL DEFAULT 0",
	'audit_key'		=> 'VARCHAR(100) NULL',
	'audit_value'	=> 'VARCHAR_MAX NULL'
);

// create the table using the following:
$db->CreateTable('quiz_audit_property', $columns, false);

// create any indexes you feel are necessary as follows:
$db->CreateIndex('quiz_audit_property', 'idx_property_audit', 'audit_id');
DB_UPDATE_FILE
);


$migrations->Run('07_enable_admin_panel.php', <<<'DB_UPDATE_FILE'
<?php
/**
 * @var $migrations \Claromentis\Setup\SetupFacade
 */

// Enable the module's admin panel for the admin user
$migrations->GetAdminPanelCreator()->Enable('quiz');

DB_UPDATE_FILE
);


$migrations->Run('08_question_description.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();


$db->AddColumn('quiz_question', 'description', 'VARCHAR_MAX NULL');
DB_UPDATE_FILE
);


$migrations->Run('08_rename_junction_created_date_column.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$db->AlterColumn('quiz_question_junction', 'date_created', 'created_date');

DB_UPDATE_FILE
);


$migrations->Run('09_rename_audit_to_attempt.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$db->RenameTable('quiz_audit', 'quiz_attempt');
$db->RenameTable('quiz_audit_property', 'quiz_attempt_property');

$db->DropIndex('quiz_attempt_property', 'idx_property_audit');

$db->AlterColumn('quiz_attempt_property', 'audit_id', 'attempt_id');
$db->AlterColumn('quiz_attempt_property', 'audit_key', 'attempt_key');
$db->AlterColumn('quiz_attempt_property', 'audit_value', 'attempt_value');

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

DB_UPDATE_FILE
);


$migrations->Run('10_threshold_dbvar.php', <<<'DB_UPDATE_FILE'
<?php
// set a threshold required for a question to be considered correct
DBVar::Set('quiz_question_threshold', 10);
DB_UPDATE_FILE
);


$migrations->Run('11_attempt_passed.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$db->AddColumn('quiz_attempt', 'has_passed', 'BOOL NOT_NULL DEFAULT 0');
DB_UPDATE_FILE
);


$migrations->Run('12_create_answer_tables.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

// Answer table
$db->CreateTable('quiz_answer', 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'
));

// Answer property table
$db->CreateTable('quiz_answer_property', array(
	'id'           => 'IDENTITY',
	'answer_id'    => 'INT NOT_NULL DEFAULT 0',
	'answer_key'   => 'VARCHAR(100) NULL',
	'answer_value' => 'VARCHAR_MAX NULL'
));

DB_UPDATE_FILE
);


$migrations->Run('12_dev_update.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$threshold = (int) DBVar::Get('quiz_question_threshold');

$sql = "UPDATE quiz_question SET threshold = $threshold";
$db->query($sql);

$sql = "UPDATE quiz_option SET points = 0";
$db->query($sql);

$sql = "UPDATE quiz_option SET points = $threshold WHERE correct = 1";
$db->query($sql);
DB_UPDATE_FILE
);


$migrations->Run('13_rename_answer_property_columns.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$db->AlterColumn('quiz_answer_property', 'answer_key', 'property_key');
$db->AlterColumn('quiz_answer_property', 'answer_value', 'property_value');

DB_UPDATE_FILE
);


$migrations->Run('14_create_answer_id_index.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

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

DB_UPDATE_FILE
);


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