# $Id: upgrade_022_to_100.sql,v 1.20 2003/09/03 00:31:09 ajdonnison Exp $
# dotproject_022_to_023.sql
#     Database Schema Update Script
#
# CHANGE LOG
#     creation by Andrew Eddie (25 Oct 2002) pre-alpha
#     updated by J. Christopher Pereira (29 Nov 2002)
#
# Use this schema for updating version 022 to 023
#
# WARNING:
# This file may be in a state of development flux at the moment.
# Watch out for changes (see above)
#

#
# ATTENTION:
# The following tables have been dropped from the schema
# Uncomment the lines to drop them if desired
#

#DROP TABLE `localization`;
#DROP TABLE `eventlog`;
#DROP TABLE `attendees`;
#DROP TABLE `attendees`;

#
# Structure for new table 'departments'
#

CREATE TABLE departments (
  dept_id int(10) unsigned NOT NULL auto_increment,
  dept_parent int(10) unsigned NOT NULL default '0',
  dept_company int(10) unsigned NOT NULL default '0',
  dept_name tinytext NOT NULL,
  dept_phone varchar(30) default NULL,
  dept_fax varchar(30) default NULL,
  dept_address1 varchar(30) default NULL,
  dept_address2 varchar(30) default NULL,
  dept_city varchar(30) default NULL,
  dept_state varchar(30) default NULL,
  dept_zip varchar(11) default NULL,
  dept_url varchar(25) default NULL,
  dept_desc mediumtext,
  dept_owner int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (dept_id),
  UNIQUE KEY dept_id (dept_id),
  KEY dept_id_2 (dept_id)
) TYPE=MyISAM COMMENT='Department heirarchy under a company';

#
# Table structure for table 'forum_watch'
#

CREATE TABLE forum_watch (
  watch_user int(10) unsigned NOT NULL default '0',
  watch_forum int(10) unsigned default NULL,
  watch_topic int(10) unsigned default NULL
) TYPE=MyISAM COMMENT='Links users to the forums/messages they are watching';

#
# Addition to the forums table to store the id of the last post
# This will mean the forum_last_date is deprecated
#
ALTER TABLE `forums` ADD `forum_last_id` INT UNSIGNED DEFAULT "0" NOT NULL AFTER `forum_last_date`;

#
# Addition to the PROJECTS table to associate a project to a company department
#
ALTER TABLE `projects` ADD `project_department` INT UNSIGNED DEFAULT "0" NOT NULL AFTER `project_company`;

#
# Minor change to the TASKS table to allow for part hours
#
ALTER TABLE `tasks` CHANGE `task_hours_worked` `task_hours_worked` FLOAT DEFAULT "0";

#
# Change to the USERS table for the new departments module
# and allow user defined user types
#
ALTER TABLE `users` ADD `user_department` INT UNSIGNED DEFAULT "0" NOT NULL AFTER `user_company`;
ALTER TABLE `users` CHANGE `user_type` `user_type` TINYINT UNSIGNED DEFAULT "0" NOT NULL;

#
# Events table
#
# The event_project field deprecates the event_parent field
# event_parent is maintained for the moment to prevent errors
#
ALTER TABLE `events` ADD `event_owner` INT UNSIGNED DEFAULT "0";
ALTER TABLE `events` ADD `event_project` INT UNSIGNED DEFAULT "0";
ALTER TABLE `events` ADD `event_private` TINYINT UNSIGNED DEFAULT "0";

#
# Task dependencies table
#
CREATE TABLE task_dependencies (
	dependencies_task_id int(11) NOT NULL,
	dependencies_req_task_id int(11) NOT NULL,
	PRIMARY KEY (dependencies_task_id, dependencies_req_task_id)
);

#
# Change to TASKS table for the new dynamic task flag
#
ALTER TABLE tasks ADD task_dynamic tinyint(1) NOT NULL default 0;

#
# Change to tickets to support longer cc lists
#
ALTER TABLE `tickets` CHANGE `cc` `cc` VARCHAR(255) NOT NULL DEFAULT '';

#
# Prepare support for user localisation
#


#
# Table changes 12 Dec 2002 (aje)
#
DROP TABLE IF EXISTS user_preferences;
CREATE TABLE `user_preferences` (
  `pref_user` varchar(12) NOT NULL default '',
  `pref_name` varchar(12) NOT NULL default '',
  `pref_value` varchar(32) NOT NULL default '',
  KEY `pref_user` (`pref_user`,`pref_name`)
) TYPE=MyISAM;

#
# Dumping data for table 'user_preferences'
#
INSERT INTO user_preferences VALUES("0", "LOCALE", "en");
INSERT INTO user_preferences VALUES("0", "TABVIEW", "0");
INSERT INTO user_preferences VALUES("0", "SHDATEFORMAT", "%d/%m/%Y");
INSERT INTO user_preferences VALUES("0", "UISTYLE", "default");

#
# Table changes 16 Dec 2002
# Allowing forum_moderated field to hold the user id of the moderator
#
ALTER TABLE `forums` CHANGE `forum_moderated` `forum_moderated` INT DEFAULT "0" NOT NULL;

# AJE (2/Jan/2003): New preference
#INSERT INTO user_preferences VALUES("0", "UISTYLE", "default");

#
# AJE (4/Jan/2003)
#

#
# Contacts table
#
ALTER TABLE `contacts` ADD `contact_owner` INT UNSIGNED DEFAULT "0";
ALTER TABLE `contacts` ADD `contact_private` TINYINT UNSIGNED DEFAULT "0";

#
# Projects table
#
ALTER TABLE `projects` ADD `project_private` TINYINT UNSIGNED DEFAULT "0";

#
# Users table
#
ALTER TABLE `users` CHANGE `signature` `user_signature` TEXT;

#
# AJE (6/Jan/2003)
#
INSERT INTO user_preferences VALUES("0", "TIMEFORMAT", "%I:%M %p");

#
# AJE (24/Jan/2003)
# ---------
# N O T E !
#
# MODULES TABLE IS STILL IN DEVELOPMENT STAGE
#

#
# Table structure for table 'modules'
#
DROP TABLE IF EXISTS modules;
CREATE TABLE `modules` (
  `mod_id` int(11) NOT NULL auto_increment,
  `mod_name` varchar(64) NOT NULL default '',
  `mod_directory` varchar(64) NOT NULL default '',
  `mod_version` varchar(10) NOT NULL default '',
  `mod_setup_class` varchar(64) NOT NULL default '',
  `mod_type` varchar(64) NOT NULL default '',
  `mod_active` int(1) unsigned NOT NULL default '0',
  `mod_ui_name` varchar(20) NOT NULL default '',
  `mod_ui_icon` varchar(64) NOT NULL default '',
  `mod_ui_order` tinyint(3) NOT NULL default '0',
  `mod_ui_active` int(1) unsigned NOT NULL default '0',
  `mod_description` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`mod_id`,`mod_directory`)
) TYPE=MyISAM;

#
# Dumping data for table 'modules'
#
INSERT INTO modules VALUES("1", "Companies", "companies", "1.0.0", "", "core", "1", "Companies", "money.gif", "1", "1", "");
INSERT INTO modules VALUES("2", "Projects", "projects", "1.0.0", "", "core", "1", "Projects", "projects.gif", "2", "1", "");
INSERT INTO modules VALUES("3", "Tasks", "tasks", "1.0.0", "", "core", "1", "Tasks", "tasks.gif", "3", "1", "");
INSERT INTO modules VALUES("4", "Calendar", "calendar", "1.0.0", "", "core", "1", "Calendar", "calendar.gif", "4", "1", "");
INSERT INTO modules VALUES("5", "Files", "files", "1.0.0", "", "core", "1", "Files", "folder.gif", "5", "1", "");
INSERT INTO modules VALUES("6", "Contacts", "contacts", "1.0.0", "", "core", "1", "Contacts", "contacts.gif", "6", "1", "");
INSERT INTO modules VALUES("7", "Forums", "forums", "1.0.0", "", "core", "1", "Forums", "communicate.gif", "7", "1", "");
INSERT INTO modules VALUES("8", "Tickets", "ticketsmith", "1.0.0", "", "core", "1", "Tickets", "ticketsmith.gif", "8", "1", "");
INSERT INTO modules VALUES("9", "User Administration", "admin", "1.0.0", "", "core", "1", "User Admin", "admin.gif", "9", "1", "");
INSERT INTO modules VALUES("10", "System Administration", "system", "1.0.0", "", "core", "1", "System Admin", "system.gif", "10", "1", "");
INSERT INTO modules VALUES("11", "Departments", "departments", "1.0.0", "", "core", "1", "Departments", "users.gif", "11", "0", "");
INSERT INTO modules VALUES("12", "Help", "help", "1.0.0", "", "core", "1", "Help", "dp.gif", "12", "0", "");
INSERT INTO modules VALUES("13", "Public", "public", "1.0.0", "", "core", "1", "Public", "users.gif", "13", "0", "");

#
#  Alter tasks table 1/February/2003
#
ALTER TABLE `tasks` ADD `task_duration_type` VARCHAR(6)  DEFAULT 'hours' NOT NULL AFTER task_duration;

#
# ! WARNING !
# BACKUP DATA BEFORE APPLYING THE NEXT UPDATE INSTRUCTIONS
# UNCOMMENT AND APPLY WHEN SAFE

# UPDATE tasks SET task_duration_type = 'days' WHERE task_duration >= 24.0;
# UPDATE tasks SET task_duration = task_duration/24.0 WHERE task_duration >= 24.0;

# AJE (17/Feb/2003)

#
# Table structure for table 'syskeys'
#

DROP TABLE IF EXISTS syskeys;
CREATE TABLE `syskeys` (
  `syskey_id` int(10) unsigned NOT NULL auto_increment,
  `syskey_name` varchar(48) NOT NULL default '',
  `syskey_label` varchar(255) NOT NULL default '',
  `syskey_type` int(1) unsigned NOT NULL default '0',
  `syskey_sep1` char(2) default '\n',
  `syskey_sep2` char(2) NOT NULL default '|',
  PRIMARY KEY  (`syskey_id`),
  UNIQUE KEY `idx_syskey_name` (`syskey_id`)
) TYPE=MyISAM;

#
# Table structure for table 'sysvals'
#

DROP TABLE IF EXISTS sysvals;
CREATE TABLE sysvals (
  sysval_id int(10) unsigned NOT NULL auto_increment,
  sysval_key_id int(10) unsigned NOT NULL default '0',
  sysval_title varchar(48) NOT NULL default '',
  sysval_value text NOT NULL,
  PRIMARY KEY  (sysval_id)
) TYPE=MyISAM;

#
# Table structure for table 'sysvals'
#

INSERT INTO syskeys VALUES("1", "SelectList", "Enter values for list", "0", "\n", "|");
INSERT INTO sysvals VALUES("1", "1", "ProjectStatus", "0|Not Defined\r\n1|Proposed\r\n2|In Planning\r\n3|In Progress\r\n4|On Hold\r\n5|Complete");

#
# Add "is provider" flag
# and "email" in companies table
# (22/Feb/2003)
#

ALTER TABLE companies ADD COLUMN company_type INT(3) NOT NULL DEFAULT 0;
ALTER TABLE companies ADD COLUMN company_email varchar(30);

INSERT INTO sysvals (sysval_key_id,sysval_title,sysval_value) VALUES("1", "CompanyType", "0|Not Applicable\n1|Client\n2|Vendor\n3|Supplier\n4|Consultant\n5|Government\n6|Internal");
# ROLES TABLES: AJE 26/Feb/2003

#
# Table structure for table 'roles'
#

DROP TABLE IF EXISTS roles;
CREATE TABLE roles (
  role_id int(10) unsigned NOT NULL auto_increment,
  role_name varchar(24) NOT NULL default '',
  role_description varchar(255) NOT NULL default '',
  role_type int(3) unsigned NOT NULL default '0',
  role_module int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (role_id)
) TYPE=MyISAM;

#
# Table structure for table 'user_roles'
#

DROP TABLE IF EXISTS user_roles;
CREATE TABLE user_roles (
  user_id int(10) unsigned NOT NULL default '0',
  role_id int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;

# 28/Feb/2003 eddieajau
# Give company address a bit more room
ALTER TABLE `companies` CHANGE `company_address1` `company_address1` VARCHAR(50) DEFAULT "";
ALTER TABLE `companies` CHANGE `company_address2` `company_address2` VARCHAR(50) DEFAULT "";

# 19/Mar/2003 eddieajau
# Alterations to the task log table
# This adds better information capture/support for other pluggins to use information

ALTER TABLE `task_log` ADD `task_log_hours` FLOAT DEFAULT "0" NOT NULL;
ALTER TABLE `task_log` ADD `task_log_date` DATETIME;
ALTER TABLE `task_log` ADD `task_log_costcode` VARCHAR(8) NOT NULL default '';
ALTER TABLE `task_log` DROP `task_log_parent`;

# copy across task comments to task log table
INSERT INTO task_log (task_log_task, task_log_name, task_log_description, task_log_creator, task_log_date)
SELECT comment_task, comment_title, comment_body, comment_user, comment_date FROM task_comments;

# uncomment when satisfied data has been copied successfully
#DROP TABLE task_comments;

# fix mis-spelt field
ALTER TABLE `tasks` CHANGE `task_precent_complete` `task_percent_complete` TINYINT(4)  DEFAULT "0";
ALTER TABLE `projects` CHANGE `project_precent_complete` `project_percent_complete` TINYINT(4)  DEFAULT "0";

# increase the description fields
ALTER TABLE `companies` CHANGE `company_description` `company_description` TEXT;
ALTER TABLE `departments` CHANGE `dept_desc` `dept_desc` TEXT;
ALTER TABLE `files` CHANGE `file_description` `file_description` TEXT;

# alpha 2 to beta 1 upgrades

# fix to convert password field to md5 based string
ALTER TABLE `users` CHANGE `user_password` `user_password` VARCHAR(32) NOT NULL DEFAULT '';

# fixes to provide more generic duration type handling
UPDATE `tasks` SET task_duration_type = 1 WHERE task_duration_type = 'hours';
UPDATE `tasks` SET task_duration_type = 24 WHERE task_duration_type = 'days';

ALTER TABLE `tasks` CHANGE `task_duration_type` `task_duration_type` int(11) NOT NULL DEFAULT 1;

INSERT INTO sysvals (sysval_key_id,sysval_title,sysval_value) VALUES("1", "TaskDurationType", "1|hours\n24|days");

# these can wait until release
#ALTER TABLE `companies` ADD `company_module` INT UNSIGNED DEFAULT "0" NOT NULL AFTER company_id;
#ALTER TABLE `projects` ADD `project_module` INT UNSIGNED DEFAULT "0" NOT NULL AFTER project_id;
#ALTER TABLE `events` ADD `event_module` INT UNSIGNED DEFAULT "0" NOT NULL AFTER event_id;

#
# Changes to the Events table
# Convert unix timestamp fields to mysql datetime formats
#
ALTER TABLE `events` CHANGE `event_start_date` `event_start_date` VARCHAR(20);
ALTER TABLE `events` CHANGE `event_end_date` `event_end_date` VARCHAR(20);

UPDATE `events` SET `event_start_date`=FROM_UNIXTIME(`event_start_date`);
UPDATE `events` SET `event_end_date`=FROM_UNIXTIME(`event_end_date`);

ALTER TABLE `events` CHANGE `event_start_date` `event_start_date` DATETIME default null;
ALTER TABLE `events` CHANGE `event_end_date` `event_end_date` DATETIME default null;

#
# Added support for an event type
#
ALTER TABLE `events` ADD `event_type` TINYINT(3) DEFAULT "0" NOT NULL;
INSERT INTO sysvals (sysval_key_id,sysval_title,sysval_value) VALUES("1", "EventType", "0|General\n1|Appointment\n2|Meeting\n3|All\nDay Event\n4|Anniversary\n5|Reminder");

# This field not used
# Uncomment if you are satisfied this will not cause you any problems
#ALTER TABLE `files` DROP `file_content`;

#
# Fixes from beta1 to release1

# 10/Jul/2003 
# add record access to tasks table
ALTER TABLE `tasks` ADD `task_access` INT(11) NOT NULL DEFAULT '0';

# 30/Aug/2003
# fix lengths of email fields
ALTER TABLE `companies` CHANGE `company_email` `company_email` VARCHAR(255) DEFAULT NULL;
ALTER TABLE `contacts` CHANGE `contact_email` `contact_email` VARCHAR(255) default NULL;
ALTER TABLE `contacts` CHANGE `contact_email2` `contact_email2` VARCHAR(255) default NULL;
ALTER TABLE `users` CHANGE `user_email` `user_email` VARCHAR(255) default '';

# Add notify column to tasks
ALTER TABLE `tasks` ADD `task_notify` INT(11) NOT NULL DEFAULT '0';