<?php
// This file contains the database schema version 11.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");
//===========================================================================================

// projects_bookmark
$table_descr = array(
	'user_id'	=>	"INT NOT_NULL DEFAULT 0",
	'project_id'	=>	"INT NOT_NULL DEFAULT 0",
);

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



// projects_company
$table_descr = array(
	'id'	=>	"IDENTITY",
	'name'	=>	"VARCHAR(255) NULL DEFAULT NULL",
);

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



// projects_contact
$table_descr = array(
	'id'	=>	"IDENTITY",
	'company_id'	=>	"INT NOT_NULL DEFAULT 0",
	'name'	=>	"VARCHAR(255) NULL DEFAULT NULL",
	'email'	=>	"VARCHAR(100) NULL DEFAULT NULL",
	'phone'	=>	"VARCHAR(40) NULL DEFAULT NULL",
	'mobile'	=>	"VARCHAR(40) NULL DEFAULT NULL",
	'notes'	=>	"VARCHAR(2000) NULL DEFAULT NULL",
);

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



// projects_display_order
$table_descr = array(
	'order_type'	=>	"INT NULL DEFAULT NULL",
	'task_id'	=>	"INT NULL DEFAULT NULL",
	'project_id'	=>	"INT NULL DEFAULT NULL",
	'section_id'	=>	"INT NULL DEFAULT NULL",
	'user_id'	=>	"INT NULL DEFAULT NULL",
	'display_order'	=>	"INT NOT_NULL DEFAULT 0",
	'status_id'	=>	"INT NULL DEFAULT NULL",
);

$db->CreateTable('projects_display_order', $table_descr, true);
$db->CreateIndex('projects_display_order', 'task_id_user_id', 'task_id', 'user_id');



// projects_follow
$table_descr = array(
	'user_id'	=>	"INT NOT_NULL DEFAULT 0",
	'object_id'	=>	"INT NULL DEFAULT 0",
	'aggregation'	=>	"INT NULL DEFAULT 0",
);

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



// projects_history
$table_descr = 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) NULL DEFAULT NULL",
	'area'	=>	"VARCHAR(30) NULL DEFAULT NULL",
	'changes'	=>	"CLOB NULL DEFAULT NULL",
	'date_modified'	=>	"INT_DATE NULL",
	'is_private'	=>	"BOOL NULL DEFAULT 0",
);

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



// projects_history_assignees
$table_descr = array(
	'history_id'	=>	"INT NOT_NULL DEFAULT 0",
	'user_id'	=>	"INT NOT_NULL DEFAULT 0",
);

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



// projects_member
$table_descr = array(
	'project_id'	=>	"INT NOT_NULL DEFAULT 0",
	'user_id'	=>	"INT NOT_NULL DEFAULT 0",
	'role'	=>	"INT NOT_NULL DEFAULT 0",
	'responsibility'	=>	"VARCHAR(255) NULL DEFAULT NULL",
);

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



// projects_notification_digest
$table_descr = array(
	'id'	=>	"IDENTITY",
	'created'	=>	"INT_DATE NULL",
	'user_id'	=>	"INT NOT_NULL DEFAULT 0",
	'subject'	=>	"VARCHAR(255) NULL DEFAULT NULL",
	'body'	=>	"VARCHAR(2000) NULL DEFAULT NULL",
);

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



// projects_notification_method
$table_descr = array(
	'notification_id'	=>	"INT NOT_NULL DEFAULT 0",
	'method'	=>	"INT NOT_NULL DEFAULT 1",
);

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



// projects_project
$table_descr = array(
	'id'	=>	"IDENTITY",
	'is_template'	=>	"BOOL NULL DEFAULT 0",
	'name'	=>	"VARCHAR(255) NULL DEFAULT NULL",
	'reference'	=>	"VARCHAR(15) NULL DEFAULT NULL",
	'company_id'	=>	"INT NOT_NULL DEFAULT 0",
	'status'	=>	"INT NOT_NULL DEFAULT 0",
	'rag'	=>	"INT NOT_NULL DEFAULT 0",
	'summary'	=>	"VARCHAR(2000) NULL",
	'description'	=>	"CLOB NULL DEFAULT NULL",
	'ic_forms'	=>	"VARCHAR(255) NULL DEFAULT NULL",
	'extensions'	=>	"VARCHAR(255) NULL DEFAULT NULL",
	'date_created'	=>	"INT_DATE NULL",
	'created_by'	=>	"INT NULL",
	'date_modified'	=>	"INT_DATE NULL",
	'last_modified_by'	=>	"INT NULL",
	'summary_date_modified'	=>	"INT_DATE NULL",
	'summary_modified_by'	=>	"INT NOT_NULL DEFAULT 0",
	'duration_units'	=>	"VARCHAR(50) NULL",
	'comment_count'	=>	"INT NOT_NULL DEFAULT 0",
	'file_count'	=>	"INT NOT_NULL DEFAULT 0",
	'dms_folder_id'	=>	"INT NOT_NULL DEFAULT 0",
	'todo_status_label'	=>	"VARCHAR(256) NULL",
	'completed_status_label'	=>	"VARCHAR(256) NULL",
);

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



// projects_status
$table_descr = array(
	'id'	=>	"IDENTITY",
	'project_id'	=>	"INT NULL",
	'name'	=>	"VARCHAR(256) NULL",
	'sort_order'	=>	"INT NULL",
);

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



// projects_task
$table_descr = array(
	'id'	=>	"IDENTITY",
	'title'	=>	"VARCHAR(255) NULL DEFAULT NULL",
	'description'	=>	"VARCHAR_MAX NULL DEFAULT NULL",
	'due_date'	=>	"INT_DATE NULL",
	'project_id'	=>	"INT NOT_NULL DEFAULT 0",
	'section_id'	=>	"INT NOT_NULL DEFAULT 0",
	'assignee'	=>	"INT NULL",
	'duration'	=>	"FLOAT NULL DEFAULT 1",
	'created_by'	=>	"INT NOT_NULL DEFAULT 0",
	'date_created'	=>	"INT_DATE NULL",
	'modified_by'	=>	"INT NOT_NULL DEFAULT 0",
	'date_modified'	=>	"INT_DATE NULL",
	'is_private'	=>	"BOOL NULL DEFAULT 0",
	'priority'	=>	"INT NULL DEFAULT 0",
	'date_completed'	=>	"INT_DATE NULL",
	'comment_count'	=>	"INT NOT_NULL DEFAULT 0",
	'file_count'	=>	"INT NOT_NULL DEFAULT 0",
	'dms_folder_id'	=>	"INT NOT_NULL DEFAULT 0",
	'status_id'	=>	"INT NULL",
);

$db->CreateTable('projects_task', $table_descr, true);
$db->CreateIndex('projects_task', 'projects_task_assignee', 'assignee');
$db->CreateIndex('projects_task', 'projects_task_project_id', 'project_id');



// projects_task_assignee
$table_descr = array(
	'task_id'	=>	"INT NOT_NULL",
	'user_id'	=>	"INT NOT_NULL",
);

$db->CreateTable('projects_task_assignee', $table_descr, true);
$db->CreatePrimaryKey('projects_task_assignee', 'task_id', 'user_id');
$db->CreateIndex('projects_task_assignee', 'projects_task_assignee_users', 'user_id');



// projects_task_section
$table_descr = array(
	'id'	=>	"IDENTITY",
	'title'	=>	"VARCHAR(255) NULL DEFAULT NULL",
	'project_id'	=>	"INT NOT_NULL DEFAULT 0",
	'colour'	=>	"VARCHAR(7) NULL DEFAULT NULL",
);

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



// projects_topic
$table_descr = array(
	'id'	=>	"IDENTITY",
	'project_id'	=>	"INT NULL",
	'title'	=>	"VARCHAR(255) NULL",
	'description'	=>	"CLOB NULL",
	'owner_id'	=>	"INT NULL",
	'modified'	=>	"INT_DATE NULL",
	'pinned'	=>	"BOOL NOT_NULL DEFAULT 0",
	'created'	=>	"INT_DATE NULL",
);

$db->CreateTable('projects_topic', $table_descr, true);
$db->CreateIndex('projects_topic', 'topic_project_id', 'project_id');
$db->CreateIndex('projects_topic', 'topic_owner', 'owner_id');





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