CREATE TABLE `zt_teamgroup` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `type` char(30) NOT NULL DEFAULT '',
  `logo` text,
  `name` varchar(255) NOT NULL DEFAULT '',
  `manager` text,
  `parent` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `grade` smallint NOT NULL DEFAULT '1',
  `path` text,
  `status` char(30) NOT NULL DEFAULT 'enable',
  `slogan` varchar(255) NOT NULL DEFAULT '',
  `declaration` text NULL,
  `createdBy` char(30) NOT NULL DEFAULT '',
  `createdDate` date DEFAULT NULL,
  `disbandedDate` date DEFAULT NULL,
  `deleted` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `zt_pi` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `ART` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  `product` text,
  `team` text,
  `status` char(30) NOT NULL DEFAULT 'normal',
  `desc` text,
  `acl` char(30) NOT NULL DEFAULT 'extends',
  `whitelist` text NULL,
  `teamkanban` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `plankanban` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `createdBy` char(30) NOT NULL DEFAULT '',
  `createdDate` date DEFAULT NULL,
  `closedBy` char(30) NOT NULL DEFAULT '',
  `closedDate` date DEFAULT NULL,
  `closedReason` text,
  `activatedBy` char(30) NOT NULL DEFAULT '',
  `activatedDate` date DEFAULT NULL,
  `deleted` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `zt_pistory` (
  `pi` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `story` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `order` mediumint(8) unsigned NOT NULL DEFAULT '0',
  UNIQUE KEY `pi_story` (`pi`,`story`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `zt_piexecution` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pi` int NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  `begin` date DEFAULT NULL,
  `end` date DEFAULT NULL,
  `order` mediumint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `zt_art` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT '',
  `status` char(30) DEFAULT 'normal',
  `product` text NULL,
  `RTE` varchar(255) DEFAULT '',
  `manager` varchar(255) DEFAULT '',
  `PO` varchar(255) DEFAULT '',
  `architect` varchar(255) DEFAULT '',
  `team` varchar(255) DEFAULT '',
  `desc` longtext NULL,
  `acl` enum('open','private') NOT NULL DEFAULT 'open',
  `whitelist` text NULL,
  `createdBy` varchar(255) DEFAULT '',
  `createdDate` date DEFAULT NULL,
  `closedBy` varchar(255) NULL DEFAULT '',
  `closedDate` date NULL,
  `activatedBy` varchar(255) NULL,
  `activatedDate` date NULL,
  `deleted` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `zt_objective` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT '',
  `BV` mediumint unsigned NOT NULL DEFAULT '0',
  `AV` mediumint unsigned NOT NULL DEFAULT '0',
  `promise` char(30) NOT NULL DEFAULT 'no',
  `createdBy` varchar(255) DEFAULT '',
  `createdDate` date DEFAULT NULL,
  `deleted` enum('0','1') NOT NULL DEFAULT '0',
  `pi` mediumint unsigned NOT NULL DEFAULT '0',
  `team` mediumint unsigned NOT NULL DEFAULT '0',
  `type` varchar(30) NOT NULL DEFAULT 'child',
  `links` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `zt_kanbanlinks` (
  `kanban` int(8) NOT NULL,
  `from` char(30) NOT NULL DEFAULT '',
  `to` char(30) NOT NULL DEFAULT '',
  UNIQUE KEY `kanbanlinks` (`kanban`,`from`,`to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `zt_kanbanlane`   ADD `team` mediumint(8) unsigned NOT NULL DEFAULT '0' AFTER `execution`;
ALTER TABLE `zt_kanbancolumn` ADD `execution` mediumint(8) unsigned NOT NULL DEFAULT '0' AFTER `type`;
ALTER TABLE `zt_kanbancolumn` ADD `piexecution` mediumint(8) unsigned NOT NULL DEFAULT '0' AFTER `execution`;
ALTER TABLE `zt_kanbancolumn` ADD `capacity` varchar(255) NOT NULL DEFAULT '0' AFTER `execution`;

ALTER TABLE `zt_team` ADD `teamgroup` mediumint(8) unsigned NOT NULL DEFAULT '0' AFTER `type`;
ALTER TABLE `zt_team` MODIFY type enum('project','task','execution', 'teamgroup') NOT NULL DEFAULT 'project';

ALTER TABLE `zt_risk` Add `team` mediumint(8) unsigned NOT NULL DEFAULT '0' AFTER `execution`;

ALTER TABLE `zt_story` ADD `cardColor` char(30) NOT NULL DEFAULT '' AFTER `color`;

ALTER TABLE `zt_project` ADD `PI` mediumint(8) unsigned NOT NULL DEFAULT '0';
ALTER TABLE `zt_effort` ADD `team` mediumint(8) unsigned NOT NULL DEFAULT '0' AFTER `execution`;
