<?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_init.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$columns = array
(
	'id' => 'IDENTITY',
	'template' => 'BOOL DEFAULT 0',
	'name' => "VARCHAR(255) DEFAULT ''",
	'reference' => "VARCHAR(255) DEFAULT ''",
	'company_id' => 'INT NOT_NULL DEFAULT 0',
	'photo_id' => 'INT',
	'status' => 'INT NOT_NULL DEFAULT 0',
	'RAG' => 'INT NOT_NULL DEFAULT 0',
	'summary' => 'VARCHAR(2000)',
	'description' => 'VARCHAR_MAX',
	'ic_forms' => "VARCHAR(255) DEFAULT ''",
	'extensions' => "VARCHAR(255) DEFAULT ''"
);
$db->CreateTable('projects_project', $columns, false);

$columns = array
(
	'id' => 'IDENTITY',
	'name' => "VARCHAR(255) DEFAULT ''",
);
$db->CreateTable('projects_company', $columns, false);

$columns = array
(
	'id' => 'IDENTITY',
	'company_id' => 'INT NOT_NULL DEFAULT 0',
	'name' => "VARCHAR(255) DEFAULT ''",
);
$db->CreateTable('projects_contact', $columns, false);
DB_UPDATE_FILE
);


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

$columns = array
(
	'user_id' => 'INT NOT_NULL DEFAULT 0',
	'project_id' => 'INT NOT_NULL DEFAULT 0',
);
$db->CreateTable('projects_starred', $columns, false);
DB_UPDATE_FILE
);


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

$db->DropColumn('projects_project', 'photo_id');
DB_UPDATE_FILE
);


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

$db->DropTable('projects_starred');
$columns = array
(
	'user_id' => 'INT NOT_NULL DEFAULT 0',
	'project_id' => 'INT NOT_NULL DEFAULT 0',
);
$db->CreateTable('projects_bookmark', $columns, false);

$columns = array
(
	'user_id' => 'INT NOT_NULL DEFAULT 0',
	'project_id' => 'INT NOT_NULL DEFAULT 0',
);
$db->CreateTable('projects_follow', $columns, false);
DB_UPDATE_FILE
);


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

$db->AddColumn('projects_project', 'created', 'INT_DATE');
$db->AddColumn('projects_project', 'created_by', 'INT');

$db->AddColumn('projects_project', 'last_modified', 'INT_DATE');
$db->AddColumn('projects_project', 'last_modified_by', 'INT');
DB_UPDATE_FILE
);


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

$columns = array
(
	'project_id' => 'INT NOT_NULL DEFAULT 0',
	'user_id' => 'INT NOT_NULL DEFAULT 0',
	'role' => 'INT NOT_NULL DEFAULT 0',
	'responsibility' => "VARCHAR(255) DEFAULT ''"
);
$db->CreateTable('projects_member', $columns, false);
DB_UPDATE_FILE
);


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


$db->AlterColumn('projects_project', 'created', 'date_created', 'INT_DATE');
$db->AlterColumn('projects_project', 'last_modified', 'date_modified', 'INT_DATE');
$db->AlterColumn('projects_project', 'RAG', 'rag', 'INT NOT_NULL DEFAULT 0');
$db->AlterColumn('projects_project', 'template', 'is_template', 'BOOL DEFAULT 0');

$db->AlterColumn('projects_project', 'name', 'name', 'VARCHAR(255) DEFAULT NULL');
$db->AlterColumn('projects_project', 'reference', 'reference', 'VARCHAR(255) DEFAULT NULL');
$db->AlterColumn('projects_project', 'ic_forms', 'ic_forms', 'VARCHAR(255) DEFAULT NULL');
$db->AlterColumn('projects_project', 'extensions', 'extensions', 'VARCHAR(255) DEFAULT NULL');

$db->AlterColumn('projects_company', 'name', 'name', 'VARCHAR(255) DEFAULT NULL');
$db->AlterColumn('projects_contact', 'name', 'name', 'VARCHAR(255) DEFAULT NULL');

$db->AlterColumn('projects_member', 'responsibility', 'responsibility', 'VARCHAR(255) DEFAULT NULL');


DB_UPDATE_FILE
);


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


$db->AddColumn('projects_project', 'summary_date_modified', 'INT_DATE');
$db->AddColumn('projects_project', 'summary_modified_by', 'INT NOT_NULL DEFAULT 0');
DB_UPDATE_FILE
);


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

$task_columns = array
(
	"id" => "IDENTITY",
	"title" => "VARCHAR(255) DEFAULT NULL",
	"description" => "VARCHAR_MAX DEFAULT NULL",
	"due_date" => "INT_DATE",
	"project_id" => "INT NOT_NULL DEFAULT 0",
	"section_id" => "INT NOT_NULL DEFAULT 0",
	"assignee" => "INT",
	"is_complete" => "BOOL DEFAULT 0",
	"duration_hours" => "INT NOT_NULL DEFAULT 0",
	"display_order" => "INT NOT_NULL DEFAULT 0",
	"created_by" => "INT NOT_NULL DEFAULT 0",
	"date_created" => "INT_DATE",
	"modified_by" => "INT NOT_NULL DEFAULT 0",
	"date_modified" => "INT_DATE"
);
$db->CreateTable('projects_task', $task_columns);

$section_columns = array
(
	"id" => "IDENTITY",
	"title" => "VARCHAR(255) DEFAULT NULL",
	"project_id" => "INT NOT_NULL DEFAULT 0",
	"is_private" => "BOOL DEFAULT 0",
	"display_order" => "INT NOT_NULL DEFAULT 0"
);
$db->CreateTable('projects_task_section', $section_columns);

DB_UPDATE_FILE
);


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


$db->AddColumn('projects_task', 'is_private',  'BOOL DEFAULT 0');
$db->DropColumn('projects_task_section', 'is_private');
DB_UPDATE_FILE
);


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


$db->AlterColumn('projects_task', 'duration_hours', 'duration', 'INT DEFAULT 1');
$db->AddColumn('projects_task', 'priority',  'INT DEFAULT 0');

DB_UPDATE_FILE
);


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

$db->AlterColumn('projects_follow', 'project_id', 'object_id', 'INT DEFAULT 0');
$db->AddColumn('projects_follow', 'aggregation',  'INT DEFAULT 0');

$db->query('UPDATE projects_follow SET aggregation = int:project_aggregation', \Claromentis\Projects\Repository\ProjectRepository::AGGREGATION_PROJECTS_PROJECT);

DB_UPDATE_FILE
);


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

$db->AddColumn('projects_project', 'duration_units',  'VARCHAR(50)');
DB_UPDATE_FILE
);


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

$task_columns = array
(
	'id' => 'IDENTITY',
	'aggregation' => 'INT NOT_NULL',
	'object_id' => 'INT NOT_NULL DEFAULT 0',
	'user_id' => 'INT NOT_NULL DEFAULT 0',
	'user_name' => 'VARCHAR(255) DEFAULT NULL',
	'area' => 'VARCHAR(30) DEFAULT NULL',
	'changes' => 'VARCHAR_MAX DEFAULT NULL',
	'date_modified' => 'INT_DATE'
);
$db->CreateTable('projects_history', $task_columns);

DB_UPDATE_FILE
);


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

$db->AddColumn('projects_task', 'date_completed',  'INT_DATE');

$today = new \DateDay();
$db->query('UPDATE projects_task SET date_completed = int:today WHERE is_complete = 1', $today->getDate(DATE_FORMAT_TIMESTAMP));
DB_UPDATE_FILE
);


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

// Create the new table
$task_columns = array
(
	'order_type' => 'INT DEFAULT NULL',
	'task_id' => 'INT DEFAULT NULL',
	'project_id' => 'INT DEFAULT NULL',
	'section_id' => 'INT DEFAULT NULL',
	'user_id' => 'INT DEFAULT NULL',
	'task_filter' => 'VARCHAR(20) DEFAULT NULL',
	'status_filter' => 'VARCHAR(20) DEFAULT NULL',
	'display_order' => 'INT NOT_NULL DEFAULT 0'
);
$db->CreateTable('projects_display_order', $task_columns);

// Copy the values from existing tasks
$result = $db->query('SELECT id, project_id, section_id, display_order FROM projects_task');
while ($arr = $result->fetchArray())
{
	$db->query('INSERT INTO projects_display_order (order_type, task_id, project_id, section_id, display_order) VALUES (2, int:task_id, int:project_id, int:section_id, int:display_order)', $arr['id'], $arr['project_id'], $arr['section_id'], $arr['display_order']);
}

// Copy the values from existing sections
$result = $db->query('SELECT id, project_id, display_order FROM projects_task_section');
while ($arr = $result->fetchArray())
{
	$db->query('INSERT INTO projects_display_order (order_type, project_id, section_id, display_order) VALUES (1, int:project_id, int:section_id, int:display_order)', $arr['project_id'], $arr['id'], $arr['display_order']);
}

// Remove the old columns
$db->DropColumn('projects_task', 'display_order');
$db->DropColumn('projects_task_section', 'display_order');
DB_UPDATE_FILE
);


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

// These values are now stored via the existing order_type column instead
$db->DropColumn('projects_display_order', 'task_filter');
$db->DropColumn('projects_display_order', 'status_filter');
DB_UPDATE_FILE
);


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

$db->AddColumn('projects_task_section', 'colour',  'VARCHAR(7) DEFAULT NULL');

$db->query("UPDATE projects_task_section SET colour = '#ff99cc'");
DB_UPDATE_FILE
);


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

$db->AddColumn('projects_project', 'comment_count', 'INT NOT_NULL DEFAULT 0');
$db->AddColumn('projects_project', 'file_count', 'INT NOT_NULL DEFAULT 0');
$db->AddColumn('projects_task', 'comment_count', 'INT NOT_NULL DEFAULT 0');
$db->AddColumn('projects_task', 'file_count', 'INT NOT_NULL DEFAULT 0');
DB_UPDATE_FILE
);


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

$db->AddColumn('projects_contact', 'email', 'VARCHAR(100) DEFAULT NULL');
$db->AddColumn('projects_contact', 'phone', 'VARCHAR(40) DEFAULT NULL');
$db->AddColumn('projects_contact', 'mobile', 'VARCHAR(40) DEFAULT NULL');
$db->AddColumn('projects_contact', 'notes', 'VARCHAR(2000) DEFAULT NULL');

DB_UPDATE_FILE
);


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

$db->AddColumn('projects_project', 'dms_folder_id', 'INT NOT_NULL DEFAULT 0');
$db->AddColumn('projects_task', 'dms_folder_id', 'INT NOT_NULL DEFAULT 0');


DB_UPDATE_FILE
);


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

$db->query('UPDATE projects_task SET priority = 0 WHERE priority > 3');
DB_UPDATE_FILE
);


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

$db->query('UPDATE projects_project SET rag = int:rag WHERE rag = 0', \Claromentis\Projects\ProjectLight::RAG_AMBER);
DB_UPDATE_FILE
);


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

$task_columns = array
(
	'notification_id' => 'INT NOT_NULL DEFAULT 0',
	'method' => 'INT NOT_NULL DEFAULT 1',
);
$db->CreateTable('projects_notification_method', $task_columns);

DB_UPDATE_FILE
);


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

$columns = array
(
	'id' => 'IDENTITY',
	'created' => 'INT_DATE',
	'user_id' => 'INT NOT_NULL DEFAULT 0',
	'subject' => 'VARCHAR(255) DEFAULT NULL',
	'body' => 'VARCHAR(2000) DEFAULT NULL',
);
$db->CreateTable('projects_notification_digest', $columns);

DB_UPDATE_FILE
);


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

$db->AlterColumn('projects_project', 'reference', 'reference', 'VARCHAR(15) DEFAULT NULL');

DB_UPDATE_FILE
);


$migrations->Run('32_comment_count.php', <<<'DB_UPDATE_FILE'
<?php

//define('AGGREGATION_PROJECTS_PROJECT',  121);

/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$result = $db->query('SELECT id FROM projects_project');
while ($arr = $result->fetchArray())
{
	list($count) = $db->query_row('SELECT count(1) FROM discussions WHERE aggregation = int:project_aggregation AND object_id = int:project_id AND parent_id = 0', 121 /*AGGREGATION_PROJECTS_PROJECT*/, $arr['id']);
	$db->query('UPDATE projects_project SET comment_count = int:comment_count WHERE id = int:project_id', $count, $arr['id']);
}
DB_UPDATE_FILE
);


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